《SQL必知必會》讀書筆記

陳振發表於2018-01-25
SQL筆記

SQL 必知必會

在mac終端操作sqlite：

  • cd 資料庫所在的目錄
  • sqlite3 資料庫檔名 //帶字尾）（此時已經開啟資料庫）
  • .tables //顯示資料庫中所有已經建立的表
  • .schema //顯示所有表的模式
  • .headers on //顯示欄位名（查詢時才會顯示）
  • .mode column/list/line
  • 執行sql語句必須在末尾加分號

###distinct

 SELECT DISTINCT name FROM TB_BOOK_TAG;
複製程式碼

關鍵字distinct，用於去除name列中所有行中重複元素。

###limit

SELECT name FROM TB_BOOK_TAG LIMIT 5;
複製程式碼

關鍵字limit,返回name列指定行數。

SELECT name FROM TB_BOOK_TAG LIMIT 5 OFFSET 0;
等同於下面寫法（shortcut）
SELECT name FROM TB_BOOK_TAG LIMIT 0，5;
複製程式碼

###limit ... offset ...

關鍵字LIMIT ... OFFSET ...,limit後跟的數字指定顯示多少行，offset後跟的數字表示從什麼位置開始。（0是第一行）

###註釋

 --this is a comment

複製程式碼

關鍵--加註釋，單行註釋。

 /* comments */
複製程式碼

關鍵/**/，多行註釋。

###order by

 SELECT * FROM TB_BOOK_TAG ORDER BY name;
複製程式碼

關鍵字：order by + 欄位名，按該欄位所屬列的首字母進行排序。要確保該子句是select語句中最後一條子句，否則會出現錯誤。

 SELECT publisher,pubdate FROM TB_BOOK_ENTITY ORDER BY publisher,pubdate;
複製程式碼

關鍵字：order by + 欄位名 + 欄位名，首先按publisher進行排序，然後按照pubdate進行排序。對於第二個欄位的排序，當且僅當具有多個相同的publisher時才會對其按照pubdate進行排序，如果publisher列中所有值都是唯一的，則不會按pubdate進行排序。

###desc

SELECT publisher,pubdate FROM TB_BOOK_ENTITY ORDER BY pubdate DESC;
複製程式碼

關鍵字：desc，order by 預設是按升序進行排序，當在欄位名後加desc後，將對該欄位進行降序排列。

SELECT pubdate,price FROM TB_BOOK_ENTITY ORDER BY pubdate DESC,price;
複製程式碼

pubdate按降序排列，price，仍然按照升序排列（在pubdate相同的行）。所以，如果想在多個列上進行降序，必須對每一列都指定desc關鍵字。

###where

SELECT * FROM TB_BOOK_TAG WHERE count = 1;
複製程式碼

關鍵字：where，指定搜尋條件進行過濾。where子句在表名（from子句）之後給出。在同時使用where和order by時，應該讓order by位於where之後。

操作符 說明
= 等於
<> 不等於
!= 不等於
< 小於
<= 小於等於
!< 不小於
> 大於
>= 大於等於
!> 不大於
BETWEEN 在指定的兩個值之間
IS NULL 為NULL值

注意：NULL和非匹配 通過過濾選擇不包含（如<>）指定值的所有行時，你可能希望返回含NULL值的行，但是這做不到，因為NULL有特殊的含義，資料庫不知道它們是否匹配，所以在進行匹配過濾或非匹配過濾時，不會返回這些結果。

where...and...

select * from contacts where name = "fff" and mobile = "d";
複製程式碼

關鍵字：and，組合where子句。

where...or...

select * from contacts where name = "fff" or mobile = "d";
複製程式碼

關鍵字：or，組合where子句。

注意：在同時使用and和or時要注意求值順序，and優先順序大於or。因此在任何時候使用具有and和or操作符的where子句時，都應該使用圓括號明確地分組操作符

where...in...

select * from contacts where mobile in ('12', '444') order by mobile;
複製程式碼

關鍵字：in，用來指定條件範圍，範圍中的每個條件都可以進行匹配。in操作符一般比一組or操作符執行的更快。in最大的優點是可以包含其他select語句，能夠更動態的建立where子句。

not

select * from contacts where not mobile = '12';
複製程式碼

關鍵字：not，where子句中用來否定其後條件的關鍵字。上面的例子也可以用<>。在簡單語句中，not沒有什麼優勢，但是，在更復雜的子句中，not非常有用。例如，在與in操作符聯合使用時，not可以非常簡單的找出與條件列表不匹配的行。如下例子：

 SELECT * FROM CONTACTS WHERE NOT mobile IN ('111111', '3333');
複製程式碼

like

萬用字元（wildcard）用來匹配值的一部分的特殊字元。
搜尋模式（search pattern）由字面值，萬用字元或兩者組合構成的搜尋條件。
複製程式碼

萬用字元搜尋只能用於文字欄位（字串），非文字資料型別欄位不能使用萬用字元搜尋

%萬用字元

在搜尋字串中，%表示任何字元出現任意次數

select * from tb_book_tag where name like '計算機%';
複製程式碼
注意字串後面所跟的空格：
許多DBMS會用空格來填補欄位內容。例如，如果某列有50個字元，而儲存文字為Fish bean bag toy（17個字元），則為填滿該列會在文字末尾追加33個空格。如果此時用‘F%y’來檢索，便檢索不到上述字串。簡單解決辦法是‘F%y%’。更好的解決辦法是用函式去掉空格。
複製程式碼
'%' 不會匹配為NULL的行
複製程式碼

下劃線_萬用字元

用途和%一樣，但它只匹配單個字元，而不是多個。

select * from tb_book_tag where name like '計算機__';
複製程式碼

使用萬用字元的技巧

SQL萬用字元搜尋比其他搜尋更耗時。

1. 不要過度使用萬用字元，如果其他操作能達到目的，使用其他操作。
2. 在確實需要使用的時候，也儘量不要把它用在搜尋模式的開始處。把萬用字元置於開始處，搜尋起來是最慢的。
3. 特別要注意萬用字元的位置不要放錯。
複製程式碼

##建立計算欄位

計算欄位並不實際存在於資料庫表中，計算欄位是執行時在SELECT語句內建立的。
複製程式碼
select rtrim('~    ') || name from tb_book_tag;
複製程式碼

關鍵字：||和rtrim()。 ||拼接操作符。rtrim()去除文字右邊的空格。trim()去除兩邊的空格。

as

select name || 'is foolish' as title from contacts;
複製程式碼

關鍵字：as，全稱alias。它指示SQL建立一個包含指定計算結果的名為title的計算欄位，任何客戶端應用可以按名稱引用這個列，就像一個實際表列一樣。

執行算術計算

+ - * /

select mobile, (mobile + 1)*2 as count_mobile from contacts;
複製程式碼

關鍵字：+或-或*或/；

函式

一下所用到的是適用於sqlite的函式，不一定適用於其他DBMS。

upper()

select name ,upper(name) as name_upper from contacts;
複製程式碼

關鍵字：upper()轉大寫

sqlite中常用於文字處理函式：

函式 說明
length() 返回字串的長度
lower() 將字串轉小寫
ltrim() 去掉字串左邊的空格
rtrim() 去掉字串右邊的空格
upper() 將字串轉大寫

avg()

select avg(mobile) as avg_id from contacts;
複製程式碼

關鍵字：avg()，對錶中某列所有行或特定行中的資料求平均值。該函式會忽略值為NULL的行。

count()

select count(*) as num_cust from contacts;

select count(name) as num_name from contacts;
複製程式碼

關鍵字：count(),使用count(*)，對錶中行的數目進行計數，不管表列中是否包含NULL值。使用count(column_name),對特定列中具有值的行進行計數，忽略NULL值。

###sum()

select sum(mobile) as sum_mobile from contacts;
複製程式碼

關鍵字：sum(), 忽略NULL值

聚集不同值

count(distinct name)

select count(distinct name) from tb_book_tag;
複製程式碼

組合聚集函式

select count(*) as num_items, min(count) as count_min, max(count) as count_max, avg(count) as count_avg from tb_book_tag;
複製程式碼

##分組資料

group by

select name, count(*) as num_names from tb_book_tag group by name order by name;
複製程式碼

關鍵字：group by，group by子句必須出現在where子句之後，order by子句之前。

group by...having...

select name , count(*) as amounts from tb_book_tag group by name having amounts >= 10;
複製程式碼

關鍵字：having。對分組進行過濾。而where對分組不起作用，它是針對表中每一行來過濾。

使用子查詢

select cust_id 
from orders 
where order_num in (select order_num 
                     from orderitems
                     where prod_id = 'RGAN01');
複製程式碼
注意：
作為子查詢的select語句只能查詢單個列。企圖檢索多個列將返回錯誤。
同時要注意效能問題。
複製程式碼

使用子查詢作為計算欄位

select cust_name, 
       cust_state,
       (select count(*) 
        from orders 
        where orders.cust_id = customers.cust_id) as orders from customers 
order by cust_name;
複製程式碼

聯結表

關係表

為理解關係表，來看一個例子：

有一個包含產品目錄的資料庫表，其中每類物品佔一行，對於每種物品，要儲存的資訊包括產品描述，價格以及生產該產品的供應商。 現有同一供應商生產的多種物品，那麼在何處儲存供應商名聯絡方法等資訊？將這些資料與產品資訊分開儲存的理由是：

  1. 同一供應商的每個產品，其供應商的資訊是相同的，對每個產品重複此資訊既浪費時間又浪費空間；
  2. 如果供應商資訊發生變化，只需修改一次即可；
  3. 如果有重複數九，則很難保證每次輸入該資料的方式都相同，

相同的資料出現多次決不是一件好事，這是關聯式資料庫設計的基礎。關係表的設計就是要把資訊分解成多個表，一類資料一個表。各表通過某些共同的值相互關聯（所以才叫關聯式資料庫）。

如果資料儲存在多個表中，怎樣用一條select語句就檢索出資料？ 答案是使用聯結，聯結是一種機制，用來在一條select語句中關聯表

select vend_name, prod_name, prod_price 
from products, vendors 
where vendors.vend_id = products.vend_id;
複製程式碼

等同於下面的寫法：

select vend_name, prod_name, prod_price 
from vendors inner join products 
on vendors.vend_id = products.vend_id;
複製程式碼

在聯結兩個表時，實際要做的是將第一個表中的每一行與第二個表中的每一行配對。where子句作為過濾條件，只包含那些匹配給定條件的行。沒有where子句，第一個表中的每一行將與第二個表中的每一行配對，而不管他們邏輯上是否能匹配在一起。這種聯結稱為等值聯結（equijoin），也稱為內聯結（inner join）。

笛卡爾積（cartesian product）：
由於沒有聯結條件的表關係返回的結果為笛卡爾積。檢索出的行的數目將是第一個表中的行數乘以第二個表中的行數。

返回笛卡爾積的聯結也叫叉聯結（cross join）。
複製程式碼

SQL不限制一條select語句可以聯結的表的數目。如下：

select prod_name, vend_name, prod_price, quantity 
from orderitems, products, vendors 
where products.vend_id = vendors.vend_id 
and orderitems.prod_id = products.prod_id 
and order_num = 20007;
複製程式碼
注意：效能考慮
DBMS在執行時關聯指定的每個表，以處理聯結。這種處理可能非常耗資源，因此應該注意不要聯結不必要的表。
複製程式碼

建立高階聯結

使用表別名

select cust_name, cust_contact 
from customers as c, orders as o, orderitems as oi 
where c.cust_id = o.cust_id 
and oi.order_num = o.order_num 
and prod_id = 'RGAN01';
複製程式碼

使用表別名的兩個主要理由：

  • 縮短SQL語句
  • 允許在一條select語句中多次使用相同的表

自聯結

select  cust_id, cust_name, cust_contact 
from customers 
where cust_name = (select cust_name 
                   from customers 
                   where cust_contact = 'Jim Jones');
複製程式碼

以上子查詢效果等同於自聯結：

select c1.cust_id, c1.cust_name, c1.cust_contact 
from customers as c1, customers as c2 
where c1.cust_name = c2.cust_name 
and c2.cust_contact = 'Jim Jones';
複製程式碼

通常情況下，許多DBMS處理聯結遠比處理子查詢快得多

外聯結

select customers.cust_id, orders.order_num 
from customers 
left outer join orders 
on customers.cust_id = orders.cust_id;
複製程式碼

檢索包括沒有訂單顧客在內的所有顧客。

SQLite支援left outer join,但不支援right outer join.
複製程式碼

組合查詢

主要有兩種情況需要使用組合查詢：

  • 在一個查詢中從不同的表返回結構資料
  • 對一個表執行多個查詢，按一個查詢返回資料

union

select cust_name, cust_contact, cust_email 
from customers 
where cust_state in ('IL', 'IN', 'MI') 
union 
select cust_name, cust_contact, cust_email 
from customers 
where cust_name = 'Fun4All';
複製程式碼

union規則

  • union必須由兩條或兩條以上的select語句組成，語句之間用關鍵字union分隔。
  • union中的每個查詢必須包含相同的列，表示式或聚集函式（不過，各個列不需要以相同的次序列出）。
  • 列資料型別必須相容：型別不必完全相同，但必須是DBMS可以隱含轉換的型別。

union all

DBMS不取消重複行。

對組合查詢結果排序

select cust_name, cust_contact, cust_email 
from customers 
where cust_state in ('IL', 'IN', 'MI') 
union 
select cust_name, cust_contact, cust_email 
from customers 
where cust_name = 'Fun4All'
order by cust_name, cust_contact;
複製程式碼

在用union組合查詢時，只能使用一條order by子句，它必須位於最後一條select語句之後，DBMS用它來排序所有的select語句返回的所有結果。

插入資料

插入完整的行

insert into... values

insert into customers 
values ('1000000006', 'Chenzhen', 'Hennansheng', 'henan', 'China', '476300', 'China', 'John jdge', 'chen@gaiml.com');
複製程式碼

這種寫法簡單，但不安全，高度依賴表中列定義的次序，還依賴於其容易獲得的次序資訊。編寫依賴列次序的SQL語句是很不安全的，這樣做遲早會出問題。

更安全的方法：

insert into customers(cust_id,
                      cust_name, 
                      cust_address, 
                      cust_city, 
                      cust_state, 
                      cust_zip, 
                      cust_country, 
                      cust_contact, 
                      cust_email) 
values('1000000007',
       'Chenzhen', 
       'Hennansheng', 
       'henan', 
       'shangqiu', 
       '476300', 
       'China', 
       'John jdge', 
       'chen@gaiml.com');
複製程式碼

插入行時，DBMS將用values列表中的相應值填入列表中的對應項。其優點是，即使表的結構改變，這條insert語句仍然可以正常工作。

insert into... select...from...

insert into customers(cust_id,
                      cust_name,
                      cust_address, 
                      cust_city, 
                      cust_state, 
                      cust_zip, 
                      cust_country, 
                      cust_contact, 
                      cust_email) 
select cust_id, 
       cust_name, 
       cust_address, 
       cust_city, 
       cust_state, 
       cust_zip, 
       cust_country, 
       cust_contact, 
       cust_email
from CustNew;
複製程式碼

select語句從CustNew檢索出要插入的值，而不是列出他們。DBMS不關心select返回的列名，它使用的是列的位置，因此select的第一列（不管列名如何）將用來填充表列中指定的第一列，如此等等。

insert select 語句可以包含where子句。

從一個表複製到另一個表

create table custcopy as select * from customers;
複製程式碼

要想只複製部分列，可以明確給出列名。

更新和刪除資料

update...set... where...

update customers 
set cust_email = 'chenzhen@gmainl.com' 
where cust_id = '1000000008';
複製程式碼

更新多個列時，只需使用一條set命令：

update customers 
set cust_email = 'lala@qq.com',
    cust_contact = 'sam' 
where cust_id = '1000000008';
複製程式碼

沒有where子句，DBMS將會更新表中所有行。

delete

delete不需要列名或萬用字元，因為它刪除的是整行而不是刪除列，要刪除指定列，使用update

delete from custcopy 
where cust_id = '1000000008';
複製程式碼

如果省略where子句，它將刪除表中的每個顧客。如果想從表中刪除所有行，不要使用delete，可使用truncate table語句，它的速度更快，因為不記錄資料的變動。

建立和操縱表

create

create table Super 
(
    prod_id char(10) not null, 
    vend_id char(10) not null, 
    prod_name char(254) not null, 
    prod_price decimal(8,2) not null,   default 10.2
    prod_desc varchar(1000) null
);
複製程式碼

not null,可以阻止插入沒有值的列。預設是null。

SQLite獲得系統時間的函式date('now')

更新表

alert table

使用alert table更改表的結構，必須給出下面的資訊：

  • 在alter table之後給出要更改的表名。
  • 列出要做出哪些更改。
alter table Vendors
add vend_phone char(20);
複製程式碼
SQLite對使用alter table執行的操作有所限制。最重要的一個限制是，它不支援使用alter table定義主鍵和外來鍵。

使用alter table要極為小心，應該在進行改動錢做完整的備份（表結構和資料的備份）。資料庫表的更改不能撤銷，如果增加了不需要的列，也許無法刪除他們。
複製程式碼

###刪除表 drop table

drop table Super;
複製程式碼

使用檢視

檢視是虛擬的表。與包含資料的表不一樣，檢視只包含使用時動態檢索資料的查詢。 檢視（View）只不過是通過相關的名稱儲存在資料庫中的一個 SQLite 語句。檢視（View）實際上是一個以預定義的 SQLite 查詢形式存在的表的組合。

SQLite僅支援只讀檢視，所以檢視可以建立，可以讀，但其內容不能更改。
複製程式碼

刪除檢視 drop view

drop view customeremaillist;
複製程式碼

建立檢視create view

create view ProductCustomers as
select cust_name, cust_contact, prod_id
from Customers, Orders, OrderItems
where Customers.cust_id = Orders.cust_id
and OrderItems.order_num = Orders.order_num;
複製程式碼
where子句與where子句
從檢視檢索資料時如果使用了一條where子句，則兩組子句（一組子在檢視中，另一組，另一組是傳遞給檢視的）將自動組合。
複製程式碼

檢視為虛擬的表。它們包含的不是資料，而是根據需要檢索資料的查詢。檢視提供了一種封裝select語句的層次，可用來簡化資料處理，重新格式化或保護基礎資料。

管理事務處理

使用事務處理（transaction processing），通過確保成批的SQL操作要麼完全執行，要麼完全不執行，來維護資料庫的完整性。

關於事務處理的一些術語：

  • 事務（transaction）指一組SQL語句；
  • 回退（rollback）指撤銷指定SQL語句的過程；
  • 提交（commit）指將未儲存的SQL語句結果寫入資料庫表；
  • 保留點（savepoint）指事務處理中設定的臨時佔位符，可以對它釋出回退（與回退整個事務處理不同）。
可以回退哪些語句：
insert,update,delete
複製程式碼

管理事務的關鍵在於將SQL語句分解為邏輯塊，並明確規定資料何時應該回退，何時不應該回退。

begin;
delete from orders where order_num = 20009;
delete from orderitems where order_num = 20009;
commit;
複製程式碼

高階SQL特性

約束，索引，觸發器。

約束（constraint）

主鍵

create table Orders
(
    order_num integer not null primary key,
    cust_id char(10) not null references Customers(cust_id)
);
複製程式碼

表中任意列只要滿足以下條件，都可以用於主鍵。

  • 任意兩行主鍵值都不相同。
  • 每行都具有一個主鍵值（既列中不允許NULL）。
  • 包含主鍵的列從不修改或更新。
  • 主鍵值不能重用。

外來鍵

外來鍵是表中的一列，其值必須列在另一表的主鍵中。

外來鍵有助防止意外刪除。
在定義外來鍵後，DBMS不允許刪除在另一個表中具有關聯行的行。例如不能刪除關聯訂單的顧客，刪除改顧客的唯一方法是首先刪除相關的訂單。
複製程式碼

唯一約束 unique

唯一約束用來保證一列中的資料是唯一的。與主鍵的區別如下：

  • 表可包含多個唯一約束，但每個表只允許一個主鍵。
  • 唯一約束列可包含NULL值。
  • 唯一約束列可修改或更新。
  • 唯一約束列的值可重複使用。
  • 與主鍵不一樣，唯一約束不能用來定義外來鍵。

檢查約束 check

create table OrderItems
(
    ...
    quantity integer not null check (quantity > 0),
    ...
)
複製程式碼

索引 create index

索引用來排序資料以加快搜尋和排序操作的速度。想象一本書後的索引。

在開始建立索引前，應該記住以下內容：

  • 索引改善檢索操作的效能，但降低了資料插入，修改，和刪除的效能。在執行這些操作時，DBMS必須動態的更新索引。
  • 索引資料可能要佔用大量的儲存空間。
  • 並非所有資料都適合做索引。
  • 索引用於資料過濾和資料排序。
  • 可以在索引中定義多個列（例如，州加上城市）。這樣的索引僅在以州加城市的順序排序時有用。如果想按城市排序，則這種索引沒有用處。
CREATE INDEX index_name
ON table_name (column_name);
複製程式碼

刪除索引 drop index

DROP INDEX index_name;
複製程式碼

觸發器 Trigger

觸發器是特殊的儲存過程，它在特定的資料庫活動發生時自動執行。

CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name 
ON table_name
BEGIN
 -- Trigger logic goes here....
END;
複製程式碼

示例：

CREATE TRIGGER audit_log AFTER INSERT 
ON COMPANY
BEGIN
   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;
複製程式碼

列出觸發器

SELECT name FROM sqlite_master
WHERE type = 'trigger';
複製程式碼

相關文章