(很详细的)数据库Transact-SQL语句：数据查询（基本查询、嵌套查询、连接查询）

这学期在学数据库及实现，语句比较多，故稍作整理，方便之后查阅：D
有问题还请指出！感谢阅读！

基本查询

SELECT select_list
[FROM table_source]
[WHERE search_condition]
[ORDER BY order_expression (某一列) [ASC|DESC] (可以有多个)] (默认为ASC升序)
[INTO new_table] (放入新表中)
[GROUP BY group_by_expression (某些列)]
[HAVING search_condition]

SELECT子句

SELECT [ ALL | DISTINCT ] [ TOP expression [ PERCENT ] [ WITH TIES] ] [ AS 别名] <select_list>

1. ALL：默认，结果可包含重复行；DISTINCT：结果中只能包含唯一行（去重）（NULL视作重复）
2. TOP expression [ PERCENT ] [ WITH TIES ]：只能从查询结果集返回指定的第一组或制定的百分比数目的行。PERCENT：返回前expression%的行；WITH TIES：还没搞懂
3. <select_list>：column_name [ AS column_alias （别名）]

expression：可以为常量，函数，由一个或多个运算符连接的列名、常量和函数的任意组合，或子查询
函数：AVG( <字段名> ), SUM(<字段名> ), MIN(<字段名> ), MAX(<字段名> ), COUNT(<字段名> )

eg.

USE AdWORK
SELECT AVG(unitPrice) AS averagePrice
FROM Sales.SalesOrder

FROM子句

FROM { <table_source> } [,…n]

eg. 显示employee表中全部员工的姓名和年龄，去掉重名

SELECT DISTINCT employee_name AS ename, YEAR(GETDATE()) - YEAR(birth_date) AS age 
FROM employee

eg. 查询公司员工总数

SELECT COUNT(*) AS staff FROM employee

WHERE子句

WHERE <search_condition>

1. search_condition：可以是单表的条件表达式，也可以是多表之间的
2. 条件运算符：
   • <字段><比较符> ALL (<子查询>)：满足子查询中所有值
   • <字段><比较符> ANY (<子查询>)：满足子查询中任意一个值
   • <字段><比较符> SOME (<子查询>)：同ANY
   • <字段> BETWEEN <范围起始> AND <范围终止>：字段内容在指定范围内
   • EXISTS(<子查询>)：测试子查询中查询结果是否为空，空则返回FALSE
   • <字段> IN <结果集合> | (<子查询>)：常用，判断是否为其中内容，子查询中只能返回一列！
   • <字段> LIKE <字符表达式>："_“表示一个字符；”%"表示0或多个字符，类似正则表达式

eg. 求出男员工的平均工资

SELECT AVG(wages) AS avgSalary
FROM employee
WHERE sex = '男'

eg. 列出部门代号为“D0001”和“D0002”的员工信息

SELECT * FROM employee
WHERE department_id IN ('D0001', 'D0002')

eg. 列出所有姓“张”的员工信息

SELECT * FROM employee
WHERE employee_name LIKE '张%'

等价于

SELECT * FROM employee
WHERE LEFT(employee_name,1) = '张'

eg. 查询所有选修了“C1”课程的学生名

SELECT Sname
FROM students
WHERE id IN (SELECT id FROM courses WHERE co_id = 'C1')

等价于

SELECT Sname
FROM students
WHERE EXISTS
(
SELECT * FROM courses
WHERE students.id = courses.id AND co_id = 'C1'
)

注：EXIST的子查询一般都SELECT *,而IN的子查询只能返回一列

参考：https://zhidao.baidu.com/question/937688736563413612.html

eg. T1:2,3 T2:1,2,3,4

SELECT * FROM T2
WHERE N > ANY(SELECT N FROM T1)

返回3，4

查询结果处理

排序输出

ORDER BY { order_by_expression [ COLLATE collation_name ] [ ASE | DESC ] } [ ,…n]

1. order_by_expression：指定要排序的列名
2. COLLATE：根据collation_name指定的排序规则，如Windows排序规则名称或SQL排序规则名称
3. ASC：默认，升序；DESC：降序

eg. 按部门顺序列出信息，部门相同的再按工资降序排列

SELECT * FROM employee
ORDER BY department_id, wages DESC

INTO子句

把查询结果放到新表中
INTO new_table

eg.

SELECT * INTO TestTable
FROM employee
WHERE department_id = 'D0001'

UNION子句

将两个或多个查询结果合并为单个结果集
{ <query_specification> } UNION [ ALL ] { <query_specification> } [ UNION [ ALL ] { <query_specification> } [,…n] ]

1. <query_specification>：查询表达式
2. ALL：允许结果中有重复行（默认去重）

eg. 列出部门编号为’D0001’'D0002’的所有员工姓名

SELECT employee_name, department_id
FROM employee
WHERE depatment_id = 'D0001'
UNION
SELECT employee_name, department_id
FROM employee
WHERE depatment_id = 'D0002'

GROUP BY子句

对每一组进行聚集操作（count，avg，min，max，sum）
将结果分为几组，针对每一组返回一行
GROUP BY < group by item > [,…n]

< group by item >：

1. <column_expression>：按某列分组的列名
2. ROLLUP( < composite element list > )
3. CUBE( < composite element list > )
4. GROUPING SET( < grouping set list> )

eg. 分别统计各部门员工人数

SELECT department_id, COUNT(employee_id) AS staff
FROM employee
GROUP BY department_id

eg. 分别统计各部门男女员工的人数

SELECT department_id, sex, COUNT(*) AS staff
FROM employee
GROUP BY department_id, sex

HAVING 子句

将分组后的查询结果按一定条件筛选
HAVING <search_condition>

eg. 列出部门平均工资大于4000元的部门编号和平均工资

SELECT department_id, AVG(wages) AS avgSalary
FROM employee
GROUP BY department_id
HAVING AVG(wages) > 4000

WHERE：在group之前去掉不满足的行
HAVING：过滤分组里的结果

COMPUTE & COPUTE BY 子句

生成合计作为附加的汇总列出现在结果集的最后
COMPUTE { { AVG | COUNT | MAX | MIN | STDEV | VAR | VARP | SUM } (expression) } [,…n] [ BY expression [ ,…n ] }

eg. 对employee表中部门编号为D0001的员工工资，按部门编号生成汇总行和明细行

SELECT department_id, wages
FROM employee
WHERE department_id = 'D0001'
ORDER BY department_id
COMPUTE sum(wages)

eg. 对employee表中部门编号为D0001或D0002的员工工资，按部门编号生成汇总行和明细行

SELECT department_id, wages
FROM employee
WHERE department_id = 'D0001' OR department_id = 'D0002'
ORDER BY department_id
COMPUTE sum(wages) BY department_id

EXCEPT & INTERSECT

比较两查询结果
EXCEPT：从EXCEPT左边的查询中返回右边查询未返回的所有非重复值（左有右没有）
INTERSECT：返回INTERSECT左右边军返回的所有非重复值

嵌套查询

注：子查询的结果不显示，所以不能对子查询用ORDER BY

单值嵌套查询

子查询返回结果是一个值
eg. 列出经理办的所有员工的编号

SELECT employee_id
FROM employee
WHERE department_id = 
(
SELECT department_id
FROM department
WHERE department_name = '经理办'
)

多值嵌套查询

子查询返回结果是一列值。通常使用ANY(SOME)、ALL和IN运算符

ANY运算符

eg. 列出D0001部门中工资比D0002部门最低工资高的员工编号和工资

SELECT employee_id, wages FROM employee
WHERE department_id = 'D0001'
AND wages > ANY(SELECT wages FROM employee WHERE department_id = 'D0002')

ALL运算符

eg. 列出D0001部门中工资比D0002部门最高工资高的员工编号和工资

SELECT employee_id, wages FROM employee
WHERE department_id = 'D0001'
AND wages > ALL(SELECT wages FROM employee WHERE department_id = 'D0002')

IN运算符

列出部门为市场部或销售部的所有员工的编号

SELECT employee_id FROM employee
WHERE department_id IN
(SELECT department_id FROM department
WHERE department_name = '市场部' OR department_id = '销售部')

连接查询

FROM first_table join_type second_table [ON (join_condition)]

内连接（INNER JOIN）

等值连接

join_condition只用了等号

eg.

SELECT *
FROM employee 
INNER JOIN department 
ON employee.DepartmentID = department.DepartmentID

结果：名称相同的列同时存在

不等值连接

join_condition用了除等号以外的运算符

自然连接

两表自然连接时，所有名称相同的列都被比较

eg.

SELECT *
FROM   employee NATURAL JOIN department

结果：同名的列只出现一次，且没有表前缀

外连接（OUTER JOIN）

连接表保留所有记录，即使没有匹配也要保留。外连接依据保留左表、右表还是两表都保留分为以下三类。

左外连接

保留左表所有记录，即使它们在右表中没有匹配。 左外连接会返回左表的所有记录和右表中匹配记录的组合(如果右表中无匹配记录,
来自于右表的所有列的值设为 NULL)。 如果左表的一行在右表中存在多个匹配行, 那么左表的行会复制和右表匹配行一样的数量,
并进行组合生成连接结果。

eg.

SELECT *  
FROM   employee  LEFT OUTER JOIN department  
ON employee.DepartmentID = department.DepartmentID

结果：左表的所有记录都保留了，将右表中没有匹配的部分设为NULL（图中Jasper）

右外连接

与左外连接类似，保留右表所有记录，即使它们在左表中没有匹配。

全外连接

左右外连接的并集。连接表包含被连接的表的所有记录, 如果缺少匹配的记录, 即以 NULL 填充。

eg.

SELECT *  
FROM   employee 
FULL OUTER JOIN department 
ON employee.DepartmentID = department.DepartmentID

结果：可以看到没有部门信息的员工，也能看到没有员工信息的部门

交叉连接（CROSS JOIN）

返回被连接的两个表的笛卡儿积
总共行数 = 左表符合条件的行数 x 右表符合条件的行数

eg.

SELECT a.employee_id, a.employee_name, b.goods_id, b.order_num
FROM department a CROSS JOIN employee b

参考：
1. https://www.cnblogs.com/programmer1/p/4403466.html
2.《数据库技术与应用——SQL Server 2008》（清华大学出版社）