MySql多表设计和多表查询白话讲解

MySql多表设计和多表查询白话讲解

本文章作为MySql的入门讲解，通俗易懂，很适合有点MySql数据库基础的朋友们阅读。

众所周知，数据库在我们实际开发中起到了至关重要的作用，在如今这种信息大爆炸的时代，各个行业的快速发展，随着数据的逐渐增多，那就需要一个很好的容器去存储这些数据，MySql就是一个比较不错的数据库，企业中用的也比较多。今天给大家介绍的是MySql数据库的多表设计以及多表之间的查询。

• 1、多表设计

  因为MySql数据库是关系型数据库，表与表之间是存在关联关系的，所以我们在设计几张数据库表的时候，就要找准它们之间存在的关系，多表之间的关系包含这几个：一对一，一对多和多对多的关系，而在我们实际开发中，最常见的关系是一对多和多对多，一对一的关系相对用的是很少很少了。接下咱们就一起来看看一对多和多对多都分别是什么样子的关系。

   

  • 一对多：  首先我们要知道，一对多就是指两张表之间是一对多的关系，其实这种关系在我们生活中很容易找到，比如说，在古代，中国的古代制度是一夫多妻制，如果我们把老公作为一张老公表，把老婆作为一张老婆表，那在古代的话，这两张表是不是就是一对多的关系了。再比如，用户和订单的关系，一个用户可以有多个定义，一个订单只属于那一个用户的，所以我们可以把用户作为一张用户表，把订单作为一张订单表，那他们之间的关系不也是一对多的关系嘛，所以在我们的生活中，这种关系也是处处可见，那接下来咱们就来设计一下，设计两张具备一对多的关系的表，并且让他们之间有某种联系。  

    用户表：user  电脑表：computer  已知：一个用户可以有多台电脑，一台电脑只属于一个用户的

    创建一张user表：  

    create table user (id int primary key auto_increment, name varchar(20)  );  

    创建一张电脑表：

    create table computer ( id int primary key auto_increment, name varchar(20), uid int, --之所以加上这一套字段，是因为为了通过uid的值找到对应user表中的id的值，都是一一对应的 );  

    两张表已经创建完了，但是这样还没有完，因为这两张表目前是没有毛线关系的，没有什么约束，在这种没有约束的情况下，我们给两张表任意操作数据都是可以的，那这样的话两张表不就是没啥关系，还谈什么一对多呢，所以我们要建立关系，建立关系的格式如下。  

    建立外键关联：  

    alter table computer add constraint uid_key foreign key (uid) references user(id);  

    这样就关联成功了，当然，我们也可以在创建computer表的同时进行外键关联，让两个表之间产生一对多的关系。  

    创建一张电脑表：  

    create table computer ( id int primary key auto_increment, name varchar(20),  uid int,  constraint uid_key foreign key (uid) references user(id);  );  

    设置完外键关联之后，如果我们想要去删除user表是没法删除成功的，因为已经被computer关联了，但是我们可以删除computer表，如果computer表被删除之后，我们再去删除user表，就可以成功删除。  

  • 多对多：  多对多是一种什么关系呢，咱们不妨继续在现实生活中查找这种关系，比如，学生和老师之间的关系，一个学生可以被很多个老师教，一个老师也可以教很多学生，这是不是就是一对多的关系呢，肯定是的，所以接下来咱们就拿这个案例例句说明。  

    学生表：student  老师表：teacher  已知：一个学生可以被很多个老师教，一个老师也可以教很多学生  

    创建一张学生表：  

    create table student ( id int primary key auto_increment,  name varchar(20) );  

    创建一张老师表：

    create table teacher（id int primary key auto_increment, name varchar(20)  );  

    好，当我们写到这里发现，如果在学生表里增加一列字段，那就是指一个学生只可以对应一个老师，一个老师可以对应N多个学生了，所以这是一对多的关系，那如果在老师里增加一列字段，学生表里不增加的话，那就成了一个老师只能对一个一个学生，一个学生可以对应多个老师这样的关系了，所以不管怎么添加字段都不好，所以这个时候就需要第三张关联表，作为学生表和老师表的关联表。  

    关联表：  

    create table t_s ( tid int, sid int,  primary key(tid, sid), constraint t_key foreign key (tid) references teacher(id),   constraint t_key foreign key (tid) references teacher(id) )  

    这就是多对多的关系，特别需要注意，多对多的时候，需要第三张表来关联其余两张表。  

• 2、多表查询

  MySql中的多表查询就是指查询多张表，将多张表中满足一定条件的数据查询出来，MySql中的多表查询分类其实并不多，总共就分几类：  

  笛卡尔积查询  外链接查询：左外连接查询，右外链接查询  内连接查询：隐式内连接查询，显示内连接查询  子查询  

  例：假设有两张表a,b，这两张表的关系是一对多的关系  

  创建a表：  

  create table a (  id int primary key auto_increment, name varchar(20)  )  

  向a表中插入几条记录：

  insert into a values(1, 'aaa');  insert into a values(2, 'bbb');  insert into a values(3, 'ccc');

  创建b表：  

  create table b ( id int primary key auto_increment,   name varchar(20)  )  

  向b表中插入几条记录：  

  insert into b values(1, '张三');  insert into b values(2, '李四');  insert into b values(4, '王五');  

  我们在进行多表查询之前，可以分别查看一下表里的数据是否完整：

  select * from a;  select * from b;  

  笛卡尔积查询：  

  select * from a, b;  

  笛卡尔积查询是最简单的多表查询，但也是最没有用的多表查询，因为这种查询的查询结果没有多大意义，没法借鉴，而且数据是混乱重复的。之所以叫笛卡尔积，因为查询出来的结果和我们当年学习数学时候的笛卡尔的结果一下，相互穿插配对的查询效果。  结果：  

  1 'aaa' 1 '张三'  2 'bbb' 1 '张三'  3 'ccc' 1 '张三'  1 'aaa' 2 '李四'  2 'bbb' 2 '李四'  3 'ccc' 2 '李四'  1 'aaa' 4 '王五'  2 'bbb' 4 '王五'  3 'ccc' 4 '王五'  

  内连接查询：  

  隐式内连接：select * from a, b where a.id=b.id;显示内连接：select * from a inner join b on a.id=b.id;结果：1 'aaa' 1 '张三'2 'bbb' 2 '李四'

  内连接一共就这两种格式，虽然有两种格式，但是它们查询出来的结果是一模一样的，所以大家能记住一个即可，相对来说第一种用的比较多，并且第一种格式就是在原有的笛卡尔积查询的基础之上，增加了一个条件而已。

  外链接查询：

  左外连接：select * from a left join b on a.id=b.id;结果：1 'aaa' 1 '张三'2 'bbb' 2 '李四'3 'ccc' null null右外连接：select * from b right join a on a.id=b.id;结果：1 '张三' 1 'aaa'2 '李四' 2 'bbb'null null 3 'ccc'

  注：以上两种写法其实查询出来的结果也算是一样的，只不过查询出来的表的列谁在前谁在后的问题，外链接查询说白了，就是在原有的内连接查询的结果基础之上，在多查询出来一个结果，就是左外连接的左边的表里的所有数据或者右外连接表里的所有的数据，既先满足on后面的条件，然后再查询左外或者右外表里的所有的数据。上面这两种写法都是查询出所有的a表里的数据，如果想要查询所有的b表里的数据如下：

  左外连接：select *from b left join a on a.id=b.id;结果：1 '张三' 1 'aaa'2 '李四' 2 'bbb'4 '王五' null null右外连接：select * from a right join b on a.id=b.id;结果：1 'aaa' 1 '张三'2 'bbb' 2 '李四'null null 4 '王五'

  子查询：  所谓的子查询就是指一个查询语句中又嵌套了一个查询语句。  

  例：         

  查询a表中id值为b表中名字为张三的id值的姓名：select name from a where id=(select id from b where name='张三');结果：'aaa'

  以上几种查询格式就是MySql中多表查询比较简单，并且不复杂的查询语句，当然，今天讲解的这几个查询也是MySql最基础入门的查询语句，在实际开发中，有很多很多复杂的查询结构，所以以后还需要大家多做一些多表查询的练习题来巩固。如果朋友们能把以上的内容掌握的话，那MySql的多表设计和多表查询也算是入门了。

以上内容就是本章要给大家讲解的知识点，内容不多，但可以帮助大家尤其是零基础的朋友们学好MySql中的多表设计和多表查询，最后祝愿每一位学习MySql的朋友们马到成功，更上一层楼。