SQL的一些面试题

SELECT查询在逻辑上的处理顺序

1.FROM
2.WHERE
3.GROUP BY
4.HAVING
5.SELECT
5.1 OVER
5.2 DISTINC
5.3 TOP
6.ORDER BY

GROUP BY 子句

如果查询涉及到分组，那么GROUP BY阶段之后的所有阶段（包括HAVING、SELECT以及ORDER BY）的操作对象将是组，而不是单独的行。每个组最终也表示为查询结果集中的一行。这意味着在GROUP BY阶段之后处理的子句中指定的所有表达式务必保证为每个组只返回一个标量（单值）。

另外，所有的聚合函数都会忽略NULL值，只有一个例外—COUNT(*)。

HAVING子句

HAVING子句是在对行进行分组后进行处理的，所以可以在逻辑表达式中引用聚合函数。HAVING子句逻辑表达式计算结果为FALSE或UNKNOWN的组将被过滤掉，所以HAVING子句只返回逻辑表达式为TRUE的组。

使用游标的步骤

1.在某个查询的基础上声明游标
2.打开游标
3.从第一个游标记录中把列值提取到指定的变量
4.当还没有超过游标的最后一行时（@@FETCH_STATUS函数的返回值为0），循环遍历游标记录；在每一次遍历时，从当前游标记录中把列值提取到指定的变量，再为当前行执行相应的操作
5.关闭游标
6.释放游标

ORDER BY子句

理解SQL最重要的一点就是要明白表不保证是有序的，因为表时是为了代表一个集合，而集合是无序的。
带有ORDER BY子句的查询会生成一种ANSI称之为游标的结果，该结果将不会符合表的要求，因为这时结果中的行将具有一定的顺序。
ORDER BY是唯一能够引用SELECT处理阶段创建的别名列的阶段，因为它是唯一一个在SELECT阶段之后被处理的阶段

当指定了DISTINCT以后，ORDER BY子句就被限制为只能选取在SELECT列表中出现的那些元素。原因是因为，当指定DISTINCT时，一个结果行可能代表多个原始行，因此，可能无法清楚地知道应该使用ORDER BY列表值中多个可能值中的哪一个。

TOP选项

当使用TOP时，同一ORDER BY子句既担当了为TOP决定行的逻辑优先顺序的角色，同时也担当了它的常规角色（展示数据），只是最终生成的结果由表变成了具有固定顺序的游标。

如果想返回TOP n行中的最后一行，需增加一个WITH TIES选项。

OVER子句

OVER子句用于为行定义一个窗口，以便进行特定的运算。可以把行的窗口简单地认为是运算将要操作地一个行的集合。聚合函数和排名函数都是可以支持OVER子句的运算类型，由于OVER子句为这些函数提供了一个行的窗口，所以这些函数也称之为开窗函数(window function)。

聚合开窗函数使用OVER子句提供窗口作为上下文，对窗口中的一组值进行操作，而不是使用GROUP BY子句提供的上下文。这样就不必对数据进行分组，还能够在同一行中同时返回基础行的列和聚合列。

带有空的圆括号的OVER子句会提供所有行进行计算。这里的“所有行”并不一定是FROM子句中出现的那些表中的所有行，而是在FROM、WHERE、GROUP BY，以及HAVING处理阶段完成后仍然可用的那些行。注意，只有SELECT和ORDER BY处理阶段才允许使用OVER子句。

OVER子句的一个优点就是能够在返回基本列的同时，在同一行对它们进行聚合。也可以在表达式中混合使用基本列和聚合值列。

OVER子句也支持四种排名函数：ROW_NUMBER（行号）、RANK（排名）、DENSE_RANK（密集排名）以及NTILE。其逻辑顺序通过OVER子句中的ORDER BY语句进行指定。要让ORDER BY子句中列出的元素能够唯一地确定各个行，可以在ORDER BY列表中增加附加属性。

即使行的排序值相同，ROW_NUMBER函数也一定为其生成唯一的行号值。如果想以同样的方式对排序值中的相同值进行更多的处理，可以考虑使用RANK或DENSE_RANK函数。它们两个的区别在于，RANK表示之前有多少行具有更低的排序值，而DENSE_RANK则表示之前有多少个更低的排序值。

NTILE函数可以把结果中的行关联到组，并为每一行分配一个所属的组的编号。NTILE函数接受一个表示组的数量的输入函数，并要在OVER子句中指定逻辑顺序。

注意，OVER子句中指定的ORDER BY逻辑与数据展示没有什么关系，并不会改变查询结果表最终的任何内容。

可以认为在同一SELECT子句中不同时指定DISTINCT和ROW_NUMBER是一条最佳实践原则，因为DISTINCT子句在这种情况下不起任何作用。因为ROW_NUMBER函数是在DISTINCT子句之前处理的，接着再处理DISTINCT子句，所以这时不会有任何重复的行要删除。

CASE表达式

CASE表达式是一个标量表达式，它基于条件逻辑来返回一个值。注意，CASE是一个表达式，而不是一条语句。也就是说，不能用它来控制活动的流程，也不能根据条件逻辑来做某些处理。相反，它只是根据条件逻辑来返回某个值。

CASE表达式有两种格式：简单表达式和搜索表达式。CASE简单格式将一个值与一组可能的取值进行比较，并返回第一个匹配的结果。如果列表中没有值等于测试值，CASE表达式就返回其ELSE子句中的值。如果CASE表达式中没有ELSE子句，默认将其视为ELSE NULL。

CASE搜索表达式可以在WHEN子句中指定谓词或逻辑表达式，而不限于只进行相等性比较。CASE搜索表达式返回结果为TRUE的第一个WHEN逻辑表达式所关联的THEN子句中指定的值。如果没有任何WHEN表达式结果为TRUE，CASE表达式就返回ELSE子句中的值。

NULL值

T-SQL使用三值谓词逻辑，结果可以是TRUE、FALSE或UNKNOWN。

查询返回表结果和返回游标有什么区别

在SQL中的某些语言元素和运算预期只对查询的表结果进行处理，而不能处理游标

什么是同时操作

从逻辑上来说，SELECT列表中各表达式的计算是没有顺序的——它们只是一组表达式。在逻辑上SELECT列表中的所有表达式都是在同一时刻进行计算的

使用OVER子句的优点

OVER子句的一个优点就是能够在返回基本列的同时，在同一行对它们进行聚合。也可以在表达式中混合使用基本列和聚合值列

聚集索引（Clustered Index）和非聚集索引（Non-Clustered Index）的区别

1.聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个
2.聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续。通俗点讲，聚集索引表示表中存储的数据按照索引的顺序存储，检索效率比非聚集索引高，但对数据更新影响较大。非聚集索引表示数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置，非聚集索引检索效率比聚集索引低，但对数据更新影响较小

聚集索引的约束是唯一性，是否要求字段也是唯一的

不需要。聚集索引可以创建在任何一列你想创建的字段上，这是从理论上讲，实际情况并不能随便指定，否则在性能上会是恶梦

是不是聚集索引就一定比非聚集索引性能优越

不是的。要看具体情况，下图总结了何时使用聚集索引或非聚集索引

在数据库里通过什么描述聚集索引与非聚集索引

索引是通过二叉树的形式进行描述的，我们可以这样区分聚集与非聚集索引的区别：聚集索引的叶节点就是最终的数据节点，而非聚集索引的叶节点仍然是索引节点，但它有一个指向最终数据的指针

在主键上创建聚集索引的表在数据插入上为什么比主键上创建非聚集索引表速度要慢

聚集索引由于索引叶节点就是数据页，所以如果想检查主键唯一性，需要遍历所有数据节点才行，但非聚集索引不同，由于非聚集索引上已经包含了主键值，所以查找主键唯一性，只需要遍历所有的索引页就行，这比遍历所有数据行减少了不少IO消耗

联接的三种基本类型

交叉联接、内联接和外联接

三种联接的区别

交叉联接只有一个步骤——笛卡尔积（Cartesian Product）；内联接有两个步骤——笛卡尔积、过滤（Filter）；外联接有三个步骤——笛卡尔积、过滤、添加外部行

交叉联接

交叉联接是最简单的联接，交叉联接只实现一个逻辑查询步骤，笛卡尔积。这一步是对输入的两表进行操作，把它们联接起来，生成二者的笛卡尔积。也就是将一个输入表的每行与另一个表的所有行进行匹配。如果一个表有m行，而另一个表有n行，将得到m*n行的结果集。

使用ANSI SQL-92的交叉联接语法，要在参与联接的两个表之间使用“CROSS JOIN”关键字。而使用ANSI SQL-89的交叉联接语法，只要简单地在表名之间加个逗号。这两种语法在逻辑上和性能上都没有区别，但是推荐使用ANSI SQL-92地语法，即使用CROSS JOIN来联接两表。

在使用自联接时，必须为表起别名，如果不这么做的话，联接结果中的列名就会有歧义

内联接

使用ANSI SQL-92语法，须在两个表名之间指定INNER JOIN关键字。INNER关键字是可选的，因为内联接是默认的联接方式，所以可以只单独指定JOIN关键字。用于对行进行过滤的谓词是在一个称为ON子句的特别设计的语句中指定的，该谓词也称为联接条件。

和交叉联接类似，内联接也可以用ANSI SQL-89语法来表达。可以像交叉联接那样在表名之间放一个逗号，在查询的WHERE子句中定义联接条件。

之所以推荐使用ANSI SQL-92的联接语法，因为在某些方面它用起来更安全。假如你想写一条内联接查询，但不小心忘记指定联接条件。如果这时用的是ANSI SQL-92语法，查询语句将是无效的，语法分析器会报错。但是使用ANSI SQL-89语法时，忘记联接条件，查询仍然是有效的

多表联接

一个联接表运算符只对两个表进行操作，而一条查询语句可以包含多个联接。通常，当FROM子句中包含多个表运算符时，表运算符在逻辑上是从左到右的顺序处理的。也就是说，第一个表运算符的结果将作为第二个表运算符的输入，第二个表运算符的结果将作为第三个表运算符左边的输入，以此类推。如果FROM子句中包含多个联接，逻辑上只有第一个联接对两个基础表进行操作，而其它联接则将前一个的结果作为其左边的输入。当处理交叉联接和内联接时，为了优化的目的，数据库引擎经常对联接顺序进行内部的调整，因为这样的优化并不会影响查询结果的正确性

外联接

与内联接和交叉联接不同，外联接是在ANSI SQL-92中才被引入的，因此它只有一种标准语法——在表名之间指定JOIN关键字，在ON子句中指定联接条件。外联接会应用内联接所应用的两个逻辑处理（笛卡尔积和ON过滤），此外还多加一个外联接特有的第三步：添加外部行。

在外联接中，要把一个表标记为“保留的”表，可以在表名之间使用关键字LEFT OUTER JOIN、RIGHT OUTER JOIN以及FULL OUTER JOIN，其中OUTER关键字是可选的。LEFT关键字表示左边表的行是保留的，RIGHT关键字表示右边表的行是保留的，而FULL关键字则表示左右两边表的行都是保留的。外联接的第三个逻辑查询处理步骤就是要识别保留表中按照ON条件在另一个表找不到与之匹配的那些行，再把这些行添加到联接的前两个步骤生成的结果表中。对于来自联接的非保留表的那些列，追加的外部行中的这些列则用NULL作为占位符。

内部行是指按照ON子句中的条件能在联接的另一边找到匹配的那些行；而外部行则是指找不到匹配的那些行。内联接只返回内部行，而外联接同时返回内部行和外部行。

使用外联接，经常会为到底是在查询的ON子句中，还是在WHERE子句指定联接条件而感到困惑。从外联接保留表中的行来考虑这个问题，ON子句中的过滤条件不是最终的。换句话说，ON子句中的条件并不能最终决定保留表中部分行是否会在结果中出现，而只是判断是否能够匹配另一边表中的某些行。所以，当需要表达一个非最终的条件时（即这个条件只决定哪些行可以匹配非保留表），就在ON子句中指定联接条件。当在生成外部行以后，要应用过滤器，而且希望过滤条件是最终的，就应该在WHERE子句中指定条件。WHERE子句是在FROM子句之后被处理的，即在处理完所有表运算符，（在外联接的情况下）生成了所有外部行以后。此外，与ON子句不同，对于行的过滤来说，WHERE子句是最终的

外联接高级主题

• 包含缺少值的数据。在查询数据时，可以用外联接来识别和包含缺少的值（即NULL值）
• 对外联接中非保留表的列进行过滤。外联接得到的外部行中来自非保留表的列值均为NULL，而NULL<运算符><值>这种格式的表达式只会得到UNKNOWN（除非使用IS NULL运算符）。WHERE子句会将UNKNOWN值过滤掉，在WHERE子句中，这样的查询条件会让所有外部行都被过滤掉，效果上相当于抵消了外联接的作用。换句话说，这好像是把联接类型在逻辑上变成了内联接。所以，在检查涉及外联接的代码，查找逻辑错误时，应该检查的一个地方就是WHERE子句。
• 在多表联接中使用外联接。FROM子句处理阶段中表运算符在逻辑上是从左到右运算的，不能随意调整它们的顺序。对于任何外联接，如果后面紧跟一个内联接或右联接，都会抵消掉外联接的外部行。当然，这一结论的前提是联接条件对来自联接左边的NULL值（即非保留表的列）和联接右边的某些值进行了比较。
• 随外联接一起使用COUNT函数。当对外联接的结果进行分组，再使用COUNT(*)聚合函数时，聚合操作会把内部行和外部行都计算在内，因为它只计算行数，不管行的内容。为了解决这个问题，应该用COUNT()来代替COUNT(*)，并从联接的非保留表中选择一个列。

独立子查询

每个子查询都有所属于的外部查询。独立子查询是独立于其外部查询的子查询。独立子查询调试起来非常方便，因为总可以把子查询代码独立出来单独运行，并确保它能够实现默认的功能。在逻辑上，独立子查询在执行外部查询之前要先执行一次，接着外部查询再使用子查询的结果继续进行查询

独立标量子查询

标量子查询是返回单个值的子查询，而不管它是不是独立子查询。标量子查询可以出现在外部查询中期望使用单个值的任何地方（WHERE、SELECT，等等）

独立多值子查询

多值子查询是在一个列中返回多个值的子查询，而不管子查询是不是独立。一些谓词（例如IN）可以处理多值子查询。IN谓词的格式可以是：
<标量表达式> IN (<多值子查询>)

相关子查询

相关子查询是指引用了外部查询中出现的表的列的子查询。这就意味着子查询要依赖于外部查询，不能独立地调用它。在逻辑上，子查询会为每个外部行单独计算一次

EXISTS谓词

EXISTS谓词的输入是一个子查询，如果子查询能够返回任何行，该谓词则返回TRUE，否则返回FALSE。

EXISTS谓词使用“短路”处理方式，无须处理所有满足条件的行，就足以决定子查询是否应该返回，可以提高效率。

EXISTS谓词在其子查询的SELECT列表中使用星号(*)在逻辑上并不是一种不好的实践方式，数据库引擎会忽略子查询的SELECT列表。

最后要注意，EXISTS谓词使用的是二值逻辑，而不是三值逻辑。因为不知道查询是否有返回行的情况是不存在的。

高级子查询

• 行为不当的子查询。当对至少返回一个NULL值的子查询使用NOT IN谓词时，外部查询总会返回一个空集。因为T-SQL使用三值逻辑，外部表中的外部值无法确切地判断该值是否包含NULL的集合所涵盖的范围，结果返回UNKNOWN（当然计算得出UNKNOWN，是因为使用了外部值＝NULL的操作导致的）。与IN不同的是，EXISTS使用的是二值谓词逻辑，总是返回TRUE或FALSE，绝不会返回UNKNOWN。使用NOT EXISTS比使用NOT IN更安全。
• 子查询列名中的替换错误。为避免这种错误，在子查询中为列名加上来源表的别名作为前缀。