美文网首页
09-14:游标2.0

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脚本注意事项：
  1. 替换mysql的；为sqlserver pgop(word中替换)
  2. 去掉mysql的`
  3. 去掉表后面关于字符集的设置
  4. 去掉各种主外键unique的设置，修改为在对应字段后添加关键字
    如mysql中primary key(tno)找到对应位置修改 tno char(8) primary key
  5. 修改int(11) 为int
  6. 去掉没有字符类型字段后面的字符集设置
  7. 修改自增长 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

  • 操作步骤
  1. 保证游标基本结构正确，手动fetch next print能够正常执行
  2. 添加循环（添加变量控制循环）使游标正常工作
  3. 业务逻辑
  • 找出最早达到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

    MySql中的空值替换 select name,ifnull(bedno,'走读') from student; ...

  • 喜欢他的时候

    惑星yooooo 09-14 22:48 仅自己可见 喜欢的...喜欢的样子... 09-14 23:38 ...

  • MongoDB之文档游标

    游标 查询操作返回的结果游标 游标的迭代与操作 db.collecton.find() 返回一个文档集合游标,在不...

  • sqlserver 游标

    游标更新删除当前数据 1.声明游标 2.打开游标 3.声明游标提取数据所要存放的变量 4.定位游标到哪一行 5.关...

  • Oracle 存储过程学习笔记

    1、存储过程简单实例 2.游标实现方式 显式游标实现方式（可多值） 隐式游标（可以实现查询多值） 带有参数的游标（...

  • sql server 游标

    定位到结果集中某一行 游标分类 静态游标（Static）：在操作游标的时候，数据发生变化，游标中数据不变。 动态游...

  • 实验九 游标操作和自定义函数

    一．实验目的： 掌握游标的声明、游标打开、标数据的提取、游标的关闭和游标的释放 掌握标量值函数的定义与调用 掌握内...

  • sql 游标，函数，触发器

    游标是数据库的一种机制或类型，可以存储结果集 ，迭代和遍历结果集，oracle中游标大致分为显示游标和隐式游标。 ...

  • GraphQL（七）：GraphQL分页及原理分析

    基于GraphQl-JAVA 11.0 GraphQL的分页是基于游标的，游标分页的方式可以提升用户体验，关于游标...

  • SQL-游标循环记录

    declare @temp_temp int --创建游标 --Local(本地游标) DECLARE a...

网友评论

      本文标题：09-14:游标2.0

      本文链接：https://www.haomeiwen.com/subject/fsmogftx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|09-14:游标2.0|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！