2019.12.20-2019.12.21_SQL SERVER复习、进阶_现在你需要编写一个sql查询,查找每个用户的最早和最晚下单日期,并计算其之间的天-CSDN博客

本文链接：https://blog.csdn.net/qq_26815137/article/details/103633104

1.数据类型

CHAR与VARCHAR的区别：
CHAR无论保存的数据是否达到length指定的长度，均会按照这个长度来保存数据，不足的用空格补齐，查询时用trim()函数比较多。但是CHAR类型的索引效率很高。
VARCHAR适用于某个数据如果你确定它不会超过10这个长度，就可以定义为VARCHAR(10)，最终占用的长度是真实长度+1，多出来的一位是用来保存真正用到的数据长度。
综上所述，如果对查询性能要求高的时候，建议使用CHAR。如果对空间使用上要求比较小的话，用VARCHAR。实际开发中应该根据情况综合考虑。
NCHAR、NVARCHAR、NTEXT：
这几个N开头的字符串数据类型比较常用，因为他们在保存中英文字符串时都是保存为2个字节，在判断长度的时候就不用区分中英文了。不带N开头的字符串类型一般只在确保这个字段不会保存中文字符时才会使用，它们的好处是用来保存英文字符串时可以节省空间。（NCHAR、NVARCHAR、NTEXT保存中英文字符都是占2个字节的，而CHAR、VARCHAR、TEXT英文占1字节，中文占2字节）

2.单表查询

使用分组查询group by时，没有包含在聚合函数或 GROUP BY 子句中的列，无法SELECT。
使用GROUP BY子句分组时，除了COUNT(*)外，其他聚合函数都会忽略NULL。
一般DISTINCT用于COUNT()函数的去重。
TOP (1) PERCENT 表示查询1%数量的记录。WITH TIES跟在TOP (5)后面，一般与order by一起使用，此时会返回比5多的记录数，多出来的记录是order by后的字段与第五条记录相同的记录（如果存在的话就会全查出来）

-- 跳过前50条记录，取后面25条记录，翻页功能的实现方式
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate, orderid
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

SELECT orderid, custid, val,
-- 开窗函数，这里意思是根据custid分组但是不聚合，然后根据val升序排序，输出一个行号rownum
  ROW_NUMBER() OVER(PARTITION BY custid
                    ORDER BY val) AS rownum
FROM Sales.OrderValues
ORDER BY custid, val;

在这里插入图片描述

CASE 的使用：

① 简单表达式：将一个值与一组可能的取值进行比较，并返回满足第一个匹配的结果；
SELECT productid,productname,categoryid,categoryname=(
    CASE categoryid
        WHEN 1 THEN 'Beverages'
        WHEN 2 THEN 'Condiments'
        WHEN 3 THEN 'Confections'
        WHEN 4 THEN 'Dairy Products'
        ELSE 'Unkonw Category'
    END)
FROM Production.Products;
② 搜索表达式：将返回结果为TRUE的第一个WHEN逻辑表达式所关联的THEN子句中指定的值。如果没有任何WHEN表达式结果为TRUE，CASE表达式则返回ELSE子句中出现的值。（如果没有指定ELSE，则默认返回NULL）；
SELECT orderid, custid, val, valuecategory=(
  CASE 
    WHEN val < 1000.00    THEN 'Less than 1000'
    WHEN val BETWEEN 1000.00 AND 3000.00 THEN 'Between 1000 and 3000'
    WHEN val > 3000.00    THEN 'More than 3000'
    ELSE 'Unknown'
  END
)
FROM Sales.OrderValues

T-SQL中字符串前加N表示使用Unicode方式存储，无论是中文、英文字符，都占2个字节。

--定义变量为nvarchar类型，所以无论加不加n，一个字符都是占2字节
declare @status nvarchar(20)
select @status = N'stopped'
select @status = 'stopped'

SELECT LEN(N'abcde'); -- 5，字符串长度
SELECT DATALENGTH(N'abcde'); -- 10，所占字节长度

3.join的用法

cross join（交叉连接）不需要连接条件，两个表的的数据直接进行笛卡尔积运算。即如果表A有m条记录，表B有n条记录，则连接后查询出的记录有m*n条。
inner join/join（内连接）表示只查询两表之间有关联的记录，先对两表进行笛卡尔积运算，然后根据on后面的谓词进行筛选。
left outer join/left join（左连接）表示查出符合条件的左表记录，右表如果有能跟左表关联上的记录则查出，没有的话用NULL补全。
right outer join/right join（右连接）表示查出符合条件的右表记录，左表如果有能跟右表关联上的记录则查出，没有的话用NULL补全。
full outer join/fulljoin（全连接）表示查出两表的并集，不存在的记录用NULL补全，相当于左连接和右连接同时处理。
对同一个表进行联接，就是自联接。交叉联接、内联接、外联接都支持自联接。自连接必须对表起别名，否则会出现歧义。

4.UNION/UNION ALL

这两个都是合并记录的作用，其中UNION会去重，UNION ALL不会去重。
并且如果UNION/UNION ALL连接的select语句查出的字段数量不一致则会报错。查出的列名以第一条select指定的列名为准。

5.子查询

查看多表的数据也可使用表连接，表连接（join on…）,表连接都可用子查询替换，但有的子查询不能用表连接替换，子查询比较灵活，方便，形式多样，适合于作为查询的筛选条件。
自包含多值子查询：

SELECT n
FROM dbo.Nums
WHERE n BETWEEN (SELECT MIN(O.orderid) FROM dbo.Orders AS O)
            AND (SELECT MAX(O.orderid) FROM dbo.Orders AS O)
  AND n NOT IN (SELECT O.orderid FROM dbo.Orders AS O);

相关子查询：
不能单独运行而是依赖外部的子查询。在逻辑上，子查询会为每个外部行单独计算一次。
例子1：查询每个客户返回在他参与活动的最后一天下过的所有订单。
1.首先用独立标量子查询查询出最大的订单日期，返回给外部查询
SELECT MAX(orderdate)
FROM sales.Orders AS O2
2.外部查询用O1.orderdate进行过滤，过滤出等于最大订单日期的订单
3.因为要查询出每个客户参与的订单，所以将独立标量子查询改成相关子查询，用子查询O2.custid与外查询O1.custid关联。
对于O1中每一行，子查询负责返回当前客户的最大订单日期。如果O1中某行的订单日期和子查询返回的订单日期匹配，那么O1中的这个订单日期就是当前客户的最大的订单日期，在这种情况下，查询便会返回O1表中的这个行。
SELECT MAX(orderdate)
FROM sales.Orders AS O2
WHERE O2.custid = O1.custid
综合上面的步骤，得到下面的查询语句：
SELECT orderid,orderdate,custid
FROM sales.Orders AS O1
WHERE O1.orderdate = ( SELECT MAX(orderdate)
FROM sales.Orders AS O2
WHERE O2.custid = O1.custid

例子2：为每个客户返回最大订单ID的订单。
第一步：
SELECT MAX(O2.orderid)
FROM Sales.Orders AS O2
第二步：
SELECT MAX(O2.orderid)
FROM Sales.Orders AS O2
WHERE O2.custid = O1.custid
第三步：
SELECT custid, orderid, orderdate, empid
FROM Sales.Orders AS O1
WHERE orderid =
(SELECT MAX(O2.orderid)
FROM Sales.Orders AS O2
WHERE O2.custid = O1.custid);

6.EXISTS

EXISTS用于在子查询是判断后面跟的子查询结果是否存在，存在返回true，否则返回false。NOT EXISTS则相反。
EXISTS作为WHERE条件时，先执行WHERE前的SELECT语句，然后把结果代入到子查询中去，如果返回true，则显示这一条记录，否则忽略。

7.派生表

派生表可以简化查询，避免使用临时表。相比手动生成临时表性能更优越。派生表与其他表一样出现在查询的FROM子句中。

select * from (select * from athors)  temp

其中，查询出来的派生表必须指定表别名，以及列名也必须全部有且唯一。
分配列别名（建议使用内嵌别名形式）

-- 这里相当于把YEAR(orderdate)，custid，一起在AS后面分配，就不用写2个AS
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM (SELECT YEAR(orderdate), custid
      FROM Sales.Orders) AS D(orderyear, custid)
GROUP BY orderyear;
-- 使用参数
DECLARE @empid AS INT = 3;
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM (SELECT YEAR(orderdate), custid
      FROM Sales.Orders
      WHERE empid = @empid) AS D(orderyear,custid)
GROUP BY orderyear;

8.公用表表达式(CTE)

CTE其实相当于一个先定义好别名的派生表。当外部查询结束，公用表表达式的生命周期就结束了。其实CTE的作用就相当于子查询。

WITH C AS
(
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;
-- 内嵌分配列别名
WITH C(orderyear, custid) AS
(
  SELECT YEAR(orderdate), custid
  FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;
-- 定义多个CTE，前面定义的一个CTE只能在后面的第一条语句中使用
WITH C1 AS
(
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
),
C2 AS
(
  SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
  FROM C1
  GROUP BY orderyear
)
SELECT orderyear, numcusts
FROM C2
WHERE numcusts > 70;

9.视图

视图与内联表、CTE的区别：
内联表、CTE不是永久的，在外查询结束后它们就不存在了，无法重用。视图和内联表值函数是可重用的，只有使用语句删除后才会消失。
视图与内联表、CTE的联系：
在很多方面，视图和内联表值函数的处理方式都类似于派生表和CTE。当查询视图和内联表值函数时，SQL Server会先扩展表表达式的定义，再直接查询底层对象。
视图的创建：
GO
CREATE VIEW [视图名称]
AS
[SELECT语句]
GO

-- 如果已存在则删除
IF OBJECT_ID('Sales.USACusts') IS NOT NULL
   DROP VIEW Sales.USACusts;
GO
CREATE VIEW Sales.USACusts
AS
SELECT 
    custid, companyname, contactname, contacttitle, address,
    city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country=N'USA';
GO

--使用
SELECT * FROM Sales.Customers;

10.函数（也称为自定义函数、内联表值函数TVF）

一种可重用的表表达式，能够支持输入参数。除了支持输入参数以外，内联表值函数在其他方面都与视图相似。
函数创建：
GO
CREATE FUNCTION [函数名]
([定义参数如：@cid AS INT]) RETURNS TABLE
AS
RETURN
[SELECT语句]
GO

IF OBJECT_ID('dbo.fn_GetCustOrders') IS NOT NULL
   DROP FUNCTION dbo.fn_GetCustOrders;
GO
CREATE FUNCTION dbo.fn_GetCustOrders
    (@cid AS INT) RETURNS TABLE
AS
RETURN 
    SELECT
        orderid, custid, empid, orderdate, requireddate,
        shippeddate, shipperid, freight, shipname, shipaddress, shipcity,
        shipregion, shippostalcode, shipcountry
    FROM Sales.Orders
    WHERE custid=@cid;
GO