【Linux】MySQL数据库的使用

linux中部署MySQL详见我的上一篇博文：
Ubuntu22部署MySQL5.7详细教程

本篇文章将介绍在Ubuntu中如何使用MySQL数据库。

一、访问MySQL数据库

首先使用root用户登录MySQL

mysql -u root -p

验证成功以后将会进入提示符为‘mysql>”的数据库操作环境，用户可以输入各种操作语句对数据库进行管理。

每条MySQL操作语句以分号 ‘;’ 表示结束，输入时可以不区分大小写。

status;表示查看当前数据库服务的基本信息。

status;

show databases;表示用于查看当前MySQL服务器中包含的库。

show databases;

经初始化后的MySQL服务器，默认建立了四个库：test.mysql.information_schema和performance_schema（其中mysql库是mysql正常运行所需的库，其中包含了用户认证相关的表）。

使用Navicat远程连接数据库可以看到这四个库

show tables;用于查看当前所在的库中包含的表。 在操作之前，需要先使用USE语句切换到所使用的库，例如，执行以下操作可以显示mysql库中包含的所有表。

use mysql;
show tables;

describe用于显示表的结构，即组成表的各字段（列）的信息。需要指定“库名.表名” 作为参数；若只指定表名参数，则需先通过USE语句切换到目标库。 例如，执行以下操作可以查看mysql库中的user表的结构。与直接执行“DESCRIBE mysql.user;’”语句的效果相同。

use mysql;
describe user;

在“mysql>”操作环境中，执行 “exit”或“quit”命令 可以退出mysql命令工具，返回原来的Shell环境。

二、创建及删除库和表

1、创建新的库

CREATE DATABASE语句：用于创建一个新的库，需指定数据库名称作为参数。 例如，执行以下操作可以创建一个名为auth的库。

create database auth;

刚创建的数据库是空的，其中不包含任何表，在数据库存放目录下会自动生成一个与新建的库名相同的文件夹。

使用以下命令可以查看数据库存放目录。

SHOW VARIABLES LIKE 'datadir';

可见在数据库存放目录下新增了一个auth文件夹。

2、创建新的表

CREATE TABLE 语句：用于在当前库中创建新的表，需指定数据表名称作为参数，并定义该表格所使用的各字段，基本格式如下所示。

CREATE TABLE 表名 (字段1名称 类型, 字段2名称 类型, ..., PRIMARY KEY (主键名))

创建表之前，应先明确数据表格的结构、各字段的名称和类型等信息，例如，若要创建一个包含用户名、密码字串的用户验证表，应先分析表格结构。

用户验证表内容

用户名	密码字串
zhangsan	123456
lisi	654321

上述表格中，“用户名”为不超过16字节的字符串，不能为空；“密码字串”为不超过48个字符的字符串（插入记录时使用MySQL的函数加密）。默认值为空字串。考虑到字符集兼容性，最好不要使用中文字段名，改用user_name,user_passwd分别表示用户名、密码字串，表格中不能拥有同名的用户。因此可将 user_name作为主键。

针对上述分析结果，可以在auth库中按如下操作创建users表，其中，字段定义部分的DEFAULT 语句用于设置默认的密码字串，PRIMARY语句用于设置主键字段名。

USE auth;
CREATE TABLE users (user_name CHAR(16) NOT NULL, user_passwd CHAR(48) DEFAULT '', PRIMARY KEY (user_name));

在Navicat中刷新连接可以看到新增的auth库和users表。

3、删除一个数据表

DROP TABLE语句：用于删除库中的表，需要指定“库名.表名”作为参数；若只指定表名参数， 则需先通过执行“USE”语句切换到目标库。

例如，执行以下操作可以删除auth库中的users表。

DROP TABLE auth.users;

4、删除一个数据库

DROP DATABASE语句：用于删除指定的库，需要指定库名作为参数。

例如，执行以下操作可以删除名为auth的库。

DROP DATABASE auth;

三、管理表中的数据记录

1、插入数据记录

INSERT INTO语句：用于向表中插入新的数据记录，语句格式如下所示。

INSERT INTO 表名(字段1, 字段2, ...) VALUES(字段1的值, 字段2的值, ...)

执行以下操作将会向 auth 库中的 users表插入一条记录：用户名为“zhangsan”，对应的密码为 “123456”，需注意的是，VALUES部分的值应与前面指定的各字段逐一对应。

USE auth;
INSERT INTO users(user_name, user_passwd) VALUES('zhangsan', PASSWORD('123456'));

在插入新的数据记录时，如果这条记录完整包括表中所有字段的值，则插入语句中指定字段的 部分可以省略。例如，执行以下操作也可以向auth库中的user表插入一条新的记录：用户名为“lisi” 对应的密码为“654321”。

INSERT INTO users VALUES('lisi', PASSWORD('654321'));

2、查询数据记录

SELECT语句：用于从指定的表中查找符合条件的数据记录。 MySQL数据库支持标准的SQL查询语句，语句格式如下所示。

SELECT 字段名1, 字段名2, ... FROM 表名 WHERE 条件表达式

表示所有字段时，可以使用通配符“*”，若要显示所有的数据记录，则可以省略WHERE条件子句。 例如，执行以下操作可以查看auth库中users表内的所有数据记录，其中由于密码字串已加密，因此不会直接显示出实际的密码内容。

SELECT * FROM auth.users;

当需要根据特定的条件查找记录时，WHERE条件子句是必不可少的。 例如，若要查找users表 中用户名为“zhangsan”的记录，显示其中用户名，密码字段的信息，可以执行以下操作。

SELECT user_name, user_passwd FROM auth.users WHERE user_name = 'zhangsan';

3、修改数据记录

UPDATE语句：用于修改、更新表中的数据记录，语句格式如下所示。

UPDATE 表名 SET 字段名1=字段值1[字段名2=字段值2] WHERE 条件表达式

执行以下操作可以修改users表中用户名为“lisi”的记录，将密码字串设为空值，验证记录内容。可以发现 lisi 用户的密码串值已变为空白。

UPDATE auth.users SET user_passwd=PASSWORD('') WHERE user_name='lisi';
SELECT * FROM auth.users;

在MySQL数据库服务器中，用于访问数据库的各种用户（如root）信息都保存在mysql库的user表中，熟练的管理员可以直接修改其中的数据记录。例如，以下操作可以将数据库用户root的密码设为“123457”，当再次使用“mysql-u root-p”访问MySQL数据库服务器时，必须使用此密码进行验证。FLUSH PRIVILEGES;用于刷新用户授权信息。

UPDATE mysql.user SET password=PASSWORD('1234567') WHERE user='root';
FLUSH PRIVILEGES;

若是在 Linux 命令行环境中执行，还可以使用mysqladmin 工具来设置密码，命令格式为 “mysqladmin-u root-p“旧密码password“新密码“。例如，执行以下操作可将数据库用户root 的密码设置为“123456”。

mysqladmin -u root -p '1234567' password '123456'

4、删除数据记录

DELETE语句：用于删除表中指定的数据记录。语句格式如下所示。

DELETE FROM 表名 WHERE 条件表达式

执行以下操作可以删除users表中用户名为“lisi”的数据记录，验证记录内容可以发现，lisi用户的数据记录已经消失。

DELETE FROM auth.users WHERE user_name='lisi';
SELECT * FROM auth.users;

在MySQL数据库服务器中，默认添加了从本机访问数据库的来宾用户（user、password均为空）。基于数据库安全性考虑，应该删除这些来宾用户。

执行以下操作显示user字段为空的用户记录，并用DELETE 语句进行该用户记录的删除。

SELECT user,host FROM mysql.user WHERE user='';
DELECT FROM mysql.user WHERE user='';

四、数据库用户授权

1、授予权限

GRANT语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时，GRANT语句将会创建新的用户，否则，GRANT语句用于修改用户信息，语句格式如下所示。

GRANT 权限列表 ON 库名.表名 TO 用户名@来源地址 [IDENTIFIRF BY '密码']

• 权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select,insert,update"。使用 “all”表示所有权限 ，可授权执行任何操作。
• 库名.表名：用于指定授权操作的库和表的名称，其中可以使用通配符 “*” 。例如，使用 “auth.*” 表示授权操作的对象为auth库中的所有表。
• 用户名@来源地址：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。 来源地址可以是域名，IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.bdqn.com”，“192.168.1.%”等。
• DENTIFEDBY：用于设置用户连接数据库时所使用的密码字符串。 在新建用户时，若省略 “IDENTIFEDBY”部分，则用户的密码将为空。

执行以下操作可以添加一个名为“xiaoqi”的数据库用户，并允许其从本机访问，对auth库中的所有表具有查询权限，验证密码为“123456”。使用GRANT语句授权的用户记录，会保存到mysql 库的user、db、tables_priv等相关表中，无须刷新即可生效。

GRANT select ON auth.* TO 'xiaoqi'@'localhost' IDENTIFIED BY '123456';

切换到其他Shell终端，以用户xiaoqi的身份连接数据库，当执行授权的数据库操作时将被允许， 而执行非授权的数据库操作时将被拒绝。例如，允许用户xiaoqi 查询auth 库中 users表的数据记录， 但禁止查询其他库中的表的记录。

用户xiaoqi登录数据库

mysql -u xiaoqi -p

验证授权的访问操作

SELECT * FROM auth.users;

验证非授权的访问操作

SELECT * FROM mysql.user;

在企业服务器的应用中，数据库与网站服务器有时候是相互独立的。因此在MySQL服务器中，应根据实际情况创建新的用户授权，允许授权用户从网站服务器访问数据库。通常的做法是，创建一个或几个网站专用的库，并授予所有权限，限制访问的来源IP地址。

例如，执行以下操作可以新建bdqn库，并授权从IP地址为192.168.4.19的主机连接，用户名为“dbuser”，密码为“pwd@123”。允许在bdqn库中执行所有操作。

CREATE DATABASE bdqn;
GRANT all ON bdqn.* TO 'dbuser'@'192.168.4.19' IDENTIFIED BY 'pwd@123';

2、查看权限

SHOW GRANTS语句：专门用来查看数据库用户的授权信息，通过FOR子句可指定查看的用户对象（必须与授权时使用的对象名称一致）语句格式如下所示。

SHOW GRANTS FOR 用户名@来源地址

执行以下操作可以查看用户dbuser从主机192.168.4.19访问数据库时的授权信息。其中USAGE权限对应的授权记录中包含了用户的连接密码字串。

SHOW GRANTS FOR 'dbuser'@'192.168.4.19';

3、撤销权限

REVOKE语句：用于撤销指定用户的数据库权限，撤销权限后的用户仍然可以连接到MySQL服务器，但将被禁止执行对应的数据库操作。 语句格式如下所示。

REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址

执行以下操作可以撤销用户xiaoqi从本机访问数据库auth的所有权限。

REVOKE all ON auth.* FROM 'xiaoqi'@'localhost';
SHOW GRANTS FOR 'xiaoqi'@'localhost';

参考资料：
Linux——MySQL数据库的使用