#-------------------分组查询-------------------
/*
	select 分组函数，列（要求出现在group by后面）
	from 表
	【where 筛选条件】
	group by 分组的列表
	【order by 子句】
注意：
	查询列表必须特殊，要求是分组函数和group by 后出现的字段
	
特点： 
	1、分组查询中筛选条件分为两类
			数据源		位置			关键字
	分组前筛选	原始表		group by子句前面	where
	分组后筛选	分组后的结果集	group by子句后面	having
	
	分组函数做条件肯定是放在having子句中;能用分组前筛选优先考虑分组前筛选
	2、group by 子句支持单个字段分组，多个字段分组（多个字段之间逗号隔开没有顺序要求），
	3、也可以添加排序（排序放在整个分组查询的最后）
*/
#引入：查询每个部门的平均工资
SELECT AVG(salary) 
FROM employees;

#案例一：查询每个工种的最高工资(简单的分组查询)
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;

#案例二：查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id

#添加筛选条件：查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%e%'
GROUP BY department_id;

#案例二：查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE !(commission_pct IS NULL)
GROUP BY manager_id;

#添加筛选条件，哪个部门的员工个数>2
#1.查询每个部门的员工个数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;
#2.根据1的结果进行筛选，查询哪个部门的员工个数>2
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

#案例二：查询每个工种有奖金的而且最高工资大于一万二的工种编号和最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000;

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

#按表达式或者函数进行分组
#案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
SELECT COUNT(*),LENGTH(last_name) AS 姓名长度
FROM employees
GROUP BY 姓名长度
HAVING COUNT(*) > 5;

#按多字段分组,添加排序

#案例：查询每个部门，每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY AVG(salary) DESC;

#===================连接查询-=================
/*
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行

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

分类：
	按年代分类:
	sq192标准
	sq199标准【推荐】：支持内连接+外连接（）+交叉连接
	按功能分类：
		内连接：
			等值连接
			非等值连接
			自连接
		外连接：
			左外连接
			右外连接
			全外连接
		交叉连接
			

*/
SELECT * FROM beauty;
SELECT * FROM boys;


#一、sql 92标准
#1.等值连接
/**
	多表等值连接的结果为多表的交集部分
	n表连接，至少需要n-1个连接条件
	多表的顺序没有要求
	一般需要为表起别名
	可以搭配前面介绍的所有子句使用，比如排序，分组，筛选
	
*/
#案例1：查询女神名和对应的部门名

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

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

#2.为表起别名
#案例：查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,job_title
FROM jobs AS j,employees AS e
WHERE e.job_id = j.job_id;

#3、可以加筛选吗？
#案例：有奖金的员工名、部门名
SELECT last_name,department_name
FROM employees AS e,departments AS d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;

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


#2.非等值连接

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

#3.自连接

#案例：查询员工名以及他的上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees AS e,employees AS m
WHERE e.manager_id = m.employee_id;
 
 
 #显示员工表最大工资，工资平均值
 SELECT MAX(salary),AVG(salary) FROM employees;
 #查询员工表employee_id,job_id,last_name,按department_id降序,salary升序
 SELECT employee_id,job_id,last_name
 FROM employees
 ORDER BY department_id DESC,salary ASC;
 #查询员工表中带a和e的，并且a在e的前面
 SELECT job_id FROM employees 
 WHERE job_id LIKE '%a%e%';
 
 
 
 #sql 99语法
 /*
语法：
	select 查询列表
	from 表1 别名 【连接类型】
	join 表2 别名
	on 连接条件
	【where 筛选条件】
	【group by 分组】
	【having 筛选条件】
	【order by 排序条件】
分类：
内连接：（☆）：in
外连接
	左外（☆）：left【outer】
	右外（☆）：right【outer】
	全外：full【outer】
交叉连接：cross

 */
 
 /*
	内连接
	语法：
		select 查询列表
		from 表1 别名
		inner join 表2 别名
		on 连接条件
	分类：
	等值
	非等值
	自连接
	
特点：添加排序、分组、筛选
      inner 可以省略
      筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
      inner join连接  和  92语法中等值连接是一样的
 */
 
#1,。等值连接
#案例一：查询员工名，部门名
SELECT last_name,department_name
FROM employees AS e 
INNER JOIN departments AS d
ON e.department_id = d.department_id;

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

#3.查询部门个数>3的城市名和部门个数，（添加分组+筛选）
SELECT city,COUNT(*)
FROM departments d
INNER JOIN locations l
ON d.location_id = l.location_id
GROUP BY city
HAVING COUNT(*) > 3;

#查询哪个部门的部门员工个数 > 3 的部门数和员工个数，并按个数降序（排序）
SELECT COUNT(*),department_name
FROM departments d
INNER JOIN employees e
ON e.department_id = d.department_id
GROUP BY department_name
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC;

#查询员工名、部门名、工种名、并按部门名降序
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;
  
 
#二：非等值连接
#查询员工工资级别

#三：自连接


#----外连接
/*
	应用场景：
		1、外连接的查询结果为主表中的所有记录，如果从表中
		   有和他匹配的，则显示匹配的值。如果从表中没有和他
		   陪陪的，则显示null
		   外连接查询结果 = 内连接结果+主表中有而从表中没有的记录
		2、左外连接，left左边的是主表
		   右外连接，right join右边的是主表
		3、左外和右外交换两个表的顺序，可以实现同样的效果
		4、全外连接 = 内连接的结果+表1中有单表2中没有+表2中有但表1中没有的
		
		
*/
#引入：查询没有男朋友的女神名
SELECT * FROM beauty;
SELECT * FROM boys;

SELECT b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id = bo.id
WHERE bo.id IS NULL;


#全外连接

SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN beauty b

#交叉连接
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

#案例练习：查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充
SELECT b.id,b.name,bo.*
FROM beauty AS b
LEFT OUTER JOIN boys AS bo
ON b.boyfriend_id = bo.id
WHERE b.id > 3;

#查询哪个城市没有部门
SELECT city,d.*
FROM departments AS d 
RIGHT OUTER JOIN locations AS l
ON d.location_id = l.location_id
WHERE d.department_id IS NULL;


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

#------------------子查询--------------------
/*
	含义：出现在其他语句中的select语句中，称子查询或内查询
	外部的查询语句，称为主查询
	分类：
	按子查询出现的位置
		select 后面：
			仅仅支持标量子查询
		from 后面：
			支持表子查询
		where 或 having后面：
			支持标量子查询（单行）
			列子查询（多行）
			行子查询
		exists后面（相关子查询）：
			表子查询
	
	按结果集的行列数不同
		标量子查询（结果集只有一列一行）
		列子查询（结果集只有一行多列）
		集子查询（结果集一般为多行多列）
		
*/

#一、where和having 后面
/*
	1、支持标量子查询（单行）
	2、列子查询（多行）
	3、行子查询（多列多行）
特点：
	子查询放在小括号内
	子查询一般放在条件的右侧
	标量子查询，一般搭配着单行操作符使用
	> < >= <= <>
	
	列子查询，一般搭配着多行操作符使用
	in、any/some、 all
	
*/

#1、标量子查询
#案例一：谁的工资比Abel 高？
SELECT *
FROM employees
WHERE salary > (
	SELECT salary
	FROM employees 
	WHERE last_name = 'Abel'
);

#案例二：查询最低工资大于五十号部门最低工资的部门ID和其最低工资
#1.查询五十号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE dapertment_id = 50

#2.查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id

#3. 筛选2，满足min salary>1
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT MIN(salary)
	FROM employees
	WHERE department_id = 50
);

#非法使用标量子查询：子查询的结果不是一行一列就是非法


#行子查询（结果一行多列或多行多列）

#案例：查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

#1.查询最小的员工编号
SELECT MIN(employee_id)
FROM employees;
#2.查询最高工资
SELECT MAX(salary)
FROM employees;

#3.查询员工信息
SELECT *
FROM employees 
WHERE employee_id = (
	SELECT MIN(employee_id)
	FROM employees
)
AND salary = (
	SELECT MAX(salary)
	FROM employees
);


#二、select 后面
#案例：查询每个部门的员工个数
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e 
	WHERE e.department_id = d.department_id
) AS 个数
FROM departments d;

#案例二：查询员工号=102的部门名
SELECT (
	SELECT department_name
	FROM departments d
	INNER JOIN employees e
	ON d.department_id = e.department_id
	WHERE e.employee_id = 102
) 部门名;

#3.from后面
#案例：查询每个部门的平均工资的工资等级
#查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;


#四：exists(是否存在) 后面（相关子查询）
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);

#案例一：查询有员工的部门名

SELECT last_name
FROM departments
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.department_id = e.department_id
	
)

#查询和Zlotkey相同的员工姓名和工资
#1、查询Zlotkey的部门
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey'
#2、查询部门号=1的姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id = (
	SELECT department_id
	FROM employees
	WHERE last_name = 'Zlotkey'
);

#查询工资比公司平均工资高的员工的员工号，姓名和工资
#1、查平均工资
SELECT AVG(salary)
FROM employees;
#2,查询工资大于1的
SELECT department_id,last_name,salary
FROM employees
WHERE salary > (
	SELECT AVG(salary)
	FROM employees
);

#查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资
#1、各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#2、把1和employees连接，再筛选
SELECT employee_id,last_name,salary
FROM employees e
INNER JOIN (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
ON e.`department_id` = ag_dep.`department_id`
WHERE salary > ag_dep.ag;

#查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
#1.查询姓名中包含字母u的员工的部门
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'
#2.查询部门号=1中的任意一个员工号和姓名
SELECT last_name,employee_id
FROM employees 
WHERE department_id IN (
	SELECT DISTINCT department_id
	FROM employees
	WHERE last_name LIKE '%u%'
	
);

#查询在部门的location_id为1700的员工部门的员工号
#1.查询location_id为1700的部门
SELECT DISTINCT department_id
FROM departments
WHERE location_id = 1700
#2.查询部门号=1中的任意一个员工号
SELECT employee_id
FROM employees
WHERE department_id = ANY(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id = 1700
);

#查询管理者是King的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE manager_id IN (
	SELECT employee_id
	FROM employees
	WHERE last_name = 'k_ing' 
);

#查询工资最高的员工姓名，要求first_name和last_name显示为一行，列名为 姓.名
SELECT MAX(salary)
FROM employees;

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

#-------------------分页查询-----------------------
/*
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
	select 查询列表
	from 表
	【join type   join 表二】
	where 筛选条件
	group by 分组字段
	having 分组后的筛选
	order by 排序的字段
	limit 【offset,】size;
	(offset要显示条目的起始索引，起始索引从0开始)
	size要显示的条目个数
特点：
	1，limit语句放在查询语句的最后
	2，公式：
		要显示的页数 page，每页的条目数size
		select 查询列表
		limit (page-1)*size,size;
	
*/

#案例：查询前五条员工信息
SELECT * FROM employees LIMIT 0,5;
SELECT * FROM employees LIMIT 5;  #offset 可省略

#案例：查询第11条-25条
SELECT * 
FROM employees 
LIMIT 10,15;

#案例：有奖金的员工信息，并且工资较高的前十名显示出来
SELECT * FROM employees 
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;

/*
	已知表 stuinfo
	id 学号
	name 姓名
	email 邮箱 john@126.com
	gradeId 年级编号
	sex 性别 男 女
	age 年龄
	已知表 grade
	id 年级编号
	gradeName 年级名称
	
*/
#一、查询  所有学员用户名 （注：邮箱中 @前面的字符）
SELECT SUBSTR(email,1,INSTR(email,'@')-1) FROM stuinfo;
#二、查询女生和男生的个数
SELECT COUNT(*) 个数，sex
FROM studinfo
GROUP BY sex;
#三、查询年龄>18岁的所有学生的姓名和年级名称
SELECT NAME,gradeName
FROM stduinfo s
INNER JOIN grade g
ON s.greadeid = g.id
WHERE age >18;
#四、查询哪个年级的学生最小年龄>20岁
#1.每个年级的最小年龄
SELECT MIN(age),gradeid
FROM stuinfo
GROUP BY gradeid
#2.在1 的结果上筛选
SELECT MIN(age),gradeid
FROM stuinfo
GROUP BY gradeid
HAVING MIN(age) > 20;
#五：试说出查询语句中涉及到的所有关键字，以及执行先后顺序
SELECT 查询列表			7
FROM 表				1
连接类型 JOIN 表2		2
ON 连接条件			3
WHERE 筛选条件			4
GROUP BY 分组列表		5	
HAVING 分组后的筛选		6
ORDER BY 排序列表		8
LIMIT 偏移，条目数		9