实验1数据库定义实验

一、实验 1.1 数据库定义实验

（一）实验目的

理解和掌握数据库 DDL 语言，能够熟练地使用 SQL DDL 语句创建、修改和
删除数据库、模式和基本表。

（二）实验内容和要求

理解和掌握 SQL DDL 语句的语法，特别是各种参数的具体含义和使用方法；
使用 SQL 语句创建、修改和删除数据库、模式和基本表。掌握 SQL 语句常见语
法错误的调试方法。

（三）实验过程

（1）定义和删除数据库
创建一个数据库：create database 数据库名；
显示现有的数据库：show databases;
删除一个数据库：drop database 数据库名；

（2）定义模式
创建模式： create schema 模式名
show schemas; 显示所创建的模式：
删除模式： drop schema 模式名;

（3）定义删除基本表
进入数据库：use 数据库名;
创建数据库表：create table 表名(列声明);
以创建students表为例，表中存放学号(Sno)、姓名(Sname)，性别(Ssex)，年龄(Sage)，院系 (Sdept)

用show tables; 显示创建的表名；
用describe 表名; 显示创建表的具体信息。

同理创建以下两个表：

显示表的信息：select * from 表名

删除表：drop table 表名;

二、实验 1.2 数据基本查询实验

（一）实验目的

掌握 SQL 程序设计基本规范，熟练运用 SQL 语言实现数据基本查询，包括单表查询、分组统计查询和连接查询。

（二）实验内容和要求

针对某个数据库设计各种单表查询 SQL 语句、分组统计查询语句；设计单
个表针对自身的连接查询，设计多个表的连接查询。理解和掌握 SQL 查询语句
各个子句的特点和作用，按照 SQL 程序设计规范写出具体的 SQL 查询语句，并
调试通过。说明：简单地说，SQL 程序设计规范包含 SQL 关键字大写、表名、属性名、
存储过程名等标识符大小写混合、SQL 程序书写缩进排列等编程规范。

（三）实验过程

1. 单表查询(实现投影操作)

select sno,sname from student;

2. 单表查询(实现选择操作)

select sname from student where sdept='CS' and sage<19;

3. 分组统计查询：使用group by语句

select sdept,avg(sage) from student group by sdept;

4. 单表自身连接查询
   查询必修课的先修课：

select first.cno,second.cno from course first,course second where first.cpno=second.cno;

5. 两表连接查询(外连接)

select student.*,cno,grade from student left outer join sc on (student.sno=sc.sno);

三、实验 1.3 数据高级查询实验

（一）实验目的

掌握 SQL 嵌套查询和集合查询等各种高级查询的设计方法等。

（二）实验内容和要求

针对自定义数据库，正确分析用户查询要求，设计各种嵌套查询和集合查
询。

（三）实验过程

1.非相关子查询：内层独立查询，不涉及与外层相关的子查询。

select sname from student where sno in (select sno from sc where cno='1');

2. 相关子查询：内层查询需要依靠外层的某些参数为限定条件。
   找出每个学生超过他自己选修课程平均分的课程号

select sno,cno from sc x where grade>=(select avg(grade) from sc y where y.sno=x.sno);

3. 带有all的查询：如果表达式的值与子查询结果所有值都满足关系。
   查询计算机科学系中学生年龄最小的学生姓名和年龄：

select sname,sage from student where sage<=all(select sage from student where sdept='cs') and sdept='cs';

Some查询：如果表达式的值与子查询结果某一个值满足关系
4. 带有exists的子查询：
查询所有选修了2号课程的学生的姓名

select sname from student where exists (select * from sc where sno=student.sno and cno='2');

查询选修了全部课程的学生的姓名

select sname from student where not exists (select * from course where not exists (select * from sc where sno=student.sno and cno=course.cno));

5. 集合查询(并)
   查询计算机科学系的学生与年龄小于19岁学生的交集

select * from student where sdept='cs' union select * from student where sage<19;

交：intersect 差：except

四、实验 1.4 数据更新实验

（一）实验目的

熟悉数据库的数据更新操作，能够使用 SQL 语句对数据库进行数据的插入、
修改、删除操作。

（二）实验内容和要求

针对自定义数据库设计单元组插入、批量数据插入、修改数据和删除数据
等 SQL 语句。理解和掌握 INSERT、UPDATE 和 DELETE 语法结构的各个组成成分， 结合嵌套SQL子查询，分别设计几种不同形式的插入、修改和删除数据的语句， 并调试成功。

（三）实验过程

(1) INSERT基本语句(插入全部列的数据)
插入一个学生信息：

insert into student values('201700119','张成民','男','18','CS');

(2) INSERT基本语句(插入部分列的数据)

insert into student(sno,sname) values('201700120','旺旺');

(3) 插入子查询结果：

create table dept_age(sdept char(15),avg_age smallint);
insert into dept_age(sdept,avg_age) select sdept,avg(sage) from student group by sdept;

(4) UPDATE语句(修改部分记录的部分列值)

update student set sage=22 where sno='201700120';

(5) UPDATE语句(修改一列)
将所有计算机系学生成绩置零：

update sc set grade=0 where sno in (select sno from student where sdept='CS');

(6) DELETE基本语句(删除给定条件的所有记录)
删除一条数据：

delete from student where sno='201700121';

带子查询的删除：

delete from sc where sno in (select sno from student where sdept='EE');

五、实验 1.5 视图

（一）实验目的

熟悉SQL语言有关视图的操作，能够熟练使用 SQL语句来创建需要的视图，
定义数据库外模式，并能使用所创建的视图实现数据管理。

（二）实验内容和要求

针对给定的数据库模式，以及相应的应用需求，创建视图和带 WITH CHECK
OPTION 的视图，并验证视图 WITH CHECK OPTION 选项的有效性。理解和掌握视 图消解执行原理，掌握可更新视图和不可更新视图的区别。

（三）实验过程

(1) 创建视图：
create view 视图名称(视图列名)
AS select 表中某些列名
from 表名
where 限定条件
create view CS_student as select sno,sname,sage from student where sdept=‘cs’ with check option;

(2) 查看视图

select * from cs_student;

(5) 更新视图

update cs_student set sage=20 where sno='201700111';

(6)with check option验证

create view seniorstu(sno,sage) as select sno,sage from student where sage<='19' with check option;
insert into seniorstu values('201215127',18);
insert into seniorstu values('201215127',21);

第一条能插入，因为sage=18<=19，而第二条不能插入，因为sage=21>19。
(7) 删除视图

 drop view seniorstu;