2022.3.23 DDL、DML、DQL、DCL

通用语法及分类

DDL： Data Definition Language, 数据定义语言，用来定义数据库对象（数据库、表、字段）
DML：Data Manipulation Language, 数据操作语言，用来对数据库表中的数据进行增删改
DQL：Data Query Language, 数据查询语言，用来查询数据库中表的记录
DCL：Data Control Language, 数据控制语言，用来创建数据库用户、控制数据库的控制权限

DDL（数据定义语言）

数据定义语言

数据库操作

 1 查询所有数据库： SHOW DATABASES;
 2 
 3 查询当前数据库： SELECT DATABASE();
 4 
 5 创建数据库： CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集] [COLLATE 排序规则 ];
 6 
 7 删除数据库： DROP DATABASE [ IF EXISTS ] 数据库名;
 8 
 9 使用数据库： USE 数据库名;
10 
11 查询数据库的建库语句： SHOW CREATE DATABASE 数据库名;

1 CREATE TABLE [IF NOT EXISTS] `表名`(
2     `字段名` 列类型 [属性] [索引] [注释],
3     `字段名` 列类型 [属性] [索引] [注释],
4      .........
5     `字段名` 列类型 [属性] [索引] [注释]
6 )[表类型] [字符集设置] [注释]

 1 -- 注意点：表的名称和字段尽量使用``括起来
 2 -- AUTO_INCREMENT (自增) COMMENT(注释) DEFAULT(默认值)
 3 -- 字符串用单引号括起来
 4 -- 所有的语句后面加 , 最后一句不用加
 5 -- PRIMARY KEY 主键一般加最后面一行，一般一个表只有一个
 6 
 7 CREATE TABLE IF NOT EXISTS `student` (
 8     -- 设置id列，长度4位的0填充(不够4位补全),自增,注释为学号
 9    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',    
10     -- 设置姓名列，默认值为匿名
11    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
12    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
13    `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
14                         -- 默认值为空
15    `birthday` DATETIME  DEFAULT NULL COMMENT '出生日期',
16    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
17    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
18     PRIMARY KEY(`id`)
19    
20 )ENGINE=INNODB DEFAULT CHARSET=utf8

注意事项

UTF8字符集长度为3字节，有些符号占4字节，所以推荐用utf8mb4字符集

表操作

1 查询当前数据库所有表： SHOW TABLES;
2 
3 查询表结构： DESC 表名;
4 
5 查询指定表的建表语句： SHOW CREATE TABLE 表名;

创建表：

1  CREATE TABLE 表名(
2      字段1 字段1类型 [COMMENT 字段1注释],
3      字段2 字段2类型 [COMMENT 字段2注释],
4      字段3 字段3类型 [COMMENT 字段3注释],
5      ...
6      字段n 字段n类型 [COMMENT 字段n注释]
7  )[ COMMENT 表注释 ];

最后一个字段后面没有逗号

 1 添加字段： ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束]; 例：ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';
 2 
 3 修改数据类型： ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
 4 
 5 修改字段名和字段类型： ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束]; 例：将emp表的nickname字段修改为username，类型为varchar(30) ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';
 6 
 7 删除字段： ALTER TABLE 表名 DROP 字段名;
 8 
 9 修改表名： ALTER TABLE 表名 RENAME TO/AS 新表名
10 
11 删除表： DROP TABLE [IF EXISTS] 表名;
12 
13 删除(截断)表，并重新创建该表： TRUNCATE TABLE 表名;

类型

 decimal：精度就是总位数，标度就是小数点后的位数
 age tinyint unsigned  --年龄没有负数 后面可以加unsigned
 score double(4,1)     --分数一般用double 参数1为总位数（100.0），参数2为小数点后的位数
 用户名 username varchar(50) -- 用户名不能超过50个字符
 性别 gender char(1)     -- 性别只占一个字符

反向推出Sql语句：选中你要查看的sql语句定义，运行

复制

 CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */

这就是创建数据库时的Sql语句

DML（数据操作语言）

插入元组

1 指定字段： INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
2 
3 全部字段： INSERT INTO 表名 VALUES (值1, 值2, ...);
4 
5 批量添加数据： INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...); INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

INTO子句

属性列的顺序可与表定义中的顺序不一致
没有指定属性列
指定部分属性列

VALUES子句

提供的值必须与INTO子句匹配
值的个数
值的类型

注意事项

字符串和日期类型数据应该包含在引号中
插入的数据大小应该在字段的规定范围内

插入子查询结果

语句格式

1  INSERT 
2  INTO <表名>  [(<属性列1> [，<属性列2>…  )]
3  子查询；

功能

将子查询结果插入指定表中

INTO子句(与插入元组类似)

子查询

SELECT子句目标列必须与INTO子句匹配值的个数值的类型

[例4] 对每一个系，求学生的平均年龄，并把结果存入数据库。

第一步：建表

1  CREATE TABLE Dept_age(
2  Sdept CHAR(15),          -- 系名
3  Avg_age SMALLINT         -- 学生平均年龄
4  );

第二步：插入数据

1  -- 插入数据
2  -- 先分组，再计算每个分组对应的AVG(Sage)
3  INSERT 
4  INTO Dept_age (Sdept,Avg_age)
5  SELECT Sdept,AVG(Sage)
6  FROM Student
7  GROUP BY Sdept;

更新和删除数据

修改数据：不写where会修改所有的数据 UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [ WHERE 条件 ]; 例： UPDATE emp SET name = 'Jack' WHERE id = 1;
删除数据：不写where会删除所有的数据 DELETE FROM 表名 [ WHERE 条件 ];

TRUNCATE 命令：完全清空数据库，表的结构和索引约束不会变

1  TRUNCATE 表名
2  
3  -- 清空student表
4  TRUNCATE `student`;

TRUNCATE与delete区别：

TRUNCATE重新设置自增列，计数器归0，而delete会继续自增
TRUNCATE 不会影响事务

了解即可：DELETE删除的问题，删除完重启数据库

InnoDB自增列会重1开始(存在内存当中的，断电即失)
MylSAM继续从上一个自增量开始(存在文件中的，不会丢失)

DQL（数据查询语言）

语法：编写顺序

 1  SELECT 字段列表
 2  FROM 表名字段
 3  WHERE 条件列表
 4  GROUP BY 分组字段列表
 5  HAVING 分组后的条件列表
 6  ORDER BY 排序字段列表
 7  LIMIT 分页参数
 8  
 9  
10  SELECT [ALL | DISTINCT]
11  {* | table.* | [table.field1[as alias1][, table.field2[as alias2]][,...]]}
12  FROM table_name [as table_alias]
13      [left | right | inner join table_name2] --联合查询
14      [WHERE ...]--指定结果需满足的条件
15      [GROUP BY ...]--指定结果按照哪几个字段来分组
16      [HAVING]--过滤分组的记录必须满足的次要条件
17      [ORDER BY ...]--指定查询记录按一个或多个条件排序
18      [LIMIT {[offset,]row_count | row_countOFFSET offset}];
19      --指定查询的记录从哪条至哪条
20

基础查询

1 查询多个字段： SELECT 字段1, 字段2, 字段3, ... FROM 表名; SELECT * FROM 表名;
2 
3 设置别名： SELECT 字段1 [ AS 别名1 ], 字段2 [ AS 别名2 ], 字段3 [ AS 别名3 ], ... FROM 表名; SELECT 字段1 [ 别名1 ], 字段2 [ 别名2 ], 字段3 [ 别名3 ], ... FROM 表名;
4 
5 去除重复记录： SELECT DISTINCT 字段列表 FROM 表名;
6 
7 转义： SELECT * FROM 表名 WHERE name LIKE '/_张三' ESCAPE '/' / 之后的_不作为通配符

条件查询

语法： SELECT 字段列表 FROM 表名 WHERE 条件列表;

条件：

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
!>	不大于
!<	不小于
BETWEEN ... AND ...	在某个范围内（含最小、最大值）
IN(...)	在in之后的列表中的值，多选一
LIKE 占位符	模糊匹配（_匹配单个字符，%匹配任意个字符）
NOT+上述操作符
IS NULL	是NULL
IS NOT NULL

LINK 模糊匹配

通配符：

%：任意长度字符串，a%b：以a为开头b为结尾任意字符串包括ab
_ ：单个字符 a_b ：以a为开头，b为结尾长度为3的字符串
汉子占2字节

使用换码字符将通配符转义为普通字符

ESCAPE '/ ' 表示“ / ” 为换码字符，“ / ” 后面不是通配符含义，而是一个字符

逻辑运算符	功能
AND 或 &&	并且（多个条件同时成立）
OR 或 \|\|	或者（多个条件任意一个成立）
NOT 或 !	非，不是

例子：

 1  -- 年龄等于30
 2  select * from employee where age = 30;
 3  -- 年龄小于30
 4  select * from employee where age < 30;
 5  -- 小于等于
 6  select * from employee where age <= 30;
 7  -- 没有身份证
 8  select * from employee where idcard is null or idcard = '';
 9  -- 有身份证
10  select * from employee where idcard;
11  select * from employee where idcard is not null;
12  -- 不等于
13  select * from employee where age != 30;
14  select * from employee where age <> 30;
15  -- 年龄在20到30之间[20,30]
16  select * from employee where age between 20 and 30;
17  select * from employee where age >= 20 and age <= 30;
18  -- 下面语句不报错，但查不到任何信息
19  select * from employee where age between 30 and 20;
20  -- 性别为女且年龄小于30
21  select * from employee where age < 30 and gender = '女';
22  -- 年龄等于25或30或35
23  select * from employee where age = 25 or age = 30 or age = 35;
24  select * from employee where age in (25, 30, 35);
25  -- 姓名为两个字
26  select * from employee where name like '__';
27  -- 身份证最后为X
28  select * from employee where idcard like '%X';
29  
30  
31  
32  -- 函数 Concat(a,b)连接字符串a,b
33  SELECT CONCAT('姓名：',Sname) AS 学生姓名
34  FROM Student
35  
36  SELECT CONCAT('姓名：',Sname) 学生姓名
37  FROM Student
38  
39  SELECT VERSION() -- 函数
40  SELECT 3*10+1    -- 表达式
41  
42  -- `Avg_age`列的值都加1
43  SELECT `Sdept`,`Avg_age`+1 AS '平均年龄+1'
44  FROM dept_age
45  
46  -- ESCAPE '/' 表示 "/" 为换码字符，"/"后面不是通配符含义，而是一个字符
47  SELECT Cno,Ccredit
48  FROM Course
49  WHERE Cname LIKE 'DB/_Design'ESCAPE'/';
50

聚合查询（聚合函数）

常见聚合函数：

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

注意事项

null值不参数所有聚合函数运算,count(1)和count(*)会记录NULL值

语法： SELECT 聚合函数(字段列表) FROM 表名;

 1  -- 聚合函数
 2  -- 1. 统计该企业员工数量
 3  select count(id) from emp;
 4  select count(*) from emp;
 5  
 6  -- 2. 统计该企业员工的平均年龄
 7  select avg(age) from emp;
 8  
 9  -- 3. 统计该企业员工的最大年龄
10  select max(age) from emp;
11  
12  -- 4. 统计该企业员工的最小年龄
13  select min(age) from emp;
14  
15  -- 5. 统计西安地区员工的年龄之和
16  select sum(age) from emp where workaddress = '西安';
17

分组查询

语法： SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后的过滤条件 ];

GROUP BY

未对查询结果分组，聚集函数将作用于整个查询结果
对查询结果分组后，聚集函数将分别作用于每个组
作用对象是查询的中间结果表
按指定的一列或多列值分组，值相等的为一组

where 和 having 的区别：

执行时机不同：where是分组之前进行过滤，不满足where条件不参与分组；having是分组后对结果进行过滤。
判断条件不同：where不能对聚合函数进行判断，而having可以。

例子：

 1  -- 根据性别分组，统计男性和女性数量（只显示分组数量，不显示哪个是男哪个是女）
 2  select count(*) from employee group by gender;
 3  -- 根据性别分组，统计男性和女性数量
 4  select gender, count(*) from employee group by gender;
 5  
 6  -- 根据性别分组，统计男性和女性的平均年龄
 7  select gender, avg(age) from employee group by gender;
 8  
 9  -- 年龄小于45，并根据工作地址分组
10  select workaddress, count(*) from employee where age < 45 group by workaddress;
11  -- 年龄小于45，并根据工作地址分组，获取员工数量大于等于3的工作地址
12  select workaddress, count(*) address_count from employee where age < 45 group by workaddress having address_count >= 3;

注意事项

执行顺序：where > 聚合函数 > having
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

排序查询

语法： SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;

排序方式：

ASC: 升序（默认）
DESC: 降序

例子：

1  -- 根据年龄升序排序
2  SELECT * FROM employee ORDER BY age ASC;
3  SELECT * FROM employee ORDER BY age;
4  
5  -- 两字段排序，根据年龄升序排序，入职时间降序排序
6  SELECT * FROM employee ORDER BY age ASC, entrydate DESC;

注意事项

如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

分页查询

语法： SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

例子：

 1  -- 查询第一页数据，展示10条
 2  SELECT * FROM employee LIMIT 0, 10; -- 0为起始数据行,10为页面大小（显示10行）显示1-10行
 3  
 4  -- 查询第二页
 5  SELECT * FROM employee LIMIT 10, 10;
 6  
 7  -- LIMIT 1,5;  显示2-6行
 8  
 9  -- 第一页 limit 0,5
10  -- 第二页 limit 5,5
11  -- 第三页 limit 10,5
12  ...
13  -- 第N页  limit (N-1)*5,5

注意事项

起始索引从0开始，起始索引 = （查询页码 - 1） * 每页显示记录数
分页查询是数据库的方言，不同数据库有不同实现，MySQL是LIMIT
如果查询的是第一页数据，起始索引可以省略，直接简写 LIMIT 10

DQL语句练习

 1  -- 1. 年龄20,21,22,23的女性 (条件查询)
 2  select * from emp where gender='女' and age in (20,21,22,23);
 3  
 4  -- 2. 男性，年龄20-40（含），姓名三个字 (条件查询)
 5  select * from emp where gender='男' and (age between 20 and 40) and name like '___';
 6  
 7  -- 3. 年龄 < 60 的男女人数 (分组查询,聚合查询)
 8  select gender, count(*) from emp where age < 60 group by gender;
 9  
10  -- 4. 年龄 <= 35 的姓名和年龄，年龄升序，相同，入职时间降序 (排序查询)
11  select name,age from emp where age <= 35 order by age ASC, entrydate DESC;
12  
13  -- 5. 性别男，年龄20-40（含）之内的前5个，年龄升序，相同，入职时间升序 (排序查询，分页查询)
14  select name,gender,age,entrydate from emp where gender='男' and (age between 20 and 40) order by age ASC, entrydate ASC limit 0,5;

DQL执行顺序(不是编写顺序)

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

多表查询

合并查询（笛卡尔积，会展示所有组合结果）

table1=子表，table2=父表

1  select * from table1, table2;

笛卡尔积：两个集合A集合和B集合的所有组合情况（在多表查询时，需要消除无效的笛卡尔积）

消除无效笛卡尔积：

1  select * from table1, table2 
2  where table1.table2_id = table2.id;

对查询结果去重：

distinct

1  -- 查询拥有员工的部门ID、部门名称
2  select distinct e.dept_id, d.name from emp as e, dept as d where e.dept_id = d.id;

案例

 1  -- 创建dept表，并插入数据
 2  create table dept
 3  (
 4      id   int auto_increment comment 'ID' primary key,
 5      name varchar(50) not null comment '部门名称'
 6  ) comment '部门表';
 7  
 8  INSERT INTO dept (id, name)
 9  VALUES (1, '研发部'),
10         (2, '市场部'),
11         (3, '财务部'),
12         (4, '销售部'),
13         (5, '总经办'),
14         (6, '人事部');
15  
16  -- 创建emp表，并插入数据
17  create table emp
18  (
19      id        int auto_increment comment 'ID' primary key,
20      name      varchar(50) not null comment '姓名',
21      age       int comment '年龄',
22      job       varchar(20) comment '职位',
23      salary    int comment '薪资',
24      entrydate date comment '入职时间',
25      managerid int comment '直属领导ID',
26      dept_id   int comment '部门ID'
27  ) comment '员工表';
28  
29  -- 添加外键
30  alter table emp
31      add constraint fk_emp_dept_id foreign key (dept_id) references dept (id);
32  
33  INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id)
34  VALUES (1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5),
35         (2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
36         (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
37         (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
38         (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
39         (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1),
40         (7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3),
41         (8, '周芷若', 19, '会计', 48000, '2006-06-02', 7, 3),
42         (9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3),
43         (10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2),
44         (11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2),
45         (12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2),
46         (13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2),
47         (14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4),
48         (15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4),
49         (16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4),
50         (17, '陈友谅', 42, null, 2000, '2011-10-12', 1, null);
51         
52         
53  -- 合并查询（笛卡尔积，会展示所有组合结果）
54  select * from emp, dept;
55  select * from emp, dept where emp.dept_id = dept.id;

联表查询

用于把来自两个或多个表的行结合起来

操作	描述
INNER JOIN (JOIN)	返回匹配的行
LEFT JOIN (LEFT OUTER JOIN)	返回左表所有的行，即使右表中没有匹配，如果右表中没有匹配，则结果为 NULL。
RIGHT JOIN (RIGHT OUTER JOIN)	返回右表所有的行，即使左表中没有匹配，如果左表中没有匹配，则结果为 NULL。
FULL JOIN (FULL OUTER JOIN)	关键字只要左表和右表其中一个表中存在匹配，则返回行。结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

内连接查询

内连接查询的是两张表交集的部分

隐式内连接： SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;

显式内连接： SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ...;

显式性能比隐式高

例子：

1  -- 查询员工姓名，关联部门名称 (隐式)
2  SELECT emp.name, dept.name FROM emp, dept WHERE emp.dept_id = dept.id ;
3  SELECT e.name, d.name FROM emp e, dept d WHERE e.dept_id = d.id;
4  -- 如果为表起了别名，就不能再通过原表名来限定字段
5  
6  -- 查询员工姓名，关联部门名称 (显式)
7  SELECT e.name, d.name FROM emp AS e JOIN dept AS d ON e.dept_id = d.id;

外连接查询

左外连接：查询左表所有数据，以及两张表交集部分数据 SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ...; 相当于查询表1的所有数据，包含表1和表2交集部分数据

右外连接：查询右表所有数据，包含两张表交集部分数据 SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...;

例子：

1  -- 查询emp表和对应的部门信息（左外）
2  SELECT e.* , d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;
3  
4  -- 查询dept表和对应的员工信息（右外）
5  SELECT d.* , e.* FROM dept d RIGHT OUTER JOIN emp e ON e.dept_id = d.id;

左连接可以查询到没有dept的emp，右连接可以查询到没有emp的dept

自连接查询

自身连接：自己的表与自己进行连接（相当于两张一样的表）
需要给表起别名以示区别
由于所有属性名都是同名属性，因此必须使用别名前缀

语法： SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;

自连接查询，可以是内连接查询，也可以是外连接查询

例子：

1  -- 查询员工 以及 所述领导的名字 (内连接自连接)
2  SELECT e1.name, e2.name FROM emp e1 JOIN emp e2 ON e1.managerid = e2.id;
3  
4  -- 查询员工 以及 所述领导的名字, 如果无，也要显示 (外连接自连接)
5  SELECT e1.name, e2.name FROM emp e1 LEFT OUTER JOIN emp e2 ON e1.managerid = e2.id;

联合查询 union, union all

把多次查询的结果合并，形成一个新的查询集

语法：

1  SELECT 字段列表 FROM 表A ...
2  UNION [ALL]
3  SELECT 字段列表 FROM 表B ...

案例：

1  -- 将薪资低于5000的员工 和 年龄大于50的员工 全部查询出来
2  SELECT * FROM emp where salary < 5000
3  UNION
4  SELECT * FROM emp where age > 50;

注意事项

UNION ALL 会有重复结果，UNION 不会
对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
联合查询比使用or效率高，不会使索引失效

子查询（嵌套查询）

SQL语句中嵌套SELECT语句，称谓嵌套查询，又称子查询。 SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2); 子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个

根据子查询结果可以分为：

标量子查询（子查询结果为单个值）
列子查询（子查询结果为一列）
行子查询（子查询结果为一行）
表子查询（子查询结果为多行多列）

根据子查询位置可分为：

WHERE 之后
FROM 之后
SELECT 之后

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等）。常用操作符：- < > > >= < <=

例子：

 1  -- 1. 查询销售部的所有员工信息
 2  -- step 1: 查询销售部的部门ID
 3  SELECT id FROM dept WHERE name  = '销售部';
 4  -- step 2: 根据ID，查询员工信息
 5  select * from emp where dept_id = 4;
 6  -- 一条就查询完
 7  select * from emp where dept_id = (SELECT id FROM dept WHERE name  = '销售部');
 8  
 9  
10  -- 查询在‘方东白’入职之后的员工信息
11  select entrydate from emp where name = '方东白' ;
12  select * from emp where entrydate > '2009-02-12';
13  select * from emp where entrydate > (select entrydate from emp where name = '方东白' );

列子查询

返回的结果是一列（可以是多行）。

常用操作符：

操作符	描述
IN	在指定的集合范围内，多选一
NOT IN	不在指定的集合范围内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

例子：

 1  -- 1. 查询 "销售部" 和 "市场部" 的所有员工信息
 2  -- step 1： 查询 "销售部" 和 "市场部" 的部门ID
 3  select id from dept where name = '销售部' or name = '市场部';
 4  -- step 2： 根据部门ID, 查询员工信息
 5  select * from emp where dept_id in (2,4);
 6  -- 一条就查询完
 7  select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');
 8  
 9  
10  -- 2. 查询比 财务部 所有人工资都高的员工信息
11  -- step 1: 查询所有 财务部 人员工
12  select id from dept where name = '财务部';
13  select salary from emp where dept_id = 3;
14  select salary from emp where dept_id = (select id from dept where name = '财务部');
15  -- step 2: 比 财务部 所有人工资都高的员工信息
16  select * from emp where salary > all ( select salary from emp where dept_id = 3 );
17  -- 一条就查询完
18  select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部') );
19  
20  
21  -- 3. 查询比研发部其中任意一人工资高的员工信息
22  select id from dept where name = '研发部';
23  select salary from emp where dept_id = ( select id from dept where name = '研发部' );
24  select * from emp where salary > any ( select salary from emp where dept_id = ( select id from dept where name = '研发部' ) ) ;

行子查询

返回的结果是一行（可以是多列）。常用操作符：=, <, >, IN, NOT IN

例子：

1  -- 行子查询
2  -- 1. 查询与‘张无忌’的薪资及领导相同的员工信息
3  select salary, managerid from emp where name = '张无忌';
4  select * from emp where salary = 12500 and managerid = 1;
5  -- 与上面相同
6  select * from emp where (salary, managerid) = (12500,1);
7  -- 一条就查询完
8  select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '张无忌');

表子查询

返回的结果是多行多列常用操作符：IN

例子：

1  -- 1. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
2  select job, salary from emp where name = '鹿杖客' or name = '宋远桥';
3  select * from emp where (job,salary) in (select job, salary from emp where name = '鹿杖客' or name = '宋远桥');
4  
5  -- 2. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
6  -- 入职日期是 "2006-01-01" 之后的员工信息
7  select * from emp where entrydate > '2006-01-01';
8  -- 把上一步查询出来的表起别名作为临时表e，后引用左外连接连接上表和部门信息
9  select e.*, d.id from (select * from emp where entrydate > '2006-01-01') as e left join dept as d on e.dept_id = d.id;

带有ANY（SOME）或ALL谓词的子查询

谓词语义：

ANY：任意一个值
ALL：所有值

需要配合使用比较运算符:

>ANY	大于子查询结果中的某个值
>ALL	大于子查询结果中的所有值
<ANY	小于子查询结果中的某个值
<ALL	小于子查询结果中的所有值
>=ANY	大于等于子查询结果中的某个值
>=ALL	大于等于子查询结果中的所有值
<=ANY	小于等于子查询结果中的某个值
<=ALL	小于等于子查询结果中的所有值
=ANY	等于子查询结果中的某个值
=ALL	等于子查询结果中的所有值（通常没有实际意义）
!=（或<>）ANY	不等于子查询结果中的某个值
!=（或<>）ALL	不等于子查询结果中的任何一个值

查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄

1  SELECT Sname,Sage
2  FROM Student
3  WHERE Sage < ANY (SELECT Sage
4                    FROM Student
5                    WHERE Sdept= 'CS') AND Sdept <> 'CS' ; /*父查询块中的条件 */

执行过程：

关系数据库管理系统（Relational Database Management System：RDBMS）

RDBMS执行此查询时，首先处理子查询，找出 CS系中所有学生的年龄，构成一个集合(20，19)
处理父查询，找所有不是CS系且年龄小于 20 或 19的学生

用聚集函数实现

1  SELECT Sname,Sage
2  FROM Student
3  WHERE Sage < (SELECT MAX(Sage)
4               FROM Student
5               WHERE Sdept= 'CS') AND Sdept <> 'CS';

查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

方法一：用ALL谓词

1  SELECT Sname,Sage
2  FROM Student
3  WHERE Sage < ALL (SELECT Sage
4                    FROM Student
5                    WHERE Sdept= 'CS') AND Sdept <> 'CS';

方法二：用聚集函数

1  SELECT Sname,Sage
2  FROM Student
3  WHERE Sage < (SELECT MIN(Sage)
4                FROM Student
5                WHERE Sdept= 'CS') AND Sdept <> 'CS';

ANY（或SOME），ALL谓词与聚集函数、IN谓词的等价转换关系

	=	<>或！=	<	<=	>	>=
ANY	IN	-	<MAX	<=MAX	>MAX	>=MAX
ALL	-	NOT IN	<MIN	<=MIN	>MAX	>=MAX

带有EXISTS谓词的子查询（难点）

EXISTS谓词

存在量词 ∃
带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
- 若内层查询结果非空，则外层的WHERE子句返回真值
- 若内层查询结果为空，则外层的WHERE子句返回假值
由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义

NOT EXISTS谓词

若内层查询结果非空，则外层的WHERE子句返回假值
若内层查询结果为空，则外层的WHERE子句返回真值

查询所有选修了1号课程的学生姓名。

思路分析：

本查询涉及Student和SC关系
在Student中依次取每个元组的Sno值，用此值去检查SC关系
若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno=‘1’，则取此Student.Sname送入结果关系

1.用嵌套查询

1  SELECT Sname
2  FROM Student
3  WHERE EXISTS(SELECT *
4               FROM SC
5               WHERE Sno=Student.Sno AND Cno= '1');

2.用连接运算

1  SELECT Sname
2  FROM Student, SC
3  WHERE Student.Sno=SC.Sno 
4  AND SC.Cno= '1';

查询没有选修1号课程的学生姓名。

1  SELECT Sname
2  FROM Student
3  WHERE NOT EXISTS(SELECT *
4                   FROM SC
5                   WHERE Sno=Student.Sno AND Cno= '1');

不同形式的查询间的替换

一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换
所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换用EXISTS/NOT EXISTS实现全称量词(难点) SQL语言中没有全称量词∀（For all）可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：

例：查询与“刘晨”在同一个系学习的学生。可以用带EXISTS谓词的子查询替换：

1  SELECT Sno,Sname,Sdept
2  FROM Student S1
3  WHERE EXISTS(SELECT *
4               FROM Student S2
5               WHERE S2.Sdept = S1.Sdept AND S2.Sname = '刘晨');

查询选修了全部课程的学生姓名。

1  SELECT Sname
2  FROM Student
3  WHERE NOT EXISTS(SELECT *
4                   FROM Course
5                   WHERE NOT EXISTS(SELECT *
6                                    FROM SC
7                                    WHERE Sno= Student.Sno
8                                    AND Cno= Course.Cno)
9                   );

用EXISTS/NOT EXISTS实现逻辑蕴函(难点)

SQL语言中没有蕴函(Implication)逻辑运算
可以利用谓词演算将逻辑蕴函谓词等价转换为：

查询至少选修了学生201215122选修的全部课程的学生号码。 解题思路：

用逻辑蕴函表达：查询学号为x的学生，对所有的课程y，只要201215122学生选修了课程y，则x也选修了y。
形式化表示：用P表示谓词 “学生201215122选修了课程y” 用q表示谓词 “学生x选修了课程y”

用NOT EXISTS谓词表示：

 1  SELECT DISTINCT Sno
 2  FROM SC SCX
 3  WHERE NOT EXISTS(SELECT *
 4                  FROM SC SCY
 5                  WHERE SCY.Sno = '201215122' 
 6                  AND NOT EXISTS(SELECT *
 7                                  FROM SC SCZ
 8                                  WHERE SCZ.Sno=SCX.Sno 
 9                                  AND SCZ.Cno=SCY.Cno
10                              )
11                  );

多表查询案例

 1  -- 案例
 2  -- 数据准备
 3  create table salgrade
 4  (
 5      grade int,
 6      losal int,
 7      hisal int
 8  ) comment '薪资等级表';
 9  
10  insert into salgrade values (1, 0, 3000);
11  insert into salgrade values (2, 3001, 5000);
12  insert into salgrade values (3, 5001, 8000);
13  insert into salgrade values (4, 8001, 10000);
14  insert into salgrade values (5, 10001, 15000);
15  insert into salgrade values (6, 15001, 20000);
16  insert into salgrade values (7, 20001, 25000);
17  insert into salgrade values (8, 25001, 30000);
18  
19  -- 1. 查询员工的姓名、年龄、职位、部门信息 (隐式内连接)
20  select e.name, e.age, e.job, d.name from emp as e, dept as d where e.dept_id = d.id;
21  
22  -- 2. 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息 (显示内连接)
23  select e.name, e.age, e.job, d.name from emp as e, dept as d where e.dept_id = d.id and age < 30;
24  select e.name, e.age, e.job, d.name from emp as e inner join dept as d on e.dept_id = d.id where age < 30;
25  
26  -- 3. 查询拥有员工的部门ID、部门名称
27  select distinct e.dept_id, d.name from emp as e, dept as d where e.dept_id = d.id;
28  
29  -- 4. 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来(左外连接)
30  select e.name, d.name from emp as e left join dept as d on e.dept_id = d.id where age > 40;
31  
32  -- 5. 查询所有员工的工资等级
33  -- 表: emp , salgrade
34  -- 连接条件 : emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal
35  select e.*, s.grade from emp as e, salgrade as s where e.salary >= s.losal and e.salary <= s.hisal;
36  -- 方式一
37  select e.*, s.grade, s.losal, s.hisal from emp e, salgrade s where e.salary >= s.losal and e.salary <= s.hisal;
38  -- 方式二
39  select e.*, s.grade, s.losal, s.hisal from emp e, salgrade s where e.salary between s.losal and s.hisal;
40  
41  -- 6.  查询 "研发部" 所有员工的信息及 工资等级
42  select id from dept where name = '研发部';
43  select e.*, s.grade from emp e, dept d, salgrade s where e.dept_id = d.id and (e.salary between s.losal and s.hisal) and d.name = '研发部';
44  
45  -- 7. 查询 "研发部" 员工的平均工资
46  select id from dept where name = '研发部';
47  select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.id = (select id from dept where name = '研发部');
48  select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发部';
49  
50  -- 8. 查询工资比 "灭绝" 高的员工信息
51  select salary from emp where name = '灭绝';
52  select * from emp where salary > (select salary from emp where name = '灭绝');
53  
54  -- 9. 查询比平均薪资高的员工信息
55  select avg(salary) from emp; select * from emp where salary > (select avg(salary) from emp);
56  
57  -- 10. 查询低于本部门平均工资的员工信息 (假设查2)
58  select avg(e1.salary) from emp e1 where e1.dept_id = 1;
59  select avg(e1.salary) from emp e1 where e1.dept_id = 2;
60  select e2.* from emp e2 where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);
61  
62  -- 11. 查询所有的部门信息, 并统计部门的员工人数
63  select d.id, d.name, count(e.dept_id) as numbers from emp e, dept d where e.dept_id = d.id group by e.dept_id;
64  select d.id, d.name , ( select count(*) from emp e where e.dept_id = d.id ) '人数' from dept d;
65  
66  -- 12. 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称
67  -- 表: student , course , student_course
68  -- 连接条件: student.id = student_course.studentid , course.id =student_course.courseid
69  select s.name, s.id, c.name from student s, course c, student_course sc where s.id = sc.studentid and c.id =sc.courseid;
70

DCL

管理用户

 1 查询用户：
 2 
 3  USE mysql; -- 使用数据库
 4  
 5  SELECT * FROM user;
 6 创建用户: CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
 7 
 8 修改用户密码： ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
 9 
10 删除用户： DROP USER '用户名'@'主机名';

例子：

 1 -- 创建用户test，只能在当前主机localhost访问
 2 create user 'test'@'localhost' identified by '123456';
 3 
 4 -- 创建用户test，能在任意主机访问
 5 create user 'test'@'%' identified by '123456';
 6 
 7 -- 修改密码    
 8 alter user 'test'@'%' identified with mysql_native_password by '1234';
 9 
10 -- 删除用户
11 drop user 'test'@'localhost';
12 
13  -- 修改密码(修改当前用户密码)
14  SET PASSWORD=PASSWORD('123456')
15 
16  -- 修改密码(修改指定用户密码)
17  SET PASSWORD FOR test=PASSWORD('123456')
18 
19  -- 重命名RENAME USER 原来名字 To 新的名字
20  RENAME USER test TO test1

注意事项

主机名可以使用 % 通配

权限控制

常用权限：

权限	说明
ALL, ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

1 查询权限： SHOW GRANTS FOR '用户名'@'主机名';
2 
3 授予权限： GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
4 
5 撤销权限： REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意事项

多个权限用逗号分隔
授权时，数据库名和表名可以用 * 进行通配，代表所有

例子：

 1  -- 查询权限
 2  show grants for 'heima'@'%';
 3  
 4  -- 授予权限 (授予heima这个用户对itcase这个数据库的所有表的所有权限)
 5  grant all on itcase.* to 'heima'@'%';
 6  -- 授予权限 (授予heima这个用户所有数据库所有表的所有权限)
 7  grant all on *.* to 'heima'@'%';
 8  
 9  -- 查询权限 (撤销heima这个用户对itcase这个数据库的所有表的所有权限)
10  revoke all on itcase.* from 'heima'@'%';
11

数据表的类型

 -- 关于数据库引擎
 /*
 INNODB 默认使用
 MYISAM 早些年使用
 */

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	约为MyISAM 的2倍

常规使用操作：

MYISAM 节约空间，速度较快
INNODB 安全性高，事务的处理，多表多用户操作

在物理空间存在的位置

所有的数据库文件都在data目录下，一个文件夹对应一个数据库

本质还是文件的存储

MySQL引擎在物理文件上的区别

InnoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件

没找到*.frm文件

设置数据库表的字符集编码

 CHARSET=utf8

不设置的话会是mysql默认的字符集编码（不支持中文）

posted @ 2022-03-23 15:18 暴躁C语言阅读(72) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· 2022.3.31 SQL函数、约束与数据库级别的MD5加密（扩展）

· 2022.4.2数据库设计与性能分析

· Mysql

· mysql基础

· MySQL

公告

昵称：暴躁C语言
园龄： 4年8个月
粉丝： 1
关注： 3

+加关注

2025年3月

日

一

二

三

四

五

六

暴躁C语言

2022.3.23 DDL、DML、DQL、DCL

通用语法及分类

DDL（数据定义语言）

数据库操作

表操作

类型

DML（数据操作语言）

插入元组

插入子查询结果

更新和删除数据

DQL（数据查询语言）

基础查询

条件查询

聚合查询（聚合函数）

分组查询

注意事项

排序查询

注意事项

分页查询

注意事项

DQL语句练习​

DQL执行顺序(不是编写顺序)

多表查询

合并查询（笛卡尔积，会展示所有组合结果）

联表查询

内连接查询

外连接查询

自连接查询

联合查询 union, union all

注意事项

子查询（嵌套查询）

标量子查询

列子查询

行子查询

表子查询

带有ANY（SOME）或ALL谓词的子查询

带有EXISTS谓词的子查询（难点）

多表查询案例

DCL

管理用户

注意事项

权限控制

注意事项

数据表的类型

公告

搜索

常用链接

随笔分类

随笔档案

阅读排行榜

推荐排行榜

DQL语句练习