数据库基础知识3

逃离我推掉我的手 2022-05-10 01:06 281阅读 0赞

**在之前的笔记中已经记录了数据库中表的各种操作，接下来的笔记将记录多表查询的各种操作，包括事务的知识以及权限分配的DCL数据控制语言，重点记录语法与一些细节操作，所以思路会有间断**

## 1. 连接查询 ##

内     连接

select ... from 表1 inner join 表2 on 连接条件 WHERE ... group by ... HAVING ... ORDER BY ... limit ... (重点)

左    外

select ... from 表1 left [outer] join 表2 on 连接条件 (重点)

右    外

select ... from 表1 right [outer] join 表2 on 连接条件

例：连接部门表和员工表  
select \* from emp a inner join dept b on a.deptno=b.deptno;

注意：
    1) 内连接是将两张表中所有符合连接条件的数据列入结果，不符合连接条件的结果中没有，例如40号部门
    2) 如果连接的两表中有同名的列，列前面要加表名(或表别名)来区分（否则会报歧义错误）
    3) inner join ... on 的写法是符合SQL-92标准写法,其实还有一种内连接的写法：		
    select ... from 表1, 表2 where 连接条件;

例： 还是连接部门表和员工表 select \* from emp a, dept b where a.deptno=b.deptno;

连接学生表成绩表
    课程表连接老师表
    课程表连接成绩表

例子：采用左外连接员工表和部门表  
select \* from emp a left join dept b on a.deptno = b.deptno;  
例子：采用左外连接部门表和员工表  
select \* from dept b left join emp a on a.deptno = b.deptno;  
左外连接，首先将符合连接条件的记录连在一起，作为结果，其次左边表中不符合连接条件的记录也会出现在结果中，只不过它对应的右边的列都是NULL

例子：右外连接  
select \* from emp a right join dept b on a.deptno = b.deptno;

注意：左外和右外与表的先后次序有关，而内连接与表的先后次序无关

## 2. 多表连接 ##

select * from 表1 
    	inner join 表2 on 连接条件
    	inner join 表3 on 连接条件
    	...

例子

select * from student a inner join sc b on a.sid = b.sid
    	                         inner join course c on b.cid = c.cid
    							 inner join teacher d on c.tid = d.tid
    							 order by a.sid, b.cid;

等价写法：

select * from student a, sc b, course c, teacher d
    				 where a.sid=b.sid and b.cid=c.cid and c.tid=d.tid;

左外多表连接：

select * from student a left join sc b on a.sid = b.sid
    	                         left join course c on b.cid = c.cid
    							 left join teacher d on c.tid = d.tid
    							 order by a.sid, b.cid;

注意：**左外多表连接要全部使用left join，不能再出现inner join**

性能上：连接的表越多，性能越低, 可以把连接查询变成分多次查询

# 3. 自连接 #

一个表自己和自己连接

找到员工的姓名和上级的姓名

select a.empno,a.ename,a.mgr,b.empno,b.ename,b.mgr
    	 from emp a left join emp b on a.mgr=b.empno;

以后经常用于树状结构的数据表示

# 4. 子查询 #

把某个select结果当做一个值，或一张表做进一步的查询

情况1：找具有最高工资的员工信息（子查询作为值）

select max(sal) from emp; // 5000
    
    select * from emp where sal = (select max(sal) from emp);

**把select max(sal) from emp当成了一个值，代入到主查询当中，代入时需要在子查询的两边加(**)

情况2： 获取每个部门的平均工资和部门的名称（子查询作为表）  
先查询平均工资

(select deptno,avg(sal) from emp group by deptno) a

再把子查询看做临时表，与其它表做表连接

select * from (子查询)a inner join dept b on a.deptno=b.deptno;

练习：

1.  查询所有同学的学号、姓名、选课数、总成绩
    
    (select sid, count(\*),sum(score) from sc group by sid) a
    
        select * from (select sid, count(*),sum(score) from sc group by sid) a 
         inner join student b on a.sid=b.sid;
    
        select a.*,b.sname from (select sid, count(*) 选课数,sum(score) 总成绩 from sc group by sid) a 
        inner join student b on a.sid=b.sid;
2.  查询学生平均成绩大于80的所有学生的学号、姓名和平均成绩

(select sid, avg(score) 平均成绩 from sc group by sid having(avg(score)>80)) a
       
       select a.*, b.sname from (select sid, avg(score) 平均成绩 from sc group by sid having(avg(score)>80))a 
    	inner join student b on a.sid=b.sid;

1.  查询课程相同且成绩相同的的学生的学号、课程号、学生成绩

(select cid,score from sc group by cid,score having count(*) > 1) a
    	
    	select * from (select cid,score from sc group by cid,score having count(*) > 1)a 
    		inner join sc b on a.cid=b.cid and a.score=b.score;

1.  查询每个部门有最高工资的员工的（所有）信息

(select deptno,max(sal) msal from emp group by deptno) a
    
    select b.* from (select deptno,max(sal) msal from emp group by deptno)a 
    	inner join emp b on a.deptno=b.deptno and a.msal=b.sal;

情况3： 将子查询当做一个函数 (了解)

(select max(sal) from emp where deptno =?)

m(deptno) 返回结果是这个deptno下的最大工资

部门编号是一个入参, 最大工资是返回结果

select * from emp e where sal = m(e.deptno);  // 伪代码
    
    select * from emp e where sal = (select max(sal) from emp where deptno = e.deptno);

# 6. 事务 #

DDL create alter drop truncate  
DML insert update delete select  
TCL start transaction, commit, rollback

## 6.1 start transaction 开始事务 (begin) ##

## 6.2 commit 提交事务 ##

## 6.3 rollback 回滚事务 ##

账户表account  
ID balance(余额)  
1 50000.0  
2 0.0

create table account (
     id int primary key,
     balance decimal(12,2) not null
    ); 
    insert into account(id,balance)values(1,50000.0),(2,0.0);

以下两条sql必须作为一个整体执行, 要么都成功，其中有一条失败，前面成功的也得撤销  
update account set balance=balance+10000.0 where id=2; /*2号账户转入10000元*/ 成功  
update account set balance=balance-10000.0 where id=1; /*1号账户转出10000元*/

所谓的事务，就是指一个事务内，多条sql语句是作为一个整体执行的。  
一个事务内的多条sql是作为一个原子操作，不可以被分割。要么都成功，要么都不成功。

start TRANSACTION;  
UPDATE  
UPDATE  
INSERT  
DELETE  
如果这个事务内多条sql全部成功 COMMIT(让更改都生效)  
如果这个事务内有sql失败了，Rollback（让更改都撤销）

事务内所有更改，在结束之前，对于其它用户来讲都是不可见的。  
事务commit提交时，这些更改才会真正生效，其它用户才能看到你的更改。  
事务执行中如果出现意外情况，这时候可以执行rollback，可以撤销事务内所有更改，恢复到事务开始的时刻  
commit 和rollback都意味着事务结束

事务有四大特性  
ACID  
A **原子性**, 指事务内多条sql是作为一个整体执行  
C **一致性**, 事务开始前后，整个数据的状态应当一致  
I **隔离性**, 指事务的隔离级别(未提交读，提交读，可重复读，序列化读)  
1) 脏读(读取到了未提交的数据)  
客户1 客户2  
1 号账户余额 10000.0  
begin;  
update 1 号账户余额50000.0  
select 1 号账户余额 50000.0 脏读  
rollback;  
select 1 号账户余额 50000.0

2)避免脏读现象, 将隔离级别升级为提交读  
查询到的肯定是别人提交后的结果，提交读下不会有脏读, 但会有不可重复读现象:  
客户1 更新 客户2查询  
1 号账户余额 10000.0  
begin;  
select … 10000.0  
begin;  
update 1 号账户余额50000.0;  
commit;  
select … 50000.0  
commit;

1.  为了避免不可重复和脏读的现象，可以将隔离级别升级可重复读（mysql默认隔离级别）
2.  幻读 (可以将隔离级别提高为序列化读，即可避免幻读现象)
    
    大致流程示意图  
    ![在这里插入图片描述][70]
3.  mysql的【可重复读】隔离级别三种现象都可以避免

D **持久性**, 事务中做的更改必须在事务结束后永久生效

增删改查(insert update delete select)  
CRUD  
c insert 插入  
r select 查询  
u update 更新  
d delete 删除

# 7. DCL 数据控制语言 (了解) #

**grant 授权  
revoke 回收权限**

create user 'user1'@'localhost' identified by 'user1';

登录之后执行use test3;  
会报告 Access denied for user ‘user1’ 含义是用户无权访问.

使用root 给user1授权

grant all on test3.* to 'user1'@'localhost';

all 是代表所有权限：select,insert,update,delete…  
test3.\* 是权限的范围：test3库中所有对象  
to 后面跟的是用户

使用root回收权限

revoke all on test3.* from 'user1'@'localhost';