SQLite3实验指导书
——2014版本
实验3SQLite中的高级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转到数学系。
(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（默
认）、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>=0AND GRADE<=100));
(4)为JXGL数据库中的STUDENT表，为其“Ssex”创建默认约束’M’。
4．创建人事关系RSGX数据库，并定义职工和部门两个关系模式：
职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码；
部门（部门号，名称，经理名，电话），其中部门号为主码；
sql中delete用法要求在模式中完成以下完整性约束条件的定义：
(1)定义每个模式的主码；
(2)定义参照完整性；
(3)定义职工年龄不得超过60岁。
5．在教学管理JXGL数据库中进行如下操作：
(1)建立一个名为“CS_Student”的计算机系学生视图，并要求进行修改和插入操作时仍须保证该视图只有计算机系的学生。（注：计算机系的Sdept值为“CS”；在使用该视图时，将显示Student表中的所有字段。）
输入下列SQL语句，然后运行，输出结果。
CREATE VIEW CS_Student
AS
SELECT Sno,Sname,Ssex,Sage
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 co=sco and came='数据库'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 i ndex 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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。