SQL——存储过程基本语法

create table stue
(
id     int       auto_increment primary key    comment '  客户编号',
name   varchar(20)             comment '客户名称',
mima   varchar(100)           comment'客户密码 ' ,
phonr  varchar(20)            comment '客户电话',
xb       char(2)                  comment '选则男，女',
y_x      varchar(20)            comment   '邮箱 ' ,
d_z         varchar(20)               comment'地址'

)comment'记录客户基本信息';

insert into stue (id, name, mima, phonr, xb, y_x, d_z) values (1,'小草','123451','183953...','男','1273@qq','安徽'),
                                                                       (2,'小理','123452','18783...','男','1253@qq','安徽'),
                                                                       (3,'小马','123453','18353...','女','1238@qq','安徽'),
                                                                       (4,'小了','123454','18334...','男','1223@qq','安徽'),
                                                                       (5,'小个','123455','18327...','女','1283@qq','安徽'),
                                                                       (6,'小逼','123456','18369...','女','12309@qq','安徽'),
                                                                       (7,'小了就','123454','18334...','男','12523@qq','安徽'),
                                                                       (8,'小个好','123455','18327...','女','12873@qq','安徽'),
                                                                       (9,'小逼个','123456','18369...','女','12396@qq','安徽'),
                                                                       (10,'小了是','123454','18334...','男','12423@qq','安徽'),
                                                                       (11,'小个女','123455','18327...','女','12283@qq','安徽');





create  table  student1
(
id int primary key  auto_increment,
name varchar(16),
no  varchar(10)
)comment '学生';
insert into student1(id, name, no) values (1,'时科',2142151207),(2,'海龟',2142151208),(3,'工会',2142151209);


create table  coure
 (
  id int primary key auto_increment,
  name varchar(18)
)comment '课程';
insert into coure(id, name) values (4,'clode'),(1,'javaEE'),(3,'MySQL'),(2,'Spring');

create table  student1_coure
(
  id int primary key  auto_increment,
  student_id int,
  coure_id   int
)comment '学生与课程的中间表';
insert into student1_coure(id, student_id, coure_id) values (1,1,1),(2,1,2),(3,1,3),(4,2,2),(5,2,3),(6,3,4);






-- 存储过程
-- 创建
create procedure p1()  -- 创建无参的存储过程P1
begin         --  begin  end 中间封装着SQL语句
select  count(*)  from student1;
end;

-- 调用p1存储过程(意味着它将调用存储中的语句select)
call p1();

-- 查看
select * from information_schema.ROUTINES  where  ROUTINE_SCHEMA='事务'; -- (查询指定数据库的存储过程及状态信息)

show create procedure p1;--  查看存储过程p1的SQL语句

-- 删除
drop procedure p1;
drop procedure if exists p1  -- (加了一个可选参数)

调用

查看