（二）DQL数据查询语言：查询

一、简单的select查询

• 语法：

 SELECT 要查询的东西 【FROM 表名】;

类似于Java中：System.out.println(要打印的东西);

• 特点：
  ① 通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
  ② 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数

  查询前注意先打开库 use 库名

如：
1、查询表中的某个字段：
(可以双击名称选择，像stata那样,navicat可以拖过去）

select last_name from employees;

2、查询表中的多个字段：

 select last_name,salary,email from employees;

3、查询表中的所有字段：

select * from employees;

4、查询常量值
如：

select 100;

5、查询字符/字符串/日期型 （不区分，全用单引号）

select ‘john’;

6、查询表达式

select 100*98;

7、查询函数
如：

select version();

8、起别名
①便于理解
②如果要查询的字段有重名的情况，使用别名可以区分开来

• 方式一：使用as

select 100*98;
  
select 100%98 as 结果；
            
select last_name as 姓，first_name as 名 from employees;

• 方式二：使用空格
  即：

select last_name 姓，first_name 名 from employees;

特殊情况：当起的别名中含有特殊符号（空格、#、out等），建议加上“”，单引号也可以

9、去重

案例：查询员工表中涉及到的所有部门的编号

去重：

select distinct department_id from employees;

不可以同时查两个字段 select distinct a,b from …

10、+号的作用 vs. concat()

拼接字符用concat， concat里面必须是字符型
案例：查询员工名和姓连接成一个字段，并显示为 姓名

 select concat(‘a’,‘b’,‘c’) as 结果;
 select concat(last_name，first_name) as 姓名 from employees;

• MySQL中的+号只有一个功能：运算符

• 两个操作数都为字符型，则做加法运算

  select 100+90
  

• 只要其中一方为字符型，试图将字符型转换为数值型

  select ‘123’+90
  

  • 如果转换成功，则继续做加法运算
  • 如果转换失败，则将字符型数值转换为0
    • select ‘jhon’+90; 返回0+90
    • select null+90; 只要其中一方为null，则结果肯定为null

练习题

• 显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT
  因为奖金率commision_pct可能为null，所以需要先判断

  • 函数：ifnull() 判断是否为空,如果为空，则返回设定的值，否则返回原本的值

    • ifnull(commission_pct,0)

  • 函数：isnull() 判断是否为null，如果是则返回1，否则返回0

    • isnull( commission_pct )

select  ifnull(commission_pct,0) as 奖金率, commission_pct
from employees;

select concat ( first_name，‘,’，last_name，‘,’，job_id，‘,’，ifnull(commission_pct,0) ) as out_put
from employees;

二、条件查询

语法：

SELECT 
    要查询的字段|表达式|常量值|函数
FROM 
    表名
WHERE 
    筛选条件;

分类：

（一）按条件表达式筛选

示例：salary > 10000

条件运算符：>, <, >=, <=, =, !=, <>

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

SELECT * FROM employees 
WHERE salary > 12000;

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

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

（二）按逻辑表达式筛选

示例：salary > 10000 && salary < 20000

逻辑运算符：AND (&&), OR (||), NOT (!)

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

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

案例：查询部门编号不在 90 到 110 之间，或工资高于 15000 的员工信息

SELECT * FROM employees 
WHERE department_id < 90 OR department_id > 110 OR salary > 15000;

（三）模糊查询

示例：last_name LIKE 'a%'
like, between and, in, is null, is not null

• LIKE

  • 判断字符型，一般和通配符搭配使用
    • 通配符：
      • % 代表任意多个字符，包含 0 个字符
      • _ 代表任意单个字符
  • 也可以判断数值型，如 WHERE id LIKE '1__'

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

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

案例：查询员工表的 job_id 中含有 ‘a’ 和 ‘e’，且 ‘a’ 在 ‘e’ 前面的员工信息

SELECT job_id FROM employees 
WHERE job_id LIKE '%a%e%';

案例：查询员工名中第三个字符为 ‘e’，第五个字符为 ‘a’ 的员工名和工资

SELECT last_name, salary FROM employees 
WHERE last_name LIKE '__e_a%';

案例：查询员工名中第二个字符为 ‘_’ 的员工名

SELECT last_name FROM employees 
WHERE last_name LIKE '_\_%';

用\转译
或任意定义一个转译符号$，

SELECT last_name FROM employees 
WHERE last_name LIKE ’_$_%’ escape ‘$’;;

• BETWEEN AND / not between

  • 提高语句简洁性
  • 包含临界值
  • 临界值不可颠倒顺序

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

SELECT * FROM employees 
WHERE job_id BETWEEN 100 AND 120;

• IN

  • 判断某字段的值是否属于 IN 列表中的某一项
  • 使用 IN 提高语句简洁度
  • IN 列表的值类型必须一致或兼容
  • 不支持通配符

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

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

• IS NULL / IS NOT NULL

  • 用于判断是否为 NULL
  • = 或 <> 不能用于判断 NULL 值

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

SELECT last_name, commission_pct FROM employees 
WHERE commission IS NULL;

• 安全等于 <=>

  • 可以判断 NULL 值
  • 还可以判断普通数值

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

SELECT last_name, commission_pct FROM employees 
WHERE commission <=> NULL;

三、排序查询

基本语法：

SELECT
    要查询的东西
FROM
    表
WHERE
    筛选条件
ORDER BY
    排序的字段|表达式|函数|别名 [ASC|DESC];

特点：

• ASC 代表升序，DESC 代表降序，如果不写，默认升序。
• 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 ASC;
   

3. 按表达式排序，按年薪的高低显示员工的信息和年薪：

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

4. 按别名排序，按年薪的高低显示员工的信息和年薪：

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

5. 按函数排序，按姓名长度显示员工的姓名和工资：

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

6. 多个字段排序，查询员工信息，要求先按工资升序排序，再按员工编号降序：

   SELECT *
   FROM employees
   ORDER BY salary ASC, employee_id DESC;
   

测试：

1. 查询员工的姓名、部门号、年薪，按年薪降序，按姓名升序：

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

2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序：

   SELECT last_name, salary
   FROM employees
   WHERE salary NOT BETWEEN 8000 AND 17000
   ORDER BY salary DESC;
   

3. 查询邮箱中包含 ‘e’ 的员工信息，并先按邮箱的字节数降序，再按部门号升序：

   SELECT *
   FROM employees
   WHERE email LIKE '%e%'
   ORDER BY LENGTH(email) DESC, department_id ASC;
   

按汉字的首字母排序

要按汉字的首字母进行排序，您可以使用MySQL的CONVERT函数将汉字转换为拼音，然后进行排序。以下是一个示例：

假设您有一个名为employees的表，其中包含一个名为last_name的汉字姓名列，您可以按照姓氏的首字母进行排序，可以使用以下查询：

SELECT last_name
FROM employees
ORDER BY CONVERT(last_name USING gbk);

在这个查询中，CONVERT函数将last_name列中的汉字按照GBK编码转换成拼音，然后按照拼音进行排序。请注意，这里使用的是GBK编码，您可以根据您的需求选择其他编码，例如UTF-8。

四、常见函数

SQL 函数调用的一般语法如下：

SELECT
    函数名(参数列表)
FROM
    表

特点：

• 叫什么（函数名）
• 干什么（函数功能）

分类：

• 单行函数（如concat、length、ifnull）
• 分组函数，又称统计函数、聚合函数、组函数（功能：做统计使用）

（一）单行函数

1. 字符函数

1. length
   获取字节个数，一个汉字占3个字节

select length('张三丰hahaha');  ------ 15

字符长度：char_length()

select char_length('张三丰');  ---- 3

2. concat 拼接字符串

select concat(last_name, '_', first_name) 
from employees;

3. upper 转换成大写

select upper('jhon');

4. lower 转换成小写

select lower('JHON');

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

select concat(upper(last_name), '_', lower(first_name)) as 姓名 from employees;
-- 函数中可以套用函数

函数中可以套用函数

5. substr 或 substring 截取子串

注意：索引从1开始

• 如果只写1个数字，指截取从指定索引处后面所有字符

select substr('李莫愁爱上了陆展元', 6) as out_put; 
-- 了陆展元

• 如果写2个数字，指截取从指定索引出指定字符长度的字符

select substr('李莫愁爱上了陆展元', 1, 3) as out_put;     -- 李莫愁

**案例：**姓名中首字符大写，其他字符小写然后用_拼接，显示出来

select concat(upper(substr(last_name, 1, 1)), '_', lower(substr(last_name, 2))) as out_put
from employees;

6. instr 返回子串第一次出现的索引

select instr('杨不悔爱上了殷六侠', '殷六侠') as out_put from employees;
   -- 7
select instr('杨不悔殷六侠爱上了殷六侠', '殷六侠') as out_put from employees;
 -- 3
select instr('杨不悔爱上了殷六侠', '殷八侠') as out_put from employees;
   -- 0

7. trim 去前后指定的空格和字符

select length(trim('    张翠山     '))  as out_put;
     -- 9
select trim('a' from 'aaaaa张aaaa翠山aaaaa') as out_put;   -- 张aaaa翠山
-- 去掉前后的a

• ltrim 去左边空格
• rtrim 去右边空格

8. lpad 用指定字符实现左填充指定长度

select LPAD('殷素素', 10, '*') as out_put;
     -- *******殷素素
-- 最后总字符长度为10，不够在左边填充*
select LPAD('殷素素', 2, '*') as out_put;  
   -- 殷素
-- 超出就截断

9. rpad 右填充

select rpad('殷素素', 12, 'ab') as out_put;
   -- 殷素素ababababa

10. replace 替换

select replace('张无忌爱上了周芷若', '周芷若', '赵敏') as out_put;
 -- 张无忌爱上了赵敏

2. 数学函数

decimal：定点型，四舍五入
float：浮点型，五舍六入

sqrt(), exp(4) = e^4, PI() = π

1. round 四舍五入 （先四舍五入，再加负号）

select round(1.65);  -- 2
select round(-1.65);  -- -2
select round(1.567, 2);  -- 1.57 小数点后保留两位

2. ceil 向上取整，返回≥该参数的最小整数

select ceil(-1.02);  -- -1

3. floor 向下取整，返回≤该参数的最大整数

4. truncate 截断

select truncate(1.6999, 1);  
-- 1.6   后面的数字指小数点后保留几位

5. mod 取余/取模

select mod(10, 3);     -- 1
select 10 % 3;   
select mod(-10, -3);   -- -1
-- 看被除数，被除数是正的，结果就是正的，反之就是负的
-- mod(a, b)  a - a / b * b    a / b 是取整数

6. rand 随机数

RAND() 函数是用于生成随机数的函数，返回一个介于0（包含）和1（不包含）之间的随机浮点数。

你可以在 SQL 查询中使用 RAND() 函数来生成随机数。以下是一些示例用法：

1. 生成一个随机浮点数：

SELECT RAND() AS random_number;

这将返回一个0到1之间的随机浮点数。

2. 生成一个介于最小值（min）和最大值（max）之间的随机整数：

SELECT FLOOR(RAND() * (max - min + 1) + min) AS random_integer;

这将生成一个介于 min 和 max 之间的随机整数，包括 min 和 max。

3. 生成一个介于最小值（min）和最大值（max）之间的随机浮点数：

SELECT RAND() * (max - min) + min AS random_float;

这将生成一个介于 min 和 max 之间的随机浮点数，包括 min 但不包括 max。

需要注意的是，RAND() 函数在每次查询时都会生成不同的随机数，但在同一查询中多次调用 RAND() 会得到相同的随机数。如果需要不同的随机数，可以在查询中多次调用 RAND()，或者使用不同的种子值初始化 RAND()。

3. 日期函数

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

select now();

2. curdate 只返回当前系统日期，不包含时间

select curdate();

3. curtime 只返回当前系统时间，不包含日期

select curtime();

4. 可以获取指定的部分，年、月、日、小时、分钟、秒

select year（now()）as 年;  -> 2020
select year(‘1998-1-1’) as 年;
select year(hiredate) as 年 from employees;

• 类似的：month()、hour()、second()
date() 返回的是年、月、日

• 如果想要返回月的英文名

  select monthname(now()) as 月;
  

5. str_to_date 将日期格式的字符转换成指定格式的日期

select str_to_date(‘9-13-1999’，‘%m-%d-%Y’);   -> 1999-09-13

注意都加引号，后面那个是对str格式的解析

序号	格式符	功能
1	%Y	四位的年份
2	%y	2位的年份
3	%m	月份（01,02…11,12）
4	%c	月份（1,2,…11,12）
5	%d	日（01,02,…）
6	%H	小时（24小时制）
7	%h	小时（12小时制）
8	%i	分钟（00,01…59）
9	%s	秒（00,01,…59）

6. date_format将日期转换成字符

 select date_format(‘2018/6/6’，‘%Y年%m月%d日’); 
  ---> 2018年06月06日
 select date_format('2018/6/6', '%Y-%m-%d')
------ 2018-06-06

 select str_to_date (‘1998-3-2’，’%Y-%c-%d’) as out_put;    
 -----1998-03-02
 select date_format(now(),’%y年%月%d日’) as out_put;    
 ------ 21年1月4日

SELECT DATE_FORMAT('2023-10-05', '%Y-%m') 
--- 2023-10

7. interval

"2015-01-03"+interval'1' day ---2015-01-04

7. DATE_ADD() / adddate()

感觉这俩函数除了名字不一样，其余参数和功能全一样
DATE_ADD() 是用于在日期时间值上添加一定数量的时间间隔的 MySQL 函数。它的语法如下：

DATE_ADD(date, INTERVAL expr unit)

• date 是一个日期时间值，你想要添加时间间隔的日期时间。
• expr 是要添加的时间数量。
• unit 是时间单位，可以是年 (YEAR)、月 (MONTH)、天 (DAY)、小时 (HOUR)、分钟 (MINUTE)、秒 (SECOND) 等。

以下是一些示例：

1. 增加一个月的时间：

SELECT DATE_ADD('2023-10-04', INTERVAL 1 MONTH);
------ 2023-11-04

2. 减少一周的时间：

SELECT DATE_ADD('2023-10-04', INTERVAL -1 WEEK);
------ 2023-09-27

3. 增加两小时：

SELECT DATE_ADD('2023-10-04 10:30:00', INTERVAL 2 HOUR);

8. DATE_SUB()

DATE_SUB() 函数用于从给定的日期时间值中减去一定数量的时间间隔。其语法如下：

DATE_SUB(date, INTERVAL expr unit)

• date 是一个日期时间值，你想要从中减去时间间隔的日期时间。
• expr 是要减去的时间数量。
• unit 是时间单位，可以是年 (YEAR)、月 (MONTH)、天 (DAY)、小时 (HOUR)、分钟 (MINUTE)、秒 (SECOND) 等。

以下是一些示例：

1. 减去一个月的时间：

SELECT DATE_SUB('2023-10-04', INTERVAL 1 MONTH);

2. 增加一周的时间：

SELECT DATE_SUB('2023-10-04', INTERVAL -1 WEEK);

3. 减去两小时：

SELECT DATE_SUB('2023-10-04 10:30:00', INTERVAL 2 HOUR);

9. datediff() 计算日期差

DATEDIFF() 是一个用于计算两个日期之间的差异的函数。它通常用于计算两个日期之间的天数差异，但也可以用于计算其他日期部分的差异，如月份或年份。

函数语法如下：

DATEDIFF(end_date, start_date)

其中：

• end_date 是结束日期。
• start_date 是开始日期。

函数返回一个整数，表示从 start_date 到 end_date 之间的差异。如果 end_date 在 start_date 之前，函数返回一个负数。

下面是一些示例：

1. 计算两个日期之间的天数差异：

SELECT DATEDIFF('2023-10-15', '2023-10-10') AS 天数差异;
-- 结果是 5

2. 计算两个日期之间的月份差异：

SELECT PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM '2023-10-01'), EXTRACT(YEAR_MONTH FROM '2023-05-01')) AS 月份差异;
-- 结果是 5

3. 计算两个日期之间的年份差异：

SELECT YEAR('2023-10-01') - YEAR('2018-05-01') AS 年份差异;
-- 结果是 5

10. timestampdiff

TIMESTAMPDIFF() 函数用于计算两个日期时间之间的差值，并返回一个整数，表示两个日期时间之间的差距。下面是各个参数的详细解释和一个示例：

• unit: 这是一个字符串，用于指定要计算的时间单位，可以是以下之一：SECOND（秒）、MINUTE（分钟）、HOUR（小时）、DAY（天）、WEEK（周）、MONTH（月）或 YEAR（年）。

• datetime_expr1: 这是第一个日期时间表达式，通常是较小的日期时间。

• datetime_expr2: 这是第二个日期时间表达式，通常是较大的日期时间。

下面是一个示例，展示如何使用 TIMESTAMPDIFF()：

假设有一个名为 orders 的表，其中包含订单创建时间和订单交付时间：

+---------------------+---------------------+
| order_created_time | order_delivered_time |
+---------------------+---------------------+
| 2023-10-01 08:00:00 | 2023-10-01 10:30:00 |
| 2023-10-02 15:45:00 | 2023-10-03 11:20:00 |
| 2023-10-04 09:30:00 | 2023-10-06 14:15:00 |
+---------------------+---------------------+

要计算每个订单的交付时间与创建时间之间的小时差，可以使用以下查询：

SELECT
  TIMESTAMPDIFF(HOUR, order_created_time, order_delivered_time) AS hours_difference
FROM
  orders;

这将返回以下结果：

+-----------------+
| hours_difference |
+-----------------+
|               2 |
|              19 |
|              52 |
+-----------------+

在这个示例中，TIMESTAMPDIFF() 函数计算了每个订单的创建时间与交付时间之间的小时差，并将结果作为 hours_difference 列返回。

10. period_diff()

PERIOD_DIFF() 是一个MySQL特定的日期函数，用于计算两个年月值之间的差异，通常用于计算两个日期之间的月份差异。该函数接受两个年月值作为参数，并返回它们之间的月份差异。

函数语法如下：

PERIOD_DIFF(period1, period2)

其中：

• period1 是第一个年月值，通常表示为一个整数，如 YYYYMM。
• period2 是第二个年月值，也是一个整数。

函数返回一个整数，表示从 period2 到 period1 之间的月份差异。如果 period1 在 period2 之前，函数返回一个正整数；如果 period1 在 period2 之后，函数返回一个负整数。

下面是一个示例：

SELECT PERIOD_DIFF(202310, 202305) AS 月份差异;
-- 结果是 5

在上面的示例中，PERIOD_DIFF() 函数计算了从 202305 到 202310 之间的月份差异，结果是5个月。

请注意，PERIOD_DIFF() 函数是MySQL的扩展函数，不一定适用于其他数据库系统。在不同的数据库系统中，可能需要使用不同的函数或方法来计算日期或年月值之间的差异。

11. extract() 提取日期时间信息

EXTRACT() 是一个用于提取日期时间信息的 SQL 函数，通常用于从日期时间值中获取特定部分的信息，如年、月、日、小时、分钟等。这个函数在不同的数据库管理系统中可能会有一些差异，但通常都提供了类似的功能。

函数的基本语法如下：

EXTRACT(field FROM source)

其中：

• field 是要提取的日期时间部分，可以是以下值之一：

  • YEAR：提取年份
  • MONTH：提取月份
  • DAY：提取日期（天）
  • HOUR：提取小时
  • MINUTE：提取分钟
  • SECOND：提取秒
  • 等等，具体取决于数据库系统的支持。

• source 是要提取信息的日期时间值，可以是一个日期时间列、表达式或文字值。

下面是一些示例用法：

-- 提取年份
SELECT EXTRACT(YEAR FROM '2023-10-04') AS 年份;

-- 提取月份
SELECT EXTRACT(MONTH FROM '2023-10-04') AS 月份;


SELECT EXTRACT(YEAR_MONTH FROM '2023-10-01');
-- 结果是 202310

-- 提取小时
SELECT EXTRACT(HOUR FROM '2023-10-04 15:30:00') AS 小时;

-- 提取分钟
SELECT EXTRACT(MINUTE FROM '2023-10-04 15:30:00') AS 分钟;

这些示例演示了如何使用 EXTRACT() 函数从日期时间值中提取特定部分的信息。请注意，具体的语法和可用的日期时间部分可能会根据您使用的数据库管理系统而有所不同。因此，最好查阅您所使用的数据库的文档以获取准确的信息。

练习：

案例1：查询入职日期为1992年4月3日的员工信息

SELECT *
FROM employees
WHERE hiredate = '1992-04-03';

案例2：查询入职日期为1992年4月3日的员工信息（另一种写法）

SELECT *
FROM employees
WHERE hiredate = STR_TO_DATE('4-3 1992', '%c-%d %Y');

案例3：查询有奖金的员工名和入职日期，日期格式为月/日 年

SELECT last_name, DATE_FORMAT(hiredate, '%m月/%c日 %y年') AS 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;

4. 流程控制函数

1. if函数，实现 if else的效果

处理双分支

select if(10 > 5, '大', '小');

案例： 有没有奖金

select last_name, commission_pct, if(commission_pct is null, '没奖金', '有奖金');

sum(if(action='confirmed',1,0)) 

sum(CASE WHEN action = 'confirmed' THEN 1 ELSE 0 END) 

2. case语句 处理多分支

1. 情况1：处理等值判断

使用一：Java中 switch case的效果，等值判断

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1; -- 如果是常量不需要加分号
when 常量1 then 要显示的值1或语句1;
-- ...
else 要显示的值n或语句n;  -- 可以不写
end

案例： 查询员工的工资，要求部门号=30，显示的工资为1.1倍，部门号=40，显示的工资为1.2倍，部门号=50，显示的工资为1.3倍，其他部门，显示的工资为原工资

SELECT `salary` AS 原始工资, `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. 情况2：处理条件判断

使用二：类似于多重if，判断的是区间，不是等值

case -- 后面不加东西
when 条件1 then 要显示的值1 或语句1;
when 条件2 then 要显示的值2 或语句2;
-- ...
else 要显示的值n 或语句n  -- 可以不写
end

案例： 查询员工的工资，如果工资>20000，显示A级别，如果工资>15000，显示B级别，如果工资>10000，显示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;

5. 其他函数

• version版本

select version();

• database当前库

select database(); -- 查看当前的库

• user当前连接用户

select user();

• password（'字符’） 返回该字符的加密形式

password('字符');

• MD5（'字符’） 也是该字符的MD5加密形式

MD5('字符');

测试：

1. 显示系统时间（日期+时间）

select now();

2. 查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）

select employee_id, last_name, salary, salary * 1.2 as "new salary"  -- 加双引号因为有空格
from employees;

3. 将员工的姓名按首字母排序，并写出姓名的长度（length）

SELECT LENGTH(last_name) 长度, SUBSTR(last_name, 1, 1) 首字符, last_name
FROM employees
ORDER BY 首字符;

4. 做一个查询，产生下面的结果
   <last_name> earns monthly but wants <salary*3>

Dream Salary
King earns 24000 monthly but wants 72000

SELECT CONCAT(last_name, ' earns', salary, ' monthly but wants ', salary * 3) AS "Dream salary"
FROM employees
WHERE salary = 24000;

5. 使用case-when，按照下面的条件：

   job	grade
   AD_PRES	A
   ST_MAN	B
   IT_PROG	C
   SA_REP	D
   ST_CLERK	E

   产生下面的结果：

Last_name	Job_id	Grade
king	AD_PRES	A

SELECT last_name, job_id AS job,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
END AS Grade
FROM employees
WHERE job_id = 'AD_PRES';

（二）分组函数：聚合函数不能出现在where子句中

分组函数用于统计、聚合数据，常见的分组函数包括：

• SUM：求和
• MAX：最大值
• MIN：最小值
• AVG：平均值
• COUNT：计数，统计不为null的个数

补充：std()标准差，var_pop()方差

1. 简单使用

SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

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

SELECT SUM(salary) AS 和, ROUND(AVG(salary), 2) AS 平均, MIN(salary) AS 最低, MAX(salary) AS 最高, COUNT(salary) AS 个数
FROM employees;  -- avg保留2位小数

2. 特点：

• 以上五个分组函数都会忽略null值，除了COUNT(*)，count(‘列名’)只会统计非空行数

• SUM 和 AVG 一般用于处理数值型数据，而 MAX、MIN、COUNT 可以处理任何数据类型。

• 分组函数可以搭配 DISTINCT 使用，用于统计去重后的结果。

  SELECT SUM(DISTINCT salary), SUM(salary) 
  FROM employees;
  

• COUNT 函数的参数可以是字段、*、常量值（一般放1），但建议使用 COUNT(*)。

• 与分组函数一同查询的字段有限制，这些字段应该是 GROUP BY 后的字段。

测试：

1. 查询公司员工工资的最大值、最小值、平均值和总和。

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

2. 查询员工表中的最大入职时间和最小入职时间的相差天数（DIFFERENCE）。

-- 新函数：datediff
-- datediff('2020-10-1', '2020-9-29') → 2
SELECT DATEDIFF(NOW(), '1997-12-20');

SELECT DATEDIFF(MAX(hiredate), MIN(hiredate)) AS DIFFERENCE
FROM employees;

3. 查询部门编号为90的员工个数。

SELECT COUNT(*) AS 部门90员工个数 
FROM employees WHERE department_id = 90;

五、分组查询

基本语法：

SELECT 查询的字段，分组函数（要求出现在GROUP BY后面）
FROM 表
WHERE 筛选条件 -- 明确：WHERE一定放在FROM后面
GROUP BY 分组的字段 asc|desc
having 。。。
-- ORDER BY 子句

注意：查询列表必须特殊，要求是分组函数和GROUP BY后出现的字段。

（一）简单的分组查询

• 查询每个工种的最高工资

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

• 查询每个位置上的部门个数

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

实现单列value_counts的效果

在 MySQL 中，你可以使用 GROUP BY 和 COUNT 来实现类似于 Pandas 的 value_counts 函数的效果，以计算某一列中每个不同值出现的次数。以下是一个示例：

假设你有一个名为 your_table 的表，其中有一个名为 your_column 的列，你想要计算每个不同值在这列中出现的次数，你可以这样做：

SELECT your_column, COUNT(*) AS count
FROM your_table
GROUP BY your_column
ORDER BY count DESC;

这个查询将返回两列：your_column 包含不同的值，count 包含每个值出现的次数，按出现次数降序排列。

这个查询使用了 GROUP BY 子句，它会根据 your_column 列的不同值将行分组，然后使用 COUNT(*) 函数计算每个分组中的行数，即每个值出现的次数。最后，使用 ORDER BY 子句将结果按出现次数降序排列。

（二）添加分组前的筛选条件

• 查询邮箱中包含字符’a’的每个部门的平均工资

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

• 查询有奖金的每个领导手下员工的最高工资

SELECT MAX(salary), manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

（三）添加分组后的筛选条件

• 查询哪个部门的员工个数大于2

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

• 查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资

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

• 查询领导编号大于102的每个领导手下的最低工资大于5000的领导编号和其最低工资

SELECT MIN(salary), manager_id
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000;

where中不能使用聚合函数，HAVING MIN(salary) > 5000放到where中会报错

• 特点：

1. 可以按单个字段分组。
2. 与分组函数一同查询的字段最好是分组后的字段。
3. 分组筛选：

	针对的表	位置	关键字
分组前筛选	原始表	GROUP BY的前面	WHERE
分组后筛选	分组后的结果集	GROUP BY的后面	HAVING

• 分组函数做条件一定是放在HAVING子句中。
• 能用分组前筛选的情况下，优先考虑使用分组前筛选。

4. 可以按表达式或函数分组。
   案例： 按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数＞5的有哪些

   SELECT COUNT(*),LENGTH(last_name)
   FROM employees
   GROUP BY LENGTH(last_name)
   HAVING COUNT(*)>5;
   

5. HAVING后可以支持别名。

   SELECT COUNT(*) AS c,LENGTH(last_name) len_name
   FROM employees
   GROUP BY len_name
   HAVING c>5;
   

6. 可以按多个字段分组，字段之间用逗号隔开，没有顺序要求。
   案例： 查询每个部门每个工种的员工的平均工资

   SELECT AVG(salary),department_id,job_id
   FROM employees
   GROUP BY department_id,job_id;    
   --（group by后的顺序可以颠倒）
   

7. 可以支持排序。
   案例： 查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示

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

   接上： 部门编号不为空，且平均工资大于10000的组

   SELECT AVG(salary),department_id,job_id
   FROM employees
   WHERE department_id IS NOT NULL
   GROUP BY department_id,job_id
   HAVING AVG(salary)>10000
   ORDER BY AVG(salary)DESC;
   

测试：

1. 查询各job_id的员工工资的最大值、最小值、平均值、总和，并按job_id升序。

SELECT MAX(salary) AS 最高, MIN(salary) AS 最低, ROUND(AVG(salary), 2) AS 平均, SUM(salary) AS 和
FROM employees
GROUP BY job_id
ORDER BY job_id ASC;

2. 查询员工最高工资和最低工资的差距（DIFFERENCE）。

SELECT MAX(salary) - MIN(salary) AS DIFFERENCE
FROM employees;

3. 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内。

SELECT MIN(salary), manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000;

4. 查询所有部门的编号、员工数量和工资平均值，并按平均工资降序。

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

5. 选择具有各个job_id的员工人数。

SELECT COUNT(*), job_id
FROM employees
GROUP BY job_id;

with rollup

在SQL中，GROUP BY 子句用于将结果集按照一个或多个列的值进行分组，并且通常返回每个分组的聚合结果（例如，总计、平均值等）。WITH ROLLUP 是一种扩展 GROUP BY 的选项，它用于在结果集中生成附加的聚合行，这些行包含了更高级别的总计信息。

WITH ROLLUP 在 GROUP BY 子句之后使用，并且会为每个列在分组中生成一个额外的聚合行，显示了该列的总计。这些总计行按照原始 GROUP BY 子句中指定的列的层次结构进行排列。每一行都表示了当前分组级别上的聚合结果，以及更高级别的总计。这对于生成汇总报表或查看不同层次的数据总计非常有用。

以下是一个示例，说明了 WITH ROLLUP 的作用：

假设有一个名为 sales 的表，包含以下数据：

+-------+----------+-------+
| Month | Category | Sales |
+-------+----------+-------+
| Jan   | A        | 100   |
| Feb   | A        | 150   |
| Jan   | B        | 200   |
| Feb   | B        | 250   |
+-------+----------+-------+

如果执行以下查询：

SELECT Month, Category, SUM(Sales) FROM sales GROUP BY Month, Category WITH ROLLUP;

则结果将包括每个月份和类别的聚合结果，以及每个月份的总计和总体总计。结果如下：

+-------+----------+-------+
| Month | Category | Sales |
+-------+----------+-------+
| Jan   | A        | 100   |
| Jan   | B        | 200   |
| Jan   | NULL     | 300   | (Jan 总计)
| Feb   | A        | 150   |
| Feb   | B        | 250   |
| Feb   | NULL     | 400   | (Feb 总计)
| NULL  | NULL     | 700   | (总计)
+-------+----------+-------+

在结果中，WITH ROLLUP 创建了总计行，显示了每个月份的总计、每个类别的总计以及总体总计。这对于汇总和分析数据非常有用，特别是在生成报表时。

如果您想使用AVG（平均值）聚合函数并结合WITH ROLLUP，您可以类似地进行操作。以下是一个示例，说明如何在AVG聚合函数中使用WITH ROLLUP：

假设有一个名为 sales 的表，包含以下数据：

+-------+----------+-------+
| Month | Category | Sales |
+-------+----------+-------+
| Jan   | A        | 100   |
| Feb   | A        | 150   |
| Jan   | B        | 200   |
| Feb   | B        | 250   |
+-------+----------+-------+

如果您想计算每个月份和类别的销售额平均值，并包括总计和总体总计，可以执行以下查询：

SELECT Month, Category, AVG(Sales) AS AverageSales
FROM sales
GROUP BY Month, Category WITH ROLLUP;

结果将包括每个月份和类别的平均销售额，以及每个月份和每个类别的总计和总体总计。结果如下：

+-------+----------+--------------+
| Month | Category | AverageSales |
+-------+----------+--------------+
| Jan   | A        | 100.00       |
| Jan   | B        | 200.00       |
| Jan   | NULL     | 150.00       | (Jan 总计avg)
| Feb   | A        | 150.00       |
| Feb   | B        | 250.00       |
| Feb   | NULL     | 200.00       | (Feb 总计avg)
| NULL  | NULL     | 175.00       | (总计avg)
+-------+----------+--------------+

在结果中，WITH ROLLUP 生成了总计行，显示了每个月份和类别的平均销售额，以及每个月份的总计、每个类别的总计和总体总计。

六、多表连接查询

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

• 笛卡尔乘积现象

  • 表1有m行，表2有n行，结果=m * n行。
  • 笛卡尔乘积：如果连接条件省略或无效则会出现。
  • 解决办法：添加上连接条件。

• 分类：

  • 按年代分类

    • SQL92标准：仅支持内连接。
    • SQL99标准【推荐】：支持内连接+外连接（但不支持全外）+交叉连接。

  • 按功能分类

    • 内连接（INNER）：等值连接、非等值连接、自连接。
    • 外连接：左外连接（LEFT OUTER）、右外连接（RIGHT OUTER）、全外连接（FULL OUTER）交叉连接（CROSS）

(一) 传统模式下的连接：等值连接（SQL92标准）

• 1、等值连接

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

SELECT NAME, boyName 
FROM beauty, boys
WHERE beauty.boyfriend_id = boys.id; 
-- 加表名来说明是谁的id

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

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

• 2、为表起别名

  • 提高语句简洁度。
  • 区分多个重名的字段。

注意： 如果为表起了别名，则查询的字段就不能使用原来的表名去限定。

案例： 查询员工名、工种号、工种名

SELECT last_name, `employees`.job_id, `job_title`
FROM `employees`, `jobs`
WHERE `employees`.`job_id` = `jobs`.`job_id`;

• 起别名：

SELECT last_name, e.job_id, `job_title`
FROM `employees` AS e, `jobs` j
WHERE e.`job_id` = j.`job_id`;

• 3、FROM中两个表的顺序可以调换。

• 4、可以加筛选

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

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

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

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

• 5、可以加分组

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

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

• 6、可以加排序

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

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

• 7、可以实现三表连接

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

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`
group by last_name

总结：

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

（二）sql92：非等值连接

**案例:**查询员工的工资和工资级别

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

测试：

• 显示所有员工的姓名，部门号和部门名称。

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

• 查询90号部门员工的job_id和90号部门的location_id。

SELECT e.job_id, d.`location_id`
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`
AND e.`department_id` = 90;

• 选择所有有奖金的员工的 last_name , department_name , location_id , city。

SELECT e.last_name, d.`department_name`, d.`location_id`, l.`city`
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND e.`commission_pct` IS NOT NULL;

• 选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name。

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

• 查询每个工种、每个部门的部门名、工种名和最低工资。

SELECT department_name, job_title, MIN(salary)
FROM departments d, jobs j, employees e
WHERE e.`department_id` = d.`department_id`
AND e.`job_id` = j.`job_id`
GROUP BY job_title, department_name;

• 查询每个国家下的部门个数大于2的国家编号。

SELECT country_id, COUNT(*)
FROM locations l, departments d
WHERE d.`location_id` = l.`location_id`
GROUP BY country_id
HAVING COUNT(*) > 2;

• 选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

employees	Emp#	manager	Mgr#
kochhar	101	king	100

SELECT e.last_name "employees",e.`employee_id` "Emp#",m.`last_name` manager,m.`employee_id` "Mgr#"
FROM employees e,employees m
WHERE e.`manager_id` = m.`employee_id`
AND e.`last_name`='kochhar';

(三）sql92 自连接：：涉及到的表只有它自己

案例： 查询员工名和直接上级的名称

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

SELECT e.last_name,m.last_name
FROM employees e,employees m 
WHERE e.`manager_id`=m.`employee_id`;

（四）sql99语法：通过join关键字实现连接

• 含义：1999年推出的sql语法
• 支持：
  • 等值连接、非等值连接 （内连接：inner）
  • 外连接：左外连接 left 【outer】，右外连接 right 【outer】
  • 交叉连接：cross

- 语法：

select 字段，...
from 表1 别名 【连接类型】
【inner|left outer|right outer|cross】join 表2   on  连接条件
【inner|left outer|right outer|cross】join 表3   on  连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】

好处：语句上，连接条件和筛选条件实现了分离，简洁明了！

内连接

1. 等值连接

• 查询员工名、部门名

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

• 筛选和等值连接：查询名字中包含e的员工名和工种名

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

• 分组和筛选与内连接：查询部门个数＞3的城市名和部门个数

SELECT city, COUNT(*) 部门个数
FROM locations l
INNER JOIN departments d
ON l.`location_id` = d.`location_id`
GROUP BY city
HAVING COUNT(*) > 3;
-- count(*)不能放where里

• 三表连接：查询每个工种、每个部门的部门名、工种名和最低工资

SELECT department_name, job_title, MIN(salary)
FROM departments d
INNER JOIN jobs j
ON e.`department_id` = d.`department_id`
AND e.`job_id` = j.`job_id`
GROUP BY job_title, department_name;

2. 非等值连接

• 非等值连接 - 查询员工的工资级别

SELECT salary, grade_level
FROM employees e
INNER JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

• 分组和筛选与内连接 - 查询每个工资级别的员工个数＞20个的级别

SELECT COUNT(*), grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*) > 20;

3. 自连接

• 查询员工名和直接上级的名称

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

外连接

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

• 特点：

  • 外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的（即连接条件成立），则显示匹配的值
  • 外连接的查询结果=内连接结果+主表中有而从表中没有的记录
    • 左外连接，left join左边的是主表
    • 右外连接，right join右边的是主表
    • 左外和右外交换两个表的顺序，可以实现同样的效果
  • 全外连接=内连接的结果+表1中有但表2中没有的+表2中有但表1中国没有的

• 查询哪个部门没有员工：左外连接

SELECT d.*, e.`employee_id`
FROM employees e
left JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

• 查询哪个部门没有员工：右外连接

SELECT department_name, last_name
FROM employees e
RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

全外连接（MySQL不支持）

USE girls;
SELECT b.*, bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id = bo.id;

交叉连接

• 实现笛卡尔乘积效果

SELECT * FROM `beauty`;
SELECT * FROM `boys`;

SELECT b.*, bo.*
FROM beauty b
CROSS JOIN boys bo;

sql92 V.S. sql99

• 功能：sql99支持的较多
• 可读性：sql99实现；连接条件和筛选条件的分离，可读性较高
  
  
  
  
  
  
  
  测试：
  一、查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充

SELECT b.`id`,b.`name`,bo.* 
FROM beauty b
LEFT JOIN boys bo
ON bo.`id`=b.`boyfriend_id`
WHERE b.`id`>3

二、查询哪个城市没有部门

SELECT city,d.`location_id`
FROM locations l
LEFT JOIN departments d
ON l.`location_id`=d.`location_id`
WHERE d.`location_id` IS NULL;

三、查询部门名为SAL或IT的员工信息

SELECT d.`department_name`,e.*
FROM employees e
RIGHT JOIN departments d    注意主表为部门表
ON d.`department_id`=e.`department_id`
WHERE d.`department_name` IN('SAL','IT');

连接查询LeetCode刷题

• 要求选出后一行大于前一行的数据

七、子查询/内查询

• 含义：

  • 出现在其他语句中的select语句，称为子查询或内查询
  • 一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询
  • 在外面的查询语句，称为主查询或外查询
  • 外面的语句可以是insert、update、select等，一般select作为外面语句较多

• 例如：

  SELECT first_name FROM employees 
  WHERE department_id IN(
     SELECT department_id FROM departments
     WHERE location_id=1700
  );
  

• 分类：

  • 按子查询出现的位置：
    • select 后面：仅仅支持标量子查询
    • from 后面：支持表子查询
    • where或having 后面：标量子查询（单行）、列子查询（多行）、行子查询
    • exsits后面（相关子查询）：放谁都行
  • 按功能不同/结果集的行列数不同：
    • 标量子查询/单行子查询（结果集只有一行一列）
    • 列子查询/多行子查询（结果集至于一列多行）
    • 行子查询（结果集有一/多行多列）
    • 表子查询（结果集一般为多行多列）

（一）where或having后面

1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多列多行）

• 特点：

1. 子查询放在小括号内。
2. 子查询一般放在条件的右侧。
3. 标量子查询，一般搭配着单行操作符使用：>、<、<=、>=、<>。
4. 列子查询，一般搭配着多行操作符使用：IN、ANY/SOME、ALL。
5. 子查询的执行优先 于主查询执行，主查询的条件用到了子查询的结果。

1. 标量子查询（单行子查询）

• 示例 1：谁的工资比 Abel 高？

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

• 示例 2：返回 job_id 与 141 号员工相同，salary 比 143 号员工多的员工姓名、job_id 和工资

  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 号部门的最低工资的部门 id 和其最低工资

  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 / NOT IN	等于列表中的任意一个
ANY / SOME	子查询返回的某一个值比较
ALL	和子查询返回的所有值比较

• 示例 5：返回 location_id 是 1400 或 1700 的部门中的所有员工姓名

  SELECT last_name
  FROM employees e
  LEFT JOIN departments d
  ON e.department_id = d.department_id
  WHERE d.location_id IN (1400, 1700);
  

  • 用子查询

  SELECT last_name
  FROM employees
  WHERE department_id IN(  
  --除了用in，也可以用=any
      SELECT DISTINCT department_id
      FROM departments
      WHERE location_id IN (1400,1700)
  );
  

Not in  -->  <>all

• 示例 6：返回其他工种中比 job_id 为 ‘IT_PROG’ 工种 任一 工资低的员工的：工号、姓名、job_id 以及 salary

  SELECT employee_id, last_name, job_id, salary
  FROM employees
  WHERE salary < ANY(
  	SELECT DISTINCT salary
  	FROM employees
  	WHERE job_id = 'IT_PROG'
  ) AND job_id <> 'IT_PROG';
  

• 示例 7：返回其他工种中比 job_id 为 ‘IT_PROG’ 工种 所有 工资低的员工的：工号、姓名、job_id 以及 salary

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG';

3. 行子查询（结果集一行多列或多行多列）

• 示例 8：查询员工编号最小且工资最高的员工信息
  凑巧了，不具有普适性

  SELECT *
  FROM employees
  WHERE employee_id = (
      SELECT MIN(employee_id)
      FROM employees
  ) AND salary = (
      SELECT MAX(salary)
      FROM employees
  );
  

  或者可以简写为：

  SELECT *
  FROM employees
  WHERE (employee_id, salary) = (
      SELECT MIN(employee_id), MAX(salary)
      FROM employees
  );
  

  WHERE (employee_id, salary) = (
  SELECT MIN(employee_id), MAX(salary)

（二）select后面的子查询：仅支持标量子查询

• 【案例】查询每个部门的员工个数

  • 分组：但没考虑到有的部门没有员工的情况
    应该用全外连接

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

  • 用子查询

  SELECT d.*,(
  	SELECT COUNT(*)
  	FROM employees e
  	WHERE d.`department_id`=e.department_id
  ) 个数
  FROM departments d;
  

• 【案例】查询员工号=102的部门名

  SELECT (
  	SELECT department_name
  	FROM departments d
  	INNER JOIN employees e
  	ON e.department_id=d.department_id
  	WHERE employee_id=102
  ) 部门名;
  

（三）from后面的子查询：将子查询结果充当一张表，要求必须起别名

• 【案例】查询每个部门的平均工资的工资等级

  SELECT avg_salary.*,g.`grade_level`
  FROM (
  	SELECT AVG(salary) a,department_id
  	FROM employees e
  	GROUP BY department_id
  ) avg_salary
  INNER JOIN job_grades g
  ON avg_salary.a BETWEEN g.`lowest_sal` AND g.`highest_sal`;
  

（四）exsits后面（相关子查询）：能用exists的一定能用in

• 语法：

  SELECT EXISTS（完整的查询语句）
  

• 结果：1或0

• 【案例】查询有员工的部门名

  • 用in

  SELECT DISTINCT department_name            
  ---- distinct避免笛卡尔乘积现象
  FROM departments d,employees e
  WHERE d.`department_id` IN(e.`department_id`);
  
  --- 或者 在where里加入子查询
  SELECT department_name
  FROM departments d
  WHERE d.`department_id` IN(
  	SELECT department_id
  	FROM employees
  );
  

  • 用相关子查询

  SELECT department_name
  FROM departments d
  WHERE EXISTS(
  	SELECT *
  	FROM employees e
  	WHERE d.`department_id`=e.`department_id`
  );
  

• 【案例】查询没有男朋友的女神信息

  • 用exists

  SELECT *
  FROM beauty b
  WHERE NOT EXISTS(
  	SELECT *
  	FROM boys bo
  	WHERE b.`boyfriend_id`=bo.`id`
  );
  

  • 用in

  SELECT *
  FROM beauty b
  WHERE b.`boyfriend_id` NOT IN(
  	SELECT bo.`id`
  	FROM boys bo
  );
  

---

• 特点：

1. 子查询都放在小括号内。

2. 子查询可以放在 FROM 后面、SELECT 后面、WHERE 后面、HAVING 后面，但一般放在条件的右侧。

3. 子查询优先于主查询执行，主查询使用了子查询的执行结果。

4. 子查询根据查询结果的行数不同分为以下两类：

   • 单行子查询：

     • 结果集只有一行。
     • 一般搭配单行操作符使用：>, <, =, <>, >=, <=.
     • 非法使用子查询的情况：
       • a、子查询的结果为一组值。
       • b、子查询的结果为空。

   • 多行子查询：

     • 结果集有多行。
     • 一般搭配多行操作符使用：ANY, ALL, IN, NOT IN.
     • IN: 属于子查询结果中的任意一个就行。
     • ANY 和 ALL 往往可以用其他查询代替。

---

• 测试

1. 查询和Zlotkey相同部门的员工姓名和工资

   SELECT last_name, salary
   FROM employees
   WHERE department_id = (
       SELECT department_id
       FROM employees
       WHERE last_name = 'Zlotkey'
   );
   

2. 查询工资比公司平均工资高的员工的员工号，姓名和工资

   SELECT employee_id, last_name, salary
   FROM employees
   WHERE salary > (
       SELECT AVG(salary)
       FROM employees
   );
   

3. 查询各部门中工资比本部门平均工资高的员工的员工号、姓名和工资

• 直接在where里用子查询

  SELECT employee_id, last_name, salary, e.department_id
  FROM employees e
  WHERE salary > (
      SELECT ag_dep.ag
      FROM (
          SELECT department_id, AVG(salary) ag
          FROM employees
          GROUP BY department_id
      ) ag_dep
      WHERE e.department_id = ag_dep.department_id
  );
  

• 在内连接里用子查询

  SELECT employee_id,last_name,salary,e.department_id
  FROM employees e
  INNER JOIN (     
  ---通过内连接就把部门平均工资加入员工信息的后面构成新表了
  	SELECT department_id,AVG(salary) ag
  	FROM employees
  	GROUP BY department_id
  ) avg_dep
  ON avg_dep.department_id=e.`department_id`
  WHERE salary>avg_dep.ag;   
  --- 在这张新表里就可以直接加条件查询了
  

4. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

   SELECT employee_id, last_name
   FROM employees e
   WHERE e.department_id IN (
       SELECT DISTINCT department_id
       FROM employees
       WHERE last_name LIKE '%u%'
   );
   

5. 查询在部门的location_id为1700的部门工作的员工的员工号

   SELECT employee_id
   FROM employees e
   WHERE e.department_id IN ( --或者 = any()
       SELECT department_id
       FROM departments
       WHERE location_id = 1700
   );
   

6. 查询管理者是King的员工姓名和工资

   SELECT last_name, salary
   FROM employees
   WHERE manager_id IN (
       SELECT employee_id
       FROM employees
       WHERE last_name = 'K_ing'
   );
   

7. 查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为"姓.名"

   SELECT CONCAT(first_name, ' ', last_name) AS "姓.名"
   FROM employees
   WHERE salary = (
       SELECT MAX(salary)
       FROM employees
   );
   

• 子查询经典案例题目

1. 查询工资最低的员工信息: last_name, salary

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

2. 查询平均工资最低的部门信息

   SELECT d.*
   FROM departments d
   WHERE d.department_id = (
       SELECT department_id
       FROM employees
       ORDER BY AVG(salary)
       LIMIT 1
   );
   

3. 查询平均工资最低的部门信息和该部门的平均工资

   SELECT d.*, ag
   FROM departments d
   JOIN (  -- 通过连接构造一张新表
       SELECT AVG(salary) ag, department_id
       FROM employees
       GROUP BY department_id
   ) ag_dep
   ON d.department_id = ag_dep.department_id
   ORDER BY ag
   LIMIT 1;
   

4. 查询平均工资最高的 job 信息

   SELECT *
   FROM jobs
   WHERE jobs.job_id = (
       SELECT job_id
       FROM employees
       GROUP BY job_id
       ORDER BY AVG(salary) DESC
       LIMIT 1
   );
   

5. 查询平均工资高于公司平均工资的部门有哪些?

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

6. 查询出公司中所有 manager 的详细信息.

   SELECT *
   FROM employees
   WHERE employee_id IN (
       SELECT DISTINCT manager_id
       FROM employees
   );
   

7. 各个部门中最高工资中最低的那个部门的最低工资是多少

   SELECT MIN(salary), department_id
   FROM employees
   GROUP BY department_id
   HAVING department_id = (
       SELECT department_id
       FROM employees
       GROUP BY department_id
       ORDER BY MAX(salary)
       LIMIT 1
   );
   

8. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

   SELECT last_name, e.department_id, email, salary
   FROM employees e
   WHERE employee_id = (
       SELECT manager_id
       FROM employees
       GROUP BY department_id
       ORDER BY MAX(salary) DESC
       LIMIT 1
   );
   

八、分页查询

• 应用场景：在显示的数据量较大且一页无法显示全部数据时，通常需要使用分页查询，以便根据用户需求提交相应的 SQL 查询语句。

• 分页查询语法

SELECT 字段|表达式,...
FROM 表
[【join type】]
[WHERE 条件]
[GROUP BY 分组字段]
[HAVING 条件]
[ORDER BY 排序的字段]
LIMIT 【起始的条目索引，】条目数;

• LIMIT 关键字用于限制查询结果返回的行数。

• 【起始的条目索引 offset，】 可选部分，用于指定要查询的起始条目索引（起始索引从 0 开始）。

• 条目数size 指定要返回的条目数量。

• LIMIT 参数说明

• offset：要显示条目的起始索引，可以省略，如果省略，默认从第一条记录开始。

• size：要显示的条目个数。

---

• 查询前5条员工信息

SELECT * FROM employees LIMIT 0,5;
-- 或者
SELECT * FROM employees LIMIT 5;

• 查询第11条到第25条员工信息

SELECT * FROM employees LIMIT 10,15;

• 查询有奖金的员工信息，并显示工资较高的前10名

SELECT * FROM employees
WHERE `commission_pct` IS NOT NULL
ORDER BY salary DESC
LIMIT 10;

• 特点：
  ① 起始条目索引从0开始
  ② limit子句放在查询语句的最后
  ③ 公式：select * from 表 limit （page-1）*sizePerPage, sizePerPage
  假如:
  每页显示条目数sizePerPage
  要显示的页数 page

9. 联合查询

• 引入
  union 联合、合并：将多条查询语句合并成一个结果

• 【案例】查询部门编号＞90或邮箱中包含a的员工信息

  SELECT * FROM employees
  WHERE department_id>90
  OR email LIKE '%a%';
  

  • 联合查询

  SELECT * FROM employees WHERE department_id>90
  UNION
  SELECT * FROM employees WHERE email LIKE '%a%';
  

• Union 查询语法

SELECT 字段|常量|表达式|函数 FROM 表 
WHERE 条件 union [all]

SELECT 字段|常量|表达式|函数 FROM 表 
WHERE 条件 union [all]
...
SELECT 字段|常量|表达式|函数 FROM 表 WHERE 条件;

• union 关键字用于合并多个查询语句的结果，如果希望保留重复记录，请使用 union all。

• 每个查询语句应包含相同数量和类型的字段，它们的顺序也应一致。

• Union 查询应用场景

  • 要查询的结果来自多张表
  • 要查询的结果来自多个数据表，这些表之间没有直接的连接关系。但查询的信息一致

---

• 查询部门编号大于 90 或邮箱中包含 ‘a’ 的员工信息

SELECT * FROM employees WHERE department_id > 90
UNION
SELECT * FROM employees WHERE email LIKE '%a%';

• 查询男性中国用户和男性外国用户的信息

SELECT id, cname, csex FROM t_ca WHERE csex = '男'
UNION
SELECT t_id, tName, tGender FROM t_ua WHERE tGender = 'male';

• Union 查询特点

1. 多个查询语句的查询的列数必须相同。
2. 多个查询语句的查询的列的数据类型和顺序最好一致。
3. union 会去除重复的记录，而 union all 不去重