2021-03-31-数据库学习之4-多表查询、事务、DCL

最新推荐文章于 2023-07-13 10:42:08 发布

努力学习的代码小白

最新推荐文章于 2023-07-13 10:42:08 发布

阅读量106

点赞数

分类专栏：学习笔记文章标签： mysql

本文链接：https://blog.csdn.net/weixin_42656213/article/details/115362935

版权

学习笔记专栏收录该内容

63 篇文章 9 订阅

订阅专栏

一、多表查询
查询语法：

select 
	列名列表
from
	表名列表
where ...

代码示例：

# 创建部门表
CREATE TABLE dept2(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
INSERT INTO dept2 (NAME) VALUES('开发部'),('市场部'),('财务部');
# 创建员工表
CREATE TABLE emp2(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	gender CHAR(1), -- 性别
	salary DOUBLE, -- 工资
	join_date DATE, -- 入职日期
	dept2_id INT,
	FOREIGN KEY (dept2_id) REFERENCES dept2(id) -- 外键，关联部门表（部门表的主键）
);
INSERT INTO emp2(NAME,gender,salary,join_date,dept2_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp2(NAME,gender,salary,join_date,dept2_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp2(NAME,gender,salary,join_date,dept2_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp2(NAME,gender,salary,join_date,dept2_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp2(NAME,gender,salary,join_date,dept2_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);
SELECT * FROM emp2,dept2; -- 多表查询
-- 笛卡尔积，会出现数据冗余

在这里插入图片描述

笛卡尔积：取两个集合的所有组成情况。要完成多表查询，需要消除无用的数据。
多表查询的分类
参考链接：https://blog.csdn.net/qq_38125058/article/details/79946850
- 1.内连接查询
  - （1）隐式内连接：使用where条件消除无用数据。
    例子：

-- 查询员工表的名称，性别，部门表的名称，第一种方法
SELECT emp2.`name`,emp2.`gender`,dept2.`name` FROM emp2,dept2 WHERE emp2.`dept2_id` = dept2.`id`;
-- 第二种方法
SELECT 
	t1.`name`,
	t1.`gender`,
	t2.`name`
FROM
	emp2 t1,dept2 t2
WHERE 
	t1.`dept2_id` = t2.`id`;

在这里插入图片描述

（2）显示内连接：
语法：select 字段列表 from 表名1 [inner] join 表名2 on 条件
代码示例：

SELECT * FROM emp2 INNER JOIN dept2 ON emp2.`dept2_id` = dept2.`id`;
SELECT * FROM emp2 JOIN dept2 ON emp2.`dept2_id` = dept2.`id`;

在这里插入图片描述
（3）内连接查询
a.从哪些表中查询数据
b.条件是什么
c.查询哪些字段

2.外连接查询
（1）左外连接
语法：select 字段列表 from 表1 left [outer] join 表2 on 条件;
查询的是左表所有数据以及其交集部分。
（2）右外连接
语法：select 字段列表 from 表1 right [outer] join 表2 on 条件;
查询的是右表所有数据以及其交集部分。

SELECT * FROM dept2;
SELECT * FROM emp2;

-- 查询所有员工信息，如果员工有部门，则b们名称查询部门名称，没有部门，则不显示
SELECT 
	t1.*,t2.`name`
FROM 
	emp2 t1,dept2 t2
WHERE
	t1.`dept2_id` = t2.`id`;

-- emp2是左表，dept2是右表
SELECT t1.*,t2.`name` FROM emp2 t1 LEFT JOIN dept2 t2 ON t1.`dept2_id` = t2.`id`;

SELECT t1.*,t2.`name` FROM emp2 t1 RIGHT JOIN dept2 t2 ON t1.`dept2_id` = t2.`id`;
SELECT * FROM dept t2 RIGHT JOIN emp2 t1 ON t1.`dept2_id` = t2.`id`;

在这里插入图片描述

3.子查询
（1）概念：查询中嵌套查询，称嵌套查询为子查询。

-- 查询工资最高的员工信息
-- 1 查询最高的工资是多少 9000
SELECT MAX(salary) FROM emp2;
-- 2 查询员工信息，并且工资等于9000的
SELECT * FROM emp2 WHERE emp2.`salary` = 9000;

-- 一条SQL语句就完成查询工资最高的员工的信息，子查询
SELECT * FROM emp2 WHERE emp2.`salary` = (SELECT MAX(salary) FROM emp2);

在这里插入图片描述

（2）子查询的不同情况1–子查询的结果是单行单列的

子查询可以作为条件，使用运算符去判断。运算符：> >= < <= =

-- 查询员工工资小于平均工资的人
SELECT AVG(salary) FROM emp2;
SELECT * FROM emp2 WHERE emp2.`salary` < (SELECT AVG(salary) FROM emp2);

在这里插入图片描述

（3）子查询的不同情况2–子查询的结果是多行单列的

-- 查询财务部的所有员工信息
SELECT id FROM dept2 WHERE NAME = '财务部';
SELECT * FROM emp2 WHERE dept2_id = 3;
-- 查询财务部和市场部的所有员工信息
SELECT id FROM dept2 WHERE NAME = '财务部' OR NAME = '市场部';
SELECT * FROM emp2 WHERE dept2_id = 3 OR dept2_id = 2; 
-- 简化写法，子查询， 查询财务部和市场部的所有员工信息
SELECT * FROM emp2 WHERE dept2_id IN (3,2);
SELECT * FROM emp2 WHERE dept2_id IN (SELECT id FROM dept2 WHERE NAME = '财务部' OR NAME = '市场部');

在这里插入图片描述

（4）子查询的不同情况2–子查询的结果是多行多列的

子查询可以作为一张虚拟表参与查询

-- 多行
-- 查询员工入职日期是2011-11-11之后的员工信息和部门信息
-- 方法1，子查询
SELECT * FROM dept2 t1, (SELECT * FROM emp2 WHERE emp2.`join_date` > '2011-11-11') t2 WHERE t1.`id` = t2.dept2_id;

-- 方法2
SELECT * FROM emp2 WHERE emp2.`join_date` > '2011-11-11';

-- 普通内连接
-- 查询所有员工和部门信息
SELECT * FROM emp2 t1,dept2 t2 WHERE t1.`dept2_id` = t2.`id`;
-- 查询员工入职日期是2011-11-11之后的员工信息和部门信息
SELECT * FROM emp2 t1,dept2 t2 WHERE t1.`dept2_id` = t2.`id` AND t1.`join_date` > '2011-11-11';

在这里插入图片描述

二、事务
1.事务的基本介绍

（1）基本概念
如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
（2）操作
- 开启事务：start transaction;
- 回滚：rollback;
- 提交：commit;
  
  第一个窗口代码：

USE db3;
SELECT DATABASE();

CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME,balance) VALUES ('zhangsan',1000), ('lisi',1000);
SELECT * FROM account; -- 用于查看临时的数据状态
UPDATE account SET balance = 1000;
-- 张三给李四转账500元
-- 0. 开启事务
START TRANSACTION;
-- 1. 张三账户 -500
UPDATE account SET balance  = balance - 500 WHERE NAME = 'zhangsan';
-- 2. 李四账户 +500
-- 出错时，这个窗口会产生临时数据，也就是张三已经-500，李四已经+500，
-- 但是新打开一个询问窗口查看时，两个人钱数没变
-- 出错了
UPDATE account SET balance  = balance + 500 WHERE NAME = 'lisi';
-- 发现执行没有问题，提交事务
-- 提交事务后，打开另一个命令编辑窗口，数据都会发生变化
-- 如果不执行commit，则打开另一个命令编辑窗口，数据不会发生变化
COMMIT;
-- 发现出问题了，回滚事务
-- 此时如果一个步骤出问题，也不会产生临时钱数改变的数据
ROLLBACK;

另一个窗口代码：

SELECT * FROM account;
USE db3;
SELECT DATABASE();

（3）MySQL数据库中事务默认自动提交
- 事务提交的两种方式：
  - 自动提交：
    - MySQL就是自动提交的
    - 一条DML（增删改）语句会自动提交一次事务。
  - 手动提交：
    - Oracle数据库默认是手动提交事务。
    - 需要先开启事务，再提交。
- 修改事务的默认提交方式：
  - 查看事务的默认提交方式：SELECT @@autocommit; -- 1 代表自动提交，0 代表手动提交
  - 修改默认提交方式：SET @@autocommit=0;

代码：

-- 查看默认提交方式
-- 1 代表自动提交，0 代表手动提交
SELECT @@autocommit;
-- 修改为手动提交
SET @@autocommit=0;
UPDATE account SET balance = 30;
-- 手动提交时，如果不执行commit，重新打开窗口不会生效
COMMIT;

2.事务的四大特征
（1）原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
（2）持久性：当事务提交或回滚后，数据会持久化的保存数据。
（3）隔离性：多个事务之间，相互独立。
（4）一致性：事务操作前后，数据总量不变。（比如转账后两个人的总钱数不变）
3.事务的隔离级别（了解）

概念：多个事务之间是隔离的，相互独立的，但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
存在问题：
- 1.脏读：一个事务，读取到另一个事务中没有提交的数据
- 2.不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样。
- 3.幻读：一个事务操作（DML）数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
隔离级别：
- read uncommitted：读未提交
  - 会产生的问题：脏读、不可重复读、幻读
- read committed：读已提交（Oracle默认的）
  - 会产生的问题：不可重复读、幻读
- repeatable read：可重复读（MySQL默认的）
  - 会产生的问题：幻读
- serializable：串行化
  - 可以解决所有问题
- 注意：隔离级别从小到大安全性越来越高，但是效率越来越低。
- 数据库查询隔离级别：
  - select @@tx_isolation;
- 数据库设置隔离级别：
  - set global transaction isolation level 级别字符串;
    
    设置之后未生效，需要重新打开数据库进行查看才可以生效。
    
    重新打开数据库连接后生效了：
    
    4. 事务的隔离级别演示1
    首先打开命令提示窗口：
    
    然后设置隔离级别：

set global transaction isolation level read uncommitted;
start transaction;
-- 转账操作
update account set balance = balance - 500 where id =1;
update account set balance = balance + 500 where id =2;

在这里插入图片描述
然后两边都开启事务：

然后一个窗口执行转账事务但是不提交，另一个窗口读取到了前面窗口为提交的数据（脏读）：

然后1号窗口执行回滚rollback，2号再查询数据时没有发生变化，还是1000和1000：

解决脏读问题，设置事务级别为read committed，再将余额都变成1000：
在这里插入图片描述
两边都同时开启事务：

1号窗口完成转账事务，2号窗口查看数据变化情况（没变）：

1号窗口提交事务（commit）后，2号窗口查看数据变化情况（变化了）：

此时会产生数据不可重复读的问题，2号窗口两次读取的数据不一样，在1号窗口执行提交事务命令后，数据发生了变化。有时候要求在同一个事务里面每次查询的数据都是一样的。

5. 事务的隔离级别演示2
解决不可重复读的问题
首先设置1号窗口的事务隔离级别为repeatable read ，并重新设置两个人的余额均为1000：
在这里插入图片描述
两个窗口同时开启事务，然后1号窗口完成转账事务，但是没有执行提交事务，2号窗口再查看（数据未变化）：

1号窗口执行事务提交后，如果2号窗口查看的数据仍然没有发生变化，则说明解决了不可重复读的问题：
在这里插入图片描述
6. 事务的隔离级别演示3
serializable串行化：如果一个事务在操作一个数据表，则另一个事务不能再操作这个数据表了。
首先1号设置事务隔离级别为serializable，重新打开一个2号窗口查看：

两个窗口同时开启事务，1号窗口完成转账事务，但是不提交事务，2号窗口查看，则2窗口的光标一直闪烁，一直等到1号完成事务提交为止：
在这里插入图片描述
1号执行事务提交后，2号窗口立马闪现数据变化情况：

三、DCL

SQL分类：
- DDL：操作数据库和表
- DML：增删改表中的数据
- DQL：查询表中的数据
- DCL：管理用户，授权
DBA：数据库管理员

DCL：管理用户，授权

管理用户
1.添加用户

-- 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

在这里插入图片描述

2.删除用户

-- 删除用户
DROP USER '用户名'@'主机名';

在这里插入图片描述

3.修改用户密码
方法1：

-- 修改lisi用户的密码为abc
-- 语法
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
-- 例如
UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';、

在这里插入图片描述
我的数据库不知道为啥密码还是原来的123，修改不了！！！

方法2：

-- 语法
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
-- 例如
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
SET PASSWORD FOR 'lisi'@'%' = PASSWORD('abc');

在这里插入图片描述

特殊情况：MySQL中因为多次修改密码导致忘记了root用户的密码怎么办？

cmd – > net stop mysql 停止mysql服务
启动MySQL服务
使用无验证方式启动MySQL服务。

mysqld --skip-grant-tables

在这里插入图片描述
3. 打开新的cmd窗口（管理员身份），直接输入mysql命令，敲回车，就可以登录成功

4. 然后修改root用户的密码：
use mysql;
5. update user set password = password(‘新密码’) where user = ‘root’;

6. 关闭两个窗口
7. 然后进入任务管理器结束掉mysqld.exe这个进程
在这里插入图片描述
8. 然后通过管理员身份进入cmd，启动mysql服务：

9. 然后通过新的密码登录数据库：

4.查询用户：

-- 1.切换到mysql数据库
USE mysql;
-- 2.查询user表
SELECT * FROM USER;

通配符：% 表示可以在任意主机使用用户登录数据库。
在这里插入图片描述

权限管理
1. 查询权限

-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'lisi'@'%';
SHOW GRANTS FOR 'root'@'%';

在这里插入图片描述

2. 授予权限

-- 权限授予
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
GRANT SELECT ON db3.account TO 'lisi'@'%';

在这里插入图片描述

给lisi授予多个权限：

-- 权限授予
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
GRANT SELECT ON db3.account TO 'lisi'@'%';
-- 授予多个权限
GRANT SELECT,DELETE,UPDATE ON db3.account TO 'lisi'@'%';

在这里插入图片描述

给张三用户授予所有权限：

-- 创建张三用户
CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123';
-- 给张三用户授予所有权限，在任意数据库任意表上
-- 所有权限的通配符是all
GRANT ALL ON *.* TO 'zhangsan'@'localhost';

在这里插入图片描述
3. 撤销权限
语法：

-- 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
-- 将李四的修改权限撤销掉
REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';

在这里插入图片描述

努力学习的代码小白

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
2021-03-31-数据库学习之4-多表查询、事务、DCL

一、多表查询查询语法：select 列名列表from 表名列表where ... 代码示例：# 创建部门表CREATE TABLE dept2( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20));INSERT INTO dept2 (NAME) VALUES('开发部'),('市场部'),('财务部');# 创建员工表CREATE TABLE emp2( id INT PRIMARY KEY AUTO_INCREME
复制链接

扫一扫