在关系型数据库中,开源数据库 MySQL
作为一个代表,很好地展现了关系型数据库的特性。
虽然基于 MySQL 8.0
进行过系统的学习,平时也在使用 MySQL
,还是有必要再次对其进行一次系统的复盘。
SQL
SQL: 一种描述性语言,用于对存储在 RDBMS
中的数据进行增删改查等操作
常用的 SQL 语言种类: DCL,DDL,DML,TCL
MySQL
开源数据库,关系型数据库的代表。
查看帮助信息:
mysql>help [option];
例如:
1 | mysql>help create user; 或者 \h create user; |
访问控制
常用 Data Control Language(DCL),主要由 grant
与 revoke
三个语法所组成
在对用户授权之前先要创建用户。
grant
创建完用户后只能使用该账户连接数据库,没有其他权限。需要进行授权后才可以进行增删查改和执行(存储过程等)的权限。
给用户授予某个表上的 use 和 host 列的查询权限:
1 | GRANT select(user,host) on {库名.表名} to {被授权用户名}@'IP'; |
授予用户某表上所有列的查询权限:
1 | GRANT select on {库名.表名} To {被授权用户名}@'IP'; |
授予用户某库中所有表的查询权限:
1 | GRANT select on {库名}.* To {被授权用户名}@'IP'; |
修改后刷新配置使配置即时生效。
1 | FLUSH PRIVILEGES; |
查询某用户的权限:
1 | 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
这种加密方式使用客户端进行连接。)
1 | CREATE USER {username}@'192.168.1.%' IDENTIFIED WITH 'mysql_native_password' BY '123456' WITH MAX_USER_CONNECTIONS 1; |
也可以使用 grant 命令同时完成创建用户和授权的操作:
给指定数据库 TUTORIALS 添加用户 Pwner
,密码为 Pwner123
1 | mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP |
Database
新建 / 修改 / 删除
1 | CREATE/ALTER/DROP {db_name}; |
WARNING:慎用一切 drop
命令,该命令会完全删除所有数据且无法通过日志恢复数据,只能通过备份的数据库文件进行恢复。
Table
新建 / 修改 / 删除
1 | CREATE/ALTER/DROP {tbl_name}; |
修改表结构有时候会影响表内已有的数据,例如枚举型。
查看表结构 / 字段
1 | DESC {tab_name}; |
清除表内数据保留表结构
TRUNCATE
操作相当于 drop
后执行 create
创建一张相同结构的空表。
1 | TRUNCATE TABLE {tbl_name}; |
TRUNCATE 的优点:
- 执行效率比 DELETE 语句高
- 不会产生大量二进制日志文件
TRUNCATE 的缺点:
- 不记录二进制日志文件,无法通过反解析二进制日志文件恢复删除的数据
重命名表
1 | RENAME TABLE {原表名} TO {新表名}; |
使用 rename
还可以对文件进行移动(笔者不禁想起在 Linux 下,如果需要重命名文件,得需要使用 mv
命令)
1 | RENAME table rookery.birds to rookery.birds_old, |
将 rookery
数据库的 birds
表改名为 bird_old
表,第二句将 test
数据库的 birds_new
表移到 rookery
数据库中的 birds
表。
Index
1 | CREATE/ALTER/DROP INDEX {index_name} ON {tbl_name}; |
删除索引不会影响表内数据,但是会影响查询计划的执行。
View
1 | CREATE/ALTER/DROP view; |
数据库操作
通常使用 DML(Data Manipulation language),主要由 insert into
, delete
, update
, select
四个语法组成。