MySQL学习笔记基础

编程 · 2023-07-30 · 286 人浏览

SQL各种名词

sql_mode

即SQL模式，作用是规范SQL语句书写方式，查看命令：select @@sql_mode;。

MySQL通过设定sql_mode参数，规范比如除法运算，从而保证不会出现违背现实数学逻辑的SQL语句。

核心SQL语句

要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在SQL语句中使用一对``（着重号）引起来。

通用语法：

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

分类：

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

DDL

数据库操作

查询所有数据库：show databases;

查询当前所在数据库：select database();

创建数据库：create database if not exists test default charset utf8mb4 COLLATE utf8mb4_general_ci;

修改数据库：alter database test charset utf8mb4;

删除数据库：drop database if exists test;

使用数据库：use test;

查看数据库基本信息：show create database test;

查看DDL语句的具体指令：? data definition

表操作

查询当前数据库所有表：show tables;

查看表结构：desc user;

查看建表语句：show create table user;

创建表：

CREATE TABLE if NOT EXISTS USER(
 id INT NOT NULL AUTO_INCREMENT COMMENT '编号',
 name VARCHAR(64) COMMENT '姓名',
 gender CHAR(1) DEFAULT 'F' COMMENT '性别',
 PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COMMENT '用户表';

建表规范：

  • 表名：小写字母、不能数字开头、表名和业务有关、表名不要太长、不能使用关键字
  • 必须设置存储引擎和字符集
  • 数据类型要合适、简短、足够
  • 必须要有主键（不使用任何业务相关的字段作为主键，使用自增整数类型或全局唯一GUID类型）
  • 每列尽量设置not null，不知道填啥，设定默认值
  • 每列要有注释
  • 列名不要太长
  • 要有表注释

修改表名：alter table user rename to tb_user;

修改存储引擎：alter table user engine=innodb;，此命令也可以用来进行InnoDB表的碎片整理。

删除指定表，并重新创建该表：truncate table tb_user;

删除表：drop table if exists tb_user;

truncate语句不能回滚，而使用delete语句删除数据，可以回滚。

delete、drop、truncate三者区别：

  • delete：逐行删除，操作很慢，可以回滚。delete操作并没有真正从磁盘上删除，只是在存储层面打标记，磁盘空间不立即释放。高水位线（High-Water Mark, HWM）不会降低
  • drop：将表结构（元数据）和数据行物理层次删除
  • truncate：清空表中所有数据页，物理磁盘空间立即释放，HWM高水位线会降低

添加字段：alter table user add nickname varchar(20) comment '昵称';

修改字段数据类型：alter table 表名 modify column 字段名 数据类型;

修改字段名称：alter table user change nickname username varchar(20) comment '用户名';

change是modify的增强版，modify可以修改字段的长度和默认值，change在modify的基础上还可以修改字段的名称，即重命名字段。

删除字段：alter table user drop username;

🤔2亿行表，想要删除其中1000W，怎么做？

  • 如果2亿行表，还没有生成，建议在设计表时，采用分区表的方式（按月range），然后删除时truncate
  • 如果2亿行表已经存在，建议使用pt-archive工具进行归档表，并且删除无用数据

DML

添加数据：insert into user values (1, '张三', '男');

修改数据：update user set name='李四' where id=1;

删除数据：delete from user where id=1;

DQL

  • SELECT

    • 字段列表

  • FROM

    • 表名列表

  • WHERE

    • 条件列表

  • GROUP BY

    • 分组字段列表

  • HAVING

    • 分组后条件列表

  • ORDER BY

    • 排序字段列表

  • LIMIT

    • 分页参数

分页查询：limit 起始索引, 查询记录数。

起始索引 = (查询页码-1) * 每页显示记录数。如查询第2页，每页显示10条记录：limit 10, 10;

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

distinct只能出现在所有字段最前面，当distinct出现后，后面多个字段一定是联合去重的。

注意：null值不参与所有聚合函数运算。

查看MySQL版本信息：select version();

组内排序：
案例：找出每个工作岗位工资排名前两名：select substring_index(group_concat(empno order by sal desc), ',', 2) from emp group by job;

模糊查询like：通配符主要包括两个：一个是%代表任意多个字符；一个是下划线_代表任意一个字符。如果想让下划线变成一个普通的下划线字符，就要使用转义字符“\”：select * from student where name like '%\_%';。

DCL

查询用户：select * from user \G，使用\G把行转化成列显示，结尾就不需要;了。或者：select user,host,authentication_string from mysql.user;。

创建用户：create user '名字'@'允许登录主机网段' identified by '密码';，例如：create user 'test'@'%' identified by '123456';，%表示所有的主机网段。默认创建的用户只有一个USAGE权限，作用是只能够登录MySQL。

修改用户密码：alter user 'test'@'localhost' identified with mysql_native_password by '123';，identified with指定使用MySQL本地密码的处理方式。

删除用户：drop user 'test'@'localhost';

注意：8.0版本以前，可以通过grant命令建立用户+授权。

显示MySQL支持的系统权限列表：show privileges;

查询用户权限：show grants for 'test'@'localhost';

授予权限：

  • grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
  • grant 权限列表 on 数据库名.表名 to '用户名'@'主机名' with grant option; -- grant option是可以给别的用户授权
  • grant all privileges on 数据库名.* to 用户名'@'主机名;

撤销权限：revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

授权后必须刷新权限才能生效：flush privileges;

如果超级管理员密码忘记了，可以这样处理：

  • 先关闭数据库
  • 使用安全模式启动：mysqld_safe --skip-grant-tables --skip-networking &，--skip-grant-tables是跳过授权表，--skip-networking是跳过TCP/IP连接
  • 以安全模式进入MySQL，手动加载授权表：flush privileges;
  • 修改密码，可以使用命令关闭数据库：shutdown;
  • 重启MySQL服务

SQL解析器原理流程

MySQL体系结构：连接层、服务层、存储引擎层、系统文件层。

连接层（协议层）：有个连接池，作用是让后续的MySQL连接，可以反复用一个连接信息，以减少数据库连接创建销毁的资源开销。

MySQL客户端与服务端的通信方式是“半双工”:

  • 全双工：能同时发送和接收数据，例如打电话
  • 半双工：指的某一时刻，要么发送数据，要么接收数据，不能同时
  • 单工：只能发送数据或只能接收数据

服务层包括：SQL接口、解析器、查询优化器、缓存。

  • 检测SQL正确性，看是否符合DDL、DML等规则
  • 针对不同SQL分类，分发给不同底层模块去执行
  • 比如接收到的是select语句，会先去cache中寻找缓存
  • SQL解析流程，对SQL语句进行解析
  • 准备SQL执行计划
  • 执行SQL计划，查询数据
  • 数据读取到之后，会添加到缓存中，便于下次加速查找

运算符

安全等于运算符

安全等于运算符（<=>）与等于运算符（=）的作用是相似的， 唯一的区别是‘<=>’可以用来对NULL进行判断。在两个操作数均为NULL时，其返回值为1；当一个操作数为NULL时，其返回值为0。

+-----------+-------------+---------------+
| 1 <=> '1' | '' <=> null | null <=> null |
+-----------+-------------+---------------+
|         1 |           0 |             1 |
+-----------+-------------+---------------+

非符号类型运算符

select least(1,2,66,3), greatest(1,2,66,3);
select 'start' regexp '^s', 'start' regexp 't$';

约束

  • 非空约束：not null
  • 检查约束：check
  • 唯一性约束：unique
  • 主键约束：primary key
  • 外键约束：foreign key

所有约束都存储在一个系统表当中：information_schema.table_constraints。

检查约束

create table t_stu(
  age int,
  check(age > 18)
);

唯一性约束

唯一性的字段值是可以为NULL的，但不能重复。使用表级约束可以为多个字段添加联合唯一：

create table t_stu(
  no int,
  name varchar(255),
  email varchar(255),
  unique(name, email)
);

也可以给约束起名字，将来可通过约束名来删除约束：constraint t_stu_name_email_unique unique(name, email)

外键约束

a表如果引用b表中的数据，可以把b表叫做父表，把a表叫做子表：

  • 创建表时，先创建父表，再创建子表
  • 插入数据时，先插入父表，在插入子表
  • 删除数据时，先删除子表，再删除父表
  • 删除表时，先删除子表，再删除父表

添加外键：alter table user add constraint fk_user_sex foreign key(gender) references sex(gender);

删除外键：alter table user drop foreign key fk_user_sex;

删除/更新行为：

  • 级联删除/更新：alter table user add constraint fk_user_sex foreign key (gender) references sex (gender) on update cascade on delete cascade;
  • 级联置空：alter table user add constraint fk_user_sex foreign key (gender) references sex (gender) on update set null on delete set null;

多表查询

连接查询

sql-joins.webp

内连接

隐式内连接：select name from user, sex where user.gender=sex.gender;

显示内连接（inner可省略）：select name from user inner join sex on user.gender=sex.gender;

非等值连接：select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

自连接：select e.ename 员工名, l.ename 领导名 from emp e join emp l on e.mgr = l.empno;

外连接

左外连接（左连接，outer可省略）：select name from user left outer join sex on user.gender=sex.gender;

右外连接（右连接）：select name from user right join sex on user.gender=sex.gender;

MySQL不支持满外连接(full outer join全连接)，但是可以用left join union right join代替。

联合查询

mysql> select gender from user
    -> union all
    -> select gender from sex;

mysql> select gender from user
    -> union
    -> select gender from sex;

union会对查询结果合并之后进行去重，union all不会去重。

子查询

标量子查询：返回结果是单个值。

列子查询：返回结果是一列。常用操作符有：in、not in、any、some、all。

行子查询：返回结果是一行。常用操作符有：in、not in、=、<>。

select * from sex where (id,gender)=(select id,gender from user where gender='男');

表子查询：返回结果是多行多列。常用操作符有：in。

  • in的执行原理实际上是采用=和or的方式，也就是说，SQL语句comm in(NULL, 300);实际上是comm = NULL or comm = 300;，in是自动忽略NULL的
  • not in的执行原理实际上是采用!=和and的方式，也就是说，SQL语句comm not in(NULL, 300);实际上是comm != NULL and comm != 300;，not in不会自动忽略NULL，在使用not in时一定要提前过滤掉NULL

in和or的效率比拼：

  • in和or所在列有索引或者主键的话，没啥差别，执行计划和执行时间都几乎一样
  • 如果in和or所在列没有索引，性能差别就很大了。随着in或者or后面的数据量越多，in的效率不会有太大的下降，但是or会随着记录越多的话性能下降非常厉害
  • 因此在给in和or的效率下定义的时候，应该再加上一个条件，就是所在的列是否有索引或者是否是主键。如果有索引或者主键性能没啥差别，如果没有索引，性能差别不是一点点！

in和exists区别：

  • in操作符是根据指定列表中的值来判断是否满足条件，而exists操作符则是根据子查询结果是否有返回记录集来判断
  • exists操作符通常比in操作符更快，尤其是在子查询返回记录数很大的情况下。因为exists只需要判断是否存在符合条件的记录，而in操作符需要比对整个列表，因此执行效率相对较低
  • in操作符可同时匹配多个值，而exists只能匹配一组条件

事务

事务四大特性ACID：

  • 原子性（Atomicity）：不可分割的特性，要么全成功，要么全回滚
  • 一致性（Consistency）：事务发生前、中、后都应该保证数据始终是一致状态
  • 隔离性（Isolation）：多个并发事务之间相互隔离
  • 持久性（Durability）：事务一旦提交，对数据库中数据的改变是永久性的，不会因为数据库实例发生故障，导致数据失效

日志序列号LSN：保存在磁盘数据页、redo文件、buffer pool、redo buffer。MySQL每次数据库启动，都会比较磁盘数据页和Redo Log的LSN，必须要求两者LSN一致数据库才能正常启动。

WAL：Write Ahead Log，日志优先于数据页写的方式实现持久化。

脏页：内存脏页，即内存中发生了修改，没回写入到磁盘之前，把内存页称之为脏页。

CKPT：Checkpoint检查点，就是将脏页刷写到磁盘的动作。

TXID：事务号，InnoDB会为每一个事务生成一个事务号，伴随着整个事务生命周期。

双一标准：参数innodb_flush_log_at_trx_commit=0/1/2。

  • 1：在每次事务提交时，会立即刷新redo buffer到磁盘，commit才能成功
  • 0：每秒刷新redo buffer到OS Cache，再fsync到磁盘，异常宕机时，会有可能导致丢失1s内的事务
  • 2：每次事务提交，都立即刷新redo buffer到OS Cache，再每秒fsync到磁盘，异常宕机时，会有可能导致丢失1s内的事务

事务日志

包括重做日志（Redo Log）和回滚日志（Undo Log）。

Redo Log

文件位置在数据目录下的：ib_logfile0~ib_logfileN。Redo Log是用来存储MySQ在做修改类(DML)操作时数据页变化过程及LSN版本号，属于物理日志。默认两个文件存储Redo，是循环覆盖使用的。

commit时会立即写入磁盘，即日志落盘成功。当MySQL出现Crash异常宕机时，主要提供前滚功能(CSR)。

控制参数：

  • innodb_log_file_size：设置文件大小
  • innodb_log_files_in_group：设置文件个数
  • innodb_log_group_home_dir：设置存储位置

Undo Log

5.7默认文件位置在数据目录下的：ibdataN、ibtmp1。Undo Log是用来存储回滚日志的，可以理解为记录了每次操作的反操作，属于逻辑日志。功能：

  • 使用快照功能，提供InnoDB多版本并发读写
  • 通过记录的反操作，提供回滚功能

在rolback时，将数据恢复到修改之前的状态；在CSR实现的是，将redo当中记录的未提交事务进行回滚（先redo前滚，再undo回滚）。undo在生成过程中，也是会记录到redo信息中的。

查看回滚段个数：select @@innodb_rollback_segments;

事务操作

查看/设置事务提交方式

select @@autocommit;
set @@autocommit=0;

开启事务：start transaction; 或 begin;

提交事务：commit; 刷新当前事务redo buffer到磁盘，还会顺便将redo buffer中没有提交的事务日志也刷新到磁盘。此时为了区分不同状态的redo，会加一些比较特殊的标记（是否提交标记）。

回滚事务：rollback;

自动提交功能：select @@autocommit;

隐式提交：DDL、DCL、锁定语句等非DML语句时，会触发隐式提交。

隐式回滚：会话关闭、数据库宕机、事务语句执行失败。

并发事务问题

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

事务隔离级别

  • RU：读未提交
  • RC：读已提交
  • RR：可重复读，利用undo的一致性快照读
  • SR：可串行化

这里的读不是SOL层数据行的select，而是指存储引擎的读，是page的读取。

隔离级别脏读不可重复读幻读
read uncommitted✓✓✓
read committed✕✓✓
repeatable read（默认）✕✕✓
serializable✕✕✕

SR串行化事务可以规避以上问题，但不利于事务的并发。

查看当前会话隔离级别：select @@transaction_isolation;

查看全局隔离级别：select @@gobal.transaction_isolation;

设置事务隔离级别：

  • 会话级：set session transaction isolation level read committed;
  • 全局级：set global transaction isolation level read committed;

RR级别（MySQL默认隔离级别）已经可以解决99%以上的幻读，但为了更加严格还加入了GAP锁、Next-Lock锁。

  • 针对快照读（普通select语句），通过MVCC（多版本并发控制）方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好的避免了幻读问题
  • 针对当前读（select ... for update等语句），通过next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行select ... for update语句时，会加上next-key lock，如果有其他事务在 next-key lock锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好的避免了幻读问题

MySQL可重复读隔离级别并没有彻底解决幻读，只是很大程度上避免了幻读现象的发生。要避免特殊场景下发生幻读现象的话，就是尽量在开启事务之后，马上执行select ... for update这类当前读的语句，因为它会对记录加next-key lock，从而避免其他事务插入一条新记录。

MySQL
2023 - 2024 三毛笔记. All Rights Reserved.
Theme Jasmine by Kent Liao
闽公网安备35020602002927 | 萌ICP备20242345号