SQLite3 实验3 SQLite中的高级SQL语句应用0526

SQLite3实验指导书

——2014版本

实验3 SQLite中的高级SQL

实验目的：

1．熟练掌握在数据库中插入、修改和删除数据的操作方法，加深对标准SQL更新语句的理解。

2．了解SQLite中用约束来维护数据完整性的机制。掌握在SQLite中如何创建、使用及删除约束的方法。

3．掌握在SQLite中创建、删除视图的SQL语句的用法，加深理解视图的用途。 4．掌握在SQLite中创建、删除索引的SQL语句，加深理解索引的用途。

5．掌握在SQLite中创建、删除触发器的SQL语句的用法，加深理解触发器的用途。 6．掌握在SQLite中创建事务的SQL语句的用法，加深理解事务的各种冲突解决方案。

实验内容：

1．基于实验一创建的教学管理JXGL数据库，参考教材数据更新的SQLite操作实例，实现数据的插入、修改和删除操作。

2．基于实验一创建的供应系统GYXT数据库，实现数据的插入、修改和删除操作。 3．基于实验一创建的教学管理JXGL数据库，实现约束的创建、查看和删除操作。 4．创建人事关系RSGX数据库及其中的职工数据表和部门数据表，并实现完整性约束条件的定义。

5．基于实验一创建的教学管理JXGL数据库及供应系统GYXT数据库，参考教材视图的SQL操作实例，实现视图的创建、删除、查询操作。

6．基于JXGL数据库，学习如何创建索引。

7．基于JXGL数据库，创建触发器，体会触发器的作用。

8．基于JXGL数据库，参考教材上事务的应用实例，掌握事务的创建及各种冲突解决方案的用法。

实验步骤：

1．在教学管理JXGL数据库中进行如下操作：

(1) 将一个新学生记录（学号：200215126；姓名：陈冬；性别：男；所在系：IS；年龄：18）插入STUDENT表中。

INSERT INTO student VALUES(200215126,'陈冬','男','18','IS');

(2) 数据的插入操作：插入一条选课记录，学号为200215126的学生选修了1号课程。

(3) 对每门课程，求学生的平均分数，并把结果存入数据库。

(4) 将学生200215126转到数学系。

1

(5) 所有学生成绩乘系数0.8。

(6) 删除200215126学生的记录。

(7) 删除数学系所有学生的选课记录。

2．在供应系统GYXT数据库中进行如下操作：

(1) 往S，P，J，SPJ表中插入部分数据（此题实验一已完成）

(2) 将供应商序号为S5的名称改为宏民厂。

(3) S表中删除供应商序号为S5的记录，考虑对SPJ表的相应操作。

(4) 在S表中插入一条供应商信息：(S6,华天,深圳)。

(5) 把全部红色零件的颜色改为粉红色。

(6) 将S1供应给J1的零件P1改为由S2供给。

(7) 删去全部蓝色零件及相应的SPJ记录。

3．完整性约束

约束是SQLite提供的自动强制数据完整性的一种方法，它是通过定义列的取值规则来维护数据的完整性。常用的约束是NOT NULL（非空）、UNIQUE（唯一性）、DEFALT（默

2

认）、CHECK（检查）、PRIMARY KEY（主键）、FOREIGN KEY（外键）等约束。

在教学管理JXGL数据库中进行如下操作： (1) 建立一个CITY表（CityNo,CityName），将CityNo指定为主键。 用SQL语句创建主键，可使用CREATE TABLE命令完成： CREATE TABLE CITY

( CityNo INTEGER PRIMARY KEY, CityName VARCHAR(20) NOT NULL);

(2) 针对JXGL数据库中的CITY表，为其“CityName”创建惟一约束。

先删除已经已存的CITY表，重新修改上面的CREATE TABLE语句，增加创建惟一约束的内容，SQL语句如下：

DROP TABLE CITY; CREATE TABLE CITY

( CityNo INTEGER PRIMARY KEY,

CityName VARCHAR(20) UNIQUE NOT NULL );

(3) 为JXGL数据库中的SC表，为其“Sno”和“Cno”创建外键约束，为其“GRADE”创建检查取值范围为[0,100]的约束。

先删除已经已存的SC表，重新修改原来的CREATE TABLE语句，增加创建check约束的内容，SQL语句如下：

Drop table SC;

CREATE TABLE sc(sno integer references student(sno),cno integer references course(cno),Grade integer,primary key(sno,cno) ,CHECK(GRADE>=0 AND GRADE<=100));

(4) 为JXGL数据库中的STUDENT表，为其“Ssex”创建默认约束?M?。

4．创建人事关系RSGX数据库，并定义职工和部门两个关系模式：

职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码； 部门（部门号，名称，经理名，电话），其中部门号为主码； 要求在模式中完成以下完整性约束条件的定义： (1) 定义每个模式的主码； (2) 定义参照完整性；

(3) 定义职工年龄不得超过60岁。

5．在教学管理JXGL数据库中进行如下操作：

(1) 建立一个名为“CS_Student”的计算机系学生视图，并要求进行修改和插入操作时仍须保证该视图只有计算机系的学生。（注：计算机系的Sdept值为“CS”；在使用该视图时，将显示Student表中的所有字段。）

输入下列SQL语句，然后运行，输出结果。 CREATE VIEW CS_Student AS

SELECT Sno,Sname,Ssex,Sage

3

FROM Student

WHERE Sdept='CS';

(2) 建立计算机系选修了1号课程的学生的视图CS_S1。（注：使用上题建立的视图CS_Student。）

(3) 删除视图CS_S1。

(4) 在计算机系学生视图CS_Student中找出选修了“数据库”课程且成绩在90分以上的学生。

sqlite> Select vs.sno,sname from cs_student vs,sc,course c where vs.sno=sc.sno a nd c.cno=sc.cno and c.cname='数据库' and grade>90;

6．在供应系统GYXT数据库中进行如下操作：

(1) 创建视图VSJ，它记录了给“三建”工程项目的供应零件的情况，包括供应商号、零件号和零件数量。

(2) 对VSJ视图查询S1供应商的供货情况。

7．建立索引可加快表的查询速度。DBMS一般会在主键列及唯一性列上自动建立索引。

注意：索引是由表的创建者创建的，但表的维护及使用是由DBMS自动完成的，用户及DBA无法干预。

(1) 创建1张表lx，并以其为基础创建唯一性索引。 Create table lx(a text, b text);

Create unique index idx_lx on lx(a,b); Insert into lx values(?010?,?beijing?); Insert into lx values(?025?,?nanjing?); Select * from lx;

Insert into lx values(?010?,?beijin?); 此语句出错，为什么？

(2) 在JXGL数据库中的STUDENT表的sname字段上创建不区分大小写的索引。 Create index idx_student_sname on student(sname collate nocase);

8．基于示例数据库foods，学习触发器的创建及应用。

1）触发器练习1

4

本文来源：https://www.bwwdw.com/article/mhqd.html