上海大學(xué)數(shù)據(jù)庫(kù)實(shí)驗(yàn)報(bào)告1

1、.上海大學(xué)數(shù)據(jù)庫(kù)實(shí)驗(yàn)報(bào)告第一組第1周（第四章：SQL體系結(jié)構(gòu)、組成、建庫(kù)建表，索引自學(xué)）一、實(shí)驗(yàn)課： 1. 建立school數(shù)據(jù)庫(kù)2. 在school下建立如下數(shù)據(jù)庫(kù)表，根據(jù)表中數(shù)據(jù)選取合適的數(shù)據(jù)類型及寬度，設(shè)置各表的主鍵及表間外鍵聯(lián)系：注意：字段名是對(duì)應(yīng)漢字字段名的漢語(yǔ)拼音第一個(gè)字母組合而成l 學(xué)生表S：學(xué)號(hào)，姓名，性別，出生日期，籍貫，手機(jī)號(hào)碼，院系號(hào)；表1：Sxhxmxbcsrqjgsjhmyxh1101李明男1993-03-06上海021102劉曉明男1992-12-08安徽011103張穎女1993-01-05江蘇011104劉晶晶女1994-11-06上海011105劉成剛男199

2、1-06-07上海011106李二麗女1993-05-04江蘇011107張曉峰男1992-08-16浙江01l 院系表D：院系號(hào)，名稱，地址，聯(lián)系電話；表2：Dyxhmc地址lxdh01計(jì)算機(jī)學(xué)院上大東校區(qū)三號(hào)樓02通訊學(xué)院上大東校區(qū)二號(hào)樓03材料學(xué)院上大東校區(qū)四號(hào)樓l 教師表T：工號(hào)，姓名，性別，出生日期，學(xué)歷，基本工資，院系編號(hào)；表3：Tghxmxbcsrqxljbgzyxh0101陳迪茂男1973-03-06副教授3567.00010102馬小紅女1972-12-08講師2845.00010201張心穎女1960-01-05教授4200.00020103吳寶鋼男1980-11-06講師

3、2554.0001l 課程表C：課號(hào)，課名，學(xué)分，學(xué)時(shí)，院系號(hào)；（默認(rèn)學(xué)分4，學(xué)時(shí)40）表4：Ckhkmxfxsyxh離散數(shù)學(xué)44001數(shù)據(jù)庫(kù)原理45001數(shù)據(jù)結(jié)構(gòu)45001系統(tǒng)結(jié)構(gòu)66001分子物理學(xué)44003通信學(xué)33002l 開(kāi)課表O：學(xué)期，課號(hào)，工號(hào)，上課時(shí)間；表5：Oxqkhghsksj2012-2013秋季0103星期三5-82012-2013冬季0101星期三1-42012-2013冬季0102星期三1-42012-2013冬季0103星期三1-42012-2013冬季0102星期五5-82013-2014秋季0101星期二1-42013-2014秋季0102星期一5-82013

4、-2014冬季0201星期一5-8l 選課表E：學(xué)號(hào)，學(xué)期，課號(hào)，工號(hào)，平時(shí)成績(jī)，考試成績(jī)，總評(píng)成績(jī)；（成績(jī)范圍1-100）表6：Exhxqkhghpscjkscjzpcj11012012-2013秋季010360606011022012-2013秋季010387878711022012-2013冬季010182828211022013-2014秋季0101nullnullnull11032012-2013秋季010356565611032012-2013冬季010275757511032012-2013冬季010284848411032013-2014秋季0102nullnullnull11

5、032013-2014秋季0101nullnullnull11042012-2013秋季010374747411042013-2014冬季0201nullnullnull11062012-2013秋季010385858511062012-2013冬季010366666611072012-2013秋季010390909011072012-2013冬季010279797911072013-2014秋季0101nullnullnull3. 在學(xué)生表中建立索引idx1：院系號(hào)升序，姓名降序在課程表中建立索引idx2：課名create database schoolgouse schoolcreate

6、table S(xh int,xm char(10),xb char(2),csrq date,jg char(20),sjhm bigint,yxh char(2),primary key (xh),foreign key (yxh) references D(yxh)create table D(yxh char(2),mc char(20),地址 char(50),lxdh int,primary key (yxh)create table T(gh char(4),xm char(10),xb char(2),csrq date,xl char(10),jbgz numeric(6,2

7、),yxh char(2),primary key (gh),foreign key (yxh) references D(yxh)create table C(kh char(8),km char(20),xf int,xs int,yxh char(2),primary key (kh),foreign key (yxh) references D(yxh)create table O(xq char(20),kh char(8),gh char(4),sksj char(20),primary key (xq,kh,gh),foreign key (kh) references C(kh

8、),foreign key (gh) references T(gh)create table E(xh int,xq char(20),kh char(8),gh char(4),pscj int CHECK(pscj BETWEEN 1 AND 100),kscj int CHECK(kscj BETWEEN 1 AND 100),zpcj int CHECK(zpcj BETWEEN 1 AND 100),primary key (xh,xq,kh,gh),foreign key (gh) references T(gh),foreign key (kh) references C(kh

9、),foreign key (xh) references S(xh)create unique index idx1 on S(yxh asc,xm desc);create unique index idx2 on C(km);第2周（第四章：投影、選擇、多表連接和嵌套，排序自學(xué)）一、實(shí)驗(yàn)課：1. 查詢2011年進(jìn)校年齡大于20歲的男學(xué)生的學(xué)號(hào)與姓名。-1.查詢2011年進(jìn)校年齡大于20歲的男學(xué)生的學(xué)號(hào)與姓名。SELECT XH,XMFROM SWHERE YEAR(2011-YEAR(CSRQ)20 AND XB=男2. 檢索劉曉明不學(xué)的課程的課程號(hào)。SELECT KHFROM OEXC

10、EPTSELECT KH FROM S,EWHERE XM=劉曉明 AND S.XH=E.XH3. 檢索馬小紅老師所授課程的學(xué)年，學(xué)期，課程號(hào)，上課時(shí)間。SELECT XQ,KH,SKSJFROM O,TWHERE T.XM=馬小紅 AND T.GH=O.GH4. 查詢計(jì)算機(jī)學(xué)院男生總評(píng)成績(jī)及格、教授開(kāi)設(shè)的課程的課程號(hào)、課名、開(kāi)課教師姓名，按開(kāi)課教師升序，課程號(hào)降序排序。SELECT E.KH,C.KM,T.XMFROM E,C,TWHERE E.ZPCJ=60 AND E.XH IN(SELECT S.XH FROM S JOIND ON S.YXH=D.YXH WHERE D.MC=計(jì)算機(jī)

11、學(xué)院 ANDS.XB=男)INTERSECTSELECT E.KH,C.KM,T.XMFROM T,E,CWHERE T.XL=教授 AND T.GH=E.GH AND E.KH=C.KHORDER BY T.XM,E.KH DESC5. 檢索學(xué)號(hào)比張穎同學(xué)大，年齡比張穎同學(xué)小的同學(xué)學(xué)號(hào)、姓名。SELECT B.XH,B.XMFROM S AS A,S AS BWHERE A.XM=張穎 AND B.XHA.XH AND B.CSRQA.CSRQ6. 檢索同時(shí)選修了“”和“”的學(xué)生學(xué)號(hào)和姓名。SELECT DISTINCT S.XH,S.XMFROM E AS A ,E AS B,SWHERE

12、 A.KH=08305001 AND B.KH=08305002 AND A.XH=B.XH AND A.XH=S.XH第3周（第四章：除法、聚合函數(shù)、分組、集合操作，外連接自學(xué)）一、實(shí)驗(yàn)課：1. 驗(yàn)證在1000萬(wàn)個(gè)以上記錄時(shí)在索引和不索引時(shí)的查詢時(shí)間區(qū)別。-1.驗(yàn)證在1000萬(wàn)個(gè)以上記錄時(shí)在索引和不索引時(shí)的查詢時(shí)間區(qū)別。declare i intset i=1while i = 1000000begininsert into A values(i)set i=i+1endSELECT SFROM AWHERE S=1000000CREATE TABLE A(S INT)2. 查詢每個(gè)學(xué)生選課

13、情況（包括沒(méi)有選修課程的學(xué)生）。SELECT S.XH,S.XM,XQ,E.KH,PSCJ,KSCJ,ZPCJFROM S LEFT JOIN E ON E.XH=S.XHORDER BY S.XH3. 檢索所有課程都選修的的學(xué)生的學(xué)號(hào)與姓名。SELECT XH,XMFROM SWHERE NOT EXISTS(SELECT *FROM CWHERE NOT EXISTS(SELECT *FROM EWHERE E.XH=S.XH AND C.KH=E.KH)4. 檢索選修課程包含1106同學(xué)所學(xué)全部課程的學(xué)生學(xué)號(hào)和姓名。SELECT DISTINCT XH,XMFROM SWHERE NOT

14、 EXISTS(SELECT *FROM E AS E1WHERE E1.XH=1106 AND NOT EXISTS(SELECT *FROM E AS E2WHERE E2.XH=S.XH AND E1.KH=E2.KH)ORDER BY XH5. 查詢每門課程中分?jǐn)?shù)最高的學(xué)生學(xué)號(hào)和學(xué)生姓名。SELECT S.XM,S.XH,C.KM,A.ZPCJFROM S,C,E AS AWHERE S.XH=A.XH AND A.KH=C.KH AND A.ZPCJ=(SELECT MAX(ZPCJ) from E WHERE E.KH=A.KH )6. 查詢年齡小于本學(xué)院平均年齡，所有課程總評(píng)成績(jī)

15、都高于所選課程平均總評(píng)成績(jī)的學(xué)生學(xué)號(hào)、姓名和平均總評(píng)成績(jī)，按年齡排序。SELECT X.XH,X.XM,AVG(ZPCJ) AS 平均成績(jī),DateDiff(YYYY,CSRQ,2013-12-22) AS 年齡FROM S,E AS E3,(SELECT S1.XH,S1.XMFROM(SELECT XH,XM,YXH,DateDiff(YYYY,CSRQ,2013-12-22) AS 年齡FROM S) AS S1,(SELECT YXH,avg(DateDiff(YYYY,CSRQ,2013-12-22) as avg_age FROM SGROUP BY YXH) AS S2WHERE

16、 S1.YXH=S2.YXH AND S1.年齡=平均成績(jī) OR E2.ZPCJ=NULL)AS YWHERE X.XH=Y.XH AND X.XH=E3.XH AND S.XH=X.XHGROUP BY X.XH,X.XM,DateDiff(YYYY,CSRQ,2013-12-22)ORDER BY 年齡第4周（第四章：數(shù)據(jù)更新、視圖、嵌入式SQL部分自學(xué)）一、實(shí)驗(yàn)課：1. 建立計(jì)算機(jī)學(xué)院總評(píng)不及格成績(jī)學(xué)生的視圖，包括學(xué)生學(xué)號(hào)、姓名、性別、手機(jī)、所選課程和成績(jī)。CREATE VIEW AAS SELECT S.XH,XM,XB,SJHM,KH,PSCJ,KSCJ,ZPCJFROM S,E,DWHERE D.MC=計(jì)算機(jī)學(xué)院 AND E.ZPCJALL(SELECT DateDiff(YYYY,CSRQ,2013-12-22) AS 年齡 FROM SWHERE XB=女)4. 在E表中修改08305001課程的平時(shí)成績(jī)，若成績(jī)小于等于75分時(shí)提高5%，若成績(jī)大于75分時(shí)提高4%。-先都提高4%，然后成績(jī)小于等于75*（