环境安装 docker
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:
数据库操作
# 进入容器(如果不是 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;
数据类型了解
字段约束
unsigned
无符号的整数(正数)auto_increment
自增(必须是主键才能自增)not null
这一列的值不能为 nulldefault
默认值(如果插入/修改时字段值为空就采用默认值)unique
唯一索引primary key
主键foreign key
外键
数据表操作
创建表
-- 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
);
删除表
-- drop table 表名
drop table user;
修改表结构
- 添加字段
-- alter table 表名 add 要添加的字段名 添加字段的属性
-- 向 user 表中添加 一个 height 字段
alter table user add height int unsigned not null;
- 删除字段
-- alter table 表名 drop 字段名
-- 删除 user 表中的 height 字段
alter table user drop height;
- 修改字段(索引不会丢失)
-- 修改字段属性: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;
- 修改表名称
-- rename table 原表名 to 新表名;
rename user to users;
查看表
-- 查看表结构, desc 表名
desc user;
-- 查看当前数据库中所有的数据表的名称
show tables;
-- 查看数据表的创建语句
show create table user;
数据表记录操作
添加数据
-- 添加一条数据: 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);
删除数据
-- 删除数据(自增属性不会重置): delete from 表名 where 条件
-- 删除数据是须写明 `where` 条件,不然会删除表中所有的数据
delete form user where id=1;
-- 清空数据(自增属性会重置): truncate 表名
truncate user;
修改数据
-- 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
查询所有数据
-- select * from 表名
select * from user;
查询指定字段
-- select 字段1,字段2..字段 from 表名
select name,age from user;
-- 查询指定字段并去重: select distinct 字段 from 表名
-- 查询user表中所有不重复的名字
select distinct name from user;
带条件的查询 where
-- 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
-- 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
-- select 分组字段 from 表名 group by 分组字段;
-- 根据性别把所有数据分为两组,并查看每组有多少数据
select sex,count(*) from user group by sex;
-- 根据性别把所有数据分为两组,并查看每组有多少数据
-- 一般所有分组字段(group by 后的字段)也必须写在 select 后面
2.聚合函数 所有的聚合函数都是对分组之后的数据进行操作的
-- 最大值: max()
-- 最小值: min()
-- 平均值: avg()
-- 求总和: sum()
数据排序 order by
-- 对查询后的数据进行排序
-- select * 字段列表 from 表名 order by 字段1,字段2 [desc];
-- 根据 age 字段升序排序,默认升序
select * from user order by age;
-- 根据 age 字段降序排序后,然后根据id字段升序排序
select * from user order by age desc,id;
分页
-- select * from 表名 limit 从第n条数据开始显示,显示m条数据
-- 从user表中查询数据,从第5条开始显示,显示5条
select * from user limit 5,5;
-- 从user表中查询数据,从第0条开始显示是,0可以不写
select * from user limit 5;
数据对应关系 & 联表查询
多表查询时, 多个表之间必须存在某种数据对应关系
数据对应关系
这是个比较抽象的概念, 主要有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 学会表
id | name |
---|---|
1001 | tom |
1002 | jack |
1003 | jason |
results 成绩表
student_id | score | score_name |
---|---|---|
1001 | tom | Math |
1002 | jack | History |
1005 | alex | History |
内链接
-- 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;
左链接
-- 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
右链接
-- 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 直接拼接字符串并且执行, 那么就会导致:
// 看起来好像没有什么问题
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 服务执行
- 预处理是: 先将语句的结构发过去, 需要改变的参数用 ? 做占位符, 然后将参数发过去执行
-- 定义预处理语句: 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...等字符集...如果一个个去修改就太难...
查看字符集
-- 参看数据库字符集
show create database db_name;
-- 参看数据表字符集
show create table table_name;
-- 参看表字段字符集
show full columns from `table_name`;
修改字符集
-- 修改数据库字符集
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 也将失效。
比如银行转账, 操作应该是这样的
- 修改账户表 A 用户的余额, -100
- 修改账户表 B 用户的余额, +100
如果两个 SQL 语句都执行成功才成功, 如果执行失败就是失败
用文字说或许不太清晰, 用代码吧
try {
executeQuery(sql1);
executeQuery(sql2);
commit(); // 成功了就一起执行
} catch (e) {
rollback(); // 如果有失败, 就一起不执行
}
查看引擎
为什么要查看引擎? 因为只有 InnoDB 才支持事务, MyISAM 不支持事务
-- 查看所有支持的引擎, 看是否支持 InnoDB
show engines;
-- 将表修改为 InnoDB
alter table your_table_name engine=innodb;
什么是事物处理?
事物主要是针对数据所有 写
的操作(delete
update
insert
)
默认情况下, MySQL 是自动提交的, 发送一条 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 后, 再到另外一个客户端来查看数据是否已经插入并修改了
事物提交模式
- 自动提交:Mysql 的提交默认是自动提交, 即发送一条执行一条
- 事物提交:事务单独开启, 手动执行提交
-- 手动开启一个事务
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
不可重复读的和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表
-- 查询当前 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% 隔离,可避免脏读、不可重复读、幻读的发生。花费最高代价但最可靠的事务隔离级别 |
锁机制
要使用锁定机制, 需要两个客户端链接:
-- 客户端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 的操作, 卡住的命令行就执行完了
行锁 & 表锁
- 行锁: 锁定某一行, 当前表的其他记录行可写
- 表锁: 锁定整个表, 任何一行都不能写
索引对锁的影响
- 索引对锁的影响: 如果被修改的字段没有索引, 那么整个表都会被锁定
-- 客户端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 的操作, 卡住的命令行就执行完了
查询范围对锁的影响
-- 客户端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)
悲观锁
-- 客户端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
字段名 | 数据类型 | 字段说明 |
---|---|---|
id | int | 自增主键 |
name | varchar | 商品名称 |
stock | int | 商品数量 |
version | int | 乐观锁版本号 |
商品表初始数据:id=1, name="iPhone 15",stock=10,version=1 商品表业务逻辑: 当一个用户下单购买 1 件商品时,程序需要做这些步骤:
-- 假设现在有两个客户端同时操作:
-- 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 |
悲观锁的优缺点:
- 需要在数据库多加一个字段, 可能是
version
也可以使用时间戳updated_at
- 客户端必须检查SQL执行后受影响的记录数(
affected_rows
)是否为 0, 然后重试 - 优点是: 实现相对简单, 可减少死锁发生概率, 提高读操作的并发性能
读锁
不建议直接锁定整个表
-- 锁定整个表,只能读,不能写
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;
写锁
不建议直接锁定整个表
-- 锁定整个表,只能写,不能读
lock table users write;
-- InnoDB 既可读, 也可写, MYISAM 只能写, 不能读
insert into users(name) values('test');
select * from users;
unlock tables;
账号管理
添加用户
# 链接数据库
mysql -u root -h localhost -P 3306 -p
# 添加用 demo 并设置密码为 demo123456
create user demo identified by 'demo123456';
# 强制刷新
flush privileges;
修改用户密码
# 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;
设置权限
-- 查看用户权限
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@'%';
数据导入/导出
导出(备份)
# 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
导入(恢复)
# 导入 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