grafana mysql 条件查询

本文详细讲解了MySQL中DQL语言，也就是数据查询语句的使用。全文3w余字，是对学习MySQL知识的整理总结，因为篇幅较长，MySQL基础知识余下部分发表在余下博客中

• DML语言学习\插入数据\删除数据\更新数据
• DDL语言学习\数据库的建立\MySQL数据类型\MySQL常见约束\表的增删改

1. 数据库和SQL概述

1.1 DB

• 数据库（database）：存储数据的“仓库”
• 指长期储存在计算机内的、有组织的、可共享的数据集合

---

1.2 DBMS

• 数据库管理系统（Database Management System）
• 是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库
• 数据库是通过DBMS创建和操作的容器
• 用户通过DBMS访问数据库中的数据，数据库管理员也通过DBMS进行数据库的维护工作

常见的数据库管理系统：MySQL、oracle、DB2、SQL server

---

1.3 SQL

• 结构化查询语言（Structured Query Language）
• 是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统
• 专门用来和数据库通信的语言

SQL的优点

• 不是某个特定数据库供应商专有的语言，几乎所有DBMS都支持SQL
• SQL语言简单易学
• 虽然简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作

---

1.4 数据库的特点

• 将数据放入表中，表在放入库中
• 一个数据库可以有多个表，每一个表都有一个名字，用来标识自己。同时，表名具有唯一性
• 表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 " 类 "
• 表由列组成，我们也称为字段。所有的表都是由一个或多个列组成，每一列类似于java中的 " 属性 "
• 表中的数据是按行存储的，每一行类似于java中的 " 对象 "

---

1.5 SQL语言分类

结构化查询语言（SQL）包含6个部分：

• DML（Data Manipulation Language)：数据操纵语句。用于添加、删除、修改、查询数据库记录，并检查数据完整性。
• DDL（Data Definition Language)：数据定义语句。用于库和表的创建、修改、删除。
• DCL（Data Control Language)：数据控制语句。用于定义用户的访问权限和安全级别。
• DQL（Data Query Language)：数据查询语句。其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。
• TCL（Transaction Control Language）： 事务控制语句。它的语句能确保被DML语句影响的表的所有行及时得以更新。
• CCL：指针控制语句。用于对一个或多个表单独行的操作。

---

2. MySQL基本使用

2.1 MySQL服务的启动和停止

1、方式一：打开计算机管理中的服务

2、方式二：DOS命令启动（以管理员身份运行cmd）

• 启动 - net start MySQL
• 停止 - net stop MySQL

---

2.2 MySQL数据库的登录和退出

1、方式一：使用MySQL Command Line Client登录

输入密码后，既可以登录MySQL数据库

2、方式二：DOS窗口中操作

命令格式为mysql -h hostname -P port -u username -p

• -h 后面跟服务器主机地址，这里客户端和服务器在一台机器上，所以是 “localhost” 或 127.0.0.1
• -P 后面跟端口，默认3306
• -u 后面跟的是登录数据库的用户名称，这里为 “root”
• -p 后面是用户登录密码

这里输入DOS命令mysql -h localhost -P 3306 -u root -p； 本机操作时，直接输入mysql -u root -p 也可以

3、退出数据库

exit + 回车即可退出

---

2.2 MySQL常见命令介绍

• 查看当前所有的数据库 : SHOW DATABASES;
• 打开指定的库 ：USE 库名;
• 查看当前库的所有表：SHOW TABLES;
• 查看当前库的其他表：SHOW TABLES FROM 库名;
• 创建一个表

CREATE TABLE 表名（
	列名 列类型,
	列名 列类型,
	...	// 最后一列末尾不用加 " , "
);

• 查看表结构：DESC 表名;
• 查看服务器的版本

• 方式一：登录到mysql服务端select version
• 方式二：没有登录到mysql服务端mysql --version 或 mysql --V

---

2.3 MySQL语法规范

• 不区分大小写， 但是建议关键字大写， 表名、列名小写
• 每句话用 " ; " 或 " \g " 结尾
• 各子句一般分行写，建议关键字占一行
• 注释：

• 单行注释： #注释文字 或 -- 注释文字
• 多行注释：/* 注释文字 */

---

3. DQL语言学习

3.1 基础查询

语法：SELECT 查询列表 FROM 表名;

SELECT
		查询列表
FROM
		表名;

特点：

• 查询列表可以是： 表中的字段、常量值、表达式、函数
• SELECT * FROM 表名：查询表中所有记录
• 查询的结果是一个虚拟的表格

1、 查询表中的单个字段

SELECT last_name FROM employees;

2、查询表中的多个字段

SELECT last_name,salary,email FROM employees;

3、查询表中所有字段

SELECT * FROM 表名;

4、查询常量值

• 注意： 字符型和日期型的常量值必须用单引号引起来

SELECT 常量值;
SELECT 100;
SELECT 'john';

5、查询表达式

SELECT 表达式;
SELECT 100*98;
SELECT 100%98;

6、查询函数：得到其返回值并显示

SELECT 函数名(实参列表);
SELECT VERSION();

---

3.1.1 转义字符 - ``

• 这里需要注意，如果查询的列名或表名和 mysql 本身的关键字冲突，可以用转义字符``



• 
  

---

3.1.2 起别名 - AS 或 空格

• 提高可读性，便于理解
• 区分字段重名情况

# 方式一：使用AS
SELECT last_name AS 姓, first_name AS 名 FROM employees;
# 方式二：使用空格
SELECT last_name 姓, first_name 名 FROM employees;

---

3.1.3 去重 - DISTINCT

# 案例：查询员工表中涉及到的所有部门编号
SELECT DISTINCT 
		department_id 
FROM 
		employees;

---

3.1.4 +号 的作用

MySQL中+号只能作运算符，具体用法举例说明

• 两个操作数都为数值型，则做加法运算，如SELECT 100+90;
• 只要其中一个为字符型，试图即将字符型数值转换成数值型，转换成功则进行加法运算，如SELECT '123'+90;
• 如果转换失败，则将字符型数值转换为0 ，如'john'+90;
• 只要其中一方为null，结果肯定为null，如SELECT null+10;

3.1.5 补充：CONCAT函数

在MySQL中，若想进行字符拼接，不能用+号，这里可以用CONCAT()函数

SELECT
		CONCAT( last_name, first_name ) AS 姓名
FROM
		employees;

3.1.6 补充：IFNULL函数

功能：判断某字段或表达式是否为NULL。若为NULL，返回指定值。否则，返回本来的值

SELECT
		IFNULL(commission_pct, 0)
FROM
		employees;

---

3.2 条件查询

语法：

SELECT
		查询列表
FROM
		表名
WHERE
		筛选条件;

• 执行顺序： FROM => WHERE => SELECT
• 通过WHERE子句，可以对数据进行过滤

3.2.1 按条件表达式筛选，比较运算符

MySQL中的比较运算符有

运算符	作用
=	等于，不可以判断NULL值
<=>	安全的等于，可以用于判断NULL值
<> ( != )	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于

案例 1：查询工资大于12000的员工信息

SELECT
		*
FROM
		employees
WHERE
		salary>12000;

案例 2：查询部门编号不等于90号的员工名和部门编号

SELECT
		last_name,
		department_id
FROM
		employees
WHERE
		department_id<>90;

---

3.2.1 按逻辑表达式筛选，逻辑运算符

作用：连接条件表达式

运算符	作用
NOT（!）	逻辑非
AND（&&）	逻辑与
OR（|| ）	逻辑或
XOR	逻辑异或

案例 1：查询工资在10000到20000之间的员工名、工资以及奖金

SELECT
		last_name,
		salary,
		commission_pct
FROM
		employees
WHERE
		salary >= 10000
AND	
		salary <= 20000;

---

3.2.1 模糊查询，LIKE、BETWEEN AND、IN、IS NULL

运算符	作用
LIKE	通配符匹配
BETWEEN AND	判断一个值是否落在两个值之间
IN	判断一个值是否为IN列表中的值
IS NULL	判断是否为NULL
IS NOT NULL	判断是否不为NULL

1、 LIKE

LIKE在进行匹配时，可以使用以下两种通配符

• %：匹配任何数目的字符，甚至包括零字符
• _：只能匹配一个字符

案例 1：查询员工名中包含字符a的员工信息

SELECT
		*
FROM
		employees
WHERE
		first_name LIKE '%a%';

特殊地，如果匹配内容中含有字符 ’ _ '，可以使用 ’ \ ’ 转义，或者是ESCAPE转义

• ESCAPE 关键字的主要作用就是指定一个字符替代“\”的作用

SELECT
		last_name
FROM
		employees
WHERE
		last_name LIKE '_$_%' ESCAPE '$';

---

2、 BETWEEN AND

语法格式：expr BETWEEN min AND max

• 包含临界值，等价于expr >= min AND expr <= max

案例 1：查询员工编号在100到120之间的员工信息

SELECT
		*
FROM
		employees
WHERE
		employee_id BETWEEN 100 AND 120;

---

3、 IN

作用：判断某字段的值是否属于IN列表中的某一项

注意：

• IN列表中的值类型必须一致或兼容（‘123’，123）
• IN列表中不支持通配符

案例 1：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个 的员工名和工种编号

SELECT
		last_name,
		job_id
FROM
		employees
WHERE
		job_id IN ('IT_PROT','AD_VP','AD_PRES');

---

4、 IS NULL

• IS NULL，用于空值判断
• " = " 不能用于空值（NULL）的判断，但是 " <=> " 可以用于判断NULL值

案例 1：查询没有奖金的员工名和奖金率

SELECT
		last_name,
		commission_pct
FROM
		employees
WHERE
		commission_pct IS NULL;

案例 2：查询有奖金的员工名和奖金率

SELECT
		last_name,
		commission_pct
FROM
		employees
WHERE
		commission_pct IS NOT NULL;

---

3.3 排序查询

语法：

SELECT
		查询列表
FROM
		表名
WHERE
		筛选条件
ORDER BY 排序列表 ASC 或 DESC;

注：

• ASC是升序排序，ASC是作为默认的排序方式，所以可以省略
• DESC是降序排序
• 执行顺序： FROM => WHERE => SELECT => ORDER BY
• ORDER BY子句中可以支持单个字段、多个字段、表达式、函数、别名
• ORDER BY子句一般放在查询的最后面，LIMIT子句除外

案例 1：查询员工信息，要求从高到低排序

SELECT * FROM employees ORDER BY salary DESC;

案例 2：查询部门编号>=90的员工信息，按入职时间的先后进行排序（筛选排序）

SELECT
		*
FROM
		employees
WHERE
		department_id >= 90
ORDER BY hiredate;

案例 3：按年薪的高低显示员工的信息和年薪（按别名排序）

SELECT
		*,
		salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM
		employees
ORDER BY 年薪 DESC;

案例 4：按姓名的长度显示员工的姓名和工资（按函数排序）

SELECT
		LENGTH(last_name) 字节长度,
		last_name,
		salary
FROM
		employees
ORDER BY LENGTH(last_name) DESC;

案例 5：查询员工信息，要求先按工资升序，再按员工编号降序（按多个字段排序）

• 先按前面的要求排序，如果相同再按后面的要求排序。前面为主要关键字，后面为次要关键字

SELECT
		*
FROM
		employees
ORDER BY salary, employee_id DESC;

---

3.4 常见函数——单行函数

概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名

调用：SELECT 函数名(实参列表)

分类：

• 单行函数：对每一条记录输入值进行计算，得到相应的计算结果，返回给用户，也就是说，每条记录作为一个输入参数，经过函数计算得到每条记录的计算结果。如CONCAT、LENGTH、IFNULL等
• 分组函数：对多条记录输入值进行计算，得到多条记录对应的单个结果。做统计使用，又称为统计函数、聚合函数、组函数。

单行函数又可以分为以下几种：

• 字符函数
• 数学函数
• 日期函数
• 流程控制函数
• 其他函数

下面将依次介绍这几种函数的使用方法

---

3.4.1 字符函数

1. LENGTH： 获取参数值的字节个数

作用：获得参数值的字节个数

SELECT 
		LENGTH('john'),		# 4
		LENGTH("张三丰yyds");	# 13

这里Navicat客户端所用编码是UTF-8，字母占一个字节，汉字占三个字节

2. CONCAT：拼接字符串

语法：CONCAT(str1,str2,...)

SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;

3. UPPER、LOWER：大小写

案例：将姓大写，名小写，后拼接

SELECT 
		CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 
FROM 
		employees;

4. SUBSTR、SUBSTRING：截取字符

• 有四种函数重载形式

SELECT 
		SUBSTR('努力百日我要上哈佛大学',8) 学校,		# 哈佛大学
		SUBSTR('努力百日我要上哈佛大学',1,4) 方法;	# 努力百日

这里要注意，SQL语言中，字符的索引从1开始

5. INSTR：返回子串第一次出现的起始索引

语法：INSTR(str,substr)

SELECT INSTR('努力百日我要上哈佛大学','哈佛大学');	# 8

6. TRIM：首尾移除

TRIM ( [ [位置] [要移除的字串] FROM ] 字串)：

• [位置] 的可能值为 LEADING (起头)，TRAILING (结尾)， or BOTH (起头及结尾)
• 这个函数将把 [要移除的字串] 从字串的起头、结尾，或是起头及结尾移除
• 如果我们没有列出 [要移除的字串] 是什么的话，那空白就会被移除
• LTRIM(字串): 将所有字串起头的空白移除
• RTRIM(字串): 将所有字串结尾的空白移除

SELECT 
		TRIM('    张无忌    '),			#张无忌
		TRIM('a' FROM 'aaaa张无忌aaaa');	#张无忌

7. LPAD、RPAD: 用指定字符 左\右填充 指定长度的字符

语法：LPAD(str,len,padstr)

• LPAD(要查询的字段，长度，用来填充的字段)
• LPAD是在左边填充，RPAD是在右边填充

SELECT
		LPAD('金毛狮王谢逊',10,'-');  #----金毛狮王谢逊
		RPAD('金毛狮王谢逊',10,'-');  #金毛狮王谢逊----

8. REPLACE：替换

语法：REPLACA(str,from_str,to_str)

SELECT REPLACE('勇敢牛牛，不怕困难','牛牛','狗狗');	#勇敢狗狗，不怕困难

---

3.4.2 数学函数

1. ROUND：四舍五入

语法：ROUND(X)，ROUND(X,D)

• ROUND(X)：对X四舍五入，如果是负数则取绝对值四舍五入再加负号
• ROUND(X,D)：对X四舍五入，保留D位小数

SELECT
		ROUND(-1.55),	# -2
		ROUND(1.678, 2) # 1.68

2. CEIL：向上取整、FLOOR：向下取整

语法：CEIL(X)、FLOOR(X)

SELECT 
		CEIL(1.002),	# 2
		FLOOR(1.002);	# 1

---

3. TRUNCATE：截断

语法：TRUNCATE(X,D)

• X是数值，D是保留小数的位数
• 按照小数位数，进行数值截取（此处的截取是按保留位数直接进行截取，没有四舍五入）

SELECT TRUNCATE(1.699999,1)	 # 1.6

---

4. MOD：取余

语法：MOD(N,M)

• 返回N除以M余值，N - N / M * M

SELECT 
		MOD(-10,-3),		# -1
		MOD(10,-3);			# 1

---

3.4.3 日期函数

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值

类型名称	日期格式
YEAR	YYYY 或 YY
DATE	YYYY-MM-DD
DATETIME	YYYY-MM-DD HH:MM:SS
TIMESTAMP	YYYY-MM-DD HH:MM:SS

这里注意两位的年份（YY）.

• 若以数字表示，范围（1 - 99）：1 - 69 和 70 - 99 范围的值分别转换为 2001 - 2069，1970 - 1999。超过范围的值被转为0000
• 若以字符表示，范围（‘00’ - ‘99’）：‘00’ 表示 2000，超过范围的值被转为2000

MySQL 中最重要的内建日期函数如下表

函数	作用
NOW()	返回当前的日期和时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	用于返回日期/时间的单独部分，比如年、月、日、小时、分钟
DATE_ADD()	给日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

1. NOW：返回当前系统日期 + 时间

SELECT NOW();		# 2021-07-05 09:16:52

2. CURDATE：返回当前日期

SELECT CURDATE();		# 2021-07-05

3. CURTIME：返回当前时间

SELECT CURTIME();		# 09:19:15

4. 获得时间指定部分

可以获得指定的部分，如年、月、日、小时、分钟、秒

• 对应YEAR、MONTH、MONTHNAME(返回英文)、HOUR、MINUTE、SECOND

对于2021-07-05 09:25:55这一时间
SELECT YEAR(NOW());			# 2021
SELECT MONTH(NOW());		# 7
SELECT MONTHNAME(NOW());	# July
SELECT HOUR(NOW());			# 9
SELECT MINUTE(NOW());		# 25
SELECT SECOND(NOW());		# 55

5. str_to_data：日期格式的字符转为指定格式日期

• 在web页面中，用户输入的日期格式不一定相同，所以可以用此方法将用户输入的字符串转换为指定的格式

SELECT STR_TO_DATE('5-7-2021','%m-%d-%Y');		# 2021-05-07

具体的日期格式符如下

序号	格式符	作用
1	%Y	四位的年份
2	%y	两位的年份
3	%m	月份(01 - 12)
4	%c	月份(1 - 12)
5	%d	日(01, 02, …)
6	%H	小时(24小时制)
7	%h	小时(12小时制)
8	%i	分钟(00 - 59)
9	%s	秒(00 - 59)
10	%U	星期(0 - 52)，星期天为第一天
11	%u	星期(0 - 52)，星期一为第一天
12	%W	星期英文(Sunday - Saturday)
13	%M	月份英文(January - December)

6. DATE_FORMAT：将日期转成字符

语法：DATE_FORMAT(date,format)

• date 参数是合法的日期
• format 规定日期/时间的输出格式

SELECT DATE_FORMAT(NOW(),"%y年%m月%d日"); 	# 21年07月05日

---

3.4.4 流程控制函数

1、 IF函数
语法：IF(condition, value_if_true, value_if_false)

• condition：判断条件
• value_if_true：条件正确输出
• value_if_false：条件错误输出

SELECT IF(10<5,'大','小');  # 小

2、CASE函数

语法一（简单函数）：CASE [col_name] WHEN [value1] THEN [result1] . . . ELSE [default] END

• 相当于java中switch case
• 枚举这个字段所有可能的值

语法二（搜索函数）：CASE WHEN [expr] THEN [result1]…ELSE [default] END

• 相当于java中多重if，实现区间判断
• 搜索函数可以写判断，并且搜索函数只会返回第一个符合条件的值

案例1：查询员工的工资，要求：

• 部门号为30，工资为原工资的1.1倍
• 部门号为40，工资为原工资的1.2倍
• 部门号为50，工资为原工资的1.3倍
• 其他部门，保持原工资

SELECT
		salary 原始工资,
		department_id,
		CASE department_id 
				WHEN 30 THEN 
						salary * 1.1 
				WHEN 40 THEN 
						salary * 1.2 
				WHEN 50 THEN 
						salary * 1.3 
				ELSE salary 
		END AS 新工资
FROM 
			employees;

案例2：查询员工的工资，要求：

• 工资>20000，显示A级别
• 15000<工资<20000，显示B级别
• 10000<工资<15000，显示C级别
• 否则，显示D级别

SELECT 
		salary 薪资,
		CASE 
				WHEN salary>20000 THEN 
						'A'
				WHEN salary>15000 THEN
						'B'
				WHEN salary>10000 THEN
						'C'
				ELSE
						'D'
		END AS 级别
FROM
		employees;

---

3.4.5 其他函数

简单介绍前期学习过程中遇到的其他几种函数

函数	作用
VERSION()	获得当前MySQL数据库版本信息
DATABASE()	查询当前的数据库
USER()	查询当前用户
MD5(‘字符’)	返回该字符的md5加密形式

---

3.5 常见函数——分组函数

用作统计使用，又称聚合函数、统计函数、组函数

• 这些函数的名称和作用如下表

函数	作用
AVG()	返回某列平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值的和

简单使用

SELECT
		SUM(salary)	和,
		ROUND(AVG(salary),2) 平均,
		MAX(salary)	最高,
		MIN(salary)	最低,
		COUNT(salary) 个数
FROM
		employees;

注意事项

1、COUNT函数，使用方法有两种

• COUNT(*)：计算表中总的行数，无论列中有数值或者为空值
• COUNT(字段名)：计算指定列下总的行数，计算时将忽略空值的行

2、MAX函数和MIN函数，不仅适用于查找数值类型，也可应用于字符类型、日期型

• 字符型按字典序，‘Z’ > ‘A’；日期型，2021 > 1990

3、SUM函数和AVG函数在计算时，忽略列值为NULL的行

4、和分组函数一起查询的字段要求是GROUP BY后的字段

---

3.6 分组查询

分组数据：GROUP BY 子句语法

• 可以使用GROUP BY子句将表中的数据分成若干组

语法：

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

3.6.1 简单的使用

案例 1：查询每个工种的最高工资

SELECT job_id, MAX(salary)
FROM employees
GROUP BY job_id;

案例 2：查询每个位置上的部门个数

SELECT COUNT(*), location_id
FROM departments
GROUP BY location_id;

---

3.6.2 分组前的筛选 —— WHERE

案例 1：查询邮箱中包含a字符的，每个部门的平均工资

SELECT AVG(salary), department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id

---

3.6.3 分组后的筛选 —— HAVING

以上是添加分组前的筛选，若想实现添加分组后的筛选，可以利用HAVING关键字

• 分组前筛选的数据源 —— 原始表
• 分组后筛选的数据源 —— 分组后的结果集
• MySQL中，GROUP BY和HAVING子句中支持别名

HAVING

语法：

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

• 在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用
• HAVING 子句可以让我们筛选分组后的各组数据

案例 1：查询哪个部门的员工个数大于2

SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

案例 2：查询每个工种有奖金的员工和最高工资

SELECT job_id, salary
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000;

---

3.6.4 按多个字段分组

• 多个字段间逗号隔开，没有顺序要求

案例 ：查询每个部门每个工种员工的平均工资

SELECT AVG(salary), department_id, job_id
FROM employees
GROUP BY department_id, job_id;

3.6.5 添加排序

• 排序放在整个分组查询的最后

案例 ：查询每个部门每个工种员工的平均工资，按工资从高到低排序

SELECT AVG(salary), department_id, job_id
FROM employees
GROUP BY job_id, department_id
ORDER BY salary DESC;

---

3.7 连接查询

含义：又称多表查询，当查询的字段来自多个表时，就会用到连接查询

3.7.1 笛卡尔乘积现象

若使用如SELECT NAME, boyName FROM boys, beauty;从beauty表和boys表查询，会出现笛卡尔集的错误情况

• 即： 表1有m行，表2有n行，查询结果为m * n行

若想避免以上现象，需要添加有效的连接条件

SELECT NAME, boyName 
FROM boys, beauty
WHERE beauty.boyfriend_id = boys.id;

---

3.7.2 连接查询的分类

1、按年代分类

• sql-92标准： 仅仅支持内连接
• sql-99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

2、按功能分类

• 内连接：等值连接、非等值连接、自连接
• 外连接：左外连接、右外连接、全外连接
• 交叉连接

---

3.7.3 sql92——内连接

下面介绍sql92标准下的内连接，依次为等值连接、非等值连接、自连接

1、等值连接

案例 1：查询女神名和对应的男神名

SELECT NAME, boyName 
FROM boys, beauty
WHERE beauty.boyfriend_id = boys.id;

案例 2：查询员工名和对应的部门名

SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

起别名

对案例2，为了减少复杂量，区分重名字段，可以为表起别名

SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

不过需要注意，如果已经起了别名，则查询字段的限定不能再使用原表名，因为执行顺序为FROM=>SELECT。

因此，如下代码会产生错误

添加筛选

案例 1：查询有奖金的员工名、部门名

SELECT last_name, department_name, commission_pct
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;

案例 2：查询城市名中第二个字符为‘o’的部门名和城市名

SELECT department_name, city
FROM departments d, locations l
WHERE d.location_id = l.location_id
AND city LIKE '_o%';

添加分组

案例 1：查询每个城市的部门个数

SELECT COUNT(*) 个数, city
FROM departments d, locations l
WHERE d.location_id = l.location_id
GROUP BY city;

案例 2：查询有奖金的每个部门的部门名和部门的领导编号以及该部门的最低工资

SELECT department_name, d.manager_id, MIN(salary)
FROM departments d, employees e
WHERE d.department_id = e.department_id
AND commission_pct IS NOT NULL
GROUP BY department_name, d.manager_id;

添加排序

案例 1： 查询每个工种的工种名和员工的个数，并按员工个数降序

SELECT job_title, COUNT(*)
FROM employees e, jobs j
WHERE e.job_id = j.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;

三表连接，利用多个AND添加筛选条件

案例 1： 查询员工名、部门名和所在城市

SELECT last_name, department_name, city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;

总结

• 多表等值连接的结果为多表的交集部分
• n个表连接，至少需要n-1个连接条件
• 多表的顺序没有要求
• 一般需要为表起别名
• 可以搭配前面介绍的所有子句使用， 比如排序、分组、筛选

---

2、非等值连接

利用BETWEEN AND

案例：查询员工的工资和工资级别

SELECT salary, grade_level
FROM employees e, job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;

---

3、自连接

• 自连接查询其实等同于连接查询，需要两张表，只不过它的左表（父表）和右表（子表）都是自己。
• 做自连接查询的时候，是自己和自己连接，分别给父表和子表取两个不同的别名，然后附上连接条件

案例： 查询员工名及其上级的名称

• 这里员工和经理的信息都在同一张表中

SELECT e.employee_id 员工编号, e.last_name 员工名, m.employee_id 经理编号, m.last_name 经理名 
FROM employees e, employees m
WHERE e.manager_id = m.employee_id;

---

sql99标准

语法
SELECT 查询列表
FROM 表1 别名
【连接类型】JOIN 表2 别名
ON 连接条件
【WHERE 筛选条件】
【GROUP BY 分组】
【HAVING 筛选条件】
【ORDER BY 排序列表】

分类及关键字
1、内连接：INNER
2、外连接：左外：LEFT 【OUTER】； 右外：RIGHT【OUTER】；全外：FULL【OUTER】
3、交叉连接：CROSS
注意：MySQL不支持全外连接

用图解释四种连接

---

3.7.4 sql99——内连接

1、等值连接

案例 1：查询员工名、部门名

SELECT last_name, department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

案例 2：查询名字中包含e的员工名和工种名（添加筛选）

SELECT last_name, job_title
FROM employees e
INNER JOIN jobs j
ON e.job_id = j.job_id
WHERE e.last_name LIKE '%e%';

案例 3： 查询部门个数>3的城市名和部门个数（添加分组+筛选）

SELECT city, COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.location_id = l.location_id
GROUP BY city
HAVING COUNT(*) > 3;

案例 4：查询人数大于3的部门名和员工个数，按降序（添加排序）

SELECT department_name 部门名, COUNT(*) 员工个数
FROM departments d
INNER JOIN employees e
ON d.department_id = e.department_id
GROUP BY department_name
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC;

案例 5： 查询员工名、部门名、工种名，并按部门名降序（多表连接）

• 只要能找到，那么连接顺序就无所谓

SELECT last_name 员工名, department_name 部门名, job_title 工种名
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id
ORDER BY department_name DESC;

特点：

• 可以添加排序、分组、筛选
• INNER可以省略
• 筛选条件放在WHERE后面，连接条件放在ON后面，提高分离性，便于阅读
• INNER JOIN连接和sql——92语法中的等值连接效果一样，都是查询多表的交集

2、非等值连接

案例 1： 查询员工的工资级别

• 利用 BETWEEN AND

SELECT salary, grade_level
FROM employees e
JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
ORDER BY grade_level

3、自连接

案例 1：查询员工名及其领导的名字

SELECT e.last_name, m.last_name
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id;

---

3.7.5 sql99——外连接

应用场景：用于查询一个表中没有另一个表中有的记录

特点：

• 外连接的查询结果为主表的所有记录，和从表中匹配的值，如果从表中没有匹配，则显示null
• 外连接查询结果 = 内连接结果 + 主表中有而从表中没有的记录
• 左外连接：left join 左边的是主表； 右外连接：right join 右边的是主表
• 左外和右外交换两个表的顺序，可以实现相同的效果

1、左（右）外连接

案例 1：查询美女名（beauty表）及其对应的伴侣名（boys表）

SELECT b.name, bo.boyName
FROM beauty b		# beauty是主表, boys是从表
LEFT OUTER JOIN boys bo
ON b.boyfriend_id = bo.id

案例 2：查询帅哥名（boys表）及其对应的伴侣名（beauty表）

• 左外

SELECT b.name, bo.boyName
FROM boys bo	# boys是主表, beauty是从表
LEFT OUTER JOIN beauty b
ON b.boyfriend_id = bo.id

• 等同于右外

SELECT b.name, bo.boyName
FROM beauty b		# beauty是主表, boys是从表
RIGHT OUTER JOIN boys bo
ON b.boyfriend_id = bo.id

2、全外连接

• 左表和右表都不做限制，所有的记录都显示，两表不足的地方用null填充
• 注意：MySQL 不支持全外连接，但如Oracle是支持全外连接的，而且用到的还比较多，因此在此简单介绍一下

3、交叉连接

• 使用sql99语法标准实现笛卡尔乘积
• 和前文sql92语法中笛卡尔乘积现象一样，前者使用 逗号， 这里使用CROSS JOIN
• 交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合

SELECT b.`name`, bo.boyName
FROM beauty b	
CROSS JOIN boys bo;

---

3.8 子查询

3.8.1 相关概念

1、出现在其他语句内部的select语句，称为子查询或内查询。外部的查询语句，称为主查询或外查询
2、子查询可以添加到SELECT、UPDATE、DELETE语句中，而且可以进行多层嵌套
3、子查询中常用的操作符有ANY（SOME）、ALL、IN、EXISTS
4、在SELECT子句中先计算子查询，子查询结果作为另一个查询的过滤条件，查询可以基于一个表或者多个表
5、子查询也可以使用比较运算符，如"<" “<=” “>” “>=” 和 “!=” 等

按子查询出现的位置分类

• SELECT后面：子查询的结果必须为单行单列（标量子查询）
• FROM后面：子查询的结果可以是多行多列
• WHERE或HAVING后面：要求查询结果必须为单列，单行子查询、多行子查询
• EXISTS后面：子查询结果必须为单列（相关子查询）

---

3.8.2 WHERE或HAVING后面

特点

• 子查询放在小括号内
• 子查询放在条件中，要求必须放在条件的右侧
• 单行子查询，搭配单行操作符使用：> < <= >= = <>
• 多行子查询，搭配多行操作符使用：in、any/some、all
• 子查询的执行优先于主查询执行

1、单行子查询

案例 1：查询谁的工资比 Abel 高

SELECT *
FROM employees
WHERE salary > (
		SELECT salary
		FROM employees
		WHERE last_name = 'Abel'
)

案例 2：查询员工信息，满足其 job_id 与141号员工相同，salary 比143号员工多

SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (
		SELECT job_id
		FROM employees
		WHERE employee_id = 141
)
AND salary > (
		SELECT salary
		FROM employees
		WHERE employee_id = 143
)

案例 3：返回公司工资最少的员工的 last_name， job_id 和 salary

SELECT last_name, job_id, salary
FROM employees
WHERE salary = (
		SELECT MIN(salary)
		FROM employees
)

案例 4：查询指定部门的部门编号和最低工资，满足其最低工资大于50号部门的最低工资

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
		SELECT MIN(salary)
		FROM employees
		WHERE department_id = 50
)

注意：

• 这里 “>”、“<”（单行操作符）后面只能跟单行子查询，也就是说查询结果只有单行单列的查询，否则会出错

2、多行子查询

• IN：判断某字段是否在指定列表内

• x in(10,20,30)

• ANY/SOME：判断某字段的值是否满足其中任意一个

• x > ANY(10,20,30) 等价于 x > MIN(10,20,30)
• x = ANY(10,20,30) 等价于 x IN(10,20,30)

• ALL：判断某字段的值是否满足其中所有条件

• x >ALL(10,20,30) 等价于 x > MAX(10,20,30)

案例 1： 返回location_id是1400或1700的部门中的所有员工姓名

SELECT last_name
FROM employees
WHERE department_id IN (
		SELECT department_id
		FROM departments
		WHERE location_id IN(1400,1700)
)

• 这里子查询的结果是多行，因此限定条件时不能用 = 而是用 IN， 即使是子查询的结果是单行，用 IN 也是没问题的

---

3.8.3 放在其他位置的子查询

1、放在SELECT后面

案例 ： 查询部门编号是50的员工个数

SELECT 
(
		SELECT COUNT(*)
		FROM employees
		WHERE department_id = 50
) 个数；

2、放在FROM后面（查询）

案例 ：查询每个部门平均工资的工资级别（和job_grades表连接查询）

SELECT dep_ag.department_id, dep_ag.ag, g.grade_level
FROM job_grades g
JOIN (
		SELECT department_id, AVG(salary) ag
		FROM employees
		GROUP BY department_id
) dep_ag ON dep_ag.ag BETWEEN g.lowest_sal AND g.highest_sal;

3、放在EXISTS后面

EXISTS：此关键字后面的参数是一个任意的子查询，系统对子查询运算判断是否返回行，如果至少返回一行，EXISTS结果为TRUE，否则为FALSE

案例 ：查询有无名字为 “张三丰” 的员工

• 对比以下两种方式

SELECT *
FROM employees
WHERE last_name = '张三丰'

SELECT EXISTS (
		SELECT *
		FROM employees
		WHERE last_name = '张三丰'
) 是否存在;

---

3.9 分页查询

1、应用场景：当页面上的数据，一页显示不全，需要分页显示时

2、一般步骤：分页查询的sql命令请求数据库服务器 => 服务器响应查询到的多条数据 => 前台页面

3、语法：

SELECT 查询列表
FROM 表1 别名
JOIN 表2 别名
ON 连接条件
WHERE 筛选条件
GROUP BY 分组
HAVING 分组后筛选
ORDER BY 排序列表
LIMIT 起始条目索引，显示的条目数

• LIMIT， 放在整个查询语句最后
• 执行顺序：FROM => JOIN => ON => WHERE => GROUP BY => HAVING => SELECT => ORDER BY => LIMIT
• 起始条目索引默认从 0 显示

4、公式：假如要显示的页数是page， 每页显示的条目数是size

• LIMIT (page-1)*size, size;

5、案例

案例1： 查询员工信息表的前五条

SELECT * FROM employees LIMIT 5;

案例 2： 查询员工信息表，从第二条开始，显示五条

SELECT * FROM employees LIMIT 2, 5;

---

3.10 联合查询

1、概述

• 当查询结果来自于多张表，但多张表之间没有关联，这个时候往往使用联合查询，也称为UNION查询。
• 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果合成单个结果集。
• 合并时，两个表对应的列数和数据类型要相同

2、语法

SELECT 查询列表 FROM 表1
UNION [ALL]
SELECT 查询列表 FROM 表2

• 各个SELECT语句之间使用UNION或UNION ALL关键字分隔
• UNION不使用关键字ALL，执行的时候删除重复的记录，所有返回的行都是唯一的

3、案例

SELECT 'MySQL', 666 
UNION
SELECT 'MySQL', 666
UNION
SELECT 'MySQL', 666
UNION
SELECT 'MySQL', 666

SELECT 'MySQL', 666 
UNION	ALL
SELECT 'MySQL', 666
UNION	ALL
SELECT 'MySQL', 666
UNION 	ALL
SELECT 'MySQL', 666