MySQL复习笔记【上】

对MySQL语法的总结，适合【复习使用】
参考视频B站尚硅谷MySQL教程，同时也很大程度上是这位小伙伴笔记的浓缩，笔记链接https://blog.csdn.net/m0_46153949/article/details/107116168
该部分的内容主要为MySQL中的查询语言，其他部分见MySQL复习笔记【下】

DQL语言

基础查询

特点：
1.查询列表可以是：表中的字段、常量值、表达式、函数;
2.查询的结果可以是一个虚拟表格.

SELECT  last_name,salary, FROM employees;
SELECT * FROM employees;  #选择所有
SELECT 100;
SELECT VERSION();
SELECT 100%8;
#起别名两种方式： AS 和 空格
SELECT last_name AS 姓 FROM employees;
SELECT last_name 姓 FROM employees；

+号的作用：

1.只能作为运算符；
2.两个操作数都为数值型,做加法运算
3.select ‘123+90’;其中一方为字符型,试图将字符型数值转换为数值型
4.select ‘john’+90; 如果转换失败,则将字符型数值转换成0
5.select null+0; 只要其中一方为null,则结果肯定为null

常见函数

concat()
拼接字符 select concat(字符1，字符2，字符3,…);
ifnull()
判断某字段或表达式是否为null，如果为null 返回指定的值，否则返回原本的值

SELECT IFNULL(commission_pct,0) FROM employees  #若为null，则为0

isnull()
判断某字段或表达式是否为null，如果是，则返回1，否则返回0

条件查询

按条件表达式筛选

条件运算符:> < = != <> >= <= <=>安全等于

	SELECT * FROM employees WHERE salary>12000

按逻辑表达式筛选

逻辑运算符:&& || |
and or not
逻辑运算符:&& || |

SELECT last_name,salary,commission_pct FROM employees WHERE salary>=10000 AND salary<=20000;

模糊查询

like:一般搭配通配符使用，可以判断字符型或数值型
通配符：%任意多个字符，_任意单个字符
like、between and、in、is null
between使用注意：
1.提高语句简洁度
2.包含临界值
3.两个临界值不能调换顺序

in 判断某字段的值是否属于in列表中的某一项
特点:
1.使用in提高语句简洁度
2.in列表的值类型必须一致或兼容

4.is null
1.=或<>不能用于判断null值,安全等于<=>可以
2.is null 或 is not null 可以判断null值

#查询员工名中第三个字符为b，第五个字符为a的员工名和工资
SELECT last_name,salary FROM employees WHERE last_name LIKE '__b_a%

#查询员工编号在100到120之间的员工信息
SELECT * FROM employees WHERE employee_id>=100 AND employee_id<=120;
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;

#查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT last_name,job_id FROM employees WHERE job_id='IT_PROG' OR job_id='AD_PRES' OR job_id='AD_VP';
SELECT last_name,job_id FROM employees WHERE job_id IN('IT_PROG','AD_PRES','AD_VP');

排序查询

语法：select 查询列表 from 表 【where 筛选条件】 order by
特点：
1.asc代表的是升序，desc代表降序，不写默认为升序
2.order by子句中可以支持单个字段、多个字段、表达式、函数、别名
3.order by子句一般是放在查询语句的最后面,limit子句除外

#查询员工信息,要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY salary;

常用函数

单行函数

字符函数、数学函数、日期函数、其他函数、流程控制函数

字符函数

#length 获取参数值的字节值  注意编码不同汉字的长度不同
SELECT LENGTH('汉字subei');

concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees

#upper:变大写、lower：变小写
SELECT UPPER('ton');
SELECT LOWER('ton');

substr、substring
#注意:索引从1开始
#截取从指定所有处后面的所以字符
SELECT SUBSTR('吴刚伐桂在天上',4) out_put;
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('吴刚伐桂在天上',1,2) out_put

#trim:去前后空格 和指定于是乃
SELECT LENGTH(TRIM('	霍山	')) AS out_put;
SELECT TRIM('+' FROM '++++李刚+++刘邦+++') AS out_put;

#lpad:用指定的字符实现左填充指【指定长度】，而非【填充长度】
SELECT LPAD('梅林',8,'+') AS out_put;

#rpad:用指定的字符实现右填充指定长度
SELECT RPAD('梅林',5,'&') AS out_put;

#replace:替换
SELECT REPLACE('莉莉伊万斯的青梅竹马是詹姆','詹姆','斯内普') AS out_put;

数学函数

#1.round:四舍五入
SELECT ROUND(1.45);
SELECT ROUND(1.567,2);

#2.ceil:向上取整,返回>=该参数的最小整数
SELECT CEIL(1.005);
SELECT CEIL(-1.002);

#3.floor:向下取整,返回<=该参数的最大整数
SELECT FLOOR(-9.99);

#4.truncate:截断
SELECT TRUNCATE(1.65,1);

#5.mod:取余
SELECT MOD(10,3);

#6.rand:获取随机数，返回0-1之间的小数
SELECT RAND();

日期函数

#1.now:返回当前系统时间+日期
SELECT NOW();

#2.year:返回年
SELECT YEAR(NOW());
SELECT YEAR(hiredate) 年 FROM employees;

#3.month:返回月
#MONTHNAME:以英文形式返回月
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());

#4.day:返回日
#DATEDIFF:返回两个日期相差的天数
SELECT DAY(NOW());
SELECT DATEDIFF('2020/06/30','2020/06/21');

#5.str_to_date:将字符通过指定格式转换成日期
SELECT STR_TO_DATE('2020-5-13','%Y-%c-%d') AS out_put;

#6.date_format:将日期转换成字符
SELECT DATE_FORMAT('2020/6/6','%Y年%m月%d日') AS out_put;
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') AS out_put;

#7.curdate:返回当前日期
SELECT CURDATE();

#8.curtime:返回当前时间
SELECT CURTIME();

其它函数

#version 当前数据库服务器的版本
SELECT VERSION();

#database 当前打开的数据库
SELECT DATABASE();

#user当前用户
SELECT USER();

#password('字符')：返回该字符的密码形式
SELECT PASSWORD('a');

#md5('字符'):返回该字符的md5加密形式
SELECT MD5('a');

流程控制函数

#1.if函数: if else效果
SELECT IF(10<5,'大','小');

#2.case函数
#使用一:switch case 的效果
#case 要判断的变量或表达式
#when 常量1 then 要显示的值1或语句1
#when 常量2 then 要显示的值2或语句2
#...
#else 要显示的值n或语句n
#end

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 else
#case 
#when 条件1 then 要显示的值1或语句1
#when 条件2 then 要显示的值2或语句2
#...
#else 要显示的值n或语句n
#end

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 计算个数
特点:
1.sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型
2.以上分组函数都忽略null
3.都可以搭配distinct使用，实现去重的统计
select sum(distinct 字段) from 表;
4.count函数
count(字段)：统计该字段非空值的个数
count(*):统计结果集的行数也可以用count(1)
5.和分组函数一同查询的字段，要求是group by后出现的字段
6.Dsitinct 表示为去重

SELECT SUM(salary) FROM employees;
#distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;

分组查询

语法

select 分组函数,分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】

特点:

1.分组查询中的筛选条件分为两类

		使用关键字	筛选的表	位置

分组前筛选 where 原始表 group by的前面
分组后筛选 having 分组后的结果 group by的后面
1.分组函数做条件肯定是放在having子句中
2.能用分组前筛选的，就优先考虑使用分组前筛选

			使用关键字	筛选的表	              位置
分组前筛选	where		原始表	         	group by的前面
分组后筛选	having		分组后的结果	  group by的后面
1.分组函数做条件肯定是放在having子句中
2.能用分组前筛选的，就优先考虑使用分组前筛选

2.group by子句支持单个字段分组，多个字段分组(多个字段之间用逗号隔开没有顺序要求),表达式或函数(使用较少)
3.也可以添加排序(排序放在整个分组查询的最后)

#查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

#查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary),department_id FROM employees
WHERE email LIKE '%a%' GROUP BY department_id;

#按多个字段查询
#添加排序
#案例:查询每个部门每个工种的员工的平均工资,按平均工资的高低查询
SELECT AVG(salary),department_id,job_id
FROM employees GROUP BY department_id,job_id
ORDER BY AVG(salary) DESC;

连接查询

含义和分类：

含义:又称多表查询,当查询的数据来自多个表时,就会用到连接查询

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行

按年代分类：
	sql92标准:仅仅支持内连接
	sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
	
按功能分类：
	内连接：
	等值连接
	非等值连接
	自连接
外连接：
	左外连接
	右外连接
	全外连接
		
交叉连接

等值连接

1.多表等值连接的结果为多表的交集部分
2.n表连接，至少需要n-1个连接条件
3.多表的顺序没有要求
4.一般需要为表起别名
5.可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

#查询员工名和对应的部门名
SELECT last_name,department_name 
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;

#为表起别名
/*
1.提高语句的简洁度
2.区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/
#查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM employees  e,jobs j
WHERE e.`job_id`=j.`job_id`;

#可以加筛选
#案例：查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;

#可以实现三表连接
#案例：查询员工名、部门名和所在的城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND city LIKE 's%'
ORDER BY department_name DESC

#非等值连接
#案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='A';

SQL99标准

语法

select 查询列表
from 表1 别名 【连接类型】
join 表2 别名 
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

分类

内连接（★）：inner
外连接
	左外(★):left 【outer】
	右外(★)：right 【outer】
	全外：full【outer】
交叉连接：cross

特点：
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

#等值连接
#查询员工名、部门名
SELECT last_name,department_name FROM departments d
INNER JOIN  employees e
ON e.`department_id` = d.`department_id`

#非等值连接
#查询员工的工资级别
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#自连接
#查询员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`= m.`employee_id`;

外连接

应用场景：用于查询一个表中有，另一个表没有的记录

特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

#左外连接
SELECT b.*,bo.* FROM boys bo
LEFT OUTER JOIN beauty b
ON b.`boyfriend_id` = bo.`id`
WHERE b.`id` IS NULL;

#右外
SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

#全外
USE girls;
SELECT b.*,bo.* FROM beauty b
FULL OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.id;

#交叉连接 笛卡尔乘积形式
SELECT b.*,bo.* FROM beauty b
CROSS JOIN boys bo;

子查询

出现在其他语句中的select语句,称为子查询或内查询外部的查询语句，称为主查询或外查询

1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多列多行）

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用

< >= <= = <>

列子查询，一般搭配着多行操作符使用
in、any/some、all
④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

where/having后

#1.标量子查询★
#谁的工资比 Abel 高?
#①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel';

#②查询员工的信息，满足 salary>①结果
SELECT *
FROM employees
WHERE salary>(
	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'	
);

#2.列子查询（多行子查询）★
#案例1：返回location_id是1400或1700的部门中的所有员工姓名

#①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700);

#②查询员工姓名，要求部门号是①列表中的某一个

SELECT last_name
FROM employees
WHERE department_id  <>ALL(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);

SELECT后

#select后面仅仅支持标量子查询
#案例：查询每个部门的员工个数

SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.`department_id`
 ) 个数
 FROM departments d;

FROM后

将子查询结果充当一张表，要求必须起别名

#案例：查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees GROUP BY department_id;

SELECT * FROM job_grades;

#②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal

SELECT  ag_dep.*,g.`grade_level`
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

EXISTS

表示存在，返回1或者0，一般可以用IN来替换

SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);

分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求。
语法：

	select 查询列表
	from 表
	【join type join 表2
	on 连接条件
	where 筛选条件
	group by 分组字段
	having 分组后的筛选
	order by 排序的字段】
	limit 【offset,】size;
注意
	offset要显示条目的起始索引（起始索引从0开始）
	size 要显示的条目个数

联合查询

含义：union (联合、合并)：将多条查询语句的结果合并成一个结果
意义

1、将一条比较复杂的查询语句拆分成多条语句
2、适用于查询多个表的时候，查询的列基本是一致。
特点

1、要求多条查询语句的查询列数是一致的！
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重，如果使用union all 可以包含重复项

语法

查询语句1
union 【all】
查询语句2
union 【all】
...

#引入的案例：查询部门编号>90或邮箱包含a的员工信息

SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

SELECT * FROM employees  WHERE email LIKE '%a%'
UNION
SELECT * FROM employees  WHERE department_id>90;