请选择 进入手机版 | 继续访问电脑版
 找回密码
 立即注册
  • 便民服务
  • 关注我们
  • 社区新手
索引
索引:是一种数据结构
索引的作用
可以用索引快速的访问数据库中的特定信息
索引分类
普通索引
show index from emp;
create index xiaoliu on emp(sid);  为emp表中的sid创建一个索引为xiaoliu
create index xiaoliu1 on emp(sid,mame)   为emp表中的sid ,name 创建个索引为xiaoliu1

删除索引
alter table emp drop index xiaoliu1;  删除小刘这个索引

唯一索引:(对应的字段值必须是唯一的,但允许有空值)
create unique index aa on emp(sid)  创建唯一索引
主键索引:和主键约束相辅相成


数据库视图
视图是一个虚拟的表
视图 假设有表emp
创建视图:create view dcs asselect sid,name,age from emp)
视图修改会影响基本表,基本表修改也会影响视图
drop view dcs  删除视图

DDL:数据库定义语言(对表和表结构操作)
create
alter table
drop
DML:对数据库操作语言(对表数据操作)
insert into
delete
update
select

数据库约束
not null 非空约束
primary key  主键约束
auto_increment  自增长约束
default  默认值约束    前四种都是对表结构约束

froeigin key   外键约束 对表与表之间的约束

show create table dcs1 查看建表语句
create table dcs1(id int(5) primary key,name varchar(10)) engine=innodb;
创建dscs1表,修改类型为innodb
create table dcs2


如果dcs1和dcs2存在外键约束,dcs1(主表) dcs2(子表)
主表中不存在数据,不能在子表中进行插入
如果要删除主表的数据,需要先删除与主表相关的子表数据;
alter table dcs2 drop foreige key  删除外键约束0 and n8 then#条件3\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"pYzF-1660121525971","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"zuBM-1660121525972","leaves":[{"text":"  select max(score) from aa;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"dJjY-1660121525973","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"W5vQ-1660121525974","leaves":[{"text":"else#其他情况\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"F2eL-1660121525975","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"6oGW-1660121525976","leaves":[{"text":" select * from aa;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":&quot5MC-1660121525977","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"6uNq-1660121525978","leaves":[{"text":"end if;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"JwOM-1660121525979","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"9io5-1660121525980","leaves":[{"text":"end if;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"9sER-1660121525982","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"iq1E-1660121525983","leaves":[{"text":"end if;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"1ROS-1660121525984","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"6Nhx-1660121525985","leaves":[{"text":"end#存储过程结束\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"zK5D-1660121525986","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"Iled-1660121525987","leaves":[{"text":"\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"NgSA-1660121525988","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"2BJP-1660121525989","leaves":[{"text":"call dcs18(-1)#调用存储过程\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"gc21-1660121525990","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"1G1W-1660121525991","leaves":[{"text":"\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"HcEq-1660121525992","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"gUKD-1660121525993","leaves":[{"text":"\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"506T-1660121525994","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"aVOX-1660121525995","leaves":[{"text":"while 语句的格式: \r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"1VvX-1660121525996","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"YOLy-1660121525997","leaves":[{"text":"while 条件 do \r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"1Z6n-1660121525998","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"yMoO-1660121525999","leaves":[{"text":"执行循环体(sql) \r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"ZQx1-1660121526000","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"BSgx-1660121526001","leaves":[{"text":"end while;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"orEJ-1660121526002","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"RoRD-1660121526003","leaves":[{"text":"\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"VO7A-1660121526004","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"tquU-1660121526005","leaves":[{"text":"注意: \r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"vKUR-1660121526006","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"m82M-1660121526007","leaves":[{"text":"什么时候进入循环:当条件成立时,进入循环 \r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"JlV9-1660121526008","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"2z18-1660121526009","leaves":[{"text":"什么时候退出循环:当条件不成立时,退出循环\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"GTqD-1660121526010","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"ZyPe-1660121526011","leaves":[{"text":"\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"eA0B-1660121526013","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"CAIa-1660121526014","leaves":[{"text":"drop procedure if exists dcs18;#如果dcs18这个存储过程存在则删除\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"ArFr-1660121526015","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"6AJ2-1660121526016","leaves":[{"text":"create procedure dcs18(n int)#创建存储过程,n是变量 int是数据类型\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"HLCd-1660121526017","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"1ZLa-1660121526018","leaves":[{"text":"begin#存储过程开始\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"O8Pg-1660121526019","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"NJJj-1660121526020","leaves":[{"text":"#sql集合\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"Yleg-1660121526021","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"rNHk-1660121526022","leaves":[{"text":"declare i int(5)default(select count(*) from aa);#declare声明变量i 默认值为表的行数\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"2GAe-1660121526023","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"I9kv-1660121526024","leaves":[{"text":"/*drop table if exists aa;#如果aa存在删除\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"Oqvu-1660121526025","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"jL80-1660121526026","leaves":[{"text":"create table aa(id int(5)primary key auto_increment,score int(5));\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"ZujI-1660121526027","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"1btg-1660121526028","leaves":[{"text":"insert into aa values(1,88),(2,75);\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"MVOj-1660121526029","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"9F4U-1660121526030","leaves":[{"text":"insert into aa values(3,79),(4,95);\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"5DWD-1660121526031","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"LTvp-1660121526032","leaves":[{"text":"insert into aa values(5,85),(6,63);\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"FLg5-1660121526033","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"Hik0-1660121526034","leaves":[{"text":"insert into aa values(7,90),(8,99);*/\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"Vnod-1660121526035","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"qk2N-1660121526036","leaves":[{"text":"/*select * from aa;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"csG6-1660121526037","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"QH4n-1660121526038","leaves":[{"text":"select * from aa where id=n;#带参数查询\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"ujUL-1660121526039","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"Nfih-1660121526040","leaves":[{"text":"#单分支if判断语句\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"eKFy-1660121526041","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":&quot8Yk-1660121526042","leaves":[{"text":"if n=0 then\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":&quotstj-1660121526043","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"HPgK-1660121526044","leaves":[{"text":"  select count(*) from aa;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"lH5r-1660121526046","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"Mcu5-1660121526047","leaves":[{"text":"else\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"epF7-1660121526048","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"8yTs-1660121526049","leaves":[{"text":"  select * from aa order by score desc;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"AbXY-1660121526050","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"UapQ-1660121526051","leaves":[{"text":"end if;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":&quotXaU-1660121526052","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"bZEW-1660121526053","leaves":[{"text":"#if多分支判断\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"8gTY-1660121526054","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"Zpps-1660121526055","leaves":[{"text":"if n=0 then#条件1\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"zUDj-1660121526056","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"XVQt-1660121526057","leaves":[{"text":"  select count(*) from aa;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"jYqf-1660121526058","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"8gyf-1660121526059","leaves":[{"text":"else if n>0 and ni then#条件3\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"4Qqz-1660121526064","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"YLhY-1660121526065","leaves":[{"text":"  select max(score) from aa;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"Zag1-1660121526066","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"gjgt-1660121526067","leaves":[{"text":"else#其他情况\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"DRSw-1660121526068","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"wGPU-1660121526069","leaves":[{"text":" select * from aa;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"1RfJ-1660121526070","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"beVN-1660121526071","leaves":[{"text":"end if;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"wD20-1660121526072","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"4v2h-1660121526073","leaves":[{"text":"end if;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"dLPg-1660121526074","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"zvTE-1660121526075","leaves":[{"text":"end if;*/\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"tzHs-1660121526077","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"byUo-1660121526078","leaves":[{"text":"#while 循环\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"JqV7-1660121526079","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"2Bx2-1660121526080","leaves":[{"text":"if n=0 then\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"vLML-1660121526081","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"J0EF-1660121526082","leaves":[{"text":"  select count(*)from aa;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"WTbS-1660121526083","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"WYLm-1660121526084","leaves":[{"text":"else if n>0 and ni do\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"enXg-1660121526091","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"qZuk-1660121526092","leaves":[{"text":" insert into aa(score)values(88);\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"AzSj-1660121526093","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"WlHY-1660121526094","leaves":[{"text":" set i=i+1;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":&quotSVy-1660121526095","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"sSUg-1660121526096","leaves":[{"text":" end while;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"fqSS-1660121526097","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"oiKk-1660121526098","leaves":[{"text":"end if;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"BmQK-1660121526099","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"gL2W-1660121526100","leaves":[{"text":"end if;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"VQDS-1660121526101","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"sJXp-1660121526102","leaves":[{"text":"end#存储过程结束\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"70QZ-1660121526103","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"yhZC-1660121526104","leaves":[{"text":"\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"bnX3-1660121526105","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"rEJ2-1660121526106","leaves":[{"text":"call dcs18(200)#调用存储过程\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"Liwb-1660121526108","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"EYU2-1660121526109","leaves":[{"text":"\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"DEFB-1660121526110","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"rnhy-1660121526111","leaves":[{"text":"drop procedure if exists dcs18;#如果dcs18这个存储过程存在则删除\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"ieoc-1660121526112","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":&quotI0U-1660121526113","leaves":[{"text":"create procedure dcs18(n int)#创建存储过程,n是变量 int是数据类型\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"0BWp-1660121526114","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"2G1a-1660121526115","leaves":[{"text":"begin#存储过程开始\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"QdQ3-1660121526116","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"4itv-1660121526117","leaves":[{"text":"#sql集合\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"DMzR-1660121526118","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"6aw0-1660121526119","leaves":[{"text":"declare i int(5)default(select count(*) from aa);#declare声明变量i 默认值为表的行数\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"6xVz-1660121526120","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"kyie-1660121526121","leaves":[{"text":"declare max int(5)default(select max(score) from aa);#最高分数变量\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"JS0a-1660121526122","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"ArS8-1660121526123","leaves":[{"text":"/*drop table if exists aa;#如果aa存在删除\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"pImY-1660121526124","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"FQfQ-1660121526125","leaves":[{"text":"create table aa(id int(5)primary key auto_increment,score int(5));\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"9Dyq-1660121526126","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"yNoN-1660121526127","leaves":[{"text":"insert into aa values(1,88),(2,75);\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"xCCe-1660121526128","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"AflP-1660121526129","leaves":[{"text":"insert into aa values(3,79),(4,95);\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"Z5OA-1660121526130","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"IhR4-1660121526131","leaves":[{"text":"insert into aa values(5,85),(6,63);\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"U3n2-1660121526132","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"aBYP-1660121526133","leaves":[{"text":"insert into aa values(7,90),(8,99);*/\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"4tC0-1660121526134","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"2WkB-1660121526135","leaves":[{"text":"/*select * from aa;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"VudE-1660121526136","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"5wcN-1660121526137","leaves":[{"text":"select * from aa where id=n;#带参数查询\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"mLgd-1660121526138","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"AIcu-1660121526139","leaves":[{"text":"#单分支if判断语句\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"hoee-1660121526140","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":&quotnzn-1660121526141","leaves":[{"text":"if n=0 then\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"nrO1-1660121526142","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"MFtB-1660121526143","leaves":[{"text":"  select count(*) from aa;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"nDZG-1660121526144","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"Njix-1660121526145","leaves":[{"text":"else\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"4ajx-1660121526146","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"7PfL-1660121526147","leaves":[{"text":"  select * from aa order by score desc;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"H2xT-1660121526149","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"I0vH-1660121526150","leaves":[{"text":"end if;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"dwk0-1660121526151","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"i7lA-1660121526152","leaves":[{"text":"#if多分支判断\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"dPHU-1660121526153","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"5Aq6-1660121526154","leaves":[{"text":"if n=0 then#条件1\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"3cTf-1660121526155","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"R6sN-1660121526156","leaves":[{"text":"  select count(*) from aa;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"fjkI-1660121526157","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"56SE-1660121526158","leaves":[{"text":"else if n>0 and ni then#条件3\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"sUlX-1660121526163","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"LlF2-1660121526164","leaves":[{"text":"  select max(score) from aa;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"wpud-1660121526165","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"AAdV-1660121526166","leaves":[{"text":"else#其他情况\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"hX9x-1660121526167","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"mvJm-1660121526168","leaves":[{"text":" select * from aa;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"vhjt-1660121526169","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"8ycY-1660121526170","leaves":[{"text":"end if;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"fgHt-1660121526171","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"qQDa-1660121526172","leaves":[{"text":"end if;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"usqi-1660121526173","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"N0e5-1660121526174","leaves":[{"text":"end if;*/\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"OaGj-1660121526175","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"9QlX-1660121526176","leaves":[{"text":"#while 循环\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"aIFP-1660121526177","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"BQsa-1660121526178","leaves":[{"text":"if n=0 then\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"jUMf-1660121526179","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"xEVf-1660121526180","leaves":[{"text":"  select count(*)from aa;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"OdF0-1660121526181","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":&quotqBP-1660121526182","leaves":[{"text":"else if n>0 and ni do\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"xSFz-1660121526189","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"rfeU-1660121526190","leaves":[{"text":"  set max=max+1;","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"ywZk-1660134619006","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"6HaJ-1660134619004","leaves":[{"text":"\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"mrYW-1660121526192","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"HVlb-1660121526193","leaves":[{"text":" insert into aa(score)values(max);\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"G3Iz-1660121526194","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"L1FN-1660121526195","leaves":[{"text":" set i=i+1;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"8HqO-1660121526196","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"ph6f-1660121526197","leaves":[{"text":" end while;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"UM6I-1660121526198","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"EnYF-1660121526199","leaves":[{"text":"end if;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"9NhS-1660121526200","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"TAIH-1660121526201","leaves":[{"text":"end if;\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"fKEo-1660121526202","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"7NmB-1660121526203","leaves":[{"text":"end#存储过程结束\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"eYfW-1660121526204","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"lmt0-1660121526205","leaves":[{"text":"\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"oXm3-1660121526206","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"3IFf-1660121526207","leaves":[{"text":"","marks":[{"type":"fontSize","value":18}]}]}],"state":{}}]">存储过程
固定格式
create procedure 存储过程名称(参数名,数据类型)#创建存储过程
begin#存储过程开始
存储过程体
end#存储过程结束

call 存储过程名称()#调用存储过程

带参数查询
drop procedure if exists dcs18;#如果dcs18这个存储过程存在则删除
create procedure dcs18(n int)#创建存储过程,n是变量 int是数据类型
begin#存储过程开始
#sql集合
drop table if exists aa;#如果aa存在删除
create table aa(id int(5)primary key auto_increment,score int(5));
insert into aa values(1,88),(2,75);
insert into aa values(3,79),(4,95);
insert into aa values(5,85),(6,63);
insert into aa values(7,90),(8,99);
select * from aa;
select * from aa where id=n;#带参数查询
end#存储过程结束

call dcs18(8)#调用存储过程


if条件判定语句
单分支

if 条件 then
  sql语句1
else
  sql语句2
end if;

单分支判断
drop procedure if exists dcs18;#如果dcs18这个存储过程存在则删除
create procedure dcs18(n int)#创建存储过程,n是变量 int是数据类型
begin#存储过程开始
#sql集合
drop table if exists aa;#如果aa存在删除
create table aa(id int(5)primary key auto_increment,score int(5));
insert into aa values(1,88),(2,75);
insert into aa values(3,79),(4,95);
insert into aa values(5,85),(6,63);
insert into aa values(7,90),(8,99);
/*select * from aa;
select * from aa where id=n;#带参数查询*/
#单分支if判断语句
if n=0 then
  select count(*) from aa;
else
  select * from aa order by score desc;
end if;
end#存储过程结束

call dcs18(0)#调用存储过程

if条件判断语句多分支
if 条件1 then
   sql语句1
else if 条件2 then
   sql语句2
else if 条件3 then
   sql语句3
else
  sql语句4

end if;
end if;
end if;


多分支判断
drop procedure if exists dcs18;#如果dcs18这个存储过程存在则删除
create procedure dcs18(n int)#创建存储过程,n是变量 int是数据类型
begin#存储过程开始
#sql集合
drop table if exists aa;#如果aa存在删除
create table aa(id int(5)primary key auto_increment,score int(5));
insert into aa values(1,88),(2,75);
insert into aa values(3,79),(4,95);
insert into aa values(5,85),(6,63);
insert into aa values(7,90),(8,99);
/*select * from aa;
select * from aa where id=n;#带参数查询
#单分支if判断语句
if n=0 then
  select count(*) from aa;
else
  select * from aa order by score desc;
end if;*/
#if多分支判断
if n=0 then#条件1
  select count(*) from aa;
else if n>0 and n<=8 then#条件2
  select * from aa order by score desc;
else if n>8 then#条件3
  select max(score) from aa;
else#其他情况
select * from aa;
end if;
end if;
end if;
end#存储过程结束

call dcs18(-1)#调用存储过程


while 语句的格式:
while 条件 do
执行循环体(sql)
end while;

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

drop procedure if exists dcs18;#如果dcs18这个存储过程存在则删除
create procedure dcs18(n int)#创建存储过程,n是变量 int是数据类型
begin#存储过程开始
#sql集合
declare i int(5)default(select count(*) from aa);#declare声明变量i 默认值为表的行数
/*drop table if exists aa;#如果aa存在删除
create table aa(id int(5)primary key auto_increment,score int(5));
insert into aa values(1,88),(2,75);
insert into aa values(3,79),(4,95);
insert into aa values(5,85),(6,63);
insert into aa values(7,90),(8,99);*/
/*select * from aa;
select * from aa where id=n;#带参数查询
#单分支if判断语句
if n=0 then
  select count(*) from aa;
else
  select * from aa order by score desc;
end if;
#if多分支判断
if n=0 then#条件1
  select count(*) from aa;
else if n>0 and n<=i then#条件2
  select * from aa order by score desc;
else if n>i then#条件3
  select max(score) from aa;
else#其他情况
select * from aa;
end if;
end if;
end if;*/
#while 循环
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;
end while;
end if;
end if;
end#存储过程结束

call dcs18(200)#调用存储过程

drop procedure if exists dcs18;#如果dcs18这个存储过程存在则删除
create procedure dcs18(n int)#创建存储过程,n是变量 int是数据类型
begin#存储过程开始
#sql集合
declare i int(5)default(select count(*) from aa);#declare声明变量i 默认值为表的行数
declare max int(5)default(select max(score) from aa);#最高分数变量
/*drop table if exists aa;#如果aa存在删除
create table aa(id int(5)primary key auto_increment,score int(5));
insert into aa values(1,88),(2,75);
insert into aa values(3,79),(4,95);
insert into aa values(5,85),(6,63);
insert into aa values(7,90),(8,99);*/
/*select * from aa;
select * from aa where id=n;#带参数查询
#单分支if判断语句
if n=0 then
  select count(*) from aa;
else
  select * from aa order by score desc;
end if;
#if多分支判断
if n=0 then#条件1
  select count(*) from aa;
else if n>0 and n<=i then#条件2
  select * from aa order by score desc;
else if n>i then#条件3
  select max(score) from aa;
else#其他情况
select * from aa;
end if;
end if;
end if;*/
#while 循环
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 max=max+1;

insert into aa(score)values(max);
set i=i+1;
end while;
end if;
end if;
end#存储过程结束





分享至 : QQ空间
收藏

0 个回复

您需要登录后才可以回帖 登录 | 立即注册