MySQL数据库——排序查询和常见单行函数

#进阶3：排序查询
引入
SELECT * FROM employees;
查到的顺序原始的顺序是一致的 
/*
语法：
select 查询列表    ③
 from 表     ①
 [where 筛选条件]     ②
 order by 排序列表 [asc | desc]    ④

特点：
    ASC:升序
    DESC：降序
    如果不写，默认是升序
    order by可以支持单个字段、多个字段、表达式、函数、别名
    order by执行顺序：一般放在查询语句的最后面，但limit子句除外
    
*/
#案例：查询员工信息，要求工资从高到低排序
SELECT 
  * 
FROM
  employees 
ORDER BY salary DESC ;

#从低到高
SELECT 
  * 
FROM
  employees 
ORDER BY salary ASC ;

#案例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 ;

#案例5：根据姓名长度显示员工姓名和工资
SELECT 
  LENGTH(last_name) 字节长度,
  last_name,
  salary 
FROM
  employees 
ORDER BY 字节长度 DESC ;

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

#进阶4：常见函数
/*
概念：类似于Java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：隐藏了实现细节，提高代码的重用性
调用：select 函数名(实参列表) from 表;(如果函数的参数用到了表中的就要加from表)
特点：叫什么（函数名)
      干什么（函数功能）
分类：
    单行函数：如concat,length,ifnull等
    分组函数：做统计使用，又称为统计函数，复用函数，组函数
*/
字符函数
数学函数
日期函数
其他函数【补充】
流程控制函数【补充】

一、字符函数
#length  获取参数值的字节个数
SELECT LENGTH('john');
SELECT LENGTH('张三丰hhh');#一个汉字三个字节

SHOW VARIABLES LIKE '%char%';

#concat 拼接字符串
SELECT 
  CONCAT(last_name, '_', first_name) 
FROM
  employees ;

#upper/lower
SELECT UPPER('john');
SELECT LOWER('JOHN');
案例：将姓变大写，将名变小写，然后拼接 
SELECT 
  CONCAT(
    UPPER(last_name),
    '_',
    LOWER(first_name)
  ) AS 姓名 
FROM
  employees ;

#substr/substring
#sql语言索引从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 ;

#instr 返回子串在字符串中的第一个起始索引，否则返回0
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put;  #7

#trim 去前后空格
SELECT LENGTH(TRIM('    张翠山      ')) AS out_put;
SELECT TRIM('a' FROM 'aaaaaaa张翠山aaaaaa') AS out_put;

#lpad 用指定的字符实现左填充长度，最终长度肯定跟指定的一样
SELECT LPAD('殷素素',10,'*') AS out_put;
SELECT LPAD('殷素素',2,'*') AS out_put; #殷素

#rpad 用指定的字符实现右填充长度

#replace 替换,会替换所有的
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put;

#二、数学函数

#round 四舍五入
SELECT ROUND(2.65);#3
SELECT ROUND(-2.33);#-2
SELECT ROUND(1.567,2);#1.57 小数点后两位

#ceil 向上取整 返回大于等于该参数的最小整数
SELECT CEIL(1.52);#1
SELECT CEIL(1.00);#1
SELECT CEIL(-1.02);#-1

#floor 向下取整 返回小于等于该参数的最大整数
SELECT FLOOR(-9.99);#-10

#truncate 截断
SELECT TRUNCATE(1.68,1);#小数点后保留1位，直接截断

#mod 取模、取余
SELECT MOD(10,3);#1
SELECT MOD(10,-3);#1

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

#curdate 返回当前系统日期，不包含时间
SELECT CURDATE();

#curtime 返回当前时间，不包含日期
SELECT CURTIME();

#可以获取指定的部分，年月日小时分钟秒
SELECT YEAR(NOW()) 年;#2021
SELECT YEAR('1998-1-1') 年;#1998
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月;#2
SELECT MONTHNAME(NOW()) 月;#February

#str_to_date:将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('2021-2-15','%Y-%c-%d') AS out_put;#2021-02-15
#查询入职日期为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; 
  
 #案例：查询有奖金的员工明和入职如期
 SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') AS 入职日期
 FROM employees
 WHERE commission_pct IS NOT NULL;
 
 #四、其他函数
 SELECT VERSION();
 SELECT DATABASE();#查看当前库
 SELECT USER();#查看当前用户
 
 #五、流程控制函数
 #1.if函数：if else
 SELECT IF(10>5,'大','小');
 
SELECT 
  last_name,
  commission_pct,
  IF(
    commission_pct IS NULL,
    '没奖金,呵呵',
    '有奖金，嘻嘻'
  ) AS 备注 
FROM
  employees ;

#2.case函数 控制结构
#使用一：switch case
/*
语法：
case 言判断的字段或表达式
when 常量1 then 语句1；
...
else 要显示的值n或语句n；
end
*/
#案例：查询员工的工资，要求部门号=30，显示工资为1.1倍，如果部门号为30，显示的工资为1.2倍...
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 条件1 then 要显示的值2或语句2
else 要显示的值n或语句n
end
*/
#案例：查询员工新工资，如果工资>20000，显示A级别，>15000显示B级别
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
ELSE 'C'
END AS 工资级别
FROM employees;

常见函数：
    字符函数：LENGTH CONCAT SUBSTR TRIM LOWER UPPER RPAD REPLACE CEIL follr TRUNCATE MOD
    日期函数：NOW CURDATE CURTIME YEAR MONTH MONTHNAME DAY HOUR MINUTE SECOND STR_TO_DATE DATE_FORMAT
    其他函数：VERSION DATABASE USER
    控制函数：IF CASE
测试：
1.显示系统时间
SELECT NOW();
2. 查询员工名、姓名、工资，以及工资提高百分之 20 后的结果 
SELECT 
  employee_id,
  last_name,
  salary,
  salary * (1+0.2) AS new_salary 
FROM
  employees ;

3. 将员工的姓名按首字母排序，并写出姓名的长度 
SELECT 
  LENGTH(last_name),
  SUBSTR(last_name, 1, 1) AS 首字符,
  last_name 
FROM
  employees 
ORDER BY 首字符 ;
#order by last_name;先按照第一个字符排，如果第一个一样按照第二个字母排