MySQL学习笔记

一、SQL

• SQL语句通用语法

  1. SQL语句可以单行或多行书写，以分号结尾。
  2. SQL语句可以使用空格/缩进来增强语句的可读性。
  3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
  4. 注释：
     • 单行注释：-- 注释内容 或 # 注释内容（MySQL特有）
     • 多行注释：/* 注释内容 */

• SQL分类

  • DDL：Data Definition Language，数据定义语言，用来定义数据库对象（数据库，表，字段）。
  • DML：Data Manipulation Language，数据操作语言，用来对数据库表中的数据进行增删改。
  • DQL：Data Query Language，数据查询语言，用来查询数据库表中的记录。
  • DCL：Data Control Language，数据控制语言，用来创建数据库用户、控制数据库的访问权限。

• DDL

  • DDL-数据库操作
    • 查询
      查询所有数据库

      SHOW DATABASES;
      

      查询当前数据库

      SELECT DATABASE();
      

    • 创建

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

    • 删除

      DROP DATABASE [IF EXISTS] 数据库名;
      

    • 使用

      USE 数据库名;
      

  • DDL-表操作-查询
    • 查询当前数据库所有表

      SHOW TABLES;
      

    • 查询表结构

      DESC 表名;
      

    • 查询指定表的建表语句

      SHOW CREATE TABLE 表名;
      

  • DDL-表操作-创建

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

    注意：[...]为可选参数，最后一个字段后面没有逗号。

  • DDL-数据类型
    • 数值类型
      
    • 字符串类型
      
    • 日期类型
      

• DML

  • DML-添加数据
    1. 给指定字段添加数据

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

    2. 给全字段添加数据

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

    3. 批量添加数据

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

    注意：

    • 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
    • 字符串和日期类型应该包含在引号中。
    • 插入的数据大小，应该在字段规定范围内。

  • DML-修改数据

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

    注意： 修改语句如果没有条件，则会修改整张表的所有数据。
  • DML-删除数据

    DELETE FROM 表名 [WHERE 条件];
    

    注意：

    • DELETE语句如果没有条件，则会删除整张表的所有数据。
    • DELETE语句不能删除某一个字段的值（可以使用UPDATE）。

• DQL

  • DQL-语法

    SELECT
    	字段列表
    FROM
    	表名列表
    WHERE
    	条件列表
    GROUP BY
    	分组字段列表
    HAVING
    	分组后条件列表
    ORDER BY
    	排序字段列表
    LIMIT
    	分页参数
    

  • DQL-执行顺序

    FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT
    

• DCL

  • DCL-管理用户
    1. 查询用户

       USE mysql;
       SELECT * FROM user;
       

    2. 创建用户

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

    3. 修改用户密码

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

    4. 删除用户

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

  • DCL-权限控制
    1. 查询权限

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

    2. 授予权限

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

    3. 撤销权限

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

    注意：

    • 多个权限之间，使用逗号分隔。
    • 授权时，数据库名和表名可以使用 * 进行通配，代表所有。

二、函数

• 字符串函数

  -- 字符串拼接
  CONCAT(S1, S2, ... , Sn)
  
  -- 将字符串转换为小写
  LOWER(str)
  
  --将字符串转换为大写
  UPPER(str)
  
  -- 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
  LPAD(str, n, pad)
  
  -- 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
  RPAD(str, n, pad)
  
  -- 去掉字符串头部和尾巴的空格
  TRIM(str)
  
  -- 返回从字符串str的start位置起的len长度的字符串
  SUBSTRING(str, start, len)
  

• 数值函数

  -- 向上取整
  CEIL(x)
  
  -- 向下取整
  FLOOR(x)
  
  -- 返回x/y的模
  MOD(x, y)
  
  -- 返回0~1内的随机数
  RAND()
  
  -- 求参数x的四舍五入的值，保留y位小数
  ROUND(x, y)
  

• 日期函数

  -- 返回当前日期
  CURDATE()
  
  -- 返回当前时间
  CURTIME()
  
  -- 返回当前日期和时间
  NOW()
  
  -- 获取指定date的年份
  YEAR(date)
  
  -- 获取指定date的月份
  MONTH(date)
  
  -- 获取指定date的日数
  DAY(date)
  
  -- 返回一个日期/时间值加上一个时间间隔expr后的时间值
  DATE_ADD(date, INTERVAL expr type)
  
  -- 返回起始时间date1和结束时间date2之间的天数
  DATEDIFF(date1, date2)
  

• 流程函数

  -- 如果value为true，则返回t，否则返回f
  IF(value, t, f)
  
  -- 如果value1不为空，返回value1，否则返回value2
  IFNULL(value1, value2)
  
  -- 如果val1为true，返回res1，...，否则返回default默认值
  CASE WHEN [val1] THEN [res1] ... ELSE [default] END
  
  -- 如果expr的值等于val1，返回res1，...，否则返回default默认值
  CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END
  

三、约束

• 概述

  1. 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

  2. 目的：保证数据库中数据的正确性、有效性和完整性。

  3. 分类：

     • 非空约束：NOT NULL，限制该字段的数据不能为null。
     • 唯一约束：UNIQUE，保证该字段的所有数据都是唯一、不重复的。
     • 主键约束：PRIMARY KEY，主键是一行数据的唯一标识，要求非空且唯一。
     • 默认约束：DEFAULT，保存数据时，如果未指定该字段的值，则采用默认值。
     • 检查约束（8.0.16版本之后）：CHECK，保证字段值满足某一个条件。
     • 外键约束：FOREIGN KEY，用来让两张表的数据之间建立连接，保证数据的一致性和完整性。

     注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

• 外键约束

  • 语法
    • 添加外键

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

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

    • 删除外键

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

  • 删除/更新行为
    • NO ACTION：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与RESTRICT一致）
    • RESTRICT：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与NO ACTION一致）
    • CASCADE：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则同时删除/更新外键在子表中的记录。
    • SET NULL：当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）。
    • SET DEFAULT：父表有变更时，子表将外键列设置成一个默认的值（Innodb不支持）。

    ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键名称) REFERENCES 主表名(主表字段名) ON UPDATE 行为1 ON DELETE 行为2;
    

四、多表查询

• 多表关系

  • 概述
    项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种:
    • 一对多（多对一）
    • 多对多
    • 一对一
  • 一对多（多对一）
    • 案例：部门与员工的关系
    • 关系：一个部门对应多个员工，一个员工对应一个部门
    • 实现： 在多的一方建立外键，指向一的一方的主键
  • 多对多
    • 案例：学生与课程的关系
    • 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
    • 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
  • 一对一
    • 案例：用户与用户详情的关系
    • 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
    • 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）

• 多表查询概述

  • 概述：是指从多张表中查询数据
  • 笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）
  • 多表查询分类

    • 连接查询
      内连接：相当于查询A、B交集部分数据
      外连接：

      • 左外连接：查询左表所有数据，以及两张表交集部分数据
      • 右外连接：查询右表所有数据，以及两张表交集部分数据

      自连接：当前表与自身的连接查询，自连接必须使用表别名

    • 子查询

• 连接查询-内连接
  内连接查询语法：

  • 隐式内连接

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

  • 显式内连接

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

  内连接是查询的两张表交集的部分

• 连接查询-内连接
  外连接查询语法：

  • 左外连接

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

    相当于查询表1（左表）的所有数据，包含表1和表2交集部分的数据
  • 右外连接

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

    相当于查询表2（右表）的所有数据，包含表1和表2交集部分的数据

• 连接查询-自连接
  自连接查询语法：
  sql SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;
  自连接查询，可以是内连接查询，也可以是外连接查询

• 联合查询-union, union all
  对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
  sql SELECT 字段列表 FROM 表A ... UNION [ALL] SELECT 字段列表 FROM 表B ...
  对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
  union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。

• 子查询

  • 概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

    SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
    

    子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个
  • 根据子查询结果不同，分为：
    • 标量子查询（子查询结果为单个值）
    • 列子查询（子查询结果为一列）
    • 行子查询（子查询结果为一行）
    • 表子查询（子查询结果为多行多列）
  • 根据子查询位置，分为：WHERE之后、FROM之后、SELECT之后。
  • 标量子查询
    子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。
    常用操作符：=、<>、>、>=、<、<=
  • 列子查询
    子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
    常用操作符：IN、NOT IN、ANY、SOME、ALL
    • IN：在指定的集合范围之内，多选一
    • NOT IN：不在指定的集合范围之内
    • ANY：不在指定的集合范围之内
    • SOME：与ANY等同，使用SOME的地方都可以使用ANY
    • ALL：子查询返回列表的所有值都必须满足
  • 行子查询
    子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
    常用操作符:=、<>、IN、NOT IN
  • 表子查询
    子查询返回的结果是多行多列，这种子查询称为表子查询。
    常用操作符：IN

五、事务

• 事务简介
  事务：是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

• 事务四大特性ACID

  • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  • 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
  • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

• 并发事务问题

  • 脏读：一个事务读到另外一个事务还没有提交的数据。
  • 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
  • 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”。

• 事务隔离级别
  

  -- 查看事务隔离级别
  SELECT @@TRANSACTION_ISOLATION;
  
  -- 设置事务隔离级别
  SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}；