1. 案例库表
2. DQL语言（数据查询）
2.1 基础查询
-- 进入指定的库
USE myemployees;
DESC employees;
-- 使用SELECT查询字段
# 1.查询表中的单个字段
SELECT last_name FROM employees;
# 2.查询表中的多个字段(字段名顺序可以无序)
SELECT last_name,salary,email FROM employees;
# 3.查询表中的所有字段(也可以在菜单栏双击字段名逐个添加)
SELECT * FROM employees;
-- 使用SELECT查询常量，表达式，函数
# 1.查询常量值(常量只要是字符都是字符型，使用单引号括起来)
SELECT 100;
SELECT 'john';
# 2.查询表达式
SELECT 100%98; # 2
# 3.查询函数
SELECT VERSION();# 5.5.15
-- 为字段起别名
/*
起别名的好处：
① 便于理解
② 如果要查询的字段有重名的情况，可以用别名区分
*/
# 方式一：使用AS
SELECT 100%98 AS 结果;
SELECT last_name AS 性,first_name AS 名 FROM employees;
# 方式二：使用空格
SELECT last_name 姓,first_name 名 FROM employees;
-- 案例：查询salary，显示结果为out put
#别名有特殊符号如空格，应将别名用""括起来
SELECT salary AS "out put" FROM employees;
-- 去重(DISTINCT关键字的使用)
#案例：查询员工表中涉及到的部门编号(有很多编号重复)
SELECT department_id FROM employees;
#对部门编号去重
SELECT DISTINCT department_id FROM employees;
-- +号的作用
/*
mysql中的+号只有一个功能：运算符
如：① SELECT 100+90; 两个操作数都为数值型则做加法运算
② SELECT '123'+90; 其中一方为字符型，试图将字符型
转换成数值型，如果转换成功则继续做加法运算。
③ SELECT 'abc'+90;其中一方为字符型，试图将字符型
转换成数值型，如果转换失败则将字符型转换为0。
④ SELECT null+10;只要其中一方为null，则结果为null。
*/
-- CONCAT(参数列表)函数 拼接多个字段
#例如：
SELECT CONCAT('a','b','c') AS 结果;
#案例：查询员工名和姓连接成一个字段并显示为姓名
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;
#案例：显示出表employees的前三个列，各个列之间用逗号连接，列头显示为OUT_PUT
SELECT
CONCAT(first_name,',',last_name,',',job_id,',',IFNULL(commission_pct,0)) AS OUT_PUT
FROM
employees;
2.2 条件查询
-- 按条件表达式筛选
#案例1：查询工资>12000的员工信息
SELECT
*
FROM
employees
WHERE salary > 12000;
#案例二：查询部门编号不等于90号的员工名和部门编号
SELECT
last_name,
department_id
FROM
employees
WHERE department_id <> 90 ;
-- 按逻辑表达式筛选
#案例一：查询工资在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 ;
-- 模糊查询
# 1. like
/*
特点：
① 一般和通配符搭配使用
②
MySQL5.5以上版本既可以判断字符型，又可以判断数值型
通配符：
① % ：任意多个字符(包括0个字符，空字符和null不一样)
② _ ：任意单个字符
*/
#案例1：查询员工名中包含字符a的员工信息
SELECT
*
FROM
employees
WHERE last_name LIKE '%a%' ;
#案例2：查询员工名中第三个字符为t，第五个字符为b的员工名和工资
SELECT
last_name,
salary
FROM
employees
WHERE last_name LIKE '__t_b%' ;
#案例3：查询员工名中第二个字符为_的员工名
SELECT
last_name
FROM
employees
#where last_name like '_\_%' ;# 可以使用转义字符\
WHERE last_name LIKE '_$_%' ESCAPE '$';#随意指定转义字符
#案例4:查询部门编号(int类型)为100以上的员工信息
SELECT
*
FROM
employees
WHERE department_id LIKE '1__' ;
# 2. between and
/*
特点：
① 可以提高代码简洁度
② 包含临界值
③ 两个临界值不能调换位置
*/
# 案例1：查询员工编号在100到120之间的员工信息
SELECT
*
FROM
employees
#where employee_id >= 100 and employee_id <= 120 ;
WHERE employee_id BETWEEN 100 AND 120 ;#等价于上行代码
# 3. in
/*
含义：判断某字段的值是否属于in列表中的某一项
特点：
① 提高了代码简洁度
② in列表的值类型必须统一或兼容('123'可以转化为123)
③ in列表中的值不支持通配符
*/
# 案例1：查询员工的工种编号是IT_PROT,AD_VP,AD_PRES中的一个
# 的员工名和工种编号
SELECT
last_name,
job_id
FROM
employees
#where job_id = 'IT_PROT' or job_id = 'AD_VP' or job_id = 'AD_PRES' ;
WHERE job_id IN('IT_PROT','AD_VP','AD_PRES') ;# 等价于上行代码
# 4. is null / is not null
/*
注意：① =或<>不能判断null值
② 使用is null或is not null判断null值
*/
#案例1：查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE commission_pct IS NULL ;#null值不能用=运算符判断
#案例2：查询有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE commission_pct IS NOT NULL ;
-- 安全等于: <=>
#
优点：既可以判断null值，也可以判断普通数值
# 案例1：查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE commission_pct <=> NULL ;
# 案例2：查询工资为12000的员工信息
SELECT
last_name,
salary
FROM
employees
WHERE salary <=> 12000;
#测试题1：查询员工号为176的员工的姓名，部门和年薪
SELECT
last_name,
department_id,
salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM
employees ;
#测试题2：查询没有奖金且工资小于18000的salary，last_name
SELECT
last_name,
salary
FROM
employees
WHERE commission_pct IS NULL AND salary < 18000 ;
#测试题3：查询employees表中，job_id不为‘IT’或则工资为12000的员工信息
SELECT
*
FROM
employees
WHERE job_id <> 'IT' OR salary = 12000 ;
#测试题4：查询部门departments表中涉及到了哪些位置编号
SELECT DISTINCT
location_id
FROM
departments ;
#面试题：select * from employees 和
# select * from employees
# where commission_pct like '%%' and last_name like '%%' ;结果是否一样
#答：可能不一样，如果判断的字段有null值就可能不返回该字段是null的员工信息
2.3 排序查询
#案例1：查询员工信息，要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;
# SELECT * FROM employees ORDER BY salary;
#案例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,employee_id DESC ;
-- 测试题
# 查询员工的姓名，部门号和年薪，按年薪降序，按姓名升序
SELECT
CONCAT(last_name,' ',first_name) AS 姓名,
department_id,
salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM
employees
ORDER BY 年薪 DESC,姓名 ASC ;
# 查询工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT
CONCAT(last_name, ' ', first_name) AS 姓名,
salary
FROM
employees
WHERE
salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC ;
# 查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT *,LENGTH(email)
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC ;
2.4 常见函数
-- -- 一.单行函数
-- 字符函数
# 1. length(str):获取参数值的字节个数
SELECT LENGTH('john');
# 2. concat(str1,str2..):拼接字符
SELECT CONCAT(last_name,'_',first_name) FROM employees;
# 3. upper(str),lower(str):对参数值进行大小写转化
SELECT UPPER('abc'),LOWER('CDE');
#案例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name), LOWER(first_name)) AS 姓名
FROM employees ;
# 4. substr() / substring():截取字符
# 重载： ①substr(str,pos)：截取str中从pos索引开始的所有字符
# ②substr(str FROM pos)
# ③substr(str,pos,len)：截取str中从pos开始字符长度为len的字符
# ④substr(strstr FROM pos FOR len)
SELECT SUBSTR('李莫愁赖上了陪展元',7);#SQL中索引从1开始
SELECT SUBSTR('李莫愁赖上了陪展元',7,3);
#案例：姓名中首字母大写，其他字母小写然后_拼接，显示出来
SELECT
CONCAT(
UPPER(SUBSTR(last_name, 1, 1)),
'_',
LOWER(SUBSTR(last_name, 2))
) out_put
FROM
employees ;
# 5. instr(str,substr):用于返回sbstr子串在str中第一次出现的索引
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put ;
# 6. trim():去掉字符前后的空格或指定字符
SELECT TRIM(' 张 翠山 ') AS out_put ;#张 翠山
SELECT TRIM('a' FROM 'aaaa张aa翠山aaa') AS out_put ;#张aa翠山
# 7. lpad(str,len,padstr):用指定的字符实现左填充指定长度，长度不够从右截断
# rpad(str,len,padstr):用指定的字符实现右填充指定长度，长度不够从右截断
SELECT LPAD('殷素素',10,'*') AS out_put ;# *******殷素素
SELECT RPAD('殷素素',10,'*') AS out_put ;# 殷素素*******
SELECT LPAD('殷素素',2,'*') AS out_put ;# 殷素
SELECT RPAD('殷素素',2,'*') AS out_put ;# 殷素
# 8. replace(str,from_str,to_str):替换str中所有的from_str子串为to_str子串
SELECT REPLACE('无忌爱芷若芷若','芷若','赵敏') AS out_put;#无忌爱赵敏赵敏
-- 数学函数
# 1. round(X) / round(X,D)：四舍五入(第二个参数为小数点后保留的位数)
SELECT ROUND(1.65);#2
SELECT ROUND(1.45);#1
SELECT ROUND(1.567);#1.58
# 2. ceil(x):向上取整,返回>=该参数的最小整数
# floor(x) 向下取整，返回<=该参数的最小整数
SELECT CEIL(1.32); #2
SELECT FLOOR(1.99); #1
# 3. truncate(X,D):截断,只保留第二个参数指定的位数
SELECT TRUNCATE(1.699,1);
# 4. mod(n,m):取余
SELECT MOD(10,3);#1
-- 日期函数
# 1. now():返回当前系统的日期+时间
# curdate():返回当前系统日期，不包含时间
# curtiome():返回当前时间，不包含日期
SELECT NOW();#2019-10-29 23:19:35
SELECT CURDATE();#2019-10-29
celect CURTIME();#23:19:35
# 2. 获取指定的部分：年，月，日，小时，分钟，秒
# year(date).. / hour(time)..
SELECT YEAR(NOW()) AS 年;#2019
SELECT YEAR('2019-1-1');#2019
SELECT YEAR(hiredate) AS 年 FROM employees;#hiredate:入职时间
SELECT MONTH(NOW());#10;
SELECT MONTHNAME(NOW());#October
# 3. str_to_date(str,format):将日期格式转换为指定格式的日期
# date_format():将日期转换为字符
#
# 格式符：%Y:4位的年份 %y:2位的年份
#
%m:月份(01，02..11,12) %c:月份(1，2..11,12)
#
%d:日(01,02...)
#
%H:小时(24小时制) %h:小时(12小时制)
#
%i:分钟(00，01..59)
#
%s:秒(00,01..59)
SELECT STR_TO_DATE('9-13-2019','%m-%d-%Y');#2019-09-13
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日')#2019年10月29日
# 案例1：查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate = '1992-4-3';#情况一
SELECT * FROM employees
WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');#情况二
# 案例2：查询有奖金的员工名和入职日期(日期格式：xx月/xx日/xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日/%y年')
FROM employees
WHERE commission_pct IS NOT NULL;
-- 其他函数
# 1. version():查看当前版本号
SELECT VERSION();#5.5.15
# 2. database():查看当前
数据库
SELECT DATABASE();#employees
# 3. user():查看当前用户
SELECT USER();#root@localhost
# 4. password('字符'):返回该字符的密码形式
SELECT PASSWORD('adsfsdf');
-- 流程控制函数
# 1. if(expr1,expr2,expr3)：实现if-else的效果,相当于三元运算符
SELECT IF('10>5','大','小');#大
SELECT
last_name,
commission_pct,
IF(commission_pct IS NULL,'没奖金','有奖金')
FROM employees;
# 2. case使用一：配合select语句充当表达式时实现switch-case效果
/*
语法格式：case 要判断的字段或表达式
when 常量1 then 要显示的值1(case作为语句时放:语句1;)
when 常量2 then 要显示的值2(case作为语句时放:语句2;)
...
(else) 要显示的值n(case作为语句时放:语句n;)
end
*/
# 案例：查询员工的工资，要求：
# 部门号=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;
# 3. case使用二：配合select语句充当表达式时实现类似于多重if的效果
/*
语法格式：case
when 条件1 then 要显示的值1(case作为语句时放:语句1;)
when 条件2 then 要显示的值2(case作为语句时放:语句2;)
...
(else) 要显示的值n(case作为语句时放:语句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;
-- 单行函数练习题
# 1. 查询员工号，姓名，工资，以及工资提高20%之后的结果(new salary)
SELECT employee_id,last_name,salary,salary*1.2 AS "new salary"
FROM employees;
# 2. 将员工的姓名按首字母排序，并写出姓名的长度(length)
SELECT last_name,LENGTH(last_name) AS "length"
FROM employees
ORDER BY SUBSTR(last_name,1,1);
# 3.做一个查询，产生以下结果
/*
例如：<last_name> earns <salary> 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 last_name = 'K_ing';
# 4. 使用case-when按照下面的条件
/*
job grade
AD_PRES A
ST_MAN B
IT_PROG C
*/
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'
END AS 'grade'
FROM employees;
2.5 分组函数
-- 分组函数的简单使用
# 1. sum(expr):求和
SELECT SUM(salary) FROM employees;#691400.00
# 2. avg(expr):求平均
SELECT AVG(salary) FROM employees;#6461.682243
# 3. max(expr):求最大值
SELECT MAX(salary) FROM employees;#24000.00
# 4. count(expr):计算个数
SELECT COUNT(salary) FROM employees;#107
# 5. 多个函数一起写
SELECT SUM(salary) 和,AVG(salary) 平均 FROM employees;
# 6. 与其他函数嵌套
SELECT ROUND(AVG(salary),2) 平均 FROM employees;#6461.68
-- 分组函数的特点测试
# 1. sum,avg一般用于处理数值型，max,min,count可以处理任何类型
SELECT SUM(last_name),AVG(last_name) FROM employees;#0 0(无意义)
SELECT SUM(hiredate),AVG(hiredate) FROM employees;#23.. 234..(无意义)
SELECT MAX(last_name),MIN(last_name) FROM employees;#Zlotkey Abel
SELECT MAX(hiredate),MIN(hiredate) FROM employees;#2016-0.. 1992-04..
SELECT COUNT(last_name) FROM employees;#107
# 2. 分组函数会忽略null值
SELECT AVG(commission_pct),SUM(commission_pct)/35,COUNT(salary)
FROM employees;#0.222857 0.222857 107
# 3. 分组函数和distinct搭配实现去重
SELECT SUM(DISTINCT salary),SUM(salary)
FROM employees;#397900.00 691400.00
# 4. count()函数的详细介绍
# 使用count(*)统计行数
# 使用count(1)在表中加一列常量值，统计常量值的个数(统计行数)
# 效率问题：
# MYISAM存储引擎下(
MySQL 5.5以前)，count(*)效率最高
# INNODB存储引擎下，count(*)和count(1)效率基本一致，比count(字段)高
SELECT COUNT(salary),COUNT(*) FROM employees;#107 107
SELECT COUNT(1) FROM employees;#107
# 5. 和分组函数一同查询的字段有限制
SELECT AVG(salary),employee_id FROM employees;
-- 测试题
# 1. 查询员工表中的最大入职时间和最小入职时间的相差天数(别名:DIFFRENCE)
# 参考一个新的日期函数datediff(expr1,expr2)求两个日期之差，返回天数
# select datediff(now(),'1998-07-30');#7762
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFRENCE
FROM employees;#8735
# 2. 查询部门编号为90的员工个数
SELECT COUN、T(*) AS 个数
FROM employees
WHERE department_id = 90;
2.6 分组查询
-- 简单的分组查询
# 案例1：查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;
# 案例2：查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
-- 添加分组前的筛选条件的分组查询
# 案例1：查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
# 案例2：查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
-- 添加分组后的筛选条件的分组查询
# 案例1：查询哪个部门的员工个数大于2？
# ① 查询每个部门的员工个数
# ② 根据①的结果筛选，查询哪个部门的员工个数大于2
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 2;
# 案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
# ① 查询每个工种有奖金的员工的最高工资
# ② 根据①的结果筛选，查询哪个最高工资>12000
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000;
# 案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，
# 以及其最低工资
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000 ;
-- 除了按单个字段分组外，还可以按表达式或函数分组
# 案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
SELECT COUNT(*),LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*) > 5;
-- 按多个字段进行分组
# 案例：查询每个部门每个工种的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;
-- 添加排序后的分组查询
# 案例：查询每个部门每个工种的平均工资，并且按平均工资的高低显示
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY AVG(salary) DESC;
-- 练习题
# 练习1：查询员工最高工资和最低工资的差距(别名:DIFFERENCE)
SELECT MAX(salary)-MIN(salary) DIFFERENCE
FROM employees;
# 练习2：查询各个管理者手下员工的最低工资，其中最低工资不能低于6000
# 没有管理者的员工不计算在内
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000;
2.7 连接查询
-- 连接查询一般会为表起别名
/*
好处：① 提高语句的简洁度
② 区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/
#案例：查询员工名，工种号，工种名
SELECT last_name,e.job_id,job_title
FROM employees AS e,jobs AS j
WHERE e.job_id = j.job_id;
-- -- 一.sql92标准
-- 1.内连接之等值连接
#案例：查询员工名和对应的部门名(简单的等值连接)
SELECT last_name,department_name
FROM employees e,departments d
#案例：查询城市名中第二个字符为o的部门名和城市名(加筛选条件)
SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';
#案例：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资(加分组)
SELECT MIN(salary),d.department_name,d.manager_id
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`
AND commission_pct IS NOT NULL
GROUP BY d.department_name,d.manager_id;
#案例：查询每个工种的工种名和员工的个数，按员工个数降序(加排序)
SELECT COUNT(*),j.job_title
FROM jobs j,employees e
WHERE j.`job_id` = e.`job_id`
GROUP BY j.`job_title`
ORDER BY COUNT(*) DESC;
#案例：查询员工名，部门名和所在的s开头的城市(三表连接)
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`
AND city LIKE 's%';
-- 2.内连接之非等值连接
#案例：查询员工的工资和工资级别
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.`department_id` = m.`employee_id`;
-- 内连接练习题
#案例1：显示所有员工的姓名，部门号和部门名称
SELECT last_name,department_name,d.department_id
FROM employees e,departments d
WHERE e.`department_id`= d.`department_id`;
#案例2：查询90号部门员工的job_id和90号部门的location_id
SELECT job_id,d.location_id
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.department_id=90;
#案例3：选择city在Toronto工作的员工的last_name,job_id,
# department_id,department_name
SELECT last_name,job_id,e.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 city='Toronto';
#案例4：查询每个工种，每个部门的部门名，工种名和最低工资
SELECT department_name,job_title,MIN(salary)
FROM employees e,departments d,jobs j
WHERE e.`department_id`=d.`department_id`
AND e.`job_id`=j.`job_id`
GROUP BY department_name,job_title;
#案例5：查询每个国家下的部门个数大于2的国家编号
SELECT COUNT(*),country_id
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY country_id
HAVING COUNT(*)>2;
#案例6：选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号
SELECT e.last_name,e.employee_id,m.last_name,m.employee_id
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
-- -- 二.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%';
#案例3：查询部门个数>3的城市名和部门个数(添加分组+筛选)
SELECT city,COUNT(*)
FROM locations l
INNER JOIN departments d
ON l.`location_id`=d.`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;
-- 2.内连接之非等值连接
#查询员工的工资级别
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
-- 3.内连接之自连接
#案例：查询员工名和上级的名称
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`department_id` = m.`employee_id`;
-- 外连接
/*
应用场景：用于查询一个表中有，另一个表中没有的数据
特点：
① 外连接的查询结果为主表中的所有记录。如果从表
中有与主表匹配的，则显示匹配的值,否则显示为null
=> 外连接查询结果=内连接结果+主表中有从表中没有的记录
② 左外连接：left join左边的是主表
右外连接：right join右边的是主表
③ 左外和右外交换两个表的顺序，可以实现同样的效果
注意：全外连接=内连接的结果+表1中有表2没有的记录+表2中有表1没有的记录
*/
#案例：查询哪个部门没有员工
#左外
SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;
#右外
SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;
-- 交叉连接
2.8 子查询
-- where或having后的子查询
/*
特点：① 子查询都会放在小括号内
② 子查询一般放在条件的右侧
③ 标量子查询，一般搭配单行操作符使用
如：> , < , >= , <= , = , <>
④ 列子查询，一般搭配多行操作符使用
如:IN , ANY/SOME , ALL
-> IN/NOT IN：等于列表中的任意一个【可以用= any替换】
-> ANY/SOME：和子查询返回的某一个值比较
-> ALL：和子查询返回的所有值比较【可以用min函数替换】
⑤ 子查询的执行优先于主查询执行，主查询的条件用到了
子查询的结果
*/
# 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. 列子查询(多行子查询)
# 案例1：返回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)
);
# 案例2：返回其他工种中比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';
# 案例3：返回其他工种中比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'
)
# 3. 行子查询(多行多列或一行多列)
# 有局限性，要求多个字段用同一个操作符判断
# 案例1：查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE(employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
-- 放在select后面的子查询(仅支持标量子查询)
# 案例1：查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id=d.`department_id`
) AS 员工个数
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;
) AS department_name;
-- 放在from后面的子查询
/*
注意：将子查询结果充当一张表，要求必须起别名
*/
# 案例1：查询每个部门的平均工资的工资等级
#步骤一
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
#步骤二
SELECT * FROM job_grades;
#步骤三
SELECT ag_dep.*,g.grade_level
FROM (
SELECT AVG(salary) AS ag,department_id
FROM employees
GROUP BY department_id
) AS ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
-- 放在exists后面的子查询(相关子查询)
/*
exists语法：exists(完整的查询语句)
exists的结果:0/1
如：SELECT EXISTS (SELECT employee_id FROM employees);#1
*/
# 案例1：查询有员工名的部门名
#使用exists的方式
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.`department_id`=e.`department_id`
);
#使用in的方式
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
SELECT department_id
FROM employees
);
# 子查询练习题
#案例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 AVG(salary),department_id
FROM employees
GROUP BY department_id;
# 步骤二：连接①的结果集和employees表，进行筛选
SELECT employee_id,last_name,salary,e.`department_id`
FROM employees e
INNER JOIN (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) AS ag_dep
ON e.`department_id`=ag_dep.department_id
WHERE salary>ag_dep.ag;
# 案例4：查询和姓名中包含字母u的员工在相同部门的员工号和姓名
SELECT employee_id,last_name
FROM employees e
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 department_id
FROM departments
WHERE location_id=1700
);
# 案例6：查询管理者是K_ing的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE manager_id IN(
SELECT employee_id
FROM employees
WHERE last_name='K_ing';
);
# 案例7：查询工资最高的员工的姓名
SELECT CONCAT(last_name,'.',first_name) AS 姓名,salary
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
);
-- 子查询经典案例题
# 案例1：查询平均工资最低的部门信息 ★
# 方式1：
# 步骤一：各部门的平均工资
SELECT AVG(salary)
FROM employees
GROUP BY department_id;
# 步骤二：查询步骤一结果上的最低平均工资
SELECT MIN(ag)
FROM (
SELECT AVG(salary) AS ag
FROM employees
GROUP BY department_id
) AS ag_dep;
# 步骤三：查询哪个部门的平均工资等于②
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) =(
SELECT MIN(ag)
FROM (
SELECT AVG(salary) AS ag
FROM employees
GROUP BY department_id
) AS ag_dep
);
# 步骤四：查询部门信息
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) AS ag
FROM employees
GROUP BY department_id
) AS ag_dep
)
);
# 方式二：
# 步骤一：各部门的平均工资
SELECT AVG(salary)
FROM employees
GROUP BY department_id;
# 步骤二：求出最低平均工资的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;
# 步骤三：查询部门信息
SELECT *
FROM departments
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
);
# 案例2：查询平均工资最低的部门信息和该部门的平均工资
# 步骤一：各部门的平均工资
SELECT AVG(salary)
FROM employees
GROUP BY department_id;
# 步骤二：求出最低平均工资的部门编号
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;
# 步骤三：查询部门信息和该部门的平均工资(使用表子查询和连接)
SELECT d.*,ag
FROM departments d
INNER JOIN(
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
)AS ag_dep
ON d.`department_id`=ag_dep.department_id;
# 案例3：查询平均工资最高的job信息
SELECT *
FROM jobs
INNER JOIN (
SELECT AVG(salary) ag,job_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
) AS ag_dep
ON jobs.`job_id`= ag_dep.`job_id`;
# 案例4：查询平均工资高于公司平均工资的部门有哪些？
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>(
SELECT AVG(salary)
FROM employees
);
# 案例5：查询出公司中所有manager的详细信息
SELECT *
FROM employees
WHERE employee_id IN(
SELECT DISTINCT manager_id
FROM employees
);
# 案例6：各个部门中 最高工资中最低的那个部门的 最低工资是多少？
# 步骤一：查询各部门的最高工资中最低的
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1;
#步骤二：查询步骤一结果的哪个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1
);
# 案例7：查询平均工资最高的部门的manager的详细信息：
# last_name,department_id,email,salary
#步骤一：查询平均工资最高的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1;
#步骤二：将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
);
2.9 分页查询
# 案例1：查询前5条员工信息
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 DESC
LIMIT 10;
2.10 union联合查询
# 案例：查询中国用户表中男性的信息以及外国用户表中男性的信息
SELECT id,cname,csex FROM t_ca WHERE csex='男'
UNION
SELECT t_id,tName,tGender FROM t_ua WHERE tGender='male';
2.11 总结
