Day 41 Mysql高级操作

Mysql高级操作

一、视图

视图（view）是一种虚拟存在的表，是一个逻辑表，本身不包含数据。本质是一个保存的子查询语句，通过视图，可以展现基表的部分数据

基表：用来创建视图的表叫做基表 base table

视图的优点

1. 简单：因为是虚拟存在的表，所以使用视图用户完全不需要关心后面对应表的结构、关联条件和筛选条件，已经是过滤的结果集
2. 安全：是能访问允许查询的结果集，对表的权限管理能简单限制具体的行列
3. 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

注意

• 使用视图以后就无需每次都重写子查询的sql，但是这么效率并不高，还不如我们写子查询的效率高
• 而且有一个致命的问题：视图是存放到数据库里的，如果我们程序中的sql过分依赖于数据库中存放的视图，那么意味着，一旦sql需要修改且涉及到视图的部分，则必须去数据库中进行修改，而通常在公司中数据库有专门的DBA负责，你要想完成修改，必须付出大量的沟通成本DBA可能才会帮你完成修改，极其地不方便

创建视图

CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;创建的格式

select * from emp inner join dep where emp.dep_id = dep.id;


create view emp2dep as select emp.*,dep.name as dep_name from emp inner join dep where emp.dep_id = dep.id;

使用视图

select * from emp2dep;
----------------------------------------------------------
+----+------------+--------+------+--------+----------+
| id | name       | gender | age  | dep_id | dep_name |
+----+------------+--------+------+--------+----------+
|  1 | 小猪佩奇   | male   |    7 |    200 | 技术     |
|  2 | 天尊杨戬   | male   |   88 |    201 | 人力资源 |
|  3 | 森下下士   | male   |   38 |    201 | 人力资源 |
|  5 | 地爆天星   | female |   19 |    200 | 技术     |
|  6 | 天山新泰罗 | male   |   18 |    204 | 运营     |
+----+------------+--------+------+--------+----------+

# 查询记录
select gender, count(1) as count from emp2dep group by gender;
-------------------------------------------------------------
+--------+-------+
| gender | count |
+--------+-------+
| male   |     4 |
| female |     1 |
+--------+-------+

# 修改记录 原始表也跟着改
update emp2dep set name="bilibili" where id=5;
select * from emp2dep;  # 视图中的数据已经修改了
--------------------------------------------------------------
+----+------------+--------+------+--------+----------+
| id | name       | gender | age  | dep_id | dep_name |
+----+------------+--------+------+--------+----------+
|  1 | 小猪佩奇   | male   |    7 |    200 | 技术     |
|  2 | 天尊杨戬   | male   |   88 |    201 | 人力资源 |
|  3 | 森下下士   | male   |   38 |    201 | 人力资源 |
|  5 | bilibili   | female |   19 |    200 | 技术     |
|  6 | 天山新泰罗 | male   |   18 |    204 | 运营     |
+----+------------+--------+------+--------+----------+

select * from emp;  # 基表中的数据也跟着修改了
+----+------------+--------+------+--------+
| id | name       | gender | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | 小猪佩奇   | male   |    7 |    200 |
|  2 | 天尊杨戬   | male   |   88 |    201 |
|  3 | 森下下士   | male   |   38 |    201 |
|  4 | 孙燕姿     | female |   18 |    202 |
|  5 | bilibili   | female |   19 |    200 |
|  6 | 天山新泰罗 | male   |   18 |    204 |
|  7 | 挺甜一郎   | female |   20 |   NULL |
+----+------------+--------+------+--------+

我们不应该修改视图中的记录，而且在涉及多个表的情况下是根本无法修改视图中的记录的

insert into emp2dep(name, gender, age, dep_id, dep_name) values("小猪配你", "male",17, 201, "技术")
---------------------------------------------------
Can not modify more than one base table through a join view 'python.emp2dep'  # 无法通过联接视图'python.emp2dep'修改多个基表

删除视图

语法：DROP VIEW 视图名称

drop view emp2dep;
------------------------------
Query OK, 0 rows affected (0.00 sec)

二、触发器

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

使用触发器可以制定用户对表进行【增(insert)、删(delete)、改(update)】操作时前后的行为，注意：没有查询

创建触发器

基本语句

create tirgger 触发器名 before/after 触发事件
on 表名 for each row
begin
	执行语句列表
end;

delimiter : 默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。

在执行语句列表中最后就是分号";" 如果我们不修改，那么整个触发器 就不会完整执行 所以我们要将 默认的分号“;” 提前修改一下， 然后再改回来 # “\” 无法识别

准备表

create table t1(id int primary key auto_increment,
name varchar(15),
age int);

create table t2(id int primary key auto_increment,
name varchar(15));

增

#before 插入前
delimiter //  
create trigger tri_after_insert_t1 before 
insert on user for each row
begin
	insert into t2(name) values(NEW.name);
end//
delimiter ;

#after 插入后
delimiter //  
create trigger tri_after_insert_t1 after 
insert on user for each row
begin
	insert into t2(name) values(NEW.name);
end//
delimiter ;

insert into t1(name, age) values("xiuxiu", 18);

删

 # 删除前
 delimiter //  
create trigger tri_after_delete_t1 before 
delete on user for each row
begin
	insert into t2(name) values(OLD.name);
end//
delimiter ;

#after 删除后
delimiter //  
create trigger tri_after_delete_t1 after 
delete on user for each row
begin
	insert into t2(name) values(OLD.name);
end//
delimiter ;

改

 # 更新前
 delimiter //  
create trigger tri_after_update_t1 before 
update on user for each row
begin
	insert into t2(name) values(NEW.name);
end//
delimiter ;

#after 更新后
delimiter //  
create trigger tri_after_update_t1 after 
update on user for each row
begin
	insert into t2(name) values(NEW.name);
end//
delimiter ;

特别的：NEW表示即将插入的数据行，OLD表示即将删除的数据行。

删除触发器

drop trigger 触发器名字

drop trigger tri_before_insert_t1;
drop trigger tri_after_insert_t1;
--------------------------------
Query OK, 0 rows affected (0.00 sec)

三、事务

什么是事务

​ 把多条语句作为一个整体进行操作的功能，被称为数据库事务。数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败，那么效果就和没有执行这些SQL一样，不会对数据库数据有任何改动。

可见事务具有以下四个特征(ACID)

• A:Atomic 原子性-强调事务的不可分割，事务要么全部完成，要么全部取消。 如果事务崩溃，状态回到事务之前（事务回滚）。
• C:Consistency 事务的执行的前后数据的完整性保持一致,只有合法的数据（依照关系约束和函数约束）才能写入数据库
• I:Isolation 一个事务执行的过程中,不应该受到其他事务的干扰, 如果2个事务 T1 和 T2 同时运行，事务 T1 和 T2 最终的结果是相同的，不管 T1和T2谁先结束。
• D:Durability 事务一旦结束,数据就持久到数据库,一旦事务提交，不管发生什么（崩溃或者出错），数据要保存在数据库中。

使用事务

# 创建相关表
create table user(name varchar(15),
balance int,
time timestamp not null default now());

# 修改表的默认编码
alter table user convert to character set utf8mb4;

# 插入数据
insert into user(name, balance) values
("毛成",10000),
("egon",-100),
("于小聪",0);

# 原子操作
begin;  # 事务开始标志 也可以是 start transaction
update user set balance=9700 where name="毛成";  # 扣去100块购买
update user set balance=-70 where name="egon";  # 中间商赚差价
update user set balance=270 where name="于小聪";  # 实际到手90
commit;  # 提交 写入数据库

select * from user;
-----------------------------------------------------------
+--------+---------+---------------------+
| name   | balance | time                |
+--------+---------+---------------------+
| 毛成   |    9900 | 2020-09-09 10:55:47 |
| egon   |     -90 | 2020-09-09 10:55:47 |
| 于小聪 |      90 | 2020-09-09 10:55:47 |
+--------+---------+---------------------+

# 假设出现问题时 我们可以回滚到初始状态
start transaction;
update user set balance=9800 where name="毛成";  # 扣去100块购买
update user set balance=-80 where name="egon";  # 中间商赚差价
update user set balance=180 where name="于小聪";  # 实际到手90
rollback;  # 回滚
commit;  # 提交 写入数据库

select * from user;  # 数据回到上次完成的位置  时间
---------------------------------------------------------------
+--------+---------+---------------------+
| name   | balance | time                |
+--------+---------+---------------------+
| 毛成   |    9900 | 2020-09-09 10:55:47 |
| egon   |     -90 | 2020-09-09 10:55:47 |
| 于小聪 |      90 | 2020-09-09 10:55:47 |
+--------+---------+---------------------+

总结：事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。

四、存储过程

概念

存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql

就是 一组为了完成特定功能的SQL 语句集

使用存储过程的优点：

• 存储过程可以重复使用，大大减小开发人员的负担；
• 对于网络上的服务器，可以大大减小网络流量，因为只需要传递存储过程的名称即可；
• 可以防止对表的直接访问，只需要赋予用户存储过程的访问权限。

创建存储过程

创建存储过程的命令是

create procedure 存储过程名(
    参数种类1 参数1 数据类型,
    [....])
begin
具体的procedure(处理)
end

# 创建无参存储过程
delimiter //
create procedure p1()
begin
	select * from user;
	update user set balance=100 where name="egon";
	select * from user;
end //

delimiter ;

# 在mysql中调用
call p1():  # 调用存储过程p1
-----------------------------------------------
+--------+---------+---------------------+
| name   | balance | time                |
+--------+---------+---------------------+
| 毛成   |    9700 | 2020-09-09 10:55:47 |
| egon   |     -70 | 2020-09-09 10:55:47 |
| 于小聪 |     270 | 2020-09-09 10:55:47 |
+--------+---------+---------------------+
3 rows in set (0.00 sec)

Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+--------+---------+---------------------+
| name   | balance | time                |
+--------+---------+---------------------+
| 毛成   |    9700 | 2020-09-09 10:55:47 |
| egon   |     100 | 2020-09-09 10:55:47 |
| 于小聪 |     270 | 2020-09-09 10:55:47 |
+--------+---------+---------------------+


# 创建有参存储过程

对于存储过程，可以接收参数，其参数有三类：
#in          仅用于传入参数用
#out        仅用于返回值用
#inout     既可以传入又可以当作返回值

delimiter //
create procedure p2(
in u_name varchar(15),
out result varchar(15))
begin
	select * from user where name = u_name;
	set result = "我是返回值";  # 设置返回值
end //
delimiter ;

set @x="sss"  # sql语句中设置变量 要与类型相对应 varchar
# call p2("egon", "xxx")  我们不能直接传参，因为在sql语句中out和inout参数在触发是必须是 变量 或者伪变量
call p2("egon", @x)
----------------------------------
+------+---------+---------------------+
| name | balance | time                |
+------+---------+---------------------+
| egon |     100 | 2020-09-09 10:55:47 |
+------+---------+---------------------+

select @x # 查看返回值
-----------------------------------------
+------------+
| @x         |
+------------+
| 我是返回值 |
+------------+

• 1）存储过程中具体的处理类容放在BEGIN和ENDBEGIN和END 之间；
• 2）存储过程需要制定参数，包括种类（IN,OUT,INOUTIN,OUT,INOUT，分别代表输入参数，输出参数和即是输入也是输出的参数），参数名和数据类型。【和函数不同，函数指定输入参数即可】

在pymysql中如何调用存储过程

import pymysql  # pip3 install pymysql

conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123", db="db4", charset="utf8mb4")
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)


cursor.callproc('p2',(3,0))  # @_p2_0=3,@_p2_1=0 pymysql对此作了变化
'''
set @_p2_0=3
set @_p2_1=0

call p2(@_p2_0,@_p2_1);

'''

print(cursor.fetchall())

cursor.execute("select @_p2_1;")
print(cursor.fetchall())

cursor.execute("select @_p2_0;")
print(cursor.fetchall())

cursor.close()
conn.close()

删除存储过程

drop procedure if exists p2;
drop procedure p2;

方式一

    MySQL：存储过程
    程序：调用存储过程
    
    -python程序：调用存储过程

	 sql语句由mysql完成，MySQL：编写存储过程

	 运行效率比方式二高，扩展性低于方式二（人为因素造成）。

方式二

    MySQL：
    程序：纯SQL语句
    
    -python：编写纯生SQL

	 MySQL：无

	 可维护上比方式一高，

方式三

    MySQL:
    程序：类和对象，即ORM（本质还是纯SQL语句）
    
    -python：基于ORM写类产生对象，由ORM转成纯生SQL

	 MySQL：无

	 本质与方式二一样，运行效率方式二高，但是开发效率高，可维护性更改

通常情况下使用方式三。

五、函数

！！！注意！！！
函数中不要写sql语句（否则会报错），函数仅仅只是一个功能，是一个在sql中被应用的功能
若要想在begin...end...中写sql，请用存储过程

内置函数

自定义函数

自定义函数分为：标量值函数或表值函数两种。

• 标量值函数：如果 return子句指定一种标量数据类型，则函数为标量值函数。
• 表值函数：如果 return 子句指定 table，则函数为表值函数。

表值函数又分为两种：内嵌表值函数（行内函数）或多语句函数

• 如果 RETURNS 子句指定的 TABLE 不附带列的列表，则该函数为内嵌表值函数。
• 如果 RETURNS 子句指定的 TABLE 类型带有列及其数据类型，则该函数是多语句表值函数

如果你不晓得Returns从哪里来，请看创建函数的语法（这里是创建标量值函数的语法）：

Create function 函数名（参数）
Returns 返回值数据类型
[with {Encryption | Schemabinding }]
[as]
begin
SQL语句(必须有return 变量或值)
End

标量值函数

delimiter //
create function f1(  # 传入两个int参数
i1 int,
i2 int)
returns int  # 返回一个数据类型
begin
	declare num int;  # DECLARE： 定义变量，变量第一个字母是“@”
	set num = i1*2 + i2*2;  # SET：设置变量
	return (num);
end //
delimiter ;

#执行函数
select python.f1(2,99);  # 在python库下
-------------------------------------
+-----------------+
| python.f1(2,99) |
+-----------------+
|             202 |
+-----------------+

# 删除函数
drop function f1; # drop function func_name;


# 复杂点
delimiter //
create function f5(
    i int
)
returns int
begin
    declare res int default 0;
    if i = 10 then
        set res=100;
    elseif i = 20 then
        set res=200;
    elseif i = 30 then
        set res=300;
    else
        set res=400;
    end if;
    return res;
end //
delimiter ;

索引原理

为什么要有索引

简单来说就是为了优化查询，加快查询速度。

什么是索引

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能
非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。