Mysql 基础（三）排序查询及常用函数
Mysql基础（三）
进阶三：
排序查询：
语法：
select 查询列表
from 表
【where 筛选条件】
order by 排序的字段|表达式|函数|别名 【asc|desc】
特点：
1、asc代表的是升序，desc代表的是降序，如果不写，默认是升序
2、order by 子句中可以支持多个字段，多个字段、表达式、函数、别名
3、order by 子句一般是放在查询语句的最后面，limit子句除外
语句执行顺序是：
1、from 表 2、where 筛选条件、3、select 查询、4、order by 排序
#案例一：查询员工信息，要求工从高到低排序
SELECT * FROM employees ORDER BY salary DESC;
#如果不写 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 salary*12*(1+IFNULL(commission_pct,0)) DESC;
#案例四【按别名排序】：按年薪的高低显示员工的信息和年薪
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC;
#案例五【按函数排序】：按姓名的长度显示员工的姓名和工资
SELECT LENGTH(last_name) 字节长度,last_name,salary
FROM employees ORDER BY LENGTH(last_name) DESC;
#案例六【按多个字段排序】：查询员工信息，要求先按工资升序排序，再按员工编号降序排序
SELECT * FROM employees
ORDER BY salary ASC,employee_id DESC;
进阶四
常见函数
函数分类：
• 字符函数
• 数学函数
• 日期函数
• 其他函数【补充】
• 流程控制函数【补充】
字符函数
# 进阶四
# 常见函数
/*
功能类似于java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节 2、提高的代码的重用性
调用：selec 函数名(实参列表) 【from 表】;
特点：
1、方法名
2、功能
分类：
1、单行函数
如：concat 、length、ifnull等
2、分组函数
功能：做统计使用，有称为统计函数，聚合函数，组函数
*/
#一、字符函数
#1、length 获取参数值的字节（返回字节数，汉字占三个字节，字母占1个字节）
SELECT LENGTH('中国');
#2、cancat 拼接字符串
SELECT CONCAT(first_name,'_',last_name) 姓名 FROM employees;
#3、upper(字符变大写)、lower(字符变小写)
SELECT UPPER('json');
SELECT LOWER('JSON');
#案例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(first_name),LOWER(last_name)) 姓名 FROM employees;
#substr,substring
#注意在sql语言中，索引都是从1开始
#截取从索引开始到最后，指定索引
SELECT SUBSTR('好好学习，天天向上',6) 截取索引后所有;
#截取一段字符，指定开始索引和字符个数
SELECT SUBSTR('好好学习，天天向上',1,4) 截取一段结果;
#案例：姓名中首字符大写，其他字符小写 然后用'_' 拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) 结果
FROM employees;
#5、instr ： 返回子串第一次出现的索引，如果你找不到返回0
SELECT INSTR('好好学习，天天向上','天天' ) AS 插入结果 ;
#6、trim:去除前后指定字符，不指定字符时，去除前后空格
SELECT TRIM(' 天天向上 ') 出去空格的结果;
SELECT TRIM('a' FROM 'aaaaa天天aaaaa向上aaaa') 去掉a;
#7、Lpad :用指定的字符实现左填充指定长度
SELECT LPAD('好好学习',10,'*') AS 结果;
#9、replace 替换函数
SELECT REPLACE('好好学习，天天向上','向上','努力') AS 替换结果;
数学函数
#数学函数
#round 四舍无入
SELECT ROUND(-1.54);
SELECT ROUND(1.456,2); #小数点后保留两位
#ceil 向上取整,返回大于等于参数的最小整数
SELECT CEIL(-1.02);
#floor 向下取整，返回 <= 该参数的最大整数
SELECT FLOOR(-0.99);
#truncate 截断,保留小数端后几位
SELECT TRUNCATE(1.68989,1);
#mod 取余
SELECT MOD(100,-98);
SELECT 100%98;
日期函数
#日期函数
#now
SELECT NOW();
#curdate:返回当前系统日期，不包含时间
SELECT CURDATE();
#curtime
SELECT CURTIME();
#可以获取指定的部分,年、月、日、小时、分钟、秒
SELECT YEAR(NOW())AS 年;
SELECT YEAR('1998-02-04') 年;
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月 ;
SELECT MONTHNAME(NOW()) 英文月名;
#str_to_date: 将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('1999-05-30','%Y-%c-%d');
#案例入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate ='1992-4-3';
#date_format: 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日');
#案例:查询有奖金的员工名和入职日期(XX月 / XX 日 XX 年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月 / %d日 %y年') 入职如期
FROM employees WHERE commission_pct IS NOT NULL;
日期函数
#日期函数
#now
SELECT NOW();
#curdate:返回当前系统日期，不包含时间
SELECT CURDATE();
#curtime
SELECT CURTIME();
#可以获取指定的部分,年、月、日、小时、分钟、秒
SELECT YEAR(NOW())AS 年;
SELECT YEAR('1998-02-04') 年;
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月 ;
SELECT MONTHNAME(NOW()) 英文月名;
#str_to_date: 将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('1999-05-30','%Y-%c-%d');
#案例入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate ='1992-4-3';
#date_format: 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日');
#案例:查询有奖金的员工名和入职日期(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函数： if else 的效果
SELECT IF(10>5,'10大于五','10小于5');
SELECT last_name,commission_pct ,IF(commission_pct is NULL,'没奖金','有奖金') FROM employees;
#2、case函数的使用一：switch case 的效果
/*
case 要判断的字段会表达式
when 常量1 then 要显示的值1或语句1;
when 常量1 then 要显示的值1或语句1;
when 常量1 then 要显示的值1或语句1;
when 常量1 then 要显示的值1或语句1;
......
else 要显示的值n 或语句n
end
*/
/*
#案例：查询员工的工资，要求的
部门号=30，显示工资为1.1倍
部门号=40，显示工资为1.2倍
其他部门，显示的工资为原工资
*/
SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
ELSE salary
END AS 新工资
FROM employees;
#case 函数的使用二：类似于 多重if
/**
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。。
else 要显示的值n 或语句n
end
*/
/*
#案例：查询员工的工资的情况
如果工资》200000，显示A级别
如果工资> 15000,显示B级别
如果工资>100000,显示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、字符函数
concat拼接
substr截取子串
upper转换成大写
lower转换成小写
trim去前后指定的空格和字符
ltrim去左边空格
rtrim去右边空格
replace替换
lpad左填充
rpad右填充
instr返回子串第一次出现的索引
length 获取字节个数
2、数学函数
round 四舍五入
rand 随机数
floor向下取整
ceil向上取整
mod取余
truncate截断
3、日期函数
now当前系统日期+时间
curdate当前系统日期
curtime当前系统时间
str_to_date 将字符转换成日期
date_format将日期转换成字符
4、流程控制函数
if 处理双分支
case语句 处理多分支
情况1：处理等值判断
情况2：处理条件判断
5、其他函数
version版本
database当前库
user当前连接用户
分组函数
#分组函数
#分类：sum ：求和,avg: 平均值、max 最大值、min 最小值、count 计算个数
#1、简单的使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
SELECT SUM(salary) 和,AVG(salary) 平均,
MAX(salary) 最高,MIN(salary) 最低,
COUNT(salary) 个数 FROM employees;
#2、参数类型支持那些类型
/*
特点：
1、sum 、avg 一般用于处理数值类型
max,min，count可以处理任何类型
2、以上分组函数都忽略null值
3、可以和Distinct搭配实现去重的运算
4、count函数的单独介绍
一般使用count(*)统计函数
5、和分组函数一同查询的字段要求是group by 后的字段
*/
SELECT SUM(last_name) ,AVG(last_name) FROM employees;
#一般sum和avg支持数字类型的字段
SELECT MAX(last_name),MIN(last_name) FROM employees;
SELECT MAX(hiredate),MIN(hiredate) FROM employees;
SELECT COUNT(last_name) FROM employees;
SELECT COUNT(commission_pct) FROM employees;
#3、是否忽略null
SELECT SUM(commission_pct),AVG(commission_pct) FROM employees;
#4、和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
#5、count函数的详细介绍
SELECT COUNT(*) FROM employees;
SELECT COUNT(salary) FROM employees;
#效率
MYISAM 存储引擎下，count(*)的效率最高;
INNODB 存储引擎下，count(*)和 count(1)的效率差不多，比count(字段)要高一些;
#6、很分组函数一同查询的字段有限制
SELECT AVG(salary),employee_id FROM employees;
