//自定义 结束符号 $
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
//update
t3
hzy
AFTER
UPDATE
begin update chengchi set tong=tong-old.tong+new.tong where name=old.name; end
删除
例：
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; //倒序排列 