13.2.12 多表连接查询
指定多个数据表,并指定表别名
使用where指定连接条件
指定多个数据表,并指定表别名
使用where指定连接条件 - [伪代码](/blog/910b9810/#伪代码)
- [广义笛卡尔积](/blog/910b9810/#广义笛卡尔积)
指定多个数据表,并指定表别名
非等值连接
指定多个数据表,并指定表别名
非等值连接
指定多个数据表,并指定表别名
使用where指定连接条件,并指定student_name列不能不为null
指定多个数据表,并指定表别名
使用where指定连接条件,并指定student_name列不能不为null - [外连接](/blog/910b9810/#外连接)
右外连接 - [自连接](/blog/910b9810/#自连接)
简写形式insert,要给所有列赋值,
第一列自增长,可以随意赋值,这里赋值null
13.2.12 多表连接查询 很多时候,需要选择的数据 并不是来自一个表,而是来自多个数据表 ,这就需要使用多表连接查询。例如,对于上面的student_table
和teacher_table
两个数据表,如果希望查询出所有学生以及他的老师名字,这就需要从两个表中取数据。
两种连接规范 SQL92 多表连接查询有两种规范,较早的SQL92
规范支持如下几种多表连接查询。
等值连接 。
非等值连接 。
外连接 。
广义笛卡儿积 。
SQL99 SQL99
规范提供了可读性更好的多表连接语法,并提供了更多类型的连接查询。SQL99
支持如下几种多表连接查询。
交叉连接 。
自然连接 。
使用using
子句的连接 。
使用on
子句的连接 。
全外连接或者左、右外连接 。
1. SQL92的连接查询 SQL92
的多表连接语法比较简洁,这种语法把多个数据表都放在from
之后,多个表之间以逗号隔开 ;连接条件放在where
之后,与查询条件之间用and
逻辑运算符连接 。
如果连接条件要求两列值相等,则称为等值连接 ,否则称为非等值连接 ;
如果没有任何连接条件,则称为广义笛卡儿积 。
语法格式 SQL92
中多表连接查询的语法格式如下:
1 2 3 select column1,column2,...from table1,table2....[where join_condition]
列同名的情况 多表连接查询中可能出现两个或多个数据列具有相同的列名,则需要在这些同名列之间使用表名前缀或表别名前缀作为限制,避免系统混淆。 实际上,所有的列都可以增加表名前缀或表别名前缀。只是进行单表查询时,绝不可能出现同名列,所以系统不可能混淆,因此通常省略表名前缀。 如下SQL
语句查询出所有学生的资料以及对应的老师姓名。
1 2 3 4 5 select s.*,teacher_namefrom student_table s,teacher_table twhere s.java_teacher=t.teacher_id;
查询结果:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 mysql> select * from teacher_table; +------------+--------------+ | teacher_id | teacher_name | +------------+--------------+ | 1 | Yeeku | | 2 | Leegang | | 3 | Martine | +------------+--------------+ 3 rows in set mysql> select * from student_table; +------------+--------------+--------------+ | student_id | student_name | java_teacher | +------------+--------------+--------------+ | 1 | 张三 | 1 | | 2 | 张三 | 1 | | 3 | 李四 | 1 | | 4 | 王五 | 2 | | 5 | _王五 | 2 | | 6 | NULL | 2 | | 7 | 赵六 | 3 | +------------+--------------+--------------+ 7 rows in set mysql> select s.*,teacher_name # 指定多个数据表,并指定表别名 from student_table s,teacher_table t # 使用where指定连接条件 where s.java_teacher=t.teacher_id; +------------+--------------+--------------+--------------+ | student_id | student_name | java_teacher | teacher_name | +------------+--------------+--------------+--------------+ | 1 | 张三 | 1 | Yeeku | | 2 | 张三 | 1 | Yeeku | | 3 | 李四 | 1 | Yeeku | | 4 | 王五 | 2 | Leegang | | 5 | _王五 | 2 | Leegang | | 6 | NULL | 2 | Leegang | | 7 | 赵六 | 3 | Martine | +------------+--------------+--------------+--------------+ 7 rows in set
上面的查询结果正好满足要求,可以看到每个学生以及他对应的老师的名字。
伪代码 实际上,多表查询的过程可理解成一个嵌套循环,这个嵌套循环的伪码如下:
1 2 3 4 5 6 7 8 9 10 11 12 for t in teacher_table{ for s in student_table { if (s.java_teacher=t.teacher_id) { output s+t } } }
广义笛卡尔积 理解了上面的伪码之后,接下来即可很轻易地理解多表连接查询的运行机制。如果求广义笛卡儿积,则where
子句后没有任何连接条件,相当于没有上面的if
语句,广义笛卡儿积的结果会有n×m
条记录。只要把where
后的连接条件去掉,就可以得到广义笛卡儿积,如下SQL
语句所示:
1 2 3 4 select s.*,teacher_namefrom student_table s,teacher_table t;
运行效果如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | student_id | student_name | java_teacher | +------------+--------------+--------------+ | 1 | 张三 | 1 | | 2 | 张三 | 1 | | 3 | 李四 | 1 | | 4 | 王五 | 2 | | 5 | _王五 | 2 | | 6 | NULL | 2 | | 7 | 赵六 | 3 | +------------+--------------+--------------+ 7 rows in set mysql> select * from teacher_table; +------------+--------------+ | teacher_id | teacher_name | +------------+--------------+ | 1 | Yeeku | | 2 | Leegang | | 3 | Martine | +------------+--------------+ 3 rows in set mysql> #不使用连接条件,得到广义笛卡尔积 select s.*,teacher_name #指定多个数据表,并指定表别名 from student_table s,teacher_table t; +------------+--------------+--------------+--------------+ | student_id | student_name | java_teacher | teacher_name | +------------+--------------+--------------+--------------+ | 1 | 张三 | 1 | Yeeku | | 1 | 张三 | 1 | Leegang | | 1 | 张三 | 1 | Martine | | 2 | 张三 | 1 | Yeeku | | 2 | 张三 | 1 | Leegang | | 2 | 张三 | 1 | Martine | | 3 | 李四 | 1 | Yeeku | | 3 | 李四 | 1 | Leegang | | 3 | 李四 | 1 | Martine | | 4 | 王五 | 2 | Yeeku | | 4 | 王五 | 2 | Leegang | | 4 | 王五 | 2 | Martine | | 5 | _王五 | 2 | Yeeku | | 5 | _王五 | 2 | Leegang | | 5 | _王五 | 2 | Martine | | 6 | NULL | 2 | Yeeku | | 6 | NULL | 2 | Leegang | | 6 | NULL | 2 | Martine | | 7 | 赵六 | 3 | Yeeku | | 7 | 赵六 | 3 | Leegang | | 7 | 赵六 | 3 | Martine | +------------+--------------+--------------+--------------+ 21 rows in set
与此类似的是,非等值连接的执行结果可以使用上面的嵌套循环来计算,如下SQL
语句所示:
1 2 3 4 5 select s.*,teacher_id,teacher_namefrom student_table s,teacher_table twhere s.java_teacher>t.teacher_id;
运行结果:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 mysql> select * from student_table; +------------+--------------+--------------+ | student_id | student_name | java_teacher | +------------+--------------+--------------+ | 1 | 张三 | 1 | | 2 | 张三 | 1 | | 3 | 李四 | 1 | | 4 | 王五 | 2 | | 5 | _王五 | 2 | | 6 | NULL | 2 | | 7 | 赵六 | 3 | +------------+--------------+--------------+ 7 rows in set mysql> select * from teacher_table; +------------+--------------+ | teacher_id | teacher_name | +------------+--------------+ | 1 | Yeeku | | 2 | Leegang | | 3 | Martine | +------------+--------------+ 3 rows in set mysql> select s.*,teacher_id,teacher_name # 指定多个数据表,并指定表别名 from student_table s,teacher_table t # 非等值连接 where s.java_teacher>t.teacher_id; +------------+--------------+--------------+------------+--------------+ | student_id | student_name | java_teacher | teacher_id | teacher_name | +------------+--------------+--------------+------------+--------------+ | 4 | 王五 | 2 | 1 | Yeeku | | 5 | _王五 | 2 | 1 | Yeeku | | 6 | NULL | 2 | 1 | Yeeku | | 7 | 赵六 | 3 | 1 | Yeeku | | 7 | 赵六 | 3 | 2 | Leegang | +------------+--------------+--------------+------------+--------------+ 5 rows in set
上面SQL
语句的执行结果相当于if
条件换成了s.java_teacher>t.teacher_id
。 如果还需要对记录进行过滤,则将过滤条件和连接条件使用and
连接起来,如下SQL
语句所示:
1 2 3 4 5 select s.*,teacher_namefrom student_table s,teacher_table twhere s.java_teacher=t.teacher_id and student_name is not null ;
运行结果:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 mysql> select * from student_table; +------------+--------------+--------------+ | student_id | student_name | java_teacher | +------------+--------------+--------------+ | 1 | 张三 | 1 | | 2 | 张三 | 1 | | 3 | 李四 | 1 | | 4 | 王五 | 2 | | 5 | _王五 | 2 | | 6 | NULL | 2 | | 7 | 赵六 | 3 | +------------+--------------+--------------+ 7 rows in set mysql> select * from teacher_table; +------------+--------------+ | teacher_id | teacher_name | +------------+--------------+ | 1 | Yeeku | | 2 | Leegang | | 3 | Martine | +------------+--------------+ 3 rows in set mysql> select s.*,teacher_name # 指定多个数据表,并指定表别名 from student_table s,teacher_table t # 使用where指定连接条件,并指定student_name列不能不为null where s.java_teacher=t.teacher_id and student_name is not null; +------------+--------------+--------------+--------------+ | student_id | student_name | java_teacher | teacher_name | +------------+--------------+--------------+--------------+ | 1 | 张三 | 1 | Yeeku | | 2 | 张三 | 1 | Yeeku | | 3 | 李四 | 1 | Yeeku | | 4 | 王五 | 2 | Leegang | | 5 | _王五 | 2 | Leegang | | 7 | 赵六 | 3 | Martine | +------------+--------------+--------------+--------------+ 6 rows in set
外连接 虽然MySQL
不支持SQL92
中的左外连接、右外连接 ,但本书还是有必要了解一下SQL92
中的左外连接和右外连接。SQL92
中的外连接就是在连接条件的列名后增加括号包起来的外连接符 (+
或*
,不同的数据库有一定的区别),当外连接符出现在左边时称为左外连接,出现在右边时则称为右外连接。如下SQL
语句所示:
1 2 3 4 select s.*,teacher_name from student_table s,teacher_table t # 右外连接 where s.java_teacher=t.teacher_id(*);
外连接就是在外连接符所在的表中增加一个”万能行”,这行记录的所有数据都是null
,而且该行可以与另一个表中所有不满足条件的记录进行匹配,通过这种方式就可以把另一个表中的所有记录选出来,不管这些记录是否满足连接条件。
自连接 除此之外,还有一种自连接
,正如前面介绍外键约束时提到的自关联
,如果同一个表中表中的不同记录之间存在主、外键约束关联 ,例如把员工、经理保存在同一个表里,则需要使用自连接查询 。注意 自连接只是连接的一种用法,并不是一种连接类型 ,不管是SQL92
还是SQL99
都可以使用自连接查询。自连接的本质就是把一个表当成两个表来用 。 下面的SQL
语句建立了一个自关联的数据表,并向表中插入了4条数据。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 create table emp_table( emp_id int auto_increment primary key , emp_name varchar (255 ), manager_id int , foreign key (manager_id) references emp_table(emp_id) ); insert into emp_tablevalues (null ,'唐僧' ,null );insert into emp_tablevalues (null ,'孙悟空' ,1 );insert into emp_tablevalues (null ,'猪八戒' ,1 );insert into emp_tablevalues (null ,'沙僧' ,1 );
如果需要查询该数据表中的所有员工名,以及每个员工对应的经理名,则必须使用自连接查询。所谓自连接就是把一个表当成两个表来用,这就需要为一个表起两个别名,而且查询中用的所有数据列都要加表别名前缀,因为两个表的数据列完全一样。下面的自连接查询可以查询出所有的员工名,以及对应的经理名。
1 2 3 select emp.emp_id,emp.emp_name 员工名,mgr.emp_name 经理名from emp_table emp,emp_table mgrwhere emp.manager_id=mgr.emp_id;
查询结果:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 mysql> select * from emp_table; +--------+----------+------------+ | emp_id | emp_name | manager_id | +--------+----------+------------+ | 1 | 唐僧 | NULL | | 2 | 孙悟空 | 1 | | 3 | 猪八戒 | 1 | | 4 | 沙僧 | 1 | +--------+----------+------------+ 4 rows in set mysql> select emp.emp_id,emp.emp_name 员工名,mgr.emp_name 经理名 from emp_table emp,emp_table mgr where emp.manager_id=mgr.emp_id; +--------+--------+--------+ | emp_id | 员工名 | 经理名 | +--------+--------+--------+ | 2 | 孙悟空 | 唐僧 | | 3 | 猪八戒 | 唐僧 | | 4 | 沙僧 | 唐僧 | +--------+--------+--------+ 3 rows in set
SQL99的连接查询
原文链接: 13.2.12 多表连接查询 1. SQL92的连接查询