实验报告数据库.docx

1、实验报告数据库实 验 报 告课程名称： 数据库系统原理及技术 实验名称： 数据查询 数据库系统原理及技术实验报告课程名称：数据库系统原理及技术实验项目名称：数据查询 实验类型：验证型 一、实验目的和要求1掌握SELECT语句的基本语法。2掌握子查询的表示。3掌握连接查询的表示。4掌握SELECT语句的统计函数的作用和使用方法。5掌握SELECT语句的GROUPBY和ORDERBY子句的作用和使用方法。二、实验内容和原理启动SQL Server查询分析器，利用SQL语句进行查询。三、实验环境（软硬件及配置）Windows2000 sql server2000四、实验步骤（含实验结果及分析）1.

2、简单查询SELECT sno,sname,sex,birthday FROM studentSELECT sno,sname,CAST(GETDATE()-birthday AS int)/365 AS age FROM studentSELECT sno,sname,sex,birthdayFROM studentWHERE dno=6100SELECT sno,sname,birthday FROM student WHERE spno=0805 AND classno=0202SELECT sno,sname,sex,birthdayFROM studentWHERE birthday1

3、989-03-28SELECT * FROM teacher WHERE pno4SELECT sno,snameFROM studentWHERE sname LIKE 李%SELECT sno,sname FROM studentWHERE sname LIKE 李_ _SELECT sno,sname FROM student WHERE sname LIKE 王黄% SELECT sno,sname,sex,birthday FROM student WHERE (dno=6100) AND (birthday1989-01-01)SELECT *FROM studentWHERE n

4、ative NOT LIKE 榆林%SELECT *FROM student WHERE CAST(GETDATE()-birthday AS int) BETWEEN 6500 AND 7600SELECT sno,sname,sex,spnoFROM studentWHERE spno IN (0804,0805)SELECT sno,sname,sex,birthdayFROM studentORDER BY birthday ASCSELECT sno,sname,sex,birthday,dnoFROM studentORDER BY dno,birthday DESCSELECT

5、sno,sname,sex,birthday,dno FROM student ORDER BY 5,4 DESCSELECT DISTINCT nativeFROM studentWHERE dno=6100SELECT sno AS 学号,sname AS 姓名,sex AS 性别 FROM student goSELECT TOP 3 sno,score FROM student_course WHERE tcid=1 ORDER BY score DESCSELECT COUNT(*) AS 信工学院学生人数 FROM student WHERE dno=6100SELECT COUN

6、T(*) AS 信工学院女学生人数 FROM student WHERE dno=6100 AND sex=女SELECT AVG(score) AS 平均分,MAX(score) AS 最高分,MIN(score) AS 最低分 FROM student_course WHERE tcid=1SELECT tcid AS 课程号, AVG(score) AS 平均分,MAX(score) AS 最高分,MIN(score) AS 最低分 FROM student_course GROUP BY tcid ORDER BY AVG(score) DESC2. 连接查询SELECT distin

7、ct sno FROM student_course WHERE id=1 or id=2SELECT sno FROM student_course WHERE tcid=1UNION SELECT sno FROM student_course WHERE tcid=2SELECT sc.sno AS 学号,sname AS 姓名, ame AS 不及格课程 FROM student AS st,student_course AS sc,teacher_course AS tc,course AS cr WHERE sc.score60 AND sc.sno=st.sno AND sc.t

8、cid=tc.tcid AND o=oSELECT sc.sno AS 学号,sname AS 姓名,COUNT(*) AS 没有及格的门数 FROM student_course AS sc,student AS st WHERE score1SELECT st.sno,st.sname,ame,sc.scoreFROM student AS st,course AS cs,student_course AS sc,teacher_course AS tcWHERE st.sno=sc.sno AND sc.tcid=tc.tcid AND o=oSELECT st.sno,sname,SU

9、M(credit) AS 总学分 FROM student AS st,student_course AS sc,course AS cs,teacher_course AS tc WHERE score60 AND st.sno=sc.sno AND sc.tcid=tc.tcid AND o=o GROUP BY st.sno,sname SELECT st.sno,sname,cname,score,credit FROM student AS st,student_course AS sc,course AS cs,teacher_course AS tc WHERE st.sno=s

10、c.sno AND sc.tcid=tc.tcid AND o=o AND st.sname=张海丽 3. 嵌套查询SELECT sno,sname,birthday FROM student WHERE dno = (SELECT dno FROM student WHERE sname = 李红豆 )SELECT sc.sno,sname,score FROM student AS st,student_course AS sc WHERE sc.tcid IN (SELECT tcid FROM teacher_course WHERE cno=H61030006) AND score=

11、(SELECT AVG(score) FROM student_course WHERE tcid IN (SELECT tcid FROM teacher_course WHERE cno=H61030006) AND st.sno=sc.snoSELECT top 3 with ties sc.sno AS 学号,st.sname AS 姓名 ,AVG(score) AS 平均分 FROM student_course AS sc,student AS st WHERE sc.sno IN (SELECT sno FROM student WHERE dno=610000) AND sc.

12、sno=st.sno GROUP BY sc.sno ,st.sname ORDER BY 平均分 DESCSELECT sno,sname FROM student WHERE EXISTS (SELECT * FROM student_course AS sc WHERE sc.sno=student.sno AND tcid=2) AND EXISTS (SELECT * FROM student_course AS sc WHERE sc.sno=student.sno AND tcid=2)SELECT sno,sname FROM student WHERE NOT EXISTS

13、(SELECT * FROM student_course AS sc WHERE student.sno=sc.sno AND tcid=1)SELECT sno,sname FROM student WHERE EXISTS(SELECT * FROM student_course AS sc WHERE student.sno=sc.sno AND tcid IN (SELECT tcid FROM teacher_course AS tc WHERE o=(SELECT cno FROM course WHERE cname=数据结构)SELECT sno,sname FROM stu

14、dent AS st WHERE NOT EXISTS(SELECT * FROM student_course AS sc WHERE score80 AND st.sno=sc.sno)AND EXISTS (SELECT * FROM student_course WHERE st.sno=sno)SELECT DISTINCT st.sno,sname FROM student AS st,student_course AS sc WHERE st.sno=sc.sno AND sc.sno NOT IN (SELECT DISTINCT sno FROM student_course

15、 AS sc WHERE score80 )四、实验作业题1SELECT语句的基本使用（1）根据实验2给出的数据表的结构，查询每个职工的职工号、姓名、缺勤天数信息。（2）查询职工号为001的职工的姓名和缺勤天数。（3）查询所有姓“李”的职工的职工号、缺勤理由。（4）找出所有缺勤天数在23天之间的职工号。2SELECT语句的高级查询使用（1）查询缺勤名称为“病假”的职工的职工号和姓名。（2）查找缺勤天数为2天的职工的职工号和缺勤名称。（3）查询“事假”的总人数。（4）求各缺勤类别的人数。（5）将各职工的考勤情况按缺勤天数由高到低排序。五、实验小结1通过实验我了解了表的更新操作，即数据的插入、修改和删除，对表数据的操作可以在企业管理器中进行，也可以由T-SQL语句实现。2掌握T-SQL中用于对表数据进行插入（INSERT）、修改（UPDATE）和删除（DELETE或TRANCATE TABLE）命令的用法。六、教师评语和成绩教师签名：年 月 日