MySQL链接
本文章所有表格:calzz,stu_info
自然连接
使用WHERE条件将两个表进行关联查询
-- 查询学员的学号、姓名、所在班级名称
SELECT
c.`code` AS '班级',
s.`name` AS '姓名',
s.`code` AS '学号'
FROM calzz c,stu_info s
WHERE c.`code` = s.clazz
注意:条件是一定要加的,否则会出现错误
内连接
内连接是可以代替自然连接的,但是效率更高
SELECT
c.`code` AS '班级',
s.`name` AS '姓名',
s.`code` AS '学号'
FROM calzz c
INNER JOIN stu_info s ON c.`code` = s.clazz;
左外连接
以左表为主,左表中的数据都会显示出来,所关联的右表中,有数据就会显示数据,没有则显示NULL
-- 左连接
SELECT
c.`code` AS '班级',
s.`name` AS '姓名',
s.`code` AS '学号'
FROM calzz c
LEFT JOIN stu_info s ON c.`code` = s.clazz;
注意:条件是用ON
右外连接
以右表为主,右表中的数据都会显示出来,所关联的左表中,有数据就会显示数据,没有则显示NULL
-- 右连接
SELECT
c.`code` AS '班级',
s.`name` AS '姓名',
s.`code` AS '学号'
FROM calzz c
RIGHT JOIN stu_info s ON c.`code` = s.clazz
全连接
MySQL并不支持FULL JOIN,只能使用UNION和UNION ALL
UNION
UNION会删除结果中重复的部分
SELECT
c.`code` AS '班级',
s.`name` AS '姓名',
s.`code` AS '学号'
FROM calzz c
LEFT JOIN stu_info s ON c.`code` = s.clazz
UNION
SELECT
c.`code` AS '班级',
s.`name` AS '姓名',
s.`code` AS '学号'
FROM calzz c
RIGHT JOIN stu_info s ON c.`code` = s.clazz;
UNION ALL
UNION ALL会列出所有记录,且效率高于UNION,当确定没有重复数据时使用UNION ALL 可以提高效率
SELECT
c.`code` AS '班级',
s.`name` AS '姓名',
s.`code` AS '学号'
FROM calzz c
LEFT JOIN stu_info s ON c.`code` = s.clazz
UNION ALL
SELECT
c.`code` AS '班级',
s.`name` AS '姓名',
s.`code` AS '学号'
FROM calzz c
RIGHT JOIN stu_info s ON c.`code` = s.clazz;