MySQL精讲(一) |DQL数据查询语句全解析

阳光穿透心脏的1/2处 2023-02-14 02:41 8阅读 0赞

> **MySQL精讲系列文章(更新完毕)**  
> [《MySQL入门必看知识》][MySQL]  
> [《MySQL精讲(一) | DQL数据查询语句全解析》][MySQL_ _ DQL]  
> [《MySQL精讲(二) | DML数据操作语句全解析》][MySQL_ _ DML]  
> [《MySQL精讲(三) | DDL数据定义语句全解析 |数据类型和约束》][MySQL_ _ DDL_]  
> [《MySQL精讲(四) | TCL事务控制语句全解析|视图》][MySQL_ _ TCL]  
> [《MySQL精讲(五) | 存储过程和函数》][MySQL_ _]  
> [《MySQL精讲(六) | 流程控制》][MySQL_ _ 1]  
>                                                                                                [– by 凡尘阿凉][by]

### 文章目录 ###

*  一、基础查询
 *  二、条件查询
 *  三、排序查询
 *  四、常见函数
 *  五、分组查询
 *  六、连接查询
 *  七、子查询
 *  八、分页查询
 *  九、联合查询

--------------------

**样本数据准备**

进行DQL查询语句实验前，首先准备对应的数据，作为样本以供查询使用：

> 样本表链接：[https://pan.baidu.com/s/1Qp81tD4htQaK5bO0k1sxQQ][https_pan.baidu.com_s_1Qp81tD4htQaK5bO0k1sxQQ]  
> 提取码：0v6l

使用SQLyog中导入该SQL脚本，可以看到准备好的样本表：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 1]

该样本是某跨国企业员工管理的4张表，下图对每张表的各个字段做了介绍：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 2]

# 一、基础查询 #

语法：`select 查询列表 from 表名;`

特点：  
查询列表可以是:表中的字段、常量值、表达式、函数。  
查询的结果是一个虚拟的表格。

执行顺序：`from > select (先找到表，再开始查询)`

注意:`是着重号，当某张表中的字段与关键字冲突时，可以在该字段两边加上着重号，以标明其是一个字段，而不是关键字(如`name`)。

【基础查询】
    
    # 选中样本库
    USER myemployees;
    
    # 1.查询表中的单个字段
    SELECT last_name FROM employees;
    
    # 2.查询表中的多个字段
    SELECT last_name,salary,email FROM employees;
    
    # 3.查询表中所有的字段
    SELECT * FROM employees;
    
    # 4. 查询常量值
    SELECT 'Tom';
    
    # 5.查询表达式
    SELECT 7%6;
    
    # 6. 查询函数
    SELECT VERSION();
    
    # 7.起别名(mysql中建议将起别名使用双引号引起来"别名")
    /* 优点:便于理解;连接查询时,如果要查询的字段有重名情况,可以使用起别名来区分 */
    # 方式一,使用as
    SELECT 7%6 AS 结果;
    SELECT last_name AS 姓,first_name AS 名 FROM employees;
    # 方式二,使用空格
    SELECT 7%6 结果;
    SELECT last_name 姓,first_name 名 FROM employees;
    # 查询员工号为176的员工的姓名、部门、nianxin
    SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees; 
    
    # 8.去重
    SELECT DISTINCT department_id FROM employees;
    
    # 9.+号的作用
    /* select 13+21; 两个操作数都是数值型,自动做加法运算 其中一个为字符型,则将字符型转换为数值型 select '13'+1; 转换成功,做加法运算 select 'hello'+1; 转换失败,将字符型转换为0 select null+10; 只要其中一方为null,结果就为null 补充ifnull函数:SELECT IFNULL(commission_pct,0) AS 奖金率,commission_pct FROM employees; mysql中用来拼接的不是+号,而是concat函数 */
    SELECT CONCAT(last_name,first_name) AS "姓名" FROM employees;

<table> 
 <thead> 
  <tr> 
   <th>基础查询总结</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>1.查询表中的单个字段</strong></td> 
   <td><code>select 字段1 from 表;</code></td> 
  </tr> 
  <tr> 
   <td><strong>2.查询表中的多个字段</strong></td> 
   <td><code>select 字段1,字段2,...字段n from 表;</code></td> 
  </tr> 
  <tr> 
   <td><strong>3.查询表中的所有字段</strong></td> 
   <td><code>select * from 表;</code></td> 
  </tr> 
  <tr> 
   <td><strong>4.查询常量值</strong></td> 
   <td><code>select '常量值;'</code></td> 
  </tr> 
  <tr> 
   <td><strong>5.查询表达式</strong></td> 
   <td><code>select 数值1 表达式 数值2;</code></td> 
  </tr> 
  <tr> 
   <td><strong>6.查询函数</strong></td> 
   <td><code>select f();</code></td> 
  </tr> 
  <tr> 
   <td><strong>7.起别名</strong></td> 
   <td><code>as</code></td> 
  </tr> 
  <tr> 
   <td><strong>8.去重</strong></td> 
   <td><code>distinct</code></td> 
  </tr> 
  <tr> 
   <td><strong>9.拼接使用concat函数,而不是"+"</strong></td> 
   <td><code>concat(last_name,first_name)</code></td> 
  </tr> 
 </tbody> 
</table>

> 学完了基础查询，尝试完成下面的练习题  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 3]  
> **答案：  
> 1.`正确`  
> 2.`正确`  
> 3.`应在英文状态下使用引号`  
> 4.`DESC departments;`；`SELECT * FROM departments;`  
> 5.`SELECT CONCAT(first_name,',',last_name,',',IFNULL(email,0)) AS "out_put" FROM employees;`**

# 二、条件查询 #

语法：`select 查询列表 from 表名 where 筛选条件;`

执行顺序：`from > where > select (先定位到表,然后开始筛选,最后走查询)`

分类:

(1)按条件运算符筛选

条件运算符有： >  <    =   >=   <=   !=(或<>)

(2)按逻辑表达式筛选

支持&& || !,但推荐使用and or not 
    逻辑表达式作用：用于连接条件表达式
    &&或and: 两个都为true,结果为true,反之为false
    ||或or : 只要有一个条件为true,结果即为true,反之为false
    !或not : 取反

(3)模糊查询

模糊查关键字：like、between and、in、is null
    (1)like关键字
    	可以判断字符型或数值型
    	  like一般和通配符搭配使用,通配符有
    		%:代表任意多个字符,包含0个
    		_:代表任意单个字符
    (2)between...and关键字
    	  可以提高语句简洁度
    	  包含临界值
    	  两个临界值不能调换顺序
    (3)in关键字
    	  可以提高语句简洁度
    	  in列表的值类型必须一致
    (4)is null
    	  取反是 is not null

【条件查询】
    
    (1)按条件运算符筛选
    
    # 1.查询工资>12000的员工
    SELECT * FROM employees WHERE salary > 12000 ;
    
    # 2.查询部门编号不等于90的员工名和部门编号
    SELECT department_name, department_id FROM departments WHERE department_id<>90;
    
    ---------------------------------------------------------------------------------------------------------------------
    (2)按逻辑表达式筛选
    
    # 1.查询工资在10000到20000之间的员工名、工资以及奖金率
    SELECT last_name,salary,commission_pct FROM employees WHERE salary>=10000 AND salary<=20000;
    
    # 2.查询部门编号不是在90到110之间的,或工资高于15000的员工信息
    SELECT * FROM employees WHERE NOT(department_id>=90 AND department_id<=110) OR salary>15000;
    
    ---------------------------------------------------------------------------------------------------------------------
    (3)模糊查询
    
    # (1)like关键字
    
    # 1.查询员工名中包含字符a的员工的信息
    SELECT * FROM employees WHERE last_name LIKE '%a%';
    # 2.查询员工名中第三个字符为n,第五个字符为l的员工名和工资
    SELECT last_name,salary FROM employees WHERE last_name LIKE '__n_l%';
    # 3.查询员工名中第二个字符为_的员工名(转义)
    SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';
    
    # (2)between...and关键字
    
    # 1.查询员工编号在100到120的员工信息
    SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
    
    # (3)in关键字
    # 1.查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的员工名和工种编号
    SELECT last_name,job_id FROM employees WHERE job_id IN('IT_PROG','AD_VP','AD_PRES');
    
    # (4)is null
    # 1.查询没有奖金的员工名和奖金率
    SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;
    
    # is null仅仅可以用来判断null值;安全等于<=>既可以用来判断null值,又可以用来判断普通值
    # is null的可读性高于<=>,建议使用is null
    SELECT last_name,commission_pct FROM employees WHERE commission_pct <=> NULL;

<table> 
 <thead> 
  <tr> 
   <th>条件查询总结</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>(1)按条件运算符筛选</strong></td> 
   <td><code>&gt; &lt; = &gt;= &lt;= !=(或&lt;&gt;)</code></td> 
  </tr> 
  <tr> 
   <td><strong>(2)按逻辑表达式筛选</strong></td> 
   <td><code>&amp;&amp; || !或and or not</code></td> 
  </tr> 
  <tr> 
   <td><strong>(3)模糊查询</strong></td> 
   <td><code>关键字:like、between...and、in、is null</code></td> 
  </tr> 
 </tbody> 
</table>

> 学完了条件查询，尝试完成下面的练习题![这里是引用][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 4]  
> **答案：  
> 一、`SELECT salary,last_name FROM employees WHERE commission_pct IS NULL AND salary < 18000;`  
> 二、`SELECT * FROM employees WHERE job_id <> 'IT' OR salary=12000;`  
> 三、`DESC departments;`  
> 四、`SELECT DISTINCT location_id FROM departments;`  
> 五、`不一定,考虑字段有null的情况.`**

# 三、排序查询 #

语法：`select 查询列表 from 表 where 筛选条件 order by 排序列表 asc|desc`

特点：

*  1.asc代表升序，esc代表降序，不写默认是升序。
 *  2.order by子句支持单个字段、多个字段、表达式、函数、别名

执行顺序：`from > where > select > 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;
    SELECT * ,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY 年薪 DESC; # ORDER BY后支持别名
    
    # 4.按照姓名的长度,显示员工的姓名和工资【按函数排序】
    SELECT LENGTH(last_name) AS 字节长度, last_name,salary FROM employees ORDER BY 字节长度 DESC;
    
    # 5.查询员工信息,先按工资升序,再按员工编号降序
    SELECT * FROM employees ORDER BY salary ASC ,employee_id DESC;

> 学完了排序查询，尝试完成下面的练习题![这里是引用][20200601012311344.png]  
> **答案：  
> 1、`SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY 年薪 DESC,last_name ASC;`  
> 2、`SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC;`  
> 3、`SELECT * FROM employees WHERE email LIKE '%e%' ORDER BY LENGTH(email) DESC,department_id ASC;`**

<table> 
 <thead> 
  <tr> 
   <th>排序查询总结</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>升序</strong></td> 
   <td><code>order by asc</code></td> 
  </tr> 
  <tr> 
   <td><strong>降序</strong></td> 
   <td><code>order by desc</code></td> 
  </tr> 
 </tbody> 
</table>

# 四、常见函数 #

调用语法：`select 函数名(实参列表) from 表;`

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

好处：  
1.隐藏了实现细节  
2.提高代码重用性

分类：分为单行函数和分组函数。其中`单行函数`又分为：字符函数、数学函数、日期函数、系统函数、流程控制函数。；`分组函数`用来做统计功能，又称为统计函数、聚合函数、组函数。

<table> 
 <thead> 
  <tr> 
   <th>单行函数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>(1)字符函数</strong></td> 
   <td><strong>参数类型为字符型</strong></td> 
  </tr> 
  <tr> 
   <td>获取参数值的字节个数</td> 
   <td><code>select length('字符串')</code></td> 
  </tr> 
  <tr> 
   <td>拼接</td> 
   <td><code>concat(字段1,字段2)</code></td> 
  </tr> 
  <tr> 
   <td>大小写转换</td> 
   <td><code>upper、lower</code></td> 
  </tr> 
  <tr> 
   <td>截取</td> 
   <td><code>substr(index,end)</code></td> 
  </tr> 
  <tr> 
   <td>查找</td> 
   <td><code>instr(主串,子串)</code></td> 
  </tr> 
  <tr> 
   <td>清除两边空格</td> 
   <td><code>trim(a from'aaaa字符串1aa')</code></td> 
  </tr> 
  <tr> 
   <td>左右填充</td> 
   <td><code>lpad('字符串1',左填充个数n,填充字符'a')</code>、<code>lpad('字符串1',右填充个数n,填充字符'a')</code></td> 
  </tr> 
  <tr> 
   <td>替换</td> 
   <td><code>replace('字符串1','被更换的字符串','新的字符串')</code></td> 
  </tr> 
  <tr> 
   <td><strong>(2)数学函数</strong></td> 
   <td><strong>参数类型为数值</strong></td> 
  </tr> 
  <tr> 
   <td>四舍五入</td> 
   <td><code>round(小数,保留位数)</code></td> 
  </tr> 
  <tr> 
   <td>截取</td> 
   <td><code>truncate(小数,保留位数)</code></td> 
  </tr> 
  <tr> 
   <td>向上取整</td> 
   <td><code>ceil(被向上取整的数值)</code></td> 
  </tr> 
  <tr> 
   <td>向下取整</td> 
   <td><code>floor(被向下取整的数值)</code></td> 
  </tr> 
  <tr> 
   <td>取余</td> 
   <td><code>mod(n,m);结果的正负和被取余数n相同</code></td> 
  </tr> 
  <tr> 
   <td>随机数</td> 
   <td><code>rand();返回0-1之间的小数</code></td> 
  </tr> 
  <tr> 
   <td><strong>(3)日期函数</strong></td> 
   <td><strong>参数为日期</strong></td> 
  </tr> 
  <tr> 
   <td>返回当前完整日期</td> 
   <td><code>select now();</code></td> 
  </tr> 
  <tr> 
   <td>返回当前年月日</td> 
   <td><code>select curdate();</code></td> 
  </tr> 
  <tr> 
   <td>返回当前时分秒</td> 
   <td><code>select curtime();</code></td> 
  </tr> 
  <tr> 
   <td>截取指定部分</td> 
   <td><code>select YEAR(now()) as 年,MONTH(now()) as 月,DAY(now()) as 日;</code></td> 
  </tr> 
  <tr> 
   <td>字符串→日期</td> 
   <td><code>STR_TO_DATE('2020-7-7','%Y-%m-%d')</code></td> 
  </tr> 
  <tr> 
   <td>日期→字符串</td> 
   <td><code>DATE_FORMAT(NOW(),'%Y年%m月%d日')</code></td> 
  </tr> 
  <tr> 
   <td>返回两个日期相差的天数</td> 
   <td><code>datediff(日期1,日期2)</code></td> 
  </tr> 
  <tr> 
   <td><strong>(4)系统函数</strong></td> 
   <td><strong>系统自带</strong></td> 
  </tr> 
  <tr> 
   <td>查看当前版本</td> 
   <td><code>select version();</code></td> 
  </tr> 
  <tr> 
   <td>查看当前数据库</td> 
   <td><code>select database();</code></td> 
  </tr> 
  <tr> 
   <td>查看当前用户</td> 
   <td><code>select user();</code></td> 
  </tr> 
  <tr> 
   <td>自动加密</td> 
   <td><code>password('字符');或md5('字符');</code></td> 
  </tr> 
  <tr> 
   <td><strong>(5)流程控制函数</strong></td> 
   <td><strong>类比Java</strong></td> 
  </tr> 
  <tr> 
   <td>if</td> 
   <td><code>if(奖金 IS NULL,'没奖金','有奖金')</code></td> 
  </tr> 
  <tr> 
   <td><img src="https://img-blog.csdnimg.cn/20200602221403226.png" alt="case"></td> 
   <td><img src="https://img-blog.csdnimg.cn/20200602221437733.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA==,size_16,color_FFFFFF,t_70" alt="在这里插入图片描述"></td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <thead> 
  <tr> 
   <th>分组函数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>sum</strong></td> 
   <td><code>求和</code></td> 
  </tr> 
  <tr> 
   <td><strong>avg</strong></td> 
   <td><code>平均值</code></td> 
  </tr> 
  <tr> 
   <td><strong>max</strong></td> 
   <td><code>最大值</code></td> 
  </tr> 
  <tr> 
   <td><strong>min</strong></td> 
   <td><code>最小值</code></td> 
  </tr> 
  <tr> 
   <td><strong>count</strong></td> 
   <td><code>计算个数</code></td> 
  </tr> 
 </tbody> 
</table>

【单行函数】
    
    # (1)字符函数-[参数类型为字符型]
    
    # 1.length 获取参数值的字节个数
    SELECT LENGTH('john');
    SELECT LENGTH('张三丰');
    SHOW VARIABLES LIKE '%char%' # 查看字符集
    
    # 2.concat拼接字符串
    SELECT CONCAT(last_name,'_',first_name) 姓名 from employees;
    
    # 3.upper、lower 大小写转换
    SELECT UPPER('tom');
    SELECT LOWER('TOM')
    # 将姓变大写,名变小写,然后拼接
    SELECT CONCAT(UPPER(last_name),LOWER(first_name))姓名 FROM employees;
    
    # 4.substr 拼接函数
    # mysql中的索引从1开始
    SELECT SUBSTR('若负平生意,何名作莫愁',7) AS out_put;
    SELECT SUBSTR('若负平生意,何名作莫愁',1,3) AS out_put;
    # 案例:姓名中首字符大写,其他字符小写,用_拼接并显示出来
    SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) oup_put FROM employees; 
    
    # 5.instr 字符查找函数
    # 返回子串在主串中的起始索引,没有返回零
    SELECT INSTR('凡尘阿凉','阿凉') AS out_put;
    
    # 6.trim 清除空格函数
    # 将字符两边的空格移除
    SELECT LENGTH(TRIM(' 凡尘 ')) AS out_put;
    SELECT TRIM('a' FROM  'aaaaaa凡aaa尘aaaa') AS out_put;
    
    # 7.lpad 左填充函数
    # 用指定的字符实现左填充指定长度
    SELECT LPAD('凡尘',10,'*') AS out_put;
    
    # 8.rpad 右填充函数
    # 用指定的字符实现右填充指定长度
    SELECT RPAD('凡尘',10,'*') AS out_put;
    
    # 9.replace 替换函数
    SELECT REPLACE('我的偶像是鲁迅','鲁迅','周冬雨') AS oup_put;
    
    ---------------------------------------------------------------------------------------------------------
    # (2)数学函数-[参数类型为数值]
    
    # 1.round 四舍五入函数
    SELECT ROUND(1.65);
    SELECT ROUND(1.567,2);
    
    # 2.ceil 向上取整函数
    # 返回>=该参数的最小整数
    SELECT CEIL(1.00);
    
    # 3.floor 向下取整函数
    # 返回<=该参数的最大整数
    SELECT FLOOR(-9.99);
    
    # 4.truncate 截取函数 
    # 保留小数点后几位
    SELECT TRUNCATE(1.65,1);
    
    # 5,mod 取余函数
    # 结果的正负和被除数相同:a-a/b*b
    SELECT MOD(10,-3);
    
    ---------------------------------------------------------------------------------------------------------
    # (3)日期函数-[参数为日期]
    # 1.now 返回当前完整日期
    SELECT NOW();
    
    # 2.curdate 返回当前年月日
    SELECT CURDATE();
    
    # 3.curtime 返回当前时分秒
    SELECT CURTIME();
    
    # 4.获取指定的部分
    SELECT YEAR(NOW()) AS 年;
    SELECT YEAR('1998-12-12') AS 年;
    SELECT YEAR(hiredate) 年 FROM employees;
    SELECT MONTH(NOW()) 月 ;
    SELECT MONTHNAME(NOW()) 月 ;
    
    # 5.str_to_date 将日期格式的字符转换为指定格式的日期
    SELECT STR_TO_DATE('2020-7-7','%Y-%m-%d') AS out_put;
    # 查询入职日期为1992-4-3的员工信息
    SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%m-%d %Y');
    # 6.date_format 将日期转换为字符
    SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日');
    # 查询有奖金的员工和入职日期(xx月/xx日 xx年)
    SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %Y年') 入职日期 FROM employees WHERE commission_pct IS NOT NULL;
    
    ---------------------------------------------------------------------------------------------------------
    # (4)系统函数
    # 1.查看当前版本
    SELECT VERSION()
    # 2.查看当前数据库
    SELECT DATABASE();
    # 3.查看当前用户
    SELECT USER();
    
    ---------------------------------------------------------------------------------------------------------
    # (5)流程控制函数
    # 1.if函数
    SELECT IF(10>5,'大于','小于');
    SELECT last_name,commission_pct, IF(commission_pct IS NULL,'没奖金','有奖金') AS out_put FROM employees;
    
    # 2.case函数
    /* 方式一：类似于Java中的switch-case： 案例:查询员工工资,要求 部门号=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;
    
    /* 方式二：类似于Java中的多重if: 案例:查询员工的工资情况 工资>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;

【分组函数】
    /* SUM 求和 AVG 平均值 MAX 最大值 MIN 最小值 COUNT 计算个数 */
    # 综合使用
    SELECT SUM(salary) "和",AVG(salary) "平均数",MAX(salary) "最大值",MIN(salary) "最小值",COUNT(salary) "总个数" 
    FROM employees;
    
    /* 分组函数的特点: 1.sum、avg一般用于处理数值型；max、min、count可以处理任何类型 2.分组函数都忽略null值,都可以和distinct搭配去重 3.和分组函数一同查询的字段要求是group by后的字段 4.count函数经常用来统计行数,使用count(*)或count(1)或count(常量) 效率问题: MYISAM存储引擎下,count(*)效率高 INNODB存储引擎下,count(*)和count(1)效率差不多,但比count(字段)要高 */

> 学完了单行函数，尝试完成下面的练习题![这里是引用][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 5]  
> **答案:  
> 1、`SELECT NOW();`  
> 2、`SELECT employee_id,last_name,salary*1.2 "new salary" FROM employees;`  
> 3、`SELECT LENGTH(last_name) "长度",SUBSTR(last_name,1) "首字符",last_name FROM employees ORDER BY 首字符;`  
> 4、`SELECT CONCAT(last_name,' earns '),salary,' monthly but wants ',salary*3 AS "Dream Salary" FROM employees WHERE salary=24000;`  
> 5、  
> 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\_PRE’ THEN ‘D’  
> WHEN ‘ST\_CLERK’ THEN ‘E’  
> END AS “Grade”  
> FROM employees  
> WHERE job\_id =“AD\_PRES”;**

> 学完了分组函数，尝试完成下面的练习题：  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 6]  
> **答案：  
> 1.`SELECT ROUND(MAX(salary),2) "最大值",ROUND(MIN(salary),2) "最小值",ROUND(AVG(salary),2) "平均值",ROUND(SUM(salary),2) "总和" FROM employees;`  
> 2.`SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) "DIFFERNCE" FROM employees;`  
> 3.`SELECT COUNT(*) AS "员工个数" FROM employees WHERE department_id=90;`**

# 五、分组查询 #

语法:  
`select 分组函数,查询列表(要求出现在group by的后面)`  
`from 表`  
`【where 筛选条件】`  
`group by 分组的列表`  
`【having 分组后的筛选】`  
`【order by 子句】`

执行顺序：`from > where > group by > having > select > order by`

<table> 
 <thead> 
  <tr> 
   <th>分类</th> 
   <th>筛选源</th> 
   <th>位置</th> 
   <th>关键字</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>分组前筛选</td> 
   <td>原始表</td> 
   <td>group by</td> 
   <td>where</td> 
  </tr> 
  <tr> 
   <td>分组后筛选</td> 
   <td>分组后的结果集</td> 
   <td>group by</td> 
   <td>having</td> 
  </tr> 
 </tbody> 
</table>

注意：  
1.查询列表必须是分组函数和group by后出现的字段。  
2.分组函数做条件一定放在having子句中。  
3.能用分组前筛选的优先使用分组前筛选。  
4.group by子句支持单个字段分组、多个字段分组(多个字段之间用逗号隔开，没有顺序要求)、表达式或函数。  
5.可以添加排序(排序放在整个分组查询最后)  
6.一般不在group by和having后使用别名。

# 1.查询每个工种的最高工资
    SELECT MAX(salary) "最高工资",job_id "工种" FROM employees GROUP BY job_id;
    
    # 2.查询每个位置上的部门个数
    SELECT COUNT(*) "部门个数",location_id "位置id" FROM departments GROUP BY location_id;
    
    # 3.查询邮箱中包含a字符的,每个部门的平均工资
    SELECT AVG(salary) "平均工资",department_id "部门id" FROM employees WHERE email LIKE '%a%' GROUP BY department_id;
    
    # 4.查询每个领导手下的有奖金的员工的最高工资
    SELECT MAX(salary) "最高工资",manager_id "领导编号" FROM employees WHERE NOT ISNULL(commission_pct)  GROUP BY manager_id;
    
    # 5.查询哪个部门的员工个数>2
    # 思路:查询每个部门的个数,再根据结果哪个部门的员工个数>2
    SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;
    
    # 6.查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
    SELECT MAX(salary) "最高工资",job_id "工种" FROM employees WHERE NOT ISNULL(commission_pct) GROUP BY job_id HAVING MAX(salary)>12000;
    
    # 7.查询领导编号>102的每个领导手下员工的最低工资>5000的领导编号是哪个,以及其最低工资
    SELECT MIN(salary) "最低工资",manager_id "领导编号" FROM employees WHERE manager_id>102 GROUP BY manager_id HAVING MIN(salary)>5000;
    
    # 8.按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
    SELECT COUNT(*) "员工个数",LENGTH(last_name) "姓名长度" FROM employees GROUP BY LENGTH (last_name) HAVING COUNT(*)>5;
    
    # 9.查询每个部门每个工种的员工的平均工资
    SELECT AVG(salary) "平均工资",department_id "部门",job_id "工种" FROM employees GROUP BY department_id,job_id; 
    
    # 10.查询每个部门每个工种的员工的平均工资,并按平均工资的高低显示
    SELECT AVG(salary) "平均工资",department_id "部门",job_id "工种" FROM employees GROUP BY department_id,job_id ORDER BY AVG(salary) DESC;

> 学完了分组查询，尝试完成下面的练习题![这里是引用][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 7]  
> **答案：  
> 1、`SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id FROM employees GROUP BY job_id ORDER BY job_id ASC;`  
> 2、`SELECT MAX(salary)-MIN(salary) "DIFFERENCE" FROM employees;`  
> 3、`SELECT MIN(salary) ,manager_id FROM employees WHERE NOT ISNULL(manager_id) GROUP BY manager_id HAVING MIN(salary)>=6000;`  
> 4、`SELECT department_id,COUNT(*),AVG(salary) FROM employees GROUP BY department_name ORDER BY AVG(salary) DESC;`  
> 5、`SELECT COUNT(*) "个数",job_id FROM employees GROUP BY job_id;`**

# 六、连接查询 #

概念：`查询的字段来自多个表。`

分类：安装年代可以分为`sql92`和`sq99`，按照功能分为`内连接(交集)`、`外连接(一个表中有,另一个表中没有)`、`交叉连接`；其中`sql92仅支持内连接`，`sql99除全外连接其他全都支持`。

注意：  
1.如果为表起了别名,则查询的字段就不能使用原来的表名去限定。  
2.当涉及到多表查询时,为表起别名可以有效提高语句简洁度,方便区分多个重名的字段。

连接查询分为下面三类：  
**(1)内连接`inner`**

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

**(2)外连接**

*  左外连接`left outer`
 *  右外连接`right outer`
 *  全外连接`full outer`

**(3)交叉连接`cross`**

【sql92标准】
    # 1.等值连接
    # 查询女神名和对应的男朋友名
    # SELECT NAME,boyName FROM boys,beauty WHERE beauty.boyfriend_id=boys.id;
    
    # 1.查询员工名和对应的部门名
    SELECT last_name "员工名",department_name "部门名" FROM employees,departments 
    WHERE employees.department_id=departments.department_id;
    
    # 2.查询员工名、工种号、工种名
    SELECT last_name,e.job_id,job_title FROM employees e,jobs j WHERE e.`job_id`=j.`job_id`;
    
    # 3.查询有奖金的员工名、部门名
    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;
    
    # 等值连接+筛选
    # 4.查询城市中第二个字符为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 departments d,locations l 
    WHERE d.`location_id`=l.`location_id` GROUP BY city;
    
    # 6.查询有奖金的每个部门的部门名、部门的领导编号、该部门最低工资
    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;
    
    # 7.查询每个工种的工种名、员工的个数并按员工的个数降序
    SELECT job_title,COUNT(*) FROM employees e,jobs j WHERE e.`job_id`=j.`job_id` 
    GOUP BY job_title ORDER BY COUNT(*) DESC;
    
    # 8.支持三表连接
    # 查询员工名、部门名、所在的城市
    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`;
    
    # (2)非等值连接
    
    /* 先执行下面的语句,在myemployees数据库中创建新的job_grades表。 CREATE TABLE job_grades (grade_level VARCHAR(3), lowest_sal INT, highest_sal INT); INSERT INTO job_grades VALUES ('A', 1000, 2999); INSERT INTO job_grades VALUES ('B', 3000, 5999); INSERT INTO job_grades VALUES('C', 6000, 9999); INSERT INTO job_grades VALUES('D', 10000, 14999); INSERT INTO job_grades VALUES('E', 15000, 24999); INSERT INTO job_grades VALUES('F', 25000, 40000); */
    # 1.查询员工的工资和工资级别
    SELECT salary,grade_level FROM employees e,job_grades j WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`;
    
    
    # (3)自连接
    # 1.查询员工名和其上级的名称.
    SELECT e.employee_id "员工id",e.last_name "员工姓名",m.employee_id "经理id",m.last_name "经理姓名" 
    FROM employees e,employees m WHERE e.`manager_id`=m.`employee_id`;

> 学完了sql92标准的连接查询，尝试完成下面的练习题![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 8]  
> 答案：  
> 1、  
> SELECT last\_name,d.department\_id,department\_name  
> FROM employees e,departments d  
> WHERE e.department\_id=d.department\_id;  
> 2、  
> SELECT e.job\_id,d.location\_id FROM employees e,departments d  
> WHERE d.department\_id=e.department\_id  
> AND e.department\_id=90;  
> 3、  
> SELECT last\_name , department\_name , l.location\_id , 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;  
> 4、  
> SELECT last\_name , job\_id , d.department\_id , department\_name  
> FROM employees e,departments d,locations l  
> WHERE e.department\_id=d.department\_id AND d.location\_id=l.location\_id AND l.city=‘Toronto’;  
> 5、  
> SELECT department\_name,job\_title,MIN(salary)  
> FROM departments d,employees e,jobs j  
> WHERE d.department\_id=e.department\_id AND e.job\_id = j.job\_id  
> GROUP BY job\_title,department\_name;  
> 6、  
> SELECT COUNT(*),country\_id FROM departments d,locations l  
> WHERE d.location\_id=l.location\_id  
> GROUP BY country\_id  
> HAVING COUNT(*)>2;  
> 7、  
> SELECT e1.last\_name “employees”,e1.employee\_id “Emp\#”,e2.last\_name “manager”,e2.employee\_id “Mgr\#”  
> FROM employees e1,employees e2  
> WHERE e1.manager\_id=e2.employee\_id AND e1.last\_name=‘kochhar’;

【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 last_name LIKE '%e%' OR job_title 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 d.department_id=e.department_id
    INNER JOIN jobs j ON e.job_id=j.job_id
    ORDER BY department_name ;
    
    # (2)非等值连接
    # 查询员工工资级别
    SELECT salary,grade_level
    FROM employees e
    JOIN job_grades j ON e.`salary` BETWEEN j.lowest_sal AND j.highest_sal;
    
    # 查询每个工资级别的个数>20的个数,并且按照工资级别降序排列
    SELECT COUNT(*),grade_level
    FROM employees e
    JOIN job_grades j ON e.`salary` BETWEEN j.lowest_sal AND j.highest_sal
    GROUP BY grade_level
    HAVING COUNT(*)>20
    ORDER BY grade_level DESC;
    
    # (3)自连接
    # 查询员工的名字、上级的名字
    SELECT e1.last_name "员工名",e2.last_name "上级名"
    FROM employees e1
    JOIN employees e2 ON e1.manager_id=e2.employee_id;
    --------------------------------------------------------------------------------------------------------------
    
    # 二、外连接
    # 1.查询男朋友不在男神表的女神名
    # 左外连接
    SELECT NAME 
    FROM beauty LEFT OUTER JOIN boys ON beauty.boyfriend_id=boys.id
    WHERE boys.id IS NULL;
    # 右外连接
    SELECT NAME
    FROM boys RIGHT OUTER JOIN beauty ON beauty.boyfriend_id=boys.id
    WHERE boys.id IS NULL;
    
    # 2.查询没有员工的部门
    SELECT d.department_name,e.employee_id
    FROM departments d LEFT JOIN employees e ON d.department_id=e.department_id
    WHERE e.manager_id IS NULL;
    SELECT * FROM employees WHERE employee_id=100;
    
    # 3.全外连接(不支持)
    # 全外连接就是就并集
    USE girls;
    SELECT b.*,bo.*
    FROM beauty b
    FULL JOIN boys bo
    ON b.boyfriend_id=bo.id;
    
    # 三.交叉连接
    # 使用99标准实现的笛卡尔乘积,使用cross代替了92中的,
    SELECT b.*,bo.*
    FROM beauty b
    CROSS JOIN boys bo

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 9]

> 学完了sql99标准的连接查询，尝试完成下面的练习题![这里是引用][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 10]  
> 答案：  
> 一、  
> SELECT b.name,bo.\*  
> FROM beauty b LEFT JOIN boys bo  
> ON b.boyfriend\_id=bo.id  
> WHERE b.id>3;  
> 二、  
> SELECT city “城市”,department\_name “城市名”  
> FROM departments d RIGHT JOIN locations l  
> ON d.location\_id=l.location\_id  
> WHERE d.department\_id IS NULL;  
> 三、  
> SELECT d.department\_name,e.\*  
> FROM departments d LEFT JOIN employees e  
> ON d.department\_id=e.department\_id  
> WHERE d.department\_name IN (‘SAL’,‘IT’);

# 七、子查询 #

含义：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或外查询。

按`子查询出现的位置`分类：

*  select后面 (仅支持标量子查询)
 *  from后面 (支持表子查询)
 *  where或having后面 (支持标量、行、列子查询)☆☆☆
 *  exists后面 (又叫相关子查询，支持表子查询)

按`结果集的行列数`分类：

*  标量子查询(结果集只有一行一列)
 *  列子查询(结果集只有一列多行)
 *  行子查询(结果集有一行多列)
 *  表子查询(结果集一般为多行多列)

【where和having后的子查询】(支持标量、行、列子查询)
    
    # 1.单个标量子查询
    # 查询工资比Abel工资高的员工名
    SELECT last_name,salary 
    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。标量子查询+having子句
    # 查询最低工资 >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);
    
    	
    # 5.列子查询(多行子查询)
    # 返回location_id是1400或1700的部门中的所有员工姓名.
    SELECT last_name FROM employees 
    WHERE department_id IN (
    	SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700)
    );
    
    # 返回其它工种中比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';
    
    # 返回其它工种中比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';
    
    # 6.行子查询(一行多列或多列多行子查询)
    # 查询出员工编号最小并且工资最高的员工信息
    # 方式一
    SELECT * FROM employees WHERE (employee_id,salary)=(SELECT MIN(employee_id),MAX(salary) FROM employees);
    # 方式二
    SELECT * FROM employees 
    WHERE employee_id=(SELECT MIN(employee_id) FROM employees)
    AND salary=(SELECT MAX(salary) FROM employees)

【select后的子查询】：(仅支持标量子查询)
    
    # 1.查询每个部门的员工个数
    SELECT d.*,(SELECT COUNT(*) FROM employees e WHERE e.department_id=d.department_id) "员工个数"
    FROM departments d;
    
    # 2.查询员工号=102的部门名
    SELECT (
    	SELECT department_name
    	FROM departments d
    	INNER JOIN employees e
    	ON d.department_id=e.department_id
    	WHERE e.employee_id=102
    ) 部门名;

【from后面的子查询】(支持表子查询)
    
    # 1.查询每个部门的平均工资的工资等级,即将子查询后的结果充当一张表,要求必须起别名
    SELECT a.*,g.grade_level "工资等级"
    FROM (
    	SELECT AVG(salary) ag,department_id
    	FROM employees
    	GROUP BY department_id
    ) a
    INNER JOIN job_grades g
    ON a.ag BETWEEN lowest_sal AND highest_sal;

【existts后面的子查询】(又叫相关子查询,可以用in代替)
    
    # exists结果只会是1或0 ：SELECT EXISTS(SELECT * FROM employees);
    
    # 1.查询有员工名的部门名
    SELECT department_name
    FROM departments d
    WHERE EXISTS(SELECT * FROM employees e WHERE d.department_id=e.department_id);

> 学完了子查询，尝试完成下面的习题  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 11]  
> 答案：  
> `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、思路:①查询各部门平均工资②查询各部门工资比①高的员工的员工号、姓名、工资`  
> SELECT employee\_id,last\_name,salary,e.department\_id  
> FROM (  
> SELECT AVG(salary) ag,department\_id  
> FROM employees e  
> GROUP BY department\_id  
> )a INNER JOIN employees e  
> ON a.department\_id=e.department\_id  
> WHERE e.salary>a.ag  
> `4、思路:①查询姓名中包含字母u的员工的部门②查询部门号=①中任意一个的员工的工号和姓名`  
> SELECT employee\_id “员工号”,last\_name “姓名”  
> FROM employees  
> WHERE department\_id IN(  
> SELECT DISTINCT department\_id  
> FROM employees  
> WHERE last\_name LIKE ‘%u%’  
> );  
> `5、思路:①查询location_id=1700的部门②查询在①部门中工作的员工的员工号`  
> SELECT employee\_id  
> FROM employees  
> WHERE department\_id IN(  
> SELECT DISTINCT department\_id  
> FROM departments  
> WHERE location\_id=1700  
> );  
> `6、思路:①查询姓名为K_ing的员工编号②查询manager_id=①的姓名和工资`  
> SELECT last\_name “员工姓名”,salary “工资”  
> FROM employees  
> WHERE manager\_id IN(  
> SELECT employee\_id  
> FROM employees  
> WHERE last\_name=‘K\_ing’  
> );  
> `7、思路:①查询最高工资②查询工资=①的姓.名`  
> SELECT CONCAT(first\_name,last\_name) “姓.名”  
> FROM employees  
> WHERE salary=(SELECT MAX(salary) FROM employees);

> 如果觉得做得不过瘾，可以继续挑战下面的子查询经典案例:  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 12]  
> 答案：  
> `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  
> GROUP BY department\_id  
> HAVING AVG(salary)=(  
> SELECT MIN(ag)  
> FROM (  
> SELECT AVG(salary) ag,department\_id  
> FROM employees  
> GROUP BY department\_id  
> ) a  
> )  
> );  
> `思路二:①使用排序求出最低平均工资的部门编号②查询部门信息`  
> SELECT \* FROM departments  
> WHERE department\_id=(  
> SELECT department\_id  
> FROM employees  
> GROUP BY department\_id  
> ORDER BY AVG(salary) ASC  
> LIMIT 1  
> );  
> `3、思路:①查询各部门平均工资;②查询最低平均工资的部门编号`  
> SELECT d.\* ,ag  
> FROM departments d  
> JOIN (  
> SELECT AVG(salary) ag,department\_id  
> FROM employees  
> GROUP BY department\_id  
> ORDER BY AVG(salary) ASC  
> LIMIT 1  
> ) a  
> ON d.department\_id=a.department\_id;  
> `4、思路:①查询job的平均工资最高的job_id;②查询job信息`  
> SELECT \*  
> FROM jobs  
> WHERE 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、思路:①查询各部门最高工资中最低的那个部门id;②查询①部门的最低工资`啊  
> SELECT MIN(salary) FROM employees WHERE department\_id=(  
> SELECT department\_id  
> FROM employees  
> GROUP BY department\_id  
> ORDER BY MAX(salary) ASC  
> LIMIT 1  
> );  
> `8、思路:①查询平均工资最高的部门编号;②将employees和departments连接查询,筛选条件是①`  
> SELECT last\_name, d.department\_id, email,salary  
> FROM employees e  
> INNER JOIN departments d ON d.manager\_id=e.employee\_id  
> WHERE d.department\_id=(  
> SELECT department\_id  
> FROM employees  
> GROUP BY department\_id  
> ORDER BY AVG(salary) DESC  
> LIMIT 1  
> )

# 八、分页查询 #

应用场景:当要显示的数据一页显示不全,需要分页提交sql请求。

语法:  
`select 查询列表`  
`from 表`  
`[join type join 表2`  
`on 连接条件`  
`where 筛选条件`  
`group by 分组字段`  
`having 分组后的筛选`  
`order by]`  
`limit offset,size;`

`offset`:从0开始的起始索引,若省略默认从第一条开始  
`size`:要显示的条目个数

执行顺序:`from > join > on > where > group by > having > select > order by > limit (limit语法和执行都在最后)`

limit分页公式：

`select * from 表 limit (page-1)*size,size;`(要显示的页数为page,每页的条目数为size)

# 1.查询前五条员工信息
    SELECT * FROM employees LIMIT 5;
    
    # 2.查询第11条到第25条
    SELECT * FROM employees LIMIT 10,15
    
    # 3.有奖金的员工信息,并且显示出工资较高的前10名
    SELECT * 
    FROM employees
    WHERE commission_pct IS NOT NULL
    ORDER BY salary
    LIMIT 10;

# 九、联合查询 #

定义：将多条查询语句的结果合并成一个结果。

语法：`查询语句1 union 查询语句2 unin ... 查询语句n`

应用场景：当要查询的结果来自多个没有连接关系的表,但查询的信息一致时，最适合使用union。

注意事项：

*  要求多条查询语句的查询列数是一致的
 *  要求多条查询语句查询的每一列的类型和顺序最好一致.
 *  union默认去重,如果使用union all可以关闭去重
 *  大多数情况下，union的查询效率比or高。

# 1.查询部门编号>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%';

[MySQL]: https://guoqianliang.blog.csdn.net/article/details/106447977
[MySQL_ _ DQL]: https://guoqianliang.blog.csdn.net/article/details/106456675
[MySQL_ _ DML]: https://guoqianliang.blog.csdn.net/article/details/106560523
[MySQL_ _ DDL_]: https://guoqianliang.blog.csdn.net/article/details/106571679
[MySQL_ _ TCL]: https://guoqianliang.blog.csdn.net/article/details/106589677
[MySQL_ _]: https://guoqianliang.blog.csdn.net/article/details/106604374
[MySQL_ _ 1]: https://guoqianliang.blog.csdn.net/article/details/106627351
[by]: https://guoqianliang.blog.csdn.net/
[https_pan.baidu.com_s_1Qp81tD4htQaK5bO0k1sxQQ]: https://pan.baidu.com/s/1Qp81tD4htQaK5bO0k1sxQQ#list/path=/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200531160610826.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200531163349219.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200531162931840.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200531172749681.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/2020060100003748.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA==,size_16,color_FFFFFF,t_70
[20200601012311344.png]: https://img-blog.csdnimg.cn/20200601012311344.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200601181239917.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200602230010348.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200602121148129.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200603003035578.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20200623105912321.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/20200603140107595.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 11]: https://img-blog.csdnimg.cn/20200604113440909.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA_size_16_color_FFFFFF_t_70 12]: https://img-blog.csdnimg.cn/20200604180109705.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY5MTA1OA==,size_16,color_FFFFFF,t_70