技术学习|CDA level I 数据库应用（数据查询语言DQL）

DQL返回的查询结果是存储在内存中的虚拟结果集，所以在DQL中执行的筛选、计算、排序等操作，都不会影响真实表中的数据

一、单表查询

1、关键词

关键词	功能	说明
selete	要返回的字段或表达式	查询语句必须包含的关键词，主要用来对查询的字段进行筛选
from	从中检索数据的表	用于限定数据查询的范围
where	行级过滤	对记录进行筛选
group by	分组字段
having	组级过滤	对分组进行筛选
order by	输出排序	对查询结果进行排序
limit	限制输出	限制查询结果的输出数量

select {*|字段列表|表达式|公式|常量}
from <表名>[,<表名2>,……,<表名n>]
[where <查询条件>]
[group by <字段名>]
[having <查询条件>]
[order by<字段名> [asc|desc]]
[limit [偏移量,]行数]
--除select和from子句外，其他子句均可以省略，如果不省略，各个子句间的书写顺序是固定的。

2、基础查询语句

select是用来对数据的字段进行筛选的关键词，常搭配from使用

(1)常量：select后接常量时，会返回以该常量为字段名，以该常量为内容的记录。若指定了表名，则返回的列的长度与该表的记录数相等。

select 常量值(如select 1)
[from <表名1>[,<表名2>,……,<表名n>];]

(2)公式：select后接公式，会返回以该公式为字段名、以该表达式结果为内容的记录。若指定了表名，则返回的列的长度与该表的记录数相等。

select 公式(如select 1+1)
[from <表名1>[,<表名2>,……,<表名n>];]

(3)表达式：select后接表达式时，若该表达式成立，则返回代表真值的1；若该表达式不成立，则返回代表假值的0，查询结果中的字段名显示为该表达式。若指定了表名，则返回的列的长度与该表的记录行数相等。

select 表达式
[from <表名1>[,<表名2>,……,<表名n>];]

(4)字段名：如果只需要查看部分字段，可以通过指定字段名的方式来查看，多个字段之间用逗号隔开。

select <字段名1>[,<字段名2>,……,<字段名n>]
from <表名1>[,<表名2>,……,<表名n>];

(5)*星号：通配符，熟悉一个表中的所有信息

select *
from <表名1>[,<表名2>,……,<表名n>];

(6)去重：使用distinct关键词对重复的记录进行去重。distinct既支持单个字段的去重，又支持多个字段的联合去重在进行多个字段的重复去重时，只有在多个字段完全一样的情况下，才会将其视为重复字段进行删除。distinct必须位于第一个字段前，多个字段间用逗号隔开

select distinct <字段名1>[,<字段名2>,……,<字段名n>]
from <表名>;

(7)设置别名：使用as关键字设置别名。as关键字可以省略，当别名中有空格或为纯数字时需要加引号来告诉数据库管理系统这是一个完整的字符串。

select <字段名> [as] <字段别名>
from <表名> [as] <表别名>;

3、条件查询

select <字段名列表>
from 表名
where <查询条件>

在限定条件查询的条件时，往往需要使用各种运算符对条件进行限定，运算符是一个保留字或字符，主要用来连接where后面的条件，分为算术运算符、逻辑运算符和比较运算符。算术运算符主要用于科学计算，比较运算符用于进行判断，并返回1(true)、0(false)或null(空值)作为结果，逻辑运算符用于连接多个条件。

类别	写法	作用
算术运算符	+	加法运算
	-	减法运算
	*	乘法运算
	/	除法运算
逻辑运算符	and	且
	or	或
	not	非
比较运算符	=	判断左右两个值是否相等
	>/>=	判断左边的值是否大于/大于或等于右边
	</<=	判断左边的值是否小于/小于或等于右边
	!=/<>	判断左边的值是否不等于右边
	between…and	判断值是否在某个连续区间范围内
	in/not in	判断左值等于/不等于右边列表中的任意一个值
	like	用于字符串型字段的匹配
	is null/is not null	判断一个值是/不是null

(1)单一条件查询：是对结果集进行单一条件限定的方法。

(2)多条件查询：多个独立条件查询，使用逻辑运算符将不同的条件根据逻辑需求连接起来。如果查询的多个条件是相互独立的多个值，可以直接将所有的值放入一个列表，使用in/not in关键词进行判断。因为列表中的值之间相互独立，所以该关键词并不限制所判断字段的数据类型。

select <字段名列表>
from <表名>
where <字段名> in (值1)[,值2，……,值n];

(3)连续范围查询：如果想对指定范围的信息进行查询，可以使用比较运算符来实现，在查询范围是一个连续区间的情况下，可以使用between…and关键词来进行判断，该范围是一个闭区间，等同于>=and<=，它用来判断值是否在某个连续的区间范围，所以只能针对数值型数据或时间日期型数据进行判断。

select <字段名列表> 
from <表名>
where between 最小值 and 最大值;

(4)空值查询

数据在插入或者通过文件批量导入数据库时，如果内部包含没有内容的记录，则会被数据库自动用空值null填充。在数据库中空值null的本质为未知值，不属于任何数据类型。因此空值和任何类型的数据进行运算的结果都为null，无法与任何值包括空值自己作比较。

--在where子句查询条件中，可以使用is null/is not null关键词进行空值查询
select <字段名列表>
from <表名>
where <字段名> is [not] null;

空值无法通过算数运算符找出，如上面，将is null改为= null，则查不出任何数据。

(5)模糊查询

在where子句中，当不知道准确的查询条件时，可以使用like关键词和通配符进行模糊查询。如单独使用like关键词时，效果等同于“=”，通配符是用于替代字符串中的任何位置的字符的符号，同时使用like关键词在where字句中构成查询条件。模糊查询只能用于文本字段（字符串型）。当不知道通配符所需要替代的字符数量时，使用%来匹配0个或多个字符，当明确知道需要替代的字符数量时，使用下划线"_"，一个下划线匹配一个字符，通配符的位置应该与需要匹配的字符位置一致。

select <字段名列表>
from 表名
where 字段名 like <通配符>;

4、查询结果排序

在数据库中，如果没有声明排序顺序，可能会出现相同的代码返回同一个查询结果，但是查询结果的顺序不一样。

使用order by子句对select语句查询出的结果按一个或多个字段进行排序。当进行多字段排序时，多个字段用逗号隔开，数据库管理系统会先按第一个字段的取值进行排序，当第一个字段的取值相同时，再按第二个字段进行排序。若第一个字段的取值中没有重复数据，则不会按照第二个字段排序。

order by子句可以指定排序方向，asc关键词代表按照字段的取值进行升序排序，desc关键词代表按照字段的取值进行降序排序，排序方向的关键词与字段名使用空格隔开。当没有指定排序方向时，默认该字段按照升序进行排序，每个用来排序的字段都可以单独指定排序方向。

select 字段1[,字段2,……,]
from 表名
order by 字段1[排序方向,字段2 排序方向,……];

5、限制查询结果数量

在使用select语句时，返回的是满足条件的所有记录行，但有时候因为记录过多，查询速度会很慢，故可使用limit子句来限制查询结果的输出数量。

limit关键词可指定一个或多个数字参数，参数必须是整数常量，第一个参数用来指定数据的偏移量，即从什么位置开始数据截取。偏移量指希望截取的第一条记录的位置距离查询结果集中第一条记录的位置偏移了几行。当希望数据从第一条记录就开始输出时，该参数可以忽略或者为0（不是1），即代表着截取的位置与第一行记录的位置偏移0行，第二个参数指定返回记录的数量，如果只给定一个参数，那么代表的就是返回记录的数量，该参数是不可以忽略的。

select 字段1[,字段2,……]
from 表名
limit [偏移量,]行数;

limit子句配合order by子句使用时，可以轻松截取特定排序范围的数据。如要查看销量前3的数据，可以先使用order by子句对数据进行排序，然后使用limit进行输出（但可能会因为重复值，导致并列数据的丢失。为避免这种情况的发生，可以额外增加一个排序字段，来尽可能减少重复的概率。）

6、聚合函数

函数名	描述
sum()	对一个数值字段求和
avg()	对一个数值字段求平均值
max()	对一个数值或日期时间字段求最大值
min()	对一个数值或日期时间字段求最小值
count()	对一个或多个字段计数

表结构中的计算通常会对一整列或者一整行数据产生影响，很难对某一个特定单元格进行单独处理，所以聚合函数也是作用于字段的。除count函数外的聚合函数只能作用于单独的数值型字段，添加多个参数或导致错误，所有的聚合运算都会自动忽略null。

select 聚合函数(字段1)[,聚合函数(字段2),……]
from <表名>

count函数可以对表中符合特定条件的所有行进行计数，它只会忽略null，如果该字段的取值非null，就会进行计数，包括数值型的0或者字符串型的空字符。对多个字段进行计数时，如果其中某一个字段的值为null，则该条记录并不会被忽略，只有多个字段的取值均为null时才会被忽略。count内的参数可以结合关键词distinct来进行不重复的计数，即在字段名前加distinct。

--返回emp表的员工总数
select count(*) 员工总数 from emp;
--本质也是统计全表的记录数，与*效果一致
select count(1) 员工总数 from emp;

7、分组查询

(1)组内聚合

group by可以把查询结果按照一个或多个字段进行分组，字段值相同的为一组。如果是多字段分组，多个字段之间没有先后顺序，则会将多个字段值相同的记录分为一组。如果分组字段中包含null，则null所在的行会单独分为一组。

因为每个组内通常包含多条记录，无法对应单个组别，所以分组后需要对每个组内的数据进行聚合运算，否则多对一的关系无法进行匹配。对维度进行聚合的本质是将原始数据中相同的多行值汇总为一行值的过程。

对度量进行聚合的本质是将原始数据中相同维度值对应的多个度量值按照运算规则计算为一个值的过程，可以使用sum，avg，count，max，min函数对数值型数据进行聚合计算（sum和avg可以对数值型数据进行求和和求平均值；max和min函数可以对数值型数据和日期时间型数据计算最大值和最小值，count函数可以对任何类型的数据进行计数）。

--单字段分组
select <字段名> 
from <表名>
[where <查询条件>]
group by <字段名列表>;

--查询公司内部不同奖金档位的获得人数(单字段分组)
select comm,count(*) from emp group by comm
--查询各部门不同职位的平均工资
select deptno,job,avg(sal) as 平均工资 from emp group by deptno,job;

(2)分组后 筛选

使用having子句来指定筛选条件，having子句通常和group by子句一起使用。having子句支持where子句中所有的操作符和语法。

区别是：where子句针对数据表进行筛选，在对查询结果进行分组前，就将不符合where查询条件的记录过滤掉，而having子句对分组聚合后的结果进行过滤，故having子句中的筛选字段必须是可以出现在分组结果中的字段。where子句是在分组聚合前执行，因此不能引用聚合函数作为筛选条件，而having子句是在分组聚合后执行的，因此可以引用聚合函数作为筛选条件。

select <字段名列表>
from <表名>
[where <查询条件>]
group by <分组字段列表>
having <筛选条件>;

--查询各部门clerk的平均工资
--用having子句筛选
select deptno,job,avg(sal) 平均工资 from emp group by deptno,job having job = 'clerk';
--用where子句筛选
select deptno,job,avg(sal) 平均工资 from emp where job = 'clerk' group by deptno,job;
​
--查询平均工资大于2000的部门
--select deptno,avg(sal) 平均工资 from emp group by deptno having avg(sal)>2000;

二、函数

可以在select语句中使用内置函数进行计算，也可以在where或having子句中使用函数计算结果作为筛选条件，对查询结果进行筛选。

1、数学函数

数字函数是用来进行数学相关运算的函数，主要用于处理数值型数据，其参数可以为数值也可以为数值型字段。如果计算产生错误，那么会返回空值null作为结果。

--返回20
select abs(-20);
--返回为空null
select sqrt(-20);
--查询emp表中收入最高的员工的工资是平均工资的多少倍，保留2位小数
select round(max(sal)/avg(sal),2) 倍数 from emp;

函数名	描述
abs(x)	返回x的绝对值
ceiling(x)	返回大于或等于x的最小整数（进一取整）
exp(x)	返回e的x次方
floor(x)	返回小于或等于x的最大整数（去掉小数取整）
greatest(expr1,expr2,expr3,……)	返回列表中的最大值
least(expr1,expr2,expr3,……)	返回列表中的最小值
ln()	返回数字的自然对数，以e为底
log(x)	返回自然对数（以e为底的对数）
log(base,x)	返回以base为底的对数
mod(x,y)	返回x除以y以后的余数
pi()	返回圆周率
pow(x,y)	返回x的y次方
rand()	返回0~1的随机数，可以通过提供一个参数（种子），生成一个指定的值
rand(x[,d])	返回x的四舍五入值，保留d位小数（d的默认值为0）
sign(x)	返回x的符号值，x是负数、0、正数时分别返回-1、0和1
sqrt(x)	返回x的平方根
truncate(x,y)	返回数值x保留到小数点后y位的值（与round函数最大的区别是不会进行四舍五入）

2、字符串函数

字符串函数主要用于处理字符串型数据，其参数可以为字符串也可以为字符串型字段。若参数中包含空值，则会返回null作为结果。

--将三个字符串合并为一个一个长字符串(返回'CDA数据分析')
select concat ('CDA','数据','分析');
--参数中包含空值，返回null(返回'null')
select concat ('CDA',null,'分析');
--参数中包含空白值，不影响结果(返回'CDA分析')
select concat ('CDA','','分析');
--从字符串第4位开始，截取长度为2个字符的字符串(返回'数据')
select substring('CDA数据分析',4,2);
--从字符串第4位开始，截取到字符串结束(返回'数据分析')
select substring('CDA数据分析',4);
--将emp表中job字段中的manager替换为director
select job,replace(job,'manager','director') from emp;

函数名	描述
ascii(s)	返回字符串s的第一个字符的ASCII码
bin(n)	返回值为n的二进制字符串表示
bit_length(s)	返回值为二进制的字符串s的长度
concat(s1,s2,…)	把字符串s1，s2等多个字符串合并为一个长字符串
concat_ws(x,s1,s2,…)	同concat(s1,s2,…)函数，但是每个字符串之间用x间隔，x可以是分隔符
field(s,s1,s2,…)	返回第一个字符串s在字符串列表(s1,s2,…)中的位置
find_in_set(s1,s2)	返回在字符串s2中与s1匹配的字符串的位置
insert(str,x,y,instr)	将字符串str从x位置开始，y个字符长的子串替换为字符串instr，返回结果
instr(str,substr)	返回子字符串substr在文本字符穿str中第一次出现的位置
locate(s1,s)	从字符串s中获取s1的开始位置
left(s,n)	返回字符串s的前n个字符
length(s)	返回字符串s的字符长度
lower(s)/lcase(s)	将字符串s的所有字母变成小写字母
ltrim(s)	去掉字符串s开头的空格
mid(s,n,len)	从字符串s的第n位置截取长度为len的子字符串，同substring(s,n,len)
position(s1,s)	返回字符串s1在字符串s中第一次出现的位置
quote(s)	用反斜线转义s中的单引号
repeat(s,n)	将字符串s重复n次
replace(s,s1,s2)	将字符串s2替代字符串s中的字符串s1
reverse(s)	将字符串s的顺序反过来
right(s,n)	返回字符串s的后n个字符
rtrim(s)	去掉字符串s结尾的空格
strcmp(s1,s2)	比较字符串s1和s2，若s1与s2相等，则返回0；若s1>s2，则返回1；若s1<s2，则返回-1
substring(s,start[,length])	从字符串s的start位置截取长度为length的子字符串
trim(s)	去掉字符串s开头和结尾处的空格
upper(s)/ucase(s)	将字符串s的所有字母变成大写字母

3、日期时间函数

标准日期和时间格式：'yyyy-mm-dd'或'hh:mm:ss'。

在MySQL中，任何标点符号都可以用作日期或者时间部分之间的分隔符，只要其作为日期是有意义的，它也可以将字符串型或数值型数据识别为一个日期。故大部分的日期时间函数均可以指定数值型数据或字符型数据作为参数，并根据上下文以字符串形式（如'yyyy-mm-dd hh:mm:ss'），或者数值形式（如yyyymmddhhmmss）来进行结果的返回。

--获取日期中的年月日(日期时间、字符串或者数值均可被识别)(2020 1 30)
select year('2020-01-30') 年份,month('20200130') 月份,day(20200130) 日;
--按照日月年的格式返回日期(30-01-20)
select date_format('20-01-30 12:00:00','%d-%m-%y') 日月年;
--计算2020年1月1日2个月后的七日期(2020-03-01)其中interval是固定关键词
select date_add("2020-01-01",interval 2 month);
--计算员工的工龄(雇佣日期与当前日期的差)
select ename,floor(datediff(curdate(),hiredate)/365) as 工龄 from emp;

函数名	描述
adddate(date,interval num type)/date_add(date,interval num type)	计算起始日期date加上长度num、单位为type的时间长度的日期
curdate()/current_date()	返回当前日期
curtime()/current_time()	返回当前时间
current_timestamp()/localtimestamp()	返回当前日期和时间
date()	从日期、日期时间表达式中提取日期值，或将字符串型数据转为日期型数据
datediff(d1,d2)	计算结束日d1和起始日d2之间间隔的天数
date_format(date,format)	根据format字符串格式化date的值
date_sub(date,interval num type)/subdate(date,interval num type)	计算起始日期date减去长度为num、单位为type的时间长度的日期。
day(date)	返回日期date的日期部分
dayname(date)	计算日期date的星期名
dayofmonth(date)	计算日期date是本月的第几天（1~31）
dayofweek(date)	返回日期date所代表的一星期中的第几天（1~7），1代表星期日，2代表星期一，以此类推
dayofyear(date)	计算日期date是本年的第几天
from_days(n)	计算从0000年1月1日开始n天后的日期
from unixtime(ts,f)	根据指定的f格式，格式化UNIX时间戳ts
hour(t)	返回t中的小时值（0~23）
last_day(date)	返回给定日期的那一月份的最后一天
maketime(hour,minute,second)	3个参数分别为小时、分钟、秒，将其组合为完整时间
minute(t)	返回t中的分钟值（0~59）
monthname(date)	返回日期date中的月份名称
month(date)	返回日期date中的月份名值（1~12）
now()	返回当前日期和时间
quarter(date)	返回日期date是第几季节（1~4）
second(t)	返回t中的秒钟值（0~59）
sec_to_time(s)	将以秒为单位的时间s转换为时分秒的格式
str_to_date(string,format_mask)	将字符串转换为日期
time()	提取传入参数的时间值
time_format(t,f)	根据指定的f格式，格式化时间t
time_to_sec(t)	将时间t转换为秒
timediff(t1,t2)	计算t1和t2的时间差值
to_days(date)	计算日期date距离0000年1月1日的天数
unix_timestamp([date])	返回一个UNIX时间戳（从1970-01-01 00:00:00开始的秒数，date默认值为当前时间）
week(date)/weekofyear(date)	计算日期date是本年的第几周（0~53）
weekday(date)	计算日期date是星期几，0代表星期一，1代表星期二
year(date)	返回日期date的年份（1000~9999）

4、分组合并函数

分组合并函数可以将同一个分组内的多条记录的取值合并为一个长文本字符串。尽管所有的数据类型都可以合并为一个长字符串的形式，但在实际应用中，非字符串类型的数据可以通过聚合函数返回一个特征值作为多条记录的聚合值，故分组合并函数在实际上用于字符串类型的数据进行聚合运算，在进行分组合并时，与其他的聚合函数一样，也会忽略空值。

group_concat([distinct] str [order by str asc/desc] [separator])
--其中distinct用来对组内字符串进行去重，order by用来指定字符串的合并顺序，separator用来指定连接的分隔符，默认为逗号

group_concat函数通常与group by子句一起使用，将分组后每个组内的多行字符串合并为一行；也可以单独使用，相当于表中所有记录为一行，将表中所有行合并为一行。

--查询各部门员工姓名(按照deptno进行分类，第二列为员工的姓名列表)
select deptno,group_concat(ename order by ename separator '/') 员工姓名列表 from emp group by deptno;
--查询公司所有部门编号
select group_concat(distinct deptno order by deptno) 部门 from emp;

5、逻辑函数

逻辑函数用来对表达式进行判断，根据表达式是否满足条件，执行相关的流程

(1)空值函数

因为空值在数据库中是一种特殊的未知值，它无法和任何值进行运算和对比，所以可以使用空值函数先对空值进行判断和处理，再进行计算和查询。

ifnull(expression,alt_value)
--若第一个表达式为null，则使用alt_value来替代空值，若第一个表达式不为null，则返回第一个表达式的值

--查询每位员工的实发工资(基本工资+奖金)
select ename,sal+ifnull(comm,0) 实发工资 from emp;

(2)if函数

若满足条件，则返回一个值；若不满足条件，则返回另外一个值

--若expr1的值为true，则返回expr2的值;若expr1的值为false，则返回expr3的值
if(expr1,expr2,expr3)

(3)case逻辑表达式

case逻辑表达式与if函数的用法类似，但if表达式一次只能判断一个表达式的值，故如果有多个表达式需要判断，就需要进行嵌套，而case逻辑表达式可以一次进行多个表达式的判断，故在判断表达式数量较多的时候，使用case，逻辑会更加清晰。

--以case开头,以end结尾，中间可以进行多个条件的判断。若expr1的值为true，则返回expr2的值;若expr1的值为false，再判断expr3的值，若expr3的值为true，则返回expr4，以此类推，若所有的when子句后面的条件都不满足，则返回expr的值
case when expr1 then expr2 [when expr3 then expr4…else expr] end;

--查询员工的工资级别，3000及以上为高，1500以及下为低，其余为中
--使用if函数
select ename,sal,if(sal>=3000,'高',if(sal<=1500,'低','中')) 工资级别 from emp;
--使用case逻辑表达式
select ename,sal,case when sal>=300 then '高' when sal<=1500 '低' else '中' end 工资级别 from emp;

6、开窗函数

聚合函数用于对表中的数据进行分组聚合，将多条记录聚合为一条，以组为最小单位返回查询结果。但很多情况下需要对单条记录与该记录所处分组进行比较，这里就需要用到开窗函数。

开窗函数的本质还是聚合运算，只是更加灵活，对数据的每一行都使用与该行相关的行进行计算并返回计算结果，有几条记录执行完返回结果还是几条。

开窗函数和聚合函数一样，开窗函数可以按照一个或者多个字段进行划分，且聚合函数也可以用于开窗函数。

(1)over函数

开窗函数使用over函数来指定分区和运算规则，over后面的括号中所有内容都可以省略，在只使用over函数的情况下，表中所有的数据为一个区，开窗函数会基于表中所有的行进行计算，并将计算的结果返回每一行记录中。

开窗函数名 ([<字段名>]) over ([partition by <分区字段>] [order by <排序字段>[desc]][<滑动窗口范围>])

--聚合函数，得到公司所有员工的平均工资
select avg(sal) 平均工资 from emp;
--开窗函数，查询每位员工与公司所有员工的平均工资之间的情况(在每行后添加一列，填写记录)
select *,avg(sal) over() 平均工资 from emp;

(2)partition by子句

与order by子句类似，在over函数中使用它来指定用来分区的一个或者多个字段，开窗函数在不同的分区内分别执行，并将每个分区的计算结果显示在分区内每条记录中。

--聚合函数，查询各部门的平均工资
select deptno,avg(sal) from emp group by deptno;
--开窗函数，查询每位员工与所属部门平均工资之间的情况
select *,avg(sal) over(partition by deptno) 平均工资 from emp;
--over函数指定了partition by deptno,即将表中所有记录按照deptno进行分区，deptno取值一样的记录会分为一个区，每个分区内会有多条记录，多个分区之间由边界分隔，开窗函数在不同分区内分别执行，跨越分区重新初始化。故avg函数在开窗运算时，计算的是同一分区内的所有记录，对同一个部门的员工计算平均值，并在每一条记录行返回计算结果。

(3)order by子句

order by子句用来指定组内字段的排列顺序。

--按入职日期查询各部门的累计工资
select *,sum(sal) over(partition by deptno order by hiredate) 累计工资 from emp;
--语句中的"order by hiredate"指定分区内字段的顺序要按照"hiredate"入职日期的升序进行排序。

order by子句可以和partition by子句配合使用，也可以单独使用。因为over函数指定排序后没有指定滑动窗口范围，所以sum函数开窗运算时，默认计算当前分区内第一条排序字段取值到当前排序字段取值范围内的记录，这种计算范围内通常用来计算累计值。

(4)序号函数

聚合函数可以用于分组聚合，也可以用于开窗计算，除聚合函数外，MySQL还有专门为开窗计算的函数，这些函数必须和over函数一起使用。

序号函数一共有三种，分别为row_number()、rank()和dense_rank()。这三者都可以返回记录按照指定的字段进行排序后每条记录的序号，这三者的区别在于序号的生成规则不同：**row_number()生成连续的序号，当排序的字段存在相同的值时，row_number()也会依次进行排序，序号值不会重复；而rank()和dense_rank()则考虑到了排序字段值相同的情况，会产生相同的序号值，相同值编号完成后，rank()将会跳过相同的排名号，按照下一顺位记录的行数和数字来排下一个，dense_rank()的排名是连续的（如1,1,2,3,4）

常见开窗函数

函数名	描述
cume_dist()	计算一组值中一个值的累计分布
dense_rank()	根据order by子句为分区中的每一行分配一个等级。它将相同的等级分配给具有相等值的行。如果两行或更多行具有相同的排名，排名值序列中将没有间隙
first_rank()	返回相对于窗口框架第一行的指定表达式的值
lag()	返回分区中当前行之前的第N行的值。如果不存在前一行，则返回null
last_value()	返回相对于窗口框架最后一行的指定表达式的值
lead()	返回分区中当前行之后的第N行的值。如果不存在后续行，则返回null
nth_value()	从窗口框架的第N行返回参数的值
ntile()	将每个窗口分区的行分配到指定数量的排名组中
percent_rank()	计算分区或结果集中行的百分数等级
rank()	与dense_rank()函数相似，不同之处在于当两行或更多行具有相同的等级时，等级值序列中存在间隙
row_number()	为分区中的每一行分配一个顺序整数

三、多表查询

1、纵向合并查询

从不同表中抓取分析所需的字段，并将查询结果合并为一个单独的结果集。

使用union关键词把多条select语句的查询结果合并为一个结果集。

纵向合并并不需要指明连接条件，而是单纯地把多个结果集进行纵向追加，因此被合并的多个结果集的字段数量、顺序必须完全一致，而其数据类型必须兼容。数据库管理系统会将第一个结果集的字段名作为合并后的结果集的字段名。

select <字段名>[,<字段2>,…] from <表名1>
union[ all]
select <字段名>[,<字段2>,…] from <表名2>
--all关键词用来指定合并后的结果集是否保留重复记录，如果省略该关键词，那么数据库管理系统会对连接后的结果去除重复值；如果保留该关键词，那么会保留合并后的结果集中的重复记录

2、横向连接查询

横向连接查询通过多个表中具有相同意义的一个或者多个字段，将两个或者多个表横向连成一个虚拟结果集，从而补充更多字段信息，达到查询不同表中字段信息的目的。（横向连接查询会导致结果集的字段数增加，即提供更多的分析数据，纵向合并查询会导致结果集的记录数增加，即对记录本身进行追加。）

(1)对应关系

表与表之间有3种不同的对应关系，主要根据用来建立连接关系的字段会不会出现重复值来决定，分别为一对一、一对多和多对多。将建立连接关系的字段中有重复值的表称为多表，没有重复值的表称为一表。

• 一对一关系：两个表中建立连接关系的字段均没有重复值。

• 一对多关系：两个表中建立连接关系的字段仅在其中一个表中有重复值。

• 多对多关系：两个表中建立连接关系的字段均有可能出现重复值。

(2)连接方式

选择连接方式时，首先要明确分析所需的信息在哪几个表，即需要对哪些表进行查询；其次要确定查询所涉及的表之间的主附关系，即哪个表作为主表，哪个表作为附表。（如主要目的是对业务行为的属性从不同角度进行统计分析，便可将一表(维度表)作为主表来保证维度的完整性；如主要目的是对业务行为产生的结果进行统计分析，便可将多表(事实表)作为主表来保证度量的准确性）通常情况下，如果业务需求中没有明确指明要保证维度完整性，那么优先保证度量 的准确性，将度量值所在的表作为主表（通常为多表）。最后确定表和表之间的对应关系和连接关系。

• 内连接：本质上是对两个表中的记录取交集，内连接没有主附表的区别，只连接两个表中满足条件的记录。

--使用on声明连接条件
select <字段名1> [,<字段名2>,…,<字段n>] from <表名1> [inner] join <表名2> on <连接条件>;
--使用using声明连接条件
select <字段名1> [,<字段名2>,…,<字段n>] from <表名1> [inner] join <表名2> using (<字段名1>[,<字段名2>,…,<字段名n>]);

这两种方法均可以用来对表进行内连接操作，如果join前面的关键词被省略，那么会默认为内连接。on与using的区别在于，using后面使用括号直接将用于连接的公共字段名括起来，所以只适用于连接表所使用的公共字段的字段名一致的情况，且只能为等值连接。当两个表之间使用多个字段名一致的字段进行连接时，若使用using声明连接条件，则书写较为简洁；若使用on声明连接条件，则在连接的表拥有相同的字段名的情况下，必须声明该字段名所在的表名，格式为表名.字段名，如果字段名不一致，可以不声明表名。

--查询manager的姓名、所属部门名称和入职日期
--使用on声明连接条件
select ename,dname,job,hiredate
from emp
inner join dept on emp.deptno=dept.deptno
where job='manager';
--使用using声明连接条件
select ename,dname,job,hiredate
from emp
inner join dept using(deptno)
where job='manager';

• 左外连接：左外连接会按照连接条件，返回两个表中满足条件的记录，包含左表中的所有记录，右表匹配不到则显示为null，左外连接以左表为主表。

--使用on声明连接条件
select <字段名1> [,<字段名2>,…,<字段n>] from <表名1> left join <表名2> on <连接条件>;
--使用using声明连接条件
select <字段名1> [,<字段名2>,…,<字段n>] from <表名1> left join <表名2> using (<字段名1>[,<字段名2>,…,<字段名n>]);

• 右外连接：右外连接会按照连接条件，返回两个表中满足条件的记录，包含右表中的所有记录，左表匹配不到则显示为null，右外连接以右表为主表。

--使用on声明连接条件
select <字段名1> [,<字段名2>,…,<字段n>] from <表名1> right join <表名2> on <连接条件>;
--使用using声明连接条件
select <字段名1> [,<字段名2>,…,<字段n>] from <表名1> right join <表名2> using (<字段名1>[,<字段名2>,…,<字段名n>]);

需要注意，在进行多表连接时，count(*)需要慎用，以避免逻辑错误

• 全外连接：全外连接会返回两个表中全部的记录，本质上是对两个表中的记录取并集。全连接没有主附表的区别，按照连接条件可以匹配到的记录会返回匹配后的结果，匹配不到的记录用null进行填充。

在MySQL 8.0中并不支持全连接的方式，即不存在直接用来对两个表进行全连接的关键词，但可以通过合并查询左外连接和右外连接结果的方式来达到全连接的效果。

select <字段名1> [,<字段名2>,…,<字段n>] from <表名1> left join <表名2> on <连接条件>;
union
select <字段名1> [,<字段名2>,…,<字段n>] from <表名1> right join <表名2> on <连接条件>;

• 自连接查询

同个表格中不同的字段或记录间也拥有逻辑关系。如地域中的省市县三级的层级递进关系、员工有上级，上级同样也是员工。

MySQL可以通过from子句中设置相同的表、不同别名的方式，将一个表虚拟成多个表进行连接。自连接本身并不是一种特殊的连接方式，本质上它将物理上为同一个表在逻辑上作为两个独立的表进行连接，连接的规则和方式与两个物理上不同的表的连接查询的规则和方式并无区别。因为本质上是一个表，所以该连接方式所有的字段引用都必须声明其所在的表，格式为表名.字段名，且不能使用using进行连接条件的声明，否则数据库会因为无法确定表名而报错。

--emp表，有empno(员工编码)、ename(员工姓名)、mgr(直属上级的员工编号)等字段
select 员工表.ename 员工姓名,领导表.ename 领导姓名
from emp 员工表 left join emp 领导表
on 员工表.mgr=领导表.empno;

• 多表连接：连接方式和规则与两个表的连接相同，本质上来讲，多表连接就是在进行查询操作之前，先使用on条件将两个表进行连接形成的一个虚拟结果集，用连接后的虚拟结果集和第三个表做连接查询再形成一个新的单一虚拟结果集，以此类推。

--查询入职日期早于直属领导的员工的姓名及所属部门
--dept有deptno、与emp中的deptno具有相同的意义、dname
select 员工表.empno,员工表.name,dname
from emp 员工表
left join emp 领导表 on 员工表.mgr=领导表.empno
left join dept on 员工表.deptno=dept.deptno
where 员工表.hiredate<领导表.hiredate;

• 交叉连接：不需要连接条件而是直接对两个表中的每行都进行两两连接。如果表A有M条记录，表B有N条记录，那么该连接会对表A和表B中的数据进行一个M*N的组合，并返回M*N条记录。

--使用from子句
select <字段名1> [,<字段名2>,…,<字段名n>] from <表名1>,<表名2>;
--使用cross join
select <字段名1> [,<字段名2>,…,<字段名n>] from <表名1> cross join <表名2>;

(3)连接条件：连接条件分为等值连接和不等值连接。

非等值连接，即使用除等号外的运算符指定连接条件，如<、>、<>/!=，也可以使用like和between…and

--使用on声明不等值的连接条件
select <字段名1> [,<字段名2>,…,<字段名n>] from <表名1> [right]/[left] join <表名2> on <不等值条件>;
--使用where声明连接条件
select <字段名1> [,<字段名2>,…,<字段名n] from <表名1>,<表名2> where <连接条件>

使用where声明连接方式对交叉连接的结果进行筛选时，即可以使用不等值的连接条件也可以使用等值的连接条件，在使用等值的连接条件时，返回的结果与内连接返回的结果一致，但因为交叉的运算量非常大，所以在记录数较多的情况下会花费大量的时间来进行运算，所以内连接的情况并不推荐使用where声明连接条件。

四、子查询

将单次完整查询所返回的虚拟结果集视为一个临时表，并对这个临时表进行再次的查询和处理。子查询又称为嵌套查询，指在一个查询语句中包含另一个或多个完整的查询语句。

1、子查询的语法规则

• 子查询必须用圆括号括起来。

• 子查询最多可以嵌套255层（个别查询可能会不支持255层的嵌套）。

• 执行顺序由内到外，先执行内部的子查询，再执行外部的主查询。

• 若子查询返回的结果集在主查询中作为一个表，则必须添加表别名；若需要引用子查询中的计算字段，则必须添加列别名才可以引用。

2、子查询分类

按照子查询返回的结果不同，子查询可分为标量子查询（返回的结果是一个单行单列的数据）、行子查询（返回的结果是一条有多个字段的记录）、列子查询（返回的结果是包含多条记录的单个字段）及表子查询（返回的结果是一个拥有多个字段和多条记录的临时表）

子查询根据需求的不同，可以搭配不同子句使用，如果需要将子查询返回的结果作为主查询的计算字段，那么可以将子查询字段放在select子句中；如果需要将子查询返回的结果作为主查询的筛选条件，那么可以将子查询放在where或having子句中；如果需要将子查询返回的结果作为主查询的一个表，那么可以将子查询放在from或having子句中。

3、select子查询

select子查询仅支持标量子查询和列子查询，常用于业务指标的计算。如计算各个类别占整体的比例时，需要先计算各个类别的数量，再计算整体的数量，最后才能计算占比。

--将整体员工数量作为子查询来查询各部门员工人数占比
select deptno,count(*) 员工人数
count(*)/(select count(*) from emp) 员工人数占比
from emp 
group by deptno;

4、from/join子查询

from/join子查询通常为表子查询。在实际业务中，有时单个原始数据表中存储的现成字段无法涵盖分析所需的全部字段，这些需要处理的字段可能会分布在多个表中，需要依次进行计算之后再连接。

from/join子查询也可以用来进行计算字段和原始数据表中数据列的比较，因为有些查询子句或函数并没有考虑数据重复的情况，直接使用它们进行查询容易导致结果丢失，因此在不确定数据的重复情况下，可以使用子查询来规避这类错误。

--查询各部门最高工资的员工
--将各部门最高工资作为子查询，找出所有部门内部员工工资等于最高工资的员工
select empno,ename,sal,emp.deptno
from emp
left join(select deptno,max(sal) as 最高工资 from emp group by deptno) as t
on emp.deptno=t.deptno
where sal=最高工资;
--亦可用下方替代(开窗函数替代from/join)
select empno,ename,sal,deptno
from(select *,dense_rank() over(partition by deptno order by sal desc)工资排名 from emp) t
where 工资排名=1;

5、where/having子查询

where/having子查询通常用于根据较为复杂的业务逻辑需求对数据进行条件筛选，支持所有种类的子查询，并可以直接使用运算符对子查询返回的结果集进行比较和筛选。

如果子查询为标量子查询或者行子查询，即在只返回一条记录的前提下，可以直接使用>、>=、<、<=、=、<>/!=等比较操作字符来进行比较；如果子查询为列子查询或者表子查询，即返回多条记录，需要使用[not ]in、any/some、all、[not ]exists等关键词进行条件筛选，通常比较操作字符会直接添加在子查询外部括号的左边。

select <字段名列表> from <表名> where expr operator(select <字段名列表> from <表名>);

(1)比较操作符

当返回的结果集是单行时，可以直接使用比较操作字符直接进行比较。如果结果集包含多个字段，需要使用括号将多个字段括起来，两侧字段的数据类型、个数、顺序必须一一对应

--查询基本工资高于公司平均工资的员工信息(标量子查询)
select * from emp where sal>(select avg(sal) from emp);
--查询和smith同部门、同职位的员工
select empno,ename,job,deptno from emp
where (deptno,job)=(select deptno,job from emp where ename='smith') and ename<>'smith'

(2)in关键词

in关键词用来比较数值是否在子查询返回的结果集中。in关键词在只有一行返回结果的情况下，与比较操作符的"="效果一样。在查询时需要进行多对多的匹配，in关键词可以判断某个值是否在一个列表中，即返回结果集可以为多条记录。

not in关键词和in关键词使用方法相同，结果相反，用来匹配不在列表中的数据。同样其结果集包含多个字段，需要使用括号将多个字段括起来，其数据类型、个数、顺序必须一一对应。如果返回的结果中带有空值，则in关键词无法对空值进行判断，会返回空值。

--查询各部门最高工资的员工(in)
select empno,ename,sal,deptno 
from emp
where(deptno,sal) in (select deptno,max(sal) from emp group by deptno);
--查询普通员工的工资等级
select empno,ename,sal
from emp
where empno not in(select distinct mgr from emp where mgr is not null);

(3)exists关键词

exists关键词通常放在where子句里面作为外查询是否运行的前提条件，用于判断子查询是否有记录返回，若子查询没有记录返回，则外查询不会执行；若子查询有记录返回，则执行外查询。

exists关键词只用来作为查询的限制条件来决定外查询执不执行，本身并不会对数据进行筛选。not exists关键词与exists关键词使用方法相同，作用相反。

--查询公司部门情况，若公司有20部门，则返回该部门的员工情况(显示deptno为20的所有信息)
select * from emp where exits (select * from dept where deptno=20) and deptno=20;
--exists后接的子查询并不对数据进行筛选，只决定外查询执行不执行(显示所有数据)
select * from emp where exits (select * from dept where deptno=20);

(4)any/some关键词

any/some为同义词，会返回满足任意一个条件的记录，该关键词不能单独使用，其前面必须加上比较操作字符。

--查询基本工资高于30号部门所有员工的员工信息
select * from emp
where sal>any(select sal from emp where deptno=30) and deptno<>30;

(5)all关键词

all关键词只会返回满足所有条件的记录，该关键词不能单独使用，其前面必须加上比较操作字符，其中"<all"表示小于子查询结果集中的所有行，即返回小于最小值的记录；">all"表示大于子查询结果集中的所有行，即返回大于最大值的记录，"=all"与in等价。

--查询基本工资高于30号部门所有员工的员工信息
--将any改为all
select * from emp
where sal>all(select sal from emp where deptno=30);

6、子查询优化（从MySQL的4.1版本开始支持）

使用子查询进行select语句嵌套查询，可以一次完成很多逻辑上需要多个步骤才能完成的SQL操作。

子查询的执行效率不高，是因为MySQL需要为内层子查询的查询结果建立一个临时表，然后外层主查询在临时表上进行查询和筛选，查询完毕后再撤销这些临时表，这里多了一个创建和销毁临时表的过程，故子查询的速度会受到一定的影响，如果查询的数据量增大，这种影响会随之增大。

在进行查询的大多数情况下，可以优先使用连接查询（join）代替子查询，连接查询不需要建立临时表，因此其速度比子查询快。

所有的连接查询都可以替换为子查询，但并不是所有的子查询都可以用连接查询代替。以下三种情况只能使用子查询：在where子句中需要使用聚合函数作为筛选条件时；对分组查询后的结果再次分组时；在where、group by或having子句中使用开窗函数。