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

1、001,数学）,002计算机003化学004中文005经济）INSERT INTO CLASS（班号,专业名,系号,入学年份,人数）101软件工程1995,20）,102微电子1996,30）,112高分子化学,25）,131现代语言142国际金融,14）,151物理化学,29）,111无机化学121离散数学141国际贸易1997143数字电路,30）INSERT INTO STUDENT（学号,姓名,年龄,班号）8101张三,18,8102钱四,16,8103王玲,17,8105李飞,19,8109赵四8110李可,20,8201张飞8302周瑜8203王亮8305董庆8409赵龙8510李

2、丽4完成以下查询功能（1）找出所有姓李的学生。 SELECT * FROM STUDENT WHERE 姓名 LIKE 李%（2）列出所有开设超过两个专业的系的名字。SELECT 系名FROM DEPARTMENTWHERE 系号 IN（SELECT 系号FROM CLASSGROUP BY 系号HAVING COUNT（DISTINCT 专业名）=2）（3）列出人数大于等于60的系的编号和名字。SELECT *WHERE 系名 in（HAVING SUM（人数）=60）5学校又新增加了一个物理系，编号为006。INSERT INTO DEPARTMENTVALUES（006物理6学生张三退学

3、，请更新相关的表。 更新前UPDATE CLASSSET 人数=人数-1WHERE 班号 in（SELECT 班号FROM STUDENTWHERE 姓名=7新建一个登录名user1，在C_S_D数据库中为user1映射一个数据库用户，使此用户对表CLASS,STUDENT,DEPARTMENT有查询和修改权限，没有插入和删除权限。GRANT SELECT,UPDATE ON CLASS TO USER1;GRANT SELECT,UPDATE ON DEPARTMENT TO USER1;GRANT SELECT,UPDATE ON STUDENT TO USER1;综合练习二有某个学生运动

4、会比赛信息的数据库，保存了如下的表：运动员（运动员编号，运动员姓名，运动员性别，所属系名）项目（项目编号，项目名称，项目比赛地点）成绩（运动员编号，项目编号，积分）请用SQL语句完成如下功能：1建立数据库，数据库名称为y_x_cCREATE DATABASE Y_X_C2. 建立数据表，并满足如下条件：（1）定义各个表的主码、外码约束（2）运动员的姓名和所属系别不能为空值。（3）积分要么为空值，要么为6,4,2,0,分别代表第一，二，三名和其他名次的积分。CREATE TABLE ATHLETE（ Ano varchar（10） PRIMARY KEY, Aname varchar（20） N

5、OT NULL, Asex varchar（6）, Adep varchar（10） NOT NULL ）;CREATE TABLE ITEM（ Ino varchar（10） PRIMARY KEY, Iname varchar（20）, Ilocation varchar（10）CREATE TABLE SCORE（ Ano varchar（10）, Ino varchar（10）, Score int CHECK（Score=6 or Score=4 or Score=2 or Score=2）, PRIMARY KEY （Ano,Ino）, FOREIGN KEY （Ano） REFE

6、RENCES ATHLETE（Ano）, FOREIGN KEY （Ino） REFERENCES ITEM（Ino）3往表中插入数据：INSERT INTO ATHLETE1001李明男计算机系1002数学系1003李四1004王二物理系1005李娜女心理系1006孙丽INSERT INTO ITEMx001男子五千米1操场x002男子标枪x003男子跳远2操场x004女子跳高x005女子三千米3操场INSERT INTO SCORE,6）,4）,2）,0）,4）4完成如下查询（1）求出目前总积分最高的系名，及其积分。SELECT Adep,SUM（Score）FROM ATHLETE,SC

7、OREWHERE ATHLETE.Ano=SCORE.AnoGROUP BY Adep HAVING SUM（Score）=ALL （ SELECT SUM（Score） FROM ATHLETE,SCORE WHERE ATHLETE.Ano=SCORE.Ano GROUP BY Adep ） （2）找出在1操场进行比赛的各项目名称及其冠军的姓名。SELECT Iname,AnameFROM ATHLETE,ITEM,SCOREWHERE Ilocation= and ATHLETE.Ano=SCORE.Ano and ITEM.Ino=SCORE.Ino and Score=（SELECT

8、 MAX（Score） FROM SCORE,ITEM WHERE Ilocation= and SCORE.Ino=ITEM.Ino ）（3）找出参加了张三所参加的所有项目的其他同学的姓名。SELECT AnameFROM ATHLETEWHERE Ano IN（SELECT Ano FROM SCORE WHERE Ino IN （SELECT Ino WHERE athlete.ano=SCORE.ano AND aname=） AND aname5建立视图文件，查询每个项目的项目名称、运动员姓名和所属系名。CREATE VIEW I_A（Iname,Aname,Adep）ASSELEC

9、T Iname,Aname,Adepfrom ATHLETE,ITEM,SCOREwhere ATHLETE.Ano=SCORE.Ano and ITEM.Ino=SCORE.Ino;6经查张三因为使用了违禁药品，其成绩都记0分，请在数据库中做出相应修改。UPDATE SCORE SET Score=0WHERE Ano in FROM ATHLETE WHERE Aname=7经组委会协商，需要删除女子跳高比赛项目。DELETE FROM SCOREWHERE Ino IN（SELECT Ino FROM ITEM WHERE Iname= DELETE FROM ITEM ;8新建一个登录

10、名user2，在y_x_c数据库中为user2映射一个数据库用户，使此用户对运动员表有查询和修改权限，没有插入和删除权限，对项目表有查询权限，没有插入、修改、删除的权限，对成绩表有查询、插入、修改、删除权限。CREATE LOGIN user2WITH PASSWORD=123CREATE USER hubingFOR LOGIN user2;GRANT CONNECT TO hubing;GRANT SELECT ON ATHLETE TO hubing;GRANT UPDATEON ITEM ON SCORE GRANT INSERT GRANT UPDATE GRANT DELETE 综

11、合练习三现有一个商店的数据库，记录顾客及其购物情况，由下面三个表组成：商品（商品号，商品名，单价，商品类别，供应商）；顾客（顾客号，姓名，住址）；购买（顾客号，商品号，购买数量）；试用SQL语言完成下列功能：1建立数据库，数据库名称自定CREATE DATABASE C_C_P2建立数据表，在定义中要求声明：（1）每个表的主外码；（2）顾客的姓名和商品名不能为空值；（3）单价必须大于0，购买数量必须再0到20之间；CREATE TABLE COMMODITY（Cno VARCHAR（10） PRIMARY KEY,Cname varchar（20） NOT NULL,Price FLOAT C

12、HECK（Price0）,Category VARCHAR（20）,Supply char（20）CREATE TABLE CUSTOMER（Cuno VARCHAR（10） PRIMARY KEY,Cname VARCHAR（20） NOT NULL,Address VARCHAR（40）CREATE TABLE PURCHASE（Cuno VARCHAR（10）,Cno VARCHAR（10）,QUANTITY INT CHECK （QUANTITY=0 AND QUANTITY=ALL （SELECT SUM（QUANTITY） GROUP BY Cno HAVING Cno IN FR

13、OM COMMODITY WHERE category=5建立视图文件，查询每种商品的商品名称和购买数量。CREATE VIEW MENUSELECT Cname,QUANTITYWHERE COMMODITY.Cno=PURCHASE.Cno;6将所有的牙膏商品单价增加10%。UPDATE COMMODITYSET price=price*1.1WHERE category=7删除从未被购买的商品记录。DELETE FROM COMMODITYWHERE Cno NOT IN8新建一个登录名user3，在数据库中为user3映射一个数据库用户，使数据库用户拥有对表商品、顾客、购买的插入和删除权限，没有查询和修改权限。CREATE LOGIN user3456CREATE USER HUBING1FOR LOGIN user3;GRANT INSERT ,DELETEON COMMODITYTO HUBING1;DENY SELECT,UPDATEGRANT INSERT,DELETEON CUSTOMERTO HUBING1ON PURCHASEto HUBING13、实验总结通过此次总和实验，能够更加熟练的使用SQL语言，对于数据库中完整性约束有了更深的认识。总之，希望在以后的学习中能学会有实验中发现的问题，让自己更好的掌握数据库知识。