Mysql进阶学习（三）排序查询与常见函数

文中用到的代码

1、语法&＃xff1a;

select 查询列表
from 表名
【where 筛选条件】
order by 排序的字段或表达式;


2、特点&＃xff1a;

1、asc代表的是升序&＃xff0c;可以省略 desc代表的是降序

2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段

3、order by子句在查询语句的最后面&＃xff0c;除了limit子句

3、排序方式

3.1、按单个字段排序

SELECT * FROM employees ORDER BY salary DESC;


3.2、添加筛选条件再排序

案例&＃xff1a;查询部门编号>&＃61;90的员工信息&＃xff0c;并按员工编号降序

SELECT *
FROM employees
WHERE department_id>&＃61;90
ORDER BY employee_id DESC;


3.3、按表达式排序

案例&＃xff1a;查询员工信息 按年薪降序

SELECT *,salary*12*(1&＃43;IFNULL(commission_pct,0))
FROM employees
ORDER BY salary*12*(1&＃43;IFNULL(commission_pct,0)) DESC;


3.4、按别名排序

案例&＃xff1a;查询员工信息 按年薪升序

SELECT *,salary*12*(1&＃43;IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 ASC;


3.5、按函数排序

案例&＃xff1a;查询员工名&＃xff0c;并且按名字的长度降序

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


3.6、按多个字段排序

案例&＃xff1a;查询员工信息&＃xff0c;要求先按工资降序&＃xff0c;再按employee_id升序

SELECT *
FROM employees
ORDER BY salary DESC,employee_id ASC;


4、测试

1. 查询员工的姓名和部门号和年薪&＃xff0c;按年薪降序 按姓名升序
2. 选择工资不在 8000 到 17000 的员工的姓名和工资&＃xff0c;按工资降序
3. 查询邮箱中包含 e 的员工信息&＃xff0c;并先按邮箱的字节数降序&＃xff0c;再按部门号升序

1. 查询员工的姓名和部门号和年薪&＃xff0c;按年薪降序 按姓名升序

SELECT last_name,department_id,salary*12*(1&＃43;IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC,LENGTH(last_name) ASC;


2. 选择工资不在 8000 到 17000 的员工的姓名和工资&＃xff0c;按工资降序

SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;


3. 查询邮箱中包含 e 的员工信息&＃xff0c;并先按邮箱的字节数降序&＃xff0c;再按部门号升序

SELECT * ,LENGTH(email) 邮箱
FROM employees
WHERE last_name LIKE &＃39;%e%&＃39;
ORDER BY 邮箱 DESC,department_id ASC;


概念&＃xff1a;类似于java的方法&＃xff0c;将一组逻辑语句封装在方法体中&＃xff0c;对外暴露方法名
好处&＃xff1a;1、隐藏了实现细节
----------2、提高代码的重用性
调用&＃xff1a;select 函数名(实参列表) 【from 表】;
特点&＃xff1a;
①叫什么&＃xff08;函数名&＃xff09;
②干什么&＃xff08;函数功能&＃xff09;

分类&＃xff1a;
1、单行函数
如 concat、length、ifnull等
2、分组函数

功能&＃xff1a;做统计使用&＃xff0c;又称为统计函数、聚合函数、组函数


1、常见函数概述

字符函数&＃xff1a;
length:获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)
concat
substr
instr
trim
upper
lower
lpad
rpad
replace
数学函数&＃xff1a;
round
ceil
floor
truncate
mod
日期函数&＃xff1a;
now
curdate
curtime
year
month
monthname
day
hour
minute
second
str_to_date
date_format
其他函数&＃xff1a;
version
database
user
控制函数
if
case


2、字符函数

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

SELECT LENGTH(&＃39;john&＃39;);
SELECT LENGTH(&＃39;张三丰hahaha&＃39;);
SHOW VARIABLES LIKE &＃39;%char%&＃39;


2.2.concat 拼接字符串

SELECT last_name,
first_name,
CONCAT(last_name,&＃39;_&＃39;,first_name) 姓名
FROM employees;


2.3.upper、lower

SELECT UPPER(&＃39;john&＃39;);
SELECT LOWER(&＃39;joHn&＃39;);


示例&＃xff1a;将姓变大写&＃xff0c;名变小写&＃xff0c;然后拼接

SELECT last_name,
first_name,
CONCAT(UPPER(last_name),LOWER(first_name)) 姓名
FROM employees;


2.4.substr、substring

注意&＃xff1a;索引从1开始
截取从指定索引处后面所有字符

SELECT SUBSTR(&＃39;李莫愁爱上了陆展元&＃39;,7) out_put;


截取从指定索引处指定字符长度的字符
从第一位开始截取三个长度的字符

SELECT SUBSTR(&＃39;李莫愁爱上了陆展元&＃39;,1,3) out_put;


案例&＃xff1a;姓名中首字符大写&＃xff0c;其他字符小写然后用_拼接&＃xff0c;显示出来

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),&＃39;_&＃39;,LOWER(SUBSTR(last_name,2))) out_put
FROM employees;


2.5.instr 返回子串第一次出现的索引&＃xff0c;如果找不到返回0

SELECT INSTR(&＃39;杨不殷六侠悔爱上了殷六侠&＃39;,&＃39;殷八侠&＃39;) AS out_put;


SELECT INSTR(&＃39;杨不殷六侠悔爱上了殷六侠&＃39;,&＃39;殷六侠&＃39;) AS out_put;


2.6.trim 从字符串的左侧和右侧去掉规定字符

从字符串中去空格

SELECT LENGTH(TRIM(&＃39; 张翠山 &＃39;)) AS out_put;


从字符串中除去前后的“aa”

SELECT TRIM(&＃39;aa&＃39; FROM &＃39;aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa&＃39;) AS out_put;


2.7.lpad 用指定的字符实现左填充指定长度

SELECT LPAD(&＃39;殷素素&＃39;,2,&＃39;*&＃39;) AS out_put;


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

SELECT RPAD(&＃39;殷素素&＃39;,12,&＃39;ab&＃39;) AS out_put;


2.9.replace 替换

SELECT REPLACE(&＃39;周芷若周芷若周芷若周芷若张无忌爱上了周芷若&＃39;,&＃39;周芷若&＃39;,&＃39;赵敏&＃39;) AS out_put;


3、数学函数

round 四舍五入

SELECT ROUND(-1.55);
**小数点后保留两位小树**
SELECT ROUND(1.567,2);


ceil 向上取整,返回>&＃61;该参数的最小整数

SELECT CEIL(-1.02);


floor 向下取整&＃xff0c;返回<&＃61;该参数的最大整数

SELECT FLOOR(-9.99);


truncate 截断 从小数点后第几位开始截断

SELECT TRUNCATE(1.69999,1);


mod取余

mod(a,b) &＃xff1a; a-a/b*b
mod(-10,-3):-10- (-10)/(-3)*&＃xff08;-3&＃xff09;&＃61;-1
SELECT MOD(10,-3);
SELECT 10%3;


4、日期函数

now 返回当前系统日期&＃43;时间

SELECT NOW();


curdate 返回当前系统日期&＃xff0c;不包含时间

SELECT CURDATE();


curtime 返回当前时间&＃xff0c;不包含日期

SELECT CURTIME();


可以获取指定的部分&＃xff0c;年、月、日、小时、分钟、秒

SELECT YEAR(NOW()) 年;
SELECT YEAR(&＃39;1998-1-1&＃39;) 年;
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;


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

SELECT STR_TO_DATE(&＃39;1998-3-2&＃39;,&＃39;%Y-%c-%d&＃39;) AS out_put;


查询入职日期为1992–4-3的员工信息

SELECT * FROM employees WHERE hiredate &＃61; &＃39;1992-4-3&＃39;;
SELECT * FROM employees WHERE hiredate &＃61; STR_TO_DATE(&＃39;4-3 1992&＃39;,&＃39;%c-%d %Y&＃39;);


date_format 将日期转换成字符

SELECT DATE_FORMAT(NOW(),&＃39;%y年%m月%d日&＃39;) AS out_put;


查询有奖金的员工名和入职日期(xx月/xx日 xx年)

SELECT last_name,DATE_FORMAT(hiredate,&＃39;%m月/%d日 %y年&＃39;) 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;


5、其他函数

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


6、流程控制函数

6.1.if函数&＃xff1a; if else 的效果

实现三元运算符的效果
SELECT IF(10<5,&＃39;大&＃39;,&＃39;小&＃39;);
SELECT last_name,commission_pct,IF(commission_pct IS NULL,&＃39;没奖金&＃xff0c;呵呵&＃39;,&＃39;有奖金&＃xff0c;嘻嘻&＃39;) 备注
FROM employees;


6.2.case函数的使用一&＃xff1a; switch case 的效果

java中
switch(变量或表达式){
case 常量1&＃xff1a;语句1;break;
...
default:语句n;break;
}
mysql中
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end


案例&＃xff1a;查询员工的工资&＃xff0c;要求

部门号&＃61;30&＃xff0c;显示的工资为1.1倍
部门号&＃61;40&＃xff0c;显示的工资为1.2倍
部门号&＃61;50&＃xff0c;显示的工资为1.3倍
其他部门&＃xff0c;显示的工资为原工资

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;


6.3.case 函数的使用二&＃xff1a;类似于 多重if

java中&＃xff1a;
if(条件1){
语句1&＃xff1b;
}else if(条件2){
语句2&＃xff1b;
}
...
else{
语句n;
}
mysql中&＃xff1a;
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句n
end


案例&＃xff1a;查询员工的工资的情况

如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000&＃xff0c;显示C级别 否则&＃xff0c;显示D级别

SELECT salary,
CASE
WHEN salary>20000 THEN &＃39;A&＃39;
WHEN salary>15000 THEN &＃39;B&＃39;
WHEN salary>10000 THEN &＃39;C&＃39;
ELSE &＃39;D&＃39;
END AS 工资级别
FROM employees;