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 变量；

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

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&

1 case 变量
2 when 值 then 语句；
3 when 值 then 语句；
4 else 语句；
5 end 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 leave 标签名  --退出循环
3 end loop；

1 [标签:]while 条件 do
2     代码
3 end while;

1 repeat
2     代码
3 until 条件 end repeat;

 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&

 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&

 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

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

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

 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表减少