mysql 常用语句，

个人整理，有部分跟sql server不同，如果实验不成功，就自行百度，这里只适用于mysql
create database shuihu
– 创建数据库

create table bumen

(
bianhao int primary key ,
mingcheng varchar(50),
didain varchar(90))

create table renwu
– 创建表
(
paihang int primary key,
xingming varchar(50),
zhiwu varchar(50),
shangji int,
ruzhishijian datetime,
gongzi numeric(6,1),
buzhu numeric (5,1),
bianhao it,
foreign key(bianhao) references bumen(bianhao))
– 外键格式特别注意

insert into bumen VALUES(1,‘总领’,‘聚义厅’)
insert into bumen VALUES(2,‘文职’,‘前山’)
insert into bumen VALUES(3,‘马军头领’,‘左山’)
insert into bumen VALUES(4,‘步军头领’,‘右山’)
insert into bumen VALUES(5,‘水军头领’,‘水边’)
insert into bumen VALUES(6,‘后勤头领’,‘山洞’)

update bumen set
mingcheng =case bianhao
when 3 then ‘马军’
when 4 then ‘步军’
when 5 then ‘水军’
when 6 then ‘后勤’
end
where bianhao in (3,4,5,6)
– 批量更新数据

insert into renwu (paihang,xingming,zhiwu,ruzhishijian,gongzi,buzhu,bianhao)
VALUES (101,‘宋江’,‘寨主’,‘2012-5-8’,‘20000’,‘3000’,1)
insert into renwu VALUES(102,‘卢俊义’,‘副寨主’,101,‘2012-5-8’,‘18000’,‘3000’,1)
insert into renwu VALUES(103,‘吴用’,‘军师’,101,‘2012-5-8’,‘17000’,‘2300’,2)
insert into renwu VALUES(104,‘公孙胜’,‘副军师’,103,‘2012-5-8’,‘16000’,‘3400’,2)
insert into renwu VALUES(105,‘关胜’,‘大将’,102,‘2012-5-8’,‘15000’,‘1200’,3)
insert into renwu VALUES(106,‘鲁智深’,‘大将’,101,‘2012-5-8’,‘14000’,‘4300’,4)
insert into renwu VALUES(107,‘武松’,‘大将’,101,‘2012-5-8’,‘13000’,‘3100’,5)
insert into renwu (paihang,xingming,zhiwu,shangji,ruzhishijian,gongzi,bianhao)
VALUES (108,‘柴进’,‘总管’,105,‘2012-5-8’,‘20000’,6)
insert into renwu (paihang,xingming,zhiwu,shangji,ruzhishijian,gongzi,buzhu,bianhao)
VALUES (109,‘宋江小’,‘总管’,106,‘2012-5-8’,‘20000’,‘2000’,4)
insert into renwu (paihang,xingming,zhiwu,shangji,ruzhishijian,gongzi,buzhu,bianhao)
VALUES (110,‘宋大江’,‘总管’,107,‘2012-5-8’,‘20000’,‘1200’,6)
– 添加记录

update renwu set gongzi=12000 where paihang=108
– 更新某条记录

update renwu set
nianling =case paihang
when 101 then 34
when 102 then 65
when 103 then 45
when 104 then 78
when 105 then 35
when 106 then 67
end
where paihang in (101,102,103,104,105,106)
– 批量更新数据

update tab_route set cid=1 WHERE rid BETWEEN 110 and 130
– 更新某个范围内的数据

select * from renwu
– 查询表

alter table renwu add nianling int not null
– 添加字段

select bianhao 编号,mingcheng 名称,didian 地点 from bumen
– 查询语句，用中文显示

alter table bumen change didain didian varchar(50)
– 修改字段

select distinct bianhao from bumen
– 查询不重复字段，即字段重复的会删除

select xingming ‘姓名’,gongzi*12+ ifnull(buzhu,0)*12 ‘年工资’ from renwu
– ifnull(a,b) 判断是否为空，不为空则取值a.为空取值b

select xingming ‘姓名’,ruzhishijian '入职时间’from renwu where ruzhishijian>‘2012-5-7’
– 记住格式一定从表中取 不能少了 from 表

select * from renwu where xingming like ‘宋%’
select * from renwu where xingming like ‘_江%’
– 模糊查询， 查询姓吴的员工，查询第二个字为进的员工
– %表示后面还有字，不论几个字，
– _ 为只有一个字

select * from renwu where paihang in( 101,102)
– 批量查询，无规律

select * from renwu where shangji is null
– 查询一把手，（即shagnji为空） 用is

select * from renwu order by gongzi desc
– 排序 order by 是排序的意思， desc 降序排列；不加的desc,则为升序排列
– 如果有where，则先执行where条件句

select xingming 姓名 from renwu order by xingming
– 根据姓名排序

select xingming,bianhao,gongzi from renwu order by bianhao,gongzi desc
– a按部门编号升序，相同部门的按工资降序

select xingming,gongzi*12+ifnull(buzhu,0)*12 nianxin from renwu order by nianxin desc
– 用起临时名的方法算年收入并降序排列

select max(gongzi) from renwu
– 显示最高工资max()，最低工资min()

select sum(gongzi) 总工资,avg(gongzi)‘平均工资’ from renwu
– 显示总工资和平均工资

select xingming,gongzi from renwu where gongzi=(select max(gongzi) from renwu)
– 显示最高工资max()，最低工资min() 括号内为嵌套，选择最大值

select xingming,gongzi from renwu where gongzi > (select avg(gongzi) from renwu)
– 显示高于平均工资的员工的姓名和工资

select count(*) 总共 from renwu
– 统计有多少条记录

select bianhao,avg(gongzi) 平均工资,sum(gongzi) 总工资 from renwu group by bianhao
– 统计每个部门的平均工资和总工资 分类汇总 后面的字段也必须出现在起前面

select bianhao,zhiwu,avg(gongzi),min(gongzi) from renwu group by bianhao,zhiwu order by bianhao
– 显示每个部门每个职务的平均工资和最低工资 排序order 一定要放在所有条件之后

select bianhao,avg(gongzi) from renwu group by bianhao having avg(gongzi) < 15000
– 显示平均工资低于15000的部门编号和平均工资 二次选择，放在group之后

–笛卡尔集现象（出现的原因主外键没有对应）
select * from renwu,bumen
select * from renwu,bumen where mingcheng = ‘水军’ and renwu.bianhao = bumen.bianhao
– and后面的是 实现主外键对应

select * from renwu,bumen where renwu.bianhao = bumen.bianhao
– 显示多表全部内容

select xingming,mingcheng,renwu.bianhao from renwu,bumen where renwu.bianhao = bumen.bianhao
– 显示姓名和所在部门以及部门编号

select xingming,mingcheng,gongzi from renwu,bumen where (renwu.bianhao = bumen.bianhao) and (bumen.bianhao =4)
– 显示部门号为4的部门名称，职员姓名和工资 记得要加括号

select xingming,mingcheng,gongzi,bumen.bianhao from renwu,bumen where (renwu.bianhao = bumen.bianhao)
order by bumen.bianhao desc
– 显示员工名，部门名和工资，并按部门编号排序

–连接查询（复杂查询）
–（内连接和外连接）

select xingming from renwu where paihang = (select shangji from renwu where xingming = ‘吴用’)
– 显示上级吴用的上级 然后排行=谁 括号内先查询出上级是谁

select a.xingming 姓名,b.xingming 上级 from renwu a,renwu b where a.shangji = b.paihang
– 显示所有员工及其上级的姓名
– //将renwu表取别名为a，将renwu表取别名为b

–（左外连接和右外连接） – left join 意为左边表全部显示，右边表根据左边表来,有就显示，没有就为null
select a.xingming 姓名,b.xingming 上级 from renwu a left join renwu b on a.shangji = b.paihang

–子查询（嵌套查询）单行子查询：返回结果为单行的子查询称为单行子查询

select xingming from renwu where bianhao = (select bianhao from renwu where xingming = ‘吴用’)
– 显示与吴用同部门的员工

–多行子查询：返回结果为多行的子查询称为多行子查询
多行子查询如果不能一次写对，就两行写，先写子查询在写主查询

select xingming,gongzi,bianhao from renwu where zhiwu in
(select distinct zhiwu from renwu where bianhao = 4) and (bianhao = 6)
– 返回个3号部门相同职务姓名，工资和部门编号

																											-- 给平均工资起了个别名																--临时表名

select xingming,gongzi,pjgz,renwu.bianhao from renwu, (select avg(gongzi) pjgz,bianhao
from renwu group by bianhao) lsb
where (renwu.bianhao = lsb.bianhao ) and (renwu.gongzi > lsb.pjgz)
– 显示高于部门平均工资的员工姓名和工资，部门编号以及部门的平均工资，

–分页查询

select xingming,gongzi from renwu order by gongzi desc limit 5
– 显示工资第1个到第5高的员工 – limit 5 实际为limit 0,5 从开始往后数5位

select xingming,gongzi from renwu order by gongzi desc limit 4,3
– 显示工资第4个到第6个的员工
–limit 4,3，4意为从第4名开始，3意为往后数3名

–删除重复记录

select distinct * into lsb from renwu
delete from renwu
insert into renwu select * from lsb
drop from lsb
– 首先选择重复记录到临时表中，
– 删除原表剩余记录（重复的）
– 把临时表内的记录（仅存的重复记录）添加到原表中
– 删除临时表

–约束 保证数据满足应有的条件
约束分为 not null（非空） unique(唯一的) primary key foreign key check(自定义) default（默认）
例如
nianling int check(nianling >0 and nianling <120) default 18

–数据库的备份和还原
–分离附加
貌似没完成
create database sss
backup database sss to disk = ‘f:/sss.bak’

drop database sss
restore database sss from disk =‘地址’

–触发器语句
begin
if new.nianling <20 or new.nianling>30
then set new.nianling =18;
end if;
end