数据库总结

数据库

什么是数据库

数据库就是存储数据的仓库

存储数据的方式

1. 变量，无法永久存储

2. 文件处理 ,可以永久存储

文件处理的弊端

1. 文件处理速度慢

2. 文件只能在自己的计算机上读写 无法被共享(局域网除外)

计算机的性能进行扩展

"""
1.垂直扩展
不断的提升硬件性能  不可取
​
2.横向扩展 (分布式计算)
添加更多的计算机 将程序的不同组件分别运行在不同的计算机上
带来的优点:
性能提高 稳定性提高(可拔插式)
现在性能问题已经解决了 通过分布式的方式
但是这些组件虽然分布在各个计算机上 但是它们还是一个整体
也就是说你操作的数据文件还是是同一份
默认情况下 程序能访问的数据 但是只有当前计算机。
"""

一旦把服务器拆分开后,数据就必须能够被不同计算机共享，必须通过网络,也就是socket

数据库软件的本质 也就是一个 基于socket(TCP)的C/S结构的程序

如果自己实现一个数据软件 需要解决的几个问题

1. 一旦数据共享 就存在竞争问题,需要处理锁

2. 不是所有的TCP连接请求都要接受,得加上安全验证

3. 文件处理带来的效率问题, 需要想办法来提高处理速度 (索引)

常见数据库

关系型数据库

数据库可以为数据与数据之间建立关联关系，人是一条数据，他可能关联着一个工作岗位数据。双方可以通过自身找到对方。

该类型的数据 提供某种机制 可以帮你维护 数据与数据之间的关系

例如 学员数据 和 班级数据 可以通过其中一方 找到另外一方

一般都是基于文件实现的

• 优点:容量大 可以永久存储

mysql 免费开源,开源支持中小型企业并发

oracle 商用收费,学习免费,功能比mysql强大,用户管理和集群

SQLServer 属于微软生态圈,只能运行于 windows上

mariaDB mysql创始人:韦德纽斯 担心 mysql的前程 于是基于mysql开发了mariaDB 用起来是一模一样

DB2 IBM 360

sqlite 非常轻量级的数据库

共同特征都支持SQL

• 结构化 查询语言

• 学习数据库的重点也就是SQL语句

非关系型数据库

数据与数据之间是独立 不存在任何关联关系

以key:value的形式来存储数据

一般基于内存

• 优点:速度快

• 问题:断电丢失

MongoDB

redis

memcache

总结: 我们通常说的数据库就是一套软件 有服务端和客户端 用来操作服务器端上的文件

数据库相关概念

"""
数据:
    用于记录事物的状态信息 可以是数字  字符  声音 图像等等 
    如name = jerry
​
记录:
    一条记录用于保存一个事物的典型特征  就相当于文件中的一行
    如jerry,180,man,帅
​
表:
    本质就是一个文件，创建表的时候其实就是在创建一个文件 ，可以在数据库目录下看到
    可不可能把所有数据全放到同一个文件里?
    为了降低耦合性 方便管理 都应该把数据分门别类 放到不同文件中 
​
库:
    就是一个文件夹
​
DBMS:   
    数据库管理软件 就是一个套接字服务器端软件
​
数据库服务器:
    运行有数据库管理软件的计算机
    在公司我们开发者关心的部分是哪些?
    从库往上的需要我们关心  DBMS 和 服务器是运维关心的

安装mysql

下载安装包

下载地址：https://dev.mysql.com/downloads/mysql/

下载的时候提供了带界面的和不带界面的 用哪个呢?

服务器通常是不带界面的linux系统，并且熟练掌握SQL语句通常是面试官的基本要求

此处安装解压版的，下载对应的32/64位压缩包，解压到指定位置即可。

mysql目录解析

"""
bin             执行文件
mysqld          服务器主程序
mysql           无界面的客户端
data            数据文件
my-default.ini  配置文件模板

启动mysql服务器

注意:5.7后的版本需要先初始化 执行：mysqld --initialize-insecure

'''
启动服务器
    1.运行CMD，通过cd 进入bin目录    
    2.执行mysqld就可以启动服务器，如果报错的话使用管理员权限来运行CMD
    这是一个socket服务器程序 这时候相当于做了一下事情：
    启动服务器socket 并监听端口 默认绑定当前ip 端口默认3306
    
启动客户端
    运行mysql 注意他是一个socket程序 要连接服务器需要ip 和 端口
    mysql  -h ip -P port -uroot -p  密码默认为空
看到欢迎界面则说明socket链接成功了
​
执行过程分析：
    这个命令提示符本质是什么?
    就是一个input
    接受一条指令然后发送给服务器 
    服务器接收到之后呢解析指令 
    取出你要的数据在send给客户端
那问题来了 指令肯定不能随便写,不然服务器不认识,大家必须得有套规范
    就是sql 语句
    sql全称为 结构化查询语言，是所有关系型数据库都支持的语言，学习数据库很大一部分时间是在学习sql语句。
    
登录成功后可以执行一下命令来查看所有存在的数据库
show databases;
对比data文件夹来看

加到环境变量

找到安装目录，将bin路径复制填入系统设置的path中即可

找到安装目录，将bin路径复制填入系统设置的path中即可
命令总结:
​
mysqld #启动服务器
启动客户端 #mysql -h -P -u -p 本机简写 mysql -u -p
​
#关闭服务器
tasklist | findstr mysqld
taskkill  /F /PID  8372
​
也可以直接退出CMD

注册系统服务

服务器的启动和关闭都需要执行命令 还是不够方便 怎么办呢?

'''
将mysql注册到系统服务中  mysqld --install  
需要注意的是 默认注册的服务名称叫做mysql 这与bin下的mysql是两码子事，一个系统服务一个是客户端执行文件
​
删除服务 sc delete mysql(服务名)
​
查看系统服务 运行->services.msc 
通常设置为自动启动。

如果注册系统服务失败则可能是因为你之前安装过mysql 卸载重装即可。

界面版的安装失败也可能是之前安装了，导致端口占用。

修改管理员密码

现在密码默认为空 这样是不安全的
万一谁过来把你数据删了 怎么办? 
​
#修改密码
在知道原始密码的情况下可以使用mysqladmin
mysqladmin是一个用于管理数据库的程序，包括修改密码，数据备份等
修改密码：
    mysqladmin  -uroot -p旧密码 password 123
    警告忽略即可
​
#破解密码:
万一你不小心把密码忘记了怎么办? 到公司不可能卸载重装
这个密码它肯定要找地方存储起来 ,那存客户端还是服务器呢?
服务器 ,那存在内存还是硬盘呢? 必然是硬盘,既然如此,一定有一文件用于存放密码信息
​
#方式1:删除用于记录用户密码数据文件
没问题 简单粗暴 但是,这个文件里不只有你的账号信息 还有别人账号数据 还有授权相关的数据
所以你最好不要这么干!
那你在思考一下,服务器验证用户的本质是什么,是不是读取一个文件的数据来和你输入的数据进行对比,
那你可不可以这样子 我告诉我服务器说你不要去读那个授权文件,可以!
​
#方式2: 跳过授权表 进入系统修改授权表推荐
跳过这个操作是服务器相关的操作所以 咱的先关掉服务器重新开 在重新开的时候来告诉它
1.停止服务
2.启动服务器并添加参数
**mysqld --skip-grant-tables**
3.使用客户端登录服务器 执行修改命令 此时不需要输入密码
update mysql.user set password = password("123123") where user="root" and host="localhost"**
4.刷新权限
flush privileges
5.命令行中重启服务器验证新密码

编码设置

使用客户端执行\s 可以查看当前服务器的设置信息
latin1 gbk 就是默认的编码。 服务器是拉丁 客户端是 GBK 
很显然要乱码，我们必须保证编码方式一致!
​
如何设置：
在mysql安装目录下有个my_default.ini  他就是配置文件,但是他不是正在使用的
而是一个模板文件,我们如果要自己编写配置文件,需要自己复制一个叫做my.ini的文件来编写
编写格式我们已经学习过了
configpaser模块  
​
[section]
option = value
​
要设置客户端的内容就写在mysql分区
要设置服务器的内容就写在mysqld分区下
mysql在启动的时候会自动到安装目录下找my.ini文件 找到命令对应的标题加载里面的设置项
​
测试：在配置文件中加入用户名密码配置
​
[mysql]
user = "root"
pasword = "123"
​
需要注意的是：mysql mysqld都会来读取这个文件，对于客户端和服务器我们需要使用section来区分
​
​
# 服务器配置部分
[mysqld]
character-set-server = utf8
# 客户端mysql配置部分
[mysql]
default-character-set = utf8
# 其余客户端配置部分
[client]
default-character-set = utf8

mac配置

首先明确 配置文件编写方式相同!
​
不同的在于:
    1.mysql的安装路径 mac默认无法修改
    位于:/usr/local  
    使用command + shift + g 来前往
    
    2.配置文件名称为 my.cnf 需要放在/etc这个目录下
    位于:/et
    使用command + shift + g 来前往
    
mac彻底删除mysql
sudo rm /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /var/db/receipts/com.mysql.*
​
windows删除安装目录  删除系统服务即可

基本sql语句

库相关 文件夹

增 
create database mydb charset utf8;
create database mydb
​
查  
show databases;
show create databases mydb;# 查看创建库的详细信息
​
改
alter database mydb charset gbk # 修改库的属性
​
删
drop database mydb;

表相关 文件

切换文件夹 (选择数据库)
use mydb;
​
增
create table t1 (id int,name char)
creata table 库.表名(id int,name char)
​
查
show tables;# 查看所有表
show create table t1;# 查看表的详细信息
desc t1;# 查看表结构(字段)
​
改
改字段
alter table 表名 add | modify | drop | change
add  跟 字段和类型
modify 跟 字段 和 类型
drop 跟字段
change 跟 旧字段  新字段 类型
​
alter table table_name add primary key (col_name);# 添加主键
​
改字符编码
alter table 表名 default character set gbk
alter table 表名 charset = 'gbk'
​
改名称
rename table tname1 to tname2
​
删
drop table 表名

记录相关 一行数据

增 
insert into tablename values(data,....),(data,....),...
insert into tablename(字段) values(data)
​
查 
select id,name from t1;     
# 字段可用*号表示通配符
select * from t1;
​
改
update 表名 set 列名 = 值 where 条件; 
可同时修改多个字段,用逗号隔开  列名=值,列名=值...
没有条件则修改全部
    
删
delete from 表名 where 条件;
没有条件则删除所有;
​
重建表,清空所有数据;
truncate table t1;

相关规范

字段名 表名 库名

由 数字 字母 @ $ # _ 组成

不能纯数字

不能是mysql关键字 如int

表的分类

引擎就是一个系统的核心部分

引擎有不同的分类是为了适应不同的使用场景

查看mysql支持的所有引擎: show engines

 

MRG_MYISAM 是一堆MYISAM表的集合

• 用于做水平分表,如果一个表中数据量太大将导致效率降低

• 水平分表就是把整个大表拆成不同的小表,每一次查询会判断数据在哪一个表中,然后对应去查找以此来提高效率

垂直分表:当一个表中 字段太多,然而常用的信息就那么一两个,那就可以把不常用字段切到另一个表中,然后建立关联关系

innodb:是最最常用的引擎,因为支持,事务,行锁,外键一系列功能....

MyISAM:没有上述一堆功能,但是存储效率比innodb要高,并且支持分表

MEMORY:速度快,不能永久存储,没有特别的功能

mysql系统架构图

5.6与5.7对比

1. 需要初始化

2. 密码字段变成了authentication_string

3. 界面安装的配置文件放到了C:\ProgramData中

数据类型

详细建表语句

[]表示可选 create table 名称(字段 类型[(宽度) 约束条件],字段 类型,......)

为什么要给数据分类

1. 不同的数据类型 描述信息可以更加方便准确

2. 在计算机中对数据分类可以减少内存开销

数字类型

整型

 

默认都是有符号的
设置为无符号 需要在类型后添加unsigned
create table t8(a tinyint unsigned);
​
宽度在整型数据中不是用来设置可存储的数据范围的,是用于控制最先小显示长度(字符数)  如果长度不足 就帮你补0 但是需要指定zerofill
create table t12(a tinyint(5) zerofill);
​
结论是: 使用整型数据 宽度这个属性一般不用管它

浮点

 

• 浮点类型(a,b)

  • a代表总位数 b表示小数位数

• 共同点:

  • 小数位最大为30

  • loat与double总长最大为255

  • 都是不精确的 double比float精确点

• 不同点:

  • decimal总长最大为65 并且是精确的

字符串

char(固定长度)和varchar(可变长度)

char(L) varchar(L) L 都是表示最大可存储的长度,不是字节

a char(4)  b varchar(4)
a:S  最终还是占4个字符长度
b:S  最终占1个字符长度 + 标识信息的长度(1-2字节)
​
char:占用空间较多  存取速度较快
varchar:存储时必须保存一个长度标识信息,所以所占空间为实际内容长度+标识信息长度,相比较char,空间节省了,但是效率降低了
​
char采取的空间换时间 提高效率
​
字符串类型的BUG:由于char类型是定长 mysql会在末尾填充空格来达到定长的效果,所以char类型 不能存储末尾带有空格的数据 会直接把空格给去了

binary(固定长度的二进制)和varbinary(可变长度的二进制)

binary类和varbinary类类似于char和varchar,不同的是他们包含二进制字符串而不要非二进制字符串

binary和varbinary允许的最大长度一样,如同char和varchar,不同的是binary和varbinary的长度是字节长度而不是字符长度

text和blob

固定长度无法修改的字符串类型和二进制

 

tinytext,tinyblob 1字符 text,tinytext 2字符 mediumtext,mediumtext 3字符 Longtext,longblob 4字符

强调

• 二进制数据类型,一般不用,如果要存储多媒体 如:音频,视频,需要将其放到FTP文件服务器上 然后数据库中只存储连接即可

时间类型

 

year 4为年份 date 日期 yyyy-MM-DD time 时间 HH:mm:SS datetime yyyy-MM-DD HH:mm:SS timestamp 时间戳 一个浮点类型 从unix元年到现在的秒数

year 可以是数字,也可以是字符串
​
可以用四位字符串,也可以2位字符或数字
00-69 == 2001-2069
70-99 == 1970 - 1999
​
甭管什么时间类型,照着标准格式字符串写就行
​
timestamp,会自动更新时间,当你插入时,或修改时
​
current_time和 now() 所有的时间,日期类型,都可以使用这两个方法来输入当前时间

所有的日期时间 数据库返回的都是字符串类型

枚举和集合

enum:提前规定该字段能选的值的范围,多选1 set:提前规定该字段能选的值的范围,区别是set字段可以有多个值,用逗号隔开,多选多

严格模式

5.7以下的版本默认是非严格模式的,例如你的类型为tinyint 存储的是128 最终存储的是127 set global sql_mode = "strict_trans_tables" 可以将其设置为严格模式 最好写到配置文件中 永久有效

数据库范式

指导该如何设计数据库

在保证数据足够保存情况下,选用占用空间最小的类型

约束

为了防⽌在数据表中插⼊错误的数据，在MySQL中，定义了⼀些维护数据库完整性的规则，这个规则就是表的约束。通过这些约束我们可以保证数据的正确性。

 

not null 非空
限制该字段 必须有值
​
default   默认值
​
1.建表直接指定
create table t(name char(10) not null default 默认值);
2.后期增加约束
alter table t modify name char(10) not null default  已有的字段
alter table t add sex char(10) not null default 新增字段

unique  唯一性约束,并且是一个索引
要求该字段的值,不能重复
单字段:
create table olbPerson(name char(20) unique,school char(10));
​
多字段联合唯一约束
create table olbPerson(name char(20),school char(10), unique(name,school));

主键约束:从约束角度来看和 非空 + 唯一 是相同的效果,不能为空且不能重复
​
⼜分为两种
​
1 单字段主键：由单个字段构成的主键，其语法格式为
​
CREATE TABALE table_name (id INT PRIMARY KEY,name CHAR(20));
​
上述表中id作为⼀条记录的主键，我们通过id就能唯⼀确定⼀条数据。 
​
​
2 多字段主键：由多个字段组合构成的主键，其语法格式为
​
CREATE TABLE table_name(stu_id INT,course_id INT,grade float,PRIMARY KEY(stu_id,course_id));
​
test1中由stu_id和course_id组合可以唯⼀确定⼀条数据；
​
需要强调的是:
主键除了有约束的效果 还是一个索引,并且是非常重要的索引,在该innodb 主键是必不可少的
​
innodb 组织数据结构是按照树形结构来组织的,该结构中必须有一个数据,不能为空且唯一的,如果没有这样的数据 树形结构也就不存在.
​
mysql在创建表的时候会检测是否存在主键
如果没有,它就找一个具有非空且唯一约束的字段提升为主键
如果也没有这样的字段 mysql就创建一个隐藏字段作为主键
​
索引 可以提高查询速度
结论是: 只要你是用innodb引擎 你就应该建表时设置一个主键 通常主键字段叫做id
  
id 有唯一标识的意思
索引,就像是字典里的目录用于快速定位数据所在位置

自动增长

为什么需要自动增长

主键具有唯⼀性,通常是⼀个整型数据,我们在添加数据时,需要⼿动指定主键的值,⼀旦插⼊了已经存在的值会引发主键冲突错误,这时我们就需要花 时间去获取⼀个正确的主键值，这是⾮常低效的。

为此 MySQL提供了⾃动增⻓长约束。

作⽤：⾃动为这⼀列赋值，在每⼀次添加新的数据时该值都会⾃增，默认从1开 始。

注意： 该约束只能创建在带有索引的整型字段上。

1.在创建表的同时添加⾃动增⻓长 
例如： 
create table student (id int primary key auto_increment,name char(30),age int)
​
2.已经存在的表 可使⽤修改语句 
例如：
alter table student3 modify id int auto_increment;
​
3.插入带有自动增长的字段时  可以直接忽略该字段 也可以插入一个null
insert into table_name values(null,"jack");
insert into table_name(name) values("jack");

要你创建一个表 就应该有主键 并且自动增长

表关联

为什么要分表

一个表中 要存储个人信息又要存储部门信息 会导致大量的数据冗余

所有数据存放在同一个表中 将导致以下几个问题

1.浪费空间 不致命

2.结构混乱

3.修改数据时 如果有一百个员工 那就要该一百次 扩展性极差

 

通过分表来解决

分表又造成新的问题 如何再将数据对应起来?

为什么需要对应起来是因为两张表的数据具备这某种关系

到底是什么关系呢?

多对一的关系

外键约束

外键

一种约束,是为了保证数据完整性,外键就是另一个表的主键

外键用于建立物理层级关联关系

在正常开发中不推荐使用?

会降低存取效率,所以一般会把关系的维护交给应用程序 就是逻辑层面的关系

外键可以解决

多对一的关联关系

语法

#创建表时：
create table dept(id,xxxxx);
create table emp(id,xxxxx,dept_id,foreign key(dept_id) references dept(id));
​
#后期修改：
alter table student add  foreign key (id) references customer(id);

主从表关系

注意要建立外键关联,必须先创建主表

约束作用

此时我们在执行添加 更新 删除时都会受到外键的约束

级联操作

级联更新

主表更新了主键时 从表会相应的更新外键的值

级联删除

主表删除了某一条记录 从表只要外键与被删除的记录主键值相同 则全部删除

语法:

create table emp(id,xxxxx,dept_id)foreign key(dept_id) references dept(id) on update cascade on delete cascade;

多对多

实例分析 什么情况下会出现多对多的关系

学生表和老师表

多对多的关系本质上是什么?

是双向的多对一

如何在数据库中表示这种关系?

使用外键是不行的,需要创建第三张表专门用于存储关系

这个第三张表 如何体现关系?

在其中存储学生和老师的id即可 并且将它们作为外键关联到学生和老师表

create table t_s_relation(id int primary key auto_increment,t_id int,s_id int,foreign key(t_id) references teacher(t_id),foreign key(s_id) references stu(s_id));

为了去除重复的关系数据 可以给关系表添加主键约束,联合两个外键(联合唯一约束)

一对一

实例分析:

客户表 与 学生表

一个客户对应一个学生 或没有成为学生

一个学生 必然只对应一个客户

如何表示这种对应关系 ?

使用外键

那么外键应该加到哪张表中呢?

因为是先有客户才有学生 所以 客户是主表 学生是从表 应该加在学生表中

我们已经知道一个学生只对应一个客户 ,但是有可能你写的时候写错了,写了一个重复的客户id

此时将会造成数据错乱 如何解决呢?

给外键字段再加上 唯一性约束

复制表

create table table_copy select *from t1;

复制数据和表结构,但不会复制key

 

只想要copy表结构怎么弄?

create table table_copy select *from t1 where 0 = 1;

给一个不成立的条件这样的话就查不到任何数据 ,只能复制表结构

 

蠕虫复制

insert into tablename(name) select name from tablename;

复杂查询语句

单表查询

完整的查询语句语法

select distinct (* or 字段名 or 四则运算or聚合函数) from 表名

where 条件

group by 字段

having 条件

order by 排序

limit 控制从哪里开始显示以及 显示几条

关键字的执行顺序

注意：书写顺序错误将报错

伪代码:
  
第一步找到对应的文件
def from(name):
    open (file)
    pass
​
第二步 读取并筛选数据
def where(条件):
读取每一行数据 判断是否满足条件
  for line in file:
        if XXXxxx
​
def group():
  将数据 按照某个字段进行分组
  pass
​
def having():
  对分组后的数据进行筛选
  pass
​
def distinct():
  对数据进行去重处理
    pass
​
def order():
    对数据进行排序
    pass
​
def limit()
    选取一部分数据
    pass
​
def select()
  from()
  where()
  group()
  having()
  distinct()
  order()
  limit()
    return data;

简单查询

select distinct from 表名;

distinct是可选的,用于取出重复记录 ,只有当显示的所有列的数据都重复时才去除,当字段名太长获取不容易理解时 可使用as来取别名

1.*表示通配符 显示所有字段

2.可以指定任意个字段名

3.可以对字段的数据进行四则运算

4.聚合函数

准备数据:

create table stu(id int primary key auto_increment,name char(10),math float,english float);

insert into stu values(null,"赵云",90,30);

insert into stu values(null,"小乔",90,60);

insert into stu values(null,"小乔",90,60);

insert into stu values(null,"大乔",10,70);

insert into stu values(null,"李清照",100,100);

insert into stu values(null,"铁拐李",20,55);

insert into stu values(null,"小李子",20,55);

 

 

查看所有数据

select *from stu;

查看 英语成绩

select name,english from stu;

查看所有人的数学成绩 并去除项姓名相同的数据

select distinct name,mathenglish from stu;

统计每个人的总分

select name,english+math 总分 from stu;

为每个为的英语加10分显示

select name,english+10 总分 from stu;

 

调整显示的格式:

需要 在字段的数据前加上字段名:

name:赵云 english:90 math:30

如果是python怎么实现?

字符串拼接

mysql中的字符串拼接函数

concat()

select concat("姓名:",name),concat("英语:",english),concat("数学:",math) from stu;

取别名时 可以省略 as

select concat("姓名:",name) name,concat("英语:",english) english,concat("数学:",math) math from stu;

where关键字

子查询 = > < >= <= !=(<>) in between and like:% 任意个任意字符,_ 一个任意字符

and or not

group by

什么是分组

把一个整体 按照某个标识分成不同的部分

分组目的

通常分组都为了要统计数据

语法

select 字段 from emp group by 某个字段;

将数据按照部门分组

select *from emp group by dept;

注意:mysql5.6 默认分组后可以查看每个分组的第一条记录的所有字段 但这个数据有意义吗? 没有

我们可以添加 ONLY_FULL_GROUP_BY 到sql_mode中避免这个问题

分组后:就只能查看分组的字段 以及对组内记录的统计结果 (通过聚合函数来统计)

 

查询每个部门有哪些人

group_concat() # 不常用 select dept,group_concat(name) from emp group by dept;

 

可以有多个分组依据 比如先按部门 再按工作岗位 select dept,job,avg(salary) from emp group by dept,job;

 

聚合函数(统计函数)

将多个数据进行计算 并得到一个结果 称为聚合

集合函数:sum,count,avg,max/min

注意注意: 聚合函数不能用在where后面

having

用于对分组后的数据进行筛选

作用与where相同 用于过滤

不同点在于:where 是从文件读取数据时的过滤条件

这导致了where中不能使用聚合函数

因为数据读取工作都没有完成 不可能统计出数据

 

having是在分组后进行的过滤条件

分组的执行顺序是在where之后

此时数据已经全部读取了

所以可以使用聚合函数来进行统计

 

为什么 不分组的时候在select 后面可以使用聚合函数呢?

select sum(salary) from where;

因为你where比select 后面的字段筛选更早执行 此时数据全都已经读取了 所以可以进行统计

 

order by

用于对记录排序

select * from emp order  by salary;
​
默认为升序
​
​
​
select * from emp order  by salary asc;
​
指定为升序
​
​
​
select * from emp order  by salary desc;
​
指定为降序
​
​
​
select * from emp order  by salary,id desc;
​
工资相同时按照id排序

 

limit

用于限制显示的记录数

limit [start,] count;

start 开始位置

count 显示条数

不指定start 时 则从第一条开始显示

 

#分页显示
起始位置的计算公式
limit (页数-1) * 每页的条数,每页条数

 

查看前三人
​
select *from emp limit 3;
​
查看工资最高的那个人信息
​
select *from emp order by salary desc limit 1;
​
指定起始位置
​
查看id为3-6的人
​
select *from emp  limit 2,4; 

 

 

limit 可用于分页

分页原理

 

正则表达式匹配

正则表达式用于模糊查询,模糊查询已经讲过了

like 仅支持 % 和 _ 远没有正则表达式灵活

当然绝大多数情况下 like足够使用

语法:

insert into emp values(1,"laowangba","男",26,1,"总监",5800);

insert into emp values(1,"laoliba","男",26,1,"总监",5800);

insert into emp values(1,"laocheng","男",26,1,"总监",5800);

select *from table where name regexp "正则表达式";

多表查询

数据准备:

不存在外键关联的两张表

一张表示员工表

存在一些不存在的部门id

create table emp (id int,name char(10),sex char,dept_id int);

insert emp values(1,"大黄","m",1);

insert emp values(2,"老王","m",2);

insert emp values(3,"老李","w",30);

 

一张表示部门表

存在一些没有员工的的部门

create table dept (id int,name char(10));

insert dept values(1,"市场");

insert dept values(2,"财务");

insert dept values(3,"行政");

笛卡尔积查询

是两张表相乘的结果,若左边有m条 右边有n条 查询结果为m*n条; 往往包含大量错误数据

select *from dept,emp;

select *from dept,emp where dept.id = dept_id;

链接查询

内连接查询 查询出两张表都有的记录

select *from dept,emp where dept.id=emp.dept_id;

where用于筛选数据,而在多表查询中药筛选的是两边的关系 on专门用于过滤关联关系

select *from dept inner join emp on dept.id=emp.dept_id;

inner可以省略

左外链接查询 在内连接的基础上 增加左边表有而右边表没有的记录

select *from dept left join emp on dept.id=emp.dept_id;

右外链接查询 在内连接的基础上 增加右边表有而左边表没有的记录

select *from dept right join emp on dept.id=emp.dept_id;

全外链接查询 在内连接的基础上 增加左边表有而右边表没有的记录 增加右边表有而左边表没有的记录

select *from dept full join emp on dept.id = emp.dept_id;

mysql 不支持全外链接查询

*mysql中可以使用合并查询结果 在所有语句最后写分号

select *from dept left join emp on dept.id=emp.dept_id
union
select *from dept right join emp on dept.id=emp.dept_id;

union 只能用于字段数量相同的两个表,会自动去除重复的记录

union all 则保留所有记录

//查询1号部门的名称和其所有员工名称;

select dept.name 部门,emp.name 姓名 from dept join emp on dept.id=emp.dept_id where dept.id = 1;

多表查询的思路是 先将多张表的数据连在一起 在使用条件来过滤

子查询

什么是子查询

当一个查询是另一个查询的条件时,这个查询称之为子查询(内层查询)

什么时候用

当查询需求比较复杂,一次性查询无法得到结果,需要多次查询时,

例如:给出一个部门名称,需要获得该部门所有的员工信息

需要先确定部门的id,

然后才能通过id确定员工

解决问题的方式是把一个复杂的问题拆分为若干个简单的问题

 

 

如何使用

首先明确子查询就是一个普通的查询,当一个查询需要作为子查询使用时,用括号包裹即可

案列:

准备数据:

create table emp (id int,name char(10),sex char,age int,dept_id int,job char(10),salary double);

insert into emp values

(1,"刘备","男",26,1,"总监",5800),

(2,"张飞","男",24,1,"员工",3000),

(3,"关羽","男",30,1,"员工",4000),

(4,"孙权","男",25,2,"总监",6000),

(5,"周瑜","男",22,2,"员工",5000),

(6,"小乔","女",31,2,"员工",4000),

(7,"曹操","男",19,3,"总监",10000),

(8,"司马懿","男",24,3,"员工",6000);

 

create table dept(id int primary key,name char(10));

insert into dept values(1,"市场"),(2,"行政"),(3,"财务");

 

需求:财务部有哪些:

数据在两张表中 可以使用链接查询

select emp.name from emp inner join dept on dept.id = emp.dept_id where dept .name = "财务";

子查询方式:

数据在两张表中,先查询那张?

emp? 不行 不知道部门名 查dept

第一步 需要知道财务部的id

select id from dept where name = "财务";

第二步 用查询的到的id作为判断条件查询emp

select name from emp where dept_id = 3;

3不能写死 是上一个查询的结果 所以直接写在后面 加上括号就变成了子查询

select name from emp where dept_id = (select id from dept where name = "财务");

 

in 关键字子查询

查询平均年龄大于25的部门名称

子查询方式:

平均年龄大于25的部门id有哪些?

先要求出每个部门的平年龄! 筛选出平均年龄大于25的部门id

拿着部门id 去查询部门表查询

select name from dept where id in (select dept_id from emp group by dept_id having avg(age) > 25);

 

多表查询方式:

先把数据拼接到一起 在加以筛选

select dept.name from emp inner join dept

on emp.dept_id = dept.id

group by dept.name

having avg(age) >25;

 

exists关键字子查询

exists 后跟子查询 子查询有结果是为True 没有结果时为False

为true时外层执行 为false外层不执行

select *from emp where exists (select *from emp where salary > 1000);

查看exists 的返回结果: 只有 0 和 1

select (exists (select *from emp where salary > 10000));

 

一个查询结果也是一个表 既然是表就能链接起来

综合练习:

查询每个部门工资最高的员工信息

先查询每个部门的最高工资

将查询结果与员工表联合起来

在加条件判断部门id相同并且 最高工资相同 则显示

select *from emp inner join

(select dept_id,max(salary) m from emp group by dept_id) t2

on emp.dept_id = t2.dept_id

where

emp.dept_id = t2.dept_id

and

emp.salary = t2.m;

 

 

三表联查

create table stu(id int primary key auto_increment,name char(10));

create table tea(id int primary key auto_increment,name char(10));

 

create table tsr(id int primary key auto_increment,t_id int,s_id int,

foreign key(s_id) references stu(id),

foreign key(s_id) references stu(id));

insert into stu values(null,"张三"),(null,"李四");

insert into tea values(null,"egon"),(null,"wer");

insert into tsr values(null,1,1),(null,1,2),(null,2,2);

 

 

egon老师教过哪些人?

select *from stu join tea join tsr

on stu.id = tsr.s_id and tea.id = tsr.t_id

where tea.name = "egon";

mysql用户管理

mysql用户指的是客户端连接服务器时使用的账户 在一些公司中,很多项目的数据 可能会放在同一个服务器, 那就必须要为每一个用户明确其所拥有的的权限 通常 到公司之后 都会给你一个账号名称和密码 并且 为你制定你可以访问那些数据库或表

对用户账号增删改查 以及权限的增删改查

mysql是如何管理的

mysql本质上是一款cs软件,它具备用户认证!

我们有没有做过用户认证呢? ATM! 购物车,都做过,

我们是如何实现的?写入文件,mysql也是一样的,

只不过它把文件称为表

那么要怎么添加账户呢?

把用户信息写入表中就可以了

权限相关表

来看看它都把数据放在哪个表中了!

自带的mysql数据库,四个表用于存储账户信息以及权限
user
db
table_priv
columns_priv

权限优先级

user->db->table_priv->columns_priv

select *from user;
#由于字段较多 以表格形式展示 会比较乱,可以添加\G来纵向显示
select *from user\G;

内置root账户字段信息解析

创建账号语句

create user 用户名@"ip地址"  "identified" by 密码;
create user tom@"192.168.101" identified by "123";

该语句表面tom只能在101机器上使用,别的机器就无法登录

用%可以表示在任意机器可用

注意:该方式创建的账号没有任何权限 所以了解即可

授权语句

授权:

授权语句执行时如果账号不存在会自动创建账号 所以更推荐使用

注意:默认只有root才能为其他账号授权

grant all on *.* to tom@"localhost" identified by "123";
#该语句中的all 增删改查所有权限 但是不包括grant权限
#*.* 表示任何数据库 任何表 存储在user表
​
grant all on db.* to tom@"localhost" identified by "123" 
#db.* 该用户可以操作db数据库的任何表 存储在 db表
​
​
grant all on db.t1 to tom@"localhost" identified by "123" 
#db.* 该用户可以操作db数据库的t1表 存储在 table_privi表
​
​
​
grant select(id) on db.t1 to tom@"localhost" identified by "123" 
#精确到字段 和  操作级别
#该用户只能查询 db下的t1表 
​
​
​
grant all on *.* to tom@"localhost" identified by "123" with grant option;
#with grant option 表示该账户可以将权限授予其他用户
​
​
​
REVOKE all privileges [column]   on db.table from user@"host";
#收回权限
​
​
drop user@"host"
#删除用户
​
flush privileges;
#刷新权限表 一些时候权限信息可能会有所延迟 可以执行该语句立即刷新权限信息

pymysql模块

pymysql是python提供的一个mysql客户端模块,用于与mysql服务器建立连接,发送查询,并获取结果等;

基本使用

import pymysql
# 1.建立连接
try:
    conn = pymysql.connect(host="127.0.0.1",port=3306,user="root",password="",db="day46",)
    print("连接服务器成功!")
    #2.获取游标对象
    cursor = conn.cursor()
    #3.执行sql语句
    count = cursor.execute("select *from user")
    print("结果数量: %s" % count)
​
    # 提取结果
    # print(cursor.fetchall())
    # print(cursor.fetchone())
    # print(cursor.fetchmany(1))
​
    # 移动游标位置  相对当前位置
    cursor.scroll(1,"relative")
    cursor.scroll(-1, "relative")
    print(cursor.fetchone())
​
    # 移动游标位置  使用绝对位置
    cursor.scroll(0, "absolute")
    print(cursor.fetchone())
​
    print(cursor.fetchall())
    # 注意 游标移动到末尾后无法在读取到数据 若需重复读取数据,需要使用scroll来移动游标
​
except Exception as e:
    print("连接服务器失败.....")
    print(type(e),e)
finally:
    if cursor:
        cursor.close()
        print("关闭游标")
    if conn:
        conn.close()
        print("关闭链接")

上述代码中 fetch 相关函数返回值类型为元组,使用起来不够方便,我们可以在创建游标时指定游标类型为字典类型像这样:

cursor = conn.cursor(pymysql.cursors.DictCursor)

sql注入攻击

何为sql注入

sql注入指的是,用户在输入数据时,按照sql的语法,来编写带有攻击目的的sql语句,并插入到原始语句中执行.

例如:登录功能,需要用户输入用户名和密码

正常的一个登录功能代码如下:

try:
    conn = pymysql.connect(host="127.0.0.1",port=3306,user="root",password="",db="day46",)
    print("连接服务器成功!")
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    user = input("username:")
    password = input("password:")
    
    count = cursor.execute("select *from user where name = '%s' and password = '%s'" % (user,password))
    if count:
        print("登录成功!")
    else:
        print("登录失败!")
except Exception as e:
    print(type(e),e)
finally:
    if cursor:cursor.close()
    if conn: conn.close()

上述代码有被注入攻击的危险

尝试在用户名中输入以下内容,密码随意
 jerry' — ass 
或者连用户名都不用写
' or 1 = 1 -- asaa

解决方案

1.客户端在发送sql给服务器前进行re判断

这样的问题在于一些程序可以模拟客户端直接发送请求给服务器

2.在服务器端将sql交给mysql是作进一步处理,相关的代码其实pymysql已经做了封装

我们只要保证不要自己来拼接sql语句即可,将拼接参数操作交给pymysql.

try:
    conn = pymysql.connect(host="127.0.0.1",port=3306,user="root",password="",db="day46",)
    print("连接服务器成功!")
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    user = input("username:")
    password = input("password:")
​
    sql = "select *from user where name = %s and password = %s"
    print(sql)
    count = cursor.execute(sql,(user,password)) # 参数交给模块
    if count:
        print("登录成功!")
    else:
        print("登录失败!")
except Exception as e:
    print(type(e),e)
finally:
    if cursor:cursor.close()
    if conn: conn.close()

增删改

import pymysql
​
​
# 1.建立连接
try:
    conn = pymysql.connect(host="127.0.0.1",port=3306,user="root",password="",db="day46",)
    print("连接服务器成功!")
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    #增
    #sql = "insert into user values(null,%s,%s,%s)"
    #count = cursor.execute(sql,("tom","man","123321"))
    # 一次性插入多条记录
    #sql = "insert into user values (null,%s,%s,%s)"
    #count = cursor.executemany(sql, [("周芷若","woman","123"), ("赵敏","woman","321")])
    
    #删
    # count = cursor.execute("delete from user where id = 1")
​
    
    #改
    count = cursor.execute("update user set name = '刘大炮' where id = 1")
​
    if count:
        print("执行成功!")
    else:
        print("执行失败!")
​
    # 获取最新的id
    # print(cursor.lastrowid)
except Exception as e:
    print(type(e),e)
​
finally:
    if cursor:cursor.close()
    if conn: conn.close()

强调:pymysql 对于数据的增删改默认都不会生效,必须调用链接对象的commit()来提交修改 或者在创建链接对象时指定为自动提交;

conn.commit() 
#或者创建链接对象时指定为自动提交
conn = pymysql.connect(host="127.0.0.1",port=3306,user="root",password="",db="day46",autocommit=True)

excutemany

视图

什么是视图

视图是由一张表或多张表的查询结果构成的一张虚拟表

视图的作用

1. 简化sql语句的编写

2. 限制可以查看的数据 可以使用权限来完成 权限某一个库 的某一个表 的某一个字段 例如:一张表存储了所有人的工资 但是每个员工只能看自己的工资信息 那就可以用视图来限制

视图的特点

1. 对于视图的增删改查,都会同步到原始表

2. 每一次对视图的查询,本质上都是执行了之前创建视图时,指定的sql语句

对于原始表的修改,也能在视图中查看到,前提是你修改的数据包含在创建视图时指定的sql语句中

使用方法

创建视图

CREATE [OR REPLACE] VIEW view_name [(column_list)]
AS select_statement

加上OR REPLACE 时如果已经存在相同视图则替换原有视图

column_list指定哪些字段要出现在视图中

注意:由于是一张虚拟表,视图中的数据实际上来源于其他其他表,所以在视图中的数据不会出现在硬盘上

使用视图

视图是一张虚拟表 所以使用方式与普通表没有任何区别

查看视图

1.desc view_name; //查看数据结构 索引信息不会体现在视图中

2.show create view view_name; //查看 创建语句

修改视图

alter view view_name as select_statement

删除视图

drop view view_name

注意 对视图数据的insert update delete 会同步到原表中,但由于视图可能是部分字段,很多时候会失败

总结:mysql可以分担程序中的部分逻辑,但这样一来后续的维护会变得更麻烦

如果需要改表结构,那意味着视图也需要相应的修改,没有直接在程序中修改sql来的方便

触发器

什么是触发器

触发器是一段与某个表相关的mysql程序 当这个表在某个时间点发生了某种事件时 将会自动执行相应的触发器程序

何时使用触发器

当我们想要在一个表记录被更新时做一些操作时就可以使用触发器

但是我们完全可以在python中来完成这个事情,因为python的扩展性更强,语法更简单

创建触发器

语法:
CREATE TRIGGER t_name t_time t_event ON table_name FOR EACH ROW
begin
stmts.....
end

支持的时间点(t_time):时间发生前和发生前后 before|after

支持的事件(t_event): update insert delete

在触发器中可以访问到将被修改的那一行数据 根据事件不同 能访问也不同 update 可用OLD访问旧数据 NEW访问新数据 insert 可用NEW访问新数据 delete 可用OLD访问旧数据

可以将NEW和OLD看做一个对象其中封装了这条数据的所有字段

delimiter 用于修改默认的行结束符 ,由于在触发器中有多条sql语句他们需要使用分号来结束,但是触发器是一个整体,所以我们需要先更换默认的结束符,在触发器编写完后在将结束符设置回分号

注意:

1.外键不能触发事件 主表删除了某个主键 从表也会相应删除 但是并不会执行触发器 2.触发器中不能使用事务,不能使用select 查询数据 3.相同时间点的相同事件的触发器 不能同时存在

删除触发器

语法:
drop trigger trigger_name;
案例:
drop trigger trigger1;

同样的这种需求我们完全可以在python中来完成! mysql最想完成的事情是将所有能处理的逻辑全部放到mysql中,那样一来应用程序开发者的活儿就变少了,相应的数据库管理员的工资就高了,可惜大多中小公司都没有专门的DBA;

疑惑:修改行结束符后,触发器内的sql语句任然是以分号结束,为什么? 实际上在mysql中输入分号回车,mysql会立即将语句发送给服务器端,修改行结束符仅仅是告诉mysql客户端,语句没有写完,不要立即发送!

事物

什么是事务

事务是逻辑上的一组操作,要么都成功,要么都失败

为什么需要事务

很多时候一个数据操作,不是一条sql语句就完成的,可能有很多个sql语句,如果部分sql执行成功而部分sql执行失败将导致数据错乱!

使用事务

start transaction; --开启事物,在这条语句之后的sql将处在同一事务,并不会立即修改数据库

commit;--提交事务,让这个事务中的sql立即执行数据的操作,

rollback;--回滚事务,取消这个事务,这个事务不会对数据库中的数据产生任何影响

案例:转账过程中发生异常

#准备数据
create table account(
    id int primary key auto_increment,
    name varchar(20),
    money double
);
insert into account values(1,'赵大儿子',1000);
insert into account values(2,'刘大牛',1000);
insert into account values(3,'猪头三',1000);
insert into account values(4,'王进',1000);
insert into account values(5,'黄卉',1000);
​
# 赵大儿子给刘大牛转账1000块
# 未使用事务
update account set money = money - 1000 where id = 1;
update account set moneys = money - 1000 where id = 1; # money打错了导致执行失败

python中使用事务案例:

try:
    conn = pymysql.connect(host="127.0.0.1",user="root",password="",db="day46")
    print("连接服务器成功!")
​
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    sql = 'update account set money = money - 1000 where id = 1;'
    sql2 = 'update account set money = money + 1000 where id = 2;'  # money打错了将导致执行失败
    try:
        cursor.execute(sql)
        cursor.execute(sql2)
        conn.commit()
        print("执行成功 提交")
    except:
        print("发送错误   回滚..")
        conn.rollback()
​
except Exception as e:
    print("连接服务器失败.....")
    print(type(e),e)
finally:
    if cursor:cursor.close()
    if conn:conn.close()

 

注意:事务的回滚的前提是能捕捉到异常,否则无法决定何时回滚,Python中很简单就实现了,另外mysql中需要使用存储过程才能捕获异常!

事务的四个特性:

原子性:

事务是一组不可分割的单位,要么同时成功,要么同时不成功

一致性:

事务前后的数据完整性应该保持一致,(数据库的完整性:如果数据库在某一时间点下,所有的数据都符合所有的约束,则称数据库为完整性的状态);

隔离性:

事物的隔离性是指多个用户并发访问数据时,一个用户的事物不能被其它用户的事务所干扰,多个并发事务之间数据要相互隔离

持久性:

持久性是指一个事物一旦被提交,它对数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响

事务的用户隔离级别:

数据库使用者可以控制数据库工作在哪个隔离级别下,就可与防止不同的并发问题

read_uncommitted --不做任何隔离,可能脏读(读取到其他事务未提交的数据)

read_committed----可以防止脏读,不能防止不可重复读(并发修改),和幻读(并发添加和删除),

Repeatable_read --可以防止脏读,不可重复读(不能并发修改),不能防止幻读

Serializable--数据库运行在串行化实现,所有问题都没有,就是性能低

修改隔离级别:

select @@tx_isolation;--查询当前级别

set[session|global] transaction isolation level .... ;修改级别

实例:

set global transaction isolation level Repeatable read ;

修改后重新连接服务器生效

配置文件:

transaction_isolation = REPEATABLE-READ

存储过程

什么是存储过程

存储过程是一组任意的sql语句集合,存储在mysql中,调用存储过程时将会执行其包含的所有sql语句;与python中函数类似;

为什么使用存储过程

回顾触发器与视图都是为了简化应用程序中sql语句的书写,但是还是需要编写,而存储过程中可以包含任何的sql语句,包括视图,事务,流程控制等,这样一来,应用程序可以从sql语句中完全解放,mysql可以替代应用程序完成数据相关的的逻辑处理!

那我们以后都是用存储过程不就完了?

三种开发方式对比

1.应用程序仅负责业务逻辑编写,所有与数据相关的逻辑都交给mysql来完成,通过存储过程(推荐使用)

优点:

应用程序与数据处理完解耦合,一堆复杂的sql被封装成了一个简单的存储过程,考虑到网络环境因素,效率高

应用程序开发者不需要编写sql语句,开发效率高

缺点:

python语法与mysql语法区别巨大,学习成本高

并且各种数据库的语法大不相同,所以移植性非常差

应用程序开发者与BDA的跨部门沟通成本高,造成整体效率低

2.应用程序不仅编写业务逻辑,还需要编写所有的sql语句

优点:扩展性高,对于应用程序开发者而言,扩展性和维护性相较于第一种都有所提高

缺点:sql语句过于复杂,导致开发效率低,且需要考虑sql'优化问题

3.应用程序仅负责业务逻辑,sql语句的编写交给ORM框架,(常用解决方案)

优点:应用程序开发者不需要编写sql语句,开发效率高

缺点:执行效率低,由于需要将对象的操作转化为sql语句,且需要通过网络发送大量sql

创建存储过程

create procedure pro_name(p_Type p_name data_type)
begin
sql语句......流程控制
end

p_type 参数类型

in 表示输入参数

out 表示输出参数

inout表示既能输入又能输出

p_name 参数名称

data_type 参数类型 可以是mysql支持的数据类型

 

案例:使用存储过程完成对student表的查询

案例:转账过程中发生异常

#准备数据
create table account(
    id int primary key auto_increment,
    name varchar(20),
    money double
);
insert into account values(1,'赵大儿子',1000);
insert into account values(2,'刘大牛',1000);
insert into account values(3,'猪头三',1000);
insert into account values(4,'王进',1000);
insert into account values(5,'黄卉',1000);
​
# 赵大儿子给刘大牛转账1000块
# 未使用事务
update account set money = money - 1000 where id = 1;
update account set moneys = money - 1000 where id = 1; # money打错了导致执行失败

python中使用事务案例:

try:
    conn = pymysql.connect(host="127.0.0.1",user="root",password="",db="day46")
    print("连接服务器成功!")
​
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    sql = 'update account set money = money - 1000 where id = 1;'
    sql2 = 'update account set money = money + 1000 where id = 2;'  # money打错了将导致执行失败
    try:
        cursor.execute(sql)
        cursor.execute(sql2)
        conn.commit()
        print("执行成功 提交")
    except:
        print("发送错误   回滚..")
        conn.rollback()
​
except Exception as e:
    print("连接服务器失败.....")
    print(type(e),e)
finally:
    if cursor:cursor.close()
    if conn:conn.close()

 

注意:事务的回滚的前提是能捕捉到异常,否则无法决定何时回滚,Python中很简单就实现了,另外mysql中需要使用存储过程才能捕获异常!

事务的四个特性

原子性:

事务是一组不可分割的单位,要么同时成功,要么同时不成功

一致性:

事务前后的数据完整性应该保持一致,(数据库的完整性:如果数据库在某一时间点下,所有的数据都符合所有的约束,则称数据库为完整性的状态);

隔离性:

事物的隔离性是指多个用户并发访问数据时,一个用户的事物不能被其它用户的事务所干扰,多个并发事务之间数据要相互隔离

持久性:

持久性是指一个事物一旦被提交,它对数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响

事务的用户隔离级别

数据库使用者可以控制数据库工作在哪个隔离级别下,就可与防止不同的并发问题

read_uncommitted --不做任何隔离,可能脏读(读取到其他事务未提交的数据)

read_committed----可以防止脏读,不能防止不可重复读(并发修改),和幻读(并发添加和删除),

Repeatable_read --可以防止脏读,不可重复读(不能并发修改),不能防止幻读

Serializable--数据库运行在串行化实现,所有问题都没有,就是性能低

修改隔离级别

select @@tx_isolation;--查询当前级别 
​
set[session|global] transaction isolation level  ....   ;修改级别
​
实例:
​
set global transaction isolation level Repeatable read ;
​
修改后重新连接服务器生效
​
配置文件:
​
transaction_isolation = REPEATABLE-READ

 

存储过程

什么是存储过程

存储过程是一组任意的sql语句集合,存储在mysql中,调用存储过程时将会执行其包含的所有sql语句;与python中函数类似;

为什么使用存储过程

回顾触发器与视图都是为了简化应用程序中sql语句的书写,但是还是需要编写,而存储过程中可以包含任何的sql语句,包括视图,事务,流程控制等,这样一来,应用程序可以从sql语句中完全解放,mysql可以替代应用程序完成数据相关的的逻辑处理!

那我们以后都是用存储过程不就完了?

三种开发方式对比

1. 应用程序仅负责业务逻辑编写,所有与数据相关的逻辑都交给mysql来完成,通过存储过程(推荐使用)

优点:

应用程序与数据处理完解耦合,一堆复杂的sql被封装成了一个简单的存储过程,考虑到网络环境因素,效率高

应用程序开发者不需要编写sql语句,开发效率高

缺点:

python语法与mysql语法区别巨大,学习成本高

并且各种数据库的语法大不相同,所以移植性非常差

应用程序开发者与BDA的跨部门沟通成本高,造成整体效率低

2. 应用程序不仅编写业务逻辑,还需要编写所有的sql语句

优点:扩展性高,对于应用程序开发者而言,扩展性和维护性相较于第一种都有所提高

缺点:sql语句过于复杂,导致开发效率低,且需要考虑sql'优化问题

3. 应用程序仅负责业务逻辑,sql语句的编写交给ORM框架,(常用解决方案)

优点:应用程序开发者不需要编写sql语句,开发效率高

缺点:执行效率低,由于需要将对象的操作转化为sql语句,且需要通过网络发送大量sql

创建存储过程

create procedure pro_name(p_Type p_name data_type)
begin
sql语句......流程控制
end

p_type 参数类型

in 表示输入参数

out 表示输出参数

inout表示既能输入又能输出

p_name 参数名称

data_type 参数类型 可以是mysql支持的数据类型

 

案例:使用存储过程完成对student表的查询

delimiter //
create procedure p1(in m int,in n int,out res int)
begin
    select *from student where chinese > m and chinese < n;
    #select *from student where chineseXXX > m and chinese < n; 修改错误的列名以测试执行失败
    set res = 100;
end//
delimiter ;
​
#调用存储过程
call p1(70,80,@res);
#查看执行结果
select @res;

​

需要注意的是,存储过程的out类参数必须是一个变量,不能是值;@表示定义局部变量

​

在python中调用存储过程

import  pymysql
#建立连接
conn = pymysql.connect(
    host="127.0.0.1",
    user="root",
    password="admin",
    database="db02",
    autocommit = auto_commit
)
# 获取游标
cursor = conn.cursor(pymysql.cursors.DictCursor)
​
}=-===]=-00
# 调用用存储过程
cursor.callproc("p1",(70,80,0)) #p1为存储过程名 会自动为为每个值设置变量,名称为 @_p1_0,@_p1_1,@_p1_2
# 提取执行结果时否有结果取决于存储过程中的sql语句
print(cursor.fetchall())
# 获取执行状态
cursor.execute("select @_p1_2")
print(cursor.fetchone())

此处pymysql会自动将参数都设置一个变量所以可以直接传入一个值,当然值如果作为输出参数的话,传入什么都无所谓!

删除存储过程

drop procedure 过程名称;

修改存储过程意义不大,不如删除重写!

查看存储过程

 #当前库所有存储过程名称
 select `name` from mysql.proc where db = 'db02' and `type` = 'PROCEDURE';
 #查看创建语句
 show create procedure p1;

存储过程中的事务应用

存储过程中支持任何的sql语句包括事务!

案例:模拟转账中发生异常,进行回滚

delimiter //
create PROCEDURE p5(OUT p_return_code char(20))
BEGIN 
    DECLARE exit handler for sqlexception 
    BEGIN 
        # ERROR 
        set p_return_code = "出现异常,回滚!"; 
        rollback; 
    END; 
    # exit 也可以换成continue 表示发生异常时继续执行
    DECLARE exit handler for sqlwarning 
    BEGIN 
        # WARNING 
        set p_return_code = "出现警告,回滚!";
        rollback; 
    END; 
​
    START TRANSACTION; 
    update account set money = money - 1000 where id = 1;
    update account set moneys = money - 1000 where id = 1; # moneys字段导致异常
    COMMIT; 
​
    # SUCCESS 
    set p_return_code = 0; #0代表执行成功
END //
delimiter ;
​
#在mysql中调用存储过程
call p5(@res);
select @res;
​

总结:抛开沟通成本,学习成本,存储过程无疑是效率最高的处理方式,面试会问,一些公司也有一些现存的存储过程,重点掌握!

函数

函数与python中的定义一致,不在啰嗦!

内置函数

日期相关:

字符串相关:

数字相关:

其他函数:

当然也包括之前学习的聚合函数

自定义函数

语法:

CREATE FUNCTION f_name(paramters)
returns dataType;
return value;

说明: paramters 只能是in 输入参数 参数名 类型 必须有返回值 不能加begin 和end returns 后面是返回值的类型 这里不加分号 return 后面是要返回的值

案例: 将两数相加

create function addfuntion(a int,b int)
returns int return a + b;
54asd#执行函数
select addfuntion(1,1);

注意:

函数只能返回一个值 函数不涉及数据的增删改查 就是一个通用的功能 调用自定义的函数 与调用系统的一致 不需要call 使用select 可获得返回值 函数中不能使用sql语句 就像在java中不能识别sql语句一样

 

数据备份与恢复

使用mysqldump程序进行备份

mysqldump -u -p db_name [table_name,,,] > fileName.sql
​

可以选择要备份哪些表 如果不指定代表 全部备份

#示例：
#单库备份
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql
​
#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql
​
#备份所有库
mysqldump -uroot -p123 --all-databases > all.sql

使用 mysql 进行恢复

1.退出数据库后

mysql -u -p < filename.sql;

2.不用退出数据库

2.1 创建空数据库

2.2选择数据库

2.3然后使用source filename; 来进行还原

use db1;
source /root/db1.sql

数据库迁移

务必保证在相同版本之间迁移
# mysqldump -h 源IP -uroot -p123 --databases db1 | mysql -h 目标IP -uroot -p456

索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构; 也称之为key

索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

为什么需要索引

思考:一个项目正常运行后,对数据库的操作中,哪些操作是最频繁的?

对数据库的写操作(增加 删除 修改)频繁吗?

对数据库的读操作(查询)频繁吗?

相比较下,对数据的读操作会更加频繁,比例在10:1左右,也就是说对数据库的查询操作是非常频繁的

随着时间的推移,表中的记录会越来越多,此时如果查询速度太慢的话对用户体验是非常不利的

索引是提升查询效率最有效的手段!

简单的说索引的就是用帮我们加快查询速度的

​

需要注意的是：在数据库中插入数据会引发索引的重建

​

索引带来的问题

例如800页数据 可能需要10页索引

1. 添加索引后,整体的数据更大了 (占用额外的磁盘空间)

2. 由于有了索引,多数数据的添加修改删除,都会引发索引的重建 (效率降低)

误区:索引不是越多越好

磁盘IO问题(了解)

数据库的数据最终存储到了硬盘上

机械硬盘由于设计原理,导致查找数据时需要有一个寻道时间与平均延迟时间,常规硬盘寻道为5ms,平均延迟按照每分钟7200转来计算,7200/60 = 120 ; 1000/120/2 = 4ms 总共为9ms,那么9毫秒对于cpu而言已经非常非常的长了,足够做很多运算操作,目前最新的处理器每秒能处理数万亿次运算,拿一个非常垃圾的处理器来举例子,假设处理器每秒处理5亿次计算,每毫秒是50万次运算,9ms可以进行450万次运算,数据库中成千上万的数据,每条数据9ms显然慢到不行!

操作系统预读取(了解)

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

索引的实现

索引最终的目的是要尽可能降低io次数,减少查找的次数,以最少的io找到需要的数据,mysql 通过B+树结构来组织数据

mysql 通过B+树这种结构来减少IO次数 B+树中每一个磁盘快 有两个数据项 一个三个地址 在查询时 会会比较大小 如果小于左边,就访问p1地址,如果大于右边就访问p3的地址,否则就访问中间p2的地址,与二分法原理相同,只不过每次把数据分成三段

树的结构越低越好 所以建议把数据量小的作为索引 来降低高度

最左匹配原则

当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候(多字段联合索引)，b+树会按照从左到右的顺序来建立搜索树，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

聚集索引

mysql官方文档原文: 插入了解 或折叠

MySQL为表把它的数据词典信息以.frm文件的形式存在数据库目录里，这对所有MySQL存储引擎都是真的。但 是每个InnoDB表在表空间内的InnoDB内部数据词典里有它自己的条目。当MySQL移除表或数据库，它不得不 删除.frm文件和InnoDB数据词典内的相应条目。这就是为什么你不能在数据库之间简单地移动.frm文件来移 动InnoDB表。

每个InnoDB表有专门索引，被称为clustered index，对行的数据被存于其中。如果你对你的表定义一 个PRIMARY KEY， 主键的索引是集束索引。

如果你没有为表定义PRIMARY KEY，MySQL拾取第一个仅有NOT NULL列的UNIQUE索引作为主键，并 且InnoDB把它当作集束索引来用。如果表中没有这样一个索引，InnoDB内部产生一个集束索引，其中 用InnoDB在这样一个表内指定给行的行ID来排序行。行ID是一个6字节的域，它在新行被插入的时候简单地增加。因此被行ID排序的行是物理地按照插入顺序排的。

通过集束索引访问一个行是较快的，因为行数据是在索引搜索引导的同一页面。如果表是巨大的，当对比于传 统解决方案，集束索引构架经常节约磁盘I/O。(在许多数据库，数据传统地被存在与索引记录不同的页)。

在InnoDB中，非集束索引里的记录(也称为第二索引)包含对行的主键值。InnoDB用这个 主键值来从集束索 引中搜索行。注意，如果主键是长的，第二索引使用更多空间。

简单总结:

聚焦索引的特点:

• 叶子节点保存的就是完整的一行记录,如果设置了主键,主键就作为聚集索引,

• 如果没有主键,则找第一个NOT NULL 且QUNIQUE的列作为聚集索引,

• 如果也没有这样的列,innoDB会在表内自动产生一个聚集索引,它是自增的

主键索引,速度快,因为只要根据id找到叶子节点,那么改行的所有数据都拿到了 innoDB,需要用主键索引来建立数据结构,所以每一个表都应该有主键

辅助索引

除了聚集索引之外的索引都称之为辅助索引或第二索引,包括 foreign key 与 unique

辅助索引的特点:

其叶子节点保存的是索引数据与所在行的主键值,InnoDB用这个 主键值来从聚集索引中搜查找数据

覆盖索引

覆盖索引指的是需要的数据仅在辅助索引中就能找到:

#假设stu表的name字段是一个辅助索引
select name from stu where name = "jack";

这样的话则不需要在查找聚集索引数据已经找到

回表

如果要查找的数据在辅助索引中不存在,则需要回到聚集索引中查找,这种现象称之为回表

# name字段是一个辅助索引 而sex字段不是索引 
select sex from stu where name = "jack";

需要从辅助索引中获取主键的值,在拿着主键值到聚集索引中找到sex的值

查询速度对比:聚集索引 > 覆盖索引 > 非覆盖索引

索引的优化

分为两方面

1. 索引结构的优化 数据量小的 重复度低的

2. sql语句的优化

   1. 最好使用主键来查询

   2. sql语句中需要包含,辅助缩影的字段才能加速查询

   3. 降低查询范围

   4. 避免使用select*(需要哪一列就查哪一列)

   5. and语句会自动选择具备索引的字段,优先判断

   6. or语句必须是每个字段都有索引才能加速查询,所以避免使用or

避免在模糊匹配中,在最前面使用%(需要全表扫描)

select count(*) from usr where name like "%asasasa";

不要在对主键进行运算,先算出具体的值在来查询

例如
 where id*10 = 100;
 where id = 100 / 10;

3. and 与 or

   1.在and 语句中 mysql 会优先查询带有索引的字段,无论书写位置在前还是在后 name 有索引 但是重复度高 id 没有索引 email 有 会跳过id直接查email

select count(*)   from usr where name = "jack" and id = 1 and email = "xxxx";

and 语句没有优化的余地

2. or语句中,不会自动选择有索引的,是依次执行,无论是否有条件成立,都查一遍,所以一定避免使用or语句

or语句的优化

select *from usr where name = "张三" or name = "李四";
select *from usr where name in ("张三","李四");
select *from usr where name = "张三"
union
select *from usr where name = "李四";

4. 多字段联合索引

如果要查询的字段较多,如果为每一个字段都创建索引,会造成额外的容量的占用,并且当你修改一条记录时,有可能所有索引都需要重建,会非常慢

顺序是重点
创建索引时 把重复度低的字段放在最左边 依次排开
create index all_index on usr (email,name,gender);
编写sql时 保证重复度低的字段出现在sql中即可

创建索引的语法
create index 索引的名字 on 表名(字段名);
​
删除索引
drop index 索引名字 on 表名;