数据库的相关sql代码

成绩表

/*
创建一个成绩表sscore，字段有：学习（sno），专业（spf），姓名（snm），出生年月（sbt）
                              学期（stm），数学（smt），英语（sen），数据库（sdb）
*/
create table sscore
(
sno varchar(13),  --学号
spf varchar(20),   --专业
snm varchar(20),   --姓名
sbt date,         --出生年月
stm int,          --学期
smt int,          --数学
sen int,          --英语
sdb int           --数据库
)
--增加c语言(scc)，总分字段(stl)，删除出生年与字段
alter table sscore add scc int
alter table sscore add stl int
alter table sscore drop column sbt

/*
	写一个代码插入10000个记录，要求学号值用AB00001-AB10000
	姓名值用nm+(A、B、C、D、E)随机中的一个字母
	专业值取'信息管理'
	学期取1-7中任意一个数字
	数学(),英语，数据库，C语言，为任意地50-99的值，总分为上述思想成绩的和
*/
declare @ssno varchar(13),@sspf varchar(20),@ssnm varchar(20),@sstm int,@ssmt int,@ssen int,@ssdb int,@sscc int,@sstl int  --定义变量
declare @i int
set @i=1
while @i<=10000
begin
set @ssno='AB'+right('0000'+ltrim(str(@i,5)),5)
set @sspf='信息管理'
set @ssnm='nm'+char(rand()*5+65)  
set @sstm=rand()*7+1 
set @ssmt=rand()*50+50  --产生一个50-99的成绩 
set @ssen=rand()*50+50 
set @ssdb=rand()*50+50 
set @sscc=rand()*50+50 
set @sstl=@ssmt+@ssen+@ssdb+@sscc 
insert into sscore (sno,spf,snm,stm,smt,sen,sdb,scc,stl) values (@ssno,@sspf,@ssnm,@sstm,@ssmt,@ssen,@ssdb,@sscc,@sstl)
set @i=@i+1
end
--update sscore set stl=smt+sen+sdb+scc 也可以进行分数的加和

select dense_rank() over (order by stl desc) as smc,* from sscore   --按照降序排名，排名连续
--select rank() over (order by stl desc) as smc,* from sscore   按照降序排名，排名跳跃

--将学号、姓名、总分字段建立视图，用视图建立游标，通过游标将姓名、总分依次用print表示
create view view_s as select sno,snm,stl from sscore  --建立视图
declare cursor_s cursor for select snm,stl from sscore --建立游标
declare @nm varchar(20),@tl int,@i int 
set @i=1
open cursor_s   --打开游标
while @i<=10000
begin
fetch from cursor_s into @nm,@tl  --使用游标
print @nm+' '+str(@tl)
set @i=@i+1
end
close cursor_s   --关闭游标
deallocate cursor_s   --释放游标

/*
查询所有不及格的学生信息并导入到s_no_pass表中。并分科目导入到s_smt数学，s_sen英语
s_sdb数据库，s_sccC语言
*/
select * into s_no_pass from sscore where smt<60 or sen<60 or sdb<60 or scc<60
--select * from s_no_pass
select sno,spf,snm,stm,smt into s_smt from s_no_pass where smt<60
--select * from s_smt
select sno,spf,snm,stm,sen into s_sen from s_no_pass where sen<60
select sno,spf,snm,stm,sdb into s_sdb from s_no_pass where sdb<60
select sno,spf,snm,stm,scc into s_scc from s_no_pass where scc<60
--select * from sscore

工资表

/*
创建一个工资表stuff_salary，字段有：工号（stuff_no），部门（stuff_pt），姓名（stuff_nm）
    基本工资（stuff_py），岗位工资（stuff_gp），绩效工资（stuff_jp），其他工资（stuff_op）
	应发工资（stuff_yf），其他扣（stuff_kq），实发工资（stuff_sf）
*/
create table stuff_salary
(
stuff_no varchar(20),
stuff_pt varchar(20),
stuff_nm varchar(20),
stuff_py int,
stuff_gp int,
stuff_jp int,
stuff_op int,
stuff_yf int,
stuff_kq int,
stuff_sf int
)
--增加扣水电费（stuff_ks）
alter table stuff_salary add stuff_ks int
/*
写一个代码插入1000个模拟记录，工号用AB00001-AB10000，部门用随机“部门A-部门E”表示
姓名用nm+（A、B、C、D、E）随机中的一个字母，基本工资随机值在2000-3000之间
岗位工资随机值在3000-4000，绩效工资随机值在1500-2500，其他工资用1800
其他扣用200表示，扣水电费随机值在0-1000
*/
declare @no varchar(20),@pt varchar(20),@nm varchar(20),@py int,@gp int
declare @jp int,@op int,@yf int,@kq int,@sf int,@ks int
declare @i int
set @i=1
while @i<=1000
begin
set @no='AB'+right('0000'+ltrim(str(@i,5)),5)
set @pt='部门'+char(rand()*5+65)
set @nm='nm'+char(rand()*5+65)
set @py=rand()*1001+2000
set @gp=rand()*1001+3000
set @jp=rand()*1001+1500
set @op=1800
set @kq=200
set @ks=rand()*1000+1
set @yf=@py+@gp+@jp+@op
set @sf=@yf-@kq-@ks
insert into stuff_salary (stuff_no,stuff_pt,stuff_nm,stuff_py,stuff_gp,stuff_jp,stuff_op,stuff_yf,stuff_kq,stuff_sf,stuff_ks) values (@no,@pt,@nm,@py,@gp,@jp,@op,@yf,@kq,@sf,@ks)
set @i=@i+1
end
--update stuff_salary set stuff_yf=stuff_py+stuff_gp+stuff_jp+stuff_op
--update stuff_salary set stuff_sf=stuff_yf-stuff_kq-stuff_ks

create view view_ss as select stuff_no,stuff_pt,stuff_nm from stuff_salary
declare cursor_ss cursor for select stuff_pt,stuff_nm from view_ss
declare @pt varchar(20),@nm varchar(20),@i int
open cursor_ss
set @i=1
while @i<=1000
begin 
fetch from cursor_ss into @pt,@nm
print @pt+' '+@nm
set @i=@i+1
end
close cursor_ss
deallocate cursor_ss

--查询最高应发工资和最低应发工资姓名
select stuff_nm as 最高应发工资员工姓名 from stuff_salary where stuff_yf=(select max(stuff_yf) from stuff_salary)
select stuff_nm as 最低应发工资员工姓名 from stuff_salary where stuff_yf=(select min(stuff_yf) from stuff_salary)