簡介： 1000行 MySQL 學(xué)習(xí)筆記，不怕你不會(huì)，就怕你不學(xué)！

作者：格物

文章來源：微信公眾號(hào) 機(jī)器學(xué)習(xí)算法與Python學(xué)習(xí)

（因?yàn)閮?nèi)容較多，進(jìn)行了文章刪減，閱讀全文可以通過點(diǎn)擊底部的“了解更多”進(jìn)行瀏覽）

Windows服務(wù)

連接與斷開服務(wù)器

mysql -h 地址 -P 端口 -u 用戶名 -p 密碼SHOW PROCESSLIST -- 顯示哪些線程正在運(yùn)行SHOW VARIABLES -- 顯示系統(tǒng)變量信息

數(shù)據(jù)庫操作

表的操作

-- 創(chuàng)建表 CREATE [TEMPORARY] TABLE[ IF NOT EXISTS] [庫名.]表名 ( 表的結(jié)構(gòu)定義 )[ 表選項(xiàng)] 每個(gè)字段必須有數(shù)據(jù)類型 最后一個(gè)字段后不能有逗號(hào) TEMPORARY 臨時(shí)表，會(huì)話結(jié)束時(shí)表自動(dòng)消失 對于字段的定義： 字段名 數(shù)據(jù)類型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT string ]-- 表選項(xiàng) -- 字符集 CHARSET = charset_name 如果表沒有設(shè)定，則使用數(shù)據(jù)庫字符集 -- 存儲(chǔ)引擎 ENGINE = engine_name 表在管理數(shù)據(jù)時(shí)采用的不同的數(shù)據(jù)結(jié)構(gòu)，結(jié)構(gòu)不同會(huì)導(dǎo)致處理方式、提供的特性操作等不同 常見的引擎：InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive 不同的引擎在保存表的結(jié)構(gòu)和數(shù)據(jù)時(shí)采用不同的方式 MyISAM表文件含義：.frm表定義，.MYD表數(shù)據(jù)，.MYI表索引 InnoDB表文件含義：.frm表定義，表空間數(shù)據(jù)和日志文件 SHOW ENGINES -- 顯示存儲(chǔ)引擎的狀態(tài)信息 SHOW ENGINE 引擎名 {LOGS|STATUS} -- 顯示存儲(chǔ)引擎的日志或狀態(tài)信息 -- 自增起始數(shù) AUTO_INCREMENT = 行數(shù) -- 數(shù)據(jù)文件目錄 DATA DIRECTORY = 目錄  -- 索引文件目錄 INDEX DIRECTORY = 目錄  -- 表注釋 COMMENT = string  -- 分區(qū)選項(xiàng) PARTITION BY ... (詳細(xì)見手冊)-- 查看所有表 SHOW TABLES[ LIKE pattern ] SHOW TABLES FROM 表名-- 查看表機(jī)構(gòu) SHOW CREATE TABLE 表名 （信息更詳細(xì)） DESC 表名 / DESCRIBE 表名 / EXPLAIN 表名 / SHOW COLUMNS FROM 表名 [LIKE PATTERN ] SHOW TABLE STATUS [FROM db_name] [LIKE pattern ]-- 修改表 -- 修改表本身的選項(xiàng) ALTER TABLE 表名 表的選項(xiàng) eg: ALTER TABLE 表名 ENGINE=MYISAM; -- 對表進(jìn)行重命名 RENAME TABLE 原表名 TO 新表名 RENAME TABLE 原表名 TO 庫名.表名 （可將表移動(dòng)到另一個(gè)數(shù)據(jù)庫） -- RENAME可以交換兩個(gè)表名 -- 修改表的字段機(jī)構(gòu)（13.1.2. ALTER TABLE語法） ALTER TABLE 表名 操作名 -- 操作名 ADD[ COLUMN] 字段定義 -- 增加字段 AFTER 字段名 -- 表示增加在該字段名后面 FIRST -- 表示增加在第一個(gè) ADD PRIMARY KEY(字段名) -- 創(chuàng)建主鍵 ADD UNIQUE [索引名](字段名)-- 創(chuàng)建唯一索引 ADD INDEX [索引名](字段名) -- 創(chuàng)建普通索引 DROP[ COLUMN] 字段名 -- 刪除字段 MODIFY[ COLUMN] 字段名 字段屬性 -- 支持對字段屬性進(jìn)行修改，不能修改字段名(所有原有屬性也需寫上) CHANGE[ COLUMN] 原字段名 新字段名 字段屬性 -- 支持對字段名修改 DROP PRIMARY KEY -- 刪除主鍵(刪除主鍵前需刪除其AUTO_INCREMENT屬性) DROP INDEX 索引名 -- 刪除索引 DROP FOREIGN KEY 外鍵 -- 刪除外鍵-- 刪除表 DROP TABLE[ IF EXISTS] 表名 ...-- 清空表數(shù)據(jù) TRUNCATE [TABLE] 表名-- 復(fù)制表結(jié)構(gòu) CREATE TABLE 表名 LIKE 要復(fù)制的表名-- 復(fù)制表結(jié)構(gòu)和數(shù)據(jù) CREATE TABLE 表名 [AS] SELECT * FROM 要復(fù)制的表名-- 檢查表是否有錯(cuò)誤 CHECK TABLE tbl_name [, tbl_name] ... [option] ...-- 優(yōu)化表 OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...-- 修復(fù)表 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] ...數(shù)據(jù)操作-- 增 INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), ...] -- 如果要插入的值列表包含所有字段并且順序一致，則可以省略字段列表。 -- 可同時(shí)插入多條數(shù)據(jù)記錄！ REPLACE 與 INSERT 完全一樣，可互換。 INSERT [INTO] 表名 SET 字段名=值[, 字段名=值, ...]-- 查 SELECT 字段列表 FROM 表名[ 其他子句] -- 可來自多個(gè)表的多個(gè)字段 -- 其他子句可以不使用 -- 字段列表可以用*代替，表示所有字段-- 刪 DELETE FROM 表名[ 刪除條件子句] 沒有條件子句，則會(huì)刪除全部-- 改 UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新條件]字符集編碼-- MySQL、數(shù)據(jù)庫、表、字段均可設(shè)置編碼-- 數(shù)據(jù)編碼與客戶端編碼不需一致SHOW VARIABLES LIKE character_set_% -- 查看所有字符集編碼項(xiàng) character_set_client 客戶端向服務(wù)器發(fā)送數(shù)據(jù)時(shí)使用的編碼 character_set_results 服務(wù)器端將結(jié)果返回給客戶端所使用的編碼 character_set_connection 連接層編碼SET 變量名 = 變量值 SET character_set_client = gbk; SET character_set_results = gbk; SET character_set_connection = gbk;SET NAMES GBK; -- 相當(dāng)于完成以上三個(gè)設(shè)置-- 校對集 校對集用以排序 SHOW CHARACTER SET [LIKE pattern ]/SHOW CHARSET [LIKE pattern ] 查看所有字符集 SHOW COLLATION [LIKE pattern ] 查看所有校對集 CHARSET 字符集編碼 設(shè)置字符集編碼 COLLATE 校對集編碼 設(shè)置校對集編碼

數(shù)據(jù)類型（列類型）

1. 枚舉和集合

-- 枚舉(enum) ----------enum(val1, val2, val3...) 在已知的值中進(jìn)行單選。最大數(shù)量為65535. 枚舉值在保存時(shí)，以2個(gè)字節(jié)的整型(smallint)保存。每個(gè)枚舉值，按保存的位置順序，從1開始逐一遞增。 表現(xiàn)為字符串類型，存儲(chǔ)卻是整型。 NULL值的索引是NULL。 空字符串錯(cuò)誤值的索引值是0。-- 集合（set） ----------set(val1, val2, val3...) create table tab ( gender set( 男 , 女 , 無 ) ); insert into tab values ( 男, 女 ); 最多可以有64個(gè)不同的成員。以bigint存儲(chǔ)，共8個(gè)字節(jié)。采取位運(yùn)算的形式。 當(dāng)創(chuàng)建表時(shí)，SET成員值的尾部空格將自動(dòng)被刪除。

選擇類型

建表規(guī)范

 -- Normal Format, NF - 每個(gè)表保存一個(gè)實(shí)體信息 - 每個(gè)具有一個(gè)ID字段作為主鍵 - ID主鍵 + 原子表 -- 1NF, 第一范式 字段不能再分，就滿足第一范式。 -- 2NF, 第二范式 滿足第一范式的前提下，不能出現(xiàn)部分依賴。 消除符合主鍵就可以避免部分依賴。增加單列關(guān)鍵字。 -- 3NF, 第三范式 滿足第二范式的前提下，不能出現(xiàn)傳遞依賴。 某個(gè)字段依賴于主鍵，而有其他字段依賴于該字段。這就是傳遞依賴。 將一個(gè)實(shí)體信息的數(shù)據(jù)放在一個(gè)表內(nèi)實(shí)現(xiàn)。

SELECT

SELECT [ALL|DISTINCT] select_expr FROM -> WHERE -> GROUP BY [合計(jì)函數(shù)] -> HAVING -> ORDER BY -> LIMIT

a. select_expr

-- 可以用 * 表示所有字段。

select * from tb;

-- 可以使用表達(dá)式（計(jì)算公式、函數(shù)調(diào)用、字段也是個(gè)表達(dá)式）

select stu, 29+25, now() from tb;

-- 可以為每個(gè)列使用別名。適用于簡化列標(biāo)識(shí)，避免多個(gè)列標(biāo)識(shí)符重復(fù)。

- 使用 as 關(guān)鍵字，也可省略 as.

select stu+10 as add10 from tb;

b. FROM 子句

用于標(biāo)識(shí)查詢來源。

-- 可以為表起別名。使用as關(guān)鍵字。

SELECT * FROM tb1 AS tt, tb2 AS bb;

-- from子句后，可以同時(shí)出現(xiàn)多個(gè)表。

-- 多個(gè)表會(huì)橫向疊加到一起，而數(shù)據(jù)會(huì)形成一個(gè)笛卡爾積。

SELECT * FROM tb1, tb2;

-- 向優(yōu)化符提示如何選擇索引

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獲得的數(shù)據(jù)源中進(jìn)行篩選。

-- 整型1表示真，0表示假。

-- 表達(dá)式由運(yùn)算符和運(yùn)算數(shù)組成。

-- 運(yùn)算數(shù)：變量（字段）、值、函數(shù)返回值

-- 運(yùn)算符：

=, <=>, <>, !=, <=, <, >=, >, !, &&, ||,

in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor

is/is not 加上ture/false/unknown，檢驗(yàn)?zāi)硞€(gè)值的真假

<=>與<>功能相同，<=>可用于null比較

d. GROUP BY 子句, 分組子句

GROUP BY 字段/別名 [排序方式]

分組后會(huì)進(jìn)行排序。升序：ASC，降序：DESC

以下[合計(jì)函數(shù)]需配合 GROUP BY 使用：

count 返回不同的非NULL值數(shù)目 count(*)、count(字段)

sum 求和

max 求最大值

min 求最小值

avg 求平均值

group_concat 返回帶有來自一個(gè)組的連接的非NULL值的字符串結(jié)果。組內(nèi)字符串連接。

e. HAVING 子句，條件子句

與 where 功能、用法相同，執(zhí)行時(shí)機(jī)不同。

where 在開始時(shí)執(zhí)行檢測數(shù)據(jù)，對原數(shù)據(jù)進(jìn)行過濾。

having 對篩選出的結(jié)果再次進(jìn)行過濾。

having 字段必須是查詢出來的，where 字段必須是數(shù)據(jù)表存在的。

where 不可以使用字段的別名，having 可以。因?yàn)閳?zhí)行WHERE代碼時(shí)，可能尚未確定列值。

where 不可以使用合計(jì)函數(shù)。一般需用合計(jì)函數(shù)才會(huì)用 having

SQL標(biāo)準(zhǔn)要求HAVING必須引用GROUP BY子句中的列或用于合計(jì)函數(shù)中的列。

f. ORDER BY 子句，排序子句

order by 排序字段/別名 排序方式 [,排序字段/別名 排序方式]...

升序：ASC，降序：DESC

支持多個(gè)字段的排序。

g. LIMIT 子句，限制結(jié)果數(shù)量子句

僅對處理好的結(jié)果進(jìn)行數(shù)量限制。將處理好的結(jié)果的看作是一個(gè)集合，按照記錄出現(xiàn)的順序，索引從0開始。

limit 起始位置, 獲取條數(shù)

省略第一個(gè)參數(shù)，表示從索引0開始。limit 獲取條數(shù)

h. DISTINCT, ALL 選項(xiàng)

distinct 去除重復(fù)記錄

默認(rèn)為 all, 全部記錄