MySQL基础（三）

上节回顾
MySQL基础（二）

1. DQL

1.1 排序查询

语法：
	select 查询列表
	from 表
	【where 筛选条件】
	order by 排序列表 【asc | desc】
	特定：
	1、desc代表降序 asc代表升序，不写默认为升序
	2、order by字句中可以支持单个字段、多个字段、表达式、函数、别名
	3、order by字句一般放置在查询语句的最后面，limit字句除外
	案例1：查询员工信息，要求从高到低排序
		select * from employees order by salary desc;
		select * from employees order by salary asc;由低到高
		注：asc可省略
	案例2：查询部门编号>=90的员工信息，按入职时间的先后进行排序【添加筛选条件】
		select * 
		from employees 
		where department_id>=90 
		order by hiredate asc;
	案例3：按年薪的高低显示员工信息和年薪【按表达式排序】
		select *,salary*12*(1+ifnull(commission_pct,0)) 年薪
		from employees
		order by salary*12*(1+ifnull(commission_pct,0)) desc;
	案例4：按年薪的高低显示员工信息和年薪【按别名排序】
		select *,salary*12*(1+ifnull(commission_pct,0)) 年薪
		from employees
		order by 年薪 desc;
	案例5：按姓名的长度显示员工姓名和工资【按函数排序】
		select length(last_name) 字节长度,last_name,salary
		from employees
		order by 字节长度 desc;
	案例6：查询员工信息，要求先按工资升序，再按照员工编号降序【按多个字段排序】
		select *
		from employees
		order by salary asc,employee_id desc;

1.2 常用函数

将一组逻辑语句封装在方法体中，对外暴露方法名
调用：select 函数名(实参列表) 【from 表】;
分类：
	1、单行函数
		concat、length、ifnull等
		一：字符函数
			1、length 获取参数值的字节个数
				select length('张三丰');-------9字节
			2、concat 拼接字符串
				select concat(last_name,'_',first_name) from employees;	
			3、upper、lower
				select upper('joHn'); 小写变大写
				select lower('joHn'); 大写变小写
			4、substr、substring
				注：索引从1开始
				截取从指定索引处后面的所有字符
				select substr('123456789',6) out_put;------56789
				截取从指定索引处指定字符长度的字符
				select substr('0123456789',1,3) out_put;------123
				案例：姓名中首字符大写，其他字符小写，然后用_拼接，显示出来
					select concat(upper(substr(last_name,1,1)),'_',lower(substr(last_name,2))) 结果 from employees
			5、instr 返回子串第一次出现的索引，找不到则返回0
				select instr('123456789','789') as  结果;------7
			6、trim 去掉前后空格
				select (trim('   123   ')) as 结果;------123
				select trim('a'from 'aaa1aaa2aaa3aaa') as 结果;------1aaa2aaa3
				select trim('aa'from 'aaa1aaa2aaa3aaa') as 结果;------a1aaa2aaa3a
			7、lpad 用指定字符实现左填充指定长度
				select lpad('123',10,'*') as 结果;------*******123
				select lpad('12345',2,'*') as 结果;------12
			8、rpad 用指定字符实现右填充指定长度
				select rpad('123',10,'*') as 结果;------123*******
			9、replace 替换
				select replace('01234444412456','4','5') as 结果;------01235555512556
		二:数字函数
			1、round 四舍五入
				select round(1.65);------ 2
				select round(-1.65);----- -2
				select round(-1.567,2);------ -1.57
			2、ceil 向上取整 返回>=该参数的最小整数
				select ceil(1.10);------ 2
			3、floor 向下取整 返回<=该参数的最大整数
				select floor(2.999);------ 2
			4、truncate 截断
				select truncate(1.6999,1);
			5、mod 取余   
				select mod(a,b) : a-a/b*b
				select mod(10,-3);------ 1
				select 10%3;------ 1
		三：日期函数
			1、now 返回当前系统日期+时间
				select now();
			2、curdate 返回当前系统日期，不包含时间
				select curdate();
			3、curtime 返回当前时间，不包含日期
				select curtime();
			4、获取指定部分：年、月、日、小时、分钟、秒
				select year(now()) 年;
				select month(now()) 月;...
			5、str_to_date 将字符通过指定格式转换为日期
				select str_to_date('2020-5-15','%Y-%c-%d');------2020-05-15
				案例1：查询入职时间为1992--4-3的员工信息
					select * from employees where hiredate = str_to_date('4-3 1992','%c-%d %Y');
			6、date_format 将日期转换为字符
				select date_format(now(),'%y年%m月%d日') as 结果;------ 20年05月15日
				案例1：查询有奖金的员工名和入职日期（XX月/XX日）
					select last_name,date_format(hiredate,'%m月/%d日 %y年') 入职时间
					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,'有奖金,haha','没奖金,hehe') 备注
				from employees;
			2、case函数
			语法一：
				case 要判断的字段或表达式
				when 常量1 then 要显示的值或语句1
				when 常量2 then 要显示的值或语句2
				................................
				else 要显示的值或语句n
				end
				案例1：查询员工工资，要求：
					   部门号=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;
			语法二：
				case
				when 条件1 then 要显示的值1或语句1
				when 条件1 then 要显示的值2或语句2
				..................................
				else 要显示的值或语句n
				end
				案例2：查询员工工资情况，要求：
					   当工资>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 新工资
					from employees;
				3.要求将员工的姓名按照首字符排序，并写出姓名的长度
					select last_name,length(last_name) 长度,substr(last_name,1,1) 首字符
					from employees
					order by last_name; 
					注：order by last_name;与order by 首字符;区别
						前者会先按首字母排序，当首字母相同时按照第二个字母排序。
						后者仅仅会根据首字母排序。
	2、分组函数
		功能：做统计使用，又称为统计函数或聚合函数或组函数
		分类：sum求和、avg平均值、max最大值、min最小值、count计算个数
			1.简单使用
				select sum(salary) from employees;
				select avg(salary) from employees;
				select max(salary) from employees;
				select min(salary) from employees;
				select count(salary) from employees;

				select sum(salary) 和,avg(salary)平均值,max(salary)最高,min(salary) 最小,count(salary) 个数
				from employees;
			2.参数支持的类型
				sum、avg一般用于处理数值型
				max、min、count用于处理任何类型
				select sum(last_name),avg(last_name) from employees;
				注：在mysql中有些语句不会进行报错但是已经没有逻辑意义。
				
				select max(last_name),min(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;每一行为1，统计1的个数
					count内的常量值，统计常量值的个数
			6.和分组函数一同查询的字段要求是group by后的字段
				错误示范：
					select avg(salary),employee_id from employees;
		练习：
			1.查询员工表中的最大入职时间和最小入职时间的相差天数(datediff)
				select datediff(max(hiredate),min(hiredate)) 相差天数 from employees;
			2.查询部门编号为90的员工个数
				select count(*) 个数
				from employees
				where department_id=90;

1.3 分组查询

语法：	
	select 分组函数，列（要求出现在group by的后面）
	from 表
	【where 筛选条件】
	group by 分组的列表
	【order by字句】
特点：
	1.分组查询的筛选条件分为两类
		where 分组前筛选:数据源为原始表 
			放在group by之前
		having 分组后筛选：数据源为分组后的结果集
			放在group by之后
		分组函数做条件肯定是放在having字句中
		能用分组前筛选的优先选用分组前筛选
	2.group by字句支持单个字段、多个字段分组（多个字段之间用逗号隔开，没有顺序之分）
	3.可以添加排序（排序放在最后面）
	案例1：查询每个工种的最高工资
		select max(salary),job_id
		from employees
		group by job_id;
	案例2：查询每个位置上的部门个数
		select count(*)
		from department_id
		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
			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;
			注：where是用在 group by之前，表示从employees表中筛选。
			having是在已经查询到的表中继续进行筛选，用于group by之后
		案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
			1.查询每个工种有奖金的员工的最高工资
				select max(salary),job_id
				from employees
				where commission_pct is not null
				group by job_id;
			2.在1的基础上查询>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的领导编号是哪个，以及其最低工资
			1.查询>102领导编号的领导手下的最低工资
				select min(salary),manager_id
				from employees
				where manager_id>102
				group by manager_id;
			2.在1的基础上查询最低工资>5000的领导编号
				select min(salary),manager_id
				from employees
				where manager_id>102
				group by manager_id
				having min(salary)>5000;
	按表达式或函数分组
		案例：按员工姓名的长度分组,查询每一组的员工个数，筛选员工个数>5的有哪些
			1.查询每个长度的员工个数
				select count(*),length(last_name) len_name
				from employees
				group by length(last_name);
			2.添加筛选条件
				select count(*),length(last_name) len_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
			where department_id is not null
			group by department_id,job_id
			order by avg(salary) desc;

1.4 连接查询

含义：又称多表查询，当查询字段来于多个表时，就会用到连接查询
笛卡尔乘积现象：表1有n行，表2有m行，结果有m*n行
	发生原因：没有有效的连接条件
	如何避免：添加有效的连接条件
	select name,boyname from boys,beauty
	where beauty.boyfriend_id=boys.id;
分类：按年代分类
	    sql92标准：仅仅俺支持内连接
		sql99标准【推荐】：支持内连接+外连接（左+右）+交叉连接
	  按功能分类
		内连接：
			等值连接
			非等值连接
			自连接
		外连接:
			左外连接
			右外连接
			全外连接
		交叉连接
		一、sql92标准：
			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;
				1、为表起别名
					提高简洁度
					区分多个重名的字段
					注意：如果为表起了别名，就不能用原来的表名进行限定
					案例：查询员工名、工种号、工种名
						select e.last_name,e.job_id,j.job_title
						from employees e,jobs j
						where e.job_id=j.job_id;
				2.表的顺序性可以调整
				3.可以添加筛选
					案例：查询有奖金的员工名、部门名
						select last_name,department_name,commission_pct
						from employees e,departments d
						where e.department_id=d.department_id
						and e.commission_pct is not null;
					案例2：查询城市名中第二个字符为o的部门名和城市名
						select department_name,city
						from departments d,locations l
						where d.location_id=l.location_id
						and city like '_o%';
				4.添加分组
					案例1：查询每个城市的部门个数
						select count(*) 个数,city
						from departments d,locations l 
						where d.location_id=l.location_id
						group by city;
					案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
						select department_name,d.manager_id,min(salary)
						from departments d,employees e 
						where d.department_id=e.department_id
						and commission_pct is not null
						group by department_name,manager_id;
				5.添加排序	
					案例：查询每个工种的工种名和员工的个数，并且按照员工个数降序
						select job_title,count(*)
						from employees e,jobs j
						where e.job_id=j.job_id
						group by job_title
						order by count(*) desc;
				6.实现三表连接
					多表等值连接的结果为多表的交集部分
					n表连接，至少需要n-1个连接条件
					多表的顺序没有要求
					一般需要为表起别名
					可以搭配前面介绍的所有字句搭配使用
					案例：查询员工名、部门名和所在城市
						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 g
					where salary between g.lowest_sal and g.highest_sal
					and g.grade_level='A'; 查看级别为A的
					注：使用salary字段判断所在范围
			3.自连接
				案例：查询员工名和上级的名称
				select	e.employee_id,e.last_name,m.employee_id,m.last_name
				from employees e,manager_id m
				where e.manager_id=m.employee_id;

2. 练习

		1.显示所有员工的姓名，部门编号和部门名称
			select	last_name,d.department_id,department_name
			from	employees e,departments d
			where e.department_id=d.department_id;
		2.查询90号部门员工的job_id和90号部门员工的location_id
			select	e.job_id,p.location_id
			from	employees e,departments,d  
			where 	e.department_id=d.department_id
			and	e.department_id=90
		3.查询所有有奖金的员工的姓名、部门名、位置编号、城市
			select	 e.last_name,d.department_name,l.location_id,l.city
			from	employees e,departments d,locations l 
			where	e.department_id=d.department_id
			and		d.location_id=l.location_id
			and		e.commission_pct is not null;
		4.查询每个工种、每个部门的部门名、工种名和最低工资
			select	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	job_title,department_name;
		5.查询每个国家下的部门个数大于2的国家编号
			select	country_id,count(*) 部门个数
			from	departments d,locations l 
			where	d.location_id=l.location_id
			group by	country_id
			having	count(*)>2;