& & & & 复制代码代码如下:& & DECLARE&@table&TABLE&([Values]&varchar(10))& & Insert&INTO&@table&Select&'AAA'& & UNION&ALL&Select&'CCC'&n……
声明：该文章系网友上传分享，此内容仅代表网友个人经验或观点，不代表本网站立场和观点；若未进行原创声明，则表明该文章系转载自互联网；若该文章内容涉嫌侵权，请及时向
论文写作技巧
上一篇：下一篇：
相关经验教程SQL总结（二）连表查询 - 停留的风 - 博客园
posts - 233, comments - 1215, trackbacks - 5, articles - 27
---恢复内容开始---
SQL总结（二）连表查询
连接查询包括合并、内连接、外连接和交叉连接，如果涉及多表查询，了解这些连接的特点很重要。
只有真正了解它们之间的区别，才能正确使用。
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
UNION&运算符通过组合其他两个结果表（例如&TABLE1&和&TABLE2）并消去表中任何重复行而派生出一个结果表。
当&ALL&随&UNION&一起使用时（即&UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自&TABLE1&就是来自&TABLE2。
注意：使用UNION时，两张表查询的结果有相同数量的列、列类型相似。
学生表信息（Students）：
教师表信息（Teachers）：
预置脚本：
INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(101,'Tom',20,'BeiJing',10)
INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(102,'Lucy',18,'ShangHai',11)
INSERT INTO Teachers(ID,Name) VALUES(101,'Mrs Lee')
INSERT INTO Teachers(ID,Name) VALUES(102,'Lucy')
1）基本UNION查询，查询学校教师、学生的总的信息表，包括ID和姓名
SELECT ID,Name FROM Students
SELECT ID,Name FROM Teachers
查询结果：
2)带条件的UNION查询，也可以查询同一张表，查询年龄为18，23岁的学生信息
SELECT ID,Name FROM Student WHERE Age=18
SELECT ID,Name FROM Student WHERE Age=23
当然，这可以使用IN或者OR很容易实现，这里只是点到，以后遇到复杂查询，相信你会用到。
3）查询教师学生全部姓名
&因为UNION只会选择不同的值，如果学生中和教师中有重名的情况，这就需要UNION ALL
SELECT Name FROM Students
SELECT Name FROM Teachers
查询结果：
2、INNER JOIN（内连接）
INNER JOIN（内连接），也成为自然连接
作用：根据两个或多个表中的列之间的关系，从这些表中查询数据。
注意:&内连接是从结果中删除其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。
重点：内连接，只查匹配行。
语法：(INNER可省略)
SELECT fieldlist
FROM table1 [INNER] join table2
ON table1.column=table2.column
学生表信息（Students）：
专业信息表（Majors）：&
&预置脚本：
DELETE FROM Students
INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(101,'Tom',20,'BeiJing',10)
INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(102,'Lucy',18,'ShangHai',11)
DELETE FROM Majors
INSERT INTO Majors(ID,Name) VALUES(10,'English')
INSERT INTO Majors(ID,Name) VALUES(12,'Computer')
实例：查询学生信息，包括ID,姓名、专业名称
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students INNER JOIN Majors
ON Students.MajorID = Majors.ID
&查询结果：
根据结果可以清晰看到，确实只有匹配的行。学生Lucy的信息丢失了。
但是，inner join也会产生重复数据。如果将Majors表的主键约束去掉，可以插入重复的ID，如：
DELETE FROM Majors
INSERT INTO Majors(ID,Name) VALUES(10,'English')
INSERT INTO Majors(ID,Name) VALUES(10,'Computer')
继续执行上面的关联语句，结果为：
如果是LEFT JOIN也会有重复记录，其结果为：
RIGHT JOIN 结果与INNER JOIN一样。
后续我们会深入研究JOIN的具体原理。
与内连接相比，即使没有匹配行，也会返回一个表的全集。
外连接分为三种：左外连接，右外连接，全外连接。对应SQL：LEFT/RIGHT/FULL OUTER JOIN。通常我们省略outer 这个关键字。写成：LEFT/RIGHT/FULL JOIN。
重点：至少有一方保留全集，没有匹配行用NULL代替。
1）LEFT OUTER JOIN，简称LEFT JOIN，左外连接（左连接）
结果集保留左表的所有行，但只包含第二个表与第一表匹配的行。第二个表相应的空行被放入NULL值。
依然沿用内链接的例子
（1）使用左连接查询学生的信息，其中包括学生ID，学生姓名和专业名称。
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students LEFT JOIN Majors
ON Students.MajorID = Majors.ID
通过结果，我们可以看到左连接包含了第一张表的所有信息，在第二张表中如果没有匹配项，则用NULL代替。&
2）RIGHT JOIN(right outer join)右外连接(右连接)
右外连接保留了第二个表的所有行，但只包含第一个表与第二个表匹配的行。第一个表相应空行被入NULL值。
右连接与左连接思想类似。只是第二张保留全集，如果第一张表中没有匹配项，用NULL代替
依然沿用内链接的例子，只是改为右连接
（1）使用右连接查询学生的信息，其中包括学生ID，学生姓名和专业名称。
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students RIGHT JOIN Majors
ON Students.MajorID = Majors.ID
查询结果：
通过结果可以看到，包含了第二张表Majors的全集，Computer在Students表中没有匹配项，就用NULL代替。&
3）FULL JOIN (FULL OUTER JOIN，全外连接）
全外连接，简称：全连接。会把两个表所有的行都显示在结果表中
1)使用全连接查询学生的信息，其中包括学生ID，学生姓名和专业名称。
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students FULL JOIN Majors
ON Students.MajorID = Majors.ID
查询结果：&
包含了两张表的所有记录，没有记录丢失，没有匹配的行用NULL代替。
4、CROSS JOIN（交叉连接）
交叉连接。交叉连接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉连接也称作笛卡尔积。&
简单查询两张表组合，这是求笛卡儿积，效率最低。
笛卡儿积：笛卡尔乘积，也叫直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有，如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选课情况。
1）交叉连接查询学生的信息，其中包括学生ID，学生姓名和专业名称。
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students CROSS JOIN Majors
查询结果：
2）查询多表，其实也是笛卡儿积，与CROSS JOIN等价，以下查询同上述结果一样。
这个可能很常见，但是大家一定要注意了，这样就查询了两张表中所有组合的全集。
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students,Majors
3）加了查询条件
注意:在使用CROSS JOIN关键字交叉连接表时，因为生成的是两个表的笛卡尔积，因而不能使用ON关键字，只能在WHERE子句中定义搜索条件。
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students CROSS JOIN Majors
WHERE Students.MajorID = Majors.ID
查询结果：
查询结果与INNER JOIN一样，但是其效率就慢很多了。
5、全部实例脚本&
CREATE DATABASE TestDB
USE TestDB
------------------------------------------
--创建相关表
IF OBJECT_ID('Students','U') IS NOT NULL
DROP TABLE Students
--学生信息表
CREATE TABLE Students(
ID int primary key not null,
Name nvarchar(50),
City nvarchar(50),
MajorID int
--专业信息表
IF OBJECT_ID('Majors','U') IS NOT NULL
DROP TABLE Majors
CREATE TABLE Majors(
ID int primary key not null,
Name nvarchar(50)
--教师信息表
IF OBJECT_ID('Teachers','U') IS NOT NULL
DROP TABLE Teachers
CREATE TABLE Teachers(
ID int primary key not null,
Name nvarchar(20) not null
--预置数据
DELETE FROM Students
INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(101,'Tom',20,'BeiJing',10)
INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(102,'Lucy',18,'ShangHai',11)
DELETE FROM Majors
INSERT INTO Majors(ID,Name) VALUES(10,'English')
INSERT INTO Majors(ID,Name) VALUES(12,'Computer')
DELETE FROM Teachers
INSERT INTO Teachers(ID,Name) VALUES(101,'Mrs Lee')
INSERT INTO Teachers(ID,Name) VALUES(102,'Lucy')
SELECT ID,Name FROM Students
SELECT ID,Name FROM Teachers
SELECT ID,Name FROM Students
SELECT ID,Name FROM Teachers
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students INNER JOIN Majors
ON Students.MajorID = Majors.ID
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students LEFT JOIN Majors
ON Students.MajorID = Majors.ID
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students LEFT JOIN Majors
ON Students.MajorID = Majors.ID
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students FULL JOIN Majors
ON Students.MajorID = Majors.ID
--交叉连接
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students CROSS JOIN Majors
--交叉连接
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students CROSS JOIN Majors
WHERE Students.MajorID = Majors.ID
--一次查询多表
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students,MajorsSQL总结（一）基本查询 - 停留的风 - 博客园
posts - 233, comments - 1215, trackbacks - 5, articles - 27
SQL总结（一）基本查询
&SQL查询的事情很简单，但是常常因为很简单的事情而出错。遇到一些比较复杂的查询我们更是忘记了SQL查询的基本语法。
本文希望通过简单的总结，把常用的查询方法予以总结，希望能够明确在心。
场景：学生信息系统，包括学生信息、教师信息、专业信息和选课信息。
--学生信息表
IF OBJECT_ID (N'Students', N'U') IS NOT NULL
DROP TABLE S
CREATE TABLE Students(
ID int primary key not null,
Name nvarchar(50),
City nvarchar(50),
MajorID int
--专业信息表
IF OBJECT_ID (N'Majors', N'U') IS NOT NULL
DROP TABLE M
CREATE TABLE Majors(
ID int primary key not null,
Name nvarchar(50)
IF OBJECT_ID (N'Courses', N'U') IS NOT NULL
DROP TABLE C
CREATE TABLE Courses(
ID int primary key not null,
Name nvarchar(50) not null
IF OBJECT_ID (N'SC', N'U') IS NOT NULL
DROP TABLE SC;
CREATE TABLE SC(
StudentID int not null,
CourseID int not null,
1、基本查询
从表中查询某些列的值，这是最基本的查询语句。
SELECT 列名1,列名2 FROM 表名
2、Where（条件）
作用：按照一定的条件查询数据
SELECT 列名1,列名2 FROM 表名 WHERE 列名1 运算符
运算符描述
在某个范围内
搜索某种模式
比较操作符都比较简单，不再赘述。关于BETWEEN和LIKE，专门拿出来重点说下
3、BETWEEN
在两个值之间，比如我从学生中查询年龄在18-20之间的学生信息
SELECT ID,Name,Age FROM Students WHERE Age BETWEEN 18 AND 20
作用：模糊查询。LIKE关键字与通配符一起使用
主要的通配符：
替代一个或多个字符
仅替代一个字符
[charlist]
字符列中的任何单一字符
[^charlist]
[!charlist]
不在字符列中的任何单一字符
1）查询姓氏为张的学生信息
SELECT ID,Name FROM Students WHERE Name LIKE '张%'
&2）查询名字最后一个为&生&的同学
SELECT ID,Name FROM Students WHERE Name LIKE '%生'
3）查询名字中含有&生&的学生信息
SELECT ID,Name FROM Students WHERE Name LIKE '%生%'
4）查询姓名为两个字，且姓张学生信息
SELECT ID,Name FROM Students WHERE Name LIKE '张_'
5)查询姓氏为张、李的学生信息
这个可以使用or关键字，但是使用通配符更简单高效
SELECT ID,Name FROM Students WHERE Name LIKE '[张李]%'
6)查询姓氏非张、李的学生信息
这个也可以使用NOT LIKE 来实现，用下面方法更好。
SELECT ID,Name FROM Students WHERE Name LIKE '[^张李]%'
SELECT ID,Name FROM Students WHERE Name LIKE '[!张李]%'
AND 在 WHERE 子语句中把两个或多个条件结合起来。表示和的意思，多个条件都成立。
1）查询年龄大于18且姓张的学生信息
SELECT ID,Name FROM Students WHERE Age&18 AND Name LIKE '张%'
&OR可在 WHERE 子语句中把两个或多个条件结合起来。或关系，表示多个条件，只有一个符合即可。
1)查询姓氏为张、李的学生信息
SELECT ID,Name FROM Students WHERE Name LIKE '张%' OR Name LIKE '李%'
IN 操作符允许我们在 WHERE 子句中规定多个值。表示：在哪些值当中。
1）查询年龄是18、19、20的学生信息
SELECT ID,Name FROM Students WHERE Age IN (18,19,20)
8、NOT 否定
NOT对于条件的否定，取非。
1）查询非张姓氏的学习信息
SELECT ID,Name FROM Students WHERE Name NOT LIKE '张%'
9、ORDER BY（排序）
功能：对需要查询后的结果集进行排序
1）查询学生信息表的学号、姓名、年龄，并按Age升序排列
SELECT ID,Name,Age FROM Students ORDER BY Age
SELECT ID,Name,Age FROM Students ORDER BY Age ASC
2)查询学生信息，并按Age倒序排列
SELECT ID,Name,Age FROM Students ORDER BY Age DESC
除了制定某个列排序外，还能指定多列排序，每个排序字段可以制定排序规则
说明：优先第一列排序，如果第一列相同，则按照第二列排序规则执行，以此类推。
3）查询学生的信息，按照总成绩倒序、学号升序排列
SELECT ID,Name,Score FROM Students ORDER BY Score DESC,ID ASC
这个查询含义：首先按Score倒序排列，如果有多条记录Score相同，再按ID升序排列。
查询结果，例子：
&10、AS(Alias)
可以为列名称和表名称指定别名（Alias）
作用：我们可以将查询的列，或者表指定需要的名字，如表名太长，用其简称，在连表查询中经常用到。
1) 将结果列改为需要的名称
SELECT ID AS StudentID,Name AS StudentName FROM Students
2）用表名的别名，标识列的来源
SELECT S.ID,S.Name,M.Name AS MajorName
FROM Students AS S
LEFT JOIN Majors AS M
ON S.MajorID = M.ID
3)在合计函数中，给合计结果命名
SELECT COUNT(ID) AS StudentCount FROM Students
11、Distinct
含义：不同的
作用：查询时忽略重复值。
SELECT DISTINCT 列名称 FROM 表名称
1）查询学生所在城市名，排除重复
SELECT DISTINCT City FROM Student
2)查询成绩分布分布情况
SELECT DISTINCT(Score),Count(ID) FROM Student GROUP BY Score
学生成绩可能重复，以此得到分数、得到这一成绩的学生数。后续会详细介绍GROUP BY 用法。&&
12、MAX/MIN
MAX 函数返回一列中的最大值。NULL 值不包括在计算中。
MIN 函数返回一列中的最小值。NULL 值不包括在计算中。
MIN 和 MAX 也可用于文本列，以获得按字母顺序排列的最高或最低值。
1）查询学生中最高的分数
SELECT MAX(Score) FROM Students
2)查询学生中最小年龄
SELECT MIN(Age) FROM Students
查询某列的合计值。
1）查询ID为1001的学生的各科总成绩
SC即为学生的成绩表，字段：StudentID，CourseID，Score.
SELECT SUM(Score) AS TotalScore FROM SC WHERE StudentID='1001'
AVG 函数返回数值列的平均值
1)查询学生的平均年龄
SELECT AVG(Age) AS AgeAverage FROM Students
2）求课程ID为C001的平均成绩
SELECT AVG(Score) FROM SC WHERE CourseID='C001'
COUNT() 函数返回匹配指定条件的行数。
1)查询学生总数
SELECT COUNT(ID) FROM Students
2)查询学生年龄分布的总数
SELECT COUNT(DISTINCT Age) FROM Students
3)查询男生总数
SELECT COUNT(ID) FROM Students WHERE Sex='男'
4）查询男女生各有多少人
SELECT Sex,COUNT(ID) FROM Students GROUP BY Sex
16、GROUP BY
&GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。
1）查询男女生分布，上面已经给了答案。
SELECT Sex,COUNT(ID) FROM Students GROUP BY Sex
2) 查询学生的城市分布情况
SELECT City,COUNT(ID) FROM Students GROUP BY City
3)学生的平均成绩，查询结果包括：学生ID，平均成绩
SELECT StudentID,AVG(Score) FROM SC GROUP BY StudentID
&4）删除学生信息中重复记录
根据列进行分组，如果全部列相同才定义为重复，则就需要GROUP BY所有字段。否则可按指定字段进行处理。
DELETE FROM Students WHERE ID NOT IN (SELECT MAX(ID) FROM Students GROUP BY ID,Name,Age,Sex,City,MajorID)
17、HAVING
&在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
1）查询平均成绩大等于于60的学生ID及平均成绩
SELECT StudentID,AVG(Score) FROM SC GROUP BY StudentID HAVING AVG(Score)&=60
2）还是用HAVING的SQL语句中，可以有普通的WHERE条件
查询平均成绩大于等于60，且学生ID等于1的学生的ID及平均成绩。
SELECT StudentID,AVG(Score) FROM SC
WHERE StudentID='1'
GROUP BY StudentID
HAVING AVG(Score)&=60
3）查询总成绩在600分以上（包括600）的学生ID
SELECT StudentID FROM SC GROUP BY StudentID HAVING SUM(Score)&=600
TOP 子句用于规定要返回的记录的数目。对于大数据很有用的，在分页时也会常常用到。
1）查询年龄最大的三名学生信息
SELECT TOP 3 ID,Name FROM Students ORDER BY Age DESC
2）还是上一道题，如果有相同年龄的如何处理呢？
SELECT ID,Name,Age FROM Students WHERE Age IN (SELECT TOP 3 Age FROM Students)
19、Case语句&
计算条件列表，并返回多个可能的结果表达式之一。CASE 表达式有两种格式：
CASE 简单表达式，它通过将表达式与一组简单的表达式进行比较来确定结果。
CASE 搜索表达式，它通过计算一组布尔表达式来确定结果。
简单表达式语法：
CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
搜索式语法：
WHEN Boolean_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
1）查询学习信息，如果Sex为0则显示为男，如果为1显示为女，其他显示为其他。
SELECT ID, Name, CASE Sex WHEN '0' THEN '男' WHEN '1' THEN '女' ELSE '其他' END AS Sex
FROM Students
2）查询学生信息，根据年龄统计是否成年，大于等于18为成年，小于18为未成年
SELECT ID, Name, CASE WHEN Age&=18 THEN '成年' ELSE '未成年'END AS 是否成年
FROM Students
3）统计成年未成年学生的个数
SELECT SUM(CASE WHEN Age&=18 THEN
1 ELSE 0 END) AS '成年',SUM(CASE WHEN Age&18 THEN
1 ELSE 0 END) AS '未成年'
FROM Students
&4）行列转换。统计男女生中未成年、成年的人数
结果如下：
SELECT CASE WHEN Sex=0 THEN '男' ELSE '女' END AS '性别',
SUM(CASE WHEN Age&18 THEN 1 ELSE 0 END) AS '未成年',
SUM(CASE WHEN Age&=18 THEN 1 ELSE 0 END) AS '成年'
FROM Students
GROUP BY Sex