数据库原理与应用 数据库查询操作

1、数据库原理与应用实验报告题目：实验四：数据库查询操作学号：1148028姓名：沈宇杰日期：2013/4/27实验预习情况：实验目的：掌握两种方式的数据查询方法：掌握简单查询、连接查询、嵌套查询等。查询工具的选择：对于简单的查询，更适合使用 SQL Server Management Studio 工具进行查询。：对于复杂的查询，更适合使用 SQL 语句进行查询。预习报告：使用SQL Server Management Studio 工具进行查询 ：查询数据表的全部数据在对象资源管理器中，右击需要查询的表，选择编辑前两百行，此时就显示了Student表中的全部数据了。：使用SELECT语句查询数

2、据表的数据在对象资源管理器中，右击需要查询的表，选择编辑前两百行命令，单击工具栏上的“显示SQL窗口”，此时子窗口将分为上下两部分，上面的部分可以输入 SQL 语句，下面的窗口会列出查询的结果。例如: 查询Student表中所有的信息。可以使用SQL语句为：SELECT TOP (200) Sno,Sname,Ssex, Sage,SdeptFROM Student：学习和使用QBE查询在对象资源管理器中，右击需要查询的表，选择编辑前两百行命令，选择QBE查询条件，执行时单击工具栏上的“执行”按钮。若单击工具按按钮“显示/隐藏SQL窗口”，则会在SQL窗格中自动生成对应的SQL语句。例：使用Q

3、BE查询数据表Student中大于等于20岁的女生的性别和年龄。直接在输出列表中选择要显示的属性名即可，在SQL窗口中会自动生成相关的SQL代码：实现多表查询在对象资源管理器中，右击需要操作的数据表，选择“编辑前两百行”命令，单击工具栏上的“显示/隐藏关系图窗格”，然后，单击工具栏按钮“添加表”，即可将数据表添加到关系图窗格中，单击字段左边的复选框选择要输出的数据列，执行时单击工具栏按钮“运行”即可。例：利用资源对象管理器查询所有男生的学生成绩，输出列包括：学号、姓名、课程号和成绩。自动生成的SQL代码为：SELECT TOP (200) Student.Sno, Student.Sname,

4、 Student.Sdept, SC.Grade, Course.CnameFROM Student INNER JOIN SC ON Student.Sno = SC.Sno INNER JOINCourse ON SC.Cno = Course.Cno使用SQL语句进行查询 简单查询：选择列：1.用SELECT语句来指定查询所需的列，多个列之间用“，”隔开例如：查询Student表中所有学生的学号、姓名、性别。SELECT Sno 学号,Sname 姓名,Ssex 性别FROM Student2.使用“*”来选取数据表的全部列例如：查询Student表中所有学生的基本情况。SELECT *

5、FROM Student3.在查询结果中增加计算列，还可以修改数据列的显示名称例如：查询Student表中所有学生的学号、姓名、性别、和出生年份。SELECT Sno 学号,Sname 姓名,Ssex 性别,YEAR(GetDate()-Sage 出生年份FROM Student选择行：1.使用WHERE子句，可以选择满足条件的部分记录例如：查询成绩在8590分之间的学生情况SELECT Sno,Cno,GradeFROM SCWHERE Grade=85 AND Grade= 200注意：SELECT子句后面使用统计函数以外的数据列，都必须出现在GROUP子句中。连接查询连接查询的分类：等值

6、连接查询、非等值连接查询、自连接查询、外部链接查询、复合条件连接查询等值连接查询1.使用WHERE子句指定连接条件例如：查询所有有2号课程成绩的学生的学号、姓名和成绩。SELECT Student.Sno,Sname,GradeFROM Student,SCWHERE Student.Sno = SC.Sno AND Cno = 22.在FROM子句中用JOIN连接符指定连接条件例如：查询所有有2号课程成绩的学号、姓名和成绩。SELECT Student.Sno,Sname,GradeFROM Student INNER JOIN SC ON Student.Sno = SC.SnoWHERE

7、 Cno = 2非等值连接查询在等值连接条件中不使用等号，而使用其他比较运算符，这就构成了非等值连接查询，可以使用的比较运算度有： = = 90)注释：子查询的 SELECT 语句中不能使用 ORDER BY 子句，ORDER BY 子句只能对最终查询结果排序。比较：查询涉及多个关系时，用嵌套查询逐步求解，层次清楚，易于构造，具有结构化程序设计的有点，还有利于提高查找效率。使用谓词EXSIT连接子查询例如：查询所有有课程成绩的学生的学号和姓名。SELECT Sno,SnameFROM StudentWHERE EXISTS(SELECT *FROM SC WHERE SC.Sno = Stud

8、ent.Sno)存储查询结果查询的信息往往需要保存下来，以便以后使用。再用 SELECT 语句查询数据时，可以设定将数据存储到一个新建的表中或变量中。将查询结果存储到表中：例如：将总分在200分以上学生的情况（学号、姓名、性别、总成绩）存储到数据表GradeList中，假设数据表GradeList不存在。SELECT A.Sno,Ssex,SUM(Grade) AS sumGradeINTO GradeListFROM Student A,SCWHERE A.Sno = SC.SnoGROUP BY A.Sno,Sname,SsexHAVING (SUM(SC.Grade)=200)这句语句的

9、语义是：将Student表中的Sno，Ssex和把SUM值作为新的数据项，存放到GradeList表中，且存放的数据需满足SUM（SC.Grade = 200）。将查询结果保存到变量中：例如：查询学号为200215121的学生的一号课的成绩，将其保存到变量Grade中DECLARE Grade INTSELECT Grade = GradeFROM SCWHERE Sno = 95001PRINT Grade注意：要将查询结果保存到变量中，只能将查询结果集中第一条记录的值赋给变量预习中遇到的问题或者是注意事项: 1.可以使用INNER JOIN 内连接来操作多个表,查询多表查询。2.子查询的

10、SELECT 语句中不能使用 ORDER BY 子句，ORDER BY 子句只能对最终查询结果排序。3. 若使用了聚簇函数则必须同时使用 GROUP BY 子句。4.层层嵌套的方式构造程序是 SQL 中“结构化”的含义所在。实验内容与完成情况： 第一题：按照数据库系统概论P82中的关系模式及数据，再次创建STUDENT、COURSE和SC表。1.每一张表都必须有主键2.需要使用外键的表必须使用外键。3.根据需要适当采用唯一值、检查、非空、和默认值约束。要求这四种约束在STUDENT、COURSE、SC三张表中至少使用一次。4.根据主键、外键、唯一值、检查、非空和默认值六中约束的特征，设计适当的

11、方案对STUDENT、COURSE、SC表中的这六中约束进行检查，方案自定。一种约束进行检查一次即可。：创建STUDENT表CREATE TABLE Student(Sno char(9) Primary Key,Sname char(8) NOT NULL,Ssex char(2) NOT NULL,Sage smallint NULL,Sdept char(2) NULL,) ：创建COURSE表 CREATE TABLE Course(Cno char(1) Primary Key,Cname char(10) Unique NOT NULL,Cpno char(1) NULL,Ccre

12、dit smallint NOT NULL Default 0,Constraint Ccredit_FK Foreign Key(Cpno) References Course (Cno) )：创建SC表Create Table SC(Sno char(9) NOT NULL Default ,Cno char(1) NOT NULL Default ,Grade smallint NOT NULL Default 0,Constraint Sno_Cno_PK Primary Key(Sno,Cno),Constraint Sno_FK Foreign Key (Sno) Referenc

13、es Student (Sno),Constraint Cno_FK Foreign Key (Cno) References Course (Cno)根据自定方案，对六种检查约束进行触发测试：先进行主键的测试操作的表：Student测试主键：Sno测试方法：输入两次相同的Sno值以触发主键约束代码：INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES(200215121,天然,女,20,CS)此句输入了一个重复的Sno名200215121，从而触发了主键约束。错误提示为：消息2627，级别14，状态1，第1 行违反了PRIMARY KEY

14、 约束PK_Student_CA1FE46424927208。不能在对象dbo.Student 中插入重复键。：外键的触发检查测试的表：SC测试外键：Sno测试方法：通过向SC中插入学号，且此学号没有在Student表中登记过，以此触发外键约束代码：INSERT INTO SC(Sno,Cno,Grade) VALUES(200215129,1,20)错误提示为：消息547，级别16，状态0，第1 行INSERT 语句与FOREIGN KEY 约束Sno_FK冲突。该冲突发生于数据库mydb，表dbo.Student, column Sno。：唯一值约束的测试测试的表：Course测试的唯一值

15、约束：Cname (ASC)测试方法：向Course表中插入，除了Cname相同，其他项均不同的元祖以排除其他项干扰，来进行唯一值检查约束。代码：INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES(1,离散数学,NULL,7)错误提示为：消息2627，级别14，状态1，第1 行违反了PRIMARY KEY 约束PK_Course_C1FE637330F848ED。不能在对象dbo.Course 中插入重复键。：检查约束测试的表：Student测试的列（检查约束）：Sage测试方法：因为性别要在男或女中选择，所以输入一个中性性别来触发检查约束代码：

16、INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES(200215129,小凡,中,20,CS)错误提示：消息547，级别16，状态0，第1 行INSERT 语句与CHECK 约束SsexCK冲突。该冲突发生于数据库mydb，表dbo.Student, column Ssex。：非空检查测试的表：SC测试的列（非空约束）：Grade 测试方法：在保证Sno，Cno满足一切约束条件的情况下，不输入Grade项，以检查非空约束。代码：INSERT INTO SC(Sno,Cno) VALUES(200215121,5) 错误提示：消息515，级

17、别16，状态2，第1 行不能将值NULL 插入列Grade，表mydb.dbo.SC；列不允许有Null 值。INSERT 失败。 ：默认值约束测试的表：SC测试的列（默认值约束）：Grade测试方法：增加Grade列的默认值为0代码：INSERT INTO SC(Sno,Cno) VALUES(200215121,5)错误提示：无，顺利插入，但是在编辑前200行中，显示Grade的值为0第二题：按照数据库系统概论P82及P75的关系模式及数据，实现以下查询。：找出所有供应商的姓名和所在的城市SELECT SNO,CITYFROM S：找出所有零件的名称、颜色、重量SELECT PNAME,W

18、EIGHTFROM P：查询供应工程J1零件的供应商号码SNOSELECT DISTINCT SNOFROM SPJWHERE JNO = J1：查询所用红色零件的工程名称SELECT DISTINCT JNOFROM SPJWHERE PNO IN(SELECT PNOFROM PWHERE COLOR = 红)：查询每个工程的信息及相应的供应信息（包括列出尚未被供应零件的那些工程）SELECT DISTINCT J.*,S.*FROM J LEFT OUTER JOIN SPJ A ON (J.JNO = A.JNO),S INNER JOIN SPJ B ON(S.SNO = B.SNO)：查询先行课的学分及相应的供应信息SELECT A.Cno,A.CnameFROM Course A,Course BWHERE A.Cno = B.Cpno AND B.Ccredit = 4：查询选修3