/*ROWNUM 是一個只有在實際回傳資料時一個附加的數列*/
select rownum, deptno , dname from dept;
insert into dept values(30,'jack','new york');
insert into dept values(40,'AAA','new york');
insert into dept values(50,'VVV','new york');
insert into dept values(60,'CCC','new york');
/*創建一個表*/
create table student(
stuName varchar2(10) NOT NULL,
stuNo char(6) not null,
stuAddress varchar2(50)
)
/*修改資料庫的欄位屬性*/
alter table student modify(stuName varchar2(50));
/*在資料表中添加欄位*/
alter table student add(stuPhone varchar(11))
/*洗掉資料表中的某一欄位*/
alter table student drop(stuPhone);
/*洗掉表*/
drop table student;
/*插入資料*/
insert into student values('張三','000001','中國');
insert into student values('李四','000002','中國');
insert into student values('王五','000003','中國');
insert into student values('張三','000004','中國');
commit;
select * from student;
/*去掉回傳集中重復的資料 as 給欄位起一個別名*/
select distinct stuname as "學生姓名" from student
/* order by 根據某一個欄位進行排序*/
select * from student order by stuName desc
/*創建一個新表，但是這個表的資料來自student*/
create table student2 as select * from student;
/* 選擇指定的列來創建新表*/
create table student3 as select stuname,stuaddress from student2
create table student4 as select * from student where 1 = 2
select * from student4
select count(*) from student;
select count(1) from student;
select count(stuname) from student
select rowid, stuname, stuno from student group by rowid, stuname ,stuno having (count(stuname||stuno)<2)
drop from student where rowid not in
select max(rowid) from student group by stuname,stuno having(count(stuname||stuno)=1)
select max(rowid) from student group by stuname,stuno having(count(stuname||stuno)>=1)
/*篩選出重復的資料*/
select rowid,stuname from student where rowid not in(
select max(rowid) from student group by stuname,stuno having(count(stuname||stuno)>=1)
)
/*洗掉重復資料，（學生姓名和學生編號相同的視為重復）*/
delete from student where rowid not in(
select max(rowid) from student group by stuname,stuno having(count(stuname||stuno)>=1)
);
commit;
select * from student
create table tTest(
str char(5) not null
)
insert into tTest values('a');
insert into tTest values('b');
insert into tTest values('c');
insert into tTest values('d')
select * from ttest
/* 創建存檔點*/
savepoint a;
insert into tTest values('e')
/*回退到某一個存檔點*/
rollback to a
/*回退所有未提交的事務*/
rollback;
commit;
select * from ttest
insert into tTest values('f');
insert into tTest values('g')
commit;
select rownum , str from ttest order by str desc
select str , rownum rn from(select str from ttest order by str desc) where rownum>1 and rownum<7
/* Orcale 分頁*/
select * from(select str , rownum rn from(select str from ttest order by str desc)) where rn >=2 and rn<=6
/*集合運算子*/
/*創建表*/
create table tablea(
/* not null 不能為空*/
str char(2) not null
)
/*創建表*/
create table tableb(
str char(2) not null
)
/*插入資料*/
insert into tablea values('a');
insert into tablea values('b');
insert into tablea values('c');
insert into tablea values('d');
insert into tablea values('a');
/*提交事務*/
commit;
select * from tablea;
insert into tableb values('a');
insert into tableb values('b');
insert into tableb values('b');
insert into tableb values('d');
insert into tableb values('e');
commit;
/*集合運算子*/
/*union 聯合查詢，回傳的資料是不重復的資料 （會自動去除重復的資料，只保留一條）*/
select str from tablea union select str from tableb ;
/*union all 會將兩個表中的所有的資料全部回傳，包括重復的資料*/
select str from tablea union all select str from tableb;
select str from tablea ;
/*minus 回傳第一個表中存在 但是第二個表中沒有的資料 （第一個表指的是minus左邊的表！！！）*/
select str from tablea minus select str from tableb;
/*intersect 回傳兩個表共有的資料*/
select str from tablea intersect select str from tableb;
uj5u.com熱心網友回復：
感謝分享。記錄在博客中多好。
uj5u.com熱心網友回復：
minus會先去重。如果a表是112三條資料，b表是12兩條資料。也會判斷為一致uj5u.com熱心網友回復：
好的 下次分享博客轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/10032.html
標籤：基礎和管理