2023年数据库概论实验报告书

《数据库系统概论》实验报告书班级：网141姓名：郭杰学号：指导老师：周兵安徽工业大学计算机科学与技术学院实验一:数据定义语言[实验日期]2０23年12月9日［实验目的］熟悉Oｒａｃｌe上机环境及Orａcle客户端的配置;纯熟掌握和使用DDL语言，建立、修改和删除数据库表、主键、外键约束关系和索引。［实验内容]Oｒaclｅ上机环境以及Oｒacｌｅ客户端的配置参见附录。1．SQL数据定义语句：例1-1:(建立数据库表)建立教学数据库的四个数据库表，其中Studｅｎｔ表中不包含SSEX(C，2)字段，Snａme字段为Ｓnamｅ(C,8）且可为空。（1)、ＣREＡTETＡBLEＳ( ＳＮOCHAＲ(５),ﻩSＮＡMEＶARＣHAR2(8), SDEPTCHAR(2)NOTNＵLL，ﻩSCＬASSCHAＲ（2）NOTNＵLＬ， SＡGＥNUMBＥR（２)CHECK(SAGEBETWEEN12ＡND60)， CONSＴRAIＮTSＮO＿pkＰＲIＭAＲYKＥY(ＳNＯ)）；(2）、ＣREATＥＴAＢLEC(ﻩCNOCHAR(３),ﻩCNＡMEVARCHAR2(16)NOＴNULＬ,ﻩCTIMＥNUMBER(3),ﻩＣONSＴＲAＩNTCNＯ＿pkPRIMARYKEＹ(CNO));(3)、CＲEATEＴＡBLEＴ( TNAMEVＡＲCHAR(８)ＮOＴNULL,ﻩTSEXCHAＲ(2)CHECＫ（TSexＩN(＇男','女＇)),ﻩCNＯCHAR(3)，ﻩＴDＡTＥDATE,ﻩTＤEPTCHＡＲ(2）, ＣＯNSTＲAINTＴ＿pkPRIＭAＲYKEY(ＴNAMＥ,CNO,TDEPT), CONSTＲAINTCNO_fkＦOREIＧNKEY(CＮO)ＲEFＥRＥNCESＣ（CNO));（4）、ＣREATＥＴABLESC( ＳNOCHAR(5)， CＮOCHＡR(3)， SCＯRENUMBER(5,2), CONSＴＲＡINＴSC_pｋPＲIMARYKEＹ(SNO,ＣNO）， CONSTRAIＮＴSNO_fｋFOＲEIGＮKＥY(SNO)REFＥRＥNCＥSS（SNO），ﻩCONSTRAINTCNＯ２_ｆkFOＲEIGNKEY(CNO）ＲEＦERENCESＣ(ＣNO), CＯNSTRAINTCｋ1ＣHECK(ＳCＯRE＞=0andＳCOＲＥ<=100)）;结果截图：例1-2:(修改数据库表)在Student表中增长SEＸ(Ｃ,2)字段。 ＡLＴEＲTAＢLESADＤＳEＸCHAR(2)ＣHECＫ（SEXIN(‘男’,’女’）);例1-3：（修改列名）将Studeｎt表中列名SＥＸ修改为ＳSEX。 ALTERＴABLESRENAMEＣOLUMNSEXTOSＳEＸ;例１-4：(修改数据库表)将Studｅnt表中把Snaｍe字段修改为Snaｍｅ（C,１0)且为非空。 ALTERTABLESＭODIFY(SNAMＥCHＡR(10)NOTNUＬL)；例1-5：(建立索引）为Ｓcore表按课程号升序、分数降序建立索引,索引名为SC_GRAＤＥ。ﻩCRＥATEUＮＩQUEINDEXSC_GRADEOＮSC（CＮＯASＣ，SCOＲＥDEＳC);例1-６：（删除索引）删除索引SC_GRADＥ。 DROPINDEXＳC_GRADE；例１-７:(建立数据库表)建立数据库表S１(SNＯ,ＳNAME，ＳD,SA),其字段类型定义与Stｕdeｎt表中的相应字段(ＳNO，SNAME,SDＥPT,ＳAGE）的数据类型定义相同。 CＲＥATＥTＡBＬEＳ1( SNOCＨAR(5)，ﻩＳNAMＥCＨＡR(１0),ﻩSＤCHAR（2）NOTＮULL,ﻩSＡNUMＢＥR(2)ＣHECK（SABＥTＷＥEN１2AND６0),ﻩCONSＴRＡINTSNO１＿pｋPRIMARYKＥＹ（SNO));例1-8：(修改数据库表)删除成绩表Scoｒe的参照完整性约束关系。ﻩAＬＴＥＲTAＢLESCDROPCＯNSTRAINＴSC_ｐｋ; AＬTEＲTABLＥＳＣDROPＣONSTＲAINTＳNO_fk; ALTＥＲTABＬＥSＣDROPCONSTＲAINTＣNO２_fk；例1-９：（修改数据库表)添加成绩表Scoｒe的参照完整性约束关系。AＬTEＲＴAＢLESCADDＣＯNSTRAINTＳC_pkPＲIＭARＹKEY（SNO，CNＯ）； ＡLＴERＴABLEＳCADDCONSTRAINTSNO＿fkFORＥIGＮKＥY(SNＯ)ＲＥFERＥNCESS(SNＯ)； ＡLＴERTＡBLESCADDＣＯＮSTＲAIＮTＣＮO2_ｆkFORＥIGNKEY(ＣNO)REＦERENCＥＳC(CＮＯ）;例１-１0：(修改数据库表名)将数据库表Ｓ1改名为Stｕｄenｔ_Tｅmp。 ＲENAＭES1ＴＯＳＴUＤENT_TEMP；[实验规定]①熟悉Ｏｒacｌe上机环境，掌握Oｒaclｅ客户端的配置；

②建立数据库表,修改数据库表结构，建立、删除索引；[实验方法］①按照附录中的操作环节进行客户端的配置;ﻫ②将实验需求用SQL语句表达；

③执行ＳＱL语句;ﻫ④查看执行结果,假如结果不对的,进行修改，直到对的为止。[实验总结］①SQL语句以及执行结果;ﻫ②对重点实验结果进行分析;ﻫ③实验中的问题和提高；实验结果及截图备注在语句的后面，各个语句均可正常执行;在创建表时要注意的约束名不可反复，即唯一性条件例如：创ＳC时外键不可命名为CNO_fk;由于在创建Ｔ表时已经用过了ＣNＯ_ｆk,则要换一个约束名在创建表时表名不可反复使用;尚有,在打完一条语句时应当检查一遍,以免打错;例如：再添加外键时，把单词ｒefeｒenｃes打错了,显示缺少关键字需要删除表时,若有外键引用时发现删不掉，有两种方法解决:a：先将有外键的表删除,再删除要删除的表；ｂ：删除表时使用CＡSCADＥ;就可以删除带有外键的表，其他表的约束性不变。ﻩ（4)收获与体会:实验二:数据操纵语言[实验日期]2０２3年1２月7日［实验目的]在实验一的基础上，纯熟掌握和使用DML语言，对数据进行增长、修改和删除操作。[实验内容］2.SQＬ数据操纵语句：例２－1：(插入数据)按前面各表中的数据分别插入到教学数据库的四个数据库表中。ﻩＳ表： IＮSERTＩNTＯSVALUEＳ('９600１','马小燕','ＣS','01＇,＇2１＇,'女');ＩNSEＲＴＩNTOSＶAＬUEＳ（'96002','黎明＇,'CS'，'０１'，'18','男＇）;INSEＲTINTOSVAＬUES('9６０0３'，'刘东明'，'MA','01','18','男');INSERTIＮTOＳＶＡLUEＳ（'960０4'，'赵志勇','IS＇,'０２',＇２0','男＇）;ＩNＳERＴIＮＴOSVALUES('97０01＇,'马蓉'，'ＭA','02','1９',＇女＇);ＩNSERTＩNTＯSVALUEＳ('97０02',＇李成功','CＳ＇,＇０１＇,'２0'，'男')；INSERTINTＯSVＡLUES(＇97０03＇,'黎明＇，'IS',＇0３','19','女');IＮSERTINTOSVＡLUEＳ(＇９7004',＇李丽＇,'CS＇,'02','１9＇,'女＇);INSERTＩNTOSVＡLUＥＳ(＇９6005',＇司马志明',＇CS'，'０2'，'18','男＇);ＩNSＥRTIＮTOSVＡLUES('２0231','赵薇','IS'，'02'，'19＇，'女');ﻩC表： INSERTＩNＴOCVAＬUEＳ('001＇,'数学分析'，＇144＇）;INＳＥRTINTOCVALUES('002','普通物理',＇14４');INＳERTINＴOＣＶALUES(＇003','微机原理','72'）;IＮＳEＲTINTＯＣVALUＥS('004','数据结构',＇72＇);INSＥＲTＩＮＴＯCVAＬUEＳ(＇0０5＇,'操作系统',＇64＇);INSERＴＩNTOCVALUES（'00６',＇数据库原理','64'）;INＳERTIＮＴOCＶALUEＳ('007','DB_Design',＇48');IＮSEＲTINＴOＣVALUEＳ('０08','程序设计'，'56'); T表：ﻩＩNSERTIＮTOTＶALＵEＳ('王成刚','男＇,'004',＇05-9月－１99９＇,＇CS＇);ＩＮSERTINTOTＶALUES(＇李正科＇，'男','003','0５-9月-１９99＇,＇ＣS');INＳERTＩNTＯTVALUＥS('严敏'，'女','001','05－9月-19９９','MA'）;INSEＲTINTOTVＡLUES('赵高','男＇,'004','05-9月－199９','IS＇）;ＩＮSＥＲTINTOＴVＡLUES('李正科','男'，'003',＇２3－2月-202３',＇ＭＡ'）;INSERTＩＮTOＴVALUES('刘玉兰'，'女',＇006＇,'23-2月－2０23','CＳ');INSＥRTＩNTOTＶＡLUES('王成刚'，'男','００４','23－2月-2023','ＩS');INSERTINTOＴVＡLUEＳ(＇马悦'，'女','0０8'，＇０６-9月-202３','CS');INＳＥRTINＴOTＶALＵES（'王成刚','男','007＇，'05-9月－1999＇,'CS')； SC表: INSEＲTINTOＳCVALＵES(＇9６0０1＇，'001','77.5＇);INSＥRTＩNTOSCVＡLUEＳ('960０1','00３',＇89');ＩNSＥＲＴINTOSCＶALUEＳ('96001','004'，'86＇);IＮSＥＲTＩNTOＳCVALＵEＳ('9600１','00５＇,'８２');INSEＲTINTOSCVALUES（＇9６002','０01','88＇)；IＮＳＥRＴＩNTOSＣＶＡLＵES(＇96002'，'003','92.５');INSERTIＮTOＳCVALUEＳ('960０2'，＇0０6','９0');INＳEＲTINTOＳCVALＵES('9600５','０0４'，'9２');INSＥRＴIＮTＯＳＣVＡLUES('9600５＇,'005',＇90＇);INSＥＲTIＮＴOSCＶALUES(＇96０0５'，＇006'，'8９')；IＮSERTＩNTOSCVＡLUEＳ('9６005＇，'007','７6');IＮＳERTINTOSＣVＡＬUES('９6０0３','001＇,'6９'）;INSＥRTINTＯSCVALUEＳ('97001＇,'０0１','9６');INSEＲTIＮTOSCVＡＬＵEＳ('９7０0１','0０8＇,'95'）;IＮSERTＩNTOＳCＶALUES（＇96004＇,'001',＇87');IＮSERTIＮTOSCＶＡＬUES('9６０03＇,＇0０３','9１'）；IＮSERＴINTOＳＣVAＬUES(＇９70０2','00３','91'）；IＮSEＲＴINTOＳCVAＬＵＥS('9７002','０04＇，'＇);INSＥRＴINTOSCＶＡLUES('9７0０2','０06＇,'92');INSEＲTINTOSＣＶＡLUES('9７0０4','005','9０'）;INSERTINTOＳＣＶＡLUＥS('97004'，＇006','85');ＩＮSERTINＴOSCVＡLUES(＇97004','0０８','7５');INＳERTIＮTOSＣＶＡLUES('970０3＇,'0０1'，'59'）;IＮSERTＩNTOＳＣVALUES('970０3','00３'，'58＇)；例2－2:(多行插入)将表Ｓtuｄent中在计算机系(‘CS’)的学生数据插入到表Stｕdent_Tｅmp中。(以上操作中,注意用ＣOMMIT提交数据,将数据保存到数据库服务器) INSEＲTINＴOＳTUDENT_TEMP(SEＬEＣTSNＯ,SNAＭE，SDEPT，SAGEFRＯMSWHＥＲＥＳDEPT=’CＳ’);例2-3：(运用查询来实现表的定义与数据插入)求每一个学生的平均成绩，把结果存入数据库表Sｔｕdenｔ_Gr中。ﻩCREAＴEＴABLESTUDＥＮT_ＧR（SNOＣHAＲ(5),SAVＧNUMBER(5,2));ﻩIＮSERTＩNTＯSTＵDＥNＴ_GＲ(ＳELECＴSNO，AＶG（SCＯRE)FＲＯMSCＯREＧROUPＢYSNＯ)；ﻩﻩ例2-4:（修改数据)将Sｔudｅnt_Ｔemp表中所有学生的年龄加2。 ＵPDATＥＳTUDEＮT＿TＥMPSＥTSA=SＡ+2;例2-５:（修改数据）将Coｕrｓe表中‘程序设计’课时数修改成与‘数据结构’的课时数相同。ﻩUPＤATＥＣSETCTIＭＥ＝７2WHERECNAＭE=’程序设计’;ﻩ例2-6:(插入数据)向Score表中插入数据（‘９８０0１’，‘001’,95)，根据返回信息解释其因素。 INSERＴINTOSCVＡＬUEＳ（‘9800１’,’001’,’95’);ﻩ由于ＳＣORE是参照SＴUDENT的SNO例2-7:(插入数据)向Sｃorｅ表中插入数据（‘97001’，‘010’,8０），根据返回信息解释其因素。ﻩＩNSERTＩNTOSCVＡLＵES(‘970０1’,’01０’，’8０’）;由于SCORE是参照COURSE的CNO例2-8:(删除数据)删除Stuｄent表中学号为‘9６0０1’的学生信息,根据返回信息解释其因素。ﻩDELEＴEFROＭSＷＨＥＲESNO＝9600１; 由于其他表参照STＵＤEＮＴ的SＮO，删除时要从子记录开始删除。例２-9：(删除数据）删除Course表中课程号为‘０03’的课程信息，根据返回信息解释其因素。ﻩDELETEFROMＣWHERECＮO=0０3；由于其他表参照COＵRSE的CＮO,删除时要从子记录开始删除。例2-１０：(删除数据)删除学生表Sｔudent_Ｔｅmp中学号以9６打头的学生信息。（此操作后，注意用ROＬLBAＣK回退也许更新的数据)DELEＴEＦROＭＳTUＤENT＿TＥMPWHERESＮＯLIKE‘96%’;ﻩ例2-11：(删除数据）删除数据库表Ｓtudent_Ｔｅmp中所有学生的数据。 DELEＴＥFRＯMＳTUＤＥNT_TＥMＰ; ＤELＥTEFROMSTUDENT_ＧＲ； 例2-1２:(删除表)删除数据库表Studeｎｔ＿Teｍｐ和Sｔudent＿Ｇr。 DROPＴAＢＬESTUDＥNT＿TEMＰ； DROPTＡBＬESＴUDＥＮT_GR; [实验规定]对数据库表进行插入、修改和删除数据的操作。[实验方法]①按照附录中的操作环节进行客户端的配置;

②将实验需求用SQL语句表达；③执行SQL语句;ﻫ④查看执行结果,假如结果不对的，进行修改,直到对的为止。[实验总结]SQL语句以及执行结果;

②对重点实验结果进行分析;

③实验中的问题和提高；

④收获与体会。实验三:数据查询语言[实验日期]２023年12月７日［实验目的]体会SQL语言数据查询功能的丰富和复杂。［实验内容]3．SQL数据查询语句:例3-1：(选择表中的若干列)求全体学生的学号、姓名、性别和年龄。 SELEＣＴSNO，SNAMＥ，SSEX,SAGEＦROMＳ;ﻩ例3－２：(不选择反复行)求选修了课程的学生学号。ﻩＳELＥCＴDISＴINCTSＮOFRＯＭSCWHＥRＥCNＯＩSNOTNULL;ﻩ例3-3：(选择表中的所有列)求全体学生的具体信息。ﻩSELECT＊FROMＳ;例3-４:(使用表达式)求全体学生的学号、姓名和出生年份。 SELECTSＮO,ＳNAＭＥ,２0２3-SＡＧＥAS“YEAＲOFBIRTH”FROＭS；ﻩ例３-５：(使用列的别名)求学生的学号和出生年份，显示时使用别名“学号”和“出生年份”。 SEＬECＴＳNＯＡS“学号”,2023－ＳAGEAS“出生年份”FROＭＳ；ﻩ例3－6：(比较大小条件)求年龄大于19岁的学生的姓名和年龄。 SELＥCTSNAME,SAGEFROMＳWHEREＳＡＧE>1９；ﻩ例3-７：(比较大小条件）求计算机系或信息系年龄大于18岁的学生的姓名、系和年龄。 SELECTＳNAME,ＳDEＰT，SAＧＥFＲOMSWＨERE(SDEPT=’ＣS’OＲSDEＰT＝’IS’)ＡNDＳAGE＞18;ﻩ例３－８：(拟定范围条件)求年龄在19岁与22岁（含2０岁和２2岁)之间的学生的学号和年龄。ﻩＳELECTSNO，ＳAGＥFRＯMSWHERＥSAGＥ>１9ＡNDSAGＥ<＝22;ﻩﻩ例3-9：(拟定范围条件)求年龄不在１9岁与２2岁之间的学生的学号和年龄。 SELＥＣTSＮO,SＡGＥFＲOMＳWＨEREＳAGE＜19OＲSAGＥ＞22；ﻩSELEＣＴSNO,ＳAGEFROＭＳWHERＥSAGENOTBＥTWＥＥＮ１9ANＤ22;ﻩ ﻩ例3-10:（拟定集合条件)求在下列各系的学生信息:数学系、计算机系。 SEＬECＴ*ＦＲOMＳWHERESDEPT＝’MＡ’ORSＤEPT＝’ＣＳ’;ﻩseｌecｔ

*

fｒom

Sｔudｅnt

wheｒe

sdｅｐt

in('ＭA＇,＇ＣS')； seｌｅｃｔ*fｒｏmＳtudｅｎtwhｅrｅSＤEＰT='MＡ'ｕnｉｏnselｅcｔ*frｏmSｔuｄentwｈereSEＰT='CS';ﻩﻩ 例3-11:(拟定集合条件)求不是数学系、计算机系的学生信息。ﻩsｅｌect

*

from

S

wheｒe

SＤEＰT

not

in('MA＇,'CS')；ﻩﻩﻩ例3-1２:(匹配查询)求姓名是以“李”打头的学生。 selｅct

*

fｒom

Ｓ

whｅrｅ

SNAME

like

'李%'; ﻩﻩ例3-13:（匹配查询)求姓名中具有“志”的学生。ﻩselecｔ*fｒｏmSｗｈｅreSＮAMElikｅ'%志％＇； 例3-14:(匹配查询）求姓名长度至少是三个汉字且倒数第三个汉字必须是“马”的学生。 seｌecｔ*fromSwhereSＮAＭEliｋe'％马____'; 例３-15：(匹配查询）求选修课程001或0０3，成绩在80至90之间，学号为96xxx的学生的学号、课程号和成绩。 ｓeｌectＳNO,CNO，SＣORＥｆromSCwｈereCNＯiｎ(＇０01'，'003')anｄSCOREbｅｔwｅen8０ａnd９0ａndSNＯlｉke'９6%'； ﻩ例3-16:(匹配查询）求课程名中包含’_’字符的课程号、课程名和学时数。 select

CＮO,CＮAME,CTＩＭE

froｍ

Ｃ

wｈere

ＣNＡMＥ

liｋe

'%\_%＇

eｓcapｅ

'\＇;ﻩ 例3-17：(涉及空值查询)求缺少学习成绩的学生的学号和课程号。ﻩseｌect

SNO,CNＯ

ｆrom

SＣ

where

SCORE

is

nｕll;ﻩ 例3－18:(控制行的显示顺序)求选修003课程或004课程的学生的学号、课程号和分数,规定按课程号升序、分数降序的顺序显示结果。 selｅcｔ

ＳNＯ,ＣＮO,SCORE

froｍ

ＳC

ｗherｅ

ＣNO

in(＇０0３＇，'00４')

ｏrdｅr

ｂy

ＣNO

asc,SCＯRE

dｅｓc; 例３-19：(组函数)求学生总人数。 selecｔ

cｏuｎt（SNO)

frｏm

S;ﻩ例3－20:(组函数）求选修了课程的学生人数。 selectcoｕnt(SNO）froｍSwhereSＮOｉn(ｓelｅctｄistiｎcｔSＮＯfroｍSCｗhｅreSCOREiｓnoｔnull）; 例３-21：（组函数)求计算机系学生的平均年龄。 sｅｌect

aｖg(SＡGE)

from

Swhere

SDEPT='ＣS＇;ﻩﻩ例３-22：(组函数)求选修了课程001的最高、最低与平均成绩。 sｅlecｔｍaｘ(SCＯRE)，ｍin(SＣＯＲＥ)，avｇ(SＣＯRE)ｆromSCwherｅＣＮO=＇０01';ﻩﻩ例3-23:（分组查询)求各门课程的平均成绩与总成绩。ﻩselectaｖg(SＣＯＲE),ｓuｍ(SCＯＲＥ)fｒomＳＣｇｒｏｕpｂyＣNＯ； 例3-24:(分组查询)求各系、各班级的人数和平均年龄。ﻩｓelect

SDEPT,ＳＣLASＳ,cｏunt（*）,avg(SＡGE)

from

SgｒｏｕpbySCLASS,SＤEPT;ﻩ 例3－25：(分组查询）输入以下查询语句并执行,观测出现的其结果并分析其因素。SＥＬECTSNAME,SDＥPＴ，ＣＯUNT（*)FRＯＭSＷHERＥSDEPT=’CS’GROUＰBYSDEPT； ﻩ例3-2６：（分组查询）分析以下语句为什么会出现错误。并给出对的的查询语句。SＥLＥCTSAGEFROMSGRＯUPBYSNO; ﻩ对分组后的语句查找满足条件的，用到HAＶＩNＧ例３-２7:（分组查询)求学生人数局限性３人的系及其相应的学生数。ﻩselectSDEPT,count(SNO)fromSgrｏupｂｙSＤEＰThaviｎｇｃount(SNＯ)＜3;ﻩﻩＳELEＣＴ后所选择的列，要在后面分组中出现,除非SELECT后面用到集函数例3－２８：(分组查询)求各系中除01班之外的各班的学生人数。 select

count(SNO)

from

Swhere

SＣLASSnｏｔin('01'）

grｏup

by

SCLASS,SDEPT；<＞例３-29：(涉及空值的查询)分别观测各组函数、行的显示顺序以及分组查询与空值的关系。ﻩＳELECＴＡVG（SCORE)FRＯMＳＣ;ＳEＬECTAＶG(SＣOＲE)FROMSCWHEＲＥSCＯREISＮOTNULＬ；结果都是84．3４782６1,说明ＡVG函数将空值没有涉及在内SＥLECTMAX（SＣORE)ＦＲＯMSＣ;ＳＥLECＴＭAX(SCOＲE）FＲOMSCＷHERＥSCＯREＩSNＯTNＵＬＬ;结果都是96,说明MＡX函数不受空值影响SELＥＣTMIＮ(SCＯRE)FRＯMSC;ＳELEＣTＭＩN（SＣＯＲE)FＲOＭＳCWHERESＣORＥＩＳＮOTNULL；结果都是5８，说明空值不是MIN函数的最小值SELECＴ

CＯUNT（SＣORＥ)

FROM

SＣ;SＥLECTＣＯUNT（SCOＲE)ＦROMSＣWHERESCOREIＳNOTNUＬL；结果是23，而总共有24个数据，因而ＣOUＮT函数对值为空的选项不予计算SELＥCT

＊

FRＯM

SＣ

ＯRDEＲ

BY

SＣＯRE;结果中分数为ＮＵLL的选项排在最后一个位置,而默认的排列顺序是升序的,所以在分组中ＮＵＬL是在后面的例3-30：(连接查询）求选修了课程０01且成绩在70分以下或成绩在９0分以上的学生的姓名、课程名称和成绩。ﻩselect

SNAME,CＮAME,ＳCＯRE

ｆrom

S,Ｃ,SC

ｗheｒe

S.SNO＝ＳC．SＮＯ

aｎd

C.CNＯ=SＣ．CNO

and

SＣ.ＣNO=＇001'

and

(ＳCOＲＥ<７0

ｏr

ＳCORE>90）;例3-３1：（连接查询与表的别名）求选修了课程的学生的学生姓名、课程号和成绩。ﻩsｅleｃｔSＮAME,ＣNＯ,SCOREfrｏmSＳ1，ＳCｗherｅＳ1.SNＯ＝SC.SNO;2４行例3-32：(自身连接查询)求年龄大于’李丽’的所有学生的姓名、系和年龄。 selｅｃtＳ1.SNAＭＥ,Ｓ1．SDＥＰT,S1．SAＧEfromSＳ1，SＳ2whereS2.ＳNAME=＇李丽＇

ａnd

Ｓ１．SAＧE>S２.SAＧE; 例３-３3：（外部连接查询)求选修了课程００２或0０3的学生的学号、课程号、课程名和成绩,规定必须将00２和0０3课程的相关信息显示出来。ﻩｓｅleｃtSNＯ，C．ＣNＯ,CＮAME,ScoreｆroｍCleftｏｕterjｏinＳCon(SC.CNO=C.ＣNO)whereC.CNOiｎ('0０２＇,'０03'); 例3-34:(子查询)求与‘黎明’年龄相同的学生的姓名和系。 AND后面的ﻩseleｃtSNＡMＥ，SDEPTｆrｏmＳwhereSAGEｉnﻩ (selectＳＡＧＥfromＳｗｈerｅSＮAMＥ='黎明＇)anｄSNAMＥ<>'黎明'；ﻩ例３-３5:(子查询）求选修了课程名为’数据结构’的学生的学号和姓名。 seleｃtSNＯ,SＮAMＥｆｒｏmSwhereSNＯiｎ(selｅcｔSNOｆrｏmSCwhereCＮOiｎ(sｅｌｅctCＮＯfromＣｗheｒeCＮＡＭＥ＝'数据结构'));例3－3６：(子查询AＮＹ)求比数学系中某一学生年龄大的学生的姓名和系。ﻩｓeｌectSＮAＭＥ,SDＥＰＴfrｏmSwhｅreＳAＧE>ａny(seleｃtSＡGEfromSｗhereSDＥPT='MＡ＇); 例3-３7：（子查询AＬL）求比数学系中全体学生年龄大的学生的姓名和系。ﻩselectSNAME,ＳDEＰTfroｍSwhｅｒｅＳＡGE>all(selectSAGEｆrｏmSwｈereＳDEPT＝'MA'); 例3-3８：(子查询EＸIＳＴS)求选修了课程004的学生的姓名和系。ﻩselect

SNＡME,SDＥPT

ｆrom

S

where

eｘｉsｔs

(sｅｌect

＊

ｆｒｏm

SＣ

ｗhｅre

SC．SNO=S．SＮＯ

ａnd

CNO='004');ﻩ例3-39：(返回多列的子查询)求与‘黎明’同系且同龄的学生的姓名和系。 sｅlｅｃｔS1.ＳNAME,S1.SDEPTｆromSS1ｗhｅｒeeｘiｓts(sｅleｃt*fromSＳ2ｗｈereS2．SNＡME='黎明'andS１.ＳDＥPT＝Ｓ2.SDＥPTａnｄS1.SAGE=S2.SAGＥ)andSＮAMＥ<＞'黎明'; 例3-４0:(多个子查询)求与‘‘黎明’同系,且年龄大于‘李丽’的学生的信息。ﻩseｌect*ｆｒomSwhｅreSＤEPTiｎ(sｅlectSDEＰTfroｍＳｗheｒeSＮAME='黎明')ａndＳＡＧE>(selｅｃｔSAGＥfromSwhereSＮAME＝'李丽＇）;例3-４1:（子查询中使用表连接)求数学系中年龄相同的学生的姓名和年龄。 sｅleｃtSNＡＭE,SＡGEfｒomSwｈereＳNOin(seｌeｃｔS1.SＮＯfroｍＳS1,SＳ2wheｒeS1.SDＥPT=＇ＭＡ＇aｎdS1．SAＧE＝S2．SAGE);或ｓｅleｃｔ

ｄistinct

S1.SＮAME，Ｓ1.SAGＥ

from

Ｓ

S1

,S

Ｓ2

wｈere

S1.ＳDEPT

='MA'

anｄ

S1.SAGＥ＝S２.SＡＧE; ﻩ例３-42:(连接或嵌套查询)检索至少选修王成刚老师所授课程中一门课程的女学生姓名。 seｌectSNAMEfrｏmＳwheｒeSSEX='女'andＳNOin(sｅｌeｃtSNOfroｍSCwherｅＣNOin(selｅctCＮOfrｏmTwｈｅreTNAMＥ='王成刚')); 例3-４3:(嵌套与分组查询)检索选修某课程的学生人数多于3人的教师姓名。 ｓｅｌecｔdistinｃｔＴNAＭEｆromTwhereCNＯiｎ(selectＣNＯfroｍＳCgrｏｕpｂyCＮOhavingcouｎt(ＳNO)>3); 例3－44：(集合查询)列出所有教师和同学的姓名和性别。 sｅｌeｃtSNAME,ＳSEＸfromSuｎｉonselectTNAMＥ,TＳEXfromT;ﻩ例３-４5：（相关子查询)求未选修课程004的学生的姓名。 selecｔ

ＳNAＭE

fｒom

S

ｗhｅｒe

SＮO

ｎot

ｉn(ｓelect

SNO

from

SC

wｈere

CNＯ='0０4＇)； 例3－４6:(相关子查询)求选修了所有课程的学生的姓名。 select

SNＡME

ｆrｏｍ

S

wｈerｅ

ｎot

exiｓts(selｅct

*

fｒoｍ

C

wheｒe

ｎｏt

ｅxiｓts

(sｅlecｔ

*

froｍ

SC

whｅre

SＮＯ=S.ＳNＯ

and

CNO=Ｃ.CＮO))； 未有学生选了所有课程。例3-47：(相关子查询)求至少选修了学生‘9６002’所选修的所有课程的学生的学号。 selectSNOfromＳwｈｅreｎotexiｓｔs（seleｃt＊fｒomSCSc1ｗhereSｃ1．SNO＝'96002'andnoteｘisｔｓ(ｓｅｌeｃｔ＊fｒomSCSc2whereSc2.SＮＯ＝S.SNＯａnｄSｃ1．ＣNO＝Sｃ2.ＣNＯ))；ﻩ例3-48：(相关子查询)求成绩比所选修课程平均成绩高的学生的学号、课程号、和成绩。 ｓeｌeｃtSc1.SNO,Ｓc1.CNO,Sｃ1.ＳＣＯREfromSCSc1wｈereSCORE＞(sｅlectavg(SCORE)ｆrｏmSCＳc2whereSc1.SNO=Sｃ2.SNO);ﻩ例３-49:（相关子查询)查询被一个以上的学生选修的课程号。 sｅlectCNOfroｍＣｗherｅCNＯin(seｌｅctCＮOfromSCgrouｐｂyCＮOhavinｇcｏunt（ＳNO)>１);例3－5０：(相关子查询)查询所有未选课程的学生姓名和所在系。ｓelｅctSNＡＭE,SＤEPTfromSwheｒeＳNＯnotin(selecｔｄistinctSNＯfｒｏmＳＣ）;[实验规定］对数据库表进行各种查询操作。[实验方法］①将实验需求用SQL语句表达；

②执行SＱL语句；ﻫ③查看执行结果，假如结果不对的，进行修改,直到对的为止。[实验总结]ＳＱL语句以及执行结果;ﻫ②对重点实验结果进行分析；

③实验中的问题和提高；

④收获与体会实验四：视图、授权控制与事务解决[实验日期]2023年12月７日[实验目的］通过实验进一步理解视图的建立和更新、数据库的权限管理和事务解决功能。[实验内容]4．SＱL视图的定义与操纵：例4-１:(建立视图)建立计算机系的学生的视图STＵDEＮT_CS。ﻩcreateｖieｗSＴUＤENT_ＣSassｅlect*fｒｏｍＳｗhｅreSDＥPＴ='CＳ'；例4－2：(建立视图）建立由学号和平均成绩两个字段的视图ＳTＵＤENT_GR。 ｃrｅａｔe

view

STUDENT＿GR(SＮO,ＡVERAGE)

as

ｓelect

ＳＮＯ,avg(ＳＣＯRE)

from

ＳＣ

ｇrｏup

ｂy

ＳＮO；例4-3：(视图查询)运用视图STＵＤENＴ＿ＣS,求年龄大于19岁的学生的所有信息。ﻩselect*frｏmSTUDENT_CSwherｅＳAＧE＞19;例4－4:（视图查询）运用视图SＴUDＥNT_ＧＲ,求平均成绩为88分以上的学生的学号和平均成绩。ﻩｓｅleｃt

＊

fｒom

STＵDENT_GR

ｗheｒe

AVERAＧE>８8;例４－5：(视图更新)运用视图STＵDENT＿ＣS，增长学生(‘96０06’，‘张然’，‘CS’，‘0２’,‘男’，19)。 inserｔ

ｉntｏ

STUＤENT_CS

ｖalues('96006','张然',＇ＣS',＇02＇,１9,'男')

;例４－6：(视图更新)运用视图ＳTＵＤＥNT_CS,将学生年龄增长1岁。观测其运营结果并分析因素。 uｐdateＳTUDENT_CSsetSAGE＝SAGＥ+1;例4-7：(视图更新)运用视图STUDＥNＴ_GR，将平均成绩增长2分。观测其运营结果并分析因素。 ｕpdａｔｅ

SＴUDEＮＴ_ＧR

sｅt

AVEＲAGE＝ＡVERＡＧE+2;例4-8:(视图更新)删除视图SＴUＤＥNT＿CS中学号为‘9６006’的学生的所有数据。ﻩdeｌetefｒomSTUＤENT_CＳwheｒｅSNO='9６006';ﻩ例４－9：(视图更新)删除视图ＳTUＤＥNT_GＲ的所有数据。 dｅleｔe

fｒｏm

STUＤＥNT_GＲ;

ﻩ例4-10：(删除视图)删除视图ＳTUDENＴ_CS和SＴＵＤENＴ_GR。ﻩdｒopｖiewSTUＤＥＮT_CS； ｄropvieｗSTＵDENＴ＿GR; 5．ＳQL数据控制语句：例5－1:(授权）给左右邻近同学(用户)授予在表Sｔudent上的SEＬEＣT权限,并使这两个用户具有给其他用户授予相同权限的权限。ﻩgrａntsｅlectoｎStoUwithgrantoption； 例５-2：(授权)给邻近同学（用户）授予Teach表上的所有权限。 graｎｔaｌlｐrivilｅgeｓonＴtｏU; 例5-3：(授权)给所有用户授予Scoｒe表上的ＳEＬECT权限。granｔseｌectonSCｔopublic;例５-4:（授权验证)观测左右邻近同学查询你所授权的表中的内容。ﻩ例5-5：(收回授权）收回上面例子中的所有授予的权限。ﻩrevokeselectonStuｄentｆroｍU；revokeａlｌpｒiviｌeｇesoｎＴeａcｈｆromU;revoｋeｓelｅｃtoｎScorｅfrompｕblic；6．SQL事务解决：例６-1：(事务回退）将课程名称表中的‘程序设计’课程学时数修改为80、‘微机原理’课程学时数修改为70学时,查询所有课程的总学时数后,取消所有修改(ROLLＢACK)。再次查询所有课程的总学时数。注意比较分析两次查询的结果。 updateCsetCTＩME=80whereＣNAME='程序设计'; upｄaｔeCｓeｔCTIME=70wｈereCNＡMＥ＝'微机原理'; seｌect＊frｏmC;ﻩ rollｂack;ﻩseｌｅct*fromC; 例６-2:(事务提交)将课程名称表中的‘程序设计’课程学时数修改为80、‘微机原理’课程学时数修改为70学时,查询所有课程的总学时数后,确认所有修改(CＯMMIT)。再次查询所有课程的总学时数。注意比较分析两次查询的结果。updaｔeCｓeｔCTＩME=80wｈｅrｅCNＡME='程序设计';updateCsｅｔCTIME＝７0whereCNAＭE='微机原理';sｅlecｔ

*

from

C;[实验规定]①建立视图，视图查询,视图更新；

②给某一或所有用户授权和收回授权；ﻫ③事务回退，事务提交。[实验方法]①将实验需求用SQL语句表达；ﻫ②执行SQL语句；ﻫ③查看执行结果,假如结果不对的，进行修改,直到对的为止。[实验总结]①ＳＱL语句以及执行结果;ﻫ②对重点实验结果进行分析；

③实验中的问题和提高；

④收获与体会。实验五:Oracle存储过程与触发器［实验目的]通过实验进一步理解和掌握Oracle数据库的存储过程和触发器。[实验内容]4．存储过程与触发器：例7－1：（存储过程)创建一个显示学生总人数的存储过程。方法一:（常用)CREATEORREＰLＡCＥPRＯCEDURＥSTU_ＣOＵNTASAＮＵMBEＲ;BＥGIＮSＥLEＣTCOUNT(*)INTOAＦROＭＳＴUDEＮT；DBＭS_OUTPUT.PUT_LＩNE(＇学生总人数为＇|｜A)；ＥND；方法二:例７-2：(存储过程)创建显示学生信息的存储过程STUＤＥNT_LIST，并引用STＵ_COUNT存储过程。CＲＥAＴＥOＲREＰＬAＣＥＰＲＯCＥDURESTUDENＴ＿LISＴAＳAＮＵMBER;CURSＯRSTUDENT_CURＳORIＳSELECT*FRＯMＳＴＵDENＴ;ＢEGINFORSTUDENT_RECＯRＤIＮSTUDEＮT_ＣURSORＬＯOPDＢＭＳ_OUTＰUＴ.PUＴ_LＩNE（STＵDENT_ＲＥCORD.ＳNO||＇'｜|SＴUDENT_RECORD．SNAME||＇'||STUＤENT_ＲＥCOＲD．ＳDEPT|｜'＇|｜ＳTUDENT＿REＣORD.ＳＣLASS||＇＇｜｜ＳTＵＤENT_RＥＣORD.SSEX|｜''｜|STUDENT＿RＥＣORD.ＳAGE);ENＤＬOOP;STU_COUNT（)；END;/例7-3：(存储过程)创建一个显示学生平均成绩的存储过程。CREATEOＲＲEPLAＣEＰROCＥDＵREＳＴU＿AVＧ(PＩNSＣORE．SNO%TYPE）ASANUMＢEＲ(5,２)；BＥＧINSELECTAVG(SCORE）INTOＡFROMＳCＯREWHEＲESＮO=ＰGＲＯUPBＹSNＯ；DBＭS＿OUＴＰUT．PUT_LIＮE('学号为'||P||＇的学生的平均成绩为：'||A);END;／例7-4：(存储过程)创建显示所有学生平均成绩的存储过程。ＣREATEOＲREPLACEPRＯCEＤURESＴUDEＮT＿SＡVＧAＳSAＶGNUＭBER(５,2);ＳＳNOCHAＲ（５);ＣＵＲSORSCORE_ＣURSORISSELECTＳNO,AVＧ(SCORＥ)ＦROMSCＯＲEGROＵPBYＳNＯ;BEGINＯPENSCORE_CUＲSＯR;LOOPＦＥTCHSCORE_CＵRSORINTＯＳSＮO,SAVG;EＸIＴＷＨEＮSCORＥ＿CＵRＳOR％ＮOTFＯUND;ＤBMS＿OUＴPＵT.PUＴ＿LINE(SSNＯ｜|＇'|｜SAＶＧ）;ＥＮDLＯOP；ＣLOSＥＳCOＲＥ_CＵRSＯR;END；/例7-5:(修改数据库表)在Stｕｄent表中增长SAVG(N,6，2）字段。ALTERTABＬEＳTUDENTＡDDSAＶＧＮＵMBER(6,2);例7-6:(存储过程)创建存储过程,计算每个学生的平均成绩保存到学生表SAVＧ字段中。CREＡＴEORREPＬACEＰRＯCＥDURESTＵDＥＮT_ＩNSERTSAＶGASSSAVGNＵMBＥR(5,２)；SＳNOCHAR(5）;ＣURSＯRSCOＲE_CURSＯRＩSＳELEＣＴSNO，AVG(SCＯRE)FROMＳCＯＲＥＧRＯUPBYSＮO;BEGＩNOPＥNＳＣORＥ_ＣURＳOＲ；LＯOPFＥＴCHＳCＯRＥ_CURSＯRＩNTＯＳSNO,SSAVG;UPＤATESTUDＥNTSETSＡVG=SSAＶGWHERESＮO=ＳＳＮO;ＥＸITWＨENSＣOＲE_CＵRSＯＲ%ＮOＴFＯUND;