SQL知识补充

作者: 山猪打不过家猪 | 来源:发表于2024-01-18 10:22 被阅读0次

SQL高级运用
mysql的用法2
简单sql积累总结，有时间就加上点
安服工程师培训
架构师之路-互联网架构模板
《Flask Web Development》第5章数据库
【互联网系统】【线上维护】long sql的紧急性-判断公式
知识补充
知识补充
知识补充

0.恢复.bak文件

下载adventure2017.bak文件
将bak文件放入到指定的文件夹，如何知道文件夹路径，如图：

选择恢复数据库

image.png
.bak放置的文件路径

image.png

1.子查询

1.1 子查询可以出现的位置

where

where，需注意的是 where里不能出现分组例如sum,avg,groupby

#找出比jack的sal大的所有结果
select * from emp where 
sal > (select sal from emp where name = 'jack')

select

select，
注意：select 后的子查询必须是返回一个结果的子查询

select a.empId, a.empName, a. sal, 
jobName = (select job from empInfo b where a. empId = b.empId) 
from emp a

from

可以把from后面的子查询看作为一个新的表，必须加上表名

select * from (select empId, empName from emp) as new_emp

注意在处理top-N的问题时，我们可以使用创建一个新的排序后的表在进行取值
获取排名前三的员工薪水，并排序

select romnum, empId,empName,
sal from (select * from emp order by sal desc) 
where rownum <3

1.2 使用关联子查询比较行

1.2.1 连续数据的对比

image.png

比较今年和上一年的sale的差，使用子查询

select s1.year,s1.sale,
'year-1' = (select s2.sale from [Sales_year] s2 where s2.year = s1.year-1),
sal_diff = s1.sale - (select s2.sale from [Sales_year] s2 where s2.year = s1.year-1) 
from [Sales_year] s1

image.png

也可以使用笛卡尔积来计算

SELECT S1.year,S1.sale ,
'A' = S1.sale - S2.sale 
FROM [demo01].[dbo].[Sales_year] S1,[demo01].[dbo].[Sales_year] S2
WHERE S1.year = S2.year +1
ORDER BY S1.year

1.2.2 非连续数据的对比

image.png

这里由于年份不稳定，不能使用-1，但是，可以找到比当前year小的，最大的year

SELECT S2.year AS pre_year, S1.year AS now_year, S2.sale AS pre_sale, S1.sale AS now_sale, S1.sale - S2.sale AS diff
FROM Sales2 S1, Sales2 S2
WHERE S2.year = (SELECT MAX(year) FROM Sales2 S3 WHERE S1.year > S3.year)
ORDER BY now_year;

image.png

1.2.3 移动和滚动

image.png

移动

累计，普通的SUM只能计算固定的和，如果需要计算每行的动态SUM，就需要窗口函数，这也是他的主要作用

  SELECT*,
  SUM(prc_amt) OVER (ORDER BY prc_date) onhand_amt 
  FROM Accounts

image.png

滚动

如果我们需要固定3行，然后一行一行偏移计算SUM或者AVG，就需要给OVER加参数

image.png

SELECT prc_date, prc_amt, 
SUM(prc_amt) OVER (ORDER BY prc_date  ROWS 2 PRECEDING) AS onhand_amt
FROM Accounts;

image.png

2. 存储过程

2.1 无参数的存储过程

语法

create proc [存储过程名称]
as
    begin 
          sql语句
    end

#执行存储过程
exec [存储过程名称]

例子

create proc proc_get_name
as
    begin
        select * from sales
    end

exec proc_get_name

修改存储过程,只需要将create 改为 alter

2.2 带参数的存储过程

语法

create proc 存储过程名称（
@参数1 类型，
@参数2 类型
）
as 
begin
    带参数的sql语句
end

#执行
exec 存储过程名称 @参数1 = 值1, @参数2 = 值2

例子

create proc proc_get_sales(
    @id int,
    @name varchar(200)
)
as
    begin
        select * from sales where Id = @id and c_name = @name
    end

exec proc_get_sales @id= 1, @type='a'

2.3 带参数和返回值的

定义

-- 创建存储过程
CREATE PROCEDURE GetEmployeeInfo
    @employeeId INT,
    @name VARCHAR(255) OUTPUT,
    @gender VARCHAR(10) OUTPUT
AS
BEGIN
    -- 查询员工信息
    SELECT @name = name, @gender = gender
    FROM tbl_employee
    WHERE empid = @employeeId;
END;

执行

-- 声明变量用于存储返回值
DECLARE @employeeName VARCHAR(255), @employeeGender VARCHAR(10);

-- 调用存储过程
EXEC GetEmployeeInfo @employeeId = 1, @name = @employeeName OUTPUT, @gender = @employeeGender OUTPUT;

-- 显示返回值
SELECT 'Employee Name: ' + @employeeName AS EmployeeName, 'Gender: ' + @employeeGender AS Gender;

3.分组和聚合

where是对原始数据过滤，having是对分组过后的数据过滤，所以having 在where之后

3.1 多字段分组

例如，先对男女进行分组，计算出所有男女的参加了多少们课程；然后在对地区分组，可以查看不同地区，男女的参加课程的总数

select count(course),gender,region from students group by gender,region

3.2 分组后的表，显示多字段为一行

有时候，我们分组过后，有些重要的信息,需要显示出来，但是由于分组的特点不能逐一显示，我们可以将多行显示为一行

使用xml将多行数转为一行

  SELECT [TotalDue] FROM [AdventureWorks2017].[Sales].[SalesOrderHeader] WHERE [ShipMethodID] =1 
  for xml path

去除<row>标签

  SELECT [TotalDue] FROM [AdventureWorks2017].[Sales].[SalesOrderHeader] WHERE [ShipMethodID] =1 
  for xml path(' ')

去除xml的所有标签，这里拼接,如果后面的字段不是字符串，需要转为字符串

  SELECT ',' + CAST([TotalDue] AS varchar(10)) FROM [AdventureWorks2017].[Sales].[SalesOrderHeader] WHERE [ShipMethodID] =1 
  for xml path('')

image.png
4.使用STUFF替换字符串

  SELECT 
  STUFF(
  (SELECT ',' + CAST([TotalDue] AS varchar(10)) FROM [AdventureWorks2017].[Sales].[SalesOrderHeader] WHERE [ShipMethodID] =1 
  for xml path('')),1,1,'')

使用declare看的更加美观

DECLARE @long_value varchar(500) = (SELECT  ',' + CAST([TotalDue] AS varchar(10)) FROM [AdventureWorks2017].[Sales].[SalesOrderHeader] WHERE [ShipMethodID] =1 
for xml path('') )

SELECT STUFF(@long_value,1,1,'')

现实使用场景，结合子查询，查询每个订单的LineTotal，并且显示为一行

 select 
CustomerID,
TotalDue,
Freight,
Total = 
(
    SELECT STUFF(
        (select 
        ','+ CAST(CAST(LineTotal AS money) AS varchar)
        from sales.SalesOrderDetail A
        where A.SalesOrderID = B. SalesOrderID
        for xml path('')),1,1,''
    )
)
 from sales.SalesOrderHeader B
 ORDER BY 1

4.字符串常用方法

4.1字符串截取substring

SUBSTRING (expression, start, length)

SELECT SUBSTRING('abcdefgh', 1, 3)  
##
-->abc

4.2字符串替换stuff

STUFF (character, start, length, replaceWith_expression)

#第一个位置，一个长度，用空字符串代替
SELECT STUFF(@long_value,1,1,' ')

4.3 去除字符串左右空格replace

SELECT REPLACE('   a  ', ' ', '')

5.CASE WHEN

5.1基础语法

image.png
注意：一定要加END不然报错

5.2 普通使用

例1

image.png

SELECT COUNT(branch_id) counties,
    CASE 
        WHEN branch_id in (1,12,111,121,1211,1212) THEN '华东'
        WHEN branch_id in (21,211,2111,2112) THEN '华南'
    ELSE 'CHIAN'        
    END district
    FROM Branch group by 
        CASE 
            WHEN branch_id in (1,12,111,121,1211,1212) THEN '华东'
            WHEN branch_id in (21,211,2111,2112) THEN '华南'
       ELSE 'CHIAN' 
       END

image.png

例2

image.png

  SELECT [pref_name],
  SUM(CASE WHEN sex = 1 THEN [population] ELSE 0 END) male,
  SUM(CASE WHEN sex = 2 THEN population ELSE 0 END) female
  FROM [population_table] group by [pref_name]

只有在case的字段是值类型的时候才可以使用运算

5.3 在update里使用

image.png

假设现在需要根据以下条件对该表的数据进行更新。

对当前工资为 30 万日元以上的员工，降薪 10%。
对当前工资为 25 万日元以上且不满 28 万日元的员工，加薪 20%。


update [employee_table] 
set salary = (
CASE 
    WHEN salary>300000 THEN salary - salary*0.1
    WHEN salary between 250000 and 280000 THEN salary+salary*0.2
ELSE salary 
END
)

image.png

5.4表于表的数据匹配

image.png

SELECT [course_name],
    CASE WHEN course_id in (select course_id from OpenCourses where month = '200706') THEN 'O' ELSE 'X' END AS '6 月',
    CASE WHEN course_id in (SELECT course_id from OpenCourses WHERE month = '200707') THEN 'O' ELSE 'X' END AS '7 月',
    CASE WHEN course_id in (SELECT course_id FROM OpenCourses WHERE month = '200708') THEN 'O' ELSE 'X' END AS '8 月'
FROM [demo01].[dbo].[CourseMaster]

总结：这类派生原表里没有的列，那么使用完整的CASE WHEN生成新的一列

6. 自连接

自连接的定义：
两张表结构和数据内容完全一样的表，在做数据处理的时候，我们通常会给它们分别重命名来加以区分（言外之意：不重命名也不行啊，不然数据库也不认识它们谁是谁），然后进行关联。

6.1 自连接去重

去重

image.png

之前的方法去重（舍弃）

传统方法:该方法有个弊端，每次只能删除一个重复的行（舍弃）

SELECT max(id) minID,name,price,COUNT(1) repeat_counts
  FROM [demo01].[dbo].[Products_rep] group by 
  name,price having count(1)>1

image.png

自连接方法去重

其实也是一种变种的子查询，自己连接自己的子查询

select * from [Products_rep] p1
    where exists(
        select * from  [Products_rep] p2 where p1.name = p2.name and p1.price = p2.price and p1.ID<p2.ID
    )

image.png

终极删除重复，适用于任何情况

现有一张表

image.png

2.其中，dep_id和name是不能重复的条件，使用ROW_NUM进行删除

  WITH DP AS( SELECT *,ROW_NUMBER() over(partition by name,dep_id order by name) as rn FROM emp_duplicate)
  SELECT * FROM DP where rn >1

image.png

6.2查找一致或不一致的列

image.png

查找地址写错的家庭，即ID相同，地址不同的家庭

select * FROM [demo01].[dbo].[Addresses] a1,[Addresses] a2
    WHERE a1.family_id = a2.family_id AND a1.address <>a2.address

7.Having

重要：只要使用GROUP BY，那么默认具有HAVING COUNT(*)

7.1 查询不含null的集合

image.png

查询准时提交的学院，既查询出来的所有的总数和有日期的总数相等，则全部提交

# method1：use having 
SELECT dpt
 FROM Students
 GROUP BY dpt
HAVING COUNT(*) = COUNT(sbmt_date);
# method2: use case when
SELECT dpt FROM Students 
GROUP BY dpt
HAVING COUNT(*)  = SUM(CASE WHEN sbmt_date is not null THEN 1 ELSE 0 END)

7.2 笛卡尔积进行购物篮分析

image.png

查找包含所有item的shop，即查询啤酒、纸尿裤和自行车同时在库的店铺

select s1.shop from ShopItems s1,Items i1
    where s1.item = i1.item 
    group by shop
    having COUNT(s1.shop) = (select COUNT(1) from Items)

8. 外连接

8.1 使用外连接解决行列转换

image.png

方法1：外连接

使用子查询C0将name的基准列写出，即侧栏; 通过C0完整的name和其他条件的name进行左连接，可以得到没有空缺的；再通过CASE WHEN根据自己的需求创建新的列

  select C0.name,
    CASE WHEN C0.name =  C1.name THEN  '○' END as 'SQL 入门',
    CASE WHEN C0.name = C2.name THEN '○' END as 'UNIX 基础',
    CASE WHEN C0.name = C3.name THEN '○' END AS 'Java 中级'
  from (select distinct [name] from Courses) C0
    left join(select name from Courses where course = 'SQL 入门') C1
        on C0.name = C1.name
    LEFT JOIN (SELECT name FROM Courses WHERE course = 'UNIX 基础') C2
        ON C0.name = C2.name
    LEFT JOIN (SELECT name FROM Courses WHERE course = 'Java 中级') C3
        ON C0.name = C3.name

注意：外连接其实都可以使用子查询来代替

 select C0.name, (select '○' from Courses C1 where C0.name = C1.name and course = 'SQL 入门') AS 'SQL 入门',
  (select '○' from Courses C2 where C0.name = C2.name and course = 'UNIX 基础') AS 'UNIX 基础'
  from (select distinct name from Courses) C0

方法2：使用CASE WHEN

  select name,
    CASE WHEN name in (select name from Courses  where course = 'SQL 入门') THEN '○' ELSE NULL END 'SQL入门',
    CASE WHEN name in (select name from Courses where course = 'UNIX 基础') THEN '○' ELSE NULL END 'UNIX基础',
    CASE WHEN name in (select name from Courses where course = 'Java 中级') THEN '○' ELSE NULL END 'Java 中级'
  from Courses  group by name

image.png

该方法虽然也显示出了表，但是不完美，和需求有出入
方法3：方法1改进CASE嵌套CASE

对于字符串我们无法SUM，可以先用CASE 转为0,1然后在嵌套另外一个CASE用来判断

  SELECT NAME,  
    CASE WHEN SUM(CASE WHEN course = 'SQL 入门' THEN 1 ELSE NULL END )= 1 THEN '○' ELSE NULL END AS  'SQL入门',
    CASE WHEN SUM(CASE WHEN course = 'UNIX 基础' THEN 1 ELSE NULL END) = 1 THEN '○' ELSE NULL END AS 'UNIX基础'
  FROM [Courses] GROUP BY name

8.2 列转行

image.png

使用`UNION ALL' 将employee分别和需要拆分的列依次连接

SELECT employee, child_1 FROM Personnel 
UNION ALL
SELECT employee, child_2 FROM Personnel
UNION ALL 
SELECT employee,child_3 FROM Personnel

8.3 两张表计算总数

image.png

方法一：先根据item_no聚合右表，然后连接左表，但是该方法有个弊端是创建了一个临时视图，即右表的聚合表

SELECT I.item_no, SH.total_qty
   FROM Items I LEFT OUTER JOIN
     (SELECT item_no, SUM(quantity) AS total_qty
        FROM SalesHistory
          GROUP BY item_no) SH ON I.item_no = SH.item_no;

方法二：由于是一对多的关系，所以可以先进行一对多的外连接，然后在进行聚合

SELECT  I.item_no, SUM(SH.quantity) AS total_qty
  FROM Items I LEFT JOIN SalesHistory SH 
    ON I.item_no = SH.item_no 
      GOURP BY  I.item_no

8.4 用外连接进行集合运算

image.png

8.4.1 INNER JOIN 集合的交集

image.png

SELECT * FROM [demo01].[dbo].[Class_A] A
INNER JOIN
Class_B B ON A.ID =B.ID

8.4.2 UNION 集合的和集

image.png

SELECT * FROM [demo01].[dbo].[Class_A] 
UNION 
SELECT * FROM Class_B

image.png

8.4.3 A - B

image.png

SELECT * FROM Class_A A LEFT OUTER JOIN Class_B B
 ON A.id = B.id
 WHERE B.name IS NULL;

image.png

8.4.4 B - A

image.png

SELECT * FROM Class_A A RIGHT JOIN Class_B B
 ON A.id = B.id

image.png

SQL高级运用
-- =================================sql基础补充==============...
mysql的用法2
-- =================================sql基础补充==============...
简单sql积累总结，有时间就加上点
最近一段时间挺忙的，一直没时间去学习新的知识，总结一一点sql知识，后期会不断补充最简单的入门sql，查询，经常...
安服工程师培训
安全概念和资讯 1. 前期安全知识的补充学习。熟悉基本的安全术语和概念（SQL注入、上传漏洞、XSS、CSRF、...
架构师之路-互联网架构模板
NoSql不是No Sql，而是Not Only SQL，即NoSql是SQL的补充。 NoSql发展到一定规模后...
《Flask Web Development》第5章数据库
SQL Databases 特点是什么？（得空补充） NoSQL Databases 特点是什么？（得空补充） S...
【互联网系统】【线上维护】long sql的紧急性-判断公式
long sql的紧急性 long sql的紧急性=数目*（耗时平方，单位：秒）补充： 1个10秒的慢SQL对系...
知识补充
在不允许修改参数变量的值时可以加上const修饰符图片在ios中会自动识别图片@几x来按比例显示假如图片是xx...
知识补充
1. 傅立叶变换： (1) 傅立叶级数：法国数学家傅里叶发现，任何周期函数都可以用正弦函数和余弦函数构成的无穷级数...
知识补充
必备词 breakfast voucher[ˈvaʊtʃɚ] 早餐卷 lunch voucher dinner v...

SQL知识补充

0.恢复.bak文件

1.子查询

1.1 子查询可以出现的位置

where

select

from

1.2 使用关联子查询比较行

1.2.1 连续数据的对比

1.2.2 非连续数据的对比

1.2.3 移动和滚动

移动

滚动

2. 存储过程

2.1 无参数的存储过程

2.2 带参数的存储过程

2.3 带参数和返回值的

3.分组和聚合

3.1 多字段分组

3.2 分组后的表，显示多字段为一行

4.字符串常用方法

4.1字符串截取substring

4.2字符串替换stuff

4.3 去除字符串左右空格replace

5.CASE WHEN

5.1基础语法

5.2 普通使用

5.3 在update里使用

5.4表于表的数据匹配

6. 自连接

6.1 自连接去重

之前的方法去重（舍弃）

自连接方法去重

终极删除重复，适用于任何情况

6.2查找一致或不一致的列

7.Having

7.1 查询不含null的集合

7.2 笛卡尔积进行购物篮分析

8. 外连接

8.1 使用外连接解决行列转换

8.2 列转行

8.3 两张表计算总数

8.4 用外连接进行集合运算

8.4.1 INNER JOIN 集合的交集

8.4.2 UNION 集合的和集

8.4.3 A - B

8.4.4 B - A

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读