數(shù)據(jù)庫實驗（一）
熟悉環(huán)境、建立/刪除表、插入數(shù)據(jù)
?
Drop?table?表名
update?dbtest?set?test=1
select?*?from?dbscore
?
1．?教師信息(教師編號、姓名、性別、年齡、院系名稱)
test1_teacher：tid?char?6?not?null、name?varchar?10?not?null、sex?char?2、age?int、dname?varchar?10。
根據(jù)教師名稱建立一個索引。
|
教師編號 |
教師姓名 |
性別 |
年齡 |
院系名稱 |
|
100101 100102 100103 |
張老師 李老師 馬老師 |
男 女 男 |
44 45 46 |
計算機學院 軟件學院 計算機學院 |
?
?
?
?
?
1、create?table?test1_teacher(
?????????tid??char(6)??primary?key,
?????????name?varchar(10)?not?null,
?????????sex??char(2),
?????????age??int,
?????????dname?varchar(10)
?????????)??
?
2．?學生信息（學生編號、姓名、性別、年齡、出生日期、院系名稱、班級）
test1_student：sid?char?12?not?null、name?varchar?10?not?null、sex?char?2、age?int、birthday?date（oracle的date類型是包含時間信息的，時間信息全部為零）、dname?varchar?10、class?varchar(10)。
根據(jù)姓名建立一個索引。
|
學號 |
姓名 |
性別 |
年齡 |
出生日期 |
院系名稱 |
班級 |
|
200800020101 200800020102 200800020103 |
王欣 李華 趙巖 |
女 女 男 |
１９ ２０ １８ |
1994-2-2 1995-3-3 1996-4-4 |
計算機學院 軟件學院 軟件學院 |
2010 2009 2009 |
?
2、create?table?test1_student(
?????????sid?char(12)?primary?key,
?????????name?varchar(10)?not?null,
?????????sex??char(2),
?????????age??int,
?????????birthday??date,
?????????dname???varchar(10),
?????????class???varchar(10)
?????????)??
3．?課程信息（課程編號、課程名稱、先行課編號、學分）
test1_course：cid?char?6?not?null、name?varchar?10?not?null、fcid?char?6、
credit?numeric?2，1（其中2代表總長度，1代表小數(shù)點后面長度）。
根據(jù)課程名建立一個索引。
|
課程號 |
課程名 |
先行課程號 |
學分 |
|
300001 300002 300003 |
數(shù)據(jù)結(jié)構(gòu) 數(shù)據(jù)庫 操作系統(tǒng) |
? 300001 300001 |
2 2.5 4 |
?
?
?
?
?
3、create?table?test1_course(
?????????cid?char(6)?primary?key,
?????????name??varchar(10)??not?null,
?????????fcid?char(6),
?????????credit?numeric(2,1)
?????????)?
?
4．?學生選課信息（學號、課程號、成績、教師編號）
test1_student_course：sid?char?12?not?null、cid?char?6?not?null、
score?numeric?5，1（其中5代表總長度，1代表小數(shù)點后面長度）、tid?char?6。
|
學號 |
課程號 |
成績 |
教師編號 |
|
200800020101 200800020101 200800020101 |
300001 300002 300003 |
91.5 92.6 93.7 |
100101 100102 100103 |
?
4、?create?table?test1_student_course(
?????????sid?char(12)?,
?????????cid?char(6)??,
?????????score?numeric(5,1),
?????????tid?char(6),
?????????primary?key(sid,cid),
?????????FOREIGN?KEY?(sid)?REFERENCES?test1_student(sid),
?????????FOREIGN?KEY?(cid)?REFERENCES?test1_course(cid),
?????????FOREIGN?KEY?(tid)?REFERENCES?test1_teacher(tid)
?????????)??
?
?
?
5．?教師授課信息（教師編號、課程編號）
test1_teacher_course：tid?char?6??not?null，cid?char?6?not?null。
|
教師編號 |
課程號 |
|
100101 100102 100103 |
300001 300002 300003 |
?
?
?
?
?
5、create?table??test1_teacher_course(
?????????tid?char(6)??,
?????????cid?char(6)??,
?????????primary?key(tid,cid),
?????????FOREIGN?KEY?(tid)?REFERENCES?test1_teacher(tid),
?????????FOREIGN?KEY?(cid)?REFERENCES?test1_course(cid)
?????????)?
?
二、創(chuàng)建索引
?
????1、create?index?index_table1?on?test1_teacher(name);
????2、create?index?index_table2?on?test1_student(name);
????3、create?index?index_table3?on?test1_course(name);
?
?
?
三、插入數(shù)據(jù)
1、
insert?into?test1_teacher?values('100101','張老師','男',44,'計算機學院');
insert?into?test1_teacher?values('100102','李老師','女',45,'軟件學院');
insert?into?test1_teacher?values('100103','馬老師','男',46,'計算機學院');
?
2、
insert?into?test1_student?values('200800020101','王欣','女',19,to_date('19940202','yyyymmdd'),'計算機學院','2010');
?
insert?into?test1_student?values('200800020102','李華','女',20,to_date('19950303','yyyymmdd'),'軟件學院','2009');
?
insert?into?test1_student?values('200800020103','趙巖','男',18,to_date('19960404','yyyymmdd'),'軟件學院','2009');
?
3、
insert?into?test1_course?values('300001','數(shù)據(jù)結(jié)構(gòu)','',2);
insert?into?test1_course?values('300002','數(shù)據(jù)庫','300001',2.5);
insert?into?test1_course?values('300003','操作系統(tǒng)','300001',4);
?
4、
Insert?into?test1_student_course?values('200800020101','300001',91.5,'100101');
?
insert?into?test1_student_course?values('200800020101','300002',92.6,'100102');
?
insert?into?test1_student_course?values('200800020101','300003',93.7,'100103');
?
5、
insert?into?test1_teacher_course?values
('100101','300001');
insert?into?test1_teacher_course?values
('100102','300002');
insert?into?test1_teacher_course?values
('100103','300003');
?
數(shù)據(jù)庫實驗（二）?檢索查詢
?
1、找出沒有選修任何課程的學生的學號、姓名。
create?table?test2_01?as?select?sid?，name
from?pub.student
where?sid?not?in
(select?sid
from?pub.student_course)
?
2、找出至少選修了學號為“200900130417”的學生所選修的一門課的學生的學號、姓名。
create?table?test2_02?as?select?distinct?student.sid，name
from?pub.student,?pub.student_course
where??student_course.sid?=?student.sid?and?student_course.cid?in
(select?cid
from?pub.student_course
where?sid='200900130417')
?
3、找出至少選修了一門其先行課程號為“300002”號課程的學生的學號、姓名。
create?table?test2_03?as?select?distinct?student.sid，name
from?pub.student,?pub.student_course
where??student_course.sid?=?student.sid?and?student_course.cid?in
(select?cid
from?pub.course
where?fcid='300002')
?
4、找出選修了“操作系統(tǒng)”并且也選修了“數(shù)據(jù)結(jié)構(gòu)”的學生的學號、姓名。
create?table?test2_04?as?select?sid,name
from?pub.student
where?sid?in
(select?sid
from?pub.student_course,pub.course
where?student_course.cid=course.cid
and?name?='操作系統(tǒng)')
and?sid?in
(select?sid
from?pub.student_course,pub.course
where?student_course.cid=course.cid
and?name?='數(shù)據(jù)結(jié)構(gòu)')
?
?
?
?
5、?查詢20歲的所有有選課的學生的學號、姓名、平均成績(avg_score，此為列名，下同)（平均成績四舍五入到個位）、總成績(sum_score)
create?table?test2_05?as?select?student.sid,name，cast(avg(score)???as???numeric(5,0))?avg_score,sum（score）?sum_score
from?pub.student,pub.student_course
where?student.sid?=?student_course.sid?and?age?='20'
group?by?student.sid，name
?
6、查詢所有課以及這門課的最高成績，test2_06有兩個列：課程號cid、最高成績max_score
create?table?test2_06?as?select?sid，max(score）?max_score
from?pub.student_course
group?by?cid
?
7、?查詢所有不姓張、不姓李、也不姓王的學生的學號sid、姓名name
create?table?test2_07?as?select?sid，name
from?pub.student
where?name?not?in
（select?name
from?pub.student
where?name?like?'張%'?or?name?like?'李%'?or?name?like?'王%')
?
8、查詢學生表中每一個姓氏及其人數(shù)（不考慮復(fù)姓），test2_08有兩個列：second_name、p_count
create?table?test2_08?as?select?substr(name,1,1)?second_name,count(*)?p_count
from?pub.student
group?by?substr(name,1,1)
?
9、查詢選修了300003號課程的學生的sid、name、score
create?table?test2_09?as?select?student.sid，student.name，score
from?pub.student,pub.student_course
where?student.sid?=?student_course.sid
and?cid?='300003'
?
10、查所有有成績記錄的學生sid和cid
create?table?test2_10?as?select?sid，cid
from?pub.student_course
where?score?is?not?null
?
數(shù)據(jù)庫實驗（三）?復(fù)制表、刪除數(shù)據(jù)
?
1.將pub用戶下的Student_31及數(shù)據(jù)復(fù)制到主用戶的表test3_01，刪除表中的學號不是12位數(shù)字的錯誤數(shù)據(jù)。
?
create?table?test3_01?as?select?*?from?pub.Student_31
delete?from?test3_01?where?length(translate(sid,'\0123456789','\'))>0
?
2.將pub用戶下的Student_31及數(shù)據(jù)復(fù)制到主用戶的表test3_02，刪除表中的出生日期和年齡不一致?(年齡=2012-出生年份)?的?錯誤數(shù)據(jù)。
?
create?table?test3_02?as?select?*?from?pub.Student_31
delete?from?test3_02?where?age?<?2012?-?extract(year?from?birthday)
delete?from?test3_02?where?age?>?2012?-?extract(year?from?birthday)
?
3.將pub用戶下的Student_31及數(shù)據(jù)復(fù)制到主用戶的表test3_03，刪除表中的性別有錯誤的數(shù)據(jù)（性別只能夠是“男”、“女”或者空值）。
?
create?table?test3_03?as?select?*?from?pub.Student_31
delete?from?test3_03?where?sex?not?in(select?sex?from?test3_03?where?sex='男'?or?sex='女'?or?sex=null)
?
4.將pub用戶下的Student_31及數(shù)據(jù)復(fù)制到主用戶的表test3_04，刪除表中的院系名稱有空格的、院系名稱為空值的或者院系名稱小于3個字的錯誤數(shù)據(jù)。
?
create?table?test3_04?as?select?*?from?pub.student_31
delete?from?test3_04?where?dname?is?null?or?length(dname)<3?or?dname?like?'%?%'
?
5.將pub用戶下的Student_31及數(shù)據(jù)復(fù)制到主用戶的表test3_05，刪除表中的班級不規(guī)范的數(shù)據(jù)，不規(guī)范是指和大多數(shù)不一致。
?
create?table?test3_05?as?select?*?from?pub.student_31
delete?from?test3_05?where?length(class)>4
?
6.將pub用戶下的Student_31及數(shù)據(jù)復(fù)制到主用戶的表test3_06，刪除表中的錯誤數(shù)據(jù)，不規(guī)范的數(shù)據(jù)也被認為是錯誤的數(shù)據(jù)。
?
create?table?test3_06?as?select?*?from?pub.student_31
?
delete?from?test3_06?where?length(translate(sid,'/0123456789','/'))<12
?
delete?from?test3_06?where?age>2012-extract(year?from?birthday)?or?age<2012-extract(year?from?birthday)
?
delete?from?test3_06?where?name?is?null?or?length(name)<2?or?name?like?'%?%'
?
delete?from?test3_06?where?sex?not?in?(select?sex?from?test3_06?where?sex?='男'?or?sex='女'?or?sex=null)
?
delete?from?test3_06?where?dname?is?null?or?length(dname)<3?or?name?like?'%?%'
?
delete?from?test3_06?where?length(class)>4
?
7.將pub用戶下的Student_course_32及數(shù)據(jù)復(fù)制到主用戶的表test3_07，刪除其中的錯誤數(shù)據(jù)，錯誤指如下情況：學號在學生信息pub.student中不存在的；
?
create?table?test3_07?as?select?*?from?pub.Student_course_32
delete?from?test3_07?where?sid?not?in?(select?sid?from?pub.student)
?
8.將pub用戶下的Student_course_32及數(shù)據(jù)復(fù)制到主用戶的表test3_08，刪除其中的錯誤數(shù)據(jù)，錯誤指如下情況：課程號和教師編號在教師授課表pub.teacher_course中不同時存在的，即沒有該教師教該課程；
?
create?table?test3_08?as?select?*?from?pub.student_course_32
?
delete?from?test3_08?where?(cid,tid)?not?in(select?test3_08.cid,test3_08.tid?from?test3_08,pub.teacher_course?where?test3_08.cid=pub.teacher_course.cid?and?test3_08.tid=pub.teacher_course.tid)
?
9.將pub用戶下的Student_course_32及數(shù)據(jù)復(fù)制到主用戶的表test3_09，刪除其中的錯誤數(shù)據(jù)，錯誤指如下情況：成績數(shù)據(jù)有錯誤（需要先找到成績里面的錯誤）。
?
create?table?test3_09?as?select?*?from?pub.student_course_32
?
delete?from?test3_09?where?score<0?or?score>100
?
10.將pub用戶下的Student_course_32及數(shù)據(jù)復(fù)制到主用戶的表test3_10，刪除其中的錯誤數(shù)據(jù)。
?
create?table?test3_10?as?select?*?from?pub.student_course_32
?
delete?from?test3_10?where?score<0?or?score>100
?
delete?from?test3_10?where?sid?not?in?(select?sid?from?pub.student)
?
delete?from?test3_10?where?cid?not?in?(select?cid?from?pub.course)
?
delete?from?test3_10?where?tid?not?in?(select?tid?from?pub.teacher)
?
delete?from?test3_10?where?(cid,tid)?not?in(select?test3_10.cid,test3_10.tid?from?test3_10,pub.teacher_course?where?test3_10.cid=pub.teacher_course.cid?and?test3_10.tid=pub.teacher_course.tid)
?
Test4?復(fù)制表、修改表結(jié)構(gòu)、修改數(shù)據(jù)
?
1、?將pub用戶下表student_41及數(shù)據(jù)復(fù)制到主用戶的表test4_01中，使用alter?table語句為表增加五個列：“總成績:sum_score”、?“平均成績:avg_score”（四舍五入到個位）、“總學分:sum_credit”、“院系編號:did?varchar(2)?”。
使用update語句,利用pub.student_course、pub.course，統(tǒng)計?“總成績”；
?
create?table?test4_01?as?select*?from?pub.student_41
alter?table?test4_01?add?sum_score?int
alter?table?test4_01?add?avg_score?numeric(5,1)
alter?table?test4_01?add?sum_credit?int
alter?table?test4_01?add?did?varchar(2)??
select?*from?test4_01
create?table?test01?as?select?sid,sum(score)?sum_score?from?pub.student_course
group?by?sid
update?test4_01
set?sum_score=(select?test01.sum_score
???????????????from?test01
???????????????where?test01.sid=test4_01.sid)
?
2、?將pub用戶下表student_41及數(shù)據(jù)復(fù)制到主用戶的表test4_02中，使用alter?table語句為表增加五個列：“總成績:sum_score”、?“平均成績:avg_score”（四舍五入到個位）、“總學分:sum_credit”、“院系編號:did?varchar(2)?”。
利用pub.student_course、pub.course，統(tǒng)計“平均成績”；
?
create?table?test4_02?as?select*?from?pub.student_41
alter?table?test4_02?add?sum_score?int
alter?table?test4_02?add?avg_score?numeric(5,1)
alter?table?test4_02?add?sum_credit?int
alter?table?test4_02?add?did?varchar(2)
select?*from?test4_02
create?table?test02?as?select?sid,avg(score)?avg_score?from?pub.student_course?group?by?sid
update?test4_02
set?avg_score=(select?test02.avg_score
???????????????from?test02
???????????????where?test02.sid=test4_02.sid)
?
3、?將pub用戶下表student_41及數(shù)據(jù)復(fù)制到主用戶的表test4_03中，使用alter?table語句為表增加五個列：“總成績:sum_score”、?“平均成績:avg_score”（四舍五入到個位）、“總學分:sum_credit”、“院系編號:did?varchar(2)?”。
使用update語句,利用pub.student_course、pub.course，統(tǒng)計?“總學分”；
?
drop?table?test4_03
create?table?test4_03?as?select*?from?pub.student_41
alter?table?test4_03?add?sum_score?int
alter?table?test4_03?add?avg_score?numeric(5,1)
alter?table?test4_03?add?sum_credit?int
alter?table?test4_03?add?did?varchar(2)
select?*from?pub.course
drop?table?test03
create?table?test031?as?select?sid,cid,score?from?pub.student_course
alter?table?test031?add?credit?int
update?test031
set?credit=(select?credit
????????????from?pub.course
????????????where?test031.cid=pub.course.cid?and?score>=60)?
update?test031
set?credit=0
where?score<60
create?table?test03?as?select?sid,sum(credit)?sum_credit?from?test031?
group?by?sid
update?test4_03
set?sum_credit=(select?test03.sum_credit
???????????????from?test03
???????????????where?test03.sid=test4_03.sid)
?
4、?將pub用戶下表student_41及數(shù)據(jù)復(fù)制到主用戶的表test4_04中，使用alter?table語句為表增加五個列：“總成績:sum_score”、?“平均成績:avg_score”（四舍五入到個位）、“總學分:sum_credit”、“院系編號:did?varchar(2)?”。
根據(jù)院系名稱到pub.department或者pub.department_41中，找到對應(yīng)編號，填寫到院系編號中，如果都沒有對應(yīng)的院系，則填寫為00。
?
drop?table?test4_04
drop?table?test04
create?table?test4_04?as?select*?from?pub.student_41
alter?table?test4_04?add?sum_score?int
alter?table?test4_04?add?avg_score?numeric(5,1)
alter?table?test4_04?add?sum_credit?int
alter?table?test4_04?add?did?varchar(2)
select?*from??pub.department
create?table?test04?as?select*?from?pub.department
insert?into?test04?select*from?pub.department_41
update?test4_04
set?did=(select?test04.did
???????????????from?test04
???????????????where?test4_04.dname=test04.dname)
where?dname?in(select?dname?from?test04)
update?test4_04
set?did='00'
where?dname?not?in(select?dname?from?test04)?or?dname?is?null
?
update?dbtest?set?test=4
select?*?from?dbscore
?
5、?將pub用戶下表student_41及數(shù)據(jù)復(fù)制到主用戶的表test4_05中，使用alter?table語句為表增加五個列：“總成績:sum_score”、?“平均成績:avg_score”（四舍五入到個位）、“總學分:sum_credit”、“院系編號:did?varchar(2)?”。
（1）?利用pub.student_course、pub.course，統(tǒng)計?“總成績”；
（2）?利用pub.student_course、pub.course，統(tǒng)計“平均成績”；
（3）?利用pub.student_course、pub.course，統(tǒng)計?“總學分”；
（4）?根據(jù)院系名稱到pub.department或者pub.department_41中，找到對應(yīng)編號，填寫到院系編號中，如果都沒有對應(yīng)的院系，則填寫為00。
?
??? create?table?test4_05?as?select*?from?pub.student_41
alter?table?test4_05?add?sum_score?int
alter?table?test4_05?add?avg_score?numeric(5,1)
alter?table?test4_05?add?sum_credit?int
alter?table?test4_05?add?did?varchar(2)
update?test4_05
set?sum_score=(select?test4_01.sum_score
???????????????from?test4_01
???????????????where?test4_01.sid=test4_05.sid)
update?test4_05
set?avg_score=(select?test4_02.avg_score
???????????????from?test4_02
???????????????where?test4_02.sid=test4_05.sid)
update?test4_05
set?sum_credit=(select?test4_03.sum_credit
???????????????from?test4_03
???????????????where?test4_03.sid=test4_05.sid)
update?test4_05
set?did=(select?test04.did
????????????????from?test04
????????????????where?test04.dname=test4_05.dname)
where?dname?in?(select?dname
???????????????from?test04)??
update?test4_05
set?did='00'
where?dname?not?in?(select?dname
???????????????from?test04)??or?dname?is?null
update?dbtest?set?test=4
select?*?from?dbscore
?
6、?將pub用戶下的Student_42及數(shù)據(jù)復(fù)制到主用戶的表test4_06中,對表中的數(shù)據(jù)進行整理，修復(fù)那些不規(guī)范的數(shù)據(jù)：
剔除姓名列中的所有空格；
?
select?*from?pub.student_42
drop?table?test4_06
create?table?test4_06?as?select*?from?pub.student_42
??
update?test4_06
??set?name=replace(name,'?','')
?
7、?將pub用戶下的Student_42及數(shù)據(jù)復(fù)制到主用戶的表test4_07中,對表中的數(shù)據(jù)進行整理，修復(fù)那些不規(guī)范的數(shù)據(jù)：
對性別列進行規(guī)范（需要先確定哪些性別數(shù)據(jù)不規(guī)范，也就是那些和大多數(shù)不一樣的就是不規(guī)范的）；
?
create?table?test4_07?as?select*?from?pub.student_42
??update?test4_07
??set?sex=replace(sex,'性','')
??update?test4_07
??set?sex=replace(sex,'?','')
?
8、?將pub用戶下的Student_42及數(shù)據(jù)復(fù)制到主用戶的表test4_08中,對表中的數(shù)據(jù)進行整理，修復(fù)那些不規(guī)范的數(shù)據(jù)：
對班級列進行規(guī)范（需要先確定哪些班級不規(guī)范）。
?
create?table?test4_08?as?select*?from?pub.student_42
??update?test4_08
??set?class=replace(class,'級','')
??update?test4_08
??set?class=replace(class,'?','')
?
9、?將pub用戶下的Student_42及數(shù)據(jù)復(fù)制到主用戶的表test4_09中,對表中的數(shù)據(jù)進行整理，修復(fù)那些不規(guī)范的數(shù)據(jù)：
年齡為空值的根據(jù)出生日期設(shè)置學生年齡（年齡=2012-出生年份），年齡不為空值的不要改變。
?
create?table?test4_09?as?select*?from?pub.student_42
??update?test4_09
??set?age=2012-extract(year?from?birthday)
??where?age?is?null
?
10、?將pub用戶下的Student_42及數(shù)據(jù)復(fù)制到主用戶的表test4_10中,對表中的數(shù)據(jù)進行整理，修復(fù)那些不規(guī)范的數(shù)據(jù)：
（1）?剔除姓名列中的所有空格；
（2）?剔除院系名稱列中的所有空格；?
（3）?對性別列進行規(guī)范（需要先確定哪些性別數(shù)據(jù)不規(guī)范，也就是那些和大多數(shù)不一樣的就是不規(guī)范的）；
（4）?對班級列進行規(guī)范（需要先確定哪些班級不規(guī)范）。
（5）?年齡為空值的根據(jù)出生日期設(shè)置學生年齡（年齡=2012-出生年份），年齡不為空值的不要改變。
?
select?*from?pub.student_42
drop?table?test4_06
?
create?table?test4_10?as?select*?from?pub.student_42
update?test4_10
set?name=replace(name,'?','')
?
update?test4_10
set?dname=replace(dname,'?','')
?
update?test4_10
set?sex=replace(sex,'性','')
update?test4_10
set?sex=replace(sex,'?','')
?
update?test4_10
set?age=2012-extract(year?from?birthday)
where?age?is?null
?
update?test4_10
set?class=replace(class,'級','')
update?test4_10
set?class=replace(class,'?','')
?
update?dbtest?set?test=4
select?*?from?dbscore
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
