sql多表查询

<!--more-->

sql会创建多表以及多表的关系

需求：

上篇文章中的商品表和分类表之间存在着所属关系，在数据库如何表示这种关系

分析：

多表之间的关系如何来维护

• 外键约束：假设现在在product表中插入一条cno为99的数据，但是cno为99的列在category中并不存在。这时候就需要外键约束，foreign key
  • 给product这个表中的cno添加一个外键约束: alter table product add foreign key(cno) references category(cid); 添加外键约束之后，再向product中插入category中没有的种类会失败。

    添加外键约束之后，如果想删除category中的某一列，也会报错，因为product中有记录在引用。首先得去product表中删除引用该种类的所有记录，才能去删除category中的相应列。

在下面的例子中，有如下2张表：

mysql> select * from product;
+-----+--------------+-------+---------------------+------+
| pid | pname        | price | pdate               | cno  |
+-----+--------------+-------+---------------------+------+
|   4 | 小米mix2s    |  2700 | 2019-04-14 13:14:31 |    1 |
|   5 | 华为p30      |  4788 | 2019-04-14 13:14:32 |    1 |
|   6 | 阿迪王       |    99 | 2019-04-14 13:14:32 |    2 |
|   7 | 老村长       |    88 | 2019-04-14 13:14:32 |    3 |
|   8 | 劲酒         |    35 | 2019-04-14 13:14:32 |    3 |
|   9 | 小熊饼干     |     3 | 2019-04-14 13:14:32 |    4 |
|  10 | 卫龙辣条     |     1 | 2019-04-14 13:14:32 |    5 |
|  11 | 旺旺雪饼     |     1 | 2019-04-14 13:14:32 |    5 |
+-----+--------------+-------+---------------------+------+
8 rows in set (0.00 sec)

mysql> select * from category;
+-----+--------------+-----------------------+
| cid | cname        | cdesc                 |
+-----+--------------+-----------------------+
|   1 | 手机数码     | 电子产品              |
|   2 | 鞋靴箱包     | 江南皮鞋厂打造        |
|   3 | 香烟酒水     | 芙蓉王，茅台          |
|   4 | 酸奶饼干     | 哇哈哈                |
|   5 | 馋嘴零食     | 瓜子花生              |
+-----+--------------+-----------------------+
5 rows in set (0.00 sec)

其中2张表存在CONSTRAINT product_ibfk_1 FOREIGN KEY (cno) REFERENCES category (cid)外键约束

建立数据库的原则

通常情况下，一个应用一个数据库。

多表之间的建表原则

一对多

例子中提到的product和category就是一对多的关系，一个种类对应多个商品

• 建表原则： 在多的一方(product)添加一个外键(cno)指向一的一方(category)的主键(cid)

  多对多

  例如：老师和学生，学生和课程等 ``` mysql> select * from student;
• ----+--------+--------+-----+ | id | name | gender | age |
• ----+--------+--------+-----+ | 1 | 张三 | 男 | 18 | | 2 | 李四 | 男 | 20 | | 3 | 王五 | 女 | 19 |
• ----+--------+--------+-----+ 3 rows in set (0.00 sec)
  mysql> select * from subject;
• ----+--------+-----------+ | id | name | teacher |
• ----+--------+-----------+ | 1 | 英语 | 张老师 | | 2 | 语文 | 李老师 | | 3 | 数学 | 杨老师 |
• ----+--------+-----------+ 3 rows in set (0.00 sec)

  假设现在学生开始选课，选课结果如下：

  张三： 英语，语文 李四： 数学 王五： 英语，语文

  student表中的学生选了不固定的subject表里的科目，这种关系为多对多
  这时候我们就需要建立如下表:

  CREATE TABLE stu-submiddletable ( stu-subId int(11) NOT NULL AUTO_INCREMENT COMMENT '中间表主键', subjectId int(11) DEFAULT NULL COMMENT '外键', studentId int(11) NOT NULL COMMENT '外键', PRIMARY KEY (stu-subId), KEY studentId (studentId), KEY subjectId (subjectId), CONSTRAINT studentId FOREIGN KEY (studentId) REFERENCES student (id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT subjectId FOREIGN KEY (subjectId) REFERENCES subject (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ```
• 建表原则： 需要多建一张中间表，将多对多的关系拆为一对多的关系，中间表至少要有2个外键，这2个外键分别指向原来的那张表。 在上述中间表中，至少需要2个外键，即学生的id和科目的id，其中对于中间表的学生id来说，student表中和中间表的学生id为一对多(即category和product的关系，student为一，中间表学生id为多)，而对于中间表中课程编号，跟subject，也是一样的(subject的id对中间表的科目编号也为一对多)。 在这张中间表中，对于上述选择结果，就可以这么存储：

一对一

例如公民和身份证的关系： People表： id name income IDcard表：sid 头像 性别

• 可以直接合并2张表
• 可以在一张表中新增列，作为外键，当初一对多的情况来处理，这个外键指向另外一张表。
• 将2张表的主键建立起连接，让2张表主键相等。

实际用途不多，比如说拆表操作(将表中的一些列拆分出来)

关于主键约束和唯一约束的区别： 主键约束：默认不能为空且唯一，并且不能有多个主键 唯一约束：默认为空且唯一，可以有多个唯一约束键 外键都是指向另一张表的主键,而唯一的约束不可以作为其他表的外键

多表查询

对于上述的product表和category表来进行下面的举例：

多表查询的几种类型：

• 交叉连接查询 笛卡尔积 对于上述的product表以及category表，如果想要一次查询2张表的内容，我们可以输入：

  select * from product,category;

  ``` mysql> SELECT * from product,category;
• -----+--------------+-------+---------------------+------+-----+--------------+-----------------------+ | pid | pname | price | pdate | cno | cid | cname | cdesc |
• -----+--------------+-------+---------------------+------+-----+--------------+-----------------------+ | 4 | 小米mix2s | 2700 | 2019-04-14 13:14:31 | 1 | 1 | 手机数码 | 电子产品 | | 4 | 小米mix2s | 2700 | 2019-04-14 13:14:31 | 1 | 2 | 鞋靴箱包 | 江南皮鞋厂打造 | | 4 | 小米mix2s | 2700 | 2019-04-14 13:14:31 | 1 | 3 | 香烟酒水 | 芙蓉王，茅台 | | 4 | 小米mix2s | 2700 | 2019-04-14 13:14:31 | 1 | 4 | 酸奶饼干 | 哇哈哈 | | 4 | 小米mix2s | 2700 | 2019-04-14 13:14:31 | 1 | 5 | 馋嘴零食 | 瓜子花生 | | 5 | 华为p30 | 4788 | 2019-04-14 13:14:32 | 1 | 1 | 手机数码 | 电子产品 | | 5 | 华为p30 | 4788 | 2019-04-14 13:14:32 | 1 | 2 | 鞋靴箱包 | 江南皮鞋厂打造 | | 5 | 华为p30 | 4788 | 2019-04-14 13:14:32 | 1 | 3 | 香烟酒水 | 芙蓉王，茅台 | | 5 | 华为p30 | 4788 | 2019-04-14 13:14:32 | 1 | 4 | 酸奶饼干 | 哇哈哈 | | 5 | 华为p30 | 4788 | 2019-04-14 13:14:32 | 1 | 5 | 馋嘴零食 | 瓜子花生 | | 6 | 阿迪王 | 99 | 2019-04-14 13:14:32 | 2 | 1 | 手机数码 | 电子产品 | | 6 | 阿迪王 | 99 | 2019-04-14 13:14:32 | 2 | 2 | 鞋靴箱包 | 江南皮鞋厂打造 | | 6 | 阿迪王 | 99 | 2019-04-14 13:14:32 | 2 | 3 | 香烟酒水 | 芙蓉王，茅台 | | 6 | 阿迪王 | 99 | 2019-04-14 13:14:32 | 2 | 4 | 酸奶饼干 | 哇哈哈 | | 6 | 阿迪王 | 99 | 2019-04-14 13:14:32 | 2 | 5 | 馋嘴零食 | 瓜子花生 | | 7 | 老村长 | 88 | 2019-04-14 13:14:32 | 3 | 1 | 手机数码 | 电子产品 | | 7 | 老村长 | 88 | 2019-04-14 13:14:32 | 3 | 2 | 鞋靴箱包 | 江南皮鞋厂打造 | | 7 | 老村长 | 88 | 2019-04-14 13:14:32 | 3 | 3 | 香烟酒水 | 芙蓉王，茅台 | | 7 | 老村长 | 88 | 2019-04-14 13:14:32 | 3 | 4 | 酸奶饼干 | 哇哈哈 | | 7 | 老村长 | 88 | 2019-04-14 13:14:32 | 3 | 5 | 馋嘴零食 | 瓜子花生 | | 8 | 劲酒 | 35 | 2019-04-14 13:14:32 | 3 | 1 | 手机数码 | 电子产品 | | 8 | 劲酒 | 35 | 2019-04-14 13:14:32 | 3 | 2 | 鞋靴箱包 | 江南皮鞋厂打造 | | 8 | 劲酒 | 35 | 2019-04-14 13:14:32 | 3 | 3 | 香烟酒水 | 芙蓉王，茅台 | | 8 | 劲酒 | 35 | 2019-04-14 13:14:32 | 3 | 4 | 酸奶饼干 | 哇哈哈 | | 8 | 劲酒 | 35 | 2019-04-14 13:14:32 | 3 | 5 | 馋嘴零食 | 瓜子花生 | | 9 | 小熊饼干 | 3 | 2019-04-14 13:14:32 | 4 | 1 | 手机数码 | 电子产品 | | 9 | 小熊饼干 | 3 | 2019-04-14 13:14:32 | 4 | 2 | 鞋靴箱包 | 江南皮鞋厂打造 | | 9 | 小熊饼干 | 3 | 2019-04-14 13:14:32 | 4 | 3 | 香烟酒水 | 芙蓉王，茅台 | | 9 | 小熊饼干 | 3 | 2019-04-14 13:14:32 | 4 | 4 | 酸奶饼干 | 哇哈哈 | | 9 | 小熊饼干 | 3 | 2019-04-14 13:14:32 | 4 | 5 | 馋嘴零食 | 瓜子花生 | | 10 | 卫龙辣条 | 1 | 2019-04-14 13:14:32 | 5 | 1 | 手机数码 | 电子产品 | | 10 | 卫龙辣条 | 1 | 2019-04-14 13:14:32 | 5 | 2 | 鞋靴箱包 | 江南皮鞋厂打造 | | 10 | 卫龙辣条 | 1 | 2019-04-14 13:14:32 | 5 | 3 | 香烟酒水 | 芙蓉王，茅台 | | 10 | 卫龙辣条 | 1 | 2019-04-14 13:14:32 | 5 | 4 | 酸奶饼干 | 哇哈哈 | | 10 | 卫龙辣条 | 1 | 2019-04-14 13:14:32 | 5 | 5 | 馋嘴零食 | 瓜子花生 | | 11 | 旺旺雪饼 | 1 | 2019-04-14 13:14:32 | 5 | 1 | 手机数码 | 电子产品 | | 11 | 旺旺雪饼 | 1 | 2019-04-14 13:14:32 | 5 | 2 | 鞋靴箱包 | 江南皮鞋厂打造 | | 11 | 旺旺雪饼 | 1 | 2019-04-14 13:14:32 | 5 | 3 | 香烟酒水 | 芙蓉王，茅台 | | 11 | 旺旺雪饼 | 1 | 2019-04-14 13:14:32 | 5 | 4 | 酸奶饼干 | 哇哈哈 | | 11 | 旺旺雪饼 | 1 | 2019-04-14 13:14:32 | 5 | 5 | 馋嘴零食 | 瓜子花生 |
• -----+--------------+-------+---------------------+------+-----+--------------+-----------------------+ 40 rows in set (0.00 sec)

  > 上述例子中查出来的结果称为笛卡尔积即2张表的乘积，没什么实际意义。其实我们需要的就是cno等于cid的那些数据，所以需要做一下过滤。

  select * from product,category where cno=cid;

对于上述查询，经常会发现cno和cid属于哪个表不明确，可以通过起个别名的方式来指明是哪个表: select * from product as P,category as C where p.cno=c.cid;

mysql> select * from product as P,category as C where p.cno=c.cid;
+-----+--------------+-------+---------------------+------+-----+--------------+-----------------------+
| pid | pname        | price | pdate               | cno  | cid | cname        | cdesc                 |
+-----+--------------+-------+---------------------+------+-----+--------------+-----------------------+
|   4 | 小米mix2s    |  2700 | 2019-04-14 13:14:31 |    1 |   1 | 手机数码     | 电子产品              |
|   5 | 华为p30      |  4788 | 2019-04-14 13:14:32 |    1 |   1 | 手机数码     | 电子产品              |
|   6 | 阿迪王       |    99 | 2019-04-14 13:14:32 |    2 |   2 | 鞋靴箱包     | 江南皮鞋厂打造        |
|   7 | 老村长       |    88 | 2019-04-14 13:14:32 |    3 |   3 | 香烟酒水     | 芙蓉王，茅台          |
|   8 | 劲酒         |    35 | 2019-04-14 13:14:32 |    3 |   3 | 香烟酒水     | 芙蓉王，茅台          |
|   9 | 小熊饼干     |     3 | 2019-04-14 13:14:32 |    4 |   4 | 酸奶饼干     | 哇哈哈                |
|  10 | 卫龙辣条     |     1 | 2019-04-14 13:14:32 |    5 |   5 | 馋嘴零食     | 瓜子花生              |
|  11 | 旺旺雪饼     |     1 | 2019-04-14 13:14:32 |    5 |   5 | 馋嘴零食     | 瓜子花生              |
+-----+--------------+-------+---------------------+------+-----+--------------+-----------------------+
8 rows in set (0.00 sec)

• 内连接查询 对于上述例子，使用where进行条件过滤的为隐式内连接，
  • 隐式内连接

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

  • 显式内链接（使用inner join）

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

    ``` mysql> select * from product as P join category as C on p.cno=c.cid;
• -----+--------------+-------+---------------------+------+-----+--------------+-----------------------+ | pid | pname | price | pdate | cno | cid | cname | cdesc |
• -----+--------------+-------+---------------------+------+-----+--------------+-----------------------+ | 4 | 小米mix2s | 2700 | 2019-04-14 13:14:31 | 1 | 1 | 手机数码 | 电子产品 | | 5 | 华为p30 | 4788 | 2019-04-14 13:14:32 | 1 | 1 | 手机数码 | 电子产品 | | 6 | 阿迪王 | 99 | 2019-04-14 13:14:32 | 2 | 2 | 鞋靴箱包 | 江南皮鞋厂打造 | | 7 | 老村长 | 88 | 2019-04-14 13:14:32 | 3 | 3 | 香烟酒水 | 芙蓉王，茅台 | | 8 | 劲酒 | 35 | 2019-04-14 13:14:32 | 3 | 3 | 香烟酒水 | 芙蓉王，茅台 | | 9 | 小熊饼干 | 3 | 2019-04-14 13:14:32 | 4 | 4 | 酸奶饼干 | 哇哈哈 | | 10 | 卫龙辣条 | 1 | 2019-04-14 13:14:32 | 5 | 5 | 馋嘴零食 | 瓜子花生 | | 11 | 旺旺雪饼 | 1 | 2019-04-14 13:14:32 | 5 | 5 | 馋嘴零食 | 瓜子花生 |
• -----+--------------+-------+---------------------+------+-----+--------------+-----------------------+ 8 rows in set (0.00 sec)

  上述2种方式查询出来的结果都是一样的，但是区别在于，隐式内连接是在查询出的结果基础上进行的where条件过滤，但是显式内连接是带着条件去查询结果的，**执行效率是比较高的**。

• 左外连接（使用 left join） 左(外)连接，左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。 对于上述例子，我们先给product表插入一条没有对应cno的数据。

  insert into product values(null, "一条没有cno的记录", 100, null, null);

  然后执行左外连接查询：

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

  ``` mysql> select * from product as P left outer join category as C on p.cno=c.cid;
• -----+--------------------------+-------+---------------------+------+------+--------------+-----------------------+ | pid | pname | price | pdate | cno | cid | cname | cdesc |
• -----+--------------------------+-------+---------------------+------+------+--------------+-----------------------+ | 4 | 小米mix2s | 2700 | 2019-04-14 13:14:31 | 1 | 1 | 手机数码 | 电子产品 | | 5 | 华为p30 | 4788 | 2019-04-14 13:14:32 | 1 | 1 | 手机数码 | 电子产品 | | 6 | 阿迪王 | 99 | 2019-04-14 13:14:32 | 2 | 2 | 鞋靴箱包 | 江南皮鞋厂打造 | | 7 | 老村长 | 88 | 2019-04-14 13:14:32 | 3 | 3 | 香烟酒水 | 芙蓉王，茅台 | | 8 | 劲酒 | 35 | 2019-04-14 13:14:32 | 3 | 3 | 香烟酒水 | 芙蓉王，茅台 | | 9 | 小熊饼干 | 3 | 2019-04-14 13:14:32 | 4 | 4 | 酸奶饼干 | 哇哈哈 | | 10 | 卫龙辣条 | 1 | 2019-04-14 13:14:32 | 5 | 5 | 馋嘴零食 | 瓜子花生 | | 11 | 旺旺雪饼 | 1 | 2019-04-14 13:14:32 | 5 | 5 | 馋嘴零食 | 瓜子花生 | | 13 | 一条没有cno的记录 | 100 | NULL | NULL | NULL | NULL | NULL |
• -----+--------------------------+-------+---------------------+------+------+--------------+-----------------------+ 9 rows in set (0.00 sec) ``` 可以看到右表的这一条的输出都为null，而左表的数据都输出了。
• 右外连接（使用right join） 和左外连接相对的，右外连接查询会将右表的所有数据查询出来，如果左表没有对应数据的话会用null代替

left join和right join分别为left outer join和right outer join的缩写 inner/outer在语句中可以省略

简单理解： 内连接查出来的是2个表的交集，左外和右外其实查询了左表或者右表的全部，并且如果有相对应的左表记录的话也会显示。

• 全连接（使用union /union all） 全连接指的是将2个表合并。 语句： (select colum1,colum2...columN from tableA ) union (select colum1,colum2...columN from tableB ); (select colum1,colum2...columN from tableA ) union all (select colum1,colum2...columN from tableB );

通过union连接的SQL它们分别单独取出的列数必须相同；不要求合并的表列名称相同时，以第一个sql 表列名为准；使用union 时，完全相等的行，将会被合并，由于合并比较耗时，一般不直接使用 union 进行合并，而是通常采用union all 进行合并；

被union 连接的sql 子句，单个子句中不用写order by ，因为不会有排序的效果。但可以对最终的结果集进行排序； (select id,name from A order by id) union all (select id,name from B order by id); //没有排序效果 (select id,name from A ) union all (select id,name from B ) order by id; //有排序效果

分页查询(使用limit)

假设规定pageSize为3

select * from product limit 0,3; // 第一页

select * from product limit 3,3; // 第二页

select * from product limit 6,3; // 第一页

即每次查询根据page和pageSize计算一个起始的索引位置即可：

select * from product limit (page-1)*pageSize,pageSize;

子查询

即sql语句中再嵌套sql语句

1. 如果要查询分类名为“手机数码”的商品，需要动态查询出在category中的cid

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

   ``` mysql> SELECT * from product as p WHERE p.cno=(SELECT cid from category WHERE cname="手机数码");

+-----+-------------+-------+---------------------+------+ | pid | pname | price | pdate | cno | +-----+-------------+-------+---------------------+------+ | 4 | 小米mix2s | 2700 | 2019-04-14 13:14:31 | 1 | | 5 | 华为p30 | 4788 | 2019-04-14 13:14:32 | 1 | +-----+-------------+-------+---------------------+------+ 2 rows in set (0.00 sec)

2. 如果要查询出商品名称(pname)和商品分类名称(cname)的信息
选用左右链接查询都可达到目的，在这介绍下子查询的写法：

select pname,(select cname from category as c where p.cno=c.cid) from product as p;

mysql> SELECT pname,(SELECT cname from category as C WHERE p.cno=c.cid) from product as P; +--------------------------+-----------------------------------------------------+ | pname | (SELECT cname from category as C WHERE p.cno=c.cid) | +--------------------------+-----------------------------------------------------+ | 小米mix2s | 手机数码 | | 华为p30 | 手机数码 | | 阿迪王 | 鞋靴箱包 | | 老村长 | 香烟酒水 | | 劲酒 | 香烟酒水 | | 小熊饼干 | 酸奶饼干 | | 卫龙辣条 | 馋嘴零食 | | 旺旺雪饼 | 馋嘴零食 | | 一条没有cno的记录 | NULL | +--------------------------+-----------------------------------------------------+ 9 rows in set (0.00 sec)