文章目錄
一 常見操作指令
1 數據庫對象操作
創建數據庫
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS]db_name
[DEFAULT CHARACTER SET ='GBK'];
- 創建數據庫
- —— 類型、名稱
- —— 可選參數，表示如果不存在時創建
- —— 默認編碼方式
查看數據庫
SHOW WARNINGS; 顯示警告
SHOW DATABASES;查看數據存在的
SHOW CREATE DATABASE;查看數據庫的定義信息
修改指定數據庫的編碼方式
ALTER {DATABASE\|SCHMA}db\_name
[DEFAULT CHARACTER SET = 'GBK'];
打開指定數據庫
USE db_name; 打開的數據庫名稱
SELECT DATABASE\|SCHEMA();查看當前打開的而數據庫名稱
刪除指定的數據庫
DROP {DATABASE\|SCHEMA}[IF NOT EXISTS] db_name
2 數據表對象操作
定義
-
數據表用來存儲數據的結構
-
由行和列構成的二維網絡
創建數據表
CREATE TABLE [IF NOT EXISTS] tbl_name
(字段名稱 字段類型[完整約束條件])
ENGINE =引擎名稱
CHARSET = 編碼方式;
數據類型補充
字節數分別是1-2-3-4-8-1
- TINYINT
- SMALLINT
- MEDIUMAINT
- INT
- BIGINT
- BOOL
- BOOLEAN
浮點類型補充
FLOAT[(m,d)]
double[(m,d)]
decemal[m,d]
字符串類型
CHAR(M) M帶變得存放的字符數，定長字符串
VARCHAR(M) 變長字符串
TEXT
SET("value","value")集合類型
ENUM("value","value")枚舉類型
日期時間類型
TIME存儲時間
DATE存儲
DATETIME存儲時間日期
TIMESTAMP存儲時間戳
YEAR存儲年份
3 存儲引擎簡介
存儲引擎
指表的類型，表在計算機中怎樣存儲
SHOW ENGINES;
SHOW VARIABLES LIKE'storage_engin';
InnoDB 支持外接，存儲.fim中，讀寫效率地，占用空間大
MyISAM 索引 結構 數據在三個文件中，占用空間小，快，但不支持事務
MEMORY 存儲在內存中的內容創建表，對應磁盤文件，對磁盤快速處理。
二 排序和校驗規則
1 基本概念
mysql數據庫在做查詢時候，有時候是英文字母大小寫敏感的，有時候又不是的，主要是由mysql的字符校驗規則的設置決定的，通常默認是不支持的大小寫字母敏感的。
什么是字符集和校驗規則？
字符集是一套符號和編碼。校對規則是在字符集內用于比較字符的一套規則。任何一個給定的字符集至少有一個校對規則，它可能有幾個校對規則。要想列出一個字符集的校對規則，使用SHOW COLLATION語句。
校對規則一般有這些特征：
- 兩個不同的字符集不能有相同的校對規則。
- 每個字符集有一個默認校對規則。例如，utf8默認校對規則是utf8_general_ci。
命名規定
存在校對規則命名約定：它們以其相關的字符集名開始，通常包括一個語言名，并且以_ci(大小寫不敏感)、_cs(大小寫敏感)或_bin(二元)結束。不同級別的字符集和校驗規則可控制大小寫敏感
MySQL5.1在同一臺服務器、同一個數據庫或甚至在同一個表中使用不同字符集或校對規則來混合定義字符串。字符集和校對規則有4個級別的默認設置：服務器級、數據庫級、表級和連接級。
2 服務器級
1 修改配置文件
MySQL按照如下方法確定服務器字符集和服務器校對規則：
- 修改配置文件/etc/my.cnf。
- 在[mysqld]下添加：collation_server = utf8_bin。
- 重啟實例
更改服務器級的校驗規則(collation_server )后，數據庫校驗規則(collation_collation)默認會繼承服務器級的。
注意：
這個只適用于在重新啟動之后, 新建的庫,已存在的庫不受影響.
同樣的, 即使庫的校驗規則改了,已經存在的表不受修改影響;
同理與已經存在的列…
mysql> create databaseyutest0;
Query OK,1 row affected (0.00sec)
mysql> useyutest0;Databasechanged
mysql> create table t1 (name varchar(10));
Query OK,0 rows affected (0.01sec)
mysql> insert into t1 values('AAA');
Query OK,1 row affected (0.00sec)
mysql> insert into t1 values('aaa');
Query OK,1 row affected (0.01sec)
mysql> select * fromt1;
+------+
| name |
+------+
| AAA |
| aaa |
+------+
2 rows in set (0.00sec)
mysql> select * from t1 where name='aaa';
+------+
| name |
+------+
| aaa |
+------+
1 row in set (0.00 sec)
可以看出，在服務器級進行相應的校對規則設置，查詢大小寫敏感。
當服務器啟動時根據有效的選項設置
當啟動mysqld時，根據使用的初始選項設置來確定服務器字符集和校對規則。
shell> mysqld --character-set-server=latin1 --collation-server=latin1_swedish_ci
更改設定值的一個方法是通過重新編譯。如果希望在從源程序構建時更改默認服務器字符集和校對規則，使用：–with-charset和–with-collation作為configure的參量。例如：
shell> ./configure --with-charset=latin1 --with-collation=latin1_german1_ci
mysqld和configure都驗證字符集/校對規則組合是否有效。如果無效，每個程序都顯示一個錯誤信息，然后終止。
3 數據庫級
規則說明
MySQL這樣選擇數據庫字符集和數據庫校對規則：
- 如果指定了character set X和collate Y，那么采用字符集X和校對規則Y。
- 如果指定了character set X而沒有指定collate Y，那么采用character set X和character set X的默認校對規則。
- 否則，采用服務器字符集和服務器校對規則。
測試驗證
- 創建數據庫時設置數據庫校驗規則
mysql> create database yutest default character setutf8 collate utf8_bin;
Query OK,1 row affected (0.00sec)
mysql> show variables like 'collation_%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_bin |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00sec)
mysql> select * fromt1;
+------+
| name |
+------+
| ABC |
| abc |
+------+
2 rows in set (0.00sec)
mysql> select * from t1 where name='abc';
+------+
| name |
+------+
| abc |
+------+
1 row in set (0.01 sec)
可以看出，在數據庫級進行相應的校對規則設置，查詢大小寫敏感。
4 表級
規則說明
MySQL按照下面的方式選擇表字符集和校對規則：
- 如果指定了character set X和collate Y，那么采用character set X和collate Y。
- 如果指定了character set X而沒有指定collate Y，那么采用character set X和character set X的默認校對規則。
- 否則，采用數據庫字符集和服務器校對規則。
測試驗證
在創建表時設置表級校驗規則：
mysql> create databaseyutest2;
Query OK,1 row affected (0.01sec)
mysql> useyutest2;Databasechanged
mysql> create table t1(name varchar(10))-> default character setutf8 collate utf8_bin;
Query OK,0 rows affected (0.01sec)
mysql> insert into t1 values('ABC');
Query OK,1 row affected (0.00sec)
mysql> insert into t1 values('abc');
Query OK,1 row affected (0.00sec)
mysql> show variables like 'collation_%';+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00sec)
mysql> select * fromt1;+------+
| name |
+------+
| ABC |
| abc |
+------+
2 rows in set (0.00sec)
mysql> select * from t1 where name='abc';+------+
| name |
+------+
| abc |
+------+
1 row in set (0.00 sec)
可以看出，在表級進行相應的校對規則設置，查詢大小寫敏感。
5 連接級
連接字符集
考慮什么是一個“連接”：它是連接服務器時所作的事情?？蛻舳税l送SQL語句，例如查詢，通過連接發送到服務器。服務器通過連接發送響應給客戶端，例如結果集。對于客戶端連接，這樣會導致一些關于連接的字符集和校對規則的問題，這些問題均能夠通過系統變量來解決：
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
- 當查詢離開客戶端后，在查詢中使用哪種字符集？
- 服務器使用character_set_client變量作為客戶端發送的查詢中使用的字符集。
- 服務器接收到查詢后應該轉換為哪種字符集？
- 轉換時，服務器使用character_set_connection和collation_connection系統變量。它將客戶端發送的查詢從character_set_client系統變量轉換到character_set_connection。
- 服務器發送結果集或返回錯誤信息到客戶端之前應該轉換為哪種字符集？
- character_set_results變量指示服務器返回查詢結果到客戶端使用的字符集。包括結果數據，例如列值和結果元數據(如列名)。
在SQL語句中使用collate
創建數據庫表時大小寫不敏感，仍然有方法在查詢時區分大小寫
使用collate子句，能夠為一個比較覆蓋任何默認校對規則。collate可以用于多種SQL語句中，比如where，having，group by，order by，as，聚合函數。
mysql> select * from t1 where name collate utf8_bin = 'ABC';+------+
| name |
+------+
| ABC |
+------+
1 row in set (0.00sec)
mysql> select * from t1 where name = 'ABC';+------+
| name |
+------+
| ABC |
| Abc |
| abc |
+------+
3 rows in set (0.00sec)
mysql> select * fromt1;+------+
| name |
+------+
| ABC |
| Abc |
| abc |
+------+
3 rows in set (0.00 sec)
binary操作符
binary操作符是collate子句的一個速記符。binary ‘x’等價與’x’ collate y，這里y是字符集’x’二元校對規則的名字。每一個字符集有一個二元校對規則。例如，latin1字符集的二元校對規則是latin1_bin，因此，如果列a是字符集latin1，以下兩個語句有相同效果：
select * from t1 order by binarya;select * from t1 order by a collate latin1_bin;
mysql> select * from t1 where binary name = 'ABC';+------+
| name |
+------+
| ABC |
+------+
1 row in set (0.00sec)
mysql>mysql> select * from t1 where name = 'ABC';+------+
| name |
+------+
| ABC |
| Abc |
| abc |
+------+
3 rows in set (0.00 sec)
析## 1 簡介
在MySQL中，EXPLAIN命令的返回值是一個表格，包含了查詢語句的執行計劃。表格中的每一行代表了一個訪問方式，每一列代表了一個屬性，例如：
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | users | const | PRIMARY | PRIMARY | 4 | const| 1 | |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
下面是表格中各個屬性的含義：
-
id: 查詢的標識符，每個查詢都有一個唯一的標識符。如果是子查詢,id的序號會遞增,id值越大優先級越高,優先被執行
-
select_type: 查詢的類型，共有以下幾種類型：
- SIMPLE：簡單select查詢,查詢中不包含子查詢或者UNION
- PRIMARY：查詢中若包含任何復雜的子查詢,最外層查詢則被標記為primary
- SUBQUERY：在select或where中包含了子查詢
- DERIVED：在from列表中包含的子查詢被標記為derived(衍生)把結果放在臨時表當
- UNION：若第二個select出現的union之后,則被標記為union。若union包含在from子句的子查詢中,外層select將被標記為deriver
- UNION RESULT：從union表獲取結果select,兩個UNION合并的結果集在最后
-
table: 此行正在訪問的表。
-
type: 表示訪問類型，包括以下幾種類型：
- ALL: 全表掃描，將訪問表的每一行數據。將全表進行掃描,從硬盤當中讀取數據,如果出現了All 切數據量非常大, 一定要去做優化
![外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳]()
- index: 全索引掃描，將訪問索引的每一行數據。index與All區別為index類型只遍歷索引樹,通常比All要快,
因為索引文件通常比數據文件要小all和index都是讀全表,
但index是從索引中讀取,all是從硬盤當中讀取![外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳]()
- range: 只檢索給定范圍的行,使用一個索引來選擇行 一般就是在你的where語句中出現between<>\ in等查詢,這種范圍掃描索引比全表掃描要好,因為它只需要開始于索引的某一點.而結束語另一點,不用掃描全部索引。
![外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳]()
- ref: 非唯一性索引掃描。出現在一對多關系中，例如部門和員工。ref 非唯一性索引掃描,返回匹配某個單獨值的所有行,本質上也是一種索引訪問,它返回所有匹配某個單獨值的行可能會找到多個符合條件的行,所以它應該屬于查找和掃描的混合體
![外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳]()
![外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳]()
- eq_ref: 唯一索引引用，類似于ref，但使用的是唯一索引。eq_ref 唯一性索引掃描 對于每個索引鍵,表中只有一條記錄與之匹配, 常見于主鍵或唯一索引掃描
![外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳]()
- const: 表示通過索引一次就找到了。常量復雜度
![外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳]()
- system: 表中有一行記錄(系統表) 這是const類型的特例,平時不會出現
![外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳]()
- ALL: 全表掃描，將訪問表的每一行數據。將全表進行掃描,從硬盤當中讀取數據,如果出現了All 切數據量非常大, 一定要去做優化
-
partitions：如果查詢是基于分區表的話, 會顯示查詢訪問的分區
-
possible_keys: 可能使用的索引列表。
-
key: 實際使用的索引。
- 實際使用的索引,如果為NULL,則沒有使用索引,查詢中若使用了覆蓋索引 ,則該索引僅出現在key列表possible_keys與key關系,理論應該用到哪些索引實際用到了哪些索引覆蓋索引 查詢的字段和建立的字段剛好吻合,
這種我們稱為覆蓋索引
- 實際使用的索引,如果為NULL,則沒有使用索引,查詢中若使用了覆蓋索引 ,則該索引僅出現在key列表possible_keys與key關系,理論應該用到哪些索引實際用到了哪些索引覆蓋索引 查詢的字段和建立的字段剛好吻合,
-
key_len: 索引鍵使用的字節數。
-
ref: 列與索引之間的匹配。索引是否被引入到, 到底引用到了哪幾個索引
-
rows: 表示MySQL估計需要掃描的行數。語句中出現了Using Filesort 和 Using Temporary說明沒有使用到索引。出現 impossible where說明條件永遠不成立
- Extra: 額外的信息，包括以下幾種類型：
- Using filesort: MySQL需要額外排序。
- Using temporary: MySQL需要創建臨時表。
- Using index: 查詢使用了覆蓋索引。
- Using where: MySQL需要過濾部分數據。
- Using join buffer: MySQL正在使用連接緩沖區。
-
filtered滿足查詢的記錄數量的比例，注意是百分比，不是具體記錄數 . 值越大越好，filtered列的值依賴統計信息，并不十分準確
通過分析執行計劃，可以發現哪些地方需要優化，例如是否可以添加索引、是否可以縮小查詢范圍等等。
2 實例
id值與執行順序
-
id值相同
![外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳]()
-
id值不同
![外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳]()
-
id值
![外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳]()
浙公網安備 33010602011771號