联接查询的使用

内部联接基本语法 ： 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