MySQL多表联查

一、多表查询

1.表与表之间的关系

一对一

​ 通过嵌套的方式

一对多【多对一】

​ 添加外键

多对多

​ 单独创建一张新的表

2.合并结果集

作用：将两个select语句的查询结果合并到一起

两种方式：

​ union：去除重复记录【并集】

​ union all；获取所有的结果

演示：

#创建表

mysql> create table A(

         name varchar(10),

         score int

      );

​

mysql> create table B( 

        name varchar(10), 

        score int 

      );

​

#批量插入数据

mysql> insert into A values('a',10),('b',20),('c',30);

mysql> insert into B values('a',10),('d',40),('c',30);

​

#查询结果

mysql> select * from A;

+------+-------+

| name | score |

+------+-------+

| a    |    10 |

| b    |    20 |

| c    |    30 |

+------+-------+

​

mysql> select * from B;

+------+-------+

| name | score |

+------+-------+

| a    |    10 |

| d    |    40 |

| c    |    30 |

+------+-------+

​

#合并结果集

mysql> select * from A

-> union

 -> select * from B;

 +------+-------+

| name | score |

+------+-------+

| a    |    10 |

| b    |    20 |

| c    |    30 |

| d    |    40 |

+------+-------+

​

mysql> select * from A

-> union all

 -> select * from B;

 +------+-------+

| name | score |

+------+-------+

| a    |    10 |

| b    |    20 |

| c    |    30 |

| a    |    10 |

| d    |    40 |

| c    |    30 |

+------+-------+

​

注意：被合并的两个结果，列数、列类型必须相同

如果遇到列数不相同的情况，如下的解决办法：

mysql> insert into C values('a',10,29),('e',20,45),('c',30,10);

​

mysql> select * from A

-> union 

 -> select name,score from C;

+------+-------+

| name | score |

+------+-------+

| a    |    10 |

| b    |    20 |

| c    |    30 |

| e    |    20 |

+------+-------+

​

3.连接查询

作用：求出多个表的乘积，例如t1和t2，如果采用了连接查询，得到的结果是t1*t2

演示：

mysql> select * from student,score;

+-------+----------+-------+-------+----------+

| stuid | stuname  | stuid | score | courseid |

+-------+----------+-------+-------+----------+

| 1001  | zhangsan | 1001  |    98 |        1 |

| 1002  | lisi     | 1001  |    98 |        1 |

| 1003  | jack     | 1001  |    98 |        1 |

| 1004  | tom      | 1001  |    98 |        1 |

| 1001  | zhangsan | 1002  |    80 |        2 |

| 1002  | lisi     | 1002  |    80 |        2 |

| 1003  | jack     | 1002  |    80 |        2 |

| 1004  | tom      | 1002  |    80 |        2 |

| 1001  | zhangsan | 1003  |    70 |        1 |

| 1002  | lisi     | 1003  |    70 |        1 |

| 1003  | jack     | 1003  |    70 |        1 |

| 1004  | tom      | 1003  |    70 |        1 |

| 1001  | zhangsan | 1004  |    60 |        2 |

| 1002  | lisi     | 1004  |    60 |        2 |

| 1003  | jack     | 1004  |    60 |        2 |

| 1004  | tom      | 1004  |    60 |        2 |

| 1001  | zhangsan | 1002  |    75 |        3 |

| 1002  | lisi     | 1002  |    75 |        3 |

| 1003  | jack     | 1002  |    75 |        3 |

| 1004  | tom      | 1002  |    75 |        3 |

+-------+----------+-------+-------+----------+

20 rows in set (0.01 sec)

​

#问题：进行连接查询，会产生笛卡尔积

#笛卡尔积：两个集合相乘的结果

#解释：假设集合A={a,b},集合B={0,1,2},则笛卡尔积的结果{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}

​

#解决办法：在实际应用中，需要去除重复记录，则需要通过条件进行过滤

mysql> select s.stuid,s.stuname,c.score,c.courseid from student s,score c where s.stuid=c.stuid;

+-------+----------+-------+----------+

| stuid | stuname  | score | courseid |

+-------+----------+-------+----------+

| 1001  | zhangsan |    98 |        1 |

| 1002  | lisi     |    80 |        2 |

| 1003  | jack     |    70 |        1 |

| 1004  | tom      |    60 |        2 |

| 1002  | lisi     |    75 |        3 |

+-------+----------+-------+----------+

​

3.1内连接-inner join on

内连接的特点：查询结果必须满足条件

演示：

#内连接

mysql> select s.stuid,s.stuname,c.score,c.courseid  from student s join score c on s.stuid=c.stuid;

+-------+----------+-------+----------+

| stuid | stuname  | score | courseid |

+-------+----------+-------+----------+

| 1001  | zhangsan |    98 |        1 |

| 1002  | lisi     |    80 |        2 |

| 1003  | jack     |    70 |        1 |

| 1004  | tom      |    60 |        2 |

| 1002  | lisi     |    75 |        3 |

+-------+----------+-------+----------+

​

#等价写法

mysql> select  s.stuid,s.stuname,c.score,c.courseid from student s,score c where s.stuid=c.stuid;

+-------+----------+-------+----------+

| stuid | stuname  | score | courseid |

+-------+----------+-------+----------+

| 1001  | zhangsan |    98 |        1 |

| 1002  | lisi     |    80 |        2 |

| 1003  | jack     |    70 |        1 |

| 1004  | tom      |    60 |        2 |

| 1002  | lisi     |    75 |        3 |

+-------+----------+-------+----------+

​

#练习：查询成绩大于80的学生记录

#方式一

mysql> select  s.stuid,s.stuname,c.score,c.courseid from student s,score c where s.stuid=c.stuid and c.score>80;

+-------+----------+-------+----------+

| stuid | stuname  | score | courseid |

+-------+----------+-------+----------+

| 1001  | zhangsan |    98 |        1 |

| 1002  | lisi     |    80 |        2 |

| 1002  | lisi     |    75 |        3 |

+-------+----------+-------+----------+

​

#方式二

#也是内连接，只不过相当于是方言，join on相当于是普通话

mysql> select s.stuid,s.stuname,c.score,c.courseid  from student s join score c on s.stuid=c.stuid and score>70;

+-------+----------+-------+----------+

| stuid | stuname  | score | courseid |

+-------+----------+-------+----------+

| 1001  | zhangsan |    98 |        1 |

| 1002  | lisi     |    80 |        2 |

| 1002  | lisi     |    75 |        3 |

+-------+----------+-------+----------+

​

#方式三

mysql> select s.stuid,s.stuname,c.score,c.courseid  from student s join score c on s.stuid=c.stuid where score>70;

+-------+----------+-------+----------+

| stuid | stuname  | score | courseid |

+-------+----------+-------+----------+

| 1001  | zhangsan |    98 |        1 |

| 1002  | lisi     |    80 |        2 |

| 1002  | lisi     |    75 |        3 |

+-------+----------+-------+----------+

​

3.2外连接-outer join on

特点：以其中一个表作为参照连接另外一个表

分类：

​ 左外连接：left join on 以左表为主,会把左表的数据全部查询出来, 会把符合条件的右表数据查询出来

​ 右外连接：right join on 以右表为主,会把右表的数据全部查询出来, 会把符合条件的左表数据查询出来

演示：

-- -- 部门表

create table dept(

    deptno int primary key auto_increment, -- 部门编号

    dname varchar(14) ,   -- 部门名字

    loc varchar(13)   -- 地址

) ;

-- -- 员工表

create table emp(

     empno int primary key auto_increment,-- 员工编号

     ename varchar(10), -- 员工姓名                                     -

     job varchar(9),    -- 岗位

     mgr int,    -- 直接领导编号

     hiredate date, -- 雇佣日期，入职日期

     sal int, -- 薪水/工资

     comm int,  -- 提成

     deptno int not null, -- 部门编号

     foreign key (deptno) references dept(deptno));

​

insert into dept values(10,'财务部','北京');

insert into dept values(20,'研发部','上海');

insert into dept values(30,'销售部','广州');

insert into dept values(40,'行政部','深圳');

insert into dept values(50,'法务部','北京');

insert into dept values(60,'部','上海');

insert into dept values(70,'销售部','广州');

insert into dept values(80,'行政部','深圳');

insert into emp values(7369,'刘一','职员',7902,'1980-12-17',800,null,20);

insert into emp values(7499,'陈二','推销员',7698,'1981-02-20',1600,300,30);

insert into emp values(7521,'张三','推销员',7698,'1981-02-22',1250,500,30);

insert into emp values(7566,'李四','经理',7839,'1981-04-02',2975,null,20);

insert into emp values(7654,'王五','推销员',7698,'1981-09-28',1250,1400,30);

insert into emp values(7698,'赵六','经理',7839,'1981-05-01',2850,null,30);

insert into emp values(7782,'孙七','经理',7839,'1981-06-09',2450,null,10);

insert into emp values(7788,'周八','分析师',7566,'1987-06-13',3000,null,20);

insert into emp values(7839,'吴九','总裁',null,'1981-11-17',5000,null,10);

insert into emp values(7844,'郑十','推销员',7698,'1981-09-08',1500,0,30);

insert into emp values(7876,'郭十一','职员',7788,'1987-06-13',1100,null,20);

insert into emp values(7900,'钱多多','职员',7698,'1981-12-03',950,null,30);

insert into emp values(7902,'大锦鲤','分析师',7566,'1981-12-03',3000,null,20);

insert into emp values(7934,'木有钱','职员',7782,'1983-01-23',1300,null,10);

​

​

#左外连接

mysql> select e.ename,e.sal,d.deptno  from emp e left join dept d on e.deptno=d.deptno;

​

+--------+------+--------+

| ename  | sal  | deptno |

+--------+------+--------+

| 刘一   |  800 |     20 |

| 陈二   | 1600 |     30 |

| 张三   | 1250 |     30 |

| 李四   | 2975 |     20 |

| 王五   | 1250 |     30 |

| 赵六   | 2850 |     30 |

| 孙七   | 2450 |     10 |

| 周八   | 3000 |     20 |

| 吴九   | 5000 |     10 |

| 郑十   | 1500 |     30 |

| 郭十一 | 1100 |     20 |

| 钱多多 |  950 |     30 |

| 大锦鲤 | 3000 |     20 |

| 木有钱 | 1300 |     10 |

+--------+------+--------+

​

#内连接

mysql> select s.stuid,s.stuname,c.score,c.courseid  from student s join score c on s.stuid=c.stuid;

+-------+----------+-------+----------+

| stuid | stuname  | score | courseid |

+-------+----------+-------+----------+

| 1001  | zhangsan |    98 |        1 |

| 1002  | lisi     |    80 |        2 |

| 1003  | jack     |    70 |        1 |

| 1004  | tom      |    60 |        2 |

| 1002  | lisi     |    75 |        3 |

+-------+----------+-------+----------+

​

#右外连接

#参照为c

mysql> select e.ename,e.sal,d.deptno  from emp e right join dept d on e.deptno=d.deptno;

+--------+------+--------+

| ename  | sal  | deptno |

+--------+------+--------+

| 孙七   | 2450 |     10 |

| 吴九   | 5000 |     10 |

| 木有钱 | 1300 |     10 |

| 刘一   |  800 |     20 |

| 李四   | 2975 |     20 |

| 周八   | 3000 |     20 |

| 郭十一 | 1100 |     20 |

| 大锦鲤 | 3000 |     20 |

| 陈二   | 1600 |     30 |

| 张三   | 1250 |     30 |

| 王五   | 1250 |     30 |

| 赵六   | 2850 |     30 |

| 郑十   | 1500 |     30 |

| 钱多多 |  950 |     30 |

| NULL   | NULL |     40 |

| NULL   | NULL |     50 |

| NULL   | NULL |     60 |

| NULL   | NULL |     70 |

| NULL   | NULL |     80 |

+--------+------+--------+

​

4.子查询

在一个select语句中包含另外一个完整的select语句【select语句的嵌套】

注意：

​ a.子查询出现的位置：

​ from后

​ where子句的后面，作为条件的一部分被查询

​ b。当子查询出现在where后面作为条件时，可以使用关键字：any、all

​ c.子查询结果集的形式

​ 单行单列

​ 单行多列

​ 多行多列

​ 多行单列

演示：

#1.查询和刘一在同一个部门的员工

#思路：先查询刘一所在的部门，然后根据部门查找所有的信息

mysql> select deptno from emp where enname='刘一';

+--------+

| deptno |

+--------+

|     20 |

+--------+

​

mysql> select * from emp where deptno=(select deptno from emp where ename='刘一');

+-------+--------+--------+------+------------+------+------+--------+

| empno | ename  | job    | mgr  | hiredate   | sal  | comm | deptno |

+-------+--------+--------+------+------------+------+------+--------+

|  7369 | 刘一   | 职员   | 7902 | 1980-12-17 |  800 | NULL |     20 |

|  7566 | 李四   | 经理   | 7839 | 1981-04-02 | 2975 | NULL |     20 |

|  7788 | 周八   | 分析师 | 7566 | 1987-06-13 | 3000 | NULL |     20 |

|  7876 | 郭十一 | 职员   | 7788 | 1987-06-13 | 1100 | NULL |     20 |

|  7902 | 大锦鲤 | 分析师 | 7566 | 1981-12-03 | 3000 | NULL |     20 |

+-------+--------+--------+------+------------+------+------+--------+

​

#2.查询工资高于王五的员工信息

#思路：先查询王五的工资，然后根据结果查询其他的员工信息

mysql> select ename,sal from emp where sal > (select sal from emp where ename = "王五");

​

+--------+------+

| ename  | sal  |

+--------+------+

| 陈二   | 1600 |

| 李四   | 2975 |

| 赵六   | 2850 |

| 孙七   | 2450 |

| 周八   | 3000 |

| 吴九   | 5000 |

| 郑十   | 1500 |

| 大锦鲤 | 3000 |

| 木有钱 | 1300 |

+--------+------+

​

#3.查询工资高于30号部门所有人的员工信息

#思路：先查询30号部门中的最高工资，根据最高工资查询其他的员工信息

mysql> select * from emp where deptno=30;

+-------+--------+--------+------+------------+------+------+--------+

| empno | ename  | job    | mgr  | hiredate   | sal  | comm | deptno |

+-------+--------+--------+------+------------+------+------+--------+

|  7499 | 陈二   | 推销员 | 7698 | 1981-02-20 | 1600 |  300 |     30 |

|  7521 | 张三   | 推销员 | 7698 | 1981-02-22 | 1250 |  500 |     30 |

|  7654 | 王五   | 推销员 | 7698 | 1981-09-28 | 1250 | 1400 |     30 |

|  7698 | 赵六   | 经理   | 7839 | 1981-05-01 | 2850 | NULL |     30 |

|  7844 | 郑十   | 推销员 | 7698 | 1981-09-08 | 1500 |    0 |     30 |

|  7900 | 钱多多 | 职员   | 7698 | 1981-12-03 |  950 | NULL |     30 |

+-------+--------+--------+------+------------+------+------+--------+

​

mysql> select max(sal) from emp where deptno=30;

+----------+

| max(sal) |

+----------+

|     2850 |

+----------+

​

mysql> select * from emp where sal>(select max(sal) from emp where deptno=30);

+-------+--------+--------+------+------------+------+------+--------+

| empno | ename  | job    | mgr  | hiredate   | sal  | comm | deptno |

+-------+--------+--------+------+------------+------+------+--------+

|  7566 | 李四   | 经理   | 7839 | 1981-04-02 | 2975 | NULL |     20 |

|  7788 | 周八   | 分析师 | 7566 | 1987-06-13 | 3000 | NULL |     20 |

|  7839 | 吴九   | 总裁   | NULL | 1981-11-17 | 5000 | NULL |     10 |

|  7902 | 大锦鲤 | 分析师 | 7566 | 1981-12-03 | 3000 | NULL |     20 |

+-------+--------+--------+------+------------+------+------+--------+

​

#4.查询工作类型和工资与张三完全相同的员工信息

#思路：先查询张三的工作类型和工资，然后再查询其他的员工信息

mysql> select * from emp where (job,sal) in (select job,sal from emp where ename='张三');

​

+-------+-------+--------+------+------------+------+------+--------+

| empno | ename | job    | mgr  | hiredate   | sal  | comm | deptno |

+-------+-------+--------+------+------------+------+------+--------+

|  7521 | 张三  | 推销员 | 7698 | 1981-02-22 | 1250 |  500 |     30 |

|  7654 | 王五  | 推销员 | 7698 | 1981-09-28 | 1250 | 1400 |     30 |

+-------+-------+--------+------+------------+------+------+--------+

二、数据库的备份和恢复

1.备份

生成SQL脚本，导出数据

命令：mysqldump -u root -p 数据库名>生成sql脚本的路径

注意：

​ 可以不需要登录数据库

​ windows系统: 使用管理员权限打开CMD

演示：

C:\Users\chenbingjie> mysqldump -u root -p demo2105 > C:/Users/chenbingjie/Desktop/school.sql

Enter password: 

2.恢复

执行sql脚本，恢复数据

前提：必须先创建数据库【空的】

注意：需要先登录数据库，然后进入指定的数据库，执行sql脚本

演示：

C:\Users\chenbingjie> mysql -u root -p

Enter password: 

​

mysql> create database test;

​

mysql> use test;

mysql> show tables;

mysql> source C:/Users/chenbingjie/Desktop/school.sql;

三. 索引

1.索引介绍

索引看着挺高大上的一个名字，说白了就是我们一本书最前面的目录。

​

假如你用新华字典来查找“张”这个汉字，不使用目录的话，你可能要从新华字典的第一页找到最后一页，可能要花二个小时。字典越厚呢，你花的时间就越多。现在你使用目录来查找“张”这个汉字，张的首字母是z，z开头的汉字从900多页开始，有了这条线索，你查找一个汉字可能只要一分钟，由此可见索引的重要性。

select * from student where name="张";

​

索引用于快速找出在某个列中有一特定值的行。

​

不使用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要看所有数据。

​

当然索引也不易过多，索引越多写入，修改的速度越慢。因为，写入修改数据时，也要修改索引。

​

MySQL中，所有数据类型的列都可以被索引，常用的存储引擎InnoDB和MyISAM能支持每个表创建16个索引。InnoDB和MyISAM使用的索引其底层算法是B-tree（B树），B-tree是一种自平衡的树，类似于平衡二叉排序树，能够保持数据有序。这种数据结构能够让查找数据、顺序访问、插入数据及删除的操作都在对数时间内完成。

2.索引分类

索引类型	功能说明
普通索引	最基本的索引，它没有任何限制
唯一索引	某一列启用了唯一索引则不准许这一列的行数据中有重复的值。针对这一列的每一行数据都要求是唯一的 unique
主键索引	它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引，常用于用户ID。类似于书中的页码 primary key
全文索引	对于需要全局搜索的数据，进行全文索引

3.查看索引

基本语法：

    show index from tablename;

示例：

    show index from user\G;

示例说明：

    查看user表的索引结构

4.普通索引

基本语法：

    alter table 表 add index(字段)

示例：

    alter table money add index(username);

示例说明：

    为money表的username字段增加索引

5.唯一索引

基本语法：

    alter table 表 add unique(字段)

示例：

    alter table money add unique(email);

示例说明：

    为money表的email字段增加唯一索引

6.主键索引

基本语法：

    alter table 表 add primary key(字段)

示例：

    alter table money add primary key(id);

示例说明：

    为money表的id字段增加主键索引

7.创建表时声明索引

创建表时可在创建表语句后加上对应的类型即可声明索引：

​

primary key(字段) 

index (字段)

unique (字段)

​

create table user (

    id int auto_increment,

    name varchar(20),

    primary key(id),

    unique (name)

);

​

8.删除索引

基本语法：

    ALTER TABLE table_name DROP INDEX index_name

示例：

    alter table money drop index age;

示例说明：

    为money表删除age索引

四. 事务

1.事务控制语言(DTL)

我们每执行一条SQL语句,每执行一组SQL语句,我们都可以称为事务

如果一组SQL语句里,某一个SQL语句失败了,称为整个事务的失败,因此出现这种情况必须要恢复到正常的情况上才能没有问题.

在逛淘宝的时候，购买了某一个货品钱付了,但淘宝服务器刚好断电了,商家没办法收到你的钱,就不发货。 担保交易银行中转帐的时候, 款已经扣除了,但突然断电,导致对方收不到钱.

​

如果有了事务，就可以避免该事情。

事务可以看作是一个“容器”，将多条语句，放入该容器，最后，只要一个命令行，来决定其中的所有语句是否“执行” 

2.事务的四大特征

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

    事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

    事务用来管理 insert,update,delete 语句

​

一般来说，事务是必须满足4个条件（ACID）：

        原子性（Atomicity） 事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行

        一致性（Consistency）事务应确保数据库的状态从一个一致状态转变为另一个一致状态

        隔离性（Isolation）多个事务并发执行时，一个事务的执行不应影响其他事务的执行

        持久性（Durability）已被提交的事务对数据库的修改应该永久保存在数据库中

​

3.事务的原理

传统的情况：

事务的机制:

4.事务模式

在mysql中默认一条sql语句一个事务

因此,如果需要开启事务模式的话

使用 start transaction开头,开启事务模式

使用 commit 语句执行后,才能真正生效

使用 rollback 语句进行回滚

5.事务的流程

执行语句	说明
start transaction	执行开始事务命令后,下面将进入事务模式.
update、delete、insert 操作	在事务执行后,这些操作只在内存状态下进行,而不是在物理状态下
commit	在事务执行完成后,确认执行无误且成功,就可以使用commit把内存中执行的结果,提交到物理内存中
rollback	如果在事务执行过程中,发生了错误,则可以使用rollback命令回滚到上一个事务操作

6.事务的使用