MySQL_04_数据查询语句

前面讲解了数据库以及数据表的结构相关的DDL，数据表中的数据相关的DML。有了数据库和数据表，并且有了数据，接下来就是数据的读取（查询语句，DQL）了。

1. 概述

数据查询语句DQL负责根据条件查询数据库并返回想要的结果。对于条件，可以根据一些关键字设置不同的条件进行查询；对于返回值，可以利用一些函数对满足条件的结果进行处理，并返回给客户端。

2. DQL

除了上面的条件查询以及对返回结果操作的函数意外，也可以从另一个角度分类：数据表由一条条数据组成，数据表也由一个个字段组成。所以可以从横纵两个维度分别查询以及组合查询。无论哪种解析，都有以下几种分类：

2.1 简单查询

这种查询语句没有什么条件限制，简单的查询全部数据、某些字段、对结果进行简单处理等等。具体查询语句如下所示：

2.1.1 简单查询

语法1结构如下所示：

select * from 表名;

这种查询方法是将表中的数据全部查询并输出，其中星号表示全部字段。

注意，这种方法仅仅适合自己查看，并不适合在程序中使用，因为数据库会先把星号转化成所有的字段，然后再执行该语句。

该方法的缺点是效率低，可读性差。在实际开发中，建议把星号换成全部字段，省去了数据库将其转换的过程。

语法2结构如下所示：

select 字段名1[, 字段名2, ...] from 表名;

查询表中指定的字段名，输出结果为该字段的所有数据。输出的字段顺序按照SQL语句中字段名的书写顺序排列。

2.1.2 起别名

这种语句就是上面提到的对查询结果进一步操作。有时候表中的字段并不是易读，可以对返回结果进行操作，比如对字段起别名。语法结构如下所示：

select 字段名 as 别名, [字段名 as 别名, ...] from 表名;

这种是将显示结果中的字段名换成别名，并不会对数据库中的字段名有影响，也可以直接将 as 关键字省略，字段名和别名之间用空格隔开即可。

注意，别名可以用单引号或双引号括起来，这样别名中就可以有任意字符了，比如空格之类。注意，单引号括起来的是字符串，双引号只能在mysql中使用，在Oracle中不被认可，所以尽量用单引号来表示。

2.1.3 查询并计算

这种语句也是上面提到的对查询结果进一步操作。有时候表中的数据不满足要求，可以对结果进行一些数学运算，比如表中存储的是月薪，而我们需要的是年薪，这时候可以在SQL语句中字段的后面加上数学表达式。语法结构如下所示：

select 字段名 数学表达式 from 表名;

同理，这种语句仅仅是对查询结果进行运算，并不会修改原始数据库中的内容。

2.2 条件查询

上面的简单查询仅仅是对原始数据简单查询，肯定不会满足日常要求，比如查询所有姓名为zhangSan的学生的相关信息，这时候需要增加一些条件语句来进行筛选，关键字where。语法结构如下所示：

select 字段名s from 表名 where 条件;

将满足条件的指定字段下的数据显示出来。这里的条件主要是限制一些数值以及字符串等等。

条件	含义
=	等于
<>或!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between … and …	两个值之间（闭区间）
>= and <=	两个值之间
is null	是null
is not null	不是null

条件语句中可以出现一些关键字，用于对条件进行限制。

关键字	含义
and	并且
or	或者
in	包含，相当于多个or
not	取非，主要用在 is 或 in 中
like	模糊查询，支持 % 和 下划线
%	匹配任意个字符
下划线	一个下划线匹配一个字符

• 注意，在判断是否为null的时候，应该用 is 或者 is not，不能用 = 来判断，因为null不是一个值，代表什么也没有，所有不能用 = 来衡量。

  

• 在所有的数据库中，只要有null参与的数学运算，最终结果都是null。

• 注意，当 and 和 or 一起出现的时候，会有优先级的问题。

  and的优先级比or高。此时如果 and 和 or 一起出现，并且优先级需要额外限制，那么可以用括号来保证优先级。注意，区分下面两个语句以及对应结果的区别。

  select id, ip from a where dns='www.a.shifen.com' and area='221.11.1.67' or area='61.132.163.68';

  select id, ip from a where dns='www.a.shifen.com' and (area='221.11.1.67' or area='61.132.163.68';

• in 关键字指的是某种集合。比如select id, ip from a where dns in ('www.a.shifen.com', 'www.b.shifen.com');，这个语句是找出dns字段中是’www.a.shifen.com'或'www.b.shifen.com'的记录，即 in 后面跟的是集合，不是区间。not in同理，指的是不在后面的集合中的记录。

  

• like 关键字指的是模糊查询，类似正则表达式。

  比如找出字符串中含有字母a的，语句如下：select dns from a where dns like '%a%';，因为%表示任意个字符，所以可以这样写，中间夹一个a即可。

  比如找出字符串中倒数第二个字母是o的，语句如下：select dns from a where dns like '%o_';，因为下划线表示一个字符，所以倒数第二个字符指定要找的即可。

  

2.3 排序

有时候需要对结果进行排序显示，这时候可以用排序关键字order。

2.3.1 简单排序

即仅仅根据某个字段的数据进行排序。语法结构如下所示：

select 字段名s from 表名 order by 字段名;

按照指定的字段名对筛选出来的数据进行升序排列。如果需要降序排列，可以在后面加上关键字desc。另外关键字asc表示升序。

其中，字段名也可以用字段的位置来代替，如下所示：

select 字段名s from 表名 order by 2 ，表示按照表中第二个字段对筛选出来的书序进行升序排列，但这种用字段位置的方法不推荐使用。

2.3.2 多字段排序

除了按照单个字段进行排序，SQL语句也支持多字段排序。比如高考的时候，先按照总分排序，总分相同的情况下按照语文成绩排序，如果语文成绩也相同，则按照数学成绩排序等等。语法结构如下所示：

select 字段名s from 表名 order by 字段名1 asc/desc[, 字段名2 asc/desc, ...];

这就是多字段排序，排序顺序按照字段的给出顺序排序。先按照字段1进行排序，如果相同，则按照字段2进行排序等等。

2.4 分组查询

在介绍分组查询之间，先简单介绍一下MySQL提供的内置函数，主要分为数据处理函数和分组处理函数两种。

2.4.1 数据处理函数

数据处理函数又被称为单行处理函数，即每次是一行一行的处理，一个输入对应一个输出。

和单行处理函数相对的是多行处理函数，一次处理多行数据，输出一个结果，即多个输入对应一个输出，比如求和函数，就是输入多行，最终只输出一个结果。

单行处理函数如下表所示：

函数名	描述
lower()	转换成小写
upper()	转换成大写
substr()	取子串
length()	取长度
trim()	去空格
str_to_date()	将字符串转换成日期
date_format()	格式化日期
format()	数字格式化
round()	四舍五入
rand()	生成随机数
concat()	字符串拼接
ifnull()	将null转换成一个具体值
case … when … then … else … end	判断case后的情况，如果是when，那么then，否则 else。

1. lower()

   将字符串转换成小写，注意依然是将查询的结果进行转换，并不是改变数据库中的数值。例子如下所示：

   select lower(name) from t_student;

   注意，这里其实就是单行处理函数，将查询中的每一行都输入到函数中，得到的结果仍然是原始行数的数据。

2. upper()

   将字符串转换成大写，同上。

3. substr(字符串, 起始下标, 截取长度)

   将指定字符串从指定位置处开始截取指定长度的子串并返回。注意，下标是从1开始。例子如下所示：

   select substr(name, 4, 8) from t_student;

   将 name 字段中的数据从下标4开始截取，截取长度为8。

4. length()

   获取指定字符串的长度。例子如下所示：

   select length(name) from t_student;

   获取 name 字段的长度。

5. trim()

   去掉字符串两边的空格，有时候，可能手动输入会输错，多了空格，这时候可以用 trim() 函数去掉空格。

   select trim(email) from t_student where no=5;

   select no, name from t_student where trim(email) = 'liSi@mysql.com';

6. ifnull()

   因为只要有null参与数学运算，那么最终结果就是null。为了避免这种情况，就需要使用ifnull函数。

   select id, (sal + ifnull(comm, 0)) * 12 from student;

   上面的例子就是从studnet表中，选择 id，并根据月薪以及补助comm计算年薪。但是补助comm有时候是null，这是就需要将null转换成 0来对待。

7. case … when … then[ … when … then …] … else … end

   该语句类似 if else 语句和 switch 语句。

   select name, job, (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal from student;

   上面的语句就是，选择 name, job, sal。但是如果 job 是MANAGER的时候，工资 sal 上调10%，job是SALESMAN的时候，工资上调50%，其余情况不变。

   注意，实际上这个语句是有返回值的，所以作为 select 后中的字段出现。

   

2.4.2 分组处理函数

正如前面提到的那样，分组处理函数又叫多处理函数，输入多行，最终输出一行。注意，分组函数在使用的时候，必须先进行分组再使用。即必须为其指定多个输入，也就是先分组，如果没有对数据进行分组，那么整张表就是一个组，输入到函数中。

分组采用的关键字是group by，功能是按照指定的字段对其他字段数据分组。如果group by不写，实际上也是有的，结果是将整张表作为一组。多行处理函数如下所示：

函数名	描述
count	计数
sum	求和
avg	求平均值
max	求最大值
min	求最小值

注意：

1. 分组函数可以自动忽略null，不需要提前对null进行处理。

2. 分组函数中，count(*)和coun(具体字段)有什么区别？

   **count(具体字段)**：统计的是该字段下不是null的记录的个数，以具体字段为基本单位进行统计。

   count(*)：统计的是整个表中所有的记录总数，即星号表示的是一条记录，以记录为基本单位进行统计。null也统计进去。

3. 分组函数不能直接使用在where子句中。

   比如，找出比最低薪资高的员工信息，比较直观的写法是：

   select ename, sal from emp where sal > min(sal);

   表面上没问题，但是这样是错误的，分组函数不能直接使用在where子句中。（具体原因见分组查询小节）

   因为分组函数必须是先分组再使用，此时在语法上来说，where在执行的时候，还没有分组，或者说 group by 没执行。

   而类似 select sum(sal) from emp; 这种语句，因为在语法上来说，select执行的时候，group by已经执行完了，就是说已经存在分组了，所以分组函数可以使用。

   可参考各个关键字的执行顺序。

4. 所有的分组函数可以组合起来一起使用。

   select sum(sal), min(sal), max(sal), avg(sal), count(8) from emp;

2.4.3 分组查询

在实际的应用中，可能有这样的需求：需要先进行分组，燃火对每一组的数据进行操作，比如一张表中的数据是某个年级的学生成绩，此时要统计每个班级的平均成绩等等，这个时候需要对数据进行分组。语法结构如下所示：

select 字段名s from 表名 group by 字段名;

即通过group by后面的字段对数据进行分组，对于分组之后的数据可以考虑用分组处理函数进一步处理。而且，必须要用分组处理函数进行处理，因为如果不用函数处理的话，分组后的数据没办法显示。

另外，如果不写 group by 而直接采用分组函数的话，其实默认是将整个表分组后作为一个组输入进去了。比如 select count(*) from t_student;

注意，在分组查询中，在一条select语句当中，如果有group by的话，select后面只能跟：参加分组的字段，以及分组函数，其他的一律不能跟，即使跟的话，也没有什么意义，或者报错。

注意，即使没有group by，实际上仍然是有的，只不过这是将整个数据表分为一组，仍然是在where之后默认执行。所以无论什么时候，分组函数只能应用在where之后，不能应用在where中。

另外，和多字段排序一样，也可以多字段分组。

select 字段名s from 表名 group by 字段1, 字段2;

这种是先根据字段1进行分组，然后在分组后的每个组中再根据字段2进行分组。

2.4.4 分组后过滤

我们知道，where条件是对分组前的数据进行处理的，那么如果想对分组后的数据进一步处理该怎么操作呢？可以采用having子句对分组后的数据进一步处理，就是说，having关键字不能单独使用，必须和group by 联合使用，不能代替where。语法结构如下：

select 字段名s from 表名 group by 字段名 having 条件;

将指定表中的数据按照字段名进行分组，对分组后的数据按照having条件进行筛选，最终显示出指定的字段名s相关的数据、

但是在某些题目的条件下，having效率可能比较低，所以能用where条件提前过过滤掉的尽量用where提前过滤掉。

简单例子如下所示：

要求显示各部门最高薪资大于3000的员工信息。

• 方法1：先对部门进行分组，然后求最大值，接着对最大值进一步筛选大于3000的。

  select deptno, max(sal) from emp group by deptno having max(sal) > 3000;

• 先对薪资进行过滤，将小于3000的全部去掉，然后对部分进行分组，取最大值，此时最大值一定大于3000，所以不必要再having判断。

  select deptno, max(sal) from emp where sal > 3000 group by deptno;

可以明显看出，方法1对小于3000的其实也进行分组了，最终却把他们过滤掉，这明显造成了效率低下。方法2直接先过滤掉没用的数据，再进行分组，效率比较高，所以能用where过滤的，就先用where提前过滤。

优化策略：where和 having，优先选择 where，where实在完成不了的，再选择having。

2.5 limit关键字

limit关键字是取查询结果集中的一部分，通常使用在分页查询中。比如百度默认一页显示10条记录，分页的作用是为了提高用户的体验，因为一次全部都显示出来用户体验差。语法结构如下所示：

select 字段名s from 表名 limit 起始下标, 长度;

将显示结果，从起始下标开始（省略，则默认从0开始），截取长度个数据记录，显示出来。

注意，mysql当中 limit 在 order by 之后执行！！！

2.6 关键字总结

简单查询的关键字有seelct、from，条件查询的关键字有where，排序的关键字有order by，分组查询的关键字有group by、having，限制显示结果的数量的关键字有limit。那么这些关键字可以可以一起使用呢？其执行顺序又是怎么样的呢？这些关键字是可以一起使用的，如下所示：

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

即先确定操作的表，然后根据where条件过滤一遍数据，之后按照group by进行分组，接着根据having条件对分组后的数据过滤，之后根据select字段选取对应的数据，然后按照order by对选取后的数据进行排序，之后对排序后的结果用limit限制输出的记录条数。

注意，先执行select，再执行order by，所以order by 后面跟的字段可以是select中的，包括别名。

2.7 去除重复记录

有时候对于同一个字段中可能存在重复的数据，那么在输出的时候可能会需要去重，这时候需要用到distinct关键字。简单例子如下所示：

select distinct ip from a;

对 ip 字段数据去重。

select distinct ip, dns from a;

对 ip, dns 两个字段去重。

select count(distinct ip) from a;

distinct可以和分组处理函数一起使用。统计不重复的 ip 个数。

2.7 连接查询

从一张表中查询数据称为单表查询。有时候我们需要的数据在两张表中分别存储，比如一张表保存了姓名和身份证号，另一张表保存了身份证号和地址，需求是查询姓名和地址信息，那么此时就需要联合两张表查询，即连接查询。语法结构如下所示：

select 字段名s from 表1, 表2, ... [where ...]

在表1和表2中查询（满足条件的）指定的字段名s。当两张表进行连接查询，而且没有任何条件的时候，最终查询结果条数是两张表条数的乘积，这种现象被称为：笛卡尔积现象。

换句话说，当两张表在连接的时候，首先从表A中选取一条记录，然后去匹配另一张表B的所有记录，因为这里没有条件限制，所以认为是满足条件的，即将二者组合起来。简单例子如下所示：

注意，连接查询在匹配条件的时候，就是按照上述表述逐条匹配的。尽管限制条件后匹配的结果条数变少了。但匹配次数没变。

也可以指明具体表的字段，对表起别名等等，简单例子如下所示：

select emp.ename, dept.name from emp, dept where emp.deptno = dept.deptno;

select e.ename, d.name from emp as e, dept as d where e.deptno = d.deptno;

起别名可以更方便操作。建议在操作的时候，在select后面的字段中，也要加上表的名字，起别名，提高效率。

注意，因为笛卡尔积现象的存在，所以表的连接越多，效率较越低，比如三张表，分别有10条记录，那么在连接查询的时候，匹配次数是10*10*10。所以，减少表的连接次数，也可以提升效率。

根据语法的年代分类，连接查询有以下两类：

• SQL92：1992年出现的语法；
• SQL99：1999年出现的语法（主要）。

根据表连接的方式分类，连接查询有以下三类：

• 内连接
  • 等值连接
  • 非等值连接
  • 自连接
• 外连接
  • 左外连接（左连接）
  • 右外连接（右连接）
  • 全连接

2.7.1 内连接

内连接是将完全匹配条件的数据查询出来。

2.7.1.1 等值连接

等值连接，即连接的条件是等量关系。

例子：查询每个员工所在部门名称，显示员工名和部门名？

和上面的类似，就是将 emp 表和 dept 表进行连接，条件是emp.depno = dept.deptno

# SQL 92 语法
select e.name, d.name from emp as e, dname as d where e.deptno = d.deptno;

# SQL 99 语法
select e.name, d.name from emp as e join dname as d on e.deptno = d.deptno;

注意，虽然 SQL99 语法看起来比 SQL92 语法更加复杂了，但是实际上是将 SQL92 语法中将 where 中的 连接条件和 过滤条件 分离了。

即上述的条件和 where 条件无关，此时如果想要对连接后的结果进一步过滤，可以添加 where 条件。而如果采用SQL92语法的话，两个条件就会混淆，所以建议采用SQL99语句。

• SQL92的缺点：结构不清晰，表的连接条件和后期进一步筛选的条件都放在了where中；

• SQL99的优点：表连接的条件是独立的，放在on中，连接之后，如果还需要进一步筛选，再往后添加where条件即可。

  select ... from a (inner) join b on a和b的连接条件 where 筛选条件;

  其中 inner表示内连接，可以省略，默认是内连接。表示将 a 和 b 按照连接条件连接，然后对结果按照筛选条件筛选，最后选择指定字段。

2.7.1.2 非等值连接

非等值连接指的是连接条件不是等量的。

例子：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？（注意薪资等级有一个薪资范围）

select e.name, e.sal, s.grade from emp as e join salgrade as s on e.sal between s.losal and s.hisal;

即，将 emp 和 salgrade 两张表连接起来，连接条件是emp表中的sal字段等级在 salgrade 表中的 losal 和 hisal 范围之间。

2.7.1.3 自连接

顾名思义，就是自己和自己连接，即将一张表看成两张表。

例子：查询员工的上级领导，要求显示员工名和对应的领导名？（注意，该表中，一条记录中有员工编号，员工姓名和员工的领导的编号，这里领导的编号其实就是其他员工的编号，所以需要自连接。）

select e.name as emplo, em.name as emploer from emp as e join emp as em on e.mgr = em.empno;

2.7.2 外连接

外连接是除了严格匹配条件之外还将一些没有匹配条件的数据也表示出来。比如A表和B表进行内连接，就是将A表和B表中按照连接条件进行匹配。但是此时，可能有需求是将 B表中没有匹配的记录也显示出来，如下图所示，dname中的所有记录都显示出来，但是最后一个OPERATIONS没有匹配A表中的记录，所以是NULL。语法如下：

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

注意，外连接和内连接在语法上的区别是，join前面有关键字，right表示将 join 右边的关键字看成主表，也就是最终显示结果中，右边表中的字段的值全部显示，捎带着关联查询左边的表。

同理，有了right，就有left，将join左边的关键字看成主表，称为左外连接，左连接。并且，任何一个左连接都有右连接的写法，任何一个右连接都有左连接的写法，即 from A right join B 和 from B left join A是等价的。

另外，有了左连接、右连接，还有全连接，就是说两张表都是主表。就是说两个表的内容都显示出来，另一张表没有对应内容，就用null表示。

注意，外连接 join 前面其实也是有一个 outer 关键字的，可以省略，因为有了right 以及 left。带着可读性强。

可以看出，外连接其实两张表示有了主次关系了，而内连接则是平等的。

2.7.4 连接总结

主要有内连接和外连接之分，内连接可以省略inner，外连接可以省略outer，但必须加上left、right。语法结构如下所示：

select
	...
from
	a
[right, left]join
	b
on
	a 和 b 的连接条件
[right, left]join
	c
on
	a 和 c 的连接条件
[right, left]join
	...
on
	...

其实就是跟多个 **join … on …**，其中每个 join 都可以单独设置内、（左、右）外连接，这些可以混合出现。

2.8 子查询

除了上述的简单查询和连接查询，有时候这些查询仍然不满足需求，此时可以进行子查询。即将查询结果作为新的一张表再次对结果进行查询，也就是select嵌套。

select语句中嵌套select语句，被嵌套的 select 语句称为子查询。其实就是将select子查询的结果作为关键字的输入。

select查询可以出现在以下位置：

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

2.8.1 where子句中的子查询

即先筛选一遍，然后将筛选结果作为where条件。

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

select name, sal from from emp where sal > (select min(sal) from emp);

即先要找出最低工资，然后再找出员工姓名和工资。

2.8.2 from子句中的子查询

即先查询一遍，然后将筛选结果作为再次查询的表。

例子：找出每个岗位的平均薪资的薪资等级？

select a.job, b.grade from (select job, avg(sal) as sal_avg from emp group by job) as a join salgrade as b on a.sal_avg between b.losal and b.hisal; 

即先筛选出工作以及平均薪资，然后和另一张等级表连接查询。

2.8.3 select子句中的子查询

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

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

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

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

2.9 union合并查询结果集

例子：查询工作岗位是MANAGEER和SALESMAN的员工？

select ename, job from emp where job = 'MANAGER' or job = 'SALESMAN';
select ename, job from emp where job in ('MANAGER', 'SALESMAN');

上面是两种最直观的方法。那么应用union关键字，还有一种比较“看起来复杂”的方法，就是执行两次简单查询，之后通过关键字 union 将结果合并。

select ename, job from emp where job = 'MANAGER'
union
select ename, job from emp where job = 'SALESMAN';

注意，其实union的效率要高一些。对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻。但是union可以减少匹配的次数，在减少匹配次数的情况下，还可以完成两个结果集的拼接。

比如， a 连接 b 连接 c：（采用正常的表连接操作）

• a：10条记录
• b：10条记录
• c：10条记录
• 那么最终的匹配次数就是1000次。

而如果采用合并查询union呢？

• a 和 b 连接：匹配次数是100次
• a 和 c 连接：匹配次数是100次
• 那么最终的匹配次数就是100 + 100次。

注意，union在进行结果集合并的时候，要求两个结果集的列数相同。要求合并时列和列的数据类型要一致（mysql允许不一致，Oracle要求一致。）

3. 备注

参考B站《动力节点》。