oracle學(xué)生表,課程表,選課表.三表聯(lián)合查詢

1、曾經(jīng)有人面試遇到這種類似的題目， 我在網(wǎng)上找過， 沒找到，特發(fā)表這文章， 供廣大網(wǎng)友學(xué)習(xí)。創(chuàng)建學(xué)生表：create table學(xué)生 ( 學(xué)號(hào) char (1) not null,姓名 varchar (6),年齡 int ,check (年齡 between 0 and 100),性別 char (2),系 varchar (10),primary key (學(xué)號(hào) );創(chuàng)建課程表：create table課程 ( 課程號(hào) char (2)not null,課程名 varchar (10),先修學(xué)科 int not null,學(xué)分 int , check (學(xué)分 between 0and 100

2、),primary key (課程號(hào) );創(chuàng)建選課表：create table選課 ( 學(xué)號(hào) char (1) not null,課程號(hào) char (2) not null,成績 int , check (成績 between 0and 100),primary key (學(xué)號(hào) , 課程號(hào) ),foreign key (課程號(hào) ) references課程 ( 課程號(hào) ),foreign key (學(xué)號(hào) ) references學(xué)生 ( 學(xué)號(hào) );查看這三個(gè)表：select * from學(xué)生select * from課程select * from選課給這三個(gè)表插入數(shù)據(jù)：insert into學(xué)

3、生 values(1,張三,20,男,中文 );insert into學(xué)生 values(2,李四,21,女,英語 );insert into學(xué)生 values(3,王五,22,男,數(shù)學(xué) );insert into學(xué)生 values(4,趙六,23,女,物理 );insert into學(xué)生 values(5,馬八,25,男,計(jì)算機(jī) );insert into課程 values(01,語文,01,30);insert into課程 values(02,數(shù)學(xué),02,40);insert into課程 values(03,英語,03,50);insert into課程 values(04,物理,04

4、,60);insert into課程 values(05,計(jì)算機(jī),05,80);insert into選課 values(1,01,20);insert into選課 values(2,02,30);insert into選課 values(3,03,40);insert into選課 values(4,04,50);insert into選課 values(5,05,60);刪除基本表drop table選課刪除表中所有的行truncate table學(xué)生創(chuàng)建索引create unique index索引名 on表名 ( 學(xué)號(hào)次序 )給學(xué)生表創(chuàng)建一個(gè)名為學(xué)生_索引年齡降序排列的唯一索引cre

5、ate unique index學(xué)生 _索引 on學(xué)生 ( 年齡 desc)給學(xué)生表創(chuàng)建一個(gè)名為學(xué)生表_索引年齡升序排列的不唯一索引create unique index學(xué)生表 _索引 on學(xué)生 ( 年齡 asc)( 默認(rèn)是升序)給學(xué)生表創(chuàng)建一個(gè)名為學(xué)生的_索引年齡降序排列的聚簇索引create clustered index學(xué)生的 _索引 on學(xué)生 ( 年齡 )刪除索引drop index表名 . 索引名刪除學(xué)生表 _索引drop index學(xué)生 . 學(xué)生表 _索引名修改數(shù)據(jù)update 表名set列名 =表達(dá)式where條件將學(xué)生表中的張三的年齡變?yōu)闅qupdate 學(xué)生 set年齡 =22

6、 where將所有學(xué)生的年齡都加一歲( 不加update 學(xué)生 set年齡 =年齡 +1學(xué)號(hào) =1where 代表全部)刪除數(shù)據(jù)delete from表名 where條件刪除學(xué)生表中的學(xué)號(hào)的學(xué)生delete from學(xué)生 where學(xué)號(hào) =1刪除所有學(xué)生選課記錄 ( 不加 where 代表全部 )delete from學(xué)生刪除計(jì)算機(jī)系所有學(xué)生的選課記錄delete from選課 where學(xué)號(hào) in (select學(xué)號(hào) from學(xué)生 where 系 = 計(jì)算機(jī))查詢學(xué)生表中選擇中文系的學(xué)號(hào)：select學(xué)號(hào) from學(xué)生 where系= 中文 查詢學(xué)生表中的所有學(xué)號(hào)：select all學(xué)號(hào)

7、from學(xué)生查詢學(xué)生表中學(xué)生的學(xué)號(hào)并去除重復(fù)的學(xué)號(hào)：select distinct學(xué)號(hào) from學(xué)生查看學(xué)生的出生日期并命名該列：select年齡 ,2009- 年齡 as 出生日期 from學(xué)生查看成績不及格的學(xué)生的學(xué)號(hào)：select學(xué)號(hào) from 選課 where成績 =60修改一個(gè)已有的元組：update 學(xué)生 set 姓名 = 牛七 ,性別 = 男 where學(xué)號(hào) =4;將所有選了中文系的學(xué)生的成績清零：update 選課 set 成績 =0 where學(xué)號(hào) in (select學(xué)號(hào) from學(xué)生 where 系=中文 );刪除一個(gè)元組：delete from學(xué)生 where 學(xué)號(hào) =

8、1將選課中學(xué)生的成績都提升百分之五：update 選課 set 成績 =成績 *1.05查看選擇中文系的學(xué)生的學(xué)號(hào)：select學(xué)號(hào) from 學(xué)生 where系= 中文 刪除選課中所有選擇中文系的信息：delete from選課 where 學(xué)號(hào) in (select學(xué)號(hào) from 學(xué)生 where 系 = 中文 );清空已有元組中的一列：update 學(xué)生 set年齡 =null查詢學(xué)生的成績?cè)?-90 之間的學(xué)生的學(xué)號(hào) , 所在系 , 課程名 , 年齡 , 姓名 , 性別 , 年齡按 . 降序排列 , 且是女生 :select 學(xué)生 . 學(xué)號(hào) , 系 , 年齡 , 姓名 , 性別 , 課

9、程 . 課程名 , 選課 . 成績 from 學(xué)生 ,課程, 選課where 學(xué)生 . 學(xué)號(hào) =選課 . 學(xué)號(hào) and 課程 . 課程號(hào) =選課 . 課程號(hào) and 選課 . 成績between 40 and 90 and 學(xué)生 . 性別 = 女order by學(xué)生 . 年齡 desc插入時(shí)間格式：create table qq(qs char (2),qdate datetime,/*datetime 1753.1.1-9999.12.31*/qtime datetime,/*smalldatetime 1900.1.1-2079.12.31*/ qsdate smalldatetime,p

10、rimary key (qs);select * from qqset dateformat mdy /* myd ymd ydm dmy dym*/insertintoqq values(02,05/15/1788,16:50:28.000,05/15/2078);所有學(xué)生他們每個(gè)人的平均成績及格的學(xué)生學(xué)號(hào)和平均成績select學(xué)號(hào) ,avg( 成績 ) from選課 group by學(xué)號(hào) having avg(成績 ) in(select avg(成績 )from 選課 group by學(xué)號(hào) ) and avg (成績 )=60;select學(xué)號(hào) ,avg( 成績 ) from選課 gro

11、up by學(xué)號(hào) having avg (成績 )=601. 列出成績不合格的學(xué)號(hào)select學(xué)號(hào) from選課 where成績 =60) and系= 中文 5. 張三的網(wǎng)絡(luò)布線的考試成績select成績 from選課 where 學(xué)號(hào) in (select課程號(hào) from選課 where學(xué)號(hào) in (select學(xué)號(hào) from學(xué)生 where姓名 = 張三 )6. 中文系的學(xué)生及年齡不大于 22 的學(xué)生select姓名 from學(xué)生 where年齡 209. 只有學(xué)分的課程代號(hào) . 課程名的視圖create view好的 ( 課程號(hào) , 課程名 , 先選修課 )as select課程號(hào) , 課

12、程名 , 先修學(xué)科 from課程 where課程號(hào) in(select程號(hào) from選課 where成績 =4)課10. 績及格和參加選課但沒有考試成績的視圖 . 學(xué)號(hào) , 課程號(hào)和成績create view 我的視圖 ( 學(xué)號(hào) , 課程號(hào) , 成績 )as select學(xué)號(hào) , 課程號(hào) , 成績 from 選課 where 成績 =60 or 成績 is null11. 創(chuàng)建選擇號(hào)課程的學(xué)生的學(xué)號(hào) , 姓名 , 成績create view 我的又一個(gè)視圖 as select 學(xué)生 . 學(xué)號(hào) , 學(xué)生 . 姓名 , 選課 . 成績from 學(xué)生 , 選課 where 學(xué)生 . 學(xué)號(hào) =選課 .

13、 學(xué)號(hào) and 選課 . 程號(hào) =05insert into視圖名 as select學(xué)號(hào) , 姓名, 年齡from學(xué)生where系= 中文 with check option;12. 顯示大于年齡的學(xué)生學(xué)號(hào)和姓名，學(xué)號(hào)按照升學(xué)輸出select學(xué)號(hào) , 姓名 from學(xué)生 where年齡 (select avg(年齡 )from學(xué)生 )order by學(xué)號(hào)13. 顯示學(xué)生表中姓名和最小年齡select姓名 , 年齡最小年齡 from學(xué)生 where年齡 in (select min(年齡 )from 學(xué)生 )14. 完全備份 mgc 數(shù)據(jù)庫use mgcexec sp_addumpdevice

14、disk,wodebeifen,c:wodebeifenwodebeifen.bakbackup database mgc to wodebeifen15. 差異備份 mgc 數(shù)據(jù)庫use mgcbackup database mgc to disk=c:wodebeifenwodechalingbeifen.bak withdiffrential注：差異備份需要一個(gè)全庫 （完全）備份做基礎(chǔ)， 差異備份就是備份最后一次全庫備份以來的數(shù)據(jù)庫改變的部分16. 備份日志文件use mgcexec sp_addumpdevicedisk,rizhidebeifen,c:wodebeifenrizhib

15、eifen.bakbackup log mgc to wodebeifen17.課本第五章復(fù)習(xí)題：（全部內(nèi)容）create table學(xué)生 ( 學(xué)號(hào) char(2),姓名 varchar(10),性別 char (2),年齡 int,所在系 varchar (10),primary key(學(xué)號(hào) );create table課程 ( 課程號(hào) char(2)default 1,課程名 varchar (6),先修課號(hào) char (2),學(xué)分 int,primary key (課程號(hào) );create table選課( 學(xué)號(hào)char (2),課程號(hào) char (2),成績 int check (成

16、績 between 0 and 100),primary key (學(xué)號(hào) , 課程號(hào) ),foreign key (學(xué)號(hào) ) references學(xué)生 ( 學(xué)號(hào) ),foreign key (課程號(hào) ) references課程 ( 課程號(hào) );select* from學(xué)生select * from課程select * from選課insert into學(xué)生 values (1,馬一 ,男,20,中文 )insert into學(xué)生 values (2,趙二 ,女,21,英語 )insert into學(xué)生 values (3,張三 ,男,22,物理 )insert into學(xué)生 values (

17、4,李四 ,女,23,化學(xué) )insert into學(xué)生 values (5,王五 ,男,24,生物 )insert into學(xué)生 values (6,錢六 ,女,25,計(jì)算機(jī) )insert into學(xué)生 values (7,劉七 ,男,26,地理 )insert into學(xué)生 values (8,馬八 ,女,27,歷史 )insert into學(xué)生 values (9,付九 ,男,28,政治 )insert into學(xué)生 values (10,孫十 , 女 ,29,中文 )insert into學(xué)生 values (11,程十一 ,男,30,中文 )insert into學(xué)生 values

18、 (12,劉十二 ,女,31,英語 )insert into學(xué)生 values (13,牛十三 ,男,32,英語 )insert into學(xué)生 values (14,虎十四 ,女,33,計(jì)算機(jī) )update 學(xué)生 set 姓名 = 馬一 where 學(xué)號(hào) =1delete from學(xué)生 where 學(xué)號(hào) =10select* from學(xué)生select * from課程select * from選課insert into課程 values (1,語文 ,1,90)insert into課程 values (2,數(shù)學(xué) ,2,80)insert into課程 values (3,英語 ,3,70)

19、insert into課程 values (4,物理 ,4,60)insert into課程 values (5,化學(xué) ,5,50)insert into課程 values (6,生物 ,6,40)insert into課程 values (7,歷史 ,7,30)insert into課程 values (8,地理 ,8,20)insert into課程 values (9,政治 ,9,10)insert into課程 values (10,linux,9,10)insert into課程 values (11,win,9,10)insert into課程 values (12,sql,9,1

20、0)insert into課程 values (13,路由器 ,9,10)insert into課程 values (14,交換機(jī) ,9,10)insert into課程 values (15,pix,9,10)insert into選課 values (1,1,90)insert intoinsert intoinsert intoinsert intoinsert intoinsert intoinsert intoinsert intoinsert intoinsert intoinsert intoinsert intoinsert intoinsert intoinsert into

21、insert intoinsert into選課選課選課選課選課選課選課選課選課選課選課選課選課選課選課選課選課values (2,2,80)values (3,3,70)values (4,4,60)values (5,5,50)values (6,6,40)values (7,7,30)values (8,8,20)values (9,9,10)values (1,9,10)values (1,2,10)values (2,1,10)values (2,3,10)values (3,2,10)values (3,5,10)values (3,4,10)values (2,4,10)valu

22、es (1,3,10)查詢選修了號(hào)課程的全部學(xué)生的姓名, 出生年份 ,(假定當(dāng)前為年 )select姓名 , 年齡 ,2002- 年齡 from學(xué)生 where學(xué)號(hào) in (select學(xué)號(hào) from選課 where 課程號(hào) =1)將年齡大于歲的的學(xué)生所有成績?cè)O(shè)為零update 選課 set 成績 =0 where學(xué)號(hào) in (select學(xué)號(hào) from學(xué)生 where年齡 25)以 name,birthday 為標(biāo)題 , 顯示學(xué)生表中所有學(xué)生的姓名 , 出生年份 ,( 假定當(dāng)前是年 ), 按姓名降序排列select姓名 as name, 2002- 年齡 as birthday from 學(xué)

23、生 orderby 姓名 desc顯示選修一號(hào)課程的學(xué)生的最高分 , 最低分 , 平均分 , 總分及人數(shù)select max( 成績 ),min( 成績 ),avg( 成績 ),sum( 成績 ),count(*) from選課where 課程號(hào) =1顯示選課數(shù)目大于 3 的學(xué)生的學(xué)號(hào)及選課數(shù)select學(xué)號(hào) ,count( 課程號(hào) ) from選課 group by學(xué)號(hào)having count(課程號(hào) )=3為計(jì)算機(jī)系建一個(gè)視圖名為 cs, 包含如下信息 : 學(xué)號(hào) , 姓名 , 性別 , 年齡 , 課程號(hào) ,成績create view cs (學(xué)號(hào) , 姓名 , 性別 , 年齡 , 課程號(hào) , 成績 )as select學(xué)生 . 學(xué)