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

改變提交符： 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表減少