1. 云图网首页
  2. 技术专区
  3. 数据库

【MySQL】MySQL总结

• 数据库


目录

1. 数据库

1.1 数据库本质

  1. 站在底层原理的角度，数据库指的专用用于操作数据的进程，eg:运行在内存中的代码
  2. 站在现实应用的角度，数据库指的是拥有操作界面的应用程序，eg:用于操作进程的界面
  3. 我们不做特殊说明的下提数据库其实都是在指数据库软件，我们也称数据库软件本质是一款cs架构的应用程序，言外之意所有的程序员理论上都可以编写：市面上已经有很多数据库软件

1.2 数据库分类

关系数据库与非关系数据库

关系数据库 非关系数据库
数据的组织方式有明确的表结构
表里面有一些表的基本信息，关系型数据库存取数据的方式可以看成是表格		 数据的组织方式没有明确的表结构
是以K:V健值对的形式组织的，{‘name’:’jason’},{‘username’:’kevin’,’pwd’:123}
表与表之间可以建立数据库层面的关系 数据之间无法直接建立数据库层面的关系
MySQL,PostgreSQL,MariaDB,Oracle,sqlite,db2,sql server
MySQL:开源，使用最为广泛，数据库学习必学
PostgreSQL:开源 支持二次开发
MariaDB:开源 与MySQL是同一个作者，用法也极其相似
Oracle:收费，安全性极高，主要用于银行及各大重要机关
sqlite:小型数据库，主要用于本地测试（django框架自带该数据库）		 redis,mongoDB,memcache
redis:目前最火，使用频率最高的缓存型数据库
mongoDB:稳定型数据库，最像关系型的非关系型，主要用于爬出，大数据memcache:已经被radis淘汰

1.3 SQL与NoSQL

  1. SQL语句的意思是操作关系数据库的语法
  2. NoSQL语句的意思操作非关系型数据库的语法
  3. SQL有时候也用来表示关系型数据库，NoSQL也用来表示非关系型数据库

1.4 数据库重要概念

库 文件夹
表 文件夹里面的文件
记录 文件里一行行数据
查看所有的数据库 show databases;
查看所有的表 show tables;
查看user表里面所有的记录 select * from mysql.user;
SQL语句结束符是分号 ；
取消SQL语句的执行 /c
如果表中字段较多出现了错乱 可以结尾写/G 可以结尾写/G

1.5 数据库存储引擎

1.5.1 定义

  1. 我们可以简单的理解为存储引擎就是针对相同的数据采用的不同的存取策略
  2. show engines; 查看所有的存储引擎

1.5.2 存储引擎

存储引擎 简介
MyISAM MySQL5.5及之前版本默认的存储引擎
存取数据的速度快，但是功能较少，安全性较低
InnoDB MySQL5.5之后版本默认的存储引擎
存取数据的速度没有MyISAM快，但是支持事物，行锁，外键等诸多功能
安全性较高
Memory 基于内存的存储引擎，存取数据极快，但是断电立刻丢失
BlackHole 黑洞，任何写进去的数据都会立刻丢失，类似于垃圾站

1.5.3 不同存储引擎之间底层文件的区别

存储引擎 区别
InnoDB .frm 表结构
.ibd 表结构，表索引（加快数据查询）
MyISAM .frm 表结构
.MYD 表数据
.MYI 表索引（加快数据查询）
Memory .frm 表结构
BlackHole .frm 表结构

2. 针对库的基本SQL语句

操作 语法
增 create database 库名;
查 show databases;
show creat database 库名;
改 alter database 库名 charset=’gbk’;
删 drop database 库名;

3. 针对表的基本SQL语句

操作 语法
查看当前所在库名 select database();
使用当前库 use 库名;
增 create table 表名(字段名 字段类型，字段名 字段类型，);
查 show tables;
show create table 表名;
describe 表名;
desc 表名;
改 alter table 旧表名 rename 新表名;
删 drop table 表名;

4. 针对记录的基本SQL语句

操作 语法
增 insert into 表名 values(数据，数据);
insert into 表名 values(数据，数据),(数据，数据);
查 select * from 表名; # 查看所有字段
select 字段1，字段2 from 表名;
表中字段较多出现了错乱可以结尾写/G
改 update 表名 set 字段名 = 新数据 where 筛选条件;
删 delete from 表名; # 删除表中所有的数据
delete from 表名 where 筛选条件; # 按照条件删除数据
操作 语法
修改表名 alter table 表名 rename 新表名;
新增字段 alter table 表名 add 字段名 字段类型(数字) 约束条件;
alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已经存在的字段;
alter table 表名 add 字段名 字段类型(数字) 约束条件 first;
修改字段 alter table 表名 change 旧字段 新字段 字段类型(数字) 约束条件;
alter table 表名 modify 字段名 新的字段类型(数字) 约束条件;# 只改字段类型
删除字段 alter table 表名 drop 字段名;

5. 字符编码

5.1 /S

  1. /s 查看数据库基本信息（用户，字符编码）

5.2 my.ini

  1. my-default.ini # windows下MySQL默认的配置文件
  2. 拷贝上述文件并且重命名为my.ini
  3. 添加字符编码相关的配置（百度查看即可）
  4. 注意如果配置文件涉及到了mysqld相关的配置修改 那么需要重启服务端才可以生效
  5. 偷懒操作 在配置文件中的MySQL下提前写好用户名和密码，之后直接mysql登录

6. 创建表的完整语法

create table 表名(
       字段名1 字段类型(数字) 约束条件,
       字段名2 字段类型(数字) 约束条件,
       字段名3 字段类型(数字) 约束条件
)engine=存储引擎；
  1. 字段名和字段类型是必须的(至少写一个)
  2. 数字跟约束条件是可选的(可有可无)
  3. 约束条件可以写多个，空格隔开即可
  4. 最后一个字段的结尾千万不能加逗号

7. MySQL字段类型

7.1 字段类型之整型

  1. 整型类型默认都会带有负号
    【MySQL】MySQL总结
  2. 自定义移除负号
unsigned 约束条件之一，意思是不需要负号
create table t7(id tinyint unsigned);
insert into t7 values(129),(256);
  1. 配置文件修改问题
    插入的数据超出了数据类型的范围，不应该让其插入并自动修改，没有意义，数据库应该直接报错（这个特性其实是有的，只是被我们改了>>>:配置文件）
  2. 方式一：命令临时修改
set session sql_mode='strict_trans_table' 当前客户端操作界面有效
set global sql_mode='STRICT_TRANS_TABLES'  服务端不重启永久有效
  1. 方式二：配置文件永久修改
[mysqld]
	sql_mode='STRICT_TRANS_TABLES'

7.2 字段类型之浮点型

  1. float, double, decimal 三者都可以存储浮点型数据，但是各自的精准度不一致
  2. 第一给数表示总共多少位，第二给数表示小数占多少位
    float(255,30)
    double(255,30)
    decimal(65.30)
  3. 总结：float < double < decimal
    一般情况下float足够使用了，如果想追求非常完美的精确度，可以使用字符串来代替

7.3 字段类型值字符类型

  1. 简介
字符类型 表现形式 例子
char 定长 char(4)
最大只能存储四个字符，如果超过范围则直接报错
如果不能超出范围，则用空格填充至四个字符
varchar 变长 varchar(4)
最大只能存储四个字符，如果超过范围则直接报错
如果不超出范围2，则有几位就存几位
  1. 区别
create table t11(id int, name char(4));
create table t12(id int, name varchar(4));
desc t11;
desc t12;

结果验证，超出范围两者都会报错,默认情况下char在存储的时候针对没有满足固定位数的字符会自动填充空格，
然后在读取的时候又会自动将填充的空格移除，如果想取消该机制，
需要 sql_mode set global sql_mode=’strict_trans_tables,pad_char_to_full_length’;
上述目录是替换，不是新增，所有之前的配置也要写上
3. 优缺点

类型 优缺点 案例
char 整存整取 速度快
浪费存储空间		 char(4)
a bcd aaaaaa 四个一组，空格也算
varchar 节省存储空间
存取数据的速度慢于char		 varchar(4)
1bytes+a1bytes+son1bytes+jack
存取数据都需要操作报头（耗时）

7.4 数字

  1. 定义
    数字大部分情况下都是用来限制字段的存储长度，但是整型除外，不是用来限制存储的长度，而是展示的长度
  2. 理解
create table t13(id int(3));
insert into t13 values(1),(123),(12345);
create table t14(id int(3) zerofill);
insert into t14 values(1),(12);
  1. 总结
    以后涉及到整型字段的定义，类型后面不需要加括号写数字，除非有业务需要必须固定位数。 eg:001 012

7.5 字段类型之枚举与集合

  1. 枚举（多选一）
# eg:性别（男，女，其他）
create table t15(
     id int,
     name varchar(32),
     gender enum('male','female','others')
);
insert into t15 values(1,'aaaaa','男')；
insert into t15 values(1,'bbbb','male')
  1. 集合（多选多）
# eg:爱好(写，跑，听)
create table t16(
    id, int,
    name varchar(32),
    hobbies set('read','run','music')
)

7.6 字段类型之日期类型

date 年月日
datetime 年月日时分秒
time 时分秒
year 年份 
create table t17(
    id int,
    name varcher(32),
    brith date,
    reg_time datetime,
    study_time time,
    join_time year
);

8. MySQL字段约束

8.1 新增语法补充

insert into 表名 vlaues()  # 默认按照创建表字段顺序添加
insert into 表名(字段) vlaues  # 可以自定义字段顺序

8.2 无负号、零填充

  1. unsigent 无负号 id int unsigned
  2. zerofill 零填充 id int zerofill

8.3 not null 非空

create table t19(id int,name varchar(32) not null);
insert into t19(id) values(1);
insert into t19(id,name) values(1,'');

8.4 default默认值

create table t20(id int,name varchar(32) not null default 'jason');
insert into t20(id) values(1);
insert into t20(id,name) values(2,'zhang');

8.5 unique 唯一值

  1. id int unique 单列唯一
create table t21(id int unique,name varchar(32));
insert into t21 values(1,'jason');
insert into t21 values(1,'zhang');
insert into t21 values(2,'zhang');
  1. unique(host,port) 联合唯一
create table t22(id int,host varchar(32),post int,unique(host,post));
insert into t20 values(1,'127.0.0.1',8080);
insert into t22 values(2,'127.0.0.1',8088);
insert into t22 values(3,'127.0.0.2',8088);
insert into t22 values(4,'127.0.0.1',8080);

9. 约束条件之主键

9.1 primary key主键

  1. 单从约束角度上而言主键等价于非空且唯一 not null unique
  2. InnoDB存储引擎规定一张表必须有且只有一个主键
    1. 如果创建的表中没有主键也没有非空且唯一的字段，那么InnoDB存储引擎会自动采用一个隐藏的字段作为主键（主键可以加快数据查询：新华字典的目录）
    2. 如果创建的表中没有主键但是有非空且唯一的字段，那么InnoDB存储引擎会自动将该字段设置为主键
  3. 创建表的时候都应该有一个’id’字段，并且该字段应该作为主键
    1. 命名不限 可以是id,cid,pid…
    2. 单列主键 id int primary key 最常见
    3. 联合主键 sid int,nid int,primary key(sid,nid) 联合主键。联合主键也属于单列主键

9.2 auto_increment自增

  1. 该约束添加不能单独使用，必须跟在键后面(主要配合主键一起使用)
  2. 自增特点
    1. 自增的操作不会因为执行删除数据的操作而回退或者重置
    2. 如果非要重置主键，需要格式化表，truncate 表名； 删除表数据并重置主键值

10. 约束条件之外键

10.1 定义

外键字段：专用于记录表于表之间数据的关系

10.2 数据关系四种(换位思考)

  1. 一对多关系
    两张表完成换位思考之后得出的答案一个可以一个不可以，那么表关系就是”一对多”针对’一对多’的关系 外键字段建在多的一方。

  2. 多对多关系
    两张表完成换位思考之后得出的答案两边都可以，那么表数据关系就是’多对多’，针对多对多表关系 外键字段不能建在任意一方。需要单独开设第三张关系表 存储数据关系。

  3. 一对一关系
    两张表完成换位思考之后得出的答案两边都不可以，那么先考虑是不是没有关系，如果有关系那么肯定就是’一对一’，针对’一对一’的表关系 外键字段建在任何一张表都可以，但是建议建在查询频率较高的表中便于后续查询，还需添加唯一性，要不然和一对多没区别

  4. 没有关系

  5. 没有多对一，统一称为一对多

10.3 Foregin key

  1. 步骤
    1. 先写普通字段
    2. 然后再写外键字段
  2. 理解（一对多代码演示）
    1. 创建表的时候需要先创建被关联的表(没有外键)，然后再是关联表(有外键)
    2. 插入表数据的时候，针对外键字段只能填写被关联表字段已经出现过的数据值
    3. 被关联字段无法修改和删除，有点不太好，操作限制性太强
  3. 级联更新，级联删除（一对多代码演示）
    1. 被关联数据一旦变动，关联的数据同步变动
    2. 级联更新 on update cascade
    3. 级联删除 on delete cascade
  4. 扩展
    1. 在实际工作中，很多时候可能并不会使用外键，因为外键增加了表之间的耦合度，不便于单独操作，资源消耗增加
    2. 我们为了能够描述出表数据的关系，又不想使用外键，自己通过写SQL,建立代码层面的关系

11. 查询关键字

11.1 查询关键字之select与from/

  1. select 自定义查询表中字段对应的数据
  2. from 指定操作的对象(到底事那张表，也可能是多张)
  3. SQL语句的关键字编写顺序与执行顺序是不一致的
    eg: select name from emp;
    肯定是先支持from确定表，之后执行select确定字段
  4. 编写SQL语句针对select和from可以先写个固定模板
    select * from 表名 其他操作
    select后的字段可能是实际的，也可能是通过SQL动态产生的，所以可以先用*占位最后再修改

11.2 查询关键字之where筛选

  1. where + 条件
# 查询id大于等于3小于等于6的数据
select id,name from emp where id >= 3 id <= 6;
select id,name from emp where 3 <= id <= 6; # 错误写法
select * from emp where id between 3 and 6;
# 查询薪资是20000或者18000或者17000的数据
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000);
# 查询岗位描述为空的员工名与岗位名 针对null不能用等号，只能用is
select name,post from emp where post_comment = NULL;  # 查询为空！
select name,post from emp where post_comment is NULL;
select name,post from emp where post_comment is not NULL;
  1. where + 条件模糊查询（like）
    1. %:匹配任意个数的任意字符
    2. —：匹配单个个数的任意字符
select name,salary from emp where name like '%o%';

11.3 查询关键字之group by 分组

  1. 分组：按照一些指定的条件将单个单个的数据分一个个整体
  2. 分组之后我们研究的对象应该是以组为单位，不应该再直接获取单个数据项，如果获取了应该直接报错，select后面可以直接填写的字段名只能是分组的依据(其他字段需要借助于一些方法才可以获取)
    set global sql_mode=’strict_trans_tables,only_full_group_by’;
select post from emp group by post;
  1. 我们写SQL是否需要使用分组，可以再题目中得到答案
    如出现 每个，平均，最大，最小
  2. 配合分组常见使用的有聚合函数
max 最大值
min 最小值
sum 总和
count 计数
avg 平均

11.4 查询关键字之distihct去重

去重的前提是数据必须一模一样

# age 数据有重复数据
select distinct age from emp;

11.5 查询关键字之order by 排序

# 1. 查询工资，排序 
select * from emp order by salary asc; # 默认升序排
select * from emp order by salary desc; # 降序排
# 2. 年龄降序排，薪资升序排
select * from emp order by age desc,salary asc;

11.6 查询关键字之limit分页

# 1. 限制展示条数
select * from emp limit 3;
# 2. 查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;
# 3. 分页显示
# 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
select * from emp limit 0,5; 
select * from emp limit 5,5;

11.7 查询关键字之regexp正则

# 查询以j开头，n或y结尾
select * from emp where name regexp '^j.*(n|y)$';

12. 多表查询

12.1 子查询

  1. 思路
    1. 将一张表的查询结果括号括起来当做另外一条sql语句的条件
    2. 类似以日常生活中解决问题的方式
      第一步干什么
      第二步基于第一步的结果在操作

12.2 连表操作

  1. 思路
    1. 先将所有涉及到结果的表全部拼接到一起形成一张大表，然后从大表中查询数据“`mysql
    2. 我们不会使用笛卡尔积来求数据 效率太低 连表有专门的语法
      select * from emp1,dep1; # 笛卡尔积

12.3 连接

  1. innerinto 内连接 只拼接两边都有的字段数据
  2. left join 左连接 以左表为基准 展示所有的数据 没有对应则NULL填充
  3. right join 右连接 以右表为基准 展示所有的数据 没有对应则NULL填充
  4. union 全连接 把左连接和有连接连接起来

12.4 多表查询思路

  1. 读题先确定需要几张表
  2. 查看这几张表的表数据
  3. 在根据要求判断是否需要分组，条件等，
  4. 如果多表，将SQL语句当做表来使用 连接的时候需要使用as起表名
  5. 思考逻辑，判断是子查询还是连表操作

13. Navicat 可视化软件

13.1 下载

  1. 下载Navicat Premium 尽量下载最新版本，功能比较多。(可自行百度下载安装)
  2. Navicat Premium 下载路径：
    链接：http://www.navicat.com.cn/download/navicat-premium
  3. 激活工具：
    链接：https://pan.baidu.com/s/1pB5xh9CvFOnaWXWnjQX6zg?pwd=ir74
    提取码：ir74

13.2 功能简介

  1. Navicat premium是一款数据库管理工具,是一个可多重连线资料库的管理工具，它可以让你以单一程式同时连线到 MySQL、SQLite、Oracle、MariaDB、Mssql、及PostgreSQL 资料库，让管理不同类型的资料库更加的方便。
  2. 不同的资料库的批次处理任务也可以设定排程并在指定的时间执行。其他功能包括汇入/汇出精灵、查询建立工具、报表产生器、资料同步、备份、工作排程及更多。

14. python 操作MySQL

14.1 第三方模块的下载两种方式

  1. 方式一：pip install 模块名==版本号
  2. 方式二：在pycharm中settings>>project中，或直接在py文件插入点击报错直接下载

14.2 python操作MySQL

  1. 只进行查看服务,结果为列表套字典
  2. autocommit=True # 执行增、改、删操作自动执行conn.commit 二次确认机制
  3. 获取结果
    1. cursor.fetchone() 获取结果集中一条数据
    2. cursor.fetchall() 获取结果集中所有数据
    3. cursor.fetchmany() 获取结果集中指定条的数据
    4. cursor.scroll(2, mode=’relative’) 基于当前位置往后移动
    5. ursor.scroll(0, mode=’absolute’) 基于数据集开头的位置往后移动
import pymysql

# 1.链接服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db3',
    charset='utf8mb4',
    autocommit=True # 执行增、改、删操作自动执行conn.commit
)
# 2.产生一个游标对象（等待输入命令）
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3.编写sql语句
sql1 = 'select * from t1'
# 4.发送给服务端
cursor.execute(sql1)
# 5.获取命令2的执行结果
# res = cursor.fetchall()  # 获取结果集中所有数据
# res = cursor.fetchone()  # 获取结果集中一条数据
# res = cursor.fetchmany(3) # 获取结果集中指定条的数据
res = cursor.fetchone()
print(res)
# 类似于文件光标的概念
# cursor.scroll(1,mode='relative') # 基于当前位置往后移动
cursor.scroll(0,mode='absolute') # 基于数据集开头的位置往后移动
res = cursor.fetchall()
print(res)

14.3 SQL注入问题

  1. 只需要用户名即可登录
  2. 不需要用户名和密码也能登录
import pymysql

# 1. 链接服务器
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db3',
    charset='utf8mb4',
    autocommit=True
)
# 2.产生一个游标对象（等待输入命令）
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3.获取用户数据
username = input('username>>>:').strip()
password = input('password>>>:').strip()
# 4.编写sql语句
sql = "select * from t1 where name='%s' and pwd='%s'"%(username,password)
print(sql)
# 5.执行sql语句
cursor.execute(sql)
# 6.获取结果
res = cursor.fetchall()
if res:
    print('登录成功')
    print(res)
else:
    print('用户名或密码错误')
  1. 本质与措施
    1. 本质：利用一些特殊符号的组合产生了特殊的含义从而逃脱了正常的业务逻辑
    2. 措施：针对用户输入的数据不要自己处理 交给专门的方法自动过滤
# mysql 三种注释语法
# 号注释
-- --注释
/* 注释*/
where 后面条件如果为True 相当与没有条件

【MySQL】MySQL总结

  1. 一次性执行多条sql语句
    cursor.executemany() 一次性执行多条sql语句
import pymysql

# 1. 链接服务器
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db3',
    charset='utf8mb4',
    autocommit=True
)
# 2.产生一个游标对象（等待输入命令）
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'insert into t1(name,pass) values(%s,%s)'
# [(),(),()]
cursor.executemany(sql,[('jason1',111),('jason2',222),('jaosn',333)])

15. 知识点补充了解

15.1 as语法

给字段起别名，起表名

15.2 comment语法

给表、字段添加注释信息

create table t3(id int primary key auto_increment comment '用户id', name varchar(32) comment '用户名') comment ' 用户表';
# 查看注释
 show create table t3;
# 打开库
use information_schema
# 查看库里表
show tables; 
# 查看以名字筛选
select * from tables where table_name='t3'/G;

15.3 concat、concat_ws语法

  1. concat用于分组之前多个字段数据的拼接
  2. concat_ws如果有多个字段 并且分隔符一致 可以使用该方法减少代码（字段越多效果越明显）

15.4 exists 语法

exists后面的sql语句如果有结果那么执行前面的sql语句，如果没有结果则不执行

15.5 help

通过help 函数名 查看帮助信息

16 视图(view)

16.1 视图概念

  1. SQL语句的执行结果是一张虚拟表 我们可以基于该表做其他操作,如果这张虚拟表需要频繁使用 那么为了方便可以将虚拟表保存起来 保存起来之后就称之为”视图”(本质就是一张虚拟表)
  2. 视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据

16.2 语法与操作

# 创建视图基本语法（SQL语句为多表查询语句）
create view 视图名 as SQL语句;
# 修改视图
alter view 视图名 as select 语句
# 显示视图创建情况
show create view 视图名;
# 查看视图
show tables；
# 删除视图
drop view 视图名[,视图名…];
# 重命名视图
Rename table 视图名 to 新视图名;

16.3 总结

  1. 在硬盘中，视图只有表结构文件，没有表数据文件
  2. 视图通常是用于查询，尽量不要修改视图中的数据
  3. 视图能尽量少用就尽量少用

17 触发器(trigger)

17.1 触发器概念

触发器是与表有关的数据库对象，针对表数据的增、改、删自动触发的功能(增前、增后、改前、改后、删前、删后)

17.2 语法结构与操作

create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
   sql语句
end;
# before/after：指定是在之前还是之后触发
# insert/update/delete：指定当表发生什么类型(insert/update/delete)的操作时会触发
# 查看触发器
show triggers;
# 删除触发器
drop triggers 触发器的名字

17.3 delimiter 修改SQL语句默认的结束符

触发器内部的SQL语句需要用到分号吗，但是分号又是SQL语句默认的结束符所以为了能够完整的写出触发器的代码，需要临时修改SQL语句默认的结束符

delimiter $$   # 第一次为将mysql默认的结束符由;换成$$
编写需要用到分号的各种语句
delimiter ;    # 第二次为把修改为$$的结束符修改成原来的分号

17.4 注意事项

  1. 触发器中不能对本表进行insert，update，delete操作，以免递归循环触发
  2. 触发器是针对每一行的数据，对增删改非常频繁的表上切记不要使用触发器，因为非常消耗资源。
  3. 尽量少使用 触发器，假设触发器触发每次执行1s，insert table 500条数据，那么就需要触发500次触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一共是1s，那么这个insert 的效率就非常低了。

18 事务(transaction)

18.1 事务概念

是将⼀组操作封装成⼀个执行单元（封装到⼀起），这⼀个执⾏单元要么⼀起执行成功，要么⼀起失败，不会出现执行“⼀半”的情况。

18.2 事务(ACID)四大性质

  1. 原子性(Atomicity)：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  2. 一致性(Consistency)：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  3. 隔离性(Isolation)：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  4. 持久性(Durability)：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

18.3 事务关键词

  1. MySQL提供两种事务型存储引擎InnoDB和NDB cluster及第三方XtraDB、PBXT
  2. 事务(transaction)、回退(rollback)、提交(commit)、保留点(savepoint)
  3. 为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符，这样如果需要回退可以回退到某个占位符(保留点)
  4. 创建占位符可以使用savepoint
  5. 回退到占位符地址使用rollback to
  6. 保留点在执行rollback或者commit之后自动释放

18.4 事务4种隔离级别

  1. 设置当前事务的隔离级别
    set transaction isolation level 级别
  2. read uncommitted(未提交读)：事务中的修改即使没有，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为”脏读”
  3. reed committed(提交读)：大多数数据库系统默认的隔离级别，一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做”不可重复读“
  4. repeatable read(可重复读)：能够解决”脏读”问题，但是无法解决”幻读”，所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(mvcc)及间隙锁策略解决该问题
  5. serializable(可串行读)：强制事务串行执行，很少使用该级别
  6. 图列
    【MySQL】MySQL总结

18.5 事务日志可以帮助提高事务的效率

  1. 存储引擎在修改表的数据时只需要修改其内存拷贝再把该修改记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘
  2. 事务日志采用的是追加方式因此写日志操作时磁盘上一小块区域内的顺序IO而不像随机IO需要操作多个地方磁头所以采用事务日志的方式相对来说要快的多
  3. 事务日志持久之后内存中被修改的数据再后台可以慢慢刷回磁盘，目前大多数存储引擎都是这样实现的，通常称之为”预写式日志”修改数据需要写两次磁盘

18.6 MVCC多版本并发控制

  1. MVCC只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作，其他两个不兼容(read uncommitted:总是读取最新，serializable:所有的行都加锁)
  2. InnoDB的MVCC通过在每个记录后面保存两个隐藏的列来实现MVCC
    1. 一个列保存了行的创建时间
    2. 一个列保存了行的过期时间(或删除时间) # 本质是系统版本号
  3. 每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询的每行记录版本号进行比较
  4. 由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来。
    1. 当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了。
    2. 当前事务id要小于delete_version值，这表示事务开始之后这行记录才被删除

19. 存储过程(procedure)

19.1 存储过程概念

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。储存过程思想上很简单，就是数据库SQL语言层面的代码封装与重用

19.2 语法与操作

create procedure 存储过程名称(in/out/inout 参数名 参数类型(长度))
begin 
     SQL语句；
end;
# in:该类型参数作为输入，也就是需要调用时传入值
# out:该类型参数作为输出，也就是该参数可以作为返回值
# inout:即可以作为输入参数，也可以作为输出参数 

# 查看存储过程
show create procedure 存储过程名字;
# 查看所有存储过程
show procedure status;
# 删除储存过程
drop procedure 存储过程名字;

19.3 存储过程的优点

  1. 封装性
  2. 可增强SQL语句的功能和灵活性
  3. 可减少网络流量
  4. 高性能
  5. 提高数据库的安全性和数据的完整性

20. 内置函数

20.1 分类

  1. 从功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、回去MySQL信息函数、聚合函数等。
  2. 从入参个数角度来说，又分单行函数和多行函数
  3. 可以通过help 函数名 查看帮助信息

20.2 移除指定字符

Trim、LTrim、RTrim

20.3 大小写转换

Lower、Upper

20.4 获取左右起始指定个数字符

Left、Right

20.5 返回读音相似值(对英文效果)

Soundex

20.6 日期格式

current_date():当前日期
current_time():当前时间
current_timestamp():当前时间戳
date(datetime):返回datetime参数的日期部分
date_add(date, interval d_value_type):在date中添加日期或时间。interval后的数值单位可以是year/day/minute/second
date_sub(date, interval d_value_type): 在date中减去日期或时间。interval后的数值单位可以是year/day/minute/second
datediff(date1, date2): 计算两个日期的时间差，单位是天
now(): 当前日期时间

21. 流程控制

21.1 流程控制概念

实际开发中，解决复杂的问题是需要执行多个sql语句的，流程控制语句的作用就是用来控制存储过程中SQL语句的执行顺序。流程分为顺序结构，分支结构和循环结构，对应MySQL来说，流程控制语句主要有3类。
【MySQL】MySQL总结

21.2 if,while

# 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 7;
    END IF;

END //
delimiter ;


# 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 ;

END //
delimiter ;

22. 索引

22.1 索引概念

索引是帮助MySql高效获取数据的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。索引是数据库优化最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的MySql的性能优化问题。

22.2 性能理解

  1. 索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
  2. 让获取的数据更有目的性，从而提高数据库检索数据的性能

22.3 索引在MySQL中也叫做“键”

  1. 索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构
  2. 三种健
    1. primary key
    2. unique key
    3. index key
  3. 上述的三种键在数据查询的时候使用都可以加快查询的速度
    1. primary key, unique key 除了可以加快查询速度
    2. index key只能加快数据查询，本身没有任何的额外限制

22.3 索引优缺点

  1. 优点：大大加快数据查询速度
  2. 缺点：
    1. 维护索引需要耗费数据库资源
    2. 索引需要占用磁盘空间
    3. 当队标的数据进行增删改的时候，因为要维护索引，速度会受到影响

22.4 索引语法

# 创建索引
alter table test add index index_name(name);	
#创建索引
create index index_name on test(name);	
#查看索引	
desc table;	
#查看索引	
show index from table;	
#删除索引	
alter table test drop key index_name;		
#添加唯一性索引	
alter table student add unique key uni_xxx(xxx);	
#查看表中数据行数
select count(*) from city;
#查看去重数据行数
select count(distinct name) from city;

22.5 树

  1. BTREE:B+树索引
  2. HASH：HASH索引
  3. FULLTEXT：全文索引
  4. RTREE：R树索引
  5. 树：是一种数据结构 主要用于优化数据查询的操作
  6. 二叉树:两个分支
    B树：除了叶子节点可以有多个分支 其他节点最多只能两个分支，所有的节点都
    可以直接存放完整数据(每一个数据块是有固定大小的)
    B+树：只有叶子节点存放真正的数据 其他节点只存主键值(辅助索引值)
    B*树：在树节点添加了通往其他节点的通道 减少查询次数
  7. 二叉树
    【MySQL】MySQL总结
    【MySQL】MySQL总结
    【MySQL】MySQL总结

22.6 慢查询优化

  1. explain命令使用方法
    explain SQL语句
  2. explain命令应用
    1. 全表扫描：在explain语句结果中type为ALL
    2. 什么时候出现全表扫描?
      1. 业务确实要获取所有数据
      2. 不走索引导致的全表扫描
        1. 没索引
        2. 索引创建有问题
        3. 语句有问题
    3. 索引扫描
      1. index
      2. range
      3. ref
      4. const
      5. eq_ref
      6. system
      7. null
  3. 生产中,mysql在使用全表扫描时的性能是极其差的，所以MySQL尽量避免出现全表扫描

原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/281484.html

赞 (0)
0
上一篇 2022年8月21日
下一篇 2022年8月21日

相关推荐

发表回复

登录后才能评论