sqlserver

drop table renwu 删除整个表


delete from renwu 删除记录表里的内容


primary key 主键不能为空也不能重复


select * from renwu  查询人物表里的所有属性


把工资低于8千的人加上2%
update renwu set gongzi=gongzi*1.02 where gongzi<8000


delete from gsyg where nianling=12 and (or) xingming='悟空'


鲁的工资，工作和所在部门
select gongzi,zhiwu,bianhao frome renwu where xingming='鲁'


查询一共有多少部门
select distinct(起去掉重复的作用) bianhao from renwu


备用名称和中文
select xingming 姓名,gongzi*12 '年工资'(引号加不加都行) from renwu 


null问题
select xingming‘姓名’,gongzi*12+isnull(buzhu,0)*12 '年总工资'from renwu 
select xingming from renwu where xingming like '李%'（或者像‘_俊%’）


where paihang in(101,105,108)
where shangji is null


默认升序，desc降序 select xingming from renwu order by gongzi (desc)工资高到低


按部门编号升序，工资降序
select xingming, bianhao,gongzi frome renwu order by bianhao,gongzi desc


通过起别名来把很长的给缩省下来
lect xingming‘姓名’,gongzi*12+isnull(buzhu,0)*12 nianxin(这就是别名) '年总工资'from renwu order by nianxin desc


显示最高和最低
select max(gongzi) from renwu
select min(gongzi) from renwu


嵌套查询  因为前面有姓名等信息要显示，所以并不知道最大工资的名字是谁的
select xingming,gongzi from renwu where gongzi=(select max(gongzi)from renwu)
select xingming,gongzi,(select avg(gongzi)from renwu) from renwu where gongzi>(select avg(gongzi) from renwu )


统计记录：select count(*)from renwu where gongzi>10000


统计每个部门的平均工资和总工资  注意：group by 后面的字段必须出现在显示字段中
 select bianhao,avg(gongzi),sun(gongzi)from renwu group by(分类汇总的意思) bianhao


显示每个部门每个职务的平均工资和最低工资   一般都有是先删选再排序
select bianhao,avg(gongzi),min(gongzi),zhiwu from renwu group by bianhao,zhiwu order by bianhao


显示平均工资低于15000的部门编号和平均工资
select bianhao,avg(gongzi) from renwu group by bianhao having avg(gongzi)<1500


两个表连起来查 select * from renwu,bumen and where renwu.bianhao=bumen.bianhao
select * from renwu,bumen where bumen.mingcheng='水军' and renw.bianhao=bumen.bianhao


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


显示部门号为4的部门名称，职员姓名和工资
select xingming,mingcheng,gongzi from renwu,bumen where (renwu.bianhao=bumen.bianhao) and(bumen.bianhao=4)
建副表查询  显示高于每个部门平均工资的员工姓名和工资，部门编号以及部门的平均工资
首先得得到每个部门的平均工资：saaaaelect avg(gongzi),bianhao from renwu group by bianhao（把这当副表）


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 top 5 xingming,zhiwu,ruzhishijian from renwu order by ruzhishijan
显6到13中的人
select top 8 xingming,ruzhishijian from renwu where paihang not in (select top 5 paihang from renwu order by ruzhishijian) order by ruzhishijian


约束
unique 不能重复但可以有一个null
主键不能重复也不能为空且唯一
自定义约束：int check(nianling>=20 and nianling<=30)