Oracle SQL文法系列（一）

標籤：des   style   blog   http   color   io   使用   ar   strong   

一、結構化查詢語言 (SQL)（SQL）

1、資料操作語言（DML）

INSERT

UPDATE

DELETE

 

2、資料定義語言 (Data Definition Language)

CREATE

ALTER

DROP

RENAME

TRUNCATE

 

3、事務控制（TC）

COMMIT

ROLLBACK

SAVEPOINT

 

4、資料控制語言（DCL）

GRANT

REVOKE

 

附錄：Linux下執行SQL指令碼

@/tmp/create_table.sql

 

二、建立資料庫

create database xiaoxu

user sys identified by 123456

user system identified by 123456

logfile

group 1 (‘/u01/logdata/redo01_xiaoxu.log’) size 20M,

group 2 (‘/u01/logdata/redo02_xiaoxu.log’) size 20M,

group 3 (‘/u01/logdata/redo03_xiaoxu.log’) size 20M,

maxlogfiles 6

maxlogmembers 5

maxloghistory 8

maxdatafiles 300

maxinstances 1

archivelog

force logging

datafile ‘/u02/dbdata/system_xiaoxu_01.dbf’ size 300M,

undo tablespace undotbs

datafile ‘/u03/dbdata/undo_xiaoxu_01.dbf’ size 80M,

default temporary tablespace temp

tempfile  ‘/u04/dbdata/temp_xiaoxu_01.dbf’ size 60M,

extent management local uniform size 1M,

character set AL32UTF8

time_zone = ‘Asia/BeiJing’;

 

 

 三、從資料庫表中檢索資訊

 1、列運算

select 10 * (10 / 2 - 2)

from dual;

 

說明

（1）行標識符（偽劣）：rowid、rownum

（2）在別名中使用空格並保持別名文本的大小寫形式，就必須使用雙引號“”將其括起來。

（3）使用串連操作符 || 來合并列的輸出結果

select first_name || ‘  ‘ || last_name as "Customer Name"

from  customers;

 

（4）理解空值

①select *  from customers

where dob is null;

②select customer_id,first_name,last_name,nvl(phone,‘not phone number‘) as phone_number

from customers;

 

（5）禁止顯示重複行

select distinct customer_id

from purchases;

 

（6）比較操作符

any：任意值

all：所有值

=：等於

<>或!=：不等於

>：大於

>=：大於等於

<：小於

<=：小於等於

 

（7）SQL操作符

like

in

between

is null

is nan

還可以使用not使一個操作符的含義相反。

 

（8）使用like操作符

底線_：匹配一個字元

百分比符號%：匹配任意個字元

 

（9）邏輯操作符

x AND y

x OR y

NOT x

注意：比較操作符的優先順序高於AND，而AND的優先順序要高於OR。

 

（10）排序

ORDER BY

ORDER BY 子句必須位於from或where子句之後。

 

2、添加、修改、刪除行

（1）向表中添加行

insert into customers

values (6,‘xiaoxu‘,‘whit‘,‘08-9月-66‘,‘900-100-1111‘);

 

（2）修改表中的行

update customers

set last_name = ‘Orange‘

where customer_id = 6;

 

（3）刪除行

delete from customers

where customer_id = 6;

 

四、串連

1、等值串連（=）

（1）串連兩個表的SELECT語句

要在查詢中將兩個表串連起來，就需要在查詢的FROM子句中同時指定兩個表，在WHERE子句中指明兩個表中的相關列。

select products.name,product_types.product_type_id

from products,product_types

where products.product_type_id = product_types.product_type_id

AND products.product_id = 3;

 

（2）串連四個表的select語句

select c.first_name,c.last_name,p.name as product,pt.name as type

from customers c,purchases pr,products p,product_types pt

where c.customer_id = pr.customer_id

and p.product_id = pr.product_id

and p.product_type_id = pt.product_type_id;

 

2、串連的三種類型

內串連：在串連條件中，如果某一行的列是空值，那麼這行就不會返回

外串連：在串連條件中，即使某一行的列是空值，那麼這行也會返回；分左外串連、右外串連

自串連：返回串連到同一個表中的行

（1）外串連

在非空值表的列的另一邊使用加號（+）

select p.name,pt.name

from products p,product_types pt

where p.product_type_id = pt.product_type_id (+)

order by p.name;

 

在左外串連中，外串連操作符（+）在等於操作符的右邊：

select p.name,pt.name

from products p,product_types pt

where p.product_type_id = pt.product_type_id (+)

order by p.name;

 

在右外串連中，外串連操作符（+）在等於操作符的左邊：

select p.name,pt.name

from products p,product_types pt

where p.product_type_id (+)= pt.product_type_id

order by p.name;

 

（2）自串連

自串連是對同一個表進行的串連，要執行一個自串連，必須使用不同的表別名來標識在查詢中每次對錶的引用。

select w.first_name || ‘ ‘ || w.last_name || ‘ works for ‘ || m.first_name || ‘ ‘ || m.last_name

from employees w,employees m

where w.manager_id = m.employee_id

order by w.first_name;

 

使用標準的SQL文法執行串連（推薦）

（3）內串連（inner join .....on .....）

①兩個表的內串連

select p.name,pt.name

from products p inner join product_types pt

on p.product_type_id = pt.product_type_id

order by p.name;

 

②多於兩個表的內串連

對customers、purchases、products、product_types這4張表進行串連。

SELECT c.first_name,c.last_name,p.name as product,pt.name as typeFROM customers c INNER JOIN purchases prUSING (customer_id)INNER JOIN products pUSING (product_id)INNER JOIN product_types ptUSING (product_type_id)ORDER BY p.name;

串連邏輯圖

使用USING關鍵字說明：

1）查詢必須是等串連

2）等串連中的列必須同名

3）在USING子句中引用列時不要使用表名或別名，否則會出錯

 

(4)全外串連（full outer join）

select p.name,pt.name

from products p full outer join product_types pt

using (product_type_id)

order by p.name;

 

（5）交叉串連

使用ON或USING子句，可以避免產生笛卡爾積。

 

3、子查詢

子查詢的類型有：

單行子查詢

多行子查詢

多列子查詢

關聯子查詢

嵌套子查詢

 

（1）單行子查詢

①在where子句中使用子查詢

select employee_id,last_name

from employees

where salary < any

(select low_salary

from salary_grades);

 

②在having子句中使用子查詢

 

③在from子句中使用子查詢（內聯視圖）

select product_id

from

(select product_id from products where product_id <3 );

 

（2）多行子查詢

可以在多行子查詢中使用any或all操作符。

在查詢中any操作符之前，必須使用一個=、<>、<、>、>=、<=操作符

select employee_id,last_name

from employees

where salary < any

(select  low_salary

from salary_grades);

 

（3）關聯子查詢

關聯子查詢會引用外部SQL語句中的一列或多列

select product_id,product_type_id,name,price

from products outer

where price >

(select avg(price) from products inner

where inner.product_type_id = outer.product_type_id);

 

（4）包含子查詢的UPDATE和DELETE語句

例如將員工ID為4的工資設定為子查詢返回的高工資層級的平均值：

update employees

set salary =

(select avg(high_salary) from salary_grades)

where employee_id =4;

 

 

五、進階查詢

1、集合操作符

 

操作符	說明
Union all	返回包括重複的行
Union	返回不包括重複的行
intersect	返回兩個查詢檢索出的共有行
minus

 

說明

使用集合操作符時，所有查詢返回的列數以及列的類型必須匹配，但列名可以不同。

select product_id,product_type_id,namefrom productsunionselect prd_id,prd_type_id,namefrom more_products;

 

2、使用報表函數

查詢2003年前三個月的每月銷量總和（total_month_amount），以及所有產品類型銷量的總和（total_product_type_amount）：

select month,prd_type_id,sum(sum(amount) over (partition by month)) as total_month_amount,sum(sum(amount)) over (partition by prd_type_id) as total_product_type_amountfrom all_saleswhere year = 2003 and month <= 3;

 

3、修改表的內容

複製表結構到新表

①資料一起複製：

create table 新表 as select * from 舊錶    

 

②只複製表結構而不複製資料：

create table t2 as select * from t1 where id<1000；

注釋：components表中的id最小值為1000，所以這裡假如選擇小於這個最小值的行，那麼就是該表的結構。

 

 

六、資料庫的完整性

1、主鍵約束

2、外鍵約束

3、資料庫事務

事務的兩個動作，即提交和復原。同時，可以在事務的任何一個地方設定儲存點（savepoint）。

事務的ACID特性

原子性：事務是原子的，一個事務包含的所有SQL語句是不可分割的工作單元。

一致性：事務必須確保資料庫的狀態保持一致。

隔離性：多個事務可以獨立運行，彼此不會產生影響

持久性：事務一旦提交，資料庫的變化就會永久被保留下來。

4、並發事務

5、事務鎖

6、交易隔離等級

幻像讀取

不可重複讀取

髒讀

7、查詢閃回

 

 七、使用者、特權和角色

1、建立使用者及其許可權

create user xiaoxu identified by 123456;

授權

grant create session,connect to xiaoxu;

修改密碼

alter user xiaoxu identified by 1234567;

刪除使用者

drop user xiaoxu;

 

授予使用者的兩種有用角色是：CONNECT、RESOURCE。

 

向使用者授予系統特權

grant create table,create user to xiaoxu;

 

查詢授予使用者的系統特權，以該使用者登入資料庫

Select * from user_sys_privs

 

撤銷使用者系統特權

revoke create table from xiaoxu;

 

2、對象特權

對象特權允許使用者對資料庫物件執行特定的操作。

①將employees表的select對象特權授予student使用者

Grant select on employees to student;

 

②將employee表的last_name和salary列的update對象特權授予student，並可授予其他使用者該許可權

Grant update (last_name,salary) on employees to student with grant option;

 

說明：

若希望使用者可將對象特權授予其他使用者，使用grant選項

若希望使用者可將系統特權授予其他使用者，使用admin選項

 

③查詢已授予的對象特權

Select * from user_tab_privs_made

Where table_name = ‘employees’;

 

④撤銷使用者的對象特權

撤銷student使用者對products表的insert特權

Revoke insert on products to student;

 

 3、角色

（1）建立角色

create role hr_manager;

 

（2）    為角色授權

Grant create user to hr_manager;

 

（3）    將角色授予使用者

Grant hr_manager to student;

 

（4）    查詢授予使用者的角色

Select * from user_role_privs;

 

 

（5）    查詢授予角色的系統特權

Select * from role_sys_privs;

 

（6）    查詢授予角色的對象特權

Select * from role_tab_privs

Where role = ‘hr_manager’;

 

（7）    撤銷角色

Revoke hr_manager from student;

 

（8）    從角色中撤銷特權

Revoke all on product_types from hr_manager;

 

（9）    刪除角色

Drop role hr_manager;

 

 

八、建立表、序列、索引和視圖

1、表

（1）建立表

create table student (

id int constraint student_id_pk primary key,

status varchar2(40),

last_modified date default sysdate

);

 

（2）修改表

添加列

alter table student

add modified_by int;

 

修改列的資料類型

Alter table student

Modify status char(15);

 

刪除列

Alter table student

Drop column status;

 

2、 約束

（1）添加check約束

 

（2）添加not null約束

 

（3）添加foreign key約束

 

（4）添加unique約束

 

（5）刪除約束

Alter table student

Drop constraint student_id_pk;

 

（6）查詢約束資訊

Select * from user_constraints;

 

（7）    查詢有關列的約束資訊

Select * from user_cons_columns

Where table_name = ‘student’;

 

Oracle SQL文法系列（一）