mysql基本操作

一、連接配接操作

 格式：mysql-h主機位址 -u使用者名 -p使用者密碼

例：連接配接到遠端主機上的mysql假設遠端主機的IP為：110.110.110.110使用者

名為root,密碼為abcd123則鍵入以下指令：

mysql-h110.110.110.110-uroot-pabcd123

二、使用者級操作

1、select user() 檢視自己的使用者名

2、Linux指令ps -el | grep mysqld用來檢測mysql伺服器是否在運作。如果結果為：

   4 S  0  1796  1  0  85   0 -  1513 wait   ?   00:00:00 mysqld_safe

   4 S  27 1856  1796  0  78   0 - 34055 -      ?        00:00:00 mysqld

   就說明伺服器已經運作起來了。

3、修改密碼

update user set password="新密碼" where user="使用者名"。

4、新加使用者

Grant select on 資料庫.*to 使用者名@登入主機 identified by “密碼”。

三、資料庫操作

1、建立資料庫 create database <資料庫名>

2、顯示資料庫 show database；

3、删除資料庫 drop database <資料庫名>

4、連接配接資料庫 use <資料庫名>

5、目前選中的資料庫 select database();

6、檢視資料庫編碼SHOW VARIABLES LIKE 'character%';

四、資料表操作

1、建立資料表 create table <表名>( <字段名1><類型1>,<字段名2><類型2>)；

2、檢視表結構 describe 表名

3、擷取表結構 desc表名，或者show columns from 表名；

4、删除資料表 drop table<表名>

5、表插入資料 insert into 表名 (字段1，字段2......) values ("值1"，“值2”...),....("值1","值2");

6、查詢表中資料 select <字段1，字段2....>from<表名>where<表達式> ，檢視前2行資料 select*from表名 limit（0，2）

7、删除表中資料 delete from 表名 where 表達式

8、修改表中資料 update 表名 set 字段=新值，..where 條件

9、增加字段 alter table 表名 add 字段 類型 其他

   加索引   alter table 表名 add index 索引名 （字段1，字段2）；

   加主鍵   alter table 表名 add primary key（字段名）；

   删除某個索引 alter table 表名 drop index 索引名；

   修改原字段名稱及類型 alter table 表名 change舊字段名 新字段名 新類型；

   删除字段 alter table 表名 drop 字段名

10、修改表名  rename table 舊表名 to 新表名

五、備份資料庫

1、導出整個資料庫

   導出檔案預設是存在mysql\bin目錄下

   mysqldump -u 使用者名 -p 資料庫名 > 導出的檔案名

   mysqldump -u user_name -p123456 database_name > outfile_name.sql

2、導出一個表

   mysqldump -u 使用者名 -p 資料庫名 表名> 導出的檔案名

   mysqldump -u user_name -p database_name table_name > outfile_name.sql

3、導出一個資料庫結構

   mysqldump -u user_name -p -d –add-drop-table database_name > outfile_name.sql

   -d 沒有資料 –add-drop-table 在每個create語句之前增加一個drop table

4、帶語言參數導出

   mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk –skip-opt      database_name > outfile_name.sql

5、導入檔案 source 檔案路徑  mysql>source d:\mydb.sql

注：導出檔案時在作業系統控制台的ySQL\MySQL Server 5.5\bin目錄下執行 ，導入檔案是在mysql控制台執行

六、query cache

Query Cache 緩存了我們用戶端送出給 MySQL 的 SELECT 語句以及該語句的結果集。大概來講，就是将 SELECT 語句和語句的結果做了一個 HASH 映射關系然後儲存在一定的記憶體區域中。

1、query cache 的五個變量（使用show variable like '%query_cache%'語句檢視）

• query_cache_limit：允許 Cache 的單條 Query 結果集的最大容量，預設是1MB，超過此參數設定的 Query 結果集将不會被 Cache
• query_cache_min_res_unit：設定 Query Cache 中每次配置設定記憶體的最小空間大小，也就是每個 Query 的 Cache 最小占用的記憶體空間大小
• query_cache_size：設定 Query Cache 所使用的記憶體大小，預設值為0，大小必須是1024的整數倍，如果不是整數倍，MySQL 會自動調整降低最小量以達到1024的倍數
• query_cache_type：控制 Query Cache 功能的開關，可以設定為0(OFF),1(ON)和2(DEMAND)三種，意義分别如下：

o 0(OFF)：關閉 Query Cache 功能，任何情況下都不會使用 Query Cache

o 1(ON)：開啟 Query Cache 功能，但是當 SELECT 語句中使用的 SQL_NO_CACHE 提示後，将不使用Query Cache

o 2(DEMAND)：開啟 Query Cache 功能，但是隻有當 SELECT 語句中使用了 SQL_CACHE 提示後，才使用 Query Cache

• query_cache_wlock_invalidate：控制當有寫鎖定發生在表上的時刻是否先失效該表相關的 Query Cache，如果設定為 1(TRUE)，則在寫鎖定的同時将失效該表相關的所有 Query Cache，如果設定為0(FALSE)則在鎖定時刻仍然允許讀取該表相關的 Query Cache。

2、query cache處理子查詢

     query cache 是以用戶端請求送出的 Query 為對象來處理的，隻要用戶端請求的是一個 Query，無論這個 Query 是一個簡單的單表查詢還是多表 Join，亦或者是帶有子查詢的複雜 SQL，都被當作成一個 Query，不會被分拆成多個 Query 來進行 Cache。是以，存在子查詢的複雜 Query 也隻會産生一個Cache對象，子查詢不會産生單獨的Cache内容。UNION[ALL] 類型的語句也同樣如此。

3、query cache 的效率

Query Cache 的查找，是在 MySQL 接受到用戶端請求後在對 Query 進行權限驗證之後，SQL 解析之前。當 MySQL 接受到用戶端的SQL後，僅僅隻需要對其進行相應的權限驗證後就會通過 Query Cache 來查找結果，甚至都不需要經過 Optimizer （查詢優化器）子產品進行執行計劃的分析優化，更不許要發生任何存儲引擎的互動，減少了大量的磁盤 IO 和 CPU 運算，是以效率非常高。

4、query cache的失效

為了保證 Query Cache 中的内容與是實際資料絕對一緻，當表中的資料有任何變化，包括新增，修改，删除等，都會使所有引用到該表的 SQL 的 Query Cache 失效。

5、query cache導緻系統整體性能下降

當開啟了 Query Cache 之後，尤其是當我們的 query_cache_type 參數設定為 1 以後，MySQL 會對每個 SELECT 語句都進行 Query Cache 查找，查找操作雖然比較簡單，但仍然也是要消耗一些 CPU 運算資源的。而由于 Query Cache 的失效機制的特性，可能由于表上的資料變化比較頻繁，大量的 Query Cache 頻繁的被失效，是以 Query Cache 的命中率就可能比較低下。是以有些場景下，Query Cache 不僅不能提高效率，反而可能造成負面影響。

6、query cache的幾種狀态參數（使用show status like'%Qcache%'語句檢視）

• Qcache_free_blocks：目前還處于空閑狀态的 Query Cache 中記憶體 Block 數目
• Qcache_free_memory：目前還處于空閑狀态的 Query Cache 記憶體總量
• Qcache_hits：Query Cache 命中次數
• Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次數，也就是沒有命中的次數
• Qcache_lowmem_prunes：當 Query Cache 記憶體容量不夠，需要從中删除老的 Query Cache 以給新的 Cache 對象使用的次數
• Qcache_not_cached：沒有被 Cache 的 SQL 數，包括無法被 Cache 的 SQL 以及由于 query_cache_type 設定的不會被 Cache 的 SQL
• Qcache_queries_in_cache：目前在 Query Cache 中的 SQL 數量
• Qcache_total_blocks：Query Cache 中總的 Block 數量

七、存儲引擎

1、線上交易範疇内的表都統一使用innodb存儲引擎，而不使用myisam的原因

n myisam不支援事務；如果存在myisam與innodb混用的話，事務資料的一緻性存在風險。

n myisam讀寫阻塞，不适用于高并發環境；innodb支援行級鎖。

n myisam不支援線上備份，配置複制時可能需要停機或者長時間鎖表。

n myisam不夠可靠，容易損壞丢失資料。

n 如果兩種引擎混用，會給資源配置設定帶來問題。因為有很多參數和設定需要停庫才能修改，為了減少停庫次數，我們目前都是采用預先最大化配置設定足額記憶體和磁盤空間的方式去做的。而myisam和innodb在記憶體和空間使用上都不一樣，如果兩者混用，那配置設定的時候就很難兩方都照顧到。如果兩者都按比例配置設定，很可能存在資源不能充分利用的情況；且随着業務的變化，需要調整的話，也會存在停機問題。

n 運維需要收集很多的運作狀态資料，innodb有非常多的狀态資料可收集；而對于myisam引擎，能收集到的資料非常有限，不利于我們做監控和容量規劃。

2、檢視存儲引擎

n 檢視支援的存儲引擎

show engines

n 檢視mysql預設的存儲引擎

show variables like "%storage_engine%"

n 檢視某個表用了什麼引擎（在顯示結果參數engine後面的就表示目前用的存儲引擎）

    show create table 表名

八、mysql架構圖

 架構示意簡圖

子產品間的關系圖

九、Mysql中的資料類型

1、數字日期類型

常用的時間存儲格式主要有datetime、date、timestamp。從存儲空間來看timestamp  4個位元組，而其他，date 3個位元組，datetime 8個位元組，而timestamp隻能存儲1970年之後的時間。

2、字元類型

char(M)是靜态長度類型，存放長度以字元數來計算，是以最終存儲長度是基于字元集的，latin1最大存儲長度為255位元組，gbk為510位元組。mysql5.0.3以前，如果定義的M值超過255，mysql會自動将char類型轉換為可以存入對應資料量的text類型，而mysql5.0.3以後，是以超過255的定義都會直接拒絕，不再自動轉換。

varchar(M)屬于動态存儲長度類型，僅存儲占實際存儲資料的長度。在mysql5.0.3以前varchar最大隻能存放255個字元，占用存儲空間的實際大小與字元集有關。從5.0.3開始，varchar的最大存儲現在已經改為位元組數。而且不再有單個字段的限制，而是除text和blog類型字段外單條記錄最大不超過65536bytes。不過M仍然表示字元數，當實際資料在255位元組之内時，會使用1個位元組來存放實際長度，而大于255位元組時，則需要使用2位元組來存放。