SQL

什么是SQL？

【百度百科】

結構化查詢語言（Structured Query Language）簡稱SQL，是一種特殊目的的編程語言，是一種資料庫查詢和程式設計語言，用于存取資料以及查詢、更新和管理關系資料庫系統，

SQL有哪幾個部分？

SQL主要包括DQL、DML、DDL、TCL、DCL、CCL六大部分，

DQL（Data Query Language）資料查詢語言

語法：SELECT
說明： 用于查詢
例子：
–普通查詢
SELECT * FROM table_name WHERE column_name = ‘xxx’;
–包含查詢(且and 、或or 、包含in、不包含not in)
SELECT * FROM table WHERE column_name1 = ‘xxx1’ and column2 = ‘xxx2’;
SELECT * FROM table WHERE column_name in(‘xxx1’,‘xxx2’);
–分組查詢(group by的欄位，在select中要包含該欄位)
SELECT column_name FROM table_name GROUP BY column_name ;
–排序查詢(默認esc升序，指定desc降序)
SELECT * from FROM table_name ORDER BY column_name desc;
–模糊查詢(模糊左邊%xx，模糊右邊xx%，模糊中間%xx%)
SELECT * FROM table_name where column_name like ‘%張%’;
–范圍查詢(between value1 and value2 或者 大于> 小于< 不等于!=(轉義寫法<>) )
SELECT * FROM table_name where column_name between 1 and 10;
–分頁查詢(limit value1,value2 從value1行(0表示第一行)開始，取value2行)
SELECT * FROM table_name limit 0,10;

DML（Data Manipulation Language ）資料操縱語言

語法：INSERT、UPDATE、DELETE
說明：分別用于插入、更新、清空操作，DML屬于顯示COMMIT提交事務，事務往后介紹；
例子：
–單條插入
INSERT INTO table_name(id,name) VALUES(1,‘Justin’);
–多條插入
INSERT INTO table_name(id,name) VALUES(1,‘Justin’),(2,‘Jack’),(3,‘Tom’);
–條件更新(條件大多可以跟前面DQL查詢的條件一樣)
UPDATE table_name SET column_name = ‘xxx2’ WHERE column_name = ‘xxx’;
–條件清空
DELETE FROM table_name WHERE column_name = ‘xxx’;

DDL（Data Definition Language）資料定義語言

語法：CREATE、ALTER、DROP、TRUNCATE
說明： 主要用于操作表，分別用于創建、修改、洗掉、清空表，TRUNCATE和DELETE都可以清空表資料，主要區別在于TRUNCATE屬于DDL，隱式COMMIT事務，無法ROLLBACK，而DELETE屬于DML，顯式COMMIT事務，只要還沒COMMIT，都可以進行ROLLBACK，
例如：
--創建表
create table test.demo_info(
id int primary key not null auto_increment,
name varchar(255),
sex char(1),
age int(3)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--修改表(洗掉/添加主鍵、添加表及欄位備注、修改欄位、添加新欄位、洗掉欄位、設定表編碼等)
alter table table_name drop primary_key; --洗掉主鍵
alter table table_name add primary key(id); --添加主鍵
alter table table_name modify COLUMN id int not null auto_increment primary key; --修改欄位為主鍵并設定主鍵自增
alter table table_name comment ‘表備注’;
alter table table_name modify COLUMN id int not null auto_incrment comment ‘主鍵欄位備注’;
alter table table_name modify COLUMN column_name varchar(255) comment ‘普通欄位備注’;
alter table table_name add COLUMN column_name varchar(255) not null ; --添加欄位
alter table table_name modify COLUMN column_name varchar(255) not null ; --修改欄位
alter table table_name drop COLUMN column_name; --洗掉欄位
alter database dabase_name character set utf8 collate utf8_bin; --修改整個庫的編碼，utf8_bin表示區分大小寫，utf8_general_ci則不區分大小寫
alter table table_name convert to character set utf8 collate utf8_bin; --修改表及所有欄位的編碼;
alter table table_name default character set utf8 collate utf8_bin; --僅修改表的編碼;
alter table table_name change column column_name column_name varchar(255) character set utf8 collate utf8_general_ci not null; --僅修改指定表下指定欄位的編碼，column_name要寫兩遍，且保留欄位非空、型別等屬性
--洗掉表
drop table_name;
--清空表
TRUNCATE table_name;

TCL（Transaction Control Language）事務控制語言

例如：BEGIN/START、SAVEPOINT、COMMIT、ROLLBACK
說明： 前面提到的顯式事務和隱式事務，DML屬于顯式事務(手動COMMIT提交)，DDL屬于隱式事務(自動COMMIT提交)；
例子：

• 開啟事務
  BEGIN TRANSACTION;
  或者START TRANSACTION;
• 保存點
  SAVEPOINT TRANSACTION SAVEPOINT_NAME;
• 提交事務
  COMMIT TRANSACTION;
• 回滾事務
  ROLLBACK TRANSACTION SAVEPOINT_NAME;
  PS：事務一旦COMMIT，無法再ROLLBACK，如果想要恢復到原來的資料，可以考慮業界內流傳的"閃回"原理實作，

DCL（Data Control Language）資料控制語言

語法：GRANT、REVOKE
說明：分別用于授權、撤權操作；
例子：
-- 授權
GRANT ALL PRIVILEGES ON . to ‘username’@’%’ identified by ‘password’; --所有權限
GRANT SELECT, INSERT, CREATE ON . TO ‘username’@’%’; – 指定權限
--撤權
REVOKE INSERT, CREATE ON . TO ‘username’@’%’; – 指定權限
--重繪
FLUSH PRIVILEGES; --GRANT/REVOKE都要重繪權限才生效
--查詢權限
select * from mysql.user where user = ‘username’ \G; --看到Y表示有權限

具體GRANT/REVOKE所有相關權限，可參照官方檔案：https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html

CCL（Cursor Control Language）指標控制語言

語法：DECLARE CURSOR、FETCH INTO、UPDATE WHERE CURRENT
說明： 用于對一個或多個表單獨行的操作，實際比較少用，感興趣的小伙伴可以查閱資料了解下，
例子：
--宣告游標
DECLARE CURSOR
--進入
FETCH INTO
--更新當前位置
UPDATE WHERE CURRENT

事務

什么是事務？

• 事務簡單說就是應用程式，比如使用C++或Java等編程語言撰寫的應用程式中，使用結構化查詢語言SQL操作資料庫的一系列操作，只不過所有的操作都比較嚴謹;
• 通過SQL的TCL事務控制語言BEGIN/SAVEPOINT/ROLLBACK/COMMIT對事務進行控制，事務具有ACID四大特性，

事務的ACID四大特性？

事務ACID四大特性， A是原子性，C是一致性，I是隔離性，D是持久性，

• 原子性（Atomicity）： 指的是事務程序中的一系列操作All success or all fail,if errror will rollback，比如銀行轉賬，轉賬成功，轉賬方賬戶-100，收款方賬戶+100，轉賬失敗則回滾事務保證雙方賬戶余額不變；
• 一致性（Consistency）： 是指事務完成后，能夠保證事務操作前、事務操作后的資料都要符合預期結果，比如轉賬前，雙方賬戶余額都是200，轉賬100成功后，轉賬方余額扣100剩余100，收款方余額增加100余額為300，不能扣了轉賬方100，收款方余額卻沒變；
• 隔離性（Isolation）： 是指并發訪問使得多個事務可能會同時對同一資料進行操作，當第一個事務對資料進行操作時，如果有其他事務也來進行操作，會被隔離，等待前一個事務完成后再操作，如果沒有隔離性，就會導致資料臟讀；
• 持久性（Durability）： 指的是對事務COMMIT即永久，也就是事務執行程序中，所有對資料庫資料做的變更，一旦事務COMMIT，將被永久生效，無法使用ROLLBACK進行回滾；

事務的隔離級別？

標準的SQL，定義了4中事務隔離級別，分別為讀提交、讀未提交、可重復讀、序列化，

• 讀未提交（READ-UNCOMMIT）： 一個事務還未提交時，它所做的變更就能被其他事務看到；

• 讀已提交（READ-COMMIT）： Oracle默認事務隔離級別，一個事務提交后，它做的變更才會被其他事務看到；

• 可重復讀（REPEATABLE-READ）： MySQL默認的事務隔離級別，一個事務在執行程序中看到的資料，總是跟這個事務在啟動時看到的資料是一致的，并且未提交變更對其他事務不可見；

• 序列化（SERIALIZABLE）： 對于同一行資料，寫會加寫鎖，讀會加讀鎖，讀寫鎖沖突時，后訪問的事務必須等待前一個事務執行完成，才能繼續執行，