内部联接基本语法 ： SELECT 字段列表 FROM 表1 INNER JOIN 表2 ON 条件表达式
USE Students
SELECT * FROM StuInfo
SELECT * FROM StuMarks
--显示学生信息及学生成绩
SELECT * FROM StuInfo INNER JOIN StuMarks ON StuInfo.StuID=StuMarks.StuID
--只选择需要使用的列
SELECT StuInfo.StuID,StuName,StuSex,Subject,Score FROM StuInfo INNER JOIN StuMarks ON StuInfo.StuID=StuMarks.StuID
-- 带条件的内部联接 查询分数大于等于60的学员信息及相关科目和分数
SELECT StuInfo.StuID,StuName,StuSex,Subject,Score FROM StuInfo INNER JOIN StuMarks ON StuInfo.StuID=StuMarks.StuID WHERE Score >=60
-- INNER JOIN -- 类似WHERE子句
SELECT s1.StuID,StuName,StuSex,Subject,Score FROM StuInfo s1,StuMarks s2 WHERE s1.StuID=s2.StuID AND Score>=60
外部联接查询语法：SELECT 字段列表 FROM 表1<LEFT/RIGHT>[OUTER] JOIN 表2 ON 条件表达式
USE Students
INSERT INTO StuInfo(StuID,StuName,StuSex) VALUES (6,'林黛玉','女')
SELECT * FROM StuInfo
SELECT * FROM StuMarks
-- 内部联接
SELECT s1.StuID,StuName,StuSex,Subject,Score FROM StuInfo s1 INNER JOIN StuMarks s2 on s1.StuID=s2.StuID
-- 左外联接
SELECT s1.StuID,StuName,StuSex,Subject,Score FROM StuInfo s1 LEFT JOIN StuMarks s2 on s1.StuID=s2.StuID
交叉联接语法：SELECT 字段列表 FROM 表1 CROSS JOIN 表2
USE Students
SELECT * FROM StuInfo
SELECT * FROM StuMarks
-- 交叉联接
SELECT s1.StuID,StuName,StuSex,Subject,Score FROM StuInfo s1 CROSS JOIN StuMarks
集合运算
- 使用UNION和UNION ALL 进行并集运算
USE Students
SELECT * FROM StuMarks
-- 在StuMarks表中查询分数大于等于70分的记录
SELECT * FROM StuMarks WHERE Score >=70
-- 在stuMarks表中查询StuID等于1的记录
SELECT * FROM StuMarks WHERE StuID = 1
--使用UNION
SELECT * FROM StuMarks WHERE Score >=70
UNION
SELECT * FROM StuMarks WHERE StuID = 1
--使用UNION ALL
SELECT * FROM StuMarks WHERE Score >=70
UNION ALL
SELECT * FROM StuMarks WHERE StuID = 1
- 使用INTERSECT 进行交集 运算
USE Students
SELECT * FROM StuMarks
-- 在StuMarks表中查询分数大于等于70分的记录
SELECT * FROM StuMarks WHERE Score >=70
-- 在stuMarks表中查询StuID等于1的记录
SELECT * FROM StuMarks WHERE StuID = 1
--使用intersect进行交集运算
SELECT * FROM StuMarks WHERE Score >=70
INTERSECT
SELECT * FROM StuMarks WHERE StuID = 1
- 使用EXCEPT 进行减集运算
USE Students
SELECT * FROM StuMarks
-- 在StuMarks表中查询分数大于等于70分的记录
SELECT * FROM StuMarks WHERE Score >=70
-- 在stuMarks表中查询StuID等于1的记录
SELECT * FROM StuMarks WHERE StuID = 1
--使用intersect进行交集运算
SELECT * FROM StuMarks WHERE Score >=70
EXCEPT
SELECT * FROM StuMarks WHERE StuID = 1
网友评论