MySQL数据库

数据存取方式的演变

1.文本文件
文件路径不固定:C:\aaa.txt D:\bbb.txt E:\ccc.txt
数据格式不统一:jason|123 jason$123 jason 123

2.软件开发目录规范
规定了数据应该保存在db目录下>>>:路径偏向统一
db/user.txt db/userinfo.txt db/jason.json db/jason
数据格式还是没有得到统一
文本:json格式对象

3.数据库服务⭐
统一路径,统一操作方式
降低学习成本,提高开发效率

数据库软件应用演变

1.单机游戏
数据存储于各个计算机的本地，无法共享
2.网络游戏
数据存储于网络中，可以共享(数据库服务)

数据库服务集群:提升数据的安全性
数据库集群，顾名思义，就是利用至少两台或者多台数据库服务器，构成一个虚拟单一数据库逻辑映像，像单数据库系统那样，向客户端提供透明的数据服务

数据库的本质

站在底层原理的角度:
数据库指的是操作数据的进程(一堆代码)
站在实际应用的角度:
数据库指的是可视化操作界面(一些软件)

ps:不做特殊说明的情况下讲数据库其实指的是数据库软件

数据库软件本质也是CS架构的程序
意味着所有的程序员其实都有资格编写一款数据库软件

数据库分类

〓关系型数据库介绍〓

1、关系型数据库的由来

虽然网状数据库和层次数据库已经很好的解决了数据的集中和共享问题，但是在数据库独立性和抽象级别上扔有很大欠缺。用户在对这两种数据库进行存取时，仍然需要明确数据的存储结构，指出存取路径。而关系型数据库就可以较好的解决这些问题。

2、关系型数据库介绍

关系型数据库模型是把复杂的数据结构归结为简单的二元关系（即二维表格形式）。在关系型数据库中，对数据的操作几乎全部建立在一个或多个关系表格上，通过对这些关联的表格分类、合并、连接或选取等运算来实现数据库的管理。

特征1:拥有固定的表结构(字段名，字段类型)

特征2:数据之间可以建立数据库层面关系

关系型数据库诞生40多年了，从理论产生发展到现实产品，例如：Oracle和MySQL，Oracle在数据库领域上升到霸主地位，形成每年高达数百亿美元的庞大产业市场。

3、关系型数据库表格之间的关系举例

关系型数据库举例:

MySQL、Oracle、MariaDB、PostgreSQL、sql server、sqlite、db2、access

〓非关系型数据库介绍〓

1、非关系型数据库诞生背景

NoSQL，泛指非关系型的数据库。随着互联网web2.0网站的兴起，传统的关系数据库在应付web2.0网站，特别是超大规模和高并发的SNS类型的web2.0纯动态网站已经显得力不从心，暴露了很多难以克服的问题，而非关系型的数据库则由于其本身的特点得到了非常迅速的发展。NoSql数据库在特定的场景下可以发挥出难以想象的高效率和高性能，它是作为对传统关系型数据库的一个有效的补充。

NoSQL(NoSQL = Not Only SQL )，意即“不仅仅是SQL”，是一项全新的数据库革命性运动，早期就有人提出，发展至2009年趋势越发高涨。NoSQL的拥护者们提倡运用非关系型的数据存储，相对于铺天盖地的关系型数据库运用，这一概念无疑是一种全新的思维的注入。

特征1:没有固定的表结构,数据存储采用K:V键值对的形式
{'name':'jason'}
{'username':'kevin','pwd':123}
特征2:数据之间无法建立数据库层面的关系
可以自己编写代码建立逻辑层面的关系

2、非关系型数据库种类

（1）键值存储数据库（key-value）

键值数据库就类似传统语言中使用的哈希表。可以通过key来添加、查询或者删除数据库，因为使用key主键访问，所以会获得很高的性能及扩展性。

键值数据库主要使用一个哈希表，这个表中有一个特定的键和一个指针指向特定的数据。Key/value模型对于IT系统来说的优势在于简单、易部署、高并发。

典型产品：Memcached、Redis(目前最火,使用频率最高的非关系型数据库(缓存数据库),虽然缓存数据库是基于内存做数据存取但是拥有持久化的功能)、MemcacheDB

（2）列存储（Column-oriented）数据库

列存储数据库将数据存储在列族中，一个列族存储经常被一起查询的相关数据，比如人类，我们经常会查询某个人的姓名和年龄，而不是薪资。这种情况下姓名和年龄会被放到一个列族中，薪资会被放到另一个列族中。

这种数据库通常用来应对分布式存储海量数据。

典型产品：Cassandra、HBase

（3）面向文档（Document-Oriented）数据库

文档型数据库的灵感是来自于Lotus Notes办公软件，而且它同第一种键值数据库类似。该类型的数据模型是版本化的文档，半结构化的文档以特定的格式存储，比如JSON。文档型数据库可以看作是键值数据库的升级版，允许之间嵌套键值。而且文档型数据库比键值数据库的查询效率更高。

面向文档数据库会将数据以文档形式存储。每个文档都是自包含的数据单元，是一系列数据项的集合。每个数据项都有一个名词与对应值，值既可以是简单的数据类型，如字符串、数字和日期等；也可以是复杂的类型，如有序列表和关联对象。数据存储的最小单位是文档，同一个表中存储的文档属性可以是不同的，数据可以使用XML、JSON或JSONB等多种形式存储。

典型产品：MongoDB(最像关系型数据库的非关系型数据库)、CouchDB

（4）图形数据库

图形数据库允许我们将数据以图的方式存储。实体会被作为顶点，而实体之间的关系则会被作为边。比如我们有三个实体，Steve Jobs、Apple和Next，则会有两个“Founded by”的边将Apple和Next连接到Steve Jobs。

典型产品：Neo4J、InforGrid

虽然数据库软件有很多,但是操作方式相差不多,学会了一个几乎就可以学会所有，其中以MySQL最为典型

MySQL简介

1.版本问题
8.0:最新版
5.7:使用频率较高
5.6:学习推荐使用
ps:站在开发的角度使用哪个版本学习都没有关系

2.下载流程

1.访问官网
2.点击DOWNLOADS并点击GPL
3.点击community server
4.点击Archives
5.选择对应系统的对应版本下载(zip压缩包)

3.主要目录介绍
bin目录
存放启动文件
mysqld.exe(服务端)

mysql.exe(客户端)
data目录
存放核心数据
my-default.ini
默认的配置文件
readme
软件说明

MySQL基本使用

cmd建议你使用管理员身份打开

1.切换到mysql的bin目录下先启动服务端
mysqld
2.保持窗口不关闭，重新打开一个新的cmd窗口
3.切换到mysql的bin目录下启动客户端
mysql
"""
直接使用mysql命令默认是游客模式，权限和功能都很少
mysql -u用户名 -p密码

管理员默认没有密码连续回车即可
mysql -uroot -p

制作系统服务

1.先把bin目录添加到环境变量
清空之前打开的cmd窗口一定要把之前用cmd启动的服务端关闭(ctrl+c)
2.将mysql添加到系统服务中
1.如何查看系统服务
鼠标右键任务栏选择服务
cmd输入services.msc回车
2.以管理员身份打开cmd窗口
mysqld --install
3.首次添加不会自动启动需要人为操作一下
1.鼠标右键点击启动
2.命令行启动
net start mysql
"""
如果想卸载重新按照
1.先关闭服务端
net stop mysql
2.移除系统服务
mysqld --remove
"""

MySQL密码操作

1.修改密码
方式1:mysqladmin
mysqladmin -u用户名 -p原密码 password 新密码
方式2:直接修改存储用户数据的表
方式3:冷门操作有些版本可能还不支持
set password=password('新密码')，修改当前登录用户的密码

2.忘记密码
方式1:卸载重新装
方式2:把data目录删除拷贝同桌的目录
方式3:小把戏操作
1.关闭正常的服务端
2.以跳过授权表的方式重启服务端(不校验密码)
3.以管理员身份进入然后修改mysql.user表数据即可
net stop mysql
mysqld --skip-grant-table
mysql -uroot -p
update mysql.user set password=password('123') where Host='localhost' and User='root';
4.关闭服务端,然后以正常方式启动即可

SQL与NoSQL

数据库服务端是可以服务多种类型的客户端
客户端可以是自己开发的,也可以是python代码编写,也可以是java代码编写

SQL
操作关系型数据库的语言
NoSQL
操作非关系型数据库的语言
ps:要想跟数据库交互就必须使用数据库指定的语言

"""
SQL有时候也指代关系型数据库
NoSQL有时候也指代非关系型数据库
"""

数据库有关概念

"""
强调:为了方便的理解，采用以下方法类比学习，但是本质是是有区别的
"""
库就相当于是文件夹
表就相当于是文件夹里面的文件
记录就相当于是文件夹里面的文件中的一行行数据

验证
1.查看所有的库名称
show databases;
2.查看所有的表名称
show tables;
3.查看所有的记录
select * from mysql.user;

基本SQL语句

1.sql语句必须以分号结尾
2.sql语句编写错误之后不用担心，可以直接执行报错即可

'''基于库的增删改查'''

1.创建库
create database 库名;
2.查看库
show databases; 查看所有的库名称
show create database 库名; 查看指定库信息
3.编辑库
alter database 库名 charset='utf8';
4.删除库
drop database 库名;

'''基于表的增删改查'''

操作表之前需要先确定库
create database db1;
切换操作库
use db1;
1.创建表
create table 表名(字段名字段类型,字段名字段类型);
2.查看表
show tables; 查看库下所有的表名称
show create table 表名; 查看指定表信息
describe 表名; 查看表结构
desc 表名;
ps:如果想跨库操作其他表只需要在表名前加库名即可
desc mysql.user;
3.编辑表
alter table 表名 rename 新表名;
4.删除表
drop table 表名;

'''基于记录的增删改查'''

1.插入数据
insert into 表名 values(数据值1,数据值2);
2.查询数据
select * from 表名; 查询表中所有的数据
3.编辑数据
update 表名 set 字段名=新数据 where 筛选条件;
4.删除数据
delete from 表名;
delete from 表名 where id=2;

字符编码与配置文件

查看MySQL相关信息的方法(\s)

在登陆进入MySQL之后使用

在该页面可以看到MySQL的当前用户、版本、编码、端口号

MySQL5.6及之前的版本编码需要人为统一，之后的版本已经全部默认统一

如果想要永久修改编码配置，需要操作配置文件，在修改配置文件时不可以直接修改源文件
默认的配置文件是my-default.ini
拷贝上述文件并重命名为my.ini

将已经编写好的配置信息直接拷贝到我们复制的配置文件中
[mysqld]
    character-set-server=utf8mb4
    collation-server=utf8mb4
[client]
    default-character-set=utf8mb4
[mysql]
    default-character-set=utf8mb4
[mysqld]修改服务端配置

修改了配置文件中关于[mysqld]的配置,需要重启服务端

[client]修改第三方客户端配置，也就是我们用代码编写的客户端

[mysql]修改mysql自带的客户端配置
1.utf8mb4能够存储表情
	功能更强大
2.utf8与utf-8是有区别的
	MySQL中只有utf8
我们还可以利用配置文件将用户名以及密码写在配置文件内，这样就可以实现再使用MySQL时不需要登录即可直接进入界面
[mysql]
	user='root'
	password=123

数据库存储引擎

存储引擎可以理解为在数据库中对数据存取采用的多种不同方式

MySQL中查看存储引擎最为常见的方法为登录后输入下列代码
show engines;
需要我们了解的四个存储引擎:
create database db2;
 	use db2;
	create table t1(id int) engine=innodb;
 	create table t2(id int) engine=myisam;
  	create table t3(id int) engine=memory;
 	create table t4(id int) engine=blackhole;
MyISAM
  MySQL5.5之前默认的存储引擎
  存取数据的速度快，但是功能较少，安全性较低
InnoDB

MySQL5.5之后默认的存储引擎
支持事务、行锁、外键等操作，存取速度没有MyISAM快，但是安全性更高

Memory

基于内存存取数据，仅用于临时表数据存取

BlackHole

任何写入进去的数据都会立刻丢失

这四种引擎的存取数据的方式不同，他们各自生成标文件后的文件夹内容也不同
'''
	1.innodb两个文件
		.frm	表结构
		.ibd	表数据(表索引)
	2.myisam三个文件
		.frm	表结构
		.MYD	表数据
		.MYI	表索引
	3.memory一个文件
		.frm	表结构
	4.blackhole一个文件
		.frm	表结构
'''
PS:在MySQL中默认是不区分大小写的

创建表的完整语法
create table 表名(
	字段名 字段类型(数字) 约束条件,
	字段名 字段类型(数字) 约束条件,
 	字段名 字段类型(数字) 约束条件
);
PS:

1.字段名和字段类型是必须的
2.数字和约束条件是可选的
3.约束条件也可以写多个,空格隔开即可
4.最后一行结尾不能加逗号

MySQL常见字段类型

字段类型之整型
tinyint			1bytes	正负号(占1bit)
smallint		2bytes	正负号(占1bit)
int			    4bytes	正负号(占1bit)
bigint			8bytes	正负号(占1bit)
验证整型默认是否携带正负号
create table t5(id tinyint);
insert into t5 values(-129),(128);
select * from t5;
结果是-128和127,也就意味着MySQL的整型默认自带正负号

取消正负号的方式
create table t6(id tinyint unsigned);
insert into t6 values(-129),(128),(1000);
select * from t6;
⭐MySQL中的严格模式

当我们在使用数据库存储数据的时候，如果数据不符合规范，应该直接报错而不是擅自修改数据，这样会导致数据的失真(没有实际意义)

在这里没有报错的原因在于修改配置文件时，该设置被我们删除掉了

查看是否为严格模式
show variables like '%mode%';
修改严格模式的方法

临时修改
set session sql_mode='strict_trans_tables';
"只在当前客户端有效，客户端重启后失效"
set global sql_mode='strict_trans_tables';
"只在当前服务端有效，服务端重启后失效"
永久修改

在配置文件中修改，添加下列代码
sql_mode=STRICT_TRANS_TABLES 
⭐定义字段类型时括号中数字的意义

数字在很多地方都是用来表示限制存储数据的长度
但是在整型中数字却不是用来限制存储长度
create table t12(id int(3));  
"不是用来限制长度"
insert into t12 values(12345);
select * from t12;

create table t13(id int(5) zerofill); 
"而是用来控制展示的长度"
insert into t13 values(123),(123456789);
select * from t13;
字段类型之浮点型
MySQL中的三中浮点型的区别在于精度不同

float < double < decimal
float(20,10)
	'总共存储20位数 小数点后面占10'
double(20,10)
	'总共存储20位数 小数点后面占10'
decimal(20,10)
	'总共存储20位数 小数点后面占10'
精确度验证
create table t7(id float(60,20));
create table t8(id double(60,20));
create table t9(id decimal(60,20));
insert into t7 values(1.11111111111111111111);
insert into t8 values(1.11111111111111111111);
insert into t9 values(1.11111111111111111111);
select * from t7;
select * from t8;
select * from t9;
字段类型之字符类型
字符类型分为char与varchar两种

char
固定长度字符，简称定长
char(4) 最多存储四个字符，超出就报错，不够四个空格填充至四个

varchar
可变长度字符
varchar(4) 最多存储四个字符，超出就报错，不够则有几位存几位
create table t10(id int, name char(4));
create table t11(id int, name varchar(4));
insert into t10 values(1, 'ha');
insert into t11 values(1, 'ha');
select * from t10;
select * from t11;
在验证时我们使用char_length()获取字段存储的数据长度后，发现存入和取出来的数据长度一样，这是因为默认情况下MySQL针对char的存储会自动填充空格和删除空格

我们也可以对这个操作采取严格模式
set global sql_mode='strict_trans_tables,pad_char_to_full_length';
两种方式的优缺点

char
优势:整存整取，速度很快

劣势:浪费存储空间

varchar
优势:节省存储空间

劣势:存取数据的速度较char慢

varchar存取速度慢的原因:

char采用整存整取的方式，所以即使当所有数据是连在一起时，char也可以按照存入的数量进行切割，依旧可以获得原本的数据，但是对于varchar来讲，有多少数据存多少数据，但是当数据全部连在一起存放，想获取原本的数据就需要进行一些额外的操作，这个操作类似于我们在TCP中学到的报头，varchar会在数据的开头用类似报头的一段数据来划分数据，这样在存和取的时候就都需要先处理报头，降低了效率但是保障了数据安全
字段类型之枚举与集合
枚举enum
create table t15(
       id int,
       name varchar(32),
       gender enum('male','female','others')
    );
 	insert into t15 values(1,'tony','猛男');
  	insert into t15 values(2,'jason','male');
 	insert into t15 values(3,'kevin','others');
枚举的作用就是在定义表的时候，提供几个选项作为之后要添加数据的参考，添加数据时只能使用其中一个进行添加

集合set
create table t16(
       id int,
       name varchar(16),
       hobbies set('basketabll','football','doublecolorball')
    );
 	insert into t16 values(1,'jason','study');
 	insert into t16 values(2,'tony','doublecolorball');
	insert into t16 values(3,'kevin','doublecolorball,football');
集合的作用也是在定义表时提供几个选项作为添加数据时的参考，但是在添加数据时，可以选择其中的一个或多个进行添加
字段类型之日期类型
datetime 年月日时分秒
date 年月日
time 时分秒
year 年
create table t17(
	id int,
  	name varchar(32),
 	register_time datetime,
 	birthday date,
 	study_time time,
 	work_time year
 );
insert into t17 values(1,'jason','2000-11-11 11:11:11','1998-01-21','11:11:11','2000');
select * from t17;
在实际使用中，有关时间的字段一般由系统自动获取，不需要刻意的人为操作
字段约束条件
无符号(unsigned)及零填充(zerofill)

unsigned即取消数字的正负号

zerofill即在输入的数据不足位数时用0进行填充
unsigned
id int unsigned
zerofill
id int(5) zerofill
非空(not null)

not null也就是限定在变种添加记录时，经过处理的选项不可以为空，必须进行传值，所有字段类型不加约束条件的情况下默认都可以为空
create table t1(
id int,
name varchar(16)
);
insert into t1(id) values(1);
insert into t1(name) values('jason');
insert into t1(name,id) values('kevin',2);
添加条件后
create table t2(
id int,
name varchar(16) not null
);
insert into t2(id) values(1);
insert into t2(name) values('jason');
insert into t2 values(1,'');
insert into t2 values(2,null);
默认值(default)

default即在定义表的字段名时，可以将一些字段名设定默认的值，如果在添加记录时没有添加该字段名的值，那么展示表中数据时会以默认值展示
create table t3(
id int default 666,
name varchar(16) default '匿名'
);
insert into t3(id) values(1);
insert into t3(name) values('jason');
insert into t3 values(2,'kevin');
唯一值(unique)

单列唯一，只在一列数据中添加唯一属性，该列数据中不允许出现重复数据，其他列的数据依旧保持原样
create table t4(
id int unique,
name varchar(32) unique
);
insert into t4 values(1,'jason'),(2,'jason');
联合唯一，设置多列数据都具有唯一属性，具有唯一属性的列填充的数据中不允许出现重复数据
create table t5(
id int,
ip varchar(32),
port int,
unique(ip,port)
);
insert into t5 values(1,'127.0.0.1',8080),(2,'127.0.0.1',8081),(3,'127.0.0.2',8080);
insert into t5 values(4,'127.0.0.1',8080);
主键(primary key)

primary key的作用在于加快数据查询速度，单从约束层面上而言主键相当于not null + unique(非空且唯一)
create table t6(
id int primary key,
name varchar(32)
);
insert into t6(name) values('jason');
insert into t6 values(1,'kevin');
insert into t6 values(1,'jerry');
2.InnoDB存储引擎规定了所有的表都必须有且只有一个主键(主键

是组织数据的重要条件并且主键可以加快数据的查询速度)

1.当表中没有主键也没有其他非空切唯一的字段的情况下

InnoDB会采用一个隐藏的字段作为表的主键隐藏意味着无法使

用,基于该表的数据查询只能一行行查找速度很慢
2.当表中没有主键但是有其他非空且唯一的字段,那么会从上往

下将第一个该字段自动升级为主键
create table t7(
id int,
	age int not null unique,
 	phone bigint not null unique,
 	birth int not null unique,
	height int not null unique
);
当我们创建表时，应当有一个字段标识数据的唯一属性，这个字段我们建议使用ID字段，也就是我们所说的编号
create table userinfo(
id int primary key,
);
自增(auto_increment)

MySQL中自增是不允许单独使用的，且一张表中只能有一列带自增属性的数据，一般与主键一起搭配使用
create table t8(
	id int primary key,
 	name varchar(32)
);

create table t9(
	id int primary key auto_increment,
 	name varchar(32)
);
自增的固有特性:

自增不会因为数据的删除而回退，永远自增往前
如果自己设置了更大的数，则之后按照更大的往前自增

如果想重置某张表的主键值可以使用
truncate 表名; 清空表数据并重置主键

外键(foreign key)

外键引入

当我们需要存储员工信息时，表格中除了员工个人信息之外，一般还需要带有员工的部门相关信息
id	name	age		dep_name	dep_desc
这样子对于我们而言，一些具体数据所指代的内容并不够明确，无法确定这张表究竟是员工信息还是部门信息

而且数据堆叠也会造成存储空间的浪费

即使以上两点都无关紧要，我们也无法忽视这种存储方式极低的数据可扩展性，当我们需要修改某一项时，就需要修改所有内容

那么为了便于管理我们可以将表内的数据分别放在两个不同的表内，分为员工信息和部门信息
id	name	age
  
id	dep_name dep_desc
这样虽然解决了上述的问题，但是又不符合我们对员工信息与部门信息之间必须相关联的要求，那么这个时候我们就需要用到外键来解决这个问题

而在SQL语句中，外键字段就是用来标识数据与数据之间关系(也叫作表关系)的字段

关系的判断

一对多

多对多

一对一

没有关系

所有的表关系可以分为以上四种

对于关系的判断我们可以用画图的方式来模拟换位思考

一对多关系

以员工表和部门表为例

1.先站在员工表的角度

问:一名员工能否对应多个部门

答:不可以

2.再站在部门表的角度

问:一个部门能否对应多名员工

答:可以

结论:一个可以一个不可以，那么关系就是'一对多'

针对'一对多'关系，外键字段建在'多'的一方

在建立外键字段之前，我们可以先定义普通的表，然后考虑添加外键字段
create table emp(
	id int primary key auto_increment,
 	name varchar(32),
 	age int,
 	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(64)
需要注意的点:

1.创建表的时候一定要先创建被关联表
2.录入表数据的时候一定要先录入被关联表
3.修改数据的时候外键字段无法修改和删除

如果在表中的数据需要修改，我们可以使用级联更新(on update cascade)与级联删除(on delete cascade)
create table emp1(
	 id int primary key auto_increment,
 	 name varchar(32),
 	 age int,
 	 dep_id int,
	 foreign key(dep_id) references dep1(id) 
    on update cascade 
    on delete cascade
);

create table dep1(
	id int primary key auto_increment,
 	dep_name varchar(32),
  	dep_desc varchar(64)
);
我们的编程过程一般是将问题拆分或者将数据拆分，称为解耦合，但是外间的操作起到的是强耦合的作用，与解耦合相悖，所以在实际应用中，我们不会使用外键，而是用代码建立逻辑层面的关系
多对多关系

以书籍表与作者表为例

1.先站在书籍表的角度

问:一本书能否对应多个作者

答:可以

2.再站在作者表的角度

问:一个作者能否对应多本书

答:可以
create table book(
	id int primary key auto_increment,
 	title varchar(32),
  	price float(5,2)
);
create table author(
	id int primary key auto_increment,
 	name varchar(32),
  	phone bigint
);
create table book2author(
	id int primary key auto_increment,
author_id int,
 	foreign key(author_id) references author(id)
  	on update cascade
  	on delete cascade,
book_id int,
  	foreign key(book_id) references book(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
);
结论:两个都可以关系就是'一对一'或者没有关系

针对'一对一'外键字段建在任何一方都可以，但是推荐建在查询频率较高的表中
MySQL中的数据查询

SQL语句中常用数据查询关键字
select
	指定需要查询的字段信息
select *
	查所有字段
select name 							
	查name字段
select char_length(name)  
	支持对字段做处理
from
	指定需要查询的表信息
from mysql.user
from t1

SQL语句中关键字的执行顺序和编写顺序并不是一致的
可能会错乱
eg:
select id,name from userinfo;
正常我们在查找数据时，编写顺序时先写select
然后是from，但是在代码执行时会先执行from

对于这种现象我们无需耗费太多时间与精力
目前我们只需要关注关键字的功能及用法
到后期关键字使用熟练后，也就可以流畅地使用了
对于编写顺序和执行顺序也会有更进一步的理解
示例数据准备

对于数据查询，首先我们需要建立数据，再在来验证数据查询中各个关键字的功能
create table emp(
  id int primary key auto_increment,
  name varchar(20) not null,
  gender enum('male','female') not null default 'male', #大部分是男的
  age int(3) unsigned not null default 28,
  hire_date date not null,
  post varchar(50),
  post_comment varchar(100),
  salary double(15,2),
  office int, #一个部门一个屋子
  depart_id int
);

#插入记录
#三个部门：教学，销售，运营
insert into emp(name,gender,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','浦东第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);
"
SQL语句编写时的一些技巧
1.针对select后面的字段名可以先用*作为占位符，最后再回来修改，因为在实际使用时，如果数据量很大的话，*查询所有数据，就会严重浪费数据库资源，占用内存空间
任何代码与指令的编写都不是一蹴而就的，对于在后期可能会出现的一些情况，我们应当在前期留有余地，方便我们对代码的优化与改进
"
查询关键字实例(基于我们创建的表)
查询关键字之筛选(where)

SQL语句是支持逻辑运算符(and，or，not)\成员运算符(in，not in)\身份运算符(is，not is)的，所以当我们编写SQL语句时也可以当做筛选条件适用
# 1.查询id大于等于3小于等于6的数据
select * from emp where id >= 3 and id <= 6;
select * from emp where id between 3 and 6;

# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=18000 or salary=17000;
select * from emp where salary in (20000,18000,17000);

# 3.查询id小于3大于6的数据
select * from emp where id<3 or id>6;
select * from emp where id not between 3 and 6;

# 4.查询员工姓名中包含字母o的员工姓名与薪资
对于查询条件不够明确的查询方式，我们称之为模糊查询，常用关键字为like
模糊查询的常用符号 
%:匹配任意个数的任意字符
eg: 
%o%	 o jason owen loo wwoww
%o	 o	asdasdo asdo
_:匹配单个个数的任意字符
_o_	 aox	wob iok 
o_	 oi ok ol
select * from emp where name like '%o%';

# 5.查询员工姓名是由四个字符组成的员工姓名与其薪资
select * from emp where name like '____'; # 查询任意为四个字符的数据
select * from emp where char_length(name) = 4;

# 6.查询岗位描述为空的员工名与岗位名，针对null不能用等号，只能用is
select * from emp where post_comment=NULL;  
# 会直接报错，不可以使用
select * from emp where post_comment is NULL;可以
"
MySQL中也有其独有的内置函数与内置方法，我们可以使用help+方法名称的方式查看使用说明
"
查询关键字之分组(group by)

分组在使用时会按照指定条件将数据划分为一个一个的整体

例如我们的五十六个民族，男女性别等等

而分组的主要目的是为了更方便的对数据进行统计操作，例如计算比例，计算平均数等

max\min\sum\avg\count
最大值\最小值\求和\平均值\计数

这样的函数我们称之为聚合函数，主要是对分组后的数据进行统计操作的
1.将员工数据按照部门分组
select * from emp group by post;
"""
MySQL5.6默认不会报错
set global sql_mode='strict_trans_tables,only_full_group_by'
MySQL5.7及8.0默认都会直接报错
原因是分组之后，select后面默认只能直接填写分组的依据，不能再写其他字段
select post from emp group by post; √

select age from emp group by age; ×	
分组之后默认的最小单位就应该是组，而不应该再是组内的单个数据或单个字段
"""
2.获取每个部门的最高工资 
'''
对于分组，我们可以根据具体的需求来考虑要不要采取分组操作，当需求中需要进行一些统计操作时，自然也就需要我们采用分组来进行操作
'''
select post,max(salary) from emp group by post;

select post as '部门',max(salary) as '最高薪资' from emp group by post;
3.一次获取部门薪资相关统计
select post,max(salary) '最高薪',min(salary) '最低薪',avg(salary) '平均薪资',sum(salary) '月支出' from emp group by post;
4.统计每个部门的人数
select post,count(id) from emp group by post;
5.统计每个部门的部门名称以及部门下的员工姓名
'''
当分组完成后，查找数据时select后只能使用分组依据进行查找，而当我们需要对分组之外的数据进行操作时，就需要用聚合函数进行操作了
'''
select post,name from emp group by post;
select post,group_concat(name) from emp group by post;
select post,group_concat(name,age) from emp group by post;
select post,group_concat(name,'|',age) from emp group by post;
select post,group_concat(name,'_NB') from emp group by post;
select post,group_concat('DSB_',name,'_NB') from emp group by post;
查询关键字之过滤(having)

having的作用与where的作用类似，都是用来筛选数据的，区别在于where是在分组前使用，进行第一次筛选，having是在分组之后使用，进行第二次筛选
1.统计各部门年龄在30岁以上的员工平均工资 并且保留大于10000的数据
'''
SQL语句的本质也属于是代码，所以当需要编写一些复杂的SQL语句时，我们也可以像编写代码时一样，先写总体框架，然后逐步完善需求
每条SQL的结果可以直接看成就是一张表，基于该表如果还想继续操作则直接在产生该表的SQL语句上添加即可
'''
步骤1:先筛选出所有年龄大于30岁的员工数据
select * from emp where age > 30;
步骤2:再对筛选出来的数据按照部门分组并统计平均薪资
select post,avg(salary) from emp where age > 30 group by post;
步骤3:针对分组统计之后的结果做二次筛选
select post,avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;
查询关键字之去重(distinct)

去重的使用与Python中的集合自带的去重效果类似，但是在去重的使用过程中，有一个最容易被忽略的限制条件，只有当数据完全一致时才可以进行去重的操作
select distinct id,age from emp; 
关键字针对的是多个字段组合的结果

select distinct age from emp; 
select distinct age,post from emp;
查询关键字之排序(order by)

排序可以按照我们的需求对表中的数据进行排序处理，可以对单个字段使用，也可以对多个字段使用，在排序使用时，默认按照升序排列，多字段使用，则会先按照升序排列第一个字段对应的数据，然后按照降序对其他数据进行处理
# 单字段排序
select * from emp order by age;  
默认升序
select * from emp order by age asc;  
因为默认是升序排列，所以关键字asc可以忽略不写
select * from emp order by age desc;
使用关键字desc可以将默认的排序方式改为降序

多字段排序
select * from emp order by age,salary desc;
这时候的排序方式会先按照age升序排序，然后再按照salary进行降序排列

统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
1.先筛选出所有年龄大于10岁的员工
select * from emp where age > 10;
2.再对他们按照部门分组统计平均薪资
select post,avg(salary) from emp where age > 10 group by post;
3.针对分组的结果做二次筛选
select post,avg(salary) from emp where age > 10 group by post having avg(salary)>1000;
4.最后按照指定字段排序
select post,avg(salary) from emp where age > 10 group by post having avg(salary)>1000 order by avg(salary);

"
在编写SQL语句时，如果有多处需要使用聚合函数处理的结果，那么我们可以使用as关键字赋予该结果一个别名，用别名代替聚合函数的使用，减少底层运算次数，提高底层的效率
select post,avg(salary) as avg_salary from emp where age > 10 group by post having avg_salary>1000 order by avg_salary;
"
查询关键字之分页(limit)

当某一个表中的数据很多时，我们很少会直接获取所有数据，就像网站的分页处理一样，一次查询其中的一部分，这时候我们就可以limit进行分页操作
select * from emp limit 5;
直接限制展示的条数
select * from emp limit 5,5;
从第5条开始往后读取5条

'
在SQL语句的编写过程中，要具体问题具体分析，在合适的时候使用聚合函数与分页，而不是形成惯性思维
'
查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;
查询关键字之正则表达式(regexp)

正则表达式一般是用在模糊匹配不符合实际要求的情况下，这个时候我们可以使用正则表达式进行查询
select * from emp where name regexp '^j.*?(n|y)$';
多表查询
多表查询思路

数据准备,准备两张写有数据的表，不建立外键关系，只建立逻辑关系
create table dep(
id int primary key auto_increment,
name varchar(20) 
);

create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'财务');

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
这个时候如果我们使用select * from emp,dep;进行查询，会出现以下现象

两张表中的所有数据都会互相对应一遍，这种现象我们成为[笛卡尔积](笛卡尔乘积_百度百科 (baidu.com)),这种对应对于需要数据的我们而言毫无意义，我们所需要的是具有对应关系的数据关联到一起

这个时候我们可以在笛卡尔积的基础上进行筛选，但是在涉及到两张表以上时，我们需要在字段前指定表名
select * from emp,dep where emp.dep_id=dep.id;
这样我们就可以实现我们的需求，并将两张表合并到一起
多表查询的两种方式

在我们之后的学习与工作过程中，会接触到更多的查询操作，这时候的查询就不仅仅只是局限于单表查询，会涉及到多表查询

我们这时候就需要用到多表查询，对于多表查询，我们经常用的方式有两种
连表操作

1.内连接(inner join)

内连接只会对两张或者多张表中共有的数据
# inner join 内连接
select * from emp inner join dep on emp.dep_id=dep.id;
 	union		   全连接
    	 select * from emp left join dep on emp.dep_id=dep.id
        union
        select * from emp right join dep on emp.dep_id=dep.id;
     	 以左右表为基准 展示所有的数据 各自没有的全部NULL填充
2.左连接(left join)/右连接(right join)

左连接(left join):以左表为基准进行连接，展示左表所有的数据，如果没有对应项则用NULL填充

右连接(right join):以右表为基准进行连接，展示左表所有的数据，如果没有对应项则用NULL填充
# left join 左连接
select * from emp left join dep on emp.dep_id=dep.id;

# right join 右连接 
select * from emp right join dep on emp.dep_id=dep.id;
3.全连接(union)

全连接(union)，以左右表为基准，展示所有的数据，互相没有的数据的全部用NULL填充
# union	全连接
select * from emp left join dep on emp.dep_id=dep.id
当我们掌握连表操作后，就可以使用别名来指代已经进行过连接的新表，与其他的表进行连接，这样的话，不论有多少张表，我们都可以实现多表连接
子查询

子查询的基本使用就是将一条SQL语句作为另一条SQL语句的查询依据来使用，类似于python代码编写时的嵌套操作

对于子查询我们也需要用分步操作，来一步步解决问题，就是编程过程中的解耦合
题目:求姓名是jason的员工部门名称
步骤1:先根据jason获取部门编号
select dep_id from emp where name='jason';
步骤2:再根据部门编号获取部门名称
select name from dep where id=200;
子查询完整写法
select name from dep where id=(select dep_id from emp where name='jason');
对于这两种方法的使用，我们需要结合实际问题来考虑采用哪种方法，甚至是两种方法的结合使用
相关补充

concat与concat_ws

concat用于分组之前的字段拼接操作

concat_ws拼接多个字段并且中间的连接符一致
# concat
select concat(name,'$',sex) from emp;
# concat_ws
select concat_ws('|',name,sex,age,dep_id) from emp;
exists
sql1 exists sql2
sql2有结果的情况下才会执行sql1,否则不执行sql1,返回空数据

表相关SQL内容
# 修改表名
alter table 表名 rename 新表名;
# 添加字段
alter table 表名 add 字段名 字段类型(数字) 约束条件; # 直接在末尾添加
alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已有字段; # 在已有字段后添加
alter table 表名 add 字段名 字段类型(数字) 约束条件 first; # 新字段以第一位添加
# 修改字段
alter table 表名 change 旧字段名 新字段名 字段类型(数字) 约束条件; # 修改字段
alter table 表名 modify 字段名 新字段类型(数字) 约束条件; # 修改字段类型
# 删除字段
alter table 表名 drop 字段名;  
可视化工具Navicat

Navicat是一套数据库管理工具，专为简化数据库的管理及降低系统管理成本而设。Navicat 是以直觉化的图形用户界面而建的，可以安全和简单地创建、组织、访问并共用信息
Navicat Premium 是 Navicat 的产品成员之一，能简单并快速地在各种数据库系统间传输数据，或传输一份指定 SQL 格式及编码的纯文本文件。其他功能包括导入向导、导出向导、查询创建工具、报表创建工具、资料同步、备份、工作计划及更多

下载链接完整版

连接MySQL数据库

依次选择连接 > MySQL，设置相关的连接信息，点击确定

查询

点击查询图标 >点击新建，然后输入SQL语句，例如：

查看数据表

双击连接 >双击数据库 >双击表：

或者用SQL语句查看：

新建（连接，数据库，数据表等）
操作如下：

添加数据（手动添加或者用SQL语句）
从已有地本地文件中导入：

手动在界面中添加：

用SQL语句添加：

首先右键数据库 >选择命令列界面…>输入添加语句

常用快捷键

PS:常用操作

创建库、表、记录、外键

逆向数据库到模型、模型创建

新建查询可以编写SQL语句并自带提示功能

SQL语句注释语法

--、#、**\

运行、转储SQL文件

多表查询练习

数据准备

下载并导入sql文件

1.查询所有的课程的名称以及对应的任课老师姓名
# 1.先确定需要用到几张表  课程表 分数表
# 2.预览表中的数据 做到心中有数
select * from course;
select * from teacher;
# 3.确定多表查询的思路 连表 子查询 混合操作
SELECT
	teacher.tname,
	course.cname
FROM
 	course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
2.查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先确定需要用到几张表,学生表\分数表
# 2.预览表中的数据
select * from student;
select * from score;
# 3.根据已知条件80分 选择切入点 分数表
# 求每个学生的平均成绩 按照student_id分组 然后avg求num即可
select student_id,avg(num) as avg_num from score group by student_id having avg_num>80;
# 4.确定最终的结果需要几张表 需要两张表 采用连表更加合适
SELECT
	student.sname,
	t1.avg_num
FROM
	student
INNER JOIN (
SELECT
		student_id,
		avg(num) AS avg_num
	FROM
		score
	GROUP BY
		student_id
	HAVING
		avg_num > 80
) AS t1 ON student.sid = t1.student_id;
3.查询没有报李平老师课的学生姓名
# 1.先确定需要用到几张表  老师表 课程表 分数表 学生表
# 2.预览每张表的数据
# 3.确定思路 思路1:正向筛选 思路2:筛选所有报了李平老师课程的学生id 然后取反即可
# 步骤1 先获取李平老师教授的课程id
select tid from teacher where tname = '李平老师';
select cid from course where teacher_id = (select tid from teacher where tname = '李平老师');
# 步骤2 根据课程id筛选出所有报了李平老师的学生id
select distinct student_id from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname = '李平老师'))
# 步骤3 根据学生id去学生表中取反获取学生姓名
SELECT
	sname
FROM
	student
WHERE
	sid NOT IN (
		SELECT DISTINCT
			student_id
		FROM
			score
		WHERE
			course_id IN (
				SELECT
					cid
				FROM
					course
				WHERE
					teacher_id = (
						SELECT
							tid
						FROM
							teacher
						WHERE
							tname = '李平老师'
					)
			)
	)
4.查询没有同时选修物理课程和体育课程的学生姓名(报了两门或者一门不报的都不算)
# 1.先确定需要的表  学生表 分数表 课程表
# 2.预览表数据
# 3.根据给出的条件确定起手的表
# 4.根据物理和体育筛选课程id
select cid from course where cname in ('物理','体育');
# 5.根据课程id筛选出所有跟物理 体育相关的学生id
select * from score where course_id in (select cid from course where cname in ('物理','体育'))
# 6.统计每个学生报了的课程数 筛选出等于1的
select student_id from score where course_id in (select cid from course where cname in ('物理','体育'))
group by student_id
having count(course_id) = 1;
# 7.子查询获取学生姓名即可
SELECT
	sname
FROM
	student
WHERE
	sid IN (
		SELECT
			student_id
		FROM
			score
		WHERE
			course_id IN (
				SELECT
					cid
				FROM
					course
				WHERE
					cname IN ('物理', '体育')
			)
		GROUP BY
			student_id
		HAVING
			count(course_id) = 1
	) 
5.查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先确定涉及到的表	分数表 学生表 班级表
# 2.预览表数据
select * from class
# 3.根据条件确定以分数表作为起手条件
# 步骤1 先筛选掉大于60的数据
select * from score where num < 60;
# 步骤2 统计每个学生挂科的次数
select student_id,count(course_id) from score where num < 60 group by student_id;
# 步骤3 筛选次数大于等于2的数据
select student_id from score where num < 60 group by student_id having count(course_id) >= 2;
# 步骤4 连接班级表与学生表 然后基于学生id筛选即可
SELECT
	student.sname,
	class.caption
FROM
	student
INNER JOIN class ON student.class_id = class.cid
WHERE
	student.sid IN (
		SELECT
			student_id
		FROM
			score
		WHERE
			num < 60
		GROUP BY
			student_id
		HAVING
			count(course_id) >= 2
	);
python操作MySQL

所需模块pymysql
pip3 install pymysql
具体使用
import pymysql

# 1.连接MySQL服务端
conn = pymysql.connect(
    host='127.0.0.1', # 服务端IP
    port=3306, # 服务端端口号
    user='root', # 服务端用户名
    password='123', # 服务端密码
    db='db4_03', # 服务端数据库名称
    charset='utf8mb4' # 服务端编码方式
)
# 2.产生游标对象(cmd中的光标)
cursor = conn.cursor()  
# 括号内不填写额外参数 数据是元组 
# 指定性不强  [(),()]
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # [{},{}]
# 3.编写SQL语句
# sql = 'select * from teacher;'
sql = 'select * from score;'
# 4.发送SQL语句
affect_rows = cursor.execute(sql)  # execute也有返回值 接收的是SQL语句影响的行数
print(affect_rows)
# 5.获取SQL语句执行之后的结果
res = cursor.fetchall()
print(res)
pymysql补充

1.获取数据
fetchall() # 获取所有的结果
fetchone() # 获取结果集的第一个数据
fetchmany() # 获取指定数量的结果集
ps:注意三者都有类似于文件操作中光标移动的特性
cursor.scroll(1,'relative')  
# 基于当前位置往后移动
cursor.scroll(0,'absolute')  
# 基于数据的开头往后移动
2.增删改查

使用python操作MySQL时，数据的增删改操作都需要进行进行二次确认
autocommit=True  
# 针对增 删 改 自动确认(直接配置)
conn.commit()  
# 针对 增 删 改 需要二次确认(代码确认)

SQL注入问题

SQL注入问题引入

我们在pycharm中使用pymysql模块执行mysql代码的时候会发现用占位符传输信息的时候若是按下方的代码输入内容，会出现下面的两种现象

sql = " insert into userinfo(name,pwd) values(%s,%s)" % (sign_name,sign_pwd)

sign_name = 'jason' -- kasdjksajd'
sign_pwd = ''

sign_name = 'xyz' or 1=1 -- aksdjasldj'
sign_pwd = ''

1:输对用户名就可以登录成功

2:不需要对的用户名和密码也可以登录成功

这时候pycharm会在报错信息中返回一个sql语句，内容如下：

select * from userinfo where name='jason' -- kasdjksajd' and pwd=''
select * from userinfo where name='xyz' or 1=1 -- aksdjasldj' and pwd=''

讲解：

通过上面的语句，我们可以发现语句的中间出现了两个'--'，而这个--符号在

SQL语句中的意思是注释的作用，因此就相当于执行了--符号前面的语句，

后面的内容成了注释内容，这种情况就叫做SQL注入

SQL注入概念和解决方案

概念

SQL注入:利用特殊符号的组合产生特殊的含义，从而避开了正常的业务逻辑

解决方案

针对上述的SQL注入问题核心在于手动拼接了关键数据交给execute处理即

可

sql = " select * from userinfo where name=%s and pwd=%s "
cursor.execute(sql, (username, password))

补充说明

我们也可以使用executemany来一次性处理多个数据，把多个数据填入下方

的小括号的即可

executemany(sql,[(),(),(),()...])

视图

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

于连表操作得到的结果。语句如下：

create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;

注意事项

1.视图的表只能用来查询不能做其他增删改操作

2.视图尽量少用，会跟真正的表产生混淆，从而干扰操作者（使用show

tables查看表名称或是进行其他操作的时候跟普通的表没有区别）

触发器

定义

触发器是由事件来触发某个操作。当数据库执行这些事件时，就会激活触发

器来执行相应的操作。这些事件称为触发条件，在MySQL中，有UPDATE，

INSERT，和DELETE（即对表进行增、删、改）

主要有六种情况:增前、增后、删前、删后、改前、改后（BEFORE

INSERT、BEFORE DELETE、BEFORE UPDATE、AFTER INSERT、

AFTER DELETE、AFTER UPDATE）

代码

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

1.触发器命名有一定的规律

tri_before_insert_t1
tri_after_delete_t2
tri_after_update_t2

讲解：

这里我们发现第一部分名称就是tirgger关键字的前三个字母，第二部分名称是出发的状态，这部分名称由两部分组成，最后的一部分名称我们可以自定义。

2.临时修改SQL语句的结束符

因为有些操作中需要使用分号，这时候就会出现冲突。
这里我们用delimiter关键字修改结束符号，通过这种方式达到目的

触发器实际应用

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') #no代表执行失败
);

CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);

delimiter $$  # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
    if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象，如果新纪录中success的值为no就会把数据也记录到errlog表中
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$
delimiter ;  # 结束之后记得再改回来，不然后面结束符就都是$$了

#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('kevin','0755','ls -l /etc',NOW(),'yes'),
    ('kevin','0755','cat /etc/passwd',NOW(),'no'),
    ('kevin','0755','useradd xxx',NOW(),'no'),
    ('kevin','0755','ps aux',NOW(),'yes');

# 查询errlog表记录
select * from errlog;
# 查看所有的触发器
show triggers;
# 删除触发器
drop trigger tri_after_insert_cmd;

事务

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

PBXT

事务的四大特性(ACID)

A:原子性

指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不

发生。

特点：

1、事务是一个完整的操作，事务的各元素是不可分的

2、事务中的所有元素必须作为一个整体提交或回滚

3、如果事务中的任何元素失败，则整个事务将失败

举例：

比如转账的时候，如果对方接收失败了，钱应该退回自己的账户中。让双方的数据都回到转账前的状态

C:一致性

指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。一致性与原子性是密切相关的

特点：

1、当事务完成时，数据必须处于一致状态

2、在事务开始前，数据库中存储的数据处于一致状态

3、在正在进行的事务中，数据可能处于不一致的状态

4、当事务成功完成时，数据必须再次回到已知的一致状态

举例：

对银行转帐事务，不管事务成功还是失败，应该保证事务结束后表中A和B的存款总额跟事务执行前一致。

I:隔离性

指在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间（多个事务之间彼此不干扰）

特点：

1、对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立

的，它不应以任何方式依赖于或影响其他事务

2、修改数据的事务可在另一个使用相同数据的事务开始之前访问

3、这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据

D:持久性

持久性也称永久性，指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，并不会被回滚

特点：

1、指不管系统是否发生故障，事务处理的结果都是永久的

2、一旦事务被提交，事务的效果会被永久地保留在数据库中

代码

transaction是事务功能的关键字

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;

事务相关关键字

事务(transaction)
回退(rollback)
提交(commit)
保留点(savepoint)
为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符，这样如果需要回退可以回退到某个占位符(保留点)
    创建占位符可以使用savepoint
    	savepoint sp01;
    回退到占位符地址
    	rollback to sp01;

保留点在执行rollback或者commit之后自动释放

事务之间的相互影响

1、脏读：一个事务读取了另一个事务未提交的数据，而这个数据是有可能

回滚的

2、不可重复读：一个事务内两个相同的查询却返回了不同数据。这是由于

查询时系统中其他事务修改的提交而引起的

3、幻读：一个事务对一个表中的数据进行了修改，这种修改涉及到表中的

全部数据行。同时，另一个事务也修改这个表中的数据，这种修改是向表中

插入一行新数据。那么，操作前一个事务的用户会发现表中还有没有修改的

数据行，就好象发生了幻觉一样

4、丢失更新：两个事务同时读取同一条记录，A先修改记录，B也修改记录

（B不知道A修改过），B提交数据后B的修改结果覆盖了A的修改结果

在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的

修改

InnoDB支持所有隔离级别

设置隔离级别语句：

set transaction isolation level 级别

查询全局事务隔离级别

show global variables like '%isolation%';
SELECT @@global.tx_isolation;

1.read uncommitted(未提交读)
事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提

交的数据，这一现象也称之为"脏读"

2.read committed(提交读)

大多数数据库系统默认的隔离级别

一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这

种级别也叫做"不可重复读"

3.repeatable read(可重复读) # MySQL默认隔离级别

能够解决"脏读"问题，但是无法解决"幻读"

所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该

范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，

InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题

4.serializable(可串行读)

强制事务串行执行，很少使用该级别

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值，这表示在事务开始之后这行记录才被

删除

存储过程

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的

可以看成是python中的自定义函数

# 无参函数
delimiter $$
create procedure p1()
begin
	select * from cmd;
end $$
delimiter ;

# 调用
call p1()


# 有参函数
delimiter $$
create procedure p2(
    in m int,  # in表示这个参数必须只能是传入不能被返回出去
    in n int,  
    out res int  # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
    select * from cmd 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;
"""

# 大前提:存储过程在哪个库下面创建的只能在对应的库下面才能使用！！！

# 1、直接在mysql中调用
set @res=10  # res的值是用来判断存储过程是否被执行成功的依据，所以需要先定义一个变量@res存储10
call p1(2,4,10);  # 报错
call p1(2,4,@res);  

# 查看结果
select @res;  # 执行成功，@res变量值发生了变化

# 2、在python程序中调用
pymysql链接mysql
产生的游表cursor.callproc('p1',(2,4,10))  # 内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=10;
cursor.excute('select @_p1_2;')

函数

可以看成是python中的内置函数

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

1.移除指定字符

Trim、LTrim、RTrim

2.大小写转换

Lower、Upper

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

Left、Right

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

Soundex

eg:客户表中有一个顾客登记的用户名为J.Lee

但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的

where Soundex(name)=Soundex('J.Lie')

5.日期格式:date_format

'''在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');

1.where Date(sub_time) = '2015-03-01'
2.where Year(sub_time)=2016 AND Month(sub_time)=07;
# 更多日期处理相关函数 
	adddate	增加一个日期 
	addtime	增加一个时间
	datediff计算两个日期差值

流程控制

# 分支结构
declare i int default 0;
IF i = 1 THEN
	SELECT 1;
ELSEIF i = 2 THEN
	SELECT 2;
ELSE
	SELECT 7;
END IF;

# 循环结构
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
	SELECT num ;
	SET num = num + 1 ;
END WHILE ;

索引相关概念

1）索引就好比一本书的目录，它能让你更快的找到自己想要的内容

2）让获取的数据更有目的性，从而提高数据库检索数据的性能

MySQL索引主要有两种结构：B+Tree索引和Hash索引

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

* primary key
* unique key
* index key

1.上述的三个key都可以加快数据查询

2.primary key和unique key除了可以加快查询本身还自带限制条件而index

key很单一就是用来加快数据查询

3.外键不属于索引键的范围是用来建立关系的与加快查询无关

索引加快查询的本质

	id int primary key auto_increment,
 	name varchar(32) unique,
  	province varchar(32)
 	age int
 	phone bigint
 	
	select name from userinfo where phone=18818888888;  # 一页页的翻
	select name from userinfo where id=99999;  # 按照目录确定页数找

索引可以加快数据查询，但是会降低增删的速度

通常情况下我们频繁使用某些字段查询数据

为了提升查询的速度可以将该字段建立索引

聚集索引(primary key)

主键、主键索引

辅助索引(unique,index)

除主键意外的都是辅助索引，辅助索引其实本质上也是用的聚集索引的数据

来查询的（用的主键的数据）

覆盖索引

select name from user where name='jason';

所谓覆盖索引就是条件中的字段名和查看的字段名称相同

非覆盖索引

select age from user where name='jason';

索引数据结构

索引的底层其实是树结构>>>:树是计算机底层的数据结构

树有很多中类型

二叉树、b树、b+树、B*树......

二叉树

二叉树里面还可以细分成很多领域,我们只需简单了解即可

二叉树的特点：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大

B树（B-Tree）

MySQL的数据是存储在磁盘文件中的，查询数据时需要先把磁盘中的数据加载到内存中，磁盘IO操作非常耗时，所以我们优化的重点就是尽量减少磁盘

IO操作，所以，我们应当尽量减少从磁盘中读取数据的次数。另外，从磁盘

中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。如果我们

能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，

那我们查找数据的时间也会大幅度降低

如果我们用树这种数据结构作为索引的数据结构，那我们每查找一次数据就

需要从磁盘中读取一个节点，也就是我们说的一个磁盘块。我们都知道平衡

二叉树可是每个节点只存储一个键值和数据的。那说明什么？说明每个磁盘

块仅仅存储一个键值和数据！那如果我们要存储海量的数据呢？

可以想象到二叉树的节点将会非常多，高度也会极其高，我们查找数据时也

会进行很多次磁盘IO，我们查找数据的效率将会极低

为了解决平衡二叉树的这个弊端，B树应运而生， B树是一种多叉平衡查找

树，主要的特点是：

1、叶子节点都在同一层，叶子节点没有指针连接

2、B树的节点中存储着多个元素，每个内节点有多个分叉

3、节点中的元素包含键值和数据，节点中的键值从大到小排列

4、所有的节点都可以存放完整的数据

下面模拟下查找key为27的data的过程：

存在的一些问题：

B树中每个节点中包含key值以及data值，而每一个节点的存储空间是有限

的(MySQL默认16K)，如果data中存放的数据较大时，将会导致每个节点

能存储的key的数量很小，所以当数据量很多，且每行数据量很大的时

候，同样会导致树的高度变得很高，增大查询时的磁盘IO次数，进而影响

查询效率
不支持范围查询的快速查找，而在实际的应用中，数据库范围查询的频率

非常高，以下的一种情况是我查找10和35之间的数据，查找到15之后，需

要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待

提高。

B+\B*树

只有叶子节点才会存放真正的数据其他节点只存放索引数据

B+树

B+叶子节点增加了指向其他叶子节点的指针

对比B树和B+树，我们发现二者主要存在以下几点不同的地方：

数据都存放在叶子节点中
非叶子节点只存储键值信息，不再存储数据
所有叶子节点之间都有一个指针，指向下一个叶子节点,而且叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表

等值查询

下面模拟下查找key为9的data的过程：

范围查询

下面模拟下查找key的范围为9到26这个范围的data的过程：

从上面的结果，我们可以知道B+树作为索引结构带来的好处：

磁盘IO次数更少
数据遍历更为方便
查询性能更稳定

由于B+树优秀的结构特性，在MySQL中，存储引擎MyISAM和InnoDB的索引就采用了B+树的数据结构。

B*树

B*叶子节点和枝节点都有指向其他节点的指针

ps：

辅助索引在查询数据的时候最会还是需要借助于聚集索引
辅助索引叶子节点存放的是数据的主键值
有时候就算采用索引字段查询数据也可能不会走索引!!!

索引失效的几种情况

1.前导模糊查询不能利用索引(like '%XX'或者like '%XX%')

'A%'就可以正常使用索引

2.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

3.OR前后存在非索引的列，索引失效

如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少

用or的原因)

要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

4.普通索引的不等于不会走索引；如果是主键，则还是会走索引；如果是键

或索引是整数类型，则还是会走索引

5.组合索引最左前缀

    如果组合索引为：(name,email)

    name and email -- 使用索引

    name -- 使用索引

    email -- 不使用索引

6.is null可以使用索引，is not null无法使用索引

最好在设计表时设置NOT NULL约束，比如将INT类型的默认值设为0，将字

符串默认值设为''

7.计算、函数导致索引失效另外一种情况

#使用到了索引

explain select * from student_info where name like 'li%';

#未使用索引，花费时间更久

explain select * from student_info where LEFT(name,2)='li';

扩展：

如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使

用索引

#不会使用name的索引

explain select * from student_info where name=123;

#使用到索引

explain select * from student_info where name='123';

如上，name字段是VARCAHR类型的，但是比较的值是INT类型的，name

的值会被隐式的转换为INT类型再比较，中间相当于有一个将字符串转为INT

类型的函数。这也相当于是函数导致的索引失效

8.字符集不统一

统一使用utf8mb4( 5.5.3 版本以上支持 ) 兼容性更好，统一字符集可以避免

由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造

成索引失效

慢查询优化

我们使用explain方法来查看语句的索引扫描类型。

Explain是什么？

主键自动建立唯一索引。
频繁作为查询条件的字段应该创建索引。
查询中与其他表关联的字段，外键关系建立索引。
单键/组合索引的选择问题，组合索引性价比更高。
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
查询中统计或者分组字段。

Explain有哪些作用

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引可以被实际使用
表之间的引用
每张表有多少行被优化器查询

Explain怎么使用

explain + SQL
执行计划包含的信息

+----+-------------+-------+-------+---------------+-----+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+-----+---------+------+------+-------+

4、常见的索引扫描类型:

1）index
2）range
3）ref
4）eq_ref
5）const
6）system
7）null

索引扫描类型详解

生产中,mysql在使用全表扫描时的性能是极其差的，所以MySQL尽量避免出现全表扫描
从上到下，性能从最差到最好，我们认为至少要达到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: 越小越好

测试索引

准备

#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号
# 由于这里要加三百万调记录要很久
#3. 查看存储过程
show create procedure auto_insert1\G 

#4. 调用存储过程
call auto_insert1();
# 表没有任何索引的情况下
select * from s1 where id=30000;
# 避免打印带来的时间损耗
select count(id) from s1 where id = 30000;
select count(id) from s1 where id = 1;

# 给id做一个主键
alter table s1 add primary key(id);  # 速度很慢

select count(id) from s1 where id = 1;  # 速度相较于未建索引之前两者差着数量级
select count(id) from s1 where name = 'jason'  # 速度仍然很慢


"""
范围问题
"""
# 并不是加了索引，以后查询的时候按照这个字段速度就一定快   
select count(id) from s1 where id > 1;  # 速度相较于id = 1慢了很多
select count(id) from s1 where id >1 and id < 3;
select count(id) from s1 where id > 1 and id < 10000;
select count(id) from s1 where id != 3;

alter table s1 drop primary key;  # 删除主键 单独再来研究name字段
select count(id) from s1 where name = 'jason';  # 又慢了

create index idx_name on s1(name);  # 给s1表的name字段创建索引
select count(id) from s1 where name = 'jason'  # 仍然很慢！！！
"""
再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分
那这个树其实就建成了“一根棍子”
"""
select count(id) from s1 where name = 'xxx';  
# 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
select count(id) from s1 where name like 'xxx';
select count(id) from s1 where name like 'xxx%';
select count(id) from s1 where name like '%xxx';  # 慢 最左匹配特性

# 区分度低的字段不能建索引
drop index idx_name on s1;

# 给id字段建普通的索引
create index idx_id on s1(id);
select count(id) from s1 where id = 3;  # 快了
select count(id) from s1 where id*12 = 3;  # 慢了  索引的字段一定不要参与计算

drop index idx_id on s1;
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件
create index idx_name on s1(name);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 并没有加速

drop index idx_name on s1;
# 给name，gender这种区分度不高的字段加上索引并不难加快查询速度

create index idx_id on s1(id);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 快了  先通过id已经讲数据快速锁定成了一条了
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 慢了  基于id查出来的数据仍然很多，然后还要去比较其他字段

drop index idx_id on s1

create index idx_email on s1(email);
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 快 通过email字段一剑封喉

联合索引

select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  
# 如果上述四个字段区分度都很高，那给谁建都能加速查询
# 给email加然而不用email字段
select count(id) from s1 where name='jason' and gender = 'male' and id > 3; 
# 给name加然而不用name字段
select count(id) from s1 where gender = 'male' and id > 3; 
# 给gender加然而不用gender字段
select count(id) from s1 where id > 3; 

# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
create index idx_all on s1(email,name,gender,id);  # 最左匹配原则，区分度高的往左放
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 速度变快

慢查询日志

设定一个时间检测所有超出该时间的sql语句，然后针对性的进行优化

全文检索

使用Mysql全文检索fulltext的先决条件

表的类型必须是MyISAM

建立全文检索的字段类型必须是char,varchar,text

MySQL的全文检索功能MYISAM存储引擎支持而InnoDB存储引擎不支持
一般在创建表的时候启用全文检索功能
create table t1(
	id int primary key auto_increment,
  content text
	fulltext(content)
)engine=MyISAM;

# match括号内的值必须是fulltext括号中定义的(单个或者多个)
select content from t1 where match(content) against('jason')
'''上述语句可以用like实现但是查询出来的结果顺序不同 全文检索会以文本匹配的良好程度排序数据再返回效果更佳'''

# 查询扩展
select note_text from productnotes where Math(note_text) Against('jason' with query expansion);
"""
返回除jason外以及其他jason所在行相关文本内容行数据
eg:
	jason is handsome and cool,every one want to be cool,tony want to be more handsome;
	二三句虽然没有jason关键字 但是含有jason所在行的cool和handsome
"""

# 布尔文本搜索
即使没有定义fulltext也可以使用，但是这种方式非常缓慢性能低下
select note_text from productnotes where Match(note_text) Against('jason' in boolean mode);

# 注意事项
1.三个及三个以下字符的词视为短词，全文检索直接忽略且从索引中排除
2.MySQL自身自带一个非用词列表，表内词默认均被忽略(可以修改该列表)
3.出现频率高于50%的词自动作为非用词忽略，该规则不适用于布尔搜索
4.针对待搜索的文本内容不能少于三行，否则检索不返回任何结果
5.单引号默认忽略

插入数据

数据库经常被多个用户访问，insert操作可能会很耗时(特别是有很多索引需要更新的时候)而且还可能降低等待处理的select语句性能
如果数据检索是最重要的(一般都是)，则可以通过在insert与into之间添加关键字low_priority指示MySQL降低insert语句优先级
	insert low_priority  into 
  
insert还可以将一条select语句的结果插入表中即数据导入:insert select
eg:想从custnew表中合并数据到customers表中
  insert into customers(contact,email) select contact,email from custnew;

更新数据

如果使用update语句更新多列值，并且在更新这些列中的一列或者多列出现一个错误会导致整个update操作被取消，如果想发生错误也能继续执行没有错误的更新操作可以采用
	update ignore custmoers ...
  """
  update ignore  set name='jason1',id='a' where id=1;
  	name字段正常修改
  update set name='jason2',id='h' where id=1;
  	全部更新失败
  """

删除数据

delete语句从表中删除数据，甚至可以是所有数据但是不会删除表本身并且如果想从表中删除所有的行不要使用delete,可以使用truncate速度更快

并且会重置主键值(实际是删除原来的表并重新创建一个表而不是逐行删除表中的数据)

主键

查看当前表主键自增到的值(表当前主键值减一)
	select last_insert_id();

外键

MySQL存储引擎可以混用，但是外键不能跨引擎,即使用一个引擎的表不能

用具有使用不同引擎表的外键

重命名表

rename关键字可以修改一个或者多个表名
rename table customer1 to customer2;
rename table back_cust to b_cust,
back_cust1 to b_cust1,
back_cust2 to b_cust2;

安全管理

1.创建用户
	create user 用户名 identified by '密码';
 	"""修改密码"""
  	set password for 用户名 = Password('新密码');
    set password = Password('新密码');  # 针对当前登录用户
2.重命名
	rename user 新用户名 to 旧用户名; 
3.删除用户
	drop user 用户名;
4.查看用户访问权限
	show grants for 用户名;
5.授予访问权限
	grant select on db1.* to 用户名;
  # 授予用户对db1数据库下所有表使用select权限
6.撤销权限
	revoke select on db1.* from 用户名;
"""
整个服务器
	grant all/revoke all
整个数据库
	on db.*
特定的表
	on db.t1
"""

隔离级别

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

锁

读锁(共享锁)
	多个用户同一时刻可以同时读取同一个资源互不干扰
写锁(排他锁)
	一个写锁会阻塞其他的写锁和读锁
死锁
	1.多个事务试图以不同的顺序锁定资源时就可能会产生死锁
    2.多个事务同时锁定同一个资源时也会产生死锁
	# Innodb通过将持有最少行级排他锁的事务回滚

事务日志

事务日志可以帮助提高事务的效率 
	存储引擎在修改表的数据时只需要修改其内存拷贝再把该修改记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘
  事务日志采用的是追加方式因此写日志操作是磁盘上一小块区域内的顺序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值，这表示在事务开始之后这行记录才被删除。
"""

转换表的引擎

主要有三种方式，并各有优缺点!
# 1.alter table
	alter table t1 engine=InnoDB;
  """
  	适用于任何存储引擎 但是需要执行很长时间 MySQL会按行将数据从原表赋值到一张新的表中，在复制期间可能会消耗系统所有的IO能力，同时原表会加读锁
  """
# 2.导入导出
	"""
	使用mysqldump工具将数据导出到文件，然后修改文件中相应的SQL语句
		1.引擎选项
		2.表名
	""" 	
# 3.insert ... select
	"""
	综合了第一种方案的高效和第二种方案的安全
		1.先创建一张新的表
		2.利用insert ... select语法导数据
	数据量不大这样做非常合适 数据量大可以考虑分批处理 针对每一段数据执行事务提交操作避免产生过多的undo
	"""
  ps:上述操作可以使用pt-online-schema-change(基于facebook的在线schema变更技术)工具，简单方便的执行上述过程

posted @ 2022-11-29 21:56 逐风若梦阅读(48) 评论(0) 编辑收藏举报

刷新页面返回顶部

lf17603472426

MySQL数据库

数据存取方式的演变

数据库软件应用演变

数据库的本质

数据库分类

〓关系型数据库介绍〓

〓非关系型数据库介绍〓

MySQL简介

MySQL基本使用

制作系统服务

MySQL密码操作

SQL与NoSQL

数据库有关概念

基本SQL语句

'''基于库的增删改查'''

'''基于表的增删改查'''

'''基于记录的增删改查'''

字符编码与配置文件

数据库存储引擎

MyISAM

InnoDB

Memory

BlackHole

创建表的完整语法

MySQL常见字段类型

字段类型之整型

字段类型之浮点型

字段类型之字符类型

字段类型之枚举与集合

字段类型之日期类型

字段约束条件

无符号(unsigned)及零填充(zerofill)

非空(not null)

默认值(default)

唯一值(unique)

主键(primary key)

自增(auto_increment)

外键(foreign key)

一对多关系

多对多关系

一对一关系

MySQL中的数据查询

SQL语句中常用数据查询关键字

示例数据准备

查询关键字实例(基于我们创建的表)

查询关键字之筛选(where)

查询关键字之分组(group by)

查询关键字之过滤(having)

查询关键字之去重(distinct)

查询关键字之排序(order by)

查询关键字之分页(limit)

查询关键字之正则表达式(regexp)

多表查询

多表查询思路

多表查询的两种方式

连表操作

子查询

相关补充

concat与concat_ws

exists

表相关SQL内容

可视化工具Navicat

多表查询练习

python操作MySQL

pymysql补充