Mysql常用技巧總結(jié)

1/1Mysql常用技巧總結(jié)

不管學(xué)習(xí)任何語言，都很難找出一個(gè)完全適合自己的書，更不用說數(shù)據(jù)庫這種綜合性教強(qiáng)的學(xué)科了。下面是xx分享的Mysql常用技巧，歡迎大家閱讀學(xué)習(xí)！

一、查詢不同表中同名字段(表連接查詢條件神器)

useinformation_schema;

select*fromcolumnswherecolumn_name='字段名';

二、查詢記錄總數(shù)

SELECTSQL_CALC_FOUND_ROWS*FROMTABLEWHERE1=1;

即可得出總數(shù)據(jù)行數(shù)

SET@RowCount=found_rows();

三、存儲(chǔ)過程數(shù)據(jù)查詢分頁

預(yù)定義變量：

/*錯(cuò)誤代碼*/

SET@RetCode='1';

/*錯(cuò)誤提示*/

SET@RetVal='1';

/*返回記錄行數(shù)*/

SET@RowCount='0';

/*頁碼*/

SET@PageCount='0';

/*每頁顯示數(shù)*/

SET@CurrentItem=0;

/*每頁顯示數(shù)*/

SET@PageSize=arg_page_size;

/*頁碼*/

SET@PageCurrent=arg_page_current;

SET@SqlExe='select*fromtablewhere1=1';

入?yún)ⅲ篴rg_page_sizeint,arg_page_currentint

IF(@PageCurrentISNOTNULL&&@PageSizeISNOTNULL)THEN

SET@CurrentItem=(@PageCurrent-1)*@PageSize;

SET@SqlExe=CONCAT(@SqlExe,'LIMIT',@PageSize,'OFFSET',@CurrentItem);

ELSE

SET@SqlExe=CONCAT(@SqlExe,'');

ENDIF;

preparestmtfrom@SqlExe;

executestmt;

deallocatepreparestmt;

IF(@RowCountISNOTNULL&&@RowCount!=0)THEN

IF(@PageSizeisnull)then

SET@PageSize=@RowCount;

ENDIF;

SET@PageCount=ceiling(@RowCount/@PageSize);

ELSE

SET@RowCount=0;

SET@PageCount=1;

ENDIF;

四、字符串相關(guān)操作

1、從左開始截取字符串

left(str,length)

說明：left(被截取字段，截取長度)

例：selectleft(content,200)asabstractfrommy_content_t

2、從右開始截取字符串

right(str,length)

說明：right(被截取字段，截取長度)

例：selectright(content,200)asabstractfrommy_content_t

3、截取字符串

substring(str,pos)

substring(str,pos,length)

說明：substring(被截取字段，從第幾位開始截取)

substring(被截取字段，從第幾位開始截取，截取長度)

例：selectsubstring(content,5)asabstractfrommy_content_t

selectsubstring(content,5,200)asabstractfrommy_content_t

(注：如果位數(shù)是負(fù)數(shù)如-5則是從后倒數(shù)位數(shù)，到字符串結(jié)束或截取的長度)

4、按關(guān)鍵字截取字符串

substring_index(str,delim,count)

說明：substring_index(被截取字段，關(guān)鍵字，關(guān)鍵字出現(xiàn)的次數(shù))

例：selectsubstring_index(""，"."，2)asabstractfrommy_content_t

結(jié)果：

(注：如果關(guān)鍵字出現(xiàn)的次數(shù)是負(fù)數(shù)如-2則是從后倒數(shù)，到字符串結(jié)束)

函數(shù)簡介：

SUBSTRING(str,pos),SUBSTRING(strFROMpos)SUBSTRING(str,pos,len),SUBSTRING(strFROMposFORlen)

不帶有l(wèi)en參數(shù)的.格式從字符串str返回一個(gè)子字符串，起始于位置pos。帶有l(wèi)en參數(shù)的格式從字符串str返回一個(gè)長度同len字符相同的子字符串，起始于位置pos。使用FROM的格式為標(biāo)準(zhǔn)SQL語法。也可能對pos使用一個(gè)負(fù)值。假若這樣，則子字符串的位置起始于字符串結(jié)尾的pos字符，而不是字符串的開頭位置。在以下格式的函數(shù)中可以對pos使用一個(gè)負(fù)值。

MySQL字符串大小寫轉(zhuǎn)換

deMySQL字符串大小寫轉(zhuǎn)化函數(shù)有兩對：lower(),uppper()和lcase(),ucase()

mysql>selectlower('DDD');

結(jié)果：ddd

mysql>selectupper('ddd');

結(jié)果：DDD

mysql>selectlcase('DDD');

結(jié)果：ddd

mysql>selectucase('ddd');

結(jié)果：DDD

通常情況下，我選擇lower(),upper()來轉(zhuǎn)換字符串大小寫，因?yàn)檫@和其他數(shù)據(jù)庫中函數(shù)相兼容。

五、時(shí)間獲取相關(guān)操作

declare_LastWeekStartTimedate;--上周開始時(shí)間

declare_LastWeekEndTimedate;--上周結(jié)束時(shí)間

--因系統(tǒng)為星期日為每星期第一天所以需要減1

set@A=date_add(curdate(),interval-1day);

--算出本周星期一的日期

set@B=subdate(@A,date_format(@A,'%w')-1);

--因系統(tǒng)為星期日為每星期第一天所以需要減2獲取上星期最后一天(上星期星期六)

set@C=date_add(@B,interval-2day);

set_LastWeekStartTime=(subdate(@C,date_format(@C,'%w')-1));

set_LastWeekEndTime=(date_add(subdate(@C,date_format(@C,'%w')-1),interval6day));

--獲取當(dāng)前日期是星期幾1為周日2為周一以此類推

SET@nowdate=(selectDAYOFWEEK(NOW()));

SET@NowD=case@nowdate

when1then'星期日'

when2then'星期一'

when3then'星期二'

when4then'星期三'

when5then'星期四'

when6then'星期五'

when7then'星期六'

end;

--周日時(shí)間需要重新計(jì)算

if(DATE_FORMAT(CURDATE(),'%w')=0)then

SET@curmon=(SELECTSUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')6));

SET@cursun=(SELECTSUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')));

else

--開始時(shí)間(周一)

SET@curmon=(SELECTSUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1));

--結(jié)束時(shí)間(周日)

SET@cursun=(SELECTSUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-7));

六、排序技巧

CREATETABLEtest(

idINTPRIMARYKEYnotnullauto_increment,

valnvarchar(50)NOTNULL

);

INSERTINTOtest(val)VALUES('0');

INSERTINTOtest(val)VALUES('1');

INSERTINTOtest(val)VALUES('2');

INSERTINTOtest(val)VALUES('3');

SELECT*fromtest;

--根據(jù)id為2013的優(yōu)先級自定義排序

SELECT*FROMtestORDERBYinstr('2013',id);

--原理樣例

SET@ORDBYD='2013';

SELECT'2',instr(@ORDBYD,'2')as`INDEX`

union

SELECT'0',instr(@ORDBYD,'0')as`INDEX`

union

SELECT'1',instr(@ORDBYD,'1')as`INDEX`

union

SELECT'3',instr(@ORDBYD,'3')as`INDEX`;

instr函數(shù)(摘自百度百科instr函數(shù)語法)

instr(string1,string2,start_position,nth_appearance)

string1:源字符串，要在此字符串中查找。

string2:要在string1中查找的字符串。

start_position:代表string1的哪個(gè)位置開始查找。此參數(shù)可選，如果省略默認(rèn)為1.字符串索引從1開始。如果此參數(shù)為正，從左到右開始檢索，如果此參數(shù)為負(fù)，從右到左檢索，返回要查找的字符串在源字符串中的開始索引。

nth_appearance:代表要查找第幾次出現(xiàn)的string2.此參數(shù)可選，如