这里提供了三个表：表1：mysql> select * from class; +------+--------+ | id | name | +------+--------+ | 1 | 一班 | | 2 | 二班 | | 3 | 三班 | +------+--------+ 3 rows in set (0.01 sec)表2：
mysql> select * from student; +------+--------+----------+ | id | name | class_id | +------+--------+----------+ | 1 | 张三 | 1 | | 2 | 李四 | 1 | | 3 | 王五 | 2 | | 4 | 赵六 | 2 | | 5 | 小七 | 3 | | 6 | 周八 | 3 | | 7 | 老九 | 3 | +------+--------+----------+ 7 rows in set (0.00 sec)表3：
mysql> select * from score; +------+------------+-------+ | id | student_id | score | +------+------------+-------+ | 1 | 1 | 100 | | 2 | 2 | 94 | | 3 | 3 | 95 | | 4 | 4 | 96 | | 5 | 8 | 97 | +------+------------+-------+ 5 rows in set (0.01 sec)接下来就针对这三个表来进行联合查询~
我们在实际开发过程中，数据往往来自不同的表，所以需要多表联合查询。联合查询就是对多张表的数据取笛卡尔积，故这里先简单介绍一下笛卡尔积：
♫笛卡尔积
笛卡尔积（Cartesian product），也称为直积，是指两个集合中每个元素之间所有可能的组合。 （如：假设集合A ={1, 2}，集合B ={a, b, c}。则集合A与集合B的笛卡尔积为{(1,a), (1,b), (1,c), (2,a), (2,b), (2,c)}）
♫联合查询
联合查询分为内连接和外连接，我们先来看看内连接的写法。
♪内连接
内连接有两种写法：
语法1：select * from 表名1,表名2;
mysql> select * from student,class; +------+--------+----------+------+--------+ | id | name | class_id | id | name | +------+--------+----------+------+--------+ | 1 | 张三 | 1 | 1 | 一班 | | 1 | 张三 | 1 | 2 | 二班 | | 1 | 张三 | 1 | 3 | 三班 | | 2 | 李四 | 1 | 1 | 一班 | | 2 | 李四 | 1 | 2 | 二班 | | 2 | 李四 | 1 | 3 | 三班 | | 3 | 王五 | 2 | 1 | 一班 | | 3 | 王五 | 2 | 2 | 二班 | | 3 | 王五 | 2 | 3 | 三班 | | 4 | 赵六 | 2 | 1 | 一班 | | 4 | 赵六 | 2 | 2 | 二班 | | 4 | 赵六 | 2 | 3 | 三班 | | 5 | 小七 | 3 | 1 | 一班 | | 5 | 小七 | 3 | 2 | 二班 | | 5 | 小七 | 3 | 3 | 三班 | | 6 | 周八 | 3 | 1 | 一班 | | 6 | 周八 | 3 | 2 | 二班 | | 6 | 周八 | 3 | 3 | 三班 | | 7 | 老九 | 3 | 1 | 一班 | | 7 | 老九 | 3 | 2 | 二班 | | 7 | 老九 | 3 | 3 | 三班 | +------+--------+----------+------+--------+ 21 rows in set (0.00 sec)语法2：select * from 表名1 inner join 表名2;
mysql> select * from student inner join class; +------+--------+----------+------+--------+ | id | name | class_id | id | name | +------+--------+----------+------+--------+ | 1 | 张三 | 1 | 1 | 一班 | | 1 | 张三 | 1 | 2 | 二班 | | 1 | 张三 | 1 | 3 | 三班 | | 2 | 李四 | 1 | 1 | 一班 | | 2 | 李四 | 1 | 2 | 二班 | | 2 | 李四 | 1 | 3 | 三班 | | 3 | 王五 | 2 | 1 | 一班 | | 3 | 王五 | 2 | 2 | 二班 | | 3 | 王五 | 2 | 3 | 三班 | | 4 | 赵六 | 2 | 1 | 一班 | | 4 | 赵六 | 2 | 2 | 二班 | | 4 | 赵六 | 2 | 3 | 三班 | | 5 | 小七 | 3 | 1 | 一班 | | 5 | 小七 | 3 | 2 | 二班 | | 5 | 小七 | 3 | 3 | 三班 | | 6 | 周八 | 3 | 1 | 一班 | | 6 | 周八 | 3 | 2 | 二班 | | 6 | 周八 | 3 | 3 | 三班 | | 7 | 老九 | 3 | 1 | 一班 | | 7 | 老九 | 3 | 2 | 二班 | | 7 | 老九 | 3 | 3 | 三班 | +------+--------+----------+------+--------+ 21 rows in set (0.00 sec)注：此处的inner可以省略掉
虽然上面通过联合查询得到了两张表的笛卡尔积的结果，但是该结果有许多不恰当的数据，我们可以通过添加条件筛选出正确的数据：
语法1：select * from 表名1 ,表名2 where 指定条件;
mysql> select * from student,class where student.class_id=class.id; +------+--------+----------+------+--------+ | id | name | class_id | id | name | +------+--------+----------+------+--------+ | 1 | 张三 | 1 | 1 | 一班 | | 2 | 李四 | 1 | 1 | 一班 | | 3 | 王五 | 2 | 2 | 二班 | | 4 | 赵六 | 2 | 2 | 二班 | | 5 | 小七 | 3 | 3 | 三班 | | 6 | 周八 | 3 | 3 | 三班 | | 7 | 老九 | 3 | 3 | 三班 | +------+--------+----------+------+--------+ 7 rows in set (0.01 sec)语法1：select * from 表名1 inner join 表名2 on 指定条件;
mysql> select * from student join class on student.class_id=class.id; +------+--------+----------+------+--------+ | id | name | class_id | id | name | +------+--------+----------+------+--------+ | 1 | 张三 | 1 | 1 | 一班 | | 2 | 李四 | 1 | 1 | 一班 | | 3 | 王五 | 2 | 2 | 二班 | | 4 | 赵六 | 2 | 2 | 二班 | | 5 | 小七 | 3 | 3 | 三班 | | 6 | 周八 | 3 | 3 | 三班 | | 7 | 老九 | 3 | 3 | 三班 | +------+--------+----------+------+--------+ 7 rows in set (0.00 sec)注：为了包证代码的可读性和避免两个表的列名冲突，写指定条件时列名前要加上表名.，以表示该列是哪个表的列
当要连接的两个表里的数据不是一一对应的，内连接只会查询互相对应的数据：
-- student.id有但student_id没有的和student.id有但student_id没有的数据不会出现 mysql> select * from student join score on student.id=score.student_id; +------+--------+----------+------+------------+-------+ | id | name | class_id | id | student_id | score | +------+--------+----------+------+------------+-------+ | 1 | 张三 | 1 | 1 | 1 | 100 | | 2 | 李四 | 1 | 2 | 2 | 94 | | 3 | 王五 | 2 | 3 | 3 | 95 | | 4 | 赵六 | 2 | 4 | 4 | 96 | +------+--------+----------+------+------------+-------+ 4 rows in set (0.03 sec)♪外连接
外连接分为左外连接和右外连接。
♩左外连接
左外连接返回左表中的所有行以及右表中符合条件的行：
语法1：selecct * from 表名1 left join 表名2 on 指定条件;
-- 左表（student）全打印 mysql> select * from student left join score on student.id=score.student_id; +------+--------+----------+------+------------+-------+ | id | name | class_id | id | student_id | score | +------+--------+----------+------+------------+-------+ | 1 | 张三 | 1 | 1 | 1 | 100 | | 2 | 李四 | 1 | 2 | 2 | 94 | | 3 | 王五 | 2 | 3 | 3 | 95 | | 4 | 赵六 | 2 | 4 | 4 | 96 | | 5 | 小七 | 3 | NULL | NULL | NULL | | 6 | 周八 | 3 | NULL | NULL | NULL | | 7 | 老九 | 3 | NULL | NULL | NULL | +------+--------+----------+------+------------+-------+ 7 rows in set (0.00 sec)♩右外连接
右外连接返回右表中的所有行以及左表中符合条件的行:
语法1：selecct * from 表名1 right join 表名2 on 指定条件;
-- 右表（score）全打印 mysql> select * from student right join score on student.id=score.student_id; +------+--------+----------+------+------------+-------+ | id | name | class_id | id | student_id | score | +------+--------+----------+------+------------+-------+ | 1 | 张三 | 1 | 1 | 1 | 100 | | 2 | 李四 | 1 | 2 | 2 | 94 | | 3 | 王五 | 2 | 3 | 3 | 95 | | 4 | 赵六 | 2 | 4 | 4 | 96 | | NULL | NULL | NULL | 5 | 8 | 97 | +------+--------+----------+------+------------+-------+ 5 rows in set (0.00 sec)♪自连接
自连接是指同一张表自己进行联合查询，通过自连接可以把表的行转换成列，间接实现行与列的条件比较：
语法1：selecct * from 表名1 as 别名1, 表名1 as 别名2 where 指定条件;
mysql> select * from class as c1,class as c2 where c1.id=c2.id; +------+--------+------+--------+ | id | name | id | name | +------+--------+------+--------+ | 1 | 一班 | 1 | 一班 | | 2 | 二班 | 2 | 二班 | | 3 | 三班 | 3 | 三班 | +------+--------+------+--------+ 3 rows in set (0.00 sec)注：自连接需要起别名，不然表名重复会报错
♫子查询
子查询是指嵌入在其他 sql 语句中的 select 语句，子查询可以分为单行子查询和多行子查询：♪单行子查询
返回一条记录的子查询：语法1： selecct * from 表名 where 列名=子查询语句;-- 根据子查询获得的李四id查询李四所在班级的学生信息 mysql> select * from student where class_id = (select class_id from student where name="李四"); +------+--------+----------+ | id | name | class_id | +------+--------+----------+ | 1 | 张三 | 1 | | 2 | 李四 | 1 | +------+--------+----------+ 2 rows in set (0.03 sec)♪多行子查询
返回多条记录的子查询：语法1： selecct * from 表名 where 列名 in (子查询语句);-- 根据子查询获得的李四和王五id查询李四和王五所在班级的学生信息 mysql> select * from student where class_id in (select class_id from student where name="李四" or name="王五"); +------+--------+----------+ | id | name | class_id | +------+--------+----------+ | 1 | 张三 | 1 | | 2 | 李四 | 1 | | 3 | 王五 | 2 | | 4 | 赵六 | 2 | +------+--------+----------+ 4 rows in set (0.00 sec)
♫合并查询
在实际应用中，为了合并多个 select 的执行结果，可以使用集合操作符 union ， union all 。使用 union和union all 时，前后查询的结果集中，字段需要一致。♪union
union操作符用于取得两个结果集的并集，当使用该操作符时，会自动去掉结果集中的重复行：mysql> select * from student where class_id=1 union select * from student where name="李四"; +------+--------+----------+ | id | name | class_id | +------+--------+----------+ | 1 | 张三 | 1 | | 2 | 李四 | 1 | +------+--------+----------+ 2 rows in set (0.00 sec)♪union all
union all操作符用于取得两个结果集的并集，当使用该操作符时，不会去掉结果集中的重复行：mysql> select * from student where class_id=1 union all select * from student where name="李四"; +------+--------+----------+ | id | name | class_id | +------+--------+----------+ | 1 | 张三 | 1 | | 2 | 李四 | 1 | | 2 | 李四 | 1 | +------+--------+----------+ 3 rows in set (0.00 sec)
9583
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
