数据库综合实验Word格式文档下载.docx

数据库综合实验Word格式文档下载.docx

《数据库综合实验Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《数据库综合实验Word格式文档下载.docx（26页珍藏版）》请在冰豆网上搜索。

001'

'

数学'

）,

002'

计算机'

003'

化学'

004'

中文'

005'

经济'

）

INSERTINTOCLASS（班号,专业名,系号,入学年份,人数）

101'

软件工程'

1995'

20）,

102'

微电子'

1996'

30）,

112'

高分子化学'

25）,

131'

现代语言'

142'

国际金融'

14）,

151'

物理化学'

29）,

111'

无机化学'

121'

离散数学'

141'

国际贸易'

1997'

143'

数字电路'

30）

INSERTINTOSTUDENT（学号,姓名,年龄,班号）

8101'

张三'

18,'

8102'

钱四'

16,'

8103'

王玲'

17,'

8105'

李飞'

19,'

8109'

赵四'

8110'

李可'

20,'

8201'

张飞'

8302'

周瑜'

8203'

王亮'

8305'

董庆'

8409'

赵龙'

8510'

李丽'

4．完成以下查询功能

（1）找出所有姓李的学生。

SELECT*

FROMSTUDENT

WHERE姓名LIKE'

李%'

（2）列出所有开设超过两个专业的系的名字。

SELECT系名

FROMDEPARTMENT

WHERE系号IN（

SELECT系号

FROMCLASS

GROUPBY系号

HAVINGCOUNT（DISTINCT专业名）>

=2）

（3）列出人数大于等于60的系的编号和名字。

SELECT*

WHERE系名in（

HAVINGSUM（人数）>

=60）

5．学校又新增加了一个物理系，编号为006。

INSERTINTODEPARTMENT

VALUES（'

006'

物理'

6．学生张三退学，请更新相关的表。

更新前

UPDATECLASS

SET人数=人数-1

WHERE班号in（

SELECT班号

FROMSTUDENT

WHERE姓名='

7．新建一个登录名user1，在C_S_D数据库中为user1映射一个数据库用户，使此用户对表

CLASS,STUDENT,DEPARTMENT有查询和修改权限，没有插入和删除权限。

GRANTSELECT,UPDATEONCLASSTOUSER1;

GRANTSELECT,UPDATEONDEPARTMENTTOUSER1;

GRANTSELECT,UPDATEONSTUDENTTOUSER1;

综合练习二

有某个学生运动会比赛信息的数据库，保存了如下的表：

运动员（运动员编号，运动员姓名，运动员性别，所属系名）

项目（项目编号，项目名称，项目比赛地点）

成绩（运动员编号，项目编号，积分）

请用SQL语句完成如下功能：

1．建立数据库，数据库名称为y_x_c

CREATEDATABASEY_X_C

2.建立数据表，并满足如下条件：

（1）定义各个表的主码、外码约束

（2）运动员的姓名和所属系别不能为空值。

（3）积分要么为空值，要么为6,4,2,0,分别代表第一，二，三名和其他名次的积分。

CREATETABLEATHLETE

（Anovarchar（10）PRIMARYKEY,

Anamevarchar（20）NOTNULL,

Asexvarchar（6）,

Adepvarchar（10）NOTNULL

）;

CREATETABLEITEM

（Inovarchar（10）PRIMARYKEY,

Inamevarchar（20）,

Ilocationvarchar（10）

CREATETABLESCORE

（Anovarchar（10）,

Inovarchar（10）,

ScoreintCHECK（Score=6orScore=4orScore=2orScore=2）,

PRIMARYKEY（Ano,Ino）,

FOREIGNKEY（Ano）REFERENCESATHLETE（Ano）,

FOREIGNKEY（Ino）REFERENCESITEM（Ino）

3．往表中插入数据：

INSERTINTOATHLETE

1001'

李明'

男'

计算机系'

1002'

数学系'

1003'

李四'

1004'

王二'

物理系'

1005'

李娜'

女'

心理系'

1006'

孙丽'

INSERTINTOITEM

x001'

男子五千米'

1操场'

x002'

男子标枪'

x003'

男子跳远'

2操场'

x004'

女子跳高'

x005'

女子三千米'

3操场'

INSERTINTOSCORE

6）,

4）,

2）,

0）,

4）

4．完成如下查询

（1）求出目前总积分最高的系名，及其积分。

SELECTAdep,SUM（Score）

FROMATHLETE,SCORE

WHEREATHLETE.Ano=SCORE.Ano

GROUPBYAdep

HAVINGSUM（Score）>

=ALL

（SELECTSUM（Score）

FROMATHLETE,SCORE

WHEREATHLETE.Ano=SCORE.Ano

GROUPBYAdep

）

（2）找出在1操场进行比赛的各项目名称及其冠军的姓名。

SELECTIname,Aname

FROMATHLETE,ITEM,SCORE

WHEREIlocation='

andATHLETE.Ano=SCORE.AnoandITEM.Ino=SCORE.Inoand

Score>

=（SELECTMAX（Score）

FROMSCORE,ITEM

WHEREIlocation='

andSCORE.Ino=ITEM.Ino）

（3）找出参加了张三所参加的所有项目的其他同学的姓名。

SELECTAname

FROMATHLETE

WHEREAnoIN

（SELECTAno

FROMSCORE

WHEREInoIN

（SELECTIno

WHEREathlete.ano=SCORE.anoANDaname='

））ANDaname<

>

'

5．建立视图文件，查询每个项目的项目名称、运动员姓名和所属系名。

CREATEVIEWI_A（Iname,Aname,Adep）

AS

SELECTIname,Aname,Adep

fromATHLETE,ITEM,SCORE

whereATHLETE.Ano=SCORE.AnoandITEM.Ino=SCORE.Ino;

6．经查张三因为使用了违禁药品，其成绩都记0分，请在数据库中做出相应修改。

UPDATESCORE

SETScore=0

WHEREAnoin

FROMATHLETE

WHEREAname='

7．经组委会协商，需要删除女子跳高比赛项目。

DELETEFROMSCORE

WHEREInoIN

（SELECTIno

FROMITEM

WHEREIname='

DELETEFROMITEM

;

8．

新建一个登录名user2，在y_x_c数据库中为user2映射一个数据库用户，使此用户对运动员表有查询和修改权限，没有插入和删除权限，对项目表有查询权限，没有插入、修改、删除的权限，对成绩表有查询、插入、修改、删除权限。

CREATELOGINuser2

WITHPASSWORD='

123'

CREATEUSERhubing

FORLOGINuser2;

GRANTCONNECTTOhubing;

GRANTSELECT

ONATHLETE

TOhubing;

GRANTUPDATE

ONITEM

ONSCORE

GRANTINSERT

GRANTUPDATE

GRANTDELETE

综合练习三

现有一个商店的数据库，记录顾客及其购物情况，由下面三个表组成：

商品（商品号，商品名，单价，商品类别，供应商）；

顾客（顾客号，姓名，住址）；

购买（顾客号，商品号，购买数量）；

试用SQL语言完成下列功能：

1．建立数据库，数据库名称自定

CREATEDATABASEC_C_P

2．建立数据表，在定义中要求声明：

（1）每个表的主外码；

（2）顾客的姓名和商品名不能为空值；

（3）单价必须大于0，购买数量必须再0到20之间；

CREATETABLECOMMODITY（

CnoVARCHAR（10）PRIMARYKEY,

Cnamevarchar（20）NOTNULL,

PriceFLOATCHECK（Price>

0）,

CategoryVARCHAR（20）,

Supplychar（20）

CREATETABLECUSTOMER（

CunoVARCHAR（10）PRIMARYKEY,

CnameVARCHAR（20）NOTNULL,

AddressVARCHAR（40）

CREATETABLEPURCHASE（

CunoVARCHAR（10）,

CnoVARCHAR（10）,

QUANTITYINTCHECK（QUANTITY>

=0ANDQUANTITY<

=20）,

PRIMARYKEY（Cuno,Cno）,

FOREIGNKEY（Cuno）REFERENCESCUSTOMER（Cuno）,

FOREIGNKEY（Cno）REFERENCESCOMMODITY（Cno）,

3．往表中插入数据

INSERTINTOCOMMODITY

M01'

佳洁士'

8.00'

牙膏'

宝洁'

M02'

高露洁'

6.50'

M03'

洁诺'

5.00'

联合利华'

M04'

舒肤佳'

3.00'

香皂'

M05'

夏士莲'

M06'

雕牌'

2.50'

洗衣粉'

纳爱斯'

M07'

中华'

3.50'

M08'

汰渍'

M09'

碧浪'

4.00'

INSERTINTOCUSTOMER

C01'

Dennis'

海淀'

C02'

John'

朝阳'

C03'

Tom'

东城'

C04'

Jenny'

C05'

Rick'

西城'

INSERTINTOPURCHASE

VALUES

3'

2'

5'

4'

1'

7'

8'

4．用SQL语句完成下列查询：

（1）求购买了供应商“宝洁”产品的所有顾客。

SELECTCuno,Cuname

FROMCUSTOMER

whereCunoin

（SELECTCuno

FROMPURCHASE

WHERECnoin

（SELECTCno

FROMCOMMODITY

WHEREsupply='

）

（2）求购买的商品包括了顾客“Dennis”所购买的任意商品的顾客姓名。

SELECTDISTINCTCuname

FROMCUSTOMER,PURCHASE

WHERECUSTOMER.Cuno=PURCHASE.CunoANDCnoIN

FROMPURCHASE,CUSTOMER

WHERECUSTOMER.Cuno=PURCHASE.CunoANDCuname='

（3）求牙膏卖出数量最多的供应商名。

SELECTsupply

FROMCOMMODITY,PURCHASE

WHERECOMMODITY.Cno=PURCHASE.CnoANDQUANTITY>

=ALL

（SELECTSUM（QUANTITY）

GROUPBYCno

HAVINGCnoIN

FROMCOMMODITY

WHEREcategory='

5．建立视图文件，查询每种商品的商品名称和购买数量。

CREATEVIEWMENU

SELECTCname,QUANTITY

WHERECOMMODITY.Cno=PURCHASE.Cno;

6．将所有的牙膏商品单价增加10%。

UPDATECOMMODITY

SETprice=price*1.1

WHEREcategory='

7．删除从未被购买的商品记录。

DELETE

FROMCOMMODITY

WHERECnoNOTIN

8．新建一个登录名user3，在数据库中为user3映射一个数据库用户，使数据库用户拥有对表商品、顾客、购买的插入和删除权限，没有查询和修改权限。

CREATELOGINuser3

456'

CREATEUSERHUBING1

FORLOGINuser3;

GRANTINSERT,DELETE

ONCOMMODITY

TOHUBING1;

DENYSELECT,UPDATE

GRANTINSERT,DELETE

ONCUSTOMER

TOHUBING1

ONPURCHASE

toHUBING1

3、实验总结

通过此次总和实验，能够更加熟练的使用SQL语言，对于数据库中完整性约束有了更深的认识。

总之，希望在以后的学习中能学会有实验中发现的问题，让自己更好的掌握数据库知识。