mysql日记

create table shop(
     id int unsigned not null  auto_increment primary key ,
     name varchar(20) not null ,
     price decimal(4,2) not null default 0.00 ,
     others text not null
     );

select distinct password from users;
select user_name,age from users where id>3;
insert into users (user_name,password,age,email,fee,create_at)values('liming','2342',23,'y2734tr72',123.78,20130627);
select user_name,id from users order by id desc(降序)/asc(升序);
select count(*)/计数 from users;
select sum/avg/max/min(age) as sum from users;
alter table users add/modify sex tinyint unsigned not null default 0 comment'0男1女' after age;
select student.name, student.age,grade.grade from student,grade where grade.stu_id=student.id ;
select student.name,grade.grade from student left join grade on grade.stu_id=student.id ;
create trigger ming afer/before insert(update) delete on biaoming for each row begin  sql语句 end;

主键primary key(字段);
唯一unique(字段);
全文索引fulltext();
外键索引foreign key();
键key/index;
 show index from user;查询索引；

create table mark(
    -> id int unsigned not null auto_increment,
    -> mark int not null,
    -> stu_id int unsigned not null,
    -> primary key(id),
    -> foreign key(stu_id) references user(id)(外键索引必须和所关联的主键定义一致)。-> );变量：

create table user(
    -> id int unsigned not null   auto_increment ,
    -> user_name varchar(32) not null,
    -> age tinyint unsigned  not null,
    -> primary key(id),
    -> unique(user_name)
    -> );

变量

1.set @变量名 =表达式。
2.{declare 变量名 类型 【default 默认值】
　　,set 变量名=值}
3.select @变量名:=值；
4.select 表达式 into 变量；

视图

create view 表名 as 查询语句；

判断

begin
...
end;
条件语句：
 if 条件 then
 代码
 end if;
if 条件 then
 代码1
 else
 代码2
 end if;

存储过程

create procedure 名字（参数1，参数2，）
 begin
     代码
 end

改变提交符： delimiter 符号

 具体语句

show procedure status;//查看过程

create procedure p1(n int)
    begin
    if n=1 then
    select 'spring' as 'season';
    elseif n=2 then
    select 'summer' as 'season';
    elseif n=3 then
    select 'autumn' as 'season';
    elseif n=4 then
    select 'winter' as 'season';
    else
    select wufawutian as season;
    end if;
    end&

调用： call 存储过程名字（参数）

csae 判断

case 变量
when 值 then 语句；
when 值 then 语句；
else 语句；
end case；

删除存储过程

 drop procedure p1& 

case实现

create procedure p2(n int)
    begin
    case n
    when 1 then select 'spring' as 'season';
    when 2 then select 'summer' as 'season';
    when 3 then select 'autumn' as 'season';
    when 4 then select 'winter' as 'season';
    else select 'wufawutian' as 'season';
    end case;
    end&

循环

1.loop循环

2.while 循环

3.repeat循环

1.loop循环

标签名:loop
leave 标签名  --退出循环
end loop；

2.while 循环

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

3.repeat循环

repeat
    代码
until 条件 end repeat;

使用loop循环，完成1到n的循环

create procedure p3(n int)
begin
declare i int default 1;
declare s int default 0;
aa:loop
    set s=s+i;
    set i=i+1;
    if i>n then
    leave aa;
    end if;
end loop;
select s;
end&

while

create procedure p3(n int)
begin
declare i int default 1;
declare s int default 0;
while i<=n do
    set s=s+i;
    set i=i+1;
end while;
select s;
end&

repeat

create procedure p4(n int)
begin
declare i int default 1;
declare s int default 0;
repeat
    set s=s+i;
    set i=i+1;
until i>n end repeat;
select s;
end&

存储过程

 create procedure 名字（参数1，参数2，）
 begin
     代码
 end

create procedure p5( str varchar(1))
begin
if str='h' then
select username from test where id>40;
else
select username from test where id<=40;
end if;
end&

复杂过程

create procedure p6(in n int,out re int)
begin
set re=n*n;
end&

create procedure p7(inout n int)
begin
set n=n*n;
end&

函数语法

create function 函数名(参数) return 返回值类型
begin
    代码
end

例

create function f1(a int,b int) returns int
begin
    return a+b;
end&

create function he(n int) returns int
begin
    declare i int default 1;
    declare s int default 0;
    while i<=n do
        set s=s+i;
        set i=i+1;
    end while;
    return s;
end&

系统函数

select rand();
select * from  teat order by rand() limit 2;
select floor(3.9)
select ceil(3.1);
select round(3.5)//四舍五入
select ucase/length/char_length/replace('i am boy')//转大写
select length(trim('  abc  '))//trim去空格
select lcase('HHH')转小写
截取字符串：
select left/right('asasasa',3)
select substring('asasasa',3,2)//从1开始
select concat('asasasa','adad')//连接
select coalesce(null,123);
coalesce(str1,str2)//若str1为空，则显示str2
日期：
select unix_timestamp();
select from_unixtime(unix_timestamp());
select now();
select year/day/month/hour/(now());
select curdate();
select datediff(now(),'1997-10-8');

取出昨天的日期

取出昨天的日期
2 select date_sub(curdate(),interval 1 day);
select date_add(curdate(),interval -1 day);
取明天：
select date_sub(curdate(),interval -1 day);
select date_add(curdate(),interval 1 day);

存入时间

 

触发器

create trigger trigger_name
after/before insert/update/delete/ on 表名
for each row
begin
    sql语句：(一句或多句)
end

例 ：插入订单表时book表数量减少

create trigger t1
after insert on b_order
for each row
begin
    update book set b_num=b_num-new.much where b_id=new.book_id;
end

例：删除b_order的一条数据时，book表恢复原来的数量。

create trigger t2
after delete on b_order
for each row
begin
    update book set b_num=b_num+old.much where b_id=old.book_id;
end

例：重新下单时，book表原来订单数量恢复，新订单book数量减少。

create trigger t3
after update on b_order
for each row
begin
    update book set b_num=b_num+old.much where b_id=old.book_id;
    update book set b_num=b_num-new.much where b_id=new.book_id;
end
//完成修改的思路
//1.撤销时订单book表恢复
//2.重新下单时book表减少