mysql 拾遺提高(函數、事務、索引)

標籤：資料庫連接   .class   xpl   封裝   date()   fine   指定   管理   l命令   

目錄

1、tips

2、事務（transaction）

3、索引(index)

4、資料庫的匯出和備份

5、函數

6、防SQL注入

7、使用Explain分析SQL語句

8、視圖（view）

 

1、tips

1)、資料庫不區分大小寫；

2)、插入新資料時忽略重複資料，可在INSERT後加關鍵字IGNORE即可

3)、通過UNION操作符來串連兩個以上的SELECT語句結果組合到一個結果到同一個集合中；

格式：

select... from... where...union distinctselect...from...where...

其中distinct是可選的，表示會重複資料刪除項，是預設值；也可使用all來保留所有的資料；

4）、拼音排序

如果漢字使用的是GBK編碼，則可直接對某一項進行排序；如果使用的UTF-8，還需要進行轉碼，

例：select * from tablename order by convert(titlename using gbk);

5）、在group by...後邊加with rollup可使資料在分組的基礎上再進資料列彙總函式操作；

例：select name as 名字,sum(times) as 次數 from tablename group by name with rollup;可在對名字分組基礎上再求次數的和，最後還有一個總和；

分組後相同項此次的統計不需要使用此關鍵詞,使用count(*)即可；

例：select name as 名字 count(*) from tablename group by name;

6）、Regex

mysql也可以使用Regex來作為過慮條件

例：select * from tablename where name REGEXP ‘^s‘;  (查詢以s開頭的名字)

7）字串的截取

select left(string,length)           從左邊開始截取string的前length個字元，left可換用right來從右邊開始截取；

select substring(string,index)      從左開始截取string從index位開始到結束的字串（包括index位，且從1開始計數），如果index為負數則從右向左的截取；

select substring_index(string,reg,index)     使用同上，reg表示的匹配原則；

select CONCAT(string1,string2,...)連接字串，但如果其中有任意一個為null，則最後結果為null;

8）條件陳述式

if(bool,value1,value2)    當bool為true時使用value1,否則value2; 例：select if(2=1,‘對‘,‘錯‘) as status; 結果為‘錯’；

ifnull(value1,value2)      如果value1為null則使用value2;

nullif(value1,value2)      如果value1和value2相同時結果為null，否則結果為value1;

coalesce(tablecolumn1,tablecolumn2,...,‘name‘)

意為如果tablecolumn1為null則使用tablecolumn2,依次順推，‘name‘為一個固定的預設值；

select    case [columnName]    when condition1 then result1    when condition2 then result2    ...    end    as ‘別名‘    from tablename;

9）、對於NULL值的處理

NULL值不能通過=或!=來比較獲得結果，尋找值為null的需要使用IS NULL,反之使用IS NOT NULL;<=>符號用於比較的兩個值都為NULL時返回true;                 

10）暫存資料表

主要用於儲存一些臨時資料，暫存資料表在當前串連可見，一旦斷開mysql串連則會自動銷毀；建立暫存資料表只需要在CREATE後加一個TEMPRARY欄位，其他文法與建立表格是一樣的；

11）SHOW CREATE TABLE tablename      查看錶的建立語句；

12）表格的完整複製

CREATE TABLE newtable LIKE oldtable;    #複製表的結構，也可以使用CREATE TABLE newtable SELECT *FROM oldtable WHERE 1=2;INSERT INTO newtable SELECT *FROM oldtable;

13)自增值序列重排：

原理：先刪除此列再建立自增列（但是在處理過程中如果有新增資料，可能會使表資料變亂）

ALTER TABLE tablename DROP id;ALTER TABLE tablename ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,ADD PRIMARTY KEY (id);

設定自增序號開始值：

ALTER TABLE tablename  AUTO_INCREMENT = 50; 此命令用於已經建好的表

或者可以在建立表的同時設定，在create table t(...)engine =innodb auto_increment=50 charset=utf8;

 

2、事務（transaction）

>事務主要用於處理操作量大，複雜度高的資料；只有使用了lnnodb資料庫引擎的資料庫才支援事務；

>事務是用來管理insert,update,delete等修改資料庫的語句的,必須滿足四個條件：

       原子性：一個事務中所有操作要麼全部完成，要麼全部不完成；

       一致性：在事務開始前和結束後，資料庫的完整性沒有被破壞；

       隔離性：資料庫允許多個並發事務同時進行；

       持久性；交易處理結束後，對資料庫的修改就是永久的；

格式：

begin;expression;commit;#確認並提交執行

事務控制語句：

    begin/start transaction   顯示的開啟一個事務；    commit/commit work        提交事務；    rollback/rollback work    復原事務，會結束事務，並撤銷進行中的所有未提交的修改；    savepoint identifier      建立一個儲存點identifier,一個事務中可以有多個儲存點；    release savepoint identifier    刪除一個儲存點；    rollback to identifier    復原到一個儲存點；    set transaction           設定交易隔離等級，值有read uncommitted,read committed,repeatable read,serializable    set autocommit = 0        禁止自動認可，如果為1則自動認可；

    

3、索引(index)

>索引可以很大的提高mysql檢索速度；索引也是一張表，儲存了主鍵與索引欄位，並指向實體表的記錄；會佔用磁碟空間，會降低表的更新速度；

>索引分單列索引和複合式索引，一個表可以有多個單列索引；複合式索引即一個索引包含表中的多個列；只對那些將應用在查詢條件（一般where後）的列建立索引；

>顯示一個表的索引資訊:SHOW INDEX FROM tablename; \G              最後的\G是可選的，用于格式化輸出資訊；

>查詢索引使用方式:SHOW STATUS LIKE "handler_read%";結果中的handler_read_rnd_next值越高說明效率越低；（為什麼越查越大。。。）

>建立索引：

    1)CREATE INDEX indexName ON mytable(username(length)); 如果是char,varchar類型length可小於實際長度，blob或text必須指定length。用UNIQUE INDEX來建立唯一索引(可以為空白）；    2)建立表時建立；CREATE TABLE mytable(ID INT NOT NULL,username CHAR(16) NOT NULL, INDEX [indexName] (username(length)));如果唯一，則使用UNIQUE替換INDEX;    3)ALTER TABLE tablename ADD INDEX indexName(columnName); 添加普通索引；    4)ALTER TABLE tablename ADD UNIQUE [indexName] (username(length)); 添加唯一索引，可為NULL;    5)ALTER TABLE tablename ADD PRIMARTY KEY (columnName);    添加主鍵；唯一且不為空白；添加為主鍵前需要先確認它不為空白:ALTER TABLE tablename MODIFY columnName NOT NULL;    6)ALTER TABLE tablename ADD FULLETXT indexName (columnName);    指定索引為FULLTEXT,用於全文索引；

>刪除索引     DROP INDEX [indexname] ON mytable;

                     ALTER TABLE tablename DROP PRIMARTY KEY;      刪除主鍵；刪除索引時需要知道索引名；

>如果like的查詢條件以%開始，或者where條件沒使用=號或條件給的資料類型與欄位類型不一致時，不會使用索引；

>注意：

1)為維度度的列建立索引（列的重複值越少維度越高）；

2）為where,on,group by,order by中的條件建立索引；

3）對較小資料列使用索引；

4）為較長字串使用首碼索引（即限制索引表中值的長度，只取前一部分）；

5）使用複合式索引可以減少檔案索引大小，速度會優於多個單列索引；

6）只為操作頻繁的列建立索引；

 

4、資料庫的匯出和備份

1）資料匯出到檔案

SELECT ... INTO OUTFILE ‘fileAddressAndName‘ #以下兩句都是用於設定輸出格式的，可選；    FIELDS TERMINATED BY ‘,‘ OPTIONALLY ENCLOSED BY ‘"‘    LINES TERMINATED BY ‘\n‘;

匯出檔案存在於伺服器主機，且檔案名稱不能是一個已經存在的檔案

2）從檔案匯入到資料庫（為匯出的逆操作）

LOAD DATA LOCAL INFILE ‘filename‘ INTO TABLE tablename #預設檔案結構和表結構順序一致，如果不一致需要使用tablename(columnName1,...)的方式來指定檔案列與倉庫中列相對應；FIELDS TERMINATED BY ‘:‘LINES TERMINATED BY ‘\n‘;

 或者也可以使用mysqlimport語句來匯入；

3）Database Backup：mysqldump -u root -p --no-create-info --table=/foldername filename

備份串連的所有資料庫：mysqldump -u root -p --all-database > dumpname.txt 其中 --all-database也可為指定倉庫名，來備份指定倉庫；

將遠程主機資料備份到本地：mysqldump -h remoteHost.com -P port -u root -p databaseName > dumpname.txt;

4）恢複備份：mysql -u root -p databaseName < dumpname.txt

 

5、函數

1>常用函數：

DATE_ADD(columnName,INTERVAL time type)    向日期列添加指定的時間間隔，time為數字，type為單位，可為MICROSECOND,SECOND,MINUTE,HOUR,DAY,WEEK,MONTH,QUARTER,YEAR,SECOND_MICROSECOND,MINUTE_MICROSECOND,MINUTE_SECOND,HOUR_MICROSECOND,HOUR_SECOND,HOUR_MINUTE,DAY_MICROSECOND,DAY_SECOND,DAY_MINUTE,DAY_HOURE,YEAR_MONTH;DATE_SUB(columnName,INTERVAL time type)    向日期列減去指定的時間間隔；DATEDIFF(date1,date2)                      返回兩個日期之間的天數；DATE_FORMAT(columnName,format)             用於定義顯示日期/時間的格式，format內容為%a,%b,...等多種不同格式，具體待查；NOW()                    返回當前的日期和時間；CURDATE()                返回當前日期；CURTIME()                返回目前時間；DATE(columnName)         返回日期/時間運算式的日期部分；EXIRACT(type FROM columnName)              返回日期/時間運算式的指定部分，type可用所有時間type

2>擷取伺服器資料：

select version()     伺服器版本資訊；select database()    當前資料庫名；select user()        目前使用者名；show status          伺服器狀態；show variables       伺服器組態變數；

 

6、防SQL注入

SQL注入，即通過把SQL命令插入到Web表單提交或輸入欄位名或頁面請求的查詢字串，以欺騙伺服器執行惡意SQL命令；

1）永遠不要信任使用者輸入，對使用者輸入進行校正，或使用Regex，限制長度，對單引號和雙"-"號進行轉換等；

2）永遠不要使用動態拼裝sql，可以使用參數化的sql或直接使用預存程序進行資料查詢存取；

3）永遠不要使用管理員權限的資料庫連接，為每個應用使用單獨的許可權有限的串連資料庫；

4）不要把機密資訊直接存放，加密或者hash掉密碼和敏感資訊；

5）應用的異常資訊應該給出儘可能少的提示，最好使用自訂的錯誤資訊對原始錯誤資訊進行封裝；

6）可採用輔助軟體或網站平台來檢測是否有SQL注入，如軟體有jsky;

 

7、使用Explain分析SQL語句

使用方法即在正常的SQL語句前加上 EXPLAIN 關鍵字，會將這條SQL語句的分析結果展示出來（sql語句在執行前都會由分析器進行分析，以判斷語句的可行性的）

結果分析：

select_type        查詢類型，有簡單查詢、聯集查詢、子查詢等；table              使用的資料表格；type               串連使用的類型，結果從優到差：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all;const表示一次就得到結果，all表示全表掃描了；好的語句要最佳化到rang,ref層級；possible_keys      顯示能使用哪個索引在該表中找到結果，如果為空白，則沒有相關索引；key                執行此語句實際使用到的索引；key_len            最長索引寬度，越短越好；ref                顯示哪個欄位或常數與key一起被使用；rows               表示遍曆了多少條資料；extra              執行狀態說明；

 

8、視圖（view）

視圖是一個虛擬表，內容是由其他表的資料群組成的；視圖只能查看不能修改；使用視圖可以隱藏一些資料，也可以簡化使用者操作；

建立視圖文法：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}]VIEW [db_name]view_name [(column_list)]AS select_statement [WITH [CASCADED|LOCAL] CHECK OPTION]

樣本：

CREATE VIEW query_view(id,name,class) AS SELECT B.u_id,B.u_name,A.class_name    FROM t_class AS A INNER JOIN t_name AS B ON A.u_id=b.u_id;  #建立視圖
DROP VIEW IF EXISTS query_view;     刪除視圖SELECT * FROM query_view　　　　　　  使用一個視圖查詢資料DESCRIBE query_view       　　　　　  查看視圖結構SHOW TABLE STATUS LIKE ‘query_view‘;顯示檢視狀態；

 

mysql 拾遺提高(函數、事務、索引)