数据库开发笔记(1)
1、游标
declare nuclide_cour cursor
for select distinct value from v_ETFNuclideParaValue order by value //定义游标
declare @value varchar(50) //定义变量,用于保存游标中的值
open nuclide_cour //打开游标
fetch next from nuclide_cour //遍历游标
into @value // 将游标中的值放到变量中
while @@FETCH_STATUS=0 // 若等于0,则fetch语句成功
begin
//根据@value的值可以进行一些操作
for select distinct value from v_ETFNuclideParaValue order by value //定义游标
declare @value varchar(50) //定义变量,用于保存游标中的值
open nuclide_cour //打开游标
fetch next from nuclide_cour //遍历游标
into @value // 将游标中的值放到变量中
while @@FETCH_STATUS=0 // 若等于0,则fetch语句成功
begin
//根据@value的值可以进行一些操作
select*from v_ETFNuclideParaValue where value=@value
//读取下一个游标值
fetch next from nuclide_cour
into @value
end
close nuclide_cour
2行转列
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+' max(case value when '''+value+''' then dcfvalue else 0 end) ['+value+']'
from(select distinct value from [v_nuclideETF_DCF] ) as a
set @sql='select nuclidename,'+@sql+' from [v_nuclideETF_DCF] group by nuclidename'
exec(@sql)
select @sql=isnull(@sql+',','')+' max(case value when '''+value+''' then dcfvalue else 0 end) ['+value+']'
from(select distinct value from [v_nuclideETF_DCF] ) as a
set @sql='select nuclidename,'+@sql+' from [v_nuclideETF_DCF] group by nuclidename'
exec(@sql)
select nuclidename,chemicalformname,
sum(case when starttime=0 then amount else 0 end) as '0',
sum(case when starttime=7201 then amount else 0 end) as '7201',
sum(case when starttime=28801 then amount else 0 end) as '28801',
sum(case when starttime=86401 then amount else 0 end) as '86401',
sum(case when starttime=345601 then amount else 0 end) as '345601',gassrctermid
from V_GasIntervalContent where gassrctermid=54 group by nuclidename,chemicalformname,gassrctermid
3、根据旧表创建新表
SQL Server下:
错误:create table t_releaseConcentration_New as select id,appid,area,halflife,value,note from t_releaseConcentration
正确:select * into t_releaseConcentration_New from t_releaseConcentration
错误:create table t_releaseConcentration_New as select id,appid,area,halflife,value,note from t_releaseConcentration
正确:select * into t_releaseConcentration_New from t_releaseConcentration
4、给存储过程中的变量赋值
declare @isAcc bit
select @isAcc=IsAccST from gassrcterm where GasSrcTermID=2
print @isAcc
select @isAcc=IsAccST from gassrcterm where GasSrcTermID=2
print @isAcc
5、复制行
insert into tz (z_name) select y_name from ty where Y_id=120
6、添加主键约束、外键约束、级联删除更新
/*
用户——研究所——角色
*/
create table t_user_institute_role
(
userID int not null references t_user(id) on update cascade on delete cascade,
instituteEncode varchar(10) not null references t_institute(encodeid) on update cascade on delete cascade,
roleEncode varchar(10) not null references t_role(encodeid) on update cascade on delete cascade
)
alter table t_user add primary key(id)
用户——研究所——角色
*/
create table t_user_institute_role
(
userID int not null references t_user(id) on update cascade on delete cascade,
instituteEncode varchar(10) not null references t_institute(encodeid) on update cascade on delete cascade,
roleEncode varchar(10) not null references t_role(encodeid) on update cascade on delete cascade
)
alter table t_user add primary key(id)
7、添加外键级联约束
alter table t_oaSysModules add constraint fk_sys_modules foreign key(sysID) references t_oaSubSys(id) on update cascade on delete cascade
8、更改数据库名称
sp_renamedb 'snpi','rgy'
9、升序、降序
desc为降序
ASC为升序,默认就是ASC