oracle（sql）基礎篇系列（三）——數據維護語句、數據定義語句、偽列，oracle數據維護

oracle（sql）基礎篇系列（三）——數據維護語句、數據定義語句、偽列，oracle數據維護

 

DML語句

insert

向表中插入新的記錄

 

--三種插入方式

--（1）不寫字段的名字，直接按照字段的順序把值逐個往裡插

insert into dept2 values(50,'DANAME','BEIJING');

 

--（2）指定某些字段往裡插，其他不插的字段默認都是空值

insert into dept2(deptno,dname) values(60,'DNAME2');

 

--（3）將子查詢的結果插入到表中

insert into dept2 select * from dept;

 

update

修改記錄。

--對部門號為10的員工的工資加2 倍

update emp2 set sal = sal*2 , ename='2'||ename where deptno =10;

 

--將編號為7369的員工的經理改為編號為7698 的經理

update emp2 set mgr = 7698 where empno =  7369;

 

delete

刪除員工名為2MILLER的員工

delete from emp2 where ename = ‘2MILLER’;

 

 

DDL語句

create

創建新的表，創建表時可以指定字段的約束條件。約束條件通常有：默認約束，非空約束，唯一約束，主鍵約束，外鍵約束，check 。約束有可以分為列級約束、表級約束。

無約束創建表

create table stu

(

id number(10),

name varchar2(20),

sdate date,

class number(10),

grade number(2) ,

email varchar2(50)

);

非空約束&check約束&默認約束&唯一約束

可以對某些字段進行非空約束和唯一約束。

--name非空，emali唯一

create table stu

(

id number(10) check(id>1000),

name varchar2(20) not null,

sdate date,

class number(10),

grade number(2) default 1,

email varchar2(50) unique

);

 

--使用constraint給約束條件取名字，如果不給約束條件起名字，系統會默認的給約束條件起一個名字

create table stu

(

id number(10),

name varchar2(20) constraint stu_name_nn not null,

sdate date,

class number(10),

grade number(2) default 1,

email varchar2(50) constraint stu_email_un unique

);

 

主鍵約束&外鍵約束

 

主鍵的一些特性：

（1）可以唯一標識整條記錄

（2）非空且唯一

（3）用類型的數值的字段做主鍵比字符類型的字段更合適

（4）可以用多個字段作為聯合主鍵

 

外鍵的一些特性：

（1）建立於一張表的兩個字段，或者兩張表的兩個字段，一個字段去參考另一個字段的值

（2）如果被參考字段沒有這個值，不能把參考字段的值設置成為其他的值（如，被參考字段為空，而將參考字段設置為一個非空值是不能的）

（3）被參考的字段必須是主鍵

（4）被參考的字段不能作為刪除條件

 

--name非空，emali唯一

create table stu

(

id number(10) primary key,

name varchar2(20) constraint stu_name_nn not null,

sdate date,

class number(10),

grade number(2) default 1,

email varchar2(50) constraint stu_email_un unique

);

 

--建立class表

create table class

(

id number(10) primary key,

name varchar2(20)

);

 

--stu表的class字段參考class表的id字段，即stu表的class字段是class表的外鍵

create table stu

(

id number(10) primary key,

name varchar2(20) constraint stu_name_nn not null,

sdate date,

class number(10) references class(id),

grade number(2) default 1,

email varchar2(50) constraint stu_email_un unique

);

列級約束&表級約束

上面sql的約束條件都是寫在字段的後面，稱為列級約束；還有另一種寫法，就是在所有字段的最後用constraint聲明，稱為表級約束。需要注意的是，非空約束與默認約束只能用於列級約束。

create table stu

(

id number(10) ,

name varchar2(20),

sdate date,

class number(10),

grade number(2) default 1,

email varchar2(50),

check(id>1000),

constraint stu_id_pk primary key(id),

constraint stu_class_fk foreign key(class) references class(id),

constraint stu_email_un unique(name,email)

);

 

alter

修改現有表的結構。

增加列

alter table stu add(address varchar2(100));

 

刪除列

alter table stu drop(address);

 

修改列

alter table stu modify(address varchar2(200));

 

刪除約束條件

alter table stu drop constraint stu_class_fk;

 

添加約束條件

alter table stu add constraint stu_class_fk foreign key(class) references class(id);

修改約束條件

通常修改約束條件的方法時把原來的刪掉，然後再添加個新的。

drop

刪除表。

drop table stu;

 

偽列

 

Oracle表中的數據的默認顯示順序是先插入的先顯示，Oracle表中rownum和rowid都是偽列，這兩個偽列都可以對記錄進行編號排序，很有用處。

rownum

rownum是根據sql查詢出的結果給每行分配一個邏輯編號，對查詢結果按照1，2，3...進行排列的，但這個字段不會顯示出來。每條記錄的rownum根據sql查詢結果的不同而不同。

--新插入的數據

insert into emp2(empno,ename,deptno) values(8888,'test',80);

 

select * from emp2;

 

 

 

--選出最早插入的3條記錄

select * from emp2 where rownum <=3;

 

 

 

可見，rownum 是對查詢結果按照先插入的先顯示的順序進行編號。但是，rownum只能和< 或者 <= 一起用，不能與 > 和 = 一起使用。

 

（1）求10行以後的後4行的員工編號和姓名

--錯誤寫法，查詢結果為空

select empno, ename from emp where rownum > 10;

 

--正確寫法，先對rownum 進行別名顯示，別名顯示後就可以采用>，>=

select empno, ename from  (

   select rownum r, empno,ename from emp

) where r > 10;

 

（2）求薪水最高的前5個人

--錯誤寫法，先取出rownum <= 5的記錄，再對記錄排序

select ename,sal from emp where rownum <= 5 order by sal desc;

 

--正確寫法，先排好序，再取出rownum <= 5的記錄

select ename,sal from (

select ename,sal from emp order by sal desc ) where rownum <=5;

 

（3）求薪水最高的第6個人到第10個人

select ename,sal from (

select rownum r ,ename,sal from (

select ename,sal from emp order by sal desc )) where r >=6 and r <=10;

 

分析：先排序，再對rownum 進行別名化，別名顯示後就可以采用>，>=。

rowid

rowid是物理結構上的記錄，在每條記錄插入到數據庫中時，都會有一個唯一的物理記錄 （不會變）。

 

select t.*,t.rowid from emp t;

 

 

rowid的一個應用

對emp表根據薪水降序分頁查詢。

--rowid分頁，第一步獲取數據物理地址rowid

select rowid rid,sal from emp order by sal desc;

 

--rowid分頁，第二步根據rownum取得最大頁數

select rownum rn,rid from(select rowid rid,sal from emp order by sal desc) where rownum<10;

 

--rowid分頁，第三步根據rownum別名化rn取得最小頁數

select rid from(select rownum rn,rid from(select rowid rid,sal from emp order by sal desc) where rownum<10) where rn>5;

 

--rowid分頁，第四步根據物理地址，查詢出具體數據

select * from emp where rowid in(select rid from(select rownum rn,rid from(select rowid rid,sal from emp order by sal desc) where rownum<10) where rn>5);

分析：

分頁的關鍵是根據邏輯rownum取得最小最大頁數，然後再根據物理rowid查詢出物理記錄。第一步先獲取數據物理地址，第二步取得最大頁數，第三步取得最小頁數，第四步因為取得的頁數都是物理地址，再根據物理地址，查詢出具體數據。