MySQL基础(2)

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

2018年9月开始接触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 {被授权用户名}@'192.168.1.%';

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

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

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

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

创建和授权后最好重新载入一下使得配置可以即时生效。

FLUSH PRIVILEGES;

使用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}；

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

Table

新建/修改/删除

CREATE/ALTER/DROP {tbl_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