mysql 李玉婷bilibili配套听课笔记（二）排序查询、常见函数

  -- 进阶3： 排序查询
  /*
语法：
select    查询列表
from     表
【where   筛选条件】
order by
排序列表
asc升序默认可省略/desc降序  
  */
  
  -- 案例1： 查询员工信息，按照工资高到低
  SELECT *
  FROM
  employees
  ORDER BY
  salary
  DESC;
  -- 案例2：查询部门编号>=90的员工信息，按照入职时间先后排序
  SELECT *
  FROM 
  employees
  WHERE
  department_id >= 90
  ORDER BY
  hiredate;
-- 案例3： 按年新的高低显示员工的信息和年薪【按表达式或者别名排序】
SELECT *, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪
FROM
employees
ORDER BY 年薪
DESC;
-- 案例5：按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT 
LENGTH(last_name) 姓氏长度, last_name, salary 
FROM
employees
ORDER BY 姓氏长度 DESC;

-- 案例6： 查询员工信息，要求先按工资再按员工编号排序【按多个字段排序】
SELECT
* 
FROM
employees
ORDER BY
salary ASC,
employee_id DESC;

-- 测试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;
-- 查询邮箱中包含e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT *, LENGTH(email)
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC, department_id ASC; 
 
 
 -- 进阶4： 常见函数
 /*
调用函数：select 函数名(实参列表) 【from 表】;
分类：       单行函数如(做处理用)：concat、length、ifnull
		 分组函数(做统计用)又称统计函数、聚合函数、组函数
 */
 
 -- ******* --
-- 单行函数 --
-- ******* --

 -- 一、字符函数
 -- length获取参数的字节个数
 SELECT LENGTH('john');
 SELECT LENGTH("张三丰"); -- utf-8中一个汉字三个字节
 
 -- concat拼接字符串
 SELECT CONCAT(last_name, '_', first_name) FROM employees;
 
 -- upper、 lower转换大小写
 SELECT UPPER('john');
SELECT LOWER("jJ");
-- 实例： 姓答谢名小写
SELECT CONCAT(UPPER(last_name), LOWER(first_name)) 姓名
FROM employees; 
 
 -- substr、substring截取子串 智能识别中英文
 -- SUBSTR('SUMESTRING', index)  截取从指定索引延申到最后的子串，索引是从1开始的
 -- SUBSTR('SUMESTRING', index, length) 截取从指定索引延申指定长度的子串
 -- 案例：姓氏中首字符大写，其他字符小写然后用_拼接， 显示出来
 SELECT CONCAT(UPPER(SUBSTR(last_name, 1, 1)), '_', LOWER(SUBSTR(last_name, 2))) 姓名
 FROM employees;
 
-- instr  和kmp一致的目的
SELECT INSTR('杨不悔爱上了殷六侠', '殷六侠') AS out_put
FROM employees;

-- trim 去前后空格
SELECT TRIM('       张翠兰     ')AS out_put;
SELECT TRIM("a" FROM'aaaaaa张脆aaaaa面aaaaaaaaaaaaaaaaaaaaaaaaaaa') AS out_out;

-- lpad  智能识别汉字和英语，用指定字符左填充至指定长度
SELECT LPAD('殷素素', 10, '*') AS out_put;  
-- rpad  同理右填充

-- replace 全部替换
SELECT REPLACE('张周芷若无忌爱上了周芷若', '周芷若', '赵敏') AS out_put;

-- 二、数学函数
-- round 绝对值四舍五入
-- round(数值)  数值四舍五入到整数
-- round(数值, 位数)  数值四舍五入到小数点后指定位数
SELECT ROUND(1.65);          -- 2
SELECT ROUND(-1.5);      	   -- -2
SELECT ROUND(-1.555, 2);   -- -1.56

-- ceil 向上取整
SELECT CEIL(-1.9999999999999);

-- floor 向下取整
SELECT FLOOR(-1.0000000003);

-- truncate 截断
SELECT TRUNCATE(1.65, 1);
SELECT TRUNCATE(-1.233, 0);

-- mod 取模
SELECT MOD(-10, -3);

-- 三、日期函数
-- now 返回当前系统日期+时间
SELECT NOW();

-- curdate 返回日期
SELECT CURDATE();

-- curtime 返回时间
SELECT CURTIME();

-- 可以获取指定的部分如年、月、日、小时、分、秒
SELECT YEAR(NOW());
SELECT DAY(CURDATE());
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTHNAME(NOW()) AS 月; -- 注意这个as 可以想到数组和函数本来一个作用都是通过一个值映射到另一个值，as这里就是针对于键的

-- str_to_date 将字符转换成日期
-- %Y四位年份、%y二位年份、%m有前导零月份、%c无前导零月份、%d日、%H时24、%h时12、%i分、%s秒。
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年') 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;
 
 -- 四、其他函数 
 SELECT VERSION();    -- 查看版本
 SELECT DATABASE(); -- 查看数据库
 SELECT USER();          -- 查看用户
 
 -- 五、流程控制函数
 -- 1. if函数：三目运算符的效果
 -- select if(arg1, arg2, arg3);
 -- 相当于  arg1 ? arg2 : arg3;
 SELECT IF(10 < 5, '大', "小");
 SELECT last_name, commission_pct, IF(commission_pct IS NULL,  '没奖金，呵呵', '有奖金，嘻嘻') 备注
 FROM employees;
 
 -- 2.case 
 -- case 函数用法一：switch case 的效果
 /*
case  要判断的字段或表达式
when 常量1 then 语句1; 或要返回的值1
when 常量2 then 语句2; 或要返回的值2
……
else 默认情况语句; 或要返回的值
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
   /*
   case 
   when 条件1 then 语句1;或者值1
   when 条件2 then 语句2;或者值2
   ……
   else 语句;或者值
   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;
-- 习题
-- 1.显示系统时间（日期加时间）
SELECT NOW();
-- 2. 查询员工号，姓名，工资，以及以上工资提高百分之20后的结果
SELECT employee_id, last_name, salary, salary * 1.2 'new salary' 
FROM employees;
-- 3.将员工的姓名按首字母排序，并写出姓名的长度（length)
SELECT LENGTH(last_name) 长度, SUBSTR(last_name, 1, 1) 首字符, last_name
FROM employees
ORDER BY 首字符;
-- 4. 做一个查询，产生如下显示：
-- Dream Salary 
-- <last_name> earns <salary> monthly but wants <salary * 3>
SELECT CONCAT(last_name, ' earns', salary, ' monthly but wants ', salary * 3) AS 'Dream Salary'
FROM employees;
-- 5. 使用case-when按照如下条件：
/*
job            grade
AD_PRES     A
ST_MAN	   B
IT_PROG      C
*/
SELECT job_id AS job,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
END AS grade
FROM employees;

-- ******* --
-- 分组函数 --
-- ******* --
-- SUM, AVG, MIN, MAX, COUNT
-- 特点：
-- 1. 参数支持哪些类型sum 和ave支持数值类型max, min, count支持任何类型
-- 2.是否忽略NULL值     忽略了
SELECT SUM(commission_pct), AVG(commission_pct) FROM employees;
-- 3. 可以和distinct搭配
SELECT SUM(DISTINCT salary),  SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary), COUNT(salary) FROM employees;
-- count 函数详细介绍
SELECT COUNT(salary) FROM employees;   -- 统计非空salary的个数
SELECT COUNT(*) FROM employees;   -- 统计行数
SELECT COUNT(1) FROM employees;   -- 统计行数
-- 上述统计行数的效率问题：MYISAM存储引擎下count((*)效率最高,INNODB存储引擎下(*)和(1)差不多
-- 用count(*)统计行数就对了
-- 4. 和分组函数一同查询的字段要求是group by 字段

-- 例题：
-- 1. 查询公司员工刚子的最大值，最小值，平均值，总和
SELECT MAX(salary) mx_sal, MIN(salary) mi_sal, AVG(salary) ag_sal, SUM(salary) sm_sal
FROM employees;
-- 2. 查询员工表中的最大入职时间和最小入职时间的相差天数
SELECT DATEDIFF(MAX(hiredate), MIN(hiredate)) difference -- 前减后
FROM employees;
-- 3. 查询部门编号为90的员工个数
SELECT COUNT(*) 个数
FROM employees
WHERE department_id = 90;