從MySQL到ORM(二)：MySQL基礎

一、基本概念

　　1.資料庫：

　　資料庫(DataBase)就是一個存儲數據的倉庫，為了方便數據的存儲和管理，它將數據按照特定的規律存儲在磁碟上。通過資料庫管理系統，可以有效的組織和管理存儲在資料庫中的數據。資料庫是數據管理軟體。數據存儲分為三個階段：人工管理階段、文件系統階段和資料庫系統階段。

　　2.資料庫範式：

　　資料庫範式即資料庫應該遵循的規則。目前關係資料庫最常用的四種範式分別是：第一範式(1NF)、第二範式(2NF)、第三範式(3NF)、BCN範式(BCNF)。

　　第一範式：無重覆的域。即資料庫表的每一列都是不可分割的原子數據項，而不是集合、數組、記錄等非原子數據項。

　　第二範式：資料庫表中的所有列都必須依賴於主鍵，這意味著一個表只描述一件事情。

　　第三範式：表中的每一列只與主鍵直接相關而不是間接相關。

　　BCN範式：Boyce-Codd，不允許出現有主鍵的一部分被主鍵另一部分或者其他部分決定。即一個表中只能有一個主鍵。

　　舉例(摘自其他博客)說明BCN：

　　假設倉庫管理關係表為StorehouseManage(倉庫ID, 存儲物品ID, 管理員ID, 數量)，且有一個管理員只在一個倉庫工作；一個倉庫可以存儲多種物品。這個資料庫表中存在如下決定關係：

　　　　(倉庫ID, 存儲物品ID) →(管理員ID, 數量)

　　　　(管理員ID, 存儲物品ID) → (倉庫ID, 數量)

　　所以，(倉庫ID, 存儲物品ID)和(管理員ID, 存儲物品ID)都是StorehouseManage的候選關鍵字，表中的唯一非關鍵欄位為數量，它是符合第三範式的。但是，由於存在如下決定關係：

　　　　(倉庫ID) → (管理員ID)

　　　　(管理員ID) → (倉庫ID)

　　即存在關鍵欄位決定關鍵欄位的情況，所以其不符合BCNF範式。

　　3.資料庫系統和資料庫管理系統：

　　資料庫系統由資料庫、資料庫管理系統、應用開發工具構成。

　　資料庫管理系統(DataBase Management System， DBMS)是用來定義、管理和維護數據的軟體。它是資料庫系統的重要組成部分。資料庫管理系統通過SQL語言來管理資料庫中的數據。

　　4.SQL語言：

　　SQL(Structured Query Language)語言的全稱是結構化查詢語言。它包括：

　　　　- 資料庫定義語言(Data Definition Language, DDL)

　　　　- 數據操作語言(Data Manipulation Language, DML)

　　　　- 數據控制語言(Data Control Language, DCL)

　　5.MySQL資料庫版本和優勢：

　　常見資料庫

　　商業資料庫：甲骨文的Oracle、IBM的DB2、微軟的Access和SQL Server。開源資料庫：PostgreSQL、MySQL。

　　版本分類

　　根據操作系統：Windows版，UNIX版，Linux版，MacOS版；根據開發情況：Alpha、Beta、Gamma與Available(GA)。

　　alpha 暗示這是一個以展示新特性為目的的版本，存在比較多的不穩定因素，還會向代碼中添加新新特性

　　beta 以後的beta版、發佈版或產品發佈中，所有API、外部可視結構和SQL命令列均不再更改,不再向代碼中添加影響代碼穩定性的新特性。Gamma比Beta版更高級。

　　GA如果沒有尾碼,則暗示這是一個大多數情況下可用版本或者是產品版本。. GA releases則是穩定版。

　　優勢：MySQL開放源碼、跨平臺性、價格優勢、功能強大且使用方便。

　　Linux安裝MySQL

　　https://www.cnblogs.com/kuaizifeng/p/8884842.html

二、MySQL數據類型（摘自菜鳥教程:http://www.runoob.com/mysql/mysql-data-types.html）

　　MySQL支持多種類型，大致可以分為三類：數值、日期/時間和字元串(字元)類型。

　　1.整數類型的存儲和範圍

　　2.日期和時間類型

　　3.字元串類型

三、MySQL表操作

　　1.插入數據 INSERT INTO

-- 插入一條新的數據
/* INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES('10006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL); */
-- 按表列欄位的順序插入數據時，列欄位可省略
INSERT INTO customers VALUES('10006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL);
-- 將一張表插入到原來的表
/* 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 cust_new WHERE cust_id NOT IN (SELECT cust_id FROM customers); */
-- 複製表
CREATE TABLE custcopy AS SELECT * FROM customers;

　　註意：

　　- 任何SELECT選項和子句都可以使用，包括WHERE和GROUP BY。

　　- 可以聯結多個表執行插入數據操作。

　　- 不管從多少個表中檢索數據，數據都只能插入到單個表中。

　　2.更新和刪除數據 UPDARE DEL

　　UPDATE可以：更新表中特定行，更新表中所有行。在沒有where子句時，UPDATE會更新所有記錄。

-- UPDATE總以要更新的表的名字開始,以SET指明一個或多個要更新的欄位，以WHERE指定要更新的記錄
UPDATE customers SET cust_email = '[email protected]' WHERE cust_id = '10005';
UPDATE customers SET cust_contact = 'Sam Roberts', cust_email = '[email protected]' WHERE cust_id = '10006';
-- 設置NULL來刪除某個列的值
UPDATE customers SET cust_email = NULL WHERE cust_id = '10005';

-- DELETE是刪除列
DELETE FROM customers WHERE cust_id = '10006';

　　DELETE用於按行刪除記錄，它本身不會修改表結構。

　　註：在對UPDATE或DELETE語句使用WHERE子句前，應該先用SELECT進行測試，以保證它過濾的是確實要刪除的記錄。

 　　3.創建表及約束條件

　　1.創建表

-- 創建表語法：CREATE TABLE [IF NOT EXISTS] table_name(column_name data_type,...);
CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), salary FLOAT(8,2) UNSIGNED);
-- 查看表結構
DESC users;
SHOW COLUMNS FROM users;
SHOW CREATE TABLE users;

　　2.約束條件

　　約束是為了保證數據的完整性和一致性，約束類型包括:

　　4.修改表結構

　　1.添加和刪除列

-- 修改數據表
-- 添加列語法: ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]
ALTER TABLE users ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10 AFTER name;
-- 刪除列語法: ALTER TABLE tbl_name DROP [COLUMN] col_name
ALTER TABLE users DROP age;
-- 混合操作
ALTER TABLE users ADD gender VARCHAR(10) NOT NULL DEFAULT "male", ADD address VARCHAR(32) NOT NULL,DROP salary;

　　2.修改列定義和列名

-- 修改列名，要重新聲明列的數據類型和約束條件
ALTER TABLE users CHANGE name username varchar(10) NOT NULL;
-- 修改列定義
ALTER TABLE users MODIFY id SMALLINT NOT NULL FIRST;
-- 修改表名
ALTER TABLE users RENAME TO tb;

四、MySQL查詢操作

　　1、查詢數據

　　1.查詢關鍵字 SELECT FROM

-- 查詢單列
SELECT prod_name FROM products;
-- 查詢多列
SELECT prod_id, prod_name, prod_price FROM products;
-- 查詢所有列
SELECT * FROM products;

　　2.排序關鍵字 ORDER BY

　　查詢的數據如果不排序，一般是以它在底層表中出現的順序顯示。如果不明確規定排序順序，則不應該假定檢索出的數據的順序有意義。

-- 單排
SELECT prod_name FROM products ORDER BY prod_name;
-- 多排
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;
-- 按列位置排
SELECT prod_id, prod_price, prod_name FROM products ORDER BY 2, 3; 註，它只能根據已選擇欄位的相對位置排序
-- 指定排序方向
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;

　　3.過濾關鍵字 where + 操作符

　　where子句操作符表：

　　用例:

-- 匹配查詢
SELECT prod_price, prod_name FROM products where prod_price = 3.49;
-- 不匹配查詢
SELECT prod_price, prod_name FROM products where prod_price <> 10;
-- 範圍查詢
SELECT prod_price, prod_name FROM products where prod_price BETWEEN 5 AND 10;　
-- 空值查詢
SELECT prod_name FROM products where prod_price IS NULL;
-- 組合查詢AND
SELECT prod_id, prod_name, prod_price FROM products WHERE vend_id='DLL01' AND prod_price <= 4;
-- 組合查詢OR
SELECT prod_name, prod_price FROM products WHERE vend_id='DLL01' OR vend_id = 'BRS01';
-- 組合查詢AND和OR
SELECT prod_name, prod_price FROM products WHERE vend_id='DLL01' OR vend_id = 'BRS01' AND prod_price <= 4;
-- 組合查詢IN
SELECT prod_name, prod_price FROM products WHERE vend_id IN ('DLL01', 'BRS01') ORDER BY prod_name;
-- 組合查詢NOT
SELECT prod_name, prod_price FROM products WHERE vend_id NOT vend_id='DLL01';
-- 通配符過濾
SELECT prod_name FROM products WHERE prod_name LIKE '%TNT%';

 　　2、數據處理函數

　　1.文本處理函數

 　　- 用例: 

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

　　2.日期和時間處理函數

　　註：日期和時間函數根據https://blog.csdn.net/qinshijangshan/article/details/72874667整理

 　　用例：

-- 獲取當前日期和時間，日期指的是年月日，時間指的是時分秒
SELECT NOW(), SYSDATE(), CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP;
-- 分別獲取當前日期和時間
SELECT CURDATE(), CURRENT_DATE, CURTIME(), CURRENT_TIME;
-- 分別獲取日期時間、年、季度、月、周、日、時、分、秒
SELECT DATE(SYSDATE()), YEAR(SYSDATE()), QUARTER(SYSDATE()), MONTH(SYSDATE()), WEEK(SYSDATE()), DAY(SYSDATE()), HOUR(SYSDATE()), MINUTE(SYSDATE()), SECOND(SYSDATE()), MICROSECOND(SYSDATE());
-- 獲取指定索引
SELECT WEEKOFYEAR(SYSDATE()), DAYOFYEAR(SYSDATE()), DAYOFMONTH(SYSDATE()), DAYOFWEEK(SYSDATE()), LAST_DAY(SYSDATE());
-- 獲取月和周的英文名稱
SELECT MONTHNAME(SYSDATE()), DAYNAME(SYSDATE());

-- DATE加，第一個參數是指定的日期和時間，第二個參數是間隔和單位
SELECT DATE_ADD(now(), INTERVAL 1 YEAR), DATE_ADD(now(), INTERVAL 2 MONTH), DATE_ADD(now(), INTERVAL 1000 SECOND);
-- DATE減，與DATE加參數相同
SELECT DATE_SUB(now(), INTERVAL 1 YEAR), DATE_SUB(now(), INTERVAL 2 MONTH), DATE_SUB(now(), INTERVAL 1000 SECOND);
-- 日期的加減運算
SELECT PERIOD_ADD(201808, 2), PERIOD_ADD(1808, 2),PERIOD_ADD(DATE_FORMAT(SYSDATE(), '%Y%m'), 2), PERIOD_DIFF(201808, 201004), PERIOD_DIFF(1808, 1004); 
-- 時間差計算
SELECT TIMEDIFF('2018-08-06', '2018-08-5');-- 不支持日期
SELECT TIMEDIFF('19:00:00', '17:00:00'), TIMEDIFF('2018-08-6 9:30:30', '2018-08-5 17:00:00');
-- 更便捷的日期/時間差值計算，第一個參數是要計算的欄位，其值為第三個日期時間減去第二個日期時間
SELECT TIMESTAMPDIFF(DAY, '2018-08-5 17:00:00', '2018-08-8 9:30:30'), TIMESTAMPDIFF(DAY, '2018-08-5', '2018-08-8');
SELECT TIMESTAMPDIFF(SECOND, '17:00:00', '19:30:30');-- 不支持單獨時間計算
-- 日期和天數的相互轉換
SELECT TO_DAYS(SYSDATE()), TO_DAYS('2018-8-8'), FROM_DAYS(737279);
-- 時間和秒數的相互轉換
SELECT TIME_TO_SEC(SYSDATE()), TIME_TO_SEC('12:00:00'), SEC_TO_TIME(43200);
-- 字元串格式化；字元串格式化成日期只能要按照字元串的寫法改寫成標準日期時間字元串
SELECT STR_TO_DATE('2018.08.6 9:30:30', '%Y.%m.%d %H:%i:%s');
-- 日期時間字元串可以隨便更改或獲取欄位
SELECT DATE_FORMAT('2018-08-06 09:30:30', '%Y%m');-- 獲取年月的組合字元串
SELECT DATE_FORMAT('2018-08-06 09:30:30', '%H%i%s');-- 獲取時分秒的組合字元串
SELECT DATE_FORMAT(SYSDATE(), '%Y年%m月%d日 %H時哈哈%i分嘿嘿%d秒呵呵');-- 重新格式化
-- 時間格式化只能格式化時間
SELECT TIME_FORMAT('2018-08-06 09:30:30', '%Y年%m月%d日 %H時%i分%d秒');
-- 只對'09:30:30'進行格式化，日期全部為00
SELECT TIME_FORMAT('09:30:30', '%H時%i分%d秒');
-- MAKEDATE根據數字組合成日期(以天數換算)，MAKETIME根據數字組合成時間
SELECT MAKEDATE(2018, 9);-- 結果是'2018-01-09'而不是'2018-09-01'
SELECT MAKEDATE(2018, 220);-- 結果是'2018-08-08'
SELECT MAKETIME(19,30,30);-- 與日期相反，支持三個參數拼接而不支持兩個參數換算
-- 日期時間和unix時間的相互轉換
SELECT UNIX_TIMESTAMP(), FROM_UNIXTIME(UNIX_TIMESTAMP());

　　3.數值處理函數

　　4.聚合函數

　　用例:

-- AVG
SELECT AVG(prod_price) AS avg_price FROM products;
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id=1003;
SELECT AVG(DISTINCT prod_price) AS avg_price