排序&分组查询

1. 排序查询

* 语法：
    select
        要查询的东西
    from
        表
    where 
        条件
    order by 排序的字段|表达式|函数|别名 【asc|desc】
* 排序方式：
    * ASC：升序，默认的
    * DESC：降序
*排序分类：
    1.按单个字段进行排序
    2.按多个字段排序
    3.按表达式排序
    4.按别名排序
    5.按函数排序
* 注意：
    * 如果有多个排序条件，则当之前的条件值一样时，才会判断第二条件

课堂练习

1. 将姓名中包含e字符的年薪和姓名显示出来，并且按年薪进行降序
SELECT 
  `salary` * 12 * (1+ IFNULL(`commission_pct`, 0)) AS 年薪,
  `last_name` 
FROM
  `employees` 
WHERE `last_name` LIKE '%e%' 
ORDER BY 年薪 DESC ;
2. 显示所有有奖金的员工姓名、奖金、姓名的长度，按姓名的长度从低到高排序
SELECT 
  `last_name`,
  `commission_pct`,
  LENGTH(`last_name`) 
FROM
  `employees` 
WHERE `commission_pct` IS NOT NULL 
ORDER BY LENGTH(`last_name`) ASC ;
3. 部门编号>50的按工资从高到低排序，如果一样，再按frist_name升序
SELECT 
  * 
FROM
  `employees` 
WHERE `department_id` > 50 
ORDER BY `salary` DESC,
  `last_name` ASC ;
​

---

2. 常见函数

1、字符函数
    concat: 拼接
    substr: 截取子串      substring 
        select substr('李莫愁爱上了陆展元',7) out_put;    陆展元(索引从1开始)
        select substr('李莫愁爱上了陆展元',1,3) out_put;  李莫愁(3代表长度)
    upper: 转换成大写
    lower: 转换成小写
    trim: 去掉前后指定的空格和字符（前后）
        select trim ('aa' from 'aaaaaaaaa张aaaa翠山aaaaaaa') as out_put;    a张aaaa翠山a
    ltrim: 去左边空格
    rtrim: 去右边空格
    replace: 替换
    lpad: 左填充
         select lpad('殷素素',10,'*') as out_put;  *******殷素素
         select lpad('殷素素',2,'*') as out_put;   殷素
    rpad: 右填充
    instr: 返回子串第一次出现的索引
    length: 获取字节个数  （UTF-8  3个字节）（GBK  2个字节）
-------------------------------------------------------------------------
2、数学函数
    round: 四舍五入 
       select round(-1.55);   -2
       select round(1.567,2);  1.57
    rand: 随机数  
       
    ceil: 向上取整 (返回>=该参数的最小整数)
    mod: 取余(被除数为正数结果为正，反之为负数)
         select mod(10,-3);   1
         select mod(-10,-3);  -1
         select mod(-10,3);   -1
    truncate: 截断
         select truncate(1.69999,1);  1.6
-------------------------------------------------------------------------
3、日期函数
    now: 当前系统日期+时间
    curdate: 当前系统日期
    curtime: 当前系统时间
    str_to_date: 将字符转换成日期
        select str_to_date('3-2 1998','%c-%d %Y') as out_put;  1998-03-02
    date_format: 将日期转换成字符
        select date_format(now(),'%y年%m月%d日') as out_put;   23年06月15日
     
    获取指定部分，年、月、日、小时、分钟、秒
        select year(now())；  2023
        select year('1998-1-1'); 1998
        select month(now());   6
-------------------------------------------------------------------------
4、流程控制函数
    if 处理双分支
        select if(10<5,'大','小')；
    case语句 处理多分支（类似switch case） 
        情况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`;
        情况2：处理条件判断（类似于多重if语句）
        /*
案例：查询员工的工资的情况
如果工资>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、其他函数
    version: 版本
    database: 当前库
    user: 当前连接用户

 

 

课堂练习

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 
  SUBSTR(`last_name`, 1, 1) AS 首字母,
  LENGTH(`last_name`) 
FROM
  `employees` ;
​
ORDER BY 首字母;

---

3. 分组函数

* count：计算个数
    * 一般选择非空的列：主键
    * count(*)
* max：计算最大值
* min：计算最小值
* sum：计算和
* avg：计算平均值
* 特点：
    1、以上五个分组函数都忽略null值，除了 count(*)
    2、sum和avg一般用于处理数值型,max、min、count可以处理任何数据类型
    3、都可以搭配distinct使用，用于统计去重后的结果
    4、count的参数可以支持：
        * 字段、*、常量值，一般放1
* 注意：聚合函数的计算，排除null值。
    解决方案：
        * 选择不包含非空的列进行计算
        * IFNULL函数
​
datediff : 求时间差

课堂练习

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

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

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

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

---

4.分组查询 （ 分组函数做条件肯定是放在having子句中）

* 语法：
    select 查询的字段，分组函数
    from 表
    【where】
    group by 分组的字段
    【having】
    【order by】
* 特点：
    1、可以按单个字段分组
    2、和分好是分组后的字段
    3、可以按多个字段分组，字段之间用逗号隔开
    4、可以支持排序
    5、having后可以支持别名
* 面试相关
    * where 和 having 的区别？
        1. where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不               满足结果，则不会被查询出来
        2. where 后不可以跟聚合函数，having可以进行聚合函数的判断。

课堂练习

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

SELECT 
  `job_id`,
  MAX(`salary`),
  MIN(`salary`),
  SUM(`salary`),
  AVG(`salary`)
FROM
  `employees` 
GROUP BY `job_id` 
ORDER BY `job_id` ASC;

2.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

SELECT 
  `manager_id`,
  MIN(`salary`) 
FROM
  `employees` 
WHERE `manager_id` IS NOT NULL 
GROUP BY `manager_id` 
HAVING MIN(`salary`) > 6000 ;

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

SELECT 
  `manager_id`,
  COUNT(*),
  AVG(`salary`) 
FROM
  `employees` 
WHERE `manager_id` IS NOT NULL
GROUP BY `manager_id` 
ORDER BY AVG(`salary`) DESC ;