MySQL学习笔记基础

81次阅读
没有评论

共计 9382 个字符,预计需要花费 24 分钟才能阅读完成。

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;

获取当前登录用户:select current_user();

mysql.user 表中用户条目的优先级,基本规则如下:

  • IP 条目的优先级最高。IP 条目中没有通配符,精确的 IP 和 IP 地址段都是 IP 条目
  • 精确 IP 的优先级比 IP 地址段的优先级高
  • 对于 2 个 IP 地址段,前缀长的优先级更高。比如 172.16.121.0/24 优先级比 172.16.0.0/16 高
  • 不使用通配符的条目比使用通配符的条目优先级高
  • 对于都使用了通配符的条目,则根据第一个通配符在 host 字段中出现的位置来判断优先级。通配符出现的位置越靠前,优先级越低。比如 ‘%’ 的优先级最低,’abc%’ 的优先级比 ‘abcd%’ 低

在 MySQL 5.6 或更早版本中,使用 mysql_install_db 来初始化数据库,会创建一个用户名为空的无密码用户,这会引起一个问题:在数据库服务器本地使用一个正常账号登录数据库,会发现无法登录,报密码错误。实际上,这并不是密码问题,而是在本地登录时,使用了 ”@’localhost’ 这个条目来进行用户认证。这个问题的解决方法一般是 删除用户名为空的用户

创建用户:create user '名字'@'允许登录主机名或 IP' identified by '密码';。例如:create user 'test'@'%' identified by '123456';,百分号 “%” 匹配任意字符串,表示所有主机网段。默认创建的用户只有一个 Usage 权限,作用是只能够登录 MySQL。

也可以使用 IP 地址段来指定客户端 IP 范围,比如下面创建的 test 用户可以在 172.16 这个网段内访问数据库。

create user 'test'@'172.16.0.0/16' identified by 'somepassword';

修改用户密码: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;

授权时,要遵循 最小权限原则,给用户授予正常业务需求之外的权限会带来额外的安全风险。

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;

多表查询

连接查询

MySQL 学习笔记基础

内连接

隐式内连接: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,从而避免其他事务插入一条新记录。

正文完
post-qrcode
 0
三毛
版权声明:本站原创文章,由 三毛 于2023-07-30发表,共计9382字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
评论(没有评论)