Timmy's Column: MySQL新手入門超級手冊學習筆記

　　銜接上一篇網誌文章所述，在對的時間離職，在對的時間參加BDSE15課程，最終如願提升資訊技術實力，又認識許多新朋友，在看似完好的BDSE15課程體驗之中，仍發現不足之處，最明顯的就是資料庫知識技術，這一系列的課程無論師資、教材等，皆未達基礎水準，因此趕緊閱讀這本由張益裕編著之《MySQL新手入門超級手冊》，希望能彌補課程之不足。

　　其實大約兩年前，我曾經在Codedata閱讀本書作者張益裕這一系列共二十篇的MySQL文章，可能是作者專欄技術文章寫得太好，加上當初自己程度太弱，工作上也不常使用SQL，無法再濃縮整理出學習筆記，因此兩年後的現在，我居然已經快要忘光這些SQL的語法；透過這次機會，以上一篇網誌文章當作基礎，再補充整理出以下應該要學會的技術重點，希望把SQL徹底學好。

補充BDSE15課程不足之處

第一章－資料庫概論與MySQL

l 本書範例程式：
http://books.gotop.com.tw/download/AED002931

第二章－基礎查詢

l 查詢敘述順序：
SELECT想要查詢的欄位
FROM想要查詢的表格
WHERE查詢條件
GROUP BY分組設定
HAVING分組條件
ORDER BY排序設定
LIMIT限制設定

第三章－運算式與函式

l 連接字串：
SET sql_mode = 'PIPES_AS_CONCAT';
SELECT 'Hello!' || 'MySQL!';

l 函式名稱和左括號之間加入空格也不會出錯：
SET sql_mode = 'IGNORE_SPACE';

l 使用GROUP BY子句的時候可以搭配WITH ROLLUP，計算全部紀錄的數量：
SELECT Continent, COUNT(*) Amount
FROM country
GROUP BY Continent WITH ROLLUP;

l 沒有使用群組函式的欄位，一定要全部出現在GROUP BY子句中：
SET sql_mode = 'ONLY_FULL_GROUP_BY';
-- 下例將出錯
SELECT Continent, Region, COUNT(*) Amount
FROM country
GROUP BY Continent;

第四章－結合與合併查詢

l 如果兩個表格結合的欄位名稱是一樣的話，就可以使用USING：
# 寫法一
SELECT empno, ename, dname
FROM cmdev.emp e, cmdev.dept d
WHERE e.deptno = d.deptno;
# 寫法二
SELECT empno, ename, dname
FROM cmdev.emp e INNER JOIN cmdev.dept d
ON e.deptno = d.deptno;
# 寫法三
SELECT empno, ename, dname
FROM cmdev.emp e INNER JOIN cmdev.dept d
USING (deptno);

l 使用UNION把多個查詢的結果合成一個查詢：
SELECT Region, Name, Population
FROM country
WHERE Region = 'Southeast Asia' AND Population < 2000000
UNION
SELECT Region, Name, Population
FROM country
WHERE Region = 'Eastern Asia' AND Population < 1000000;

第五章－資料維護

l 新增敘述：
# 寫法一
INSERT INTO cmdev.dept (deptno, dname)
VALUES (90, 'SHIPPING');
# 寫法二
INSERT INTO cmdev.dept
SET deptno = 90, dname = 'SHIPPING';

l 在INSERT後面使用IGNORE，如果新增的紀錄違反主索引鍵規則的話，就不會新增紀錄也不會有錯誤：
INSERT IGNORE INTO cmdev.dept
VALUES (50, 'MIS', DEFAULT);

l ON DUPLICATE KEY UPDATE可以用來指定在違反重複索引值的規定時要執行的修改工作，此例第一欄與第二欄為主索引鍵，第三欄為counter：
INSERT INTO cmdev.travel
VALUES (7900, 'BOSTON', 1)
ON DUPLICATE KEY UPDATE counter = counter + 1;

l REPLACE敘述在沒有違反索引值的規定時，效果跟INSERT敘述完全一樣，同樣會新增紀錄到表格中，但如果新增的紀錄違反主索引鍵規則的話，INSERT敘述會發生錯誤，REPLACE會執行修改紀錄的動作：
REPLACE INTO cmdev.dept
VALUES (50, 'MIS', DEFAULT);

l 要執行刪除表格中所有的紀錄：
# 寫法一
DELETE FROM cmdev.emp;
# 寫法二(效率好一些)
TRUNCATE TABLE cmdev.emp;

第六章－字元集與資料庫

l 查詢支援的字元集資訊：
SHOW CHARACTER SET;

l 查詢支援的Collation資訊：
SHOW COLLATION;

l 設定為自己指定的字元集與Collation：
CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER DATABASE mydb CHARACTER SET utf8 COLLATE utf8_unicode_ci;

第八章－表格

l 使用其它表格建立一個新表格：
CREATE TABLE cityoftaiwan
SELECT Name, Population
FROM world.city
WHERE CountryCode = 'TWN';

l 建立暫存表格：
CREATE TEMPORARY TABLE IF NOT EXISTS cityoftaiwan LIKE world.city;

l 增加欄位：
ALTER TABLE mytable ADD newcolumn int FIRST;
ALTER TABLE mytable ADD newcolumn int AFTER two;

l 修改欄位：
# CHANGE可以修改欄位的名稱與定義
ALTER TABLE mytable
CHANGE one changecolumn BIGINT AFTER two;
# MODIFY只能修改欄位的定義
ALTER TABLE mytable
MODIFY one BIGINT AFTER three;

l 刪除欄位：
ALTER TABLE mytable DROP two;

l 修改表格名稱：
# 寫法一
ALTER TABLE mytable RENAME mynewtable;
# 寫法二
RENAME TABLE mytable TO mynewtable;

第九章－索引

l 建立索引：
# 寫法一
CREATE TABLE addressbook (
id INT UNSIGNED PRIMARY KEY,
name VARCHAR(20),
tel VARCHAR(20),
email VARCHAR(36) UNIQUE KEY
);
# 寫法二
CREATE TABLE addressbook (
id INT UNSIGNED,
name VARCHAR(20),
tel VARCHAR(20),
email VARCHAR(36),
PRIMARY KEY (id),
UNIQUE KEY (email),
INDEX (name, tel)
);

l 流水號欄位：
# 寫法一
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE;
# 寫法二
id SERIAL;

第十一章－Views

l View元件用來保存一個設定好的查詢敘述：
CREATE OR REPLACE VIEW CountryView (Continent, Region, Code, Code2, Name) AS
SELECT Continent, Region, Code, Code2, Name
FROM country
ORDER BY Continent, Region, Code;

l View元件的規定與限制：
View不可以跟Triggers建立連結。
FROM子句中不可以使用子查詢。
不可以使用TEMPORARY表格。
不可以使用自行定義的變數、Procedure與Prepared statement參數。
在使用查詢敘述提供View元件的欄位時，也要注意欄位名稱重複的問題。
要使用View元件執行新增、修改或刪除的工作，不可以包含計算或函式的欄位，只允許一對一的結合查詢，ALGORITHM亦不可以設定為TEMPTABLE。

l 執行新增或修改的時候，又可能會產生一些有問題的資料，可以為View元件加入WITH CHECK OPTION的設定：
CREATE OR REPLACE VIEW cmdev.EmpDept30View AS
SELECT empno, ename, job, manager, hiredate, salary, comm
FROM cmdev.emp
WHERE deptno = 30
-- 選項有CASCADE或LOCAL
WITH CASCADE CHECK OPTION;

l View元件的演算法預設為UNDEFINED：
# MERGE最後是使用整合以後的敘述
CREATE ALGORITHM = MERGE VIEW cmdev.EmpSalaryView AS
SELECT * FROM cmdev.emp WHERE salary >= 1500;
# TEMPTABLE先執行View元件儲存的查詢敘述，將結果儲存為一個暫時的表格
CREATE ALGORITHM = TEMPTABLE VIEW cmdev.EmpSalaryView AS
SELECT * FROM cmdev.emp WHERE salary >= 1500;

第十二章－Prepared Statements

l Prepared Statements的規定與限制：
LIMIT子句指定的數字不可以使用變數。
用戶端離線以後，所設定的使用者變數與Prepared Statements都會被清除。
建立Prepared Statements時，不可以指定它是屬於哪一個資料庫。

l 步驟一，設定使用者變數：
# 寫法一，使用=或:=都可以
SET @my_code = 'USA';
# 寫法二，只能使用:=
SELECT @my_code := 'JPN';

l 步驟二，請伺服器準備好這個敘述：
PREPARE my_country FROM
'SELECT Code, Name, GNP FROM country WHERE Code = ?';

l 步驟三，執行這個已經在伺服器準備好的敘述：
EXECUTE my_country USING @my_code;

l 刪除指定的Prepared Statement：
# 使用DROP亦可
DEALLOCATE PREPARE my_country;

第十三章－Stored Routines - Stored Procedures

l MySQL資料庫管理系統把Stored Procedures與Stored Functions合稱為Stored Routines，SQL Script是一個包含許多SQL敘述的檔案，可以把想要執行的SQL敘述都集中在一個檔案。

l 範例一，建立無參數的Stored Procedures元件：
DELIMITER $$
CREATE PROCEDURE my_world_count ()
BEGIN
SELECT COUNT(*) countrycount FROM country;
SELECT COUNT(*) languagecount FROM countrylanguage;
SELECT COUNT(*) citycount FROM city;
END $$
DELIMITER ;

l 範例一，呼叫無參數的Stored Procedures元件：
CALL my_world_count();

l 範例一，刪除Stored Procedures元件：
DROP PROCEDURE IF EXISTS my_world_count;

l 範例二，建立有參數的Stored Procedures元件：
-- IN：輸入、input用的參數
-- OUT：輸出、output用的參數
-- INOUT：輸入與輸出、input與output用的參數
CREATE PROCEDURE test_param (IN pi_in INT, OUT po_out INT, INOUT pio_inout INT)
BEGIN
SELECT pi_in, po_out, pio_inout;
SET pi_in = 99, po_out = 99, pio_inout = 99;
END

l 範例二，呼叫有參數的Stored Procedures元件：
-- OUT與INOUT參數必須指定變數名稱，這是因為OUT與INOUT參數在執行完成後會回傳資料，才可以接收
CALL test_param(1, @my_out, @my_inout);
SELECT @my_out, @my_inout;

l 範例三，建立有參數的Stored Procedures元件：
CREATE PROCEDURE country_count2 (IN pi_con VARCHAR(26), OUT po_count INT)
BEGIN
SELECT COUNT(*) INTO po_count
FROM country
WHERE Continent = pi_con;
END

l 範例三，呼叫有參數的Stored Procedures元件：
CALL country_count2('Asia', @my_count);
SELECT @my_count;

第十三章－Stored Routines - Stored Functions

l 範例一，建立無參數的Stored Functions元件：
-- RETURNS回傳型態，RETURN回傳值
DELIMITER $$
CREATE FUNCTION my_date2 ()
RETURNS VARCHAR(64)
BEGIN
DECLARE d, t, w VARCHAR(24);
SET d = DATE_FORMAT(CURDATE(), '%Y/%m/%d');
SET t = TIME_FORMAT(CURDATE(), '%H/%i/%s');
SET w = DAYNAME(CURDATE());
RETURN CONCAT(d, ' ', t, ' ', w);
END $$
DELIMITER ;

l 範例一，呼叫無參數的Stored Functions元件：
SELECT my_date2();

l 範例一，刪除Stored Functions元件：
DROP FUNCTION IF EXISTS my_date2;

l 範例二，建立有參數的Stored Functions元件：
CREATE FUNCTION my_summary (p_num INT, p_num2 INT)
RETURNS INT
BEGIN
RETURN p_num + p_num2;
END;

l 範例二，呼叫有參數的Stored Functions元件：
SELECT my_summary(3, 5);

第十四章－Stored Routines的變數與流程

l 判斷IF：
CREATE PROCEDURE test_weight3 (IN p_weight INT)
BEGIN
IF p_weight > 100 THEN SELECT 'You are heavy!';
ELSEIF p_weight < 100 THEN SELECT 'Good!';
ELSE SELECT 'Standard!';
END IF;
END;

l 判斷CASE：
CREATE FUNCTION get_season (p_num INT)
RETURNS VARCHAR(7)
BEGIN
DECLARE season VARCHAR(7);
CASE p_num
WHEN 1 THEN SET season = 'Spring';
WHEN 2 THEN SET season = 'Summer';
WHEN 3 THEN SET season = 'Autumn';
WHEN 4 THEN SET season = 'Winter';
END CASE;
RETURN season;
END;

l   迴圈WHILE：
CREATE FUNCTION summary_while (p_num INT)
RETURNS INT
BEGIN
DECLARE v_count INT DEFAULT 1;
DECLARE v_total INT DEFAULT 0;
WHILE v_count <= p_num DO
    SET v_total = v_total + v_count;
    SET v_count = v_count + 1;
END WHILE;
RETURN v_total;
END;

l   迴圈REPEAT：
CREATE FUNCTION summary_repeat (p_num INT)
RETURNS INT
BEGIN
DECLARE v_count INT DEFAULT 1;
DECLARE v_total INT DEFAULT 0;
REPEAT
    SET v_total = v_total + v_count;
    SET v_count = v_count + 1;
    UNTIL v_count > p_num
END REPEAT;
RETURN v_total;
END;

l   迴圈LOOP：
CREATE FUNCTION summary_loop (p_num INT)
RETURNS INT
BEGIN
DECLARE v_count INT DEFAULT 1;
DECLARE v_total INT DEFAULT 0;
my_label: LOOP
    SET v_total = v_total + v_count;
    SET v_count = v_count + 1;
    IF
      v_count > p_num THEN LEAVE my_label;
    END IF;
END LOOP my_label;
RETURN v_total;
END;

l   ITERATE可以回到指定標籤範圍的開始位置，不可以使用在BEGIN-END區塊中：
CREATE FUNCTION summary_iterate (p_num INT)
RETURNS INT
BEGIN
DECLARE v_count INT DEFAULT 0;
DECLARE v_total INT DEFAULT 0;
my_label: WHILE v_count <= p_num DO
    SET v_count = v_count + 1;
    IF
      v_count % 2 = 0 THEN ITERATE my_label;
    END IF;
    SET v_total = v_total + v_count;
END WHILE my_label;
RETURN v_total;
END;

第十五章－Stored Routines進階

l 區塊中宣告的位置：
BEGIN
DECLARE 變數名稱[,...] 變數型態 [DEFAULT值]
DECLARE ... CONDITION FOR ...
DECLARE CURSOR名稱 CURSOR FOR 查詢敘述
DECLARE ... HANDLER FOR ...
...
END;

l HANDLER用來處理Stored Routines中可能會發生的錯誤：
CREATE PROCEDURE cmdev.test_handler2 (
p_deptno INT, p_dname VARCHAR(16), p_location VARCHAR(16)
)
BEGIN
-- EXIT是離開HANDLER所屬區塊，CONTINUE則為繼續往後執行
DECLARE EXIT HANDLER FOR SQLSTATE '23000'
BEGIN
SELECT 'ERROR!';
END;
INSERT INTO cmdev.dept VALUES (p_deptno, p_dname, p_location);
SELECT 'Success!';
END;

l   CONDITION用來代表某一種問題：
CREATE PROCEDURE cmdev.test_handler5 (
p_deptno INT, p_dname VARCHAR(16), p_location VARCHAR(16)
)
BEGIN
DECLARE v_message VARCHAR(64) DEFAULT 'Success!';
DECLARE c_null CONDITION FOR 1048;
DECLARE c_dupkey CONDITION FOR 1062;
DECLARE CONTINUE HANDLER FOR c_null
BEGIN
    SET v_message = 'Cannot be null!';
END;
DECLARE CONTINUE HANDLER FOR c_dupkey
BEGIN
    SET v_message = 'Duplicate key!';
END;
INSERT INTO cmdev.dept VALUES (p_deptno, p_dname, p_location);
INSERT INTO deptlog (message) VALUES (v_message);
END;

l   CURSOR用來代表一個查詢的結果，然後使用CURSOR依序處理所有紀錄資料：
CREATE PROCEDURE cmdev.test_cursor ()
BEGIN
DECLARE v_dname VARCHAR(16);
DECLARE cur_dept CURSOR FOR
    SELECT dname FROM cmdev.dept;
OPEN cur_dept;
BEGIN
    DECLARE EXIT HANDLER FOR 1329 BEGIN END;
    LOOP
      FETCH cur_dept INTO v_dname;
    END LOOP;
END;
CLOSE cur_dept;
END;

第十六章－Triggers

l Triggers可以把一些在特定狀況要執行的敘述儲存起來，每一個表格最多可以建立六種Trigger，每一種只能有一個：
BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、
AFTER INSERT、AFTER UPDATE、AFTER DELETE

l Triggers的規定與限制：
TEMPORARY表格與VIEW不可以建立Trigger。
不可以使用SELECT敘述。
不可以使用CALL敘述。
不可以使用與交易(transactions)相關的敘述，包含START TRANSACTION、COMMIT與ROLLBACK。

l 建立Triggers：
DELIMITER $$
DROP TRIGGER IF EXISTS cmdev.emp_before_update$$
CREATE TRIGGER cmdev.emp_before_update BEFORE UPDATE
ON cmdev.emp FOR EACH ROW
BEGIN
DECLARE v_message VARCHAR(64) DEFAULT 'Before Update: ';
-- OLD關鍵字取得修改或刪除前的欄位值，NEW相反
SET v_message = CONCAT(v_message, OLD.deptno, ' -> ', NEW.deptno);
INSERT INTO emplog (message) VALUES (v_message);
END $$
DELIMITER ;

第十八章－錯誤處理與查詢

l 設定為Non-Strict模式資料庫會試著處理錯誤的資料：
SET SESSION sql_mode = '';

l 設定為Strict模式資料庫不會試著處理錯誤的資料：
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
SET GLOBAL sql_mode = 'STRICT_ALL_TABLES';

第十九章－匯入與匯出資料

l 使用SQL敘述匯出資料：
-- FIELDS ESCAPED BY '$'用$設為跳脫字元
-- LINE STARTING BY '>>>'用>>>設為每行開頭
SELECT * INTO OUTFILE 'C:/Masoloa/data/out/deptcsv.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r'
FROM cmdev.dept;

l 使用SQL敘述匯入資料：
-- 沒有使用IGNORE或REPLACE，索引值重複時，將產生錯誤且馬上停止
-- IGNORE指定要跳過的資料筆數
-- (指定儲存的欄位)
-- SET將處理後的資料儲存到欄位中
LOAD DATA LOCAL INFILE 'C:/Masoloa/data/in/newdeptcsv.txt'
IGNORE
INTO TABLE cmdev.dept
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r'
IGNORE 1 LINES
(deptno, @v_dname)
SET dname = UPPER(@v_dname);

Timmy's Column

2021年3月11日星期四

MySQL新手入門超級手冊學習筆記

沒有留言:

張貼留言

2021年3月11日 星期四

MySQL新手入門超級手冊學習筆記

沒有留言:

張貼留言

2021年3月11日星期四