SQLSERVER的知识点

1.列和表别名

SQL 别名用于为 表 或 表中的列 提供临时名称。 SQL 别名通常用于使 表名 或 列名 更具可读性。 SQL 一个别名只存在于查询期间。

别名使用 AS 关键字&＃xff0c;通常可以省略。

建议在下列情况下使用别名 查询涉及多个表 用于查询函数 需要把两个或更多的列放在一起 列名长或可读性差

SELECT 列名
FROM 表名 AS 别名;

2. 连接表

在关系数据库中&＃xff0c;数据分布在多个逻辑表中。

要获得完整有意义的数据集&＃xff0c;需要使用连接来查询这些表 中的数据。

SQL Server支持多种连接&＃xff0c;包括 [内连接]&＃xff0c;[左连接]&＃xff0c;[右连接]&＃xff0c;[全外连接]和[交叉连接]。 每种连接类型指定SQL Server如何使用一个表中的数据来选择另一个表中的行。

&＃xff08;1).SQL Server内连接

内连接是SQL Server中最常用的连接之一。 内部联接子句用于查询来自两个或多个相关表的数据。 SQL Server INNER JOIN 子句的语法&＃xff1a;

SELECT
product_name,
category_name,
brand_name,
list_price
FROM
production.products p
INNER JOIN production.categories c ON c.category_id &＃61; p.category_id
INNER JOIN production.brands b ON b.brand_id &＃61; p.brand_id
ORDER BY
product_name DESC;


(2).SQL Server左连接

LEFT JOIN 子句用于查询来自多个表的数据。

它返回左表中的所有行和右表中的匹配行。 如果在右表中找不到匹配的行&＃xff0c;则使用 NULL 代替显示。 以下图说明了两个结果集的左连接结果&＃xff1a;

SELECT
product_name,
order_id
FROM
production.products p
LEFT JOIN sales.order_items o ON o.product_id &＃61; p.product_id
ORDER BY
order_id

(2).ON与WHERE子句 

SELECT
product_name,
order_id
FROM
production.products p
LEFT JOIN sales.order_items o ON o.product_id &＃61; p.product_id
WHERE order_id &＃61; 100
ORDER BY
order_id;

(3). SQL Server右连接

RIGHT JOIN 子句组合来自两个或多个表的数据。

RIGHT JOIN 开始从右表中选择数据并与左表中的行 匹配。

RIGHT JOIN 返回一个结果集&＃xff0c;该结果集包含右表中的所有行&＃xff0c;无论是否具有左表中的匹配行。

如果右表中的行没有来自右表的任何匹配行&＃xff0c;则结果集中右表的列将使用 NULL 值。 以下是 RIGHT JOIN 的语法&＃xff1a;

SELECT
product_name,
order_id
FROM
sales.order_items o
RIGHT JOIN production.products p
ON o.product_id &＃61; p.product_id
ORDER BY
order_id;


 &＃xff08;4).SQL Server 交叉连接

CROSS JOIN 连接两个或多个不相关的表。 以下是两个表的SQL Server CROSS JOIN 的语法&＃xff1a;

SELECT
select_list
FROM
T1
CROSS JOIN T2;
或者
SELECT
select_list
FROM
T1, T2;

CROSS JOIN 将第一个表(T1)中的每一行与第二个表(T2)中的每一行连接起来。

换句话说&＃xff0c;交叉连接返回 两个表中行的笛卡尔积。

与INNER JOIN或LEFT JOIN不同&＃xff0c;交叉连接不会在连接的表之间建立关系。

假设 T1 表包含三行&＃xff1a; 1 , 2 和 3 &＃xff0c; T2 表包含三行&＃xff1a; A &＃xff0c; B 和 C 。

CROSS JOIN 从第一个表(T1)获取一行&＃xff0c;然后为第二个表(T2)中的每一行创建一个新行。

然后它对第一个 表(T1)中的下一行执行相同操作&＃xff0c;依此类推。 在此图中&＃xff0c; CROSS JOIN 总共创建了 9 行。 通常&＃xff0c;如果第一个表有 n 行&＃xff0c;第二个表有 m 行&＃xff0c;则交叉连接 将产生 n x m 行。 

(5).SQL Server自连接

自联接用于将表连接到自身(同一个表)。

它对于查询分层数据或比较同一个表中的行很有用。 自联接使用内连接或左连接子句。 由于使用自联接的查询引用同一个表&＃xff0c;因此表别名用于为查询中的表 分配不同的名称。

SELECT
e.first_name &＃43; &＃39; &＃39; &＃43; e.last_name employee,
m.first_name &＃43; &＃39; &＃39; &＃43; m.last_name manager
FROM
sales.staffs e
INNER JOIN sales.staffs m ON m.staff_id &＃61; e.manager_id
ORDER BY
manager

(6).SQL Server全外链接

 FULL OUTER JOIN当左表或右表中存在匹配项时&＃xff0c;该命令将返回所有行。 下面创建一些示例表来演示全外连接。 首先&＃xff0c;创建一个名为 pm 的新模式&＃xff0c;它代表项目管理

INSERT INTO
pm.projects(title)
VALUES
(&＃39;New CRM for Project Sales&＃39;),
(&＃39;ERP Implementation&＃39;),
(&＃39;Develop Mobile Sales Platform&＃39;);
INSERT INTO
pm.members(name, project_id)
VALUES
(&＃39;John Doe&＃39;, 1),
(&＃39;Lily Bush&＃39;, 1),
(&＃39;Jane Doe&＃39;, 2),
(&＃39;Jack Daniel&＃39;, null);

3.分组数据

(1).SQL Server Group By语句

 Group By 从字面意义上理解就是根据“By”指定的规则对数据进行分组&＃xff0c;

所谓的分组就是将一个“数据集” 划分成若干个“小区域”&＃xff0c;

然后针 对若干个“小区域”进行数据处理。 以下是 GROUP BY 子句的语法&＃xff1a;

SELECT
customer_id,
YEAR (order_date) order_year
FROM
sales.orders
WHERE
customer_id IN (1, 2)
ORDER BY
customer_id;

(2).GROUP BY子句和聚合函数

 GROUP BY 子句通常与聚合函数一起用于统计数据。

聚合函数对组执行计算并返回每个组的唯一值。

例如&＃xff0c; COUNT() 函数返回每个组中的行数。

其他常用的聚合函数是&＃xff1a; SUM() &＃xff0c; AVG() &＃xff0c; MIN() &＃xff0c; MAX()。

GROUP BY 子句将行排列成组&＃xff0c;聚合函数返回每个组的统计量(总数量&＃xff0c;最小值&＃xff0c;最大值&＃xff0c;平均值&＃xff0c;总和 等)。 例如&＃xff0c;以下查询返回客户按年度下达的订单数&＃xff1a;

SELECT
customer_id,
YEAR (order_date) order_year,
COUNT (order_id) 订单数量
FROM
sales.orders
WHERE
customer_id IN (1, 2)
GROUP BY
customer_id,
YEAR (order_date)
ORDER BY
customer_id;


(1).带有COUNT()函数示例的GROUP BY子句

SELECT
city,
COUNT (customer_id) customer_count
FROM
sales.customers
GROUP BY
city
ORDER BY
city;

(2).GROUP BY子句带有MIN和MAX函数示例

SELECT
brand_name,
MIN (list_price) min_price,
MAX (list_price) max_price
FROM
production.products p
INNER JOIN production.brands b ON b.brand_id &＃61; p.brand_id
WHERE
model_year &＃61; 2018
GROUP BY
brand_name
ORDER BY
brand_name;


4.SQL Server Having子句

HAVING 子句通常与[GROUP BY]子句一起使用&＃xff0c;以根据指定的条件列表过滤分组。 以下是 HAVING 子句 的语法&＃xff1a;

SELECT
select_list
FROM
table_name
GROUP BY
group_list
HAVING
conditions;


在此语法中&＃xff0c;

GROUP BY 子句将行汇总为分组&＃xff0c;

HAVING 子句将一个或多个条件应用于这些每个分组。

只有使条件评估为 TRUE 的组才会包含在结果中。

换句话说&＃xff0c;过滤掉条件评估为 FALSE 或 UNKNOWN 的 组。

因为SQL Server在 GROUP BY 子句之后处理 HAVING 子句&＃xff0c;所以不能通过使用列别名来引用选择列表中指 定的聚合函数。 以下查询将失败&＃xff1a; 

SELECT
column_name1,
column_name2,
aggregate_function (column_name3) column_alias
FROM
table_name
GROUP BY
column_name1,
column_name2
HAVING
column_alias > value

5.子查询

(1).SQL Server子查询

子查询是嵌套在另一个语句(如&＃xff1a;[SELECT]&＃xff0c;[INSERT]

&＃xff0c;[UPDATE]或[DELETE])中的查询。 以下语句显示如何在 SELECT 语句的[WHERE]子句中使用子查询来查找位于纽约( New York )的客户的销售订单:

SELECT
order_id,
order_date,
customer_id
FROM
sales.orders
WHERE
customer_id IN (
SELECT
customer_id
FROM
sales.customers
WHERE
city &＃61; &＃39;New York&＃39;
)
ORDER BY
order_date DESC;

(2).SQL Server嵌套子查询

子查询可以嵌套在另一个子查询中。

SQL Server最多支持 32 个嵌套级别。 请考虑以下示例&＃xff1a; 

SELECT
product_name,
list_price
FROM
production.products
WHERE
list_price > (
SELECT
AVG (list_price)
FROM
production.products
WHERE
brand_id IN (
SELECT
brand_id
FROM
production.brands
WHERE
brand_name &＃61; &＃39;上海永久&＃39;
OR brand_name &＃61; &＃39;凤凰&＃39;
)
)
ORDER BY
list_price;

 (3).SQL Server相关子查询

相关子查询是使用外部查询的值的[子查询]。

换句话说&＃xff0c;它取决于外部查询的值。

由于这种依赖性&＃xff0c;相 关子查询不能作为简单子查询独立执行。

此外&＃xff0c;对外部查询评估的每一行重复执行一次相关子查询。 相关子查询也称为重复子查询。 以下示例查找价格等于其类别的最高价格的产品。

SELECT
product_name,
list_price,
category_id
FROM
production.products p1
WHERE
list_price IN (
SELECT
MAX (p2.list_price)
FROM
production.products p2
WHERE
p2.category_id &＃61; p1.category_id
GROUP BY
p2.category_id
)
ORDER BY
category_id,
product_name;
-- 子查询写法
SELECT
p1.product_id,p1.product_name,p1.category_id,p1.list_price
FROM
production.products p1
INNER JOIN (
SELECT
category_id,
max(list_price) max_price
FROM
production.products
GROUP BY
category_id
) p2 ON p1.category_id &＃61; p2.category_id
AND p1.list_price &＃61; p2.max_price;

(4).SQL Server Exists运算符

EXISTS 运算符是一个逻辑运算符&＃xff0c;用于检查子查询是否返回任何行。 如果子查询返回一行或多行&＃xff0c;则 EXISTS 运算符返回 TRUE 。 以下是SQL Server EXISTS 运算符的语法&＃xff1a;

EXISTS ( subquery)


(1).1. 带子查询的EXISTS返回NULL示例 以下示例返回 customers 表中的所有行&＃xff1a;

SELECT
customer_id,
first_name,
last_name
FROM
sales.customers
WHERE
EXISTS (SELECT NULL)
ORDER BY
first_name,
last_name;

(5).SQL Server Any运算符

ANY 运算符是一个逻辑运算符&＃xff0c;它将标量值与子查询返回的单列值集进行比较。 以下是 ANY 运算符的语法&＃xff1a;

scalar_expression comparison_operator ANY (subquery)

 在上面语法中&＃xff0c;

scalar_expression - 是任何有效的表达式。

comparison_operator - 是任何比较运算符,包括等于( &＃61; )&＃xff0c;不等于( <> )&＃xff0c;大于( > )&＃xff0c;大于或等于 ( >&＃61; )&＃xff0c;小于( <)&＃xff0c;小于或等于( <&＃61; )。

subquery 是一个[SELECT]语句&＃xff0c;它返回单个列的结果集&＃xff0c;其数据与标量表达式的数据类型相同。

SELECT
product_name,
list_price
FROM
production.products
WHERE
product_id &＃61; ANY (
SELECT
product_id
FROM
sales.order_items
WHERE
quantity >&＃61; 2
)
ORDER BY
product_name;


(7).SQL Server All运算符

SQL Server ALL 运算符是一个逻辑运算符&＃xff0c;

它将标量值与子查询返回的单列值列表进行比较。 以下是 ALL 运算符语法&＃xff1a; 

scalar_expression comparison_operator ALL ( subquery)

在上面语法中&＃xff0c; scalar_expression 是任何有效的表达式。

comparison_operator 是任何有效的比较运算符&＃xff0c;包括等于( &＃61; )&＃xff0c;不等于( <> )&＃xff0c;大于( > )&＃xff0c;大于或 等于( >&＃61; )&＃xff0c;小于( <)&＃xff0c;小于或等于( ALL ( subquery ) 如果 scalar_expression 大于子查询返回的最大值&＃xff0c;则表达式返回 TRUE 。

例如&＃xff0c;以下查询查找价格大于所有品牌产品的平均价格的产品&＃xff1a;

SELECT
product_name,
list_price
FROM
production.products
WHERE
list_price > ALL (
SELECT
AVG (list_price) avg_list_price
FROM
production.products
GROUP BY
brand_id
)
ORDER BY
list_price;

(2).scalar_expression

如果标量表达式( scalar_expression )小于子查询( subquery )返回的最小值&＃xff0c;则表达式求值为 TRUE 。 以下示例按品牌查找价格低于平均价格中最低价格的产品&＃xff1a;

SELECT
product_name,
list_price
FROM
production.products
WHERE
list_price SELECT
AVG (list_price) avg_list_price
FROM
production.products
GROUP BY
brand_id
)
ORDER BY
list_price DESC;