目录
5、显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及院系;
6、列出选修了课程编号为‘1’的课程的学生,按成绩的降序排列;
7、按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系,在结果集中列标题分别指定为“学号,姓名,性别,出生年份,院系”;
8、按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩;
10、按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩;
11、列出同时选修“1”号课程和“2”号课程的所有学生的学号;
12、列出有二门以上课程(含两门)不及格的学生的学号及该学生的平均成绩.
一、实验目的
- 掌握SELECT语句的基本语法和查询条件表示方法;
- 掌握查询条件表达式和使用方法;
- 掌握GROUP BY子句的作用和使用方法;
- 掌握HAVING子句的作用和使用方法;
- 掌握ORDER BY子句的作用和使用方法。
二、实验环境
已安装SQL Server 2012的计算机;具有局域网环境,有固定IP。
三、实验学时
2学时
四、实验要求
- 了解数据库查询;
- 了解数据库查询的实现方式;
- 完成实验报告;
五、实验内容及步骤
首先启动SSMS程序登录服务器,执行“附加”操作,把案例数据库“S_T”添加到自己所登录的SQL Server服务器中。以“S_T”中的数据为基础,使用T-SQL语句实现以下查询操作,同时截屏记录下执行结果。
注意:先下载附件“S_T.rar”,解压缩后得到S_T.mdf和S_T_log.ldf两个文件,把它们复制到SQL Server程序安装目录下的DATA子目录中,通常为“C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA”(如果不放在该目录下,则要对自行放置这两个文件的目录进行“安全”设置,需要“完全控制”权限才可以进行后续的“附加”操作)。然后,再进行“附加”操作。“附加”操作的过程参见附件文档说明。
1、列出所有不姓“刘”的所有学生;
SQL语句:
SELECT Sname
FROM student
WHERE Sname NOT LIKE '刘%';
结果:
说明:
1、SELECT语句
SELECT [ALL/DISTINCT] <目标列表达式> [别名] [...]
FROM <表名或视图> [别名] [...]
[WHERE <条件表达式>]
[GROUP BY <列名> [HAVING <条件表达式>]]
[ORDER BY <列名> [ASC|DESC]]
[LIMIT <行数> [OFFSET <行数>]];
2、字符匹配:查找指定属性列中域<匹配串>向匹配的元组
语法:
[NOT] LIKE '<匹配串>' [ESCAPE '<换码字符>']
(1)<匹配串>:可以是一个完整的字符,也可以含有通配符%和_
①%:代表任意长度(长度可为0)的字符串
eg:a%b即以a开头,以b结尾的任意长度字符串。acb、adfrgb、ab均符合要求
②_:代表任意单个字符
eg:a_b代表以a开头,以b结尾的长度为3的任意字符串。acb、afb、aeb均符合要求
(2)若LIKE后的匹配串不含通配符(%或_)可以用“=”(等于)代替LIKE,用“!=”或“<>”(不等于)代替NOT LIKE
(3)ESCAPE '<换码字符>':用户要查询的字符串本身就含有通配符,使用ESCAPE '<换码字符>'对通配符进行转义
eg:ESCAPE '\'表示\(右斜线)为换码字符,匹配串中紧跟在\后面的通配符不再具有特殊含义,转换为普通字符
2、列出姓“沈”且全名为3个汉字的学生;
SQL语句:
SELECT Sname
FROM student
WHERE Sname LIKE '沈%' AND LEN(Sname)=3;
结果(经人肉核对,表中没有符合条件的元组):
说明:
1、SELECT *等价于SELECT ALL
2、其实这一题就算将限制条件改成“张”、“王”、“李”这些存在符合条件的元组也是筛选不出的。因为在设表时,数据Sname被设置为定长字段,即使数据长度不足也会自动补齐长度,故在数据提取出来后应对数据长度做出处理,才能加以筛选。
3、显示在2000年以后出生的学生的基本信息;
SQL语句:
SELECT *
FROM student
WHERE Sage<(2024-2000);
结果:
4、查询出课程名含有“数据”字串的所有课程基本信息;
SQL语句:
SELECT *
FROM course
WHERE cname LIKE '数据%';
结果:
5、显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及院系;
SQL语句:
SELECT *
FROM student
WHERE Sno LIKE '_______1_' OR Sno LIKE '_______2_' OR Sno LIKE '_______3_'
OR Sno LIKE '_______4_' OR Sno LIKE '_______9_' OR Sno LIKE '________1'
OR Sno LIKE '________2'OR Sno LIKE '________3' OR Sno LIKE '________4'
OR Sno LIKE '________9';
结果:
说明:
1、多重条件查询:用逻辑运算符AND和OR来连接多个查询条件。AND的优先级高于OR,但用户可以用括号来改变优先级。
6、列出选修了课程编号为‘1’的课程的学生,按成绩的降序排列;
SQL语句:
SELECT *
FROM sc
WHERE cno=1
ORDER BY grade DESC;
结果:
说明:
1、ORDER BY 子句:可以对查询结果按照一个或多个属性的升序(ASC)或降序(DESC)排序,默认值为升序
2、对于空值,排序时显示的次序由具体系统实现来决定
3、排序字段中,ASC可省略,DESC需要明确写出
7、按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系,在结果集中列标题分别指定为“学号,姓名,性别,出生年份,院系”;
SQL语句:
SELECT Sno,Sname,Ssex,(2024-Sage) Birthyear,Sdept
FROM student
ORDER BY Birthyear ASC;
结果:
8、按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩;
SQL语句:
SELECT *
FROM sc
WHERE grade BETWEEN 70 AND 80
ORDER BY cno DESC, grade DESC;
结果:
说明:
1、BETWEEN...AND...和BETWEEN...NOT AND...可以用来查找属性值在(不在)指定范围内的元组。(BETWEEN 低值 (NOT) AND 高值)
9、显示选修的课程数大于3的各个学生的选修课程数;
SQL语句:
SELECT sno,COUNT(cno) cnum
FROM sc
GROUP BY sno
HAVING COUNT(cno)>3;
结果:
说明:
1、GROUP BY 子句:将查询结果按某一列或多列的值分组,值相等的为一组,如果是多列,则先对第一列分组,然后对每一组中的值按照第二列值分组,以此类推。若集中结果中存在重复的列组值,则合并为一行输出
2、分组后还要求按一定的条件对这些组进行筛选,只输出满足指定条件的组,则使用HAVING短语进行筛选
3、WHERE和HAVING的区别:作用对象不同。WHERE子句作用域基本表或者视图,从中选择满足条件的元组,HAVING短语作用于组,从中选择满足条件的组
4、关系数据库管理系统处理SQL语句时,先处理WHERE子句,根据条件筛选出合格元组,生成一个临时表,再处理GROUP BY子句按照条件进行分组,最后通过HAVING短语进行处理
5、聚集函数
COUNT(*) | 统计元组个数 |
COUNT([DISTINCT|ALL] <列名>) | 统计一列中值的个数 |
SUM([DISTINCT|ALL] <列名>) | 计算一列值的总合(此列必须是数值型) |
AVG([DISTINCT|ALL] <列名>) | 计算一列值的平均值(此列必须是数值型) |
MAX([DISTINCT|ALL] <列名>) | 求一列中的最大值 |
MIN([DISTINCT|ALL] <列名>) | 求一列中的最小值 |
(1)DISTINCT短语:计算时取消指定列中的重复值
(2)ALL短语:不取消重复值(ALL为默认值)
(3)出COUNT(*)外,其余聚集函数均只处理非空值(跳过空值)
10、按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩;
SQL语句:
SELECT cno,COUNT(sno) snum,MAX(grade) 最高成绩,MIN(grade) 最低成绩,AVG(grade) 平均成绩
FROM sc
GROUP BY cno
ORDER BY cno DESC;
结果:
11、列出同时选修“1”号课程和“2”号课程的所有学生的学号;
SQL语句:
SELECT sno
FROM sc
WHERE cno IN('1','2')
GROUP BY sno
HAVING COUNT(DISTINCT cno)=2;
结果:
12、列出有二门以上课程(含两门)不及格的学生的学号及该学生的平均成绩.
SQL语句:
SELECT sno,AVG(grade) 平均成绩
FROM sc
WHERE grade<60
GROUP BY sno
HAVING COUNT(*)>=2;
结果:
——终——