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