sql数据库查询

sql数据库查询

实验二 数据查询

一、实验目的
1掌握使用Transact-SQL的SELECT语句进行基本查询的方法。
2掌握使用SELECT语句进行条件查询的方法。
3掌握嵌套查询的方法。
4掌握多表查询的方法。
5掌握SELECT语句的GROUP BY和ORDER BY子句的作业和使用方法。
二、实验内容
附加数据库：通过自己的U盘附加实验一所创建的“学生成绩管理”数据库。
表“Student表”中增加新字段 “province”，表示生源所属省份，字符类型为varchar(20)；并自行录入一些省份。如：福建、四川、广东、辽宁。
语法参考：ALTER TABLE 表名 ADD 列名 数据类型[(长度)]

（一）新建查询窗口，录入以下SQL语句，写成查询结果：
1、SELECT COUNT(*) FROM SC
查询结果为：

2、SELECT SQRT(SCORE) FROM SC WHERE SCORE>=85
查询结果为：

3、SELECT YEAR(GETDATE()),MONTH(GETDATE()),DAY(GETDATE())
查询结果为：

（二）查询语句练习
请在横线上写上你的SQL查询语句
A. 单表查询(含条件查询)
1、查询学号为“S002”学生的姓名和联系电话，结果中的字段名用中文表示:

SELECT Sname ,Stelno FROM Student
WHERE Snum=‘S002’
查询结果为：

2、查询ssex为“女”并且所属院系为“生物工程”的学生姓名
SELECT Sname FROM Student
WHERE Ssex=‘女’ AND Sdept=‘生物工程’

查询结果为：

3、查询<学生表>，查询姓"张"或者姓”李”的学生的基本信息。
提示：谓词LIKE
SELECT * FROM Student
WHERE Sname LIKE ‘张__’ OR Sname LIKE ‘李__’
查询结果为：

4、查询<学生表>，查询"所属省份"是"北京"、”新疆”、”上海"的学生信息。
提示：谓词IN
SELECT * FROM Student
WHERE province IN(‘北京’,‘上海’,‘新疆’)
查询结果为：

5A.查询<学生表>，在1999~2000年之间出生的学生的学号，姓名，出生日期。
提示：谓词BETWEEN AND
SELECT Snum, Sname,Sbirth FROM Student
WHERE Sbirth BETWEEN ‘1999’ AND ‘2000’
查询结果为：

5B 拓展（选做题）：查询年龄在18~19岁的学生的学号，姓名，出生日期。
提示：P84页，函数DATADIFF
SELECT Snum, Sname,Sbirth FROM Student
WHERE DATEDIFF(YEAR,Sbirth,2018)>19
AND DATEDIFF(YEAR,Sbirth,2018)<20

5、查询<学生表>，查询全部学生信息，并按照“性别”排序，性别相同的情况下按照“所在院系”排序。
提示：ORDEY BY

SELECT * FROM Student
GROUP BY Ssex,Snum,Sname,Sbirth,Stelno,province,Sdept
ORDER BY Ssex,Sdept
查询结果为：

B、聚合函数练习
1、在<选课表>中查询课程编号为S003的学生的平均分。
SELECT AVG(Score) FROM SC
WHERE Sunm=‘S004’
查询结果为：

2、显示学生信息表中的学生总人数及平均年龄、在结果集中字段题目分别指定为“学生总人数，平均年龄”；
SELECT COUNT(*)AS ‘学生总人数’,AVG( DATEDIFF(YEAR,Sbirth,‘2018’))AS’平均年龄’ FROM Student
查询结果为：

3、查询选修数据库基础（‘C01’）课的学生人数以及该课的最高、最低成绩、平均成绩。
SELECT COUNT(*)AS’选课人数’,MAX(Score)AS’最高成绩’,MIN(Score)AS’最低成绩’,AVG(Score)AS’平均成绩’ FROM SC
WHERE Cnum=‘C01’
查询结果为：

C、分组统计查询
1、统计<选课表>，统计每个课程的选修人数。
SELECT COUNT(*)AS’选课人数’,MAX(Score)AS’最高成绩’,MIN(Score)AS’最低成绩’,AVG(Score)AS’平均成绩’ FROM SC
GROUP BY Cnum
查询结果为：

2、显示选修的课程数大于3的各个学生的选修课程数；
SELECT COUNT()AS’选修课程数’ FROM SC
GROUP BY Sunm HAVING COUNT()>3
查询结果为：

3、显示有两门及两门以上课程不及格的学生的学号
SELECT Sunm FROM SC
WHERE Score<60
GROUP BY Sunm HAVING COUNT(*)>1
查询结果为：

D.连接查询
（1）.查询选修“C04”课,且成绩为90分以上的学生的学号和姓名。
SELECT Sunm,Sname FROM SC,Student
WHERE SC.Sunm=Student.Snum AND Cnum=‘C02’ AND Score>90
查询结果为：

（2）查询学习“VB”课程的学生的学号、姓名、分数。
SELECT Sunm,Sname,Score FROM SC,Student,Couse
WHERE SC.Sunm=Student.Snum AND SC.Cnum=Couse.Cnum AND Cname=‘VB’
查询结果为：

（3A）找出讲授“数据库基础”的老师的教师名
SELECT Tname FROM Teacher,Couse
WHERE Teacher.Tnum=SC.Tno AND Cname=‘VB’
(3B）拓展（选做题）：
查询“数据库基础”课程分数最高的学生的姓名、 性别、 课程名称、成绩和所学专业。
SELECT Sunm,Sname,Score FROM Student,SC,Couse
WHERE SC.Sunm=Student.Snum AND SC.Cnum=Couse.Cnum AND Cname=‘数据库基础’
GROUP BY Score,Sunm,Sname HAVING Score = MAX(Score)
查询结果为：

E.嵌套查询
（1）在<Student表>中查找与“李勇”性别相同的所有学生的姓名、出生日期。
SELECT Sname,Sbirth FROM STUDENT WHERE Ssex= (SELECT Ssex FROM Student WHERE Sname=‘李勇’)
查询结果为：

（2）. 使用IN子查询查找所修课程编号为C02或C04的学生学号、姓名、性别。
提示：select 学号,姓名,性别 from ？ where 学号 in( seltect ？)
SELECT Snum,Sname,Ssex FROM STUDENT WHERE Snum IN (SELECT Sunm FROM SC WHERE Cnum=‘C01’ OR Cnum=‘C02’)
查询结果为：

（四）视图相关练习

1. 请使用T-SQL语句完成以下内容，并将SQL语句写在实验报告册中：

1. 创建stu_CS视图，包括信息学院所有学生的基本信息；
   CREATE VIEW stu_CS AS SELECT * FROM Student

2. 向CS系学生视图stu_CS中插入一个新的学生记录，学号S010，姓名为“赵红平”，性别为男，出生年月为98-08-08；
   INSERT INTO stu_CS (Snum,Sname,Ssex,Sbirth)
   VALUES (‘S0011’,‘赵红萍’,‘男’,‘19980808’)
   查看视图全部内容。结果为：

3. 删除视图stu_cs；
   DROP VIEW stu_CS