SQL语言：DQL语言的学习

DQL语言的学习

• DQL：（Data Query Language）：数据查询语言

作为案例的数据库中的表的结构

基础查询：

语法：
select 查询列表 from 表名
特点：
1.查询列表可以是:表中的字段、常量值、表达式、函数
2.查询的结果是一个虚拟的表格

#1.查询表中的单个字段
select last_name from employees;

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

#3.查询表中的所有字段
select * from employees;

#4.查询常量值
select 100;
select 'john';

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

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

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

#方式一：使用AS
select 100%98 AS 结果;
select last_name AS 姓,first_name AS 名 from employees;

#方式二：使用空格
select last_name 姓,first_name 名 from employees;

select salary "out put" from employees; (出现歧义加上双引号)

#8.去重

#案例：查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id from employees;

mysql中的＋号：
仅仅有一个功能：运算符

SELECT 100+90;两个操作数都为数值型，则做加法运算
select '123' + 90;   其中一方为字符型，试图将字符型数值转换成数值型
				     如果转换成功，则继续做加法运算
select 'john'+90;    如果转换失败，则将字符型数值转换成0
select null+10;      只要其中一方为null,则结果肯定为null

#案例：查询员工名和姓连接成一个字段，并显示为姓名
select concat(last_name,' ',first_name) as 姓名 from employees;

条件查询：

语法: select 查询列表 from 表名 where 筛选条件;(执行顺序:先查表名再筛选条件之后进行查询)
分类: 一、按条件表达式筛选  条件运算符： > < = != <> >= <= 
     二、按逻辑表达式筛选     作用：用于连接条件表达式    逻辑运算符： && || ! and or not 
     三、模糊查询    like       between and      in       is null

#一、按条件表达式筛选

#案例1：查询工资>12000的员工信息
select * from employees where salary > 12000;

#案例2：查询部门编号不等于90号的员工名和部门编号
select last_name,department_id from employees where department_id != 90;(!= 也可以换成<>)

#二、按逻辑表达式筛选

#案例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 !(department_id >= 90 and department_id <= 110) or salary >= 15000;

#三、模糊查询
/*
like
特点：
①：一般和通配符搭配使用
	通配符：
	% 任意多个字符，包含0个字符
	_ 任意单个字符
between and
in 
is null | is not null
*/
#1.like 

#案例1：查询员工名中包含字符a的员工信息
select * from employees where last_name like '%a%';

#案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资
select last_name,salary from employees where last_name like '__e_a%';

#案例3：查询员工名中第二个字符为_的员工名
select last_name from employees where last_name like '_\_%';(使用转义字符)
select last_name from employees where last_name like '_$_%' ESCAPE '$';(指定任意字符为转义字符)

#2.between and
/*
①使用between and可以提高语句的简洁度
②包含临界值
③两个临界值不要调换顺序(完全等价于大于等于左边的值，小于等于右边的值)
*/

#案例1：查询员工编号在100到120之间的员工信息
select * from employees where employee_id between 100 and 120;

#3.in
/*
含义：判断某字段的值是否属于in列表中的某一项
特点：
①使用in提高语句简洁度
②in列表的值类型必须一致或兼容
③不支持通配符的使用
select last_name,job_id from employees where job_id in('IT_PROG','AD_%')(不支持这种写法，通配符与like相搭配使用,in相当于代替的是=)
*/

#案例：查询员工的工种编号是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
/*
=或<>不能用于判断null值
is null 或 is not null 可以判断null值
*/

#案例1：查询没有奖金的员工名和奖金率
select last_name,commission_pct from employees where commission_pct is null;(=号不能用来判断null值)
select last_name,commission_pct from employees where commission_pct is not null;

#安全等于  <=>

#案例1：查询没有奖金的员工名和奖金率
select last_name,commission_pct from employees where commission_pct <=> null;

#案例2：查询工资为12000的员工信息
select * from employees where salary <=> 12000;

#is null 与 <=>的比较
/*
is null:仅仅可以判断null值，可读性较高，推荐使用
<=>:既可以判断null值，又可以判断普通的数值，可读性较低
*/

#查询员工号为176的员工的姓名和部门号和年薪
#IFNULL(expr1,expr2) 如果expr1为NULL,则用expr2的值来代替
select last_name,department_id,salary * 12 * (1+IFNULL(commission_pct,0)) AS 年薪 from employees where employee_id=176;

测试：
#查询员工号为176的员工的姓名和部门号和年薪
select last_name,department_id,salary * 12 * (1+IFNULL(commission_pct,0)) AS 年薪 from employees where employee_id=176;

#查询没有奖金，且工资小于18000的salary,last_name
select last_name,salary from employees where commission_pct is null and salary < 18000;

#查询employees表中，job_id 不为 'IT' 或者工资为12000的员工信息
select * from employees where salary = 12000 or job_id <>'IT';

#查询部门departments表的结构
desc departments;

#查询部门department表中涉及到了哪些位置编号
select distinct location_id from departments;

select * from employees;
select * from employees where commission_pct like '%%' and last_name like '%%';
#(区别在于like不能查出为null的字段)

排序查询：

#进阶3：排序查询

/*
语法：
select 查询列表 from 表 where 筛选条件 order by 排序列表 asc | desc
特点：
			1.asc代表的是升序，desc代表的是降序
			如果不写，默认是升序
			2.order by子句中可以支持单个字段、多个字段、表达式、函数、别名
			3.order by字句一般是放在查询语句的最后面，limit子句除外
			4.执行顺序：先找表，后进行条件筛选，在进行查询最后进行排序

*/

#案例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;

#案例3：按年薪的高低显示员工的信息和年薪[按表达式排序]
select *,salary * 12 * (1+IFNULL(commission_pct,0)) as 年薪 from employees order by salary * 12 * (1+IFNULL(commission_pct,0));

#案例4：按年薪的高低显示员工的信息和年薪[按年薪排序]
select *,salary * 12 * (1+IFNULL(commission_pct,0)) as 年薪 from employees order by 年薪;

#案例5：按姓名的长度显示员工的姓名和工资[按函数排序]
select length(last_name) as 字节长度,last_name,salary from employees order by LENGTH(last_name) desc;

#案例6：查询员工信息，要求先按工资升序，再按员工编号降序[按多个字段排序]
select * from employees order by salary asc,employee_id desc;

常见函数：

#进阶4：常见函数
/*
概念：类似于Java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节  2、提高代码的重用性 
调用：select 函数名(实参列表) from 表;
特点：
		①叫什么(函数名)
		②干什么(函数功能)

分类：
		1、单行函数：
		如 concat、length、ifnull等
		2、分组函数
		功能：做统计使用，又称为统计函数、聚合函数、组函数
		1
常见函数：
		字符函数：length、concat、substr、instr、trim、upper、lower、lpad、rpad、replace
		
		数学函数：round、ceil、floor、truncate、mod
		
		日期函数：now、curdate、curtime、year、month、monthname、day、hour、minute、second、str_to_date、date_format
		
		其他函数：version、database、user
		
		控制函数if、case
*/

#一、字符函数
#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(last_name) from employees;
select LOWER(first_name) from employees;

#示例：将姓变大写，名变小写，然后拼接
select CONCAT(UPPER(last_name),'_',LOWER(first_name)) from employees;

#4.substr、substring
#注意：索引从1开始
#截取从指定索引处后面所有字符
select SUBSTR('李莫愁爱上了陆展元',7) out_put;

#截取从指定索引处指定字符长度的字符
select SUBSTR('李莫愁爱上了陆展元',1,3) out_put;

#案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来
select CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) as 姓名 from employees;

#5.instr 返回子串第一次出现的索引，如果找不到返回0
select INSTR('杨不悔爱上了殷六侠','殷六侠') as out_put;

#6.trim
select LENGTH(TRIM('    张翠山     ')) as out_put;

select TRIM('a' from 'aaaaaaaa张aaaaa翠山aaaaaaaaaaaaaaaaaaaaa') as out_put;

#7.lpad 用指定的字符实现左填充指定长度
select LPAD('殷素素',10,'*') as out_put;

#8.rpad 用指定的字符实现右填充指定长度
select RPAD('殷素素',12,'ab') as out_put;

#9.replace 替换
select replace('张无忌爱上了周芷若张无忌爱上了周芷若','周芷若','赵敏') as out_put;

#二、数学函数

#round 四舍五入

select ROUND(1.65);
select ROUND(-1.45);
select ROUND(1.567,2);

#ceil 向上取整，返回>=该参数的最小整数

select CEIL(1.52);
select CEIL(1.00);
select CEIL(-1.52);

#floor 向下取整，返回<=该参数的最大整数
select FLOOR(9.99);
select FLOOR(-9.99);

#truncate 截断
select TRUNCATE(1.65,1);

#mod 取余
select MOD(10,3);
select MOD(-10,-3);

#三、日期函数
#now 返回当前系统日期+时间
select NOW();

#curdate 返回当前系统日期，不包含时间
select CURDATE();

#curtime 返回当前时间，不包含日期
select CURTIME();

#可以获取指定的部分，年、月、日、小时、分钟、秒
select YEAR(NOW()) 年;
select YEAR('2000-3-5') 年;
select YEAR(hiredate) 年 from employees;

select MONTH(NOW()) 月;
select MONTHNAME(NOW()) 月;

#STR_TO_DATE(str,format) 将字符通过指定的格式转换成日期
select STR_TO_DATE('2000-3-5','%Y-%c-%d') as out_put;

#查询入职日期为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');

#data_format 将日期转换成字符
select DATE_FORMAT(NOW(),'%y年%m月%d日') as out_put;

#查询有奖金的员工名和入职日期（xx月/xx日 xx年）
select last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') AS 入职时间 from employees where commission_pct is not null;

#四、其他函数
select VERSION();
select DATABASE();
select USER();

#五、流程控制函数
#1.if函数：if else 的效果

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

select last_name,commission_pct,IF(commission_pct is null,'没奖金，哈哈','有奖金，嘻嘻') 备注 from employees;

#2.case函数的使用一：switch case 的效果
/*
switch (变量或表达式){
				case 常量1: 语句1: break;
				...
				default:语句n:break;
}

mysql中

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句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 函数的使用二：类似于 多重if
/*
java中：
if(条件1){
			语句1;
}else if(条件2){
			语句2;
}
...
else{
			语句n;
}

mysql中：
case 
when 条件1 then 要显示的值1或 语句1;
when 条件2 then 要显示的值2或 语句2;
...
else 要显示的值n或语句n;
end
*/

#案例：查询员工的工资的情况
/*
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则,显示D级别
*/
select last_name,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 计算个数
特点：
1、sum 、 avg 一般用于处理数值型
	 max 、 min 、 count 可以处理任何类型

2、以上分组函数都忽略null值

3、可以和distinct搭配实现去重的运算

4、count函数的单独介绍
一般使用count(*)用作统计行数

5、和分组函数一同查询的字段要求是group by后的字段

*/

#1、简单的使用
select SUM(salary) from employees;
select AVG(salary) from employees;
select MIN(salary) from employees;
SELECT MAX(salary) from employees;
SELECT COUNT(salary) from employees;
select COUNT(commission_pct) from employees;

select SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最大,MIN(salary) 最小,COUNT(salary) 个数 from employees;

#2、参数支持哪些类型

select SUM(last_name) , AVG(last_name) from employees;#不会报错，但没有意义

select MAX(last_name),MIN(last_name) from employees;

select MAX(hiredate),MIN(hiredate) from employees;

select COUNT(commission_pct) from employees;
select COUNT(last_name) from employees;

#3、忽略null
select SUM(commission_pct) , AVG(commission_pct) from employees;
select MAX(commission_pct) , MIN(commission_pct) from employees;

#4、和distinct搭配
select SUM(DISTINCT salary),SUM(salary) from employees;

select COUNT(DISTINCT salary) from employees;

#5、count函数的详细介绍
select COUNT(salary) from employees;

select COUNT(*) from employees;#用来统计行数

select COUNT(1) from employees;#相当于在表中加了一列常量值用来统计行数

/*
效率：
MYISAM存储引擎下(MySQL5.1之前的存储引擎),count(*)的效率高
INNODB存储引擎下,count(*)和count(1)的效率差不多,比count(字段)要高一些
*/

#6、和分组函数一同查询的字段要求是group by后的字段

练习：
select MAX(salary),MIN(salary),AVG(salary),SUM(salary) from employees;

select YEAR(MAX(hiredate)),MONTH(MAX(hiredate)),DAY(MAX(hiredate)) from employees;

select YEAR(MIN(hiredate)),MONTH(MIN(hiredate)),DAY(MIN(hiredate)) from employees;

select DATEDIFF('2020-9-4','2020-9-1');

select DATEDIFF(NOW(),'2000-3-5');

select NOW();

select DATEDIFF(MAX(hiredate),MIN(hiredate)) diffrence from employees;

select COUNT(1) from employees where department_id=90;

#注：datediff(expr1,expr2) 可以用来查询两个日期相差的天数

分组查询：

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

#案例1：查询每个工种的最高工资
select job_id,MAX(salary) from employees group by job_id order by salary desc;

#案例2：查询每个位置上的部门个数
select COUNT(*),location_id from departments GROUP BY location_id;

#添加筛选条件
#案例1：查询邮箱中包含a字符的，每个部门的平均工资
select AVG(salary),department_id,email 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 order by salary asc;

#添加复杂的筛选条件

#案例1：查询哪个部门的员工个数>2

#①查询每个部门的员工个数
select COUNT(*),department_id from employees GROUP BY department_id;
#②根据①的结果进行筛选，查询哪个部门的员工个数>2

select COUNT(*),department_id from employees GROUP BY department_id having Count(*)>2;

#案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
#①查询每个工种有奖金的员工的最高工资
select MAX(salary),job_id from employees group GROUP BY job_id;

#②根据①结果继续筛选，最高工资>12000
select job_id,MAX(salary) 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(*),LENGTH(last_name) len_name from employees GROUP BY LENGTH(last_name);


#②添加筛选条件
select LENGTH(last_name) len_name,COUNT(*) c from employees GROUP BY LENGTH(last_name) having COUNT(*)>5 order by COUNT(*);
select LENGTH(last_name) len_name,COUNT(*) c from employees GROUP BY len_name having c>5 order by COUNT(*);#mysql中支持group by子句和order by子句后面使用别名

#按多个字段分组
#案例：查询每个部门每个工种的员工的平均工资
select AVG(salary),department_id,job_id from employees GROUP BY department_id,job_id;

#添加排序
#案例：查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示
select AVG(salary),department_id,job_id from employees where department_id is not null GROUP BY department_id,job_id having AVG(salary)>10000 order by AVG(salary) desc;

#练习
#1、查询各job_id的员工工资的最大值、最小值、平均值、总和，并按job_id升序
select MAX(salary),MIN(salary),AVG(salary),SUM(salary) from employees GROUP BY job_id ORDER BY job_id asc;

select MAX(salary)-MIN(salary) diffrence from employees;

select MAX(salary) from employees;

select MIN(salary) from employees where manager_id is not null group by manager_id having MIN(salary)>=6000;

select department_id,COUNT(*),AVG(salary) from employees GROUP BY department_id order by AVG(salary) desc;

select COUNT(*),job_id from employees group by job_id;

连接查询：

select * from beauty;

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

笛卡尔乘积现象：表1 有m行 ， 表2 有n行， 结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：
				按年代分类：
				sql92标准：仅仅支持内连接在mysql中不支持外连接
				sql99标准[推荐]：支持内连接+外连接(左外连接和右外连接)+交叉连接
				
				按功能分类：
								内连接
												等值连接
												非等值连接
												自然连接
								外连接
												左外连接
												右外连接
												全外连接
								交叉连接
*/
#笛卡尔积的错误情况
select name,boyName from boys,beauty where beauty.boyfriend_id=boys.id;

#一、sql92标准
#1、等值连接
/*
① 多表等值连接的结果为多表的交集部分
② n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④ 一般需要为表起别名
⑤ 可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
*/
#案例1：查询女神名和对应的男神名
select name,boyName from boys,beauty where beauty.boyfriend_id=boys.id;

#案例2：查询员工名和对应的部门名
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 AS e,jobs where employees.job_id=jobs.job_id;(注意给表起了别名，就不能再使用表名)
select last_name,e.job_id,job_title from employees AS e,jobs where e.job_id=jobs.job_id;

#3、两个表的顺序是否可以调换
select last_name,e.job_id,job_title from jobs,employees AS e where e.job_id=jobs.job_id;

#4、可以加筛选条件

#案例：查询有奖金的员工名、部门名
select last_name,department_name from employees,departments where employees.department_id=departments.department_id and commission_pct is not null;

#案例2：查询出城市中第二个字符为o的部门名和城市名
select department_name,city from locations,departments where departments.location_id=locations.location_id and city like '_o%';

#5、可以加分组

#案例1：查询每个城市的部门个数
select COUNT(*),city from departments,locations where departments.location_id=locations.location_id GROUP BY locations.city;

#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
select department_name,employees.manager_id,MIN(salary) from employees,departments where employees.department_id=departments.department_id and commission_pct is not null group by department_name,departments.manager_id;

#6、可以加排序

#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
select job_title,COUNT(*) from employees,jobs where employees.job_id=jobs.job_id group by job_title order by COUNT(*) desc;

#7、可以实现三表连接

#案例：查询员工名、部门名和所在的城市
select last_name,department_name,city from employees,departments,locations where employees.department_id=departments.department_id and departments.location_id=locations.location_id;

#查询所有部门的所有员工及部门名称
select last_name,e.department_id,department_name from employees e,departments d where e.department_id=d.department_id;

#查询90号部门员工的job_id和90号部门的location_id
select e.job_id,d.location_id,e.department_id from employees e,departments d,locations l where e.department_id=90 and e.department_id=d.department_id and d.location_id=l.location_id;

select job_id,location_id from employees e,departments d where e.department_id=d.department_id and e.department_id=90;

#选择所有有奖金的员工的e姓名，部门名，location_id以及city
select e.last_name,d.department_name,l.location_id,l.city from employees e,departments d,locations l where e.commission_pct is not null and e.department_id=d.department_id and d.location_id=l.location_id;

#选择city在Toronto工作的员工的姓名，工作编号，部门编号和部门名
select e.last_name,e.job_id,e.department_id,d.department_name,l.city 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';


#查询每个工种、每个部门的部门名、工种名和最低工资
select d.department_name,j.job_title,MIN(salary) from departments d,jobs j,employees e where e.department_id=d.department_id and e.job_id=j,job_id group by job_title,department_name;

#查询每个国家下的部门个数大于2的国家编号
select COUNT(*) 部门个数,country_id from departments d,locations l where d.location_id=l.location_id group by country_id having COUNT(*)>2;

#选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号
select e.last_name employees,e.employee_id "Emp#",m.last_name manager,m.employee_id "Mgr#" from employees e,employees m where e.manager_id=m.employee_id and e.last_name='kochhar';

#二、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 连接条件 where 筛选条件 group by 分组列表 having 分组后的筛选 order by 排序列表 limit 子句;

分类：
等值
非等值
自连接

特点：
				①添加排序，分组，筛选
				②inner可以省略
				③筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
				④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
*/


#1、等值连接

#1.查询员工名、部门名
select last_name,d.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 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 d inner join employees e on e.department_id=d.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;

#二) 非等值连接

#查询员工的工资级别
select salary,grade_level from employees e inner join job_grades j on e.salary BETWEEN j.lowest_sal and j.highest_sal;

#查询每个工资级别的个数>20的个数，并且按工资级别降序
select salary,grade_level,COUNT(*) from employees e inner 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;

#三) 自连接
#查询姓名中包含字符k的员工的名字、上级的名字
select e.last_name 员工,m.last_name 领导 from employees e inner join employees m on e.manager_id=m.employee_id where e.last_name like '%k%';

#二、外连接

/*
应用场景：用于查询一个表中有，另一个表中没有的

特点：
1、外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的，则显示Null
	 外连接查询结内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
	 右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1中没有的
*/
#引入：查询男朋友不在男神表的女神名
select b.name from beauty b left outer join boys bo on b.boyfriend_id=bo.id where bo.id is null;

#案例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;

#全外(mysql中不支持全外连接)
use girls;
select b.*,bo.* from beauty b full OUTER join boys bo on b.boyfriend_id = bo.id;

#交叉连接(使用99语法实现笛卡尔乘积)
use girls;
select b.*,bo.* from beauty b cross join boys bo;

#sql92和sql99的对比
/*
功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高
*/

#测试
#一、查询编号>3的女神的男朋友信息，如果有则列除详细，如果没有，用null填充(左外连接)
use girls;
select b.id,b.name,bo.* from beauty b left outer join boys bo on b.boyfriend_id=bo.id where b.id > 3;

#二、查询哪个城市没有部门
use myemployees;
select city from locations l left outer join departments d on l.location_id=d.location_id where d.department_id is null;

#三、查询部门名为SAL或IT的员工信息
#(使用内连接当SAL或IT下没有员工时就无法查出)
select e.*,d.department_name from employees e inner join departments d on e.department_id=d.department_id where d.department_name in ('SAL','IT');

#(使用外连接,通常情况下对主从表进行区分看查询的条件一般为主表，以主表去从表中进行匹配，再进行查询)
select e.*,d.department_name from departments d left join employees e on d.department_id=e.department_id where d.department_name in ('SAL','IT');

子查询：

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

分类：
按子查询出现的位置：
				select后面:
									 仅仅支持标量子查询
				from后面:
									 支持表子查询
				where或having后面: √
									 标量子查询 √
									 列子查询 √
									 
									 行子查询
									 
				exists后面(相关子查询)
									 表子查询
按结果集的行列数不同：
				标量子查询(结果集只有一行一列)
				列子查询(结果集只有一列多行)
				行子查询(结果集有一行多列，也可以是多列多行)
				表子查询(结果集一般为多行多列)
*/


#一、where或having后面
/*
1、标量子查询(单行子查询)
2、列子查询(多行子查询)

3、行子查询(多列多行)

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
> < >= <= = <>

④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

列子查询，一般搭配着多行操作符使用
in、any/some、all
*/

#1、标量子查询

#案例1：谁的工资比Abel高

#①查询Abel的工资
select salary from employees whe,salaryre last_name='Abel';

#②查询员工的信息，满足 salary > ① 结果
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 job_id from employees where employee_id=141;
select 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_bame,job_id和salary
select MIN(salary) from employees;
select last_name,job_id,salary from employees where salary = (select MIN(salary) from employees);

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
select MIN(salary) from employees where department_id-50; 

select department_id,MIN(salary) from employees group by department_id having MIN(salary) > (select MIN(salary) from employees where department_id =50);

#非法使用标量子查询(> < 属于单行操作符)
select department_id,MIN(salary) from employees group by department_id having MIN(salary) > (select salary from employees where department_id =50);

#列子查询(多行子查询)
/*
搭配多行操作符
in/not in:等于列表中的任意一个
any/some:和子查询返回的某一个值比较
all:和子查询返回的所有值比较
*/

#返回location_id是1400或1700的部门中的所有员工姓名
#先查询location_id是1400或1700的部门
select department_id from departments where location_id in (1400,1700);

select last_name from employees where department_id in (select department_id from departments d where location_id in (1400,1700));
#或
select last_name from employees where department_id = any(select department_id from departments d where location_id in (1400,1700));
#(in与=any not in与<>all 可以起到同样的作用)
#返回其他工种中比job_id为'IT_PROG'任一工资低的员工的员工号、姓名、Job_id以及salary
#先查询job_id为'IT_PROG'部门的任一工资
select 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 DISTINCT MAX(salary) from employees where job_id='IT_PROG') and job_id<>'IT_PROG';

#返回其它工种中比job_id为'IT_PROG'部门所有工资都低的员工的员工号、姓名、job_id以及salary
select DISTINCT salary from employees where job_id='IT_PROG';

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 distinct MIN(salary) from employees where job_id='IT_PROG') and job_id<>'IT_PROG';

#3、行子查询(结果集一行多列或多行多列)
#案例：查询员工编号最小并且工资最高的员工信息
select * from employees where (employee_id,salary)=(select MIN(employee_id),MAX(salary) from employees);

select MIN(employee_id) from employees;
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后面

/*
仅支持标量子查询
*/
#案例：查询每个部门的员工个数
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后面

/*
将子查询结果充当一张表，要求必须起别名
*/

#案例:查询每个部门的平均工资的工资等级
#查询每个部门的平均工资

select AVG(salary),department_id from employees GROUP BY department_id;

#②连接①的结果集和job_grades表，筛选条件平均工资between lowest_sal and highest_sal;
select ag_dep.ag,ag_dep.department_id,grade_level from (select AVG(salary) ag,department_id from employees GROUP BY department_id) ag_dep inner join job_grades on ag_dep.ag BETWEEN lowest_sal and highest_sal;

#四、exists后面(相关子查询)
/*
语法:
exists(完整的查询语句)
结果:
1或0
*/

select EXISTS(select employee_id from employees where salary=30000);

#案例1：查询有员工的部门名
select department_name from departments d where EXISTS(select * from employees e where 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 where bo.id=b.boyfriend_id);

#测试：
#1.查询和Zlotkey相同部门的员工姓名和工资
select last_name,salary from employees e where department_id=(select department_id from employees where last_name='Zlotkey') and last_name<>'Zlotkey';

#2.查询工资比公司平均工资高的员工的员工号，姓名和工资
select AVG(salary) from employees;

select employee_id,last_name,salary from employees where salary > (select AVG(salary) from employees);

#3.查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资
#①查询各部门的平均工资
select AVG(salary) from employees group by department_id;

#②连接①结果集和employees表，进行筛选
select e.department_id,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;

#4.查询姓名中包含字母u的员工所在相同部门的员工的员工号和姓名
select DISTINCT department_id from employees where last_name like '%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 department_id from departments where location_id=1700;

select employee_id,last_name from employees where department_id in (select department_id from departments where location_id=1700);

#6.查询管理者是King的员工姓名和工资
select employee_id from employees where last_name='K_ing';

select last_name,salary from employees where manager_id in (select employee_id from employees where last_name='K_ing');

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

select CONCAT(first_name,last_name) AS "姓.名" from employees where salary = (select MAX(salary) from employees);

分页查询：

#进阶8：分也查询 √
/*

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
			select 查询列表 [from 表 [join type] join 表2 on 连接条件 where 筛选条件 group by 分组字段 having 分组后的筛选 order by 排序的字段] limit [offset,]size;
			offset 要显示条目的起始索引(起始索引从0开始)
			size 要显示的条目个数

特点：
			①limit语句放在查询语句的最后 执行顺序：先走from 后的表，在走join 进行连接，之后 on 连接条件 ，接下来where 筛选条件，group by 分组字段,having 分组后的筛选，之后进行select 查询列表，然后进行排序 order by 排序字段，最后执行limit子句
			②公式
			要显示的页数 page,每页的条目数 size
			
			select 查询列表 from 表 limit (page-1)*size,size;
*/

#案例1：查询前五条员工信息
select * from employees limit 0,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;

union联合查询：

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

应用场景:
要查询的结果来自于多个表，且多个表没有直接得连接关系，但查询的信息一致时

特点:
1、要求多条查询语句的列数是一致的！
2、要求多条查询语句的查询的每一列的类型和顺序是一致的！
3、union关键字默认去重，如果使用union all可以包含重复项
*/

#引入的案例:查询部门编号>90或邮箱中包含a的员工信息
select * from employees where email like '%a%' UNION select * from employees where department_id > 90;