lampabc.com,lamp学习本应更简单, 互帮 互助 共享 ~~~

简明MySQL——SQL进阶

--存储过程
我们常用的操作数据库语言的SQL语句在执行的时候需要先编译然后执行,而存储过程(Stroed Procedure)是一组为了完成特定功能的SQL语句集,经编译后储存在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)一调用执行它。

=========
优点:
=========
1、可以用流程控制语句编写,增加了SQL语言的灵活性。
2、可以多次调用,而不必重新编写该存储过程的SQL语句, 加快了执行速度、减少网络流量。
3、可以对存储过程进行访问权限控制,被作为一种安全机制来充分利用。

=========
定义:
=========
/*
DELIMITER分隔符后指定$$作为结束符标志
存储过程的参数的传送形式共有3种
in(传入参数) out(传出参数) inout(传入传出参数)
*/
--使用call调用存储过程
call sapo.proc_stu(@t);
select @t as 总人数;

delimiter //;
create procedure proc_stat
(in lowscore float,in highscore float,out total int )
begin
select count(*) into total from student where
score between lowscore and highscore;
end//
delimiter ;

若是在PHP代码中,定义的话不需要delimiter关键字,如:
create procedure proc_stat
(in lowscore float,in highscore float,out total int )
begin
select count(*) into total from student where
score between lowscore and highscore;
end;


call sapo.proc_stat(40.0,45.0,@t);
select @t as 总人数;

delimiter //;
create procedure proc_inout
(inout statscore float)
begin
select sum(score) into statscore from student where
score >statscore;
end//
delimiter ;

set @a=60.0;
call sapo.proc_inout(@a);
select @a as 及格同学的总分;

delimiter //;
create procedure proc_if
()
begin
declare a int default 5;
if a=1 then
select 'a:=1';
elseif a=2 then
select 'a:=2';
else
select 'a:=5';
end if;
end//
delimiter ;

call proc_if();

delimiter //;
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1;
IF p1 < 10 THEN
ITERATE label1; --继续循环lable1指向的循环体
END IF;
LEAVE label1;--跳出循环label1指向的循环体
END LOOP label1;
SET @x = p1;
END//
delimiter ;

call doiterate(100);
select @x;

--游标cursor
select * from student;

CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;--声明游标
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;--声明游标
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN cur1;--打开游标
OPEN cur2;--打开游标

REPEAT--repeat开始
FETCH cur1 INTO a, b;--从游标中抓取信息存储到变量中
FETCH cur2 INTO c;
IF NOT done THEN--外层if
IF b < c THEN--内层if
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;--内层if结束
END IF;--外层if结束
UNTIL done END REPEAT;--repeat结束

CLOSE cur1;--关闭游标

--预处理语句
--定义预处理语句
prepare stmt_select from
'select * from student limit ?,?';
--定义变量执行预处理语句
set @p1=0;
set @p2=2;

execute stmt_select using @p1,@p2;
--销毁预处理语句
deallocate prepare stmt_select;
--当存储过程需要返回结果集的时候需要使用预处理语句
delimiter //;
create procedure proc_prepare
(in startIndex int,in pageNo int)
begin
set @p1=startIndex;
set @p2=pageNo;
prepare stmt_page from 'select * from student limit ?,?';
execute stmt_page using @p1,@p2;
deallocate prepare stmt_page;
end//
delimiter ;
call proc_prepare(0,2);


--触发器(属于表trigger)
/*
触发器使用中存在OLD,NEW两张虚表
一般的使用语法为OLD.列名或者NEW.列名
可以取出一行记录中某列上数据的值

*/
insert into student values(null,'赵六',20.0);
select * from student;
drop trigger trigger_stu;
delimiter //;
create trigger trigger_update
before update
on student
for each row
begin
insert into mylog values('最新记录',OLD.score+NEW.score);

end//
delimiter ;
drop trigger trigger_update;
create table mylog
(
comm varchar(30),
score float
)
update student set score=120.0 where sid=4;
select * from mylog;

--修改表结构
alter table student
add column sex varchar(5);
--按照性别统计学生人数
select sex,count(*) from student group by sex;
--按照性别统计及格学生人数
select sex,count(*) from student where score>=60
group by sex ;
--统计女生及格学生的人数
select sex,count(*) from student where score>=60
group by sex having sex='f';
--统计所有学生的总分以及平均分
select sum(score) 总分,avg(score) 平均分 from student;
--按照性别统计学生的平均分
select sex,avg(score) from student
group by sex;
--统计分数在70至90分之间的男生的学生人数
select count(*) from student where
score between 70 and 90
and sex='f';
select max(score),min(score) from student;


--字符串函数
select upper('abcdefg');
select lower('ABCDEFG');
select length('abcdefg');
select CONCAT('123','456','abc');


select 1+1;
select 1/2;
select 3*4;

select CURRENT_DATE();
select CURRENT_TIME();
select dayofmonth(CURRENT_DATE());
select ADDDATE('1981-10-01',10);
select ADDDATE(now(),interval 1 day);
--计算时间区间
select datediff('2010-06-01','2010-05-20');