字节流的博客

DDL、DML 和 DCL

0. 数据准备

需要操作的表如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
`user` 表
mysql> DESC user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

`info` 表
mysql> DESC info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| school | varchar(10) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+

`salary` 表
mysql> DESC salary;
+--------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| money | int(11) | YES | | NULL | |
| time | timestamp | NO | | CURRENT_TIMESTAMP | |
| depart | varchar(100) | YES | | NULL | |
+--------+--------------+------+-----+-------------------+----------------+

1. DDL 常用命令

DDL,数据定义语言,Data Define Language,常见命令包括CREATE、DROP和ALTER等;

  • 修改字段名称及类型 ALTTER TABLE user CHANGE age ages INT [FIRST|AFTER clo_name]
  • 添加字段 ALTTER TABLE user ADD school VARCHAR(100) [FIRST|AFTER clo_name]
  • 修改字段类型 ALTER TABLE user MODIFY age VARCHAR(10) [FIRST|AFTER clo_name]
  • 删除字段 ALTER TABLE user DROP clo_name
  • 修改表名 ALTER TABLE user RENAME [TO] b

2. DML 常用命令

DML,数据操纵语句,Date Manipulation Language,常用命令包括SELECT、UPDATE、INSERT和DELETE等;

  • 插入多条数据,以逗号分割:INSERT INTO user VALUES(1,'a'),(2,'b')
  • 修改多表数据,以逗号分割:UPDATE user u,info i SET u.id = u.id + 3,i.id = i.id + 3 WHERE u.id = i.id
  • 删除多表数据:DELETE u,i FROM user u, info i WHERE u.id = i.id
  • 查询不重复数据:SELECT DISTINCT * FROM info
  • 因为在Windows下不严格区分大小写, DISTINCT 命令查询出的内容也不区分大小写,所以字段修改为 BINARY 类型的:
    ALTER TABLE info MODIFY school VARCHAR(10) BINARY
  • 结果集排序(多限制条件):
    SELECT * FROM salary [WHERE condition] ORDER BY money desc, id ASC [field DESC|ASC ...] 【注:如果多条件限制时,前面的条件导致结果一致时,按后续条件继续排序】
  • 结果集限制条目:
    SELECT * FROM salary LIMIT 1, 5 【注:[LIMIT offset_start,row_count],offset_start起始偏移量,row_count显示的行数;】

3. DCL 常用命令

DCL,数据控制语句,Data Control Language,常见命令包括GRANT和REVOKE等;

(1). 聚合

语法如下:

1
2
3
4
5
6
SELECT [field1, field2, ...] fun_name 
FROM table_name
[WHERE condition]
[GROUP BY field1, field2, ...
[WITH ROLLUP]]
[HAVING where_condition]

其中:

  • fun_name 聚合函数,常用的有SUM(求和),COUNT(记录数),MAX(最大值),MIN(最小值);
  • GROUP BY 要进行聚合的字段;
  • WITH ROLLUP 可选语法,表示对分类聚合后的结果进行再汇总。如计算每一年的利润, GROUP BY year ,加上 WITH ROLLUP ,可以汇总所有年份的总利润;

示例:

  • 查询各部门工资总和 SELECT depart,SUM(money) FROM salary GROUP BY depart
  • WITH ROLLUP SELECT depart,SUM(money) FROM salary GROUP BY depart WITH ROLLUP
  • 可以在GROUP BY后添加DESC|ASC排序 SELECT depart,SUM(money) FROM salary GROUP BY depart DESC
  • ORDER BY 和 GROUP BY互斥,即不可同时存在,可用别名绕过 SELECT depart,SUM(money) AS moneys FROM salary GROUP BY depart WITH ROLLUP ORDER BY moneys DESC

(2). 连接

  • 内连接:仅选出两张表中互相匹配的记录;【常用】
  • 外连接:会选出其他不匹配的记录。
    • 左连接:包含所有的左边表中的记录甚至是右边表中没有和他匹配的记录;
    • 右连接:包含所有的右边表中的记录甚至是左边表中没有和他匹配的记录;

示例:

  • 内连接 SELECT name,school FROM user,info WHERE user.id=info.id
  • 左连接 SELECT name,school FROM user LEFT JOIN info ON user.id=info.id
  • 右连接 SELECT name,school FROM user RIGHT JOIN info ON user.id=info.id

(3). 联合

  • UNION ALL: 将两个表查询的结果集合并在一起。
  • UNION:和UNION ALL 不同的是UNIONUNION ALL的结果执行一次DISTINCT,去除重复结果;

示例:
UNION:SELECT id FROM user UNION SELECT id FROM info
UNION ALL:SELECT id FROM user UNION ALL SELECT id FROM info

(4). 权限分配

  • GRANT 为数据库 test1 创建用户 zzz ,密码为 mmm ,权限是可以对该数据库所有表进行 SELECTINSERT 操作:GRANT SELECT,INSERT ON test1.* to 'zzz'@'localhost' INDENTIFIED BY 'mmm'
  • 收回 INSERT 权限:REVOKE INSERT ON test1.* FROM 'zzz'@'localhost'

4. 帮助

输入 mysql>? CONTENTS 命令即可获取帮助;

Thanks! 😊