求職複習系列14 oracle

DML數據操作語言,增刪改查

DDL:數據定義語言,表操作(創建表 修改表 刪除表 建立索引 刪除索引)

DCL.數據控制語言

(GRANT：授予訪問權限

REVOKE：撤銷訪問權限

COMMIT：提交事務處理

ROLLBACK：事務處理回退

SAVEPOINT：設置保存點

LOCK：對數據庫的特定部分進行鎖定

SELECT

空值
\t空值是無效的，未指定的，未知的或不可預知的值
\t空值不是空格或者0。
\t空值的數學表達式都是空值
別名
\t使用as 別名加上雙引號,區分大寫特殊字符或包含的空格
\t也可以不使用 as 
連接符
\t||使用這個鏈接 select name ||`is`||job fromemployees;
字符串
\t字符串使用``連接起來的 

去重
\tdistinct 
顯示錶結構
\tdesc tablename
\tdescribe tablename
\t

sql與sqlplus的區別

SQL

一種語言

ANSI 標準

關鍵字不能縮寫

使用語句控制數據庫中的表的定義信息和表中的數據

SQL*Plus

一種環境

Oracle 的特性之一

關鍵字可以縮寫

命令不能改變數據庫中的數據的值

日期
select sysdate from dual;
空值的運算結果都是null

where 條件

between ...and ... 
\t包括邊界
in 等於值列表中的一個
like \t模糊查詢
is null \t空值
and or not
優先級問題
 1 算術運算符
 2 連接符
 3 比較符
 4 IS [NOT] NULL, LIKE, [NOT] IN
 5 [NOT] BETWEEN
 6 NOT
 7 AND
 8 OR
order by
\tASC 升序(默認)
\tDESC 降序
\t在select 語句末尾
\t多個 col1 ASC|DESC ,col2 ASC|DESC;

函數

字符,數字,日期的轉換函數

單行函數:

數據操作對象

接受參數返回一個結果

只對一行進行變換

每行返回一個結果

可以轉換數據類型

可以嵌套

參數可以是一列或一個值

可以傳入參數(字段 字符等)

字符串
lower() 所有小寫
upper() 所有大寫
initcap() 單詞首字母大寫
CONCAT('Hello', 'World')//連接函數
SUBSTR('HelloWorld',1,5)截取 結果Hello
LENGTH('HelloWorld')獲取長度
INSTR('HelloWorld', 'W')//計數
LPAD(salary,10,'*')//左邊不足10未不*
RPAD(salary, 10, '*')//右邊不足10位補*
TRIM('H' FROM 'HelloWorld')//默認刪除前後的空格 
 trim(選項 目標 from 目標字符串)
 選項 trailing末尾 leading 開頭 both前後一起刪除
 
REPLACE(‘abcd’,’b’,’m’)替換
數字====================
Round(222.33,1)//四捨五入 222.3
trunc(222.456,2)//截斷 保留幾位 222.45
mod(222,100)//求餘 22
日期================
sysdate 返回日期和時間
日期加上數字或減去 返回任是日期
兩個日期相減是日期
Months_between() 兩個日期的月數
add_Months() 指定日期加上若干月份
next_day()指定下一個星期幾的日期
last_day()本月的最後一天 

Round()日期的四捨五入
trunc()日期裁剪
類型轉換
date<===>varchar<==>number
to_char(date,'yyyy-mm-dd');轉為字符
to_date('1995-05-13','yyyy-mm-dd');轉為日期
to_char(12345,'999,999,999') 轉為 12,345 
to_char(12345,'000,000,999') 轉為 000,012,345 0補位 9不補位之區分 前面可加$ L本地的貨幣符號
to_number('$12345','$999,999.99')轉為 $012,345.00
通用類型的
\t適用於任何類型,也適用於null
NVL(exp1,exp2);將空值轉為默認類型
NVL2(exp1,exp2,exp3);轉換為 exp1=nuull?exp2:exp3
NULLIF(expr1, expr2) : 相等返回NULL，不等返回expr1
coalesce(exp1,exp2,exp3
•COALESCE 與 NVL 相比的優點在於 COALESCE 可以同時處理交替的多個值。
•如果第一個表達式為空,則返回下一個表達式，對其他的參數進行COALESCE 。
case和DECODE
case [字段]when []then[]
 when []then[]
 when[] then[]
 else [] 
 end 別名
decode([字段],[條件],[結果],
 [條件],[結果],
 [條件],[結果],
 [else結果]) 別名
\t

多表查詢

1. 等值和不等值鏈接
2. 自連接
3. 外連接

笛卡爾積
\t無連接條件
\t條件無效
\t所有行相互連接
相同列前加上表名前綴
n各表至少需要n-1個條件
內連接:等值&不等值 輸出出來
左右連接
左
select * from left_table l,right_table where l.id=r.id(+)
右
select * from left_table l,right_table where l.id(+)=r.id()
cross 
natural 列名一樣的連接
select * from table1 join jion useing([相同的列名]) --瞭解
select * from table1 join table2 on[條件]
左
select * from table1 left outer join table2 on[條件]
右
select * from table1 right outer join table2 on[條件]
滿
select * from table1 full outer join table2 on[條件]
左右都會連接起來
自連接
兩個比較的字段都在一個表中
\t

分組查詢

max() --最大值
min() --最小值
avg()--平均值
count() --計數
sum() --求和
max min count(不為空的記錄數) 能對任意類型的數據使用
avg 忽略空值
需要計算空值的 avg(NVL(exp1,0))
非空且不重複 count(distanct exp1)
分組
select colum, group_fun(colum) from tableName
where [condiction]
 group by exp_group
 order by exp_order ASC|DESC
 
可以包含多個分組
不能where後包含組函數
可以在having字句中包含組函數
select colum, group_fun(colum) from tableName
where [condiction]
 group by exp_group
 having group_condition --組函數位置
 order by exp_order ASC|DESC

子查詢

\t子查詢包含在括號內
\t子查詢放在比較的右側
子查詢返回單行 適應的操作符為 = > < <> >= <= \t空值不會返回任何行
\t多行 in(任意一個) any(某一個) all(所有) 空值返回no rows selected
\t 

DDL

不能回滾,操作了立馬保存;

表 基本的數據存儲集合，由行和列組成。

視圖 從表中抽出的邏輯上相關的數據集合。

序列 提供有規律的數值。

索引 提高查詢的效率

同義詞 給對象起別名

SELECT * FROM user_tables;--用戶創建的表
SELECT * FROM user_oobjects;--查看用戶定義的數據庫對象
SELECT * FROM user_catalog;--查看用戶定義的表 視圖 同義詞和序列
表名和列名的命名規則
 •必須以字母開頭
 •必須在 1–30 個字符之間
 •必須只能包含 A–Z, a–z, 0–9, _, $, 和 #
 •必須不能和用戶定義的其他對象重名 

 •必須不能是Oracle 的保留字
#創建表
CREATE TABLE dept
 (deptno NUMBER(2),
 dname VARCHAR2(14),
 loc VARCHAR2(13));
依託於原來的表,數據也一起導入
create table tableName as select [] from tableName2 --第二種方式
確認 desc dept;--查看
常見類型
 VARCHAR2(size) 可變長字符數據
 CHAR(size) 定長字符數據
 NUMBER(p,s) 可變長數值數據
 DATE 日期型數據
 LONG 可變長字符數據，最大可達到2G
 CLOB 字符數據，最大可達到4G
 RAW (LONG RAW) 原始的二進制數據
 BLOB 二進制數據，最大可達到4G
 BFILE 存儲外部文件的二進制數據，最大可達到4G
 ROWID 行地址
 
修改表
alter table [tableName] add(colum,類型約束)|modify(colum,類型約束)|drop colum|rename colum coulm1 to coulm2
刪除表
drop table tableName;
清空
truncate table tableName;
修改表名
rename tableName1 to tableName2

數據操作(DML)

#插入
insert into tableName([column1],[column2],...) 
values([value1],[value2],...)
字符和日期放在''中
插入空值 在tableName後不寫列名
或者直接在value中寫null
從其他表導入進來
insert into tableName1([column1],[column2])
select * from tableName2
使用腳本輸入
insert into tableName([column1],[column2])
values(&id,'&date')--&為變量執行時候需要手動輸入
--update
update tableName 
set coulmn1=value1,....
where [條件]
--刪除
delete from tableName
where [條件]

事務

以一個DML語句作為開始執行.
commit或rollback
DDL自動提交
用戶會話正常結束
系統異常終止
commiit和rollback的優點:
\t確保數據完整性。
\t數據改變被提交之前預覽。
\t將邏輯上相關的操作分組。
\t
savepoint PointName;--設置保存點
rollback pointName;--回滾到事務 

當前用戶沒有提交前,其他用戶不能查看到
當前用戶修改的列,沒有結束(commit或rollback)其他用戶才能操作

約束

NOT NULL非空 列上約束

UNIQUE唯一,可以允許多個null值

PRIMARY KEY不能為空 不能重複

FOREIGN KEY

CHECK

創建
CREATE TABLE tableName(
 id NUMBER(6) ,
 name VARCHAR2(20) ,
 job_id VARCHAR2(10) NOT NULL,--系統命名
 phone varchar(12) unique,
 age number check(age>0 and age<120),
 --或 CONSTRAINT phone_unique(用戶命名) UNIQUE(phone);
 --表級的約束
 CONSTRAINT pk_id(pk名) PRIMARY KEY (id),
 CONSTRAINT job_fk FOREIGN KEY (job_id)--當前表
 REFERENCES tb_job(id)--主表的表名和列名
 #ON DELETE CASCADE(級聯刪除): 當父表中的列被刪除時，子表中相對應的列也被刪除
 #ON DELETE SET NULL(級聯置空): 子表中相應的列置空
 );
添加
添加或刪除 
有效化和無效化 

alter table tableName 
add Constraint [約束名] 
alter table tableName
modify [字段設置]
刪除約束
alter table tableName
drop Constraint [約束名] 
無效化
alter table tableName
disable Constraint [約束名] 
無效化
alter table tableName
enable Constraint [約束名] 

視圖

表中抽取的邏輯上相關的數據集合
\t視圖時一種虛表,視圖上建議在已有表的基礎上,
create view 視圖名
as
查詢語句
創建視圖時可以使用別名 調用視圖時使用的就是相同的別名
修改視圖會同時修改到基表中
複雜視圖:包含了組函數
特性 簡單視圖 複雜視圖
表的數量 一個 一個或多個
函數 沒有 有
分組 沒有 有
DML 操作 可以 有時可以
修改視圖
create or replace view viewName 

as
[查詢語句]
with read only --屏蔽DML 任何的DML都會報錯
複雜視圖
在視圖名後加上列名,查詢語句的列名和數據一致
視圖中以下情況不能刪除
 組函數
 GROUP BY 子句
 DISTINCT 關鍵字
 ROWNUM 偽列
視圖中以下情況不能修改
 組函數
 GROUP BY子句
 DISTINCT 關鍵字
 ROWNUM 偽列
 列的定義為表達式
視圖中以下情況不能insert
 組函數
 GROUP BY 子句
 DISTINCT 關鍵字
 ROWNUM 偽列
 列的定義為表達式
 表中非空的列在視圖定義中未包括
 
drop view viewName;--只會刪除查詢語句不會刪除對應的數據

分頁

rownum 偽列 rownum 只能查詢< <=;不能查詢其他 分頁查詢 
select * from 
--rownum 不作為一個偽列 

 (select rownum rn,* from 
--已經排好的表 
(select * from tableName order by [order_exp]) )
 where rn>=(page-1)*size and rn<page>