数据库实验报告

数据库设计实验报告数据库设计实验报告 学院学院 计算机科学与软件学院 班级班级 姓名姓名 学号学号 1 实验一实验一 实验实验 1 1 数据定义数据定义 一 实验目的一 实验目的 熟悉 SQL 的数据定义语言 能够熟练的使用 SOL 语句来创建和更改基本表 创建和取消索引 二 实验内容二 实验内容 本实验的主要内容包括 使用 CREATE 语句创建基本表 更改基本表的定义 增加列 删除列 修改列的数据类型 创建表的升降序索引 取消表 表的索引或表的约束 三 习题三 习题 1 AGENTS AID ANAME ITY PERCEN 数据库表 PRODUCTS PID PNAME 其中 CID AID PID 分别是各表的主键 具有唯一性约束 创建 CUSTOMERS 表 CREATE TABLE CUSTOMERS CID INT NOT NULL CNAME CHAR 8 CITY CHAR 8 DISCNT CHAR 8 PRIMARY KEY CID 创建 AGENTS 表 CREATE TABLE AGENTS AID INT NOT NULL ANAME CHAR 8 CITY CHAR 8 PERCEN CHAR 8 PRIMARY KEY AID 创建 PRODUCTS 表 2 CREATE TABLE PRODUCTS PID INT NOT NULL ANAME CHAR 8 PRIMARY KEY PID 2 创建数据库表创建数据库表ORDERS ORDNA MANTH CID AID PID QTY DOLLARS 其中其中 ORDNA是主键 具有唯一性约束 是主键 具有唯一性约束 CID AID PID分别是外键引用自表分别是外键引用自表 CUSTOMERS AGENTS PRODUCTS CREATE TABLE ORDERS ORDNA INT NOT NULL MONTH INT CID INT AID INT PID INT QTY CHAR 8 DOLLARS CHAR 8 PRIMARY KEY ORDNA FOREIGN KEY CID REFERENCES CUSTOMERS FOREIGN KEY AID REFERENCES AGENTS FOREIGN KEY PID REFERENCES PRODUCTS 3 3 增加数据库表PRODUCTS三个属性列 CITY QUANTITY PRICE ALTER TABLE PRODUCTS ADD CITY CHAR 8 ALTER TABLE PRODUCTS ADD QUANTITY CHAR 8 ALTER TABLE PRODUCTS ADD PRICE CHAR 8 4 为以上四个表建立各自的按组建增序排列的索引 CREATE INDEX XCNO ON CUSTOMERS CID CREATE INDEX XCNO ON AGENTS AID CREATE INDEX XCNO ON PRODUCTS PID CREATE INDEX XCNO ON ORDERS ORDNA 5 取消 4 建立的四个索引 DROP INDEX AGENTS XCNO DROP INDEX CUSTOMERS XCNO DROP INDEX PRODUCTS XCNO DROP INDEX ORDERS XCNO 实验实验 1 21 2 数据查询数据查询 一 实验目的 一 实验目的 熟悉 SQL 语句的数据查询语言 能够使用 SQL 语句对数据库进行单表查询 链 接查询 嵌套查询和统计查询 二 实验内容二 实验内容 实验内容主要是对数据库进行查询操作 包括如下四类查询方式 1 单表查询 查询的目标表达式为所有列 指定的列或指定的列的运算三种不同 使用 DISTINCT 保留字消除重复列 对查询结果排序和分组 集合分组使用集函数进行各项统计 2 连接查询 笛卡尔连接和等值连接 自连接 外连接 复合条件连接 多表连接 3 嵌套查询 通过实验验证对子查询的两个限制条件 体会相关子查询和不相关子查询的不同 考察四类谓词的用法 包括 第一类 IN NOT IN 第二类 带有比较运算符的查询 第三类 SOME ANY 或 ALL 谓词的子查询 查询最大值和最小值的情况 第四类 带有 EXISTS 谓词的子查询 实现 所有 等情况 4 4 集合运算 使用保留字 UNION 进行集合或运算 采用逻辑运算符 AND 或 OR 来实现集合交和减运算 三 习题三 习题 1 STUDENTS 2 TEACHERS 3 COURSES 4 CHOICES 1 查询全部课程的详细记录 结果 5 2 查询所有有选课的学生的编号 结果 3 查询课时60 GROUP BY SID HAVING COUNT CID 2 8 统计各个学生的选修课程数目和平均成绩 select SID COUNT CID AVG SCRCE FROM CHOICES GROUP BY SID 9 SELECT STUDENTS SID SNAME FROM STUDENTS CHOICES COURSES WHERE STUDENTS SID CHOICES SID AND CHOICES CID COURSES CID AND COURSES cname java 7 10 SELECT CHOICES CID CHOICES SCRCE FROM CHOICES STUDENTS WHERE sname SSSTH AND CHOICES SID STUDENTS sid SELECT CID SCRCE FROM CHOICES WHERE SID IN SELECT STUDENTS SID FROM STUDENTS WHERE sname sssTH 11 SELECT C1 CNAME FROM COURSES AS C1 COURSES AS C2 WHERE C1 hour C2 hour AND Came C 12 SELECT SID SNAME FROM STUDENTS WHERE sid IN SELECT C1 SID FROM CHOICES AS C1 CHOICES AS C2 WHERE C1 SCRCE C2 SCRCE AND C1 CID C2 CID AND 8 C2 SID SELECT SID FROM STUDENTS WHERE sname ZNKOO AND C1 CID SELECT CID FROM COURSES WHERE cname C 13 SELECT SNAME FROM STUDENTS WHERE GRADE IN SELECT GRADE FROM STUDENTS WHERE SID IN 883794999 850955252 14 SELECT SNAME FROM STUDENTS WHERE SID NOT IN SELECT SID FROM CHOICES WHERE CID SELECT CID FROM COURSES WHERE CNAME JAVA 15 SELECT FROM COURSES WHERE HOUR ALL SELECT HOUR FROM COURSES 9 16 SELECT CHOICES TID CHOICES CID FROM CHOICES WHERE TID SELECT TID FROM TEACHERS WHERE salary SELECT MAX SALARY FROM TEACHERS WHERE salary IN SELECT SALARY FROM TEACHERS WHERE tid IN SELECT TEACHERS tid FROM CHOICES TEACHERS WHERE CHOICES TID TEACHERS tid 17 SELECT SID FROM CHOICES WHERE SCRCE SELECT MAX SCRCE FROM CHOICES WHERE CID SELECT CID FROM COURSES WHERE CNAME ERP 18 SELECT CNAME FROM COURSES WHERE CID NOT IN SELECT CID FROM CHOICES 10 19 SELECT CNAME FROM COURSES WHERE CID SOME SELECT CID FROM CHOICES WHERE TID SOME SELECT TID FROM COURSES CHOICES WHERE CNAME UML AND COURSES CID CHOICES CID 20 SELECT SNAME FROM STUDENTS WHERE SID IN SELECT SID FROM CHOICES WHERE CID IN SELECT CID FROM CHOICES WHERE TID 200102901 21 SELECT SID FROM CHOICES COURSES WHERE COURSES CID CHOICES CID AND COURSES CNAME DATABASE UNION SELECT SID FROM CHOICES COURSES WHERE COURSES CID CHOICES CID AND COURSES CNAME UML 11 22 SELECT X SID FROM CHOICES AS X CHOICES AS Y WHERE X CID SELECT CID FROM COURSES WHERE CNAME DATEBASE AND Y CID SELECT CID FROM COURSES WHERE CNAME UML AND X SID Y SID 23 SELECT X SID FROM CHOICES AS X CHOICES AS Y WHERE X CID SELECT CID FROM COURSES WHERE CNAME DATEBASE AND X SID Y SID AND NOT Y CID SELECT CID FROM COURSES WHERE CNAME UML 实验实验 1 31 3 数据更新数据更新 一 实验目的 一 实验目的 熟悉数据库的数据更新操作 能够使用 SQL 语句对数据库进行数据的插入 更 新 删除操作 二 实验内容二 实验内容 在本实验中 主要的内容是如何用 SQL 语句对数据进行更新 使用 INSERT INTO 语句插入数据 包括插入一个元祖或将子查询的结果插入到 数据库的两种方式 使用 INSERT INTO 语句 产生一个新表并插入数据 使用 UPDATE 语句可以修改指定表中满足 WHERE 子句条件的元祖 有三种修改方 12 式 修改某一个元祖的值 修改多个元祖的值 带子查询的修改语句 使用 DELETE 语句删除数据 删除某一个元祖的值 删除多个元祖的值 带子查 询的删除语句 三 习题三 习题 1 insert students sid sname values 800022222 wanglan 2 insert teachers tid tname email salary values 200001000 lxl s4zrnet 3024 3 update teachers set salary 4000 where tid 200010493 4 update teachers set salary 2500 where salary1998 14 9 delete from students where sid not in select sid from choices 10 delete from choices where scrce1998 4 SELECT FROM VIEWS WHERE SNAME UXJOF 5 SELECT SID SCRCE FROM VIEWC WHERE CNAME UML 16 6 INSERT INTO SI VALUES 6000001 LILY 2001 8 UPDATE VIEWS SET SCRCE SCRCE 5 9 DROP VIEW VIEWS DROP VIEW VIEWC DROP VIEW SI 实验实验 1 51 5 数据控制数据控制 一 实验目的 一 实验目的 熟悉 SQL 的数据控制功能 能够使用 SQL 语句来向用户授予和收回权限 二 实验内容 二 实验内容 1 使用 GRANT 语句来对用户授权 对单个用户或单个用户授权 或使用保留 字 PUBLIC 对所有用户授权 对不同的操作对象包括数据库 视图 基本表等进 行不同权限的授权 2 使用 WITH GRANT OPTION 子句授予用户传播该权限的权利 3 当在授权时发生循环授权 考察 DBS 能否发现这个错误 如果不能 结合 取消权限操作 查看 DBS 对循环授权的控制 4 使用 REVOKE 子句收回权限 取消授权的级联反应 三 习题 三 习题 1 GRANT SELECT ON STUDENTS TO PUBLIC 2 GRANT SELECT UPDATE ON COURSES 17 TO PUBLIC 3 GRANT SELECT UPDATE SALARY ON TEACHERS TO user1 WHERE GRANT OPTION 4 GRANT SELECT UPDATE SCRCE ON CHOICES TO user1 WHERE GRANT OPTION 5 CREATE VIEW TV AS SELECT TID TNAME EMAIL FROM TEACHERS GRANT SELECT ON TV TO USER2 6 GRANT SELECT ON TEACHERS TO USER2 WITH GRANT OPTION 7 GRANT SELECT ON TEACHERS TO USER3 WITH GRANT OPTION GRANT SELECT ON TEACHERS TO USER2 WITH GRANT OPTION 8 REVOKE SELECT ON TEACHERS FROM USER1 CASCADE 9 REVOKE SELECT UPDATE ON COURSES FROM USER1 USER2 实验实验 1 61 6 空值和空值的处理空值和空值的处理 一 实验目的一 实验目的 认识 NULL 值的特殊含义 了解空值和空集对于数据库的数据查询操作 特别 是空值在条件表达式中与其他的算术运算符或逻辑运算符的运算中 空集作为 嵌套子查询的返回结果时候的特殊性 能够熟练使用 SQL 语句来进行与空值空 18 集的相关操作 二 实验内容二 实验内容 1 在查询的目标表达式中包含空值的运算 2 在查询条件中与比较运算符的运算结果 3 使用 IS NULL 或 IS NOT NULL 来判断元组是否为空值 4 对存取空值的列按值进行 ORDER BY 排序 5 使用保留字 DISTINCT 对空值的处理 区分数据库的多种取值与现实中的 多种取值的不同 三 习题三 习题 1 SELECT CID HOUR 18 FROM COURSES 2 查询选修c 的总人数 SELECT COUNT FROM CHOICES WHERE CID SELECT CID FROM COURSES WHERE CNAME C 成绩合格的人数 SELECT COUNT FROM CHOICES WHERE CID SELECT CID FROM COURSES WHERE CNAME C AND SCRCE 60 成绩不合格的人数 SELECT COUNT FROM CHOICES 19 WHERE CID SELECT CID FROM COURSES WHERE CNAME C AND SCRCE 60 3 SELECT CID SCRCE FROM CHOICES WHERE CID SELECT CID FROM COURSES WHERE CNAME C ORDER BY SCRCE 4 SELECT CID SCRCE FROM CHOICES WHERE CID SELECT CID FROM COURSES WHERE CNAME C ORDER BY SCRCE 5 SELECT DISTINCT GRADE FROM STUDENTS GROUP BY GRADE 20 6 select AVG SCRCE COUNT MAX SCRCE MIN SCRCE FROM CHOICES GROUP BY CID 7 SELECT MAX GRADE FROM STUDENTS 8 SELECT COUNT FROM STUDENTS TEACHERS WHERE STUDENTS SID TEACHERS tid 实验二实验二 21 实验实验 2 1 实体完整性实体完整性 一一 实验目的实验目的 学习试实体整性的建立 以及实践违反实体完整性的结果 二二 习题程序习题程序 实验实验 2 1 实体完整性实体完整性 习题 1 实验中用到的 SQL 语句 create table calss classID varchar 4 className varchar 10 calssDepartment varchar 50 constraint PK Class primary key classID 执行结果 2 实验中用到的SQL语句 set xact abort on begin transaction T4 insert into calss classID className calssDepartment values 0001 01csc cs begin transaction T5 insert into calss classID className calssDepartment values 0001 01csc cs commit transaction T5 commit transaction T4 执行结果 22 T4的插入也不成功 所以执行查询后为空 实验实验 2 2 参照完整性参照完整性 一一 实验目的实验目的 学习建立外键 以及利用 FOREIGN KEY REFERENCES 字句以及各种约束保证参照完整 性 二二 习题程序习题程序 1 实验中用到的 SQL 语句 备注 对于实验多要求的将 alter Table 语句把 On delete cascade 改为 ON delete restrict 实验 非常不成功 于是我又直接把SelectCourse删除直接用下面实验中用到的 SQL 语句建 表 Create table SelectCourse sno Char 5 References Stu Union sno on delete restrict cno char 4 References Course cno on delete restrict grade int constraint PK SC Primary Key sno cno 结果提示 执行结果 数据库不允许 students 表以及 course 表中对应的元组 2 执行结果 数据库不允许 students 表以及 course 表中对应的元组 3 实验中用到的 SQL 语句 create table help 23 stu id char 8 stu name varchar 20 help id char 8 not null constraint PK help Primary key stu id alter table help add constraint FK help foreign key help id references help stu id 执行结果 4 实验中用到的SQL语句 create table leader sid char 9 sname varchar 20 myleader char 9 constraint PK leader primary key sid create table monitor sid char 9 sname varchar 20 mymonitor char 9 constraint PK monitor primary key sid constraint FK monitor foreign key mymonitor references leader sid alter table leader add constraint FK leader foreign key myLeader references monitor sid 执行结果 实验实验 2 3 用户自定义完整性用户自定义完整性 一一 实验目的实验目的 学习自定义约束 并实践自定义完整性 利用 sql 查询分析器用短语 NOTNULL UNIQUE CHECK 保证定义完整性 二二 习题程序习题程序 24 用到的辅助实验中用到的 SQL 语句 create Table worker number char 5 name char 8 constraint U1 unique sex char 1 Sage int constraint U2 check Sage 0 实验结果 2 实验中用到的 SQL 语句 Create rule rule sage as Value between 1 and 100 go exec sp bindrule rule sage worker sage 实验结果 25 实验实验 2 4 触发器触发器 一一 实验目的实验目的 通过实验目的使学生加深对数据完整性的理解 学会创建和使用触发器 二二 习题程序习题程序 1 实验中用到的 SQL 语句 use School go Create trigger T4 on worker for insert as if select sage from inserted select max sage from worker begin print 年龄不够大 rollback Transaction end 实验结果 2 实验中用到的 SQL 语句 create trigger T5 on worker for update as if select sage from inserted select sage from deleted begin print 年龄只能增不能降 谁定的规则 我要返老还童 rollback Transaction end 实验结果 26 27 实验三实验三 实验实验 3 1