09-14:游标2.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分以上的情况

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 202,607评论 5赞 476
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,047评论 2赞 379
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 149,496评论 0赞 335
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,405评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,400评论 5赞 364
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,479评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,883评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,535评论 0赞 256
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,743评论 1赞 295
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,544评论 2赞 319
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,612评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,309评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,881评论 3赞 306
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,891评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,136评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,783评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,316评论 2赞 342

09-14:游标2.0

游标2.0

推荐阅读更多精彩内容