MySQL基础day02-CSDN博客

本文链接：https://blog.csdn.net/m0_66986239/article/details/149425271

1.多表关联查询-表之间的3种关联关系:

表之间的关系可以分为如下 3 种：一对一关系、一对多关系、多对多关系。

① 一对一关系：A表的一行记录只对应B表的一行记录，反过来B表的一行记录也只对应A表的一行记录。

举例：员工基础信息表、员工详细信息表

② 一对多关系：A表的一行记录对应B表的多行记录，反过来B表的一行记录只对应A表的一行记录。

举例：商品分类和商品表

③ 多对多关系：A表的一行记录对应B表的多行记录，反过来B表的一行记录也对应A表的多行记录。

举例：学生表和课程表(选课关系)

2.多表关联查询-外键约束:

(1)主表与从表:

假设有两张表A和B，B表的某列引用了A表的主键列，则B表的这一列称为B表的外键列(Foreign Key)，其中A表称为主表，B表称为从表。

在一对多关联关系建表时，在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键。

(2)外键约束语法：

CONSTRAINT FOREIGN KEY (外键字段) REFERENCES 主表名(主键)

-- 示例1：新建分类表 category 和 商品表 product
# 创建分类表
CREATE TABLE category (
    cid   VARCHAR(32) PRIMARY KEY, # 分类id
    cname VARCHAR(100) # 分类名称
);

DESC category;

# 商品表
CREATE TABLE products (
    pid         VARCHAR(32) PRIMARY KEY,
    pname        VARCHAR(40),
    price       DOUBLE,
    category_id VARCHAR(32),
    # CONSTRAINT 约束
    # REFERENCES 引用
    CONSTRAINT FOREIGN KEY (category_id) REFERENCES category (cid) # 添加外键约束
);

(3)外键约束的作用：

保证插入数据的准确性：从表中外键的值在主表主键中必须有对应的值。
保存删除数据的完整性：主表的主键值被从表外键引用之后，主表中对应的记录不能被删除。

(4)外键约束的缺点：

过分强调或者说使用主键／外键会平添开发难度。
添加外键，也会降低数据增删改的性能。
注意：实际开发，很少使用外键约束，而是从代码层面保持表之间的关系。

(5)外键其他操作：

-- 查看表的约束
SHOW CREATE TABLE 表名;
-- 删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;

-- 示例：
-- 查看表的约束
SHOW CREATE TABLE products;
-- 删除外键约束
ALTER TABLE products DROP FOREIGN KEY products_ibfk_1;

3.多表关联查询-关联查询操作:

关联查询的语法：

SELECT
    *
FROM 左表
INNER|LEFT|RIGHT|FULL JOIN 右表
ON 左表.列名 = 右表.列名 OR 左表.列名 = 右表.列名;

关联查询的 4 种分类：

(1）内连接：INNER JOIN，简写为 JOIN

也称为等值连接，返回两张表都满足条件的部分(交集)。
左右两表关联时，满足关联条件的数据，才会出现在最终的关联结果中。

-- 查询所有英雄所对应的功夫名称，如果没有则不显示(内连接)
SELECT * FROM hero INNER JOIN kongfu ON hero.kongfu_id=kongfu.kid;

(2）左外连接：LEFT OUTER JOIN，简写为 LEFT JOIN

左侧+交集部分
左右两表关联时，除满足关联条件的数据会出现在最终的关联结果中，左表中不能和右边表联的数据也会出现，右表侧自动填充为NULL。

-- 查询所有英雄对应的武功，没有武功的的英雄也需要展示出来(左连接)
SELECT * FROM hero LEFT JOIN kongfu ON hero.kongfu_id=kongfu.kid;

(3）右外连接：RIGHT OUTER JOIN，简写为 RIGHT JOIN

右侧+交集部分
左右两表关联时，除满足关联条件的数据会出现在最终的关联结果中，右表中不能和左表关联的数据也会出现，左表侧自动填充为NULL。

-- 查询所有武功对应的英雄，保留所有的武功，没有武功的英雄不展示(右连接)
SELECT * FROM hero RIGHT JOIN kongfu ON hero.kongfu_id=kongfu.kid;

(4）全外连接：FULL OUTER JOIN，简写为 FULL JOIN

注意：MySQL数据库不支持全连接，需要将左外连接和右外连接的结果利用 UNION 关键字组合实现全连接的效果。

左侧 + 交集部分 + 右则
左右两表关联时，除满足关联条件的数据会出现在最终的关联结果中，左右两表不能相互关联的数据也都会出现，对应侧自动填充为NULL。

-- 查询所有英雄对应的武功，保留所有的英雄和武功，没有武功的英雄和没有英雄的武功也都有展示(全连接)
SELECT * FROM hero LEFT JOIN kongfu ON hero.kongfu_id=kongfu.kid
UNION
SELECT * FROM hero RIGHT JOIN kongfu ON hero.kongfu_id=kongfu.kid;

4.多表关联查询-自关联查询:

进行关联时，左表和右表是同一个表，这样的连接叫自关联。注意：自关联时，需要给表起别名。

-- 创建一个地区表
CREATE TABLE areas(
    id VARCHAR(30) NOT NULL PRIMARY KEY,
    title VARCHAR(30),
    pid VARCHAR(30)
);

-- 示例1：查询'山西省'下的所有市的信息
-- 查询结果字段：
--   市级地区id、市级地区名称、父级地区id、父级地区名称

SELECT
    c.id,
    c.title,
    c.pid,
    p.title
FROM areas c -- 理解为市表
JOIN areas p -- 理解为省表
ON c.pid = p.id
WHERE p.title = '山西省';

5.SQL查询时使用AS关键字起别名:

在SQL查询时，可以使用 AS 给表或者字段起别名,其中AS可以省略,当别名为关键字时,两边需添加``。

(1）给查询字段起别名:

-- 示例1：查询每个分类的商品数量
SELECT
    category_id,
    -- COUNT(*) AS product_count
    COUNT(*) product_count -- 起别名时，AS关键字可以省略
FROM products
GROUP BY category_id;

SELECT
    category_id,
    COUNT(*) `desc` -- 注意：别名为关键字时，别名两边要加``，否则报错
FROM products
GROUP BY category_id;

(2）给表起别名:

-- 示例2：查询每个分类名称所对应的商品数量(没有商品的分类的也要显示)
SELECT
    c.cname,
    COUNT(*) product_count
FROM category c -- category表的别名叫c
LEFT JOIN products p -- products表的别名叫p
ON c.cid = p.category_id
GROUP BY c.cid, c.cname;

6.SQL子查询操作:

在一个 SELECT 语句中，嵌入了另外一个 SELECT 语句，那么被嵌入的 SELECT 语句称之为子查询语句，外部那个SELECT 语句则称为主查询。

主查询和子查询的关系：

(1）子查询是嵌入到主查询中。

(2）子查询是辅助主查询的，要么充当条件，要么充当数据源，要么充当查询字段。

(3）子查询是可以独立存在的语句，是一条完整的 SELECT 语句。

-- (子查询做查询条件)：查询当前商品大于平均价格的商品
-- ① 查询商品的平均价格
SELECT AVG(price) FROM products;

-- ② 查询所有商品
SELECT * FROM products;

-- ③ 将第①步的结果作为第②步的查询条件
SELECT
    *
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- (子查询做数据源)：查询不同类型商品的平均价格
-- 查询结果字段：
--  category_id(分类id)、cname(分类名称)、avg(分类商品平均价格)

SELECT
    category_id,
    cname,
    avg
FROM (
    SELECT
        category_id,
        AVG(price) AS `avg`
    FROM products
    GROUP BY category_id
) a -- 子查询作用数据源时，必须起别名
JOIN category b
ON a.category_id = b.cid;

-- (子查询做查询字段)：针对 students 表的数据，计算每个同学的Score分数和整体平均分数的差值
SELECT
    *,
    (SELECT AVG(Score) FROM students) AS `avg`,
    Score - (SELECT AVG(Score) FROM students) AS `difference`
FROM students;

7.SQL进阶-窗口函数简介 :

窗口函数是 MySQL8.0 以后加入的功能，之前需要通过定义临时变量和大量的子查询才能完成的工作，使用窗口函数实现起来更加简洁高效。同时窗口函数也是面试是的高频考点。

-- 示例1：针对 students 表的数据，计算每个同学的Score分数和整体平均分数的差值
SELECT
    *,
    AVG(Score) OVER() AS `avg`,
    Score - AVG(Score) OVER() AS `difference`
FROM students;

窗口函数的优点：

简单 : 窗口函数更易于使用。在上面的示例中，与使用聚合函数然后合并结果相比，使用窗口函数的 SQL 语句更加简单。
快速 : 这一点与上一点相关，使用窗口函数比使用替代方法要快得多。当你处理成百上千个千兆字节的数据时，这非常有用。
多功能性 : 最重要的是，窗口函数具有多种功能，比如：添加移动平均线、添加行号和滞后数据等等。

8.窗口函数-基本用法:

(1)OVER()关键字：

# 基础语法
<window function> OVER(...)

<window function> 表示使用的窗口函数，窗口函数可以使用之前已经学过的聚合函数，比如COUNT()、SUM()、AVG()等，也可以是其他函数，比如 ranking 排序函数等。
OVER(...)的作用就是设置每行数据关联的窗口数据范围，OVER()时，每行关联的数据范围都是整张表的数据。

(2)SQL举例：

SELECT
    ID,
    Name,
    Gender,
    Score,
    -- OVER()：表示每行关联的窗口数据范围都是整张表的数据
    -- AVG(Score)：表示处理每行数据时，应用 AVG 对每行关联的窗口数据中的 Score 求平均
    AVG(Score) OVER() AS `AVG_Score`
FROM students;

(3)典型应用场景：

场景1：计算每个值和整体平均值的差值

-- 需求：计算每个学生的 Score 分数和所有学生整体平均分的差值。
SELECT
    ID,
    Name,
    Gender,
    Score,
    AVG(Score) OVER() AS `AVG_Score`,
    Score - AVG(Score) OVER() AS `difference`
FROM students;

场景2：计算每个值占整体之和的占比

-- 需求：计算每个学生的Score分数占所有学生分数之和的百分比
SELECT
    ID,
    Name,
    Gender,
    Score,
    SUM(Score) OVER() AS `sum`,
    -- 计算百分比：要把 Score / SUM(Score) OVER() 的结果再乘 100
    Score / SUM(Score) OVER() * 100 AS `ratio`
FROM students;

9.窗口函数-PARTITION BY分区:

(1)PARTITION BY分区：

# 基础语法
<window function> OVER(PARTITION BY 列名, ...)

PARTITION BY 列名, ...的作用是按照指定的列对整张表的数据进行分区
分区之后，在处理每行数据时，<window function>是作用在该行数据关联的分区上，不再是整张表上

(2)SQL举例：

SELECT
    ID,
    Name,
    Gender,
    Score,
    -- PARTITION BY Gender：按照性别对整张表的数据进行分区，此处会分成2个区
    -- AVG(Score)：处理每行数据时，应用 AVG 对该行关联分区数据中的 Score 求平均
    AVG(Score) OVER(PARTITION BY Gender) AS `Avg`
FROM students;

(3)应用示例：

-- 需求：计算每个学生的 Score 分数和同性别学生平均分的差值
SELECT
    ID,
    Name,
    Gender,
    Score,
    -- PARTITION BY Gender：按照性别对整张表的数据进行分区，此处会分成2个区
    -- AVG(Score)：处理每行数据时，应用 AVG 对该行关联分区数据中的 Score 求平均
    AVG(Score) OVER(PARTITION BY Gender) AS `Avg`,
    Score - AVG(Score) OVER(PARTITION BY Gender) AS `difference`
FROM students;


-- 需求：计算每人各科分数与对应科目最高分的占比
SELECT
    name,
    course,
    score,
    -- 处理每行数据时，计算相同科目成绩的最高分
    MAX(score) OVER(PARTITION BY course) AS `max`,
    score / MAX(score) OVER(PARTITION BY course) AS `ratio`
FROM tb_score;

10.窗口函数-排序函数使用:

(1)排序函数：

# 基础语法
<ranking function> OVER (ORDER BY 列名, ...)

OVER() 中可以指定 ORDER BY 按照指定列对每一行关联的分区数据进行排序，然后使用排序函数对分区内的每行数据产生一个排名序号

(2)SQL举例：

SELECT
    name,
    course,
    score,
    -- 此处 OVER() 中没有 PARTITION BY，所以整张表就是一个分区
    -- ORDER BY score DESC：按照 score 对每个分区内的数据降序排序
    -- RANK() 窗口函数的作用是对每个分区内的每一行产生一个排名序号
    RANK() OVER(ORDER BY score DESC) as `rank`
FROM tb_score;

注意：RANK()产生的排名序号可能会不连续(当有并列情况时)

(3)不同的排序函数：

RANK()：产生的排名序号，有并列的情况出现时序号不连续
DENSE_RANK() ：产生的排序序号是连续的，有并列的情况出现时序号会重复
ROW_NUMBER() ：返回连续唯一的行号，排名序号不会重复

(4)PARTITION BY和排序函数配合使用：

-- 需求：按照不同科目，对学生的分数从高到低进行排名(要求：连续可重复)
SELECT
    name,
    course,
    score,
    -- 对每个分区内的每一行产生排名序号
    DENSE_RANK() OVER(
        -- 将整张表的数据按照科目进行分区
        PARTITION BY course
        -- 对每个分区内的数据按照score降序排列
        ORDER BY score DESC
    ) AS `dense_rank`
FROM tb_score;

(5)排序函数典型应用：

场景：获取指定排名的数据

-- 需求：获取每个科目，排名第二的学生信息
SELECT
    name,
    course,
    score
FROM (
    SELECT
        name,
        course,
        score,
        DENSE_RANK() OVER(
            PARTITION BY course
            ORDER BY score DESC
        ) AS `dense_rank`
    FROM tb_score
) s
WHERE `dense_rank` = 2;

(6)CTE公用表表达式：

CTE(公用表表达式)：Common Table Expresssion，类似于子查询，相当于一张临时表，可以在 CTE 结果的基础上，进行进一步的查询操作。

WITH some_name AS (
  --- your CTE ---
)
SELECT
  ... 
FROM some_name

需要给CTE起一个名字（上面的例子中使用了some_name)，具体的查询语句写在括号中
在括号后面，就可以通过SELECT 将CTE的结果当作一张表来使用
将CTE称为“内部查询”，其后的部分称为“外部查询”
需要先定义CTE，即在外部查询的SELECT之前定义CTE

-- 需求：获取每个科目，排名第二的学生信息
WITH ranking AS (
    SELECT
        name,
        course,
        score,
        DENSE_RANK() OVER(
            PARTITION BY course
            ORDER BY score DESC
        ) AS `dense_rank`
    FROM tb_score
)
SELECT
    name,
    course,
    score
FROM ranking
WHERE `dense_rank` = 2;

11.窗口函数-自定义 window frame:

(1)分区数据范围和window frame范围：

在使用窗口函数处理表中的每行数据时，每行数据关联的数据有两种：

①每行数据关联的分区数据

OVER()中什么都不写时，整张表默认是一个分区
OVER(PARTITION BY 列名, ...)：整张表按照指定的列被进行了分区

②每行数据关联的分区中的window frame数据

每行关联的分区window frame数据范围 <= 每行关联的分区数据范围

目前我们所学的窗口函数中，有些窗口函数作用在分区上，有些函数作用在window frame上：

聚合函数(SUM、AVG、COUNT、MAX、MIN)作用于每行关联的分区window frame数据上
排序函数(RANK、DENSE_RANK、ROW_NUMBER)作用于每行关联的分区数据上

(2)自定义 window frame 范围：

自定义 window frames 有两种方式： ROWS 和 RANGE

# 基本语法
<window function> OVER (
  PARTITION BY 列名, ...
  ORDER BY 列名, ...
  [ROWS|RANGE] BETWEEN 上限 AND 下限
)

PARTITION BY 列名, ...：按照指定的列，对整张表的数据进行分区
ORDER BY 列名, ...：按照指定的列，对每个分区内的数据进行排序
[ROWS|RANGE] BETWEEN 上限 AND 下限：在分区数据排序的基础上，设置每行关联的分区window frame范围

(3)上限和下限的设置：

UNBOUNDED PRECEDING：对上限无限制
PRECEDING：当前行之前的 n 行（ n 表示具体数字如：5 PRECEDING ）
CURRENT ROW：仅当前行
FOLLOWING：当前行之后的 n 行（ n 表示具体数字如：5 FOLLOWING ）
UNBOUNDED FOLLOWING：对下限无限制
注意：上限需要在下限之前，比如：ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING 是错误的

-- 需求：计算截止到每个月的累计销量。1月：1月销量，2月：1月销量+2月销量，3月：1月销量+2月销量+3月销量，依次类推
SELECT
    month,
    sales,
    SUM(sales) OVER(
        # 按照 month 对每个分区(注：此处就一个分区->整张表)数据进行排序
        ORDER BY month
        # 指定每行关联分区的 window frame 范围
        # UNBOUNDED PRECEDING：上限不限制
        # CURRENT ROW：当前行
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS `running_total`
FROM tb_sales;

(4)window frame定义的简略写法：

自定义 window frame 的边界时，如果使用了CURRENT ROW作为上边界或者下边界，可以使用如下简略写法：

ROWS UNBOUNDED PRECEDING 等价于 BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
ROWS n PRECEDING 等价于 BETWEEN n PRECEDING AND CURRENT ROW
ROWS CURRENT ROW 等价于 BETWEEN CURRENT ROW AND CURRENT ROW
注意，简略写法不适合FOLLOWING的情况

(5)ROWS和RANGE的区别：

ROWS和RANGE关键字，都可以用来自定义 windowframe 范围：

ROWS BETWEEN 上限 AND 下限
RANGE BETWEEN 上限 AND 下限

但两者区别如下：

ROWS是根据分区数据排序之后，每一行的 row_number(ROW_NUMBER) 确定每行关联的 window frame 范围的

CURRENT ROW：仅代表当前行

# 假设某一行数据的 row_number 为5，ROWS自定义window frame如下：
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
# 则这一行关联的window frame是：5-2 <= row_number <= 5+2 的数据

RANGE是根据分区数据排序之后，每一行的排序列(ORDER BY COLUMN)的值确定每行关联的 window frame 范围的

CURRENT ROW: 代表和当前行排序列的值相同的所有行

# 假设某一行排序列的值为5，RNAGE自定义window frame如下：
RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING
# 则这一行关联的window frame是：5-2 <= 排序列的值 <= 5+2 的数据

(6)默认的window frame：

在 OVER 中只要添加了 ORDER BY，在没有写ROWS或RANGE的情况下，会有一个默认的 window frame范围：

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- 需求：计算截止到每个月的累计销量。1月：1月销量，2月：1月销量+2月销量，3月：1月销量+2月销量+3月销量，依次类推
SELECT
    month,
    sales,
    DENSE_RANK() OVER(
        ORDER BY month
    ) AS `dense_rank`,
    SUM(sales) OVER(
        # 按照 month 对每个分区(注：此处就一个分区->整张表)数据进行排序
        ORDER BY month
        # OVER 中添加了 ORDER BY 之后，默认的 window frame 范围
        # RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS `running_total`
FROM tb_sales;