创建临时表 CREATE TEMPORARY TABLE tmp_table ```
CREATE TEMPORARY TABLE tmp_table SELECT * FROM `tb_fdt_courseitem` WHERE `StudentId`='0e02d67f-02ba-4cb7-87de-d5e1d4dcfa8e'
AND `StartDate`>='2012-01-01'
AND `EndDate`<='2013-12-31'
GROUP BY `SubjectId`;
查询
SELECT * FROM tmp_table;
删除
DROP TEMPORARY TABLE IF EXISTS tmp_table;
-- Mysql 存储过程
/*
set @result = 0;
create procedure login( -- 定义参数,有in、out、inout三种类型
in user varchar(10),
in pass varchar(10),
out result int
)
begin
declare passd varchar(10);-- declare 声明临时变量、类型,然后用set 进行赋值,declare 临时变量只能放在begin end 区域中,而其作用域也只是在begin end 中, 而 set @ 定义的变量是全局变量
select password into passd from login where username=user;
if passd like pass then -- If 语句,后面要加上 End IF,就像是case 后也要加 End Case 一样
select 'Login Success' as Massage;
set result = 1;
else
select 'Login Failed' as Message;
set result =0;
end if;
end;
*/
-- 调用存储过程 call login('root','root',@result);
-- 删除存储过程 drop procedure login
create procedure translate(
id int
)
begin
case id
when 1 then
select 'one' as trans;
when 2 then
select 'two' as trans;
when 3 then
select 'three' as trans;
else
select 'no trans' as trans;
end case;
end;
/*
case 用法有两种:
1. 条件变量在when 中
select name, case
when age>10 then xxxxx
else
xxxxxx
end case
2. 条件变量在case 中
select name,case age
when >10 then xxx
else xxxxxs
end case
*/
//再来一个演示 BEGIN
DECLARE long_status VARCHAR(20);
IF in_status = 'O' THEN
SET long_status='Overdue';
ELSEIF in_status = 'U' THEN
SET long_status='Up to date';
ELSEIF in_status = 'N' THEN
SET long_status='New';
END IF;
RETURN(long_status);
END;
//给指定时间,增加一天 DATE_ADD(OrderDate,INTERVAL 1 DAY)
//指定时间,转换为 年月日
DATE_FORMAT(p_time,'%Y-%m-%d')