Mysql学习笔记(2):排序查询、常见函数

1. 排序查询

1.1 基本语法

select 
	查询列表
from 
	表
where 
	筛选条件
order by 排序列表 【asc}desc】

1.2 特点

1.asc:升序，如果不写默认升序
desc:降序
2.排序列表支持单个字段、多个字段、函数、表达式、别名
3.order by的位置一般放在查询语句的最后（除limit语句之外）

#1、按单个字段排序
SELECT * FROM employees ORDER BY salary DESC;

#2、添加筛选条件再排序

#案例：查询部门编号>=90的员工信息，并按员工编号降序

SELECT *
FROM employees
WHERE department_id>=90
ORDER BY employee_id DESC;


#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 年薪 ASC;

#5、按函数排序
#案例：查询员工名，并且按名字的长度降序

SELECT LENGTH(last_name),last_name 
FROM employees
ORDER BY LENGTH(last_name) DESC;

#6、按多个字段排序

#案例：查询员工信息，要求先按工资降序，再按employee_id升序
SELECT *
FROM employees
ORDER BY salary DESC,employee_id ASC;

2. 常见函数

2.1 字符函数

concat:连接
substr:截取子串
upper:变大写
lower：变小写
replace：替换
length：获取字节长度
trim:去前后空格
lpad：左填充
rpad：右填充
instr:获取子串第一次出现的索引

#1.LENGTH：获取的是字节长度，收到编码的影响
SELECT LENGTH('jhon')
SELECT LENGTH('哈哈哈')

#2.CONCAT
SELECT CONCAT(last_name, '_', first_name) AS '姓名' FROM employees;

#3.UPPER、LOWER
SELECT UPPER('jhon')
SELECT LOWER('ABC')
#姓变大写、名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) AS '姓名' FROM employees;

#4.SUBSTR、SUBSTRING 
#注意：索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR('没有什么能够阻挡孙悟空', 9) AS output;
#截取指定索引处指定长度的字符
SELECT SUBSTR('没有什么能够阻挡孙悟空',1,3) AS output;  

# 案例:姓名中首字符大写，其他字符小写然后用_拼接，显示
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(SUBSTR(last_name,1)))) AS output
FROM employees;

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) AS output FROM employees;

#5.instr:返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('abcdefg','efg') AS out_put FROM employees;

#6.trim:去掉前后的字符
SELECT LENGTH(TRIM('   acb    ')) AS output
SELECT TRIM('a' FROM 'aaaaaaaaaaaaaaaaaa吴aaaaaaaaaaaa彦aaaaa祖aaaa') AS output;

#7.lpad:用指定的字符实现左填充指定长度
SELECT LPAD('吴彦祖',10,'*') AS output;

#8.rpad:用指定的字符实现右填充指定长度
SELECT RPAD('吴彦祖',12,'ab') AS output;

#9.replace 替换
SELECT REPLACE('周芷若张无忌爱上了周芷若','周芷若','赵敏') AS output;

2.2 数学函数

ceil:向上取整
round：四舍五入
mod:取模
floor：向下取整
truncate:截断
rand:获取随机数，返回0-1之间的小数

#round 四舍五入
SELECT ROUND(1.45);
SELECT ROUND(1.567,2);

#ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(1.10);

#floor 向下取整,返回<=该参数的最小整数
SELECT FLOOR(9.99);

#truncate 截断
SELECT TRUNCATE(1.699999,1);

#mod取余
SELECT MOD(10,3);
SELECT 10%3;

2.3 日期函数

now：返回当前日期+时间
year:返回年
month：返回月
day:返回日
date_format:将日期转换成字符
curdate:返回当前日期
str_to_date:将字符转换成日期
curtime：返回当前时间
hour:小时
minute:分钟
second：秒
datediff:返回两个日期相差的天数
monthname:以英文形式返回月

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

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

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

#可以获得指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) AS 年;
SELECT YEAR('1998-1-1') AS 年;
SELECT YEAR(hiredate) AS 年 FROM employees;

SELECT MONTH(NOW()) AS 月;
SELECT MONTHNAME(NOW()) AS 月;

SELECT DAY(NOW()) AS 天;

SELECT HOUR(NOW()) AS　小时;
SELECT MINUTE(NOW()) AS 分钟;
SELECT SECOND(NOW()) AS 秒钟;


#str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1996-3-4','%Y-%c-%d') AS output;

#查询入职日期为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 output;

#查询有奖金的员工和入职日期(xx月/xx日  xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') AS 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;

2.4 其他函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

2.5 流程控制函数

1.if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2
2.case情况1
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
…
else 值n
end

3.case情况2
case
when 条件1 then 值1
when 条件2 then 值2
…
else 值n
end

#1.if函数：if else 的效果
SELECT IF(10<5, '大', '小');


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

#2.case函数的使用一：
/*
jave中
switch(变量或表达式){
	case 常量1: 语句1; break;
	...
	default:语句n;break;
}

mysql中

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 AS 原始工资,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;

#3.case函数的使用二： 类似多重if
/*
java中
if(条件1){
	语句1;
}
esle if(条件2){
	语句2;
}
...
else{
	语句n;
}

mysql中:
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end
*/

#案例：查询员工的工资情况
/*
如果工资>20000 显示A级别
如果工资>15000 显示B级别
如果工资>10000 显示C级别
否则显示D级别
*/

SELECT salary,
CASE 
WHEN salary >= 20000 THEN 'A'
WHEN salary BETWEEN 15000 AND 20000 THEN 'B'
WHEN salary BETWEEN 10000 AND 15000 THEN 'C'
ELSE 'D'
END AS 等级
FROM employees;

2.6 分组函数

max 最大值
min 最小值
sum 和
avg 平均值
count 计算个数

SELECT 
	SUM(salary) AS '求和', 
	ROUND(AVG(salary),2) AS '平均', 
	MIN(salary) AS '最小', 
	MAX(salary) AS '最大',
	COUNT(salary) AS '个数'
FROM employees;

特点：

1. sum和avg一般用于处理数值型,max、min、count可以处理任何数据类型
2. 以上分组函数都忽略null
3. 都可以搭配distinct使用，实现去重的统计

SELECT 
	COUNT(department_id), 
	COUNT(DISTINCT department_id) 
FROM employees;

4. count函数
   count(字段)：统计该字段非空值的个数
   count(*):统计结果集的行数

SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees; #总行数
SELECT COUNT(1) FROM employees; #相当于加了一列常量，来统计行数

效率上：
MyISAM存储引擎，count(*)最高
InnoDB存储引擎，count(✳)和count(1)效率>count(字段)
5. 和分组函数一同查询的字段，要求是group by后出现的字段

参考资料：
《尚硅谷MySQL数据库教程》