快捷導(dǎo)航

Oracle數(shù)據(jù)庫(kù)實(shí)現(xiàn)建表、查詢方式

更新時(shí)間：2024年04月01日 17:00:21 作者：manhoho

這篇文章主要介紹了Oracle數(shù)據(jù)庫(kù)實(shí)現(xiàn)建表、查詢方式，具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

一. 創(chuàng)建三張表

1.學(xué)生信息表(stuInfo): 學(xué)號(hào)(主鍵), 姓名(不能為空), 性別(只能是男或女), 生日

` create table stuInfo(
    stuInfoID number primary key,
    stuInfoName varchar2(32) not null,
    stuInfoSex varchar2(32) not null check(stuInfoSex in('男','女')),
    stuInfoBrithday date
    )
    /`

2.課程信息表(subInfo)：編號(hào)（主鍵），課程名（不能為空）

create table subInfo(
    subInfoID number primary key,
    subInfoName varchar2(32) not null
    )
    /

3.分?jǐn)?shù)信息表(scoreInfo)：編號(hào)（主鍵），學(xué)號(hào)（外鍵），課程號(hào)（外鍵），分?jǐn)?shù)（0-100之間）

 create table scoreInfo(
    scoreInfoID number primary key,
     scoreInfo_stuInfoID number references stuInfo(stuInfoID),
    scoreInfo_subInfoID number references subInfo(subInfoID),
    score number check(score >= 0 and score <= 100)
    )
    /

二. 分別向三張表中插入數(shù)據(jù)，不少于5行

stuInfo表插入數(shù)據(jù):

//stuInfo表
insert into stuInfo values(1,'張三','男',to_date('1990-01-01','yyyy-MM-dd'));
insert into stuInfo values(2,'李四','男',to_date('1990-02-01','yyyy-MM-dd'));
insert into stuInfo values(3,'王五','女',to_date('1990-03-01','yyyy-MM-dd'));
insert into stuInfo values(4,'馬六','男',to_date('1990-04-01','yyyy-MM-dd'));
insert into stuInfo values(5,'劉七','女',to_date('1990-05-01','yyyy-MM-dd'));
insert into stuInfo values(6,'小明','男',to_date('2003-06-01','yyyy-MM-dd'));
insert into stuInfo values(7,'小紅','女',to_date('2002-07-01','yyyy-MM-dd'));
insert into stuInfo values(8,'小亮','男',to_date('2002-07-01','yyyy-MM-dd'));

stuInfo表插入數(shù)據(jù)后的效果：

select stuInfoID as 學(xué)號(hào),stuInfoName as 姓名,stuInfoSex as 性別,stuInfoBrithday as 生日 from stuInfo;

subInfo表插入數(shù)據(jù)：

//subInfo表
insert into subInfo values(1,'語(yǔ)文');
insert into subInfo values(2,'數(shù)學(xué)');
insert into subInfo values(3,'英語(yǔ)');
insert into subInfo values(4,'物理');
insert into subInfo values(5,'化學(xué)');
insert into subInfo values(6,'生物');
insert into subInfo values(7,'地理');
insert into subInfo values(8,'歷史');

subInfo表插入數(shù)據(jù)后的效果：

select subInfoID as 編號(hào),subInfoName as 課程名 from subInfo;

score表插入數(shù)據(jù)：

//score表
insert into scoreInfo values(1,1,1,95);
insert into scoreInfo values(2,2,2,97);
insert into scoreInfo values(3,3,3,100);
insert into scoreInfo values(4,4,4,86);
insert into scoreInfo values(5,5,5,89);
insert into scoreInfo values(6,6,6,91);
insert into scoreInfo values(7,7,7,93);
insert into scoreInfo values(8,8,8,90);

scoreInfo表插入數(shù)據(jù)后的效果：

select scoreInfoId as 編號(hào),scoreInfo_stuInfoID as 學(xué)號(hào),scoreInfo_subInfoID as 課程號(hào),score as 分?jǐn)?shù) from scoreInfo;

三. 創(chuàng)建新表stu1并將stuinfo中的性別為女的學(xué)生的學(xué)號(hào)

姓名和性別的信息插入到該表中

create table stu1 as select stuInfoId,stuInfoName,stuInfoSex from stuInfo where stuInfoSex='女';

效果：

select stuInfoId as 學(xué)號(hào),stuInfoName as 姓名,stuInfoSex as 性別 from stu1;

四. 完成下列查詢

1.查詢所有姓張的學(xué)員信息(模糊查詢)

select stuInfoId as 學(xué)號(hào),stuInfoName as 姓名,stuInfoSex as 性別 ,stuInfoBrithday as 生日 from stuInfo where stuInfoName like '張%';

2.查詢所有20歲以上的學(xué)員信息(用日期函數(shù)實(shí)現(xiàn))

select stuInfoId as 學(xué)號(hào),stuInfoName as 姓名,stuInfoSex as 性別 ,stuInfoBrithday as 生日 from stuInfo where months_between(sysdate,stuInfoBrithday)/12>20;

3.查詢同一天出生的學(xué)員信息（表連接）

select a .stuInfoID as 編號(hào),a.stuInfoName as 姓名,a.stuInfoSex as 性別,a.stuInfoBrithday as 生日,b.score as 成績(jī) from stuInfo a left join scoreInfo b on a.stuInfoID = b.scoreInfoID where a.stuInfoBrithday in (select stuInfoBrithday from stuInfo group by stuInfoBrithday having count(stuInfoBrithday)>1);

4.查詢參加考試的所有學(xué)員的名稱，科目和成績(jī)(按成績(jī)排序)

select c.scoreInfoID as 學(xué)生編號(hào),a.stuInfoName as 學(xué)生姓名,b.subInfoName as 課程名稱 ,c.score as 課程成績(jī) from stuInfo a,subInfo b,scoreInfo c where a.stuInfoID = c.scoreInfo_stuInfoID and b.subInfoID = c.scoreInfo_subInfoID order by c.score asc;

5.查詢所有沒(méi)有參加考試的學(xué)員學(xué)號(hào),姓名，只要有1門(mén)課沒(méi)考都要查詢出來(lái)(子查詢)

select stuInfoID as 學(xué)員學(xué)號(hào),stuInfoName as 姓名 from stuInfo where stuInfoID = (select scoreInfo_stuInfoID from scoreInfo where score is null);

6.查詢出沒(méi)有學(xué)生考試的課程名稱