Mysql学习二 查询函数

排序查询

/*
语法：

select 查询列表
from 表名
where 筛选条件
order by 排序列表 【升序（asc,默认）|降序（desc）】

order by后可以支持单个字段、多个字段、函数、表达式
*/

#查询员工信息，工资从高到低

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)) 年薪
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 字节长度 DESC;						

#查询员工信息，要求先按工资排序，再按员工编号排序

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

常见函数

/*
调用：select 函数名(实参列表) from 表;
分类：
单行函数：处理使用,such as concat、length、ifnull等
分组函数：做统计使用，又称为统计函数、聚合函数、组函数
*/

字符函数

#length

SELECT LENGTH('哈hhh');

#查看使用的字符集

SHOW VARIABLES LIKE '%char%';

#concat 拼接字符串

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

#upper、lower

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

#substr、substring 截取字符串
#索引从1开始！

SELECT SUBSTR('hello',3);

#截取从指定索引处指定字符长度的字符

SELECT SUBSTR('hello',3,2);

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',first_name) 姓名,salary FROM employees;

#instr 返回子串第一次在主串的索引，找不到返回0

SELECT INSTR('hello','lol'); 

#trim
#去除首尾空格

SELECT TRIM('  dd  ');

#去除首尾指定字符

SELECT TRIM('ab'FROM'abababddababaab');

#lpad 用指定字符实现左填充指定长度

SELECT LPAD('hello',10,'*');

#rpad 用指定字符实现右填充指定长度

SELECT RPAD('hello',10,'*');

#replace

SELECT REPLACE('abababddababaab','ab','');

数学函数

#round

SELECT ROUND(-1.55);
SELECT ROUND(1.567,2);

#ceil 向上取整,返回>=该参数的最小整数

SELECT CEIL(-1.56);

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

SELECT FLOOR(-9.99);

#truncate 截断

SELECT TRUNCATE(1.899,1);

#mod取余

SELECT MOD(-10,3);

日期函数

#now 返回当前系统日期和时间

SELECT NOW();

#curdate 返回当前系统日期，不包含时间

SELECT CURDATE();

#curtime 返回当前时间，不包含系统日期

SELECT CURTIME();

#获取年、月、日、小时、分钟、秒

SELECT YEAR(NOW());
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());

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

SELECT STR_TO_DATE('1998-4-3','%Y-%m-%d');

#date_format将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日');

其他函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

流程控制

#if函数：if else的效果

SELECT IF(10<5,'大','小');

#case函数 使用一：switch case的效果
/*
语法：

case	要判断的字段或表达式
when	常量1 then 要显示的语句1；
when 	常量2 then 要显示的语句2；
…
else	要显示的语句n；
end

*/

/*
部门号=30，显示工资为1.1倍
部门号=40，显示工资为1.2倍
部门号=50，显示工资为1.3倍
其他部门，显示工资为原工资
*/

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
ORDER BY department_id ASC; 	

#case函数使用二：类似于多重if
/*

case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
…
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计算个数
参数支持哪些类型 sum、avg一般用于处理数值型
其他的所有类型都支持
是否忽略null 都忽略null
和分组函数一同查询的字段要求是group by后的字段
*/

SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

#count函数的详细解释
#统计表中的总行数

SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;

#日期差计算

SELECT DATEDIFF(NOW(),'1998-05-01');

分组查询

/*
语法：

select 分组函数，列（要求出现在group by的后面）
from 表
【where 筛选条件】
group by 分组的列表
	Having 分组后筛选的条件
【order by 子句】

注意：查询列表比较特殊，要求是分组函数和group by后出现的字段

*/
#查询每个部门的平均工资

SELECT AVG(salary),department_id FROM employees GROUP BY department_id;

#查询每个工种的最高工资

SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;

#查询每个部门员工人数
#第一步，查询每个部门的员工人数

SELECT COUNT(*),department_id FROM employees GROUP BY department_id;

#第二步，根据第一步的结果，查询哪个部门的员工人数>2

SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;

#查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

#查找领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及最低工资

SELECT 	*,MIN(salary) '最低工资'
FROM 	employees
WHERE	manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;

#按表达式或函数分组
#按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些

SELECT 	COUNT(*) AS 个数,LENGTH(last_name)
FROM 	employees
GROUP BY LENGTH(last_name)
HAVING 	COUNT(*)>5	
ORDER BY COUNT(*) ASC;

#按多个字段分组
#查询每个部门的每个工种的员工的平均工资

SELECT 	AVG(salary),department_id,job_id
FROM 	employees
GROUP BY department_id,job_id;