十四、MySQL进阶

（一）索引

索引：数据库的性能调优；提升数据库的工作效率。

1、索引分类（逻辑分类）

1、主键索引：主键索引是一种唯一性索引，即不允许为空以及值重复

2、唯一性索引：在创建表的时候加上unique，值必须是唯一的

3、普通索引：执行任务的时候可以加快对数据的访问速度；没有任何限制条件。使用的关键字index或者key

4、空间索引：空间索引主要用于地理空间的数据类型。

5、全文索引：全文索引只能在varchar或text类型的字段上。

2、使用索引

索引可以在一定情况下可以加快查询速度，但是在某些情况下会降低效率

当表的查询大于修改、删除的操作 ， 可以创建索引；

表查询的操作很少，表的数据很少使用到，不建议创建索引；

3、创建索引

-- 在创建表的时候创建索引
create table 表名(
字段名 数据类型，
字段名 数据类型，
index 索引名(字段名),
unique 索引名(字段名)
);

-- 在已有表中创建索引
create index 索引名 on 表名(字段名);
-- 唯一索引
create unique index 索引名 on 表名(字段名);

-- 删除索引
drop index 索引名 on 表名;

-- 查看表索引
show keys from 表名;
-- Non_unique
-- 返回1表示索引值不唯一
-- 返回0表示索引是唯一的

-- create index id on t1(id);
-- create unique index id on t1(id);

-- drop index id on t1;

-- create index age on t1(age);

-- desc t1;

-- 查看表索引
show keys from t1;

（二）视图

视图：视图是一个虚拟表。其内容由查询结果定义。

视图的结构与真实的表是一样的，视图最好只做查询操作。

视图可以简化高频、复杂查询操作；提高数据的安全性

1、视图语法

-- 创建视图
create view 视图名称 as select 查询语句;

-- 修改视图
-- 原表数据进行修改 ， 视图中的数据也会发生改变
alter view 视图名称 as select 查询语句;

-- 删除视图
drop view 视图名称;
drop view if exists 视图名称;

-- 查看数据库所有的视图
show full tables in 数据库名 where table_type like 'VIEW';

-- create view old as select * from t1 where age>20;
-- create view young as select * from t1 where age<20;

-- select * from old;

-- update t1 set age=25 where name='阿宸';
-- update old set age=24 where name='阿宸';
-- select * from t1;
-- select * from old;

-- drop view young;

-- 查看数据库所有的视图
-- show full tables in 数据库名 where table_type like 'VIEW';
show full tables in class7 where table_type like 'VIEW';

（三）函数和存储过程

1. 函数以及存储过程是
2. 事先经过编译并存储在数据库中的一段sql语句的集合，
3. 调用存储过程或者函数可以简化代码，提高数据处理效率。
4. 函数有且只有一个返回值，并且要的是一个结果；
5. 存储过程不能有返回值要的是执行过程；

1、函数的创建

delimiter 符号	-- 自定义语句结束符号 
create function 函数名(形参) returns 返回类型
begin
	函数体
	return 返回值
end
delimiter ;

-- 调用函数
select 函数名();

-- 变量
set @变量名=值
select @变量名


set @name='阿宸';
select @name;

-- 在函数内创建变量
declare 变量名 数据类型

set 变量名=值

delimiter $$	-- 自定义语句结束符号 
create function ac() returns int
begin
	declare age int(3);
	set age=(select age from t1 where name='阿宸');
	return age;
end $$
delimiter ;

select ac();


在MySQL8会创建函数会出现的创建不成功的问题解决
这是因为有一个安全参数没有开启,log_bin_trust_function_creators 默认为0，是不允许function的同步的，开启这个参数，就可以创建成功了。
查看是否开启
show variables like '%func%';
为on则是开启了

找到安装路径
在programdata--mysql--mysql sever
在my.ini中的[mysqld]中lower_case_table_names=1
后面增加一条
log_bin_trust_function_creators=1

2、存储过程

存储过程可以实现比较复杂的业务逻辑

delimiter 符号	-- 自定义语句结束符号 
create procedure 存储过程名称(参数)
begin
存储过程
end 符号
delimiter ;

-- 调用存储过程
call 存储过程名称();



delimiter 符号	-- 自定义语句结束符号 
create procedure 存储过程名称([in/out] 参数名 数据类型)
begin
存储过程
end 符号
delimiter ;

-- 调用存储过程
call 存储过程名称(参数);

in , 可以接收常量或者是变量

out：只能接收变量

-- delimiter $$ 
-- 
-- create procedure ss()
-- begin
-- select * from t1 where age>20;
-- select name from t1 where age>20;
-- end $$
-- 
-- delimiter ;

-- call ss();


-- delimiter $$ 
-- 
-- create procedure s1(in num int)
-- begin
-- select * from t1 where age>num;
-- end $$
-- 
-- delimiter ;

call s1(19);
set @n=22;
call s1(@n);

（四）事务、存储引擎

1、事务

MySQL事务主要是用于处理操作

数量比较大 ，

复杂度比较高的数据；

多个数据必须同时操作成功，如果有一条操作不成功则则所有数据都不会发生变化，

这时数据库操作语句就构成一个事务。事务主要是处理是保护局增删改操作；

-- 开启事务
begin;

-- 结束事务
commit;

-- 数据回滚,回滚带上一条操作语句
rollback;


begin;

insert into students values 
(10,2202,'刘永华','男');

delete from students where id=7;

rollback;

-- 查看事务隔离级别
select @@transaction_isolation;

事务的四大特性：原子性、一致性、隔离性、持久性

MySQL是属于可重复度的隔离界别

2、存储引擎

MySQL是如何存储数据的

-- 查看存储引擎
show engines;
InnoDB -- 默认的存储引擎，比较平衡的读写的效率都是可以的
MyISAM -- 注重查询 ， 表级锁；对于写的效率不好

（五）触发器

在数据中设置一段静态代码 ，这个代码并不会自己执行 ， 是当某个操作触发到这个设置的条件是，才会执行.

触发器像一个比较特殊的存储过程；不需要手动执行sql语句 ，自动触发sql语句

delimiter $$
create trigger 触发器名称  触发时间  触发条件 on 表名 for each row
begin
触发事件
end $$
delimiter ;

-- 触发时间（有两个）：before 在触发条件之前；after 在触发条件之后
-- 触发条件：insert、update、delete
-- new 新数据 insert 、update
-- old 旧数据 update、delete

-- 增加学生信息 ， 对应班级的人数自动增加
delimiter $$
create trigger tri  after  insert on students for each row
begin
	declare num int;
	-- 获得到学生对应班级的人数
	set num=(select count_stu from class where class_id=new.class_id);
	-- 对班级人数+1
	update class set count_stu = num+1 where class_id=new.class_id;
end $$
delimiter ;

insert into students values (7,2202,'马晨旺','男');
-- 一下代码触发两次
insert into students values 
(7,2202,'李金林','男'),
(7,2203,'朱晓际','男');

-- 修改学生班级 ， 对应班级人数的触发操作
delimiter $$
create trigger tri_update  after  update on students for each row
begin
	declare num_old int;
	declare num_new int;
	-- 获得到两个班级的人数
	set num_old=(select count_stu from class where class_id=old.class_id);
	set num_new=(select count_stu from class where class_id=new.class_id);
	-- 对班级人数+1/-1
	update class set count_stu = num_old-1 where class_id=old.class_id;
	update class set count_stu = num_new+1 where class_id=new.class_id;
end $$
delimiter ;

update students set class_id=2203 where id=7;

（六）pymysql

pymysql就是python的第三方库，用来连接mysql的

使用python来操作数据库pip install pymysql

import pymysql

'''
1、建立数据库连接
db = pymysql.connect(
    host : 连接MySQL主机的，本机连接直接写‘localhost’ , 本地ip:127.0.0.1
    user : 连接用户名
    password : 数据库密码
    charset : 使用utf8
    database : 数据库名称
    port : mysql端口 默认都是3306（在本机连接MySQL可以不用端口）
)
'''

db = pymysql.connect(
    host="localhost",
    user = 'root',
    password='root',
    charset='utf8',
    database='class7'
)

# 2、创建游标对象
cursor = db.cursor()


# sql = 'show tables'
# # 把sql语句交给游标方法
# cursor.execute(sql)

# 获取数据
# 返回的是一个元组 返回所有数据表格
# all = cursor.fetchall()
# print(all)

# 返回多条数据，参数不写默认为1
# many = cursor.fetchmany(2)
# print(many)

# # 返回一条数据
# one = cursor.fetchone()
# print(one)

# 创建数据表
table_name = 'user'
# sql = 'create table %s (id int(4) not null , name varchar(5))'%(table_name)
# cursor.execute(sql)

# 查看表结构
# sql = 'desc %s'%(table_name)
# cursor.execute(sql)
# print('表结构为：',cursor.fetchall())

# 插入数据
# 增加数字类型需要在insert后面加上ignore
# sql = "insert ignore into user values('1' ,'阿宸');"
# cursor.execute(sql)

# sql = "insert ignore into user values(%s ,%s);"
# # 每一条数据保存为元组类型 ， 最后将多个元组放在一个列表中
# value = [
#     ('2','张三'),
#     ('3','李四'),
#     ('4','王五'),
#     ('5','赵六'),
#     ('6','老七'),
#     ('7','马大哈')
# ]
# # 提交多条数据时要使用executemany()
# cursor.executemany(sql , value)

# 在python中操作数据库对数据进行增删改操作时 ， 必须对操作事务进行提交
# db.commit()


# 数据修改
# sql = "update user set name='二哈' where id='7'"
# cursor.execute(sql)
# db.commit()

# 数据删除
# sql = "delete from user where name='阿宸'"
# cursor.execute(sql)
# db.commit()

# sql = "select * from user"
# cursor.execute(sql)
# print(cursor.fetchall())
# 获得多条数据
# print(cursor.fetchmany(4))
# 单条数据
# print(cursor.fetchone())


sql = 'select * from user where name="二哈"'
cursor.execute(sql)
print(cursor.fetchone())


# sql = 'select * from %s'%(table_name)
# cursor.execute(sql)
# print(cursor.fetchall())

# 关闭游标关闭
cursor.close()
# 关闭数据库连接
db.close()