sql 基本语句

--新建数据库
 create database shuguo
--建表的同时建字段
create table renwu
(
bianhao int,
xingming nvarchar(5),
xingbie nchar(1),
zhiwu nvarchar(5),
nianling int,
gongzi numeric(6,2),
)
--删除表
drop database shubuo
drop table renwu
--查询语句
select*from renwu
增加一份工资：
gongzi numeri(6,2)
--若查找bianhao,gongzi
select bianhao,gongzi from renwu
--小名或者别名
select bianhao 编号,gongzi 工资 from renwu
--添加记录
insert into renwu values(1,'刘备','男','主公',49,9999)
insert into renwu values(2,'甘夫人','女','夫人',45,9000)
insert into renwu values(3,'关羽','男','将军',45,8500)
insert into renwu values(4,'赵云','男','将军',30,8000)
--按条件删除
delete from renwu where bianhao=2
insert into renwu (bianhao,xingming,xingbie,zhiwu)values(2,'甘夫人','女','夫人')
--按条件查找
select bianhao,xingming,gongzi from renwu where gongzi>7000
delete from renwu where xingming='甘夫人'
--修改记录
--把工资低于八千的人加上2%
update renwu set gongzi=gongzi*1.02 where gongzi<9900
--数据类型
create table aaa
(shijian datetime)
insert into aaa values(getdate())
select*from aaa
insert into aaa values('2003-7-10')
--主键
create table gsgy
(
bianhao int primary key,
xingming nvarchar(10),
nianling int
)
 insert into gsgy values(1,'悟空',30)
 insert into gsgy values(2,'八戒',26)
 insert into gsgy (bianhao,xingming) values(3,'沙僧')
update gsgy set nianling=23 where nianling=26

create database shuihu
 
create table bumen
 (
  bianhao int primary key,
  mingcheng  nvarchar(5),
  didian nvarchar(5)
 )
  create table renwu
   (
  paihang int primary key,
  xingming nvarchar(5),
  zhiwu nvarchar(5),
  shangji int,
  ruzhishijian datetime,
  gongzi numeric(6,1),
  buzhu  numeric(5,1),
  bianhao int foreign key 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,'后勤','后山')
   select*from renwu
  
  insert into renwu(paihang,xingming,zhiwu,ruzhishijian,gongzi,buzhu,bianhao) values(101,'宋江','寨主','2003-03-15',20000,3000,1)
  insert into renwu values(102,'卢俊义','寨主',101,'2002-06-16',18000,3000,1)
  insert into renwu values(103,'吴用','军师',101,'2002-09-18',17000,2800,2)
  insert into renwu values(104,'公孙胜','军师',101,'2002-08-20',16000,2600,2)
  insert into renwu values(105,'关胜','大将',102,'2002-02-15',15000,2300,3)
  
  delete from renwu where bianhao=102
  --查找 关胜的工资，职务和所在部门
   select gongzi,zhiwu,bianhao from renwu where xingming='关胜'
   --查询一共有多少个部门
   select  distinct bianhao from renwu
   select distinct bianhao,xingming from renwu
   --显示每个员工的年薪并且把字段以中文方式显示
   select xingming'姓名',gongzi*12'年工资'from renwu
   --计算年总收入和空值做四则运算
    select xingming'姓名',gongzi*12+isnull(buzhu,0)*12'年总收入'from renwu 
--查找2004年1月1日以后入职的员工
  select xingming'姓名',ruzhishijian'入职时间'from renwu where ruzhishijian>'2002-7-1'
  --查找工资在13000到18000之间的员工
   select xingming'姓名',gongzi'年工资'from renwu where gongzi>13000 and gongzi<=18000
  --模糊查询
   --查询姓李的员工 ，查询第二个字是俊的员工
 select xingming from renwu where xingming like'李%'
 select xingming from renwu where xingming like'_俊%'
*******************
select xingming,paihang from renwu where paihang in(1,2,3,4)
--显示一把手的姓名
 select xingming,zhiwu from renwu where shangji is null
--排序，默认升序，desc降序，中文排序按照拼音顺序和音调
select xingming,gongzi from renwu order by gongzi desc
select xingming,gongzi from renwu order by gongzi desc
--按部门编号升序，按工资降序
select xingming,bianhao,gongzi from renwu order by bianhao,xingming desc
--用起临时名的方法算年总收入并降排列
 select xingming,gongzi*12+isnull(buzhu*12,0)'年薪' from renwu order by '年薪' desc
--显示最高工资和最低工资
select max(gongzi)from renwu
select min(gongzi)from renwu 
--显示总工资和平均工资 
 select sum(gongzi)'总工资',avg(gongzi)'平均工资'from renwu
select xingming,gongzi from renwu where gongzi=(select max(gongzi)from renwu)
显示高于平均工资 的员工的姓名和工资 
select xingming,gongzi from renwu where gongzi>(select avg(gongzi)from renwu)
select xingming,gongzi,(select avg(gongzi)from renwu)'平均工资' from renwu where gongzi>(select avg(gongzi)from renwu)
--统计有多少记录
 select count(*)from renwu
select count(*)from renwu where gongzi>1000
 
--统计每个部门的平均工资和总工资
select bianhao,avg(gongzi),sum(gongzi)from renwu group by bianhao
--显示每个部门每个职务的平均工资和最低工资
select bianhao,avg(gongzi),min(gongzi)zhiwu from renwu group by bianhao,zhiwu order by bianhao
--显示每个部门每个职务的平均工资 和最低 工资
 select bianhao avg(gongzi),min(gongzi)zhiwu from renwu group by bianhao,zhiwu order by bianhao 
--显示平均工资低于1500的部门编号和平均工资
select bianhao,avg(gongzi)from renwu group by bianhao having avg(gongzi)<15000
--笛卡尔集现象
select*from renwu,bumen
select*from renwu,bumen where bumen.mingcheng='水军'
--主-外键没有对应上，造成错误
 select*from renwu,bumen  where  renwu.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=1)
**********************
--显示员工名，部门名和工资 并按部门编号排序
select xingming,mingcheng,gongzi from renwu,bumen where renwu.bianhao=bumen.bianhao order by bumen.bianhao
--复杂查询
--显示李逵上级
select shangji from renwu where xingming='李逵'
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
--10.21显示李逵上级
select shangji from renwu where xingming='李逵'
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

--10.22复杂查询
--子查询（嵌套查询）单行子查询：返回结果为单行的子查询称为单行了查询
--显示与鲁智深 同部门的员工
 select xingming from renwu where bianhao=(select bianhao from renwu where xingming='关胜')
 --多行子查询：返回结果为多行的子查询称为多行子查询
  --返回和5号部门相同职务的员工姓名，工资 和部门编号多行子查询如果不能一次写对同，就两行写，先写子查询，再写主查询
  select xingming,gongzi,bianhao from renwu where zhiwu in
  (select distinct zhiwu from renwu where bianhao=5)and(bianhao!=5)
--10.23分页查找
 --显示高于部门平均工资的员工姓名和工资，部门编号以及部门的平均工资 ，首先得到每个部门的平均工资
 select avg(gongzi),bianhao from renwu group by bianhao select xingming,gongzi,pigz,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)
--查询第1个到第5个入职的员工
  select top 5 xingming,zhiwu,ruzhishijian from renwu order by ruzhishijian
  --显示第6到第13个入职的员工（选1-13，去1-5）
 select top 8 xingming,ruzhishijian from renwu where paihang not in(select top 5 paihang from renwu order by ruzhishijian)
order by ruzhishijian
 10.24
--内连接与外连接
select a.xingming '姓名',b.xingming'上级'from renwu a,renwu b where a.shangji=b.paihang
--左连接和右连接
select a.xingming'姓名',b.xingming'上级'from renwu a right join renwu b on a.shangji=b.paihang
-