MySQL多表操作：建表与多表查询

深碍√TFBOYSˉ_ 2022-01-20 23:55 323阅读 0赞

### 目录 ###

*  多表间的建表原则
 *   *  一对多
     *  多对多
     *  一对一
 *  实例演示多表查询
 *   *  数据准备
     *  多表查询
     *   *  笛卡尔积
         *  内连接
         *   *  隐式内连接
             *  显式内连接
         *  外连接
         *   *  左外连接
             *  右外连接
         *  子查询
         *   *  子查询的多种情况

# 多表间的建表原则 #

## 一对多 ##

如**分类与商品**是一对多的关系（一个分类对应多个商品）

**建表原则**：在“多”的一方添加一个外键，指向“一”的一方的主键

## 多对多 ##

如老师和学生，学生和课程  
**建表原则**：多建一张中间表，将多对多的关系拆成一对多关系，中间表至少要有两个字段，作为中间表的外键：这两个外键分别指向两张表的主键。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70]

## 一对一 ##

如班级和班长，公民和身份证，国家和国旗  
**建表原则**：

*  将一对一的情况当成一对多情况处理，在任意一张表添加一个外键，并且这个外键要唯一，指向另外一张表
 *  将两张表合并成一张表
 *  将两张表的主键建立起连接，让两张表里面主键相等  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 1]

# 实例演示多表查询 #

网上商城表实例分析：用户购物流程

## 数据准备 ##

以一个实例来展示多表间的操作。  
各个表之间的关系如下图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 2]  
下面需要进行数据准备，也就是建表与插入数据。

*  用户表 (用户的ID,用户名,密码,手机)

create table user(
    	uid int primary key auto_increment,
      	username varchar(31),
      	password varchar(31),
      	phone  varchar(11)
    );
    
    insert into user values(1,'zhangsan','123','13811118888');

*  订单表(订单编号,总价,订单时间 ,地址,外键-用户的ID)

create table orders(
      	oid int primary key auto_increment,
        	sum int not null,
          otime timestamp not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        	address varchar(100),
        	uno int,
        	foreign key(uno) references user(uid)
      );
      insert into orders (oid,sum,address,uno) values(1,200,'中科大666号',1);
      insert into orders (oid,sum,address,uno) values(2,250,'苏大199号',1);

*  商品表(商品ID, 商品名称,商品价格,外键cno)

create table product(
    	pid int primary key auto_increment,
    	pname varchar(10),
    	price double,
    	pdate timestamp not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    	cno int，
    	foreign key(cno) references category(cid)
    );
    insert into product (pid,pname,price,cno)values(null,'华为p30',4999,1);
    insert into product (pid,pname,price,cno)values(null,'红米note4',1999,1);
    insert into product (pid,pname,price,cno)values(null,'耐克',499,2);
    insert into product (pid,pname,price,cno)values(null,'老村长',88,3);
    insert into product (pid,pname,price,cno)values(null,'劲酒',35,3);
    insert into product (pid,pname,price,cno)values(null,'小熊饼干',4,4);
    insert into product (pid,pname,price,cno)values(null,'卫龙辣条',2,5);
    insert into product (pid,pname,price,cno)values(null,'旺旺雪饼',3,5);

*  订单项（(订单ID,商品ID,商品数量,订单项总价)  
    这个表就相当于订单表与商品表之间的中间表，将多对多的关系转换成一对多的关系。

create table orderitem(
    	ono int,
      	pno int,
      	foreign key(ono) references orders(oid),
      	foreign key(pno) references product(pid),
      	ocount int,
      	subsum double
    );
    --给1号订单添加商品 200块钱的商品
    insert into orderitem values(1,7,100,100);
    insert into orderitem values(1,8,101,100);
    
    --给2号订单添加商品 250块钱的商品 ()
    aainsert into orderitem values(2,5,1,35);
    insert into orderitem values(2,3,3,99);

## 多表查询 ##

需求分析：  
在我们的商城案例中,我的订单中包含很多信息，打开我的订单需要去查询表

### 笛卡尔积 ###

一种交叉连接查询 。  
现在这两张表长这样：

select * from product;
    select * from category;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 3]  
**笛卡尔积**，查出来的是两张表的乘积（10 x 6 X 8），这样的结果是没有意义的

select * from product,category;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 4]  
所以，要进行过滤（符合条件的有8行，因此结果为8 X 8）：

select * from product,category where cno=cid;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 5]  
为了提高语句可读性，可以采用别名：

select * from product as p,category as c where p.cno=c.cid;

省略as后:

select * from product p,category c where p.cno=c.cid;

其实这种写法就是下面的“隐式内连接”。

### 内连接 ###

应用场景:  
![在这里插入图片描述][20200407112915813.png]  
这种场景下得到的是满足某一条件的A，B内部的数据；正因为得到的是内部共有数据，所以连接方式称为内连接。

#### 隐式内连接 ####

select * from product p,category c where p.cno=c.cid;

#### 显式内连接 ####

select * from product p inner join category c on p.cno=c.cid;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 6]  
查询结果和隐式内连接是一样的。  
（3）区别  
隐式：在查询出结果的基础上去做where条件过滤  
显示：带着条件查询结果，效率比较高

### 外连接 ###

#### 左外连接 ####

应用场景:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 7]  
这种场景下得到的是A的所有数据，和满足某一条件的B的数据;  
左外连接会将左表中的所有数据都查询出来，如果右表中没有对应的数据，用null代替.  
先看两张表（10 X 5） (6 X 3)：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 3]  
左外连接（10 X 8）：

select * from product p left outer join category c on p.cno=c.cid;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 8]  
注意其中回力这一条数据，右表中并没有找到对应条件的结果（cid = cno = NULL），于是用NULL填充了。

#### 右外连接 ####

应用场景:  
![在这里插入图片描述][2020040711310568.png]  
这种场景下得到的是B的所有数据，和满足某一条件的A的数据；  
先看两张表（10 X 5） (6 X 3)：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 3]  
与左外连接类似，右外连接会将右表数据全部查询出来。（10 X 8）

select * from product p right outer join category c on p.cno=c.cid;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 9]  
左表并没有cno=cid=6的数据，所以用NULL填充了。  
ps:以上查询语句中的"outer"是可以省略的

### 子查询 ###

概念：查询中嵌套查询，称嵌套查询为子查询。  
下面通过实例体会：  
一、查询分类名称为“手机数码”的所有商品  
注意，这无法只从一个表中得出结果

1.查询分类名为“手机数码”的ID

select cid from category where cname='手机数码';

2.得出ID为1的结果

select * from product where cno=( select cid from category where cname='手机数码');

二、 查询出（商品名称，商品分类名称）信息  
有两种方法来做：

*  左连接：

select p.pname,c.cname from product p left outer join category c on p.cno=c.cid;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 10]

*  子查询

select pname,(select cname from category c where p.cno=c.cid) as 商品分类名称 
    from product p ;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 11]

#### 子查询的多种情况 ####

1.  子查询的结果是单行单列的：  
    子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= =

例如：查询员工工资小于平均工资的人

SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

1.  子查询的结果是多行单列的：  
    子查询可以作为条件，使用运算符in来判断  
    例如：查询’财务部’和’市场部’所有的员工信息

SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
    SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;

– 子查询

SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

1.  子查询的结果是多行多列的：  
    子查询可以作为一张虚拟表参与查询  
    例如：查询员工入职日期是2011-11-11日之后的员工信息和部门信息

– 子查询

SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2
    WHERE t1.id = t2.dept_id;

– 普通内连接

SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` >  '2011-11-11'

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70]: /images/20220121/00620e0e43784aaab0fc735b2945d76f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 1]: /images/20220121/59cf2188fd334d32bfae248ce7aa1efd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 2]: /images/20220121/f14995c6a42847b78d7ccc5c946e86dd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 3]: /images/20220121/47539a5b5f01450880d4ad68286bfe71.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 4]: /images/20220121/d4ef83b311dc4c09a0af727738b10225.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 5]: /images/20220121/bb6806c111e940478816748b1282da25.png
[20200407112915813.png]: /images/20220121/e135f7ef70924fa88f78a9141e794abe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 6]: /images/20220121/2cfe3bdafb74416c82a4c2becf72bb28.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 7]: /images/20220121/e0ecf26e67d046f38466d03f08a0f3ef.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 8]: /images/20220121/2fd2b36dc01247cd9199f4858c0d9e24.png
[2020040711310568.png]: /images/20220121/10350519adb94312b6a30b86dd2dd629.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 9]: /images/20220121/5fb69f0f561c4372b9d7bbb996e58745.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 10]: /images/20220121/ad167eff0e8942a7a0541c0481591e5d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 11]: /images/20220121/2405159e30cf4bc4a6f4d19d4a48545f.png