数据库里面的一些增删改查操作代码

--更新OLDdetail各项统计信息为NULL的数据
----update OLDdetail set XXSJ=0 where  XXSJ is null
----update OLDdetail set XXSJ=XXSJ*60 where  XXSJ<>0  --将学习时间按秒计算
----update OLDdetail set DYTWS=0 where DYTWS is null
----update OLDdetail set DYHDS=0 where  DYHDS is null
----update OLDdetail set TZS=0 where  TZS is null
----update OLDdetail set ZYS=0 where  ZYS is null
----update OLDdetail set ZYPYS=0 where ZYPYS is null
----update OLDdetail set XDS=0 where  XDS is null
----update OLDdetail set ziyuans=0 where  ziyuans is null
----update OLDdetail set LSDYS=0 where  LSDYS is null
----update OLDdetail set LSXDS=0 where  LSXDS is null
----update OLDdetail set LSFQSLS=0 where  LSFQSLS is null
----update OLDdetail set LSBZZYS=0 where  LSBZZYS is null
----update OLDdetail set LSPYZYS=0 where  LSPYZYS is null
----update OLDdetail set LSFBGGS=0 where   LSFBGGS is null
----update OLDdetail set LSFBJBS=0 where   LSFBJBS is null

update OLDdetail set BJ='宜章小学教科3班' where BJ='宜章小学科教3班' and Class_id=''

--根据用户身份证号码补齐用户的USERID
--查询数据
--select a.*,b.ID_CARD_NUM,B.USER_ID,B.USER_NAME,NAME from OLDdetail as a inner join pub.ce_pub.dbo.t_pub_user as b
--on a.IDCardNumber=b.id_card_num
--where UserId=0 and IDCardNumber is not null    --1039

--更新数据（pub.ce_pub.dbo.t_pub_user。。。就是。。pub.被侉的那个数据库名.dbo.被侉数据库里面要使用到的表名）
--update OLDdetail set UserId=B.USER_ID from  OLDdetail as a,pub.ce_pub.dbo.t_pub_user as b
--where a.IDCardNumber=b.id_card_num and UserId=0

 

--查询根据班级名称查询班级ID
--select a.UserId,a.IDCardNumber,a.BJ,b.CLASS_ID,B.CLASS_NAME from OLDdetail as a
--inner join pub.ce_pub.dbo.t_pub_class as b
--on a.BJ=b.CLASS_NAME
--where UserId<>0 and a.Class_id=''  --904

--更新OLDdetail表中班级ID
--UPDATE OLDdetail SET Class_id=b.CLASS_ID  from  OLDdetail as a,pub.ce_pub.dbo.t_pub_class as b
--where a.BJ=b.CLASS_NAME and UserId<>0 and a.Class_id=''

----(模糊查询)查询根据班级名称查询班级ID
--select a.UserId,a.IDCardNumber,a.BJ,b.CLASS_ID,B.CLASS_NAME from OLDdetail as a
--inner join pub.ce_pub.dbo.t_pub_class as b
--on a.BJ like '%'+b.CLASS_NAME+'%'
--where UserId<>0 and a.Class_id='' 

----（模糊更新）更新OLDdetail表中班级ID
--UPDATE OLDdetail SET Class_id=b.CLASS_ID  from  OLDdetail as a,pub.ce_pub.dbo.t_pub_class as b
--where a.BJ like '%'+b.CLASS_NAME+'%' and UserId<>0 and a.Class_id=''

 

--更新某人的学习信息
--update OLDdetail set DYTWS=2,DYHDS=1,TZS=2,ZYS=5,ZYPYS=4,XDS=3,ziyuans=2
--where  IDCardNumber='430111197003130414'

--插入一条新数据
insert into OLDdetail values(123935,'430426197811225121','新课标全员','祁东新课标小数1班','4300000000517',1792*60,3,33,2,3,3,1,1,0,0,0,0,0,0,0)

--根据用户身份证号码查找用户ID
select USER_ID from pub.ce_pub.dbo.t_pub_user where id_card_num='432801197706252028'
--根据班级名称查找班级ID
select CLASS_ID from pub.ce_pub.dbo.t_pub_CLASS WHERE CLASS_NAME='宜章师德小学3班'

 

--执行存储过程更新数据
exec HZX_ClassUser_LearnStatistic   --统计平台中所有用户的学情信息

exec HZX_Detail_STATISTIC  --将统计信息

 

跨库查询

SELECT * from t_learning_project  as a
inner join pub.ce_pub.dbo.t_pub_org as b
on a.ORG_ID=b.OrgID

select * from t_learning_project  as a,pub.ce_pub.dbo.t_pub_org as b
where a.ORG_ID=b.OrgID