学习笔记--【MS SQL 学习：3】

SQL 关联与函数

JION：

先列出可以使用的不同的 SQL JOIN 类型：

INNER JOIN：如果表中有至少一个匹配，则返回行

LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行

RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行

FULL JOIN：只要其中一个表中存在匹配，则返回行

SELF JOIN：用于将表连接到自己，就好像该表是两个表一样，临时重命
名了SQL语句中的至少一个表

CARTESIAN JOIN：从两个或多个连接表返回记录集的笛卡儿积

1.SQL INNER JOIN 语法(内部连接)

SELECT column_name(s)
    FROM table1
    INNER JOIN table2 ON table1.column_name = table2.column_name;

2.SQL LEFT JOIN 语法(左连接)

SELECT column_name(s)                
    FROM table1                
    LEFT JOIN table2                
    ON table1.column_name=table2.column_name;

3.SQL RIGHT JOIN 语法(右连接)

SELECT column_name(s)
    FROM table1
    RIGHT JOIN table2 ON table1.column_name = table2.column_name;

4.SQL FULL OUTER JOIN 语法(外连接)

SELECT column_name(s)
    FROM table1
    FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

5.Self JOIN语法(自连接)

SELECT column_name(s)
    FROM table1 T1, table1 T2
    WHERE condition;

函数：

时间函数

ETDATE()	返回当前的日期和时间
DATEPART()	返回日期/时间的单独部分
DATEADD()	在日期中添加或减去指定的时间间隔
DATEDIFF()	返回两个日期之间的时间
CONVERT()	用不同的格式显示日期/时间

SQL Server 使用下列数据类型在数据库中存储日期或时间值：

DATE - 格式：YYYY-MM-DD
DATETIME - 格式：YYYY-MM-DD HH:MM:SS
SMALLDATETIME - 格式：YYYY-MM-DD HH:MM:SS
TIMESTAMP - 格式：唯一的数字

窗口函数 、分析排名函数

OVER()函数
CUME_DIST 计算的逻辑是：小于等于当前值的行数/分组内总行数

PERCENT_RANK 计算的逻辑是：（分组内当前行的RANK值-1）/ （分组内总行数-1），排名值是RANK()函数排序的结果值。
PERCENTILE_CONT和PERCENTILE_DISC都是为了计算百分位的数值，比如计算在某个百分位时某个栏位的数值是多少。

在一次查询中，对数据表进行排序，把已排序的数据从上向下看作是一个序列，对当前行而言，在序列上方的为后，在序列下方的为前。

在同一分组内，对于当前行，Lag()函数用于获取从当前行开始向后（或向上）计数的第N行，Lead()函数用于获取从当前行开始向前（或向下）计数的第N行。

RANK、NTILE、DENSE_RANK和ROW_NUMBER，和OVER()函数搭配使用，按照特定的顺序排名。

ROW_NUMBER函数实际上是一个序列，每个分组内都会创建一个序列，序列从1开始，按照顺序依次 +1 递增。

ROW_NUMBER ( ) 
    OVER ( [ PARTITION_BY_clause ] order_by_clause )
分组内序列的最大值就是该分组内的行的数目。

RANK函数用于排名时，不会返回连续的整数。RANK函数的语法是：在分组内，按照特定的顺序排名，序号从1依次递增，排名函数以tie为单位，每个tie中的所有行的排名是相同的，排名可能是不连续的。

RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

排名的算法是：

step1：按照指定的分区字段分组，在每个分组内按照指定的字段排序。
step2：在每个分组内，如果相邻的两行或多行相同在排序字段上的值相同，那么这些行称作一个tie，每个tie中的所有行都会获得相同的排名。
step3：后面的排名会计算每个tie中的行数，RANK函数不总是返回连续的整数，例如，班级中，A，B分数都是100分，C的分数是90分，那么A和B的排名是1，C的排名是3。

DENSE_RANK函数用于排名时，会返回连续的整数。每个tie占用一个排名，每个tie中的所有行的排名是相同的。排名值是连续的

DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )
排名的算法是：

step1：按照指定的分区字段分组，在每个分组内按照指定的字段排序。
step2：在每个分组内，如果相邻的两行或多行相同在排序字段上的值相同，那么这些行称作一个tie，每个tie中的所有行都会获得相同的排名。
step3：后面的排名会计算每个tie中的行数，RANK函数总是返回连续的整数，例如，班级中，A，B分数都是100分，C的分数是90分，那么A和B的排名是1，C的排名是2。

在每个分组中，NTILE按照指定的顺序，把数据行分为N个小组（tile），NTILE返回小组编号。在每个分组内，具有相同的小组编号的数据行，位于同一个小组。注意：小组的编号是按照行数，而不是按照列值。在同一分组内，存在两行的列值相同，而小组编号不同。

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )
如果分区中的行数不能被integer_expression整除，那么会导致小组相差一个成员：较大的小组按OVER子句指定的顺序位于较小的小组之前。 例如，如果把8行分为3个小组，前2个小组有3行，后一个小组有2行。

如果分区中的中行数能被integer_expression整除，那么每个小组具有相同的行数。

特别地，NTILE(4) 把一个分组分成4份，叫做Quartile。

数据类型转换

CASE

CASE WHEN 条件 
     THEN 结果
     ELSE 结果
END

MERGE

MERGE INPUT_table a USING source_table c
ON a.column=c.column
WHEN MATCHED
    THEN UPDATE SET 
        t.category_name = s.category_name,
        t.amount = s.amount
WHEN NOT MATCHED
    THEN INSERT (category_id, category_name, amount)
         VALUES (s.category_id, s.category_name, s.amount)
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

日期函数

DATENAME
以字符串形式返回日期部分

DATEPART
以整数形式返回日期部分

DAY
以整数形式返回指定日期

MONTH
以整数形式返回指定日期的月份

YEAR
以整数形式返回日期的年份

DATEDIFF
返回两个日期之间的日期部分的差值。

DATEDIFF_BIG
返回两个日期之间的日期部分的差值

DATEADD
将值添加到日期部分并返回新的日期值。

EOMONTH
返回包含指定日期的月份的最后一天，并带有可选的偏移量。

SWITCHOFFSET
更改DATETIMEOFFSET值的时区偏移量并保留UTC值。

TODATETIMEOFFSET
将datetime2值转换为datetimeoffset值。

分组

SELECT
    d1,
    d2,
    d3,
    aggregate_function (c4)
FROM
    table_name
GROUP BY
    GROUPING SETS (
        (d1,d2),
        (d1),
        (d2),
        ()
     );

与上文相同

GROUP BY
    CUBE (d1, d2);

其他

ISNULL: 当第一个字段为null时获取后面的内容

GETDATE():返回当前系统的日期和时间,

Truncate table table_name; 清空表内容

select distinct  去重

WITH AS短语，也叫做子查询部分（subquery factoring），可以定义一个SQL片断，该SQL片断会被整个SQL语句用到。 可以使SQL语句的可读性更高，也可以在UNION ALL的不同部分，作为提供数据的部分。 对于UNION ALL，使用WITH AS定义了一个UNION ALL语句，当该片断被调用2次以上，优化器会自动将该WITH AS短语所获取的数据放入一个Temp表中。

COUNT():计数,
MAX():最大,
MIN():最小,
AVG():均值, 
SUM():求和,
LAST():最后一个值,
FIRST():第一个值,
LOWER(s):转为大写,
LCASE(s):转为小写,
UCASE():转为大写,
LCASE()转为小写,
MID():用于从文本字段中提取字符,
LEN():返回文本字段中值的长度,
ROUND():用于把数值字段舍入为指定的小数位数, 
FORMAT():用于对字段的显示进行格式化, 
SQRT:用于计算得出任何数值的平方根,
RAND:用于产生 0 至 1 之间的随机数,
CONCAT:用于将两个字符串连接为一个字符串,
SQL REPLACE():字符串替换函数,
SQL TRIM():去除字符串头尾空格

CUME_DIST
计算一组值中的值的累积分布

DENSE_RANK
为结果的分区内的每一行分配一个排名值，排名值没有间隙。

FIRST_VALUE
获取结果集的有序分区中第一行的值。

LAG
提供对当前行之前的给定物理偏移的行的访问。

LAST_VALUE
获取结果集的有序分区中最后一行的值。

LEAD
提供对当前行之后的给定物理偏移量的行的访问。

NTILE
将有序分区的行分配到多个组或存储桶中。

PERCENT_RANK
计算一组值中值的百分比排名。

RANK
为结果集的分区中的每一行分配一个排名值。

ROW_NUMBER
为结果集的分区内的行分配唯一的顺序整数，第一行从1开始。

TOP

SELECT - 从数据库中提取数据
SELECT TOP 子句用于指定要返回的记录数量。
SELECT TOP子句在包含数千条记录的大型表上很有用。返回大量记录会影响性能。

注：并不是所有的数据库系统都支持SELECT

TOP子句。MySQL支持LIMIT子句来选择有限数量的记录，而Oracle使用ROWNUM。

以下SQL语句从"Customers"表中选择前三个记录：
SELECT TOP 3 * FROM Customers;

注：　LIMIT作为一种简单的分页方法，主要是为了减少数据返回的时间，如果您查询一个非常大的表(例如一个有数十万或数百万行的表)而不使用限制，那么您可能会等待很长时间才能显示所有的结果，所以使用LIMIT可以减少查询数据返回的时间，提高效率。

运算符：加减乘除求余、大于小于等于、and、or、all 、any 、between 、exists 、in、 like 、not 、is null、unique

IS NULL
IS NULL运算符用于将值与NULL值进行比较。

EXISTS
EXISTS运算符用于搜索指定表中是否存在满足特定条件的行。
集合
UNION - 将两个或多个查询的结果集合并到一个结果集中。INTERSECT - 返回两个或多个查询的结果集的交集。
EXCEPT - 查找两个输入查询的两个结果集之间的差集。

IF EXISTS(SELECT * FROM sysobjects WHERE name='PK_stuNo' and xtype='PK')
  Alter TABLE stuInfo
    Drop Constraint PK_stuNo
Go

运算符	描述
ALL   	ALL运算符用于将值与另一个值集中的所有值进行比较。

AND 	AND运算符允许在SQL语句的WHERE子句中指定多个条件。

ANY 	ANY运算符用于根据条件将值与列表中的任何适用值进行比较。

BETWEEN ### BETWEEN运算符用于搜索在给定最小值和最大值内的值。

SQL BETWEEN 运算符

BETWEEN运算符用于选取介于两个值之间的数据范围内的值。
BETWEEN运算符选择给定范围内的值。值可以是数字，文本或日期。
BETWEEN运算符是包含性的：包括开始和结束值，且开始值需小于结束值。     

IN ### IN运算符用于将值与已指定的文字值列表进行比较。

SQL IN 运算符

IN 运算符允许您在 WHERE 子句中指定多个值。
IN 运算符是多个 OR 条件的简写。

SQL IN 语法
SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);
或者
SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT STATEMENT);

LIKE ### LIKE运算符用于使用通配符运算符将值与类似值进行比较。

有两个通配符与LIKE运算符一起使用：
％ - 百分号表示零个，一个或多个字符
_ - 下划线表示单个字符


LIKE              运算符       	    描述

WHERE CustomerName LIKE 'a%'    	查找以“a”开头的任何值
WHERE CustomerName LIKE '%a'	    查找以“a”结尾的任何值
WHERE CustomerName LIKE '%or%'  	在任何位置查找任何具有“or”的值
WHERE CustomerName LIKE '_r%'   	在第二个位置查找任何具有“r”的值
WHERE CustomerName LIKE 'a_%_%'	  查找以“a”开头且长度至少为3个字符的值
WHERE ContactName  LIKE 'a%o'	    找到以"a"开头，以"o"结尾的值

注意： MS Access使用问号（?）而不是下划线（_）。百分号和下划线也可以组合使用！

NOT	    NOT运算符反转使用它的逻辑运算符的含义。 例如：NOT EXISTS, NOT BETWEEN, NOT IN等等，这是一个否定运算符。

OR	    OR运算符用于组合SQL语句的WHERE子句中的多个条件。

UNIQUE ### UNIQUE运算符搜索指定表的每一行的唯一性(无重复项)。

UNION运算符用于组合两个或更多SELECT语句的结果集，而不返回任何重复的行。
UNION中的每个SELECT语句必须具有相同的列数
这些列也必须具有相似的数据类型
每个SELECT语句中的列也必须以相同的顺序排列
每个SELECT语句必须有相同数目的列表达式
但是每个SELECT语句的长度不必相同

SQL UNION 语法：
SELECT column_name(s) FROM table1 [WHERE condition]
UNION
SELECT column_name(s) FROM table2 [WHERE condition];

SQL UNION ALL 语法：
SELECT column_name(s) FROM table1 [WHERE condition] 
UNION ALL
SELECT column_name(s) FROM table2 [WHERE condition];

注：where后可跟运算符表达式不能跟函数，hving后可以跟任何条件

通配符：通配符用于替换字符串中的任何其他字符。

通配符与 SQL LIKE 运算符一起使用。
在 WHERE子句中使用LIKE运算符来搜索列中的指定模式。 
有两个通配符与 LIKE 运算符一起使用：
％ - 百分号表示零个，一个或多个字符
_ - 下划线表示单个字符

注意：

MS Access 使用星号(*）通配符而不是百分比符号(%)通配符。 
MS Access 使用问号（?）而不是下划线（_）。
在MS Access和SQL Server中，你也可以使用：
[ charlist ] - 定义要匹配的字符的集合和范围
[^ charlist ]或[！charlist ] - 定义不匹配字符的集合和范围
通配符也可以组合使用！

表达式：有不同类型的sql表达式，如下所示：布尔型、数值型、日期

1.
SELECT column1, column2, columnN FROM table_name WHERE [CONDITION|EXPRESSION];

1.SQL布尔表达式基于匹配单个值获取数据。
句法：
SELECT column1, column2, columnN FROM table_name WHERE SINGLE VALUE MATCHING EXPRESSION;
例如：
SELECT * FROM CUSTOMERS WHERE SALARY = 10000;



2.数值表达式用于在任何查询中执行任何数学运算。
句法：
SELECT numerical_expression as  OPERATION_NAME [FROM table_name WHERE CONDITION] ;
例如：SELECT (15 + 6) AS ADDITION
SELECT COUNT(*) AS "RECORDS" FROM CUSTOMERS; 


3.日期表达式。日期表达式返回当前系统日期和时间值：
SELECT CURRENT_TIMESTAMP;
：+---------------------+
  | Current_Timestamp   |
  +---------------------+
  | 2009-11-12 06:40:23 |
  +---------------------+
  1 row in set (0.00 sec)
  
SELECT  GETDATE();