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 符号

1563070581041

 具体语句

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 whenthen 语句;
3 whenthen 语句;
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&

1563072759743

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&

1563073118505

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&

1563073278075

存储过程

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&

1563074467484

1563074498251

复杂过程

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

1563075174584

1 create procedure p7(inout n int)
2 begin
3 set n=n*n;
4 end&

1563075412087

函数语法

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&

1563076739509

1563076757256

系统函数

 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');

1563078214433

1563078468501

1563078974039

1563079047202

1563079181058

取出昨天的日期

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);

1563088564035

存入时间

1563091068522

1563091111042

 

1563091233309

触发器

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


1563112970466

1563113008230

例:删除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

1563113491607

1563113521171

1563113654075

1563113584280

1563113584280

例:重新下单时,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表减少

1563115294989

1563115374136

1563115441562

posted @ 2019-07-16 12:29 philxling 阅读(...) 评论(...) 编辑 收藏