排序查询
使用 ORDER BY 子句排序
ASC(ascend):升序
DESC(descend):降序
语法
SELECT 查询列表
FROM 表名
[WHERE 筛选条件]
ORDER BY 排序的字段或表达式;
特点
ASC代表的是升序,可以省略。
ORDER BY子句可以支持 单个字段、别名、表达式、函数、多个字段
ORDER BY子句在查询语句的最后面,除了LIMIT子句
案例
单个字段排序
查询员工表按薪水降序
SELECT * FROM employees ORDER BY salary DESC;
查询部门编号>=90的员工信息,并按员工编号降序
SELECT
*
FROM
employees
WHERE
department_id >= 90
ORDER BY employee_id DESC;
按表达式排序
查询员工信息,按年薪降序
SELECT *, salary * 12 * (1 + IFNULL(commission_pct, 0))
FROM employees
ORDER BY salary * 12 * (1 + IFNULL(commission_pct, 0)) DESC;
按别名排序
查询员工信息,按年薪升序
SELECT *, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪
FROM employees
ORDER BY 年薪 ASC;
按函数排序
查询员工名,并且按名字的长度降序
SELECT LENGTH(last_name), last_name
FROM employees
ORDER BY LENGTH(last_name) DESC;
多个字段排序
查询员工信息,要求先按工资降序,再按employee_id升序
SELECT * FROM employees ORDER BY salary DESC, employee_id ASC;
单行函数
字符函数
LENGTH(s)
获取参数值的字节个数
SELECT LENGTH('Tom');
# utf8字符集下,一个汉字占3个字节,gbk中,一个汉字占2个字节
SELECT LENGTH('张三丰hahaha');
CONCAT(s1, s2,..., sn)
拼接字符串
# 这里表示使用 _ 连接字段last_name和字段first_name
SELECT CONCAT(last_name, '_', first_name) 姓名 FROM employees;
UPPER(s)
将字符串转换为大写
SELECT UPPER('tomcat');
LOWER(s)
将字符串转换为小写
SELECT LOWER('tomCat');
# 示例:将姓变大写,名变小写,然后拼接
SELECT CONCAT(UPPER(last_name), LOWER(first_name)) 姓名 FROM employees;
SUBSTR(s, start, length)、SUBSTRING()
SUBSTR(s, start, length):从字符串s的start位置截取长度为length的子字符串
截取字符串(注意:索引从1开始)
# 从第7个字符开始截取(包括7)
SELECT SUBSTR('日向雏田爱上了旋涡鸣人', 7) out_put;
# SUBSTRING() 效果和SUBSTR()一致
SELECT SUBSTRING('日向雏田爱上了旋涡鸣人', 7) out_put;
SELECT SUBSTR('日向雏田爱上了旋涡鸣人', 1, 4) out_put;
# 案例:姓名中首字符大写,其他字符小写然后用_拼接,显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name, 1,1)), '_', LOWER(SUBSTR(last_name, 2))) out_put FROM employees;
INSTR(s, s)
返回子串第一次出现的索引,如果找不到,返回0
SELECT INSTR('日向雏田爱上了漩涡鸣人,漩涡鸣人爱上了雏田', '漩涡鸣人') AS out_put;
TRIM(s)
LTRIM(s):去掉字符串s开始处的空格
RTRIM(s):去掉字符串s结尾处的空格
去掉字符串开始和结尾处的空格:
SELECT LENGTH(TRIM(' 日向雏田 ')) AS out_put;
# 去除字符串前后指定的字符
SELECT TRIM('a' FROM 'aaaaa雏aa田aaaaaa');
# aa会被当做一个整体
SELECT TRIM('aa' FROM 'aaaaa雏aa田aaaaaa');
LPAD(s1, len, s2)
在字符串s1的开始处填充字符串s2, 使字符串长度达到len,如果长度小于原字符串长度,则只取前len个字符
SELECT LPAD('日向雏田', 6, '*') AS out_put;
# result: **日向雏田
SELECT LPAD('日向雏田', 2, "*") AS out_put;
# result: 日向
RPAD(s1, len, s2)
在字符串s1结尾处添加字符串s2,使字符串的长度达到len,如果长度小于原字符串,则只取前len个字符
SELECT RPAD('日向雏田', 6, '*') AS out_put;
# result:日向雏田**
SELECT RPAD('日向雏田', 2, '*') AS out_put;
# result:日向
REPLACE(s, s1, s2)
用字符串 s2 替代字符串 s 中的字符串s1
SELECT REPLACE('漩涡鸣人爱上了春野樱', '春野樱', '日向雏田') AS out_put;
# result : 漩涡鸣人爱上了日向雏田
数学函数
ROUND(x)
ROUND(x, d):四舍五入,保留d位小数
SELECT ROUND(-1.55); --> -2
SELECT ROUND(1.567, 2); --> 1.57
CEIL(x)
向上取整
SELECT CEIL(-1.02); --> -1
SELECT CEIL(1.02); --> 2
FLOOR(x)
向下取整
SELECT FLOOR(-1.58); --> -2
SELECT FLOOR(1.58); --> 1
TRUNCATE(x, y)
返回数值 x 保留到小数点后 y 位的值(与ROUND最大的区别是不会进行四舍五入)
SELECT TRUNCATE(1.567, 2); --> 1.56
MOD(x, y)
MOD(x, y)的结果会等于:x - x / y * y
返回 x 除以 y 以后的余数
SELECT MOD(10, -3); --> 1
# 等价于
SELECT 10%-3; --> 1
# 余数的符号取决于被除数的符号
SELECT MOD(-10, 3); --> -1
SELECT MOD(-10, -3); --> -1
日期函数
NOW()
返回当前系统时间(注:日期+时间)
SELECT NOW();
CURDATE()
返回当前系统日期,不包含时间
SELECT CURDATE();
CURTIME()
返回当前时间,不包含日期
SELECT CURTIME(); --> 00:41:45
YEAR(d)
返回年份
SELECT YEAR(NOW());
SELECT YEAR('1999-1-1'); --> 1999
案例:
SELECT YEAR(hiredate) 入职时间 FROM employees;
MONTH(d)
返回日期 d 中的月份值,1到12
SELECT MONTH(NOW());
MONTHNAME(d)
返回日期当中的月份的英文名称,如 September
SELECT MONTHNAME(NOW());
STR_TO_DATE(s, f)
将字符通过制定的格式转换成日期
SELECT STR_TO_DATE('1998-9-25', '%Y-%c-%d') AS out_put; --> 1998-09-25
案例:查询入职时间为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(d, f)
将日期通过指定的格式转换成字符
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;
其他函数
VERSION()
返回数据库的版本号
SELECT VERSION();
DATABASE()
返回当前数据库名
SELECT DATABASE();
USER()
返回当前用户
SELECT USER();
PASSWORD('字符')
返回该字符的密码形式
SELECT PASSWORD('雏田');
MD5('字符')
返回该字符的md5加密形式
SELECT MD5('雏田');
流程控制函数
IF(expr, v1, v2)
如果表达式expr成立,返回结果 v1, 否则返回结果 v2
SELECT IF(1 > 0, '正确', '错误'); --> 正确
案例:
SELECT last_name, commission_pct,
IF(commission_pct IS NULL, '没奖金,日了狗了', '有奖金,开心') 备注
FROM employees;
CASE
CASE expression
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
when conditionN THEN resultN
ELSE result
END
CASE表示函数开始,END表示函数结束。如果condition1成立,则返回result1,如果condition2成立,则返回result2,当全部不成立则返回result,而当有一个成立之后,后面的就不执行了。
CASE 要判断的字段或表达式
WHEN 常量1 THEN 要显示的值1或语句1
WHEN 常量2 THEN 要现实的值2或语句2
...
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;
案例二:
查询员工的工资情况,要求:
如果工资>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;