数据库中的多表查询、备份与恢复

╰+攻爆jí腚メ 2022-06-06 13:16 184阅读 0赞

> 数据库：数据库（数据的集合）和数据库管理系统（操作管理数据库）构成，是存储、维护和管理数据的集合

数据的完整性：（=创建表时添加约束）  
1.实体完整性：表示每一行数据不重复  
约束类型： 主键约束（primary key） 唯一约束(unique) 自动增长列(auto\_increment)  
2.域完整性：限制此单元格的数据正确，不对照此列的其它单元格比较  
约束类型：数据类型（int varchar double…） 非空约束（not null） 默认值约束(default) Check约束（mysql不支持） check();  
3.引用完整性（参照完整性）：外键  
外键和主键类型必须一致

> 多表查询

1.合并查询 union，union all

select * from user1 union select * from user2;

结果：user1和user2的交集，去除重复记录

select * from user1 union all select * from user2;

user1和user2的并集，而且不去除重复记录  
*注意：使用union合并查询的两个表必须保证列的类型及列数一致，列名可以不同*  
2.连接查询：就是求出多个表的乘积，然后再根据使用主外键关系做为条件来去除无用信息  
假设现有student表

<table> 
 <thead> 
  <tr> 
   <th>sid</th> 
   <th>name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1001</td> 
   <td>Dream</td> 
  </tr> 
  <tr> 
   <td>1002</td> 
   <td>Mary</td> 
  </tr> 
  <tr> 
   <td>1003</td> 
   <td>Tom</td> 
  </tr> 
  <tr> 
   <td>1004</td> 
   <td>Tony</td> 
  </tr> 
 </tbody> 
</table>

course表

<table> 
 <thead> 
  <tr> 
   <th>cid</th> 
   <th>sid</th> 
   <th>score</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>01</td> 
   <td>1001</td> 
   <td>96</td> 
  </tr> 
  <tr> 
   <td>02</td> 
   <td>1002</td> 
   <td>93</td> 
  </tr> 
  <tr> 
   <td>03</td> 
   <td>1003</td> 
   <td>89</td> 
  </tr> 
  <tr> 
   <td>04</td> 
   <td>1001</td> 
   <td>100</td> 
  </tr> 
  <tr> 
   <td>05</td> 
   <td>1001</td> 
   <td>80</td> 
  </tr> 
 </tbody> 
</table>

2.1内连接查询：\[inner\] join on

select * from student s,course c where s.sid = c.sid;

结果：查询的是学生及其所在班级的信息  
如果用\[inner\] join on，则写法如下：

select * from student s [inner] join course c on s.sid=c.sid;

以上都是内查询，查询的结果是一致的，对于上述两表，查询的结果是不包含1004 Tony的，因为在course表中sid均不是1004，是不符合条件s.sid = c.sid，所以不存在查询结果中  
2.2外连接查询：（查询出的结果存在不满足条件的可能）

*  左外连接查询：left \[outer\] join on 以左边的表作为参照，先查询出左表，然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL

select * from student s left join course c on s.sid = c.sid;

*  右外连接查询：right \[outer\] join on 以右边的表作为参照，先查询出右表，然后查询左表，左表中满足条件的显示出来，不满足条件的显示NULL

select * from student s right join course c on s.sid=c.sid;

*多表查询：N个表连接查询，至少需要N-1个条件*  
3.自然连接查询：  
natural join  
natural left join  
natural right join

select * from student natural join course;
    select * from student natural left join course;
    select * from student natural rigth join course;

使用自然连接查询，无需你去给出主外键等式，它会自动找到这一等式；两张连接的表中名称和类型完全一致的列作为条件！

> 子查询：一个select语句中包含另一个完整的select语句。  
> 子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

1.第一次的查询结果是**单行单列**

//查询工资高于JONES的员工
    select * from emp where sal>(
    select sal from emp where ename = 'JONES');

2.第一次查询结果**多行单列**

//工资高于30号部门的所有人的员工的信息
    select * from emp where sal > ALL (select sal from emp where deptno=30);

//有两个以上直接下属的员工，mgr表示员工的领导编号
    select * from emp where empno IN (select mgr from emp group by mgr having count(mgr)>=2);

3.第一次查询结果**单行多列**

//查询工作和工资与MARTIN完全相同的员工的信息
    select * from emp where (job,sal) IN (select job,sal from emp where ename='MARTIN');

4.第一次查询结果**多行多列**

select * from emp e,(SELECT * from dept) d where e.deptno=d.deptno;
    //上述语句是很鸡肋的写法，和下述语句实现的功能一样，一般直接写下列方式
    select * FROM emp e, dept d where e.deptno = d.deptno

> 自连接查询：自己连接自己，起别名

//求7369员工编号、姓名、经理编号和经理姓名
    SELECT e1.empno , e1.ename,e2.mgr,e2.ename FROM emp e1, emp e2 WHERE e1.mgr = e2.empno AND e1.empno = 7369;

//求各个部门薪水最高的员工的所有信息
    select * frm emp e,(select MAX(sal) maxsal,deptno from emp group by deptno)d where e.deptno=d.deptno and e.sal=d.maxsal;

> 数据备份和恢复

学了好久的数据库，竟然才知道数据库备份的是脚本，而不是数据库里的数据……

*  备份

在控制台使用如下命令实现数据的备份：  
*注意：如果在安装数据库的时候如果已经将windows的环境变量path中添加该目录，那么打开控制台后直接输入命令即可；但是如果没有添加该目录，则需要进入到MySQL目录下的bin文件夹*  
在我的机子上没有配置，所以首先进入该文件夹：  
cd C:\\Program Files\\MySQL\\MySQL Server 5.7\\bin 回车  
备份是不需要登录数据库的，只需要执行下列命令（如图）：  
mysqldump –u用户名 –p 数据库名>生成的脚本文件路径 回车后再输入密码即可！  
![SouthEast][]  
此时查看D盘下会有mydb.sql文件，打开该文件后会发现其中存储的都是脚本，也就是SQL语句！

*  恢复
    
    有两种方式  
    1.登录数据库，需要指明使用的是哪个数据库，使用的数据库必须和备份时所在的数据库名保持一致！  
    ![SouthEast 1][]  
    首先选择数据库，输入命令：use daily\_dream 回车  
    然后输入以下命令实现备份：  
    SOURCE C:\\mydb.sql  
    其中，C:\\mydb.sql是当时备份的存储位置  
    2.无需登录数据库，可输入以下命令进行备份操作：  
    mysql –u用户名 –p密码 数据库<要执行脚本文件路径  
    ![SouthEast 2][]  
    *注意，在执行脚本时需要先行核查当前数据库中的表是否与脚本文件中的语句有冲突！例如在脚本文件中存在create table a的语句，而当前数据库中已经存在了a表，那么就会出错！*

[SouthEast]: /images/20220606/bacb2deb9934430facffa525979cbc29.png
[SouthEast 1]: /images/20220606/2e3ed8dbe8194aaea02638405d03353f.png
[SouthEast 2]: /images/20220606/5511b45a6e134662a421e1b112cf4758.png