MySQL （五）插入修改数据、视图、存储过程、自定义函数

一、数据

1.插入数据

create database test;
use test;
create table total  (
`id` int primary key, 
`dept` int,
`age` int,
`height` float ,
`university` varchar(20)
)engine = InnoDB charset = utf8mb4 collate = utf8mb4_bin;


#插入数据
insert into `total` values(1,3500,18,null,"CUMT"); #插入一行数据(缺失值用null补位)
insert into total(id,dept,university) values(2,3522,"JNU"); #插入部分数据，其余数据为默认值
insert into total(id,dept,university) values(2,3522,"JNU"),(3,3521,"CUMT"); #批量插入数据
#insert into 表名(列名) values(与列名相对应的值)

2.修改数据

#修改数据
update test.total
set university = 'CUMT'
where dept = '3522'; #将学号dept为3522的学校改为'CUMT'

直接修改数据会报错

需要进一步调整：

！！！！最后：关掉重启，再运行

3.删除数据

#删除数据
delete from test.total
where dept = '3522'; #删除学号dept为3522的数据

二、视图

类似于excel中的透视图，先有数据表，再有视图

视图存储了查询的SQL逻辑，不保存查询结果，不占物理内存

视图的很多操作，类似于表

#数据仍旧沿用（四）查询部分的总表和分表
create database test;
use test;
create table total  (
`id` int primary key, 
`dept` int,
`age` int,
`height` float ,
`university` varchar(20)
)engine = InnoDB charset = utf8mb4 collate = utf8mb4_bin;

insert into `total` values(1,3500,18,182,"CUMT");
insert into `total` values(2,3522,23,167,"JNU");
insert into `total` values(3,3545,20,178,"ABC");
insert into `total` values(4,3576,20,173,"ICBC");
insert into `total` values(5,3587,25,173,"CCB");
insert into `total` values(6,3523,22,178,"CUMT");

create table section  (
`id` int primary key, 
`dept` int,
`chinese` float,
`math` float ,
`english` float
)engine = InnoDB charset = utf8mb4 collate = utf8mb4_bin;

insert into `section` values(1,3500,76,84,99);
insert into `section` values(2,3522,45,40,82);
insert into `section` values(3,3545,89,88,82);
insert into `section` values(4,3576,26,88,92);

1.插入视图

#插入视图
create view `total_section` (id,dept,university,math) as #新视图名称为`total_section`,列包括id,dept,university,math，注意非单引号
select 
	tt.id, #防止模糊指代，加上tt.
    tt.dept,
    university,
    math #无,
from test.total as tt
join test.section as ts on tt.id = ts.id; #左表要求 = 右表要求 

运行结果：

2.修改视图

#增加、删除、修改列（主要调整select后面的参数，select后面有什么，视图中就有什么）
#create or replace view `total_section` as 与alter具有同样的效果
alter view `total_section` as
select 
	tt.id,
    tt.dept,
    university,
    chinese #去掉math列，增加chinese列
from test.total as tt
join test.section as ts on tt.id = ts.id;

运行结果：

#视图重命名（先删除再插入）
drop view `total_section`; #删除视图

create view `total_section` as #重新插入视图
select 
	tt.id,
    tt.dept,
    university,
    chinese
from test.total as tt
join test.section as ts on tt.id = ts.id;

#修改字段
update total_section #update 表名
set university = 'CUMT'
where dept = '3522'
#将学号dept为3522的学校改为'CUMT'
#数据表和视图一起变

3.删除视图

drop view `total_section`; #删除视图

4.检查选项

当视图依赖于多个基本表时，不能插入数据

当视图依赖于一个基本表时，可以插入数据，但不建议。

cascaded强制上级检查，local才检查

a. cascaded

#建立视图
create or replace view `total_1` (id,age) as 
select id, age
from test.total
where id < 20 #此时没有加检查选项，不用检验id < 20
#此处检验条件： 无

insert into `total_1` values(7,22); #向视图中插入数据
insert into `total_1` values(23,22); #此时可以插入id > 20的数据

视图：                                视图只增加id=7，基本表增加id=7、23                                 原基本表：

（为什么视图没有增加id = 23？？？有问题戳我改正）

create or replace view `total_2` (id,age) as 
select id, age
from test.total_1
where id > 10 with cascaded check option
#此处检验条件： 20 > id > 10

insert into `total_2` values(24,22); 
#不仅要检验id>10，而且要检验上一级的条件id<20，故此条sql代码会报错

create or replace view `total_3` (id,age) as 
select id, age
from test.total_2
where id > 15  
#此时没有加检查选项，不用检验id < 15
#但要依据上一级检验条件20 > id > 10
#此处检验条件： 20 > id > 10

insert into `total_3` values(12,22); #不报错

b. local

#建立视图：同cascaded第一步的结果
create or replace view `total_1` (id,age) as 
select id, age
from test.total
where id < 20 #此时没有加检查选项，不用检验id < 20
#此处检验条件： 无

insert into `total_1` values(7,22); #向视图中插入数据
insert into `total_1` values(23,22); #此时可以插入id > 20的数据

create or replace view `total_2` (id,age) as 
select id, age
from test.total_1
where id > 10 with local check option
#此处检验条件: id > 10

insert into `total_2` values(24,22); 
#仅需检验id>10，不需要检验上一级的条件id<20，故此条sql代码不报错

create or replace view `total_3` (id,age) as 
select id, age
from test.total_2
where id > 15  
#此时没有加检查选项，不用检验id < 15
#但要依据上一级检验条件id > 10
#此处检验条件：id > 10

insert into `total_3` values(12,22); #不报错

再次重申：不建议使用视图修改原基本表，上面的检查选项的讲解完全是防止面试问到

 三、存储过程

1.创建存储过程

类似于python中的函数，文件中代码如下：

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_math`(stdNum int) 
#create procedure 函数名（参数名 参数类型）
#函数名为`get_math`，参数名为stdNum，参数的类型为int
BEGIN #函数体
	select 
		tt.id,
        tt.dept,
        math
	from test.total as tt
    join test.section as ts on tt.id = ts.id
    where tt.dept = stdNum;  #当学号和输入的学号相等时，加;
END
#输入学号，输出该学号的数学成绩

在另一文件中，运行存储过程：

call get_math('3522'); #参数为int，但要带单引号

运行结果：

2.删除存储过程

use test;
drop procedure if exists get_math;

四、自定义函数

1.创建自定义函数

文件中的代码：

CREATE DEFINER=`root`@`localhost` FUNCTION `get_math`(stdnum int) #函数名`get_math`,参数名stdnum，参数类型int
RETURNS int(11) #返回值类型int(11)
NO SQL #没有函数体，只有返回值
RETURN (
	select 
        math
	from test.total as tt
    join test.section as ts on tt.id = ts.id
    where tt.dept = stdNum #无;
) #返回值
#输入学号，返回数学成绩math

CREATE DEFINER=`root`@`localhost` FUNCTION `get_chinese`(stdnum int) 
RETURNS int(11)
BEGIN #函数体
set @A =( #将以下函数的结果 赋值给 @A
    select 
        chinese
    from test.total as tt
    join test.section as ts on tt.id = ts.id
    where tt.dept = stdnum #无;
); #有;
RETURN @A; #返回@A的值
END

！！！注意：最后返回值只能有一列！！！

剩余操作同存储过程的创建。

另一个文件夹中，运行函数：

select get_chinese('3522')

运行结果：

原本向单独开一篇写存储过程，但确实没必要，python连数据库之后直接写函数就好了