阅读 353

MYSQL-多表查询

首先创建数据表tb_departments

create table tb_departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(25), dept_call INT, dept_type VARCHAR(10));

DESC tb_departments;

INSERT INTO tb_departments VALUES (1, 'Computer', 11111, 'A'), (2, 'Math', 22222, 'A'), (3, 'Chinese', 33333, 'B'), (4, 'Economy', 44444, 'B'), (5, 'History', 55555, 'B');
复制代码

学生表信息如下

  • 内连接查询

内连接是通过在查询中设置连接条件的方式,来移除查询结果集中某些数据行后的交叉连接。简单来说,就是利用条件表达式来消除交叉连接的某些数据行,在FROM子句中使用关键字INNER JOIN连接两张表,并使用ON子句来设置连接条件。如果没有任何条件,INNER JOIN和CROSS JOIN在语法上是等同的,两者可以互换。

内连接是系统默认的表连接,所以在FROM子句后可以省略INNER关键字,只用关键字JOIN。使用内连接后,FROM子句中的ON子句可用来设置连接表的条件。

SELECT <列名1,列名2 …> 
FROM <表名1> INNER JOIN <表名2> [ ON子句]
复制代码
SELECT id, name, age, dept_name FROM tb_students_info, tb_departments WHERE tb_students_info.dept_id=tb_departments.dept_id;
复制代码

使用INNER JOIN语法进行内连接查询

SELECT id, name, age, dept_name FROM tb_students_info INNER JOIN tb_departments ON tb_students_info.dept_id=tb_departments.dept_id;
复制代码

  • 外连接查询

内连接是在交叉连接的结果集上返回满足条件的记录;而外连接先将连接的表分为基表和参考表,再以基表为依据返回满足和不满足条件的记录。

外连接更加注重两张表之间的关系。按照连接表的顺序,可以分为左外连接和右外连接。

SELECT name, dept_name FROM tb_students_info s LEFT OUTER JOIN tb_departments d ON s.dept_id=d.dept_id;
复制代码

SELECT name, dept_name FROM tb_students_info s RIGHT OUTER JOIN tb_departments d ON s.dept_id=d.dept_id;
复制代码

  • 自连接查询

自连接是将一个表和它自身进行连接,也是内连接的一种,同样使用INNER JOIN或者JOIN关键字来进行连接。

如果需要在一个表中查找具有相同列值的行,就可以考虑用自连接。注意,在使用自连接的时候,需要为表指定两个不同的别名,且对所有查询列的引用必须使用表别名的限定,否则SELECT操作会失败。

查询id为1的学生所在学院的其他学生的信息,输入的SQL语句和执行结果如下所示。

SELECT s1.id, s1.name FROM tb_students_info s1, tb_students_info s2 WHERE s1.dept_id=s2.dept_id AND s2.id=1;
复制代码