1.4号 数据库的语言、外键、存储过程、分支

数据库的语言：
DDL语言：数据库定义语言（对表结构和表字段进行操作）
create
alter table
drop
DML语言：数据库操作语言（对表数据进行操作）
insert into
delete
update
select

外键约束
表1：学号、姓名、班级、分数、名次、科目 ==》学生基础信息表
表2：学号、姓名、班级、地址、家长、爱好 ==》学生其他信息表
外键特点：一般是在设计表结构的时候，避免数据的冗余，尽量让表数据单一化，保持数据一致性和完整性
   

创建dcs1表，修改数据引擎为INNODB（主表/父表）
create table dcs1(id int(4)PRIMARY key,name varchar(10))ENGINE=INNODB;
创建dcs2表，修改数据引擎为INNODB（副表/子表）
create table dcs2(sid int(4)primary key,sname varchar(10),CONSTRAINT fk_sid FOREIGN key(sid)REFERENCES dcs1(id))ENGINE=INNODB;

constraint：约束，限制
fk_***：外键名称，fk就是foreign key的缩写
foreign key：外键
references：参考
engine=INNODB：数据库引擎

插入数据：如果dcs1和dcs2存在外键约束，主表中不存在的数据 若直接在子表中插入 则会插入失败，必须现在主表中插入一条对应的数据后，才能在子表中插入
删除数据：如果dcs1和dcs2存在外键约束，想要删除主表中的数据，必须先删除子表中对应的数据

外键的创建格式：
create table 子表名称(子表字段及其约束，CONSTRAINT fk_*** FOREIGN key(子表的字段)REFERENCES 父表的表名称(父表字段))ENGINE=INNODB;

面试题：怎么样保持两张表数据的一致性？
在建表的时候创建外键关系

存储过程
存储过程的优点：
1、可以进行多次调用
2、可以通过存储过程来进行批量数据的插入
3、工作中需要造大量的数据作为测试的前提
   

存储过程的固定格式：
drop procedure if exists xiaoshan;    #如果存在xiaoshan这个存储过程就把它删除（慎用），为了增加代码的健壮性，避免重名报错
create procedure xiaoshan()     #创建一个存储过程
begin   #存储过程的开始
drop table if exists aa;    #如果存在aa表 就把它删除
create table aa(id int(5)primary key auto_increment,score int(5));    #创建一个aa表
insert into aa values(1,88),(2,77),(3,79),(4,94),(5,85),(6,90),(7,66),(8,99);   #插入数据
select * from aa;     #查询aa表中的所有数据
end     #存储过程的结束
call xiaoshan()       #调用存储过程

单分支判断：
if 条件 then
执行sql1
else
执行sql2
end if;

需求：创建一个存储过程
1）如果输入的是0，就统计aa表中的数据量
2）如果不是0，就把aa表中的数据按照成绩倒叙排列
伪代码写法：
if n = 0 然后
统计aa表中的数据量
else （只要不是0的情况，不管输入什么数字，统一都归属到这个场景）
把aa表中的数据按照成绩倒叙排列
end if;（结束当前的判断句）

代码如下：
drop procedure if exists xiaoshan;
create procedure xiaoshan(n int)
begin
drop table if exists aa;
create table aa(id int(5)primary key auto_increment,score int(5));
insert into aa values(1,88),(2,77),(3,79),(4,94),(5,85),(6,90),(7,66),(8,99);
if n = 0 then
SELECT count(*) from aa;
else
SELECT * from aa ORDER BY score desc;
end if;
end
call xiaoshan(1) #括号里面加参数进行调用

多分支判断：
if 条件1 then
执行sql1
else if 条件2 then
执行sql2
else if 条件3 then
执行sql3
......
else
执行sql4
end if;
end if;
end if;  #有多少个if,就需要写多少个end if

需求：创建一个存储过程
1）如果输入的数字是0，就统计aa表中有多少条数据
2）如果输入的数字是0<n<=8，就把aa表中的数据按照成绩降序排列</n<=8，就把aa表中的数据按照成绩降序排列
3）如果输入的数字是n>8，就求出aa表中成绩最大值
4) 其他情况，则查询aa表的数据
伪代码：
if n = 0 然后
统计aa表中有多少条数据
else if n > 0 且 n <= 8
把aa表中的数据按照成绩降序排列
else if n > 8
求出aa表中成绩最大值
else
查询aa表中的所有数据
end if;
end if;
end if;

代码如下：
drop procedure if exists xiaoshan;
create procedure xiaoshan(n int)
begin
drop table if exists aa;
create table aa(id int(5)primary key auto_increment,score int(5));
insert into aa values(1,88),(2,77),(3,79),(4,94),(5,85),(6,90),(7,66),(8,99);
if n = 0 then
SELECT count(*) from aa;
else if n > 0 and n <= 8 then
SELECT * from aa ORDER BY score desc;
else if n > 8 then
SELECT max(score) from aa;
else
SELECT * from aa;
end if;
end if;
end if;
end
call xiaoshan(7)   #括号里面加参数进行调用

条件分支：
while 语句的格式：
while 条件 do
执行sql1
end while;

注意：
什么时候进入循环：当条件成立时，进入循环
什么时候退出循环：当条件不成立时，退出循环

需求：创建一个存储过程
1）如果输入的数字是0，就统计aa表中有多少条数据
2）如果输入的数字是n!=0，小于表的行数，就把aa表中的数据按照成绩降序排列
3）如果输入的数字是n>行数，就把数据补到n行，成绩录入88分
代码如下：
drop procedure if exists xiaoshan;
create procedure xiaoshan(n int)
begin
declare i int(5)default(select count(*) from aa);
/*drop table if exists aa;
create table aa(id int(5)primary key auto_increment,score int(5));
insert into aa values(1,88),(2,77),(3,79),(4,94),(5,85),(6,90),(7,66),(8,99);*/
if n = 0 then
SELECT count(*) from aa;
else if n > 0 and n <= i then
SELECT * from aa ORDER BY score desc;
else
while n > i do
insert into aa(score)values(88);
set i = i+1;   #让下一轮的i等于上一轮的i+1，另一个写法是i+=1
end while;
end if;
end if;
end

call xiaoshan(20)

需求：创建一个存储过程
1）如果输入的数字是0，就统计aa表中有多少条数据
2）如果输入的数字是n!=0，小于表的行数，就把aa表中的数据按照成绩降序排列
3）如果输入的数字是n>行数，就把数据补到n行，成绩录入最大成绩逐次加1，补充到100条数据
drop procedure if exists xiaoshan;
create procedure xiaoshan(n int)
begin
declare i int(5)default(select count(*) from aa);
declare j int(5)default(select max(score) from aa);
/*drop table if exists aa;
create table aa(id int(5)primary key auto_increment,score int(5));
insert into aa values(1,88),(2,77),(3,79),(4,94),(5,85),(6,90),(7,66),(8,99);*/
if n = 0 then
select count() from aa;
else if n > 0 and n < i then
select * from aa order by score desc;
else
while n > i do
set j=j+1;
insert into aa(score)values(j);
set i=i+1;
end while;
end if;
end if;
end

call xiaoshan(100)