Skip to content

环境安装 docker

yaml
services:
  mysql_server:
    image: mysql:8 # mysql:8 or mysql:5.7
    container_name: mysql_server
    restart: always
    ports:
      - 3306:3306
    environment:
      MYSQL_DATABASE: demo
      MYSQL_USER: dev
      MYSQL_PASSWORD: dev123456
      MYSQL_ROOT_PASSWORD: root123456
    volumes:
      # ./mysql_conf and ./mysql_init must be existed
      - ./mysql_conf:/etc/mysql/conf.d
      - ./mysql_init:/docker-entrypoint-initdb.d
      - mysql_data:/var/lib/mysql

volumes:
  mysql_data:

数据库操作

sh
# 进入容器(如果不是 docker 方式安装的, 不用进行这个步骤)
docker exec -it mysql_server bash

# 链接数据库
# -h 服务器地址
# -P MYSQL 服务端口号
# -u 用户名
# -p 密码
mysql -h 127.0.0.1 -P 3306 -u root -p

# 断开链接
exit;

# 查看所有数据库
show databases;

# 创建数据库
# create database 数据库名 default charset=字符集
create database test default charset=utf8;

# 使用数据库(切换到数据库)
use test;

# 删除数据库
drop database test;

数据类型了解

字段约束

  1. unsigned 无符号的整数(正数)
  2. auto_increment 自增(必须是主键才能自增)
  3. not null 这一列的值不能为 null
  4. default 默认值(如果插入/修改时字段值为空就采用默认值)
  5. unique 唯一索引
  6. primary key 主键
  7. foreign key 外键

数据表操作

创建表

sql
-- create table 表名(
-- 字段1  数据类型  字段约束,
-- 字段2  数据类型  字段约束,
-- ....
-- 字段n  数据类型  字段约束
-- );

create table user(
	id int unsigned primary key auto_increment,
  name char(16) not null,
  sex  char(1)  not null,
  age  tinyint not null
);

删除表

sql
-- drop table 表名
drop table user;

修改表结构

  1. 添加字段
sql
-- alter table 表名 add 要添加的字段名 添加字段的属性
-- 向 user 表中添加 一个 height 字段
alter table user add height int unsigned not null;
  1. 删除字段
sql
-- alter table 表名 drop 字段名
-- 删除 user 表中的 height 字段
alter table user drop height;
  1. 修改字段(索引不会丢失)
sql
-- 修改字段属性:alter table 表名 modify 字段名-字段属性;
-- 将user表中的 age 字段修改为可以为 不能为负数
alter table user modify age tinyint unsigned not null;

-- 修改字段名:alter table 表名 change 原字段名 新字段名-字段类型-字段属性;
-- 将user表 sex 字段修改为 gender
alter table user change sex gender int tinyint unsigned not null;
  1. 修改表名称
sql
-- rename table 原表名 to 新表名;
rename user to users;

查看表

sql
-- 查看表结构, desc 表名
desc user;

-- 查看当前数据库中所有的数据表的名称
show tables;

-- 查看数据表的创建语句
show create table user;

数据表记录操作

添加数据

sql
-- 添加一条数据: insert into 表名(字段1,字段2...字段n) values(值1,值2...值n);
-- 此时id是自增属性,可以不填写
insert into user(name,age) values('user1',18);

-- 添加多条数据: insert into 表名(字段1,字段2...字段n) values(值1,值2...值n),(值1,值2...值n);
-- **注** : 插入数据是,字段和值必须一一对应,插入多个数据是,括号之间必须用逗号隔开
insert into user(name,age) values('user2',20),('user3',22),('user4',24);

删除数据

sql
-- 删除数据(自增属性不会重置): delete from 表名 where 条件
-- 删除数据是须写明 `where` 条件,不然会删除表中所有的数据
delete form user where id=1;

-- 清空数据(自增属性会重置): truncate 表名
truncate user;

修改数据

sql
-- update 表名 set 字段1=值1,字段2=值2...字段n=值n where 字段=值;
-- 也是需要指定 where 语句, 否则会修改所有记录
update user set name='testUserName',age=30  where id=1;

查询数据(DQL)

查询数据的 SQL 命令是通用的, 多数关系型数据库的使用方式都是一模一样的

查询数据的关键字使用顺序(不能改):

select -> from -> where -> group by -> having -> order by -> limit

查询所有数据

sql
-- select * from 表名
select * from user;

查询指定字段

sql
-- select 字段1,字段2..字段 from 表名
select name,age from user;

-- 查询指定字段并去重: select distinct 字段 from 表名
-- 查询user表中所有不重复的名字
select distinct name from user;

带条件的查询 where

sql
-- select *|字段列表 from 表名 where 条件
select * from user where id=1; -- 查询user表中id为一的数据
select * from user where id>3; -- 查询user表中所有id大于3的数据
select * from user where id>=3;-- 查询user表中所有id大于等于3的数据
select * from user where id<5; -- 查询user表中所有id小于5的数据
select * from user where id<=5;-- 查询user表中所有id小于等于5的数据
select * from user where id in(1,3,5);              -- 查询user表中id是1或3或5的数据
select * from user where id between 10 and 20;      -- 查询user表中id在10到20之间的数字
select * from user where name is null;              -- 查询user表中所有name字段值为null的数据
select * from user where name id not null;          -- 查询user表中所有name字段值不为null的数据
select * from user where id>3 and name is not null; -- 查询user表中id大于3并且name不为空的数据
select * from user where id>3 or name is not null;  -- 查询user表中id大于3或者name不为空的数据

模糊查询 like

sql
-- select * from 表名 where 字段 like '%需要查找的内容%';
-- 查询所有user表中name字段包含 测试 的数据
select name,age from user where name like '%测试%';

-- 查询所有user表中name字段包含所有 是以任意字符开头并且以测试结尾的数据
select name,age from user where name like '_测试';

-- 查询所有user表中name字段包含所有 是以任意字符开头并且以测试开头的数据
select name,age from user where name like '测试_';

分组聚合(重在理解,没有数据,笔记不好做)

1.数据分组 group by

sql
-- select 分组字段 from 表名 group by 分组字段;
-- 根据性别把所有数据分为两组,并查看每组有多少数据
select sex,count(*) from user group by sex;

-- 根据性别把所有数据分为两组,并查看每组有多少数据
-- 一般所有分组字段(group by 后的字段)也必须写在 select 后面

2.聚合函数 所有的聚合函数都是对分组之后的数据进行操作的

sql
-- 最大值: max()
-- 最小值: min()
-- 平均值: avg()
-- 求总和: sum()

数据排序 order by

sql
-- 对查询后的数据进行排序
-- select * 字段列表 from 表名 order by 字段1,字段2 [desc];

-- 根据 age 字段升序排序,默认升序
select * from user order by age;

-- 根据 age 字段降序排序后,然后根据id字段升序排序
select * from user order by age desc,id;

分页

sql
-- select * from 表名 limit 从第n条数据开始显示,显示m条数据

-- 从user表中查询数据,从第5条开始显示,显示5条
select * from user limit 5,5;

-- 从user表中查询数据,从第0条开始显示是,0可以不写
select * from user limit 5;

数据对应关系 & 联表查询

多表查询时, 多个表之间必须存在某种数据对应关系

数据对应关系

sql
这是个比较抽象的概念, 主要有3类关系:

1. 一对一: 比如一个人只能有一个母亲
那放到数据库中就是 一个表存储当前用户信息(users)并且有一个字段存储母亲的信息(mother_id)
那么另外一个 用户母亲信息表(users_mother) 的主键就是存储在 users 表中 mother_id 的值

users:
|id|name|mother_id|
|1 |aaa |11       |
|2 |bbb |12       |

users_mother:
|id|name      |
|11|aaa_mother|
|12|bbb_mother|


2. 一对多: 比如一个人可以有多个手机
那么放到数据库中就是 一个表存储当前用户信息(users), 并且主键是不重复的值(id)
那么另外一个 用户手机信息表(users_phone) 就必须有一个字段用来存储 users 表的 id 的值

users:
|id|name|
|1 |aaaa|

users_phone:
|id |name   | owner_id |
|11 |huawei | 1        |
|12 |iphone | 1        |
|13 |xiaomi | 1        |


3. 多对多: 比如一个老师可以有多个学生, 一个学生也可以有多个老师
放到数据库中就必须要有一个中间表来存储对应关系

students:
|id|name|
|11| zs |
|22| ls |

teachers:
|id|name|
|33| ww |
|44| zl |

students_teachers: 中间表
|id| student_id | teacher_id |
|1 | 11         | 33         |
|2 | 11         | 44         |
|3 | 22         | 33         |

连表查询

students 学会表

idname
1001tom
1002jack
1003jason

results 成绩表

student_idscorescore_name
1001tomMath
1002jackHistory
1005alexHistory

内链接

sql
-- select * from 表1 join 表2 on 表1.id=表2.id where 其他条件
select * from student as s join results as r on s.id=r.student_id where r.score>60;

左链接

sql
-- left join 左边的表中的数据全部查询出来,右边的的表中符合条件的查询出来
-- select * from 表1 left join 表2 on 表1.id=表2.id where 其他条件
select * from student as s left join retults as r on s.id=r.student_id where r.score>60

右链接

sql
-- rigit join 右边的表中的数据全部查询出来,左边的的表中符合条件的查询出来
-- select * from 表1 right join 表2 on 表1.id=表2.id where 其他条件
select * from student as s right join retults as r on s.id=r.student_id where r.score>60

预处理 与 SQL 注入

什么是 SQL 注入?

简单来说就是将用户的输入拼接到 SQL 语句中,然后直接执行 SQL 语句 如下代码, 看起来好像没有什么问题? 但是如果, 不校验 username 直接拼接字符串并且执行, 那么就会导致:

js
// 看起来好像没有什么问题
let { username, password } = req.body;
let sql = `select * from users where username='${username}' and password='${password}'`;
executeQuery(sql);

// 如果 username 的内容是这样的 `abc' -- `那么就不会验证密码,
// 因为最终拼接的字符串是这样的:
// select * from users where username='abc' -- and password='xxx'
// 这行代码在 mysql 中, `--` 会被当成注释, 那么查询的条件就变成了
// 只有 `username=abc` 没有密码验证了, 相当于执行的代码变成了:
// select * from users where username='abc' 因为后面的被注释掉了

什么是预处理?

又称参数化查询, 是数据库操作中的一种安全且高效的技术, 核心思想是将SQL语句的结构与数据值分离, 它有效解决了传统SQL拼接的两个主要痛点: SQL注入安全漏洞和重复查询的性能问题

  • 执行拼接SQL字符串执行, 是一次性将所有内容发给 MySQL 服务执行
  • 预处理是: 先将语句的结构发过去, 需要改变的参数用 ? 做占位符, 然后将参数发过去执行
sql
-- 定义预处理语句: prepare 语句变量名 from 'SQL语句, 其中需要改变的用 ? 作为占位符'
prepare ppsql from 'select * from demos where username=? and password=?';

-- 定义变量
SET @username="admin";
SET @password="123456";

-- 执行语句时, 使用变量(按照顺序对应?的顺序)
EXECUTE ppsql USING @username, @password;

-- 释放结果集
DEALLOCATE PREPARE ppsql;

数据字符集

因为一些一键安装包的环境, my.ini 默认配置的字符集是 latin1 或者其他, 如果此时一旦不注意, 使用 sql 语句去创建数据库, 表 默认都是 latin1, 因为有些字符集是不能存储中文的,如果需要存储中文, 需要使用 GBK,utf8...等字符集...如果一个个去修改就太难...

查看字符集

sql
-- 参看数据库字符集
show create database db_name;

-- 参看数据表字符集
show create table table_name;

-- 参看表字段字符集
show full columns from `table_name`;

修改字符集

sql
-- 修改数据库字符集
alter database `test_db` character set 'utf8' collate 'utf8_general_ci';

-- 修改数据表字符集
alter table `test_db`.`user` character set = utf8mb4, collate = utf8mb4_bin;
alter table `table_name` convert to character set utf8 collate utf8_bin;

-- 修改数据表字段字符集
alter table `test_db`.`username`  modify column `password` varchar(30)  character set utf8mb4 collate utf8mb4_bin;

事物处理机制

事务是保证多个 SQL 操作的一致性,如果一条失败全部 SQL 也将失效。

比如银行转账, 操作应该是这样的

  1. 修改账户表 A 用户的余额, -100
  2. 修改账户表 B 用户的余额, +100

如果两个 SQL 语句都执行成功才成功, 如果执行失败就是失败

用文字说或许不太清晰, 用代码吧

js
try {
  executeQuery(sql1);
  executeQuery(sql2);
  commit(); // 成功了就一起执行
} catch (e) {
  rollback(); // 如果有失败, 就一起不执行
}

查看引擎

为什么要查看引擎? 因为只有 InnoDB 才支持事务, MyISAM 不支持事务

sql
-- 查看所有支持的引擎, 看是否支持 InnoDB
show engines;

-- 将表修改为 InnoDB
alter table your_table_name engine=innodb;

什么是事物处理?

事物主要是针对数据所有 的操作(delete update insert)

默认情况下, MySQL 是自动提交的, 发送一条 SQL(在命令行敲回车)就执行一条, 这样就无法使用事物了

sql
begin; -- 或者: start transaction;
  insert into users (`email`) values('tom@qq.com');
  update users set `email`='tom123@qq.com' where `email`='tom@qq.com';
commit; -- 或者 rollback 取消这些操作

-- 先执行 insert 语句, 然后用另外一个客户端来查看数据是否直接插入
-- 执行 commit 后, 再到另外一个客户端来查看数据是否已经插入并修改了

事物提交模式

  1. 自动提交:Mysql 的提交默认是自动提交, 即发送一条执行一条
  2. 事物提交:事务单独开启, 手动执行提交
sql
-- 手动开启一个事务
begin;
  insert into students (id,name,sex)values(1,'zs','男');
  insert into students (id,name,sex)values(2,'ls','女');
commit;

-- 全局开启事务: 所有 SQL 都使用事务操作(关闭自动提交)
set autocommit = 0;
insert into students (id,name,sex)values(3,'ww','女');
commit;

-- 开启自动提交
set autocommit = 1;

事物隔离

并发问题

当高并发访问会遇到多个事务的隔离问题,可能会出现以下:

  • 脏读:事务 A 读取了事务 B 更新的数据,然后 B 回滚操作,那么 A 读取到的数据是脏数据
  • 不可重复读:事务 A 多次读取同一数据,事务 B 在事务 A 多次读取的过程中,对数据作了更新并提交,导致事务 A 多次读取同一数据时,结果不一致
  • 幻读:系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级,但是系统管理员 B 就在这个时候插入了一条具体分数的记录,当系统管理员 A 改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读

TIP

不可重复读的和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表

sql
-- 查询当前 session 的事物隔离级别
select @@transaction_isolation;

-- 查询全局的事物隔离级别
select @@global.transaction_isolation;

-- 设置会话隔离级别,影响当前连接
set session transaction isolation level read uncommitted;

-- 设置全局隔离级别,影响所有连接
set global transaction isolation level read uncommitted;

隔离级别

事务隔离级别脏读不可重复读幻读说明
读未提交(read-uncommitted)最低的隔离级别,一个事务还没提交时,它的变更就能被别的事务看到
读已提交(read-committed)保证一个事物提交后才能被另外一个事务读取, 另外一个事务不能读取该事物未提交的数据
可重复读(repeatable-read)多次读取同一范围的数据会返回第一次查询的快照,即使其他事务对该数据做了更新修改, 事务在执行期间看到的数据前后必须是一致的
可串行化(serializable)事务 100% 隔离,可避免脏读、不可重复读、幻读的发生。花费最高代价但最可靠的事务隔离级别

锁机制

要使用锁定机制, 需要两个客户端链接:

sql
-- 客户端A:
set autocommit = 0; -- 关闭自动提交
update students set name='zhangshang' where id=2;

-- 注意此时 客户端A 还没有执行 commit, 这条数据就会被锁定
-- 此时 客户端B 无法操作这个记录, 这个命令执行后命令行会阻塞(卡住)
update students set name='lisi' where id=2;

-- 如果此时 客户端A 执行 commit, 客户端A的update语句会执行, 然后解除数据锁定
commit;

-- 这时 客户端B 就可以执行 update 的操作, 卡住的命令行就执行完了

行锁 & 表锁

  • 行锁: 锁定某一行, 当前表的其他记录行可写
  • 表锁: 锁定整个表, 任何一行都不能写

索引对锁的影响

  • 索引对锁的影响: 如果被修改的字段没有索引, 那么整个表都会被锁定
sql
-- 客户端A:
set autocommit = 0; -- 关闭自动提交
update students set name='zhangshang' where email='test@qq.com';

-- 注意此时 客户端A 还没有执行 commit, 如果 email 这个字段没有索引
-- 那么整个表都会被锁定, 此时 客户端B 无法操作这个表中的任何一条记录
-- 这个命令执行后命令行会阻塞(卡住)
update students set name='lisi' where id=3;

-- 如果此时 客户端A 执行 commit, 客户端A的update语句会执行, 然后解除数据锁定
commit;

-- 这时 客户端B 就可以执行 update 的操作, 卡住的命令行就执行完了

查询范围对锁的影响

sql
-- 客户端A:
set autocommit = 0; -- 关闭自动提交
update students set name='zhangshang' where id>1 and id<5;

-- 注意此时 客户端A 还没有执行 commit, 此时会
-- 锁定 where 后面的所有被条件限制的行
-- 此时 客户端B 无法操作 id 是 2,3,4 的这三个记录的内容
update students set name='lisi' where id=3; -- 阻塞
update students set name='lisi' where id=8; -- 不会阻塞,因为这一行没有被锁定

-- 如果此时 客户端A 执行 commit, 客户端A的update语句会执行, 然后解除数据锁定
commit;

-- 这时 客户端B 就可以执行 update 的操作, 被阻塞的命令行就执行完了(id=3)

悲观锁

sql
-- 客户端A:
set autocommit = 0; -- 关闭自动提交
select * from students where id=1 for update; -- 悲观锁

-- 注意此时 客户端A 还没有执行 commit, 此时会锁定 id=1 的记录
-- 此时 客户端B 无法操作 id 是 1 的这个记录的内容
update students set name='lisi' where id=1; -- 阻塞


-- 如果此时 客户端A 执行 commit, 客户端A的update语句会执行, 然后解除数据锁定
commit;

-- 这时 客户端B 就可以执行 update 的操作, 被阻塞的命令行就执行完了

乐观锁

乐观锁不是一种强制锁定导致阻塞的状态, 而是在更新的时候校验要更新的数据是否符合条件 经典应用场景为: 电商平台商品库存扣减

  • 商品表 pruducts
字段名数据类型字段说明
idint自增主键
namevarchar商品名称
stockint商品数量
versionint乐观锁版本号

商品表初始数据:id=1, name="iPhone 15",stock=10,version=1 商品表业务逻辑: 当一个用户下单购买 1 件商品时,程序需要做这些步骤:

sql
-- 假设现在有两个客户端同时操作:

-- 1.客户端A和客户端B查询商品当前信息(库存 stock 和 版本号 version)
SELECT id, name, stock, version FROM products WHERE id = 1;

-- 两者获取到的结果相同:
-- +----+------------+-------+---------+
-- | id | name       | stock | version |
-- +----+------------+-------+---------+
-- |  1 | iPhone 15  |    10 |       1 |
-- +----+------------+-------+---------+

-- 2.现在假设 客户端A 速度稍快点先执行, 客户端B后执行
UPDATE products
  -- 库存减1,版本号+1
  SET stock = 9, version = version + 1
  -- 关键: 使用步骤1查询到的旧版本号(1)作为更新条件
  WHERE id = 1 AND version = 1;

-- 此时: 客户端 A 先执行, 会将 version 修改为 2
-- 客户端B 在执行时, 就无法符合 where 条件的 version 字段
-- 那么此时, 客户端 B 只需要再次执行 步骤1 和 步骤2 即可修改成功

整体执行步骤如下:

操作客户端 A客户端 B数据库状态
初始状态--stock=10, version=1
执行成功(affected_rows=1)-stock=9, version=2
首次执行失败, 因为
affected_rows = 0
-(affected_rows=0)stock=9, version=2
再次执行, 这次查的
是修改后的数据
-(affected_rows=1)stock=8, version=3

悲观锁的优缺点:

  1. 需要在数据库多加一个字段, 可能是 version 也可以使用时间戳 updated_at
  2. 客户端必须检查SQL执行后受影响的记录数(affected_rows)是否为 0, 然后重试
  3. 优点是: 实现相对简单, 可减少死锁发生概率, 提高读操作的并发性能

读锁

不建议直接锁定整个表

sql
-- 锁定整个表,只能读,不能写
lock table users read;

-- 可以执行读取操作
select * from users;

-- 写入操作无法执行:
-- ERROR 1099 (HY000): Table 'users' was locked with a READ lock and can't be updated
insert into users(name) values('test');

-- 解锁
unlock tables;

写锁

不建议直接锁定整个表

sql
-- 锁定整个表,只能写,不能读
lock table users write;

-- InnoDB 既可读, 也可写, MYISAM 只能写, 不能读
insert into users(name) values('test');
select * from users;

unlock tables;

账号管理

添加用户

sh
# 链接数据库
mysql -u root -h localhost -P 3306 -p

# 添加用 demo 并设置密码为 demo123456
create user demo identified by 'demo123456';

# 强制刷新
flush privileges;

修改用户密码

sh
# 1.链接数据库
mysql -u root -h localhost -P 3306 -p

# 2. 修改 root 用户的登录密码为 123456
# 注意mysql8加密方式为: caching_sha2_password, 5.7 为 mysql_native_password

# 2.1远程链接密码
alter user 'root'@'%' identified with caching_sha2_password by '123456';

# 2.2本地链接密码
alter user 'root'@'localhost' identified with caching_sha2_password by '123456';

# 3.强制刷新权限或者重启 MySQL 服务
flush privileges;

设置权限

sql
-- 查看用户权限
show grants for 'demo'@'%';

-- 移除用户所有权限
revoke all on *.* from demo@'%';

-- 用户可以为 test_db 库中的所有表执行 select 与 insert 操作
grant select,insert on test_db.* to demo@'%';

-- 可以从任何电脑登录服务器并管理所有库和表
grant all on *.* to demo@'%';

-- 用户可以管理 test_db 库中的所有表,客户端IP以 192.168.0 开始的地址
grant all on test_db.* to demo@'192.168.0.%';

-- 用户 demo 可以对 test_db 库中的所有表执行 alter,create,drop 指令,并且IP不受限制
grant alter,create,drop on test_db.* to demo@'%';

数据导入/导出

导出(备份)

sh
# mysqldump 是系统命令,不是链接后的命令

# --no-data:仅导出结构,不导出数据
# --no-create-info:仅导出数据,不导出表结构
# --all-database: 所有数据库
# --default-character-set=utf8mb4: 指定字符集

# 导出一个/多个数据库
mysqldump -u root -p -h localhost -P 3306 test_db demo_db > backup_test_and_demo.sql

# 导出所有数据库
mysqldump -u root -p -h localhost -P 3306 --all-database > db.sql

# 导出并压缩
mysqldump -u root -p -h localhost -P 3306 --all-database | gzip > db_backkup.sql.gz

导入(恢复)

sh
# 导入 sql 文件
mysql -u root -p test_db < test_db.sql

# 解压缩并导入
gunzip < ./test_db_backup.sql.gz | mysql -u root -p -h localhost -P 3306 test_db

Released under the MIT License.