mysql日记
1 create table shop( 2 id int unsigned not null auto_increment primary key , 3 name varchar(20) not null , 4 price decimal(4,2) not null default 0.00 , 5 others text not null 6 );
1 select distinct password from users; 2 select user_name,age from users where id>3; 3 insert into users (user_name,password,age,email,fee,create_at)values('liming','2342',23,'y2734tr72',123.78,20130627); 4 select user_name,id from users order by id desc(降序)/asc(升序); 5 select count(*)/计数 from users; 6 select sum/avg/max/min(age) as sum from users; 7 alter table users add/modify sex tinyint unsigned not null default 0 comment'0男1女' after age; 8 select student.name, student.age,grade.grade from student,grade where grade.stu_id=student.id ; 9 select student.name,grade.grade from student left join grade on grade.stu_id=student.id ; 10 create trigger ming afer/before insert(update) delete on biaoming for each row begin sql语句 end;
1 主键primary key(字段); 2 唯一unique(字段); 3 全文索引fulltext(); 4 外键索引foreign key(); 5 键key/index; 6 show index from user;查询索引；
1 create table mark( 2 -> id int unsigned not null auto_increment, 3 -> mark int not null, 4 -> stu_id int unsigned not null, 5 -> primary key(id), 6 -> foreign key(stu_id) references user(id)(外键索引必须和所关联的主键定义一致)。-> );变量：
1 create table user( 2 -> id int unsigned not null auto_increment , 3 -> user_name varchar(32) not null, 4 -> age tinyint unsigned not null, 5 -> primary key(id), 6 -> unique(user_name) 7 -> );
变量
1.set @变量名 =表达式。 2.{declare 变量名 类型 【default 默认值】
,set 变量名=值} 3.select @变量名:=值； 4.select 表达式 into 变量；
视图
SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。create view 表名 as 查询语句；
判断
1 begin 2 ... 3 end; 4 条件语句： 5 if 条件 then 6 代码 7 end if; 8 if 条件 then 9 代码1 10 else 11 代码2 12 end if;
存储过程
1 create procedure 名字（参数1，参数2，） 2 begin 3 代码 4 end
改变提交符： delimiter 符号
具体语句
1 show procedure status;//查看过程
1 create procedure p1(n int) 2 begin 3 if n=1 then 4 select 'spring' as 'season'; 5 elseif n=2 then 6 select 'summer' as 'season'; 7 elseif n=3 then 8 select 'autumn' as 'season'; 9 elseif n=4 then 10 select 'winter' as 'season'; 11 else 12 select wufawutian as season; 13 end if; 14 end&
调用： call 存储过程名字（参数）
csae 判断
1 case 变量 2 when 值 then 语句； 3 when 值 then 语句； 4 else 语句； 5 end case；
删除存储过程
1 drop procedure p1&
case实现
1 create procedure p2(n int) 2 begin 3 case n 4 when 1 then select 'spring' as 'season'; 5 when 2 then select 'summer' as 'season'; 6 when 3 then select 'autumn' as 'season'; 7 when 4 then select 'winter' as 'season'; 8 else select 'wufawutian' as 'season'; 9 end case; 10 end&
循环
1.loop循环
2.while 循环
3.repeat循环
1.loop循环
1 标签名:loop 2 leave 标签名 --退出循环 3 end loop；
2.while 循环
1 [标签:]while 条件 do 2 代码 3 end while;
3.repeat循环
1 repeat 2 代码 3 until 条件 end repeat;
使用loop循环，完成1到n的循环
1 create procedure p3(n int) 2 begin 3 declare i int default 1; 4 declare s int default 0; 5 aa:loop 6 set s=s+i; 7 set i=i+1; 8 if i>n then 9 leave aa; 10 end if; 11 end loop; 12 select s; 13 end&
while
1 create procedure p3(n int) 2 begin 3 declare i int default 1; 4 declare s int default 0; 5 while i<=n do 6 set s=s+i; 7 set i=i+1; 8 end while; 9 select s; 10 end&
repeat
1 create procedure p4(n int) 2 begin 3 declare i int default 1; 4 declare s int default 0; 5 repeat 6 set s=s+i; 7 set i=i+1; 8 until i>n end repeat; 9 select s; 10 end&
存储过程
1 create procedure 名字（参数1，参数2，） 2 begin 3 代码 4 end
1 create procedure p5( str varchar(1)) 2 begin 3 if str='h' then 4 select username from test where id>40; 5 else 6 select username from test where id<=40; 7 end if; 8 end&
复杂过程
1 create procedure p6(in n int,out re int) 2 begin 3 set re=n*n; 4 end&
1 create procedure p7(inout n int) 2 begin 3 set n=n*n; 4 end&
函数语法
1 create function 函数名(参数) return 返回值类型 2 begin 3 代码 4 end
例
1 create function f1(a int,b int) returns int 2 begin 3 return a+b; 4 end&
1 create function he(n int) returns int 2 begin 3 declare i int default 1; 4 declare s int default 0; 5 while i<=n do 6 set s=s+i; 7 set i=i+1; 8 end while; 9 return s; 10 end&
系统函数
1 select rand(); 2 select * from teat order by rand() limit 2; 3 select floor(3.9) 4 select ceil(3.1); 5 select round(3.5)//四舍五入 6 select ucase/length/char_length/replace('i am boy')//转大写 7 select length(trim(' abc '))//trim去空格 8 select lcase('HHH')转小写 9 截取字符串： 10 select left/right('asasasa',3) 11 select substring('asasasa',3,2)//从1开始 12 select concat('asasasa','adad')//连接 13 select coalesce(null,123); 14 coalesce(str1,str2)//若str1为空，则显示str2 15 日期： 16 select unix_timestamp(); 17 select from_unixtime(unix_timestamp()); 18 select now(); 19 select year/day/month/hour/(now()); 20 select curdate(); 21 select datediff(now(),'1997-10-8');
取出昨天的日期
1 取出昨天的日期
2 select date_sub(curdate(),interval 1 day); 3 select date_add(curdate(),interval -1 day); 4 取明天： 5 select date_sub(curdate(),interval -1 day); 6 select date_add(curdate(),interval 1 day);
存入时间
触发器
1 create trigger trigger_name 2 after/before insert/update/delete/ on 表名 3 for each row 4 begin 5 sql语句：(一句或多句) 6 end
例 ：插入订单表时book表数量减少
1 create trigger t1 2 after insert on b_order 3 for each row 4 begin 5 update book set b_num=b_num-new.much where b_id=new.book_id; 6 end
例：删除b_order的一条数据时，book表恢复原来的数量。
1 create trigger t2 2 after delete on b_order 3 for each row 4 begin 5 update book set b_num=b_num+old.much where b_id=old.book_id; 6 end
例：重新下单时，book表原来订单数量恢复，新订单book数量减少。
1 create trigger t3 2 after update on b_order 3 for each row 4 begin 5 update book set b_num=b_num+old.much where b_id=old.book_id; 6 update book set b_num=b_num-new.much where b_id=new.book_id; 7 end 8 //完成修改的思路 9 //1.撤销时订单book表恢复 10 //2.重新下单时book表减少
更多精彩
