MySQL

目录

一.python操作MySQL

1.介绍

2.操作步骤

（1）先连接MySQL数据库

（2）获取游标

（3）书写SQL语句

（4）开始执行SQL语句

（5）想获取到结果：

3.SQL注入问题

二.视图

1.什么是视图

2.为什么要用视图

3.如何用视图

4.注意事项

 三.触发器

1.什么是触发器

2.为什么要用触发器

3.如何创建触发器

四.事务

1.什么是事务

2.事务的作用

3.事务具有的四个属性

4.如何使用

五.存储过程

1.基本使用

2.三种开发模型

第一种：

第二种：

第三种：

3.创建存储过程

4.如何用存储过程

六.函数

七.流程控制

八.索引

1.什么是索引

2.索引的介绍

3.索引的影响

（1）在表中有大量数据的前提下，创建索引速度会很慢（建表的时候，如果明显需要索引，就提前加上）

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

4.补充

5.测试索引

---

一.python操作MySQL

1.介绍

MySQL本身就是一款C/S架构，有服务端、客户端，自身带了有客户端：mysql.exe

python这门语言成为了MySQL的客户端（对于一个服务端来说，客户端可以有很多）

2.操作步骤

1. 先连接MySQL数据库
2. 在python中书写SQL语句
3. 开始执行SQL语句，拿到结果
4. 在python中做处理（进一步对数据做处理）

需要第三方的一个模块：pymysql或者mysqldb或者mysqlclient

# pip install pymysql

import pymysql

（1）先连接MySQL数据库

conn=pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='1234',
    db='db10',
    charset='utf8',
    autocommit=True
)

（2）获取游标

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

（3）书写SQL语句

sql='select * from student'
sql='insert into teacher(tid, tname) values (7, "ly1")'

（4）开始执行SQL语句

affect_rows=cur.execute(sql)
 
# 输出结果16是影响的行数

需要二次确认：除了查询之外都要二次确认提交

conn.commit()
print(affect_rows)

（5）想获取到结果：

res=cur.fetchone()
res=cur.fetchall()
# res=cur.fetchmany(5)
# {'sid': 1, 'gender': '男', 'class_id': 1, 'sname': '理解'}
print(res) # (1, '男', 1, '理解')  元组类型
 
'''
for i in res:
    print(i.get("sid"))
'''                        取指定数据

3.SQL注入问题

import pymysql
 
 
# 连接MySQL服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db8_3',
    charset='utf8',
    autocommit=True  # 针对增 改 删自动二次确认
)
# 产生一个游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 编写SQL语句
username = input('username>>>:').strip()
password = input('password>>>:').strip()
# sql = "select * from userinfo where name='%s' and pwd='%s'" % (name,pwd)
sql = "select * from userinfo where name=%s and pwd=%s"
cursor.execute(sql,(username,password))
data = cursor.fetchall()
if data:
    print(data)
    print('登录成功')
else:
    print('用户名或密码错误')
 
 
# 1.只需要用户名也可以登录
username:>>>   kevin111 " -- ddasfdfsdfdsfsdfsdfsdfdsfsdfsd
username:>>>   xxx " or 1=1 
# 2.不需要用户名和密码也可以登录
 
"""
SQL注入的原因 是由于特殊符号的组合会产生特殊的效果
    实际生活中 尤其是在注册用户名的时候 会非常明显的提示你很多特殊符号不能用
        原因也是一样的
结论:设计到敏感数据部分 不要自己拼接 交给现成的方法拼接即可
"""
 
 
# sql = 'insert into userinfo(name,pwd) values("jason","123"),("kevin","321")'
# res = cursor.execute(sql)
 
# print(res)
"""
在使用代码进行数据操作的时候 不同操作的级别是不一样的
    针对查无所谓
    针对增 改 删都需要二次确认
        conn.commit()
"""

二.视图

1.什么是视图

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

2.为什么要用视图

如果频繁使用一张虚拟表，可以不用重复查询

3.如何用视图

create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;
"""
创建好了之后 验证它的存在navicat验证 cmd终端验证
最后文件验证 得出下面的结论 视图只有表结构数据还是来源于之前的表
delete from teacher2course where id=1;
"""

4.注意事项

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

drop view teacher2course;

开发过程中一般不会使用到视图，视图是mysql的功能，如果你的项目里面大量的使用到了视图，那意味着你后期想要扩张某个功能的时候这个功能恰巧又需要对视图进行修改，意味着你需要先在mysql这边将视图先修改一下，然后再去应用程序中修改对应的sql语句，这就涉及到跨部门沟通的问题，所以通常不会使用视图，而是通过重新修改sql语句来扩展功能

 三.触发器

1.什么是触发器

在满足对某张表数据的增、删、改（没有查）的情况下，自动触发的功能称之为触发器

2.为什么要用触发器

触发器专门针对我们对某一张表数据增（insert）、删（delete）、改（update）的行为，这类行为一旦执行就会触发触发器的执行，即自动运行另外一段SQL代码

3.如何创建触发器

"""语法结构
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
	sql语句
end
"""
 
# 针对插入
create trigger tri_after_insert_t1 after insert on 表名 for each row
begin
    sql代码。。。
end 
create trigger tri_after_insert_t2 before insert on 表名 for each row
begin
    sql代码。。。
end
 
# 针对删除
create trigger tri_after_delete_t1 after delete on 表名 for each row
begin
    sql代码。。。
end
create trigger tri_after_delete_t2 before delete on 表名 for each row
begin
    sql代码。。。
end
 
# 针对修改
create trigger tri_after_update_t1 after update on 表名 for each row
begin
    sql代码。。。
end
create trigger tri_after_update_t2 before update on 表名 for each row
begin
    sql代码。。。
end
 
"""
需要注意 在书写sql代码的时候结束符是; 而整个触发器的结束也需要分号;
这就会出现语法冲突 需要我们临时修改结束符号
delimiter $$
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') #0代表执行失败
);
 
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对象
        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
    ('egon','0755','ls -l /etc',NOW(),'yes'),
    ('egon','0755','cat /etc/passwd',NOW(),'no'),
    ('egon','0755','useradd xxx',NOW(),'no'),
    ('egon','0755','ps aux',NOW(),'yes');
 
# 查询errlog表记录
select * from errlog;
# 删除触发器
drop trigger tri_after_insert_cmd;

四.事务

1.什么是事务

开启一个事务可以包含一些SQL语句，这些SQL语句要么同时成功，要么一个都不能成功，这种称之为事务的原子性

2.事务的作用

保证了对数据操作的数据安全性

###  案例：两个不同的银行账户相互转钱  ###

3.事务具有的四个属性

原子性、一致性、隔离性、持久性，这四个属性通常称为ACID特性

1. 原子性（atomicity）：一个事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做
2. 一致性（consistency）：事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的
3. 隔离性（isolation）：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰
4. 持久性（durability）：持久性也称为永久性，值一个事务一旦提交，他对数据库中数据的改变就应该是永久的，接下来的其他操作或故障不应该对其有任何影响

4.如何使用

# 先介绍事务的三个关键字 再去用表实际展示效果
start transaction;
commit;
rollback;
 
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
 
insert into user(name,balance)
values
('jason',1000),
('egon',1000),
('tank',1000);
 
# 修改数据之前先开启事务操作
start transaction;
 
# 修改操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元
 
# 回滚到上一个状态
rollback;
 
# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;
"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""
 
# 站在python代码的角度，应该实现的伪代码逻辑，
try:
	# 少了开事务...
    update user set balance=900 where name='jason'; #买支付100元
    update user set balance=1010 where name='egon'; #中介拿走10元
    update user set balance=1090 where name='tank'; #卖家拿到90元
except 异常:
    rollback;
else:
    commit;

五.存储过程

存储过程包含了一系列可执行的SQL语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql，类似于python中的自定义函数

1.基本使用

delimiter $$
create procedure p1()
begin
	select * from user;
end $$
delimiter ;
 
# 调用
call p1()

2.三种开发模型

第一种：

应用程序：只需要开发应用程序的逻辑
mysql:编写好存储过程，以供应用程序调用
优点：开发效率，执行效率都高
缺点：考虑到人为因素、跨部门沟通等问题，会导致扩展性差

第二种：

应用程序：除了开发应用程序的逻辑，还需要编写原生sql
优点：比方式1，扩展性高(非技术性的)
缺点：
1、开发效率，执行效率都不如方式1
2、编写原生sql太过于复杂，而且需要考虑到sql语句的优化问题

第三种：

应用程序：开发应用程序的逻辑，不需要编写原生sql，基于别人编写好的框架来处理数据，ORM
优点：不用再编写纯生sql，这意味着开发效率比方式2高，同时兼容方式2扩展性高的好处
缺点：执行效率连方式2都比不过

3.创建存储过程

# 介绍形参特点  再写具体功能
 
delimiter $$
 
create procedure p2(
    in m int,  # in表示这个参数必须只能是传入不能被返回出去
    in n int,  
    out res int  # out表示这个参数可以被返回出去
)
begin
    select tname from teacher where tid > m and tid < n;
    set res=0;  # 用来标志存储过程是否执行
end $$
delimiter ;
 
 
# 针对res需要先提前定义
set @res=10;  定义
select @res;  查看
call p1(1,5,@res)  调用
select @res  查看

4.如何用存储过程

# 大前提:存储过程在哪个库下面创建的只能在对应的库下面才能使用！！！
 
# 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.execute('select @_p1_2;')
 
 
# 3、存储过程与事务使用举例(了解)
delimiter //
create PROCEDURE p5(
    OUT p_return_code tinyint
)
BEGIN
    DECLARE exit handler for sqlexception
    BEGIN
        -- ERROR
        set p_return_code = 1;
        rollback;
    END;
 
 
  DECLARE exit handler for sqlwarning
  BEGIN
      -- WARNING
      set p_return_code = 2;
      rollback;
  END;
 
  START TRANSACTION;
      update user set balance=900 where id =1;
      update user123 set balance=1010 where id = 2;
      update user set balance=1090 where id =3;
  COMMIT;
 
  -- SUCCESS
  set p_return_code = 0; #0代表执行成功
 
 
END //
delimiter ;

六.函数

注意与存储过程的区别，MySQL内置的函数只能再sql语句中使用

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');
 
 
+----+--------------------------------------+---------------------+
| id | NAME                                 | sub_time            |  month
+----+--------------------------------------+---------------------+
|  1 | 第1篇                                | 2015-03-01 11:31:21 |  2015-03
|  2 | 第2篇                                | 2015-03-11 16:31:21 |  2015-03
|  3 | 第3篇                                | 2016-07-01 10:21:31 |  2016-07
|  4 | 第4篇                                | 2016-07-22 09:23:21 |  2016-07
|  5 | 第5篇                                | 2016-07-23 10:11:11 |  2016-07 
|  6 | 第6篇                                | 2016-07-25 11:21:31 |  2016-07
|  7 | 第7篇                                | 2017-03-01 15:33:21 |  2017-03
|  8 | 第8篇                                | 2017-03-01 17:32:21 |  2017-03
|  9 | 第9篇                                | 2017-03-01 18:31:21 |  2017-03
+----+--------------------------------------+---------------------+
 
select count(*) from blog group by month;
 
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
 
 
https://blog.csdn.net/GG_Bruse/article/details/131484538  # 补充的一些内置函数，可以了解一下

七.流程控制

if条件语句：

delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
    
    declare i int default 0;
    if i = 1 THEN
        SELECT 1;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 7;
    END IF;
 
END //
delimiter ;

while循环：

delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
 
    DECLARE num INT ;
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT
            num ;
        SET num = num + 1 ;
    END WHILE ;
 
END //
delimiter ;

八.索引

1.什么是索引

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

2.索引的介绍

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

• primary key
• unique key
• index key

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

其本质都是通过不断缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据

3.索引的影响

（1）在表中有大量数据的前提下，创建索引速度会很慢（建表的时候，如果明显需要索引，就提前加上）

以后实际添加索引的时候，尽量在空表的时候添加，在创建表的时候就添加索引，此时添加索引是最快的

如果表中数据已经有了，还要添加索引，也可以，只不过创建索引的速度会很慢，不建议这样做

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

写的性能影响不是很大，因为在实际中，写的频率很少，大部分操作都是查询

具体给哪些字段添加索引还是要看实际的查询条件

select * from user where name='' and password='';

不要一建表就加索引，在一张表中，最多不要超过15个索引，索引越多，性能要求越高

4.补充

有哪些树：

二叉树   平衡树   b树   b+树   b-树

只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据
查询次数由树的层级决定，层级越低次数越少
一个磁盘的大小是一定的，那也就意味着能存放的数据量是一定的，那么如何保证树的层级最低呢？一个磁盘存放占用空间比较小的数据项

以后加索引的时候，尽量给字段中存的是数字的列加，我们使用主键查询速度很快

select * from user where name = ''
select * from user where id = ''  # 主键查询的更快一些

5.测试索引

准备：

#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<1000000)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 = 1000000;
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';  # 速度变快