MySql教程
python学习-Day49-MySQL
本文主要是介绍python学习-Day49-MySQL，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
目录- python操作MySQL
- pymysql模块
- 基本使用
- 代码
- execute和executemany
- 创建数据库案例
- 插入单条数据
- 批量插入多条数据
- 查询数据
- fetchone()
- fetchmany()
- fetchall()
- 控制光标
- 基本使用
- SQL注入问题
- 介绍
- 二次确认
- 修改表SQL语句补充
- 视图
- 视图的概念
- 视图的作用
- 视图的制作
- 触发器
- 概念
- 作用
- 触发器种类
- 触发器创建
- 语法
- 具体案例
- 触发器其他补充
- 事务
- 概念
- 使用事务的目的
- 事务的四大特性 — ACID
- 使用案例
- 事务的创建
- 代码
- 存储过程
- 参数
- in — 输入参数
- out — 回传参数
- inout — 输出输出参数
- 参数
- 函数
- 流程控制
- 索引
- 概念
- 索引的基本用法
- 索引的影响
- B+树
- 索引的优势和劣势
- pymysql模块
python操作MySQL
python中支持操作MySQL的模块很多 其中最常见的当属'pymysql'
pymysql模块
由于能操作MySQL的模块是第三方模块，我们需要pip安装。
pip3 install pymysql
基本使用
# 使用过程: 引用API模块 获取与数据库的连接 执行sql语句与存储过程 关闭数据库连接
代码
# 导入模块
import pymysql
# 1.链接服务端
conn_obj = pymysql.connect(
host='127.0.0.1', # MySQL服务端的IP地址
port=3306, # MySQL默认PORT地址(端口号)
user='root', # 用户名
password='jason123', # 密码 也可以简写 passwd
database='jp04_3', # 库名称 也可以简写 db
charset='utf8' # 字符编码 千万不要加杠utf-8
)
'''
要想操作数据库，光连接数据是不够的，必须拿到操作数据库的游标，才能进行后续的操作，比如：
读取数据、添加数据。通过获取到的数据库连接实例conn下的cursor()方法来创建游标。游标用来接收返回结果。
'''
# 2.产生获取命令的游标对象
cursor = conn_obj.cursor(
cursor=pymysql.cursors.DictCursor
) # 括号内不写参数 数据是元组 不够精确，添加参数则会将数据处理成字典
# 3.编写SQL语句
sql = 'select * from teacher;' # SQL语句会被高亮显示
# 4.执行SQL语句
affect_rows = cursor.execute(sql1)
print(affect_rows) # 执行SQL语句之后受影响的行数
# 5.获取结果
res = cursor.fetchall()
print(res)
execute和executemany
execute(query,args=None) # 函数作用：执行单条的sql语句，执行成功后返回受影响的行数 参数说明： query：要执行的sql语句，字符串类型 args：可选的序列或映射，用于query的参数值。如果args为序列，query中必须使用%s做占位符；如果args为映射，query中必须使用%(key)s做占位符 executemany(query,args=None) # 函数作用：批量执行sql语句，比如批量插入数据，执行成功后返回受影响的行数 参数说明： query：要执行的sql语句，字符串类型 args：嵌套的序列或映射，用于query的参数值
创建数据库案例
'''创建数据库'''
import pymysql
#打开数据库连接，不需要指定数据库，因为需要创建数据库
conn = pymysql.connect('localhost',user = "root",passwd = "root")
#获取游标
cursor=conn.cursor()
#创建pythonBD数据库
cursor.execute('CREATE DATABASE IF NOT EXISTS pythonDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;')
cursor.close()#先关闭游标
conn.close()#再关闭数据库连接
print('创建pythonBD数据库成功')
插入单条数据
'''插入单条数据'''
import pymysql
#打开数据库连接，不指定数据库
conn=pymysql.connect('localhost','root','root')
conn.select_db('pythondb')
cur=conn.cursor()#获取游标
#创建user表
cur.execute('drop table if exists user')
sql="""CREATE TABLE IF NOT EXISTS `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0"""
cur.execute(sql)
insert=cur.execute("insert into user values(1,'tom',18)")
print('添加语句受影响的行数：',insert)
#另一种插入数据的方式，通过字符串传入值
sql="insert into user values(%s,%s,%s)"
cur.execute(sql,(3,'kongsh',20))
cur.close()
conn.commit()
conn.close()
print('sql执行成功')
批量插入多条数据
'''插入多条数据'''
import pymysql
#打开数据库连接，不指定数据库
conn=pymysql.connect('localhost','root','root')
conn.select_db('pythondb')
#获取游标
cur=conn.cursor()
#另一种插入数据的方式，通过字符串传入值
sql="insert into user values(%s,%s,%s)"
insert=cur.executemany(sql,[(4,'wen',20),(5,'tom',10),(6,'test',30)])
print ('批量插入返回受影响的行数：',insert)
cur.close()
conn.commit()
conn.close()
print('sql执行成功')
'注意'：批量插入多条sql语句采用的是executemany(sql,args)函数，返回受影响的行
数。args参数是一个包含多个元组的列表，每个元组对应一条mysql中的一条数据。这里的%s不需要加引号，否则插入数据的数据会类型错误。
查询数据
使用execute()函数得到的只是受影响的行数，并不能真正拿到查询的内容。cursor对象还提供了3种提取数据的方法：fetchone、fetchmany、fetchall.。每个方法都会导致游标动，所以必须注意游标的位置。 'cursor.fetchone()':获取游标所在处的一行数据，返回元组，没有返回None 'cursor.fetchmany(size)':接受size行返回结果行 'cursor. fetchall()':接收全部的返回结果行。
fetchone()
复制代码123PYTHON 从execute()函数的查询结果中取数据，以元组的形式返回游标所在处的一条数据，如果游标所在处没有数据，将返回空元组，该数据执行一次，游标向下移动一个位置。 'fetchone()函数必须跟exceute()函数结合使用，并且在exceute()函数之后使用。'
fetchmany()
从exceute()函数结果中获取游标所在处的size条数据，并以元组的形式返回，元组的每一个元素都也是一个由一行数据组成的元组，如果size大于有效的结果行数，将会返回cursor.arraysize条数据，但如果游标所在处没有数据，将返回空元组。查询几条数据，游标将会向下移动几个位置。 'fetmany()函数必须跟exceute()函数结合使用，并且在exceute()函数之后使用。'
fetchall()
获取游标所在处开始及以下所有的数据，并以元组的形式返回，元组的每一个元素都也是一个由一行数据组成的元组，如果游标所在处没有数据，将返回空元组。执行完这个方法后，游标将移动到数据库表的最后。
控制光标
获取SQL语句执行的结果 跟读取文件内容的read方法几乎一致(光标)
# 移动光标，scroll(value, mode)方法
参数：
当mode='relative'时，代表相对移动，默认值，value就是移动的长度，
value>0向后移动（从位置0移动到位置2），value<0向前移动（比如从位置2移动到位置0）
当mode='absolute'时，代表绝对移动，value就代表移动的绝对位置，
value=0就代表移动到位置0处，就是结果集开头，value=3就是移动到位置3处，也就是第4条记录处。
cursor.scroll(1, 'relative') # 相对于当前位置往后移动一个单位
cursor.scroll(1, 'absolute') # 相对于起始位置往后移动一个单位
SQL注入问题
介绍
# SQL 注入是一种非常常见的数据库攻击手段，SQL 注入漏洞也是网络世界中最普遍的漏洞 之一。 'SQL 注入其实就是恶意用户通过在表单中填写包含 SQL 关键字的数据来使数据库执行非常 规代码的过程。'简单来说，就是数据「越俎代庖」（yuè zǔ dài páo）做了代码才能干的 事情。这个问题的来源是，SQL 数据库的操作是通过 SQL 语句来执行的，而无论是执行代 码还是数据项都必须写在SQL 语句之中，这就导致如果我们在数据项中加入了某些 SQL 语 句关键字（比如说 SELECT、DROP 等等），这些关键字就很可能在数据库写入或读取数据 时得到执行。
# 在正常的情况下，在登入时，我们需要输入正确的用户名和密码才能登入。
import pymysql
1.连接数据库
conn = pymysql.connect(
host='localhost',
port = 3306,
user = 'root',
passwd = '123456',
database = 'test',
charset = 'utf8'
)
cursor=conn.cursor(
cursor=pymysql.cursors.DictCursor
)
# 1.获取用户名和密码
name = input('请输入您的用户名>>>:').strip()
password = input('请输入您的密码>>>:').strip()
# 2.拼接查询语句
sql = "select * from userinfo where name=%s and password=%s;"
# 3.执行SQL语句
cursor.execute(sql, (name, password))
res = cursor.fetchall()
if res:
print('登录成功')
else:
print('用户名或密码错误')
# 写正确的用户名错误的密码也可以登录
用户名:jojo' -- balabala
密码:直接回车
# 用户名和密码都不需要也可以登录
用户名:xxx' or 1=1 -- balabala
密码:直接回车
"""上述现象就是典型的SQL注入问题"""
上述情况利用的是MySQL注释语法及逻辑运算符
# 解决SQL注入的问题其实也很简单 就是想办法过滤掉特殊符号
execute方法自带校验SQL注入问题 自动处理特殊符号
ps:涉及到敏感数据的拼接 全部交给execute方法即可!!!
sql = "select * from userinfo where name=%s and password=%s;"
cursor.execute(sql, (name, password))
二次确认
"""
数据的增删改查四个操作是有轻重之分的
查 不会影响真正的数据 重要程度最低
增、改、删 都会影响真正的数据 重要程度较高
pymysql针对增、改、删三个操作 都设置了二次确认 如果不确认则不会真正影响数据库
"""
方式1:代码直接编写
affect_row = cursor.execute(sql)
conn_obj.commit() # 手动二次确认
方式2:配置固定参数
conn_obj = pymysql.connect(
autocommit=True # 自动二次确认
)
修改表SQL语句补充
# 1.修改表的名字 rename alter table t1 rename ttt; # 2.添加字段 add alter table ttt add pwd int; '''默认是尾部追加字段''' alter table ttt add tid int after name; '''指定追加位置''' alter table ttt add nid int first; '''指定头部添加字段''' # 3.修改字段 change(名字类型都可)/modify(只能改类型不能改名字) alter table ttt change pwd password tinyint; # 4.删除字段 drop alter table ttt drop nid;
视图
视图的概念
通过SQL语句的执行得到的一张虚拟表 保存下来之后就称之为'视图'
'视图是一个虚拟表，是从数据库中一个或多个表中导出来的表'，其内容由查询定义。同真实表一样，视图包含一系列带有名称的列和行数据。但是，数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。
视图的作用
'如果需要频繁的使用一张虚拟表 可以考虑制作成视图 降低操作难度' eg: emp与dep表拼接 视图是存储在数据库中的查询的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语句的结束符
delimiter $$
临时修改的原因是因为触发器 存储过程等技术点 代码中也需要使用分号
如果不修改 则无法书写出完成的代码
"""
# 1.先创建两张表
# 案例
CREATE TABLE cmd (
id INT PRIMARY KEY auto_increment,
USER CHAR (32),
priv CHAR (10),
cmd CHAR (64),
sub_time datetime, #提交时间
success enum ('yes', 'no') #0代表执行失败
);
CREATE TABLE errlog (
id INT PRIMARY KEY auto_increment,
err_cmd CHAR (64),
err_time datetime
);
# 2.需求:cmd表插入数据的success如果值为no 则去errlog表中插入一条记录
delimiter $$ # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
if NEW.success = 'no' then # 新记录都会被MySQL封装成NEW对象
insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
end if;
end $$
delimiter ; # 结束之后记得再改回来，不然后面结束符就都是$$了
# 3.仅仅往cmd表中插入数据
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');
触发器其他补充
1.在 MySQL 5 中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一，即同一数据库中的两个表可能具有相同名字的触发器
2.每个表的每个事件每次只允许一个触发器，因此，每个表最多支持 6 个触发器，before/after insert、before/after delete、before/after update
# 查看当前库下所有的触发器信息
show triggers\G;
# 删除当前库下指定的触发器信息
drop trigger 触发器名称;
事务
概念
事务即 （transaction） ，是数据库系统区别于文件系统的重要特性之一。在文件系统中，如果我们正在写文件，但是操作系统崩溃了，那么文件中的数据可能会丢失。但是数据库可以通过事务机制来确保这一点。
使用事务的目的
事务会把数据库从一中状态转换为另一种状态。在数据库提交工作时，可以确保要么所有工作都已经保存了，要么所有修改都不保存。
事务的四大特性 — ACID
'A:原子性（atomicity）' 所谓原子性，是指整个数据库的每个事务都是不可分割的单位。只有事务中的所有 SQL 语句都执行成功，才算整个事务成功，事务才会被提交。如果事务中任何一个 SQL 语句执行失败，整个事务都应该被回滚。 场景：如在银行取款机取款，要么取款成功、要么取款失败。不能存在卡里钱扣了，取款机并没有出金额；或者钱取到了，但是卡里并没有减去该金额。 'C:一致性（consistency）' 所谓一致性，是指将数据库从一种一致性状态转换为下一种一致性状态。不允许数据库中的数据出现新老数据都有的情况，要么都是老数据，要么都是新数据。用更书面化的表达就是：数据的完整性约束没有被破坏。 场景：如在一个用户表中，存在一个身份证号的字段，且身份证号满足唯一约束条件；如果一个事务对身份证号进行了修改，在事务进行提交或回滚后，身份号信息变的不具有唯一性了，这就破坏了事务的一致性。 'I:隔离性（isolation）' 所谓隔离性，是指一个事务的影响在该事务提交前对其他事务都不可见，它通过锁机制来实现。 场景：多个并行交叉的事务间的操作可以相互分离，即多个事务对于其他事务不可见。 'D:持久性（durability）' 所谓持久性，是指事务一旦被提交，其结果就是永久性的。即使发生宕机等故障，数据库也能将数据恢复。 场景：事务提交后，所有变化都是永久的，即使数据库崩溃而需要恢复时也能保证恢复后提交的数不会丢失。
事物以第一个DML(insert、update、delete) 语句的执行作为开始，以下面的其中之一作为结束: COMMIT 或ROLLBACK 语句 DDL 或DCL 语句（自动提交） 用户会话正常结束 系统异常终止
"""
事务的概念
事务可以包含诸多SQL语句并且这些SQL语句
要么同时执行成功 要么同时执行失败 这是事务的原子性特点
事务的作用
戴某某欠了赵某某一笔钱 现在想要还钱
戴某某拿着交行的银行卡去招商银行的ATM机给赵某某的建行卡转钱
1.朝交行的服务器发送请求 修改戴某某账户余额(减钱)
2.朝建行的服务器发送请求 修改赵某某账户余额(加钱)
(重点)
ACID
A:原子性
一个事务是一个不可分割的整体 里面的操作要么都成立要么都不成立
C:一致性
事务必须使数据库从一个一致性状态变到另外一个一致性状态
I:隔离性
并发编程中 多个事务之间是相互隔离的 不会彼此干扰
D:持久性
事务一旦提交 产生的结果应该是永久的 不可逆的
课下可以自己百度搜索整理 上面是老师整理的简单概述版本
ps:辛辛苦苦一个月 换来的就是一条SQL语句!!!
"""
具体使用
1.创建表及录入数据
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);
2.事务操作
开启一个事务的操作
start transaction;
编写SQL语句(同属于一个事务)
update user set balance=900 where name='jason';
update user set balance=1010 where name='kevin';
update user set balance=1090 where name='tank';
事务回滚(返回执行事务操作之前的数据库状态)
rollback; # 执行完回滚之后 事务自动结束
事务确认(执行完事务的主动操作之后 确认无误之后 需要执行确认命令)
commit; # 执行完确认提交之后 无法回滚 事务自动结束
使用案例
事务的创建
'隐式的事务'：事务没有明显的开启和结束的标记 比如insert、update、delete语句 delete from 表 where id=1; '显示事务'：事物具有明显的开启和结束的标记 '前提'：必须先设置自动提交功能为禁用 set autocommit=0; # 步骤1：开启事务 set autocommit=0; start transaction;可选的 # 步骤2：编写事务中的sql语句(select、insert、update、delete) 语句1; 语句2; ...... # 步骤3：结束事务 commit;提交事务 rollback;回滚事务 开启事务的语句 update 表 set 张三丰的余额=500 where name='峤桑'; update 表 set 郭襄的余额=1500 where name='琴琴子'; 结束事务的语句;
代码
SHOW VARIABLES LIKE 'autocommit';
DROP TABLE IF EXISTS account;
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
balance DOUBLE
);
INSERT INTO account(username,balance)
VALUES('峤桑',1000),('琴琴子',1000);
#演示事务的使用步骤
#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance=500 WHERE username='峤桑';
UPDATE account SET balance=1500 WHERE username='琴琴子';
#结束事务
COMMIT;
SELECT * FROM account;
#试一下回滚的
#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance=1000 WHERE username='峤桑';
UPDATE account SET balance=1000 WHERE username='琴琴子';
#结束事务
ROLLBACK;
#在没有明确的结束标志，只是滞留在内存了。
存储过程
类似于python中的自定义函数
1. 结束符 DELIMITER $$ #应用于shell指令行 2. 声明存储过程 CREATE PROCEDURE 名称(入参或回参) 3. 开始与结束符 BEGIN ... END 4. 变量赋值 SET @变量名 = 1 5. 定义变量 DECLARE 变量名 int unsigned default 100; 6. 存储过程体 create function 存储函数名(参数) 7. 调用存储过程 call 存储过程名(入参) 8. 删除存储过程 drop procedure 名称 9. 查看某库中存储过程 show procedure status where db = '数据库名' 10. 查看特定存储过程 show create procedure 数据库名.存储过程名 ''' 查看存储过程具体信息 show create procedure pro1; 查看所有存储过程 show procedure status; 删除存储过程 drop procedure pro1; '''
delimiter $$ create procedure 名称(入参或回参) begin SQL语句 # select * from cmd; end $$ delimiter ; call 名称(入参或回参)
参数
存储过程的参数用在存储过程定义时，共有三种参数类型。 IN：表示调用者需要对存储过程传入参数。 OUT：表示调用者会得到一个或多个返回值。 INOUT：表示调用者既要传入值，又要传出值。
in — 输入参数
create PROCEDURE test(in data_in int)
begin
SELECT data_in;
set data_in = 5;
select data_in;
end
set @data = 1
call test(@data) #首先查出data_in = 1 修改之后 data_in = 5
select @data #data = 1,因为存储过程中修改的是局部变量，不影响全局
out — 回传参数
create PROCEDURE test(out data_out int)
begin
set data_out = 5;
end
set @data = 1
call test(@data)
select @data #data被修改，因为是回传参数。
inout — 输出输出参数
create PROCEDURE test(inout data_inout int)
begin
select data_inout; #data_inout = 1
set data_inout = 5;
end
set @data = 1
call test(@data)
select @data #data = 5
函数
注意与存储过程的区别，mysql内置的函数只能在sql语句中使用
"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 计算两个日期差值
...
流程控制
# python if判断
if 条件:
子代码
elif 条件:
子代码
else:
子代码
# js if判断
if(条件){
子代码
}else if(条件){
子代码
}else{
子代码
}
# MySQL if判断
if 条件 then
子代码
elseif 条件 then
子代码
else
子代码
end if;
# MySQL while循环
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT num ;
SET num = num + 1 ;
END WHILE ;
索引
索引就是一种数据结构
概念
官方介绍索引是帮助MySQL高效获取数据的数据结构。'类似于书的目录。意味着以后再查数据应该先找目录再找数据，而不是用翻页的方式查询数据。' 般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构 primary key 主键 unique key 唯一键 index key 索引键 上面三种key前两种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一，unique key:唯一)，而index key没有任何约束功能只会帮你加速查询 # ps:foreign key不是用来加速查询用的,不在我们研究范围之内
索引的基本用法
id name pwd post_comment addr age 基于id查找数据很快 但是基于addr查找数据就很慢 解决的措施可以是给addr添加索引 '''索引虽然好用 但是不能无限制的创建!!!'''
索引的影响
* 在表中有大量数据的前提下，创建索引速度会很慢 * 在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低
B+树
索引的底层数据结构是b+树
我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。
'b树 红黑树 二叉树 b*树 b+树' 上述结构都是为了更好的基于树查找到相应的数据 只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据 查询次数由树的层级决定，层级越低次数越少 一个磁盘块儿的大小是一定的，那也就意味着能存的数据量是一定的。如何保证树的层级最低呢？一个磁盘块儿存放占用空间比较小的数据项 思考我们应该给我们一张表里面的什么字段字段建立索引能够降低树的层级高度>>> 主键id字段
'聚集索引(primary key)'
'辅助索引(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';
索引的优势和劣势
# 优势：
可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些。
如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。
# 劣势：
索引会占据磁盘空间
索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅
要保存数据，还有保存或者更新对应的索引文件。
这篇关于python学习-Day49-MySQL的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
-
mysql ddl常用的方法和语句-icode9专业技术文章分享08-27
-
MySQL入门：轻松掌握数据库基础操作与技巧08-13
-
Seata和Mysql存储演示学习入门：初级开发者实战指南08-12
-
从 MySQL 迁移到 TiDB：使用 SQL-Replay 工具进行真实线上流量回放测试 SOP08-10
-
蔚来汽车 x TiDB丨单表超 20 亿条数据，从 MySQL 到 TiDB 的迁移思考与实践08-05
-
【Mysql】Docker下Mysql8数据备份与恢复07-22
-
MySQL报错Duplicate entry '0' for key 'PRIMARY'06-25
-
阿里 Canal 实时同步 MySQL 增量数据至 ClickHouse 数据库05-29
-
在Linux下管理MySQL的大小写敏感性05-24
-
MySQL查出时间比实际晚8小时的解决方案04-26
-
JPA不识别MySQL的枚举类型04-01
-
mysql数据库表卡死解决方法03-30
-
MySQL多数据源笔记5-ShardingJDBC实战03-15
-
natural join mysql03-11
-
关于VS2017，VS2015 中利用 EF使用Mysql 不显示数据源问题解决方案03-11
栏目导航
