ubuntu 20.04.3 安装mariadb

安装

执行以下命令即可

sudo apt install mariadb-server # 安装的时候会自动安装mariadb

启动停止

sudo systemctl start mysql.service  # 启动
sudo systemctl stop mysql.service  # 停止

查看启动状态

sudo systemctl status mariadb

绿色的running代表已启动

登录

sudo mysql -u //user_name// -p -h //ip_address// //db_name//

新建用户并授权

# 新建一个test用户，密码为1234567，并授予全部权限，可以从任意主机登录
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY '1234567' WITH GRANT OPTION;

遇到的问题

使用Navicat登录时提示“10061 unknown error”

# 修改配置文件
sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
# 找到如下内容并在前面加#注释
# bind-address = 127.0.0.1
# 修改之后重启mariadb服务
sudo systemctl restart mysql.service

常用sql命令

建库建表

# 建库test
CREATE DATABASE IF NOT EXISTS test;
# 切换到test库
USE test;
# 建表books 
CREATE TABLE IF NOT EXISTS books (
  BookID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
  Title VARCHAR(100) NOT NULL, 
  SeriesID INT, AuthorID INT);
# 建表authors
CREATE TABLE IF NOT EXISTS authors 
(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT);
# 建表series 
CREATE TABLE IF NOT EXISTS series 
(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT);
# 向books 表插入数据
INSERT INTO books (Title,SeriesID,AuthorID) 
VALUES('The Fellowship of the Ring',1,1), 
      ('The Two Towers',1,1), ('The Return of the King',1,1),  
      ('The Sum of All Men',2,2), ('Brotherhood of the Wolf',2,2), 
      ('Wizardborn',2,2), ('The Hobbbit',0,1);

查询验证

show databases;  # 展示所有库
show tables;  # 展示所有表