mysql procedure

set global auto_increment_increment=1; -- 设置序列的增长值
show global variables; -- 显示所有的global变量
show global variables like '%test%' -- 查询包含test字符串的global变量


group by分组
having 条件查询
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。
select  name from bank group by name having count(distinct bankName)>2
连接数据库：
mysql -uedb -pedbUser*1 -h192.168.36.214 -P3399 --default-character-set=gbk edb

DROP PROCEDURE IF EXISTS `insert_stu`;

CREATE PROCEDURE `insert_stu`(IN age INT(10),IN name VARCHAR(40),OUT result INT(10))
BEGIN
if age>10 then
insert into  user(name,sex,age) values(name,1,age);
set result=3;
end if;
END;

DROP TRIGGER `Tri_insert_user_after`;

CREATE DEFINER=`root`@`localhost` TRIGGER `Tri_insert_user_after` AFTER INSERT ON `user` FOR EACH ROW begin
if NEW.age>20 then  insert into bank(bankName,moneyNum,name) values('triTest',100,'testTri');
end if ;
for()
end;

//数据导出
mysqldump --user=root test >>d:\\test.txt







DROP PROCEDURE IF EXISTS `test`;

CREATE PROCEDURE `test_proc_while`()
BEGIN
#Routine body goes here...
declare i int default 10;
/*set @a=0;*/
while  i>3 do
set i=i-1;
select  num from share into @a;
if @a =5 then
insert into share values(i);
end if;
end while;

END;


DROP PROCEDURE IF EXISTS `test_proc_repeat`;

CREATE PROCEDURE `test_proc_repeat`()
BEGIN
#Routine body goes here...
declare b int default 0;
repeat
set b=b+1;
set @a=concat(@a,'aaaa');
UNTIL  b>10          /*这里不用分号*/
end repeat;
END ;


DROP PROCEDURE IF EXISTS `test_proc_loop`;

CREATE PROCEDURE `test_proc_loop`()
BEGIN
#Routine body goes here...
set @p1=1;
label1:loop
if @p1<10 then
set @p1=@p1+1;
insert into share values(@p1);
iterate label1;  /*ITERATE只可以出现在LOOP, REPEAT, 和WHILE语句内*/
else
leave label1;
end if;
end loop label1;
END ;



DROP PROCEDURE IF EXISTS `test_proc_case`;

CREATE PROCEDURE `test_proc_case`()
BEGIN
#Routine body goes here...
select * from share into @a;
if @a is null then
insert into share values(1);
select * from share into @a;
end if;
case  @a
when 1 then
update share set num=10;
end case;
END ;