【教奶奶学SQL】(task6)秋招秘籍B

学习总结

文章目录

• ​​学习总结​​
• ​​练习一：行转列​​
• ​​练习二：列转行​​
• ​​练习三：带货主播​​
• ​​练习四：MySQL 中如何查看sql语句的执行计划？可以看到哪些信息？​​
• ​​练习五：解释一下 SQL 数据库中 ACID 是指什么​​

• ​​5.1 原子性(Atomicity)​​
• ​​5.2 一致性(Consistency)​​
• ​​5.3 隔离性（Isolation)​​

• ​​（1）脏读​​
• ​​（2）幻读​​

• ​​5.4 持久性（Durability)​​

• ​​Reference​​

练习一：行转列

假设 A B C 三位小朋友期末考试成绩如下所示：

+-----+-----------+------|
| name|   subject |score |
+-----+-----------+------|
|  A  |  chinese  |  99  |
|  A  |  math     |  98  |
|  A  |  english  |  97  |
|  B  |  chinese  |  92  |
|  B  |  math     |  91  |
|  B  |  english  |  90  |
|  C  |  chinese  |  88  |
|  C  |  math     |  87  |
|  C  |  english  |  86  |
+-----+-----------+------|

请使用 SQL 代码将以上成绩转换为如下格式：

+-----+-----------+------|---------|
| name|   chinese | math | english |
+-----+-----------+------|---------|
|  A  |     99    |  98  |    97   |
|  B  |     92    |  91  |    90   |
|  C  |     88    |  87  |    86   |
+-----+-----------+------|---------|

方法一：

# 核心步骤 
SELECT  
  name,
  SUM(CASE WHEN subject = 'chinese' THEN score
       ELSE NULL
    END) AS 'chinese',
  SUM(CASE WHEN subject = 'math' THEN score
       ELSE NULL 
    END) AS 'math',
  SUM(CASE WHEN subject = 'english' THEN score
       ELSE NULL 
        END) AS 'english'
FROM score1 
GROUP BY name;

可以回顾​​SQL中的CASE使用方法​​​。
PS：在Oracle中可以使用​​​PIVOT​​​函数如下，mysql中也是可以的，参考​​MySQL实现pivot行转列​​

SELECT a.name AS name, a.chinese as chinese, a.math as math, a.english as english
FROM score1 
PIVOT(
  SUM(score) FOR subject IN (chinese, math, english)
)AS a
GROUP BY a.name;

练习二：列转行

假设 A B C 三位小朋友期末考试成绩如下所示：

+-----+-----------+------|---------|
| name|   chinese | math | english |
+-----+-----------+------|---------|
|  A  |     99    |  98  |    97   |
|  B  |     92    |  91  |    90   |
|  C  |     88    |  87  |    86   |
+-----+-----------+------|---------|

请使用 SQL 代码将以上成绩转换为如下格式：

+-----+-----------+------|
| name|   subject |score |
+-----+-----------+------|
|  A  |  chinese  |  99  |
|  A  |  math     |  98  |
|  A  |  english  |  97  |
|  B  |  chinese  |  92  |
|  B  |  math     |  91  |
|  B  |  english  |  90  |
|  C  |  chinese  |  88  |
|  C  |  math     |  87  |
|  C  |  english  |  86  |
+-----+-----------+------|

这里我们先直接利用练习1select出现的表：

DROP TABLE score2;
CREATE TABLE score2 AS 
SELECT * FROM (
SELECT  
  name,
  SUM(CASE WHEN subject = 'chinese' THEN score
       ELSE NULL
    END) AS 'chinese',
  SUM(CASE WHEN subject = 'math' THEN score
       ELSE NULL 
    END) AS 'math',
  SUM(CASE WHEN subject = 'english' THEN score
       ELSE NULL 
        END) AS 'english'
FROM score1 
GROUP BY name
) AS score2;
SELECT * FROM score2;

这里创建的表就是刚才练习一的了：

注意在题目表中如语文的成绩字段名是​​chinese​​​而非​​score​​​，所以需要​​chinese AS score​​：

# 核心步骤 
SELECT name, 'chinese' AS subject, chinese AS score 
FROM score2  
UNION ALL 
SELECT name, 'math' AS math, math AS score 
FROM score2 
UNION ALL  
SELECT name, 'english' AS english, english AS score 
FROM score2 
ORDER BY name;

练习三：带货主播

假设，某平台2021年主播带货销售额日统计数据如下：
表名 ​​​anchor_sales​​

+-------------+------------+---------|
| anchor_name |     date   |  sales  | 
+-------------+------------+---------|
|      A      |  20210101  |  40000  |
|      B      |  20210101  |  80000  |
|      A      |  20210102  |  10000  |
|      C      |  20210102  |  90000  |
|      A      |  20210103  |   7500  |
|      C      |  20210103  |  80000  |
+-------------+------------+---------|

定义：如果某主播的某日销售额占比达到该平台当日销售总额的 90% 及以上，则称该主播为明星主播，当天也称为明星主播日。

首先我们插入数据：

# 练习3 
DROP TABLE if EXISTS anchor_sales;
CREATE TABLE anchor_sales
(anchor_name VARCHAR(4) NOT NULL,
date INTEGER NOT NULL,
 sales INTEGER NOT NULL,
 PRIMARY KEY(anchor_name, date));

INSERT INTO anchor_sales VALUES('A',20210101,40000);
INSERT INTO anchor_sales VALUES('B',20210101,80000);
INSERT INTO anchor_sales VALUES('A',20210102,10000);
INSERT INTO anchor_sales VALUES('C',20210102,90000);
INSERT INTO anchor_sales VALUES('A',20210103,7500);
INSERT INTO anchor_sales VALUES('C',20210103,80000);
SELECT * FROM anchor_sales;

请使用 SQL 完成如下计算：

a. 2021年有多少个明星主播日？
b. 2021年有多少个明星主播？

方法一：
这其实是一个问题，找到明星主播日及其对应的明星主播，我们可以先计算出不同人在当天的销售比率（占当天所有额），这一步通过聚合函数​​​SUM​​和窗口函数的结合使用。

# 计算出不同人在当天的销售比率（占当天所有额）
SELECT date, anchor_name, 
(SUM(sales) / SUM(sales) OVER (PARTITION BY date)) AS sale_rate 
FROM anchor_sales 
GROUP BY date, anchor_name 
ORDER BY date, anchor_name DESC;

然后在上面这个表中进行筛选出​​sale_rate​​大于等于90%，全部过程：

SELECT * FROM (
SELECT date, anchor_name, 
(SUM(sales) / SUM(sales) OVER (PARTITION BY date)) AS sale_rate 
FROM anchor_sales 
GROUP BY date, anchor_name 
ORDER BY date, anchor_name DESC
) AS a 
WHERE sale_rate >= 0.9;

练习四：MySQL 中如何查看sql语句的执行计划？可以看到哪些信息？

可以使用profiles来查看sql 语句执行计划，要使用该功能，mysql的版本必须在5.0.37版本以上。否则只能使用explain 的方式来检查。
profiling 功能可以了解到cpu io 等更详细的信息。
show profile 的格式如下：

SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type:
    ALL  | BLOCK IO  | CONTEXT SWITCHES  | CPU  | IPC  | MEMORY  | PAGE FAULTS  | SOURCE  |

默认方式下该功能是关闭的。

练习五：解释一下 SQL 数据库中 ACID 是指什么

ACID为原子性（Atomicity）、一致性(Consistency)、隔离性（Isolation)、持久性（Durability)的总称。

5.1 原子性(Atomicity)

整个事务是一个不可分割整体，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚(Rollback)到事务开始前的状态，就像这个事务从来没有执行过一样。

每一条的T-SQL语句都是一个事务，如insert语句、update语句等。用户也可以定义自己的事务，使用TYR-CATCH方法将多条语句合为一个事务，比如银行转账，在A账户中减钱与在B账户中增钱是一个自定义的事务。

5.2 一致性(Consistency)

一致性,即在事务开始之前和事务结束以后，数据库的完整性约束（唯一约束，外键约束,Check约束等）没有被破坏。业务的一致性可以转化为数据库的一致性。

5.3 隔离性（Isolation)

隔离执行事务，多个事务的执行互相不干扰。一个事务不可能获取到另一个事务执行的中间数据。SQL Server利用加锁造成阻塞来保证事务之间不同等级的隔离性。

事务之间的互相影响的情况分为几种，分别为:脏读(Dirty Read)，不可重复读，幻读。

（1）脏读

脏读表示一个事务获取了另一个事务的未提交数据，这个数据有可能被回滚。

不可重复度表示一个事务执行两次相同的查询，出现了不同的结果，这是因为两次查询中间有另一事务对数据进行了修改。

（2）幻读

幻读,是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，第一个事务的用户发现表中还有 没有修改的数据行，就好象发生了幻觉一样。

为了避免上述几种事务之间的影响,SQL Server通过设置不同的隔离等级来进行不同程度的避免。因为高的隔离等级意味着更多的锁，从而牺牲性能.所以这个选项开放给了用户根据具体的需求进行设置。不过默认的隔离等级Read Commited符合了99%的实际需求.

5.4 持久性（Durability)

在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

Reference

（1）datawhale notebook