Mysql查询相关知识（进阶三 排序查询，进阶四：常见函数）

前言

上次说到了MySQL的查询进阶一和二，这次继续讨论查询的相关的知识 链接: https://pan.baidu.com/s/1a_wstxPVhxeliZkt6-BTiw 提取码: gaj8

进阶三 排序查询

语法

 select 
        查询列表
    from
        表
    where
        筛选条件
        order by 排序列表【asc/desc】
    特点：
        1.asc代表的是升序，desc代表的是降序
        2.如果不写的话就表示默认的升序
        3.order by子句中可以支持单个字段，多个字段，表达式，函数，别名
        4.order by子句一般是放在查询语句的最后面，limit子句除外

案例

#案例一、查询出工资从高到底的所有员工的信息
SELECT 
    *
FROM
    employees
ORDER BY salary DESC;
#案例二、查询出部门>=90按入职的时间的先后顺序排序的所有员工的信息（添加筛选条件）
SELECT
    *
FROM
    employees
WHERE 
    `department_id`>=90
ORDER BY
    `hiredate` ASC;
#案例三、按年薪的高低显示员工的信息，年薪（按表达式排序）
SELECT
    *,
    `salary`*12*(1+IFNULL(`commission_pct`,0)) AS 年薪
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
    年薪 DESC
#案例五、按姓名的长度显示员工的姓名和工资（按函数排序）
SELECT
    LENGTH(`last_name`) AS 姓名的长度,
    `last_name`,
    `salary`
FROM
    `employees`
ORDER BY LENGTH(`last_name`) DESC;
#案例六、按员工的编号降序，工资升序查询所有的员工的信息（按多个字段排序）
SELECT
    *
FROM
    `employees`
ORDER BY `employee_id` DESC,`salary`;

测试题

#查询员工的姓名和部门号和年薪，按年薪降序，姓名升序
SELECT
    last_name,
    department_id,
    salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM
    employees
ORDER BY 年薪 DESC,last_name;
#查询工资不在8000-17000的员工的姓名和工资，工资降序
SELECT
    last_name,
    salary
FROM
    `employees`
WHERE
    NOT salary BETWEEN 8000 AND 17000 ORDER BY salary DESC;
#查询员工邮箱中含e的信息，并先按邮箱的字节数排序，再按部门号升序
SELECT
    *
FROM
    `employees`
WHERE
    `email` LIKE '%e%' ORDER BY LENGTH (email) DESC,`department_id`; 

进阶四：常见函数

概念

类似于Java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名

好处：
①隐藏了实现细节 ②提高了代码的重用性
调用：select 函数名（实参列表） 【from 表】;
特点：
叫什么（函数名）
干什么（函数功能）
分类：
1.单行函数
如：concat、length、ifnull等
字符函数
数字函数
日期函数
其他函数
流程控制函数
2.分组函数

功能：做统计使用，又称为统计函数，聚合函数，组函数

一、字符函数

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

SELECT LENGTH('hahaha');
SELECT LENGTH('留个名hahaha');

2.concat拼接字符串

SELECT CONCAT(`first_name`,':',`last_name`) FROM employees;

3.upper(变大写) lower(变小写)

SELECT UPPER('jdk');
SELECT LOWER('JDK');
#例子：将姓变大写名变小写打出来
SELECT CONCAT(UPPER(`first_name`),':',LOWER(`last_name`)) FROM `employees`;

4.substr,subtring

#截取从指定索引处后面的所有字符
注意：索引从1开始的（Java索引是从0开始的）
SELECT SUBSTR('黎豪爱吃屎',2);
#截取从指定索引出指定字符长度的字符
SELECT SUBSTR('黎豪爱吃屎',1,2);
#案例、名字中的首字母大写，其余的小写然后用_连接起来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))
FROM `employees`

5.instr 返回字串第一次出现的索引，如果找不到的话就返回0

SELECT INSTR('黎豪好爱吃屎，没人拦得住他','拦得住');
#6.trim 去掉前后的空格
SELECT TRIM('   黎豪吃屎   ') AS out_put;
SELECT TRIM('a' FROM 'aaaaa黎aaa豪aaa');#去掉的是前后的a,中间的a去不掉

6.lpad 用指定的字符实现左填充指定字符的长度(当如果指定的长度小于最前面写的字符的时候，前面的字符会被截断)

SELECT LPAD('黎豪',10,'*');

7.rpad 用指定的字符实现右填充指定字符的长度

SELECT RPAD('黎豪',10,'#');

8.replace替换

SELECT REPLACE('黎豪爱上了屎','屎','尿');

二、数学函数

1.round四舍五入

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

2.ceil向上取整（返回>=该参数的最小整数）

SELECT CEIL(1.02);

3.floor向下取整（返回<=该参数的最大整数）

SELECT FLOOR(2.01);

4.truncate截断

SELECT TRUNCATE(1.6523,2);

5.mod取余(mod(a,b)里面的运算就是（a-a/b*b）)

SELECT MOD(10,3);

三、日期函数

1.now 返回当前系统的如期＋时间

SELECT NOW();

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

SELECT CURDATE();

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

SELECT CURTIME();

4.可以获取指定的部分的年，月，日，小时，分钟，秒

SELECT YEAR(NOW());
SELECT YEAR('2012-1-2');
SELECT YEAR(hiredate) FROM employees;

SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());#显示的是英文的月

5.str_to_date 将字符通过指定的格式转换成日期

SELECT STR_TO_DATE('5-20 1999','%m-%d %Y');
#案例、查询入职时间为1992-4-3的员工的信息
SELECT
	last_name,
	hiredate
FROM
	`employees`
WHERE
	hiredate=STR_TO_DATE('4-3 1992','%m-%d %Y');

6.date_format将日期转换成字符

SELECT DATE_FORMAT('1523-2-3','%m月-%d日-%Y年');
#案查询有奖金的员工的员工名和入职日期（xx月-xx日-xx年）
SELECT `last_name`,DATE_FORMAT(hiredate,'%m月-%d日-%Y年') AS 入职日期
FROM `employees`
WHERE `commission_pct` IS NOT NULL;

四、其他函数

SELECT VERSION();#查看版本
SELECT DATABASE();#查看当前的数据库
SELECT USER();#查看当前的用户

五、流程控制函数

#1.if函数->if else的效果：if（1，2，3）如果1成立则是结果2如果不成立的话则是结果3
#案例、查询出有奖金（有奖金，厉害啊）的与没有奖金（没有奖金，加油啊）的员工进行备注
SELECT `last_name`,IF(`commission_pct` IS NOT NULL,'有奖金，厉害啊','没有奖金，加油啊') AS 备注
FROM `employees`

#2.case函数的使用一、类似于Java中的swich
/*
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
/*
case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
when 条件3 then 要显示的值3或语句3
。。。
else 要显示的值n或语句n
end
*/
/*
案例、查询员工的工资情况
如果工资大于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`;

六、分组函数

简要概述

功能：
做统计使用，又称为聚合函数统计函数和组函数
分类：
sum求和、avg平均值、max最大值、min最小值、count计算个数
特点：
1.sum,avg一般用于处理数值型
max,min,count可以处理任何类型
2.以上的分组函数都忽略null值
3.以上和distinct搭配
4.count函数的单独介绍
5.和分组函数一同查询的字段要求是group by 后的字段

1.简单的使用

SELECT SUM(salary) FROM `employees`;

2.和distinct搭配使用

SELECT SUM(DISTINCT salary),SUM(salary) FROM `employees`;

3.count函数的单独介绍

#一般使用count(*)用作统计行数
SELECT COUNT(*) FROM `employees`;
SELECT COUNT(1) FROM employees;#也可以统计行数
SELECT COUNT('黎豪') FROM employees;#也可以统计行数
#案例、查询部门编号为90的员工个数
SELECT COUNT(*)
FROM `employees`
WHERE department_id = '90';