当前位置:   article > 正文

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

用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 表中已有的导演, 因此该数据插入操作违反了引用完整性约束, 会被系统拒绝更新。

MySQL_2_1

图 1 违反引用完整性约束 cproducerc 实例
INSERT INTO movies VALUES('我的世界',1948,146,'ABB','hunyuanxingyi',1001);
  • 1

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

MySQL_2_2

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

对4的验证:

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

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

MySQL_2_3

图 3 违反引用完整性约束 cmovies 实例
INSERT INTO starsin VALUES('我的世界',1948,'张国立');
  • 1

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

MySQL_2_4

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

对5的验证:

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

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

MySQL_2_5

图 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, 系统会拒绝此更新操作。

MySQL_2_7

图 7 违反完整性约束 clength 实例
INSERT INTO movies VALUES('他的世界',2002,200,'ABB','hunyuanxingyi',1001);
  • 1

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

MySQL_2_8

图 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 所示。

MySQL_2_9

图 9 存储过程显示结果

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

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

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

3.本次实验使我熟悉了MySQL如何创建约束,视图,索引,存储过程,调用存储过程,深入理解了约束在更新数据时所起到的作用。
  • 1
  • 2
  • 3
  • 4
  • 5
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/小蓝xlanll/article/detail/173142
推荐阅读
相关标签
  

闽ICP备14008679号