MySQL数据库多表查询
2018-09-17 本文已影响21人
泡泡龙吐泡泡
1. 简介
多表查询就是将多个表的数据横向联合起来。多表查询的分类有:
1)内连接
2)外链接: 左外链接,右外连接
3)交叉连接
4)自然连接
2.多表查询
2.1 内连接【inner join】
语法一:select 列名 from 表1 inner join 表2 on 表1.公共字段=表2.公共字段
语法二:selcet 列名 from 表1,表2 where 表1.公共字段=表2.公共字段
例题:
方法一:
mysql> select stuname,stusex,writtenexam,labexam from stuinfo inner join stumarks on stuinfo.stuno=stumarks.stuno;
+--------------+--------+-------------+---------+
| stuname | stusex | writtenexam | labexam |
+--------------+--------+-------------+---------+
| 李斯文 | 女 | 80 | 58 |
| 李文才 | 男 | 50 | 90 |
| 欧阳俊雄 | 男 | 65 | 50 |
| 张秋丽 | 男 | 77 | 82 |
| 争青小子 | 男 | 56 | 48 |
+--------------+--------+-------------+---------+
方法二:
mysql> select stuname,stusex,writtenexam,labexam from stuinfo,stumarks where stuinfo.stuno=stumarks.stuno;
+--------------+--------+-------------+---------+
| stuname | stusex | writtenexam | labexam |
+--------------+--------+-------------+---------+
| 李斯文 | 女 | 80 | 58 |
| 李文才 | 男 | 50 | 90 |
| 欧阳俊雄 | 男 | 65 | 50 |
| 张秋丽 | 男 | 77 | 82 |
| 争青小子 | 男 | 56 | 48 |
+--------------+--------+-------------+---------+
脚下留心:显示公共字段需要指定表名
不指定公共字段的表名会报错:
mysql> select stuno,stuname,stusex,writtenexam,labexam from stuinfo inner join stumarks on stuinfo.stuno=stumarks.stuno;
ERROR 1052 (23000): Column 'stuno' in field list is ambiguous
指定公共字段的表名:
mysql> select stuinfo.stuno,stuname,stusex,writtenexam,labexam from stuinfo inner join stumarks on stuinfo.stuno=stumarks.stuno;
+--------+--------------+--------+-------------+---------+
| stuno | stuname | stusex | writtenexam | labexam |
+--------+--------------+--------+-------------+---------+
| s25303 | 李斯文 | 女 | 80 | 58 |
| s25302 | 李文才 | 男 | 50 | 90 |
| s25304 | 欧阳俊雄 | 男 | 65 | 50 |
| s25301 | 张秋丽 | 男 | 77 | 82 |
| s25318 | 争青小子 | 男 | 56 | 48 |
+--------+--------------+--------+-------------+---------+
多学一招:三个表的内连接如何实现?
select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段
inner join 表3 on 表2.公共字段=表3.公共字段
2.2 左外连接【left join】
以左边的表为标准,如果右边的表没有对应的记录,用NULL填充。
语法:select 列名 from 表1 left join 表2 on 表1.公共字段=表2.公共字段
例题:
mysql> select stuname,writtenexam,labexam from stuinfo left join stumarks on stuinfo.stuno=stumarks.stuno;
+--------------+-------------+---------+
| stuname | writtenexam | labexam |
+--------------+-------------+---------+
| 李斯文 | 80 | 58 |
| 李文才 | 50 | 90 |
| 欧阳俊雄 | 65 | 50 |
| 张秋丽 | 77 | 82 |
| 争青小子 | 56 | 48 |
| 诸葛丽丽 | NULL | NULL |
| 梅超风 | NULL | NULL |
+--------------+-------------+---------+
2.3 右外连接【right join】
以右边的表为标准,如果左边的表没有对应的记录,用NULL填充。
语法:select 列名 from 表1 right join 表2 on 表1.公共字段=表2.公共字段
例题:
mysql> select stuname,writtenexam,labexam from stuinfo right join stumarks on stuinfo.stuno=stumarks.stuno;
+--------------+-------------+---------+
| stuname | writtenexam | labexam |
+--------------+-------------+---------+
| 李斯文 | 80 | 58 |
| 李文才 | 50 | 90 |
| 欧阳俊雄 | 65 | 50 |
| 张秋丽 | 77 | 82 |
| 争青小子 | 56 | 48 |
| NULL | 66 | 77 |
+--------------+-------------+---------+
2.4 交叉连接【cross join】
插入测试数据:
mysql> create table t1(
-> id int,
-> name varchar(10)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> insert into t1 values (1,'tom'),(2,'berry');
Query OK, 2 rows affected (0.00 sec)
mysql> create table t2(
-> id int,
-> score int
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t2 values (1,99),(2,88);
- 如果没有连接表达式,返回的是笛卡尔积:
mysql> select * from t1 cross join t2;
+------+-------+------+-------+
| id | name | id | score |
+------+-------+------+-------+
| 1 | tom | 1 | 99 |
| 2 | berry | 1 | 99 |
| 1 | tom | 2 | 88 |
| 2 | berry | 2 | 88 |
+------+-------+------+-------+
- 如果有连接表达式,等价于内连接:
mysql> select * from t1 cross join t2 where t1.id=t2.id;
+------+-------+------+-------+
| id | name | id | score |
+------+-------+------+-------+
| 1 | tom | 1 | 99 |
| 2 | berry | 2 | 88 |
+------+-------+------+-------+
2.5 自然连接【natural】
自然连接,自动地通过判断条件连接,它是通过同名字段来判断的。
自然连接又分为:
- 自然内连接 natural join
- 自然左外连接 natural left join
- 自然右外连接 natural right join
例题:
# 自然内连接
mysql> select * from stuinfo natural join stumarks;
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | examNo | writtenExam | labExam |
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | s271811 | 80 |
58 |
| s25302 | 李文才 | 男 | 31 | 3 | 上海 | s271813 | 50 |
90 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | s271815 | 65 |
50 |
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | s271816 | 77 |
82 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | s271819 | 56 |
48 |
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
5 rows in set (0.00 sec)
# 自然左外连接
mysql> select * from stuinfo natural left join stumarks;
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | examNo | writtenExam | labExam |
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | s271816 | 77
82 |
| s25302 | 李文才 | 男 | 31 | 3 | 上海 | s271813 | 50 |
90 |
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | s271811 | 80 |
58 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | s271815 | 65
50 |
| s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 | NULL | NULL
NULL |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | s271819 | 56
48 |
| s25319 | 梅超风 | 女 | 23 | 5 | 河北 | NULL | NULL |
ULL |
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
7 rows in set (0.00 sec)
# 自然右外连接
mysql> select * from stuinfo natural right join stumarks;
+--------+---------+-------------+---------+----------+--------+--------+---------+------------+
| stuNo | examNo | writtenExam | labExam | stuName | stuSex | stuAge | stuSeat | stuAddress |
+--------+---------+-------------+---------+----------+--------+--------+---------+------------+
| s25303 | s271811 | 80 | 58 | 李斯文 | 女 | 22 | 2 | 北京
|
| s25302 | s271813 | 50 | 90 | 李文才 | 男 | 31 | 3 | 上海
|
| s25304 | s271815 | 65 | 50 | 欧阳俊雄 | 男 | 28 | 4 | 天津
|
| s25301 | s271816 | 77 | 82 | 张秋丽 | 男 | 18 | 1 | 北京
|
| s25318 | s271819 | 56 | 48 | 争青小子 | 男 | 26 | 6 | 天津
|
| s25320 | s271820 | 66 | 77 | NULL | NULL | NULL | NULL | NULL |
+--------+---------+-------------+---------+----------+--------+--------+---------+------------+
6 rows in set (0.00 sec)
自然连接结论:
-
表连接通过同名的字段来连接的
-
如果没有同名的字段返回笛卡尔积
-
会对结果进行整理,整理的规则如下
a)连接字段保留一个
b)连接字段放在最前面
c)左外连接左边在前,右外连接右表在前
2.6 using()
- 用来指定连接字段
- using()也会对连接字段进行整理,整理方式和自然连接一样。
例题:
mysql> select * from stuinfo inner join stumarks using(stuno);
+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | examNo | writtenExam | labExam |
+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | s271811 | 80 | 58 |
| s25302 | 李文才 | 男 | 31 | 3 | 上海 | s271813 | 50 | 90 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | s271815 | 65 | 50 |
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | s271816 | 77 | 82 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | s271819 | 56 | 48 |
+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+
5 rows in set (0.00 sec)