数据库表简单查询操作
一、实验目的
掌握创建数据表的查询命令
二、实验知识要点
了解和掌握实验相关知识点:
SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …
FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句)
[AS]<别名>
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];
三、实验内容
创建一个名为:jiaoxue的数据库,在该库下创建如下的各表以及视图等数据库对象。
STUDENT表
学 号 | 姓 名 | 性 别 | 年 龄 | 所 在 系 |
---|---|---|---|---|
Sno | Sname | Ssex | Sage | Sdept |
200215121 | 李勇 | 男 | 20 | CS |
200215122 | 刘晨 | 女 | 19 | CS |
200215123 | 王敏 | 女 | 18 | MA |
200515125 | 张立 | 男 | 19 | IS |
COURSE表 | ||||
课程号 | 课程名 | 先行课 | 学分 | |
--------------------------- | --------------- | -------------- | ------------ | |
Cno | Cname | Cpno | Ccredit | |
1 | 数据库 | 5 | 4 | |
2 | 数学 | 1 | 2 | |
3 | 信息系统 | 6 | 4 | |
4 | 操作系统 | 7 | 3 | |
5 | 数据结构 | 6 | 4 | |
6 | 数据处理 | 2 | ||
7 | PASCAL语言 | 4 | ||
SC表 | ||||
学号 | 课程号 | 成绩 | ||
--------------------------- | --------------- | -------------- | ||
Sno | Cno | Grade | ||
200215121 | 1 | 92 | ||
200215121 | 2 | 85 | ||
200215121 | 3 | 88 | ||
200215122 | 2 | 90 | ||
200215122 | 3 | 80 |
四、实验操作及过程
--1 查询全体学生的学号与姓名
select sno,sname from student
--2 查询全体学生的姓名及其出生年份
select sname,year(getdate())-sage from student
--3 查询选修了课程的学生的学号
select distinct sno from sc
--4 查询计科系全体学生名单
select sname from student where sdept='CS'
--5 查询所有年龄在20岁以下的学生姓名及其年龄
select sname,sage from student where sage < '20'
--6 查询考试有不及格的学生的学号
select distinct sno from sc where grade < '60'
--7 查询年龄不在20-23岁之间的学生姓名、系别和年龄
select sname,sdept,sage from student where sage not between '20' and '30'
--8 查询计科系、数学系和信息系学生的姓名和性别
select sname,ssex from student where sdept in('CS','MA','IS')
--9 查询姓“欧阳”且全名为3个汉字的学生的姓名
select sname from student where sname = '欧阳_'
--10 查询所有不姓名“刘”的学生姓名
select sname from student where sname != '刘%'
--11 查询所有有成绩的学生学号和课程号
select sno,cno from sc where grade is not null
--12 查询计科系年龄在20岁以下的学生姓名
select sname from student where sdept = 'CS' and sage < '20'
--13 查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列
select cno,grade from sc where cno = '3' order by grade desc
--14 查询全体学生情况,查询结果按所在系的系号升序排列,同一系的学生按年龄降序排列
select * from student order by sdept,sage desc
--15 查询学生总人数
select count(*) from student
--16 查询选修了课程的学生人数
select count(distinct sno) from sc
--17 计算1号课程的学生平均成绩
select avg(grade) from sc where cno ='1'
--18 查询选修1号课程的学生最高分数
select max(grade) from sc where cno='1'
--19 求各个课程号及其相应的选课人数
select cno,count(sno) from sc group by cno
--20 查询选修了3门以上课程的学生学号
select sno from sc group by sno having count(*)>3
--21 查询DB_Design课程的课程号和学分
select cno,ccredit from course where cname like 'DB\_Design' escape '\'
--22 查询以”DB_”开头,且倒数第3个字符为i的课程的详细情况
select * from course where cname like 'DB\_%i_ _' escape '\'
--23 列出学生所有可能的选课情况
select student.* ,sc.* from student,sc where student.sno=sc.sno
--24 统计被学生选修的课程门数
select count(distinct cno) from sc
--25 查询平均成绩都在80分以上的学生学号及平均成绩
select sno,avg(grade) from sc group by sno having avg(grade)>80
--26 查询成绩在80~90之间的记录
select * from sc,student,course where student.sno=sc.sno and sc.grade between 80 and 90 and sc.cno=course.cno
--27 查询至少有4个同学选修的课程名
select cname from course where cno=any(select cno from sc group by sc.cno having count(*)>=4 )
--28 查询其他系中比“IS”系所有学生年龄都大的学生名单及年龄,并按年龄降序输出
select sname,sage from student where sage > all (select sage from student where sdept = 'IS') and sdept <> 'IS'
--29 查询与学生王敏同岁的所有学生的学号,姓名和系别
select sno,sname,sdept from student where sage =any(select sage from student where sname ='王敏') and sname <> '王敏'
--30 查询选修了2门以上课程的学生名单
select sno,sname from student where sno in (select sc.sno from sc group by sc.sno having count(*)>2)
五、实验小结
本次实验是关于学生信息库的基本查询,通过基本的查询语句了解相关的WHERE,GROUP BY,ORDER BY语句,了解最基本的连接查询。
在实验中遇到的问题是,刚开始建表的时候把列名写错了,通过网上搜查用ALTER 语句修改还是有错,不清楚在SQL SERVER中修改列名的语句是什么,只能在交互式界面修改。对于第23列出所有学生的选课情况,对于表之间的连接有些迷糊,认为在课后对表的连接部分还应加强练习。