《深入淺出SQL》讀書筆記

之前寫過兩篇sql學習筆記，SQL語句練習筆記（以加州薪水為例）

SQL語句多表連接（以相親連連看為例）

此次閱讀中已掌握的知識點不再贅述

摘要版目錄：

一、數據和表

創建資料庫和表，學習如何在表中插入最常見的數據類型同時保證需要值的所有列

結果：

在查詢框下查看錶：desc secret2

如果創建表secret2後想再填一列，不能在原代碼直接添加。

任何屬於varchar/char/date/nlob列的值都需要加單引號

把數據錄入資料庫：

結果

NULL不等於0，不等於空字元串，甚至不等於自己。

NULL代表未定義的值。

default 用於日後輸入缺乏部分數據記錄時自動填入的默認值

二、select語句

最有用的查詢

select *from （表名） where (條件)n

聯合查詢

select*from (表名) where （條件1）+（條件2）n

找null值用關鍵字 is null

不想一直and or重複，用like通配符

select*from （表） where （某列） like %can

表示查找所有結尾為ca的內容

批量的or等於in

三、delete和update

改變數據和刪除數據

刪除數據：

delete from (表) where （滿足條件的列）n

刪除所有行：（慎用）

delete from (表)n

更新原表某個值

一次性更新很多列

四、聰明的表數據

原子性數據：當數據具有原子性，表示已經分割至最小塊，不能再分了，兩個規則：同列的數據類型相同，具有原子性數據的表中不會有多個儲存同類數據的列

總結一點來說，列的數據類型相同，不會有重複意思的列。

主鍵：表中某個列，可以讓每一條記錄成為唯一的。特點：主鍵不能為null，不可以被修改，插入新記錄時必須制定主鍵值

show create table

使用這個命令來呈現創建現有表的正確語法

原表增加一列名為fruit_id並設為主鍵的列

alter table fruits add column fruit_id int not null auto_increment first,add primary key (fruit_id);n

五、alter改變歷史

alter table

保留現有數據為前提，修改表名稱和整體結構

在原表中增加某列

給表換個名字

移除某列

alter table （表） drop column （要移除的列名稱）n

如果想把某列改名同時更改數據類型

alter table (表) modify column (要修改的列名) （數據類型比如varchar(100)）n

想一下子修改很多列和類型，比如想把1列2列改名改類型

六、select進階

count/distinct/avg/min/group by/sum/max這些就不說了，這裡講一個書中很有趣的例子來加深對select高級用法的理解。

例子一

假如你是電影院經理，這天收到一位媽媽投訴說自己小孩本來看的是動畫片，結果影院放的是R片，需要影院給個解釋。

修正的代碼如下：

update movienset category=ncasenwhen drama=T then dramanwhen comedy=T then comedynwhen action =T then actionnwhen gore=T then horrornwhen scifi=T then scifinwhen for_kids=T then familynwhen cartoon=T and rating=G then familynelse miscnend;n

例子二

如果有3000多條電影信息，如何選出家庭類的電影並將電影名按照A-Z字母排序？

結果預覽：

例子三——group by應用

七、多張表的資料庫設置

這兩章主要講表連接，第7章的一對一、一對多、多對多、第一範式、第二範式、第三範式、外鍵、組合鍵等知識點，理論詳見書《深入淺出sql》

八、子查詢，查詢中的查詢

後邊目錄有改動，直接按子查詢和連結表兩塊進行總結，這裡以《mysql必知必會》中子查詢例子為例,子查詢最常見的使用是在where子句的 in 操作符中，以及用來填充計算列。

資料庫原始文件：6個表

例子1：假如需要列出訂購物品 TNT2 的所有客戶，應該怎樣檢索？（用到表為customers和orders）

select cust_name,cust_contact from customers where cust_id in(nselect cust_id from orders where order_num in (select order_num from orderitems where prod_id=TNT2));n

ps：這裡用聯接的相同查詢更方便，代碼和對比如下：

select cust_name,cust_contact from customers,orders,orderitems where customers.cust_id=orders.cust_idnand orderitems.order_num=orders.order_numnand prod_id=TNT2;n

例子2：假如需要顯示 customers表中每個客戶的訂單總數。

select cust_name,cust_state,(select count(*) from orders where orders.cust_id=customers.cust_id) as orders nfrom customers order by cust_namen

九、聯結表

總的分法：

內部聯結：最常用最簡單不說了

自聯結：能把單一表當成兩表具有完全相同信息的表來進行查詢

自然聯結：不用管它，書上說迄今為止我們建立的每個內部聯結都是自然聯結，很可能我們永遠都不會用到不是自然聯結的內部聯結。

外部連接：聯結包含了那些在相關表中沒有關聯行的行。這種類型的聯結稱為外部聯結。

----------聯結表的例子----------

1.自聯結例子：(與子查詢對比)====================

假如你發現某物品（其ID為 DTNTR ）存在問題，因此想知道生產該物品的供應商生產的其他物品是否也存在這些問題。此查詢要求首先找到生產ID為 DTNTR 的物品的供應商，然後找出這個供應商生產的其他物品。

查詢代碼及結果

#方法一 利用子查詢nselect prod_id,prod_name from products where vend_id=(select vend_id from products where prod_id=DTNTR);n#方法二 利用自聯接nselect p1.prod_id,p1.prod_name from products as p1,products as p2 where p1.vend_id=p2.vend_id and p2.prod_id=DTNTRn

2.簡單聯結例子（兩種方法）========================

例子一：有兩個表，供應商（vendors）和供應商對應的商品、價格表(products)，如何查看每個供應商對應的商品和商品價格？

操作代碼：

select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id=products.vend_id order by vend_name,prod_namen

上面的語句如果去掉where vendors.vend_id=products.vend_id order就變成了笛卡爾積，如果這裡用笛卡爾積，返回的數據用每個供應商匹配了每個產品，它包括了供應商不正確的產品。實際上有的供應商根本就沒有產品，不正確。這裡再記錄一個比較常用的cross join(交叉也叫笛卡爾連接)返回兩張表每一列相乘結果(比如A，B表，A的第1行與B123456行匹配，A第二行與B的123456行匹配，A的第三行與B的123456行匹配......)

這道題也可以用inner join

select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id=products.vend_idn

3.外部聯結（left join）例子========================

? 對每個客戶下了多少訂單進行計數，包括那些至今尚未下訂單的客戶；

? 列出所有產品以及訂購數量，包括沒有人訂購的產品；

? 計算平均銷售規模，包括那些至今尚未下訂單的客戶。

十、總結

• 子查詢與聯接結表相比，感覺聯結表更方便
• where語句很常用，cross join很少用
• 聯結這塊有些複雜，需要不斷練習

推薦閱讀：