一千行 MySQL 學習筆記
作者：Shocker
鏈接：https://shockerli.net/post/1000-line-mysql-note/
Windows服務
-- 啟動MySQL start
mysql
-- 創建Windows服務
sccreate
mysql binPath= mysqld_bin_path(注意：等號與值之間有空格)連接與斷開伺服器
SHOW PROCESSLIST -- 顯示哪些線程正在運行 SHOW VARIABLES -- 顯示系統變數信息mysql -h 地址 -P 埠 -u 用戶名 -p 密碼
資料庫操作
-- 查看當前資料庫 SELECT DATABASE
-- 顯示當前時間、用戶名、資料庫版本
SELECT
now
(),user
(),version
();
-- 創建庫
CREATE
DATABASE
[IF
NOT
EXISTS
] 資料庫名 資料庫選項 資料庫選項：CHARACTER
SET
charset_nameCOLLATE
collation_name
-- 查看已有庫
SHOW
DATABASES
[LIKE
"PATTERN"
]-- 查看當前庫信息
SHOW
CREATE
DATABASE
資料庫名-- 修改庫的選項信息
ALTER
DATABASE
庫名 選項信息-- 刪除庫
DROP
DATABASE
[IF
EXISTS
] 資料庫名 同時刪除該資料庫相關的目錄及其目錄內容表的操作
-- 創建表
CREATE
[TEMPORARY
]TABLE
[IF
NOT
EXISTS
] [庫名.]表名 ( 表的結構定義 )[ 表選項] 每個欄位必須有數據類型 最後一個欄位後不能有逗號TEMPORARY
臨時表，會話結束時表自動消失 對於欄位的定義： 欄位名 數據類型 [NOT
NULL
|NULL
] [
DEFAULT
default_value] [AUTO_INCREMENT] [UNIQUE
[KEY
] | [PRIMARY
]KEY
] [COMMENT"string"
]-- 表選項
-- 字符集
CHARSET
= charset_name 如果表沒有設定，則使用資料庫字符集-- 存儲引擎
ENGINE
= engine_name 表在管理數據時採用的不同的數據結構，結構不同會導致處理方式、提供的特性操作等不同 常見的引擎：InnoDB
MyISAM Memory/Heap BDBMerge
Example CSV MaxDB Archive 不同的引擎在保存表的結構和數據時採用不同的方式 MyISAM表文件含義：.frm表定義，.MYD表數據，.MYI表索引InnoDB
表文件含義：.frm表定義，表空間數據和日誌文件SHOW
ENGINES
-- 顯示存儲引擎的狀態信息
SHOW
ENGINE
引擎名 {LOGS
|
STATUS
}-- 顯示存儲引擎的日誌或狀態信息
-- 自增起始數
AUTO_INCREMENT = 行數-- 數據文件目錄
DATA
DIRECTORY ="目錄"
-- 索引文件目錄
INDEX
DIRECTORY ="目錄"
-- 表注釋
COMMENT ="string"
-- 分區選項
PARTITION
BY
... (詳細見手冊)-- 查看所有表
SHOW
TABLES
[LIKE
"pattern"
]SHOW
TABLES
FROM
表名-- 查看錶機構
SHOW
CREATE
TABLE
表名 （信息更詳細）DESC
表名 /DESCRIBE
表名 /EXPLAIN
表名 /SHOW
COLUMNS
FROM
表名 [LIKE
"PATTERN"
]SHOW
TABLE
STATUS
[FROM
db_name] [LIKE
"pattern"
]-- 修改表
-- 修改表本身的選項
ALTER
TABLE
表名 表的選項 eg:ALTER
TABLE
表名ENGINE
=MYISAM;-- 對錶進行重命名
RENAME
TABLE
原表名TO
新表名RENAME
TABLE
原表名TO
庫名.表名 （可將表移動到另一個資料庫）-- RENAME可以交換兩個表名
-- 修改表的欄位機構（13.1.2. ALTER TABLE語法）
ALTER
TABLE
表名 操作名-- 操作名
ADD
[COLUMN
] 欄位定義-- 增加欄位
AFTER
欄位名-- 表示增加在該欄位名後面
FIRST
-- 表示增加在第一個
ADD
PRIMARY
KEY
(欄位名)-- 創建主鍵
ADD
UNIQUE
[索引名] (欄位名)-- 創建唯一索引
ADD
INDEX
[索引名] (欄位名)-- 創建普通索引
DROP
[COLUMN
] 欄位名-- 刪除欄位
MODIFY[COLUMN
] 欄位名 欄位屬性-- 支持對欄位屬性進行修改，不能修改欄位名(所有原有屬性也需寫上)
CHANGE
[COLUMN
] 原欄位名 新欄位名 欄位屬性-- 支持對欄位名修改
DROP
PRIMARY
KEY
-- 刪除主鍵(刪除主鍵前需刪除其AUTO_INCREMENT屬性)
DROP
INDEX
索引名-- 刪除索引
DROP
FOREIGN
KEY
外鍵-- 刪除外鍵
-- 刪除表
DROP
TABLE
[IF
EXISTS
] 表名 ...-- 清空表數據
TRUNCATE
[TABLE
] 表名-- 複製表結構
CREATE
TABLE
表名LIKE
要複製的表名-- 複製表結構和數據
CREATE
TABLE
表名 [AS
]SELECT
*FROM
要複製的表名-- 檢查表是否有錯誤
CHECK
TABLE
tbl_name [, tbl_name] ... [option
] ...-- 優化表
OPTIMIZE
[LOCAL
|NO_WRITE_TO_BINLOG
]TABLE
tbl_name [, tbl_name] ...-- 修復表
REPAIR
[LOCAL
|NO_WRITE_TO_BINLOG
]TABLE
tbl_name [, tbl_name] ... [QUICK
] [EXTENDED
] [USE_FRM]-- 分析表
ANALYZE
[LOCAL
|NO_WRITE_TO_BINLOG
]TABLE
tbl_name [, tbl_name] ...數據操作
-- 增 INSERT INTO VALUES -- 如果要插入的值列表包含所有欄位並且順序一致，則可以省略欄位列表。 -- 可同時插入多條數據記錄！ REPLACE INSERT INSERT INTO SET -- 查 SELECT FROM -- 可來自多個表的多個欄位 -- 其他子句可以不使用 -- 欄位列表可以用*代替，表示所有欄位 -- 刪 DELETE FROM -- 改 UPDATE SET
字符集編碼
-- MySQL、資料庫、表、欄位均可設置編碼 -- 數據編碼與客戶端編碼不需一致 SHOW VARIABLES LIKE "character_set_%" -- 查看所有字符集編碼項 SET SET
SET
character_set_results = gbk;SET
character_set_connection = gbk;SET
NAMES
GBK;-- 相當於完成以上三個設置
-- 校對集
校對集用以排序SHOW
CHARACTER
SET
[LIKE
"pattern"
]/SHOW
CHARSET
[LIKE
"pattern"
] 查看所有字符集SHOW
COLLATION
[LIKE
"pattern"
] 查看所有校對集CHARSET
字符集編碼 設置字符集編碼COLLATE
校對集編碼 設置校對集編碼數據類型（列類型）
-- a. 整型 ---------- -- b. 浮點型 ---------- -- c. 定點數 ---------- -- 可變長度 -- a. char, varchar ---------- CREATE TABLE int char 30 varchar charset1. 數值類型
-- b. blob, text ----------
blob 二進位字元串（位元組字元串） tinyblob, blob, mediumblob, longblob text 非二進位字元串（字元字元串） tinytext, text, mediumtext, longtext text 在定義時，不需要定義長度，也不會計算總長度。 text 類型在定義時，不可給default值-- c. binary, varbinary ----------
類似於char和varchar，用於保存二進位字元串，也就是保存位元組字元串而非字元字元串。 char, varchar, text 對應 binary, varbinary, blob.3. 日期時間類型 一般用整型保存時間戳，因為PHP可以很方便的將時間戳進行格式化。 datetime 8位元組 日期及時間 1000-01-01 00:00:00 到 9999-12-31 23:59:59 date 3位元組 日期 1000-01-01 到 9999-12-31 timestamp 4位元組 時間戳 19700101000000 到 2038-01-19 03:14:07 time 3位元組 時間 -838:59:59 到 838:59:59 year 1位元組 年份 1901 - 2155datetime YYYY-MM-DD hh:mm:sstimestamp YY-MM-DD hh:mm:ss YYYYMMDDhhmmss YYMMDDhhmmss YYYYMMDDhhmmss YYMMDDhhmmssdate YYYY-MM-DD YY-MM-DD YYYYMMDD YYMMDD YYYYMMDD YYMMDDtime hh:mm:ss hhmmss hhmmssyear YYYY YY YYYY YY4. 枚舉和集合-- 枚舉(enum) ----------
enum(val1, val2, val3...) 在已知的值中進行單選。最大數量為65535. 枚舉值在保存時，以2個位元組的整型(smallint)保存。每個枚舉值，按保存的位置順序，從1開始逐一遞增。 表現為字元串類型，存儲卻是整型。 NULL值的索引是NULL。 空字元串錯誤值的索引值是0。-- 集合（set） ----------
set
(val1, val2, val3...)create
table
tab ( genderset
("男"
,"女"
,"無"
) );insert
into
tabvalues
("男, 女"
); 最多可以有64個不同的成員。以bigint存儲，共8個位元組。採取位運算的形式。 當創建表時，SET
成員值的尾部空格將自動被刪除。選擇類型
-- PHP角度 -- IP存儲 ----------
列屬性（列約束）
create table int varchar 10 primary key1. PRIMARY 主鍵 - 能唯一標識記錄的欄位，可以作為主鍵。 - 一個表只能有一個主鍵。 - 主鍵具有唯一性。 - 聲明欄位時，用 primary key 標識。 也可以在欄位列表之後聲明 例：
create
table
tab ( idint
, stuvarchar
(10
), ageint
,primary
key
(stu, age));2. UNIQUE 唯一索引（唯一約束） 使得某欄位的值也不能重複。3. NULL 約束 null不是數據類型，是列的一個屬性。 表示當前列是否可以為null，表示什麼都沒有。 null, 允許為空。默認。 not null, 不允許為空。insert
into
tabvalues
(null
,"val"
);-- 此時表示將第一個欄位的值設為null, 取決於該欄位是否允許為null
4. DEFAULT 默認值屬性 當前欄位的默認值。insert
into
tabvalues
(default
,"val"
);-- 此時表示強制使用默認值。
create
table
tab ( add_timetimestamp
default
current_timestamp
);-- 表示將當前時間的時間戳設為默認值。
current_date, current_time5. AUTO_INCREMENT 自動增長約束 自動增長必須為索引（主鍵或unique） 只能存在一個欄位為自動增長。 默認為1開始自動增長。可以通過表屬性 auto_increment = x進行設置，或alter
table
tbl auto_increment = x;6. COMMENT 注釋 例：create
table
tab ( idint
) comment"注釋內容"
;7. FOREIGN KEY 外鍵約束 用於限制主表與從表數據完整性。alter
table
t1add
constraint
`t1_t2_fk`
foreign
key
(t1_id)references
t2(id);-- 將表t1的t1_id外鍵關聯到表t2的id欄位。
-- 每個外鍵都有一個名字，可以通過 constraint 指定
存在外鍵的表，稱之為從表（子表），外鍵指向的表，稱之為主表（父表）。 作用：保持數據一致性，完整性，主要目的是控制存儲在外鍵表（從表）中的數據。 MySQL中，可以對InnoDB引擎使用外鍵約束： 語法： foreign key (外鍵欄位） references 主表名 (關聯欄位) [主表記錄刪除時的動作] [主表記錄更新時的動作] 此時需要檢測一個從表的外鍵需要約束為主表的已存在的值。外鍵在沒有關聯的情況下，可以設置為null.前提是該外鍵列，沒有not null。 可以不指定主表記錄更改或更新時的動作，那麼此時主表的操作被拒絕。 如果指定了 onupdate
或on
delete
：在刪除或更新時，有如下幾個操作可以選擇：1.
cascade
，級聯操作。主表數據被更新（主鍵值更新），從表也被更新（外鍵值更新）。主表記錄被刪除，從表相關記錄也被刪除。2.
set
null
，設置為null
。主表數據被更新（主鍵值更新），從表的外鍵被設置為null
。主表記錄被刪除，從表相關記錄外鍵被設置成null
。但注意，要求該外鍵列，沒有not
null
屬性約束。3.
restrict
，拒絕父表刪除和更新。 注意，外鍵只被InnoDB
存儲引擎所支持。其他引擎是不支持的。建表規範
-- Normal Format, NF -- 1NF, 第一範式 -- 2NF, 第二範式 -- 3NF, 第三範式
SELECT
SELECT [ALL|DISTINCT] select_expr FROM -> WHERE -> GROUP BY [合計函數] -> HAVING -> ORDER BY -> LIMITa. select_expr -- 可以用 * 表示所有欄位。 select * from tb; -- 可以使用表達式（計算公式、函數調用、欄位也是個表達式） select stu, 29+25, now() from tb; -- 可以為每個列使用別名。適用於簡化列標識，避免多個列標識符重複。 - 使用 as 關鍵字，也可省略 as. select stu+10 as add10 from tb;b. FROM 子句 用於標識查詢來源。 -- 可以為表起別名。使用as關鍵字。 SELECT * FROM tb1 AS tt, tb2 AS bb; -- from子句後，可以同時出現多個表。 -- 多個表會橫向疊加到一起，而數據會形成一個笛卡爾積。 SELECT * FROM tb1, tb2; -- 向優化符提示如何選擇索引 USE INDEX、IGNORE INDEX、FORCE INDEX SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3; SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;c. WHERE 子句 -- 從from獲得的數據源中進行篩選。 -- 整型1表示真，0表示假。 -- 表達式由運算符和運算數組成。 -- 運算數：變數（欄位）、值、函數返回值 -- 運算符： =, <=>, <>, !=, <=, <, >=, >, !, &&, ||, in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor is/is not 加上ture/false/unknown，檢驗某個值的真假 <=>與<>功能相同，<=>可用於null比較d. GROUP BY 子句, 分組子句 GROUP BY 欄位/別名 [排序方式] 分組後會進行排序。升序：ASC，降序：DESC 以下[合計函數]需配合 GROUP BY 使用： count 返回不同的非NULL值數目 count(*)、count(欄位) sum 求和 max 求最大值 min 求最小值 avg 求平均值 group_concat 返回帶有來自一個組的連接的非NULL值的字元串結果。組內字元串連接。e. HAVING 子句，條件子句 與 where 功能、用法相同，執行時機不同。 where 在開始時執行檢測數據，對原數據進行過濾。 having 對篩選出的結果再次進行過濾。 having 欄位必須是查詢出來的，where 欄位必須是數據表存在的。 where 不可以使用欄位的別名，having 可以。因為執行WHERE代碼時，可能尚未確定列值。 where 不可以使用合計函數。一般需用合計函數才會用 having SQL標準要求HAVING必須引用GROUP BY子句中的列或用於合計函數中的列。f. ORDER BY 子句，排序子句 order by 排序欄位/別名 排序方式 [,排序欄位/別名 排序方式]... 升序：ASC，降序：DESC 支持多個欄位的排序。g. LIMIT 子句，限制結果數量子句 僅對處理好的結果進行數量限制。將處理好的結果的看作是一個集合，按照記錄出現的順序，索引從0開始。 limit 起始位置, 獲取條數 省略第一個參數，表示從索引0開始。limit 獲取條數h. DISTINCT, ALL 選項 distinct 去除重複記錄 默認為 all, 全部記錄
UNION
select SELECT UNION ALL DISTINCT SELECT DISTINCT SELECT ORDER BY LIMIT select select select 將多個
子查詢
-- from型 select from select from where 0 as where 1 - 子查詢需用括弧包裹。
-- where型
- 子查詢返回一個值，標量子查詢。 - 不需要給子查詢取別名。 - where子查詢內的表，不能直接用以更新。select
*from
tbwhere
money = (select
max
(money)from
tb);-- 列子查詢
如果子查詢結果返回的是一列。 使用 in 或 not in 完成查詢 exists 和 not exists 條件 如果子查詢返回數據，則返回1或0。常用於判斷條件。select
column1from
t1where
exists
(select
*from
t2);-- 行子查詢
查詢條件是一個行。select
*from
t1where
(id, gender)in
(select
id, genderfrom
t2); 行構造符：(col1, col2, ...) 或 ROW(col1, col2, ...) 行構造符通常用於與對能返回兩個或兩個以上列的子查詢進行比較。-- 特殊運算符
!= all() 相當於 not in = some() 相當於 in。any 是 some 的別名 != some() 不等同於 not in，不等於其中某一個。 all, some 可以配合其他運算符一起使用。連接查詢(join)
-- 內連接(inner join) -- 交叉連接 cross join select from cross join 將多個表的欄位進行連接，可以指定連接條件。
-- 外連接(outer join)
- 如果數據不存在，也會出現在連接結果中。-- 左外連接 left join
如果數據不存在，左表記錄會出現，而右表為null填充-- 右外連接 right join
如果數據不存在，右表記錄會出現，而左表為null填充-- 自然連接(natural join)
自動判斷連接條件完成連接。 相當於省略了using，會自動查找相同欄位名。 natural join natural left join natural right joinselect
info.id, info.name, info.stu_num, extra_info.hobby, extra_info.sexfrom
info, extra_infowhere
info.stu_num = extra_info.stu_id;導出
select into outfile from
-- 導出表數據
load
data
[local
]infile
文件地址 [replace
|ignore
]into
table
表名 [控制格式];-- 導入數據
生成的數據默認的分隔符是製表符 local未指定，則數據文件必須在伺服器上replace
和ignore
關鍵詞控制對現有的唯一鍵記錄的重複的處理-- 控制格式
fields
控制欄位格式默認：fields
terminated
by
" "
enclosed
by
""
escaped
by
""
terminated
by
"string"
-- 終止
enclosed
by
"char"
-- 包裹
escaped
by
"char"
-- 轉義
-- 示例：
SELECT
a,b,a+bINTO
OUTFILE
"/tmp/result.text"
FIELDS
TERMINATED
BY
","
OPTIONALLY
ENCLOSED
BY
"""
LINES
TERMINATED
BY
"
"
FROM
test_table;lines 控制行格式默認：lines terminated by "" terminated by "string"
-- 終止
INSERT
select insert values set INSERT INTO SET field value
INSERT
INTO
tbl_nameVALUES
(), (), ();可以在列值指定時，使用表達式。INSERT
INTO
tbl_nameVALUES
(field_value,10
+10
,now
());可以使用一個特殊值 DEFAULT，表示該列使用默認值。INSERT
INTO
tbl_nameVALUES
(field_value,DEFAULT
);可以通過一個查詢的結果，作為需要插入的值。INSERT
INTO
tbl_nameSELECT
...;可以指定在插入的值出現主鍵（或唯一索引）衝突時，更新其他非主鍵列的信息。INSERT
INTO
tbl_nameVALUES
/SET
/SELECT
ON
DUPLICATEKEY
UPDATE
欄位=值, …;DELETE
DELETE FROM WHERE ORDER BY LIMIT row_count where limit order by limit delete from 1 2 using
TRUNCATE
TRUNCATE TABLE 1 truncate delete 2 truncate delete 3 truncate delete 4 truncate
備份與還原
備份，將數據的結構與表內數據保存起來。利用 mysqldump 指令完成。-- 導出mysqldump [options] db_name [tables]mysqldump [options] ---database DB1 [DB2 DB3...]mysqldump [options] --all--database1. 導出一張表 mysqldump -u用戶名 -p密碼 庫名 表名 > 文件名(D:/a.sql)2. 導出多張表 mysqldump -u用戶名 -p密碼 庫名 表1 表2 表3 > 文件名(D:/a.sql)3. 導出所有表 mysqldump -u用戶名 -p密碼 庫名 > 文件名(D:/a.sql)4. 導出一個庫 mysqldump -u用戶名 -p密碼 --lock-all-tables --database 庫名 > 文件名(D:/a.sql)可以-w攜帶WHERE條件-- 導入1. 在登錄mysql的情況下： source 備份文件2. 在不登錄的情況下 mysql -u用戶名 -p密碼 庫名 < 備份文件
視圖
-- 創建視圖 CREATE OR REPLACE MERGE VIEW AS select SELECT -- 查看結構 SHOW CREATE VIEW -- 刪除視圖 DROP VIEW IF EXISTS -- 修改視圖結構 ALTER VIEW AS -- 視圖作用 1. 2. -- 視圖演算法(ALGORITHM) MERGE什麼是視圖： 視圖是一個虛擬表，其內容由查詢定義。同真實的表一樣，視圖包含一系列帶有名稱的列和行數據。但是，視圖並不在資料庫中以存儲的數據值集形式存在。行和列數據來自由定義視圖的查詢所引用的表，並且在引用視圖時動態生成。 視圖具有表結構文件，但不存在數據文件。 對其中所引用的基礎表來說，視圖的作用類似於篩選。定義視圖的篩選可以來自當前或其它資料庫的一個或多個表，或者其它視圖。通過視圖進行查詢沒有任何限制，通過它們進行數據修改時的限制也很少。 視圖是存儲在資料庫中的查詢的sql語句，它主要出於兩種原因：安全原因，視圖可以隱藏一些數據，如：社會保險基金錶，可以用視圖只顯示姓名，地址，而不顯示社會保險號和工資數等，另一原因是可使複雜的查詢易於理解和使用。
事務(transaction)
-- 事務開啟 START TRANSACTION事務是指邏輯上的一組操作，組成這組操作的各個單元，要不全成功要不全失敗。 - 支持連續SQL的集體成功或集體撤銷。 - 事務是資料庫在數據晚自習方面的一個功能。 - 需要利用 InnoDB 或 BDB 存儲引擎，對自動提交的特性支持完成。 - InnoDB被稱為事務安全型引擎。
BEGIN
; 開啟事務後，所有被執行的SQL語句均被認作當前事務內的SQL語句。-- 事務提交
COMMIT
;-- 事務回滾
ROLLBACK
; 如果部分操作發生問題，映射到事務開啟前。-- 事務的特性
1. 原子性（Atomicity） 事務是一個不可分割的工作單位，事務中的操作要麼都發生，要麼都不發生。 2. 一致性（Consistency） 事務前後數據的完整性必須保持一致。 - 事務開始和結束時，外部數據一致 - 在整個事務過程中，操作是連續的 3. 隔離性（Isolation） 多個用戶並發訪問資料庫時，一個用戶的事務不能被其它用戶的事物所干擾，多個並發事務之間的數據要相互隔離。 4. 持久性（Durability） 一個事務一旦被提交，它對資料庫中的數據改變就是永久性的。-- 事務的實現
1. 要求是事務支持的表類型 2. 執行一組相關的操作前開啟事務 3. 整組操作完成後，都成功，則提交；如果存在失敗，選擇回滾，則會回到事務開始的備份點。-- 事務的原理
利用InnoDB的自動提交(autocommit)特性完成。 普通的MySQL執行語句後，當前的數據提交操作均可被其他客戶端可見。 而事務是暫時關閉「自動提交」機制，需要commit
提交持久化數據操作。-- 注意
1.
數據定義語言（DDL）語句不能被回滾，比如創建或取消資料庫的語句，和創建、取消或更改表或存儲的子程序的語句。2.
事務不能被嵌套-- 保存點
SAVEPOINT
保存點名稱-- 設置一個事務保存點
ROLLBACK
TO
SAVEPOINT
保存點名稱-- 回滾到保存點
RELEASE
SAVEPOINT
保存點名稱-- 刪除保存點
-- InnoDB自動提交特性設置
SET
autocommit =0
|1
; 0表示關閉自動提交，1表示開啟自動提交。 - 如果關閉了，那普通操作的結果對其他客戶端也不可見，需要commit
提交後才能持久化數據操作。 - 也可以關閉自動提交來開啟事務。但與START
TRANSACTION
不同的是，SET
autocommit是永久改變伺服器的設置，直到下次再次修改該設置。(針對當前連接) 而START
TRANSACTION
記錄開啟前的狀態，而一旦事務提交或回滾後就需要再次開啟事務。(針對當前事務)鎖表
-- 鎖定 LOCK TABLES AS -- 解鎖 UNLOCK TABLES表鎖定只用於防止其它客戶端進行不正當地讀取和寫入MyISAM 支持表鎖，InnoDB 支持行鎖
觸發器
-- 創建觸發器 CREATE TRIGGER ON FOR EACH ROW before after INSERT UPDATE DELETE TEMPORARY BEGIN END -- 刪除 DROP TRIGGER -- 注意 1. 觸發程序是與表有關的命名資料庫對象，當該表出現特定事件時，將激活該對象 監聽：記錄的增加、修改、刪除。
-- 字元連接函數 -- 分支語句 end if
-- 修改最外層語句結束符
delimiter 自定義結束符號 SQL語句自定義結束符號delimiter ;-- 修改回原來的分號
-- 語句塊包裹
begin
語句塊end
-- 特殊的執行
1.
只要添加記錄，就會觸發程序。2.
Insert
into
on
duplicatekey
update
語法會觸發： 如果沒有重複記錄，會觸發before
insert
,after
insert
; 如果有重複記錄並更新，會觸發 beforeinsert
,before
update
,after
update
; 如果有重複記錄但是沒有發生更新，則觸發 beforeinsert
,before
update
3.
Replace
語法 如果有記錄，則執行before
insert
,before
delete
,after
delete
,after
insert
SQL編程
--// 局部變數 ---------- -- 變數聲明 declare default value default default null -- 賦值 set select into --// 全局變數 ---------- -- 定義、賦值 set set var value
select
into
語句為變數初始化並賦值。這樣要求select
語句只能返回一行，但是可以是多個欄位，就意味著同時為多個變數進行賦值，變數的數量需要與查詢的列數一致。還可以把賦值語句看作一個表達式，通過select
執行完成。此時為了避免=被當作關係運算符看待，使用:=代替。（set
語句可以使用= 和 :=）。select
@var
:=20
;select
@v1:=id, @v2=namefrom
t1limit
1
;select
*from
tbl_namewhere
@var
:=30
;select
into
可以將表中查詢獲得的數據賦給變數。 -|select
max
(height)into
@max_heightfrom
tb;-- 自定義變數名
為了避免select
語句中，用戶自定義的變數與系統標識符（通常是欄位名）衝突，用戶自定義變數在變數名前使用@作為開始符號。@var
=10
; - 變數被定義後，在整個會話周期都有效（登錄到退出）--// 控制結構 ----------
-- if語句
if search_condition then statement_list [elseif search_condition then statement_list]...[else statement_list]end
if
;-- case語句
CASE value WHEN [compare-value] THEN result[WHEN [compare-value] THEN result ...][ELSE result]END
-- while循環
[begin_label:] while search_conditiondo
statement_listend
while [end_label];- 如果需要在循環內提前終止 while循環，則需要使用標籤；標籤需要成對出現。-- 退出循環
退出整個循環 leave 退出當前循環 iterate 通過退出的標籤決定退出哪個循環--// 內置函數 ----------
-- 數值函數
abs(x)-- 絕對值 abs(-10.9) = 10
format(x, d)-- 格式化千分位數值 format(1234567.456, 2) = 1,234,567.46
ceil(x)-- 向上取整 ceil(10.1) = 11
floor(x)-- 向下取整 floor (10.1) = 10
round(x)-- 四捨五入去整
mod(m, n)-- m%n m mod n 求余 10%3=1
pi()-- 獲得圓周率
pow(m, n)-- m^n
sqrt(x)-- 算術平方根
rand()-- 隨機數
truncate
(x, d)-- 截取d位小數
-- 時間日期函數
now
(),current_timestamp
();-- 當前日期時間
current_date();-- 當前日期
current_time();-- 當前時間
date("yyyy-mm-dd hh:ii:ss");-- 獲取日期部分
time("yyyy-mm-dd hh:ii:ss");-- 獲取時間部分
date_format("yyyy-mm-dd hh:ii:ss", "%d %y %a %d %m %b %j");-- 格式化時間
unix_timestamp();-- 獲得unix時間戳
from_unixtime();-- 從時間戳獲得時間
-- 字元串函數
length(string)-- string長度，位元組
char_length(string)-- string的字元個數
substring(str, position [,length])-- 從str的position開始,取length個字元
replace
(str
,search_str ,replace_str)-- 在str中用replace_str替換search_str
instr
(string
,substring
)-- 返回substring首次在string中出現的位置
concat
(string
[,...])-- 連接字串
charset
(str
)-- 返回字串字符集
lcase
(string
)-- 轉換成小寫
left
(string
, length)-- 從string2中的左邊起取length個字元
load_file
(file_name)-- 從文件讀取內容
locate
(substring
,string
[,start_position])-- 同instr,但可指定開始位置
lpad
(string
, length,pad
)-- 重複用pad加在string開頭,直到字串長度為length
ltrim
(string
)-- 去除前端空格
repeat
(string
,count
)-- 重複count次
rpad
(string
, length,pad
)--在str後用pad補充,直到長度為length
rtrim
(string
)-- 去除後端空格
strcmp
(string1 ,string2)-- 逐字元比較兩字串大小
-- 流程函數
case
when
[condition]then
result [when
[condition]then
result ...] [else
result]end
多分支if
(expr1,expr2,expr3) 雙分支。-- 聚合函數
count
()sum
();max();min();avg();group_concat()-- 其他常用函數
md5();default();--// 存儲函數，自定義函數 ----------
-- 新建
CREATE
FUNCTION
function_name (參數列表)RETURNS
返回值類型 函數體 - 函數名，應該合法的標識符，並且不應該與已有的關鍵字衝突。 - 一個函數應該屬於某個資料庫，可以使用db_name.funciton_name的形式執行當前函數所屬資料庫，否則為當前資料庫。 - 參數部分，由"參數名"
和"參數類型"
組成。多個參數用逗號隔開。 - 函數體由多條可用的mysql語句，流程式控制制，變數聲明等語句構成。 - 多條語句應該使用begin
...end
語句塊包含。 - 一定要有return
返回值語句。-- 刪除
DROP
FUNCTION
[IF
EXISTS
] function_name;-- 查看
SHOW
FUNCTION
STATUS
LIKE
"partten"
SHOW
CREATE
FUNCTION
function_name;-- 修改
ALTER
FUNCTION
function_name 函數選項--// 存儲過程，自定義功能 ----------
-- 定義
存儲存儲過程 是一段代碼（過程），存儲在資料庫中的sql
組成。一個存儲過程通常用於完成一段業務邏輯，例如報名，交班費，訂單入庫等。而一個函數通常專註與某個功能，視為其他程序服務的，需要在其他語句中調用函數才可以，而存儲過程不能被其他調用，是自己執行 通過call
執行。-- 創建
CREATE
PROCEDURE
sp_name (參數列表) 過程體參數列表：不同於函數的參數列表，需要指明參數類型IN
，表示輸入型OUT，表示輸出型INOUT，表示混合型注意，沒有返回值。/* 存儲過程 */
------------------
存儲過程是一段可執行性代碼的集合。相比函數，更偏向於業務邏輯。調用：CALL
過程名-- 注意
- 沒有返回值。- 只能單獨調用，不可夾雜在其他語句中-- 參數
IN
|OUT|INOUT 參數名 數據類型IN
輸入：在調用過程中，將數據輸入到過程體內部的參數OUT 輸出：在調用過程中，將過程體處理完的結果返回到客戶端INOUT 輸入輸出：既可輸入，也可輸出-- 語法
CREATE
PROCEDURE
過程名 (參數列表)BEGIN
過程體END
用戶和許可權管理
-- root密碼重置 --skip-grant-tables & --skip-grant-tables use
UPDATE
`user`
SET
PASSWORD
=PASSWORD
("密碼"
)WHERE
`user`
="root"
;5.FLUSH
PRIVILEGES
;用戶信息表：mysql.user-- 刷新許可權
FLUSH
PRIVILEGES
;-- 增加用戶
CREATE
USER
用戶名IDENTIFIED
BY
[PASSWORD
] 密碼(字元串) - 必須擁有mysql資料庫的全局CREATE
USER
許可權，或擁有INSERT
許可權。 - 只能創建用戶，不能賦予許可權。 - 用戶名，注意引號：如"user_name"
@"192.168.1.1"
- 密碼也需引號，純數字密碼也要加引號 - 要在純文本中指定密碼，需忽略PASSWORD
關鍵詞。要把密碼指定為由PASSWORD
()函數返回的混編值，需包含關鍵字PASSWORD
-- 重命名用戶
RENAME
USER
old_userTO
new_user-- 設置密碼
SET
PASSWORD
=PASSWORD
("密碼"
)-- 為當前用戶設置密碼
SET
PASSWORD
FOR
用戶名 =PASSWORD
("密碼"
)-- 為指定用戶設置密碼
-- 刪除用戶
DROP
USER
用戶名-- 分配許可權/添加用戶
GRANT
許可權列表ON
表名TO
用戶名 [IDENTIFIED
BY
[PASSWORD
]"password"
] -all
privileges
表示所有許可權 - *.* 表示所有庫的所有表 - 庫名.表名 表示某庫下面的某表GRANT
ALL
PRIVILEGES
ON
`pms`
.*TO
"pms"
@"%"
IDENTIFIED
BY
"pms0817"
;-- 查看許可權
SHOW
GRANTS
FOR
用戶名-- 查看當前用戶許可權
SHOW
GRANTS
; 或SHOW
GRANTS
FOR
CURRENT_USER
; 或SHOW
GRANTS
FOR
CURRENT_USER
();-- 撤消許可權
REVOKE 許可權列表 ON 表名 FROM 用戶名REVOKE ALL PRIVILEGES,GRANT
OPTION
FROM
用戶名-- 撤銷所有許可權
-- 許可權層級
-- 要使用GRANT或REVOKE，您必須擁有GRANT OPTION許可權，並且您必須用於您正在授予或撤銷的許可權。
全局層級：全局許可權適用於一個給定伺服器中的所有資料庫，mysql.user
GRANT
ALL
ON
*.*和REVOKE
ALL
ON
*.*只授予和撤銷全局許可權。資料庫層級：資料庫許可權適用於一個給定資料庫中的所有目標，mysql.db, mysql.hostGRANT
ALL
ON
db_name.*和REVOKE
ALL
ON
db_name.*只授予和撤銷資料庫許可權。表層級：表許可權適用於一個給定表中的所有列，mysql.talbes_privGRANT
ALL
ON
db_name.tbl_name和REVOKE
ALL
ON
db_name.tbl_name只授予和撤銷表許可權。列層級：列許可權適用於一個給定表中的單一列，mysql.columns_priv 當使用REVOKE
時，您必須指定與被授權列相同的列。-- 許可權列表
ALL
[PRIVILEGES
]-- 設置除GRANT OPTION之外的所有簡單許可權
ALTER
-- 允許使用ALTER TABLE
ALTER
ROUTINE-- 更改或取消已存儲的子程序
CREATE
-- 允許使用CREATE TABLE
CREATE
ROUTINE-- 創建已存儲的子程序
CREATE
TEMPORARY
TABLES
-- 允許使用CREATE TEMPORARY TABLE
CREATE
USER
-- 允許使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE
VIEW
-- 允許使用CREATE VIEW
DELETE
-- 允許使用DELETE
DROP
-- 允許使用DROP TABLE
EXECUTE
-- 允許用戶運行已存儲的子程序
FILE-- 允許使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX
-- 允許使用CREATE INDEX和DROP INDEX
INSERT
-- 允許使用INSERT
LOCK
TABLES
-- 允許對您擁有SELECT許可權的表使用LOCK TABLES
PROCESS-- 允許使用SHOW FULL PROCESSLIST
REFERENCES
-- 未被實施
RELOAD-- 允許使用FLUSH
REPLICATION CLIENT-- 允許用戶詢問從屬伺服器或主伺服器的地址
REPLICATIONSLAVE
-- 用於複製型從屬伺服器（從主伺服器中讀取二進位日誌事件）
SELECT
-- 允許使用SELECT
SHOW
DATABASES
-- 顯示所有資料庫
SHOW
VIEW
-- 允許使用SHOW CREATE VIEW
SHUTDOWN-- 允許使用mysqladmin shutdown
SUPER-- 允許使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL語句，mysqladmin debug命令；允許您連接（一次），即使已達到max_connections。
UPDATE
-- 允許使用UPDATE
USAGE
-- 「無許可權」的同義詞
GRANT
OPTION
-- 允許授予許可權
表維護
-- 分析和存儲表的關鍵字分布 ANALYZE LOCAL NO_WRITE_TO_BINLOG TABLE -- 檢查一個或多個表是否有錯誤 CHECK TABLE option option QUICK FAST MEDIUM EXTENDED CHANGED -- 整理數據文件的碎片 OPTIMIZE LOCAL NO_WRITE_TO_BINLOG TABLE
雜項
/* 注釋內容 */ -- 注釋內容 (標準SQL注釋風格，要求雙破折號後加一空格符（空格、TAB、換行等）)1. 可用反引號（`）為標識符（庫名、表名、欄位名、索引、別名）包裹，以避免與關鍵字重名！中文也可以作為標識符！2. 每個庫目錄存在一個保存當前資料庫的選項文件db.opt。3. 注釋： 單行注釋 # 注釋內容 多行注釋
●編號
405
，輸入編號直達本文
●輸入m獲取文章
目錄
推薦↓↓↓
Web開發
更多推薦
《
18個技術類微信公眾號
》
涵蓋：程序人生、演算法與數據結構、黑客技術與網路安全、大數據技術、前端開發、Java、Python、Web開發、安卓開發、iOS開發、C/C++、.NET、Linux、資料庫、運維等。
推薦閱讀：
※【學易】系統學習易經基礎知識（8）
※我們向大自然學習什麼
※邵培仁：傳播觀念斷想———天益：學習型社會領航者
※年度重點學習書籍《別拿男人不當動物
※學習自我管理血糖