美文网首页
SQL查询基础-源动力

SQL查询基础-源动力

作者: 苦难_69e0 | 来源:发表于2021-07-19 14:27 被阅读0次

    遇事不会查文档：

    https://docs.microsoft.com/zh-cn/sql/sql-server/?view=sql-server-ver15

    单表查询

    Select语句的元素

    USE AdventureWorks2014;
GO
SELECT SH.SalesPersonID,
       YEAR(SH.OrderDate) AS OrderYear,
       COUNT(*) NumOrders
FROM Sales.SalesOrderHeader SH
WHERE SH.CustomerID = 29825
GROUP BY SH.SalesPersonID,
         YEAR(SH.OrderDate)
HAVING COUNT(*) > 1
ORDER BY SH.SalesPersonID,
         OrderYear;
    执行顺序.png

    谓词和运算符

    TSQL可以使用逻辑表达式

    • Where子句
    • Having子句
    • Check约束

    逻辑表达式可以使用的谓词(取值为True/False/Unknown)

    • in
    • between
    • Like
    • 运算符

    比较运算符
    =, >,<,>=, <=, <> (!=, !>, !<为非标准写法)

    逻辑运算符
    or, and, not

    算术运算符
    +,-,*,/,%(取模)

    运算符优先级

    • ()
    • *,/,%
    • +,-,
    • 比较运算符
    • Not
    • And
    • Between,in,like,or
    • =(赋值)

    CASE表达式

    标量表达式, 基于条件逻辑返回一个值

    • 简单表达式
    USE Test;
GO
SELECT *,
       CASE gender
           WHEN 'male' THEN
               N'男生'
           WHEN 'female' THEN
               N'女生'
       END genderInChinese
FROM dbo.student;
    • 搜索表达式
    USE Test;
GO
SELECT *,
       CASE
           WHEN DATEDIFF(YEAR, birthday, GETDATE()) <= 10 THEN
               N'小于10岁'
           ELSE
               N'超过10岁'
       END ageDesc
FROM dbo.student;

    NULL值

    NULL是Unkown

    NULL与任何值进行运算结果是NULL

    NULL取反仍是NULL

    NULL的判断

    • IS NULL
    • IS NOT NULL

    SQL Server对于唯一约束中, 多个NULL视作一个NULL

    处理字符数据

    1.两种数据类型

    • 普通字符(char,varchar), 一个字节存储
    • Unicode字符(nchar, nvarchar), 两个字节存储
      2.排序规则collation
    • 排序规则,大小写敏感,区分重音
    • Sys.fn_helpcollations()
      3.运算符和函数
    • 连接符+
    • Left/right/len/charindex/replace/upper/lower/ltrim/rtrim/substring/replicate
      4.Like谓词
    • 通配符_, %
    • escape

    处理日期和时间数据

    1.日期类型

    • DATETIME/SMALLDATETIME/DATE/TIME/DATETIME2/DATETIMEOFFSET

    2.字符串到日期的转换

    隐式转换依赖于

    • Set language
    • Set DateFormat

    显式转换

    • Cast(‘20210613’ as datetime)
    • Convert(datetime, ‘20210613’,112)

    3.常用日期函数

    • Year/month/day/getdate()/dateadd/datediff/datepart/datename

    联接查询

    • where条件丢到辅表上，外连接自动变成内连接，
    • where条件丢到主表上，先过滤在连接；
    • 把where条件放到on里面，不过滤表，只是一个连接条件

    交叉联接

    • 对输入的两个表进行操作,生成二者的笛卡尔积
    • 两种语法
    image.png 
    USE Test;
GO
SELECT *
FROM dbo.student
CROSS JOIN dbo.class;


    USE Test;
GO
SELECT *
FROM dbo.student,dbo.class;

    内连接

    • 先像交叉联接一样生成笛卡尔积,然后根据谓词进行过滤
    • 两种语法
    USE Test;
GO
SELECT *
FROM dbo.student
INNER JOIN dbo.class
ON class.id=student.classid;


    USE Test;
GO
SELECT *
FROM dbo.student,dbo.class
Where class.id=student.classid;

    特殊的联接实例

    • 组合联接:关联条件不止一个列
     USE Test;
GO
SELECT *
FROM dbo.T1
INNER JOIN dbo.T2
ON T1.col1=T2.col1 and T1.col2=T2.col2;
    • 不等联接:关联条件包含非等值条件
    USE Test;
GO
SELECT *
FROM dbo.T1
INNER JOIN dbo.T2
ON T1.col1<T2.col1;
    • 多表联接
    USE Test;
GO
SELECT *
FROM dbo.T1
INNER JOIN dbo.T2
ON T1.col1=T2.col1
INNER JOIN dbo.T3
ON T1.col1=T3.col1;

    外联接

    1.笛卡尔积+ON过滤+外部行

    2.3种类型

    • 左联接(left [outer] join)
      以左表为主表
      二表匹配的记录+不匹配的补充为NULL
    USE Test;
GO
SELECT *
FROM dbo.class
Left JOIN dbo.student
ON class.id=student.classid;
    • 右联接(right [outer] join)
      以右表为主表
      二表匹配的记录+不匹配的补充为NULL
    USE Test;
GO
SELECT *
FROM dbo.class
right JOIN dbo.student
ON class.id=student.classid;
    • 全联接(full [outer] join)
      二表匹配的记录+左表不匹配的补充为NULL+右表不匹配的补充为NULL
    USE Test;
GO
SELECT *
FROM dbo.class
full JOIN dbo.student
ON class.id=student.classid;

    子查询

    独立子查询

    • 独立于其外部查询的子查询
    • 独立子查询在执行外部查询之前只要先执行一次,接着外部查询再使用子查询的结果继续进行查询
    USE AdventureWorks2014;
GO
SELECT s.SalesOrderNumber,
       s.OrderDate,
       s.CustomerID,
       s.SalesPersonID
FROM sales.SalesOrderHeader s
WHERE s.SalesOrderID =
(
    SELECT MAX(SalesOrderID) 
    FROM sales.SalesOrderHeader
);

    相关子查询

    • 引用了外部查询中出现的表的列的子查询
    • Exists谓词
    • 高级子查询
      返回前/后一个记录
      连续聚合
    • 行为不当的子查询
      NOT IN与NULL值问题
    USE AdventureWorks2014;
GO
SELECT SH.CustomerID,
       SH.SalesOrderID,
       SH.SalesPersonID
FROM sales.SalesOrderHeader SH
WHERE SH.SalesOrderID =
(
    SELECT MAX(SH2.SalesOrderID)
    FROM sales.SalesOrderHeader SH2
    WHERE SH.CustomerID = SH2.CustomerID
);

    表表达式

    派生表

    1.在外部查询的FROM子句中定义
    2.存在范围为定义它的外部查询,只要外部查询一结束,派生表就不存在了
    3.派生表可以嵌套
    4.查询语句满足的条件

    • 没有order by子句(除非带TOP)
    • 所有列必须有名称
    • 所有列名必须唯一
    USE AdventureWorks2014;
GO
SELECT *
FROM
(
    SELECT s.SalesOrderID,
           s.CustomerID,
           s.SalesPersonID
    FROM sales.SalesOrderHeader s
    WHERE s.TerritoryID = 5
) AS T;

    CTE

    CTE:公用表表达式

    用途

    • 命名子查询,可以反复引用
    • 实现递归查询

    注意

    • 多个cte之前用逗号分隔
    • 实现递归时需要显式给列名
    • 多个cte之前前向引用
    • 递归时防止死循环option(maxrecursion n)
    USE AdventureWorks2014;
GO
WITH source
AS (SELECT s.SalesOrderID,
           s.CustomerID,
           s.SalesPersonID
    FROM sales.SalesOrderHeader s
    WHERE s.TerritoryID = 5)
SELECT *
FROM source;


    USE AdventureWorks2014;
GO
WITH subq (n, factorial)
AS (SELECT 1,
           1
    UNION ALL
    SELECT n + 1,
           factorial * (n + 1)
    FROM subq
    WHERE n < 5)
SELECT *
FROM subq;

    内联表值函数

    • 一种可重用的表表达式
    • 支持输入参数
    USE AdventureWorks2014;
GO
CREATE FUNCTION dbo.fn_getCustOrder
(
    @cid INT
)
RETURNS TABLE
AS
RETURN SELECT SH.SalesOrderID,
              SH.CustomerID,
              SH.SalesPersonID
       FROM sales.SalesOrderHeader SH
       WHERE SH.CustomerID = @cid;
GO
Select * from db.fn_getCustOrder(1);

    APPLY运算符

    两种形式

    • Cross apply:只返回匹配的记录
    • Outer apply:返回匹配的记录, 不匹配的为NULL
    USE AdventureWorks2014;
GO
SELECT SH.CustomerID, A.SalesOrderID, A.OrderDate
FROM sales.Customer SH
CROSS/OUTER APPLY(
SELECT TOP 3 SH2.SalesOrderID, SH2.OrderDate
FROM sales.SalesOrderHeader SH2
WHERE SH.CustomerID=SH2.CustomerID
ORDER BY SH2.OrderDate DESC, SH2.SalesOrderID DESC
) A;

    集合运算

    并集UNION

    并集.png
    • 返回包含两个集合中所有元素的集合
    • 注意UNION与UNION ALL的区别
      UNION会去掉重复
      UNION ALL 不会去掉重复
    USE AdventureWorks2014;
GO
SELECT 1 id,
       'a' name
UNION
SELECT 1,
       ‘a';

    USE AdventureWorks2014;
GO
SELECT 1 id,
       'a' name
UNION ALL
SELECT 2,
       'b';

    交集InsertSect

    返回两个集合中共同的部分

    交集.png 
    USE AdventureWorks2014;
GO
SELECT d.ProductID
FROM sales.SalesOrderHeader h
    INNER JOIN sales.SalesOrderDetail d
        ON h.SalesOrderID = d.SalesOrderID
WHERE h.CustomerID = 29825
INTERSECT
SELECT d.ProductID
FROM sales.SalesOrderHeader h
    INNER JOIN sales.SalesOrderDetail d
        ON h.SalesOrderID = d.SalesOrderID
WHERE h.CustomerID = 29672;

    差集except

    返回两个集合不同的部分

    差集.png 
    USE AdventureWorks2014;
GO
SELECT *
FROM (
SELECT 1 id, 'a' name
UNION ALL
SELECT 2 id, 'b' name
) A
EXCEPT
SELECT *
FROM
(
SELECT 1 id, 'a' name
UNION ALL
SELECT 3 id, 'c' name
) B

    集合运算的优先级

    • InterSect运算比Union和Except运算优先级高
    • Union和Except优先级相等
    With A as (
SELECT 1 id, 'a' name
UNION ALL
SELECT 2 id, 'b' name
),B as (
SELECT 1 id, 'a' name
UNION ALL
SELECT 3 id, 'c' name
),C as (
SELECT 1 id, 'a' name
UNION ALL
SELECT 2 id, 'b' name
UNION ALL
SELECT 3 id, 'c' name
)
select *
from A
except
select *
from B
intersect 
select *
from C

    思维导图

    思维导图.png

    实战

    从AdventureWorks2014数据库，写一个单表聚合查询，表示某一年的所有地区的，产品大类的销量统计

    USE AdventureWorks2014
GO
SELECT st.Name AS customTerritory,pc.Name AS productcategory,SUM(sd.OrderQty) AS totalqty
FROM Sales.SalesOrderHeader SH
INNER JOIN Sales.SalesOrderDetail SD
ON SD.SalesOrderID = SH.SalesOrderID
INNER JOIN Sales.Customer SC
ON SC.CustomerID = SH.CustomerID
INNER JOIN Sales.SalesTerritory ST
ON ST.TerritoryID = SC.TerritoryID
INNER JOIN Production.Product PP
ON PP.ProductID = SD.ProductID
INNER JOIN Production.ProductSubcategory PS
ON PS.ProductSubcategoryID = PP.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = PS.ProductCategoryID
WHERE SH.OrderDate BETWEEN CAST('20110101' AS DATETIME) AND CAST('20111231' AS DATETIME)
GROUP BY st.Name,pc.Name
ORDER BY totalqty desc

    查看Color有多少null

    SELECT 
SUM(CASE WHEN pp.Color IS NULL THEN 1 ELSE 0 END) AS totalnull,
SUM(CASE WHEN pp.Color IS NOT NULL THEN 1 ELSE 0 END) AS NOTNULL
FROM Production.Product pp

    展开各种颜色都有多少

    SELECT pp.Color,SUM(CASE WHEN pp.Color IS NULL THEN 1 ELSE 0 END) AS total
FROM Production.Product pp
GROUP BY pp.Color

    打印九九乘法表

    WITH nums
AS (SELECT number
    FROM master..spt_values
    WHERE type = 'P'
          AND number
          BETWEEN 1 AND 9)
SELECT a.number,
       (
           SELECT CAST(b.number AS VARCHAR) + '*'+CAST(a.number AS VARCHAR)+ '=' + CAST(a.number*b.number AS VARCHAR) + ' '
           FROM nums b
           WHERE b.number <= a.number
           ORDER BY b.number
           FOR XML PATH('')
       )
FROM nums a;

    ABCD*E=FGHI 解题 0<A～I<=9

    WITH nums
AS (SELECT number
    FROM master..spt_values
    WHERE type = 'P'
          AND number
          BETWEEN 1 AND 9)
SELECT a.number,
       (
        select cast(a.number*1000+b.number*100+c.number*10+d.number as varchar) + '*' + cast(e.number as varchar) +'='+ cast(f.number*1000+g.number*100+h.number*10+i.number as varchar)
        from nums b,nums c,nums d,nums e,nums f,nums g,nums h,nums i 
        where a.number not in (b.number,c.number,d.number,e.number,f.number,g.number,h.number,i.number) and
        b.number not in (c.number,d.number,e.number,f.number,g.number,h.number,i.number) and
        c.number not in (d.number,e.number,f.number,g.number,h.number,i.number) and
        d.number not in (e.number,f.number,g.number,h.number,i.number) and
        e.number not in (f.number,g.number,h.number,i.number) and
        f.number not in (g.number,h.number,i.number) and
        g.number not in (h.number,i.number) and
        h.number not in (i.number) and
        (a.number*1000+b.number*100+c.number*10+d.number)*e.number = (f.number*1000+g.number*100+h.number*10+i.number)
     ORDER BY e.number
     FOR XML PATH('')
        )
FROM nums a;

    拆分字符串

    DECLARE @LongStr VARCHAR(MAX) = '';
WITH nums
AS (SELECT number
    FROM master..spt_values
    WHERE type = 'P'
          AND number
          BETWEEN 1 AND 9)
SELECT @LongStr = @LongStr + CAST(number AS VARCHAR) + ','
FROM nums;

PRINT @LongStr;

--SELECT CHARINDEX(',',@LongStr);
--GO
CREATE TABLE #T( ID INT IDENTITY(1,1),ELE VARCHAR(100));
DECLARE @LongStr VARCHAR(MAX) = 'asfgjhajkshfgkljas';
DECLARE @x VARCHAR(MAX);
DECLARE @i INT=1
WHILE @i<= LEN(@LongStr)
BEGIN
    SELECT @x = SUBSTRING(@LongStr,@i,CHARINDEX(SUBSTRING(@LongStr,@i,1),@LongStr)-1)
    INSERT INTO #T
    (
        ELE
    )
    VALUES
    (@x)
    --SET @LongStr=
    SET @i=@i+1
END
SELECT * FROM #T
DROP TABLE #T;

    身份证号统计90后

    with source as (
select 1 id, 'a' name,  '42092219900101000' code union all
select 2 id, 'b' name,  '42092219910101000' code union all
select 3 id, 'c' name,  '42092219920101000' code union all
select 4 id, 'd' name,  '42092219930101000' code union all
select 5 id, 'e' name,  '42092219940101000' code union all
select 6 id, 'f' name,  '42092220000101000' code union all
select 7 id, 'g' name,  '42092220100101000' code union all
select 8 id, 'h' name,  '42092220200101000' code union all
select 9 id, 'i' name,  '42092220300101000' code union all
select 10 id, 'j' name, '42092219950101000' code union all
select 11 id, 'k' name, '42092219960101000' code union all
select 12 id, 'l' name, '42092219970101000' code union all
select 13 id, 'm' name, '42092219980101000' code 
)
select *
from source;

    给定一个父级节点,找到该节点的所有子孙节点;给定一个节点, 找到该节点的所有祖宗

    WITH source AS (
SELECT 1  id, NULL AS parentid, 'a' name UNION ALL
SELECT 2  id, 1    AS parentid, 'b' name UNION ALL
SELECT 3  id, 1           AS parentid, 'c' name UNION ALL
SELECT 4  id, 2    AS parentid, 'd' name UNION ALL
SELECT 5  id, 2    AS parentid, 'e' name UNION ALL
SELECT 6  id, 2    AS parentid, 'f' name UNION ALL
SELECT 7  id, 3 AS parentid, 'g' name UNION ALL
SELECT 8  id, 3 AS parentid, 'h' name UNION ALL
SELECT 9  id, 4 AS parentid, 'i' name UNION ALL
SELECT 10 id, 4 AS parentid, 'j' name UNION ALL
SELECT 11 id, 5 AS parentid, 'k' name UNION ALL
SELECT 12 id, 5 AS parentid, 'l' name UNION ALL
SELECT 13 id, 6 AS parentid, 'm' name UNION ALL
SELECT 14 id, 7 AS parentid, 'n' name UNION ALL
SELECT 15 id, 14 AS parentid, 'o' name 
)
SELECT * INTO employee
FROM source;
GO
SELECT * FROM dbo.employee;

--1,给定一个父级节点,找到该节点的所有子孙节点
DECLARE @NodeId INT = 14;

WITH mycte (parentid, ID, level, name)
AS (SELECT parentid,
           id,
           0 level,
           name
    FROM employee
    WHERE id = @NodeId
    UNION ALL
    SELECT p.parentid,
           p.id,
           o.level + 1,
           p.name
    FROM mycte o
        INNER JOIN employee p
            ON o.ID = p.parentid)
SELECT *
FROM mycte
where level>0

--2,给定一个节点, 找到该节点的所有祖宗
DECLARE @NodeId1 INT = 15;

WITH mycte (parentid, ID, level, name)
AS (SELECT parentid,
           id,
           0 level,
           name
    FROM employee
    WHERE id = @NodeId1
    UNION ALL
    SELECT p.parentid,
           p.id,
           o.level + 1,
           p.name
    FROM mycte o
        INNER JOIN employee p
            ON o.parentid = p.ID)
SELECT *
FROM mycte
where level>0
ORDER BY mycte.parentid

    相关文章

      网友评论

          本文标题：SQL查询基础-源动力

          本文链接：https://www.haomeiwen.com/subject/onnspltx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|SQL查询基础-源动力|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！