南邮MySQL实验报告二_用create database命令创建电影数据库(moviedb)。

约束、视图、索引与存储过程

一、实验目的和要求

1. 通过上机实践，熟悉约束的创建，深入理解约束的作用。

2. 掌握视图和索引的创建。

3. 熟练掌握存储过程的设计和使用方法。
1
2
3
4
5

二、实验环境(实验设备)

硬件：微机

软件：Windows xp/Windows 10和MySQL

素材：实验数据
1
2
3
4
5

三、实验内容

1.用create database命令创建电影数据库(以自己的学号命名)。

使用语句1：

CREATE DATABASE B1806xxxx;
1

2.在电影数据库中用create table 命令创建如下5个关系模式。

Movies (title,year,length,gender,studioName,producerC)

使用语句2：

CREATE TABLE Movies

(

   title CHAR(20),

   YEAR INT,

   LENGTH INT,

   gender CHAR(10),

   studioName CHAR(30),

   producerC INT,

   PRIMARY KEY(title,YEAR)

);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

MovieStar (name,address,gender,birthday)

使用语句3：

CREATE TABLE MovieStar
(
    
	NAME CHAR(12),
	
	address CHAR(30),
	
	gender CHAR(2),
	
	birthdate DATE,
	
	PRIMARY KEY(NAME)
    
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

StarsIn(Title,Year,starName)

使用语句4：

CREATE TABLE StarsIn
(
    
	movieTitle CHAR(20),
	
	movieYear INT,
	
	starName CHAR(12),
	
	PRIMARY KEY(movieTitle,movieYear,starName)
    
);
1
2
3
4
5
6
7
8
9
10
11
12

MovieExec(NAME,address,cert,netWorth)

使用语句5：

CREATE TABLE MovieExec
(
    
	NAME CHAR(12),
	
    address CHAR(30),
	
    cert INT,
	
    netWorth INT,
	
    PRIMARY KEY(cert)
);
1
2
3
4
5
6
7
8
9
10
11
12
13

Studio(NAME,address,presC)

使用语句6：

CREATE TABLE Studio

(
   
    NAME CHAR(30),
   
    address CHAR(30),
   
    presC INT,
   
    PRIMARY KEY(NAME)
    
);
1
2
3
4
5
6
7
8
9
10
11
12
13

3. 用alter table命令修改movies关系模式，增加引用完整性约束cproducerc，要求movies表中的producerC必须是在MovieExec表中已有的导演。若违反了此约束，则拒绝更新操作。

使用语句7：

ALTER TABLE movies ADD CONSTRAINT cproducerc FOREIGN KEY (producerC) REFERENCES movieexec(cert) ON UPDATE NO ACTION
1

4.用alter table命令修改StarsIn关系模式，增加引用完整性约束cmovies，要求StarsIn表中的电影名称和年份必须是在movies表中已有的电影名称和年份。若违反了此约束，则拒绝更新操作。

使用语句8：

ALTER TABLE starsin ADD CONSTRAINT cmovies FOREIGN KEY (movietitle,movieyear) REFERENCES movies(title,YEAR) ON UPDATE NO ACTION 
1

5.用alter table命令修改movies关系模式，增加完整性约束cyear，要求电影年份不能是1915年以前的。

使用语句9：

ALTER TABLE movies ADD CONSTRAINT cyear CHECK(YEAR>=1915)
1

6.用alter table命令修改movies关系模式，增加完整性约束clength，要求电影长度不能小于60也不能多于250。

使用语句10：

ALTER TABLE movies ADD CONSTRAINT clength CHECK(LENGTH BETWEEN 60 AND 250)
1

7.设计数据对3~6的内容进行验证。

对3的验证：

先在 MovieExec 中插入数据：

INSERT INTO MovieExec VALUES('张艺谋','wuli',1001,10000000);

INSERT INTO MovieExec VALUES('吴京','wuli',1003,40000000);
1
2
3

再在 movies 中插入数据：

INSERT INTO movies VALUES('我的世界',1948,146,'ABB','hunyuanxingyi',1002);
1

如图 1 所示， 因 movies 表中的 producerC 不是 MovieExec 表中已有的导演， 因此该数据插入操作违反了引用完整性约束， 会被系统拒绝更新。

图 1 违反引用完整性约束 cproducerc 实例

INSERT INTO movies VALUES('我的世界',1948,146,'ABB','hunyuanxingyi',1001);
1

如图 2 所示， 当 movies 表中的 producerC 是 MovieExec 表中已有的导演时， 不违反外键约束， 数据可以插入。

图 2 未违反引用完整性约束 cproducerc 实例

对4的验证：

INSERT INTO starsin VALUES('1943',2013,'张国立');
1

如图 3 所示， 与图 1 的原理一样， 引用完整性约束 cmovies 要求 StarsIn 表中的电影名称和年份必须是在 movies 表中已有的电影名称和年份， 若违反了此约束， 则拒绝更新操作。 StarsIn 表中的电影名称和年份不在 movies 表中， 故系统会拒绝更新操作。

图 3 违反引用完整性约束 cmovies 实例

INSERT INTO starsin VALUES('我的世界',1948,'张国立');
1

如图 4 所示， StarsIn 表中的电影名称和年份在 movies 表中已有的电影名称和年份，不违反外键约束， 数据可以插入。

图 4 未违反引用完整性约束 cmovies 实例

对5的验证：

INSERT INTO movies VALUES('你的世界',1911,146,'ABB','hunyuanxingyi',1001);
1

如图 5 所示， 完整性约束 cyear 要求电影年份不能是 1915 年以前的。 而此数据中电影年份在 1915 年之前， 违反了完整性约束 cyear， 系统会拒绝此更新操作。

图 5 违反完整性约束 cyear 实例

INSERT INTO movies VALUES('你的世界',1945,146,'ABB','hunyuanxingyi',1001);
1

如图 6 所示， 电影年份在 1915 年之后， 则可插入成功。

图 6 违反完整性约束 cyear 实例

对6的验证：

INSERT INTO movies VALUES('他的世界',2002,260,'ABB','hunyuanxingyi',1001);
1

如图 7 所示， 完整性约束 clength， 要求电影长度不能小于 60 也不能多于 250， 违反了完整性约束 clength， 系统会拒绝此更新操作。

图 7 违反完整性约束 clength 实例

INSERT INTO movies VALUES('他的世界',2002,200,'ABB','hunyuanxingyi',1001);
1

如图 8 所示， 把电影长度设置为[60,250]区间内则数据插入成功。

图 8 未违反完整性约束 clength 实例

8.用create view命令创建如下视图。

a)视图RichExec给出了所有资产在10000000以上的导演的姓名、地址、证书号和资产；

b)视图StudioPress给出了既是电影公司经理又是导演的那些人的名字、地址和证书号；

c)视图ExecutiveStar给出了既是演员又是导演的那些人的名字、地址、性别、生日、证书号和资产总值。

a) 使用语句11：

CREATE VIEW richexec

AS

SELECT *

FROM movieexec

WHERE networth>10000000
1
2
3
4
5
6
7
8
9

b) 使用语句12：

CREATE VIEW studiopress

AS

SELECT t1.name,t1.address,t1.cert

FROM movieexec t1,studio t2

WHERE t1.cert=t2.presc
1
2
3
4
5
6
7
8
9

c)使用语句13：

CREATE VIEW executivestar

AS

SELECT t1.name,t1.address,t1.gender,t1.birthday,t2.cert,t2.networth

FROM moviestar t1,movieexec t2

WHERE t1.name=t2.name
1
2
3
4
5
6
7
8
9

9.用create index命令在StarsIn的StarName属性上创建索引aindex。

使用语句14：

CREATE INDEX aindex ON starsin(starname) 
1

10.编写存储过程(函数)，求产品数据库Laptop内存容量的平均值和总和，然后调用该存储过程(函数)。

创建存储过程使用语句15：

DROP PROCEDURE IF EXISTS selectAvgAndSum;

delimiter;;

CREATE PROCEDURE selectAvgAndSum(out avg int,out sum int)

begin

select avg(ram) into avg from laptop;

select sum(ram) into sum from laptop;

end;;

delimiter;  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

调用存储过程使用语句 16：

call selectAvgAndSum(@avg,@sum); 

select @avg,@sum; 
1
2
3

调用存储过程结果如图 9 所示。

图 9 存储过程显示结果

四、实验小结（写出实验过程中所遇到的问题和解决的办法，解决问题的过程中得到的经验和体会）

1.编写存储过程遇到了较大困难，MySQL相关语法与课堂上所学的略有出入，在查询相关资料，认真研究后对语句进行修改实验才得以成功。

2.完整性约束语句写好执行后，添加违法约束的数据发现仍然成功，网上查资料后发现 MySQL5.5 版本不支持用户自定义的约束， 下载了 MySQL8.0 版本， 实验得以成功。

3.本次实验使我熟悉了MySQL如何创建约束，视图，索引，存储过程，调用存储过程，深入理解了约束在更新数据时所起到的作用。
1
2
3
4
5