[查用] SQL 語法筆記


建立資料庫
CREATE SCHEMA 'YTShop' DEFAULT CHARACTER SET utf8;

使用資料庫
use YTShop;

建立資料表create table
create table customer(
    id int,
    name varchar(50),
    gender varchar(1),
    address varchar(200)
);

檢查表格是否存在
create table if not exists customer(
    id int,
    name varchar(50),
    gender varchar(1),
    address varchar(200)
);

描述表格
describe customer;

修改表格
ALTER TABLE customer
CHANGE COLUMN id id INT AUTO INCREMENT PRIMERY KEY,
CHANGE COLUMN name cname VARCHAR(50) NOT NULL,
ADD COLUMN phone VARCHAR(10);
上面這串的效果有
1. 把原本的 id 欄位改成 id 且具有自動遞增的primary key性質
2. 把原本的 name 欄位改成 cname 欄位，程度為變動字串最大50字元，且增加不可為空的限制條件
3. 增加一個欄位叫做 phone 長度為變動字串最大10個字元

刪除/捨棄表格 drop
DROP TABLE customer;
會把customer表格給刪除掉

在既有的資料表中塞進資料 insert into
INSERT INTO customer(name,gender,address)
VALUES('Tao','M','Taipei');

資料查詢 SELECT FROM
這裏使用網路上下載的sample 資料庫來做查詢說明
USE classicmodels;
SELECT * FROM customers;

查詢特定欄位 SELECT FROM
USE classicmodels;
SELECT customerNumer, customerName FROM customers;

取個別名來呈現欄位 AS
SELECT contactFirstName AS firstname, contactLastName AS lastname 
FROM customers;
原本的contactFirstName與contactLastName被取代成
firstname, lastname

查詢不重複的資料 (distinct 與 group by)
SELECT DISTINCT country FROM customers;
或是使用
SELECT country FROM customers GROUP BY country;
效能上 Group by 會比較好
因為 Distinct 會先做一個排序的動作

使用 case when else 來做類似 if else 的效果
SELECT customerNumber, creditLimit,
   CASE
      WHEN creditLimit > 10000 THEN 'Premium'
      WHEN creditLimit BETWEEN 5000 AND 10000 THEN 'Gold'
      ELSE 'Standard'
   END
FROM customers; 
上面的意思是說信用額度大於一萬的話就給 'Premium等級'
如果信用額度介於5000到10000之間則就分類為 'Gold'
其他情況分類為 'Standard'

使用 Concat 連結字串
SELECT contactFirstName, contactLastName,
CONCAT(contactFirstName, " ",contactLastName)
FROM customers;

使用 Cast 來變更data type
SELECT creditLimit,
CONCAT('creditLimit', ":",CAST(creditLimit AS CHAR(20))) AS creditLimit
FROM customers;
上面的效果是將整數跟字串連結在一起
使用Cast把整數轉換成字串

使用 SUBSTR
SELECT SUBSTR('123456789',3,6);
可以拿到 3456

計算字串長度 CHAR_LENGTH
SELECT CHAR_LENGTH("abcdefg");
會拿到 7

使用 reverse 反轉字串
SELECT REVERSE("abcdefg");
會拿到 gfedcba

資料篩選條件查詢 WHERE
SELECT * 
FROM customers
WHERE creditLimt > 5000;

條件查詢搭配字串處理活用，取得手機號碼0921開頭的使用者名字
SELECT customerName, phone
FROM customers
WHERE SUBSTR(phone,1,4) = '0921';

使用 Like 來做上述的篩選描述語句
SELECT customerName, phone
FROM customers
WHERE phone LIKE '0921%';
在這裏%的意思代表任意長度的字元
所以一樣可以取得電話號碼開頭0921的使用者名字

使用 OR 來做複合條件篩選
找出住在台北或基隆的客戶
SELECT customerName, address
FROM customers
WHERE (address = 'Taipei' OR address = 'Keelung');

使用 IN 來做複合條件篩選
這裏就做跟上面一步驟做的事情一樣，這次使用 IN 了完成任務
SELECT customerName, address
FROM customers
WHERE address IN ('Taipei', 'Keelung');

使用 update 做資料修改的動作
UPDATE customers SET name = 'Tao'
WHERE id = 1;
這可以把customers 資料比當中 id=1的那筆資料的名字改成 Tao

使用 REPALCE 修改資料
REPLACE INTO customer
(id, name, gender, address)
VALUES (1,'Tao', 'M', 'Taipei');
replace的運作機制是看id
如果id不在的話就做Insert資料
如果id存在的話就做 update 的動作

資料刪除 DELET
DELET FROM customer
WHERE id = 1;

資料刪除 使用 Truncate
TRUNCATE customer
這會把customer table裡面的資料全刪掉
效果等同於
DELET FROM customer
比較 Truncate 與 DELET
TRUNCATE 速度比 DELETE 快很多
是因為 Truncate ：
只會記錄一筆交易紀錄，不會記錄詳細到每一筆資料的刪除記錄
只會針對整個表格做 lock 不會針對每一筆資料做 lock 的動作
反之
Delete 就會很詳細的記錄與Lock所花的空間跟時間會比較多

資料排序 ORDER BY
USE classicmodels;
SELECT customerName, creditLimt
FROM customers 
ORDER BY creditLimit;
預設會由小排到大

排序順序 DESC (遞減), ASC(遞增)
SELECT customerName, creditLimt
FROM customers 
ORDER BY creditLimit DESC;
這樣可以由大排到小

多欄位排序
SELECT customerName, customerNumber, creditLimt
FROM customers 
ORDER BY creditLimit, customerNumber DESC;
這個呈現出來的效果是會先針對信用額度排序
再來才是對於顧客編號來排序

限制條件 LIMIT
SELECT customerName, creditLimt
FROM customers 
ORDER BY creditLimit DESC
LIMIT 3;
這個效果會把客戶的信用額度由大到小排序完成後
取出前三比資料

SELECT customerName, creditLimt
FROM customers 
ORDER BY creditLimit DESC
LIMIT 3,6;
這個效果會把客戶的信用額度由大到小排序完成後
從第三筆起算開始，列出六比資料

使用 OFFSET 搭配 LIMIT 來做資料限制條件 
SELECT customerName, creditLimt
FROM customers 
ORDER BY creditLimit DESC
LIMIT 6 OFFSET 3;
這個效果會把客戶的信用額度由大到小排序完成後
從第三筆起算開始，列出六比資料

計算資料數 COUNT
SELECT COUNT(*) FROM customers;
或是
SELECT COUNT(1) FROM customers;
這兩個效果是一樣的

平均 AVG
SELECT AVG(creditLimt) 
FROM customers;
加總 SUM
SELECT SUM(creditLimt) 
FROM customers;
最小 MIN
SELECT MIN(creditLimt) 
FROM customers;
最大 MAX
SELECT MAX(creditLimt) 
FROM customers;

分組計算 GROUP BY
SELECT country, AVG(creditLimt) 
FROM customers
GROUP BY country;
求出以國家為單位裡面的平均信用額度

找出符合條件的分組統計數 HAVING 
SELECT country, COUNT(1)
FROM customers
GROUP BY country
HAVING COUNT(1) >= 10;
可以找出有十個以上顧客的國家

合併表格 INNER JOIN
合併兩個表格所以要select 兩個table from customers表格跟orders表格
SELECT customerName, shippedDate
FROM customers 
INNER JOIN orders
ON customers.customerNumber = orders.customerNumber;
INNER JOIN 內部連結
ON 後面接連結條件 
可從 customers 表格中拿出 customerName
從 orders 表個中拿出 shippedDate

也可以用WHERE 來寫上面這件事
SELECT customerName, shippedDate
FROM customers, orders
WHERE customers.customerNumber = orders.customerNumber;

使用LEFT JOIN
想要知道所有顧客的資料，無論有沒有下訂單都可以秀出
此時就需要使用到LEFTJOIN 以左邊的為主來做JOIN
SELECT customerName, shippedDate
FROM customers 
LEFT JOIN orders
ON customers.customerNumber = orders.customerNumber;
WHERE orders.customerNumber = NULL
最後一句 ＮＵＬＬ的檢查條件可以撈出沒下訂單的客戶們
因為是LEFT JOIN 所以 orders 裡面 shippedDate 沒有訂單資料的話的話也會被秀出來，值會是 NULL

RIGHT JOIN的邏輯則是同理，依此類推
SELECT customerName, shippedDate
FROM customers 
RIGHT JOIN orders
ON customers.customerNumber = orders.customerNumber;
因為有訂單一定是客戶下的，所以不會有訂單產生時卻沒有對應到客戶的情況
因此這裡所做的結果等同於上面的INNER JOIN

資料的聯集 UNION 會自動去除重複的資料
SELECT customerName Country FROM customers WHERE credirLimit > 10000
UNION
SELECT customerName Country FROM customers WHERE country = 'USA'
可以查到使用者信用額度大於一萬，或是住在美國，重複的資料就會被自動刪除

資料的聯集 UNION ALL 可以保留重複的資料
SELECT customerName Country FROM customers WHERE credirLimit > 10000
UNION
SELECT customerName Country FROM customers WHERE country = 'USA'

使用子查詢來做資料的交集
例如找出顧客表格中找出最高的信用額度
並且找出那位客戶的名字
法一：先Order by把客戶名字信用額度由大到小排料 
再用 Limit做到篩選出第一名的信用額度
SELECT customerName, creditLimit
FROM customers
ORDER BY creditLimit DESC
LIMIT 1

法二：透過子查詢subquery來完成
SELECT customerName, creditLimit
FROM customers
WHERE creditLimit = 
(SELECT MAX(creditLimit) FROM customers);

使用 IN 來對子查詢做篩選
SELECT customerName, Country, creditLimit
FROM customers
WHERE country IN (
   SELECT country
   FROM customers
   GROUP BY country
   HAVING AVG(CreditLimit) > 90000
)
查出平均信用額度高於90000的國家

使用 EXISTS 來看下的查詢是否符合子查詢的條件
SELECT customerName, customerNumber
FROM customers
WHERE EXISTS(
    SELECT * FROM orders
    WHERE status = 'Shipped'
    AND customers.customerNumber = orders.customerNumber
);
可以拿出或已運到的客戶資訊

使用 NOT EXISTS
SELECT customerName, customerNumber
FROM customers
WHERE NOT EXISTS(
    SELECT * FROM orders
    WHERE status = 'Shipped'
    AND customers.customerNumber = orders.customerNumber
);
取得貨未運到的客戶資訊

使用子查詢來做資料的交集
SELECT DISTINCT customerName, creditLimit, Country
FROM customers
WHERE creditLimit > 100000
AND (creditLimit, Country) IN 
(SELECT creditLimit, Country FROM customers WHERE country = 'USA');
找出不重複的資料
客戶信用額度超過十萬且客戶住在美國

檢視表 View 的用途
將查詢先建成一個表格，簡化查詢的步驟
有點像是程式中variable的感覺
CREATE VIEW customer_v AS 
SELECT * FROM customers
WHERE country = 'USA';
上面這些步驟先把一段查詢記錄在 customer_v 當中
可篩選出住在美國的客戶
接著再進一步對剛剛建好的 View 做查詢
SELECT * FROM customer_v
... 
...

建立或取代 VIEW 的用法 CREATE OR REPLACE
CREATE OR REPLACE VIEW customer_v AS 
SELECT * FROM customers
WHERE country = 'canada';
Create or Replace 的效果會去看這個 view 有沒有定義過
如果沒有的話就 create 一個新的
如果已經存在了就 replace 取代掉它

刪除 VIEW 的方法 DROP VIEW
DROP VIEW customer_v;

建立索引 CREATE INDEX 讓資料快速可以找到
CREATE INDEX idx_customer 
ON customers(customerName);
這是針對customer 表格裡面的 customerName欄位做index

對多個欄位建立索引
CREATE INDEX idx_customer2 
ON customers(customerName, country);

指定索引的類型 USING 例如 BTREE ＨASH
CREATE INDEX idx_customer3 
ON customers(customerName, country) USING BTREE;

刪除/捨棄索引 DROP INDEX
DROP INDEX idx_customer;

留言

張貼留言

這個網誌中的熱門文章

[筆記] CRLF跟LF之區別 --- 隱形的 bug

圖片
最近在當助教時，偶然間遇到一個隱形的Bug 為什麼稱之為隱形呢？ 請見下面兩張圖 說明：answer.txt 與 answer_test.txt 這兩個檔案的內容用文字編輯器打開時，完全一模一樣，但是我在使用 diff 指令的時候居然出現判定為每一行都不一樣的情形 指令 diff answer.txt answer_test.txt 由最上面第一張圖中可以注意到，answer.txt 的檔案大小是 958 byte answer_test.txt 的檔案大小則為 924 byte 此時不禁讓我疑惑到，兩個檔案內容相同，空白數量也完全相同的情況下，造成檔案大小不同的原因是什麼呢？ 下圖說明了一切 原來在 answer.txt 檔案當中，他的換行是採用 CRLF 而 answer_test.txt 的換行則是一般UNIX的情形 LF # 以下感謝梁浩穎同學解釋 CRLF 就是 \r\n 是只有在 windows 系統在用的形式 所有的 UNIX 系統都是用 \n   (的確，上圖中的 answer.txt 是用windows 7系統的記事本所編輯的，answer_test.txt 則是用 mac 系統透過 sublime text 所編輯的) CR是carriage return的意思， 也就是\r  LF是line feed的意思，也就是\n 這就是為什麼有些檔案從unix系統拿去windows上面看 會變成全部都在同一行 因為只有LF 他不知道是換行 windows只吃CRLF 除了一些比較聰明的文字編輯器會 分的出來 不過其實，CRLF才是真正的打字機運作方式，你可以想像：打字機打到最右邊，要先return到最左邊之後，再往下一行， 所以是CRLF 如果想要清空 answer.txt 裡面所有的 \r 的話，可以使用以下指令 tr -d '\r’ answer.txt # 遇到這個隱形的bug真是難得的好玩經驗，分享給大家 :)
Read more »

[ML筆記] Batch Normalization

圖片
Batch Normalization 本篇為台大電機系李宏毅老師 Machine Learning and having it Deep and Structured (2017)  課程筆記 上課影片： https://www.youtube.com/watch?v=BZh1ltr5Rkg 先從 Feature scaling 或是稱作 Feature Normalization 說起 假設 x1 跟 x2 的數值差距很大 x1 值的範圍 1,2,3,4, ... x2 值的範圍 100, 200, 300, … x1 的 weight 是 w1，x2 的 weight 是 w2 w1 前面乘的值比較小，所以他對於結果的影響比較小 w2 前面乘的值比較大，所以他對於結果的影響比較大
Read more »

[筆記] 統計實習(1) SAS 基礎用法 (匯入資料並另存SAS新檔，SUBSTR，計算總和與平均，BMI)

圖片
使用版本 SAS 9.3 繁體中文 OS: windows xp 匯入資料並另存成SAS新檔 step 1: 匯入資料 step 2: 選擇匯入檔案類型 step 3: 去資料夾 Browse 找尋要匯入的資料檔案 (這裡我命名為Man) step 4: 選好檔案後按OK step 5: 然後按Next step 6: 成功匯入excel資料  (我命名為 Man)，欲找尋這個檔案在哪 -> 按左側的資料館 step 7: 再選Work資料夾 step 8: 就會看到剛剛被我命名的 Man 檔案資料 step 9: 做些簡單的資料分析，在Editor視窗中寫code             Data test;             set man;             run;             以上code做的事會把這個 man (大小寫沒差)資料表複製一份，另命名成 test 存起來 step 10: 要複製另存成SAS檔，使用libname語法 後面放 目錄名稱'檔案路徑'               這裡的目錄名稱為 J 路徑為 '   ' 之中。                      step 11: 如此的code寫完，按下小人圖示提交執行程式 step 12: 會發現資料館裡面，多了一個名稱為 J ...
Read more »

[ML筆記] Ensemble - Bagging, Boosting & Stacking

圖片
ML Lecture 27: Ensemble 本篇為台大電機系李宏毅老師 Machine Learning (2016) 課程筆記 上課影片： https://www.youtube.com/watch?v=tH9FH1DH5n0 課程網 (投影片出處)： http://speech.ee.ntu.edu.tw/~tlkagk/courses_ML16.html Ensemble 方法其實就是 “團隊合作” 好幾個模型一起上的方法 假如我們手上有一堆 Classifier ，這些 Classifier 每個都有不同的屬性， 要把他們集合起來一起合作，集合再一起需要用比較好的方法 以下來介紹一些 Ensemble 的方法 Bagging
Read more »