进阶3： 排序查询

/* 
order by 排序列表【asc||desc】
 */

案例： 实现工资从高到低

SELECT * FROM `employees` ORDER BY `salary`;

案例：查询部门编号>=90的员工信息，按入职时间进行排序

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

案例：按照年薪的高低显示员工的信息和年薪

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

找到包含e的邮箱，通过邮箱字节数进行降序，再通过部门编号进行升序

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

进阶4 常见函数

/*
概念：类似与java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏实现细节  2、提高代码的复用性
调用：select 函数名(实参列表) from 表;
特点：
	1.叫什么（函数名）
	2.做什么（函数功能）
分类：
	1.单行函数：
	如：concat、length、ifnull
	2.分组函数
	功能：做统计使用，又称为统计函数、聚合函数、组函数
*/

一、字符函数

1. length 获取参数的字节个数

SELECT LENGTH("length");

2. concat 拼接字符串

SELECT CONCAT(first_name,"_",last_name) FROM `employees`;

3. upper、lower

SELECT UPPER("john");
SELECT LOWER("joHn");

实例 姓大写，名小写

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

3. substr、substring 截取字符

两个参数

SELECT SUBSTR("李莫愁爱上了陆展元", 7) out_put;#陆展元

三个参数

SELECT SUBSTR("李莫愁爱上了陆展元", 1, 3) out_put;#李莫愁

案例： 姓名中首字母大写，用下划线连接其他字符

SELECT CONCAT(CONCAT(UPPER(SUBSTR(last_name, 1, 1)), "_", LOWER(SUBSTR(last_name, 2))), "_", LOWER(first_name)) 姓名 FROM `employees`;

5. instr 返回字符串第一次出现的位置，找不到返回0

SELECT INSTR("倚天屠龙记", '屠龙') `name`;

6. trim 清除字符串的前后空格

SELECT LENGTH(TRIM("   张翠山   ")) out_put;

去除其他字符

SELECT TRIM('a' FROM "aaaaaaaaaaaaaaaaaaa张a翠aaaaaaaaa山aaaaaaaaaaaaa") out_put;

7. lpad 左填充 rpad右填充 将内容补充为相应的长度

SELECT RPAD("殷素素", 10, "*");

8. replace 字符串替换

SELECT REPLACE("周芷若周芷若周芷若", "周芷若", "赵敏");

二、数学函数

round 四舍五入

SELECT ROUND(4.5);
SELECT ROUND(4.5456, 2); # 小数点后保留两位

ceil 向上取整

SELECT CEIL(1.15135);

floor 向下取整

SELECT FLOOR(9.99);

truncate 截断

SELECT TRUNCATE(6.699999, 1);

mod 取余

SELECT MOD(10, 3);
SELECT 10%3;

三、日期函数

1. now 返回当前系统的日期和时间

SELECT NOW();

2. curdate 返回系统的日期，不包含时间

SELECT CURDATE();

3. curtime 返回当前的时间，不包含日期

SELECT CURTIME();

3. 可以获取指定的部分

SELECT YEAR(NOW()) 年;
SELECT YEAR("1998-01-01") 年;
SELECT YEAR(`hiredate`) 年 FROM `employees`;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;

5. str_to_date 字符串转时间

SELECT STR_TO_DATE('2021-8-24', '%Y-%m-%d');

6. date_format 将日期转换成字符串

SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日');

四、其他函数

查看数据库版本号

SELECT VERSION();

查看当前数据库

SELECT DATABASE();

查看当前的用户

SELECT USER();

五、流程控制函数

1. if 函数

SELECT IF(10 > 5, "大", "小");

案例：找出没有奖金的可怜员工

SELECT `last_name`, `commission_pct`, IF( commission_pct IS NULL, "没有奖金", "有奖金") 备注 FROM `employees`; 

2. case 语句

/* 案例: 查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示工资为1.2倍
。。。
其他的部门展示原来的
*/

case 的使用一

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 的使用二

SELECT salary, 
CASE 
WHEN salary > 20000 THEN 'A'
WHEN salary > 15000 THEN 'B'
WHEN salary > 10000 THEN 'C'
ELSE 'D'
END AS 工资等级
FROM `employees`;