SQLServer2000程序设计

《SQLServer2000程序设计》由会员分享，可在线阅读，更多相关《SQLServer2000程序设计（30页珍藏版）》请在金锄头文库上搜索。

1、SQL Server 2000程序设计主题：数据查询及分类汇总数据查询及分类汇总朱 士 明安徽电子信息职业技术学院1数据的查询简单查询命令 select * from 例：有一学生基本信息表，其结构如下： create table xs ( 学号 char(6) not null , 姓名 char(8) not null, 专业名 char(10) , 性别 bit not null , 出生时间 smalldatetime , 入学时间 smalldatetime not null default(getdate(), 总学分 decimal(4,1), 备注 text ) Select

2、* from xs2数据的简单查询2。选取前n个数据选取结果集中前n行数据 例：查看xs表中总分在50分以上的前5名学生情况 select top 5 * from xs where 总学分=50 一般若需排序，则命令: select top 5 * from xs where 总学分=50 order by总学分 desc 注：with ties选项的作用 33。Select 结果集中的列 可以是*, 也可以是以下的情况 可以是列名 例1：select 学号 as 学生学号,姓名,专业名 from xs 数据的简单查询 可以是别名 例2：查看学生的姓名,专业名,出生年份 select 姓名,

3、专业名,datename(year,出生时间)+年 as 出生年份 from xs 可以是表达式 例3：查看学生的姓名,性别,专业名。其中：值为0的为女，1代表男 select 学号,姓名, case 性别 when 1 then 男 when 0 then 女 end as 性别, 专业名 from xs此为简单的case语句4数据的简单查询 select 学号,姓名, case when 总学分=50 then 良好 when 总学分=45 then 中 when 总学分=40 then 合格 else 不合格 end as 成绩, 专业名 from xs例4：查看学生的姓名,成绩,专业名

4、。其中：成绩显示为：当总学分=50时显示为良好，45-49则显示为中，40-44显示为合格。此为布尔case语句5数据的简单查询4。Where条件 条件表达式可以是由比较运算符构成，还可以由and、or、not运算符构成，还可以是between、in、like和is符号。4.1 Between的使用: 查询2001年入学学生学号及姓名 select 学号,姓名 from xs where 入学时间 between 2001-1-1 and 2001-12-314.2 in的使用: 查询专业为“计算机”“电气工程”的学生学号及姓名 select 学号,姓名 from xs where 专业名 i

5、n(计算机,电气工程) 或： 查询专业不为“计算机”、“电气工程”的学生学号及姓名 select 学号,姓名 from xs where 专业名 not in(计算机,电气工程)6数据的简单查询4.3 like的使用 : 使用通配符在字符串内查找指定的模式例：查找所有学生学号最后一位为1或2的学生学号、姓名及专业名。 select 学号,姓名,专业名 from xs where 学号 like %1-2例：查找所有姓王的学生学号、姓名及专业名。 select 学号,姓名,专业名 from xs where 姓名 like 王%通配符例 子描 述 %able like a%任意多个字符_able

6、 like abl_单个字符 a like a-g匹配指定范围或集合中的任意单个字符 a like w-z匹配不属于指定范围或集合中的任意单个字符7数据的简单查询-排序4.4 order by的使用: 对结果集排序。 2。也可以使用表达式指定排序 select 学号,姓名,性别,专业名,总学分 from xs order by 专业名+姓名 select 学号,姓名,性别,专业名,总学分 from xs order by 专业名+ cast(总学分 as varchar(6) 1。 使用列名指定排序顺序 按总学分升序排列，当总学分相同时，按性别的值升序 排列： select 学号,姓名,性别,

7、专业名,总学分 from xs order by 总学分,性别 3。 使用别名指定排序顺序 4。 使用列名指定排序顺序 8数据的查询-分组与总汇1. 聚合函数的使用: 常用的聚合函数如下 聚合函数支持的数据类型描 述Sum()数字对指定列中的所有非空值求总和Avg()数字对指定列中的所有非空值求总和Min()数字、字符、datetime返回指定列中的相应类型的最小值Max()数字、字符、datetime返回指定列中的相应类型的最大值Count(*)任意基于行的数据类型统计结果集中全部记录行的数量（个数）以下给出简单的汇总例子，说明聚合函数的使用。例. 对表xs中的总学分，求所有学生的平均学分。

8、 select avg(总学分) as 平均学分 from xs 例. 对表xs中的总学分，求计算机专业学生的平均学分。 select avg(总学分) as 平均学分 from xs where 专业名=计算机 9数据的查询-分组与总汇1. 聚合函数的使用: count() 例. 对表xs统计学生人数。 select count(*) as 学生总数 from xs 例. 对表xs中，统计专业个数。 select count(distinct 专业名) as 专业个数 from xs 例. 对表xs, 统计女生学生人数。（设性别为0的代表女性） select count(*) as 女生人数

9、 from xs where 性别=0 10数据的查询-分组与总汇-分组求平均 例：需知道各专业的总学分的平均值。而不是指定的某个专业。 select 专业名, avg(总学分) as 平均学分 from xs group by 专业名问题：需知道各专业的男女学生各自人数是多少？ -按多个列分组计数2。分组-在结果集内分组group by -分组计数例：需知道各专业的学生人数。而不是指定的某个专业。 select 专业名, count(*) as 人数 from xs group by 专业名 select 专业名, 性别, count(*) as 人数 from xs group by 专业

10、名,性别 order by 专业名11数据的查询-分组与总汇另有例：有一学生选课成绩表xs_kc，其结构如下： create table xs_kc ( 学号 char(6) not null unique(学号,课程号), 课程号 char(3) not null , 选课学期 char(12) not null default(datename(yy,getdate()+年 +datename(mm,getdate()+月) 成绩 decimal(5,1) ) 其中数据记录大致如下： 学号 课程号 选课时间 成绩 001101101 2001年1月 80.0 001101102 2002年

11、1月 78.0 001101206 2004年9月 76.0 001103101 2001年1月 62.0 001103206 2004年9月 81.0 001104101 2001年1月 90.0 001104102 2002年1月 84.0学号 课程号 选课时间 成绩001104206 2004年9月 65.0001102102 2002年1月 78.0001102206 2004年1月 78.0001202101 2001年1月 65.0001203301 2003年9月 87.0001203206 2001年1月 60.0001204101 2001年1月 91.0001204102

12、2002年1月 64.0001204301 2003年9月 50.012数据的查询-分组与总汇-分组计数 查找xs_kc中各个学生分别已选科目数。 select 学号,count(*) as 数量 from xs_kc group by 学号-分组求平均 在xs_kc表中，分别按不同的课程，查看各科平均成绩 select 课程号,avg(成绩) as 平均成绩 from xs_kc group by 课程号-对表中部份数据进行分组汇总 在xs_kc表中，分别按不同的课程，查看指定学期的各课程平均成绩 select 课程号, avg(成绩) as 平均成绩 from xs_kc where su

13、bstring(选课学期,1,4)=2004 group by 课程号13数据的查询-分组与总汇3。筛选分组结果 having 以表xs_kc为例，查看指定“选课学期”的学生的各课平均学号及成绩。 分析：参与求平均的记录（条件）-指定学期， 分组依据-学号 命令： select 学号,avg(成绩) as 平均成绩 from xs_kc where substring(选课学期,1,4)=2001 group by 学号再看：查看指定“选课学期”的学生的各课平均成绩在70分以上的学号及成绩。 14数据的查询-分组与总汇筛选分组结果 having having 子句为group by 设置条件方

14、式，其搜索条件在分组操作之后应用，where子句搜索条件在进行分组操作之前应用。练习：1。查看2002年每个学生选课数目。 2。查看2002年每个学生选课数目在3门以上的学生学号及选课数目分析：参与求平均的记录（条件）-指定学期， 分组依据-学号 显示结果（筛选条件）-平均成绩 命令： select 学号,avg(成绩) as 平均成绩 from xs_kc where substring(选课学期,1,4)=2001 group by 学号 having avg(成绩)70 15数据的查询-分组与总汇练习答案1题 命令： select 学号,count(*) as 选课数目 from xs_

15、kc where substring(选课学期,1,4)=2001 group by 学号2题 命令： select 学号,count(*) as 选课数目 from xs_kc where substring(选课学期,1,4)=2001 group by 学号 having count(*) =316在结果集中生成汇总值使用带有 ROLLUP 运算符的 GROUP BY 子句,计算组中的汇总值计算组中的汇总值select 学号,课程号,avg(成绩) from xs_kc group by 学号,课程号 with rollup order by 学号,课程号 -查看各学生的各课程平均成绩，

16、并汇总总全部学生的平均成绩select 学号,课程号,avg(成绩) from xs_kc group by 学号,课程号 with cube order by 学号,课程号 -查看各学生的各课程平均成绩，并汇总总全部学生的平均成绩, 并汇总各课的平均成绩。17在结果集中生成汇总值ROLLUP ROLLUP 操作符操作符与CUBE操作符 的不同lROLLUP ROLLUP 选项，只返回GROUP BY 子句中的第一个分组条件指定的列的统计行。l除了返回由GROUP BY 子句指定的列外还返回按组统计的行，返回的结果先按分组的第一个条件列排序显示，再按第二个条件列排序显示，以此类推。统计行包括了

17、GROUP BY 子句指定的列的各种组合的数据统计18区分结果集中的细节数据和汇总数据区分结果集中的细节数据和汇总数据 使用 GROUPING 函数能够帮助识别结果集中的空值是表中原有的空值，还是由 ROLLUP 或 CUBE 生成的含有汇总值的新记录。其中：lSQL Server 将为 GROUPING 函数指定的字段生成一个新的字段l如果 SQL Server 返回 1，代表结果集中的这一记录是由 ROLLUP 或 CUBE 生成的lSQL Server 返回 0，代表该记录原本就在数据库的表中select 学号,grouping(学号),课程号,grouping(课程号),avg(成绩)

18、 from xs_kc group by 学号,课程号 with rollup order by 学号,课程号在结果集中生成汇总值使用-GROUPING 函数19多表连接查询也可使用多表连接命令： select a.学号,a.姓名,a.专业名,c.课程名,b.成绩,c.学分 from xs a join xs_kc b on a.学号=b.学号 join kc c on c.课程号=b.课程号Select语句并非只能对一个或两个表进行操作。SQL Server的select语句最多可以同时引用256个表。另外，因为SQL是一种描述性的语言，数据源的顺序是无关紧要的。如：查看学生学号、姓名、专业

19、名、课程名、学分。多表的查询： 命令： select xs.学号, xs.姓名, xs.专业名, kc.课程名, xs_kc.成绩, xs.总学分 from xs,xs_kc,kc where xs.学号=xs_kc.学号 and kc.课程号=xs_kc.课程号 20多表连接查询 1。 内连接命令： select kc.课程名,xs_kc.学号 from xs_kc inner join kc on kc.课程号=xs_kc.课程号 select kc.课程名,xs_kc.学号 from xs_kc right join kc on kc.课程号=xs_kc.课程号若要求：查看选课情况：看课

20、程名及该课程所选学生学号，要求没人选的课程也要列出来。2。左/右连接命令： 内连接Inner join 选取两个数据集合中的重叠部分以内的行连接外连接Left out join还可选取左侧数据集合中非匹配部分的行连接Right out join还可选取右侧数据集合中非匹配部分的行连接Full outer join返回参与连接的两个数据集合中的全部数据。 连接的类型21多表连接查询 1。 内连接命令： select kc.课程名,xs_kc.学号 from xs_kc inner join kc on kc.课程号=xs_kc.课程号若要求：查看选课情况：看课程名及该课程所选学生学号，要求没人选

21、的课程也要列出来。学号课程号选课学期成绩1001a1011001a1021002a101课程号课程名a101电子技术a102数据库原理a103C程序设计a104数据结构Xs_kc表 kc表课程名学号电子技术1001数据库原理1001电子技术100222多表连接查询 select kc.课程名,xs_kc.学号 from xs_kc right join kc on kc.课程号=xs_kc.课程号若要求：查看选课情况：看课程名及该课程所选学生学号，要求没人选的课程也要列出来。2。左/右连接命令： 学号课程号选课学期成绩1001a1011001a1021002a101课程号课程名a101电子技术

22、a102数据库原理a103C程序设计a104数据结构Xs_kc表 kc表课程名学号电子技术1001电子技术1002数据库原理1001C程序设计null数据结构null23多表连接查询 例： 假设在xs_kc表中，允许学生提交一些暂未开设的课，即在xs_kc表中插入一记录，其课程号在kc表中不存在。 insert into xs_kc (学号,课程号) values(xxxx,ccc)要求：查看学生选课情况：看学生学号,及该所选课程名。命令：select kc.课程名,xs_kc.学号 from xs_kc left join kc on kc.课程号=xs_kc.课程号 要求：查看学生选课情况

23、：看学生学号及该生所选课程总数。 select 学号, count(*) as 门数 from xs_kc group by 学号 若考虑其所选课程并未开设的因素,则该生的选课门数应为0. 测试命令: select xs_kc.学号, count(*) as 门数 from xs_kc left join kc on xs_kc.课程号=kc.课程号 group by xs_kc.学号24多表连接查询-自连接 上述命令结果也未达到要求. 提示: count(*) 将统计所有记录的行数,包括空值的行. 自连接连接可以在多表之间进行，也可以使一个表同其自身进行连接，这种连接称为自连接。相应的查询称

24、为自查询。 例：查询xs_kc表中,选课相同的学生的学号select a.课程号,a.学号, b.学号 from xs_kc a join xs_kc b on a.课程号=b.课程号 where a.学号b.学号 25多表连接查询-自连接 例：如右表为person表查询看“张华“的孩子。select distinct psn.编号,psn.姓名,m.姓名 from person psn inner join person m on psn.母亲id=m.编号 where rtrim(m.姓名)=张华编号姓名性别父亲id母亲id出生日期101张三110210319801230105李强1102

25、10319831012102李四1105107103张华0104108195535104张老大1110107193141107王芳0111112113钱二1108109108钱一1110107109张丽011111226子查询 在一个select语句中的where子句中, 嵌套另一个select语句的查询称为子查询。 当查询依赖于另一个查询的结果时，子查询是有用的。1。返回单个值 要求：查找xs_kc中成绩在平均水平之上的学生学号及成绩. select 学号,成绩 from xs_kc where 成绩(select avg(成绩) from xs_kc ) 要求：查找xs_kc中成绩在平均水

26、平之上的学生姓名及成绩. select xs_kc.学号,xs.姓名,xs_kc.成绩 from xs_kc join xs on xs_kc.学号=xs.学号 where 成绩(select avg(成绩) from xs_kc )27子查询2。返回一组数据 要求：查看在xs_kc表中有选课程的学生学号,姓名,专业名 select 姓名,学号,专业名 from xs where 学号 in (select 学号 from xs_kc)3。多层嵌套 要求：查看选修了“离散数学”课程的学生的学号，姓名，专业名. select 学号,姓名,专业名 from xs where 学号 in (sele

27、ct 学号 from xs_kc where 课程号 in (select 课程号 from kc where 课程名=离散数学)28存储查询结果1。 存储查询结果到新表中： 格式：select into select 姓名,专业名,出生时间 into newtable1 from xs where 性别=0 功能：将xs表中性别为0的记录的由“姓名,专业名,出生时间” 三列组成的 结果集存储到newtable1表中。2。存储结果集到已存在的表中： 格式： insert select 设已存在表a_table： create table a_table(a char(6),b char(8) 命令： insert a_table select 学号,姓名 from xs where 性别=029SQL Server 2000程序设计感谢各位！主题：数据查询及分类汇总数据查询及分类汇总主讲：彭 勇30