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

目錄

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 distinct
select...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）、正規表示式

mysql也可以使用正規表示式來作為過慮條件

例：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;

14）資料型別轉換（CAST,CONVERT)

CAST()   和CONVERT()   函式可用來獲取一個型別的值，併產生另一個型別的值;

格式：CAST(expr   AS   type),   CONVERT(expr,type)   ,   CONVERT(expr   USING   transcoding_name)  

可相互轉換的型別有：BINARY,CHAR,DATE,DATETIME,DECIMAL,INTEGER,TIME

15）表名和欄位名都可以用as來設定別名，且as關鍵字可以省略，但通常不建議省略。

 

 

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）資料庫備份：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）永遠不要信任使用者輸入，對使用者輸入進行校驗，或使用正規表示式，限制長度，對單引號和雙"-"號進行轉換等；

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';顯示檢視狀態；