第十周学习笔记总结

总结

字符编码与配置文件

查看数据库基本信息(用户，字符编码)的命令是：\s

mysql版本为5.x系列的显示的编码有多种，如果是8.x系列，统一显示的是utf8mb4(utf8mb4是utf8优化版本，支持存储表情)

由于5.6版本编码不统一，会造成乱码，我们需要统一修改>>>:utf8

修改步骤：

步骤一:进入mysql的目录下找到my-default.ini配置文件，拷贝一份该配置文件并修改名称为my.ini。

步骤二:清空my.ini文件的内容，添加下面固定的配置信息即可。

[mysqld]
    character-set-server=utf8
    collation-server=utf8_general_ci
[client]
    default-character-set=utf8
[mysql]
    default-character-set=utf8

步骤三:保存并重启服务端即可生效，然后可以通过\s查看编码是否修改成功。

ps:偷懒操作 在配置文件中的mysql下提前写好用户名和密码 之后直接mysql登录

数据库存储引擎

我们可以简单的理解为存储引擎就是针对相同的数据采用的不同的存储策略

查看所有的存储引擎的命令: show engines;

需要掌握的存储引擎

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

ps：MySQL中默认是大小写不敏感的(忽略大小写)

创建表的完整语法

create table 表名(
    字段名1  字段类型(数字)  约束条件,
    字段名2  字段类型(数字)  约束条件,
    字段名3  字段类型(数字)  约束条件
)engine=存储引擎;

注意:

1.字段名和字段类型是必须的(至少写一个)

2.数字跟约束条件是可选的(可有可无)

3.约束条件可以写多个，空格隔开即可

4.最后一个字段的结尾千万不能加逗号

MySQL字段类型

字段类型之整型

整数类型	字节	有负号范围	无负号范围
tinyint	1bytes	-128~127	0-255
smallint	2bytes	-32768~32767	0~65535
int	4bytes	-2147483648~2147483647	0~4294967295
bigint	8bytes	-263~263-1	0~2^64-1

上述整型的区别在于从上往下能够存储的数字范围越来越大。

注意事项：

1.需要考虑正负数的问题 如果需要存储负数 则需要占据一个比特位

2.注意手机号如果使用整型来存储 需要使用bigint才可以

数字的含义:

整形字段括号里的数字并不是用来限制长度，而是用来控制展示长度。

插入的数据值超出了数据类型的范围 不应该让其插入并自动修改 没有意义
数据库应该直接报错(这个特性其实是有的 只是被我们改了>>>:配置文件)

方式1：命令临时修改
    set session sql_mode='strict_trans_tables' # 当前客户端操作界面有效
    set global sql_mode='STRICT_TRANS_TABLES' # 服务端不重启永久有效
方式2：配置文件永久修改
    步骤1：找到配置文件my.ini并打开
    步骤2：在[mysqld]下面添加
    sql_mode='STRICT_TRANS_TABLES'
    步骤3：重启服务端永久生效

字段类型之浮点型

浮点型	使用方式	说明
float	float(255,30)	总共255位，小数占30位
double	double(255,30)	总共255位，小数占30位
decimal	decimal(65,30)	总共65位，小数占30位

精确度结论：decimal>double>float。虽然三者精确度有差距，一般情况下float足够使用了，如果想追求非常完美的精确度，可以使用字符串来代替。

字段类型之字符类型

字符类型	使用方式	说明	优点	缺点
char(定长)	char(3)	最大只能存储三个字符，如果超过范围则直接报错，如果不超过范围则用空格填充至三个字符	整存整取，速度快	浪费存储空间
varchar(变长)	varchar(3)	最大只能存储三个字符，如果超过范围则直接报错，如果不超过范围则有几位就存几位	节省存储空间	存取数据的速度慢于char

验证两者的区别
    create table t12(id int, name char(4));
    create table t13(id int, name varchar(4));
1.结果验证，超出范围两者都会报错
    注意sql_mode='strict_trans_tables'
2.验证定长和变长特性
    char_length() # 统计字段数据的长度

"""
默认情况下char在存储的时候针对没有满足固定位数的字符会自动填充空格
然后在读取的时候又会自动将填充的空格移除，如果想取消该机制需要sql_mode
   set global sql_mode='strict_trans_tables,pad_char_to_full_length';
上诉目录是替换，不是新增，所以之前的配置也要写上
"""

注意：

varchar在存数据的时候会生成一个1bytes的报头，记录数据长度；varchar在取数据的时候先会读取1bytes的报头，从中获取真实数据长度。

工作中使用char还是varchar？注意根据使用场景决定使用哪个。比如:针对统一中国人的姓名应该采取那个类型varchar，规模较小，数据量相对固定的字典采用char。

字段类型之枚举与集合

枚举(enum)：多选一

create table t14(
    id int,
    name varchar(32),
    gender enum('male','female','others')
  );
insert into t14 value(1,'tom','男');  # ERROR 1265 (01000): Data truncated for column 'gender' at row 1
'''插入数据的时候 针对gender只能填写提前定义好的数值'''

集合(set)：多选多(包含多选一)

create table t15(
    id int,
    name varchar(32),
    hobbies set('read','run','rap')
);
insert into t15 value(1,'tom','rap');
insert into t15 value(1,'tom','rap,read,run');

字段类型之日期类型

日期类型	格式	示例
date	年月日	2022-08-15
datetime	年月日时分秒	2022-08-15 19:27:32
time	时分秒	19:27:38
year	年份	2022

create table t16(
    id int,
    name varchar(32),
    birth date,
    reg_time datetime,
    study_time time,
    join_time year
);
insert into t16 values(1,'jason','2022-08-15','2022-08-15 19:27:32','19:27:38','2022')

MySQL字段约束

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

无需正负号---unsigned

create table t17(id tinyint unsigned);
insert into t17 value(-1); # ERROR 1264 (22003): Out of range value for column 'id' at row 1

零填充---zerofill

create table t18(id int(3) zerofill); # 位数不够用0填充
insert into t18 values(1); # 001

非空---not null

create table t19(id int,name varchar(32));
'''插入数据的另外一种方式，打破字段顺序'''
insert into t19(name,id) values('jason',1);
insert into t19(id) values(2);

create table t20(id int,name varchar(32) not null);
insert into t20(id) values(2); # 报错
insert into t20(id,name) values(2,null); # 报错
insert into t20(id,name) values(2,''); # 不报错

默认值---default

create table t21(id int,name varchar(32) default 'jason');
insert into t21(id) values(1);  # 不传值使用默认的值
insert into t21(id,name) values(2,'kevin');  # 传值则使用传入的值

唯一值---unique

'''单列唯一:某个字段下对应的数据不能重复 是唯一的'''
create table t22(
    id int unique,
    name varchar(32) 
  );
insert into t22 value(1,'tom');
insert into t22 value(1,'bob');  # ERROR 1062 (23000): Duplicate entry '1' for key 'id'
 '''联合唯一:多个字段下对应的数据组合到一起的结果不能重复 是唯一的'''
create table t23(
    id int,
    host varchar(32),
    port int,
    unique(host,port)
  );

约束条件之主键---primary key

1.单从约束角度上而言主键等价于非空且唯一(not null unique)

2.innodb存储引擎规定一张表必须有且只有一个主键

1.如果创建的表中没有主键也没有非空且唯一的字段，那么innodb存储引擎会自动采用一个隐藏的字段作为主键(主键可以加快数据查询，比如新华字典的目录)
2.如果创建的表中没有主键但是有非空且唯一的字段，那么innodb存储引擎会自动将该字段设置为主键

3.创建表的时候都应该有一个id字段并且该字段应该作为主键

补充：

id int primary key  单列主键


sid int,
nid int,
primary key(sid,nid)  联合主键

auto_increment 自增：该约束条件不能单独使用，必须跟在键后面(主要配合主键一起使用)

自增的特点：自增的操作不会因为执行删除数据的操作而回退或者重置，只会前进，如果非要重置主键，需要格式化表

delete from 表名 where 筛选条件;
truncate 表名; # 删除表数据并重置主键值

约束条件之外键---foreign key

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

1.先写普通字段
2.然后在写外键字段

create table emp(
    id int primary key auto_increment,
    name varchar(32),
    gender enum('male','female','others') default'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
);
create table dep(
    id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(32)
);
"""
1.创建表的时候需要先创建被关联的表(没有外键)，然后再是关联表(有外键)
2.插入表数据的时候，针对外键字段只能填写被关联字段已经出现过的数据值
3.被关联字段无法修改和删除，有点不太好，操作限制性太强
"""

级联更新，级联删除

被关联数据一旦变动，关联的数据同步变动
create table emp(
    id int primary key auto_increment,
    name varchar(32),
    gender enum('male','female','others') default'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
    on update cascade # 级联更新
    on delete cascade # 级联删除
);
create table dep(
    id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(32)
);
"""
扩展:
    在实际工作中 很多时候可能并不会使用外键
      因为外键增加了表之间的耦合度 不便于单独操作 资源消耗增加
    我们为了能够描述出表数据的关系 又不想使用外键
      自己通过写SQL 建立代码层面的关系
"""

表关系之一对多

外键字段是用来记录表与表之间数据的关系，数据的关系有四种

1.一对多关系

2.多对多关系

3.一对一关系

4.没有关系

表数据关系的判定  >>>: '换位思考'
针对员工表和部门表判断数据关系
1.先站在员工表的角度
  问:一条员工数据能否对应多条部门数据
  翻:一名员工能否属于多个部门
  答:不可以
2.再站在部门表的角度
  问:一条部门数据能否对应多条员工数据
  翻:一个部门能否拥有多个员工
  答:可以
完成换位思考之后得出的答案 一个可以一个不可以
那么表关系就是"一对多",部门是一,员工是多
针对'一对多'的关系,外键字段建在多的一方 
ps:没有多对一 统一称为'一对多'

create table emp(
    id int primary key auto_increment,
    name varchar(32),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id) 
    on update cascade 
    on delete cascade
);
create table dep(
    id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(32)
);

表关系之多对多

以书籍表和作者表为例
1.先站在书籍表的角度
  问:一条书籍数据能否对应多条作者数据
  答:可以
2.再站在作者表的角度
  问:一条作者数据能否对应多条书籍数据
  答:可以
总结:两边都可以 那么表数据关系就是'多对多'
针对多对多表关系 外键字段不能建在任意一方!!!

create table book(
    id int primary key auto_increment,
    title varchar(32),
    author_id int,
    foreign key(author_id) references author(id) 
    on update cascade
    on delete cascade
);
create table author(
    id int primary key auto_increment,
    name varchar(32),
    book_id int,
    foreign key(book_id) references book(id) 
    on update cascade 
    on delete cascade
);

需要单独开设第三张关系表 存储数据关系
create table book(
    id int primary key auto_increment,
    title varchar(32)
);
create table author(
    id int primary key auto_increment,
    name varchar(32)
);
create table book2author(
    id int primary key auto_increment,
    book_id int,
    foreign key(book_id) references book(id) 
    on update cascade 
    on delete cascade,
    author_id int,
    foreign key(author_id) references author(id) 
    on update cascade 
    on delete cascade
);

表关系之一对一

以用户表和用户详情表
1.先站在用户表的角度
  问:一条用户数据能否对应多条用户详情数据
  答:不可以
2.再站在用户详情表的角度
  问:一条用户详情数据能否对应多条用户数据
  答:不可以
总结:两边都不可以 那么先考虑是不是没有关系
    如果有关系那么肯定就是'一对一'
针对'一对一'的表关系 外键字段建在任何一张表都可以，但是建议你建在查询频率较高的表中便于后续查询

create table user(
    id int primary key auto_increment,
    name varchar(32),
    detail_id int unique,
    foreign key(detail_id) references userDetail(id) 
    on update cascade
    on delete cascade
);
create table userDetail(
     id int primary key auto_increment,
     phone bigint
);

ps:一对一关系与一对多关系的唯一不同是
    一对一关系里被关联表的id要做唯一值限制(unique)

表查询关键字

操作表的SQL语句补充

1.修改表名
    alter table 表名 rename 新表名;
2.新增字段
    alter table 表名 add 字段名(数字) 约束条件;
    # 在表的最后面添加新的字段
    alter table 表名 add 字段名(数字) 约束条件 after 已存在字段;
    # 在已存在的字段后面添加新的字段
    alter table 表名 add 字段名(数字) 约束条件 first;
    # 在表的最前面添加新的字段
3.修改字段
    alter table 表名 change 旧字段 新字段 字段类型(数字) 约束条件;
    # 可以修改字段名和字段类型
    alter table 表名 modify 字段名 新的字段类型(数字) 约束条件;
    # 只能修改字段类型
4.删除字段
    alter table 表名 drop 字段名;

表查询关键字select与from

"""
SQL语句的关键字编写顺序与执行顺序是不一致的
先执行from确定表之后执行select确定字段
"""
select:自定义查询表中字段对应的数据
from:指定操作的对象(到底是哪张表，也可能是多张)

表查询关键字之where筛选

where:后面跟条件筛选出符合这些条件的数据

"""
在你刚开始解除mysql查询的时候，建议你按照查询的优先级顺序拼写出你的sql语句
先是查哪张表 from emp
再是根据什么条件去查 where name like '%o%'
再是对查询出来的数据筛选展示部分 select name,salary
"""

表查询关键字之group by分组

group by:按照一些指定的条件将单个单个的数据分为一个个整体

分组之后我们研究的对象就应该是以组为单位，不应该再直接获取单个数据，如果获取了应该直接报错，select后面可以直接填写的字段名只能是分组的依据(其他字段需要借助于一些方法才可以获取)

配合分组使用的常见聚合函数

聚合函数	功能
max	最大值
min	最小值
sum	总和
count	计数
avg	平均

表查询关键字之having过滤

where与having的功能其实是一样的，都是用来筛选数据。where是用于分组之前的筛选，而having是用于分组之后的筛选。为了更好的区分，所以叫where是筛选，having是过滤。

表查询关键字之distinct去重

distinct:去除重复的数据
去重的前提是数据必须是一模一样的才可以(如果数据有主键肯定无法去重)
比如说只查询年龄，不显示其他，年龄有重复，则可以使用distinct去重

表查询关键字之order by排序

order by: 给数据排列顺序
但是用 order by的时候，后面跟需要排序的字段，字段后面没写什么则默认升序排列，
字段后面跟关键字asc也是升序排列，asc可以省略
字段后面跟关键字desc是降序排列。
设置第二排序字段：当第一个字段排序是相等的时候，我们可以用逗号隔开后面接着写第二个排序字段用来排序。

表查询关键字之limit分页

limit：分页既是限制展示条数

select * from emp limit 0,5; # 第一个参数表示起始位置，第二个参数表示条数，不是索引位置

表查询关键字之regexp正则

select * from emp where name regexp '^j.*(n|y)$';  # regexp 正则表达式写在''里

多表查询思路

多表查询思路总共就两种：

子查询

子查询：将一张表的查询结果括号括起来当做另外一条SQL语句的条件。

连表操作

连表操作：先将所有涉及到结果的表全部拼接到一起形成一张大表，然后大表中查询数据。

关键字	名称	说明
inner join	内连接	只拼接两边都有的字段数据
left join	左连接	以左表为基准，展示所有的数据，没有对应则NULL填充
right join	右连接	以右表为基准，展示所有的数据，没有对应则NULL填充
union	全连接	左右两表数据全部展示 没有对应项则用NULL填充

多表查询解题思路

1.理清关系，先确定需要几张表
2.简单查看每张表中的数据，确定所需要的数据都在表中
3.思考查询逻辑
   子查询
   连表操作

Navicat可视化软件

Navicat可以充当很多数据库软件的客户端，提供了图形化界面能够让我们更加快速的操作数据库，并且提供操作数据库的快捷方式(鼠标点击)。

下载官网: http://www.navicat.com.cn/

内部封装了SQL语句，用户只需要鼠标点点点就可以快速操作（用鼠标点击代替了查找数据库的代码）。

查询(自己写SQL语句)：选择数据库后，点击查询，然后新建查询，就可以自己写sql语句了。

使用navicat编写SQL，如果自动补全语句，那么关键字都会变大写。SQL语句注释语法(快捷键与pycharm中的一致 ctrl+?)。注释的方法一个是#还有一个是--还有一个多行注释/**/。

python操作MySQL

第三方模块下载: pip3.8 install pymysql

1.导入模块

import pymysql

2.链接服务端

conn = pymysql.connect(
    host='127.0.0.1',  # MySQL服务端的IP地址
    port=3306,  # MySQL默认PORT地址(端口号)
    user='root',  # 用户名
    password='123',  # 密码  也可以简写 passwd
    database='db5',  # 库名称  也可以简写 db
    charset='utf8mb4'  # 字符编码 千万不要加杠utf-8
    autocommit=True  # 执行增、改、删操作自动执行conn.commit
)  # 要善于查看源码获取信息

3.产生获取命令的游标对象(等待输入命令)

cursor = conn.cursor(
      cursor=pymysql.cursors.DictCursor
  )  # 括号内不写参数数据是元组 要是元组不够精确 添加参数则会将数据处理成字典

4.编写SQL语句

# SQL语句会被高亮显示 不用惊慌
sql1 = 'show tables;'
sql2 = 'select * from userinfo;'

5.发送给服务端(执行SQL语句)

'''execute执行sql语句 会自动帮你加分号结束符 如果你没有写的话'''
affect_rows = cursor.execute(sql1)
print(affect_rows)  # 执行SQL语句之后受影响的行数

6.获取结果

print(cursor.fetchall())  # 获取结果集中所有数据
print(cursor.fetchone())  # 获取结果集中的第一条数据
print(cursor.fetchmany(3))  # 获取结果集中的指定条数的数据
'''
控制结果集中光标的移动
类似于文件光标的概念
'''
cursor.scroll(2, mode='relative')  # 基于当前位置往后移动
cursor.scroll(0, mode='absolute')  # 基于数据集开头的位置往后移动

SQL注入问题

注入问题就是利用的是MySQL注释语法及逻辑运算符，对代码的判断进行干扰。

本质：利用一些特殊符号的组合产生了特殊的含义从而逃脱了正常的业务逻辑。

问题：1.只需要用户名即可登录

2.不需要用户名和密码也能登录

解决SQL注入的问题其实也很简单，就是想办法过滤掉特殊符号。

方法：execute方法自带校验SQL注入问题，自动处理特殊符号。

sql = "select * from userinfo where name=%s and pwd=%s"
cursor.execute(sql, (username, password))  # 自动识别%s 并自动过滤各种符合 最后合并数据

# executemany方法(了解)---批量插入数据
sql = 'insert into userinfo(name,password) values(%s,%s)'
cursor.executemany(sql,[('tom',123),('lavin',321),('pony',333)])

小知识点补充

1.as语法
    给字段起别名、起表名
2.comment语法
    给表、字段添加注释信息
    create table server(id int) comment '这个server意思是服务器表'
    create table t1(
        id int comment '用户编号',
        name varchar(16) comment '用户名'
    ) comment '用户表';
    """
    查看注释的地方
    show create table 
    use information_schema
    """
3.concat、concat_ws语法
    concat用于分组之前多个字段数据的拼接
    concat_ws如果有多个字段 并且分隔符一致 可以使用该方法减少代码
4.exists语法
    select * from userinfo where exists (select * from department where id<100)
    exists后面的sql语句如果有结果那么执行前面的sql语句
    如果没有结果则不执行

视图

视图：通过SQL语句的执行得到的一张虚拟表，保存下来之后就称之为视图。

作用：如果需要频繁的使用一张虚拟表，可以考虑制作成视图，降低操作难度，方便查询。

制作视图

create view 视图名 as SQL语句;

注意：

1.在硬盘中，视图只有表结构文件，没有表数据文件

2.视图通常是用于查询，尽量不要修改视图中的数据

总结：保存的视图会占据在库中的表格里，也会占用资源；视图如果过多会造成库下的表混乱，而且库不支持增删改。

触发器

触发器：针对表数据的增，删，改自动触发的功能(增前，增后，删前，删后，改前，改后）。

作用：专门针对表数据的操作，定制个性化配套功能。

种类：表数据新增之前，新增之后；表数据修改之前，修改之后；表数据删除之前，删除之后。

触发器的创建

create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
    sql语句
end

'''
触发器的名字一般情况下建议采用下列布局形式
    tri_after_insert_t1
    tri_before_update_t2
    tri_before_delete_t3
'''

注意触发器内部的SQL语句需要用到分号 但是分号又是SQL语句默认的结束符
所以为了能够完整的写出触发器的代码 需要临时修改SQL语句默认的结束符
    delimiter $$
    编写需要用到分号的各种语句
    delimiter ;

事务

事务：事务（Transaction）是并发控制的单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。通过事务，SQL Server能将逻辑相关的一组操作绑定在一起，以便服务器保持数据的完整性。事务应用的一个广泛例子就是银行中的存取款业务。

作用：1、为数据库操作提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。

2、当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

事务属性：

事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。

1.原子性（atomicity）一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。

2.一致性（consistency）事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

3.隔离性（isolation）一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

4.持久性（durability）持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

具体使用

create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
('jason',1000),
('kevin',1000),
('tank',1000);

# 修改数据之前先开启事务操作
start transaction;

# 修改操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='kevin'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元

# 回滚到上一个状态
rollback;

# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;
"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""

# 站在python代码的角度，应该实现的伪代码逻辑，
try:
    update user set balance=900 where name='jason'; #买支付100元
    update user set balance=1010 where name='kevin'; #中介拿走10元
    update user set balance=1090 where name='tank'; #卖家拿到90元
except 异常:
    rollback;
else:
    commit;

扩展知识点

MySQL提供两种事务型存储引擎InnoDB和NDB cluster及第三方XtraDB、PBXT

事务处理中有几个关键词汇会反复出现
  事务(transaction)
  回退(rollback)
  提交(commit)
  保留点(savepoint)
    为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符，这样如果需要回退可以回退到某个占位符(保留点)
    创建占位符可以使用savepoint
      savepoint sp01;
    回退到占位符地址
      rollback to sp01;
    # 保留点在执行rollback或者commit之后自动释放


在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB支持所有隔离级别
    set transaction isolation level 级别

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


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

MVCC多版本并发控制

MVCC只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作，其他两个不兼容(read uncommitted:总是读取最新  serializable:所有的行都加锁)

InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
一个列保存了行的创建时间
  一个列保存了行的过期时间(或删除时间)  # 本质是系统版本号
每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录版本号进行比较

例如
刚插入第一条数据的时候，我们默认事务id为1，实际是这样存储的
    username    create_version    delete_version
    jason                 1
可以看到，我们在content列插入了kobe这条数据，在create_version这列存储了1，1是这次插入操作的事务id。
然后我们将jason修改为jason01，实际存储是这样的
    username    create_version    delete_version
    jason                 1                 2
    jason01               2
可以看到，update的时候，会先将之前的数据delete_version标记为当前新的事务id，也就是2，然后将新数据写入，将新数据的create_version标记为新的事务id
当我们删除数据的时候，实际存储是这样的
    username    create_version    delete_version
    jason01               2                 3
"""
由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：
   1.当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了。
   2.当前事务id要小于delete_version值，这表示在事务开始之后这行记录才被删除。
"""

存储过程

mysql的存储过程类似于python中的自定义函数。

delimiter 临时结束符
create procedure 名字(参数,参数)
begin
    sql语句;
end 临时结束符
delimiter ;
# 相当于调用函数
call 名字()

类似于有参函数

 delimiter $$
  create procedure p1(
      in m int,  # in表示这个参数必须只能是传入不能被返回出去
      in n int,
      out res int  # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
  )
  begin
      select tname from userinfo where id > m and id < n;
      set res=0;  # 用来标志存储过程是否执行
  end $$
  delimiter ;

# 针对res需要先提前定义
  set @res=10;  定义
  select @res;  查看
  call p1(1,5,@res)  调用
  select @res  查看

"""
查看存储过程具体信息
    show create procedure pro1;
查看所有存储过程
    show procedure status;
删除存储过程
    drop procedure pro1;
"""

内置函数

注意与存储过程的区别，mysql内置的函数只能在sql语句中使用！.

ps:可以通过help 函数名 查看帮助信息!

1.移除指定字符

ltrim(str):去除字符串左边的空格
rtrim(str):去除字符串右边的空格
trim(str): 去除字符串两边的空格

SELECT TRIM("    RUNOOB       ");

2.大小写转换

Lower(str):将字符串str的所有字母转换成小写字母
Upper(str):将字符串str的所有字母转换成大写字母

SELECT UPPER('hello world!');

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

Left(str,length):返回具有指定长度的字符串的左边部分
right(str,length):返回具有指定长度的字符串的右边部分

select left('hello world',7);

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

Soundex(str):返回读音和str相似的值

eg:客户表中有一个顾客登记的用户名为J.Lee
    但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的
where Soundex(name)=Soundex('J.Lie')

5.日期格式:date_format

ps:在MySQL中表示时间格式尽量采用2022-11-11形式。

CREATE TABLE blog (
    id INT PRIMARY KEY auto_increment,
    NAME CHAR (32),
    sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
    ('第1篇','2015-03-01 11:31:21'),
    ('第2篇','2015-03-11 16:31:21'),
    ('第3篇','2016-07-01 10:21:31'),
    ('第4篇','2016-07-22 09:23:21'),
    ('第5篇','2016-07-23 10:11:11'),
    ('第6篇','2016-07-25 11:21:31'),
    ('第7篇','2017-03-01 15:33:21'),
    ('第8篇','2017-03-01 17:32:21'),
    ('第9篇','2017-03-01 18:31:21');

# 查询每个月的篇数
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
# 查询某一天的日记
select * from blog where Date(sub_time) = '2015-03-01';
# 查看某个月的日记
select * from blog where Year(sub_time)=2016 AND Month(sub_time)=07;

# 更多日期处理相关函数
    adddate 增加一个日期
    addtime 增加一个时间
    datediff 计算两个日期差值

流程控制

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

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

索引

索引就是一种数据结构，类似于书的目录。意味着以后再查数据应该先找目录再找数据，而不是用翻页的方式查询数据。

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。

primary key  主键   非空且唯一
unique key  唯一键  唯一
index key  索引建   无约束条件

上面三种key前两种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一，unique key:唯一)，而index key没有任何约束功能只会帮你加速查询。

索引的基本用法

比如说一个表里有id，name，pwd，post_comment，addr，age等字段。基于id查找数据很快，但是基于addr查找数据就很慢。

解决的措施：可以是给addr添加索引

ps：索引虽然好用 但是不能无限制的创建!!!

索引的影响：

• 在表中有大量数据的前提下，创建索引速度会很慢

• 在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低

问题：

比如一个表有一个字段，想经常用这个字段查数据但是很慢，有没有什么方式解决？

可以将这个字段添加成索引但是索引不能添加的过多，添加的过多的话会影响表数据增，删，改的操作。

索引相关概念

聚集索引(primary key)

指的就是表的主键，innodb引擎规定一张表中必须要有主键

特点：叶子结点放的一条条完整的记录

辅助索引(unique key,index key)

查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引

特点：叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如:按照name字段创建索引，那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})
数据查找 如果一开始使用的是辅助索引 那么还需要使用聚焦索引才可以获取到真实数据

覆盖索引

只在辅助索引的叶子节点中就已经找到了所有我们想要的数据

select name from user where name='jason';

非覆盖索引

虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找

select age from user where name='jason';

前缀索引

根据字段的前N个字符建立索引

alter table test add index idx_name(name(10));

避免对大列建索引，如果有，就使用前缀索引

联合索引

多个字段建立一个索引
特点：前缀生效特性

原则：把最常用来做为条件查询的列放在最前面
#创建people表
    create table people (id int,name varchar(20),age tinyint,money int ,gender enum('m','f'));
#创建联合索引
    alter table people add index idx_gam(gender,age,money);

索引底层原理

树: 是一种数据结构 主要用于优化数据查询的操作

索引的底层数据结构是b+树

二叉树:两个分支
B树(B-树)、B+树、B*树
    B树:
        除了叶子节点可以有多个分支 其他节点最多只能两个分支
        所有的节点都可以直接存放完整数据(每一个数据块是有固定大小的)
    B+树:
        只有叶子节点存放真正的数据 其他节点只存主键值(辅助索引值)
    B*树
        在树节点添加了通往其他节点的通道 减少查询次数

慢查询优化

explain命令使用方法

mysql> explain select name,countrycode from city where id=1;

explain命令应用

查询数据的方式

1.全表扫描

1）在explain语句结果中type为ALL

2）什么时候出现全表扫描?

1. 业务确实要获取所有数据

2.不走索引导致的全表扫描
  1. 没索引
  2. 索引创建有问题
  3. 语句有问题

生产中,mysql在使用全表扫描时的性能是极其差的，所以MySQL尽量避免出现全表扫描

2.索引扫描

常见的索引扫描类型:
1.index
2.range
3.ref
4.eq_ref
5.const
6.system
7.null

从上到下，性能从最差到最好，我们认为至少要达到range级别

index：Full Index Scan，index与ALL区别为index类型只遍历索引树。
range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。

mysql> alter table city add index idx_city(population);
mysql> explain select * from city where population>30000000;

ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。

mysql> alter table city drop key idx_code;
mysql> explain select * from city where countrycode='chn';
mysql> explain select * from city where countrycode in ('CHN','USA');
mysql> explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件A

join B
on A.sid=B.sid

const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。

如将主键置于where列表中，MySQL就能将该查询转换为一个常量

mysql> explain select * from city where id=1000;

NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

mysql> explain select * from city where id=1000000000000000000000000000;

Extra（扩展）
Using temporary
Using filesort 使用了默认的文件排序（如果使用了索引，会避免这类排序）
Using join buffer

如果出现Using filesort请检查order by ,group by ,distinct,join 条件列上没有索引

mysql> explain select * from city where countrycode='CHN' order by population;

当order by语句中出现Using filesort，那就尽量让排序值在where条件中出现

mysql> explain select * from city where population>30000000 order by population;
mysql> select * from city where population=2870300 order by population;

key_len: 越小越好

前缀索引去控制

rows: 越小越好