约束、视图、索引与存储过程
一、实验目的和要求
1. 通过上机实践,熟悉约束的创建,深入理解约束的作用。
2. 掌握视图和索引的创建。
3. 熟练掌握存储过程的设计和使用方法。
二、实验环境(实验设备)
硬件:微机
软件:Windows xp/Windows 10和MySQL
素材:实验数据
三、实验内容
1.用create database命令创建电影数据库(以自己的学号命名)。
使用语句1:
CREATE DATABASE B1806xxxx;
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)
);
MovieStar (name,address,gender,birthday)
使用语句3:
CREATE TABLE MovieStar
(
NAME CHAR(12),
address CHAR(30),
gender CHAR(2),
birthdate DATE,
PRIMARY KEY(NAME)
);
StarsIn(Title,Year,starName)
使用语句4:
CREATE TABLE StarsIn
(
movieTitle CHAR(20),
movieYear INT,
starName CHAR(12),
PRIMARY KEY(movieTitle,movieYear,starName)
);
MovieExec(NAME,address,cert,netWorth)
使用语句5:
CREATE TABLE MovieExec
(
NAME CHAR(12),
address CHAR(30),
cert INT,
netWorth INT,
PRIMARY KEY(cert)
);
Studio(NAME,address,presC)
使用语句6:
CREATE TABLE Studio
(
NAME CHAR(30),
address CHAR(30),
presC INT,
PRIMARY KEY(NAME)
);
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
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
5.用alter table命令修改movies关系模式,增加完整性约束cyear,要求电影年份不能是1915年以前的。
使用语句9:
ALTER TABLE movies ADD CONSTRAINT cyear CHECK(YEAR>=1915)
6.用alter table命令修改movies关系模式,增加完整性约束clength,要求电影长度不能小于60也不能多于250。
使用语句10:
ALTER TABLE movies ADD CONSTRAINT clength CHECK(LENGTH BETWEEN 60 AND 250)
7.设计数据对3~6的内容进行验证。
对3的验证:
先在 MovieExec 中插入数据:
INSERT INTO MovieExec VALUES('张艺谋','wuli',1001,10000000);
INSERT INTO MovieExec VALUES('吴京','wuli',1003,40000000);
再在 movies 中插入数据:
INSERT INTO movies VALUES('我的世界',1948,146,'ABB','hunyuanxingyi',1002);
如图 1 所示, 因 movies 表中的 producerC 不是 MovieExec 表中已有的导演, 因此该数据插入操作违反了引用完整性约束, 会被系统拒绝更新。
INSERT INTO movies VALUES('我的世界',1948,146,'ABB','hunyuanxingyi',1001);
如图 2 所示, 当 movies 表中的 producerC 是 MovieExec 表中已有的导演时, 不违反外键约束, 数据可以插入。
对4的验证:
INSERT INTO starsin VALUES('1943',2013,'张国立');
如图 3 所示, 与图 1 的原理一样, 引用完整性约束 cmovies 要求 StarsIn 表中的电影名称和年份必须是在 movies 表中已有的电影名称和年份, 若违反了此约束, 则拒绝更新操作。 StarsIn 表中的电影名称和年份不在 movies 表中, 故系统会拒绝更新操作。
INSERT INTO starsin VALUES('我的世界',1948,'张国立');
如图 4 所示, StarsIn 表中的电影名称和年份在 movies 表中已有的电影名称和年份,不违反外键约束, 数据可以插入。
对5的验证:
INSERT INTO movies VALUES('你的世界',1911,146,'ABB','hunyuanxingyi',1001);
如图 5 所示, 完整性约束 cyear 要求电影年份不能是 1915 年以前的。 而此数据中电影年份在 1915 年之前, 违反了完整性约束 cyear, 系统会拒绝此更新操作。
INSERT INTO movies VALUES('你的世界',1945,146,'ABB','hunyuanxingyi',1001);
如图 6 所示, 电影年份在 1915 年之后, 则可插入成功。
对6的验证:
INSERT INTO movies VALUES('他的世界',2002,260,'ABB','hunyuanxingyi',1001);
如图 7 所示, 完整性约束 clength, 要求电影长度不能小于 60 也不能多于 250, 违反了完整性约束 clength, 系统会拒绝此更新操作。
INSERT INTO movies VALUES('他的世界',2002,200,'ABB','hunyuanxingyi',1001);
如图 8 所示, 把电影长度设置为[60,250]区间内则数据插入成功。
8.用create view命令创建如下视图。
a)视图RichExec给出了所有资产在10000000以上的导演的姓名、地址、证书号和资产;
b)视图StudioPress给出了既是电影公司经理又是导演的那些人的名字、地址和证书号;
c)视图ExecutiveStar给出了既是演员又是导演的那些人的名字、地址、性别、生日、证书号和资产总值。
a) 使用语句11:
CREATE VIEW richexec
AS
SELECT *
FROM movieexec
WHERE networth>10000000
b) 使用语句12:
CREATE VIEW studiopress
AS
SELECT t1.name,t1.address,t1.cert
FROM movieexec t1,studio t2
WHERE t1.cert=t2.presc
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
9.用create index命令在StarsIn的StarName属性上创建索引aindex。
使用语句14:
CREATE INDEX aindex ON starsin(starname)
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;
调用存储过程使用语句 16:
call selectAvgAndSum(@avg,@sum);
select @avg,@sum;
调用存储过程结果如图 9 所示。
四、实验小结(写出实验过程中所遇到的问题和解决的办法,解决问题的过程中得到的经验和体会)
1.编写存储过程遇到了较大困难,MySQL相关语法与课堂上所学的略有出入,在查询相关资料,认真研究后对语句进行修改实验才得以成功。
2.完整性约束语句写好执行后,添加违法约束的数据发现仍然成功,网上查资料后发现 MySQL5.5 版本不支持用户自定义的约束, 下载了 MySQL8.0 版本, 实验得以成功。
3.本次实验使我熟悉了MySQL如何创建约束,视图,索引,存储过程,调用存储过程,深入理解了约束在更新数据时所起到的作用。