關(guān)于Mysql整理的需要記憶和熟練掌握的內(nèi)容

1.查詢數(shù)據(jù)表的信息(好比有多少行數(shù)據(jù))：

show table status like 'tab_User' -- 數(shù)據(jù)表中的數(shù)量

2. 使用 explain查看sql運(yùn)行效率時(shí)

1)通過 'row' 來查看掃描的行數(shù)

2)通過 'key' 來查看使用的索引

1. /* 查看操作 */ ------------------------------------------------------------------------------------------------------- 1. /* 查看操作 */

SHOW PROCESSLIST -- 顯示哪些線程正在運(yùn)行

SHOW VARIABLES -- 查看變量

2. /* 數(shù)據(jù)庫操作 */ ------------------------------------------------------------------------------------------------------ 2. /* 數(shù)據(jù)庫操作 */

-- 查看當(dāng)前數(shù)據(jù)庫

select database();

-- 顯示當(dāng)前時(shí)間、用戶名、數(shù)據(jù)庫版本

select now(), user(), version();

-- 復(fù)制表結(jié)構(gòu)

CREATE TABLE 表名 LIKE 要復(fù)制的表名

-- 復(fù)制表結(jié)構(gòu)和數(shù)據(jù)

CREATE TABLE 表名 [AS] SELECT * FROM 要復(fù)制的表名

3. /* 字符集編碼 */ --------------------------------------------------------------------------------------------------------- 3. /* 字符集編碼 */

字符編碼

-- 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è)置

4./* 數(shù)據(jù)類型(列類型) */ ---------------------------------------------------------------------------------------------------4. /* 數(shù)據(jù)類型(列類型) */

1) 數(shù)值類型

int 4字節(jié)

bigint 8字節(jié)

int(M) M表示總位數(shù)

- 默認(rèn)存在符號位,unsigned 屬性修改

- 顯示寬度,如果某個(gè)數(shù)不夠定義字段時(shí)設(shè)置的位數(shù),則前面以0補(bǔ)填,zerofill 屬性修改

例：int(5) 插入一個(gè)數(shù)'123',補(bǔ)填后為'00123'

- 在滿足要求的情況下,越小越好.

- 1表示bool值真,0表示bool值假.MySQL沒有布爾類型,通過整型0和1表示.常用tinyint(1)表示布爾型.

2) 字符串類型

-- a. char, varchar ----------

char 定長字符串,速度快,但浪費(fèi)空間

varchar 變長字符串,速度慢,但節(jié)省空間

M表示能存儲(chǔ)的最大長度,此長度是字符數(shù),非字節(jié)數(shù).

不同的編碼,所占用的空間不同.

char,最多255個(gè)字符,與編碼無關(guān).

varchar,最多65535字符,與編碼有關(guān).

一條有效記錄最大不能超過65535個(gè)字節(jié).

utf8 最大為21844個(gè)字符,gbk 最大為32766個(gè)字符,latin1 最大為65532個(gè)字符

varchar 是變長的,需要利用存儲(chǔ)空間保留 varchar 的長度,如果數(shù)據(jù)小于255個(gè)字節(jié),則采用一個(gè)字節(jié)來保留長度,反之需要兩個(gè)字節(jié)來保留.

varchar 的最大有效長度由最大行大小和使用的字符集確定.

最大有效長度是65532字節(jié),因?yàn)樵趘archar存字符串時(shí),第一個(gè)字節(jié)是空的,不存在任何數(shù)據(jù),然后還需兩個(gè)字節(jié)來存放字符串的長度,所以有效長度是64432-1-2=65532字節(jié).

例：若一個(gè)表定義為 CREATE TABLE tb(c1 int, c2 char(30), c3 varchar(N)) charset=utf8; 問N的最大值是多少? 答：(65535-1-2-4-30*3)/3

5./* 建表規(guī)范 */ ------------------------------------------------------------------------------------------------------------------ 5./* 建表規(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).

6./* select 查詢語句*/ -------------------------------------------------------------------------------------------------------- 6./* select 查詢語句*/

1) 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ù)中的列.

7./* 備份與還原 */ ------------------------------------------------------------------------------------------------------------- 7./* 備份與還原 */

備份,將數(shù)據(jù)的結(jié)構(gòu)與表內(nèi)數(shù)據(jù)保留起來.

利用 mysqldump 指令完成.

-- 導(dǎo)出

1) 導(dǎo)出一張表

mysqldump -u用戶名 -p暗碼 庫名 表名 > 文件名(D:/a.sql)

2)導(dǎo)出多張表

mysqldump -u用戶名 -p暗碼 庫名 表1 表2 表3 > 文件名(D:/a.sql)

3)導(dǎo)出所有表

mysqldump -u用戶名 -p暗碼 庫名 > 文件名(D:/a.sql)

4)導(dǎo)出一個(gè)庫

mysqldump -u用戶名 -p暗碼 -B 庫名 > 文件名(D:/a.sql)

可以-w攜帶備份條件

-- 導(dǎo)入

1)在登錄mysql的情況下：

source 備份文件

2)在不登錄的情況下

mysql -u用戶名 -p暗碼 庫名 < 備份文

8./* 鎖表 */ ------------------------------------------------------------------------------------------------------------------ 8./* 鎖表 */

表鎖定只用于防止其它客戶端進(jìn)行不正本地讀取和寫入

MyISAM 支持表鎖,InnoDB 支持行鎖

-- 鎖定

LOCK TABLES tbl_name [AS alias]

-- 解鎖

UNLOCK TABLES

9./* 用戶和權(quán)限管理 */ -------------------------------------------------------------------------------------------------------- 9./* 用戶和權(quán)限管理 */

用戶信息表：mysql.user

-- 刷新權(quán)限

FLUSH PRIVILEGES

-- 增加用戶

CREATE USER 用戶名 IDENTIFIED BY [PASSWORD] 暗碼(字符串)

- 必需擁有mysql數(shù)據(jù)庫的全局CREATE USER權(quán)限,或擁有INSERT權(quán)限.

- 只能創(chuàng)建用戶,不能賦予權(quán)限.

- 用戶名,注意引號：如 'user_name'@'192.168.1.1'

- 暗碼也需引號,純數(shù)字暗碼也要加引號

- 要在純文本中指定暗碼,需忽略PASSWORD關(guān)鍵詞.要把暗碼指定為由PASSWORD()函數(shù)返回的混編值,需包含關(guān)鍵字PASSWORD

-- 重命名用戶

RENAME USER old_user TO new_user

-- 設(shè)置暗碼

SET PASSWORD = PASSWORD('暗碼') -- 為當(dāng)前用戶設(shè)置暗碼

SET PASSWORD FOR 用戶名 = PASSWORD('暗碼') -- 為指定用戶設(shè)置暗碼

-- 刪除用戶

DROP USER 用戶名

-- 分配權(quán)限/添加用戶

GRANT 權(quán)限列表 ON 表名 TO 用戶名 [IDENTIFIED BY [PASSWORD] 'password']

- all privileges 表示所有權(quán)限

- *.* 表示所有庫的所有表

- 庫名.表名 表示某庫下面的某表

-- 查看權(quán)限

SHOW GRANTS FOR 用戶名

-- 查看當(dāng)前用戶權(quán)限

SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();

-- 撤消權(quán)限

REVOKE 權(quán)限列表 ON 表名 FROM 用戶名

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用戶名 -- 撤銷所有權(quán)限

-- 權(quán)限層級

-- 要使用GRANT或REVOKE,您必需擁有GRANT OPTION權(quán)限,并且您必需用于您正在授予或撤銷的權(quán)限.

全局層級：全局權(quán)限適用于一個(gè)給定服務(wù)器中的所有數(shù)據(jù)庫,mysql.user

GRANT ALL ON *.*和 REVOKE ALL ON *.*只授予和撤銷全局權(quán)限.

數(shù)據(jù)庫層級：數(shù)據(jù)庫權(quán)限適用于一個(gè)給定數(shù)據(jù)庫中的所有目標(biāo),mysql.db, mysql.host

GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤銷數(shù)據(jù)庫權(quán)限.

表層級：表權(quán)限適用于一個(gè)給定表中的所有列,mysql.talbes_priv

GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤銷表權(quán)限.

列層級：列權(quán)限適用于一個(gè)給定表中的單一列,mysql.columns_priv

當(dāng)使用REVOKE時(shí),您必需指定與被授權(quán)列相同的列.