【精粹系列】Mysql精粹_ZenDei技術網路在線

關於Mysql整理的需要記憶和熟練掌握的內容 ...

關於Mysql整理的需要記憶和熟練掌握的內容

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

SHOW PROCESSLIST -- 顯示哪些線程正在運行
SHOW VARIABLES -- 查看變數



2. /* 資料庫操作 */ ------------------------------------------------------------------------------------------------------ 2. /* 資料庫操作 */

-- 查看當前資料庫
    select database();
-- 顯示當前時間、用戶名、資料庫版本
    select now(), user(), version();

-- 複製表結構
    CREATE TABLE 表名 LIKE 要複製的表名
-- 複製表結構和數據
    CREATE TABLE 表名 [AS] SELECT * FROM 要複製的表名

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

字元編碼
-- MySQL、資料庫、表、欄位均可設置編碼
-- 數據編碼與客戶端編碼不需一致
SHOW VARIABLES LIKE 'character_set_%'    -- 查看所有字元集編碼項
    character_set_client        客戶端向伺服器發送數據時使用的編碼
    character_set_results        伺服器端將結果返回給客戶端所使用的編碼
    character_set_connection    連接層編碼
SET 變數名 = 變數值
    set character_set_client = gbk;
    set character_set_results = gbk;
    set character_set_connection = gbk;
SET NAMES GBK;    -- 相當於完成以上三個設置


4./* 數據類型（列類型） */ ---------------------------------------------------------------------------------------------------4. /* 數據類型（列類型） */

1) 數值類型

　　int            4位元組
    bigint        8位元組

 int(M)    M表示總位數
    - 預設存在符號位，unsigned 屬性修改
    - 顯示寬度，如果某個數不夠定義欄位時設置的位數，則前面以0補填，zerofill 屬性修改
        例：int(5)    插入一個數'123'，補填後為'00123'
    - 在滿足要求的情況下，越小越好。
    - 1表示bool值真，0表示bool值假。MySQL沒有布爾類型，通過整型0和1表示。常用tinyint(1)表示布爾型。

2) 字元串類型
-- a. char, varchar ----------
    char    定長字元串，速度快，但浪費空間
    varchar    變長字元串，速度慢，但節省空間
    M表示能存儲的最大長度，此長度是字元數，非位元組數。
    不同的編碼，所占用的空間不同。
    char,最多255個字元，與編碼無關。
    varchar,最多65535字元，與編碼有關。
    一條有效記錄最大不能超過65535個位元組。
        utf8 最大為21844個字元，gbk 最大為32766個字元，latin1 最大為65532個字元
    varchar 是變長的，需要利用存儲空間保存 varchar 的長度，如果數據小於255個位元組，則採用一個位元組來保存長度，反之需要兩個位元組來保存。
    varchar 的最大有效長度由最大行大小和使用的字元集確定。
    最大有效長度是65532位元組，因為在varchar存字元串時，第一個位元組是空的，不存在任何數據，然後還需兩個位元組來存放字元串的長度，所以有效長度是64432-1-2=65532位元組。
    例：若一個表定義為 CREATE TABLE tb(c1 int, c2 char(30), c3 varchar(N)) charset=utf8; 問N的最大值是多少？ 答：(65535-1-2-4-30*3)/3

5./* 建表規範 */ ------------------------------------------------------------------------------------------------------------------ 5./* 建表規範 */
    -- Normal Format, NF
        - 每個表保存一個實體信息
        - 每個具有一個ID欄位作為主鍵
        - ID主鍵 + 原子表
    -- 1NF, 第一範式
        欄位不能再分，就滿足第一範式。
    -- 2NF, 第二範式
        滿足第一範式的前提下，不能出現部分依賴。
        消除符合主鍵就可以避免部分依賴。增加單列關鍵字。
    -- 3NF, 第三範式
        滿足第二範式的前提下，不能出現傳遞依賴。
        某個欄位依賴於主鍵，而有其他欄位依賴於該欄位。這就是傳遞依賴。
        將一個實體信息的數據放在一個表內實現。

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

1） having 子句，條件子句
    與 where 功能、用法相同，執行時機不同。
    where 在開始時執行檢測數據，對原數據進行過濾。
    having 對篩選出的結果再次進行過濾。
    having 欄位必須是查詢出來的，where 欄位必須是數據表存在的。
    where 不可以使用欄位的別名，having 可以。因為執行WHERE代碼時，可能尚未確定列值。
    where 不可以使用合計函數。一般需用合計函數才會用 having
    SQL標準要求HAVING必須引用GROUP BY子句中的列或用於合計函數中的列。

7./* 備份與還原 */ ------------------------------------------------------------------------------------------------------------- 7./* 備份與還原 */
備份，將數據的結構與表內數據保存起來。
利用 mysqldump 指令完成。

-- 導出
1） 導出一張表
　　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密碼 -B 庫名 > 文件名(D:/a.sql)

可以-w攜帶備份條件

-- 導入
1）在登錄mysql的情況下：
　　source  備份文件
2）在不登錄的情況下
　　mysql -u用戶名 -p密碼 庫名 < 備份文

8./* 鎖表 */  ------------------------------------------------------------------------------------------------------------------ 8./* 鎖表 */
表鎖定只用於防止其它客戶端進行不正當地讀取和寫入
MyISAM 支持表鎖，InnoDB 支持行鎖
-- 鎖定
    LOCK TABLES tbl_name [AS alias]
-- 解鎖
    UNLOCK TABLES

9./* 用戶和許可權管理 */ -------------------------------------------------------------------------------------------------------- 9./* 用戶和許可權管理 */

用戶信息表：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_user TO new_user
-- 設置密碼
SET PASSWORD = PASSWORD('密碼')    -- 為當前用戶設置密碼
SET PASSWORD FOR 用戶名 = PASSWORD('密碼')    -- 為指定用戶設置密碼
-- 刪除用戶
DROP USER 用戶名
-- 分配許可權/添加用戶
GRANT 許可權列表 ON 表名 TO 用戶名 [IDENTIFIED BY [PASSWORD] 'password']
    - all privileges 表示所有許可權
    - *.* 表示所有庫的所有表
    - 庫名.表名 表示某庫下麵的某表
-- 查看許可權
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.host
    GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤銷資料庫許可權。
表層級：表許可權適用於一個給定表中的所有列，mysql.talbes_priv
    GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤銷表許可權。
列層級：列許可權適用於一個給定表中的單一列，mysql.columns_priv
    當使用REVOKE時，您必須指定與被授權列相同的列。