MySQL基础 Day02

排序查询介绍

-- 语法
select
    查询列表
from
    表
where
    筛选条件
order by
    排序列表 asc|dsc

案例1：查询员工信息，要求工资从高到低排序

SELECT
	*
FROM
	employees
ORDER BY
	salary DESC;

特点：
①asc升序，desc降序，默认升序

排序查询示例

案例2：查询部门编号>=90的员工信息，按入职时间先后排序【添加筛选条件】

SELECT
	*
FROM
	employees
WHERE
	department_id>=90
ORDER BY
	hiredate;

案例3：按年薪的高低显示员工的信息和年薪【按表达式排序】

SELECT
	*, salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM
	employees
ORDER BY
	salary*12*(1+IFNULL(commission_pct,0));

案例4：按年薪的高低显示员工的信息和年薪【按别名排序】

SELECT 
    *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM 
    employees
ORDER BY 
    年薪;

案例5：按姓名的长度显示员工的姓名和工资【按函数】

SELECT LENGTH(last_name) 长度, last_name, salary
FROM
	employees
ORDER BY
	长度;

案例6：查询员工信息，要求先按工资排序，再按员工编号排序【按多个字段排序】

SELECT
	* 
FROM
	employees
ORDER BY
	salary,employee_id;

排序查询总结

1. asc升序，desc降序。默认升序；
2. order by子句中可以支持单个字段、多个字段、表达式、函数、别名；
3. order by子句一般是放在查询语句的最后面，limit子句除外。

test_lesson03 排序数据 

1. 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序

SELECT
	last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM
	employees
ORDER BY
	salary*12*(1+IFNULL(commission_pct,0)) DESC, last_name;

2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序

SELECT
	last_name, salary
FROM
	employees
WHERE
	salary NOT BETWEEN 8000 AND 17000
ORDER BY
	salary DESC;

3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序

SELECT
	*
FROM
	employees
WHERE
    email LIKE '%e%'
ORDER BY
	LENGTH(email) DESC, department_id;

常用函数

概念：类似于java中的方法，将一组逻辑方法封装在方法体中，对外暴露方法名

好处：1. 隐藏了实现细节； 2. 提高代码的重用性

调用：select 函数名(实参列表)  【from 表】;

特点：①叫什么（函数名）；②干什么（函数功能）

分类：1. 单行函数  如：concat、length、ifnull

2. 分组函数  功能：做统计使用，又称为统计函数、聚合函数、组函数

单行函数

字符函数

1. length

SELECT LENGTH('john');
-- 4
SELECT LENGTH('浙江大学ZJU');
-- 15 utf8中一个汉字占3个字节

SHOW VARIABLES LIKE '%char%';
-- 显示当前使用的什么字符集

2. concat 拼接字符串

SELECT CONCAT(last_name, '-', first_name) 姓名 FROM employees;

3. upper、lower

示例：将 姓 变大写，名变小写，然后拼接

SELECT CONCAT(UPPER(last_name),'_', LOWER(first_name)) 姓名 FROM employees;

4. substr 

-- substr(str,pos) 浙大（从pos开始到结尾）
SELECT SUBSTR('考上浙大', 3);
-- substr(str FROM pos) 上浙大（从pos开始）
SELECT SUBSTR('考上浙大' FROM 2);
-- substr(str, pos, len) 浙（从pos开始长度为len的子串）
SELECT SUBSTR('考上浙大', 3, 1);

5. instr 返回子串第一次出现的索引，如果找不到返回0

SELECT INSTR('我爱的人不爱我', '爱') out_put;
-- 2

6. trim 删除前后空格，或指定字符

SELECT TRIM('     浙江大学     ') AS out_put;
--浙江大学

SELECT TRIM('zju' FROM 'zjuzjuzju浙江zjuzjuzju大学zjuzjuzju') AS out_put;
--浙江zjuzjuzju大学

7. lpad 用指定的字符实现左填充指定长度

SELECT LPAD('zju',4,'*') AS out_put;
--*zju
SELECT LPAD('zju',2,'*') AS out_put;
--zj

8. rpad用指定的字符实现右填充

9. replace 替换

SELECT REPLACE('I am graduated from hdu', 'hdu', 'zju') AS out_put;
--I am graduated from zju

数学函数

1. round 四舍五入

SELECT ROUND(-1.55);
-- -2

2.ceil 向上取整：返回>=该参数的最小整数

SELECT CEIL(-1.02);
-- -1

3.floor 向下取整：返回<=该参数的最大整数

SELECT FLOOR(-9.99);
-- -9

4. truncate 截断，保留 位小数

SELECT TRUNCATE(1.69999,1);
-- 1.6

5. mod 取模

MOD(a,b)：a-a/b*b
例：mod(10,3)：10-10/3*3 = 10-3*3 = 1

SELECT MOD(-10, -3);

日期函数

 

-- 返回当前系统日期和时间
SELECT NOW();
--2021-03-24 16:43:32

-- 返回当前系统日期，不包含时间
SELECT CURDATE();
--2021-03-24

-- 返回当前系统时间，不包含日期
SELECT CURTIME();
--16:45:22

获取指定的部分：年、月、日、时、分、秒

SELECT YEAR(NOW()) 年;
--2017

str_to_date：将日期格式的字符 转换成 指定格式的日期

SELECT STR_TO_DATE('9-13-1999','%m-%d-%Y') AS out_put;
--1999-09-13

date_format：将日期转换成字符

SELECT DATE_FORMAT('2021/3/24','%Y年%m月%d日') AS out_put;
--2021年03月24日

其他函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

流程控制函数

if函数：if else 的效果

类似三元运算符

SELECT IF(10>5,'大','小');
--大

SELECT 
    last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，哎','有奖金，好开心~') 备注
FROM
    employees;

case函数

一、switch case 的效果

case  要判断的字段或表达式
when  常量1  then  要显示的值1或语句1;
when  常量1  then  要显示的值1或语句1;
when  常量1  then  要显示的值1或语句1;
...
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 ;

二、类似于多重 if

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 ;

test_lesson04 单行函数

1. 显示系统时间(注：日期+时间)

SELECT NOW();

 2. 查询员工号，姓名，工资，以及工资提高百分之 20%后的结果（new salary）

SELECT employee_id,last_name,salary,salary*1.2 提成后工资
FROM employees;

3. 将员工的姓名按首字母排序，并写出姓名的长度（length）

SELECT 
  last_name,
  LENGTH(last_name) 长度 
FROM
  employees 
ORDER BY last_name ASC ;

4. 做一个查询，产生下面的结果

 

分组函数

功能：用作统计使用，又称为聚合函数、统计函数、组函数。

分类：sum, avg, max, min, count

1. sum、avg一般用于处理数值型
max、min、count可以处理任何类型

2. 以上所有分组函数都忽略null值

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

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) 和,
  ROUND(AVG(salary)) 平均,
  MIN(salary) 最小,
  MAX(salary) 最大,
  COUNT(salary) 个数
FROM
  employees ;

2. 参数类型支持哪些类型：

3. 忽略null

4. 和distinct搭配

SELECT 
  SUM(DISTINCT salary) 去重求和,
  SUM(salary) 求和 
FROM
  employees ;

count函数的具体介绍

统计总行数

SELECT COUNT(*) FROM employees;

SELECT COUNT(1) FROM employees;

效率：
MYISAM存储引擎下，*效率最高
INNODB存储引擎下，*和1的效率差不多，比COUNT(字段)要高一些。

和分组函数一同查询的字段有限制

test_lesson05 分组函数（1）

1. 查询公司员工工资的最大值，最小值，平均值，总和

SELECT 
  MAX(salary),
  MIN(salary),
  AVG(salary),
  SUM(salary) 
FROM
  employees;

2. 查询员工表中的最大入职时间和最小入职时间的相差天数（DIFFERENCE）

SELECT 
	DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFRENCE
FROM
	employees;

3.查询部门编号为90的员工个数

SELECT 
  COUNT(*) 
FROM
  employees 
WHERE department_id = 90 ;

分组查询

分组查询的简单实用

案例1：查询每个工种的最高工资

SELECT 
  MAX(salary),
  job_id
FROM
  employees 
GROUP BY job_id ;

  

案例2：查询每个位置上的部门个数

SELECT 
  COUNT(*),
  location_id 
FROM
  departments 
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

①查询每个部门的员工个数

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 
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 ;

添加筛选的总结

1.分组查询中的筛选条件：

	数据源	位置	关键字
分组前筛选	原始表	group by子句的前面	where
分组后筛选	分组后的结果集	group by子句的后面	having

分组函数作条件可定放在having子句中，max，min，sum....

优先考虑使用分组前筛选

按表达式or函数分组

案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>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 department_id,
  job_id ;

添加排序

案例：查询每个部门每个工种的员工的平均工资，且按平均工资降序排序

SELECT 
  AVG(salary) 平均工资,
  department_id,
  job_id 
FROM
  employees 
GROUP BY department_id,
  job_id 
ORDER BY 平均工资 DESC ;

分组查询总结

group by 子句支持多个字段分组，用逗号隔开，无顺序要求

也可以添加排序，放在整个分组查询的最后

test_lesson05 分组函数

1. 查询各 job_id 的员工工资的最大值，最小值，平均值，总和，并按 job_id 升序

SELECT 
  job_id,
  MAX(salary),
  MIN(salary),
  AVG(salary),
  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，没有管理者的员工不计算在内

 

4. 查询所有部门的编号，员工数量和工资平均值,并按平均工资降序

 

5. 选择具有各个 job_id 的员工人数