sql进阶部分语句整理

-- 创建一个user表

create table t_user(

u_id int(11) primary key auto_increment,

u_name varchar(50) not null,

u_pwd varchar(50) not null,

u_address VARCHAR(50) not null

)

-- 创建单个普通索引

create index index_u_name on t_user(u_name);

-- 创建多个普通索引

create index index_pwd_address on t_user(u_pwd,u_address);

-- 查看已经存在的索引

show index from t_user;

-- 删除索引

drop index index_pwd_address on t_user;

drop index index_u_name on t_user;

-- 创建索引

alter table t_user add index (u_name);

-- 删除索引

alter table t_user drop index u_name;

-- 查看表的详细信息

explain select * from t_user where u_name='zhangsan';

-- 创建一个普通无参数的存储过程

create procedure pro_test1()

BEGIN

update t_user set u_pwd='321' where u_name='张三';

update t_user set u_name='二狗子' where u_name='张三';

end

-- 调用存储过程

call pro_test1();

-- 删除存储过程

drop procedure pro_test1;

-- 创建一个带（输入 in）参数的存储过程,in是默认就有的，创建过程中可以不写in

create procedure pro_test2(in uName1 varchar(50),in uName2 VARCHAR(50))

begin

update t_user set u_name=uName2 WHERE u_name=uName1;

end

call pro_test2('二狗子','张三');

drop procedure pro_test2;

-- 创建一个（输出 out）参数的存储过程

create procedure pro_test3(in num1 INT(11),in num2 INT(11),out result int(11))

BEGIN

set result:=num1+num2;

END

-- 声明一个用户变量（相当于java中的全局变量）

set @sum:=0;

call pro_test3(3,5,@sum);

-- 产看调用函数后的sum值

select @sum as '运算结果';

drop procedure pro_test3

-- 创建一个输入输出(inout)的存储过程

-- 既能接收用户传进去的变量自身的值，也能给用户返回一个结果值

create procedure pro_test4(num1 INT(11),num2 INT(11),inout result INT(11))

BEGIN

set result:=num1+num2+result;

END

set @sum:=10;

call pro_test4(3,6,@sum)

SELECT @sum as '运算结果';

drop procedure pro_test4;

-- 创建一个声明局部变量(declare)，并且将查询的结果赋值(into)给局部变量的存储过程

create procedure pro_test5(uName VARCHAR(50))

begin

-- 这里声明一个局部变量，未给其赋值，如果要赋值，则用dufault+值；

declare uId int(11);

SELECT u_id into uId from t_user where u_name=uName;

-- 这里是为了测试赋值后的局部变量值

SELECT uId as '查询到的用户ID';

END

call pro_test5('赵六');

drop procedure pro_test5

-- 流程控制

-- 条件控制语句

-- 如果t_stu这个表存在就删除，不存在就不做任何操作

drop table if exists t_stu;

-- 创建一个带有if的存储过程,当条件满足时才执行then后面的语句

-- if elseif

create procedure pro_if(age int(11))

BEGIN

if age>50 then SELECT '老年人' as '年龄段';

elseif age>30 then SELECT '中年人' as '年龄段';

elseif age>18 then SELECT '青年人' as '年龄段';

else SELECT '未成年' as '年龄段';

end if;-- 用于结束if语句

end

call pro_if(40);

drop procedure pro_if;

-- 创建一个if里面嵌套if的存储过程

create procedure pro_if2(age int(11))

BEGIN

if age>50 then SELECT '老年人' as '年龄段';

elseif age>30 then

if age>40 then SELECT '中老年人' as '年龄段';

else SELECT '中年人' as '年龄段';

end if;

elseif age>16 then

if age>20 then SELECT '青年人' as '年龄段';

else SELECT '青少年人' as '年龄段';

end if;

else SELECT '未成年' as '年龄段';

end if;-- 用于结束if语句

end

call pro_if2(48);

drop procedure pro_if2;

-- case 条件选择，相当于java中的switch

-- 这里的case是值匹配的形式,else在这里相当于java中的default

create procedure pro_case(age int(11))

BEGIN

case age

when 50 then SELECT '老年人' as '年龄段';

when 40 then SELECT '中老年人' as '年龄段';

when 30 then SELECT '中年人' as '年龄段';

when 20 then SELECT '青年人' as '年龄段';

else SELECT '未成年' as '年龄段';

end case;-- 用于结束if语句

end

call pro_case(30);

drop procedure pro_case;

-- case条件选择，这里的case是区间判断条件,else在这里相当于java中的default

create procedure pro_case2(age int(11))

BEGIN

case

when age>50 then SELECT '老年人' as '年龄段';

when age>40 then SELECT '中老年人' as '年龄段';

when age>30 then SELECT '中年人' as '年龄段';

when age>20 then SELECT '青年人' as '年龄段';

else SELECT '未成年' as '年龄段';

end case;-- 用于结束if语句

end

call pro_case2(30);

drop procedure pro_case2;

-- 循环结构

-- while循环

create procedure pro_while()

begin

declare i INT(11) default 1;

declare sum INT(11) default 0;

while i<=100 do

set sum := sum+i;

set i := i+1;

end while;

select sum as '结果';

end

call pro_while();

drop procedure pro_while;

-- repeat 重复执行

-- 重复执行repeat里面的语句，直到until后面的条件为真（满足时），才执行end repeat 结束repeat重复

create procedure pro_repeat()

BEGIN

declare i int default 1;

declare sum int default 1;

repeat

set sum:=sum*i;

set i:=i*2;

until i>100 end repeat; -- 这里的条件满足了就会执行end repeat结束repeat循环；

SELECT sum as '结果';

END

call pro_repeat();

drop procedure pro_repeat;

-- loop 一直循环执行，直到遇到leave才会结束循环

create procedure pro_loop()

begin

DECLARE i int default 1;

declare sum int default 1;

begin_start:loop

set sum:=sum*i;

set i:=i*2;

if i>100 THEN

leave begin_start;

end if;

end loop begin_start;

SELECT sum as '结果';

end

call pro_loop();

drop procedure pro_loop;