Mysql进阶学习（三）排序查询与常见函数
一、进阶3：排序查询1、语法：2、特点：3、排序方式3.1、按单个字段排序3.2、添加筛选条件再排序案例：查询部门编号>=90的员工信息，并按员工编号降序 3.3、按表达式排序案例：查询员工信息 按年薪降序 3.4、按别名排序案例：查询员工信息 按年薪升序 3.5、按函数排序案例：查询员工名，并且按名字的长度降序 3.6、按多个字段排序案例：查询员工信息，要求先按工资降序，再按employee_id升序 4、测试 二、进阶4：常见函数1、常见函数概述2、字符函数2.1.length 获取参数值的字节个数2.2.concat 拼接字符串2.3.upper、lower2.4.substr、substring2.5.instr 返回子串第一次出现的索引，如果找不到返回02.6.trim 从字符串的左侧和右侧去掉规定字符2.7.lpad 用指定的字符实现左填充指定长度2.8.rpad 用指定的字符实现右填充指定长度2.9.replace 替换 3、数学函数4、日期函数5、其他函数6、流程控制函数6.1.if函数： if else 的效果6.2.case函数的使用一： switch case 的效果6.3.case 函数的使用二：类似于 多重if 三、常见函数测试文中用到的代码
一、进阶3：排序查询
1、语法：
select 查询列表from 表名【where 筛选条件】order by 排序的字段或表达式;
2、特点：
1、asc代表的是升序，可以省略 desc代表的是降序
2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段
3、order by子句在查询语句的最后面，除了limit子句
3、排序方式
3.1、按单个字段排序
SELECT * FROM employees ORDER BY salary DESC;
3.2、添加筛选条件再排序
案例：查询部门编号>=90的员工信息，并按员工编号降序
SELECT *FROM employeesWHERE department_id>=90ORDER BY employee_id DESC;
3.3、按表达式排序
案例：查询员工信息 按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0))FROM employeesORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
3.4、按别名排序
案例：查询员工信息 按年薪升序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪FROM employeesORDER BY 年薪 ASC;
3.5、按函数排序
案例：查询员工名，并且按名字的长度降序
SELECT LENGTH(last_name),last_name FROM employeesORDER BY LENGTH(last_name) DESC;
3.6、按多个字段排序
案例：查询员工信息，要求先按工资降序，再按employee_id升序
SELECT *FROM employeesORDER BY salary DESC,employee_id ASC;
4、测试
1. 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序
3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
1. 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪FROMemployeesORDER BY 年薪 DESC,LENGTH(last_name) ASC;
选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序
SELECT last_name,salaryFROM employeesWHERE salary NOT BETWEEN 8000 AND 17000ORDER BY salary DESC;
查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT * ,LENGTH(email) 邮箱FROM employeesWHERE last_name LIKE '%e%' ORDER BY 邮箱 DESC,department_id ASC;
二、进阶4：常见函数
概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节
----------2、提高代码的重用性
调用：select函数名(实参列表) 【from表】;
特点：
①叫什么（函数名）
②干什么（函数功能）
分类：
1、单行函数
如concat、length、ifnull等
2、分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数
1、常见函数概述
字符函数：length:获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)concatsubstrinstrtrimupperlowerlpadrpadreplace数学函数：roundceilfloortruncatemod日期函数：nowcurdatecurtimeyearmonthmonthnamedayhourminutesecondstr_to_datedate_format其他函数：versiondatabaseuser控制函数ifcase
2、字符函数
2.1.length 获取参数值的字节个数
SELECT LENGTH('john');SELECT LENGTH('张三丰hahaha');SHOW VARIABLES LIKE '%char%'
2.2.concat 拼接字符串
SELECT last_name,first_name,CONCAT(last_name,'_',first_name) 姓名 FROM employees;
2.3.upper、lower
SELECT UPPER('john');SELECT LOWER('joHn');
示例：将姓变大写，名变小写，然后拼接
SELECT last_name,first_name,CONCAT(UPPER(last_name),LOWER(first_name)) 姓名FROM employees;
2.4.substr、substring
注意：索引从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))) out_putFROM employees;
2.5.instr 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;
SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷六侠') AS out_put;
2.6.trim 从字符串的左侧和右侧去掉规定字符
从字符串中去空格
SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put;
从字符串中除去前后的“aa”
SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') AS out_put;
2.7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',2,'*') AS out_put;
2.8.rpad 用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',12,'ab') AS out_put;
2.9.replace 替换
SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
3、数学函数
round 四舍五入
SELECT ROUND(-1.55);**小数点后保留两位小树**SELECT ROUND(1.567,2);
ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.02);
floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-9.99);
truncate 截断从小数点后第几位开始截断
SELECT TRUNCATE(1.69999,1);
mod取余
mod(a,b) ： a-a/b*bmod(-10,-3):-10- (-10)/(-3)*（-3）=-1SELECT MOD(10,-3);SELECT 10%3;
4、日期函数
now 返回当前系统日期+时间
SELECT NOW();
curdate 返回当前系统日期，不包含时间
SELECT CURDATE();
curtime 返回当前时间，不包含日期
SELECT CURTIME();
可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;SELECT YEAR('1998-1-1') 年;SELECT YEAR(hiredate) 年 FROM employees;SELECT MONTH(NOW()) 月;SELECT MONTHNAME(NOW()) 月;
str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
查询入职日期为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 out_put;
查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期FROM employeesWHERE commission_pct IS NOT NULL;
5、其他函数
查看当前数据库版本SELECT VERSION();查看当前使用的数据库 SELECT DATABASE();查看当前用户SELECT USER();
6、流程控制函数
6.1.if函数： if else 的效果
实现三元运算符的效果SELECT IF(10<5,'大','小');SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注FROM employees;
6.2.case函数的使用一： switch case 的效果
java中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 原始工资,department_id,CASE department_idWHEN 30 THEN salary*1.1WHEN 40 THEN salary*1.2WHEN 50 THEN salary*1.3ELSE salaryEND AS 新工资FROM employees;
6.3.case 函数的使用二：类似于 多重if
java中：if(条件1){语句1；}else if(条件2){语句2；}...else{语句n;}mysql中：case when 条件1 then 要显示的值1或语句1when 条件2 then 要显示的值2或语句2。。。else 要显示的值n或语句nend
案例：查询员工的工资的情况
如果工资>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;
三、常见函数测试
显示系统时间(注：日期+时间)
查询员工号，姓名，工资，以及工资提高百分之 20%后的结果（new salary）
将员工的姓名按首字母排序，并写出姓名的长度（length）
做一个查询，产生下面的结果 <last_name> earns monthly but wants <salary*3>
使用 case-when，按照下面的条件： job grade AD_PRES A ST_MAN B IT_PROG C SA_REP D ST_CLERK E 产生下面的结果
答案：
显示系统时间(注：日期+时间)
select now();
查询员工号，姓名，工资，以及工资提高百分之 20%后的结果（new salary）
SELECT employee_id,last_name,salary,salary*1.2 'new salary'FROM employees;
将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT LENGTH(last_name),SUBSTR(last_name,1,1) 首字符,last_nameFROM employeesORDER BY 首字符;
做一个查询，产生下面的结果
<last_name> earns monthly but wants <salary*3>
SELECT CONCAT(last_name,'earns',salary,'monthly but wants',salary*3) 'Dream Salary'FROM employeesWHERE salary = 24000;
使用 case-when，按照下面的条件：
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
产生下面的结果
SELECT last_name,job_id AS job, CASE job_idWHEN 'AD_PRES' THEN 'A'WHEN 'ST_MAN' THEN 'B'WHEN 'IT_PROG' THEN 'C'WHEN 'SA_REP' THEN 'D'WHEN 'ST_CLERK' THEN 'E'END gradeFROM employeesWHERE job_id = 'AD_PRES';
