第二天MySQL
2020/5/5
一、 DQL条件查询
SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。语法：（执行顺序已标出）
SELECT
查询列表 3
FORM
表名 1
WHERE
筛选条件; 2
分类：
1. 按条件表达式筛选
条件运算符：> < = != <> >= <=
#查找员工月薪>12000的员工信息
SELECT
*
FROM
employees
WHERE
salary>12000;
2. 按逻辑表达式筛选
逻辑运算符：and or not
#查找员工编号在90至120的员工名、薪水以及员工编号
SELECT
last_name,
salary,
employee_id
FROM
employees
WHERE
employee_id >= 90 AND employee_id <= 120;
3. 模糊查询
关键字：
3.1 like
特点：一般和通配符搭配使用，可以判断字符型或数值型。
通配符
% 可带表任意多个字符（包括空字符）
_ 只能代表任意一个字符
案例一：查找员工名中含有字符a的员工名和部门编号
运行结果:
案例二：查找员工名中第二个字符为'a'，第五个字符为'o'的员工名、部门编号和薪水
运行结果;
注意：若想查找的字符本身就为特殊的字符如_或%，则需要通过对该字符转义来实现。共有俩种方式
方式一：使用’\’来转义
方式二: 使用ESCAPE关键字配合
如：#查找员工名第二个字符为'_'的员工名、月薪以及奖金率
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE
last_name LIKE '_\_%';
-----------------------------------------------------
SELECT
last_name,salary,
commission_pct
FROM
employees
WHERE
last_name
LIKE
'_$_%' ESCAPE '$'; (相当于把‘$‘附上转义的功能)
3.2 between and
优点：
1. 能够提高语句的简洁度
2. 包含临界值
3. 两个临界值不要调换顺序
案例： 查找员工部门编号在90到120之间的员工名、部门编号、工作编号
运行结果：
以上两种方法返回的查询结果一样，两种方式的表达意义完全等价！
3.3 in
特点：
- 可以提高语句简洁度
- in 列表的值类型必须一致或兼容（可以隐式的转换）
- 不支持使用通配符表示的的模糊字符
#查找员工部门编号是'AD_VP'、'FI_MGR'、'FI_ACCOUNT'的员工名、薪水、部门编号以及部门编号
SELECT
last_name,
salary,
job_id,
department_id
FROM
employees
WHERE
job_id = 'AD_VP' OR job_id = 'FI_MGR' OR job_id = 'FI_ACCOUNT';
---------------------------------------------------------------------
SELECT
last_name,
salary,
job_id,
department_id
FROM
employees
WHERE
job_id IN ('AD_VP','FI_MGR','FI_ACCOUNT');
上面这两种方式完全等价
3.4 is null 、is not null
特点：=或<>不能用于判断null值，而is null 或is not null可以判断null值（is只能用于判断null，与null搭配，不可以判断数值如12000）
注意：安全等于<=>即可以判断NULL值，也可以判断其他普通的数值，可读性较差。
#查找奖金率为null的员工名、月薪以及奖金率
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE
commission_pct IS NULL;（可读性很好！）
--------------------------------------------------------
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE
commission_pct <=> NULL;（可读性较差）
以上两种方式等价，返回查询结果一致！！！
#查找奖金率不为null的员工名、月薪、奖金率以及年薪
SELECT
last_name,
salary,
commission_pct,
salary * (1 + IFNULL(commission_pct, 0)) * 12 AS 年薪
FROM
employees
WHERE
commission_pct IS NOT NULL;
注意，有个IFNULL()函数，原型为IFNULL(expression, alt_value)，如果第一个参数的值expression为null，则替换它并返回第二个参数alt_value。
二、 DOL排序查询
特点：
1. ASC代表升序，DESC代表降序（若不写，则默认为升序）
2. order by 字句中可以支持单个字段，多个字段，表达式、函数、别名
3. order by 字句一般放在查询语句的最后面，LIMIT子句除外
语法：（执行顺序已标出）
SELECT
查询列表 3
FROM
表名 1
[WHERE
筛选条件] 2
ORDER BY
排序列表 ASC|DESC; 4
案例一: #查询员工信息，要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;
案例二：#查询部门编号>=90的员工信息，按入职时间的先后顺序排序
SELECT
*
FROM
employees
WHERE
department_id >= 90
ORDER BY
hiredate DESC;
案例三：#按年薪的高低显示员工的信息和年薪【按表达式排序】
SELECT
*,salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪
FROM
employees
ORDER BY
salary * 12 * (1 + IFNULL(commission_pct, 0)) DESC;
案例四：#按年薪的高低显示员工的信息和年薪【按别名排序】
SELECT
*, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪
FROM
employees
ORDER BY
年薪 DESC;
案例五：#按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT
last_name,
salary,
LENGTH(last_name) 姓名长度
FROM
employees
ORDER BY
LENGTH(last_name) ASC; （LENGTH（str）函数->返回str的字节长度）
案例六：#查询员工信息，要求先按工资排序，再按员工编号排序【按多个字段排序】
SELECT
*
FROM
employees
ORDER BY
salary DESC, employee_id DESC;
三、 常见函数介绍
调用方法：SELECT 函数名（实参列表）[from 表名];
分类：
1.单行函数
如：concat、length、ifnull、isnull等
2.分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数
单行函数分类：
1． 字符函数
# length函数->返回当前参数的字节数(若字符集使用的是gbk则一个汉字占2个字节，若是utf8则为3个字节)
SELECT LENGTH(‘jane’); ->4
SELECT LENGTH(last_name) AS 姓名长度 FROM employees;
#concat函数->拼接字符
SELECT CONCAT(last_name,’_’,first_name) AS 姓名 FROM employees;
#upper、lower函数（类似于python中的str.lower()与str.upper()函数,UPPER(str)->将str转换为大写字母返回，LOWER(str)-> 将str转换为小写字母返回）
SELECT UPPER(last_name) FROM employees;
SELECT CONCAT(UPPER(last_name),’_’,LOWER(first_name)) AS 姓名 FROM employees;
#substr,substring(截断字符串)
截取从指定索引处后面的所有字符
SELECT SUBSTR('abcefghijk',2) AS output;
截取从指定索引处指定”字符“长度的字符
SELECT SUBSTR('abcdefghijk',2,3) AS output;
SELECT SUBSTR('呀呀呀',2,1) AS output;
#instr(返回子串第一次出现的索引，如果找不到则返回0)
SELECT INSTR(‘我去上学啦’,’上学啦’);->3
#trim(去”前后”的制定字符，若不指定则默认为去掉空格。注意，字符串中间的内容不会去除)
SELECT TRIM(‘ lalalla ’) AS output; ->’lalalla’
SELECT TRIM( ‘a’ FROM ‘aaaaaaaabbbbbabbbaaabbbbaaaaa’);-> bbbbbabbbaaabbbb
#lpad(在左边用指定字符进行填充并且返回对应长度的字符结果)
SELECT LPAD(‘aaabbb’,10,’c’);->’ccccaaabbb’
SELECT LPAD(‘aaabbb’,4,’c’);->’aaab’
#rpad(在右边用指定字符进行填充并且返回对应长度的字符结果)
SELECT RPAD(‘aaabbb’,10,’c’);->’aaabbbcccc’
SELECT RPAD(‘aaabbb’,4,’c’);->’aaab’
#replace(用指定字符值替换指定字符值)
REPLACE(str,from_str,to_str)（str->作用的字符串，from_str->要被替换的字符串，to_str->用来替换的字符串）
SELECT REPLACE(’哈哈哈，我不知道’,’我不知道’,’我知道’)；->’哈哈哈，我知道’
2． 数字函数
#round(对传入的参数四舍五入并返回)
SELECT ROUND(8.9);->9
SELECT ROUND(-8.9);->-9
SELECT ROUND(1.567,2);->1.57(2代表保留小数点后2位)
#ceil(向上取整，返回>=该参数的最小整数)
SELECT CEIL(1.002);->2
SELECT CEIL(1.00);->1
SELECT CEIL(-1.002);->-1
#floor(向下取整，返回<=该参数的最小整数)
SELECT CEIL(1.002);->1
SELECT CEIL(1.00);->1
SELECT CEIL(-1.002);->-2
#truncate 截断
TRUNCATE(X,D)->(X->要被处理的参数，D->截断后的小树点位数)
SELECT TRUNCATE(1.69999,1);->1.6
#mod（取余）
MOD(a,b): a-a/b*b(返回的取余结果的正负值取决于参数a)
SELECT MOD(10,3);->1
SELECT MOD(10,-3);->1
SELECT MOD(-10,-3);->-1
SELECT MOD(-10,3);->-1
3． 日期函数
#now (返回当前系统日期+时间)
SELECT NOW();->2020-05-05 20:20:15
#curdate(返回当前系统日期,不包含时间)
SELECT CURDATE();->2020-05-05
#curtime(返回当前系统时间,不包含日期)
SELECT CURTIME();->20:21:53
#year、month、day、hour、minute、second(用于获取指定的部分，年、月、日、时、分、秒)
SELECT YEAR(NOW());->2020
SELECT MONTH(NOW());->5
SELECT DAY(NOW());->5
SELECT HOUR(NOW());->20
SELECT MINUTE(NOW());->25
SELECT SECOND(NOW());->4
#str_to_date（将日期格式的字符转换成指定格式的日期）
相对应的格式符如下:
SELECT STR_TO_DATE(‘9-13 1999’,’%c-%d %Y’);-> 1999-09-13
#date_format（将日期转换成字符）
SELECT DATE_FORMAT(‘2018/6/6’,’%Y年%m月%d日’);-> 2018年06月06日
4． 其他函数
#version(返回当前mysql的版本)
SELECT VERSION();->5.7.25-log
#database（返回当前打开的数据库）
SELECT DATABASE();->myemployees
#user（返回当前的用户）
SELECT USER();->root@localhost
5． 流程控制函数
#if函数
IF(expr1,expr2,expr3)->(expr1:条件或逻辑表达式，若真则返回expr2,假则返回expr3)
SELECT IF(5>2,’大’,’小’);->’大’
#case函数
使用一：（switch case的效果）
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
…
else常量n then 要显示的值n或语句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;
使用二：（类似于多重if）
case
when 条件1 then 要显示的值1语句1;
when 条件2 then 要显示的值2或语句2;
…
else条件n then 要显示的值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;
