Oracle基礎操作

標籤：ade   uml   core   模糊查詢   個數   let   new   日期   tun   

首先建表：

--學生表(學號，姓名，性別，出生日期，年級，電話,家住地址)
create table student(
stuno number(5,0) primary key, --學號
stuname varchar2(30) not null, --姓名
sex char(2) default(‘男‘) check(sex=‘男‘ or sex=‘女‘) not null ,
birthday date not null,
gradeid number(5,0) not null,
moible varchar2(15) not null,
address varchar2(50) null
)
--年級表(年級編號,年級名稱)
create table grade(
gradeid number(5,0) primary key, --年級編號
gradename varchar2(30) not null --年級名稱
)

--課程表(課程編號，課程名，所屬的年級，學時)
create table course(
courseid number(5,0) primary key, --課程編號
coursename varchar2(30) not null,--課程名稱
gradeid number(5,0) not null ,--所屬年級編號
hours number(3,0) default(0) --學時
)
--成績表(學號，課程編號，成績)
create table chenji(
stuno number(5,0) not null , --學號
courseid number(5,0) not null ,--課程號
score number(4,1) check(score<=100 and score>=0)
)

select * from grade;
select * from chenji;
select * from course;
select * from student;

--學生表關聯到年級表 學生表從表，年級表是主表
--增加外部索引鍵關聯
alter table student add constraint FK_stu_grade foreign key (gradeid)
references grade(gradeid)
--課程表關聯到年級表 課程表是從表，年級表是主表
alter table course add constraint FK_course_grade foreign key (gradeid)
references grade(gradeid)
--主鍵約束,給成績表增加主鍵約束
alter table chenji add constraint PK_chenji primary key(stuno,courseid)
--成績表中的學號關聯到學生表的學號,學生表是主表，成績表是從表

alter table chenji add constraint FK_chenj_student foreign key (stuno)
references student(stuno)
--成績表中的課程號關聯到課程表中的課程號
alter table chenji add constraint FK_chenji_course foreign key (courseid)
references course(courseid)

--添加預設值約束
alter table student MODIFY (address default ‘深圳‘)
--添加check約束
alter table student add constraint CK_student_sex check(sex=‘男‘ or sex=‘女‘)
--刪除約束
alter table student drop constraint CK_student_sex

create table gradeTable(
gradeid number(3,0) primary key,
gradename varchar2(20) not null
)
--建表時建外鍵
create table test1(
id number(3,0) primary key,
name varchar2(20) not null,
gradeid number(3,0) not null,
foreign key (gradeid) references gradeTable(gradeid)
)

--建立序列
create sequence grade_seq --產生一個從1開始每次增長1的一個序列
--刪除序列
drop sequence grade_seq
--建立序列-指定啟始值，增長值,最大值
create sequence grade_seq
start with 100
increment by 1
nomaxvalue
--修改序列
alter sequence grade_seq
increment by 1
nomaxvalue
--建立遞減序列
create sequence grade_seq2
start with 100
maxvalue 100
increment by -1
minvalue -999999999999

select * from grade
--向年級表中插入資料
insert into grade(gradeid,gradename) values (grade_seq.nextval,‘一年級‘);
insert into grade(gradeid,gradename) values (grade_seq.nextval,‘二年級‘);
insert into grade(gradeid,gradename) values (grade_seq.nextval,‘三年級‘);
insert into grade(gradeid,gradename) values (grade_seq.nextval,‘四年級‘);
insert into grade(gradeid,gradename) values (grade_seq.nextval,‘五年級‘);
insert into grade(gradeid,gradename) values (grade_seq.nextval,‘六年級‘);
insert into grade(gradeid,gradename) values (grade_seq.nextval,‘初一年級‘);
insert into grade values (grade_seq.nextval,‘初二年級‘);

 

--學生表插入資料
select * from student
--建立學生表的序列
create sequence stu_seq
start with 1000
increment by 1
nomaxvalue
cache 10;

select * from student
--SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

insert into student values (stu_seq.nextval,‘張三‘,default,‘10-9月-1990‘,1,‘13211111111‘,‘深圳福田‘);
insert into student values (stu_seq.nextval,‘李四‘,default,to_date(‘1990-10-22‘,‘YYYY-MM-DD‘),1,‘13211111111‘,‘深圳福田‘);
insert into student values (stu_seq.nextval,‘王五‘,default,to_date(‘1991-2-12 10:30:10‘,‘YYYY-MM-DD HH:mi:ss‘),1,‘13211111111‘,‘深圳福田‘);
insert into student values (stu_seq.nextval,‘趙五‘,‘女‘,to_date(‘1991-2-12 10:30:10‘,‘YYYY-MM-DD HH:mi:ss‘),1,‘13211111111‘,‘深圳福田‘);

insert into student values (stu_seq.nextval,‘張四‘,default,‘10-9月-1990‘,3,‘13211111111‘,‘深圳寶安‘);
insert into student values (stu_seq.nextval,‘李四‘,‘女‘,to_date(‘1993-10-22‘,‘YYYY-MM-DD‘),4,‘13211111111‘,‘深圳羅湖‘);
insert into student values (stu_seq.nextval,‘趙五‘,default,to_date(‘1996-2-12 10:30:10‘,‘YYYY-MM-DD HH:mi:ss‘),3,‘13211111111‘,‘深圳南山‘);
insert into student values (stu_seq.nextval,‘趙六‘,‘女‘,to_date(‘1991-2-12 10:30:10‘,‘YYYY-MM-DD HH:mi:ss‘),5,‘13211111111‘,‘深圳福田‘);
insert into student values (stu_seq.nextval,‘張三三‘,default,to_date(‘1996-2-12 10:30:10‘,‘YYYY-MM-DD HH:mi:ss‘),3,‘13211111111‘,‘深圳南山‘);
insert into student values (stu_seq.nextval,‘張四四‘,‘女‘,to_date(‘1991-2-12 10:30:10‘,‘YYYY-MM-DD HH:mi:ss‘),5,‘13211111111‘,‘深圳福田‘);
insert into student values (stu_seq.nextval,‘張王‘,default,to_date(‘1996-2-12 10:30:10‘,‘YYYY-MM-DD HH:mi:ss‘),3,‘13211111111‘,null);
insert into student values (stu_seq.nextval,‘張六‘,‘女‘,to_date(‘1991-2-12 10:30:10‘,‘YYYY-MM-DD HH:mi:ss‘),5,‘13211111111‘,‘‘);

--向課程表中插入資料
insert into course values(100,‘java‘,101,60);
insert into course values(101,‘oracle‘,102,50);
insert into course values(102,‘html‘,101,40);
insert into course values(103,‘jQuery‘,102,48);

insert into course values(104,‘css‘,101,30);
insert into course values(105,‘JS‘,104,48);
insert into course values(106,‘SSH‘,106,80);
insert into course values(107,‘uml‘,103,48);

--修改學號為1001性別為‘女‘
update student set sex=‘女‘ where stuno=1001
update student set sex=‘男‘,address=‘廣州花都‘ where stuno=1001
--刪除性別為女的學生
delete from student where sex=‘女‘
--刪除學生表中所有記錄
delete from student
--truncate刪除學生表中所有記錄
--delete 和truncate,drop異同
--相同點：都可以刪除表中記錄
--不同點：1，truncate不能接條件，將表中記錄全部刪除；而delete可以接where條件，可以條件刪除
--2.truncate刪除表中記錄時，如果表中主外部索引鍵關聯，不論是否有關聯的資料，均無法執行
delete 沒有關聯到 數可以刪除，如果資料有關聯到其他表，則懸賞從表記錄在刪除主標記錄
--3.truncate刪除表時，不會記錄到日誌，不能復原，刪除資料不可恢複，也不會觸發表中刪除資料的觸發器，執行速度快
--delete 刪除資料會記錄到日誌，可以復原，可恢複，會觸發表中刪除記錄的觸發器，執行速度慢
--delete 和 truncate 只刪除表中資料，表還在
--drop 指的是刪除表，表中的約束都會刪除
--可以用sys_guid() 或序列產生主鍵
select sys_guid() from dual;

truncate table student;
select * from student;

insert into chenji values (1013,100,90);
insert into chenji values (1013,102,80);
insert into chenji values (1013,104,73);
insert into chenji values (1015,100,91);
insert into chenji values (1015,102,90);
insert into chenji values (1020,104,98);

insert into gradetable values (1,‘aa‘);
insert into gradetable values (2,‘ada‘);
insert into gradetable values (3,‘aad‘);
insert into gradetable values (4,‘aaa‘);
commit;
truncate table gradetable;

drop table gradetable;

select grade_seq2.nextval from dual;
delete from grade where gradeid>10;

select * from student;
select * from grade;
select * from course;
select * from chenji;

--查詢所有男生資訊
select * from student where sex=‘男‘;
--查詢家住地址是深圳南山的學生的學號，姓名，性別，生日
select stuno,stuname,sex,birthday from student where address=‘深圳南山‘;
--查詢年級編號為1的深圳的女同學
select * from student where gradeid=1 and address like ‘深圳%‘ and sex=‘女‘

--模糊查詢
--like和not like --萬用字元%表示0個或多個任一字元 _表示一個任一字元
--查詢學生表中姓張的同學的學號，姓名，性別。年齡
select stuno,stuname,sex,birthday from student where stuname like ‘張%‘
--查詢學生表中姓張的同學的學號，姓名，性別，年齡，名字是三個字
select stuno,stuname,sex,birthday from student where stuname like ‘張__‘

--in和not in
select * from student where stuno=1008 or stuno=1018 or stuno=1022
--等價於
select * from student where stuno in (1008,1018,1022)
select * from course;

--查詢jvaa, oracle，jquery三門課的課程情況
select * from course where coursename in(‘java‘,‘oracle‘,‘jQuery‘)

--查詢不是java ,oracle,jQuery三門課的課程情況
select * from course where coursename not in(‘java‘,‘oracle‘,‘jQuery‘)

--between ...and...
--查詢學時在30到60之間課程的資訊[30,60] 相當於>=30 and <=60
select * from course where hours between 30 and 60
--等價於
select * from course where hours<=60 and hours>=30

--查詢學產生績不等90分的成績資訊
select * from chenji where score<>90.0
--null 和 not null
--查詢地址為空白的學生的資訊
select * from student where address is null;
--查詢地址部位空的學生的資訊
select * from student where address is not null;

--彙總函式 max(),min(),avg(),count(),sum()
--查詢學時最多的那門課的資訊
select * from course where hours=(select max(hours) from course)
--查詢學時最少的那門課的資訊
select * from course where hours=(select min(hours) from course)
select * from chenji;
--課程編號為100這門課的平均分
select avg(score) from chenji where courseid=100
--所有課程的平均學時
select avg(hours) from course
--求學號為1013這個學生的總分
select stuno,sum(score) from chenji where stuno=1013
group by stuno
--注意：查詢的列中除了彙總函式那一列的其它列都必須作為group by 的條件
--統計課程號為100這門課，成績大於80分的個數
select count(*) from chenji where score>80 and courseid=100
--統計學生表中年級一年級的人數
--方法一
select count(*) from student
where gradeid in (select gradeid from grade where gradename=‘一年級‘);

--方法二
select count(*) from student s,grade g
where s.gradeid=g.gradeid
and g.gradename=‘一年級‘

--查詢一年級學生的學號，姓名，性別，年級名稱
select stuno as 學號,stuname 姓名,sex 性別,gradename 年級
from student s,grade g
where s.gradeid=g.gradeid
and g.gradename=‘一年級‘

--查詢一年級的年級編號
select gradeid from grade where gradename=‘一年級‘

Oracle基礎操作