mysql初識（一）

to_be_Dba發表於2013-06-24
MySql
mysql 常用工具：
phpmyadmin
mysql workbench
 
一、mysql的儲存引擎
mysql支援多種儲存引擎。
檢視方法：show engines;
語句的結尾可以使用“；”，也可以用“\g”或“\G”  用\G結果更美觀
1.InnoDB引擎：
1）支援自動增長列。自動增長列值不能為空，值唯一，且必須是表的主鍵。
插入資料時該列不輸入值、輸入0或NULL，插入的值都是自動增長後的值；
如果插入某個確定值，且該值在以前沒出現過，就直接插入。
2）支援外來鍵。刪除、更新父表時，子表必須有相應變化。
InnoDB引擎中建立的表的表結構儲存在.frm檔案中，資料和索引儲存在innodb_data_home_dir和innodb_data_file_path表空間中。
InnoDB提供了良好的事務管理、崩潰修復能力和併發控制。但讀寫效率稍差，佔用的資料空間相對比較大。
使用環境：
更新密集的表 適合多重併發的更新請求
事務 是管理敏感資料（金融、使用者資訊等） 等的必須軟體
自動災難恢復 雖然MyISAM表也能提供災難修復，但其過程要長很多
2.MyISAM引擎
是基於ISAM儲存引擎發展起來的。
MyISAM儲存引擎的表儲存成3個檔案。檔名與表名相同，副檔名包括frm、myd、myi
frm儲存表結構
myd儲存資料
myi儲存索引
MyISAM引擎的表支援3中儲存格式，靜態型、動態型和壓縮型。
（1）MyISAM靜態型
如果表列大小都是靜態的（未使用xBLOB、xTEXT、varchar），mysql就是用靜態MyISAM格式。這種表效能高，維護和訪問以預定義格式儲存的資料時開銷很低，但分配的空間大。
（2）MyISAM動態型
有表列是動態的，mysql自動使用動態格式。動態表佔用空間比靜態表小，但效能差。因為某個欄位內容的修改可能導致位置移動，並導致碎片產生。隨著資料集中的碎片增加，訪問效能下降。
修復方法為：
1）儘可能使用靜態資料型別
2）經常使用optimize table語句整理碎片
（3）MyISAM壓縮型
建立在整個應用程式生命週期內都只讀的表。可以使用myisampack工具將其轉換為MyISAM壓縮表。
MyISAM引擎佔用空間小，速度快，但不支援事務完整性和併發性
3.MEMORY引擎
是Mysql中特殊的引擎。使用儲存在記憶體中的內容來建立表，所有資料放在記憶體中。
每個基於memory引擎的表實際對應一個磁碟檔案。檔名為表名.frm，儲存結構。內容存在記憶體中。伺服器需要足夠記憶體來維持memory儲存引擎的表的使用。不需要使用的表應該釋放掉。
memory引擎的索引型別預設是hash索引，比B樹索引塊。
該引擎通常很少用到。重啟機器或關機會導致資料消失。基於memory儲存引擎的表生命週期較短，通常是一次性的。
建立該引擎的出發點是速度，但memory表大小收到max_rows和max_heap_table_size限制，max_rows在建表時指定，max_heap_table_size預設16MB。
memory表不支援varchar、blob、text資料型別，必須是定長的。
使用場景為：暫時性的表、如果突然丟失也沒關係的表

4.選擇儲存引擎
InnoDB引擎：用於事務處理應用程式。對事務要求比較高、要求併發或者資料更新頻繁，應該選擇InnoDB引擎。
MyISAM引擎：管理非事務表，提供高速儲存和檢索，及全文搜尋功能。用於插入新資料和讀出記錄效率高；應用完整性、併發性要求低時也可以選擇。
MEMORY引擎：所有資料在記憶體，速度快，安全性差。適合快速讀寫、安全性要求低的場景。表不能太大。
 

二、資料型別
1.數字型別
根據長度選擇，按範圍和所佔位元組數：
TINYINT、BIT、BOOL所佔位元組數分別為1、2、3、4，取值範圍就是2的N次方除以2，N為8、16、24、32。
浮點型包括FLOAT\DOUBLE\DECIMAL
2.字串型別
普通型別:char varchar  0~255
可變型別：text blob 分別適合於長文字和二進位制資料
特殊型別：set enum
3.日期和時間
datetime
date
timestamp
time
year

三、基本操作
建庫：create database xxxx;
檢視庫：show databases；
選擇庫：use databae；
刪庫：drop database xxxx；
建表：create [temporary ] table [if not exists] xxx
[(列名 屬性，……)]  基本和oracle相同
檢視錶結構： show [full] columns from 表名 [from  資料庫名];
  show [full] columns from 資料庫名.表名;
  describe 表名；
  describe 表名 列名；
修改結構：alter table xxx
注：使用alter修改表列，必須將資料全部刪除後才可以執行（不這樣會報錯嗎？）
重新命名錶：rename table xxx to xxxx；
刪表：drop table [if exists] xxx； 刪除不存在的表會報錯
注：
1)mysql的group by不要求所選的列必須在group by子句中，如：
select bookname,avg(price),type from tb_book group by type;
2)order by 可以對結構進行限制，如：
select * from t order by id desc limit 3;
3)用limit限制查詢結果，還可以從查詢結果的中間部分取值：
select * form. t where id limit 1,4; 表示從第二條開始查詢四條記錄。

在查詢語句中，mysql的正規表示式與oracle不太一樣。但符號含義一樣
如：
^ 表示以xxxxx開頭
$ 表示以xxxxx結尾
. 表示任意字元，包括回車和換行
[字符集合] 匹配“字符集合”中的任意一個字元
[^字符集合] 匹配“字符集合”以外的任意一個字元
A|B|C 匹配A\B\C中的任意一個字串
* 匹配多個該符號前面的字元，包括1、0
+ 匹配多個該符號前面的字元，包括1
字串{N} 匹配字串出現N次
字串{M,N} 匹配字串出現最少M次，最多N次

四、函式
1.字串函式
當前日期\時間\日期和時間：curdate()、curtime()、now()
2.系統資訊函式
version()資料庫版本
connection_id() 伺服器連線數
database(),schema() 當前資料庫名
user(),system_user(),session_user(),current_user() 當前使用者
charset(str) 字串str的字符集
collation(str) 獲取字串str的字元排列方式
last_insert_id() 最近生成的auto_increment的值
3.加密函式
password(str)  加密後資料是不可逆的
md5(str) 用於普通資料加密
encode(str,pswd_Str)  用pswd_Str為str加密。結果是二進位制數，需要blob型別欄位儲存
decode(crypt_str,pswd_Str) 用pswd_str為crypt_str加密。crypt_str是encode函式加密後的二進位制資料

五、查詢優化
1.索引
分析查詢優化： explain 資料庫查詢語句
mysql建立索引與oracle基本一樣。
索引欄位上使用like '%xxx'的形式查詢也不會用到索引。
（多列索引必須使用前導列才可以用到嗎？？？？）
使用or關鍵字的查詢，需要or兩邊的條件都有索引，才可以用到索引
2.分析查詢語句
explain select xxx；
describe select xxx；（可縮寫為desc）

3.查詢快取記憶體
使用者通過select查詢資料時，結果集被存到特殊的快取記憶體中。首次查詢後再次做相同查詢，mysql可以從快取記憶體檢索結果。
檢視是否開啟快取記憶體：
show variables like '%query_cache%';
顯示的結果中
have_query_cache表示伺服器是否配置了快取記憶體；
query_cache_size 分配給告訴快取的空間（MB）
query_cache_type 快取記憶體開啟狀態。0或OFF表示快取記憶體關閉；1或ON表示快取記憶體開啟了；2或DEMAND表示根據需要執行的是否為sql_cache選項的select語句提供查詢快取記憶體。
使用快取記憶體的例子：
select sql_cache * from xxxx;
不適用快取記憶體可以應用關鍵字sql_no_cache
4.優化多表查詢
巢狀語句可以採用以下的select語句進行優化：
select @avgage:=avg(age) from studentinfo;
select distinct name from studentinfo where age>@avgage;
也就是將某個查詢結果非給臨時變數，並在以後的查詢中呼叫該變數。
5.臨時表
mysql的臨時表是針對單一會話的。會話結束，臨時表就被刪除了。臨時表只儲存在記憶體中。

六、mysql索引
所有儲存引擎最少支援每個表16個索引。
mysql索引分為普通索引、唯一索引（union）、全文索引（fulltext）、單列索引、多列索引和空間索引等。
空間索引只能建立在空間資料型別上，只有MyISAM引擎支援空間檢索，且檢索欄位不能為空。
只有MyISAM引擎支援全文索引。
在建表語句中包含了索引建立語句。
create table t(
id int(11) auto_increment primary key not null,
tel varchar(50),
index tel_num(tel(20))
);
表示在tel欄位建立欄位長度為20的索引，名稱為tel_num
要檢視錶t的建表語句，可以執行如：show create table t；

在已建立的表上建立索引：
create [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
ON table_name(屬性 [(length)][ASC|DESC]) ;
或
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name(屬性 [(length)][ASC|DESC]) ;
刪除索引：
drop index index_name on table_name;
 
七、檢視
1.檢視是否有許可權建立檢視
select selece_priv,create_view_priv from mysql.user where user='使用者名稱';
建立語句：
CREATE [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
 VIEW XXX
 AS SELECT ……
 [WITH [CASCADED|LOCAL] CHECK OPTION];
ALGORITHM表示檢視選擇的演算法
with check option表示更新檢視時保證在該檢視許可權範圍內
注意：
如果加了or replace選項，需要drop view許可權；
select語句不能包含from子句中的子查詢、引用系統或使用者變數、引用預處理語句引數；

2.檢視檢視
desc 檢視名；
show table status like '檢視名'; 可以看到儲存引擎、資料長度等
show create view； 顯示檢視的建立語句（定義）
3.修改檢視
create or replace view xxx  as select ……
alter view修改檢視的定義
4.更新檢視
update xxxx ……；
其實就是對基表的更新
更新檢視也會有很多限制，最好對基表進行操作，而不是檢視。
5.刪除檢視
drop view if exists xxx [restrict|cascade];

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-764637/，如需轉載，請註明出處，否則將追究法律責任。

相關文章