立即学习:https://edu.csdn.net/course/play/27328/362530?utm_source=blogtoedu
表连接:在多个表之间通过一定的连接条件,使表之间发生关联,进而能从多个表之间获取数据
语法:
select table1.column,table2.column from table1,table2 where table1.column = table2.column
三种方式:内连接,外连接,自连接
MySQL中没有全连接,交叉连接应用中没什么意义
例:
score表:
+----+----------+---------+-------+
| id | course | stu_no | score |
+----+----------+---------+-------+
| 1 | 计算机 | 2016001 | 99 |
| 2 | 离散数学 | 2016001 | 85 |
| 3 | 计算机 | 2016002 | 78 |
+----+----------+---------+-------+
student表:
+---------+--------+----------+
| stu_no | name | address |
+---------+--------+----------+
| 2016001 | 张三 | 贵州贵阳 |
| 2016002 | 李芳 | 陕西兴平 |
| 2016003 | 张晓燕 | 江西南昌 |
+---------+--------+----------+
内连接:
select A.stu_no, A.name, B.course, B.score
from student A join score B on(A.stu_no = B.stu_no);
这样写也行:
select A.stu_no, A.name, B.course, B.score
from student A ,score B where A.stu_no = B.stu_no;
+---------+------+----------+-------+
| stu_no | name | course | score |
+---------+------+----------+-------+
| 2016001 | 张三 | 计算机 | 99 |
| 2016001 | 张三 | 离散数学 | 85 |
| 2016002 | 李芳 | 计算机 | 78 |
+---------+------+----------+-------+
左连接:
select A.stu_no, A.name, B.course, B.score
from student A left join score B on(A.stu_no = B.stu_no);
+---------+--------+----------+-------+
| stu_no | name | course | score |
+---------+--------+----------+-------+
| 2016001 | 张三 | 计算机 | 99 |
| 2016001 | 张三 | 离散数学 | 85 |
| 2016002 | 李芳 | 计算机 | 78 |
| 2016003 | 张晓燕 | NULL | NULL |
+---------+--------+----------+-------+