技術問答集錦（九）資料庫系列

猿碼道發表於2018-01-30
資料庫

1 資料庫SQL分為幾種

DML（資料操作語言）、DDL（資料定義語言）、DCL（資料控制語言）

2 描述表SQL

desc table_name 描述表結構、型別、約束

3 重新給列取別名

select last_name , salary*12 "year_sal" from employee; （別名也可以不加雙引號）

4 連線運算子

select first_name || ' ' || last_name || ','|| title "Employees" from employee;

5 空值處理

select last_name , title , salary*NVL(commission_pct , 0)/100 COMM from s_emp;

如果某一個欄位為NULL，則對該欄位進行任何操作後仍為NULL；

NVL函式：表示該欄位為NULL時，則用第二個引數值來代替；

6 like 使用

select last_name from s_emp where last_name like 'M%'; 匹配多個字元

select last_name from s_emp where last_name like '_M%';匹配單個字元

select last_name from s_emp where last_name like 'S\_M%' escape '\';

escape：指定一個字元為拖字元，意義指拖字元後面的字元不再為關鍵字，僅是一個字元。

7 排序 order by

select last_name , salay * 12 from s_emp order by 2; 代表按查詢結果的第二列升序排列。（預設為升序排序）

8 SQL函式使用

LOWER('Sql Course')=> sql course 轉換為小寫形式

UPPER('Sql Course')=> SQL COURSE 轉換為大寫形式

INITCAP('SQL COURSE')=> Sql Course 轉換為每個單詞首字母大寫

CONCAT('Good','String')=> GoodString 連線字串

SUBSTR('String',1,3)=>Str 1代表起始位置，3代表擷取的長度

LENGTH('String')=>6 獲取字串長度

ROUND(45.923,2)=>45.92 做四捨五入處理 表示保留到小數點後2位

ROUND(45.923,0)=>46 做四捨五入處理 表示保留到個位，若第二個引數省略，預設為0

ROUND(45.923,-1)=>50 做四捨五入處理 表示保留到十位

TRUNC(45.923,2)=>45.92 做直接截斷處理，不進行四捨五入處理，表示保留到小數點後2位

TRUNC(45.923,0)=>45 做直接截斷處理，不進行四捨五入處理，表示保留到個位，若第二個引數省略，預設為0

TRUNC(45.923,-1)=>40 做直接截斷處理，不進行四捨五入處理，表示保留到十位

TO_CHAR(birthday,'YYYY-MM-DD') Date轉為字串

TO_DATE('1980-03-04','YYYY-MM-DD') 字串轉為Date

TO_NUMBER('1980') 字串轉為數字

MOD(id,2) 表示對2進行求餘運算

9 函式巢狀使用

select last_name , NVL(TO_CHAR(manager_id),'No Manager') from s_emp where manager_id is null;

10 獲取系統當前日期

select name , birthday , SYSDATE from employee;

11 多表連線查詢

等值連線、交叉連線、外連線、自連線

12 分組查詢

SELECT 後可出現分組依據欄位、分組函式

FROM 表名

WHERE 後不能夠使用分組函式作為條件的，是對分組前資料篩選，因為資料還沒有分組，所以就沒有分組函式可言。

GROUP BY 指定分組依據欄位

HAVING 對分組記錄進行篩選，而且必須伴隨group by 出現，後能跟分組依據欄位、分組函式

ORDER BY 對分組記錄進行排序，後能跟分組依據欄位、分組函式

13 分組函式

AVG()=>平均值；COUNT()=>計數；MAX()=>最大值；MIN()=>最小值；SUM()=>求和值；

14 資料完整性約束

實體完整性：規定表的每一行在表中是惟一的實體。

列完整性： 是指表中的列必須滿足某種特定的資料型別約束，其中約束又包括取值範圍、精度等規定。

參照完整性： 是指兩個表的主鍵欄位和外來鍵欄位的資料應一致，保證了表之間的資料的一致性，防止了資料丟失或無意義的資料在資料庫中擴散。

使用者定義的完整性： 不同的關聯式資料庫系統根據其應用環境的不同，往往還需要一些特殊的約束條件。使用者定義的完整性即是針對某個特定關聯式資料庫的約束條件，它反映某一具體應用必須滿足的語義要求。

15 資料庫設計三正規化

第一正規化(確保每列保持原子性)：最基本的正規化。如果資料庫表中的所有欄位值都是不可分解的原子值，就說明該資料庫表滿足了第一正規化。

第二正規化(確保表中的每列都和主鍵相關)：在第一正規化的基礎之上更進一層。第二正規化需要確保資料庫表中的每一列都和主鍵相關，而不能只與主鍵的某一部分相關（主要針對聯合主鍵而言）。也就是說在一個資料庫表中，一個表中只能儲存一種資料，不可以把多種資料儲存在同一張資料庫表中。

第三正規化(確保每列都和主鍵列直接相關,而不是間接相關)：在第二正規化的基礎之上更進一層 。要確保資料表中的每一列資料都和主鍵直接相關，而不能間接相關。

16 varchar2和char區別

varchar2(size) 可變長度的字元值，節省空間，查詢效率低

char(size) 固定長度的字元值，浪費空間，查詢效率高

17 資料型別

NUMBER(8,2) 8位有效數字，小數點後有效位數2位

CLOB 字元大資料，儲存很長的文字，最大可為4G

BLOB 二進位制大資料，儲存圖片，最大可為4G

18 日期函式

select SYSDATE from DUAL 可檢視日期

MONTHS_BETWEEN('01-12-95','01-10-94') 獲得相隔的月數

ADD_MONTHS('01-10-95',2) 獲得2個月後的日期

NEXT_DAY('01-10-95','FRIDAY') 從該日期開始的星期五是幾號

LAST_DAY('01-10-95') 該月份的最後一天

ROUND(SYSDATE,'MONTH') 對月份做四捨五入處理

ROUND(SYSDATE,'YEAR') 對年份做四捨五入處理

TRUNC(SYSDATE,'MONTH') 對月份直接進行截斷處理

TRUNC(SYSDATE,'YEAR') 對年份直接進行截斷處理

19 表約束與列約束定義主鍵約束

主鍵欄位：

  1. 一個表只能建立一個主鍵；

  2. 主鍵欄位的值唯一；

  3. 主鍵欄位的任意部分都不允許為空；

  4. 凡是建立了主鍵約束的列，在主鍵列都會自動建立唯一性索引；

    create table stu
    (
       id number(7) constraint stu_id_pk PRIMARY KEY ,  列約束
       name varchar2(20) constraint stu_name_nn not null, 列約束
       constraint stu_id_name_pk PRIMARY KEY(id,name) 表約束
    );
複製程式碼

表約束可以定義組合欄位為主鍵約束，列約束是不可以的。

20 表約束與列約束定義外來鍵約束

列約束：

create table s_emp
(

... dept_id number(7) constraint s_emp_dept_id_fk references s_dept(id),...

);
複製程式碼

表約束：

create table s_emp
(

... constraint s_emp_dept_id_fk FOREIGN KEY(dept_id) references s_dept(id),...

);
複製程式碼

on delete cascade : 級聯刪除（把主鍵表中的記錄刪除，則外來鍵表中自動刪除相應的記錄） on delete set null ：刪除後，欄位置空

21 表約束與列約束定義唯一性束

列約束：

create table s_emp
(

...,phone varchar2(10) constraint s_emp_phone_uk UNIQUE,...

);
複製程式碼

表約束：

create table s_emp
(

...,constraint s_emp_phone_uk UNIQUE(phone,name),...

);
複製程式碼

如果定義了唯一約束欄位，會自動給該列新增唯一索引。

22 自定義檢查約束

表約束：

create table s_emp
(

...,constraint s_emp_com_pct_ck CHECK(com_pct in(10,12.5,15,17.5,20)),...

);
複製程式碼

23 使用子查詢建立表

create table s_emp_41
AS
select id , last_name , userid , start_date
from s_emp
where dept_id=41
複製程式碼

原來表中僅有非空約束被拷貝，其他約束都沒有被拷貝。

24 新增、修改、刪除列

alter table s_emp add(comments varchar2(255));僅能新增非空約束/無約束的列，其他約束不可以。當表中有記錄時，新增一個非空的列，則會報錯。

alter table s_emp drop(comments);

alter table s_emp modify(comments varchar2(50)); 僅能修改欄位的資料型別、非空約束、預設值。

25 新增、刪除、生效、失效約束

alter table s_emp
add constraint s_emp_manager_id_fk FOREIGN KEY(manager_id) REFERENCES s_emp(id);

alter table s_emp
drop constraint s_emp_manager_id_fk ;

alter table s_emp
drop PRIMARY KEY cascade；級聯刪除外來鍵約束；刪除主鍵約束時，可以不用寫約束名；（因為主鍵約束唯一）

alter table s_emp
disable constraint s_emp_id_pk cascade;外來鍵約束級聯失效

alter table s_emp
enable constraint s_emp_id_pk cascade;外來鍵約束級聯生效
複製程式碼

26 刪除、重新命名錶

drop table s_emp cascade constraints;在刪除表的同時，級聯刪除該表上的所有約束。屬於DDL，刪除表的結構，釋放表的空間，不可恢復。

truncate table s_emp;屬於DDL，刪除表記錄，保留表結構，不可恢復。

delete table s_empl;屬於DML，刪除表記錄，保留表介面，可按條件刪除，可恢復。

rename s_emp to s_emp_new;重新命名錶名。

27 增加、刪除、修改表記錄

insert into  s_emp (...,...,...) values('','','');

insert into  s_emp(...)
select id , last_name , salary
from s_emp
where start_date <'01-02-94';

update s_emp set dept_id=10 where id=2;

delete from s_emp where id=2; 刪除記錄時不能違反表的參照完整性約束；
複製程式碼

凡是增加、刪除、修改，均要使用commit提交！

28 資料庫事務

一個或多個DML語句組成，各DML語句是不被打斷的，可為一事務。

一個DDL語句組成，可為一事務。

一個DCL語句組成，可為一事務。

29 資料庫事務結束

commit/rollback。（rollback 回滾到最近一次commit位置）

執行到一個DDL/DCL，事務結束，事務會自動提交。

系統崩潰，自動回滾。

30 序列 Sequence

能夠自動產生一個唯一數字的物件；

可以被共享的物件；

只能保證給出的值是有序的；

典型的建立主鍵值的方法；

31 索引

索引的原理：二叉樹結構；

如果在表中的某個欄位加上了主鍵約束或唯一約束，系統會自動為該欄位建立唯一索引；

索引是建在欄位上的，是與欄位相關的；

使用索引能減少磁碟的IO，索引是自動被Oracle伺服器使用和維護；

32 檢視

一張虛表，與表有相同的結構，虛表不儲存資料，只保留結構，資料都從表中來；

33 集合操作

UNION：並集，去掉兩個查詢結果中重複的記錄；

UNIONALL：並集，不會去掉重複記錄；

INTERSECT：交集，取兩查詢結果中相同記錄；

MINUS：取相應的補集；

相關文章