MySQL学习笔记——DQL

MySQL源码

库和表

myemployees表

girls表

student表

DQL

基础查询

#进阶1：基础查询
/*
语法：
select 查询列表 from 表名;

特点：
1.查询列表可以是：表中的字段、常量值、表达式
2.查询结果是一个虚拟的表格
*/
#每次查询之前，要先打开对应的库，SQLyog软件的左上方也可以手动选择打卡哪个库
USE myemployees ;

#1.查询表中的单个字段
SELECT last_name FROM employees;

#2.查询表中的多个字段
SELECT last_name,salary,email FROM employees;

#3.查询表中的所有字段

#方式一：
SELECT 
  `employee_id`,
  `first_name`,
  `last_name`,
  `email`,
  `phone_number`,
  `job_id`,
  `salary`,
  `commission_pct`,
  `manager_id`,
  `department_id`,
  `hiredate` 
FROM
  employees ;

#方式二：
SELECT * FROM employees;

#4.查询常量值
SELECT 100;
SELECT 'john';#SQL不区分字符和字符串，统一用单引号

#5.查询表达式
SELECT 100*98;

#6.查询函数
SELECT VERSION();

#7.起别名
/*
好处：
1.便于理解
2.若要查询的字段有重名，使用别名可以区分出来
注意：别名在select内部时不可重复使用，select外部的语句可以使用
*/
#方式一：AS
SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;

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

#案例：查询salary，显示结果为out put
#分析：别名中含有特殊字符时，例如空格、#时，使用双引号将别名引起来，
#单引号也可也，但建议使用双引号
SELECT salary AS "out put" FROM employees;

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

#9.+的作用
/*
java中：
1.运算符：两个操作数都是数组型
2.连接符：主要有一个是字符串，结果就为字符串

MySQL中：
只表示运算符
select 100+90; 190 两个操作数都是数值型，做加法运算
select '123'+90; 213 其中一方为字符型，试图将字符型转换成数值型，
		 若转换成功，做加法运算
select 'join'+90; 90 如果转换失败，则将字符型转换为0，再做加法运算
select null+其他; null 只要其中一方为null，结果肯定为null（concat()也是）
*/
#案例：查询员工的姓和名，连接为一个字段，并显示 姓名
SELECT last_name+first_name AS 姓名 FROM employees;

#拼接函数 concat(str1,str2...);
SELECT CONCAT('a','b','c') AS 结果;
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;

#练习：显示出表employees的所有列，用‘,'隔开，列头显示out_put（以前某几列为例）
SELECT 
  CONCAT(
    `first_name`,
    ',',
    `last_name`,
    ',',
    `email`,
    `commission_pct`
  ) AS out_put 
FROM
  employees ;
#结果为null，原因是commission_pct中有些值是null，在拼接其他值时结果为null
#ifnull(expr1,expr2)将expr1中值为null的用expr2代替
SELECT 
  IFNULL(commission_pct, 0) AS 奖金率,
  commission_pct 
FROM
  employees ;
 
#上述拼接可以修改成：
SELECT 
  CONCAT(
    `first_name`,
    ',',
    `last_name`,
    ',',
    `email`,
    IFNULL(`commission_pct`,0)
  ) AS out_put 
FROM
  employees ;

条件查询

#进阶2：条件查询
/*
语法：
select 查询列表 from 表名 where 筛选条件;

分类：
一、按条件表达式筛选
	条件运算符：> < = != <> >= <=
二、按逻辑表达式筛选
	作用：用于连接条件表达式
	逻辑运算符：&& || ! and or not
三、模糊查询
	like
	between and
	in
	is null/is not null
*/

#一、按条件表达式筛选
#案例1：查询工资>12000的员工信息
SELECT * FROM employees WHERE salary>12000;

#案例2：查询部门编号不等于90的员工名和部门编号
SELECT 
  last_name,
  first_name,
  department_id 
FROM
  employees 
WHERE department_id <> 90 ;

#二、按逻辑表达式筛选
#案例1：查询工资在10000和20000之间的员工名、工资和奖金
SELECT 
  `last_name`,
  `first_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` <= 150
  ) 
  OR (salary > 15000) ;

#三、模糊查询
#1.like
/*
特点：
1.一般和通配符搭配使用
	通配符：
	% 任意多个字符，包含0个字符
	_ 任意单个字符，不包含0个字符
	
	转义字符：
	\ 
	escape ''自定义(建议使用)
2.通配符不能表示null
*/
#案例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 '_\_%';

#自定义转义字符
SELECT 
  last_name 
FROM
  employees 
WHERE last_name LIKE '_$_%' ESCAPE '$';

#between and
/*
1.使用between and可以提高语句的简洁度
2.包含临界值，即>=,<=
3.两个临界值不能调换顺序
*/
#案例1：查询员工编号在100到120之间的员工信息
SELECT 
  * 
FROM
  employees 
WHERE employee_id >= 100 
  AND employee_id <= 120 ;
#----------------------------
SELECT 
  * 
FROM
  employees 
WHERE employee_id BETWEEN 100 
  AND 120 ;

#in
/*
含义：用于判断某字段的值是否属于in列表中的某一项
特点：
	1.提高语句的简洁度
	2.in列表的值类型必须与被查询字段的的值类型必须统一或兼容
	3.不支持通配符
*/
#案例1：查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中其一的员工名和工种编号
SELECT 
  last_name,
  job_id 
FROM
  employees 
WHERE job_id = 'IT_PROG' 
  OR job_id = 'AD_VP' 
  OR job_id = 'AD_PRES' ;

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

#4.is null/is not null
/*
1. =或<>不能判断null
   is null或is not null可以判断null
   
*/
#案例1：查询没有奖金的员工名和奖金率
SELECT 
  last_name,
  `commission_pct` 
FROM
  `employees` 
WHERE `commission_pct` IS NULL ;

#案例1：查询有奖金的员工名和奖金率
SELECT 
  last_name,
  `commission_pct` 
FROM
  `employees` 
WHERE `commission_pct` IS NOT NULL ;

#补充：安全等于<=>
/*
可以判断普通值也可以判断null
可读性较差
建议使用is null/is not null
*/
#案例1：查询没有奖金的员工名和奖金率
SELECT 
  last_name,
  `commission_pct` 
FROM
  `employees` 
WHERE `commission_pct` <=> NULL ;

#案例2：查询工资为12000的员工信息
SELECT 
  *
FROM
  `employees` 
WHERE salary <=> 12000 ;

#练习：查询员工编号为176的员工的姓名、部门号以及年薪
SELECT 
  CONCAT(last_name, ' ', first_name) AS 姓名,
  employee_id,
  salary * 12 * (1+ IFNULL(commission_pct, 0)) AS 年薪 
FROM
  employees 
WHERE employee_id = 176 ;

排序查询

#进阶3：排序查询
/*
语法：
select 查询列表 
from 表名 
where 筛选条件
order by 排序列表 asc/desc;
特点：
1.asc可以省略,不写默认升序
2.order by子句支持单个字段，多个字段，函数，表达式，别名
3.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：按函数排序；按姓名的长度显示员工的姓名和工资
#长度函数：length(str)
SELECT 
  CONCAT(last_name, first_name) AS 姓名,
  salary 
FROM
  employees 
ORDER BY LENGTH(姓名) ;

#案例6：查询员工信息，先按工资升序排序，再按员工编号降序排序
SELECT 
  * 
FROM
  employees 
ORDER BY salary ASC,
  employee_id DESC ;#先按工资升序排，工资相同时，按照编号降序排
#练习
#1.查询员工的姓名、部门编号以及年薪，并按照年薪降序，姓名升序
SELECT 
  last_name,
  department_id,
  salary * 12 * (1+ IFNULL(`commission_pct`, 0)) AS 年薪 
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 ;

常见函数之单行函数

USE myemployees;
#进阶4-1：常见函数之单行函数
/*
概念：类似于java中的方法
好处：1.隐藏实现细节 2.提高代码的复用性
调用：select 函数名(实参列表) [from 表名]
特点：1.叫什么(函数名) 2.干什么(函数功能)
分类：1.单行函数，如concat()、isnull()、length()
	（1）字符函数
	（2）数学函数
	（3）日期函数
	（4）其他函数
	（5）流程控制函数
      2.分组函数：做统计使用，又称统计函数、聚合函数、组函数等

*/

#单行函数
#一、字符函数
/*
1.length(str) 获取str字节长度
2.concat(str1, str2,...) 拼接
3.upper(str) lower(str) 大小写
4.substr()截取，注意：索引从1开始
	(1)substr(str,pos) 截取从pos开始到结尾的子串
	(2)substr(str,pos,len) 截取从pos开始长度为len的子串
5.instr(str,substr) 返回substr在str中第一次出现的位置
6.trim()
	(1)trim(str) 去除str前后的空格
	(2)trim('char' from str) 去除str中前后指定的字符
7.lpad(str,len,padstr) 用padstr左填充str，最终长度为len
		       如果str长度小于len，则从左到右截取
		       长度为len的字符串
8.rpad(str,len padstr) 右填充，与lpad()相同
9.replace(str,from_str,to_str) 用to_str替换str中的所有from_str
*/
#案例1：姓名的首字母大写，其他小写，然后再拼接
SELECT 
  CONCAT(
    UPPER(SUBSTR(last_name, 1, 1)),
    '_',
    LOWER(SUBSTR(last_name, 2))
  ) AS output 
FROM
  employees ;

#案例2：去除前后字符a
SELECT TRIM('a' FROM 'aaa1a2aaa3aa') AS output ;

#二、数学函数
/*
1.round(x) 四舍五入，round(x,D) 四舍五入保留D位小数
2.ceil(x) 向上取整
3.floor(x) 向下取整
4.truncate(x,D) 截断，只保留小数点后D位，其余位之间丢弃
5.mod(x) 取模
*/

#三、日期函数
/*
1.now() 返回当前系统日期 + 时间
2.curdate() 返回当前系统日期
3.curtime() 返回当前系统时间
4.year(date),month(date),day(date),
  hour(date),minute(date),second(date)
5.str_to_date:将日期格式的字符转换成指定格式的日期	      
	      %Y 四位年份
	      %y 两位年份
	      %m 月份：01,02,...,12
	      %c 月份：1,2,...,12
	      %d 日期: 01,02,...
	      %H 时间：24小时制
	      %h 时间：12小时制
	      %i 分钟：00,01,...,59
	      %s 秒：00,01,...,59
	      select str_to_date('9-13-1999','%m-%d-%Y'); 1999-09-13
6.date_format() 将日期转换成字符
	      select date_format('2018/6/6','%Y年%m月%d日'); 2018年6月6日
*/
#案例1：查询当前年份
SELECT YEAR(NOW());

#案例2：查询员工入职年份
SELECT 
  YEAR(hiredate),
  MONTH(hiredate),
  DAY(hiredate) 
FROM
  employees ;#使用到了字段，就要from表			

#案例3：查询入职时间为1992-4-3的员工信息
SELECT 
  * 
FROM
  employees 
WHERE hiredate = STR_TO_DATE('1992-4-3', '%Y-%m-%d') ;

#案例4：
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') AS output;

#案例5：查询有奖金的员工名和入职日期（xx月/xx日 xx年）
SELECT 
  last_name,
  DATE_FORMAT(hiredate, '%m月/%d日 %y年') 
FROM
  employees 
WHERE `commission_pct` IS NOT NULL ;

#四、其他函数
/*
1.version() 查看MySQL版本
2.database() 查看当前数据库
3.user() 查看当前用户
*/
#五、流程控制函数
/*
1.if(expr1,expr2,expr3) 若expr1为true，则返回expr2，否则返回expr3
2.case：
	(1)switch case：
	case 要判断的字段或表达式
	when 常量1 then 要显示的语句1;或值1
	when 常量2 then 要显示的语句2;或值2
	...
	else 要显示的语句2;或值2
	end
	
	(2)多重if：
	case
	when 条件1 then 要显示的语句1;或值1
	when 条件2 then 要显示的语句2;或值2
	...
	else 要显示的语句n;或值n
	end
*/
#案例1：查询有无奖金的员工名，并分别为有无奖金添加备注
SELECT 
  last_name,
  IF(
    `commission_pct` IS NOT NULL,
    '有奖金',
    '没奖金'
  ) AS 备注 
FROM
  employees ;

#案例2：查询员工的工资
/*
要求：
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资
*/
SELECT 
  department_id,
  salary AS 原始工资,
  CASE
    department_id 
    WHEN department_id = 30 
    THEN salary * 1.1 
    WHEN department_id = 40 
    THEN salary * 1.2 
    WHEN department_id = 50 
    THEN salary * 1.3 
    ELSE salary 
  END AS 新工资 
FROM
  employees ;

#案例3：查询员工的工资情况
/*
要求：
工资>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.显示系统时间（日期+时间）
SELECT NOW();

#2.查询员工号、姓名、工资，以及工资提高20%后的结果
SELECT 
  `employee_id`,
  `last_name`,
  `salary`,
  salary * 1.2 AS 新工资 
FROM
  employees ;

#3.将员工的姓名按首字母排序，并写出姓名的长度
SELECT 
  last_name,
  LENGTH(last_name) AS 长度
FROM
  employees 
ORDER BY SUBSTR(last_name, 1, 1) ;

#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
    WHEN job_id = 'AD_PRES' 
    THEN 'A' 
    WHEN job_id = 'ST_MAN' 
    THEN 'B' 
    WHEN job_id = 'IT_PROG' 
    THEN 'C' 
    WHEN job_id = 'BA_PRE' 
    THEN 'D' 
    WHEN job_id = 'ST_CLERK' 
    THEN 'E' 
  END AS grade 
FROM
  employees 
WHERE job_id = 'AD_PRES' ;

#-------------------------
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 job_id = 'BA_PRE' 
    THEN 'D' 
    WHEN job_id = 'ST_CLERK' 
    THEN 'E' 
  END AS grade 
FROM
  employees
WHERE job_id = 'AD_PRES' ;

常见函数之分组函数

USE myemployees;
#进阶4-2：常见函数之分组函数
/*
分类：
	(1)sum() 求和
	(2)avg() 平均值
	(3)max() 最大值
	(4)min() 最小值
	(5)count() 计算个数
注意：
	(1)sum()、avg()只能处理数值型数据
	   max()、min()、count()可以处理任何类型数据
	(2)以上分组函数都忽略null值
	(3)参数部分可以使用distinct实现去重
	(4)count(*) 常用来统计总行数
	(5)和分组函数一同查询的字段必须是group by后的字段
	
*/
#1.简单使用
SELECT SUM(salary) FROM employees;
SELECT ROUND(AVG(salary),2) FROM employees;

SELECT 
  MAX(salary) AS 最高,
  MIN(salary) AS 最低,
  COUNT(salary) AS 总数 
FROM
  employees ;

#2.distinct
SELECT SUM(DISTINCT salary) FROM employees;
SELECT COUNT(DISTINCT salary) FROM employees;

#count()详细介绍
#3.统计总行数 count(*) count(1)
SELECT COUNT(*) FROM employees;

#4.和分组函数一同查询的字段必须是group by后的字段
#下面的查询语句是错误的，结果中的employee_id无意义
SELECT SUM(salary),employee_id FROM employees;

#练习
#1.查询公司员工工资的最大值，最小值，平均值，总和
SELECT 
  MAX(salary),
  MIN(salary),
  AVG(salary),
  SUM(salary) 
FROM
  employees ;

#2.查询员工表中的最大入职时间和最小入职时间相差的天数
#datediff(expr1,expr2) 返回两个日期的差值
SELECT 
  DATEDIFF(MAX(hiredate), MIN(hiredate)) AS diffierence 
FROM
  employees ;

#3.查询部门编号为90的员工个数
SELECT 
  COUNT(*) AS 个数 
FROM
  employees 
WHERE department_id = 90 ;

分组查询

#进阶5：分组查询
/*
语法：
	select 分组函数, 列(要求出现在group by后面)
	from 表名
	[where 删选条件]
	group by 分组列表
	[order by 子句]

注意：查询列表要求是分组函数和group by后出现的字段

特点：1.		数据源		位置			关键字
	分组前筛选	原始表		group by子句前面	where
	分组后筛选	分组后的结果表	group by子句后面	having

	分组函数做条件肯定放在having子句中
	能用分组前筛选的尽量使用分组前筛选
	
      2.group by子句支持单个字段分组、多个字段分组(多个字段之间
      用逗号隔开，没有顺序要求)、函数或表达式
      
      3.可以添加排序order by子句，放在整个分组查询的最后
*/
#案例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 ;

#添加分组后的筛选条件
/*
当筛选条件在原始表中不存在时，使用：
having + 筛选条件
追加在group by后面
*/
#案例1：查询哪个部门的员工个数大于2
#(1)查询每个部门的员工个数
#(2)根据(1)查询员工个数大于2的部门
SELECT 
  COUNT(*),
  department_id 
FROM
  employees 
GROUP BY department_id 
HAVING COUNT(*) > 2 ;

#案例2：查询每个工种中，有奖金且最高工资>12000的员工的工种编号和最高工资
SELECT 
  employee_id,
  MAX(salary),
  job_id 
FROM
  employees 
WHERE commission_pct IS NOT NULL 
GROUP BY job_id 
HAVING MAX(salary) > 12000 ;

#案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，
#以及其最低工资
SELECT 
  manager_id,
  MIN(salary) 
FROM
  employees 
WHERE manager_id > 102 
GROUP BY manager_id 
HAVING MIN(salary) > 5000 ;

#按函数或表达式分组
#案例：按员工姓名长度分组，查询每一组的员工个数，并筛选员工个数>5的有哪些
SELECT 
  COUNT(*) AS 个数,
  LENGTH(last_name) AS 姓名长度 
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.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序
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) 
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 ;

连接查询

sql92

#进阶6：连接查询
/*
含义：又称多表查询，当要查询的字段来自于多个表时，就会用到连接查询

笛卡尔成乘积现象：表1有m行，表2有n行，结果m*n行

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：
	按年代分类：
		sql92：只支持内连接
		sql99(推荐使用)：支持内连接、外连接(不支持全外连接)、交叉连接
	按功能分类：
		内连接：
			等值连接
			非等值连接
			自连接
		外连接：
			左外连接
			右外连接
			全外连接
		交叉连接
*/
#一、sql92标准
#1.等值连接
/*
(1)多表的等值连接的结果为多表的交集部分
(2)n表连接，至少需要(n-1)个连接条件
(3)多表的顺序没有要求
(4)一般需要为表起别名
(5)可以搭配筛选、分组、排序等语句使用
*/
#案例1：查询每个girl对应的boy
SELECT 
  `name`,
  boyName 
FROM
  beauty,
  boys 
WHERE beauty.boyfriend_id = boys.id ;

#案例2：查询员工名和对应的部门名
SELECT 
  last_name AS 员工名,
  department_name AS 部门名 
FROM
  employees,
  departments 
WHERE employees.`department_id` = departments.`department_id` ;

#2.为表起别名 
/*
(1)提高语句的简洁度
(2)区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能再使用原来的表名去限定，而只能用别名
*/ 
#案例1：查询员工名，工种号，工种名
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 AS j
WHERE e.`job_id` = j.`job_id` ;

#3.添加筛选：使用 and or not 
#案例1：查询有奖金的员工名和部门名
SELECT 
  last_name,
  department_name 
FROM
  employees e,
  departments d 
WHERE e.`department_id` = d.`department_id` 
  AND e.`commission_pct` IS NOT NULL ;

#案例2：查询城市名中第二个字符为o的城市名和部门名
SELECT 
  city,
  department_name 
FROM
  locations l,
  departments d 
WHERE l.`location_id` = d.`location_id` 
  AND city LIKE '_o%' ;

#4.添加分组
#案例1：查询每个城市的部门个数
SELECT 
  COUNT(*) 个数,
  city 城市
FROM
  locations AS l,
  departments AS d 
WHERE l.`location_id` = d.`location_id` 
GROUP BY city ;

#案例2：查询有奖金的每个部门的部门名、部门的领导编号和该部门的最低工资
SELECT 
  department_name,
  d.manager_id,
  MIN(salary),
  d.department_id 
FROM
  departments AS d,
  employees AS e 
WHERE d.`department_id` = e.`department_id` 
  AND commission_pct IS NOT NULL 
GROUP BY d.`department_id` ;

#5.添加排序
#案例：查询每个工种的工种名和员工的个数，并按照员工的个数降序
SELECT 
  job_title,
  COUNT(*),
  j.job_id 
FROM
  employees e,
  jobs j 
WHERE e.`job_id` = j.`job_id` 
GROUP BY j.job_id 
ORDER BY COUNT(*) DESC ;

#6.多表连接
#案例：查询员工名，部门名和所在城市
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.非等值连接
#可以与其他语句搭配使用
#案例：查询员工的工资和工资级别
SELECT 
  salary,
  grade_level 
FROM
  employees e,
  job_grades jg 
WHERE salary BETWEEN jg.`lowest_sal` 
  AND jg.`highest_sal` ;

#3.自连接
#案例：查询员工名和上级名称
SELECT 
  e.last_name 员工名称,
  m.last_name 上级名称
FROM
  employees e,
  employees m 
WHERE e.`manager_id` = m.`employee_id` ;

#练习
#查询每个国家下，部门个数大于2的国家个数
SELECT 
  l.city 国家,
  COUNT(*) 部门个数 
FROM
  locations l,
  departments d 
WHERE l.`location_id` = d.`location_id` 
GROUP BY city 
HAVING 部门个数 > 2 ;

sql99

内连接

#二、sql99标准
/*
语法：
	select 查询列表
	from 表1 (别名) 
	[连接类型] join 表2 (别名) 
	on 连接条件
	[where 删选条件]
	[group by 分组]
	[having 筛选条件]
	[order by 排序] ;
	
内连接：inner
外连接：
	左外连接：left [outer]
	右外连接：right [outer]
	全外连接：full [outer]
交叉连接：cross 

*/
#(一)内连接
/*
语法：
	select 查询列表
	from 表1 (别名) 
	inner join 表2 (别名) 
	on 连接条件
	... ;

分类：
	等值连接
	非等值连接
	内连接	
	
特点：
	(1)可以添加筛选、分组、排序等
	(2)inner可以省略
	(3)筛选条件放在where后，连接条件放在on后，便于阅读
	(4)sql92和sql99的等值连接结果相同，都是查询多表的交集
	
*/
#1.等值连接
#案例1：查询员工名所对应的部门名
SELECT 
  last_name,
  department_name 
FROM
  employees AS e 
  INNER JOIN departments AS d 
    ON e.`department_id` = d.`department_id` ;

#案例2：查询名字中包含e的员工名和工种名
SELECT 
  last_name,
  job_title 
FROM
  employees e 
  INNER JOIN jobs j 
    ON e.`job_id` = j.`job_id` 
WHERE e.last_name LIKE '%e%' ;

#案例3：查询部门个数大于3的城市名和部门个数
SELECT 
  city,
  COUNT(*) 部门个数
FROM
  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 AS d 
  INNER JOIN employees AS 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.非等值连接
#案例1：查询员工的工资级别
SELECT 
  salary,
  grade_level 
FROM
  employees e 
  INNER JOIN job_grades jg 
    ON e.`salary` BETWEEN jg.`lowest_sal` 
    AND jg.`highest_sal` ;

#案例2：查询工资级别的个数大于20的个数，并按工资级别降序
SELECT 
  grade_level 工资级别,
  COUNT(*) 个数
FROM
  employees e 
  INNER JOIN job_grades jg 
    ON e.`salary` BETWEEN jg.`lowest_sal` 
    AND jg.`highest_sal` 
GROUP BY grade_level 
HAVING COUNT(*) > 20 
ORDER BY grade_level DESC ;

#3.自连接
#查询员工名以及其上级名称
SELECT 
  e.last_name 员工名称,
  m.last_name 上级名称
FROM
  employees e 
  INNER JOIN employees m 
    ON e.`manager_id` = m.`employee_id` ;

外连接

#二、sql99标准
#(二)外连接
/*
1.用途：
	用于查询包含在表1中，但不包含在表2中的数据
	
2.特点：
	1.外连接的查询结果为主表中的所有数据
	如果从表中有与主表匹配的数据，则显示匹配的值
	如果从表中没有与主表匹配的数据，则显示null
	
	即：外连接查询结果 = 内连接查询结果 + 主表中有但从表中没有的数据

3.左外连接：left join左边的是主表
  右外连接：right join右边的是主表

4.左外和右外交换两个表的顺序可以达到相同效果

5.一般查询的字段在哪个表，哪个表就是主表

6.进行null值筛选时，一般使用主键字段进行筛选

*/
#案例1：查询男朋友不在boys表中的女性有哪些
#左外连接
SELECT 
  be.`name`
FROM
  beauty be 
  LEFT OUTER JOIN boys bo 
    ON be.`boyfriend_id` = bo.`id` 
WHERE bo.`id` IS NULL ;
#---------------------------------
#右外连接
SELECT 
  be.`name`
FROM
  boys bo 
  RIGHT OUTER JOIN beauty be 
    ON be.`boyfriend_id` = bo.`id` 
WHERE bo.`id` IS NULL ;


#案例2：查询哪个部门没有员工
#左外连接
SELECT 
  department_name 
FROM
  departments d 
  LEFT JOIN employees e 
    ON d.`department_id` = e.`department_id` 
WHERE e.`employee_id` IS NULL ;
#-------------------------------------------
#右外连接
SELECT 
  department_name 
FROM
  employees e 
  RIGHT JOIN departments d 
    ON d.`department_id` = e.`department_id` 
WHERE e.`employee_id` IS NULL ;

#全外连接MySQL不支持

#(三)交叉连接
#是sql99标准下的笛卡尔乘积
SELECT 
  be.*,
  bo.* 
FROM
  beauty be 
  CROSS JOIN boys bo ;

#练习
#1.查询编号>3的女性对应的男朋友的信息，如果有则列出详细信息，如果没有，则用null填充
USE girls;
SELECT 
  be.name,
  bo.* 
FROM
  boys AS bo 
  RIGHT JOIN beauty AS be 
    ON bo.`id` = be.`boyfriend_id` 
WHERE be.`id` > 3 ;

#2.查询哪个城市没有部门
USE myemployees;
SELECT 
  city 
FROM
  locations l 
  LEFT JOIN departments d 
    ON l.`location_id` = d.`location_id` 
WHERE d.`department_id` IS NULL ;

#3.查询部门名为SAL或IT的员工信息
USE myemployees;
SELECT 
  e.* 
FROM
  employees e 
  INNER JOIN departments d 
    ON e.`department_id` = d.`department_id` 
WHERE d.`department_name` IN ('SAL', 'IT') ;

子查询1

#进阶7：子查询
/*
含义：
	出现在其他语句中的select语句，称为子查询或内查询
	外部的查询语句称为主查询或外查询

分类：
	按子查询出现的位置：
		select后：只支持标量子查询
		from后：表子查询
		where或having后：标量子查询、列子查询、行子查询
		exists后(相关子查询)：表子查询
	
	按结果集的行列数不同：
		标量子查询：结果集只有一行一列
		列子查询：结果集为一列多行
		行子查询：结果集为一行多列
		表子查询：结果集为多行多列
		
特点：
	(1)子查询放在小括号内
	(2)子查询一般放在条件的右侧
	(3)标量子查询一般搭配单行操作符使用 > < = >= <= <>
	(4)列子查询一般搭配多行操作符使用 in any some all
	(5)子查询优先于主查询执行，主查询使用子查询的结果

*/
#一、where或having后
#1.标量子查询(单行子查询)
#案例1：查询谁的工资比Abel高
#(1)查询Abel的工资
SELECT 
  salary 
FROM
  employees 
WHERE last_name = 'Abel' ;
#(2)查询工资大于(1)结果的员工信息
SELECT 
  * 
FROM
  employees 
WHERE salary > 11000 ;
#合并：
SELECT 
  * 
FROM
  employees 
WHERE salary > 
  (SELECT 
    salary 
  FROM
    employees 
  WHERE last_name = 'Abel') ;

#案例2：查询job_id与141号员工相同，salary比143号员工高的员工的姓名、job_id以及工资
#(1)查询141号员工的job_id
SELECT 
  job_id 
FROM
  employees 
WHERE employee_id = 141 ;
#(2)查询143号员工的工资
SELECT 
  salary 
FROM
  employees 
WHERE employee_id = 143 ;
#合并：
SELECT 
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE job_id = 
  (SELECT 
    job_id 
  FROM
    employees 
  WHERE employee_id = 141) 
  AND salary > 
  (SELECT 
    salary 
  FROM
    employees 
  WHERE employee_id = 143) ;

#案例3：查询公司工资最少的员工的last_name、job_id以及salary
#(1)查询公司得最低工资
SELECT 
  MIN(salary) 
FROM
  employees ;
#(2)查询工资为最低工资(1)的员工信息
#合并：
SELECT 
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE salary = 
  (SELECT 
    MIN(salary) 
  FROM
    employees) ;

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
#(1)查询50号部门的最低工资
SELECT 
  MIN(salary) 
FROM
  employees 
WHERE department_id = 50 ;
#(2)查询每个部门的最低工资，再与(1)比较
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：属于(或不属于)列表中的任意一个【in可以换成(= any),not in可以换成(<> all)】
	any/some：和子查询返回的某一个值进行比较【可以用min(),max()代替，转换为标量子查询】
	all：和子查询返回的所有值进行比较【可以用min(),max()代替，转换为标量子查询】
*/
#案例1：查询location_id是1400或1700的部门中所有员工的姓名
#(1)查询location_id是1400或1700的部门编号
SELECT 
  department_id 
FROM
  departments 
WHERE location_id IN ('1400', '1700') ;
#(2)查询(1)部门中的员工信息 
SELECT 
  last_name 
FROM
  employees 
WHERE department_id IN 
  (SELECT 
    department_id 
  FROM
    departments 
  WHERE location_id IN ('1400', '1700')) ;

#案例2：查询其他工种中，比job_id为IT_PROG中的任意一个工资低的员工的工号、姓名、job_id以及salary
#(1)查询job_id为IT_PROG的部门的所有工资
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG';
#合并
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' ;
#----------------------------
#改写成标量子查询
SELECT 
  employee_id,
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE salary < 
  (SELECT 
    MAX(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') 
  AND job_id <> 'IT_PROG' ;
#--------------------------
SELECT 
  employee_id,
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE salary < 
  (SELECT 
    MIN(salary) 
  FROM
    employees 
  WHERE job_id = 'IT_PROG') 
  AND job_id <> 'IT_PROG' ;
 
#3.行子查询(多列子查询) 
#案例：查询员工编号最小，但工资最高的员工信息
#(1)查询最小的员工编号
SELECT 
  MIN(employee_id) 
FROM
  employees ;
#(2)查询最高工资
SELECT 
  MAX(salary) 
FROM
  employees ;
#合并
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) ;

子查询2

#进阶7：子查询
/*
只支持标量子查询
*/
#二、select后
#案例1：查询每个部门的员工个数
SELECT 
  d.department_name, 
  COUNT(*) 
FROM
  departments d 
  LEFT JOIN employees e 
    ON e.`department_id` = d.`department_id` 
GROUP BY d.`department_id` ;
#-------------------------------------------
SELECT 
  d.*,
  (SELECT 
    COUNT(*) 
  FROM
    employees e 
  WHERE e.department_id = d.department_id) 
FROM
  departments d ;

#案例2：查询员工号为102的部门名
SELECT 
  department_name 
FROM
  departments d 
  INNER JOIN employees e 
    ON e.`department_id` = d.`department_id` 
WHERE e.`employee_id` = 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 
  department_id,
  AVG(salary) 
FROM
  employees 
GROUP BY department_id ;
#(2)查询每个部门的平均工资的工资等级
SELECT 
  department_avg.*,
  jg.grade_level 
FROM
  (SELECT 
    department_id,
    AVG(salary) avg_salary 
  FROM
    employees 
  GROUP BY department_id) department_avg #第一张包括平均工资的表
  INNER JOIN job_grades jg 
    ON department_avg.avg_salary BETWEEN jg.`lowest_sal` 
    AND `highest_sal` ;

#exists后(相关子查询)
/*
语法：
	exists(查询语句)
	
结果：1或0
*/
#案例1：查询有员工的部门名   
SELECT 
  department_name 
FROM
  departments d 
WHERE EXISTS 
  (SELECT 
    * 
  FROM
    employees e 
  WHERE e.department_id = d.`department_id`);
#--------------------------------------------
SELECT 
  department_name 
FROM
  departments d 
WHERE d.`department_id` IN 
  (SELECT 
    department_id 
  FROM
    employees e) ;
#-------------------------
SELECT DISTINCT 
  department_name 
FROM
  departments d 
  INNER JOIN employees e 
    ON d.`department_id` = e.`department_id` ;

#案例2：查询没有女朋友的男性信息
#in
SELECT 
  bo.* 
FROM
  boys bo 
WHERE bo.`id` NOT IN 
  (SELECT 
    boyfriend_id 
  FROM
    beauty) ;
    
#exists
SELECT 
  bo.* 
FROM
  boys bo 
WHERE NOT EXISTS 
  (SELECT 
    boyfriend_id 
  FROM
    beauty be 
  WHERE be.`boyfriend_id` = bo.`id`) ;

#练习
#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 
FROM
  employees e 
  INNER JOIN 
    (SELECT 
      department_id,
      AVG(salary) ag 
    FROM
      employees 
    GROUP BY department_id) dep_avg 
    ON e.`department_id` = dep_avg.department_id 
WHERE e.salary > dep_avg.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的员工姓名和工资
SELECT 
  last_name,
  salary 
FROM
  employees 
WHERE manager_id IN 
  (#该员工的manager_id要等于K_ing的employee_id
  SELECT 
    employee_id 
  FROM
    employees 
  WHERE last_name = 'K_ing') ;

#7.查询工资最高的员工姓名，要求first_name和last_name显示在一列
SELECT 
  CONCAT(first_name, ' ', last_name) 姓名,
  salary 
FROM
  employees 
WHERE salary = 
  (SELECT 
    MAX(salary) 
  FROM
    employees) ;
    

分页查询

#进阶8：分页查询
/*
适用：当要显示的数据在一页内显示不全时，需要分页提交sql请求

语法：
	select 查询列表
	from 表
	[连接类型 join 表2
	on 连接条件
	where 筛选条件
	group by 分组
	having 筛选
	order by 排序]
	limit offset, size ;
	
	offset-要显示条目的起始索引，从0开始
	size-要显示的条目个数
	注：从0开始时，0可以省略

特点:
	(1)limit语句放在所有语句的最后
	(2)limit语句最后执行
	(3)假设要显示的页数为page，每页的条目数为size
		select 查询列表
		from 表
		limit (page-1)*size, size ;
		
*/
#案例1：查询前5条员工信息
SELECT * FROM employees LIMIT 0, 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 0, 10 ;

联合查询

#进阶9：联合查询
/*
关键字：
	union 联合、合并：将多条查询语句的结果合并成一个结果
	
语法：
	查询语句1
	union 
	查询语句2
	...

适用：
	要查询的结果来自于多个表，且多个表之间没有连接关系，但查询的信息相同时，
	(例如都查询姓名、年龄)

特点：
	(1)多条查询语句查询的列数要一致，即查询的字段数目要相同
	(2)多条查询语句查询的字段名称和顺序最好一致
	(3)union会自动去重，若不想去重，需要使用union all
	
*/
#案例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%' ;