MySQL 基础 (2)- 常用操作

在关系型数据库中，开源数据库 MySQL 作为一个代表，很好地展现了关系型数据库的特性。

虽然基于 MySQL 8.0 进行过系统的学习，平时也在使用 MySQL，还是有必要再次对其进行一次系统的复盘。

SQL

SQL： 一种描述性语言，用于对存储在 RDBMS 中的数据进行增删改查等操作

常用的 SQL 语言种类： DCL，DDL，DML，TCL

MySQL

开源数据库，关系型数据库的代表。

查看帮助信息：

mysql>help [option];

例如：

mysql>help create user; 或者 \h create user;

mysql>show plugins;

mysql>show privileges;

访问控制

常用 Data Control Language(DCL)，主要由 grant 与 revoke 三个语法所组成

在对用户授权之前先要创建用户。

grant

创建完用户后只能使用该账户连接数据库，没有其他权限。需要进行授权后才可以进行增删查改和执行（存储过程等）的权限。

给用户授予某个表上的 use 和 host 列的查询权限：

GRANT select(user,host) on {库名.表名} to {被授权用户名}@'IP';

授予用户某表上所有列的查询权限：

GRANT select on {库名.表名} To {被授权用户名}@'IP';

授予用户某库中所有表的查询权限：

GRANT select on {库名}.* To {被授权用户名}@'IP';

修改后刷新配置使配置即时生效。

FLUSH PRIVILEGES;

查询某用户的权限：

show grants for {username};

使用 grant 命令的注意事项

• 使用 grant 授权的数据库账户必须存在，当然也可以使用 grant 命令同时完成创建用户和授权的操作
• 授权者只能授权自己已经具备的权限给被授权者

revoke

是 grant 的逆向操作，与其使用方法类似，可以类推。

维护数据库对象

通常使用 Data Definition Language(DDL)，主要由 CREATE、ALTER 与 DROP 三个语法所组成。

User

创建用户并设定最大的连接数为 1, 并指定认证方式为 mysql_native_password。（在 MySQL8.0 开始引入了新特性 caching_sha2_password，这种密码加密方式某些客户端不支持；如果不支持，则需要改成 mysql_native_password 这种加密方式使用客户端进行连接。）

CREATE USER {username}@'192.168.1.%' IDENTIFIED WITH 'mysql_native_password' BY '123456' WITH MAX_USER_CONNECTIONS 1;

也可以使用 grant 命令同时完成创建用户和授权的操作：

给指定数据库 TUTORIALS 添加用户 Pwner ，密码为 Pwner123

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON TUTORIALS.*
    -> TO 'Pwner'@'localhost'
    -> IDENTIFIED BY 'Pwner123';

Database

新建 / 修改 / 删除

CREATE/ALTER/DROP {db_name}；

WARNING：慎用一切 drop 命令，该命令会完全删除所有数据且无法通过日志恢复数据，只能通过备份的数据库文件进行恢复。

Table

新建 / 修改 / 删除

CREATE/ALTER/DROP {tbl_name};

修改表结构有时候会影响表内已有的数据，例如枚举型。

查看表结构 / 字段

DESC {tab_name};

清除表内数据保留表结构

TRUNCATE 操作相当于 drop 后执行 create 创建一张相同结构的空表。

TRUNCATE TABLE {tbl_name};

TRUNCATE 的优点：

• 执行效率比 DELETE 语句高
• 不会产生大量二进制日志文件

TRUNCATE 的缺点：

• 不记录二进制日志文件，无法通过反解析二进制日志文件恢复删除的数据

重命名表

RENAME TABLE {原表名} TO {新表名};

使用 rename 还可以对文件进行移动（笔者不禁想起在 Linux 下，如果需要重命名文件，得需要使用 mv 命令）

RENAME table rookery.birds to rookery.birds_old,
test.birds_new to rookery.birds;

将 rookery 数据库的 birds 表改名为 bird_old 表，第二句将 test 数据库的 birds_new 表移到 rookery 数据库中的 birds 表。

Index

CREATE/ALTER/DROP INDEX {index_name} ON {tbl_name};

删除索引不会影响表内数据，但是会影响查询计划的执行。

View

CREATE/ALTER/DROP view；

数据库操作

通常使用 DML（Data Manipulation language），主要由 insert into， delete， update， select 四个语法组成。

INSERT INTO

DELETE

UPDATE

SELECT

WHERE