Mysql_DQL数据查询语言
employees员工表 部门表
位置表 工种表
DQL数据查询语言：
进阶一： 基础查询 语法：select 询查 from 表名;
特点：1.查询列表可以是：表中的字段、常量值、表达式、函数，也可以是多个。
2.查询的结果是一个虚拟的表格。
一、查询表中的单个字段
select last_name from employees;
#2.查询表中的多个字段(将查询的字段进行自定义)
SELECT last_name AS '姓',salary AS '薪资',email AS '邮箱' FROM employees;
SELECT last_name '姓',salary '薪资',email '邮箱' FROM employees;
#3.查询表中的所有字段(两种方式 )
方式一：SELECT
`commission_pct`,`manager_id`,`department_id`,`hiredate`
FROM
employees ;
方式二： SELECT * FROM employees;
#4.查询常量值 字符型和日期型的常量值必须用单引号，数值型不需要
SELECT 100;
SELECT '俊杰';
#5.查询表达式 类似于运算
SELECT 120/5;
#6.查询函数 调用一个方法或函数 得到返回值并显示
SELECT VERSION(); select 函数名()
#7.查询字段起别名
1.便于理解
2.如果要查询的字段有重名的情况，使用别名区分开来
#方式一：使用as
SELECT last_name AS 姓,first_name AS 名 FROM employees;
SELECT 120/5 AS 运算;
#方式二：使用空格
SELECT last_name 姓,first_name 名 FROM employees;
#如果要给字段起别名 如个给salary字段起名为out put用双引号 有关键字,空格,字符等
SELECT salary "out put" FROM employees;
#8.去除重复的数据 用distinct关键字
SELECT DISTINCT department_id "部门ID" FROM employees;
#9.+号的作用
java中的+号：1.运算符，两个操作数都为基本类型进行数值运算
2.连接符，只要有一个操作数为字符串做连接
mysql中的+号：仅仅只有一个功能：运算符
select 100+98; 两个操作都为数值型，则做数值运算
select 'tomcat'+98;只要其中有一个为字符型，试图将字符转化为数值型
如果转换成功，则继续做加法运算;
如果转换失败，则字符型转换为0再做加法运算
select 'tomcat'+98; 结果为98
select null+98; 只要其中一方为null，其结果一定为null
#案例查询员工名和姓连接成一个字段，并显示为一个字段 使用concat()函数
SELECT CONCAT('hello，','世界','！！') AS 结果;
SELECT CONCAT(last_name,first_name) "姓名" FROM employees;
ifnull(expr1，expr2)函数 如果expr1字段为null，则返回expr2参数
如：IFNULL(commission_pct, '无奖金率') commission_pct为null返回无奖金
isnull(expr)函数 判断某字段或表达式是否为null，如果是null则返回1，否则返回0;
select isnull(commission_pct) from employees;
进阶二：条件查询 select 查询列表 from 表名 where 筛选条件; 一是先查询库中是否有这个表，二进行条件筛选，第三部再进行查询
select 查询列表 from 表名 where 筛选条件;
一、按条件表达式筛选
条件运算符：> < = != <> >= <=
#案例1.查询工资>12000的员工
SELECT * FROM employees WHERE salary>12000;
#案例2.查询部门编号不等于90号的员工名和部门编号
SELECT last_name 员工名,department_id 部门编号 FROM employees WHERE department_id<>90;
二、按逻辑运算符筛选
逻辑运算符：&& || ！ 在mysql中 and or not 作用：连接条件表达式
1.&&和and：两条件都为true，结果为true，反之为false
2.||和or：只要有一个条件为ture，结果为ture，反之为false
3.！和not：连接条件本身为false，取反结果为true，反之false
#案例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 OR department_id>110 OR salary>15000;
#NOT(department_id>90 AND department_id<110) 取反
SELECT * FROM employees WHERE NOT(department_id>=90 AND department_id<=110) OR salary>15000;
#案例3.查询员工年薪大于20万的员工名和年薪
SELECT last_name,salary*12*(1+IFNULL(commission_pct,0)) '年薪'FROM employees WHERE salary*12*(1+IFNULL(commission_pct,0)) >200000;
三、模糊查询
用到的关键字：
like(像) ，between and 在...之间，in，is null | is not null
# 1.like(像)
特点：一般与通配符使用在一起，可以用于字符型数值'tom'或者数值'2018'
1. % 代表任意多个字符
2. _ 代表任意单个字符
%a%：查询字段内容，包含a前后任意多个字符
%a：查询a前面有任意多个字符
a%：查询a后面有任意多个字符
#案例1.查询员工名中包含字符a的员工信息
SELECT * FROM employees WHERE last_name LIKE '%a';
SELECT * FROM employees WHERE last_name LIKE 'a%';
#案例2：查询员工名中第二个字符为o，第五个字符为h的员工名和薪资
SELECT last_name,salary FROM employees WHERE last_name LIKE '_o__h%';
#案例3：查询员工名第二个字符为_的员工名
#通过转义的字符 将通配符转义为普通的字符 escape关键字 '定义的转义符号' 或 \
SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';
SELECT last_name FROM employees WHERE last_name LIKE '_\_%';
#2.between and 在...之间
Ⅰ.使用between and 可以提高语句简洁度
Ⅱ.包含零界值 [100,120]
Ⅲ.两个零界值不能挑换顺序
#案例1：查询员工编号在100到120之间的员工信息
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
SELECT * FROM employees WHERE employee_id>=100 AND employee_id<=120;
#3.in关键字
含义：判断某字段的值是否属于in列表中的某一字段
特点：Ⅰ.使用in语句比or提高了简洁度
Ⅱ.in列表的值类型必须与查询字段必须一致或兼容('123'自动转为123)
如：job_id IN('AD_VP' ,'IT_PROG' ,'FI_MGR') job_id为varchar 列表中也为字符型
Ⅲ.in列表中不支持通配符
#案例：查询员工的工种编号为AD_VP,IT_PROG,FI_MGR中的任意一个的员工名和工种编号
SELECT last_name,job_id FROM employees WHERE job_id IN('AD_VP' ,'IT_PROG' ,'FI_MGR');#两者语句相等
SELECT last_name,job_id FROM employees WHERE job_id='AD_VP' OR job_id='IT_PROG' OR job_id='FI_MGR';
#4.is null与is not null
①=或<> 不能用于判断null值
②is null与is not null可以用于判断null值
is null为空的字段
is not null不部为空的字段
#案例：查询没有奖金的员工名和奖金 commission_pct为空null
SELECT last_name,commission_pct FROM employees WHERE commission_pct = NULL;
#等号无法判断null值 只能用is null为空的字段
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;
#案例：查询有奖金的员工名和奖金 commission_pct为不空null
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;
#5.安全等于 <=> 是可以判断null值
is null vs <=>null
is null:仅仅可判断null值，可读性较高，建议使用
<=> null:既可以判断null值，又可以判断普通数值，可读性较低
#案例：查询没有奖金率的员工名及奖金率
SELECT last_name,commission_pct FROM employees WHERE commission_pct <=> NULL;
#案例：查询工资为12000的员工名及薪资
SELECT last_name,salary FROM employees WHERE salary <=> 12000;
四、进阶3：排序查询 order by
语法：select 查询列表 from 表 【where 筛选条件】 order by 查询字段 【asc/desc】
特点：1.asc为升序 desc为降序
2.不写asc或desc 默认为升序asc
3.按多个字段排序ORDER BY salary DESC,department_id ASC;
#案例1.查询员工信息，员工工资为从高到低 【添加筛选语句排序】
SELECT * FROM employees ORDER BY salary ASC; #默认为升序asc ORDER BY salary;
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)) 年薪
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; #将表达式改为别名 ORDER BY支持 where不支持
#案例5.按姓名的长度显示员工的姓名和工资【按函数排序】length(char)函数返回字符长度
SELECT LENGTH(last_name) 字节长度,last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;
#案例6：查询员工信息，要求先按工资从高到底排序，再按部门编号从低到高排序 【按多个字段排序】
SELECT * FROM employees ORDER BY salary DESC,department_id ASC; #整个排序先按照salary排 在按照department_id排
五、进阶4：常见函数
概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1.隐藏了功能实现细节 2.提高代码的复用性
调用：select 函数名(参数列表) 【from 表】
特点：
Ⅰ、叫什么(函数名)
Ⅱ、什么用(函数功能)
分类：1.单行函数 如 concat、length、ifnull等等
2.分组函数 多个表汇聚成一个表数据
功能：做统计使用，又称统计函数、聚合函数、组函数
一、单行函数
1、字符函数
#1.length(str) 获取参数值的字节个数
SELECT LENGTH('tomcat');
SELECT LENGTH('我是男生hehe');#utf-8中文字符 一个占3个字节，英文字符占1个字节
SHOW VARIABLES LIKE '%char%'; 查看数据字符集
#2.concat(str1,str2,...) 拼接字符串
SELECT CONCAT(last_name,'.',first_name) 姓名 FROM employees;
#3.upper(str)、lower(str)
SELECT UPPER('tomcat');#字符升级为大写
SELECT LOWER('TomCat');#字符升级为小写
#案例：将姓变大写，名变小写，然后在拼接
SELECT CONCAT(UPPER(last_name),'.',LOWER(first_name)) FROM employees;
#一个函数的返回值可以充当另一个函数的参数
#4.substr、substring 截取字符串 两个函数是一个意思
注意：在sql中索引从1开始
#截取从指定索引处后面所有字符 substr(str,index)
SELECT SUBSTR('李媛媛因为萍水相逢过啦',4) 输出;
#截取从指定索引处指定字符的长度 比如从1开始 字符长度为3 substr(str,index,strlength)
SELECT SUBSTR('李媛媛因为萍水相逢过啦',1,3) 输出;
#案例：姓名中的首字母的大写，其它字母小写，用_拼接起来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) 姓名 FROM employees;
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name,2)),'_',LOWER(SUBSTR(first_name,1))) 姓名 FROM employees;
SELECT last_name,first_name FROM employees;
#5.instr 返回子串第一次出现的位置索引，如果找不到返回0
SELECT INSTR('马大仙爱上了李萌萌一起浪月球','李萌萌') 首次出现的位置;
#6.trim 去除字符前后的空格或 指定的字符
SELECT LENGTH(TRIM(' ROSE ')) trim函数;
#去除字符前后指定的字符
SELECT TRIM('a' FROM 'aaaaaaaROaaaaSEaaaaaaa') 去指定字符;
#7.lpad 用指定字符实现左填充，填充长度为指定长度
SELECT LPAD('网球',5,'9') 用9左填充;
#8.rpad 用指定字符实现右填充，填充长度为指定长度
SELECT RPAD('羽毛球',6,'6') 用6右填充;
SELECT RPAD('羽毛球',2,'6') ; #str本身超过指定填充长度，将原字符长度进行截断
#9.replace替换
SELECT REPLACE('科比暴打韦德','韦德','大猩猩') 替换函数;
2、数学函数
#round 四舍五入
SELECT ROUND(1.65); #结果为2
SELECT ROUND(-1.55); #结果为-2
SELECT ROUND(1.767,2); #小数点后保留两位
#ceil 向上取整，返回大于等于该参数的最小整数
SELECT CEIL(-1.002); 参数为-1.002 大于该参数的为-1 如果为1返回为1
SELECT CEIL(1.00);
#floor 向下取整，返回小于等于该参数的最大整数
SELECT FLOOR(-9.99); 小于-9.99的最大整数，为-10
#truncate 截断 truncate(x,d) 小数点后保留d位
SELECT TRUNCATE(12.632,2); 小数点后2进行截断
#mod 取模 与java中运算一致 取模数与 看被除数的正负性有关
公式a-a/b*b MOD(-10,-3) -10-(-10)/-3*-3 =-1
SELECT MOD(10,-3);
SELECT MOD(-10,-3); 为-1
3、日期函数
#now 返回当前系统日期+时间
SELECT NOW();
#curdate 返回当前系统日期，不包括时间
SELECT CURDATE();
#可以获取指定的部分，年、月、日、小时、分钟mintue、秒second()
SELECT YEAR(NOW()) 年; #获取当前日期的年份
SELECT MONTH('1998-1-1') 年;
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTHNAME(NOW()) 月; #获取英文状态下的月
# str_To_date('字符时期','转换为指定格式')将字符通过指定的格式转换成日期型
SELECT STR_TO_DATE('1996-2-17','%Y-%c-%d'); 将字符型的日期转换为日期型
#查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate <=> '1992-4-3';
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%m-%d %Y');
#有可能用户不安年月日填写 而是按照月日年填写 就需要指定的格式去解析 通过%m-%d %Y格式进行解析
#date_format(日期，转换为指定格式字符型日期) 将日期转换成字符串
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') 日期;
#查询有奖金的员工名和入职日期 日期格式（xx月/xx日 xx年）
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d %Y年') 入职日期
FROM employees WHERE commission_pct IS NOT NULL;
#datediff：返回两个日期相差的天数
select datediff(now(),'2018-1-1')
4、流程控制函数
#1.if函数：if else('条件表达式','返回为真',‘返回为假’);
SELECT IF(10>5,'大','小') if函数;
#查询员工名和奖金 用if函数，如果有奖金返回有奖金，嘻嘻，如果没有返回有奖金，呵呵。
SELECT last_name,commission_pct, IF(commission_pct IS NULL,'没有奖金，呵呵','有奖金，嘻嘻') 备注
FROM employees;
#2.case函数的使用一：
java中：
switch(变量或表达式){
case 常量1:
语句1; break;
.....
default 语句n; break;
}
mysql中：
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1; case与select搭配使用分号就不能使用';'代表语句的结束
when 常量2 then 要显示的值1或语句2;
.....
else 要显示的语句n
end
#案例：查询员工的工资 要求 部门号=30，显示的工资1.1倍；部门号=40，显示的工资1.2倍；部门号=50，显示的工资1.3倍；其它部门，显示的工资保持不变
SELECT last_name,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 新工资 #case到end 相当于一个表达式
FROM employees;
#2.case函数的使用二： 与java 多重if类似
java中：
if(条件1){
语句1;
}else if(){
语句2;
}
... ...
else{
语句n;
}
mysql中：case后面不需要接值或表达式
case
when 条件1 then 要显示的值1 或语句[;] 如果是case语句就要加分号
when 条件2 then 要显示的值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;
5.其他函数
version() 当前数据库服务器的版本
database() 当前打开的数据库
user() 当前用户
password('字符')：返回该字符的密码形式 进行加密
md5('字符')：返回该字符的md5加密形式
二、分组函数
功能：用于统计使用，又称为聚合函数或统计函数或组函数
分类：
sum求和、avg平均值 、max最大值、min最小值 count计数
特点：
1.sum、avg一般用于处理数值型
max、min、count可以用于处理任何类型
2.以上分组函数都忽略null值
3.可以和distinct搭配实现去重的运算
4.count函数的详细介绍
一般使用count(*)统计行数
#1.简单的使用
#求salary字段总和
SELECT SUM(salary) FROM employees;
#求salary字段平均数
SELECT AVG(salary) FROM employees;
#salary字段中的最大值
SELECT MAX(salary) FROM employees;
#salary字段中的最小值
SELECT MIN(salary) FROM employees;
#salary字段有多少个数 计数
SELECT COUNT(salary) FROM employees;
SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均值,MAX(salary) 最大值,MIN(salary) 最小值,COUNT(salary) 个数
FROM employees;
#2、参数支持那些类型
sum，avg适用于处理数值型 放字符型或日期型 没有任何意义
SELECT SUM(last_name),AVG(last_name) FROM employees;#统计字符型 没有显示0 没有任何意义
SELECT SUM(hiredate),AVG(hiredate) FROM employees;#日期求和没有意义
#max，min是支持字符型或日期型
SELECT MAX(last_name),MIN(last_name) FROM employees;
SELECT COUNT(last_name) FROM employees;
#count计算不为null的值 计算非空数
SELECT COUNT(commission_pct) FROM employees;
SELECT last_name FROM employees ORDER BY SUBSTR(last_name,1,1) ASC;
#3、是否忽略null值
null加任何字段为null，sum函数显示结果没有为null值，说明sum是忽略null
avg也是忽略null的
SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107
FROM employees;
# max,min 是忽略null的 不忽略null的话为最小值应该为null
SELECT MAX(commission_pct),MIN(commission_pct) FROM employees;
#count 计算非空数值
SELECT COUNT(commission_pct) FROM employees;
SELECT commission_pct FROM employees WHERE commission_pct IS NOT NULL;
SELECT DISTINCT department_id FROM employees;
#4.与distinct搭配
SELECT SUM(DISTINCT salary) 取重之后再求和,SUM(salary) FROM employees;
#不加distinct 就会有35种奖金率的 加了去重只有7种奖金率
SELECT COUNT(DISTINCT commission_pct) 奖金率有几种, COUNT(commission_pct) 有奖金率的人 FROM employees;
SELECT AVG(DISTINCT salary) 取重之后再求平均值,AVG(salary) FROM employees;
SELECT salary,commission_pct FROM employees;
#5.count函数的介绍
#1.count里加上一个字段 count(字段)：统计该字段非空值的个数
SELECT COUNT(salary) FROM employees;
#2.count(*) 加上*号 统计有多少字段 只要有字段一个不为null 就统计上 count(*)：统计结果集的行数
SELECT COUNT(*) FROM employees;
#3.count(1) 字段有多少行 就有多少个1 统计有多少个1的个数 如果1改为2的话效果一样的 就是统计有多少个2
SELECT COUNT(1) FROM employees; count(1) 统计结果集的行数
效率：
MYISAM的存储引擎下，COUNT(*)的效率高
INNODB的存储引擎下，COUNT(*)与COUNT(1)的效率差不多，但比count(字段)效率要高
六、#进阶5：分组查询
#明确一点where字句 一定放在from语句后面
语法：
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(排序可以放在整个分组查询的最后)
#引入：查询公司员工的平均工资
SELECT AVG(salary) FROM employees;
#1.简单的分组查询
#案例1.查询每个工种的最高工资 （每个工种 就需要用分组查询）
#job_id有多种比如IT、销售、市场、行政等等，每个共工种有最高或最低的薪资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;
#案例2.查询每个位置上的部门的个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
#2.添加筛选条件
#案例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;
#3.添加复杂的筛选条件
#案例1：查询哪个部门的员工个数>2 根据原始表是筛选不出来的，必须更具分组查询的结果集来筛选
#①.查询每个部门的员工个数 比如10号部门有多少个人
SELECT COUNT(*), department_id
FROM employees
/*where COUNT(*)>2 这样写是错误的*/
GROUP BY department_id;
#②.根据①的查询结果进行筛选，再查询哪个部门的员工个数>2
SELECT COUNT(*), department_id
FROM employees
/*where COUNT(*)>2 这样写是错误的*/
GROUP BY department_id
HAVING COUNT(*)>2; #根据分组后的结果再进行筛选，在group by后追加子句 使用的关键字having
#案例2.查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
#①.查询每个工种有奖金的员工的最高工资 按工种分 查询每个工种的最高工资，再添加条件筛选
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id;
SELECT job_id FROM employees GROUP BY job_id;
在上述的分组查询的结果集基础上在进行筛选
#②.根据①的分组查询的结果集，在添加筛选最高工资>12000的工种和最高工资 group by子句后接having做条件筛选
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; #分组查询 每个员工的领导id大于102且每个员工的最低工资
#2.根据1的分组查询的结果集，在进行筛选最低工资>5000的领导编号是哪个以及其最低工资
SELECT MIN(salary),manager_id
FROM employees WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;
#4.按表达式或函数分组
#案例：按员工姓名的长度分组，查询每一组的员工个数，筛选每一组员工个数>5的有那些
SELECT last_name,LENGTH(last_name) FROM employees WHERE LENGTH(last_name) = 3;
#1.先查询按员工姓名长度分组，每组有多少个人
SELECT COUNT(*),LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
#2.在1分组查询结果集进行筛选员工个数>5有那几个组
SELECT COUNT(*),LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;
#group by子句和having子句是支持别名的 明确where是不支持取别名的 在oracle中group by子句和having子句是不支别名的
SELECT COUNT(*) 个数,LENGTH(last_name) 名字长度
FROM employees
GROUP BY 名字长度
HAVING 个数>5;
#5.按多个字段分组
#案例.查询每个部门每个工种的员工的平均工资 部门的id与工种id一致才分为一组
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;
SELECT department_id,job_id,salary FROM employees;
#6.添加排序条件
#案例.查询每个部门每个工种的员工的平均工资，并且按平均工资的高到底显示 追加子句AVG(salary) desc
SELECT AVG(salary),department_id,job_id
FROM employees
WHERE department_id IS NOT NULL #部门id不为null的
GROUP BY department_id,job_id
HAVING AVG(salary)>=10000 #平均工资大于等于1w的
ORDER BY AVG(salary) DESC;
七、#进阶六：连接查询
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1 有m行数据，表2 有n行数据，结果=m*n 没有添加有效的连接条件，导致多个表所有行实现完全连接
发生的原因：没有有效的连接条件
如何避免：添加有效的连接条件
分类：
按年代分类：
sql92标准：仅仅支持内连接
sql99标准：【推荐使用】：支持内连接+外连接(左外和右外)+交叉连接
按功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接
交叉连接
SELECT id,NAME,boyfriend_id FROM beauty;
SELECT * FROM boys;
#在WHERE 加入有效的连接条件。
SELECT `name`,boyName FROM beauty,boys
#where boyfriend_id = id;# 这样写会出现错误 两个字段不清楚存在与哪个表中
WHERE beauty.boyfriend_id=boys.id;#使用表名前缀在多个表中区分相同的列
#一、sql92标准
#1、等值连接
①.多表等值连接的结果为多表的交集部分
②.n表连接，至少需要n-1个连接条件
③.多表的顺序没有要求
④.一般需要为表起别名
⑤.可以搭配前面介绍的所有子句使用，比如排序，分组，筛选
#案例：查询女神名和对应的男神名
SELECT `name`,boyName
FROM beatuy,boys
WHERE beauty.`boyfriend_id`=boys.`id`; #卡迪乘积 那beauty表中的第一行与boys表一一匹配，在用过通过筛选 进行个等值判断
#案例1.1：查询员工名和对应部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`;
#1.2、为表起别名
Ⅰ 提高了语言的简洁度
Ⅱ 区分重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
#查询员工名、工种号、工种名
SELECT last_name,e.job_id,job_title
FROM employees AS e,jobs j
WHERE e.`job_id` = j.`job_id`;
#1.3、两个表的顺序是否可以调换 可以
#查询员工名、工种号、工种名
SELECT last_name,e.job_id,job_title
FROM jobs j,employees AS e
WHERE e.`job_id` = j.`job_id`;
#1.4、可以加筛选？
#案例1：查询有奖金的员工名及对应的部门名
SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`
AND commission_pct IS NOT NULL; #再添加筛选条件 用and关键字
#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';
#1.5、可以分组吗？
#案例1：查询每个城市部门的个数
SELECT COUNT(department_id) 个数,city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
GROUP BY city;
#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT MIN(salary),department_name,d.manager_id
FROM departments d,employees e
WHERE d.`department_id` = e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,d.manager_id
#1.6、可以加排序
#案例：查询每个工种的工种名称和员工的个数，并且按员工的个数降序排序
SELECT COUNT(*),job_title
FROM employees e,jobs j
WHERE e.job_id = j.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;
#查询每个工种的员工个数，并按员工个数降序排序
SELECT COUNT(*),job_id
FROM employees
GROUP BY job_id
ORDER BY COUNT(*) DESC;
#1.7、可以实现三表连接？
#案例：查询员工名、部门名和所在的城市
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 ASC;
#2、非等值连接 有可能是不等于 小于 大于
那employee表中的salary字段 与grade表中的字段做筛选条件
#案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
#也可以添加分组 筛选 排序
AND grade_level = 'C'
ORDER BY e.`salary` DESC;
#3、自连接
筛选条件 员工表 manager_id 与 管理表的employe_id 进行匹配 e.employee_id = m.manager_id;
自连接意思 把原本的一张表当作两章表甚至多张表去使用
特定的表中 如 领导id = 员工id在同一个表中 本员工id是101,上级领导id是100. 那本员工的上级领导id去匹配员工id
select 字段1,字段2
from 表 别名1，表 别名2
where 等值连接的条件筛选
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
#案例1.查询员工名 和员工上级领导的名称
SELECT e.last_name,e.`employee_id`,m.last_name,m.employee_id 把一个表看作两个表
FROM employees e,employees m
WHERE e.employee_id = m.manager_id;
#二、sql99语法 重点掌握
语法：
select 查询列表
from 表1 别名 【连接类型】是内连接或者外连接
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
内连接 (重点)：内连接用inner关键字
外连接
左外(重点)left 【outer】 outer是可以省略的
右外(重点)rigth 【outer】
全外：full【outer】
交叉连接：cross
#一）内连接
语法：
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`;
#案例2.查询名字中包含e的员工名和工种名(添加筛选)
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id` = j.job_id
WHERE last_name LIKE '%e%';
#案例3.查询部门个数>3的城市名和部门个数(分组+筛选)
SELECT COUNT(*),city
FROM locations l
INNER JOIN departments d
ON l.`location_id`=d.`location_id`
GROUP BY city
HAVING COUNT(*)>3;
#案例4.查询哪个部门的部门员工个数>3的部门名和员工个数，并按个数降序排序
SELECT COUNT(*),department_name
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;
#案例5.查询员工名、部门名、工种名，并按部门名降序(三表连接)
三表连接
select 查询列表
from 表1
inner join 表2 on 等值连接条件
inner join 表3 on 等值连接条件
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`
#二、非等值连接
#案例1.查询员工的工资级别
SELECT last_name,salary,grade_level
FROM employees e
INNER JOIN job_grades g
ON e.salary BETWEEN lowest_sal AND highest_sal;
#on e.`salary`>lowest_sal and e.`salary`<highest_sal 与上面条件是一个意思
#案例2.每个工资级别的个数>2，并且按工资级别降序
SELECT COUNT(*),grade_level
FROM employees
INNER JOIN job_grades g
ON salary BETWEEN lowest_sal AND highest_sal
GROUP BY grade_level
HAVING COUNT(*)>2
ORDER BY grade_level DESC;
#三、自连接
#案例1、查询员工的名字、上级的名字
SELECT e.last_name,e.employee_id,m.last_name,m.employee_id
FROM employees e
INNER JOIN employees m
ON e.`manager_id`=m.`employee_id`;
#案例2、查询员工的名字中包含k字符的、上级的名字
SELECT e.last_name,e.employee_id,m.last_name,m.employee_id
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没有的
beauty表中对应boys表中没有匹配的字段 显示结果为null
主要先看 谁是主表谁是从表
#引入：查询女神表中对应着男神表，没有的男朋友女神名
#比如柳岩boyfriend_id=8 对应boys表中的男神id没有
#左外连接
SELECT girl.`id`,girl.name,boy.*
FROM beauty girl
LEFT OUTER JOIN boys boy #左连接
ON girl.`boyfriend_id`=boy.`id`
WHERE boy.id IS NULL;#条件筛选 id为主键不可为null 有可能boyName或userCP字段为null
再去除boy.*字段 对应男神表中 没有对应男神的女神名字
#案例、显示男神表中没有对应女神 的男神名
#右外连接
SELECT girl.*,boy.*
FROM beauty girl
RIGHT JOIN boys boy
ON girl.`boyfriend_id`=boy.`id` #如果将boy为主表 显示信息为boys表的字段
WHERE girl.`id` <=> NULL;
显示的boy表中的信息
#将beauty表中王语嫣的
UPDATE beauty SET boyfriend_id=5 WHERE id=10;
#案例1.哪个部门没有员工
#一。先找主表 左外连接
SELECT d.*,employee_id
FROM departments d
LEFT JOIN employees e
ON d.`department_id`=e.department_id
WHERE employee_id IS NULL; #employee_id 为null说明没有匹配的
#全外连接) 语法 在MySQL语法不支持
#在MySQL语法中不支持 显示所有表中的字段信息 不分主表与从表之分
#全外连接的显示结果类似于左外连接+左外连接从表中与主表中不匹配的信息
SELECT girl.id,girl.name,boy.*
FROM beauty girl
FULL JOIN boys boy
ON girl.boyfriend=boy.id;
全外连接 的显示结果为beauty与boy 条件匹配的结果 + beauty与boy 条件不匹配的结果 + boy表中不与beauty表中匹配的
#交叉连接) 用9*9乘法表中来实现一个笛卡尔乘积
SELECT girl.name,boy.*
FROM beauty girl
CROSS JOIN boys boy;
总结：sql92 VS sql99
#总结：sql92 与 sql99pk
#功能：sql99支持的较多
#可读性：sql实现连接条件和筛选条件分离，可读性较高
八、#进阶七：子查询
含义：
出现在其它语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
示例：
select first_name from employees
where department_id in(
select department_id from departments
where location_id=1700
);
分类：
按子查询出现的位置：
select后面：
仅仅支持标量子查询
from后面：
支持表子查询
where或having后面(重点)
标量子查询（单行）✔
列子查询 （多行）✔
行子查询(用的较少)
exists后面(相关子查询)
按结果集的行列数不同：
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
#一、where或having后面
1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多行多列子查询）
特点：
1.子查询放在小括号内
2.子查询一般放在条件的右侧
3.标量子查询，一般搭配着多行操作符使用
如：> < >= <= = <>
列子查询，一般搭配着多行操作符使用
如： in any/some all
#1.标量子查询 （单行子查询）where后面
#案例1：谁的工资比 Abel 高?
#①查询Abel的工资
SELECT salary FROM employees
WHERE last_name ='Abel';
#②查询员工的信息，满足salary>①结果 在①的结果集上进行添加筛选
SELECT * FROM employees
WHERE salary>(/*查询员工大于Abel的工资的人数*/
SELECT salary FROM employees
WHERE last_name ='Abel'#11000
);
#一个查询里 可以放两个甚至更多个子查询
#案例2：返回job_id与141号员工相同， salary比143号员工多的员工姓名， job_id 和工资
#1.先查询工种与141员工相同的员工 先查询141员工是什么类型为ST_CLERK
SELECT job_id
FROM employees
WHERE employee_id='141';
#2.在查询salary比143员工多的员工 先查询143员工的工资是多少 工资为2600
SELECT salary
FROM employees
WHERE employee_id='143';
#3.查询员工的姓名，job_id的工资 要求job_id=ST_CLERK salary>2600
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'
);
#子查询放在having后面
#3.查询最低工资大于50号部门最低工资的部门id和其最低工资
#查询每个部门最低工资 这些部门的最低工资> 50部门的最低工资 有那些部门
#1.先查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id=50;
#2.查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id;
#3.在2分组查询的基础上筛选，满足salary>1的结果集 在分组的基础上添加筛选 having后面
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id=50
);
#非法使用标量子查询
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>( #这里的子查询应该是标量子查询 如果多个结果的集就会出现错误
SELECT salary #这里是多个数据 就会报错 因为用的是> < >= <= = <> 是单行操作符只能用标量子查询
FROM employees
WHERE department_id=50
);
#2.列子查询（多行多列查询）一列多行
a in(10,20,30) a字段是等于in列表中任意一个值 a not in(10,20,30) not in不等于列表中任意一个值
any/some任意 a>any(10,20,30) a字段大于 any列表中任意一个。a字段只需要大于 any列表中最小的一个值 就可以换为a>min(列表(10,20,30))
all 都 a>all(10,20,30) a字段大于 all列表中的所有值 a字段只需要大于 all列表中最大的一个值 就可写为a>max(列表(10,20,30))
#Ⅰ.使用in操作符
#案例1.返回location_id是1400或1700的部门中的所有员工姓名
#1.查询location_id是1400或1700的部门
SELECT department_id
FROM departments
WHERE location_id IN('1400','1700');
#2.在查询部门中的所有员工姓名 部门号是列表①中的某一个
#department_id是等于1结果集一列表中的任意一个
SELECT department_id,last_name
FROM employees
WHERE department_id IN(
SELECT department_id
FROM departments
WHERE location_id IN('1400','1700')
);
#Ⅱ.多行子查询中使用 ANY 操作符
#案例2.返回其它部门中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、 job_id 以及salary
#1.先查询工种为IT_PROG类型的工资
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG';
#2.在查询员工姓名、 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';
#或者也可写成 使用any也可以用其他的代替
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY(
SELECT MAX(salary)
FROM employees
WHERE job_id='IT_PROG'
)AND job_id<>'IT_PROG';
#Ⅲ.多行子查询中使用 ALL 操作符
#案例3.返回其它部门中比job_id为‘IT_PROG’部门 所有 工资都低的员工的员工号、姓名、 job_id 以及salary
#1.先查询工种为IT_PROG类型的工资
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG';
#2.在查询查询员工姓名、 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 <ALL(
SELECT MIN(salary)
FROM employees
WHERE job_id='IT_PROG'
);
#in(List列表) 也可以改为 =any，not in(List列表) 也可以改为<>all
#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;
#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`
) 部门个数
FROM departments d;
#案例2.查询员工号=102的部门名
SELECT (
SELECT department_name
FROM departments d
WHERE d.`department_id`=e.department_id
) 部门名 FROM employees e
WHERE employee_id=102;
#三、from后面 将子查询结果充当一张表，要求必须起别名
#案例1：查询每个部门的平均工资的工资等级
#1.查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
#2连接1的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
#查询结果集充当一张表 from表后必须起别名 avg_dep方便识别 以及起别名AVG(salary) ag
SELECT avg_dep.*,g.`grade_level`
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) avg_dep
INNER JOIN job_grades g
ON avg_dep.ag BETWEEN g.`lowest_sal`AND g.`highest_sal`;
#案例2.查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资
#1先查询每个部门的平均工资 那一结果集 与employees表去比对
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
#2再查询本部门员工高于本部门的平均工资的员工
#比如20部门平均工资为9500 20部门的员工
SELECT employee_id,last_name,salary,department_id
FROM (
SELECT AVG(salary) ag,department_id dep_id
FROM employees
GROUP BY department_id
) avg_dep
INNER JOIN employees e
ON avg_dep.dep_id=e.`department_id` AND avg_dep.ag < e.`salary`
#四、exists后面（相关子查询）
能用exists的一定能用 in
语法：exists(完整的查询语句)
结果：1或0
如：SELECT EXISTS(SELECT employee_id FROM employees WHERE employee_id=10000);
#案例1：查询员有员工名的部门名
#方式1.in
SELECT department_name
FROM departments d
WHERE d.department_id IN (
SELECT e.department_id
FROM employees e
);
#方式2.exists
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE e.`department_id`=d.`department_id`
);
#方式3.连接查询
SELECT department_name
FROM departments d
INNER JOIN employees e
ON d.`department_id`=e.`department_id`
GROUP BY department_name;
#案例2：查询没有女朋友的男神信息
#方式1.in
SELECT boy.*
FROM boys boy
WHERE boy.`id` NOT IN (
SELECT girl.`boyfriend_id`
FROM beauty girl
);
#方式2.exists
SELECT boy.*
FROM boys boy
WHERE NOT EXISTS (
SELECT *
FROM beauty g
WHERE boy.id=g.`boyfriend_id`
);
#进阶8：分页查询 （重点） 关键字 limit
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
select 查询列表 ⑦ 对分组后的显示你要查的字段
from 表 ① 锁定数据源
【join type】 join 表2 ② 执行完两个表以后 1和2表就连接起来了 就形成了一个笛卡尔乘积
【on 连接条件】③ 笛卡尔乘积大表中 进行连接筛选 形成一个新的虚拟表
【where 筛选条件】④ 在新的虚拟表进行筛选
【group by 分组字段】⑤ 进行分组
【having 分组后的筛选】⑥ 对分组后的结果集再进行筛选
【order by 排序的字段】⑧ 在⑦基础上 进行排序
limit 【offset,】size;⑨ 最后在进行分页显示
offset要显示条目的起始索引（起始索引从0开始）
size要显示的条目个数
特点：
Ⅰ.limit语句放在查询语句的最后
Ⅱ.公式
要显示的页数page，每页的条目数size
select 查询列表
from 表
limit (page-1)*size, size;
size=10 每行显示10页
page offset
1 0
2 10
3 20
#案例1：查询前五条员工信息
SELECT * FROM employees LIMIT 0,5; #0也可以省略
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 10;
#进阶9：联合查询
union 联合 合并：将多条查询语句的结果合并成一个结果
语法：
查询语句1
union
查询语句2
union
.... 可以是多个
应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时
就好比用户在整个网站进行搜索 一个网站不可能有一个表，用户在网站进行搜索时是对所有表进行搜索最后得到到结果进行联合
特点：(重点)
1.要求多条查询语句的查询列数是一致的！ 比如：第一条查询列表是3个第二条查询列表是2个字段 就报错误
2.要求多条查询语句的查询的每一列的类型和顺序最高一致
3.union关键字默认去重，如果使用union all 可以包含重复项
#引入案例：查询部门编号>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%';
#案例.查询中国用户中难寻个的信息以及外国用户中男性的信息
SELECT id,cname,csex FROM t_ca WHERE csex='男'
UNION ALL #不去重复的
SELECT t_id,tName,tGender FROM t_ua tGender='male';