美文网首页
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://docs.microsoft.com/zh-cn/sql/sql-server/...

  • sql

    sql-基础sql-基础查询-1sql-基础查询-2sql-更新 概览 数据库（Database，DB）：将大量数...

  • SQL基础-索引-源动力

    索引的基本概念 什么是索引？ 索引可以帮助数据库引擎在表中查找数据而不需要扫描整个表。索引是减少磁盘IO和逻辑读次...

  • SQL基础(二)

    前言 基础SQL入门详情看SQL(一)多表联接的原理详细看SQL(一) 查询 在SQL中, 最难的莫过于查询.因为...

  • SQL查询_基本查询

    SQL查询_基本查询 select 用于指定查询所获得的结果列，from 指定查询的数据源，数据源可以是一个表，也...

  • mysql手工注入

    SQL基础 1.1 什么是sql？ SQL（structured query language），即结构化查询语言...

  • SQL基础-事务和锁--源动力

    事务隔离级别 什么是事务？ 事务（Transaction）由作为包含执行的单个命令或一组命令组成。 通过事务可以将...

  • SQL基础-执行计划-源动力

    什么是执行计划 执行计划:是查询优化器分析语句后，生成的一种确定性访问所需数据的最高效方式。 执行计划的输入： 查...

  • 数据库笔记-SQL查询&过滤

    基础查询 查询排序 查询过滤 基础查询 以极客时间专栏《SQL必知必会》的王者荣耀表格为例。 SELECT语句 检...

  • SQL基础(一)

    SQL SQL是关系数据库的查询语言.在SQL中最难的应该算是查询。本篇文章, 注重点在于总结基础的sql语句, ...

网友评论

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

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

      延伸阅读

      深度阅读

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

      栏目导航

      热点阅读

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

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

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

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

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

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