09-14:游标2.0

作者: 神坛下的我 | 来源:发表于2018-09-14 15:30 被阅读0次

MySql中的空值替换 select name,ifnull(bedno,'走读') from student;
SQLServer中的空值替换 select name,isnull(bedno,'走读') from student;
数据库建表与约束
mysql脚本改写为sqlserver脚本注意事项：

替换mysql的；为sqlserver ^pgop(word中替换)
去掉mysql的`
去掉表后面关于字符集的设置
去掉各种主外键unique的设置，修改为在对应字段后添加关键字
如mysql中primary key(tno)找到对应位置修改 tno char(8) primary key
修改int(11) 为int
去掉没有字符类型字段后面的字符集设置
修改自增长 auto_increment 修改为 identity primary key 同时插入语句到
word中控制换行 ),( 替换为 ),^p( 换行后去掉第一个字段

CREATE DATABASE gxa
go
USE gxa
go





CREATE TABLE admin (
  adno char(4)  NOT NULL,
  adpwd varchar(10)  DEFAULT NULL,
  adname varchar(20)  DEFAULT NULL,
  adstate int DEFAULT NULL,
  PRIMARY KEY (adno)
)
go

insert  into admin(adno,adpwd,adname,adstate) values ('1','123','liguang',1),('2','123','admin',1),('3','gxa','gxa',2);


go

CREATE TABLE teacher (
  tno char(4) NOT NULL,
  tname varchar(10) NOT NULL,
  sex char(4) DEFAULT NULL,
  birthday datetime DEFAULT NULL,
  jointime datetime DEFAULT NULL,
  area varchar(10) NOT NULL,
  imgsrc varchar(50) DEFAULT NULL,
  pwd varchar(10) DEFAULT NULL,
  tdesc varchar(255) DEFAULT NULL,
  PRIMARY KEY (tno)
)
go

insert  into teacher(tno,tname,sex,birthday,jointime,area,imgsrc,pwd,tdesc) values ('0001','赵世晨1','男','1977-02-01 00:00:00','2006-05-01 00:00:00','南充',NULL,NULL,'硕士研究生学历'),('0002','陈涛','男','1980-12-03 00:00:00','2006-04-03 00:00:00','宜宾',NULL,NULL,'教研室主任'),('0003','李广','男','1983-10-01 00:00:00','2006-08-01 00:00:00','成都',NULL,NULL,'优秀青年教师'),('0004','曾海阁','男','1974-03-01 00:00:00','2007-02-01 00:00:00','成都',NULL,NULL,'优秀青年教师'),('0005','张兰','女','1985-03-01 00:00:00','2007-05-01 00:00:00','成都',NULL,NULL,'优秀青年教师'),('0006','夏宁静','女','1984-07-01 00:00:00','2009-05-01 00:00:00','成都',NULL,NULL,'深受学生喜爱'),('0007','焦成','男','1984-02-01 00:00:00','2008-05-01 00:00:00','贵阳',NULL,NULL,'技术专家'),('0008','王静','女','1984-02-01 00:00:00','2008-09-02 00:00:00','成都',NULL,NULL,'深受学生喜爱');


go

CREATE TABLE class (
  cno char(6) primary key,
  cname varchar(10) DEFAULT NULL,
  descript varchar(20) DEFAULT NULL,
  tno char(4) DEFAULT NULL
  CONSTRAINT fk_class_teacher FOREIGN KEY (tno) REFERENCES teacher (tno)
) 
go

insert  into class(cno,cname,descript,tno) values ('090101','09软班','09级软件班','0001'),('090102','09软班','09级软件班','0002'),('090201','09商班','09级电商班','0003'),('090301','09网班','09级网络班','0004'),('100101','10软班','10级软件班','0005'),('100201','10商班','10级电商班','0006'),('100301','10艺班','10级电艺班','0007');

go

CREATE TABLE comment (
  cmid int NOT NULL,
  cmname varchar(20)  DEFAULT NULL,
  cmlevel int DEFAULT NULL,
  cmpid int DEFAULT NULL,
  cmval int DEFAULT NULL,
  PRIMARY KEY (cmid)
)
go
insert  into comment(cmid,cmname,cmlevel,cmpid,cmval) values (1,'性别',1,0,NULL),(2,'男',2,1,NULL),(3,'女',2,1,NULL),(10,'审核状态',1,0,NULL),(11,'新申请',2,10,0),(12,'通过',2,10,1),(13,'锁定',2,10,2),(100,'行政区',1,0,NULL),(101,'四川省',2,100,NULL),(102,'成都市',3,101,NULL),(103,'湖南省',2,100,NULL);
go



CREATE TABLE course (
  cno char(4) primary key,
  cname varchar(10) DEFAULT NULL,
  grade int DEFAULT NULL,
  cdesc varchar(200) DEFAULT NULL
)
go
insert  into course(cno,cname,grade,cdesc) values ('0101','数据库原理',1,'数据库类课程'),('0102','SQLSERVER',2,'数据库类课程，中小型数据库'),('0103','MYSQL',3,'数据库类课程，中小型数据库'),('0104','ORACLE',4,'数据库类课程，大型数据库'),('0201','C语言',1,'语言类课程，面向过程'),('0202','C++',2,'语言类课程，面向对象'),('0203','JAVA',3,'语言类课程，面向对象'),('0301','电商概论',1,'管理类课程'),('0302','消费心理学',2,'管理类课程'),('0303','SEO',3,'管理类课程');
go

CREATE TABLE student (
  sno char(8) primary key,
  name varchar(20) DEFAULT NULL,
  sex char(4) DEFAULT NULL,
  high int DEFAULT NULL,
  birthday datetime DEFAULT NULL,
  jointime datetime DEFAULT NULL,
  homephone char(12) DEFAULT NULL,
  bedno char(6)unique DEFAULT NULL,
  address varchar(50) DEFAULT NULL,
  avgscore float DEFAULT NULL,
  area varchar(10) DEFAULT NULL,
  cno char(6) DEFAULT NULL,
  imgsrc varchar(50) DEFAULT NULL,
  pwd varchar(10) DEFAULT NULL
  CONSTRAINT fk_student_class FOREIGN KEY (cno) REFERENCES class (cno) ON UPDATE CASCADE
) 
go

insert  into student(sno,name,sex,high,birthday,jointime,homephone,bedno,address,avgscore,area,cno,imgsrc,pwd) values ('09010101','张三','女',172,'1989-03-01 00:00:00','2009-09-01 00:00:00','13931111112','010101','嘉陵区',73.3,'南充','090101','','123'),('09010102','李斯','男',168,'1991-02-02 00:00:00','2009-09-01 00:00:00','13817171112','010102','翠屏区',76.1,'宜宾','090101',NULL,'124'),('09010201','王二','男',175,'1989-03-06 00:00:00','2009-09-01 00:00:00','13721712712','010103','自流井区',52.6,'自贡','090102',NULL,NULL),('09020101','赵武','男',185,'1992-05-01 00:00:00','2009-09-01 00:00:00','13611251172','010104','顺庆区',75.3,'南充','090201',NULL,NULL),('09020102','刘三','女',154,'1989-06-22 00:00:00','2009-09-01 00:00:00','13141521612','020101','金牛区',72.5,'成都','090201',NULL,NULL),('09020103','陈启','男',180,'1993-07-13 00:00:00','2009-09-01 00:00:00','13513119112','010201','兴文县',65.5,'宜宾','090201',NULL,NULL),('09030101','何五','男',164,'1991-02-17 00:00:00','2009-09-01 00:00:00','13251151112','010202','金牛区',83.2,'成都','090301',NULL,NULL),('09030102','郑留','女',158,'1992-06-23 00:00:00','2009-09-01 00:00:00','13914153112','020102','金泉路',90.3,'成都','090301',NULL,NULL),('10010101','田霸','女',165,'1989-07-21 00:00:00','2010-09-01 00:00:00','13113152212','020103','自流井区',70.2,'自贡','100101',NULL,NULL),('10010102','林事尔','女',170,'1989-09-05 00:00:00','2010-09-01 00:00:00','13971612312','020104','嘉陵区',72.5,'南充','100101',NULL,NULL),('10020101','陈宇','男',166,'1993-05-03 00:00:00','2010-09-01 00:00:00','18616131712','010301','武侯区',77.5,'成都','100201',NULL,NULL),('10020102','何其','男',170,'1992-06-07 00:00:00','2010-09-01 00:00:00','13912413512','010302','双流县',74.3,'成都','100201',NULL,NULL),('10030101','李林','男',175,'1994-10-08 00:00:00','2010-09-01 00:00:00','18623121212','010401','嘉陵区',72.4,'南充','100301',NULL,NULL),('10030102','陈玉','女',157,'1990-12-09 00:00:00','2010-09-01 00:00:00','13314161412',NULL,'武侯区',81.6,'成都','100301',NULL,NULL);


go

CREATE TABLE sc (
  scno int identity primary key,
  sno char(8) NOT NULL DEFAULT '',
  cno char(4) NOT NULL DEFAULT '',
  tno char(4) NOT NULL DEFAULT '',
  score int DEFAULT NULL,
  examtime datetime DEFAULT NULL,
  exampoint varchar(20) DEFAULT NULL,
  addtime datetime DEFAULT NULL,
  scstate int DEFAULT NULL,
  adno char(4) DEFAULT NULL
  CONSTRAINT fk_sc_course FOREIGN KEY (cno) REFERENCES course (cno),
  CONSTRAINT fk_sc_student FOREIGN KEY (sno) REFERENCES student (sno),
  CONSTRAINT fk_sc_teacher FOREIGN KEY (tno) REFERENCES teacher (tno)
) 

go

insert  into sc(sno,cno,tno,score,examtime,exampoint,addtime,scstate,adno) values ('09010101','0101','0001',77,'2014-05-18 16:30:00','3教','2014-05-12 16:30:00',1,'0001'),('09010101','0102','0001',66,'2014-05-18 16:30:00','2教','2014-05-12 16:30:00',1,'0001'),('09010101','0103','0001',64,'2014-05-20 16:30:00','3教','2014-05-12 16:30:00',1,'0002'),('09010101','0201','0003',78,'2014-05-20 17:30:00','3教','2014-05-12 16:30:00',1,'0003'),('09010101','0202','0002',92,'2014-06-18 17:30:00','4教','2014-05-12 16:30:00',1,'0001'),('09010102','0101','0001',85,'2014-05-18 16:30:00','3教','2014-05-12 16:30:00',1,'0001'),('09010102','0102','0001',54,'2014-05-19 16:30:00','2教','2014-05-12 16:30:00',1,'0002'),('09010102','0103','0001',52,'2014-05-20 17:30:00','3教','2014-05-12 16:30:00',1,'0003'),('09010102','0201','0002',77,'2014-06-18 17:30:00','3教','2014-05-12 16:30:00',1,'0001'),('09010102','0202','0003',79,'2014-05-18 16:30:00','4教','2014-05-12 16:30:00',1,'0002'),('09010201','0101','0001',90,'2014-05-18 16:30:00','3教','2014-05-12 16:30:00',1,'0002'),('09010201','0102','0001',91,'2014-05-18 16:30:00','2教','2014-05-12 16:30:00',0,NULL),('09010201','0103','0001',88,'2014-05-20 16:30:00','3教','2014-05-12 16:30:00',0,NULL),('09010201','0104','0001',82,'2014-11-18 17:30:00','3教','2014-05-12 16:30:00',0,NULL),('09010201','0201','0002',86,'2014-05-18 16:30:00','3教','2014-05-12 16:30:00',0,'0002'),('09010201','0202','0003',85,'2014-11-18 17:30:00','4教','2014-05-12 16:30:00',0,NULL),('09020101','0301','0006',84,'2014-05-18 17:30:00','2教','2014-05-12 16:30:00',0,'0002'),('09020101','0302','0006',67,'2014-06-18 16:30:00','2教','2014-05-12 16:30:00',0,NULL),('09020101','0303','0006',75,'2014-11-18 17:30:00','3教','2014-05-12 16:30:00',0,NULL),('09020102','0301','0006',76,'2014-11-18 17:30:00','2教','2014-05-12 16:30:00',0,NULL),('09020102','0302','0006',77,'2015-11-18 17:30:00','2教','2014-05-12 16:30:00',0,'0003'),('09020102','0303','0006',74,'2016-07-18 17:30:00','3教','2014-05-12 16:30:00',0,NULL);



go


CREATE TABLE studentreg (
  srid int identity primary key,
  regno int NOT NULL,
  regday datetime DEFAULT NULL,
  sno char(8) NOT NULL DEFAULT '',
  CONSTRAINT fk_studentreg_student FOREIGN KEY (sno) REFERENCES student (sno)
)
go

insert  into studentreg(regno,regday,sno) values (9010101,'2009-09-01 00:00:00','09010101'),(9010101,'2010-03-01 00:00:00','09010101'),(9010101,'2010-09-01 00:00:00','09010101'),(9010102,'2009-09-01 00:00:00','09010102'),(9010102,'2010-03-01 00:00:00','09010102'),(9020101,'2009-09-01 00:00:00','09020101'),(9020101,'2010-03-01 00:00:00','09020101');

游标2.0

操作步骤

保证游标基本结构正确，手动fetch next print能够正常执行
添加循环（添加变量控制循环）使游标正常工作
业务逻辑

找出最早达到2个学生考试得分80的管理员的信息，并且显示达到的时间

declare cur_getEarlyAdmin cursor for 
 select adno from sc 
 where score >= 80 and adno is not null 
 group by adno 
 having count(score) >=2 
open cur_getEarlyAdmin
 declare @adno char(4),
     @count int=0,
     @countadminno int,
     @arrive datetime
 select @countadminno = count(*) from (
  select adno from sc 
  where score >= 80 and adno is not null 
  group by adno) t 
  -- 临时表存放每个管理员第二个达到80分的时间
  create table tempadnoandexamtime(adno varchar(20),arrivetime datetime
  )
  
 -- @count < @countadminno表示循环次数小于符合条件的管理员的数量
 while(@@fetch_status = 0 or @ count = 0 and @count < @countadminno)
 begin
  fetch next form cur_getEarlyAdmin into @adno
  
  select top 1 @arrivetime = examtime from sc 
   where score >=80 and adno = @adno 
   order by examtime desc
  set @count = @count + 1 
  insert into tempadnoandexamtime values(@adno,@arrivetime)
 end 
 select * from tempadnoandexamtime order by arrivetime
 drop table tempadnoandexamtime
close cur_getEarlyAdmin
deallocate cur_getEarlyAdmin

上题加强版（双重游标）

declare  cur_getEarlyAdmin cursor for
 SELECT adno FROM sc
 WHERE score >= 80 AND adno IS NOT NULL
 GROUP BY adno
 HAVING COUNT(score) >= 2
open cur_getEarlyAdmin
declare @adno char(4),@count int = 0,
        @countadminno int,@arrivetime datetime
select  @countadminno = count(*) from 
 (SELECT adno FROM sc
 WHERE score >= 80 AND adno IS NOT NULL
 GROUP BY adno) t
 -- 临时表存放 每个管理员第2个达到80分的时间
 create table tempadnoandexamtime(
  adno varchar(20),
  examtime datetime,
  sno char(8),
  name varchar(20),
  score float,
  odrcol int,
  ordgroup int
 )
 
-- @count < @countadminno表示循环次数小于复合条件的管理员的数量
while((@@FETCH_STATUS = 0 or @count = 0) and  @count < @countadminno)
begin
 fetch next from cur_getEarlyAdmin into @adno
 -- print @adno
 
 declare  cur_getSc  cursor for
  select top 2 examtime,score,s.sno,s.name from sc  join student s
       on s.sno = sc.sno
  where score >=80 and adno = @adno
  order by examtime desc
  open cur_getSc
    declare @subcount int = 0,@sno char(8),@name varchar(20),@examtime datetime,@score float   
    while((@@FETCH_STATUS = 0 or @subcount = 0) and  @subcount < 2)
    begin
      fetch next from cur_getSc into @examtime,@score,@sno,@name
      insert into tempadnoandexamtime values(@adno,@examtime,@sno,@name,@score,0,@count)
     if @subcount = 0 -- 表示如果是当前内层游标第一行取得的时间就是该管理员最终达到的时间
      begin
        set @arrivetime = @examtime
      end
      set @subcount = @subcount + 1
    end
 close cur_getSc
 deallocate cur_getSc
 insert into tempadnoandexamtime(adno,examtime,odrcol,ordgroup) values(@adno,@arrivetime,1,@count)
 set @count = @count + 1

end
select adno,examtime,isnull(sno,''),ISNULL(score,''),ordgroup from tempadnoandexamtime
order by ordgroup desc,adno,odrcol desc,examtime
-- 四个排序字段的作用 ordergroup 让先达到的组在最前，adno让本组数据在一起
-- odrcol 让标题在最前，examtime让本组学生按时间排
drop table tempadnoandexamtime
close cur_getEarlyAdmin
deallocate cur_getEarlyAdmin

求出 0001 管理员第二个达到80分的学生参加考试的时间

select top 1 examtime,score from sc 
where score >=80 and adno = '0001' 
order by examtime desc

select examtime,score,s.sno,s.name from sc 
join student s 
on s.sno = sc.sno
where score >=80 and adno = '0001' 
order by examtime desc

显示各个管理员前两个学生达到80分以上的情况

09-14:游标2.0

游标2.0

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读