MySQL6天学习笔记 —— day02
程序员文章站
2022-05-30 13:17:56
...
文章目录
5. DQL语言的学习
5.1 基础查询
-- 进阶1：基础查询
/*
语法:
select 查询列表 from 表名;
类似于: System.out.println(要打印的东西)
特点:
1、查询列表可以是: 表中的字段、常量值、表达式、函数
2、查询的结果是一个虚拟的表格(临时存在的)
*/
-- 指定使用的数据库
USE myemployees;
-- 1. 查询表中的单个字段
SELECT last_name FROM employees;
-- 2.查询表中的多个字段
SELECT last_name,salary,email FROM employees;
-- 3.查询表中的所有字段
-- 方式一:
SELECT
`employee_id`,
`first_name`,
`last_name`,
`phone_number`,
`job_id`,
`salary`,
`commission_pct`,
`manager_id`,
`department_id`,
`hiredate`
FROM
employees;
-- 注意：``表示的是里面的内容是一个字段，而不是关键字，建议每次查询字段都要加上这个
-- 方式二:
SELECT * FROM employees;
-- 4.查询常量值
-- 注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要
SELECT 100;
-- 5.查询表达式
SELECT 100%98;
-- 6.查询函数
SELECT VERSION();
-- 7.为字段起别名
/*
1、便于理解
2、如果要查询的字段有重名的情况，使用别名可以区分开来
*/
-- 方式一：使用as
SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
-- 方式二：使用空格
SELECT last_name 姓,first_name 名 FROM employees;
-- 案例：查询salary，显示结果为 out put
-- 注意:如果起的别名是关键字，建议使用双引号括起来
SELECT salary AS "out put" FROM employees;
-- 8.去重
-- 案例：查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees;
-- 根据a和b两个字段去重的
SELECT DISTINCT a,b FROM 表名;
-- 9.+号的作用
/*
java中的+号：
1、运算符，两个操作数都为数值型
2、连接符，只要有一个操作数为字符串
mysql中的+号：
仅仅只有一个功能：运算符
select 100+90; 两个操作数都为数值型，则做加法运算
select '123'+90;只要其中一方为字符型，试图将字符型数值转换成数值型
如果转换成功，则继续做加法运算
select 'john'+90; 如果转换失败，则将字符型数值转换成0
select null+10; 只要其中一方为null，则结果肯定为null
*/
-- 10.concat函数
/*
1.作用：拼接字符
2.语法格式：select concat(字符1,字符2,...)
*/
-- 案例：查询员工名和姓连接成一个字段，并显示为 姓名
SELECT CONCAT('a','b','c') AS 结果;
SELECT
CONCAT(last_name,first_name) AS 姓名
FROM
employees;
-- 11.isnull函数
/*
1.作用：判断某字段或表达式是否为null，如果是null，返回1，否则返回0
2.语法格式：select isnull(字段名) from 表名;
*/
SELECT
ISNULL( commission_pct ),
commission_pct
FROM
employees;
5.1.1 基础查询练习
-- 1.显示表departments的结构，并查询其中的全部数据
DESC departments;
SELECT * FROM `departments`;
-- 2.显示出表employees中的全部job_id（不能重复）
SELECT DISTINCT job_id FROM employees;
-- 3.显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT
-- IFNULL函数
/*
1.作用：判断某字段或表达式是否为null，如果为null，就返回指定的值，否则返回原本的值
2.语法格式: select IFNULL(字段名,指定的值) from 表名;
*/
SELECT
IFNULL(commission_pct,0) AS 奖金率,
commission_pct
FROM
employees;
#-------------------------------------------
SELECT
CONCAT(`first_name`,',',`last_name`,',',`job_id`,',',IFNULL(commission_pct,0)) AS out_put
FROM
employees;
5.2 条件查询
-- 进阶2：条件查询
/*
语法:
select
查询列表
from
表名
where
筛选条件;
分类:
一、按条件表达式筛选
简单条件运算符：> < = != <> >= <=
注意：<>表示的是不等于，使用不等于的话建议用这个
二、按逻辑表达式筛选
逻辑运算符：
&& || !
and or not
作用：用于连接条件表达式
说明：
&&和and：两个条件都为true，结果为true，反之为false
||或or： 只要有一个条件为true，结果为true，反之为false
!或not： 如果连接的条件本身为false，结果为true，反之为false
三、模糊查询
like
between and
in
is null
*/
-- 1.按条件表达式筛选
-- 案例1：查询员工工资>12000的员工信息
SELECT
*
FROM
employees
WHERE
salary>12000;
-- 案例2：查询部门编号不等于90号的员工名和部门编号
SELECT
last_name,
department_id
FROM
employees
WHERE
department_id<>90;
-- 案例3：查询员工号为 176 的员工的姓名和部门号和年薪
SELECT
last_name,
department_id,
salary * 12 * ( 1+IFNULL ( commission_pct, 0 ) ) AS 年薪
FROM
employees;
-- 2.按逻辑表达式筛选
-- 案例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
NOT(department_id>=90 AND department_id<=110) OR salary>15000;
-- 方式二：
SELECT
*
FROM
employees
WHERE
department_id < 90 OR department_id > 110
OR salary > 15000
-- 3.模糊查询
-- 3.1 like
/*
特点：
①一般和通配符搭配使用,可以判断字符型或数值型
通配符：
% 任意多个字符,包含0个字符
_ 任意单个字符
*/
-- 案例1：查询员工名中包含字符a的员工信息
SELECT
*
FROM
employees
WHERE
last_name LIKE '%a%';
-- 案例2：查询员工名中第三个字符为n，第五个字符为l的员工名和工资
SELECT
last_name,
salary
FROM
employees
WHERE
last_name LIKE '__n_l%';
-- 案例3：查询员工名中第二个字符为_的员工名
-- 方式一：使用转义字符 "\_"
SELECT
last_name,
salary
FROM
employees
WHERE
last_name LIKE '_\_%';
-- 方式二：使用ESCAPE '任意字符'，将这个字符当做转义字符使用（推荐使用）
SELECT
last_name,
salary
FROM
employees
WHERE
last_name LIKE '_$_%' ESCAPE '$';
-- 案例4：查询部门编号是1开头的，并且是3位数的员工信息
SELECT
*
FROM
employees
WHERE
department_id LIKE '1__';
-- 3.2 between and
/*
①使用between and 可以提高语句的简洁度
②包含临界值
③两个临界值不要调换顺序
*/
-- 案例1：查询员工编号在100到120之间的员工信息
-- 方式一：
SELECT
*
FROM
employees
WHERE
employee_id >= 100
AND employee_id <= 120;
-- 方式二：推荐使用
SELECT
*
FROM
employees
WHERE
employee_id BETWEEN 100 AND 120;
-- 3.3 in
/*
含义：判断某字段的值是否属于in列表中的某一项
特点：
①使用in提高语句简洁度
②in列表的值类型必须一致或兼容（同一字段）
③in列表中不支持通配符
*/
-- 案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个的员工名和工种编号
-- 方式一：
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES';
-- 方式二：推荐使用
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');
-- 3.4 is null
/*
!=或<>不能用于判断null值
is null或is not null 可以判断null值
*/
-- 案例1：查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS NULL;
-- 案例2：查询有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS NOT NULL;
-- 错误写法，执行报错
SELECT
last_name,
commission_pct
FROM
employees
WHERE
salary IS 12000;
-- 3.5 安全等于 <=>
/*
isnull和<=>的区别?
IS NULL:仅仅可以判断NULL值，可读性较高，建议使用
<=> :既可以判断NULL值，又可以判断普通的数值，可读性较低
*/
-- 案例1：查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct <=> NULL;
-- 案例2：查询工资为12000的员工信息
SELECT
last_name,
salary
FROM
employees
WHERE
salary <=> 12000;
5.2.1 条件查询练习
-- 1.查询工资大于 12000 的员工姓名和工资
SELECT
last_name,
salary
FROM
employees
WHERE
salary > 12000;
-- 2.查询员工号为 176 的员工的姓名和部门号和年薪
SELECT
last_name,
department_id,
salary * 12 * ( 1+IFNULL ( commission_pct, 0 ) ) AS 年薪
FROM
employees;
-- 3.选择工资不在 5000 到 12000 的员工的姓名和工资
SELECT
last_name,
salary
FROM
employees
WHERE
NOT ( salary < 5000 AND salary > 12000 );
-- 4.选择在 20 或 50 号部门工作的员工姓名和部门号
SELECT
last_name,
department_id
FROM
employees
WHERE
department_id = 20
OR department_id = 50;
-- 5.选择公司中没有管理者的员工姓名及 job_id
SELECT
last_name,
job_id
FROM
employees
WHERE
manager_id IS NULL;
-- 6.选择公司中有奖金的员工姓名，工资和奖金级别
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE
commission_pct IS NOT NULL;
-- 7.选择员工姓名的第三个字母是 a 的员工姓名
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '__a%';
-- 8.选择姓名中有字母 a 和 e 的员工姓名
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '%a%'
AND last_name LIKE '%e%';
-- 9.显示出表 employees 表中 first_name 以 'e'结尾的员工信息
SELECT
*
FROM
employees
WHERE
first_name LIKE '%e';
-- 10.显示出表 employees 部门编号在 80-100 之间 的姓名、职位
SELECT
last_name,
job_id
FROM
employees
WHERE
department_id BETWEEN 80
AND 100;
-- 11.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位
SELECT
last_name,
job_id
FROM
employees
WHERE
manager_id IN ( 100, 101, 110 );
5.2.2 经典面试题
试问：select * from employees; 和
select * from employees where `commission_pct` like '%%' and `last_name` like '%%';
结果是否一样？并说明原因
回答：如果判断的字段中有null值，结果就是不一样的；而如果判断的字段中没有null值，结果就是一样的。
因为like '%%' 表示的是不为空，而如果`commission_pct`或者`last_name`有一个为空，结果就不一样了。
5.3 排序查询
-- 进阶3：排序查询
/*
1.语法： [ ]里面的内容表示可以省略的
select 查询列表
from 表名
[where 筛选条件]
order by 排序列表 [asc | desc];
2.特点：
1、asc代表的是升序，desc代表的是降序(如果不写，默认就是降序)
2、order by子句中可以支持单个字段、多个字段、表达式、函数、别名
3、order by子句一般是放在查询语句的最后面的，limit子句除外
*/
-- 案例1：查询员工信息，要求工资从高到低排序
SELECT
*
FROM
employees
ORDER BY
salary DESC;
-- 案例2：查询员工信息，要求工资从低到高排序
SELECT
*
FROM
employees
ORDER BY
salary;
-- 案例3：查询部门编号>=90的员工信息，按入职时间先后进行排序(升序)[添加筛选条件]
SELECT
*
FROM
employees
WHERE
department_id >= 90
ORDER BY
hiredate ASC;
-- 案例4：按年薪的高低显示员工的信息和年薪[按表达式排序]
SELECT
*,
salary * 12 * ( 1+IFNULL ( commission_pct, 0 ) ) 年薪
FROM
employees
ORDER BY
salary * 12 * ( 1+IFNULL ( commission_pct, 0 ) ) DESC;
-- 案例5：按年薪的高低显示员工的信息和年薪[按别名排序]
SELECT
*,
salary * 12 * ( 1+IFNULL ( commission_pct, 0 ) ) 年薪
FROM
employees
ORDER BY
年薪 DESC;
-- 案例6：按姓名的长度显示员工的姓名和工资[按函数排序]
SELECT
LENGTH( last_name ) 字节长度,
last_name,
salary
FROM
employees
ORDER BY
LENGTH( last_name ) DESC;
-- 案例7：查询员工信息，要求先按工资升序排序，再按员工编号降序排序[按多个字段排序]
SELECT
*
FROM
employees
ORDER BY
salary ASC,
employee_id DESC;
5.3.1 排序查询练习
-- 1. 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
SELECT
last_name,
department_id,
salary * 12 * ( 1+IFNULL ( commission_pct, 0 ) ) 年薪
FROM
employees
ORDER BY
年薪 DESC,
last_name ASC;
-- 2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序
SELECT
last_name,
salary
FROM
employees
WHERE
salary NOT BETWEEN 8000
AND 17000
ORDER BY
salary DESC;
-- 3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT
*,
LENGTH( email ) 邮箱字节数
FROM
employees
WHERE
email LIKE '%e%'
ORDER BY
邮箱字节数 DESC,
department_id ASC;
5.4 常见函数
常见函数：
字符函数：
length 获取字节个数
concat 拼接
substr 截取子串
instr 返回子串第一次出现的索引
trim 去前后指定的空格和字符
upper 转换成大写
lower 转换成小写
lpad 左填充
rpad 右填充
replace 替换
数学函数：
round 四舍五入
ceil 向上取整
floor 向下取整
truncate 截断
mod 取余
rand 获取随机数，返回0-1之间的小数 [0,1)
日期函数：
now 当前日期+时间
curdate 返回当前日期
curtime 返回当前时间
year 年
month 月
monthname 以英文形式返回月
day 日
hour 小时
minute 分钟
second 秒
datediff 返回两个日期相差的天数
str_to_date 将字符转换成日期
date_format 将日期转换成字符
其他函数：
version 当前数据库服务器的版本号
database 当前打开的数据库
user 当前用户
password('字符') 返回该字符的密码形式(自动加密) 注意：8版本不支持此种加密方式
md5('字符') 使用md5加密形式对该字符进行加密
流程控制函数：
if(条件表达式,表达式1,表达式2) 如果条件表达式成立,返回表达式1，否则返回表达式2
case(两种情况)
case情况一：
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
end
case情况二：
case
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end
分组函数：
max 最大值
min 最小值
sum 总和
avg 平均值
count 计算个数
-- 进阶4：常见函数
/*
概念：类似于Java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节 2、提高代码的重用性
调用：select 函数名(实参列表) [from 表名]
特点：
①叫什么（函数名）
②干什么（函数的功能）
分类：
1、单行函数
如：concat、length、ifnull等
2、分组函数
功能：做统计使用的，又称为统计函数、聚合函数、组函数
*/
-- 1.字符函数
-- 1.1 length 获取参数值的字节个数
SELECT
LENGTH( 'john' );
SELECT
LENGTH( '张三丰haha' );
-- 获取当前mysql的字符集
SHOW VARIABLES LIKE '%char%';
-- 1.2 concat 拼接字符串
SELECT
CONCAT( last_name, '_', first_name ) 姓名
FROM
employees;
-- 1.3 upper:将指定字符变成大写的，lower:将指定字符变成小写的
SELECT
UPPER( 'john' );
SELECT
LOWER( 'joHn' );
-- 案例：将姓变大写，名变小写，然后拼接
SELECT
CONCAT( UPPER( last_name ), LOWER( first_name ) ) 姓名
FROM
employees;
-- 1.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 ) )
) 姓名
FROM
employees;
-- 1.5 instr 返回指定字符在另一个指定字符中第一次出现的索引，如果找不到返回0
SELECT
INSTR( '杨不殷六侠悔爱上了殷六侠', '殷八侠' ) AS out_put; # 0
SELECT
INSTR( '杨不殷六侠悔爱上了殷六侠', '殷六侠' ) AS out_put; # 3
-- 1.6 trim
-- 方法一：去掉前后的空格
SELECT
LENGTH( TRIM( ' 张翠山 ' ) ) AS out_put; # TRIM( ' 张翠山 ' )返回'张翠山'
-- 方法二：过滤掉前后指定的字符
SELECT
TRIM( 'aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ) AS out_put;
# a张aaaaaaaaaaaa翠山a
-- 1.7 lpad 用指定的字符实现左填充指定长度
SELECT
LPAD( '殷素素', 10, 'A' ) AS out_put; # AAAAAAA殷素素
SELECT
LPAD( '殷素素', 2, 'A' ) AS out_put; # 殷素
-- 1.8 rpad 用指定的字符实现右填充指定长度
SELECT
RPAD( '殷素素', 12, 'ab' ) AS out_put; # 殷素素ababababa
-- 1.9 replace 替换
SELECT REPLACE
( '周芷若周芷若周芷若周芷若张无忌爱上了周芷若', '周芷若', '赵敏' ) AS out_put;
# 赵敏赵敏赵敏赵敏张无忌爱上了赵敏
-- 2.数学函数
-- 2.1 round
-- 方法一：四舍五入
SELECT
ROUND( - 1.55 );# -2
-- 方法二：小数点后保留几位小数
SELECT
ROUND( 1.567, 2 );# 1.57
-- 2.2 ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(1.002); # 2
SELECT CEIL(-1.02); # -1
-- 2.3 floor 向下取整,返回<=该参数的最大整数
SELECT FLOOR(9.8); # 9
SELECT FLOOR(-9.99); # -10
-- 2.4 truncate 截断,小数点后保留几位小数
SELECT TRUNCATE(1.69999,1); # 1.6
-- 2.5 mod 取余,左边的数是正数，结果就是正的，反之则为负数
/*
mod(a,b) 相当于 a - a/b * b
mod(-10,-3) : -10 - (-10)/(-3) * (-3) = -1
mod(10,-3) : 10 - 10/(-3) * (-3) = 1
*/
SELECT MOD(10,3); # 1
SELECT MOD(10,-3); # 1
SELECT MOD(-10,3)； # -1
-- 3.日期函数
-- 3.1 now 返回当前系统日期+时间
SELECT NOW(); # 2020-10-04 18:12:56
-- 3.2 curdate 返回当前系统日期,不包含时间
SELECT CURDATE(); # 2020-10-04
-- 3.3 curtime 返回当前时间,不包含日期
SELECT CURTIME(); # 18:16:18
-- 3.4 可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年; # 2020
SELECT YEAR('1999-04-28') 年; # 1999
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月; # 10
SELECT MONTHNAME(NOW()) 月; # October
-- 3.5 str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1999-4-28','%Y-%c-%d') 日期; # 1999-04-28
-- 案例：查询入职日期为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');
-- 3.6 date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS 日期; # 20年10月04日
-- 案例：查询有奖金的员工名和入职日期，要求入职日期的格式是：(xx月/xx日 xx年)
SELECT
last_name,
DATE_FORMAT( hiredate, '%m月/%d日 %y年' ) 入职日期
FROM
employees
WHERE
salary IS NOT NULL;
-- 4.其他函数
-- 4.1 version 查看当前系统的mysql版本号
SELECT VERSION(); # 8.0.19
-- 4.2 database 查看当前所在数据库
SELECT DATABASE(); # myemployees
-- 4.3 user 查看当前用户
SELECT USER(); # [email protected]
-- 5.流程控制函数
-- 5.1 if函数 实现if-else的效果
SELECT IF(10>5,'大','小'); # 大
SELECT
last_name,
commission_pct,
IF
( commission_pct IS NULL, '没奖金，呵呵', '有奖金，嘻嘻' ) 备注
FROM
employees;
-- 5.2 case函数
-- 5.2.1 case函数的使用一：实现switch-case的效果
/*
Java中
switch(表达式){
case 常量1:
执行语句1;
break;
case 常量2:
执行语句2;
break;
...
defaule:
执行语句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;
-- 5.2.2 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 salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;
5.4.1 常见函数练习
-- 1. 显示系统时间(注：日期+时间)
SELECT NOW();
-- 2. 查询员工号，姓名，工资，以及工资提高百分之 20%后的结果（new salary）
SELECT
employee_id,
last_name,
salary,
salary * ( 1+0.2 ) AS "new salary"
FROM
employees;
-- 3. 将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT
last_name,
LENGTH( last_name ) 姓名长度,
SUBSTR( last_name, 1, 1 ) 首字母
FROM
employees
ORDER BY
首字母 ASC;
-- 4. 做一个查询，产生下面的结果
/*
<last_name> earns <salary> monthly but wants <salary*3>
Dream Salary
King earns 24000 monthly but wants 72000
*/
SELECT
CONCAT(
last_name,
' earns ',
salary,
' monthly but wants ',
salary * 3
) AS "Dream Salary"
FROM
employees
WHERE
salary = 24000;
-- 5. 使用 case-when，按照下面的条件：
/*
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
产生下面的结果
last_name job_id Grade
K_ing AD_PRES A
*/
SELECT last_name,job_id,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
END AS 'Grade' FROM employees WHERE job_id = 'AD_PRES';
5.5 分组函数
-- 进阶4：常见函数2
-- 分组函数
/*
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
sum 求和 、 avg 平均值 、 max 最大值 、 min 最小值 、 count 计算个数
特点：
1、sum、avg一般用于处理数值型
max、min、count可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配实现去重的运算
4、count函数的单独介绍
count(字段)：统计该字段非空值的个数
一般使用count(*)用作统计行数
另外 count(1)也是用来统计行数
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
SUM( salary ) 工资总和,
AVG( salary ) 工资平均值,
MIN( salary ) 最小工资,
MAX( salary ) 最大工资,
COUNT( salary ) 总共多少份工资
FROM
employees;
SELECT
SUM( salary ) 工资总和,
ROUND( AVG( salary ), 2 ) "工资平均值(保留2位小数)",
MIN( salary ) 最小工资,
MAX( salary ) 最大工资,
COUNT( salary ) 总共多少份工资
FROM
employees;
-- 2.参数支持哪些类型
SELECT SUM(last_name),AVG(last_name) FROM employees; # 不支持
SELECT SUM(hiredate) ,AVG(hiredate) 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：都忽略null值
SELECT
SUM( commission_pct ),
AVG( commission_pct ),
SUM( commission_pct ) / 35,
SUM( commission_pct ) / 107
FROM
employees;
SELECT MAX(commission_pct) ,MIN(commission_pct) FROM employees;
SELECT COUNT(commission_pct) FROM employees;
SELECT commission_pct FROM employees;
-- 4.和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
-- 5.count函数的详细介绍
/*
效率：
MYISAM存储引擎下 ，COUNT(*)的效率高
INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些
*/
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees; # 经常用来统计行数 107
SELECT COUNT(1) FROM employees; # 107
-- 6.和分组函数一同查询的字段有限制
SELECT AVG(salary),employee_id FROM employees; #报错
5.5.1 分组函数练习
-- 1. 查询公司员工工资的最大值，最小值，平均值(保留两位小数)，总和，并按 job_id 升序
SELECT
MAX( salary ) 最大值,
MIN( salary ) 最小值,
ROUND( AVG( salary ), 2 ) 平均值,
SUM( salary ) 总和
FROM
employees
ORDER BY
job_id ASC;
-- 2. 查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT
( MAX( salary ) - MIN( salary ) ) DIFFERENCE
FROM
employees;
-- 3. 查询各个管理者手下员工的最低工资，其中最低工资不能低于 6000，没有管理者的员工不计算在内
SELECT
-- 4. 查询所有部门的编号，员工数量和工资平均值,并按平均工资降序
-- 5. 选择具有各个 job_id 的员工人数
-- 6. 查询员工表中的最大入职时间和最小入职时间的相差天数（DIFFERENCE）
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFERENCE FROM employees;
-- 7. 查询某人从出生到现在的总天数
SELECT DATEDIFF(NOW(),'1999-04-28') 总天数;
-- 8.查询部门编号为90的员工个数
SELECT
COUNT( * )
FROM
employees 个数
WHERE
department_id = 90;
5.6 分组查询
-- 进阶5：分组查询
/*
语法： [ ]里面的内容表示可以省略的
select 查询列表 ①
from 表 ②
[where 筛选条件] ③
group by 分组的字段 ④
[having 分组后的筛选] ⑤
[order by 排序的字段]; ⑥
执行顺序：② -> ③ -> ④ - > ⑤ -> ① -> ⑤
注意：
和分组函数一同查询的字段必须是group by后出现的字段
特点：
1.分组查询中的筛选条件分为两类
数据源 位置 关键字
分组前筛选 原始表(真实存在的表) group by子句的前面 where
分组后筛选 分组后的结果集 group by子句的后面 having
① 分组函数做筛选条件，肯定是放在having子句中
② 能用分组前筛选的，就优先考虑使用分组前筛选(性能问题)
2.group by子句支持单个字段分组，多个字段分组(多个字段之间用逗号隔开并且没有顺序要求)，
表达式或函数(用得较少)
3.也可以添加排序（排序放在整个分组查询的最后）
*/
-- 1.简单的分组查询
-- 案例1：查询每个工种的最高工资
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
-- ① 查询每个部门的员工个数
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
job_id,
MAX( salary )
FROM
employees
WHERE
commission_pct IS NOT NULL
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;
-- 4.按表达式或函数分组
-- 案例1：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
-- ① 查询每个长度的员工个数
SELECT
COUNT( * ),
LENGTH( last_name ) 姓名长度
FROM
employees
GROUP BY
LENGTH( last_name );
-- ② 添加筛选条件
SELECT
COUNT( * ) c,
LENGTH( last_name ) 姓名长度
FROM
employees
GROUP BY
姓名长度
HAVING
c > 5;
-- 5.按多个字段进行分组
-- 案例1：查询每个部门每个工种的员工的平均工资
SELECT
AVG( salary ),
department_id,
job_id
FROM
employees
GROUP BY
department_id,
job_id;
-- 6.添加排序
-- 案例1：查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示，要求部门id不能为null，平均工资要大于10000
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;
5.6.1 分组查询练习
-- 1. 查询各 job_id 的员工工资的最大值，最小值，平均值，总和，并按 job_id 升序
SELECT
job_id,
MAX( salary ) 最大值,
MIN( salary ) 最小值,
ROUND( AVG( salary ), 2 ) 平均值,
SUM( salary ) 总和
FROM
employees
GROUP BY
job_id
ORDER BY
job_id ASC;
-- 2. 查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT
( MAX( salary ) - MIN( salary ) ) DIFFERENCE
FROM
employees;
-- 3. 查询各个管理者手下员工的最低工资，其中最低工资不能低于 6000，没有管理者的员工不计算在内
SELECT
manager_id,
MIN( salary ) 最低工资
FROM
employees
WHERE
manager_id IS NOT NULL
GROUP BY
manager_id
HAVING
MIN( salary ) > 6000;
-- 4. 查询所有部门的编号，员工数量和工资平均值,并按平均工资降序
SELECT
department_id,
COUNT( * ) 员工数量,
AVG( salary ) 平均工资
FROM
employees
GROUP BY
department_id
ORDER BY
AVG( salary ) DESC;
-- 5. 查询各个 job_id 的员工人数
SELECT
job_id,
COUNT( * ) 员工人数
FROM
employees
GROUP BY
job_id;
上一篇： Node.js使用formidable实现文件上传的功能
下一篇： 1019