1.數(shù)據(jù)庫(kù)操作
/* 數(shù)據(jù)庫(kù)操作 */ ------------------
-- 查看當(dāng)前數(shù)據(jù)庫(kù)
SELECT DATABASE();
-- 顯示當(dāng)前時(shí)間、用戶名、數(shù)據(jù)庫(kù)版本
SELECT now(), user(), version();
-- 創(chuàng)建庫(kù)
CREATE DATABASE [IF NOT EXISTS] 數(shù)據(jù)庫(kù)名 數(shù)據(jù)庫(kù)選項(xiàng)
數(shù)據(jù)庫(kù)選項(xiàng)：
CHARACTER SET charset_name
COLLATE collation_name
-- 查看已有庫(kù)
SHOW DATABASES [LIKE 'PATTERN']
-- 查看當(dāng)前庫(kù)信息
SHOW CREATE DATABASE 數(shù)據(jù)庫(kù)名
-- 修改庫(kù)的選項(xiàng)信息
ALTER DATABASE 庫(kù)名 選項(xiàng)信息
-- 刪除庫(kù)
DROP DATABASE [IF EXISTS] 數(shù)據(jù)庫(kù)名
同時(shí)刪除該數(shù)據(jù)庫(kù)相關(guān)的目錄及其目錄內(nèi)容
2.表操作
-- 創(chuàng)建表
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [庫(kù)名.]表名 ( 表的結(jié)構(gòu)定義 ) [表選項(xiàng)]
每個(gè)字段必須有數(shù)據(jù)類型
最后一個(gè)字段后不能有逗號(hào)
TEMPORARY 臨時(shí)表，會(huì)話結(jié)束時(shí)表自動(dòng)消失
對(duì)于字段的定義：
字段名 數(shù)據(jù)類型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
-- 表選項(xiàng)
-- 字符集
CHARSET = charset_name
如果表沒(méi)有設(shè)定，則使用數(shù)據(jù)庫(kù)字符集
-- 存儲(chǔ)引擎
ENGINE = engine_name
表在管理數(shù)據(jù)時(shí)采用的不同的數(shù)據(jù)結(jié)構(gòu)，結(jié)構(gòu)不同會(huì)導(dǎo)致處理方式、提供的特性操作等不同
常見(jiàn)的引擎：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ì)見(jiàn)手冊(cè))
-- 查看所有表
SHOW TABLES [LIKE 'pattern']
SHOW TABLES FROM 庫(kù)名
-- 查看表結(jié)構(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;
-- 對(duì)表進(jìn)行重命名
RENAME TABLE 原表名 TO 新表名
RENAME TABLE 原表名 TO 庫(kù)名.表名 （可將表移動(dòng)到另一個(gè)數(shù)據(jù)庫(kù)）
-- RENAME可以交換兩個(gè)表名
-- 修改表的字段機(jī)構(gòu)（13.1.2. ALTER TABLE語(yǔ)法）
ALTER TABLE 表名 操作名
-- 操作名
ADD [COLUMN] 字段定義 -- 增加字段
AFTER 字段名 -- 表示增加在該字段名后面
FIRST -- 表示增加在第一個(gè)
ADD PRIMARY KEY(字段名) -- 創(chuàng)建主鍵
ADD UNIQUE [索引名] (字段名)-- 創(chuàng)建唯一索引
ADD INDEX [索引名] (字段名) -- 創(chuàng)建普通索引
DROP [COLUMN] 字段名 -- 刪除字段
MODIFY [COLUMN] 字段名 字段屬性 -- 支持對(duì)字段屬性進(jìn)行修改，不能修改字段名(所有原有屬性也需寫(xiě)上)
CHANGE [COLUMN] 原字段名 新字段名 字段屬性 -- 支持對(duì)字段名修改
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] ...
3.數(shù)據(jù)操作
-- 增
INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), ...]
-- 如果要插入的值列表包含所有字段并且順序一致，則可以省略字段列表。
-- 可同時(shí)插入多條數(shù)據(jù)記錄！
REPLACE 與 INSERT 完全一樣，可互換。
INSERT [INTO] 表名 SET 字段名=值[, 字段名=值, ...]
-- 查
SELECT 字段列表 FROM 表名[ 其他子句]
-- 可來(lái)自多個(gè)表的多個(gè)字段
-- 其他子句可以不使用
-- 字段列表可以用*代替，表示所有字段
-- 刪
DELETE FROM 表名[ 刪除條件子句]
沒(méi)有條件子句，則會(huì)刪除全部
-- 改
UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新條件]
/* SELECT */ ------------------
SELECT [ALL|DISTINCT] select_expr FROM -> WHERE -> GROUP BY [合計(jì)函數(shù)] -> HAVING -> ORDER BY -> LIMIT
① select_expr
-- 可以用 * 表示所有字段。
select * from tb;
-- 可以使用表達(dá)式（計(jì)算公式、函數(shù)調(diào)用、字段也是個(gè)表達(dá)式）
select stu, 29+25, now() from tb;
-- 可以為每個(gè)列使用別名。適用于簡(jiǎn)化列標(biāo)識(shí)，避免多個(gè)列標(biāo)識(shí)符重復(fù)。
- 使用 as 關(guān)鍵字，也可省略 as.
select stu+10 as add10 from tb;
② FROM 子句
用于標(biāo)識(shí)查詢來(lái)源。
-- 可以為表起別名。使用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;
③ 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比較
④ GROUP BY 子句, 分組子句
GROUP BY 字段/別名 [排序方式]
分組后會(huì)進(jìn)行排序。升序：ASC，降序：DESC
以下[合計(jì)函數(shù)]需配合 GROUP BY 使用：
count 返回不同的非NULL值數(shù)目 count(*)、count(字段)
sum 求和
max 求最大值
min 求最小值
avg 求平均值
group_concat 返回帶有來(lái)自一個(gè)組的連接的非NULL值的字符串結(jié)果。組內(nèi)字符串連接。
⑤ HAVING 子句，條件子句
與 where 功能、用法相同，執(zhí)行時(shí)機(jī)不同。
where 在開(kāi)始時(shí)執(zhí)行檢測(cè)數(shù)據(jù)，對(duì)原數(shù)據(jù)進(jìn)行過(guò)濾。
having 對(duì)篩選出的結(jié)果再次進(jìn)行過(guò)濾。
having 字段必須是查詢出來(lái)的，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ù)中的列。
⑥ ORDER BY 子句，排序子句
order by 排序字段/別名 排序方式 [,排序字段/別名 排序方式]...
升序：ASC，降序：DESC
支持多個(gè)字段的排序。
⑦ LIMIT 子句，限制結(jié)果數(shù)量子句
僅對(duì)處理好的結(jié)果進(jìn)行數(shù)量限制。將處理好的結(jié)果的看作是一個(gè)集合，按照記錄出現(xiàn)的順序，索引從0開(kāi)始。
limit 起始位置, 獲取條數(shù)
省略第一個(gè)參數(shù)，表示從索引0開(kāi)始。limit 獲取條數(shù)
⑧ DISTINCT, ALL 選項(xiàng)
distinct 去除重復(fù)記錄
默認(rèn)為 all, 全部記錄
/* JOIN */ ------------------
將多個(gè)表的字段進(jìn)行連接，可以指定連接條件。
-- 內(nèi)連接(inner join)
- 默認(rèn)就是內(nèi)連接，可省略inner。
- 只有數(shù)據(jù)存在時(shí)才能發(fā)送連接。即連接結(jié)果不能出現(xiàn)空行。
on 表示連接條件。其條件表達(dá)式與where類似。也可以省略條件（表示條件永遠(yuǎn)為真）
也可用where表示連接條件。
還有 using, 但需字段名相同。 using(字段名)
-- 交叉連接 cross join
即，沒(méi)有條件的內(nèi)連接。
select * from tb1 cross join tb2;
-- 外連接(outer join)
- 如果數(shù)據(jù)不存在，也會(huì)出現(xiàn)在連接結(jié)果中。
-- 左外連接 left join
如果數(shù)據(jù)不存在，左表記錄會(huì)出現(xiàn)，而右表為null填充
-- 右外連接 right join
如果數(shù)據(jù)不存在，右表記錄會(huì)出現(xiàn)，而左表為null填充
-- 自然連接(natural join)
自動(dòng)判斷連接條件完成連接。
相當(dāng)于省略了using，會(huì)自動(dòng)查找相同字段名。
natural join
natural left join
natural right join
select info.id, info.name, info.stu_num, extra_info.hobby, extra_info.sex from info, extra_info where info.stu_num = extra_info.stu_id;
4.數(shù)據(jù)類型
/* 數(shù)據(jù)類型（列類型） */ ------------------
1. 數(shù)值類型
-- ① 整型 ----------
類型 字節(jié) 范圍（有符號(hào)位）
tinyint 1字節(jié) -128 ~ 127 無(wú)符號(hào)位：0 ~ 255
smallint 2字節(jié) -32768 ~ 32767
mediumint 3字節(jié) -8388608 ~ 8388607
int 4字節(jié)
bigint 8字節(jié)
int(M) M表示總位數(shù)
- 默認(rèn)存在符號(hào)位，unsigned 屬性修改
- 顯示寬度，如果某個(gè)數(shù)不夠定義字段時(shí)設(shè)置的位數(shù)，則前面以0補(bǔ)填，zerofill 屬性修改
例：int(5) 插入一個(gè)數(shù)'123'，補(bǔ)填后為'00123'
- 在滿足要求的情況下，越小越好。
- 1表示bool值真，0表示bool值假。MySQL沒(méi)有布爾類型，通過(guò)整型0和1表示。常用tinyint(1)表示布爾型。
-- ② 浮點(diǎn)型 ----------
類型 字節(jié) 范圍
float(單精度) 4字節(jié)
double(雙精度) 8字節(jié)
浮點(diǎn)型既支持符號(hào)位 unsigned 屬性，也支持顯示寬度 zerofill 屬性。
不同于整型，前后均會(huì)補(bǔ)填0.
定義浮點(diǎn)型時(shí)，需指定總位數(shù)和小數(shù)位數(shù)。
float(M, D) double(M, D)
M表示總位數(shù)，D表示小數(shù)位數(shù)。
M和D的大小會(huì)決定浮點(diǎn)數(shù)的范圍。不同于整型的固定范圍。
M既表示總位數(shù)（不包括小數(shù)點(diǎn)和正負(fù)號(hào)），也表示顯示寬度（所有顯示符號(hào)均包括）。
支持科學(xué)計(jì)數(shù)法表示。
浮點(diǎn)數(shù)表示近似值。
-- ③ 定點(diǎn)數(shù) ----------
decimal -- 可變長(zhǎng)度
decimal(M, D) M也表示總位數(shù)，D表示小數(shù)位數(shù)。
保存一個(gè)精確的數(shù)值，不會(huì)發(fā)生數(shù)據(jù)的改變，不同于浮點(diǎn)數(shù)的四舍五入。
將浮點(diǎn)數(shù)轉(zhuǎn)換為字符串來(lái)保存，每9位數(shù)字保存為4個(gè)字節(jié)。
2. 字符串類型
-- ① char, varchar ----------
char 定長(zhǎng)字符串，速度快，但浪費(fèi)空間
varchar 變長(zhǎng)字符串，速度慢，但節(jié)省空間
M表示能存儲(chǔ)的最大長(zhǎng)度，此長(zhǎng)度是字符數(shù)，非字節(jié)數(shù)。
不同的編碼，所占用的空間不同。
char,最多255個(gè)字符，與編碼無(wú)關(guān)。
varchar,最多65535字符，與編碼有關(guān)。
一條有效記錄最大不能超過(guò)65535個(gè)字節(jié)。
utf8 最大為21844個(gè)字符，gbk 最大為32766個(gè)字符，latin1 最大為65532個(gè)字符
varchar 是變長(zhǎng)的，需要利用存儲(chǔ)空間保存 varchar 的長(zhǎng)度，如果數(shù)據(jù)小于255個(gè)字節(jié)，則采用一個(gè)字節(jié)來(lái)保存長(zhǎng)度，反之需要兩個(gè)字節(jié)來(lái)保存。
varchar 的最大有效長(zhǎng)度由最大行大小和使用的字符集確定。
最大有效長(zhǎng)度是65532字節(jié)，因?yàn)樵趘archar存字符串時(shí)，第一個(gè)字節(jié)是空的，不存在任何數(shù)據(jù)，然后還需兩個(gè)字節(jié)來(lái)存放字符串的長(zhǎng)度，所以有效長(zhǎng)度是65535-1-2=65532字節(jié)。
例：若一個(gè)表定義為 CREATE TABLE tb(c1 int, c2 char(30), c3 varchar(N)) charset=utf8; 問(wèn)N的最大值是多少？ 答：(65535-1-2-4-30*3)/3
-- ② blob, text ----------
blob 二進(jìn)制字符串（字節(jié)字符串）
tinyblob, blob, mediumblob, longblob
text 非二進(jìn)制字符串（字符字符串）
tinytext, text, mediumtext, longtext
text 在定義時(shí)，不需要定義長(zhǎng)度，也不會(huì)計(jì)算總長(zhǎng)度。
text 類型在定義時(shí)，不可給default值
-- ③ binary, varbinary ----------
類似于char和varchar，用于保存二進(jìn)制字符串，也就是保存字節(jié)字符串而非字符字符串。
char, varchar, text 對(duì)應(yīng) binary, varbinary, blob.
3. 日期時(shí)間類型
一般用整型保存時(shí)間戳，因?yàn)镻HP可以很方便的將時(shí)間戳進(jìn)行格式化。
datetime 8字節(jié) 日期及時(shí)間 1000-01-01 00:00:00 到 9999-12-31 23:59:59
date 3字節(jié) 日期 1000-01-01 到 9999-12-31
timestamp 4字節(jié) 時(shí)間戳 19700101000000 到 2038-01-19 03:14:07
time 3字節(jié) 時(shí)間 -838:59:59 到 838:59:59
year 1字節(jié) 年份 1901 - 2155
datetime YYYY-MM-DD hh:mm:ss
timestamp YY-MM-DD hh:mm:ss
YYYYMMDDhhmmss
YYMMDDhhmmss
YYYYMMDDhhmmss
YYMMDDhhmmss
date YYYY-MM-DD
YY-MM-DD
YYYYMMDD
YYMMDD
YYYYMMDD
YYMMDD
time hh:mm:ss
hhmmss
hhmmss
year YYYY
YY
YYYY
YY
4. 枚舉和集合
-- 枚舉(enum) ----------
enum(val1, val2, val3...)
在已知的值中進(jìn)行單選。最大數(shù)量為65535.
枚舉值在保存時(shí)，以2個(gè)字節(jié)的整型(smallint)保存。每個(gè)枚舉值，按保存的位置順序，從1開(kāi)始逐一遞增。
表現(xiàn)為字符串類型，存儲(chǔ)卻是整型。
NULL值的索引是NULL。
空字符串錯(cuò)誤值的索引值是0。
-- 集合（set） ----------
set(val1, val2, val3...)
create table tab ( gender set('男', '女', '無(wú)') );
insert into tab values ('男, 女');
最多可以有64個(gè)不同的成員。以bigint存儲(chǔ)，共8個(gè)字節(jié)。采取位運(yùn)算的形式。
當(dāng)創(chuàng)建表時(shí)，SET成員值的尾部空格將自動(dòng)被刪除。
