第5章.txt

sql server 2000 数据库与应用

【例5-1】查询学生基本信息表中的所有信息。
在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT * FROM 学生基本信息表
GO
【例5-2】在学生基本信息表中查询学生的学号、姓名、性别和族别信息。
在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT 学号,姓名,性别,族别 FROM 学生基本信息表
GO
【例5-3】从学生基本信息表中查询学生由几个民族构成。
  从例2结果可知，学生的族别有多行重复，要快速查询学生的民族构成，实际上就是对相同值的族别只需要显示一行，可使用DISTINCT关键字实现。
在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT DISTINCT 族别 FROM 学生基本信息表
GO
【例5-4】显示课程信息表中前5行的信息。
在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT TOP 5 * FROM 学生基本信息表
GO
运行结果如图5-4所示，只显示查询结果的前5行数据。
【例5-5】从学生基本信息表中只显示5%的信息。
在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT TOP 5 PERCENT * FROM 学生基本信息表
GO
【例5-6】从学生基本信息表中查询所有团员的信息资料，并形成新表为团员基本信息表。
在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT * INTO 团员基本信息表 FROM 学生基本信息表
WHERE 政治面貌='团员'
GO
SELECT * FROM 团员基本信息表
GO
【例5-7】从成绩表中查询学生成绩。
在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT *  FROM 成绩表
GO
【例5-8】从相关表中查询每一位学生的学号、姓名、课程名称、成绩。
从各表数据可知，“学号”存在于“学生基本信息表”和“成绩表”，“姓名”存在于“学生基本信息表”，“课程名称”存在于“课程信息表”，“成绩”存在于“成绩表”，要实现本例查询，则需要对“学生基本信息表”、“课程信息表”、“成绩表”进行多表检索，也可以来自不同的数据库。在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT 学生基本信息表.学号,姓名,课程名称,成绩  FROM 学生基本信息表,课程信息表,成绩表
WHERE 学生基本信息表.学号=成绩表.学号 AND 课程信息表.课程编号=成绩表.课程编号
GO
【例5-9】在课程信息表中查找“Delphi程序设计”课程的任课老师。
在查询分析器中运行如下命令：
    USE XSCJ
GO
SELECT 任课教师  FROM 课程信息表
WHERE 课程名称='Delphi程序设计'
GO
【例5-10】查询少数民族学生的基本情况。
在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT *  FROM 学生基本信息表
WHERE 族别<>'汉族'
GO
【例5-11】检索1985年1月1日以后出生的女生基本信息。
在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT *  FROM 学生基本信息表
WHERE 出生日期>'1985-01-01' AND 性别='女'
GO
【例5-12】查询每位同学的课程门数、总成绩、平均成绩。
查询每位学生的课程成绩情况，实际上就是按照“学号”列分类统计，可使用GROUP BY 学号子句，统计课程门数、总成绩、平均成绩分别可以使用聚合函数COUNT（课程编号）、SUM（成绩）、AVG（成绩）。
在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT 学号,COUNT(课程编号) AS '课程门数',SUM(成绩) AS '总成绩',AVG(成绩) AS '平均成绩' FROM 成绩表
GROUP BY 学号
GO
【例5-13】从学生基本信息表中统计各民族学生人数。
此例实际上是将要对学生按民族进行分类统计，可使用聚合函数COUNT(族别)实现功能。在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT 族别,COUNT(族别) AS '学生人数' FROM 学生基本信息表
GROUP BY 族别
GO
【例5-14】从学生基本信息表中统计汉族学生的人数。
此例就是在上例统计出各民族学生人数的基础上进一步限定查询汉族学生人数，可在GROUP BY子句之后跟HAVING 族别='汉族'子句实现此功能。
在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT 族别,COUNT(族别) AS '学生人数' FROM 学生基本信息表
GROUP BY 族别
HAVING 族别='汉族'
GO
此例也可使用WHERE子句完成功能。
USE XSCJ
GO
SELECT 族别,COUNT(族别) AS '学生人数' FROM 学生基本信息表
WHERE 族别='汉族'
GROUP BY 族别
GO
【例5-15】显示平均成绩大于等于80分以上的学生情况。
此例的限定条件是AVG(成绩)>=80，只能使用HAVING子句，如果使用WHERE子句限定条件，则系统会显示如图5-16所示的错误信息。
错误使用WHERE子句的SELECT语句如下：
USE XSCJ
GO
SELECT 学号,AVG(成绩) AS '平均成绩' FROM 成绩表
WHERE AVG(成绩)>=80
GROUP BY 学号
GO
使用HAVING子句的正确语句如下：
USE XSCJ
GO
SELECT 学号,AVG(成绩) AS '平均成绩' FROM 成绩表
GROUP BY 学号
HAVING AVG(成绩)>=80
GO
【例5-16】将学生平均成绩按升序排序。
     在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT 学号,AVG(成绩) AS '平均成绩' FROM 成绩表
GROUP BY 学号
ORDER BY AVG(成绩)
GO
【例5-17】查询成绩表中的全部信息，要求查询结果首先按学号升序排序，学号相同时，按成绩降序排序。
    在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT *  FROM 成绩表
ORDER BY 学号,成绩 DESC
GO
【例5-18】按学号显示学生成绩，并计算每人的平均成绩和总成绩。
此例要求按人对课程及成绩进行分组显示，并计算每人的平均成绩、总成绩。则显示成绩应按学号分类，分组计算平均成绩、总成绩的语句为COMPUTE AVG(成绩),SUM(成绩) BY 学号，使用COMPUTE BY子句首先要用ORDER BY子句对要分组的学号列排序，即ORDER BY 学号。
    在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT *
FROM 成绩表
ORDER BY 学号
COMPUTE AVG(成绩),SUM(成绩) BY 学号
GO
【例5-19】从系部表中检索系部名称，从班级表中检索班级名称。
从系部表中检索系部名称的SELECT语句为：SELECT 系部名称 FROM 系部表，从班级表中检索班级名称的SELECT语句为：SELECT 班级名称 FROM 班级表，合并这两个查询结果，需要使用UNION运算符。
在查询分析器中运行如下命令：
    USE XSCJ
GO
SELECT 系部名称 FROM 系部表
UNION
SELECT 班级名称 FROM 班级表
GO 
【例5-20】查询1985年出生的学生基本信息。
  1985年出生的学生即出生日期在1985年1月1日至12月31日之间的学生。在查询分析器中运行如下命令：
   USE XSCJ
   GO
 SELECT * FROM 学生基本信息表
WHERE 出生日期 BETWEEN '1985-01-01' AND '1985-12-31'
GO 
【例5-21】查询不及格学生成绩信息。
查询不及格学生成绩信息，也就是查询0—59之间的学生成绩，可用BETWEEN关键字表示为：WHERE 成绩 BETWEEN 0 AND 59。
在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT * FROM 成绩表
WHERE 成绩 BETWEEN 0 AND 59
GO
【例5-22】查询课程编号为002、003、007、014的课程编号、课程名称、任课教师和上课时间。
课程编号为002、003、007、014可以写成：WHERE 课程编号 IN('002','003','007','014')，也可写成WHERE 课程编号=’002’ OR课程编号=’003’ OR课程编号=’007’ OR课程编号=’014’ 。显然，使用IN关键字进行检索比使用3个OR运算符进行检索更为简单，而且易于理解和阅读。
在查询分析器中运行如下命令： 
USE XSCJ
GO
SELECT 课程编号,课程名称,任课教师,上课时间 FROM 课程信息表
WHERE 课程编号 IN('002','003','007','014') 
GO
在查询分析器中运行以下命令，也可得到相同的查询结果，但这种写法显然比较繁琐。
USE XSCJ
GO
SELECT 课程编号,课程名称,任课教师,上课时间 FROM 课程信息表
WHERE 课程编号=’002’ OR课程编号=’003’ OR课程编号=’007’ OR课程编号=’014’  
GO
【例5-23】检索所有姓刘的学生基本信息。
匹配所有姓刘的学生可以表示为：姓名 LIKE ‘刘%’。在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT * FROM 学生基本信息表
WHERE 姓名 LIKE '刘%' 
GO
【例5-24】检索包含“技术”两字的课程信息。
匹配“技术”两字的课程名称可以表示为：课程名称 LIKE ‘%技术%’。
在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT * FROM 课程信息表
WHERE 课程名称 LIKE '%技术%' 
GO
【例5-25】检索少数民族学生的基本信息。
少数民族学生或以表示为：WHERE 族别 NOT LIKE ‘汉族’。
在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT * FROM 学生基本信息表
WHERE 族别 NOT LIKE '汉族' 
GO
【例5-26】查询第2 个字为“丽”的学生信息。
在学生基本信息表中，匹配第2 个字为“丽”的学生姓名应表示为：姓名 LIKE ‘_丽%’。
在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT * FROM 学生基本信息表
WHERE 姓名  LIKE '_丽%' 
GO
【例5-27】查询课程信息表中教师未定的课程信息。
课程信息表中教师未定的表达式可以表示为：WHERE 任课教师 IS NULL。
在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT * FROM 课程信息表
WHERE 任课教师  IS NULL 
GO
【例5-28】统计成绩表中各门课程的学生人数、总成绩、平均成绩。
统计成绩表中各门课程信息，需要将学生成绩按课程编号分组GROUP BY 课程编号，统计学生人数、总成绩、平均成绩分别需要使用聚合函数COUNT（学号）、SUM（成绩）、AVG（成绩）。因为新生成的学生人数、总成绩、平均成绩三列没有列名，所以可使用AS子句实现。
    在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT 课程编号,COUNT(学号) AS '学生人数',SUM(成绩) AS '总成绩',AVG(成绩) AS '平均成绩' 
FROM 成绩表
GROUP BY 课程编号
GO
【例5-28-1】检索单科成绩高于全班平均分的学生成绩信息。
此例中，全班平均成绩为SELECT AVG(成绩) AS '平均成绩' FROM 成绩表，单科成绩高于全班平均分可以表述为WHERE 成绩>(SELECT AVG(成绩) FROM 成绩表)。
在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT AVG(成绩) AS '平均成绩' FROM 成绩表
GO
SELECT * FROM 成绩表
WHERE 成绩>(SELECT AVG(成绩) FROM 成绩表)
GO
【例5-29】检索系部信息和班级信息。
此例要检索系部表和班级表的所有信息，即显示两个表的所有信息。可在SELECT子句中使用*、系部表.*或班级表.*，连接条件是两个表的系部编号的值要相等，即系部表.系部编号=班级表.系部编号。
在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT * FROM 系部表,班级表
WHERE 系部表.系部编号=班级表.系部编号
GO
使用ANSI连接语法的SELECT语句如下：
USE XSCJ
GO
SELECT * FROM 系部表 INNER JOIN 班级表
ON 系部表.系部编号=班级表.系部编号
GO
【例5-30】检索系部信息和班级信息，要求连接的列只显示一次。
本例与上例的区别是对连接的列只显示一列，用SELECT子句可以写成：SELECT 系部表.*,班级编号,班级名称。
在查询分析器中运行如下命令:
USE XSCJ
GO
SELECT 系部表.*,班级编号,班级名称 FROM 系部表,班级表
WHERE 系部表.系部编号=班级表.系部编号
GO
使用ANSI连接语法的SELECT语句如下：
USE XSCJ
GO
SELECT 系部表.*,班级编号,班级名称 FROM 系部表 INNER JOIN 班级表
ON 系部表.系部编号=班级表.系部编号
GO
【例5-31】检索没有录入成绩的课课程情况。
在查询分析器中运行如下命令:
USE XSCJ
GO
SELECT DISTINCT 课程信息表.* FROM 课程信息表,成绩表
WHERE 课程信息表.课程编号<>成绩表.课程编号
GO
【例5-32】使用左外连接检索学生成绩信息（学号，姓名，课程名称）。
 在查询分析器中运行如下命令:
USE XSCJ
GO
SELECT 学号,课程信息表.课程编号,课程信息表.课程名称,成绩 
FROM 课程信息表 LEFT JOIN 成绩表 ON 课程信息表.课程编号=成绩表.课程编号
GO
【例5-33】使用右外连接检索学生成绩信息（学号，姓名，课程名称）。
 在查询分析器中运行如下命令:
USE XSCJ
GO
SELECT 学号,课程信息表.课程编号,课程信息表.课程名称,成绩 
FROM 课程信息表 RIGHT JOIN 成绩表 ON 课程信息表.课程编号=成绩表.课程编号
GO
【例5-34】使用全外连接检索学生成绩信息（学号，姓名，课程名称）。
 在查询分析器中运行如下命令:
USE XSCJ
GO
SELECT 学号,课程信息表.课程编号,课程信息表.课程名称,成绩 
FROM 课程信息表 FULL  JOIN 成绩表 ON 课程信息表.课程编号=成绩表.课程编号
GO
【例5-35】计算系部表和班级表的交叉连接。
在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT * FROM 班级表 CROSS JOIN 系部表
GO
此例也可用FROM子句写成如下语句，运行结果相同。
USE XSCJ
GO
SELECT * FROM 班级表,系部表
GO
【例5-36】查找同名同姓的学生信息。
该例是对学生基本信息表进行行自连接，这里将学生基本信息表分别定义别名为A1、A2，将FROM子句写成FROM 学生基本信息表 A1,学生基本信息表 A2，连接条件为WHERE A1.姓名=A2.姓名 AND A1.学号<>A2.学号。
在查询分析器中运行如下命令：
USE XSCJ
GO
SELECT A1.* FROM 学生基本信息表 A1,学生基本信息表 A2
WHERE A1.姓名=A2.姓名 AND A1.学号<>A2.学号
GO