mysql数据库基本操作（七)

多表查询

     数据表之间的关系无外乎分为三种：一对一、一对多、多对多。表的查询我们前面只说过单表的，多表的查询在实际应用中也很重要。在多表查询中，最重要的是连接查询。连接查询又可以分为内连接（inner join）、外连接（left join、right join）、全连接（full join）。下面主要讲的这些连接查询。

      在开始之前，我们要准备两张表，还是用上一篇的班级学生表吧：

CREATE TABLE Classhost(

       host_id TINYINT PRIMARY KEY auto_increment,
       name VARCHAR (20),
       age INT ,
       is_marriged boolean 
);

INSERT INTO Classhost (name,age,is_marriged) VALUES ("丽丽",22,0),
                                                       ("莉莉",24,0),
                                                       ("李丽",22,0),
                                                       ("李莉",20,0);
CREATE TABLE student(
       student_id INT PRIMARY KEY auto_increment,
       name VARCHAR (20),
       host_id TINYINT   
)ENGINE=INNODB;

INSERT INTO Student(name,host_id) VALUES ("pengfy",2),
                                            ("pyq",4),
                                            ("xiaojiang",1),
                                            ("pyq2",3),
                                            ("pengfy2",1),
                                            ("pyq3",3),
                                            ("lily",2);

1.笛卡尔积查询

       每张表的内容都已经很熟了，注意这里我没有设置外键，只是两张关联的表，就没有主次之分了。首先要看的是一种叫笛卡尔积查询的方法，就是同时展示两张表的信息：

select * from classhost,student;

       通过结果就看到，这种最简单的方法展示两张表的信息，是我们查询两张表的一个乘积（m*n）,但是很多信息对我们都是没有意义的，这张表对我们真正有意义的内容就只有7条。那我们能不能把有意义的筛选出来呢？肯定是可以的，这就涉及到我们内连接的概念。

2.内连接

       内连接就是利用条件从笛卡尔积结果中筛选出了正确的结果。我们前面学过的where就可以用在这，真正对我们有意义的结果就是两张表host_id相同的时候那条数据：

select * from classhost,student where classhost.host_id=student.host_id;

      现在的结果就是我们想要的结果吧，内连接其实还有自己专门的语法，就是开始提到的 inner join：

 select * from student inner join classhost on student.host_id=classhost.host_id;

      拿到的结果是一样的，大家可以试试。因为这里我们是select *，我们还是显示我们想看到的，比如班主任的名字和年纪，学生的名字，就改成这样：

select classhost.name,classhost.age,student.name as stuednt_name from student inner join classhost on student.host_id=classhost.host_id;

     我把学生的名字显示改了一下，这样更符合我们的要求了，内连接就是这样。

3.外连接

     现在班里来了一个新同学，我们插入一个新的学生信息：

 insert into student(name,host_id) values ("lucy",5);

     Lucy对应的班主任还没有入职，这个时候我们用内连接查询的话，Lucy就查不到了，这里就要使用外连接的方式来查询了，外连接分为左外连接、右外连接和全外连接。

左外连接

    从结果看，左外连接就是在内连接的基础上增加左边有右边没有的结果

select * from student left join classhost on student.host_id=classhost.host_id;

        这里增加的就是Lucy的信息，内连接是不能显示的。

右外连接

       这里我们能猜到，右外连接就是在内连接的基础上增加右边有左边没有的结果，那我们新增两位老师：

insert into classhost(name,age,is_marriged) values 
     ('李丽丽',25,0)，
     ('李莉莉',23,0);

select * from student right join classhost on student.host_id=classhost.host_id;

 

      结果和我们猜测的应该是一样的。

全外连接

     这里我们应该还是可以猜到，全外连接是在内连接的基础上增加左边有右边没有的和右边有左边没有的结果，我们又要新增一位学生了，那么sql语句如下：

insert into student(name,host_id) values ('jim',8);

select * from student right join classhost on student.host_id=classhost.host_id
     UNION
     select * from student left join classhost on student.host_id=classhost.host_id;

     这里我们用到的是union，那么还有一个方法是union all，注意 union与union all的区别在于union会去掉相同的纪录，大家可以试一下。

4.多表查询之复合条件连接查询

　　上面讲的都是把两张表的数据放在一张表上全拿出来，如果还有其他附加条件该怎么办？比如我要查老师年纪在23岁以上带的学生姓名，我们可以这样写：

SELECT student.name 
FROM student,classhost 
WHERE student.host_id=classhost.host_id 
AND classhost.age>22;

      因为我这里学生姓名都不一样，如果要去重复名可以在select后面加上distinct，大家试一下内连接的写法。再写一个需求：用内连接的方式查完两张表后，按老师年龄降序排列。

select * from student inner join classhost on student.host_id=classhost.host_id order by age desc;

5.多表查询之子查询

　　顾名思义，子查询，应该是子查询是将一个查询语句嵌套在另一个查询语句中的一种查询方式，内层查询语句的查询结果，可以为外层查询语句提供查询条件。在子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字，还可以包含比较运算符：= 、 !=、> 、<等。我们先看一下比较常用的IN关键字。

带IN关键字的子查询

　　我们用这种方式来模拟一下内连接查询，找到有安排班主任的学生：

select * from student 
where host_id IN
(select host_id from classhost);

      我们只有jim同学没有安排老师，这里结果正确。

带比较运算符的子查询

      我们常用的比较运算符有=、!=、>、>=、<、<=、<>，我们现在重新来模拟一下复合查询里面的第一题，查老师年纪在23岁以上带的学生姓名：

select * from student 
where host_id IN
(select host_id from classhost where age>22);

      结果和我们上面一样吧。

带EXISTS关键字的子查询

       EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，而是返回一个真假值Ture或False。当返回Ture时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询。那我们就以丽丽老师是否存在作为判断依据，如果存在我就返回所有学生表：

select * from student 
where exists 
(select name from classhost where name='丽丽');

       如果想只返回丽丽老师班上的同学，就在后面加一个and host_id=1就好了。大家可以试一下如果老师不存在返回的是什么结果。

       最后补充一个小知识点，就是我们可以使用这种嵌套的方法，快速的复制一张表，比如我现在想要复制学生表，只需要这样：

create table aa(select * from student);

       我们复制的这张表名字叫aa，我们查一下复制成功了没有：

      很完美，这样可以很快的复制一张表了，不用一个个去输入数据了。到这里，mysql的基础知识基本就讲完了，坚持写博客是一种好习惯，后面会是mysql的进阶。