Python开发-042_mysql数据库的常见概念

1 视图

1.1 视图的作用

如果需要频繁的操作一张虚拟表，

视图就是通过查询得到一张虚拟表，然后将虚拟表保存下来，方便下次使用

# 在school数据库中 查询 老师对应课程
MariaDB [(none)]> use school;
MariaDB [school]> select tid as '教师编号',tname as '教师名',cid as '课程编号',cname as '课程名' from teacher inner join course on teacher.tid = course.teacher_id;
+--------------+-----------+--------------+-----------+
| 教师编号     | 教师名    | 课程编号     | 课程名    |
+--------------+-----------+--------------+-----------+
|            1 | 波多      |            1 | 生物      |
|            1 | 波多      |            2 | 体育      |
|            2 | 苍空      |            3 | 物理      |
|            3 | 饭岛      |            4 | 化学      |
+--------------+-----------+--------------+-----------+
4 rows in set (0.00 sec)

# 如何保存这张表(如何创建视图)
# create view 表名 as 虚拟表的查询sql语句
MariaDB [school]> create view teacher_course as select tid as '教师编号',tname as '教师名',cid as '课程编号',cname as '课程名' from teacher inner join course on teacher.tid = course.teacher_id;
Query OK, 0 rows affected (0.00 sec)

MariaDB [school]> select * from teacher_course;
+--------------+-----------+--------------+-----------+
| 教师编号     | 教师名    | 课程编号     | 课程名    |
+--------------+-----------+--------------+-----------+
|            1 | 波多      |            1 | 生物      |
|            1 | 波多      |            2 | 体育      |
|            2 | 苍空      |            3 | 物理      |
|            3 | 饭岛      |            4 | 化学      |
+--------------+-----------+--------------+-----------+
4 rows in set (0.00 sec)

1.2 视图的特点

视图本质也是表，但是在例如navicat软件中会单独进行归类

[root@localhost school]# pwd
/var/lib/mysql/school
[root@localhost school]# ls -al
total 612
drwx------.  2 mysql mysql    226 Nov 22 22:13 .
drwxr-xr-x. 12 mysql mysql    272 Nov 21 15:13 ..
-rw-rw----.  1 mysql mysql   8594 Nov 16 19:51 class.frm
-rw-rw----.  1 mysql mysql  98304 Nov 16 19:51 class.ibd
-rw-rw----.  1 mysql mysql   8632 Nov 17 11:03 course.frm
-rw-rw----.  1 mysql mysql 114688 Nov 18 20:38 course.ibd
-rw-rw----.  1 mysql mysql     61 Nov 16 17:02 db.opt
-rw-rw----.  1 mysql mysql   8668 Nov 17 11:09 score.frm
-rw-rw----.  1 mysql mysql 131072 Nov 19 03:12 score.ibd
-rw-rw----.  1 mysql mysql   8672 Nov 16 20:00 student.frm
-rw-rw----.  1 mysql mysql 114688 Nov 19 03:11 student.ibd
-rw-rw----.  1 mysql mysql   1026 Nov 22 22:13 teacher_course.frm
-rw-rw----.  1 mysql mysql   8590 Nov 16 19:54 teacher.frm
-rw-rw----.  1 mysql mysql  98304 Nov 16 19:54 teacher.ibd

在数据库文件夹中能找到teacher_course.frm视图表的身影

# 注意
1.创建视图在硬盘上只有表结构 没有表数据(数据在每次读取视图是从之前的表中提取，如果原始表修改视图也会改)
MariaDB [school]> select * from teacher;
+-----+--------+
| tid | tname  |
+-----+--------+
|   1 | 波多   |
|   2 | 苍空   |
|   3 | 饭岛   |
+-----+--------+
3 rows in set (0.00 sec)

MariaDB [school]> update teacher set tname='波多野结衣' where tid =1;
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> select * from teacher_course;
+--------------+-----------------+--------------+-----------+
| 教师编号     | 教师名          | 课程编号     | 课程名    |
+--------------+-----------------+--------------+-----------+
|            1 | 波多野结衣      |            1 | 生物      |
|            1 | 波多野结衣      |            2 | 体育      |
|            2 | 苍空            |            3 | 物理      |
|            3 | 饭岛            |            4 | 化学      |
+--------------+-----------------+--------------+-----------+
4 rows in set (0.00 sec)

2.视图只用来查询 里面的数据不能进行修改
MariaDB [school]> delete from teacher_course where '教师编号' = 1;
ERROR 1395 (HY000): Can not delete from join view 'school.teacher_course'

2 触发器

2.1 触发器的作用

在对表数据进行增、删、改等修改数据的操作下，能够自动触发功能。使用触发器可以帮助我们实现监控、日志等功能

# 基本语法结构
create trigger 触发器名字 before/after insert/update/delete on 表名 for each row begin sql语句 end

# 语法结构说明
before	在...操作之前
after	在...操作之后

insert	增加
update	修改
delete	删除

# 具体使用
## 针对触发器的名字 我们需要做到见名知意
# 案例 创建用户日志
## 创建用户表
create table user(
	uid int primary key auto_increment,
    username varchar(32) not null,
    password varchar(32) not null
);
## 创建用户日志表
create table user_log(
	id int primary key auto_increment,
    time timestamp, # 存储时间
    log_text varchar(100)
);
## 定义触发器 school_user_after_insert
### 代码含义：添加触发器school_user_after_insert在user表插入数据之后将日期和操作插入user_log数据表
create trigger school_user_after_insert after insert on user for each row
insert into user_log(time,log_text) values(now(),'new');

# 测试
MariaDB [school]> insert into user(username,password) values('kinght','123456');
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> select * from user;
+-----+----------+----------+
| uid | username | password |
+-----+----------+----------+
|   1 | kinght   | 123456   |
+-----+----------+----------+
1 row in set (0.00 sec)

MariaDB [school]> select * from user_log;
+----+---------------------+----------+
| id | time                | log_text |
+----+---------------------+----------+
|  1 | 2022-11-23 20:05:28 | new      |
+----+---------------------+----------+
1 row in set (0.00 sec)

2.2 补充知识修改mysql默认结束符号

# mysql默认语句结束符号位; 现在修改为$$
## 注意：修改只作用于当前窗口
delimiter $$

# 案例
MariaDB [school]> select * from user_log;
+----+---------------------+----------+
| id | time                | log_text |
+----+---------------------+----------+
|  1 | 2022-11-23 20:05:28 | new      |
+----+---------------------+----------+
1 row in set (0.00 sec)

MariaDB [school]> delimiter $$
MariaDB [school]> select * from user_log$$
+----+---------------------+----------+
| id | time                | log_text |
+----+---------------------+----------+
|  1 | 2022-11-23 20:05:28 | new      |
+----+---------------------+----------+
1 row in set (0.00 sec)

MariaDB [school]> select * from user_log;
    ->

2.3 触发器条件语句

模拟错误日志功能，当cmd表中的记录succes字段是no，则触发触发器执行errlog表中插入数据

create database log;
use log;

create table cmd(
	id int primary key auto_increment,
    user char(32),
    priv char(10),
    cmd char(64),
    sub_time datetime, # 提交时间
    success enum('yes','no') # 0表示执行失败
);

create table errlog(
	id int primary key auto_increment,
    err_cmd char(64),
    err_time datetime
);

按照需求触发器

# 触发器 (错误)
## NEW是指一条条数据对象
create trigger tri_after_insert_cmd after insert on cmd
for each row
begin
	# 固定搭配
	# if 条件 then 条件成立执行语句 end if;
	if NEW.success = 'no' then
	# 如果cmd表的success字段是no，则往errlog表中插入数据
		insert into errlog(err_cmd,err_time)
values(NEW.cmd,NEW.sub_time);
	end if;
end

由于在触发器的语句中出现了分号，以上的语句执行会出现问题，所以需要修改默认结束符号

# 需要提前修改结束符号
delimiter $$

create trigger tri_after_insert_cmd after insert on cmd
for each row
begin
	if NEW.success = 'no' then
		insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
	end if;
end $$ # 用新的结束符号

# 用完之后还需要改回来结束符号
delimiter ;

测试数据

insert into cmd
(USER,priv,cmd,sub_time,success)
Values
('kinght','0755','ls -l /etc',NOW(),'yes'),
('kinght','0755','cat /etc/passwd',NOW(),'no'),
('kinght','0755','useradd xxx',NOW(),'no'),
('kinght','0755','ps aux',NOW(),'yes');

测试结果

MariaDB [log]> select * from cmd;
+----+--------+------+-----------------+---------------------+---------+
| id | user   | priv | cmd             | sub_time            | success |
+----+--------+------+-----------------+---------------------+---------+
|  1 | kinght | 0755 | ls -l /etc      | 2022-11-27 15:26:18 | yes     |
|  2 | kinght | 0755 | cat /etc/passwd | 2022-11-27 15:26:18 | no      |
|  3 | kinght | 0755 | useradd xxx     | 2022-11-27 15:26:18 | no      |
|  4 | kinght | 0755 | ps aux          | 2022-11-27 15:26:18 | yes     |
+----+--------+------+-----------------+---------------------+---------+
4 rows in set (0.00 sec)

MariaDB [log]> select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd         | err_time            |
+----+-----------------+---------------------+
|  1 | cat /etc/passwd | 2022-11-27 15:26:18 |
|  2 | useradd xxx     | 2022-11-27 15:26:18 |
+----+-----------------+---------------------+
2 rows in set (0.00 sec)

2.4 删除触发器

# drop trigger 触发器名字;
drop trigger tri_after_insert_cmd;

3 事务

注意：innodb引擎支持事务，myisam不支持

create table demo(
	id int auto_increment primary key,
    name varchar(32)
)engine=InnoDB default CHARSET=utf8;

# engine=InnoDB 设置数据表引擎为InnoDB
# default CHARSET=utf8 设置数据表默认编码为utf8

3.1 什么是事务

事务可以包含多条SQL语句，这些SQL语句存在连坐关系，即要么全部执行成功，只要有一个报错，即全部失败，被统称为事务的原子性

事务的原子性保证了对数据操作的一致性，因为在操作多条数据的时候可能存在某条指令不成功的情况

# 案例
账户A向账户B转款
	1.账户A-1000元
	2.账户B+1000元
但是可能存在 账户A-1000元 后断网或者错误的情况 导致 账户B+1000元 未执行

3.2 事务的四大特性

事务的四大特性ACID

A:原子性
	事务是不可分割的单位，事务中包含的操作要么同时成功，要么同时失败
C:一致性
	执行的前后数据完整性保持一致
I:隔离性
	一个事务的执行不会被其他事务干扰(即事务的内部操作及使用到的数据对并发的其他事务是隔离互不干扰的)
D:持久性
	事务一旦执行成功，对数据库的修改是永久性的(即直接将数据刷到硬盘)

3.3 如何使用事务

# 1.开启事务 后续语句在一个事物中
start transaction;
# 2.回滚	回到事务执行之前的状态
rollback;
# 3.确认	确认之后就无法回滚了
commit;

案例：模拟转账功能

# 创建表
create table user(
	id int primary key auto_increment,
    name char(16),
    balance int
)engine=InnoDB default CHARSET=utf8;

insert into user(name,balance) values('aaa',1000),('bbb',1000),('ccc',1000);

MariaDB [log]> select * from user;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | aaa  |    1000 |
|  2 | bbb  |    1000 |
|  3 | ccc  |    1000 |
+----+------+---------+
3 rows in set (0.00 sec)

# 需求 ccc 通过 bbb 向 aaa 借 100元钱 (bbb抽成10元)
# 1.开启事务
MariaDB [log]> start transaction;
MariaDB [log]> update user set balance=900 where name='aaa';
MariaDB [log]> update user set balance=1010 where name='bbb';
MariaDB [log]> update user set balance=1090 where name='ccc';

MariaDB [log]> select * from user;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | aaa  |     900 |
|  2 | bbb  |    1010 |
|  3 | ccc  |    1090 |
+----+------+---------+
3 rows in set (0.00 sec)

# 2.回滚 ccc觉得利息太高放弃交易
MariaDB [log]> rollback;
MariaDB [log]> select * from user;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | aaa  |    1000 |
|  2 | bbb  |    1000 |
|  3 | ccc  |    1000 |
+----+------+---------+
3 rows in set (0.00 sec)

# 3.重启交易并确认 
MariaDB [log]> start transaction;
MariaDB [log]> update user set balance=900 where name='aaa';
MariaDB [log]> update user set balance=1010 where name='bbb';
MariaDB [log]> update user set balance=1090 where name='ccc';
MariaDB [log]> commit;

MariaDB [log]> select * from user;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | aaa  |     900 |
|  2 | bbb  |    1010 |
|  3 | ccc  |    1090 |
+----+------+---------+
3 rows in set (0.00 sec)

# 4.确认后无法回滚
MariaDB [log]> rollback;
Query OK, 0 rows affected (0.00 sec) # 会执行成功 但数据不会回滚

MariaDB [log]> select * from user;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | aaa  |     900 |
|  2 | bbb  |    1010 |
|  3 | ccc  |    1090 |
+----+------+---------+
3 rows in set (0.00 sec)

4 存储过程

存储过程类似于Python的自定义函数，它的内部包含一系列可以执行的sql语句，存储过程可以放于mysql服务端中，可以直接调用存储过程触发内部SQL语句的执行

4.1 基本使用

# 定义存储过程
create procedure 存储过程的名字(形参1,形参2,...)
begin
	sql代码
end

# 调用
call 存储过程的名字(形参1,形参2,...);

案例演示

# 在school库中由我们需要的数据表
use school;

# 由于会内嵌sql语句，所以定义的时候修改结束符
delimiter $$
create procedure p1(
	in m int,	# in 只能传入不能返回 int为类型
    in n int,
    out res int # out 可以返回 int为类型
)
begin
	select tname from teacher where tid>m and tid<n;
	set res = 666; # 将变量res修改为666 当前用于标识代码执行成功
end $$
delimiter ;

# 执行存储过程
# call 存储过程名字(参数...)
MariaDB [school]> call p1(1,5,10);
ERROR 1414 (42000): OUT or INOUT argument 3 for routine school.p1 is not a variable or NEW pseudo-variable in BEFORE trigger
# 这里报错的原因是我们定义了一个可返回的类型res
# 可返回的类型只能传入变量
# 变量的定义
MariaDB [school]> set @ret = 10;
Query OK, 0 rows affected (0.00 sec)
# 查看变量
MariaDB [school]> select @ret;
+------+
| @ret |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

# 执行存储过程
MariaDB [school]> call p1(1,5,@ret);
+--------+
| tname  |
+--------+
| 苍空   |
| 饭岛   |
+--------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

# ret发生改变
MariaDB [school]> select @ret;
+------+
| @ret |
+------+
|  666 |
+------+
1 row in set (0.01 sec)

5 常用函数

存储过程是自定义函数，mysql函数是内置函数

5.1 NOW() 当前日期

MariaDB [school]> select NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2022-11-27 23:24:12 |
+---------------------+
1 row in set (0.00 sec)

5.2 date_format() 日期格式化

MariaDB [school]> select date_format(NOW(),'%Y-%m');
# date_format(日期,'%Y-%m拼接格式')
+----------------------------+
| date_format(NOW(),'%Y-%m') |
+----------------------------+
| 2022-11                    |
+----------------------------+
1 row in set (0.00 sec)

# 案例 文章年月分组
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');

6 流程控制

6.1 if判断

delimiter //
create procedure proc_if()
begin
	declare i int default 0;
	if i = 1 then
		select 1;
	elseif i = 2 then
		select 2;
	else
		select 3;
	end if;
end //
delimiter ;

6.2 while循环

delimiter //
create procedure proc_while()
begin
	declare num int,
	set num = 0;
	while num < 10 do
		select
			num;
		set num = num+1;
	end while;

7 索引

7.1 什么是索引

数据都是存储与硬盘上的，查询数据不可避免需要进行IO操作

索引是一种数据结构，类似于书的目录，意味着以后再查询数据的时候应该先找目录而不是一页一页翻书，降低IO操作，提升查询效率，mysql内部索引是由不同的引擎实现的,主要为InnoDB和MyISAM这两种引擎中的索引

索引在MySQL中也叫做键，是存储引擎快速查找记录的一种数据结构

索引的组成通常是 key:value

常见key：
primary key
unique key
index key

注意 foreign key 不适用于加速查询

primary key 、 unique key 除了用于加速查询，他们还具备约束条件 ，而 index key 没有任何约束条件，只是为了快速查询数据

索引的本质是通过不断缩小需要查询的数据范围筛选出最后结果，将随机事件变为顺序事件

一张表中可以有多个索引，索引虽然可以加快查询速度，但是也有缺点

1.表中有大量数据存在 创建索引速度非常慢
2.在索引创建完毕之后 对表的查询性能会大幅度提升 但是写的性能会大幅度降低

7.2 b+树

索引之所以能够加速查找，是因为内部采用了b+树的方式进行存储

# 例如查询键为15的磁盘区域
# 主树干 第一层 蓝色区域为范围
p1 : 主键 < 17
p2 :  35 > 主键 > 17 目标磁盘
p3 : 主键 > 35
# 树干 第二层 
p1 : 主键 < 8
p2 :  12 > 主键 > 8 
p3 : 主键 > 12 目标磁盘
# 树叶层 第三层
直接找到15磁盘区块

只有叶子节点存放的是真实数据或者它的内存地址其他节点存放的都是路引(虚拟数据)

树的层级越高数据查询所需要经历的步骤就越多

7.3 引擎与索引的关系

数据库的索引是基于B+树实现的，但是创建数据库表时，指定不同的引擎，底层使用的原理也会有所不同

myisam引擎，非聚簇索引（数据和索引结构分开存储）
innodb引擎，聚簇索引（数据和主键索引结构存储在一起）

t1表 innodb引擎
	t1.frm	表结构
	t1.ibd	表数据
t2表 myisam引擎
	t2.frm	表结构
	t2.MYD	表数据
	t2.MYI	索引 类似于书的目录 基于目录查找对应数据速度更快

7.3.1 非聚簇索引(mysiam引擎)

该索引和数据文件是分开存放，B+树的叶子只存放数据指针地址，再根据数据指针地址到数据文件中找到对应的值

7.3.2 聚簇索引(innodb引擎)

聚簇索引则将索引和数据放在同一文件中，B+树的叶子层直接就是数据

7.3.3 查看索引信息

MariaDB [school]> show create table student \G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(32) DEFAULT NULL,
  `gender` enum('男','女') DEFAULT '男',
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  KEY `class_id` (`class_id`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: No query specified

7.4 聚集索引(primary key)

聚集索引指的就是主键id

一个磁盘块存储是有限的，为什么建议使用id作为索引，是因为id为整型，占的空间少，一个磁盘块能够存储的数据多，降低了树的高度，从而减少索引次数

Innodb	两个文件	主键直接存放于idb表中
MyIsam	三个文件	单独将索引放于一个文件

7.5 辅助索引(unique,index)

除了主键都是辅助索引

在查询数据的时候也会利用其他字段索引进行查询，其他字段被称为辅助索引，在它的b+树中存放的不是数据，而是聚集索引的主键值，通过该值查询聚焦索引，找到数据

select * from user where username='admin';
# b+树 树叶中存放的是 admin 的 id 
# 然后通过 id 查询 具体的数据

7.6 常见索引创建

7.6.1 主键索引和联合主键索引

# 定义
create table 表名(
    id int not null auto_increment primary key,   -- 主键 同行定义
    name varchar(32) not null
);

create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    primary key(id)								-- 单独定义主键
);

create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    primary key(列1,列2)          -- 如果有多列，称为联合主键（不常用且myisam引擎支持）
);

# 增加主键索引
alter table 表名 add primary key(列名);
# 减少主键索引
alter table 表名 drop primary key;

# 注意：删除索引时可能会报错，因为自增的数据必须是唯一
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
# 把自增属性去掉 修改 表 change id列 改为 id列 添加属性 不为空
alter table 表 change id id int not null;

7.6.2 唯一和联合唯一索引

create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    unique ix_name (name),		-- 唯一索引 name 值不能重复
    unique ix_email (email),
);

create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    unique (列1,列2)               -- 如果有多列，称为联合唯一索引 列1和列2的值可以重复 但不能同行都重复
);

# 增加唯一索引
create unique index 索引名 on 表名(列名);
# 删除唯一索引
drop unique index 索引名 on 表名;

7.6.3 索引和联合索引

create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    index ix_email (email),
    index ix_name (name),
);

create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    index ix_email (name,email)     -- 如果有多列，称为联合索引。
);

# 增加索引
create index 索引名 on 表名(列名);
# 删除索引
create index 索引名 on 表名(列名);

7.6.4 命中索引

既然选择了创建索引，就要在查找的过程中尽可能的命中索引来提高查找效率，以下情况会导致无法命中索引

# 1.类型不一致
## name和email如果是字符类型，而查询时使用了整型就无法命中索引
select * from big where name = 123;		-- 未命中
select * from big where email = 123;	-- 未命中
## 但是id主键不会存在这个问题，会默认转换为整型
select * from big where id = "123";	-- 命中

# 2.使用不等于
## 创建索引的情况下，使用不等于查询，也无法命中索引
select * from big where name != "kinght";				-- 未命中
select * from big where email != "kinght@geekxk.com";  -- 未命中
## 但是主键不存在这个问题
select * from big where id != 123;	-- 命中

# 3.or，当or条件中有未建立索引的列才失效
## or的其中一方未建立索引就无法使用索引
select * from big where id = 123 or password="xx";			-- 未命中
select * from big where name = "wupeiqi" or password="xx";	-- 未命中

# 4.排序，当根据索引排序时候，选择的映射如果不是索引，则不走索引
## 如果查询列明有索引就可以命中
select name from big order by name asc;	-- 命中
select * from big order by name asc;     -- 未命中
select * from big order by name desc;    -- 未命中
## 主键也是例外，都命中
select * from big order by id desc;  -- 命中

# 5.like，模糊匹配
## 通配符在最后可以命中索引，在前面无法命中
select * from big where name like "%u-12-19999";	-- 未命中
select * from big where name like "_u-12-19999";	-- 未命中
select * from big where name like "wu-%-10";		-- 未命中
select * from big where name like "wu-1111-%";	-- 命中
select * from big where name like "wuw-%";		-- 命中

# 6.函数
select * from big where reverse(name) = "wupeiqi";  -- 未命中
## 如果先处理结果再比对是可以命中索引
select * from big where name = reverse("wupeiqi");  -- 命中

# 7.联合索引 遵循向左看原则
如果联合索引为：(name,password) ?  
name and password ? ? ? -- 命中 ?  
name ? ? ? ? ? ? ? ?    -- 命中 ? 可以单独命中左边的联合
password ? ? ? ? ? ? ? ?-- 未命中 ?  不能命中右边的联合
name or password ? ? ?  -- 未命中

7.8 执行计划

其中比较重要的是 type，他他SQL性能比较重要的标志，性能从低到高依次：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

ALL，全表扫描，数据表从头到尾找一遍。(一般未命中索引，都是会执行权标扫描)

select * from big;

特别的：如果有limit，则找到之后就不在继续向下扫描.
	select * from big limit 1;

INDEX，全索引扫描，对索引从头到尾找一遍

explain select id from big;
explain select name from big;

RANGE，对索引列进行范围查找

explain select * from big where id > 10;
explain select * from big where id in (11,22,33);
explain select * from big where id between 10 and 20;
explain select * from big where name > "wupeiqi" ;

INDEX_MERGE，合并索引，使用多个单列索引搜索

explain select * from big where id = 10 or name="武沛齐";

REF，根据索引直接去查找（非键）。

select *  from big where name = '武沛齐';

EQ_REF，连表操作时常见。

explain select big.name,users.id from big left join users on big.age = users.id;

CONST，常量，表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快。

explain select * from big where id=11;					-- 主键
explain select * from big where email="w-11-0@qq.com";	-- 唯一索引

SYSTEM，系统，表仅有一行(=系统表)。这是const联接类型的一个特例。
```
 explain select * from (select * from big where id=1 limit 1) as A;
```

其他列：

id，查询顺序标识

z，查询类型
    SIMPLE          简单查询
    PRIMARY         最外层查询
    SUBQUERY        映射为子查询
    DERIVED         子查询
    UNION           联合
    UNION RESULT    使用联合的结果
    ...
    
table，正在访问的表名

partitions，涉及的分区（MySQL支持将数据划分到不同的idb文件中，详单与数据的拆分）。 一个特别大的文件拆分成多个小文件（分区）。

possible_keys，查询涉及到的字段上若存在索引，则该索引将被列出，即：可能使用的索引。
key，显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。例如：有索引但未命中，则possible_keys显示、key则显示NULL。

key_len，表示索引字段的最大可能长度。(类型字节长度 + 变长2 + 可空1)，例如：key_len=195，类型varchar(64)，195=64*3+2+1

ref，连表时显示的关联信息。例如：A和B连表，显示连表的字段信息。

rows，估计读取的数据行数（只是预估值）
	explain select * from big where password ="025dfdeb-d803-425d-9834-445758885d1c";
	explain select * from big where password ="025dfdeb-d803-425d-9834-445758885d1c" limit 1;
filtered，返回结果的行占需要读到的行的百分比。
	explain select * from big where id=1;  -- 100，只读了一个1行，返回结果也是1行。
	explain select * from big where password="27d8ba90-edd0-4a2f-9aaf-99c9d607c3b3";  -- 10，读取了10行，返回了1行。
	注意：密码27d8ba90-edd0-4a2f-9aaf-99c9d607c3b3在第10行
	
extra，该列包含MySQL解决查询的详细信息。
    “Using index”
    此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
    “Using where”
    这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
    “Using temporary”
    这意味着mysql在对查询结果排序时会使用一个临时表。
    “Using filesort”
    这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
    “Range checked for each record(index map: N)”
    这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。

8 权限管理

之前，我们连接mysql客户端均是使用root账户，拥有Mysql数据库最高管理权，如果有多个程序的数据库都放在统一Mysql服务器中，一旦被SQL注入攻击，所有的网站都会直接被攻破

通常在生产环境，会创建多个账户，并给账户分配权限

8.1 用户管理

在MySQL的默认数据库 mysql 中的 user 表中存储着所有的账户信息(含账户、权限)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo               |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

# host代表可连接的地址
MariaDB [(none)]> select user,authentication_string,host from mysql.user;
+------+-----------------------+-------------+
| user | authentication_string | host        |
+------+-----------------------+-------------+
| root |                       | %           |
+------+-----------------------+-------------+
1 rows in set (0.00 sec)

8.1.1 创建用户

# create user '用户名'@'连接者的IP地址(%表示所有地址 通配符)' identified by '密码';
MariaDB [(none)]> create user 'kinght'@'%' identified by '123456';
# 'aym'@'127.0.0.1' aym账户只支持本地登录
MariaDB [(none)]> create user 'aym'@'127.0.0.1' identified by '123123';
# 通配符支持网段 admin账户支持192.168.0.x网段的主机登录
MariaDB [(none)]> create user 'admin'@'192.168.0.%' identified by '123123';

8.1.2 删除用户

# drop user '用户名'@'连接者的IP地址';
# 删除用户需要把用户名和登陆主机写完整
MariaDB [(none)]> drop user 'admin'@'192.168.0.%';

8.1.3 修改用户

# rename user '用户名'@'IP地址' to '新用户名'@'IP地址';
MariaDB [(none)]> rename user 'kinght'@'%' to 'kinghtxg'@'%';

8.1.4 修改密码

# set password for '用户名'@'IP地址' = Password('新密码')
set password for 'kinghtxg'@'%' = Password('aym');

8.2 用户授权

mysql.user表中同时存放了权限信息

MariaDB [(none)]> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11)                           | NO   |     | 0       |       |
| plugin                 | char(64)                          | NO   |     |         |       |
| authentication_string  | text                              | NO   |     | NULL    |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.00 sec)

8.2.1 授权

#?grant 权限 on 数据库.表 to '用户'@'IP地址';

# 1.1 授予kinghtxg用户所有数据库的所有权限
MariaDB [(none)]> grant all privileges on *.* to 'kinghtxg'@'%'; 
	# all privileges 等于 所有权限
	# *.* 等于 所有数据库.所有数据表
# 1.2 授予kinghtxg用户demo数据库的所有权限
MariaDB [(none)]> grant all privileges on demo.* to 'kinghtxg'@'%';
# 1.3 授予kinghtxg用户demo数据库User数据表的所有权限
MariaDB [demo]> grant all privileges on demo.User to 'kinghtxg'@'%';
# 1.4 授予kinghtxg用户demo数据库User数据表的查询权限
MariaDB [demo]> grant select on demo.User to 'kinghtxg'@'%';
# 1.5 授予kinghtxg用户demo数据库User数据表的查询和插入权限
MariaDB [demo]> grant select,insert on demo.User to 'kinghtxg'@'%';

flush privileges;   -- 将数据读取到内存中，从而立即生效。

8.2.2 权限说明

all privileges  除grant外的所有权限
select          仅查权限
select,insert   查和插入权限
...
usage                   无访问权限
alter                   使用alter table
alter routine           使用alter procedure和drop procedure
create                  使用create table
create routine          使用create procedure
create temporary tables 使用create temporary tables
create user             使用create user、drop user、rename user和revoke  all privileges
create view             使用create view
delete                  使用delete
drop                    使用drop table
execute                 使用call和存储过程
file                    使用select into outfile 和 load data infile
grant option            使用grant 和 revoke
index                   使用index
insert                  使用insert
lock tables             使用lock table
process                 使用show full processlist
select                  使用select
show databases          使用show databases
show view               使用show view
update                  使用update
reload                  使用flush
shutdown                使用mysqladmin shutdown(关闭MySQL)
super                   ????使用change master、kill、logs、purge、master和set global。还允许mysqladmin????????调试登陆
replication client      服务器位置的访问
replication slave       由复制从属使用

8.2.3 对于数据库和表授权

数据库名.*            数据库中的所有
数据库名.表名          指定数据库中的某张表
数据库名.存储过程名     指定数据库中的存储过程
*.*                  所有数据库

8.2.4 查看授权

show grants for '用户'@'IP地址'

show grants for 'root'@'%';

8.2.5 取消授权

revoke 权限 on 数据库.表 from '用户'@'IP地址'

revoke ALL PRIVILEGES on * from 'kinght'@'%';
注意：flush privileges;   -- 将数据读取到内存中，从而立即生效。

一般情况下，在很多的正规公司，数据库都是由 DBA 来统一进行管理，DBA为每个项目的数据库创建用户，并赋予相关的权限

9 数据库备份

9.1 导入数据库

# mysql -uroot -p 数据库名 < /导入路径
mysql -uroot -p demo < ~/mysqldump/1.sql

9.2 导出数据库

# 结构加数据
# mysqldump -uroot -p 数据库名 > 路径.sql
mysqldump -uroot -p school > ~/mysqldump/1.sql
# 结构
# mysqldump -uroot -p -d 数据库名 > 路径.sql
mysqldump -uroot -p -d school > ~/mysqldump/1.sql

10 锁

在使用mysql时，如果有很多用户同时进行更新、插入、删除动作，如何能够保证mysql数据不出错

mysql内部自带锁的功能，能够帮我们实现开发过程中遇到的同时处理数据的情况，对于数据库中的锁，从锁的范围来讲：

表级锁
- 即A用户操作表时，其他人对整个表都不能操作，等待A操作完成之后，才能继续
行级锁
- 即A用户操作表时，其他人对指定行数据不能进行操作，其他行可以操作，等待A操作完成后，才能继续

MYISAM支持表锁，不支持行锁；
InnoDB引擎支持行锁和表锁。

即：在MYISAM下如果要加锁，无论怎么加都会是表锁。
    在InnoDB引擎支持下如果是基于索引查询的数据则是行级锁，否则就是表锁。

所以，一般情况下我们会选择使用innodb引擎，并且在搜索时也会使用索引（命中索引）

接下来的操作就基于innodb引擎来操作：

CREATE TABLE L1 (
  id int(11) PRIMARY KEY AUTO_INCREMENT,
  name varchar(255) DEFAULT NULL,
  count int(11) DEFAULT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
# ENGINE=InnoDB 指定使用InnoDB引擎
# DEFAULT CHARSET=utf8 指定默认编码utf8

insert into L1(name,count) values('kinght',1000),('aym',3000),('aliter',10000);

在innodb引擎中，update、insert、delete的行为内部都会先申请锁（排它锁），申请到之后才执行相关操作，最后再释放锁。

所以，当多个人同时像数据库执行：insert、update、delete等操作时，内部加锁后会排队逐一执行。

而select则默认不会申请锁。

select * from xxx;

如果，你想要让select去申请锁，则需要配合 事务 + 特殊语法 来实现

for update排他锁加锁之后，其他不可以读写

begin;
	select * from L1 where id=1 for update; # 使用id则是行锁
commit;

begin; # 或者 start transaction;
	select * from L1 where name='kinght' for update; # 除了id索引其他的都是表锁
commit;

lock in share mode共享锁加锁之后其他可读不可写

begin;
	select * from L1 where id=1 lock in share mode; # 使用id则是行锁
commit;

begin; # 或者 start transaction;
	select * from L1 where name='kinght' lock in share mode; # 除了id索引其他的都是表锁
commit;

#Python

#python #mysql #数据库 #关系型数据库

Python开发-042_mysql数据库的常见概念

http://localhost:8080/archives/lfSC2yv2

作者

kinght

发布于

2024年11月11日

更新于

2024年11月12日

许可协议

Python开发-043_Python操作Mysql数据库上一篇

Python开发-041_数据库查询条件下一篇

Python开发-042_mysql数据库的常见概念

1 视图

1.1 视图的作用

1.2 视图的特点

2 触发器

2.1 触发器的作用

2.2 补充知识 修改mysql默认结束符号

2.3 触发器条件语句

2.4 删除触发器

3 事务

3.1 什么是事务

3.2 事务的四大特性

3.3 如何使用事务

4 存储过程

4.1 基本使用

5 常用函数

5.1 NOW() 当前日期

5.2 date_format() 日期格式化

6 流程控制

6.1 if判断

6.2 while循环

7 索引

7.1 什么是索引

7.2 b+树

7.3 引擎与索引的关系

7.3.1 非聚簇索引(mysiam引擎)

7.3.2 聚簇索引(innodb引擎)

7.3.3 查看索引信息

7.4 聚集索引(primary key)

7.5 辅助索引(unique,index)

7.6 常见索引创建

7.6.1 主键索引和联合主键索引

7.6.2 唯一和联合唯一索引

7.6.3 索引和联合索引

7.6.4 命中索引

7.8 执行计划

8 权限管理

8.1 用户管理

8.1.1 创建用户

8.1.2 删除用户

8.1.3 修改用户

8.1.4 修改密码

8.2 用户授权

8.2.1 授权

8.2.2 权限说明

8.2.3 对于数据库和表授权

8.2.4 查看授权

8.2.5 取消授权

9 数据库备份

9.1 导入数据库

9.2 导出数据库

10 锁

2.2 补充知识修改mysql默认结束符号