MySQL 学习笔记

发表于 2022-08-22 分类于数据库本文字数： 8.9k 阅读时长 ≈ 32 分钟

MySQL 是一个免费的关系型数据库，在同类产品中的占比较高。这是一篇在学习 MySQL 时的学习笔记

SQL 分类

DQL—— 数据查询语句

数据查询语言 DQL 基本结构是由 SELECT 子句，FROM 子句，WHERE
子句组成的查询块：
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>

DML—— 数据操作语言

数据操作语言主要三种形式：insert、update 和 delete，主要是针对数据表内容进行增删改操作

DDL—— 数据定义语言

数据定义语言主要三种形式：create、alter 和 drop，主要是针对数据表结构进行增删改操作

TCL—— 事务控制语言

事务相关的主要是提交和回滚，分别是 commit 和 rollback

DCL—— 数据控制语言

数据控制语言主要是对权限的授权与撤销，分别是 grant 和 revoke

SQL 基本操作

1	mysql -u root -p

查询所有数据库

1	show databases;

使用数据库

use 数据库名

查询数据库中的表

1	show tables;

查看表结构

desc 表名

数据源：蓝奏云百度网盘提取码：yyds

查询语句

首先查看所有的数据库中存在的表

7DD7A87D89CE4420FCE677ED0C558892

dept 是部门表

emp 是员工表

salgrade 是工资等级表

查询所有数据

查询部门表所有数据

9CB4DBCF148F3D4AA8544F1EE8B2AD3C

deptno 部门编号

dname 部门名称

loc 部门地址

查询员工表所有数据

22A26B6CFC958A8519704470154E655E

empno 员工编号

ename 员工名称

job 工作岗位

mgr 上级领导编号

hiredate 入职日期

sal 工资

comm 补助

deptno 部门编号

查询工资等级表所有数据

61103C2E858D38B071047AEB91A06132

grade 工资等级

losal 最低工资

hisal 最高工资

查看表结构

不看表数据只看表结构

AFCB04AA9CBC68462ACF4BC7B257EBF3

MySQL 常用命令

exit;	退出
quit;	退出
select version();	MySQL版本
select database();	当前使用的数据库

E49C16815B397F352E9561FA71820219

简单查询

1	select 字段名 from 表名;

select 和 from 是关键字，字段名和表名都是标识符；

强调

SQL 语句以 “;” 结尾

MySQL 不区分大小写

1 查询部门名称

1	select dname from dept;

164802646CE0B2838BBAF6D89CC71142

2 查询两个字段怎么办

使用逗号隔开

1	select deptno,dname from dept;

289368C20C5AA1E6913C5C692F4CD155

3 查询所有字段

1	select * from dept;

4A2B60D8EFBA498DE1C4C1A514092A50

缺点：效率低、可读性差

4 给查询的列起别名

28313D74B36AB0B73F5017DE3293AD1D

只是将显示的显示结果列明显示出来，并不会修改原表数据

select 永远不会修改操作

思考：as 关键字可以省略吗？

实践：

3B82B2D1A45E754B6F87C92A4468BA07

根据实践可得，as 关键字可以省略，当别名中需要有空格时，别名使用单引号引起来

注意： MySQL 语句中单引号才是最符合语法的，不推荐双引号，在 Oracle 中使用双引号是非法的。

在此强调： 单引号是正统，双引号是异端（必须铲除！）

5 计算员工的年薪

计算年薪则是 sal 字段乘以 12

1	select ename,sal*12 as '年薪' from emp;

EACE0910D39F537220A0C7A1C98E8BE0

注意： 1、字段名可以参与数学运算

2、别命中有中文需要用单引号引起来，

条件查询

什么是条件查询

很显然，条件查询是查询符合条件的数据，那条件查询的语法格式如下：

select 字段 from 表名 where 条件

等于

查询薪资等于 800 的员工名字和编号

1	select empno,ename from emp where sal = 800;

AA2A4B71DF254441CB4842893C3031B9

大于、小于和不等于

查询不等于 800 的员工

1 2	select empno,ename from emp where sal != 800; select empno,ename from emp where sal <> 800;

9BA22111F3515600B9B6CBC1D78C6AE4

查询薪资低于 2000 的员工信息

1	select empno,ename from emp where sal < 2000;

93AB6C87B76E897D04B0D610E4F963DE

查询薪资小于等于 3000 的员工信息

1	select empno,ename from emp where sal <= 3000;

637CF9D979A3F81D72657C24EFC3025D

查询薪资大于 3000 的员工

1	select empno,ename from emp where sal > 3000;

E45E9B2CDB400CAD5E656C97268DAADE

查询薪资大于等于 2500 的员工

1	select empno,ename from emp where sal >= 2500;

F274D606F7A6559924770005EA3C30D6

思考： 能否查询字符串

实践：

1	select empno,ename from emp where ename="KING";

CE8723F056813961E46F6D93656EE140

between……and

查询薪资在 1000 到 3000 之间的员工信息

1	select empno,ename from emp where sal>=1000 and sal <= 3000;

7F9DBB088B340BD4C0AAE392CEE34A19

第二种方法实现 ——between and

1	select empno,ename from emp where sal between 1000 and 3000;

897AF274660A2B843ABF0AC2FC659CA2

可以看出效果是同样的

注意： 在使用 between……and 时，必须遵循左小右大的规则

is null

查询补助为 null 的员工信息

1	select empno,ename,comm from emp where comm is null;

FBE80DE2B58C2D1EDFE9B060867EA8B9

注意：在数据库中 null 不能使用等号衡量，因为数据库中代表什么也没有，所以不能使用等号

80AFC8FDBEEED56305C14CB401674200

查询补助不为 null 的员工信息

1	select empno,ename,comm from emp where comm is not null;

5458C423AB3DFFEC4D084B6976A3EF31

注意：0 是数值 0，不是 null

and

查询工作岗位是 manager 并且工资大于 2500 的员工信息

1	select empno,ename,job,sal from emp where job='MANAGER' and sal > 2500;

3FDF807F475376C973BF6C25B1445003

or

查询工作岗位是 manager 或者 salesman 的员工信息

1	select empno,ename,job from emp where job='MANAGER' or job='SALESMAN';

924B7E750804080E192749BB4460F6B5

思考：and 和 or 同时使用，有优先级吗？

查询薪资大于 2500，并且部门部门编号为 10 或者 20 的员工信息

1	select empno,ename,sal,deptno from emp where sal>2500 and (deptno=10 or deptno=20);

and 和 or 同时出现，and 优先级比较高

in 和 not in

这表示的是包含和不包含

查询工作岗位是 manager 和 salesman 的员工

由上面的题可知，可以用等于和 or 来完成，但这里需要使用 in 或者 not in 来实现

1	select empno,ename,job from emp where job in ('MANAGER','SALESMAN');

989AB56B012A815213A170D492CA9F00

找出工资是 800 和 5000 的员工信息（注意，不是 800 到 5000）

1	select empno,ename,sal from emp where sal in ('800','5000');

FE0249E9A8A69C249AB6D355BBDFD3B2

not

not 的作用是取非，主要是在 is 和 in 中使用

找出工资不是 800 并且不是 5000 的员工信息

1	select empno,ename,sal from emp where sal not in ('800','5000');

084BF128CF11A0E1506847AA0557854A

模糊查询 ——like

支持 % 和下划线匹配

% 匹配多个字符

下划线匹配一个字符

查询名字中间含有 o 的

1	select empno,ename from emp where ename like '%o%';

ACF12332D4397CD98B001138E384B5B6

查询名字以 T 结尾的

1	select empno,ename from emp where ename like '%t';

查询名字以 K 开头的

1	select empno,ename from emp where ename like 'k%';

45D408BE47A5A4F9F4B635E9F90736A1

查询第二个字母为 A 的

1	select empno,ename from emp where ename like '_a%';

EFE0B650BEEBAF86BC00E4258BF2C790

找出第三个字母是 R 的名字

1	select empno,ename from emp where ename like '__R%';

F4CB40EDEACC8197951274B3B8956986

临时创建一个表

1	create table t_student(name varchar(255));

表结构如下

48692B1DC3382B34160DF78A9606B2B1

在表中插入以下数据：

insert into t_student values('zhangsan');
insert into t_student values('wangwu');
insert into t_student values('zhaoliu');
insert into t_student values('jack_son');

C804F774BC38AFF71F8A122618D4F03E

在这个表中，使用模糊查询 like 查询名字带下划线的名字

1	select name from t_student where name like '%\_%';

因为下划线在 MySQL 中有特殊含有，所以需要查询下划线字符时，需要进行转义

CF7DA9C090CDE58B4D6CC2BB05660943

排序

查询员信息按工作排序

1	select empno,ename,sal from emp order by sal;

FFBD0E6EC4690609F9ADB1C5709C081D

不难看出，默认使用的是升序

如何实现降序呢？

1	select empno,ename,sal from emp order by sal desc;

805FB2E5DA319C35AEA4C62ABD301B45

思考：可以通过多个字段排序嘛？

查询员工姓名和薪资，需要按薪资升序，如果薪资相同时按名字升序排序

1	select ename,sal from emp order by sal,ename asc;

A3E079CEB7E7B4328729FA70D4BBFCA7

asc 表示升序表示

desc 表示降序

注意：sal 在前起主导作用，在 sal 相等情况下再根据 ename 排序

根据字段的位置排序

1	select ename,sal from emp order by 2;

根据查询结果的第二个排序

不推荐这样写，因为列的顺序很容易发生改变，只需了解即可

DCCB431E31AC610663F115CFA3A701F2

综合案例：

找出工资在 1250 到 3000 之间的员工信息，要求按薪资降序排序。

1	select ename,sal from emp where sal between 1250 and 3000 order by sal desc;

0982926360EAA910C8C164D48581FC1A

关键字顺序不可变

select
	……
from
	……
where
	……
order by
	……

排序永远在最后执行

语句的执行顺序如下

from
where
select
order by

数据处理函数 / 单行处理函数

数据处理函数又被成为单行处理函数，单行处理函数的特点是一个输入对应一个输出，和单行处理函数对应的是多行处理函数，接下来学习一下常用的单行处理函数

lower

lower 的作用是转换小写

1	select ename,lower(ename) as 'lower ename' from emp;

90FFB02FA4598EC7BB7F99C9499053BD

14 个输入对应 14 个输出

upper

upper 的作用是转换大写

1	select name,upper(name) as 'upper name' from t_student;

83C5407369F226594B43B9842E6BF754

substr

substr 的作用是取子串，在此尝试截取名字的首个字母，substr 的参数分别是字段、开始下标，截取长度，可以看出，MySQL 中的下标是从 1 开始的，如果下标设置为 0 时取不到数据。

1	select substr(ename,1,1) as ename from emp;

78354299BC31E8DB5E7654FA99702E71

在前边，我们通过模糊查询实现了查询首字母为 K 的例子，那么使用取子串的 substr 函数是否可以实现呢？当然可以

1	select ename from emp where substr(ename,1,1)='K';

3EDDADE7698FC05BDE99A138092AA155

length

怎么获取名字的长度呢？length 函数

1	select ename,length(ename) from emp;

4AC5781C1164A8955E70CBBA3A09010E

concat

既然能获取到首个字母，那能否在 emp 表和 t_student 表实现名字首字母大写呢？

首先是拿到 emp 表的姓名首字母

1	select substr(ename,1,1) from emp;

接下来就是拿到除去首字母外的字母，通过 length 来获取

1	select substr(ename,2,length(ename)-1) from emp;

接下来就是对剩下的字母变成小写

1	select lower(substr(ename,2,length(ename)-1)) from emp;

这就到了我们的的 concat 进行字符拼接了

1	select concat(substr(ename,1,1),lower(substr(ename,2,length(ename)-1))) as name from emp;

F97A9421BFC16BC5D17E134CF01BB9DE

同理的，t_student 也是同样的操作

1	select concat(upper(substr(name,1,1)),substr(name,2,length(name)-1)) as name from t_student;

EEC0CC5C2964FB435B9429575F45551A

trim

trim 的作用是去除前后的空白

例如我们需要查询 King 的信息，我们会使用以下的 SQL 语句进行查询

1	select ename from emp where ename = ' KING ';

但由于条件查询的条件参数带有空格，并不能匹配到 KING，此时可以使用 trim 函数对参数进行处理。

1	select ename from emp where ename = trim(' KING ');

此时便可得到想要的结果

C5BA8C00B66BC39D598E67FAA59CB2A5

date_format

这个函数主要将时间数据根据一定的格式进行输出

1	select ename,hiredate,date_format(hiredate,'%Y年%m月%d日') as '年月日' from emp;

563932ACB4D95871440E149D8182EEE8

str_to_date

这个函数和上面的函数恰好相反，这个函数是将特定格式的字符转成时间格式

1	select ename,date_format(hiredate,'%Y年%m月%d日') as '年月日',str_to_date(date_format(hiredate,'%Y年%m月%d日'),'%Y年%m月%d日') as date from emp;

F5C8F55DC9275EBBC62E8DABE8587431

可以看到，兜兜转转一大圈，最后还是回到了原来的数据

format

对数字格式化，讲数字转成货币的表现形式

1	select ename,sal,format(sal,0) from emp;

8496DFC117D2CD2C8C73A4F735FC2A8E

可以看到，原本普通形式的表现形式变成了三位一分号的表现形式，并且对小数点后两位进行了省略。

round

四舍五入

1	select 1234.567,round(1234.567,0),round(1234.567,1),round(1234.567,2),round(1234.567,3),round(1234.567,4);

DBEE7F38267177F89899598DC9AD33A0

可以明显的看到，当不保留小数时，因为小数第一位是 5，所以进行运算后个位进一，1234 变成了 1235，后面的都是同理，当保留的小数位数大于最大的小数位数时，可以发现并不会在小数的后面做加 0 操作。

rand()

rand 函数的作用是生成随机数

1	select rand() as random from emp;

C58312C46315E71F982E340FFA97C143

看到这个 SQL 语句是否有所疑惑，其实跟上面的语句一样，上面使用的都是字面值，而查询的结果有多少个数据，则会返回多少个与之对应的数据。

case …… when …… then

这个函数有点像其实不难理解，当什么时候做什么事情。

使用数据库中的数据做一个例子，当岗位为 clerk 时，薪资提升 20%，当岗位为 salesman 时，工资提升 30%。

select 
	ename,
	job,
	sal,
	case 
		job 
	when 
		'CLERK' 
	then 
		sal*1.2
    when 
    	'SALESMAN'
    then
    	sal*1.3
    else
    	sal
    end 
    	'涨薪后的工作'
    from emp;

496C96BCFB8988EE505D94E25AF9FD71

ifnull

ifnull 函数需要接收两个参数，当前面的参数为不为空时，则会输出参数的内容，而当参数为空时，输出第二个参数的内容。

例子：在 emp 表中找出谁才是 BOSS

1	select ename,ifnull(mgr,'BOSS') as 'who is boss?' from emp;

EFAB1CC0FB672BE601DBA7C48F7A3EB7

分组函数 / 多行处理函数

多行处理函数的特点是多个输入对应一个输出。

注意：分组函数在使用的时候必须分组才能使用，如果没有分组，整张表默认为一组，分组函数会自动忽略 Null 值，无需对 null 进行处理

count 计数

统计公司员工人数

1	select count(empno) as '人数统计' from emp;

DC32F06C3FA015AE276CF382A282CDDE

sum 求和

求公司每月工资开销

1	select sum(sal) as '工资开销' from emp;

E814377E47DEC7B43868246FD615B99D

avg 平均值

求公司员工的平均工资

1	select avg(sal) as '平均工资' from emp;

CEFDD0C0E1B99B7D644FEC7801C77BDF

max 求最大值

求最高工资

1	select max(sal) from emp;

8701F830739571A003B5FB476991796D

min 求最小值

求最低工资

1	select min(sal) from emp;

8D84BEEC8CA377FBC5D826D1C0487227

思考，count (*) 和 count (具体自动) 的区别

count (具体自动) 代表该字段不为 null 的总和

count (*) 代表该表的行数总和，因为只要存在一行记录，那么这行数据则不可能所有字段都为 null，全部为 null 则代表没有这行记录

注意：分组函数不能直接使用在 where 子语句中

分组函数可以组合使用

分组查询（重点）

group by

计算每个岗位的工资总和

1	select job,sum(sal) from emp group by job;

594F844A568D0FD5884418100D335D76

计算每个工作岗位的平均工资

1	select job,avg(sal) from emp group by job;

3303A7259F0C16F141C61A8661E8EC0D

找出每个工作岗位的最高工资

1	select job,max(sal) from emp group by job;

2A508C86A3A4A844009707FBEE268820

在 SQL 语句中，select 语句含有 group by 的话，select 只能跟参加分组的字段，以及分组函数，在 MySQL 中无意义，其他 SQL 中报错。

求每个部门的的最高工资

1	select deptno,max(sal) from emp group by deptno;

CE8E0A9719D78B1ED6F49CD8D5588044

找出每个部门不同工作岗位的最高工资？

group by 可以多个字段

1	select deptno,job,max(sal) from emp group by deptno,job;

7508D6E37F444B18ECE39FE43FAD9608

having

使用 having 可以对分完组后的数据进一步进行过滤

求每个部门不同工作岗位的最高工资，并且要求薪资大于 1500。

1	select deptno,job,max(sal) from emp group by deptno,job having max(sal)>1500;

19AFA995B67682869FEEDC039C96D5BA

通过 having 可以对结果进行二次过滤，但上面的 SQL 语句效率就相对较低了，因为要求工资是大于 1500，所以可以在分组之前对薪资进行过滤。

1	select deptno,job,max(sal) from emp where sal > 1500 group by deptno,job;

4E80C4E6F81AD8DDC8C0B21BAAC435D2

求每个部门的平均薪资，要求平均薪资大于 1500 的。

面对这个题目，使用 where 就无法处理了，那么就需要使用 having 了。

1	select deptno,job,avg(sal) from emp group by deptno,job having avg(sal)>1500;

1910D542CC0A6C3A0BBBF76129ECF0EE

select 语句总结

select

……

from

……

where

……

group by

……

having

……

order by

……

执行顺序如下：

1、from

2、where

3、group by

4、having

5、select

6、order by

从某张表查询数据，

先经过 where 条件筛选出有价值的数据，

对这些数据进行分组

分组后通过 having 进行二次过滤

通过 select 查询

最后进行排序输出

思考：为什么分组函数不能在 where 子语句中使用？

因为分组函数需要分组后才可以使用，如果没有分组则会将整张表作为一个分组，而 where 子语句中，并没有完成分组的，当没有 where 并且没有 group by 时，可以看做 group by 已经执行了。而 where 子语句，不管怎么看都是处于 where 到 group by 时的，那么就是还没开始 group by，所以不能使用分组函数。

找出每个岗位的平均薪资，要求显示平均薪资大于 1000 的，除去 MANAGER 岗位之外，要求按照平均薪资的降序排列。

1	select job,avg(sal) from emp where job != 'MANAGER' group by job having avg(sal) > 1000 order by avg(sal) desc;

243141D94954E71FEC7249259AEE11A5

技巧：在写 SQL 语句时，select 后可以先留空，按照这执行的顺序来写 SQL 语句

例如此处的 SQL 语句，可以先这样写，指定好查询的表

1	select xxx from emp;

下一步是 where，对排除的岗位进行筛选

1	select xxx from emp where job != 'MANAGER';

此时可以进行分组，根据工作岗位进行分组

1	select xxx from emp where job != 'MANAGER' group by job;

在分组完成后，对分组的数据进行二次筛选，将只显示平均工资大于 1000 的

1	select xxx from emp where job != 'MANAGER' group by job having avg(sal) > 1000;

到了这一步就可以确定 select 后的字段内容了，因为分组后只能跟参与分组的字段和分组函数，所以是 job 和 avg (sal)

1	select job,avg(sal) from emp where job != 'MANAGER' group by job having avg(sal) > 1000;

又因为需要对平均工资进行降序排序，所以最后需要进行一次 order by，降序的话就是 order desc

1	select job,avg(sal) from emp where job != 'MANAGER' group by job having avg(sal) > 1000 order by avg(sal) desc;

去重

如何实现去重呢？

1	select deptno,job from emp;

2815087020E35BF36AF97BB17F888BD8

像图中这种同一个部门同一个岗位的情况，需要进行去重，如何来实现呢？此处需要使用 distinct 关键字

1	select distinct deptno,job from emp;

151EE5CF2F8318589C64FA5D6DF41187

可以看到，使用 distinct 关键字后，对于部门和岗位相同的数据进行了去重。

注意：distinct 关键字只能出现在所有的字段前面

思考：那么在 distinct 中是否可以使用函数呢？

统计工作岗位的数量

1	select count(distinct job) from emp;

39196438FC24DE864783E6EC54A0CEBB

可以看到，完全是可以在函数中使用 distinct 的

连接查询

连接查询的分类

内连接

等值连接
非等值连接
自连接

外连接

左连接
右连接

笛卡尔积现象

思考：当两张表进行连接查询时没有限制的情况会发生什么现象

查询每个员工所在部门名称？

1	select ename,dname from emp,dept;

此处是未做限制的情况对两张表进行连接查询，结果如下：

不难发现，这里是用的 emp 表中的 ename 跟 dept 表中的 dname 都进行了匹配，这种情况是我们不希望发生的

等值连接

那么引用 SQL92 的写法写一个简单的 SQL 来实现这道题目，但规范还是参考后续的 SQL99 语法

1	select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;

可以看出，这已经符合了预期效果了，那么再看一看 SQL99 的语法怎么写的：

1	select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno;

所以连接的语法大概如下

select 
	查询的字段
from
	表1的名
inner join
	需要连接的表名
on
	筛选的条件

inner join 表示为内连接，可写可不写

非等值连接

找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？

1	select e.ename,e.sal,s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;

自连接

查询员工的上级领导，要求显示员工名和对应的领导名？

1	select a.ename '员工名',b.ename '领导名' from emp a inner join emp b on a.mgr = b.empno;

自连接主要是将同一张表看成两张表便可

外连接和内连接的区别

上面的连接便称为内连接，只有在条件满足的情况才会显示

而外连接分为左连接和右连接以及全连接，右连接指的是把 join 关键字右边的表看为主表，而左连接指的是把 join 关键字左边的表看为主表，即使主表的部分内容无法与次表的数据匹配也会进行展示。

查询每个员工的名字和所对应的部门名称，即使没有与之对应的部门也要查询出来

1	select e.ename,d.dname from emp e right join dept d on e.deptno = d.deptno;

在参考内连接的结果，会发现使用外连接多了一条记录

思考：外连接的查询记录数一定大于或者等于内连接的查询记录数

对的，因为外连接会将主表未匹配的数据也查询出来

带有 right 的是右外连接，又叫做右连接。

带有 left 的是左外连接，又叫做左连接。

任何一个右连接都有左连接的写法。

任何一个左连接都有右连接的写法。

多张表连接

语法：

select
	***
from
	a
join
	b
on
	a和b的连接条件
left join
	c
on
	a和c的连接条件
right join
	d
on
	a和d的连接条件

一条 SQL 中内连接和外连接可以混合使用，可以同时出现

案例：找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级

1	select e.ename,d.dname,e.sal,s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal;

在此基础上，对题目进行一次升级

案例：找出每个员工的部门名称以及工资等级，还有上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级？

1	select e.ename,e2.ename '领导名',d.dname,e.sal,s.grade from emp e left join emp e2 on e.mgr = e2.empno join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal;

子查询

什么是子查询？

select 语句中嵌套 select 语句，被嵌套的 select 语句称为子查询。

子查询都可以出现在哪里呢？

select
..(select).
from
..(select).
where
..(select).

where 子句中的子查询

案例：找出比最低工资高的员工姓名和工资？

错误思路：

1	select sal from emp where sal>min(sal);

这个语句会报错，因为不能在 where 中使用分组函数

解决思路：

第一步：找到最低工资

select min(sal) from emp;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+

第二部：找到比最低工资高

select sal from emp where sal > 800;
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 3000.00 |
| 1300.00 |
+---------+

第三步：合并

mysql> select sal from emp where sal > (select min(sal) from emp);
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 3000.00 |
| 1300.00 |
+---------+
13 rows in set (0.00 sec)

from 子句中的子查询

from 后面的子查询，可以将子查询的查询结果当做一张临时表。

案例：找出每个岗位的平均工资的薪资等级。

第一步：找到岗位平均工资

mysql> select job,avg(sal) avgsal from emp group by job;
+-----------+-------------+
| job       | avgsal      |
+-----------+-------------+
| ANALYST   | 3000.000000 |
| CLERK     | 1037.500000 |
| MANAGER   | 2758.333333 |
| PRESIDENT | 5000.000000 |
| SALESMAN  | 1400.000000 |
+-----------+-------------+
5 rows in set (0.00 sec)

第二步：把第一步的结果当成一张存在的表进行查询薪资等级

mysql> select t.job,t.avgsal,s.grade from (select job,avg(sal) avgsal from emp group by job) t join salgrade s on t.avgsal between s.losal and s.hisal;
+-----------+-------------+-------+
| job       | avgsal      | grade |
+-----------+-------------+-------+
| ANALYST   | 3000.000000 |     4 |
| CLERK     | 1037.500000 |     1 |
| MANAGER   | 2758.333333 |     4 |
| PRESIDENT | 5000.000000 |     5 |
| SALESMAN  | 1400.000000 |     2 |
+-----------+-------------+-------+
5 rows in set (0.00 sec)

select 后面出现的子查询

案例：找出每个员工的部门名称，要求显示员工名，部门名？

1	select e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;

注意：对于 select 后面的子查询来说，这个子查询只能一次返回 1 条结果

union 合并查询结果集

案例：查询工作岗位是 MANAGER 和 SALESMAN 的员工？

使用老方法的实现方式：

mysql> select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)

mysql> select ename,job from emp where job in ('MANAGER','SALESMAN');
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)

使用 union 实现

mysql> select ename,job from emp where job = 'MANAGER'
    -> union
    -> select ename,job from emp where job = 'SALESMAN';
+--------+----------+
| ename  | job      |
+--------+----------+
| JONES  | MANAGER  |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)

使用 union 的效率比之前的效率更高，对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的增长，但是 union 可以在减少匹配次数的情况下，还可以完成两个结果集的拼接。

多表连接

a 连接 b 连接 c
a 10 条记录
b 10 条记录
c 10 条记录
匹配次数是：1000

使用 union 拼接

a 连接 b 一个结果：10 * 10 –> 100 次
a 连接 c 一个结果：10 * 10 –> 100 次
100+100=200

union 在使用的时候有注意事项吗？

union 在进行结果集合并的时候，要求两个结果集的列数相同。
结果集合并时列和列的数据类型也要一致。
MYSQL 可以，oracle 语法严格，不可以

limit

limit 作用：将查询结果集的一部分取出来。通常使用在分页查询当中。

limit startIndex,lenght
startIndex是起始下标，Lenght是长度，起始下标从0开始
缺省用法：
limit 5;
这是取前5

mysql 当中 limit 在 order by 之后执行

案例：按照薪资降序，取出排名在前 5 名的员工？

mysql> select ename,sal from emp order by sal desc limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| FORD  | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)

案例：取出工资排名在 [3-5] 名的员工？

mysql> select ename,sal from emp order by sal desc limit 2,3;
+-------+---------+
| ename | sal     |
+-------+---------+
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
3 rows in set (0.01 sec)

这个案例中的 2 代表从下标 2 开始，3 表示要获取的长度。

通用分页

Page No	Pages	Start Index	PageSize
1	[0,1,2,3,4]	0	5
2	[5,6,7,8,9]	5	5
3	[10,11,12,13,14]	10	5
4	[15,16,17,18,19]	15	5

在做分页查询时，Page Size 始终保持不变，而 Start index 和 Page No 的关系则是（Page No - 1）* Page Size

例如当 Page No 为 5 时，Page Size 保持不变继续为 5，Start index 则等于（5 - 1）* 5 = 20

DQL 语句大总结

整体结构

select
	......
from
	......
where
	......
group by
	......
having
	......
order by
	......
limit
	......

执行顺序

1、from
2、where
3、group by
4、having
5、select
6、order by
7、limit

pass

练习

取得每个部门最高薪水的人员名称

mysql> select e.ename,e.sal,e.deptno from emp e join (select max(sal) sal,deptno from emp group by deptno) as t on e.sal = t.sal and e.deptno = t.deptno;
+-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| BLAKE | 2850.00 |     30 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+
4 rows in set (0.00 sec)

哪些人的薪水在部门的平均薪水之上

mysql> select e.ename,e.sal from emp e join (select avg(sal) sal ,deptno from emp group by deptno) t on e.deptno = t.deptno and e.sal>t.sal;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| ALLEN | 1600.00 |
| BLAKE | 2850.00 |
+-------+---------+
6 rows in set (0.00 sec)

取得部门中（所有人的）平均的薪水等级

mysql> select deptno,avg(grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by deptno;
+--------+------------+
| deptno | avg(grade) |
+--------+------------+
|     10 |     3.6667 |
|     20 |     2.8000 |
|     30 |     2.5000 |
+--------+------------+
3 rows in set (0.00 sec)

不准用组函数（Max），取得最高薪水（给出两种解决方案）
方法一

mysql> select sal from emp order by sal desc limit 0,1;
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+
1 row in set (0.00 sec)

方法二

mysql> select sal from emp where sal>=all(select sal from emp);
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+
1 row in set (0.02 sec)

方法三

首先通过自连接查出工资不属于最高的
mysql> select distinct t.sal from emp e join emp t on e.sal > t.sal;
+---------+
| sal     |
+---------+
|  800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 1300.00 |
+---------+
11 rows in set (0.01 sec)
再查询一次工资排除工资不属于最高的
mysql> select sal from emp where sal not in (select t.sal from emp e join emp t on e.sal > t.sal);
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+
1 row in set (0.00 sec)

取得平均薪水最高的部门的部门编号（至少给出两种解决方案）

mysql> select deptno from emp group by deptno order by avg(sal) desc limit 1;
+--------+
| deptno |
+--------+
|     10 |
+--------+
1 row in set (0.00 sec)

mysql> select max(t.avgsal) from (select avg(sal) avgsal from emp group by deptno) t;
+---------------+
| max(t.avgsal) |
+---------------+
|   2916.666667 |
+---------------+
1 row in set (0.00 sec)

取得平均薪水最高的部门的部门名称

mysql> select d.dname from emp e join dept d on e.deptno=d.deptno group by e.deptno order by avg(sal) desc limit 1;
+------------+
| dname      |
+------------+
| ACCOUNTING |
+------------+
1 row in set (0.00 sec)

求平均薪水的等级最低的部门的部门名称

SELECT
	t.*,
	s.grade 
FROM
	(
	SELECT
		d.dname,
		avg( sal ) AS avgsal 
	FROM
		emp e
		JOIN dept d ON e.deptno = d.deptno 
	GROUP BY
		d.dname 
	) t
	JOIN salgrade s ON t.avgsal BETWEEN s.losal 
	AND s.hisal 
WHERE
	s.grade = (
	SELECT
		grade 
	FROM
		salgrade 
	WHERE
		( SELECT avg( sal ) AS avgsal FROM emp GROUP BY deptno ORDER BY avgsal ASC LIMIT 1 ) BETWEEN losal 
	AND hisal 
	);

结果

+-------+-------------+-------+
   | dname | avgsal      | grade |
   +-------+-------------+-------+
   | SALES | 1566.666667 |     3 |
   +-------+-------------+-------+

取得比普通员工 (员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名

mysql> select ename,sal from emp where sal > (select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null));
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)

取得薪水最高的前五名员工

mysql> select ename,sal from emp order by sal desc limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| FORD  | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)

取得薪水最高的第六到第十名员工

mysql> select ename,sal from emp order by sal desc limit 5,5;
+--------+---------+
| ename  | sal     |
+--------+---------+
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD   | 1250.00 |
+--------+---------+
5 rows in set (0.00 sec)

取得最后入职的 5 名员工

mysql> select * from emp order by hiredate desc limit 5;
+-------+--------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME  | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+--------+---------+------+------------+---------+------+--------+
|  7876 | ADAMS  | CLERK   | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |
|  7788 | SCOTT  | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7934 | MILLER | CLERK   | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |
|  7900 | JAMES  | CLERK   | 7698 | 1981-12-03 |  950.00 | NULL |     30 |
|  7902 | FORD   | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+--------+---------+------+------------+---------+------+--------+
5 rows in set (0.00 sec)

取得每个薪水等级有多少员工

mysql> select grade,count(grade) num from emp e join salgrade s on e.sal between s.losal and s.hisal group by grade;
+-------+-----+
| grade | num |
+-------+-----+
|     1 |   3 |
|     2 |   3 |
|     3 |   2 |
|     4 |   5 |
|     5 |   1 |
+-------+-----+
5 rows in set (0.00 sec)

题目：有 3 个表 S (学生表)，C（课程表），SC（学生选课表） S（SNO，SNAME）代表（学号，姓名） 95 / 103 C（CNO，CNAME，CTEACHER）代表（课号，课名，教师） SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）问题： 1，找出没选过 “黎明” 老师的所有学生姓名。 2，列出 2 门以上（含 2 门）不及格学生姓名及平均成绩。 3，即学过 1 号课程又学过 2 号课所有学生的姓名。
13.1：找出没选过 “黎明” 老师的所有学生姓名。
1
13.2：列出 2 门以上（含 2 门）不及格学生姓名及平均成绩。
13.3：即学过 1 号课程又学过 2 号课所有学生的姓名。

列出所有员工及领导的姓名

mysql> select e.ename,t.ename from emp e left join emp t on e.mgr = t.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| KING   | NULL  |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+
14 rows in set (0.00 sec)

列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称

mysql> select a.empno,a.ename,d.dname from emp a join emp b on a.mgr = b.empno and a.hiredate < b.hiredate join dept d on a.deptno = d.deptno;
+-------+-------+------------+
| empno | ename | dname      |
+-------+-------+------------+
|  7369 | SMITH | RESEARCH   |
|  7499 | ALLEN | SALES      |
|  7521 | WARD  | SALES      |
|  7566 | JONES | RESEARCH   |
|  7698 | BLAKE | SALES      |
|  7782 | CLARK | ACCOUNTING |
+-------+-------+------------+
6 rows in set (0.00 sec)

列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门.

mysql> select * from dept d left join emp e on d.deptno = e.deptno;
+--------+------------+----------+-------+--------+-----------+------+------------+---------+---------+--------+
| DEPTNO | DNAME      | LOC      | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+--------+------------+----------+-------+--------+-----------+------+------------+---------+---------+--------+
|     20 | RESEARCH   | DALLAS   |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|     30 | SALES      | CHICAGO  |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|     30 | SALES      | CHICAGO  |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|     20 | RESEARCH   | DALLAS   |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|     30 | SALES      | CHICAGO  |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|     30 | SALES      | CHICAGO  |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|     10 | ACCOUNTING | NEW YORK |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|     20 | RESEARCH   | DALLAS   |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|     10 | ACCOUNTING | NEW YORK |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|     30 | SALES      | CHICAGO  |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|     20 | RESEARCH   | DALLAS   |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|     30 | SALES      | CHICAGO  |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|     20 | RESEARCH   | DALLAS   |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|     10 | ACCOUNTING | NEW YORK |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|     40 | OPERATIONS | BOSTON   |  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |
+--------+------------+----------+-------+--------+-----------+------+------------+---------+---------+--------+
15 rows in set (0.00 sec)

列出至少有 5 个员工的所有部门

mysql> select d.dname,count(e.deptno) num from emp e join dept d on d.deptno = e.deptno group by e.deptno having count(e.deptno) >= 5;
+----------+-----+
| dname    | num |
+----------+-----+
| RESEARCH |   5 |
| SALES    |   6 |
+----------+-----+
2 rows in set (0.00 sec)

列出薪金比”SMITH” 多的所有员工信息

mysql> select * from emp where sal > (select sal from emp where ename = 'SMITH');
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)

列出所有”CLERK”(办事员) 的姓名及其部门名称，部门的人数.

mysql> select e.ename,d.dname,num from emp e join dept d on e.deptno = d.deptno join (select deptno,count(deptno) num from emp group by deptno) t on t.deptno = e.deptno where job = 'CLERK';
+--------+------------+-----+
| ename  | dname      | num |
+--------+------------+-----+
| SMITH  | RESEARCH   |   5 |
| ADAMS  | RESEARCH   |   5 |
| JAMES  | SALES      |   6 |
| MILLER | ACCOUNTING |   3 |
+--------+------------+-----+
4 rows in set (0.00 sec)

列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数.

mysql> select job,count(*) from emp group by job having min(sal)>1500;
+-----------+----------+
| job       | count(*) |
+-----------+----------+
| ANALYST   |        2 |
| MANAGER   |        3 |
| PRESIDENT |        1 |
+-----------+----------+
3 rows in set (0.02 sec)

列出在部门”SALES”<销售部> 工作的员工的姓名，假定不知道销售部的部门编号.

mysql> select e.ename from emp e where deptno = (select deptno from dept where dname = 'SALES');
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+
6 rows in set (0.00 sec)

列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，雇员的工资等级.

mysql> select e.ename,d.dname,a.ename '领导名',grade from emp e left join emp a on e.mgr = a.empno left join dept d on e.deptno = d.deptno left join salgrade s on e.sal between s.losal and s.hisal where e.sal > (select avg(sal) from emp);
+-------+------------+--------+-------+
| ename | dname      | 领导名 | grade |
+-------+------------+--------+-------+
| CLARK | ACCOUNTING | KING   |     4 |
| JONES | RESEARCH   | KING   |     4 |
| SCOTT | RESEARCH   | JONES  |     4 |
| FORD  | RESEARCH   | JONES  |     4 |
| BLAKE | SALES      | KING   |     4 |
| KING  | ACCOUNTING | NULL   |     5 |
+-------+------------+--------+-------+
6 rows in set (0.00 sec)

列出与”SCOTT” 从事相同工作的所有员工及部门名称

mysql> select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno where e.job = (select job from emp where ename = 'SCOTT');
+-------+----------+
| ename | dname    |
+-------+----------+
| SCOTT | RESEARCH |
| FORD  | RESEARCH |
+-------+----------+
2 rows in set (0.00 sec)

列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金

1 2	mysql> select ename,sal from emp where sal in (select distinct sal from emp where deptno = 30) and deptno <> 30; Empty set (0.00 sec)

列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金。部门名称
列出在每个部门工作的员工数量，平均工资和平均服务期限
列出所有员工的姓名、部门名称和工资
列出所有部门的详细信息和人数
列出各种工作的最低工资及从事此工作的雇员姓名
列出各个部门的 MANAGER (领导) 的最低薪金
列出所有员工的年工资，按年薪从低到高排序
求出员工领导的薪水超过 3000 的员工名称与领导名称
求出部门名称中，带’S’字符的部门员工的工资合计、部门人数
给任职日期超过 30 年的员工加薪 10%.