SQL复习（三）-- 多表联合查询 & 事务 & 数据库的备份及还原操作 & DCL

最新推荐文章于 2022-06-10 15:39:33 发布

wuyanwenyun

最新推荐文章于 2022-06-10 15:39:33 发布

阅读量367

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/wuyanwenyun/article/details/105511663

版权

MySQL 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

4.1 命令行操作（注意配置MySQL的路径）

4.2 Navicat操作

4.3 DCL（ Data Control Language）

1 多表联合查询

练习用的数据

-- 部门表：department
create table dept(
    deptno int primary key comment '部门编号',
    dname varchar(14) comment '部门名称',
    loc varchar(13) comment '部门地址'
);

-- 员工表：employee
create table emp(
    empno int primary key comment '员工编号',
    ename varchar(10) comment '员工姓名',
    job varchar(9) comment '职位',
    mgr int comment '上级领导编号',
    hiredate date comment '入职日期',
    sal double comment '工资',
    comm double comment '提成',
    deptno int comment '所在部门编号'
);

/*工资等级表*/  
create table salgrade (  
    grade int comment'等级', 
    losal double comment'最低工资',
    hisal double comment'最高工资' 
);

插入测试数据

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');

insert into emp values (7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,'1981-4-2',2975,null,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);
insert into emp values (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,null,30);
insert into emp values (7782,'CLARK','MANAGER',7839,'1981-6-09',2450,null,10);
insert into emp values (7788,'SCOTT','ANALYST',7566,'1987-4-19',3000,null,20);
insert into emp values (7839,'KING','PRESIDENT',null,'1981-11-17',5000,null,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,'1981-9-08',1500,0,30);
insert into emp values (7876,'ADAMS','CLERK',7788,'1987-5-23',1100,null,20);
insert into emp values (7900,'JAMES','CLERK',7698,'1981-12-31',950,null,30);
insert into emp values (7902,'FORD','ANALYST',7566,'1981-12-3',3000,null,20);
insert into emp values (7934,'MILLER','CLERK',7782,'1982-2-23',1300,null,10);

insert into salgrade values (1,700,1200);
insert into salgrade values (2,1201,1400);
insert into salgrade values (3,1401,2000);
insert into salgrade values (4,2001,3000);
insert into salgrade values (5,3001,9999);

2 多表联合查询的分类

笛卡尔积：两个集合所有元素可能的组合，会产生无效数据。因此在查询是要消除笛卡尔积

2.1 内连接

消除笛卡尔积的连接操作，查询两张表中满足连接条件的数据。

隐式内连接：需要通过 where 子句设置连接条件

select ... from 表1，表2 where...；

显示内连接（推荐）：使用 join 关键字连接表格, 使用 on 关键字设置关联条件

select ... from 表1 [inner] join 表2 on 连接条件 [重复左边] where 过滤条件 ...

隐式内连接和显示内连接（推荐）的区别？

隐式连接好理解好书写，语法简单，担心的点较少。

显式连接可以减少字段的扫描，有更快的执行速度。这种速度优势在3张或更多表连接时比较明显

2.2 外连接

在内连接的基础上, 要求查出某张表中不满足连接条件的数据.

外连接分为两种: 左外连接和右外连接（如下图）：

select ... from 表1 left | right [outer] join 表2 on 关联条件 where ...

2.3 自连接

一张表格的某个字段参照当前表格的另一个字段, 这种关联称为自连接.

实现：为同一张表起两个别名

select ... from 表名 e1 join 表名 e2 on 关联条件

2.4 子查询

查询中嵌套查询, 嵌套查询被称之为子查询. 一个查询的结果被另一个查询使用的情况.

子查询根据返回结果的不同可以分为三种情况。

返回结果为单行单列的子查询
此时, 子查询一般会作为where条件使用, 可以通过比较运算符进行比较
返回结果为多行单列的子查询
此时, 子查询一般会作为where条件使用, 但是不能使用简单的比较运算符, 可以使用 in 进行操作
返回结果为多行多列的子查询
此时, 子查询一般会作为一张虚拟表, 和其他表进行关联查询.

2.5 代码演示及练习

演示笛卡尔积（结果会出现56条数据）

select * from emp, dept;  
select * from emp inner join dept;

2.5.1 内连接

查询所有员工及其所在部门的信息（14条）

select * from emp e, dept d where e.deptno=d.deptno;  
select * from emp e join dept d on e.deptno=d.deptno;

查询20部门的员工编号, 姓名, 工资和部门名称（5条）

select
	e.empno,
	e.ename,
	e.sal,
	d.dname 
from
	emp e
	join dept d on e.deptno = d.deptno 
where
	d.deptno = 20;

补充：自然连接（natural join），自动使用两张表格的同名列进行等值连接

select e.empno,e.ename,e.sal,d.dname 
from emp e natural join dept d where d.deptno = 20;

-- 如果两张表格有多个同名列，使用 using 制定同名列进行连接
-- 注意using关键字后的字段名要加 ()
-- where 后的字段名不需加表名，↑ 是d.deptno=20;  ↓ 是deptno=20;
select e.empno,e.ename,e.sal,d.dname from emp e join dept d
using (deptno)
where deptno=20;

3.5.2 外连接

在使用外连接的时候，要注意哪张表是基础表（哪张表需要查所有数据），哪张表是连接表。

查询所有的员工的编号和姓名及其所在部门的名称（用两种外连接实现）（14条数据）

select e.empno,e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno; 
select e.empno,e.ename,d.dname from dept d right join emp e on e.deptno=d.deptno;

错误的右外连接语句（15条数据）

-- 错误的右连接
select e.empno,e.ename,d.dname from emp e right join dept d on e.deptno=d.deptno;

我们可以看见表中是没有部门编号为40的员工的，所以错误的右外连接会出现15条数据。个人建议左外连接更简单一些。

2.5.3 自连接

查询所有员工的编号，姓名以及上级领导的编号和姓名（14条）

-- 同一张表，起两个别名，e （代表员工表）和emgr （员工的上级领导表）
-- 必须使用左外连接，员工KING没有上级领导，使用内连接的话，KING会消失
select
	e.empno,
	e.ename,
	emgr.empno mno,   -- 上级编号，别名mno
	emgr.ename mname  -- 上级姓名，别名mname
from
	emp e
	left join emp emgr on e.mgr = emgr.empno;

2.5.4 子查询

单行单列

查询和SCOTT相同工资的员工信息

select * from emp where sal =( select sal from emp where ename = 'SCOTT' );

查询和SMITH从事相同职位的员工信息

select * from emp where job =( select job from emp where ename = 'smith' );

多行单列

查询SALES和RESEARCH部门的员工信息

select
	e.* 
from
	emp e
	left join dept d on e.deptno = d.deptno 
where
	d.dname = 'SALES' 
	or d.dname = 'RESEARCH';


select
	* 
from
	emp 
where
	deptno in (
	select
		deptno 
	from
		dept 
	where
	dname in ( 'SALES', 'RESEARCH' ));

多行多列

查询在1981-02-20前入职的员工的编号, 姓名和部门名称

-- 查询结果做新的表t，使用自然连接
select
	t.empno,
	t.ename,
	d.dname 
from
	( select * from emp where hiredate <= '1981-02-20' ) t
	natural join dept d;

2.5.5 练习题

练习单拿出一篇文章链接-------------- https://blog.csdn.net/wuyanwenyun/article/details/105558601

3 事务

3.1 概念及特性

事务, Transaction, 通常被简写为tx. 在某些情况下, 一个操作有可能涉及到多条DML语句. 例如银行转账, 此时, 要求多条DML要么同时成功, 要么同时失败. 事务有四大特性:

原子性(A): Automicity, 事务是操作数据库的最小单元, 不可分割.
一致性(C): Consistency, 事务执行前后状态要保持一致.
隔离性(I): Isolation, 多个事务之间项目独立, 互不影响.
持久性(D): Durability, 事务一旦提交, 数据将永久保存, 不可逆.

3.2 MySQL的事务管理

默认情况下, MySQL的事务是自动被提交的. 每当执行一条DML语句, 事务就自动进行提交. 如果要手动管理事务, 需要先开启事务, 然后通过指定的命令对事务进行提交或回滚.

开启事务: start transaction;
提交事务: commit;
回滚事务: rollback;

-- 新建银行账户表tb_account, 有三个字段, id, name, balance  
-- 录入两个用户张三和李四
create table tb_account (
 id int primary key,
 name varchar ( 10 ),
 balance double ( 7, 2 ) 
);

insert into tb_account
values
	( 101, 'zhangsan', 1000.00 ),
	( 102, 'lisi', 1000.00 );
select * from	tb_account;

-- 完成一次转账操作, zhangsan向lisi账户转入100元.  
-- 开启事务
start transaction;
-- zhangsan 转出100元
update tb_account set balance = balance - 100 where	name = 'zhansgan';
-- lisi 转入100元
update tb_account set balance = balance + 100 where	name = 'lisi';

-- 如果双方都成功，提交事务
commit;

-- 如果有一方失败，回滚事务
rollback;

-- 查看自动提交状态，1 自动提交，0 关闭自动提交
select @@autocommit;

-- 设置关闭自动提交
set @@autocommit = 0;

4 数据的备份和还原 & DCL

4.1 命令行操作（注意配置MySQL的路径）

备份

mysqldump -u 用户名 -p 密码数据库名 > 导出文件的存放位置

还原

连接数据库
创建database
使用database
执行命令: source .sql文件的绝对路径

4.2 Navicat操作

4.3 DCL（ Data Control Language）

数据控制语言，用于处理权限相关操作，属于DBA 范畴。

4.3.1 创建用户并设置密码

create user ‘用户名’@'主机名/IP/%' identified by '密码';

-- 创建用户 
create user 'wuyw'@'localhost' identified by 'wuywpwd';

-- 查询系统所有用户，在mysql库中的user表中
select user ,host from mysql.user;

4.3.2 给用户授权

需要有能给用户授权的权限. 刚开始时只有root用户有这个权限.

grant 权限 on 数据库.表 to ‘用户名’@’主机名’ identified by ‘密码’[with grant option];

以上语句执行时, 用户名如果存在, 则为已存在的用户授权; 如果用户不存在, 自动创建用户并授权.

flush privileges; 可以刷新权限

-- 授权操作  
grant all privileges on db_scott.emp to 'wuyw'@'localhost' identified by 'wuyw' with grant option;  

-- 刷新权限  
flush privileges;  

-- 如果没有用户, 可以自动创建用户  
grant all on db_scott.* to 'wuywno'@'%' identified by 'wuywno';  
flush privileges;

4.3.3 撤销授权

revoke 权限 on 数据库.表 from 用户;

-- 撤销授权  
revoke all on db_scott.* from 'wuyw'@'%';  
flush privileges;

4.3.3 删除用户

drop user '用户名'@'主机名';

drop user 'wuyw'@'%';

4.3.4 修改密码

-- 修改已登录的用户的密码
set password for 'wuyw'@'localhost' = password('newpwd123');
--通过mysqladmin命令修改密码
mysqladmin -u 用户名 -p 旧密码 password

wuyanwenyun

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
2
评论
SQL复习（三）-- 多表联合查询 & 事务 & 数据库的备份及还原操作 & DCL

多表联合查询，事务，数据库的还原及备份，DCL（数据控制语言）
复制链接

扫一扫

专栏目录