SQL SERVER 从入门到精通 第5版 第二篇 第7章 SQL数据查询基础 读书笔记

 第7章 SQL数据查询基础

 　　　SELECT 检索数据

　　　　>.SELECT语句是SQL语句中功能最强大,也是最复杂的语句,所以我决定略过.

　　　　>.WITH子句指定临时命名的结果集(非临时表),这些结果集称为公用表表达式

WITH temp_name AS (
    -- 在这里定义临时结果集的查询
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
-- 在这里引用临时结果集并进行其他操作
SELECT *
FROM temp_name;

　上面这种写法与下面这种写法等效

WITH temp_name (column1, column2) AS (
    -- 在这里定义临时结果集的查询
    SELECT value1 AS column1, value2 AS column2
    FROM table_name
    WHERE condition
)
-- 在这里引用临时结果集并进行其他操作
SELECT *
FROM temp_name;

 

　　　　>.SELECT...FROM子句

　　　　　　在这个子句里,你可以使用各种聚合函数,以及给字段取个别名.别名的定义方法有3种:

　　　　　　　　>. 别名=列名

　　　　　　　　>.列名 as 别名 (推荐)

　　　　　　　　>.列名  别名

　　　　>.INTO 子句　　　　

--INTO子句用于将查询结果插入到指定的表中。
SELECT column1, column2
INTO new_table
FROM existing_table
WHERE condition;

 

　　　　>.WHERE 子句,指定查询返回行的搜索条件

EXISTS(包括 NOT EXISTS )子句的返回值是一个B00L值。EXISTS内部有一个子查询语句(SELECT.FROM..)，我将其称为EXIST的内查询语句。其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。
种通俗的可以理解为:将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这行行可作为外查询的结果行，否则不能作为结果。
分析器会先看语句的第一个词，当它发现第一个词是SELECT关键字的时候，它会跳到FROM关键字，然后通过FROM关键字找到表名并把表装入内存，接着是找WHERE关键字，如果找不到则返回到SELECT找字段解析，如果找到WHERE，则分析其中的条件，完成后再回到SELECT分析字段。最后形成一张我们要的虚表。
WHERE关键字后面的是条件表达式。条件表达式计算完成后，会有一个返回值，即非0或0，非0即为真(true)，0即为假(false)，同理WHERE后面的条件也有一个返回值，真或假，来确定接下来执不执行SELECT。分析器先找到关键字SELECT，然后跳到FROM关键字将STUDENT表导入内存，并通过指针找到第一条记录，接着找到WHERE关键字计算它的条件表达式，如果为真那么把这条记录装到一个虚表当中，指针再指向下一条记录。如果为假那么指针直接指向下一条记录，而不进行其它操作，一直检索完整个表，并把检索出来的虚拟表返回给用户，EXISTS是条件表达式的一部分，它也有一个返回值(tue或false).EXISTS子句的结果决定了主查询语句是否被执行.

　　　　　　WHERE子句非常复杂,它里面可以使用

　　　　　　　　>.逻辑运算符(优先级依次由高到低为: NOT ,AND,OR)
　　　　　　　　>.比较运算符(>,<,<>,=,>=,<=)
　　　　　　　　>.LIKE,IN,BETWEEN,EXISTS,IS (NOT) NULL,ALL / SOME / ANY 等关键字,其中Like关键字会涉及到通配符的使用,请各位额外查询其用法

-- LIKE：用于模糊匹配，通常与通配符结合使用
SELECT column1 
FROM table_name
WHERE column2 LIKE 'A%';

-- IN：用于匹配多个值中的任何一个
SELECT column1 
FROM table_name
WHERE column2 IN ('value1', 'value2', 'value3');

-- BETWEEN：用于选择在指定范围内的值
SELECT column1 
FROM table_name
WHERE column2 BETWEEN 10 AND 20;

-- EXISTS：用于检查子查询是否返回任何行
SELECT column1 
FROM table_name
WHERE EXISTS (SELECT * FROM another_table WHERE another_table.column3 = table_name.column2);

-- IS (NOT) NULL：用于检查值是否为NULL
SELECT column1 
FROM table_name
WHERE column2 IS NOT NULL;

-- ALL / SOME / ANY：用于与子查询一起使用，比较主查询和子查询的结果
SELECT column1 
FROM table_name
WHERE column2 > ALL (SELECT column3 FROM another_table);

  

　　　　>.GROUP BY 子句

　　　　GROUP BY表示按一个或者多个列或者表达式的值,将一组选定行,组合成一个摘要行的集合.

　　　　注意SELECT子句必须包含在聚合函数或者GROUP BY子句中:

　　　　　　>.包含在聚合函数中： 如果 SELECT 子句中的列没有直接出现在 GROUP BY 子句中，那么它必须作为聚合函数的参数之一出现。这意味着对于每个分组，聚合函数将计算该列的聚合结果，例如 COUNT、SUM、AVG 等。

　　　　　　>.包含在 GROUP BY 子句中： 如果 SELECT 子句中的列不在聚合函数中，那么它必须在 GROUP BY 子句中显式地列出，以表示它是用于分组的列之一。这意味着查询结果将按照这些列的值进行分组，并且对于每个唯一组合，都会返回一个结果。

SELECT     -- 使用YEAR函数将OrderDate按年分组，并计算每年的总销售额
    YEAR(OrderDate) AS OrderYear, 
    SUM(TotalAmount) AS TotalSales
FROM 
    Orders
GROUP BY 
    YEAR(OrderDate) -- 按年分组
UNION ALL
SELECT     -- 按Region和ProductCategory分组，并计算每组的总销售额
    Region, 
    ProductCategory, 
    SUM(SalesAmount)
FROM 
    Sales
GROUP BY 
    Region, ProductCategory -- 按Region和ProductCategory分组

SELECT     -- 使用GROUPING SETS按不同的组合进行分组，并计算每组的总销售额
    Region, 
    NULL, -- NULL表示不分组
    SUM(SalesAmount)
FROM 
    Sales
GROUP BY 
    GROUPING SETS ((Region), ()) -- 按Region分组，以及不分组

SELECT     -- 使用ROLLUP生成分层总计，并计算每层的总销售额.
    Region, 
    ProductCategory, 
    SUM(SalesAmount)
FROM 
    Sales
GROUP BY 
    ROLLUP(Region, ProductCategory) -- 生成Region和ProductCategory的分层总计

SELECT     -- 使用CUBE生成所有可能的组合，并计算每组合的总销售额.

  Region, ProductCategory, SUM(SalesAmount) FROM Sales GROUP BY CUBE(Region, ProductCategory) -- 生成所有可能的Region和ProductCategory的组合

　　　　

　　　　>.HAVING 子句

　　　　HAVING子句指定组或者聚合的搜索条件.它只能与SELECT语句一起使用,通常会在GROUP BY子句中使用.如果不使用GROUP BY子句,则HAVING的作用与WHERE一样

　　　　>.ORDER BY 子句

　　　　　　提供了三种排序方法

　　　　　　>.ASC : 升序,默认值,缺省值

　　　　　　>.DESC : 降序

　　　　　　>.COLLATE collate_name:根据 collate_name中指定的排序规则,而不是表或视图中定义的列的排序规则,应执行的ORDER BY操作.

这对于需要特定排序规则的场景非常有用，例如，当你需要按照不同语言或区域的排序规则进行排序时。

假设我们有一个包含中文字符的表，而默认的排序规则可能不适用于中文字符的排序。这时，我们可以使用COLLATE关键字指定一个适合中文字符的排序规则，以确保正确的排序顺序。

下面是一个使用COLLATE关键字进行排序的示例：

-- 假设有一个包含中文字符的表
CREATE TABLE ChineseNames (
    ID INT,
    Name NVARCHAR(100)
);

-- 向表中插入一些中文姓名数据
INSERT INTO ChineseNames (ID, Name) VALUES
(1, N'张三'),
(2, N'李四'),
(3, N'王五'),
(4, N'赵六');

-- 查询中文姓名，并按照拼音排序（拼音排序规则不同于默认排序规则）
SELECT Name
FROM ChineseNames
ORDER BY Name COLLATE Chinese_Pinyin_BIN; -- 使用COLLATE指定拼音排序规则

这里顺便说一下 N''  的用法.大写字母作前缀的 N'xxx'是将其内容xxx作为 Unicode字符常量(双字节)。而没有N的''yyy' ,是将'yyy'作为字符常量(单字节)。

一个很神奇的用法,源自@Ever Dream

-- 声明两个变量用于存储动态SQL语句和参数定义
declare @SQLString nvarchar(1000)
declare @ParmDefinition Nvarchar(1000)

-- 设置动态SQL语句，查询表aaa中满足条件name=@name和value=@value的数据
set @SQLString=N'select * from aaa where name=@name and value=@value' ;

-- 设置参数定义，定义两个参数name和value，它们的数据类型为varchar(50)
SET @ParmDefinition = N' @name varchar(50),@value varchar(50)';

-- 执行动态SQL语句，将参数传递给查询语句，@name='name1', @value='value1'
EXECUTE sp_executesql @SQLString, @ParmDefinition,@name='name1',@value='value1' ;

还有:

-- 检查是否存在名为#t11的临时表，如果存在则删除
IF OBJECT_ID(N'tempdb..#t11', N'U') IS NOT NULL    
    DROP TABLE #t11

-- 将aaa表的数据复制到临时表#t11中
SELECT * INTO #t11 FROM aaa 

-- 声明一个表变量@t，包含SID、name和value三个字段
DECLARE @t TABLE (SID VARCHAR(40), name VARCHAR(100), value VARCHAR(100))

-- 将aaa表的数据插入到表变量@t中
INSERT INTO @t 
SELECT * FROM aaa

-- 查询临时表#t11中的数据
SELECT * FROM #t11

-- 查询表变量@t中的数据
SELECT * FROM @t

 

　　　　>. COMPUTE子句　　新版本中已被删除,请不要使用

　　　　>.DISTINCT关键字 去除重复数据

-- 1. 基本用法：从表中选择唯一不重复的值
SELECT DISTINCT column_name
FROM table_name;

-- 2. 多列去重：选择多个列的组合，确保组合是唯一的
SELECT DISTINCT column1, column2
FROM table_name;

-- 3. 带有聚合函数的去重：将聚合函数应用于去重后的结果集
SELECT COUNT(DISTINCT column_name)
FROM table_name;

-- 4. 嵌套查询中的去重：在子查询中使用DISTINCT以确保外部查询不会返回重复行
SELECT column1, column2
FROM (
    SELECT DISTINCT column1, column2
    FROM table_name
) AS subquery;

-- 5. 联合查询中的去重：合并多个查询结果并去重
SELECT column1
FROM table1
UNION
SELECT column1
FROM table2;

 

　　　　>.TOP关键字　　　

-- 返回前 10 条记录
SELECT TOP 10 column1, column2
FROM table_name;

-- 返回前 5 条记录并按特定列排序
SELECT TOP 5 column1, column2
FROM table_name
ORDER BY column1;

-- 返回前 20% 的记录, 带percent关键字
SELECT TOP 20 PERCENT column1, column2
FROM table_name;

-- 使用 WITH TIES 返回与 TOP N 相同数量的记录，如果存在并列第 N 位的记录
SELECT TOP 5 WITH TIES column1, column2
FROM table_name
ORDER BY column1;

-- 结合子查询使用 TOP
SELECT *
FROM (
    SELECT TOP 5 column1, column2
    FROM table_name
    ORDER BY column1 DESC
) AS subquery
ORDER BY column1;

　　>.UNION 合并多个查询结果

　　使用须知:

　　　　>.两个select语句选择列表中的列数必须一致,而且对应位置上的列的数据类型必须相同或者兼容.

　　　　>.列名或者别名,是由第一个SELECT语句的选择列表来决定的.

　　　　>.可以为每个SELECT语句都增加一个表示行的数据来源表达式.

　　　　>.可以将合并操作作为SELECT INTO 命令的一部分使用,但是INTO 关键字必须放在第一个SELECT语句中.

　　　　>.SELECT命令在默认情况下不会去年重复行,除非明确地为它指定 DISTENCT 关键字.而 UNION 的合并操作却相反,它会主动的去执行去重操作.除非你使用 UNION ALL关键字

　　　　>.结合并出来的数据集使用ORDER BY子句,必须放到最后一个SELECT后面,并且所使用的列名必须是第一个SELECT选择列表中的列名

　　使用ALL关键字会减少计算量,提升运行速度,尤其是处理大型表时.要使用UNION ALL,请务必明确:

　　　　>.知道有重复行并想保留这些行.

　　　　>.知道不可能有任何重复的行.

　　　　>.不在乎是否有任何重复的行.

-- 简单的 UNION 示例：合并两个查询的结果集并去除重复行
SELECT column1, column2
FROM table1
UNION
SELECT column1, column2
FROM table2;

-- 使用 UNION ALL 合并结果集，不去重
SELECT column1, column2
FROM table1
UNION ALL
SELECT column1, column2
FROM table2;

-- 结合其他条件使用 UNION：在每个查询中添加筛选条件
SELECT column1, column2
FROM table1
WHERE condition
UNION
SELECT column1, column2
FROM table2
WHERE condition;

-- UNION 结果集与排序：合并结果集并按特定列排序
SELECT column1, column2
FROM table1
UNION
SELECT column1, column2
FROM table2
ORDER BY column1;

-- 结合聚合函数使用 UNION：在合并结果集前对各自表进行聚合操作
SELECT SUM(column1) AS total
FROM table1
UNION
SELECT AVG(column1)
FROM table2;