SQL Server 2000程序设计

1、1,SQL Server 2000程序设计,主题： 数据查询及分类汇总 朱 士 明 安徽电子信息职业技术学院,2,数据的查询,简单查询命令 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 xs,3,数据的简单查询,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选项的作用,4,3。Select 结果集中的列 可以是*, 也可以是以下的情况, 可以是列名 例1：select 学号 as 学生学号,姓名,专业名 from xs,数据的简单查询, 可以是别名 例2：查看学生的姓名,专业名,出生年份 sele

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

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

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

6、序,4.4 order by的使用: 对结果集排序。,2。也可以使用表达式指定排序 select 学号,姓名,性别,专业名,总学分 from xs order by 专业名+姓名 select 学号,姓名,性别,专业名,总学分 from xs order by 专业名+ cast(总学分 as varchar(6),1。 使用列名指定排序顺序 按总学分升序排列，当总学分相同时，按性别的值升序 排列： select 学号,姓名,性别,专业名,总学分 from xs order by 总学分,性别,3。 使用别名指定排序顺序,4。 使用列名指定排序顺序,9,数据的查询-分组与总汇,1. 聚合函数的

7、使用: 常用的聚合函数如下,以下给出简单的汇总例子，说明聚合函数的使用。 例. 对表xs中的总学分，求所有学生的平均学分。 select avg(总学分) as 平均学分 from xs,例. 对表xs中的总学分，求计算机专业学生的平均学分。 select avg(总学分) as 平均学分 from xs where 专业名=计算机,10,数据的查询-分组与总汇,1. 聚合函数的使用: count(),例. 对表xs统计学生人数。 select count(*) as 学生总数 from xs,例. 对表xs中，统计专业个数。 select count(distinct 专业名) as 专业个

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

9、om xs group by 专业名,select 专业名, 性别, count(*) as 人数 from xs group by 专业名,性别 order by 专业名,12,数据的查询-分组与总汇,另有例：有一学生选课成绩表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(

10、5,1) ) 其中数据记录大致如下： 学号 课程号 选课时间 成绩 001101101 2001年1月 80.0 001101102 2002年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,13,数据的查询-分组与总汇,-分组计数 查找xs_kc中各个学生分别已选科目数。 select 学号,count(*) as 数量 from xs_kc group by 学号,-分组求平均 在xs_kc表中

11、，分别按不同的课程，查看各科平均成绩 select 课程号,avg(成绩) as 平均成绩 from xs_kc group by 课程号,-对表中部份数据进行分组汇总 在xs_kc表中，分别按不同的课程，查看指定学期的各课程平均成绩 select 课程号, avg(成绩) as 平均成绩 from xs_kc where substring(选课学期,1,4)=2004 group by 课程号,14,数据的查询-分组与总汇,3。筛选分组结果 having,以表xs_kc为例，查看指定“选课学期”的学生的各课平均学号及成绩。 分析：参与求平均的记录（条件）-指定学期， 分组依据-学号,命令：

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

13、果（筛选条件）-平均成绩,命令： select 学号,avg(成绩) as 平均成绩 from xs_kc where substring(选课学期,1,4)=2001 group by 学号 having avg(成绩)70,16,数据的查询-分组与总汇,练习答案 1题 命令： select 学号,count(*) as 选课数目 from xs_kc where substring(选课学期,1,4)=2001 group by 学号 2题 命令： select 学号,count(*) as 选课数目 from xs_kc where substring(选课学期,1,4)=2001 gr

14、oup by 学号 having count(*) =3,17,在结果集中生成汇总值,使用带有 ROLLUP 运算符的 GROUP BY 子句,计算组中的汇总值,select 学号,课程号,avg(成绩) from xs_kc group by 学号,课程号 with rollup order by 学号,课程号,-查看各学生的各课程平均成绩，并汇总总全部学生的平均成绩,select 学号,课程号,avg(成绩) from xs_kc group by 学号,课程号 with cube order by 学号,课程号,-查看各学生的各课程平均成绩，并汇总总全部学生的平均成绩, 并汇总各课的平均

15、成绩。,18,在结果集中生成汇总值,ROLLUP 操作符与CUBE操作符 的不同 ROLLUP 选项，只返回GROUP BY 子句中的第一个分组条件指定的列的统计行。 除了返回由GROUP BY 子句指定的列外还返回按组统计的行，返回的结果先按分组的第一个条件列排序显示，再按第二个条件列排序显示，以此类推。统计行包括了GROUP BY 子句指定的列的各种组合的数据统计,19,区分结果集中的细节数据和汇总数据 使用 GROUPING 函数能够帮助识别结果集中的空值是表中原有的空值，还是由 ROLLUP 或 CUBE 生成的含有汇总值的新记录。 其中： SQL Server 将为 GROUPING

16、 函数指定的字段生成一个新的字段 如果 SQL Server 返回 1，代表结果集中的这一记录是由 ROLLUP 或 CUBE 生成的 SQL Server 返回 0，代表该记录原本就在数据库的表中 select 学号,grouping(学号),课程号,grouping(课程号),avg(成绩) from xs_kc group by 学号,课程号 with rollup order by 学号,课程号,在结果集中生成汇总值使用-GROUPING 函数,20,多表连接查询,也可使用多表连接命令： select a.学号,a.姓名,a.专业名,c.课程名,b.成绩,c.学分 from xs a

17、join xs_kc b on a.学号=b.学号 join kc c on c.课程号=b.课程号,Select语句并非只能对一个或两个表进行操作。 SQL Server的select语句最多可以同时引用256个表。另外，因为SQL是一种描述性的语言，数据源的顺序是无关紧要的。 如：查看学生学号、姓名、专业名、课程名、学分。,多表的查询： 命令： select xs.学号, xs.姓名, xs.专业名, kc.课程名, xs_kc.成绩, xs.总学分 from xs,xs_kc,kc where xs.学号=xs_kc.学号 and kc.课程号=xs_kc.课程号,21,多表连接查询,1。 内连接命令： select kc.课程名,xs_kc.学号 from xs_kc inner join kc on kc.课程号=xs_kc.课程号,select kc.课程名,xs_kc.学号 fr