-- 进阶3: 排序查询
/*
语法:
select 查询列表
from 表
【where 筛选条件】
order by
排序列表
asc升序默认可省略/desc降序
*/
-- 案例1: 查询员工信息,按照工资高到低
SELECT *
FROM
employees
ORDER BY
salary
DESC;
-- 案例2:查询部门编号>=90的员工信息,按照入职时间先后排序
SELECT *
FROM
employees
WHERE
department_id >= 90
ORDER BY
hiredate;
-- 案例3: 按年新的高低显示员工的信息和年薪【按表达式或者别名排序】
SELECT *, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪
FROM
employees
ORDER BY 年薪
DESC;
-- 案例5:按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT
LENGTH(last_name) 姓氏长度, last_name, salary
FROM
employees
ORDER BY 姓氏长度 DESC;
-- 案例6: 查询员工信息,要求先按工资再按员工编号排序【按多个字段排序】
SELECT
*
FROM
employees
ORDER BY
salary ASC,
employee_id DESC;
-- 测试1:查询员工的姓名和部门号和年薪,按年薪降序,按姓名升序
SELECT last_name, department_id, salary * 12 * (1 + IFNULL(commission_pct, 0)) 年薪
FROM employees
ORDER BY 年薪 DESC, last_name ASC;
-- 测试2: 选择工资不在8000和17000之间的员工姓名和工资,按照工资降序
SELECT last_name, salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;
-- 查询邮箱中包含e 的员工信息,并先按邮箱的字节数降序,再按部门号升序
SELECT *, LENGTH(email)
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC, department_id ASC;
-- 进阶4: 常见函数
/*
调用函数:select 函数名(实参列表) 【from 表】;
分类: 单行函数如(做处理用):concat、length、ifnull
分组函数(做统计用)又称统计函数、聚合函数、组函数
*/
-- ******* --
-- 单行函数 --
-- ******* --
-- 一、字符函数
-- length获取参数的字节个数
SELECT LENGTH('john');
SELECT LENGTH("张三丰"); -- utf-8中一个汉字三个字节
-- concat拼接字符串
SELECT CONCAT(last_name, '_', first_name) FROM employees;
-- upper、 lower转换大小写
SELECT UPPER('john');
SELECT LOWER("jJ");
-- 实例: 姓答谢名小写
SELECT CONCAT(UPPER(last_name), LOWER(first_name)) 姓名
FROM employees;
-- substr、substring截取子串 智能识别中英文
-- SUBSTR('SUMESTRING', index) 截取从指定索引延申到最后的子串,索引是从1开始的
-- SUBSTR('SUMESTRING', index, length) 截取从指定索引延申指定长度的子串
-- 案例:姓氏中首字符大写,其他字符小写然后用_拼接, 显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name, 1, 1)), '_', LOWER(SUBSTR(last_name, 2))) 姓名
FROM employees;
-- instr 和kmp一致的目的
SELECT INSTR('杨不悔爱上了殷六侠', '殷六侠') AS out_put
FROM employees;
-- trim 去前后空格
SELECT TRIM(' 张翠兰 ')AS out_put;
SELECT TRIM("a" FROM'aaaaaa张脆aaaaa面aaaaaaaaaaaaaaaaaaaaaaaaaaa') AS out_out;
-- lpad 智能识别汉字和英语,用指定字符左填充至指定长度
SELECT LPAD('殷素素', 10, '*') AS out_put;
-- rpad 同理右填充
-- replace 全部替换
SELECT REPLACE('张周芷若无忌爱上了周芷若', '周芷若', '赵敏') AS out_put;
-- 二、数学函数
-- round 绝对值四舍五入
-- round(数值) 数值四舍五入到整数
-- round(数值, 位数) 数值四舍五入到小数点后指定位数
SELECT ROUND(1.65); -- 2
SELECT ROUND(-1.5); -- -2
SELECT ROUND(-1.555, 2); -- -1.56
-- ceil 向上取整
SELECT CEIL(-1.9999999999999);
-- floor 向下取整
SELECT FLOOR(-1.0000000003);
-- truncate 截断
SELECT TRUNCATE(1.65, 1);
SELECT TRUNCATE(-1.233, 0);
-- mod 取模
SELECT MOD(-10, -3);
-- 三、日期函数
-- now 返回当前系统日期+时间
SELECT NOW();
-- curdate 返回日期
SELECT CURDATE();
-- curtime 返回时间
SELECT CURTIME();
-- 可以获取指定的部分如年、月、日、小时、分、秒
SELECT YEAR(NOW());
SELECT DAY(CURDATE());
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTHNAME(NOW()) AS 月; -- 注意这个as 可以想到数组和函数本来一个作用都是通过一个值映射到另一个值,as这里就是针对于键的
-- str_to_date 将字符转换成日期
-- %Y四位年份、%y二位年份、%m有前导零月份、%c无前导零月份、%d日、%H时24、%h时12、%i分、%s秒。
SELECT STR_TO_DATE('1998-3-2', '%Y-%c-%d') AS out_put;
-- 查询入职日期为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');
-- date_format将日期转换成字符
SELECT DATE_FORMAT(NOW(), '%y年%m月%d日') AS out_put;
-- 案例:查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name, DATE_FORMAT(hiredate, '%m月/%d日 %y年') 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;
-- 四、其他函数
SELECT VERSION(); -- 查看版本
SELECT DATABASE(); -- 查看数据库
SELECT USER(); -- 查看用户
-- 五、流程控制函数
-- 1. if函数:三目运算符的效果
-- select if(arg1, arg2, arg3);
-- 相当于 arg1 ? arg2 : arg3;
SELECT IF(10 < 5, '大', "小");
SELECT last_name, commission_pct, IF(commission_pct IS NULL, '没奖金,呵呵', '有奖金,嘻嘻') 备注
FROM employees;
-- 2.case
-- case 函数用法一:switch case 的效果
/*
case 要判断的字段或表达式
when 常量1 then 语句1; 或要返回的值1
when 常量2 then 语句2; 或要返回的值2
……
else 默认情况语句; 或要返回的值
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;
-- case 函数用法二:类似于多重if
/*
case
when 条件1 then 语句1;或者值1
when 条件2 then 语句2;或者值2
……
else 语句;或者值
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;
-- 习题
-- 1.显示系统时间(日期加时间)
SELECT NOW();
-- 2. 查询员工号,姓名,工资,以及以上工资提高百分之20后的结果
SELECT employee_id, last_name, salary, salary * 1.2 'new salary'
FROM employees;
-- 3.将员工的姓名按首字母排序,并写出姓名的长度(length)
SELECT LENGTH(last_name) 长度, SUBSTR(last_name, 1, 1) 首字符, last_name
FROM employees
ORDER BY 首字符;
-- 4. 做一个查询,产生如下显示:
-- Dream Salary
-- <last_name> earns <salary> monthly but wants <salary * 3>
SELECT CONCAT(last_name, ' earns', salary, ' monthly but wants ', salary * 3) AS 'Dream Salary'
FROM employees;
-- 5. 使用case-when按照如下条件:
/*
job grade
AD_PRES A
ST_MAN B
IT_PROG C
*/
SELECT job_id AS job,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
END AS grade
FROM employees;
-- ******* --
-- 分组函数 --
-- ******* --
-- SUM, AVG, MIN, MAX, COUNT
-- 特点:
-- 1. 参数支持哪些类型sum 和ave支持数值类型max, min, count支持任何类型
-- 2.是否忽略NULL值 忽略了
SELECT SUM(commission_pct), AVG(commission_pct) FROM employees;
-- 3. 可以和distinct搭配
SELECT SUM(DISTINCT salary), SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary), COUNT(salary) FROM employees;
-- count 函数详细介绍
SELECT COUNT(salary) FROM employees; -- 统计非空salary的个数
SELECT COUNT(*) FROM employees; -- 统计行数
SELECT COUNT(1) FROM employees; -- 统计行数
-- 上述统计行数的效率问题:MYISAM存储引擎下count((*)效率最高,INNODB存储引擎下(*)和(1)差不多
-- 用count(*)统计行数就对了
-- 4. 和分组函数一同查询的字段要求是group by 字段
-- 例题:
-- 1. 查询公司员工刚子的最大值,最小值,平均值,总和
SELECT MAX(salary) mx_sal, MIN(salary) mi_sal, AVG(salary) ag_sal, SUM(salary) sm_sal
FROM employees;
-- 2. 查询员工表中的最大入职时间和最小入职时间的相差天数
SELECT DATEDIFF(MAX(hiredate), MIN(hiredate)) difference -- 前减后
FROM employees;
-- 3. 查询部门编号为90的员工个数
SELECT COUNT(*) 个数
FROM employees
WHERE department_id = 90;
mysql 李玉婷bilibili配套听课笔记(二)排序查询、常见函数
最新推荐文章于 2024-07-21 19:06:05 发布