【Mysql】mysql学习之旅05-多表操作

mysql学习之旅05-多表操作

1. 创建多表

常见关系：

一对一：一夫一妻
一对多：用户和订单，分类和商品
多对多：订单和商品，老师和学生

E-R图可以描述实体与实体之间的关系

矩形表示实体
椭圆表示属性
菱形表示关系
推荐在PowerDesigner软件中使用

一对多：

在开发中，我们将一方称之为主或一表，多方称之为从表或多表，
为了表示一对多的关系，一般会在从表中加一个字段，字段名称自定义（建议：主表名称_id）
为保证数据的有效性和一致性，在多表添加外键约束

多对多：

在开发中，我们一般引入一张中间表，在中间表中存放两张表的主键，
一般还会把这两个主键设置成中间表的联合主键，将多对多转换成两个一对多，
为了保证数据的有效性和一致性，在中间表添加两个外键约束

2. 多表查询

内连接([inner] join):查询两个表公共的部分：

select * from 表1 as t1,表2 as t2 where t1.主键=t2.外键 and [条件];

select * from 表1 as t1 [inner] join 表2 as t2 on t1.主键=t2.外键 and [条件];

外连接

1. 左外连接(left [outer] join):以左表为主，右表为从，右表中 没有对应数据为null

SELECT * FROM 主表t1 LEFT JOIN 从表t2 ON t1.主键=t2.外键 and [条件];

2. 右外连接(right join):以右表为主，左表为从，左表中没有对应数据为null

select * from 从表t1 right  join 主表t2 on t1.主键=t2.外键 and [条件];

3. 全连接(full join):没有主次关系，显示全部内容，mysql不支持

select * from 表t1 full join 表t2 on t1.主键=t2.外键 and [条件];

4. 交叉表连接(cross join): 笛卡尔积，两表数据的乘法

select * from 表1 cross join 表2；

5. 关联连接(union):多个表查询结果的关联，对查询结果进行堆积，列数要相同，数据类型可以转换

select 列1，列2 from 表1 union select 列1，列2 from 表2；

3. 子查询

概念：

sql语句中嵌套sql语句，不仅仅是查询，可以出现在sql语句的任何地方。
可以操作的sql语句有：insert update delete select
子查询可以用在单表操作，也可以用在多表操作中。

形式：

1. select后面：子查询的结果必须是一行一列

select (select count(*) from employee) as count1, 
(select count(*) from salary) as count2;

2. from 后面：多行多列，必须有别名，是一个虚表/临时表，作为后续的过滤条件。

select * from (select id, name, sex, age from employee) as t1;

3. where后面：只能有一个结果，除非使用any和all

select * from employee where id >= (select id from employee where age = 38);

4. in后面

select * from employee where id in (select distinct eid from salary);

5. exists后面：只要只查询有一条以上的记录（与内容无关），就会执行父查询

select * from employee as t1 where exists(select distinct eid from salary as t2 where t1.id = t2.eid);

6. insert值里面
7. update set/where后面
8. delete from/where后面

特别注意：

update/delete和子查询不能使用一张表，需要将子查询结果创建为临时表，再嵌套查询