USE myemployees;
#进阶四:排序查询()
/*
语法: select 查询列表 from 表 order by 排序列表【asc|desc】asc升序desc降序,可不写,默认升序
/
#案例:查询员工信息,要求工资从高到底排序
SELECT * FROM employeesORDER BYsalary ASC;
#案例:查询部门编号》=990 的员工信息,按入职时间的先后顺序排序
SELECT
*
FROM
employees
WHERE department_id >= 90
ORDER BYhiredate ASC ;
#案例:按年薪的高低显示员工的信息和年薪【按表达式排序】
SELECT
last_name,
salary * 12 * (1+ IFNULL(commission_pct, 0)) 年薪
FROM
employees
ORDER BYsalary * 12 * (1+ IFNULL(commission_pct, 0)) DESC ;
#案例:按年薪的高低显示员工的信息和年薪【按别名排序】
SELECT
last_name,
salary * 12 * (1+ IFNULL(commission_pct, 0)) 年薪
FROM
employees
ORDER BY年薪 DESC ;
#案例:按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT
LENGTH(last_name) 字节长度,
last_name,
salary
FROM
employees
ORDER BYLENGTH(last_name) DESC ;
#案例:查询员工信息,先按工资排序,再按员工编号排序
SELECT
*
FROM
employees
ORDER BYsalary ASC,
employee_id DESC ;
#:常见函数
/
语法: selec 函数名(实参列表)【from 列表】
分类:
单行函数(字符函数,数学函数,日期函数,其他函数,流程控制函数】
分组函数
/
#一:字符函数
#length
SELECT LENGTH(‘hahaha’);
SELECT LENGTH(‘郭金韧hahaha’);
#2.concat 拼接字符串
SELECT CONCAT(‘郭金韧’,‘hahaha’);
#3.upper lower
SELECT UPPER(‘hahaha’);
#substr = substring
SELECT SUBSTR(‘李莫愁爱上了陆展元’,7) output;
SELECT SUBSTR(‘李莫愁爱上了陆展元’,2,5);
#5.instr 返回子串第一次出现的索引,如果不存在返回0
SELECT INSTR(‘杨不悔爱上了殷六侠’,‘殷六侠’) output;
#6.trim 去前后空格或指定字符/字符串
SELECT TRIM(’ trim ‘);
SELECT TRIM(‘a’FROM’aaaabbbbbbaa’);
#7.Lpad(左填充) Rpad(右填充)
SELECT LPAD(‘郭金韧’,10,’’);
#8.replace 替换
SELECT REPLACE(‘aaabbbcccddd’,‘a’,‘x’);
#二:数学函数
#round四舍五入
SELECT ROUND(-1.65);
SELECT ROUND(1.567,2);
#ceil 向上取整(返回>=参数的整数)
SELECT CEIL(1.02);
#floor向下取整 #truncate截断
SELECT TRUNCATE(1.666999,2);
#mod取余
SELECT MOD(10,-3);
SELECT 10/-3;
#日期函数
#1.now返回当前日期时间
SELECT NOW();
#2.curdate 返回当前系统日期,不包含时间
SELECT CURDATE();
#3.curtime 返回当前时间,不包含系统日期
#可以获取指定的部分:年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年 ;
SELECT YEAR(‘1999-1-1’) 年;
#STR_TO_DATE将字符转换为日期
SELECT STR_TO_DATE(‘2000-05-06’,’%Y-%c-%d’);
#查询入职日期为199922-4-3的员工信息
SELECT * FROM employees WHERE hiredate = ‘1992-4-3’;
#date_format:将日期转化成字符
SELECT DATE_FORMAT(NOW(),’%m月%d日%Y年’);
#其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
#五、流程控制函数
#1.if函数:if else的效果
SELECT IF(10>5,‘大’,‘小’);
#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倍
其他部门,显示为原工资
*/
SELECT
salary 原始工资,
department_id,
CASE
department_id
WHEN 30
THEN salary * 1.1
WHEN 40
THEN salary * 1.2
ELSE salary
END AS 新工资
FROM
employees ;
#3.case函数的使用2:类似于java的多重if
/*
java中:
if(条件1)
语句1;
else if(条件2)
语句2;
else
语句n;
mysql中:
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
…
else 要显示的值n或语句n
end;
/
/
二、分组函数
功能:用作统计使用
分类: sum 求和 avg 平均值 max 最大值 min 最小值 count 计算个数
*/
#1. 简单使用
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;
SELECT SUM(salary) 和,AVG(salary) 平均值 FROM employees;
#2. 参数支持哪些类型?
/*
sum avg一般只支持数值型 max min count 一般处理任何类型
/
#3.是否支持null
/
以上分组函数都忽略null值
*/
#4.和distinct搭配
/*
都可以和distinct搭配使用
*/
SELECT SUM(DISTINCT salary) 去重,SUM(salary) 非去重 FROM employees;
#5.count函数的详细介绍
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;
/*
作用相同,myisam存储引擎下count()效率最高
innodb存储引擎下,count()count(1)的效率差不多,但比count(字段)更高一些
/
#6.和分组函数一同查询的字段有限制
/
和分组函数一同查询的字段要求是group by 后的字段,
例如这样的查询无意义:
SELECT AVG(salary) 工资平均值, salary 所有人的工资分别为工资 FROM employees;
*/
SELECT AVG(salary) 工资平均值, salary 所有人的工资分别为工资 FROM employees;