MySQL基础（二）

1、排序查询

1、ORDER BY

1、asc 代表的是升序，desc 代表的是降序；
2、如果不写，则默认为升序；
3、order by 子句中可以支持单个字段，也支持多个字段，表达式，函数，别名
4、执行顺序：order by 一般放在查询语句的最后边，但是 limit 字句除外

语法：

SELECT
查询列表
FROM
表名
WHERE
筛选条件
order by 排序列表 asc|desc；

案例一：查询员工信息，要求工资进行从高到低排序

SELECT
		*
FROM
		employees
WHERE
		salary>10000
ORDER BY	salary DESC;

案例二：查询员工信息，要求工资进行从低到高排序

SELECT
		*
FROM
		employees
WHERE
		salary>10000
ORDER BY	salary ASC;

案例三：查询部门编号大于等于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;

2、常见函数

概念：类似于java方法，将一组逻辑语句封装在方法体中，对外暴露的是方法名；
优势：1、隐藏了实现细节，2、提高了代码的重用性
调用：SELECT 函数名(实参列表) 【FROM 表】；
特点：1、函数名
2、功能
3、返回值

分类：1、单行函数
例 ： concat，length，ifnull 等；
2、分组函数
功能：做统计使用，又称为统计函数，聚合函数，组函数；

2.1、字符函数
2.1.1、length （ 获取参数值的字节个数 ）

SELECT LENGTH('john');

2.1.2、concat （拼接字符）

SELECT CONCAT(last_name,'_',first_name)   姓名   FROM   employees;

2.1.3、upper，lower

SELECT UPPER('John');	//字符全部变大写
SELECT LOWER('John');	//字符全部变小写

实例：姓变大写，名变小写，之后拼接

SELECT 
	CONCAT(UPPER(last_name),LOWER(first_name))  姓名  
FROM 
	employees;    //发现函数可以嵌套调用

2.1.4、substr（），subString（）；

SELECT SUBSTR('15485231464564',11) out_put;  //截取返回第11个字符后所有字符
SELECT SUBSTR('15485231464564',1,3) out_put; 	//截取从第一个字符开始长度为3的字符串

案例：姓名中首字母大写，其他字符小写然后用 _ 进行拼接，显示出来

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

2.1.5、instr（）；
//instr 返回子串第一次出现的起始索引，如果找不到则返回0；

SELECT INSTR('123456789','789') AS out_put;   //‘7’

2.1.6、trim（）
、、、、只能都删除一个字符串前后的空格
、、、、或者删除一个字符串前后的指定字符；

SELECT TRIM('    5     ') AS out_put;
SELECT TRIM(A FROM 'AAA7878AAA') AS out_put;

2.1.7、lpad
lpad … 用指定的字符实现左填充指定的长度

SELECT LPAD('123456',10,'*') AS out_put;  //****123456
SELECT LPAD('123456',4,'*') AS out_put; 	//1234

2.1.8、rpad
rpad … 用指定的字符实现右填充指定的长度

SELECT RPAD('123456',10,'*') AS out_put;		//123456****
SELECT RPAD('123456',4,'*') AS out_put; 	//1234

2.1.9、replace 替换

SELECT REPLACE('123456789','123','999') AS out_put;   //999456789

2.2、数学函数
2.2.1、round //四舍五入

SELECT ROUND(1.456);	//1
SELECT ROUND(1.654);	//2
SELECT ROUND(1.256,2);	//1.26

2.2.2、ceil 向上取整，返回 >= 该参数的最小整数；

SELECT CEIL(-1.02);   //-1
SELECT CEIL(2.005);    //3

2.2.2、floor 向下取整，返回 <= 该参数的最大整数；

SELECT FLOOR(9.9999);	//9
SELECT FLOOR(-9.9999);	 //-10	

2.2.3、truncate 、、截断，小数点后直接截断

SELECT TRUNCATE(1.699999,1);	//1.6

2.2.4、mod 取余

SELECT MOD(10,3);	//1
SELECT 10%3;

2.3、日期函数
2.3.1、now 返回当前日期+时间

SELECT NOW();

2.3.2、curdate 返回当前日期，不包含时间

SELECT CURDATE();

2.3.3、curtime 返回当前时间，不包含日期

SELECT CURTIME();

2.2.4、也可以获取指定部分的，年，月，日，小时，分钟，秒

SELECT YEAR(NOW()) AS year;		//获取年
SELECT MONTH(NOW()) AS month;		//获取月份

2.2.5、str_to_date 将日期格式的"字符"转化为指定格式的字符

SELECT STR_TO_DATE('9-13-1999','%m-%d-%Y') AS time;		//1999-9-13

案例一：查询入职日期是 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');

2.2.6、date_format … 将日期转化为字符

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') AS out_put; //

2.4、其他函数
2.4.1、VERSION

SELECT VERSION();

2.4.2、DATABASE

SELECT DATABASE();

2.4.3、USER

SELECT USER();

2.5、流程控制函数
2.5.1、if 函数：if else 的效果

SELECT IF(10>5,'大','小') AS result;
。。。。。。。。。。。
SELECT 
		last_name,commission_pct,IF(commission_pct IS NULL,'没奖金','有奖金')  备注 
FROM 
		employees;

2.5.2、case 函数
CASE函数的第一种用法，语法1：

case 	要判断的字段或者表达式
when	常量1	then	要显示的值1或语句1;
when	常量2	then	要显示的值2或语句2;
when	常量3	then	要显示的值3或语句3;
..............
else	要显示的值n或语句n;
end

案例：查询员工的工资，要求
1、部门号=30，显示工资为原工资的1.1倍
2、部门号=40，显示工资为原工资的1.2倍
3、部门号=50，显示工资为原工资的1.3倍
4、其余部门号，显示工资为原工资

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;

CASE函数的第二种用法，语法2：

CASE 
WHEN	条件一	THEN	要显示的值1或语句1
WHEN	条件二	THEN	要显示的值2或语句2
WHEN	条件三	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;

3、分组函数

功能：用作统计使用，又称之为聚合函数或者统计函数或组函数

特点1、参数支持的类型
1.1、sum，avg 一般用于处理数值类型
1.2、max，min，count 可以处理任何类型
特点2、是否忽略 null 值
2.1、所有的分组函数都 忽略null值
特点3、可以和distinct搭配使用实现去重运算
特点4、一般使用 count* 作为统计函数
特点5、和分组函数一同查询的字段，要求是 group by 后的字段

3.1、sum 求和

SELECT
		SUM(salary)
FROM
		employees;

3.2、avg 平均值

SELECT
		AVG(salary)
FROM
		employees;

3.3、max 最大值

SELECT
		MAX(salary)
FROM
		employees;

3.4、min 最小值

SELECT
		MIN(salary)
FROM
		employees;

3.5、count 计算个数

SELECT
		COUNT(salary)
FROM
		employees;

3.6、与 distinct 搭配 //去重之后再求和

SELECT 
		SUM(DISTINCT salary),
		salary
FROM
		employees;

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

3.7、COUNT 函数的详细介绍
1、

SELECT 
		COUNT(salary)
FROM
		employees;

2、使用 * 统计行数

SELECT
		COUNT(*)
FROM
		employees;

3、、、、加入一个常量值，统计行数

SELECT 
		COUNT(1)
FROM
		employees;

案例1：查询员工工资的最大值，最小值，平均值，总和；

SELECT
		MAX(salary) mx_sal,MIN(salary) mi_sal,ROUND(AVG(salary),2) ag_sal,SUM(salary) sm_sal
FROM
		employees;

案例2、查询员工表中的最大入职时间和最小入职时间相差的天数(DIFFERENCE)

SELECT
		DATEDIFF(MAX(hiredate),MIN(hiredate))	AS	DIFFERENCE
FROM
		employees;

案例3、查询部门编号为90的员工个数

SELECT 
		COUNT(*)
FROM
		employees
WHERE
		department_id = 90;