小白菜学mysql 笔记（一）--DQL查询语言_三、查询部门名为 sal 或 it 的员工信息-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_44048781/article/details/102091379

在B站看的学习视频，女老师，讲的挺详细的，点击下面链接
尚硅谷

一、sql简介

	1.1、简介
	
		数据库：持续化存储，优化读写，保证数据的有效性
		关系型数据库：基于E-R模型，sql语言
		分类：
			文档型：类似word，sqlite、移动终端
			服务型：不能直接访问
		设计：
			三范式（规则模型）：
					列不可拆分：数据不能拆分、
					唯一标识：定位区分每一行数据都是实体、
					引用主键：引用唯一标识.
		关系及存储：
			1对1:一个对象A对应着一个对象B
				一个对象B对应着一个对象A
				关系可已存入A或B对象中,
			1对多:一个对象A对应着N个对象B
				一个对象B对应着一个对象A
				关系存入B,
			多对多:一个对象A对应着N个对象B
				一个对象B对应着M个对象A
				关系存入新建的关系表中

	1.2MySQL:
	
		DB:database保存有组织的数据
		DBMS:database management system.数据库是DBMS创建和操作的容器
			-基于共享文件系统(access)
			-基于客户机服务器的DBMS
		SQL:structure qury language结构化查询语言
		
		1.表:
			-列(字段,属性),行(元组),分量(元组中的一个属性)
			
	1.3登录：
		开启服务 net start/stop mysql
		1.mysql自带的客户端，只限root
		2.windows的客户端
			mysql 【-h 主机名 -P 3306】 -u 用户名 -p密码

二、sql 常用命令

show database;#查看所有库 
use 库名；#进入库
show table；#查看库的表
show xx form 库名；#查看库查找，未进去
create table 表名(
	列名 列类型,
	列名 列类型,
);
select database();#查询先所在的库
desc 表名;#查看表结构结构
select * form 表名;查看表
delete  where
select version();或退出mysql --version,mysql --V#查看mysql版本

2.1语法规范
	1.大小写不区分,关键字大写 表名列名小写
	2.分号;结尾
	3.注释
		单行#文字或-- 文字
		多行:/*文字*/

三、DQL语言:数据查询

	3.1基础查询:
	/*
	select 查询列表 from 表名
	类似于打印
	特点
	1.查询列表可以是：表中的字段、常量值、表达式、函数
	2.查询结果是一个虚拟的表格
	*/
	#必须加
	USE myemployees;
	SELECT last_name FROM employees;

	SELECT last_name,salary FROM employees;

	SELECT last_name,salary FROM employees;

	SELECT 
		`phone_number`,
		`email`,
		`commission_pct`,
		`department_id`
	FROM 
		employees;
		#多个
		#``为着重号,标识为字段号
		#select `NAME` from 
		#f9为执行,f12为格式化
		
		
		
		
	SELECT * FROM employees;#所有

	#查询常量值
	SELECT 100;
	SELECT 'john';
	#查询表达式
	SELECT 100%98;
	#查询函数
	SELECT 	VERSION();
	#起别名
	/*
	查询的字段有重名的字段,使用别名可以区分开来
	*/
	SELECT 100%98 AS 结果;
	SELECT last_name AS 姓,first_name AS 名 FROM employees;
	SELECT last_name 姓,first_name 名 FROM employees;
	#插入特殊的别名,用双引号
	SELECT salary AS "OUT put" FROM employees;

	#8.去重 DISTINCT

	#查询员工表中涉及到的部门编号

	SELECT DISTINCT department_id FROM employees;

	#9.+号的作用
	#查询员工名和姓连接成一个字段,并显示为姓名
	#仅有一个功能:运算符
	/*
	select 100+20;两个操作数同为数值型,则做加法
	select '20'+100;其中一个为字符型,试图将字符转为数值
				转换视为字符返回为零
	select null+10; 其中一方为null,结果肯定为null
	*/
	SELECT CONCAT('a','b','c')AS 结果;
	
	SELECT
		CONCAT(last_name,first_name) AS 姓名
	FROM 
		employees;
		
	DESC departments;#显示结构

	SELECT 
		IFNULL(commission_pct,0) AS 奖金率,
		commission_pct
	FROM 
		employees;
		
		
	SELECT 
		CONCAT(`first_name`,',',`last_name`,',',IFNULL(commission_pct,0)) AS out_put
		#ifnull(字段,指定值)判断某字段或者表达式是否为null 是则返回指定值,否则返回原值
		#isnull(字段)判断某字段或者表达式是否为null,是则返回1,否则为0
	FROM 
		employees;
		
3.2条件查询:
	/*
	语法:
	select
		查询列表
	from
		表名
	where
		筛选条件;
	执行顺序:表,条件,查询列表
	分类:
		1.条件表达式筛选
		条件运算符:
			< > = != <>(不等于) <= >=
		2.逻辑表达式筛选
		逻辑运算符:
			作用:连接条件表达式
			&& || !
			and or not
		3.模糊查询
			like
			between and
			in
			is null 
	*/

	#1.条件表达式
	
	#查询工资大于12000的员工信息
	SELECT
		*
	FROM
		employees
	WHERE
		salary>12000;

	#查询部门编号不等于90的员工名和部门编号
	SELECT
		last_name,
		department_id
	FROM
		employees
	WHERE
		department_id<>90;
		
	#2. 逻辑表达式
	#查询工资在10000到20000的员工名,工资及奖金
	SELECT
		last_name,
		salary,
		commission_pct
	FROM
		employees
	WHERE
		salary>=10000 AND salary<=20000;
		
	#查询部门编号在90到110之间,或者工资高于15000的员工信息
	SELECT
		*
	FROM
		employees
	WHERE
		NOT(department_id>=90 AND department_id<=110) 
		OR 
		salary>15000;
	
3.3模糊查询
	/*
	like
		特点
		①一般搭配通配符使用
		%：任意多个字符
		_: 任意单个字符
	between
	in
	is null|is not null
	*/
	#1.like
	#查询员工名中包含字符a的员工信息
	SELECT 
		*
	FROM 
		employees
	WHERE 
		last_name LIKE '%a%'#%为通配符

	# 查询员工名中第三个字符为n,第五个字符为l的员工名和工资
	SELECT 
		last_name,
		salary
	FROM 
		employees
	WHERE 
		last_name LIKE '__n_l%';

	#查询员工名中第二个字符为_的员工名

	SELECT 
		last_name
	FROM 
		employees
	WHERE 
		last_name LIKE '_\_%';#\xx 为转义

	SELECT 
		last_name
	FROM 
		employees
	WHERE 
		last_name LIKE '_$_%' ESCAPE '$';#指定转义字符
		
	#2.between and
	/*
	提高语句简洁度
	包含两个临界值
	两个临界值不可颠倒
	*/
	#查询员工编号在100到120的员工信息

	SELECT 
		*
	FROM 
		employees
	WHERE 
		employee_id BETWEEN 100 AND 120;
		
	#3.in
	/*
	判断某字段的值是否属于in列表的一种
	in列表中的值类型统一或者兼容
	不支持通配符

	*/
	#查询员工的工种编号是 IT_PROG,AD_VP,AD_PRES中的员工名和种种编号

	SELECT 
		last_name,
		jod_id
	FROM 
		employees
	WHERE 
		jod_id IN ('IT_PROT','AD_VP','AD_PRES');
		
	#4.is null
	/*
	=或<>不能判断null
	*/

	#查询没有奖金的员工名和奖金率
	SELECT 
		last_name,
		commission_pct
	FROM 
		employees
	WHERE 
		commission_pct IS NULL;
		
	#查询有奖金的员工名和奖金率
	SELECT 
		last_name,
		commission_pct
	FROM 
		employees
	WHERE 
		commission_pct IS NOT NULL;
		
	#安全等于  <=> 可读性差

	#查询没有奖金的员工名和奖金率
	SELECT 
		last_name,
		commission_pct
	FROM 
		employees
	WHERE 
		commission_pct <=> NULL;
		
	#查询工资为12000的员工信息
	SELECT 
		*
	FROM 
		employees
	WHERE 
		salary <=>12000;

	/* 
	is null:仅可以判断null
	IS NOT NULL
	<=>判断数值
	*/

	#查询没有奖金且工资小于18000的工资和名

	SELECT 
		salary,
		last_name
		
	FROM 
		employees
	WHERE 
		salary <=>12000
		AND
		commission_pct IS NULL;
			
			
3.4.排序查询

	/*
		select * from 表名;
		select 查询列表
		from 表名
		[where 筛选条件]
		order by 排序列表 [asc升序|desc降序]#默认为升序;
		放在查询语句的最后面，除了 limit语句
	*/

	#查询员工信息,要求工资从高到低排序
	SELECT 
		* 
	FROM
		employees
	ORDER BY
		salary DESC;

	#查询部门编号>= 90 的员工信息,按入职时间先后排序
	SELECT 
		* 
	FROM
		employees
	WHERE
		department_id>=90
	ORDER BY
		hiredate ASC;

	#查询按表达式排序,按年薪的高低显示员工信息和年薪
	SELECT 
		* ,
		salary*12*(1+IFNULL(commission_pct,0)) 年薪
	FROM
		employees

	ORDER BY
		salary*12*(1+IFNULL(commission_pct,0)) ASC;
		#筛选值可为别名 年薪
		
	#查询 按姓名的长度显示员工姓名和工资
	SELECT 
		LENGTH(last_name) 字节长度,last_name,salary
	FROM
		employees

	ORDER BY
		LENGTH(last_name) DESC;
		
	#查询员工信息,要求先按工资排序升序,再按员工编号排序降序
	SELECT 
		*
	FROM
		employees

	ORDER BY
		salary ASC,employee_id DESC;	

	#选择工资不在8000到17000的员工的姓名和工资,按工资排序
	SELECT last_name,salary
	FROM employees
	WHERE salary NOT BETWEEN 8000 AND 17000
	ORDER BY salary DESC;
3.5.常见单行函数

	/*
	概念:类似于方法,将一组逻辑语句封装在方法体中,对外暴露方法名
	调用:	select 函数名(实参列表) [from 表];
	特点:
	1.单行函数:concat,length,ifnull.....
		字符函数,数学函数,日期函数,流程控制函数
	2.分组函数:做统计使用,统一函数,聚合函数,组函数
		
	*/

	3.5.1字符函数
		#1.length
		SELECT LENGTH('john');
		SELECT LENGTH('张三丰haha');#汉字占三个字节

		#查询字节参数
		SHOW VARIABLES LIKE '%char%'


		#2.concat 拼接字符串
		SELECT CONCAT(first_name,'_',last_name) 姓名
		FROM employees;

		#3.upper,lower
		SELECT UPPER('john');
		SELECT UPPER('joHn');
		
		#将姓变大写,名小写,然后拼接
		SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名
		FROM employees;

		#4.substr,substring 索引从1开始
		/*
		substr('xxxx',start,length),获取从第start位取length长度的字符串
		*/
		SELECT SUBSTR('李莫愁爱上了陆展元',6) out_put;
		SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;

		#姓名中手字符大写,其他字符小写,用下划线拼接

		SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),
				'_',
				LOWER(SUBSTR(last_name,2))) out_put
		FROM employees;

		#5.instr ('父串','子串')返回子串在父串第一次出现的索引,如果找不到则返回0

		SELECT INSTR('杨不悔爱上了殷六侠','殷六侠')AS out_put;

		#6.trim('xx'   from   '字符串')去掉字符串前后指定字符串,默认为空格
		SELECT LENGTH(TRIM('    张翠山     ')) AS out_put;
		SELECT LENGTH('a'  FROM  TRIM('aaaaaaaa张aaaaa翠山aaaaaa')) AS out_put;

		#7.lpad('str',len,'char') 用指定字符实现左填充显示指定长度,如长度小于字符串,则删减

			SELECT LPAD('殷素素',10,'*') AS out_put;

			#rpad('str',len,'char') 用指定字符实现右填充长度

			SELECT RPAD('殷素素',10,'*') AS out_put;


		#8.replace(str,'bestr','newstr') 替换
		SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
		
		
	3.5.2数学函数
		
		#1.round(num,bit)四舍五入,bit为保留小数后bit位,默认返回证书
		SELECT ROUND(-1.55);
		SELECT ROUND(-1.457,2);

		#2.ceil()向上取整,返回>=该参数的最小整数

		SELECT CEIL(1.00);
		SELECT CEIL(-1.02);

		#3.floor(),返回<=改参数的最大整数

		SELECT FLOOR(-9.99);

		#4.truncate(num,bit) 截断到小数点后bit位,默认为整数

		SELECT TRUNCATE (1.6999,1);

		#5.mod(x,y)==x%y,取余,被除数为正,结果则为正
		#mod(a,b):a-a/b*b

		SELECT MOD(10,-3);
		SELECT 10%3;
		
		#6.rand():随机返回0~1的小数
		
		
	3.5.3日期函数
		#1.now()返回当亲系统日期+时间
		SELECT NOW();

		#2.curdate 返回当前系统日期,不包含时间

		SELECT CURDATE();

		#3.durtime 返回当前系统时间不包含日期

		SELECT CURTIME();

		#4.可以获取指定部分,年year(),月month(),日day(),小时hour(),分钟Minute(),秒second()


		SELECT YEAR(NOW()) 年;
		SELECT YEAR('1998-1-1') 年;

		SELECT YEAR(hiredate) 年
		FROM employees;

		SELECT MONTHNAME(NOW()) 月;#显示月份的英文


		#str_to_date():将日期格式的字符转换成指定格式的日期
		/*
		%Y:四位的年份
		%y:两位的年份
		%m:月份（01,02.....11,12）
		%c:月份(1,2...11,12)
		%d:日(01,02...30,31)
		%H:小时,24小时制
		%h:小时,12小时制
		%i:分钟,00,01...59
		%s:秒,00,01....59
		*/

		SELECT STR_TO_DATE('1998-3-2','%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');


		#date_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;
		
		#datediff():返回两个日期相差的天数
		
	3.5.4其他函数
		SELECT VERSION();
		SELECT DATABASE();
		SELECT USER();
		select password("王世玉"):返回一串加密的
		select md5("字符"):返回字符的md5加密形式
		
	3.5.5,流程控制函数
		#1.if(表达式,true,false)函数: 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:语句;break;
		}

		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;

		/*
		case 函数的使用二
		if(){
			语句1;
		}else if(语句2){
			语句2
		}
		...
		else{语句n}


		case 
		when 条件1 the要显示的值1或者语句1
		when 条件2 the要显示的值2或者语句2
		........
		else 要显示的值n或语句n
		end
		*/
		/*
		查询员工的工资情况
		如果工资>20000,显示A
		如果工资>15000,显示A
		如果工资>10000,显示A
		否则显示 D
		*/

		SELECT salary,last_name,
		CASE 
		WHEN salary>20000 THEN 'A'
		WHEN salary>15000 THEN 'B'
		WHEN salary>10000 THEN 'C'
		ELSE 'D'
		END AS 工资级别
		FROM employees;
		
3.6常见分组函数

	/*
	功能:用作统计使用,聚合函数,统计函数,组函数

	分类:
	sum 求和,avg 平均值,max 最大值,min 最小值, count 计算个数

	参数类型支持
	1.sum avg 处理数值型
	2.max min  conut 支持处理任何类型
	3.忽略null值:sum avg max min count 
	*/

	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 SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最大
	FROM employees;

	#和distinct 搭配

	SELECT SUM(DISTINCT salary),SUM(salary) 
	FROM employees;

	SELECT COUNT(DISTINCT salary),COUNT(salary)
	FROM employees;

	#count 函数的详细介绍

	#统计行数
	SELECT COUNT(*) 
	FROM employees;

	#同上,可填任何常量值
	SELECT COUNT(1)
	FROM employees;

	/*
	myisam存储引擎下,count(*)效率高
	innodb下,效率差不多,比count(字段)要高一些
	*/

	#和分组函数一同查询的字段有限制,要求去是group by后的字段
	SELECT AVG(salary0),employee_id 
	FROM employees;#无意义



	#查询员工中最大入职时间和最小入职时间的相差天数(diffrence)

	SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) 相差天数
	FROM employees;
	
	
	3.6.1分组查询
		/*
		语法
		select 分组函数,列(要去出现在group by后面)
		from 表
		where 筛选条件
		***group by 分组列表
		order by 子句;

		查询列表必须特殊,要求是分组函数和group by 后出现的字段
		*/

		#查询每个工种的最高工资
		SELECT MAX(salary),job_id
		FROM employees
		GROUP BY job_id;

		#查询每个位置上的部门个数
		SELECT COUNT(*) 个数,location_id
		FROM employees
		GROUP BY location_id;

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

		#查询有奖金的每个领导的员工的最高工资

		SELECT MAX(salary),manager_id
		FROM employees
		WHERE commission_pct IS NOT NULL
		GROUP BY manager_id;

		#添加分组后复杂筛选条件
		#查询哪个部门的员工个数>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;


		#查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
		#①查询每个工种有奖金的员工的最高工资
		SELECT MAX(salary),job_id
		FROM employees
		WHERE commission_pct IS NOT NULL
		GROUP BY job_id;

		#②继续筛选,最高工资大于12000
		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)
		FROM employees
		GROUP BY LENGTH(last_name)
		HAVING COUNT(*)>5;


		#按多个字段分组
		#查询每个部门每个工种的平均工资
		SELECT AVG(salary),department_id,job_id
		FROM employees
		GROUP BY job_id,department_id;

		#条件排序
		#查询每个部门每个工种的平均工资,按平均工资高低排序
		SELECT AVG(salary),department_id,job_id
		FROM employees
		WHERE department_id IS NOT NULL
		GROUP BY job_id,department_id
		HAVING AVG(salary)>10000
		ORDER BY AVG(salary) DESC;
		
3.7、sql99标准

	连接查询
		/*
		含义:多表查询,当查询的字段来自于多个表
		#笛卡尔乘积现象 表1有m行 表2有n行,结果又m*n行
		原因:没有有效的连接条件,表名.字段

		有效连接的分类
			sql99标准:

			功能分类:
			内连接:
				等值连接
				非等值连接
				自连接
			外连接:
				左外连接
				右外连接
				全连接
			交叉连接

		*/

		SELECT * FROM beauty;

		SELECT * FROM boys;

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

	3.7.1等值连接
		/*
		1.多表等值连接的结果为多表的交集部分
		2.n表连接,至少需要n-1个连接条件
		3.多表的顺序没有要求
		4.为表起别名
		5.可以搭配所有子句使用,排序,分组,筛选
		*/

		#查询女神名和对应的男神名
		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;

		#为表起别名 简洁 区分多个重名的字段 不能使用表名限定
		#查询员工名,工种号,工种名
		SELECT e.last_name,e.job_id,j.job_title
		FROM employees e,jobs j
		WHERE employees.`job_id`=jobs.`job_id`;

		#加筛选
		#查询有奖金的员工名,部门名
		SELECT e.last_name,e.`commission_pct`,d.`department_name`
		FROM employees e,departments d
		WHERE e.`department_id`=d.`department_id` 
		AND e.`commission_pct` IS NOT NULL;


		#查询城市名中第二个字符为o的部门名和城市名

		SELECT d.`department_name`,l.`city`
		FROM locations l,departments d
		WHERE d.`location_id`=l.`location_id`
		AND l.`city` LIKE '_o%';

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

		#查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资

		SELECT department_name,d.manager_id,MIN(salary)
		FROM departments d,employees e
		WHERE e.`commission_pct` IS NOT NULL
		AND d.`department_id`=e.`department_id`
		GROUP BY department_name;

		#加排序
		#查每个工种的工种名和员工的个数,并且按员工个数降序

		SELECT job_title,COUNT(*)
		FROM employees e ,jobs j
		WHERE e.`job_id`= j.`job_id`
		GROUP BY job_title 
		ORDER BY COUNT(*) DESC;

		#三表连接
		#查询员工名,部门名,所在的城市

		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%'

		ORDER BY department_name DESC;
	3.7.2非等值连接
		
		#查询员工的工资和工资级别

		SELECT last_name,salary,grade_level
		FROM employees e,job_grades g
		WHERE e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;


	3.7.3自连接
	
		#查询员工名和上级的名称

		SELECT e.employee_id,e.last_name,m.`employee_id` 领导编号,m.`last_name` 领导名字
		FROM employees e,employees m
		WHERE e.manager_id = m.`employee_id`;
	3.8sql99语法
		/*
			select 查询列表
			*
			from 表1 别名【连接类型】
			join 表2 别名
			on 连接条件
			*
			where 筛选条件
			group by 分组
			hving 筛选条件
			order by 排序列表
			
		*内连接：inner
		*外连接
			左外*：left【outer】
			右外*：right【outer】
			全外：full【outer】
		交叉连接：cross
			
		*/
		#3.8.1内连接
			/*
				select 查询列表
				*
				from 表1 别名
				inner join 表2 别名
				on 连接条件
				*
				
			分类:
				等值,非等值,自连接
			*/

			#1.等值连接
			#查询员工名,部门名
			SELECT last_name,department_name
			FROM employees e
			INNER JOIN departments d
			ON e.`department_id`=d.`department_id`;


			#加筛选,查询名字中包含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的城市名和部门个数
			SELECT city,COUNT(*)
			FROM locations l
			INNER JOIN departments d
			ON l.`location_id`=d.`location_id`
			GROUP BY city
			HAVING COUNT(*)>=3;


			#查询哪个部门的部门员工个数>3的部门名和员工个数,并按个数降序
			SELECT department_name,COUNT(*) 员工个数
			FROM employees e
			INNER JOIN departments d
			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;

			#非等值连接
			#查询员工的工资级别
			SELECT salary,grade_level
			FROM employees e
			JOIN job_grades g 
			ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;


			#查询每个工资级别的个数大于20,并且降序

			SELECT grade_level,COUNT(*) 个数
			FROM employees e
			JOIN job_grades g 
			ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
			GROUP BY grade_level
			HAVING COUNT(*)>20
			ORDER BY grade_level DESC;


			#自连接

			#查询员工的名字和上级的名字
			SELECT e.last_name 员工,m.last_name 领导
			FROM employees e
			JOIN employees m ON e.`manager_id`=m.`employee_id`;

			#查询员工姓名中包含k的名字和上级的名字
			SELECT e.last_name 员工,m.last_name 领导
			FROM employees e
			JOIN employees m ON e.`manager_id`=m.`employee_id`
			WHERE e.`last_name` LIKE "%k%";
			
		3.8.2外连接
			/*
			应用场景:用于查询一个表中有的,另外一个表中没有的记录

			特点:
			1.外连接的查询结果为主表中的所有记录
				如果从表中没有和他匹配的,则显示匹配的值
				如果从表中没有和他匹配的,则显示null
				外连接查询结果=内连接结果+主表中有而从表没有的记录
			2.左外连接,left join 右边的是主表
			  右外连接,right join 左边的是主表
			3.左外和右外交换两个表的顺序,可以实现同样的效果
			4.全外连接=内连接的结果+表1中有但表2中没有的+表2中有的但表1没有的
				mysql不支持
			*/

			#引入:查询男朋友不在男神表的女生名
			SELECT b.`name` 
			FROM beauty b
			LEFT OUTER JOIN boys bo
			ON b.`boyfriend_id`= bo.`id`
			WHERE bo.`id` IS NULL;

			#查询那个部门没有员工
			#左外
			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;
			
		#3.8.3交叉连接
			#笛卡尔乘积
			SELECT b.*,bo.*
			FROM beauty b
			CROSS JOIN boys bo;

		练习题
		#查询编号>3的女神的男朋友信息,如果有则列出详细信息,没有则用null填充

		SELECT b.`id`,b.`name`,bo.*
		FROM beauty b
		LEFT OUTER JOIN boys bo
		ON b.`boyfriend_id`=bo.`id`
		WHERE b.`id`>3;

		#查询哪个城市没有部门

		SELECT city,d.*
		FROM locations l
		LEFT OUTER JOIN departments d
		ON l.`location_id`=d.`location_id`
		WHERE d.`location_id` IS NULL;

		#查询部门名为sal或it的员工信息

		SELECT d.`department_name`,e.*
		FROM employees e
		LEFT JOIN departments d
		ON e.`department_id`=d.`department_id`
		WHERE d.`department_name` IN ('SAL','IT');
		
		
	3.9子查询
		/*
		含义:
		出现在其他语句的中的select语句,称为子查询或内查询
		外部的查询语句,称为主查询

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


			#3.9.1where或having后面
				/*
				特点:
				1.子查询放在小括号内
				2.子查询一般放在条件的右侧
				3.标量子查询,一般搭配着单行操作符使用> < <= >= <>

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

				4.子查询的执行优先于主查询,主查询的条件用到了子查询的结果
				*/

				#1.标量子查询(单行)				
				#查询谁的工资比abel高?
				#①查询abel的工资
				SELECT salary
				FROM employees
				WHERE last_name='Abel';
				#②查询员工的信息,满足salary>①的结果
				SELECT *
				FROM employees
				WHERE salary>(
					SELECT salary
					FROM employees
					WHERE last_name='Abel'
				);

				#查询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
				);


				#加分组,查询公司工资最少的员工的last_name,job_id和salary
				SELECT last_name,job_id,salary
				FROM employees
				WHERE salary=(
					SELECT MIN(salary)
					FROM employees
				);


				#查询最低工资大于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.列子查询(多行子查询)
				/*
				多行操作符:
					in/not in 等于列表中的一个
					any/some 和子查询返回的某一个值比较
					all 和子查询返回的所有值比较
					in 等于  = any
					not 等于 <> all
				*/

				#查询location_id是1400或1700的部门中的所有员工姓名

				SELECT last_name
				FROM employees
				WHERE department_id IN (
					SELECT DISTINCT department_id
					FROM departments
					WHERE location_id=(1400,1700)
				);

				SELECT last_name
				FROM employees
				WHERE department_id =ANY (
					SELECT DISTINCT department_id
					FROM departments
					WHERE location_id=(1400,1700)
				);

				#返回其它工种中比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';
				#或
				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';


				#返回其它工种中比job_id为‘IT_PROG’工种工资都低的员工的员工号、姓名、job_id 以及salary
				SELECT employee_id,last_name,job_id,salary
				FROM employees
				WHERE salary <ALL(
					SELECT 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.行子查询(一行多列)

				#查询员工编号最小并且工资最高的员工信息

				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
				);
			#3.9.2放在select后面
				#返回结果只是一行一列
				#查询每个部门的员工个数

				SELECT d.*,(
					SELECT COUNT(*)
					FROM employees e
					WHERE e.department_id=d.`department_id`
				) 员工个数
				FROM departments d;
				
			#3.9.3放在from 后面
				#查询每个部门的平均工资的工资等级
				SELECT ag_dep.*,g.`grade_level`
				FROM (
					SELECT d.department_name,AVG(salary) ag
					FROM departments d
					JOIN employees e ON d.`department_id`=e.`department_id`
					GROUP BY d.`department_name`
				)ag_dep #起别名
				JOIN job_grades g
				ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;#连接条件
				
			#3.9.4放在exists后面（相关子查询）
				/*
				语法 
				exists（完整的查询语句）
				bool
				结果1或0
				*/
				SELECT EXISTS (
					SELECT employee_id 
					FROM employees
					WHERE salary = 30000000
				);
				
				#查询有员工的部门名
				#先执行主查询,再通过子查询过滤
				SELECT department_name
				FROM departments d
				WHERE EXISTS (
					SELECT * 
					FROM employees e
					WHERE d.`department_id`=e.`department_id`
				);

				#查询没有女朋友的男神信息

				SELECT bo.*
				FROM boys bo
				WHERE  NOT EXISTS(
					SELECT boyfriend_id
					FROM beauty b
					WHERE bo.`id`=b.`boyfriend_id`
				);


				SELECT bo.*
				FROM boys bo
				WHERE bo.`id` NOT IN(
					SELECT boyfriend_id
					FROM beauty
				);

			练习题：
			#查询和zlotkey相同部门的员工姓名和工资
			SELECT last_name,salary
			FROM employees e
			WHERE e.`department_id`=(
				SELECT department_id
				FROM employees
				WHERE last_name='Zlotkey'
			);

			#查询工资比公司平均工资高的员工号,姓名和工资

			SELECT employee_id,last_name,salary
			FROM employees e
			WHERE e.`salary`>(
				SELECT AVG(salary)
				FROM employees
			);

			#查询每部门中工资比本部门平均工资高的员工号,姓名和工资,部门号
			SELECT e.department_id,last_name,salary
			FROM employees e
			JOIN (
				SELECT AVG(salary) ag,department_id
				FROM employees
				GROUP BY department_id
			)ag_dep
			ON ag_dep.department_id = e.`department_id`
			WHERE e.`salary`>ag_dep.ag;

			#查询和姓名中包含u的员工在相同部门的员工的员工号和姓名


			SELECT employee_id,e.last_name
			FROM employees e
			WHERE e.`department_id`IN(
				SELECT DISTINCT department_id
				FROM employees
				WHERE last_name LIKE '%u%'
			);


			#查询在部门的location_id为1700的部门工作的员工的员工号
			SELECT employee_id,e.department_id
			FROM employees e
			WHERE e.`department_id`IN(#=any
				SELECT DISTINCT department_id
				FROM departments
				WHERE location_id=1700
			);

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

			#查询工资最高的员工姓名,要求frist_name和last_name显示为一列,列名为姓.名
			SELECT CONCAT(last_name,'.',first_name) 姓名
			FROM employees
			WHERE salary=(
				SELECT MAX(salary)
				FROM employees
			);
			
			
	3.10分页查询※
		/*
		应用场景:
		当显示的数据,一页显示不全,需要分页提交sql请求
		语法:
			select 查询列表
			from 表
			[join type join 表2 
			on 连接条件
			where 筛选条件
			group by 分组字段
			having 分组后的筛选
			erder by 排序的字段]
			
			limit [offset,]size;#默认为0
			#offset:要显示条目的起始索引(起始索引从0开始)
			size:要显示的条目个数
			
		特点:
		1.放在查询语句最后,执行也是最后
		2.公式
			要显示的页数page 每页的条目数size
			select 查询列表 
			from 表 
			limit (page-1)*size,size
			
		*/
		#查询前五条员工信息

		SELECT *
		FROM employees
		LIMIT 0,5;

		#查询第11-25员工信息

		SELECT *
		FROM employees
		LIMIT 10,15;

		#查询有奖金的员工信息,并且工资较高的前10名

		SELECT *
		FROM employees
		WHERE commission_pct IS NOT NULL
		ORDER BY salary DESC
		LIMIT 10;


练习题:
#1. 查询工资最低的员工信息: last_name, salary  
SELECT last_name,salary
FROM employees e
WHERE e.`salary`=(
	SELECT MIN(salary)
	FROM employees
);

#2. 查询平均工资最低的部门信息  
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) ag,department_id
			FROM employees
			GROUP BY department_id
		) ag_dep
	)
);
或
SELECT d.*
FROM departments d
JOIN(
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1
)ag_dep
ON ag_dep.department_id=d.department_id;


#3. 查询平均工资最低的部门信息和该部门的平均工资
SELECT d.*,ag_dep.ag
FROM departments d
JOIN(
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1
)ag_dep
ON ag_dep.department_id=d.department_id;


#4. 查询平均工资最高的 job 信息
SELECT j.*,ag_dep.ag
FROM jobs j
JOIN(
	SELECT AVG(salary) ag,job_id
	FROM employees
	GROUP BY job_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
)ag_dep
ON j.`job_id`=ag_dep.job_id;
  
#5. 查询平均工资高于公司平均工资的部门有哪些?
SELECT e.department_id,AVG(salary),d.`department_name`
FROM employees e
JOIN departments d
ON d.`department_id`=e.`department_id`
GROUP BY e.`department_id`
HAVING AVG(salary) >(
	SELECT AVG(salary)
	FROM employees
);

#6. 查询出公司中所有 manager 的详细信息.
SELECT e.*
FROM employees e
WHERE e.`employee_id`=ANY(
	SELECT DISTINCT manager_id
	FROM employees
);
#7. 各个部门中 最高工资中最低的那个部门的 最低工资是多少
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
);

#8. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

SELECT last_name,e.department_id,email,salary
FROM employees e
JOIN departments d ON e.`employee_id`=d.`manager_id`
WHERE d.`department_id`=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
);



#作业题
#一、查询每个专业的学生人数
SELECT majorid,COUNT(*)
FROM student
GROUP BY majorid;

#二、查询参加考试的学生中，每个学生的平均分、最高分
SELECT AVG(score),MAX(score),studentno
FROM result
GROUP BY studentno;

#三、查询姓张的每个学生的最低分大于60的学号、姓名
SELECT s.studentno,s.`studentname`,MIN(score)
FROM student s
JOIN result r
ON s.`studentno`=r.`studentno`
WHERE s.`studentname` LIKE '张%'
GROUP BY s.`studentno`
HAVING MIN(score)>60;
#四、查询每个专业生日在“1988-1-1”后的学生姓名、专业名称

SELECT m.`majorname`,s.`studentname`
FROM student s
JOIN major m
ON m.`majorid`=s.`majorid`
WHERE DATEDIFF(borndate,'1988-1-1')>0
GROUP BY m.`majorid`;


#五、查询每个专业的男生人数和女生人数分别是多少

SELECT COUNT(*),sex,majorid
FROM student
GROUP BY sex,majorid;
#六、查询专业和张翠山一样的学生的最低分
#①查询张翠山的专业编号
SELECT majorid
FROM student
WHERE studentname = '张翠山'

#②查询编号=①的所有学生编号
SELECT studentno
FROM student
WHERE majorid=(
	SELECT majorid
	FROM student
	WHERE studentname = '张翠山'

)
#②查询最低分
SELECT MIN(score)
FROM result
WHERE studentno IN(

	SELECT studentno
	FROM student
	WHERE majorid=(
		SELECT majorid
		FROM student
		WHERE studentname = '张翠山'

	)
)

#七、查询大于60分的学生的姓名、密码、专业名

SELECT studentname,loginpwd,majorname
FROM student s
JOIN major m ON s.majorid=  m.majorid
JOIN result r ON s.studentno=r.studentno
WHERE r.score>60;
#八、按邮箱位数分组，查询每组的学生个数
SELECT COUNT(*),LENGTH(email)
FROM student
GROUP BY LENGTH(email);
#九、查询学生名、专业名、分数

SELECT studentname,score,majorname
FROM student s
JOIN major m ON s.majorid=  m.majorid
LEFT JOIN result r ON s.studentno=r.studentno


#十、查询哪个专业没有学生，分别用左连接和右连接实现
#左
SELECT m.`majorid`,m.`majorname`,s.`studentno`
FROM major m
LEFT JOIN student s ON m.`majorid` = s.`majorid`
WHERE s.`studentno` IS NULL;

#右
SELECT m.`majorid`,m.`majorname`,s.`studentno`
FROM student s
RIGHT JOIN  major m ON m.`majorid` = s.`majorid`
WHERE s.`studentno` IS NULL;
#十一、查询没有成绩的学生人数

SELECT COUNT(*)
FROM student s
LEFT JOIN result r ON s.`studentno` = r.`studentno`
WHERE r.`id` IS NULL
	
	
		3.11union联合查询
			/*
			union 联合 合并:将多条查询语句的结果合并成一个结果
			语法
			查询语句1
			union
			查询语句2
			union
			.....
			
			应用场景:
			要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致
			
			特点:
			1.要求多条查询语句的查询列数是一致的
			2.要求多条查询语句的查询的每一列的类型和顺序最好一致
			3.union 会自动去重,需要加union all可以包含重复项
			*/
			#查询部门编号>90或者邮箱中包含a的员工信息

			SELECT * 
			FROM employees
			WHERE email LIKE '%a%' OR department_id>90;

			SELECT * 
			FROM employees
			WHERE email LIKE '%a%' 
			UNION 
			SELECT * 
			FROM employees
			WHERE department_id>90;