mySql 常用命令

//自定义 结束符号 $
delimiter $

//创建表(库存表）
create table a2(
xuhao int auto_increment,
primary key(xuhao),
name varchar(10) charset utf8 collate utf8_general_ci not null,
bianhao int(10),
danjia int(6),
kucun int(10)
)
engine=innodb charset utf8 collate utf8_general_ci$

//创建订单表
create table b2(
xuhao int auto_increment,
primary key(xuhao),
name varchar(10) charset utf8 collate utf8_general_ci,
bianhao int(10),
number int(10)
)
engine=innodb charset utf8 collate utf8_general_ci$

insert into a2(name,bianhao,danjia,kucun)values('苹果',100001,15,90000),
('草莓',100002,18,5000),('香蕉',100003,22,4000),
('葡萄',100004,19,2000)$


insert into b2(name,bianhao,number)values('苹果',100001,234);

//触发器添加
create trigger cfq after insert on dd for each row begin update kc set
kucun=kucun-new.number where bianhao=new.bianhao; end$


//触发器删除
create trigger t2 after delete on dingdan for each row begin update kucun set 
kucun=kucun+old.number where bianhao=old.bianhao; end$

//触发器修改
create trigger t3 after update on dingdan for each row begin update kucun set 
kc=kc+old.dingnum-new.dingnum where number=old.number; end$

内容增加
insert into 表名()values();

内容删除
delete from 表名 where 字段名='值';

内容修改
update 表名 set kucun=9000;


删除数据库
 mysql> drop database php29;













mysql


关键词的顺序

where-group by-having-order by -limit


数据库 and 表:

c                //去除报错声音

show databases;            //查看数据库
show tables            //查看表
create database            //创建数据库

rename table 原表名 to 新表名;    //表重命名
drop database 库名;        //删除数据库

创建表的常用类型
int         都是正数   0-2的32次方    有负值   -？~？

char(length)        固定长度
varchar(length)     不固定长度 

auto_increment   //自增（只有int型）
notnull          //该字段内容不能为空
default '默认值' //该字段如果不添加内容执行默认值
comment '值'     //该字段名的备注
primary key(id)  //设置主键  （自增字段自带主键）




内容
insert into 表名(字段)value(值),(值);         //添加内容
    
select * from 表名;                  //查询内容
select 字段名 from 表名; 
select 字段1,字段2 from 表名;

update 表名 set 字段名='值' where 字段名='值'; //修改内容

delete from 表名 where 字段名='值';        //删除内容


字段结构
（after 后的...）
alter table 表名 add 字段 类型;            //添加字段

alter table 表名 drop 字段名;            //删除字段

alter table 表名 change 原字段 新字段 类型;    //修改字段





where条件 查询（<,>,<=,>=,!=）
select * from 表名 where name="王二";
select * from 表名 id > 2;
select * from 表名 where id in (3,5,7);

select * from 表名 where name like '_小%';    //模糊查询
            
select * from 表名 where name like '_小%' and id=9;//多条件查询

select * from 表名 order by 字段名 asc/desc;    //升/降排列

select * from class1 order by cj desc,name limit 2,5;//分页查询






max()  最大值
min()  最小值
sum()  求和
avg()  平均数 
count()统计记录数
例:select name,sum(cj) from 表名 group by name;

通过生日计算年龄
select now()  //获取当前时间 
select year(now()) //获取当前年份
select name,sex,birth,year(now())-year(birth) 
as age from class where id > 1;





合并
select * from a union select * from b;        //a,b表合并

select name,sum(cj) from class1 group by name having sum(cj)>380 order by sum(cj) desc limit 0,3;    //虚拟字段查询

where-group by-having-order by -limit        //关键词的顺序
例:
select km as 科目,sum(cj) as 总成绩 from (select a.km,a.cj from a union all select b.km,b.cj from b) c group by c.km; 
//按照成绩查询合并后的表 （不覆盖相同项）


外联(left join)(right join)
例:
select gril.name,gril.phone,boy.name,boy.phone from 
gril left join boy on gril.phone=boy.phone;

内联 
例:
select gril.name,gril.phone,boy.name,boy.phone from 
gril inner join boy on gril.phone=boy.phone;





选择判断   
case 字段名 when 原值1 then 新值1
            when 原值2 then 新值2    
            ………………
            end






触发器　trigger

① 监视操作 insert / update / delete
② 触发时间 after / before
③ 触发操作 insert / update / delete
④ 监视表   表的名称 


例:
增加
create trigger t1 after insert on dingdan for each row begin update kucun set kc=kc-new.dingnum where number=new.number; end$

create trigger t1 after insert on hzy for each row begin update chengchi set tong=tong+new.tong where name=new.name; end$

create trigger t_up after update on hzy for each row begin update chengchi set liang=liang-old.liang+new.liang where name=old.name; end$
delimiter $
//insert
t1
hzy
AFTER
INSERT
begin update chengchi set tong=tong+new.tong where name=new.name; end
root@localhost
//update
t3
hzy
AFTER
UPDATE
begin update chengchi set tong=tong-old.tong+new.tong where name=old.name; end
root@localhost


删除
例：
create trigger t2 after delete on dingdan for each row begin update kucun set kc=kc+old.dingnum where number=old.number; end$

修改
create trigger t3 after update on dingdan for each row begin update kucun set kc=kc+old.dingnum-new.dingnum where number=old.number; end$


//root设置密码 ,密码为空和修改密码
mysql> use mysql;
mysql> UPDATE user SET password=PASSWORD("123456") WHERE user='root';
mysql> FLUSH PRIVILEGES;


select name,href from rtfz where id=1 order by id desc limit 0,4; //倒序排列