相关推荐recommended
MySQL 学习记录 1
作者:mmseoamin日期:2024-02-22

原文:https://blog.iyatt.com/?p=12631

1 前言

去年年初报考 3 月的计算机二级(C 语言)【https://blog.iyatt.com/?p=9266 】考过了,这次打算报考 3 月的计算机三级(数据库)。数据库这一块,很久之前用过 SQLite,基本没怎么深入学习。准备现学 MySQL,主要是面向应用目的,顺带后续结合开发。三级考试似乎是用微软的 SQL Server,这个问题不大,很多东西都是相通的,MySQL 熟练使用,其它估计也不是问题。

2 环境

  • MySQL 社区版 8.0.35
  • IDEA 2023:之前学 Kotlin 和 Android 开发的时候安装的,里面也有内置 DataGrip,用来图形化操作数据库很方便,也支持 SQL 语句提示,作为辅助工具。(https://blog.iyatt.com/?p=12618)

    注:

    • 数据库中的关键字用大写或者小写都行,但是一般应该是用大写比较规范,读代码的时候一眼明了。至于自己命名部分的,像数据库名、表名等等,在 Windows 下大小写等同,Linux 下默认是会区分大小写的。MySQL 实际应用中应该还是在 Linux 服务器上为主,开始学习的时候还是规范大小写比较好,形成习惯。
    • SQL 语句结束使用分号。
    • 下面示例用法中使用中括号的语句代表可选。

      3 数据类型

      3.1 字符串类型

      \begin{array}{|l|l|}
      \hline
      类型 & 描述 \
      \hline
      CHAR & 纯文本字符串,字符串长度是固定的。当实际字段内容小于定义的长度时,MySQL 会用空白空白符好补足。 \
      \hline
      VARCHAR & 纯文本字符串,字符串长度是可变的。\
      \hline
      BINARY & 二进制字符串,字符串长度是固定的。 \
      \hline
      VARBINARY & 二进制字符串,字符串长度是可变的。\
      \hline
      TINYTEXT & 二进制字符串,最大为 255 个字节。\
      \hline
      TEXT & 二进制字符串,最大为 65K。\
      \hline
      MEDIUMTEXT & 二进制字符串,最大为 16M。 \
      \hline
      LONGTEXT & 二进制字符串,最大为 4G。\
      \hline
      ENUM & 枚举;每个列值可以分配一个 ENUM 成员。 \
      \hline
      SET & 集合;每个列值可以分配零个或多个 SET 成员。 \
      \hline
      \end{array}
      

      3.2 数字类型

      \begin{array}{|l|l|}
      \hline
      类型 & 描述 \
      \hline
      TINYINT & 一个非常小的整数,最大为 1 个字节。\
      \hline
      SMALLINT & 一个小整数,最大为 2 个字节。\
      \hline
      MEDIUMINT & 一个中等大小的整数,最大为 3 个字节。\
      \hline
      INT & 标准整数,最大为 4 个字节。\
      \hline
      BIGINT & 一个大整数,最大为 8 个字节。\
      \hline
      DECIMAL & 一个定点数。\
      \hline
      FLOAT & 单精度浮点数,最大为 4 个字节。\
      \hline
      DOUBLE & 双精度浮点数,最大为 8 个字节。\
      \hline
      BIT & 按位存储。\
      \hline
      \end{array}
      

      3.3 布尔类型

      MySQL 没有内置的布尔类型,但是有 BOOLEAN 和 BOOL 关键字,内部当做 TINYINT 类型处理,TRUE 对应 1,FALSE 对应 0。

      3.4 日期和时间类型

      \begin{array}{|l|l|}
      \hline
      类型 & 描述 \
      \hline
      DATE & CCYY-MM-DD 格式的日期值 \
      \hline
      TIME & hh:mm:ss 格式的时间值 \
      \hline
      DATETIME & CCYY-MM-DD hh:mm:ss 格式的日期和时间值 \
      \hline
      TIMESTAMP & CCYY-MM-DD hh:mm:ss 格式的时间戳值 \
      \hline
      YEAR & CCYY 或 YY 格式的年份值 \
      \hline
      \end{array}
      

      3.5 二进制类型

      \begin{array}{|l|l|}
      \hline
      类型 & 描述 \
      \hline
      TINYBLOB & 最大为 255 个字节。\
      \hline
      BLOB & 最大为 65K。\
      \hline
      MEDIUMBLOB & 最大为 16M。\
      \hline
      LONGBLOB & 最大为 4G。\
      \hline
      \end{array}
      

      3.6 空间数据类型

      \begin{array}{|l|l|}
      \hline
      类型 & 描述 \
      \hline
      GEOMETRY & 任何类型的空间值 \
      \hline
      POINT & 使用横坐标和纵坐标表示的一个点 \
      \hline
      LINESTRING & 一条曲线(一个或多个 POINT 值) \
      \hline
      POLYGON & 一个多边形 \
      \hline
      GEOMETRYCOLLECTION & GEOMETRY 值的集合 \
      \hline
      MULTILINESTRING & LINESTRING 值的集合 \
      \hline
      MULTIPOINT & POINT 值的集合 \
      \hline
      MULTIPOLYGON & POLYGON 值的集合 \
      \hline
      \end{array}
      

      3.7 JSON 类型

      MySQL 从 5.7.8 开始支持 JSON 数据类型。

      4 符号

      4.1 比较运算符

      \begin{array}{|l|l|}
      \hline
      符号 & 描述 \
      \hline
      \gt & 大于 \
      \hline
      \gt= & 大于等于 \
      \hline
      \lt & 小于 \
      \hline
      \lt= & 小于等于 \
      \hline
      = & 等于 \
      \hline
      \lt\gt 或 != & 不等于 \
      \hline
      BETWEEN ... AND ... & 在某个范围之内(含最小和最大值) \
      \hline
      IN\ (...) & 在列表中的值(多选一)\
      \hline
      LIKE\ 占位符 & 模糊匹配(\_匹配单个字符,\% 匹配任意多个字符)\
      \hline
      IS NULL & 为空 \
      \hline
      \end{array}
      

      4.2 逻辑运算符

      \begin{array}{|l|l|}
      \hline
      符号 & 描述 \
      \hline
      AND 或 \&\& & 和,多个条件同时成立 \
      \hline
      OR 或 || & 或,任意一个条件成立 \
      \hline
      NOT 或 | & 否 \
      \hline
      \end{array}
      

      5 DDL

      Data Definition Language,数据定义语言

      5.1 数据库操作

      查询所有数据库

      SHOW DATABASES;
      

      MySQL 学习记录 1,file,第1张

      创建数据库

      CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]
      

      MySQL 学习记录 1,file,第2张

      使用数据库

      USE 数据库名
      

      MySQL 学习记录 1,file,第3张

      查询当前使用的数据库

      SELECT DATABASE();
      

      MySQL 学习记录 1,file,第4张

      5.2 表操作

      表操作前要先通过 USE 指定使用的数据库

      创建表

      CREATE TABLE 表名 (
      字段1 字段1的类型 [COMMENT 字段1的注释],
      字段2 字段2的类型 [COMMENT 字段2的注释],
      字段3 字段3的类型 [COMMENT 字段3的注释],
      ......
      );
      

      MySQL 学习记录 1,file,第5张

      查看当前数据库中的所有表

      SHOW TABLES;
      

      MySQL 学习记录 1,file,第6张

      查看表结构

      DESC 表名
      

      MySQL 学习记录 1,file,第7张

      查看创建表的语句

      SHOW CREATE TABLE 表名
      

      MySQL 学习记录 1,file,第8张

      表添加字段

      ALTER TABLE 表名 ADD 字段 类型 [COMMENT 注释] [约束]
      

      MySQL 学习记录 1,file,第9张

      表修改字段类型

      ALTER TABLE 表名 MODIFY 字段 新类型 [COMMENT 注释] [约束]
      

      MySQL 学习记录 1,file,第10张

      表修改字段名和字段类型

      ALTER TABLE 表名 CHANGE 旧字段 新字段 新类型 [COMMENT 注释] [约束
      

      MySQL 学习记录 1,file,第11张

      表删除字段

      ALTER TABLE DROP 表名
      

      MySQL 学习记录 1,file,第12张

      修改表名

      ALTER TABLE 表名 RENAME TO 新表名
      

      MySQL 学习记录 1,file,第13张

      MySQL 学习记录 1,file,第14张

      删除表

      DROP TABLE [IF EXISTS] 表名
      

      MySQL 学习记录 1,file,第15张

      删除表中的所有数据但是保留结构

      TRUNCATE TABLE 表名
      

      6 DML

      Data Manipulation Language,数据操作语言

      给指定字段添加数据

      INSERT INTO 表名 (字段1, 字段2,...) VALUES (值1, 值2,...)
      

      MySQL 学习记录 1,file,第16张

      给全部字段添加数据

      INSERT INTO 表名 VALUES (值1, 值2, ...);
      

      MySQL 学习记录 1,file,第17张

      MySQL 学习记录 1,file,第18张

      修改数据

      UPDATE 表名 SET 字段1=值1, 字段2=值2,... [WHERE 条件];
      

      MySQL 学习记录 1,file,第19张

      MySQL 学习记录 1,file,第20张

      删除数据

      DELETE FROM 表名 [WHERE 条件];
      

      MySQL 学习记录 1,file,第21张

      MySQL 学习记录 1,file,第22张

      7 DQL

      Data Query Language,数据查询语言

      7.1 基本查询

      # 查询指定字段
      SELECT 字段1, 字段2, ... FROM 表名;
      # 查询所有字段
      SELECT * FROM 表名;
      

      MySQL 学习记录 1,file,第23张

      条件查询

      SELECT 字段列表 FROM 表名 WHERE 条件
      

      MySQL 学习记录 1,file,第24张

      7.2 聚合函数

      常用聚合函数

      \begin{array}{|l|l|}
      \hline
      函数 & 描述 \
      \hline
      COUNT & 统计数量 \
      \hline
      MAX & 最大值 \
      \hline
      MIN & 最小值 \
      \hline
      AVG & 平均值 \
      \hline
      SUM & 求和 \
      \hline
      \end{array}
      

      使用

      SELECT 聚合函数(字段列表) FROM 表名
      

      MySQL 学习记录 1,file,第25张

      7.3 分组查询

      SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段 [HAVING 分组后的过滤条件]
      

      MySQL 学习记录 1,file,第26张

      统计男女数量

      MySQL 学习记录 1,file,第27张

      统计男女各自的平均年龄

      MySQL 学习记录 1,file,第28张

      首先用 WHERE 筛选出年龄大于 20 的,再根据城市分组,然后 HAVING 从分组中找 COUNT 计数大于 1 的,最后 SELECT 显示出对应的 city 和数量。

      MySQL 学习记录 1,file,第29张

      MySQL 学习记录 1,file,第30张

      7.4 排序查询

      SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式, 字段2 排序方式, ...
      

      ASC 升序,默认;

      DESC 降序。

      根据年龄排序(升序)

      MySQL 学习记录 1,file,第31张

      按年龄降序排序,年龄相同时会采用第二个字段身高排序(不指定默认升序)

      MySQL 学习记录 1,file,第32张

      7.5 分页查询

      SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
      

      起始索引从 0 开始,和大多数编程语言里的数组索引一样。

      下面示例是查询从索引 3 开始(第 4 个)的 3 个数据

      MySQL 学习记录 1,file,第33张

      8 DCL

      Data Control Language,数据控制语言

      8.1 用户管理

      查询用户

      USE mysql;
      SELECT * FROM user;
      

      MySQL 学习记录 1,file,第34张

      创建用户

      CREATE USER "用户名"@"主机名" IDENTIFIED BY "密码";
      

      其中 localhost 指本地主机,如果要任意主机都可访问,可以使用 %。

      MySQL 学习记录 1,file,第35张

      MySQL 学习记录 1,file,第36张

      修改用户密码

      ALTER USER "用户名"@"主机名" IDENTIFIED WITH mysql_native_password BY "新密码";
      

      MySQL 学习记录 1,file,第37张

      删除用户

      DROP USER "用户名"@"主机名";
      

      MySQL 学习记录 1,file,第38张

      8.2 权限控制

      常用权限

      \begin{array}{|l|l|}
      \hline
      权限 & 描述 \
      \hline
      ALL/ALL\ PRIVILEGES & 所有权限 \
      \hline
      SELECT & 查询数据 \
      \hline
      INSERT & 插入数据 \
      \hline
      UPDATE & 修改数据 \
      \hline
      DELETE & 删除数据 \
      \hline
      ALTER & 修改表 \
      \hline
      DROP & 删除数据库/表/试图 \
      \hline
      CREATE & 创建数据库/表 \
      \hline
      \end{array}
      

      查询权限

      SHOW GRANTS FOR "用户名"@"主机名";
      

      MySQL 学习记录 1,file,第39张

      授予权限

      GRANT 权限列表 ON 数据库名.表名 TO "用户名"@"主机名";
      

      撤销权限

      REVOKE 权限列表 ON 数据库名.表名 FROM "用户名"@"主机名";
      

      9 常用内置函数

      9.1 字符串函数

      \begin{array}{|l|l|}
      \hline
      函数 & 描述 \
      \hline
      CONCAT(S1, S2,...,Sn) & 字符串拼接 \
      \hline
      LOWER(S) & 将字符串转为小写 \
      \hline
      UPPER(S) & 将字符串转为大写 \
      \hline
      LPAD(S, n, pad) & 字符串左侧填充,用 pad 填充 S 的左侧,使总长度达到 n \
      \hline
      RPAD(S, n, pad) & 字符串又填充,用 pad 填充 S 的右侧,使总长度达到 n \
      \hline
      TRIM(S) & 去掉字符串头尾的空格 \
      \hline
      SUBSTRING(S, start, len) & 返回字符串 S 从 start 开始的 len 个长度的字符串 \
      \hline
      \end{array}
      

      MySQL 学习记录 1,file,第40张

      MySQL 学习记录 1,file,第41张

      MySQL 学习记录 1,file,第42张

      MySQL 学习记录 1,file,第43张

      MySQL 学习记录 1,file,第44张

      MySQL 学习记录 1,file,第45张

      MySQL 学习记录 1,file,第46张

      9.2 数值函数

      \begin{array}{|l|l|}
      \hline
      函数 & 描述 \
      \hline
      CEIL(x) & 向上取整 \
      \hline
      FLOOR(x) & 向下取整 \
      \hline
      MOD(x, y) & 计算 x / y 的模 \
      \hline
      RAND() & 生成 0-1 之间的随机数 \
      \hline
      ROUND(x, y) & 求 x 的四舍五入值,保留 y 位小数 \
      \hline
      \end{array}
      

      MySQL 学习记录 1,file,第47张

      MySQL 学习记录 1,file,第48张

      MySQL 学习记录 1,file,第49张

      MySQL 学习记录 1,file,第50张

      MySQL 学习记录 1,file,第51张

      9.3 日期函数

      \begin{array}{|l|l|}
      \hline
      CURDATA() & 返回当前日期 \
      \hline
      CURTIME() & 返回当前时间 \
      \hline
      NOW() & 返回当前日期和时间 \
      \hline
      YEAR(date) & 获取 date 的年份 \
      \hline
      MONTH(date) & 获取 date 的月份 \
      \hline
      DAY(date) & 获取 date 的日期 \
      \hline
      DATE\_ADD(date, INTERVAL expr type) & 返回 date 加上时间间隔 expr 后的时间值 \
      \hline
      DATEDIFF(date1, date2) & 返回 date1 和 date2 之间的天数差值 \
      \hline
      \end{array}
      

      MySQL 学习记录 1,file,第52张

      MySQL 学习记录 1,file,第53张

      MySQL 学习记录 1,file,第54张

      MySQL 学习记录 1,file,第55张

      MySQL 学习记录 1,file,第56张

      MySQL 学习记录 1,file,第57张

      MySQL 学习记录 1,file,第58张

      不得不感慨从小学开始读书至今已经过去 5974 天了

      MySQL 学习记录 1,file,第59张

      9.4 流程函数

      \begin{array}{|l|l|}
      \hline
      日期 & 函数 \
      \hline
      IF(value, t, f) & 如果 value 为 TRUE,则返回 t,否为返回 f \
      \hline
      IFNULL(value1, value2) & 如果 value1 不为空则返回 value1,否则返回 value2 \
      \hline
      CASE\ WHEN\ [val1]\ THEN\ [res1]\ ...\ ELSE\ [default]\ END & 如果 val1 为 TRUE,返回res1,...,否则返回 default 默认值 \
      \hline
      CASE\ expr\ WHEN\ [val1]\ THEN\ [res1]\ ...\ ELSE\ [default]\ END & 如果 expr 等于val1,返回 res1,...,否则返回default默认值 \
      \hline
      \end{array}
      

      MySQL 学习记录 1,file,第60张

      MySQL 学习记录 1,file,第61张

      MySQL 学习记录 1,file,第62张

      MySQL 学习记录 1,file,第63张

      MySQL 学习记录 1,file,第64张

      10 约束

      常用约束

      \begin{array}{|l|l|l|}
      \hline
      约束 & 描述 & 关键字 \
      \hline
      非空约束 & 限制字段数据不能为 NULL & NOT NULL \
      \hline
      唯一约束 & 字段的所有数据都是唯一的,不能重复 & UNIQUE \
      \hline
      主键约束 & 主键是一行数据的唯一标识,要求非空且唯一 & PRIMARY\ KEY \
      \hline
      默认约束 & 未指定字段的值采用默认值 & DEFAULT \
      \hline
      检查约束(8.0.16 以后)& 保证字段值满足某条件 & CHECK \
      \hline
      外键约束 & 让两张表的数据建立连接,保证数据的一致性和完整性 & FOREIGN\ KEY \
      \hline
      \end{array}
      

      10.1 一般约束示例

      创建一个满足下面结构要求的表

      • id:唯一标识,字段类型 INT,约束条件:主键且自动增长
      • name:姓名,字段类型 VARCHAR(10),约束条件:不为空且唯一
      • age:年龄,字段类型 TINYINT,约束条件:不小于 0 且不超过 200
      • status:身体健康状态,字段类型 CHAR(1),约束条件:不指定默认为 1
      • gender:性别,字段类型 CHAR(1),约束条件:值为男或女

        创建表

        CREATE TABLE new_user (
            id INT PRIMARY KEY AUTO_INCREMENT COMMENT "唯一标识",
            name VARCHAR(10) NOT NULL UNIQUE COMMENT "姓名",
            age TINYINT CHECK ( age >=0 && age <= 200 ),
            status CHAR(1) DEFAULT "1" COMMENT "健康状态",
            gender CHAR(1) CHECK ( gender = "男" || gender = "女" ) COMMENT "性别"
        ) COMMENT "用户表";
        

        插入数据验证:

        ID 可以不用填写,提交自动从 1 开始生成

        当 name 重复时,提交报错

        MySQL 学习记录 1,file,第65张

        当年龄超出约束范围,提交报错

        MySQL 学习记录 1,file,第66张

        status 不填写,提交默认为 1

        当性别填写非男非女时,提交报错

        MySQL 学习记录 1,file,第67张

        10.2 外键约束示例

        10.2.1 创建表时添加外键

        CREATE TABLE 表名 (
        	字段名 类型,
        	...
        	[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
        );
        

        这里先创建一个父表

        CREATE TABLE classes (
                                 id TINYINT COMMENT "班级标识" PRIMARY KEY , # 主键约束
                                 name CHAR(2) COMMENT "班名"
        ) COMMENT "班级表";
        

        父表插入内容

        INSERT INTO classes VALUES
                                (1, "1班"),
                                (2, "2班"),
                                (3, "3班");
        

        MySQL 学习记录 1,file,第68张

        创建一个关联到父表的子表

        CREATE TABLE students (
            name VARCHAR(10) COMMENT "姓名",
            age TINYINT COMMENT "年龄",
            class_id TINYINT COMMENT "班级标识",
            CONSTRAINT fk_students_classes_id FOREIGN Key (class_id) REFERENCES classes(id)
        ) COMMENT "学生表";
        

        子表插入数据

        INSERT INTO students VALUES
                                 ("小强", 19, 1),
                                 ("小红", 20, 2),
                                 ("小张", 20, 3),
                                 ("小军", 18, 2);
        

        MySQL 学习记录 1,file,第69张

        如果尝试删除父表中的行数据就会提示不能操作

        MySQL 学习记录 1,file,第70张

        10.2.2 现有表添加外键

        ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);
        

        假如是已经创建了一张表

        CREATE TABLE stu (
            name VARCHAR(10) COMMENT "名字",
            class_id TINYINT COMMENT "班级标识"
        ) COMMENT "学生表";
        

        子表插入数据

        INSERT INTO stu VALUES
                            ("小明", 1),
                            ("小红", 2),
                            ("小强", 3);
        

        MySQL 学习记录 1,file,第71张

        后期添加外键约束

        ALTER TABLE stu ADD CONSTRAINT fk_stu_class_id FOREIGN KEY (class_id) REFERENCES classes(id);
        

        MySQL 学习记录 1,file,第72张

        10.2.3 删除外键

        ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
        

        MySQL 学习记录 1,file,第73张

        外键名称可以通过查看表的创建语句看到

        SHOW CREATE TABLE 表名
        

        MySQL 学习记录 1,file,第74张

        MySQL 学习记录 1,file,第75张

        10.2.4 外键约束行为

        \begin{array}{|l|l|}
        \hline
        行为 & 描述 \
        \hline
        NO ACTION & 父表中删除或更新记录时,首先检查该记录是否有对应外键,有则不允许删除或更新 \
        \hline
        RESTRICT & 作用同上,只是 RESTRICT 是在操作发生前就起作用,而 NO ACTION 是在删除或更新操作触发时才起作用,可以看做延迟检查。默认行为。 \
        \hline
        CASCADE & 父表中删除或更新记录时,首先检查记录是否有对应外键,如果有,则也删除或更新外键在子表中的记录 \
        \hline
        SET NULL & 父表中删除记录时,首先检查记录是否有对应外键,有则设置子表中该外键为 NULL(需要改外键允许取 NULL) \
        \hline
        \end{array}
        

        指定外键约束行为

        ```mysql
        CREATE TABLE 表名 (
        	字段名 类型,
        	...
        	[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名) ON UPDATE 更新时行为 ON DELETE 删除时行为;
        );
        
        ```mysql
        ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE 更新时行为 ON DELETE 删除时行为;
        

        注:如果已有外键约束,要修改行为,需要先删除原有的外键约束,然后重新建立外键时指定行为。

        示例:

        创建一个父表

        CREATE TABLE classes (
                                 id TINYINT COMMENT "班级标识" PRIMARY KEY , # 主键约束
                                 name CHAR(2) COMMENT "班名"
        ) COMMENT "班级表";
        

        父表插入内容

        INSERT INTO classes VALUES
                                (1, "1班"),
                                (2, "2班"),
                                (3, "3班");
        

        创建一个子表,外键约束行为都是 CASCADE

        CREATE TABLE students (
                                  name VARCHAR(10) COMMENT "姓名",
                                  age TINYINT COMMENT "年龄",
                                  class_id TINYINT COMMENT "班级标识",
                                  CONSTRAINT fk_students_classes_id FOREIGN Key (class_id) REFERENCES classes(id) ON UPDATE CASCADE ON DELETE CASCADE
        ) COMMENT "学生表";
        

        子表插入数据

        INSERT INTO students VALUES
                                 ("小强", 19, 1),
                                 ("小红", 20, 2),
                                 ("小张", 20, 3),
                                 ("小军", 18, 2);
        

        修改父表

        MySQL 学习记录 1,file,第76张

        子表同步更新了 id

        MySQL 学习记录 1,file,第77张

        删除父表数据

        MySQL 学习记录 1,file,第78张

        子表同步删除了

        MySQL 学习记录 1,file,第79张

        如果设置的外键行为是 SET NULL,则修改父表或删除子表的某行元素后,子表中对应的外键会改为 NULL。

        MySQL 学习记录 1,file,第80张

        11 多表查询

        11.1 关系

        11.1.1 一对一

        比如学生可以有学生信息,也有社会信息,分别建立一张表,可以通过身份证号建立外键关联起来

        创建一张学生信息表

        CREATE TABLE student (
            id CHAR(1) COMMENT "身份证号",
            name VARCHAR(10) COMMENT "姓名",
            name_of_school VARCHAR(36) COMMENT "学校名"
        ) COMMENT "学生信息";
        

        插入数据

        INSERT INTO student VALUES
                                ("1", "小强", "A school"),
                                ("2", "小红", "B school"),
                                ("3", "小张", "C school");
        

        MySQL 学习记录 1,file,第81张

        创建一张社会信息表

        CREATE TABLE person (
            id CHAR(1) COMMENT "身份证号" PRIMARY KEY,
            name VARCHAR(10) COMMENT "姓名",
            age TINYINT COMMENT "年龄",
            address VARCHAR(128) COMMENT "地址"
        ) COMMENT "社会信息";
        

        插入数据

        INSERT INTO person VALUES
                                ("1", "小强", 20, "AAA"),
                                ("2", "小红", 19, "BBB"),
                                ("3", "小张", 20, "CCC");
        

        MySQL 学习记录 1,file,第82张

        建立外键

        ALTER TABLE student
            ADD CONSTRAINT fk_student_person_id
            FOREIGN KEY (id)
            REFERENCES person (id);
        

        查询

        MySQL 学习记录 1,file,第83张

        11.1.2 一对多

        一个学生只属于一个班,一个班里有多个学生。

        创建一个学生表

        CREATE TABLE student_class (
            name CHAR(2) COMMENT "名字",
            class_id CHAR(1) COMMENT "所属班级代号"
        ) COMMENT "学生-班级表";
        

        插入数据

        INSERT INTO student_class VALUES 
                                      ("小强", "1"),
                                      ("小张", "2"),
                                      ("小红", "3"),
                                      ("小刚", NULL);
        

        MySQL 学习记录 1,file,第84张

        创建班级表

        CREATE TABLE classes (
            id CHAR(1) COMMENT "班级代号" PRIMARY KEY,
            name CHAR(3) COMMENT "班级名称"
        ) COMMENT "班级表";
        

        插入数据

        INSERT INTO classes VALUES 
                                ("1", "火箭班"),
                                ("2", "实验班"),
                                ("3", "平行班");
        

        MySQL 学习记录 1,file,第85张

        建立外键

        ALTER TABLE student_class
            ADD CONSTRAINT fk_student_class_id
            FOREIGN KEY (class_id)
            REFERENCES classes (id);
        

        11.1.3 多对多

        一个学生可以上多门课,每门课可以有多个学生上,这就是一种多对多的关系。

        首先创建一个学生表

        CREATE TABLE stu (
            id CHAR(1) COMMENT "学号" PRIMARY KEY,
            name CHAR(2) COMMENT "姓名"
        ) COMMENT "学生表";
        

        插入数据

        INSERT INTO stu VALUES 
                            ("1", "小强"),
                            ("2", "小张"),
                            ("3", "小红");
        

        MySQL 学习记录 1,file,第86张

        再创建一个课程表

        CREATE TABLE course (
            id CHAR(1) COMMENT "课程代号" PRIMARY KEY ,
            name CHAR(2) COMMENT "课程名称"
        ) COMMENT "课程表";
        

        插入数据

        INSERT INTO course VALUES
                               ("1", "高数"),
                               ("2", "大物"),
                               ("3", "英语");
        

        MySQL 学习记录 1,file,第87张

        再创建一张表,建立外链关联两张表

        CREATE TABLE stu_course (
            stu_id CHAR(1) COMMENT "学号",
            course_id CHAR(1) COMMENT "课程代号",
            CONSTRAINT fk_stu_course_stu_id FOREIGN KEY (stu_id) REFERENCES stu(id),
            CONSTRAINT fk_stu_course_course_id FOREIGN KEY (course_id) REFERENCES course(id)
        ) COMMENT "学生课表";
        

        插入数据

        INSERT INTO stu_course VALUES
                                   ("1", "2"),
                                   ("2", "1"),
                                   ("2", "3"),
                                   ("3", "1"),
                                   ("3", "2"),
                                   ("3", "3");
        

        MySQL 学习记录 1,file,第88张

        11.2 内连接

        内连接主要是查询两张表的交集部分,示例使用上面一对多创建的表。

        隐式内连接

        SELECT 字段列表 FROM 表1, 表2 WHERE 条件
        

        MySQL 学习记录 1,file,第89张

        显式内连接

        SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件;
        

        MySQL 学习记录 1,file,第90张

        11.3 外连接

        查询其中一张表及两张表交集的部分

        左外连接(查询表1及表1和表2的交集部分),右外连接,把 LEFT 改成 RIGHT 就行,也可以把表1和表2对换,一样的效果

        SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
        

        还是使用前面一对多创建的表演示

        MySQL 学习记录 1,file,第91张

        MySQL 学习记录 1,file,第92张

        11.4 自连接

        可以是内连接也可以是外连接

        SELECT 字段列表 FROM 表A 别名1 JOIN 表A 别名2 ON 条件;
        

        这里创建一张表用于演示

        CREATE TABLE emp (
            id TINYINT COMMENT "工号",
            name VARCHAR(10) COMMENT "姓名",
            mid TINYINT COMMENT "领导的工号"
        ) COMMENT "职工表";
        

        插入数据

        INSERT INTO emp VALUES
                            (1, "AAA", NULL),
                            (2, "BBB", 1),
                            (3, "CCC", 1),
                            (4, "DDD", 2),
                            (5, "EEE", 2),
                            (6, "FFF", 3);
        

        MySQL 学习记录 1,file,第93张

        内连接

        MySQL 学习记录 1,file,第94张

        MySQL 学习记录 1,file,第95张

        外连接

        MySQL 学习记录 1,file,第96张

        左外连接查询结果中,左边罗列出所有职工,右边是对应职工的领导,没有领导的就显示 NULL

        右外连接查询结果中,右边是罗列出所有职工,左边给出对应的下属,没有下属就显示 NULL

        11.5 联合查询

        UNION [ALL]
        

        这里随便创建了两张表用于演示查询,使用 UNINO ALL 会直接把两张表的结果拼起来

        SELECT * FROM test1
        UNION ALL
        SELECT * FROM test2;
        

        MySQL 学习记录 1,file,第97张

        如果去掉 ALL,只使用 UNINO,则呈现的结果是去除重复的

        MySQL 学习记录 1,file,第98张

        11.6 子查询/嵌套查询

        11.6.1 标量子查询

        用查询到的一个结果作为条件进一步查询

        这里使用前面创建的两张表演示

        MySQL 学习记录 1,file,第99张

        首先在班级表里查询火箭班的 ID,然后再到学生表中查询具有这个 ID 的学生信息

        SELECT * FROM student_class
                 WHERE class_id = (SELECT id FROM classes WHERE name = "火箭班");
        

        MySQL 学习记录 1,file,第100张

        11.6.2 列子查询

        常用的操作符

        \begin{array}{l l}
        操作符 & 描述 \
        \hline
        IN & 在指定的集合范围内 \
        NOT IN & 不在指定的集合范围内 \
        ANY & 子查询返回列表中,有任意一个满足即可 \
        SOME & 与 ANY 等同,使用 SOME 的地方都可以使用 ANY \
        ALL & 子查询返回列表的所有值都必须满足
        \end{array}
        

        前面是先查询出一个结果,再用这个结果作为条件查询。列子查询则是查询出符合某条件的一列结果,将这一列结果作为条件进行查询。

        有下面两张表:

        MySQL 学习记录 1,file,第101张

        MySQL 学习记录 1,file,第102张

        查询班级平均成绩大于 80 的班级中的学生信息

        SELECT * FROM student WHERE class_id in (SELECT class_id
                                           FROM class
                                           WHERE average_grades > 80);
        

        MySQL 学习记录 1,file,第103张

        11.6.3 行子查询

        列子查询是同时筛选一列的多行,行子查询就是筛选一行的多列(多表头)

        在上面表上加上个人学生成绩

        MySQL 学习记录 1,file,第104张

        现在要找出在火箭班中成绩与班级平均成绩相同的

        SELECT * FROM student WHERE (grades, class_id) = (
            SELECT average_grades, class_id FROM class WHERE name = '火箭班');
        

        MySQL 学习记录 1,file,第105张

        11.6.4 表子查询

        筛选多行多列

        将学生表中年龄大于 16 的数据筛选出来,将这些数据和班级表结合起来外连接查询

        SELECT s.name, c.name FROM (SELECT * FROM student WHERE age > 16) s LEFT JOIN class c on s.class_id = c.class_id;
        

        MySQL 学习记录 1,file,第106张

        12 事务

        MySQL 中默认每次执行修改数据指令后会立即提交(默认自动事务),但是这种情景中(A向B转账,首先查询A的余额,余额足够,从A的余额扣除,再给B增加余额),可能就会出现问题(如果在A扣除余额后和在B增加余额之间的操作中出现异常导致终止),这种情形下A的余额扣了,但是B的余额没有增加。

        MySQL 中的(手动)事务则可以应对这个情况,可以将整个流程操作作为一个事务(查询A余额,扣除A余额,增加B余额),中间的操作不会最终修改原始数据,只是暂存,操作成功最后提交修改就行,操作失败放弃暂存的操作,原始数据不修改(回滚)。

        创建用于测试的表

        CREATE TABLE account(
            name varchar(2),
            balance int
        );
        INSERT into account VALUES
                                ('小明', 2000),
                                ('小红', 2000);
        

        恢复数据

        UPDATE account set balance = 2000 WHERE name = '小明' or name = '小红';
        

        查看事务提交方式

        0 为手动,1 为自动

        SELECT @@autocommit;
        

        MySQL 学习记录 1,file,第107张

        改为手动就把这个变量值设置为 0

        SET @@autocommit = 0;
        

        提交事务

        在手动事务状态(或显式启用事务)下,执行了修改操作不会直接修改,在执行完每个指令或者一系列指定后手动执行这个指令才会提交生效

        COMMIT;
        

        回滚事务

        ROLLBACK;
        

        显式启用事务

        start transaction;
        

        转账模拟实现

        # 查询余额
        SELECT * FROM account;
        # 小明余额 -1000
        UPDATE account SET balance = balance - 1000 WHERE name = '小明';
        # 小红余额 +1000
        UPDATE account SET balance = balance + 1000 WHERE name = '小红';
        # 查询余额
        SELECT * FROM account;
        

        模拟异常

        我在小明扣除转出金额核小红增加金额中间添加了一个错误的语句

        MySQL 学习记录 1,file,第108张

        这样就出现了小明余额扣除,但是小红余额没有增加的情况

        MySQL 学习记录 1,file,第109张


        可以显式启用事务,在操作时遇到异常就执行回滚,则会恢复原数据并结束当前事务。

        事务这个设计有点像 Git,添加删除修改等操作后只是添加到暂存区,最终操作完使用 commit 才提交。只是 MySQL 默认状态每执行一次操作就会自动提交一次,设置显式事务后,则不会自动提交,中间可以回滚放弃修改。

        12.1 特性(ACID)

        • 原子性(Atomicity)

          事务是最小的操作单元,一个事务可以是一个操作或者多个操作的集合(但不可分割),其中任一组员执行失败就是整个事务的失败,而全部执行成功,事务才执行成功。

          • 一致性(Consistency)

            一致性可以体现在上面的转账案例中,小明转出了钱,余额减少了,转给小红了,小红余额对应增加了,不会出现小明余额减少了,但小红余额没有增加的情况。

            • 隔离性(Isolation)

              多个事务并发执行时互不影响,各自独立执行。

              • 持久性(Durability)

                事务被提交(或回滚)对数据的修改就是永久的,写入了硬盘中的数据库文件里了。

                12.2 并发事务存在的问题

                • 脏读

                  一个事务读到另外一个事务还没有提交的数据

                • 不可重复读

                  一个事务先后读取同一条记录,但两次读取的数据不同。一个事务有两个读取操作,第一次读取后,在第二次读取前,另外一个事务对数据进行了修改,第二次读取时数据就和第一次不一样了。

                • 幻读

                  一个事务在读取数据时,没有对应的数据,尝试插入数据的时候发现已经存在了。一个事务在读取操作的时候发现数据不存在,然后准备插入数据,在插入之前另外一个事务先执行了插入,等到原事务打算插入的时候又发现已经存在。

                  12.3 事务隔离级别

                  \begin{array}{l}
                  隔离级别 & 脏读 & 不可重复读 & 幻读 \
                  READ\ UNCOMMITTED & ✓ & ✓ & ✓ \
                  READ\ COMMITTED & ✖ & ✓ & ✓ \
                  REPEATABLE\ READ(默认) & ✖ & ✖ & ✓ \
                  SERIALIZABLE & ✖ & ✖ & ✖
                  \end{array}
                  

                  查看事务隔离级别

                  SELECT @@TRANSACTION_ISOLATION;
                  

                  设置事务隔离级别

                  SET 作用范围 TRANSACTION ISOLATION LEVEL 隔离级别;
                  

                  作用范围可以写 SESSION(只在当前客户端生效)和 GLOBAL(全局)