记录

MySQL-04

  • 2022-07-18
  • 作者 我爱小小蝶
  • ~15.10K 字
  1. 1. ANY
    1. 1.1. 含义
    2. 1.2. 语法
    3. 1.3. 案例
  2. 2. ALL
    1. 2.1. 含义
    2. 2.2. 语法
    3. 2.3. 案例
  3. 3. not in
    1. 3.1. 注意点
    2. 3.2. 方案
    3. 3.3. 案例
  4. 4. 表之间的关系
    1. 4.1. 种类
    2. 4.2. 案例
  5. 5. 嵌套子查询
    1. 5.1. 位置
    2. 5.2. 作用
    3. 5.3. 执行过程
  6. 6. 相关子查询
    1. 6.1. 含义
    2. 6.2. 特点
    3. 6.3. 执行过程
    4. 6.4. 入门案例
      1. 6.4.1. 示意图1
      2. 6.4.2. 示意图2
    5. 6.5. 其他案例
    6. 6.6. 使用位置
    7. 6.7. 如何判断
    8. 6.8. 课堂练习
  7. 7. 视图
    1. 7.1. 含义
    2. 7.2. 示意图
    3. 7.3. 为什么要用
    4. 7.4. 语法
    5. 7.5. 选项
    6. 7.6. 案例一
    7. 7.7. 案例二
    8. 7.8. 案例三
    9. 7.9. 案例四
  8. 8. 权限
    1. 8.1. 含义
    2. 8.2. 相关表
    3. 8.3. 执行示意图
  9. 9. 用户管理
    1. 9.1. 语法
    2. 9.2. 案例
  10. 10. 权限管理
    1. 10.1. 语法
    2. 10.2. 案例
    3. 10.3. 核实步骤
  11. 11. 事务
    1. 11.1. 含义
    2. 11.2. 特性
    3. 11.3. 组成
    4. 11.4. 手动结束
      1. 11.4.1. 案例一
      2. 11.4.2. 案例二
    5. 11.5. 自动结束
      1. 11.5.1. 自动提交
      2. 11.5.2. 自动回滚
    6. 11.6. 还原点
    7. 11.7. 事物隔离级别
      1. 11.7.1. 解释
      2. 11.7.2. 查看
      3. 11.7.3. 修改

ANY

含义

任意,也可以理解为只要满足一个就好了。

语法

= ANY 相当于in

<ANY 相当于小于最大值

>ANY 相当于大于最小值

案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 查询是经理的员工姓名，工资
select ename,sal from emp where empno in (select mgr from emp);

-- 查询部门编号不为10，且工资比10部门任意一名员工工资高的员工编号，姓名，职位，工资。
select empno,ename,job,sal from emp where deptno !=10 and sal > any (select sal from emp where deptno = 10);

--因为>ANY代表大于最小,所以如下和上面等价
select empno,ename,job,sal from emp where deptno !=10 and sal > (select min(sal) from emp where deptno = 10)

-- 查询部门编号不为10，且工资比10部门任意一名工资低的员工编号，姓名，职位，工资。

select empno,ename,job,sal from emp where deptno !=10 and sal < any (select sal from emp where deptno = 10);

--因为<ANY代表小于最大,所以如下和上面等价
select empno,ename,job,sal from emp where deptno !=10 and sal < (select max(sal) from emp where deptno = 10)

ALL

含义

满足全部

语法

= ALL 等于全部,没有任何意义

< ALL 小于最小值

>ALL 大于最大值

案例

1
2
3
4
5
6
7
8
9
10
11
-- 查询部门编号不为20，  且工资比 20部门所有员工工资高 的     员工编号，姓名，职位，工资。
select empno,ename,job,sal from emp where deptno !=20 and sal > ALL (select sal from emp where deptno = 20)

-- 因为 > ALL 含义是大于最大值所以如下和上面等价
select empno,ename,job,sal from emp where deptno !=20 and sal > (select max(sal) from emp where deptno = 20)

-- 查询部门编号不为20，  且工资比 20部门所有员工工资低 的     员工编号，姓名，职位，工资。
select empno,ename,job,sal from emp where deptno !=20 and sal < ALL (select sal from emp where deptno = 20);

-- 因为 > ALL 含义是小于最小值所以如下和上面等价
select empno,ename,job,sal from emp where deptno !=20 and sal < (select min(sal) from emp where deptno = 20);

not in

注意点

只要空值有可能成为子查询结果集合中的一部分，就不能使用NOT IN 运算符

方案

子查询中将null值排除掉

案例

1
2
3
4
5
6
7
8
-- 查找是经理的员工信息
select *from emp where empno in (select mgr from emp)
	 
-- 查询不是经理的员工姓名
select *from emp where empno not in (select mgr from emp)    -- not in 不可以放在有null的地方
	 
-- 优化后方案	 
select *from emp where empno not in (select mgr from emp where mgr is not null)

表之间的关系

种类

  • one to many 【many to one】
    • dept和emp
    • customer和book
    • user和car
  • many to many
    • stu和teacher
    • user和role
  • one to one
    • qq和zone
    • user和card

案例

oneToMany

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `deptno` int NOT NULL,
  `dname` varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `loc` varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`deptno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES (40, 'OPERATIONS', 'BOSTON');

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`  (
  `empno` int NOT NULL,
  `ename` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `job` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `mgr` int NULL DEFAULT NULL,
  `hiredate` date NULL DEFAULT NULL,
  `sal` decimal(7, 2) NULL DEFAULT NULL,
  `comm` decimal(7, 2) NULL DEFAULT NULL,
  `deptno` int NULL DEFAULT NULL,
  PRIMARY KEY (`empno`) USING BTREE,
  INDEX `fk_deptno`(`deptno` ASC) USING BTREE,
  CONSTRAINT `fk_deptno` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

manyToMany

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '小红');
INSERT INTO `student` VALUES (2, '小明');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '王老师');
INSERT INTO `teacher` VALUES (2, '李老师');

-- ----------------------------
-- Table structure for middle_teacher_student
-- ----------------------------
DROP TABLE IF EXISTS `middle_teacher_student`;
CREATE TABLE `middle_teacher_student`  (
  `teacher_id` int NOT NULL,
  `student_id` int NOT NULL,
  PRIMARY KEY (`teacher_id`, `student_id`) USING BTREE,
  INDEX `student_id`(`student_id` ASC) USING BTREE,
  CONSTRAINT `middle_teacher_student_ibfk_1` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `middle_teacher_student_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of middle_teacher_student
-- ----------------------------
INSERT INTO `middle_teacher_student` VALUES (1, 1);
INSERT INTO `middle_teacher_student` VALUES (2, 1);
INSERT INTO `middle_teacher_student` VALUES (2, 2);

oneToOne

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- ----------------------------
-- Table structure for qq
-- ----------------------------
DROP TABLE IF EXISTS `qq`;
CREATE TABLE `qq`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `num` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of qq
-- ----------------------------
INSERT INTO `qq` VALUES (1, '110');
INSERT INTO `qq` VALUES (2, '120');


-- ----------------------------
-- Table structure for zone
-- ----------------------------
DROP TABLE IF EXISTS `zone`;
CREATE TABLE `zone`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `space` int NOT NULL,
  `site` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `qq_id` int NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `qq_id`(`qq_id` ASC) USING BTREE,
  CONSTRAINT `zone_ibfk_1` FOREIGN KEY (`qq_id`) REFERENCES `qq` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of zone
-- ----------------------------
INSERT INTO `zone` VALUES (1, 100, 'www...', 1);

嵌套子查询

位置

  • from
  • where
  • having

作用

  • 条件
    • where
    • having
  • 表
    • from

执行过程

  • 子查询首先执行一次
    • 给主查询作为查询参数
    • 作为一张临时表

相关子查询

含义

当子查询中引用了父查询表中的一个列时,此时数据库服务器就执行相关子查询。

1658113510385

特点

  • 主查询有N条记录,子查询就要执行N次

执行过程

1658072098970

入门案例

1
2
3
4
5
6
7
8
-- 比本部门平均薪水高的  员工姓名，薪水,分别使用嵌套子查询、相关子查询

-- 嵌套子查询方式
select e.ename,e.sal from emp e,(select avg(sal) avgSal,deptno from emp GROUP BY deptno) t 
   where e.deptno = t.deptno and e.sal > t.avgSal
	 
-- 相关子查询方式
   select e.ename,e.sal from emp e where e.sal > (select avg(sal) from emp where deptno =   e.deptno)

示意图1

1658070918009

示意图2

1658070881729

其他案例

1
2
3
4
5
6
7
8
9
10
11
-- 查询所有部门名称  和  人数,使用相关子查询

select count(e.empno),e.deptno,(select dname from dept where deptno = e.deptno ) from emp e group by deptno

 
-- 查询哪些员工是经理,分别使用嵌套子查询、相关子查询
-- 嵌套子查询
select e.* from emp e where e.empno in (select mgr from emp)

-- 相关子查询  (管理了员工的并且管理的数量>0)
select e.* from emp e where (select count(*) from emp where mgr = e.empno)>0

使用位置

  • select
  • where

如何判断

根据子查询是否使用主查询的参数来判断

课堂练习

  • 查询所有雇员编号、名字、部门名字
  • 查询哪些员工是经理
  • 查询哪些员工不是经理
  • 查询每个部门工资最低的两个员工编号、姓名、工资

视图

含义

  • 是sql语句的封装
  • 本身不保存数据
  • 数据来自基础表或者其他视图

示意图

1652941537152

为什么要用

  • 避免重复编写sql语句
  • 封装复杂的sql语句
  • 显示部分列数据

语法

创建或修改

1
2
3
4
CREATE [OR REPLACE] 
VIEW view_name [(column_list)] 
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION

删除

1
drop view 视图名;

查看

1
desc 视图名;

使用

1
select * from 视图名 [where 条件]

选项

1
with [cascaded | local] check option -- 对DML操作起到约束作用
  • cascaded 这是默认的,代表检查选项会级联传递
  • local 代表选项不会级联传递

案例一

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 创建一个视图v_emp10，通过该视图只能查看10号部门的员工编号，员工姓名，职位
create or replace view v_emp10 as select empno,ename,job from emp where deptno = 10;

create or replace view v_emp10 as select empno as xx,ename,job from emp where deptno = 10;

create or replace view v_emp10 (xx,yy,zz) as select empno as xx ,ename,job from emp where deptno = 10;

-- 使用视图
select *from v_emp10;

-- 查看视图
desc v_emp10;

-- 创建一个视图，通过该视图可以   查看每个部门的编号，最低工资，最高工资，平均工资
create or replace view v_empinfo as select deptno,min(sal),max(sal),avg(sal) from emp group by deptno;

-- 使用视图
select *from v_empinfo where deptno=20;

-- 删除视图
drop view v_empinfo;

案例二

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- WITH [CASCADED | LOCAL] CHECK OPTION


CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


-- 这种视图上可以执行DML操作,因为使用的是简单视图并且没有添加任何检查选项
create view v_user as select *from user;
select *from v_user;

-- 这种视图上不可以执行DML操作
create view  v_user_count as select count(*) from user;
select *from v_user_count;

-- 视图上执行DML操作
insert into v_user (name,age) values('ls',10);

update v_user set age = 20 where id = 2;

delete from v_user where id = 2;

select *from v_user;
 
 
 -- 如下视图上可以执行DML操作,但是操作的id必须满足>10,否则DML操作无法执行成功
 create or replace view v_user as select *from user where id >10 WITH CHECK OPTION;
 
  -- 成功
 insert into v_user (id,name,age) values(11,'ls',10);
 -- 不成功
 insert into v_user (id,name,age) values(1,'ls',10);

 
 -- 成功
 delete from v_user where id = 11;
 -- 不成功
 delete from v_user where id = 1;

 
 -- 成功
 update v_user set id = 12 where id = 11;
 
 -- 不成功
 update v_user set id = 12 where id = 1;
 -- 不成功
 update v_user set id = 2 where id = 12;

案例三

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
create or replace view v_user as select *from user where id >10;

create or replace view v_view_user as select *from v_user where id < 20 with CASCADED CHECK OPTION; 

-- insert 
-- 不成功
insert into v_view_user(id,name,age) values(2,'xx',10);
-- 成功
insert into v_view_user(id,name,age) values(14,'xx',10);

-- update
-- delete
  
create or replace view v_user as select *from user where id >10;
create or replace view v_local_user as select *from v_user where id < 20 with local CHECK OPTION; 

-- 成功
insert into v_local_user(id,name,age) values(2,'xx',20); 
-- update
-- delete

案例四

1
2
create or replace view xx select count(*) from user;
-- 请问对上面的视图能否进行DML操作,为什么?

权限

含义

对于资源只有有权限的用户才能操作

相关表

  • user
  • db
  • tables_priv
  • columns_priv
  • procs_priv

执行示意图

1652951635462

用户管理

语法

创建用户

1
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']

修改名字

1
RENAME USER old_user TO new_user;

修改密码

1
ALTER USER userIDENTIFIED BY 'password';

删除用户

1
DROP USER user_name

案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建用户
CREATE USER 'xx'@'localhost' IDENTIFIED BY 'xx';

-- 修改用户名字
RENAME USER 'xx'@'localhost' TO 'xxxx'@'localhost';

-- 修改用户密码
ALTER USER 'xxxx'@'localhost' IDENTIFIED BY 'xxxx';

-- 修改root的密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

-- 删除用户
drop user 'xxxx'@'localhost'

权限管理

语法

1
2
3
4
5
6
7
8
9
-- 授权
grant 指定权限 on 指定库.指定表 to 用户 [with grant option];


-- 回收权限
revoke 指定权限,[grant optioin] on 指定库.指定表 from 用户;

-- 查看权限
show grants for 用户;

案例

1
2
3
4
5
6
7
8
9
10
11
-- 给zs授予所有库中所有表的select权限
grant select on *.* to 'zs'@'localhost';

-- 给yy授予所有库中所有表的全部权限,并且yy用户可以将全部权限授予其他用户
grant all privileges on *.* to 'yy'@'localhost' with grant option;

-- 给yy用户授予xx数据库中user表的删除、更新id列的权限
grant delete,update(id) on xx.user to 'yy'@'localhost';

-- 收回yy用户在所有库中的所有表的所有权限
revoke all privileges on *.* from 'yy'@'localhost';

核实步骤

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
grant select,update  on *.* to 'zs'@'localhost'; 

-- 授予全局的查询、更新权限
create user 'xx'@'localhost' IDENTIFIED by 'xx';

grant select on xx.* to 'xx'@'localhost';

-- xx 登录进去了
    use xx;
		  select *from emp; -- 权限核实是如何的？
			                    --user表中select 是否Y?
									 -- 不是Y
											-- db 表中的 select 是否 Y?   【数据库级别的权限】
												-- 是Y 直接访问
																							
																							

create user 'yy'@'localhost' IDENTIFIED by 'yy';	
grant select on xx.emp to 'yy'@'localhost';		
grant delete on xx.emp to 'yy'@'localhost';		
grant update(ename) on xx.emp to 'yy'@'localhost';	
-- yy 登录进入了
   use xx; 
	    select *from emp;  -- 权限核实是如何的？
			                   -- user表中select 是否为Y
									-- 不是的
										-- db表中的select 是否为Y
											-- 不是的
													-- table_priv表中select是否为Y
															-- 是Y 直接访问

事务

含义

对数据库的一组操作要不同时成功,要不同时失败。

特性

特性 含义
原子性 不可分割的最小单位
一致性 事务结束后数据保持不变
隔离性 事务之间互不影响【隔离级别有关系】
持久性 事务结束之后断电重启后事务不会回滚

组成

  • DML+TPL
  • DML+DDL
  • DML+DCL

手动结束

案例一

1
2
3
4
5
6
-- 不自动提交[遇到dml不会自动提交事务需要手动来处理,commit|rollback]
set @@autocommit = 0;
update user set money = money - 500 where id  = 1;
update user set money = money + 500 where id  = 2;

rollback;  -- commit;

案例二

1
2
3
4
5
6
7
set @@autocommit=1; -- 恢复默认

begin;
update user set money = money - 500 where id  = 1;
update user set money = money + 500 where id  = 2;

rollback;  -- commit;

自动结束

自行验证,了解即可

自动提交

  • 执行一个DDL(CREATE、ALTER、DROP、TRUNCATE、RENAME）语句；
  • 执行一个DCL(GRANT、REVOKE)语句；

自动回滚

  • 客户端强行退出
  • 客户端连接到服务器端异常中断
  • 系统崩溃

还原点

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
BEGIN;                      -- 开始事务操作
DELETE FROM test ;
ROLLBACK;                        -- 撤消DELETE操作

BEGIN;  -- 等价于  start transaction;

INSERT INTO test VALUES('A');
SAVEPOINT insert_a;            -- 定义insert_a保存点

INSERT INTO test VALUES('B');
SAVEPOINT insert_b;             -- 定义insert_b保存点

INSERT INTO test VALUES('C');
ROLLBACK TO insert_b;    -- 撤消操作到insert_b保存点,事务没有结束

DELETE FROM test WHERE name = 'A';
COMMIT;                          -- 将所有修改写入数据库

ROLLBACK;    -- 所有操作已经COMMIT提交，不能回滚

事物隔离级别

READ UNCOMMITTED READ COMMITTED REPEATABLE READ【默认】 SERIALIZABLE
脏读 y n n n
不可重复读 y y n n
幻读(虚读) y y y n

解释

脏读: 一个事务读取到另一个事务未提交的数据

不可重复读:一个相同的事务多次读取的数据不一样

幻读:强调在于某一个范围内的数据行变多或者是变少了，侧重说明的是数据集不一样导致了产生了幻读。

查看

1
SELECT @@TRANSACTION_ISOLATION;

修改

1
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE}

SESSION 代表只会对当前会话有效

GLOBAL 代表会对所有会话有效

最后编辑：2022-07-18