SQL SERVER 从入门到精通 第5版 第二篇 第8章 SQL数据高级查询 读书笔记

 第8章 SQL数据高级查询

 　　>.子查询与嵌套查询

　　　　>.子查询概述:

　　　　子查询是一个嵌套在SELECT,INSERT,UPDATE和DELETE语句或者其他子查询中的查询,任何允许使用表达式的地方都可以使用子查询.子查询语法规则如下:

　　　　　　>.子查询的SELECT查询总使用圆括号括起来.

　　　　　　>.不能包括COMPUTE或者FOR BROWSE子句

　　　　　　>.如果同时指定TOP子句,则可能只包括ORDER BY子句.

　　　　　　>.子查询最多可以嵌套32层,个别查询可能不支持32层嵌套.

　　　　　　>.任何可以使用表达式的地方都可以使用子查询,只要它返回的是单个值.

　　　　　　>.如果某个表,只出现在子查询中而不出现在外部查询中,那么该表中的列就无法包含在输出中.

-- 示例展示了子查询的各种使用情况和相关规则

-- 1. 使用圆括号括起来的子查询
SELECT column1
FROM table1
WHERE column1 IN (SELECT column2 FROM table2);

-- 2. 不包括 COMPUTE 或者 FOR BROWSE 子句的使用
-- 下面的示例是不合法的
-- SELECT column1
-- FROM table1
-- COMPUTE SUM(column1);

-- 3. 子查询中可能只包括 ORDER BY 子句
SELECT column1
FROM table1
WHERE column1 IN (SELECT TOP 10 column2 FROM table2 ORDER BY column2 DESC);

-- 4. 最多可以嵌套 32 层的复杂嵌套示例
SELECT column1
FROM table1
WHERE column1 IN (SELECT column2 FROM table2 WHERE column2 IN (SELECT column3 FROM table3 WHERE column3 IN (SELECT column4 FROM table4 WHERE column4 IN (...))));

-- 5. 任何可以使用表达式的地方都可以使用子查询
SELECT column1
FROM table1
WHERE column1 = (SELECT MAX(column2) FROM table2);

-- 6. 表 table2 中的列无法直接包含在输出中，因为它只出现在子查询中
SELECT column1
FROM table1
WHERE column1 IN (SELECT column2 FROM table2);

 　　　　>.嵌套查询概述:

　　　　　　>.嵌套查询是指将一个查询块,嵌套在另一个查询块的WHERE子句或者HAVING条件中的查询.

　　　　　　>.嵌套查询中,外层的查询块称为外侧查询或者父查询,内层查询块称为内层查询或者子查询.

　　　　　　>.SQL允许多层嵌套,但是在子查询中不能出现ORDER BY子句.ORDER BY子句只能用在最外层的查询块当中.

　　　　　　>.嵌套查询的处理顺序是: 先处理最内层的子查询,然后一层一层向上处理,直到最外层的查询块.　　

-- 外层查询查找符合条件的订单
SELECT order_id, customer_id
FROM orders
WHERE customer_id IN (
    -- 内层查询查找 VIP 客户的 ID
    SELECT customer_id
    FROM customers
    WHERE status = 'VIP'
);

-- 嵌套查询可以进一步嵌套，这里是一个更复杂的例子：
SELECT product_name
FROM products
WHERE category_id IN (
    -- 内层查询查找销售额高于平均值的产品类别 ID
    SELECT category_id
    FROM sales
    GROUP BY category_id
    HAVING SUM(sales_amount) > (
        -- 再嵌套一层查询，求取销售额的平均值
        SELECT AVG(sales_amount)
        FROM sales
    )
);

　　　　>.SOME,ANY,ALL定量比较谓词.常用在条件的比较中.

-- SOME、ANY 和 ALL 通常用于子查询中的比较操作符。

-- 1. SOME 和 ANY 是等价的，它们表示与子查询中的任何一个值进行比较。
-- 2. ALL 表示与子查询中的所有值进行比较。

-- 示例 SQL 语句如下：

-- 使用 SOME/ANY，找出所有大于某个值的学生成绩
SELECT *
FROM students
WHERE score > SOME (SELECT score FROM exam_scores);

-- 使用 ALL，找出所有学生成绩均大于某个值的记录
SELECT *
FROM students
WHERE score > ALL (SELECT score FROM exam_scores);

-- 更复杂的示例，找出某个学生的成绩是否高于所有其他学生的成绩
SELECT *
FROM students s1
WHERE score > ALL (SELECT score FROM students WHERE student_id <> s1.student_id);

　　　　>.带EXISTS的嵌套查询

　　　　EXISTS谓词只注重子查询是否返回行.如果有返回行,则EXITST则返回True,否则返回False.EXITST的返回值决定了父查询是否执行查询.

　　　　能用IN的嵌套查询,都可以改写成用带EXISTS的嵌套查询.

SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1    --因为EXITST只关心子查询是否有行返回,所以这里无所谓列名
    FROM orders o
    WHERE o.customer_id = c.customer_id
);    

　　　　>.连接查询

　　　　连接查询是由一个笛卡尔乘积运算再加一个选取运算构成的查询.首先用笛卡尔乘积完成对两个数据集合的乘运算,然后对生成的结果集合进行选取运算,确保只把分别来自两个数据集合并且具有重叠部分的行合并在一起.连接的全部意义在于水平方向上合并两个数据集合,并产生一个新的结果集合.　　

--笛卡尔乘积是指在关系型数据库中，当执行交叉连接（CROSS JOIN）时产生的结果。它是两个表的所有可能组合的集合，即每个表的每一行都与另一个表的每一行组合，形成一个新的结果集。这种组合是通过将第一个表中的每一行与第二个表中的所有行进行配对而实现的。

--例如，如果表A有m行，表B有n行，那么它们的笛卡尔乘积将产生m x n行的结果。在实际应用中，笛卡尔乘积通常需要谨慎使用，因为它会生成非常大的结果集，可能会导致性能问题和意外的结果。

　　　　连接条件可在FROM或WHERE子句中指定,建议在FROM子句中指定连接条件.WHERE和HAVING子句还可以包含搜索条件,以进一步筛选根据连接条件选择的行.

　　　　连接可分为3类:内连接,外连接与交叉连接.

　　　　　　>.内连接

　　　　　　　　内连接是最早的连接,最初被叫为普通连接或者自然连接.它是从结果中删除其他被连接表中,没有匹配到的所有行,所以内连接可能会丢失信息.

　　　　　　　　简单来说,就是取交集,返回两表中同时存在的记录集

　　　　　　>.外连接

　　　　　　　　与内连接相比,外连接不会丢失数据.外连接还分为左连接/右连接的全外连接,其中左连接和右连接的区别,在于左连接能保证左表记录的完整性,右连接能保证右表记录的完整性.表现为:如果左表(右表)的某一行在右表(左表)中没有匹配到数据,则会填充空值

　　　　　　　　全外连接会返回左表与右表的所有行.

　　　　　　>.交叉连接

　　　　　　　　>.使用 CROSS JOIN 进行连接

　　　　　　　　>.交叉连接中的列=原表中列的数量的总和(相加)

　　　　　　　　>.交叉连接中的行=原表中的行数的积(相乘)

-- 创建示例表
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT
);

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

-- 插入示例数据
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID)
VALUES (1, 'John', 'Doe', 1),
       (2, 'Jane', 'Smith', 2),
       (3, 'Alice', 'Johnson', NULL);

INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES (1, 'HR'),
       (2, 'IT'),
       (3, 'Finance');

-- 内连接 (INNER JOIN)：返回两个表中符合条件的行
SELECT e.EmployeeID, e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- 左外连接 (LEFT OUTER JOIN)：返回左表中的所有行，以及符合条件的右表中的行
SELECT e.EmployeeID, e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
LEFT OUTER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- 右外连接 (RIGHT OUTER JOIN)：返回右表中的所有行，以及符合条件的左表中的行
SELECT e.EmployeeID, e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
RIGHT OUTER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- 全外连接 (FULL OUTER JOIN)：返回两个表中的所有行，无论是否符合条件
SELECT e.EmployeeID, e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
FULL OUTER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- 交叉连接 (CROSS JOIN)：返回两个表的笛卡尔积，即每个行组合都会出现
SELECT e.EmployeeID, e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
CROSS JOIN Departments d;

　　>.连接多表

　　　　>.在WHERE子句中连接多表

　　　　在FROM子句中写入需要连接的多个表的名称,然后将任意两个表的连接条件分别写在WHERE子句后

--在WHERE子句中手动指定连接条件。
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders, Customers
WHERE Orders.CustomerID = Customers.CustomerID;

　　　　>.在FROM子句中连接多表

　　　　在FROM子句中连接多个表是内连接的扩展.当在FROM子句中连接多表时,要书写多个用来定义其中两个表的公共部分的ON语句,ON语句必须遵循FROM后面所列表的顺序,即FROM后面先写的表,相应的ON语句就要先写.

--连接两个表：
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
--连接三个表：
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID;
-- 在FROM子句中连接多表（使用ON语句）：
--在这种方法中，我们在FROM子句中使用多个ON语句来连接多个表
SELECT Orders.OrderID, Customers.CustomerName, Employees.EmployeeName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID;

　　　　>.使用CASE函数进行查询

-- 示例1：简单CASE表达式
SELECT 
    CustomerName,
    -- 根据顾客所在国家选择不同的国家名称
    CASE Country
        WHEN 'USA' THEN 'United States'
        WHEN 'UK' THEN 'United Kingdom'
        ELSE 'Other'
    END AS CountryName
FROM Customers;

-- 示例2：搜索CASE表达式
SELECT 
    OrderID,
    -- 根据订单数量选择不同的数量级别
    CASE 
        WHEN Quantity > 100 THEN 'High'
        WHEN Quantity > 50 THEN 'Medium'
        ELSE 'Low'
    END AS QuantityLevel
FROM OrderDetails;

-- 示例3：CASE表达式用于UPDATE语句
UPDATE Products
SET StockStatus = CASE
                    WHEN UnitsInStock > 0 THEN 'In Stock'
                    ELSE 'Out of Stock'
                 END;

-- 示例4：CASE表达式用于ORDER BY子句
SELECT ProductName, UnitsInStock
FROM Products
ORDER BY 
    -- 根据库存量进行排序，库存大于50的排在前面
    CASE 
        WHEN UnitsInStock > 50 THEN UnitsInStock
        ELSE 0
    END DESC;