Mysql-多表操作

目录

mysql执行sql顺序

多表关系

外键约束

多表联合查询

子查询

表自联

---

mysql执行sql顺序

1、FROM  table1 left join table2 on 将table1和table2中的数据产生笛卡尔积，生成Temp1

2、JOIN table2  所以先是确定表，再确定关联条件

3、ON table1.column = table2.columu 确定表的绑定条件 由Temp1产生中间表Temp2

4、WHERE  对中间表Temp2产生的结果进行过滤  产生中间表Temp3

5、GROUP BY 对中间表Temp3进行分组，产生中间表Temp4

6、HAVING  对分组后的记录进行聚合 产生中间表Temp5

7、SELECT  对中间表Temp5进行列筛选，产生中间表 Temp6

8、DISTINCT 对中间表 Temp6进行去重，产生中间表 Temp7

9、ORDER BY 对Temp7中的数据进行排序，产生中间表Temp8

10、LIMIT 对中间表Temp8进行分页，产生中间表Temp9

多表关系

• 一对一关系

  • 一张身份张对应一个人

  • 在任一表中增加唯一外键,指向另一主键,确保一对一关系

  • 一般一对一关系很少见,遇到一对一关系最好是合并表

• 一对多/多对一关系

  • 一个部分有多个员工,一个员工只能对应一个部门

  • 在多的一方建立外键,指向一的一方的主键

• 多对多关系

  • 一个学生可以选择很多课程,一个课程也可以被很多学生选择

  • 原则:多对多关系需要借助于第三张表.中间表至少包含两个字段,将多对多的关系,拆成一对多的关系,中间表至少需要两个外键,这两个外键分别指向原来的那两张表的主键

外键约束

• 介绍

  Mysql外键约束(FOREIGN KEY)是表的一个特殊手段,经常与主键约束一起使用.对于两个具有关联关系的表而言,相关联字段中主键所在的表就是主表(父表),外键所在的表就是从表(子表).

  外键用来建立主表与从表的关联关系,为两个表的数据建立连接,约束两个表中数据的一致性和完整性.

• 特点:定义一个外键时,需要遵守以下规则:

  • 主表必须存在于数据库中,或者是当前正在创建的表

  • 必须为主表定义主键

  • 主键不能包含空值,但允许在外键中出现空值.也就是说,只要外键的每个非空值出现在指定的主键中,这个外键的内容就是正确的

  • 在主表的表名后面指定列名或者列名的组合.这个列或者列的组合必须是主表的主键或者候选键

  • 外键中列的数目必须和主表的主键中列的数目相同(主键可能是一个组合列,外键中也必须是这样的一个组合列)

  • 外键中列的数据类型必须和主表主键中对应列的数据类型一致

• 操作-创建外键约束

  • 方式1-在创建表时设置外键约束

    在create table语句中,通过foreign key关键字来指定外键,具体的语法格式如下:

[constraint <外键名>] foreign key 字段名 [,字段名,...] references <主表名> 主键列1 [,主键列2,...]

• 主表

•  从表

• 方式2-在创建表后修改表结构设置外键约束

  • 前提:从表中外键列中的数据必须与主表中外键列中的数据一致2或者是没有数据

alter table <数据(从)表名> add constraint <外键名> foreign key (<列名>) references <主表名> (<列名>);

• 操作-在外键约束下的数据操作

  • 插入数据操作

    • 注意给从表添加数据时,外键列的值不能随便写,必须依赖

  • 删除数据操作

    • 主表的数据被从表依赖时,不能删除,否则可以删除;从表的数据可以随便删除;

  • 删除外键约束(当一个表中不需要外键约束时,就需要从表中将其删除.外键一旦删除,就会解除主表和从表间的关联关系)

    • alter table <从表名> drop foreign key <外键约束名>

alter table emp drop foreign key emp_fk

• 外键约束-多对多关系

  • 在多对多关系表中,A表的一行对应B的多行,B表的一行对应A表的多行,,我们需要增加一个中间表,来建立多对都关系.

•  外键约束需要建立2次

• 添加数据时sid和cid在各自的主表中必须存在

• 删除数据时中间表可以随便删除,但是两边的主表受从表依赖的数据不能删除或者修改

多表联合查询

多表查询就是同时查询两个或者两个以上的表,因为有的时候用户在查看数据的时候,需要显示的数据来自多张表,多表查询有以下分类:

• 交叉连接查询[产生笛卡儿积]

  • 一张表的每一行与另外的一张表的任意一行进行匹配

  • 假如A表中有M行数据,B表中有N行数据,返回M*N行数据

• 内连接查询[使用的关键字inner join -]

• 外连接查询[outer join]
  • 左外连接:left join
  • 右外连接:right join

 

•  满连接: mysql中可以使用 union 左外union右外(可以去重);左外union all右外(不可以去重,会有重复的数据)

子查询

• 在一个完整的查询语句中,嵌套若干个不同功能的小查询,从而一起完成复杂查询的一种编写形式,通俗一点就是包含select嵌套的查询

• 单行单列:返回的是一个具体列的内容,可以理解为一个单值数据

• 单行多:返回的是一行数据中多个列的内容

• 多行单列:返回多行记录之中同一列的内容,相当于给出了一个操作范围

• 多行多列:查询返回的结果是一张临时表(作为from来源)

• 子查询关键字

  • ALL

    与子查询返回的所有值比较为true,则返回true

    ALL可以与=,>,>=,<,<=,<>结合使用,分别表示......其中所有的数据

    select ... from...where c > all(查询语句)

    不用再去找子查询中的最大值,最小值,某个范围

  • ANY

    select ... from...where c > any(查询语句)

    与子查询返回的任何值比较为true,则返回true

    ALL可以与=,>,>=,<,<=,<>结合使用,分别表示......其中的任何一个数据

    SOME和ANY的作用一样,SOME可以理解为ANY的别名

  • SOME

    同ANY

  • IN

    • select ... from... where c in (查询语句)

    • IN关键字,用于判断某个记录的值,是否在指定1的集合中

    • 在IN关键字前边加上not可以将条件反过来

  • EXISTS

    • select ... from... where exists (查询语句)

    • 该子查询如果"有数据结果"(至少返回一行数据),则该EXISTS()的结果为"true",外层查询执行

    • 该子查询如果"没有数据结果"(没有任何数据返回),则该EXISTS()的结果为"false",外层查询不执行

    • EXISTS后面的子查询不返回任何实际数据,只返回真或者假,当返回真时where条件成立

    • 注意,EXISTS关键字,比IN关键字的运算效率高,因此,在实际开发中,特别是大数据量时,推荐使用EXISTS关键字

//查询公司是否有大于60岁的员工,有则输出(查询一行数据时先去判断后面的exists是否成立)
//查询第一行时,拿第一行的年龄去判断;查询第二行时,拿第二行的年龄去判断

select * from emp a where exists(select *  from emp where a.age > 60)

//用in来替代

select * from emp a where eid in (select eid  from emp where a.age > 60)




//查询有所属部门的员工信息(查询第一行时,判断第一行的dept_id是否在dept3表中是否存在)

select * from emp a where exists (select * from dept b where  a.dept_id = b.deptno)

用in来替换

select * from emp a where dept_id in (select deptno from dept b where  a.dept_id = b.deptno)

表自联

• 概念

  • Mysql有时在信息查询时需要进行对表自身进行关联查询,即一张表自己和自己关联,一张表当成多张表来用,注意自关联时必须给表起别名      

select * from 表1 a left join 表1 b on 条件

• 操作

//查询每个三国任务及他的上级信息
select a.ename,b.ename from t_sanguo a join t_sanguo b on a.mananger_id = b.eid;(该结果不包含刘协)

//查询所有任务及上级
select a.ename,b.ename from t_sanguo a left join t_sanguo b on a.mananger_id = b.eid;(该结果包含刘协)

//查询所有人物,上级,上上级
select a.ename,b.ename,c,ename
  from t_sanguo a 
  left join t_sanguo b on a.mananger_id = b.eid
  left join t_sanguo c on b.mananger_id = c.eid