多表连接查询:
建立两张初始表:
表一:A
s_id s_name
1 小王
2 小张
3 小李
表二:B
b_id b_score b_number
1 93 1
2 88 2
注意!建表时以表B b_number 为外键连接表A s_id
1、内连接
语法:(表名 + inner join + 表名 + on + 外键约束)
----查询学生对应的考试成绩
select * from A inner join B on b_number = s_id;
显示结果:
+------+--------+------+---------+----------+
| s_id | s_name | b_id | b_score | b_number |
+------+--------+------+---------+----------+
| 1 | 小王 | 1 | 93 | 1 |
| 2 | 小张 | 2 | 88 | 2 |
+------+--------+------+---------+----------+
2、外连接
-》左连接
语法:(表名 + left join + 表名 + on + 外键约束)
select * from A left join B on b_number = s_id;
显示结果:
+------+--------+------+---------+----------+
| s_id | s_name | b_id | b_score | b_number |
+------+--------+------+---------+----------+
| 1 | 小王 | 1 | 93 | 1 |
| 2 | 小张 | 2 | 88 | 2 |
| 3 | 小李 | NULL | NULL | NULL |
+------+--------+------+---------+----------+
(由于左连接以A表为主表,B表没有小李相关的考试信息,所以显示为空)
-》右连接
语法:(表名 + right join + 表名 + on + 外键约束)
select * from A right join B on b_number = s_id;
显示结果:
+------+--------+------+---------+----------+
| s_id | s_name | b_id | b_score | b_number |
+------+--------+------+---------+----------+
| 1 | 小王 | 1 | 93 | 1 |
| 2 | 小张 | 2 | 88 | 2 |
+------+--------+------+---------+----------+
左连查询和右连查询的区别是:以执行语句中的哪个表为主表,左连接即左为主表(右连接即右表),主表中有的数据才显示,主表中没有的数据即使附表中有也不显示在结果中,如附表有一行或多行记录为空则显示null。
3、子查询
查询小王的考试成绩:(单行单列)
select b_score from B where b_number = (select s_id from A where s_name = '小王');
显示结果:
+---------+
| b_score |
+---------+
| 93 |
+---------+
嵌套的语句查询中如果子查询结果返回多个时要用in 关键词约束(多行单列)
如:现要求查询参加考试的学生姓名
select s_name from A where s_id in (select b_number from B);
(在 B 成绩表中出现过的学生id)
显示结果:
+--------+
| s_name |
+--------+
| 小王 |
| 小张 |
+--------+