海星吧的MySQL笔记：基础部分。
最近两天看MySQL做的笔记，视频连接在这里。
有点长，做个记录放这里，有需要的兄弟可以拿去看看。
有些建表的当时没存代码，可能得看看视频来操作了。
# DDL 数据库操作
## 查询
```
查询所有数据库：SHOW DATABASES;
查询当前数据库：SELECT DATABASE();
```
## 创建
```
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]
```
## 删除
```
DROP DATABASE [IF EXISTS] 数据库名;
```
## 使用
```
USE 数据库名;
```
# DDL 表操作
## 查询
```
查询当前数据库所有表：SHOW TABLES;
查询表结构：DESC 表名;
查询指定表的建表语句：SHOW CREATE TABLE 表名;
```
## 创建
```
创建：
CREATE TABLE 表名(
字段名 字段类型 [COMMENT 注释],
...
) [COMMENT 表注释];
create table emp(
id int comment '自增 key',
workno varchar(10) comment '工号',
name varchar(10) comment '员工名',
gender char(1) comment '员工性别',
age tinyint unsigned comment 'how old',
idcard char(18) comment '身份证号',
entrydate date comment '入职时间'
) comment '员工表';
```
## 修改
```
修改表名
ALTER TABLE 表名 RENAME TO 新表名;
添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释][约束];
修改字段
ALTER TABLE 表名 MODIFY 字段名 新的数据类型(长度);
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度)[COMMENT 注释][约束];
```
## 删除
```
删除表
DROP TABLE [IF EXISTS] 表名；
删除指定表，并重新创建该表，相当于清空表内容。
TRUNCATE TABLE 表名；
删除字段
ALTER TABLE 表名 DROP 字段名；
```
# DML
```
表操作：
insert 添加数据
insert into 表名(字段1，字段2， ...) values (值1，值2，...) /(值1，值2，...),(值1，值2，...)...
insert into 表名 values (值1，值2，...) /(值1，值2，...),(值1，值2，...)...
test -> INSERT INTO userTable(username, password) VALUES ('哈哈哈','abcdefg')；
update 修改数据
update 表名 set 字段名1 = 值1,字段名2 = 值2,... [where 更改条件] // 注意，没有更改条件将会更改整张表的数据！！！
test -> UPDATE userTable SET password = '123456' where username = '哈哈哈'；
delete 删除数据
delete form 表名 [where 删除条件] // 注意，没有删除条件将会删除整张表的数据！！！
test -> UPDATE FORM userTable where username = '哈哈哈'；
```
# DQL
## 查询关键字
```
select | 字段列表
form | 表名列表
where | 条件列表
group by | 分组字段列表
having | 分组后条件列表
order by | 排序字段列表
limit | 分页列表
-----------------------------
上列关键字的执行顺序
form -> where -> group by -> having -> select -> order by -> limit;
可以对表进行别名，但是在 select 执行前的where 和 group by ... having这段时间里无法使用表的别名。
错误的使用：
sclect e.age as eage, e.gender as egen from emp as e where eage > 20 group by egen order by eage asc limit 5;
正确的使用：
sclect e.age as eage, e.gender as egen from emp as e where e.age > 20 group by e.gen order by eage asc limit 5;
```
## 基础查询
```
查询多个字段
select a,b,c... from 表名;
select * from 表名
设置别名
select 字段1 [as 别名1],... from 表名；
去除重复记录
select distinct 字段列表 from 表名
```
## 条件查询
```
SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件运算符：
> | 大于
>= | 大于
< | 小于
<= | 小于等于
= | 等于
<> 或 != | 不等于
BETWEEN...AND... | 在某个范围之内（含最小，最大值）
IN(...) | 在 in 之后的列表中的值，多选一
LIKE | 模糊匹配，（ _ 匹配单个字符，% 匹配多个字符）
IS NULL | 是NULL
逻辑运算符：
AND | && | 与，多个条件同时匹配则匹配
OR | || | 或，多个条件任意一个匹配则匹配
NOT | ! | 非，不是后续条件下则匹配
-- SELECT DISTINCT workaddress FROM emp;
-- SELECT username,idcard FROM emp WHERE workaddress = '北京';
-- SELECT * FROM emp WHERE age = 88;
-- SELECT * FROM emp WHERE age < 20;
-- SELECT * FROM emp WHERE idcard IS NULL;
-- SELECT * FROM emp WHERE idcard LIKE '%';
-- SELECT * FROM emp WHERE idcard IS NOT NULL;
-- SELECT * FROM emp WHERE age != 88;
-- SELECT * FROM emp WHERE age >= 15 AND age <= 20;
-- SELECT * FROM emp WHERE age >= 15 && age <= 20;
-- SELECT * FROM emp WHERE age BETWEEN 15 AND 20;
-- SELECT * FROM emp WHERE gender = '女' AND age < 25;
-- SELECT * FROM emp WHERE age = 18 OR age = 20 OR age = 40;
-- SELECT * FROM emp WHERE age IN(18,20,40);
-- SELECT * FROM emp WHERE username LIKE '__';
-- SELECT * FROM emp WHERE idcard LIKE '%X';
-- SELECT * FROM emp WHERE idcard LIKE '_________________x';
-- SELECT * FROM emp WHERE idcard LIKE BINARY '%X'; -- BINARY 区分大小写
```
## 聚合函数
```
函数 | 功能
COUNT | 统计数量
MAX | 最大值
MIN | 最小值
AVG | 平均值
SUM | 求和
----------------------------
所有聚合函数中的字段，当值为NULL时，不参与计算。
-- SELECT COUNT(id) FROM emp;
-- SELECT MIN(age) FROM emp;
-- SELECT MAX(age) FROM emp;
-- SELECT AVG(age) FROM emp;
-- SELECT SUM(age) FROM emp;
-- SELECT SUM(age) FROM emp WHERE workaddress = '西安';
```
## 分组查询
```
语法：
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
WHERE 和 HAVING 区别
执行时机不同，WHERE是分组前过滤，不满足条件不参与分组，HAVING分组后过滤；
判断条件不同，WHERE不能对聚合函数进行判断，HAVING可以判断聚合函数；
先执行 where 再执行 having
分组后，查询的字段一般为聚合函数和分组字段，查询其他字段无意义。
-------------------------------------------------------------------
-- SELECT gender,COUNT(*) FROM emp GROUP BY gender;
-- SELECT gender,AVG(age) FROM emp GROUP BY gender;
-- SELECT workaddress FROM emp WHERE age < 45 GROUP BY workaddress HAVING COUNT(id) >= 3;
-- SELECT workaddress,COUNT(*) as worker FROM emp WHERE age < 45 GROUP BY workaddress HAVING worker >= 3;
```
## 排序查询
```
语法：
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;
排序方式：
ASC 升序（默认）
DESC 降序
第二个排序只会在第一个排序的值相同时执行，之后的以此类推。
------------------------------------------------------------------
-- SELECT * FROM emp ORDER BY age ASC;
-- SELECT * FROM emp ORDER BY entrydate DESC;
-- SELECT * FROM emp ORDER BY age ASC , entrydate DESC;
```
## 分页查询
```
语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查询记录数;
起始索引从0开始，起始索引 = (查询页码 - 1) * 每页显示记录数
分页查询是数据库的方言，不同数据库有不同的实现，MySQL使用LIMIT关键字。
如果查询的是第一页数据，起始索引可省略，简写为 LIMIT 10。
LIMIT 要放在查询语句最后；
------------------------------------------------------------------
-- SELECT * FROM emp LIMIT 10; # 0 - 10
-- SELECT * FROM emp LIMIT 4,8; # 5 - 12
-- SELECT * FROM emp LIMIT 10,10; # 11 - 20
```
## 查询练习
```
# '查询年龄为 20,21,22,23 岁的女性员工信息'
SELECT * FROM emp WHERE gender = '女' AND age IN(20,21,22,23);
# '查询性别 男，且年龄 20 - 40岁(含)以内的姓名为三个字的员工。
SELECT * FROM emp WHERE gender = '男' AND age BETWEEN 20 AND 40 LIKE '___';
# 统计员工表中，年龄小于六十岁，男性和女性员工人数。
SELECT gender,COUNT(*) FROM emp WHERE age < 60 GROUP BY gender;
# 查询所有年龄小于等于三十五岁员工的姓名和年龄，并对查询结果按照年龄升序，入职时间降序。
SELECT username, age FROM emp WHERE age <= 35 ORDER BY age ASC, entrydate DESC;
# 查询性别为男性，且年龄 20 - 40 岁(含)以内的前五个员工信息，并对结果按照年龄升序，入职时间降序。
SELECT * FROM emp WHERE gender = '男' AND age BETWEEN 20 AND 40 ORDER BY age ASC , entrydate DESC LIMIT 5;
```
# DCL
```
作用： 管理数据库用户，数据库的访问权限。
```
## 管理用户
```
查询用户:
USE mysql; # 查询 数据库名为 mysql 的数据库
SELECT * FROM user; # 查询 mysql 数据库 下的 user 表;
创建用户:
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
实例：
CREATE USER 'itcast'@'localhost' IDENTIFIED BY '123456';
修改用户密码:
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '密码'; # mysql_native_password 是 mysql 数据库的一种加密方式
ALTER USER 'itcast'@'localhost' IDENTIFIED WITH mysql_native_password BY 'ABCDEFG';
删除用户:
DROP USER '用户名'@'主机名';
```
## 权限控制
```
常用权限:
权限 | 权限说明
ALL,ALL PRIVILEGES | 所有权限
SELECT | 查询数据
INSERT | 插入数据
UPDATE | 修改数据
DELETE | 删除数据
ALTER | 修改表
DROP | 删除数据库/表/视图
CREATE | 创建数据库/表
----------------------------------------------------
查询权限:
SHOW GRANTS FOR '用户名'@'主机名'
实例:
SHOW GRANTS FOR 'nb'@'localhost'
授予权限:
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'
实例:
# 授予 所有数据库下所有表的所有权限给 本地的 nb 用户
GRANT ALL ON *.* 'nb'@'localhost';
# 授予 dbName下所有表的所有权限给 本地的 nb 用户
GRANT ALL ON dbName.* 'nb'@'localhost';
GRANT ALL PRIVILEGES ON dbName.* 'nb'@'localhost';
# 授予 dbName下所有表的 查询,修改,插入 给 本地的 nb 用户
GRANT SELECT,UPDATE,INSERT ON dbName.* 'nb'@'localhost';
# 授予 dbName下 user 表 的 查询,修改,插入 给 本地的 nb 用户
GRANT SELECT,UPDATE,INSERT ON dbName.user 'nb'@'localhost';
撤销权限：
REVOKE 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'
实例:
# 撤销 本地的 nb 用户 的 所有数据库下所有表的所有权限
REVOKE ALL ON *.* 'nb'@'localhost'
# 其余授予一样，只是将 GRANT 替换为 REVOKE
```
# 函数
```
函数是指一段可以直接被另一端程序调用的程序或代码
```
## 字符串函数
```
函数 | 功能
CONCAT(N1,N2,N3) | 字符串拼接，将参数内的数据拼接成一个字符串
LOWER(STR) | 将字符串转小写
UPPER(STR) | 将字符串转大写
LPAD(STR,N,PAD) | 左填充，str字符串，n填充长度，pad填充用的字符串
RPAD(STR,N,PAD) | 右填充，str字符串，n填充长度，pad填充用的字符串
TRIM(STR) | 去掉字符串头尾空格
SUBSTRING(STR,START,LEN)| 返回str从start位置起的len个长度的字符串
----------------------------------------------------
练习：
# 企业员工工号统一为五位数，不足五位往前补0,1填充为 00001
UPDATE emp SET workno = LPAD(workno, 5, '0');
```
## 数值函数
```
函数 | 功能
CEIL(X) | 向上取整
FLOOR(X) | 向下取整
MOD(X,Y) | 返回 x / y 的模，
RAND() | 返回 0 ~ 1之内的随机数
ROUND(X,Y) | 求 X 的四舍五入值，保留 Y 位小数
----------------------------------------------------
练习：
# 生成一个六位数的随机验证码
SELECT LPAD( ROUND(RAND() * 1000000, 6) , 6, '0')
SELECT RPAD( ROUND(RAND() * 1000000, 6) , 6, '0')
SELECT SUBSTRING(RAND(),3,6)
```
## 日期函数
```
函数 | 功能
CURDATE() | 返回当前日期
CURTIME() | 返回当前时间
NOW() | 返回当前日期和时间
YEAR(DATE) | 返回指定date的年份
MONTH(DATE) | 返回指定date的月份
DAY(DATE) | 返回指定date的日份
DATE_ADD(DATE,INTERVAL EXPR TYPE) | 返回一个日期/时间值加上一个时间间隔 expr 后的时间值
DATEDIFF(DATE_1,DATE_2) | 返回起始时间 date_1 和结束时间 date_2 之间的天数
------------------------------------------------------
SELECT CURDATE();
SELECT CURTIME();
SELECT NEW();
SELECT YEAR(NEW());
SELECT MONTH(NEW());
SELECT DAY(NEW());
SELECT DATE_ADD(NEW(), INTERVAL 70 DAY); # 当前时间七十天之后
SELECT DATEDIFF('2023-03-18','2023-03-14'); # 4， 相差 4 天
SELECT DATEDIFF('2023-04-11','2023-03-14'); # 28， 相差 28 天
SELECT DATEDIFF('2023-02-11','2023-03-14'); # -31， 相差 -31 天
------------------------------------------------------
练习：
# 查询所有员工的入职天数，并根据入职天数倒序排序
SELECT username as '员工名', DATEDIFF(CURDATE(), entrydate) as '天数' FROM emp ORDER BY entrydate DESC;
```
## 流程函数
```
函数 | 功能
IF(VALUE,T,F) | 如果 VALUE 为 true 返回 T，否则返回 F
IFNULL(VALUE_1,VALUE_2) | 如果 VALUE_1 不为 NULL 则返回 VALUE_1，否则 返回 VALUE_2
CASE WHEN [VAL_1] THEN [RES_1] ... ELSE [DEFAULT] END | 如果 VAL_1 为 true，返回 RES_1,否则返回 DEFAULT 默认值
CASE [EXPR] WHEN [VAL_1] THEN [RES_1] ... ELSE [DEFAULT] END | 如果 EXPR 等于 VAL_1，返回 RES_1,否则返回 DEFAULT 默认值
--------------------------------------------------------------------------------------------------------------------------------------
SELECT IFNULL('ok','default') # 'ok'
SELECT IFNULL('','default') # ''
SELECT IFNULL(NULL,'default') # 'default'
------------------------------------------------------
练习：
# 查询 emp 员工的姓名和工作地址，北京/上海为一线城市，其他的为二线城市。
SELECT username, (CASE workaddress WHEN '北京' THEN '一线城市' WHEN '上海' THEN '一线城市' ELSE '二线城市' END) AS '工作地址' FROM emp;
# 建表先：
CREATE TABLE score ( id INT, sname VARCHAR ( 20 ), math INT, english INT, chinese INT ) COMMIT '学员成绩列表';
INSERT INTO score ( id, sname, math, english, chinese )
VALUES
( 1, 'Tom', 67, 88, 95 ),
( 2, 'Rose', 23, 66, 90 ),
( 3, 'Jack', 56, 98, 76 );
# 统计 班级学员成绩：
>= 85 , 优秀
>= 60 , 及格
< 60 , 不及格
SELECT
sname,
( CASE WHEN math >= 85 THEN '优秀' WHEN math >= 60 THEN '及格' ELSE '不及格' END ) AS '数学',
( CASE WHEN english >= 85 THEN '优秀' WHEN english >= 60 THEN '及格' ELSE '不及格' END ) AS '英语',
( CASE WHEN chinese >= 85 THEN '优秀' WHEN chinese >= 60 THEN '及格' ELSE '不及格' END ) AS '语文'
FROM
score;
```
# 约束
```
概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
目的：保证数据库中数据的正确性、有效性和完整性。
注意：约束是作用于表中字段上的，可在创建表和修改表的时候添加约束。
分类：
关键字 | 约束 | 描述
NOT NULL | 非空约束 | 限制字段数据不为NULL
UNIQUE | 唯一约束 | 保证字段数据唯一，不重复
PRIMARY KEY | 主键约束 | 一行数据的唯一标识，非空且唯一
DEFAULT | 默认约束 | 保存数据且未指定值时，该字段采用默认值
CHECK | 检查约束(v8.0.16)| 保证字段值满足某一条件
FOREIGN KEY | 外键约束 | 用来让两张表的数据之间建立链接，保证数据的一致性和完整性
-----------------------------------------------------------------------------------------------------
CREATE TABLE test1 (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '主键自增id',
name varchar(10) NOT NULL UNIQUE COMMENT '姓名',
age int CHECK (age > 0 && age <= 120) COMMENT '年龄',
status char(1) DEFAULT '1' COMMENT '状态',
gender char(1) COMMENT '性别'
) COMMENT '创建约束表';
INSERT INTO user(name,age,status,gender) VALUES
('Tom',19,'1','男'),
('Jim',25,'0','男');
错误：
违反非空约束 ->
INSERT INTO user(name,age,status,gender) VALUES (NULL,25,'0','男');
违反唯一约束 ->
INSERT INTO user(name,age,status,gender) VALUES ('Tom',25,'0','男');
违反检查约束 ->
INSERT INTO user(name,age,status,gender) VALUES ('jojo',-1,'0','男');
INSERT INTO user(name,age,status,gender) VALUES ('jojo',121,'0','男');
```
## 外键约束
```
概念：
外键是用来将两张表的数据建立链接，从而保证数据的一致性和完整性。
添加外键：
建表时：
create table 表名(
[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
)
自主创建：
ALTER TABLE 表名 CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);
外键约束：
ALTER TABLE 表名 CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名) ON 具体操作 删/更行为;
删除外键：
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称
删除 更新行为
行为 | 说明
NO ACTION | 在父表中删除更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与RESTRICT一致）
RESTRICT | 在父表中删除更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与NO ACTION一致）
CASCADE | 在父表中删除更新对应记录时，首先检查该记录是否有对应外键，如果有,则也删除/更新外键在子表中的记录。
SET NULL | 在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为NULL。前提是该外键允许为NULL。
SET DEFAULT | 父表有变更时，子表将外键列设置成一个默认的值（InnoDB不支持）
建表：
create table dept (
id int auto_increment comment 'id' primary key,
name varchar(50) not null comment '部门名称'
) comment '部门表'
CREATE TABLE emp2 (
id INT auto_increment COMMENT 'id' PRIMARY KEY,
name VARCHAR ( 50 ) NOT NULL COMMENT '姓名',
age INT COMMENT '年龄',
job VARCHAR ( 20 ),
salary INT COMMENT '薪资',
entrydate DATE COMMENT '入职时间',
managerid INT COMMENT '领导id',
dept_id INT COMMENT '部门id，外键'
) COMMENT '员工表';
INSERT INTO emp2( name, age, job, salary, entrydate, managerid, dept_id) VALUES
('金庸',66,'总裁',20000,'2000-01-01',null,5),
('张无忌',20,'项目经理',12500,'2005-12-05',1,1),
('杨逍',33,'开发',8400,'2000-11-03',2,1),
('韦一笑',48,'开发',11000,'2002-02-05',2,1),
('常遇春',43,'开发',10500,'2004-09-07',3,1),
('小昭',19,'程序员鼓励师',6600,'2004-10-12',2,1);
ALTER TABLE emp2 ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY ( dept_id ) REFERENCES dept ( id );
ALTER TABLE emp2 DROP FOREIGN KEY fk_emp_dept_id;
ALTER TABLE 表名 CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名) ON UPDATE SET NULL ON DELETE CASCADE;
```
# 多表查询
```
多表关系
多表查询概述
内连接
外连接
自链接
子链接
多表查询案例
```
## 多表关系
```
一对一
多对多
一对多/多对一
一对多/多对一
在多的一方建立外键，指向一的一方的主键
比如一个员工只能归属一个部门，而一个部门可以有多个员工，则部门是一的一方，员工是多的一方。
这样就在多的一方建立外键，外键指向一的一方的唯一主键键值。
多对多
一个学生可以选择多门课程，而课程也可以供多个学生选择
多对多的情况下，需要建立第三张中间表，中间表建立两个外键，分别关联学生和课程的主键。
一对一
```
```
多对多
建表
CREATE TABLE students ( id INT auto_increment PRIMARY KEY COMMENT '主键', s_name VARCHAR ( 10 ) COMMENT '姓名', c_no VARCHAR ( 10 ) COMMENT '学号' ) COMMENT '学生表';
INSERT INTO students(s_name, c_no)
VALUES
( '黛绮丝', '2000100101' ),
( '张辽', '2000101002' ),
( '刘备', '2000101003' ),
( '张飞', '2000101004' )
;
CREATE TABLE course ( id INT auto_increment PRIMARY KEY COMMENT '主键', c_name VARCHAR ( 10 ) COMMENT '课程名称' ) COMMENT '课程表';
INSERT INTO course ( c_name )
VALUES
( 'java' ),
( 'php' ),
( 'mysql' ),
( 'hadoop' );
CREATE TABLE student_course (
id INT auto_increment PRIMARY KEY COMMENT '主键',
s_id INT NOT NULL,
c_id INT NOT NULL,
CONSTRAINT fk_c_id FOREIGN KEY ( s_id ) REFERENCES course ( id ),
CONSTRAINT fk_s_id FOREIGN KEY ( c_id ) REFERENCES students ( id )
) COMMENT '学生课程中间表';
INSERT INTO student_course ( s_id, c_id )
VALUES
( 1, 1 ),
( 1, 2 ),
( 1, 3 ),
( 2, 2 ),
( 2, 3 ),
( 3, 4 );
```
```
一对一
用户与用户详情，有时候不需要查询这么多的数据，只需要查询用户的基本信息就够了
这时候将用户的详情信息存入第二张用户ID关联的详情表中形成一对一的关系。
在任意一方加入外键，关联另外一方的主键，并设置外键为唯一(UNIQUE)
CREATE TABLE tb_user (
id INT auto_increment PRIMARY KEY COMMENT '主键',
NAME VARCHAR ( 10 ) UNIQUE COMMENT '用户名',
age INT COMMENT '用户年龄',
gender CHAR ( 1 ) COMMENT '性别',
phone CHAR ( 13 ) NOT NULL COMMENT '用户手机号'
) COMMENT '创建用户表';
CREATE TABLE tb_user_edu (
id INT auto_increment PRIMARY KEY COMMENT '主键',
degree VARCHAR ( 10 ) COMMENT '学历',
major VARCHAR ( 10 ) COMMENT '专业',
primaryschool VARCHAR ( 20 ) COMMENT '小学',
middleschool VARCHAR ( 20 ) COMMENT '中学',
university VARCHAR ( 20 ) COMMENT '大学',
tb_uesr_id INT UNIQUE NOT NULL COMMENT '外键',
CONSTRAINT fk_tb_uesr FOREIGN KEY ( tb_uesr_id ) REFERENCES tb_user ( id )
) COMMENT '创建用户内容表';
INSERT INTO tb_user ( name, age, gender, phone )
VALUES
( '黄渤', 45, '1', '18800001111' ),
( '冰冰', 35, '2', '18800003333' ),
( '码云', 55, '1', '18800008888' ),
( '李彦宏', 50, '1', '18800005555' );
```
## 多表查询概述
```
多表查询实例：
SELECT * FROM emp2 e, dept d WHERE e.dept_id = d.id;
内连接：表与表之间交际部分的数据
外连接：
左外连接：左表所有数据以及两表交际部分
右外连接：右表所有数据以及两表交际部分
自连接：当前表与自身的链接查询，自链接必须使用表别名
子查询：
```
## 内连接
```
隐式内连接：
select 字段列表 from 表1,表2 where 条件;
显示内连接：
select 字段列表 from 表1 [INNER] JOIN 表2 ON 连接条件;
# 查询每一个员工的姓名，关联部门名称：
SELECT e.name, d.name FROM emp2 e, dept d WHERE e.dept_id = d.id;
SELECT e.name, d.name FROM emp2 e INNER JOIN dept d on e.dept_id = d.id;
```
## 外连接
```
左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 连接条件;
右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 连接条件;
# 查询emp2表的所有数据和对应部门信息
SELECT e.* , d.name FROM emp2 e LEFT OUTER JOIN dept d on e.dept_id = d.id;
# 查询dept表的所有数据和对应员工信息
SELECT e.*, d.name FROM emp2 e RIGHT OUTER JOIN dept d on e.dept_id = d.id;
```
## 自连接
```
自链接可以是内连接查询，也可以是外连接查询。
语法：
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件;
先添加点数据：
INSERT INTO emp2 ( name, age, job, salary, entrydate, managerid, dept_id ) VALUES
( '灭绝师太', 60, '财务总监', 8500, '2002-09-12', 1, 3 ),
( '周芷若', 19, '会计', 4800, '2006-06-12', 7, 3 ),
( '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3 ),
( '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2 ),
( '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2 ),
( '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2 ),
( '方东白', 19, '职员', 5500, '2009-02-12', 10 , 2 ),
( '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4 ),
( '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4 ),
( '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4 ),
( '陈友谅', 42, NULL, 2000, '2011-10-12', 1, NULL );
查询员工以及所属领导的名字
SELECT e1.name as '员工', e2.name as '领导' FROM emp2 e1 JOIN emp2 e2 ON e1.managerid = e2.id;
SELECT e1.name as '员工', e2.name as '领导' FROM emp2 e1 JOIN emp2 e2 ON e1.managerid = e2.id ORDER BY e1.managerid asc;
查询员工以及所属领导的名字，没有领导也查出来
SELECT e1.name as '员工', e2.name as '领导' FROM emp2 e1 LEFT JOIN emp2 e2 ON e1.managerid = e2.id;
SELECT e1.name as '员工', e2.name as '领导' FROM emp2 e1 LEFT JOIN emp2 e2 ON e1.managerid = e2.id ORDER BY e1.managerid asc;
```
```
内连接的联合查询
关键字 UNION ， UNION ALL
将多次查询的结果合并，形成新的查询结果集。
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;
UNION 不包括重复的，
UNION ALL 重复匹配的也会显示。
联合查询多张表的列数必须保持一致！字段类型必须保持一致！
# 将薪资低于5000的员工和年龄大于50的员工全部查询出来。
-- 鹿杖客显示一次，因为两次都匹配了，但是UNION不包含重复的。
SELECT * FROM emp2 WHERE salary < 5000
UNION
SELECT * FROM emp2 WHERE age > 50 ;
-- 鹿杖客显示两次，因为两次都匹配了
SELECT * FROM emp2 WHERE salary < 5000
UNION ALL
SELECT * FROM emp2 WHERE age > 50 ;
```
## 子连接
```
SQL语句嵌套SELECT语句，又称嵌套查询
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2)
子连接外部的语句可以是 INSERT/UPDATE/DELETE/SELECT中的任何一个
子连接结果：
标量子查询 -- 子查询的结果为单个值
列子查询 -- 子查询结果为一列
行子查询 -- 子查询结果为一行
表子查询 -- 子查询结果为多行多列
根据子查询位置，分为：WHERE之后、FORM之后、SELECT之后
```
### 标量子查询
```
常用操作符： = ,!= | <>, >, >=, <, <=;
返回结果为单个值（数字，字符串，日期...）
# 查询销售部所有员工信息
-- SELECT id FROM dept WHERE name = '销售部'; -- 4
-- SELECT * FROM emp2 WHERE dept_id = 4;
SELECT * FROM emp2 WHERE dept_id = (SELECT id FROM dept WHERE name = '销售部');
# 查询 员工 东方白 入职之后的员工信息
-- SELECT entrydate FROM emp2 WHERE name = '方东白'; -- 2009-02-12
-- SELECT * FROM emp2 WHERE entrydate > '2009-02-12';
SELECT * FROM emp2 WHERE entrydate > (SELECT entrydate FROM emp2 WHERE name = '方东白');
```
### 列子查询
```
常用操作符：
IN(arg1,arg2...) 指定值内,
NOT IN(arg1,arg2...) 不在指定值内,
ANY 满足一条即可,
SOME 与ANY等同,
ALL 所有值必须满足;
返回结果为一列，可以是多行。
# 查询 销售部 和 市场部所有员工信息
SELECT * FROM emp2 WHERE dept_id IN(SELECT id FROM dept WHERE name IN('销售部' , '市场部'))
# 查询 薪资比财务部所有人都高的员工信息
-- SELECT id FROM dept WHERE name = '财务部';
-- SELECT salary FROM emp2 WHERE dept_id = (SELECT id FROM dept WHERE name = '财务部');
SELECT * FROM emp2 WHERE salary > ALL(SELECT salary FROM emp2 WHERE dept_id = (SELECT id FROM dept WHERE name = '财务部'));
# 查询比研发部任意一人薪资高的员工信息
-- SELECT id FROM dept WHERE name = '研发部';
-- SELECT salary FROM emp2 WHERE dept_id = (SELECT id FROM dept WHERE name = '研发部');
SELECT * FROM emp2 WHERE salary > SOME(SELECT salary FROM emp2 WHERE dept_id = (SELECT id FROM dept WHERE name = '研发部'))
```
### 行子查询
```
常用操作符：
=
!=|<>
IN
NOT IN
返回结果为一行，可以是多列。
# 查询与 张无忌 薪资和领导相同的员工信息
-- SELECT salary, managerid FROM emp2 WHERE name = '张无忌'; -- 12500 , 1
-- SELECT * FROM emp2 WHERE salary = 12500 AND managerid = 1;
-- SELECT * FROM emp2 WHERE (salary, managerid) = (12500,1);
SELECT * FROM emp2 WHERE (salary, managerid) = (SELECT salary, managerid FROM emp2 WHERE name = '张无忌');
```
### 表子查询
```
常用操作符： IN
# 查询与鹿杖客，宋远桥的职位和薪资相同的员工信息
-- SELECT job, salary FROM emp2 WHERE name IN('鹿杖客','宋远桥');
SELECT * FROM emp2 WHERE (job, salary) IN(SELECT job, salary FROM emp2 WHERE name IN('鹿杖客','宋远桥'))
# 查询入职日期是 '2006-01-01' 之后的员工信息，以及部门信息。
-- SELECT * FROM emp2 WHERE entrydate > '2006-01-01';
SELECT e.*, d.* FROM
(SELECT * FROM emp2 WHERE entrydate > '2006-01-01') e
LEFT JOIN dept d ON e.dept_id = d.id;
```
## 多表查询案例
```
根据需求完成sql语句的编写
查询员工的姓名，年龄，职位，部门信息。
查询年龄小于30岁的员工姓名，年龄，职位，部门信息。
查询拥有员工的部门ID、部门名称。
查询所有年龄大于四十岁的员工，以及其归属的部门名称，如果员工没有分配部门也要展示。
查询所有员工的工资等级
查询研发部所有员工的信息以及工资等级
查询研发部员工的平均工资
查询工资比灭绝高的员工工资
查询比平均工资高的员工信息
查询低于本部门平均工资的员工信息
查询所有部门信息并统计部门的员工人数
查询所有学生的选课情况，展示出学生名称、学号、课程名称
```
```
创建薪资等级表
CREATE TABLE salgrade (
grade int,
losal int,
hisal int
) COMMENT '薪资等级表';
INSERT INTO salgrade values
(1,0, 3000),
(2,3001, 5000),
(3,5001, 8000),
(4,8001, 10000),
(5,10001, 15000),
(6,15001, 20000),
(7,20001, 25000),
(8,25001, 30000);
```
```
# 查询员工的姓名，年龄，职位，部门信息。
SELECT e.name,e.age,e.job,d.name FROM emp2 e,dept d WHERE e.dept_id = d.id;
# 查询年龄小于30岁的员工姓名，年龄，职位，部门信息。
SELECT e.name,e.age,e.job,d.name FROM emp2 e,dept d WHERE e.age < 30 AND e.dept_id = d.id ;
# 查询拥有员工的部门ID、部门名称。
SELECT DISTINCT d.id ,d.name FROM emp2 e,dept d WHERE e.dept_id = d.id;
# 查询所有年龄大于四十岁的员工，以及其归属的部门名称，如果员工没有分配部门也要展示。
SELECT e.*, d.name FROM emp2 e LEFT JOIN dept d ON e.dept_id = d.id WHERE e.age > 40;
# 查询所有员工的工资等级
SELECT e.name, s.grade FROM emp2 e LEFT JOIN salgrade s ON e.salary BETWEEN s.losal AND s.hisal;
# 查询研发部所有员工的信息以及工资等级
SELECT e.*, s.grade FROM
(SELECT * FROM emp2 e WHERE e.dept_id = (SELECT d.id FROM dept d WHERE d.name = '研发部') )
e LEFT JOIN salgrade s ON e.salary BETWEEN s.losal AND s.hisal;
# 查询研发部员工的平均工资
SELECT AVG(e.salary) FROM emp2 e WHERE e.dept_id = (SELECT d.id FROM dept d WHERE d.name = '研发部')
# 查询工资比灭绝高的员工工资
SELECT * FROM emp2 e WHERE e.salary > (SELECT salary FROM emp2 WHERE name = '灭绝师太');
# 查询比平均工资高的员工信息
SELECT e.* FROM emp2 e WHERE e.salary > (SELECT AVG(e.salary) FROM emp2 e)
# 查询低于本部门平均工资的员工信息
SELECT * FROM emp2 e2 WHERE e2.salary <(SELECT avg(e1.salary) FROM emp2 e1 WHERE e1.dept_id = e2.dept_id);
# 查询所有部门信息并统计部门的员工人数
SELECT d.id, d.name, (SELECT COUNT(*) FROM emp2 WHERE dept_id = d.id) '人数' FROM dept d;
# 查询所有学生的选课情况，展示出学生名称、学号、课程名称
SELECT s.s_name, s.c_no, c.c_name FROM students s, student_course sc, course c WHERE s.id = sc.s_id and sc.c_id = c.id;
```
# 事务
```
事务简介
事务是一个操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求
即这些操作要么同时成功，要么同时失败。
事务操作
事务四大特性
并发事务问题
事务隔离级别
```
## 事务操作
```
查看/设置事务提交方式
SELECT @@autocommit;
SET @@autocommit = 0;
开启事务：
START TRANSACTION / BEGIN
提交事务
COMMIT;
回滚事务
ROLLBACK;
```
```
CREATE TABLE `account` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`money` int unsigned DEFAULT '0',
PRIMARY KEY (`id`)
) COMMENT '创建用户金额表';
INSERT INTO `account`(name, money) values
('张三', 2000),
('李四', 2000);
SET @@autocommit = 0;-- 设置手动提交
SELECT @@autocommit;--查询提交方式
START TRANSACTION;--开启事务
UPDATE account SET money = money + 1000 WHERE `name` = '张三';
error异常； -- 主动发生异常
UPDATE account SET money = money - 1000 WHERE `name` = '李四';
COMMIT;--确认数据之后可以手动提交
ROLLBACK;--发生错误之后手动回滚
SELECT * FROM account;-- 查询数据
```
## 事务特性
```
原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
一致性：事务完成时，必须使所有的数据都保持一致状态
隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
```
## 并发事务问题
```
问题 | 描述
脏读 | 一个事务读到另外一个事务还没有提交的数据
不可重复读 | 一个事务先后读取同一条记录，但两次读取的数据不同
幻读 | 一个事务按照条件查询数据时，没有对应的数据行，但是插入数据时，又发现这行数据已经存在
```
## 事务隔离级别
```
隔离级别 | 脏读 | 不可重复读 | 幻读
READ UNCOMMITTED | 1 | 1 | 1
READ COMMITTED | 0 | 1 | 1
REPEATABLE READ | 0 | 0 | 1
SERIALIZABLE | 0 | 0 | 0
1表示会出现这样的问题
0表示不会出现这样的问题
！：SERIALIZABLE会阻塞事务，在多个事务操作同一个数据时，会先执行第一个获取数据的，
等第一个事务提交完，然后才是下一个。安全最高但是性能最差
查看事务隔离级别
SELECT @@transaction_isolation;
设置隔离级别
SESSION 当前会话有效
GLOBAL 所有会话有效
语法：
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
实例
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE
```弱鸡程序员年底还在加班
收藏的用户（0）
X
正在加载信息~
