當前位置:學問谷 >

行業範例 >資料庫作業系統 >

Mysql常用技巧總結

Mysql常用技巧總結

不管學習任何語言,都很難找出一個完全適合自己的書,更不用說資料庫這種綜合性教強的學科了。下面是小編分享的Mysql常用技巧,歡迎大家閱讀學習!

Mysql常用技巧總結

  【Mysql常用技巧總結

  一、查詢不同表中同名欄位(表連線查詢條件神器)

use information_schema;

select * from columns where column_name='欄位名';

  二、查詢記錄總數

SELECT SQL_CALC_FOUND_ROWS * FROM TABLE WHERE 1=1;

即可得出總資料行數

SET @RowCount=found_rows();

  三、儲存過程資料查詢分頁

預定義變數:

/*錯誤程式碼*/

SET @RetCode='1';

/*錯誤提示*/

SET @RetVal='1';

/*返回記錄行數*/

SET @RowCount='0';

/*頁碼*/

SET @PageCount='0';

/*每頁顯示數*/

SET @CurrentItem=0;

/*每頁顯示數*/

SET @PageSize=arg_page_size;

/*頁碼*/

SET @PageCurrent=arg_page_current;

SET @SqlExe='select * from table where 1=1';

入參:arg_page_size int,arg_page_current int

IF(@PageCurrent IS NOT NULL && @PageSize IS NOT NULL) THEN

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

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

ELSE

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

END IF;

prepare stmt from @SqlExe;

execute stmt;

deallocate prepare stmt;

IF(@RowCount IS NOT NULL && @RowCount != 0) THEN

IF(@PageSize is null)then

SET @PageSize= @RowCount;

END IF;

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

ELSE

SET @RowCount = 0;

SET @PageCount = 1;

END IF;

  四、字串相關操作

1、從左開始擷取字串

left(str, length)

說明:left(被擷取欄位,擷取長度)

例:select left(content,200)as abstract from my_content_t

2、從右開始擷取字串

right(str, length)

說明:right(被擷取欄位,擷取長度)

例:select right(content,200)as abstract from my_content_t

3、擷取字串

substring(str, pos)

substring(str, pos, length)

說明:substring(被擷取欄位,從第幾位開始擷取)

substring(被擷取欄位,從第幾位開始擷取,擷取長度)

例:select substring(content,5)as abstract from my_content_t

select substring(content,5,200)as abstract from my_content_t

(注:如果位數是負數 如-5則是從後倒數位數,到字串結束或擷取的長度)

4、按關鍵字擷取字串

substring_index(str,delim,count)

說明:substring_index(被擷取欄位,關鍵字,關鍵字出現的'次數)

例:select substring_index("",".",2)as abstract from my_content_t

結果:

(注:如果關鍵字出現的次數是負數 如-2 則是從後倒數,到字串結束)

函式簡介:

SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)

不帶有len 引數的格式從字串str返回一個子字串,起始於位置pos。帶有len引數的格式從字串str返回一個長度同len字元相同的子字串,起始於位置pos。 使用FROM的格式為標準SQL 語法。也可能對pos使用一個負值。假若這樣,則子字串的位置起始於字串結尾的pos字元,而不是字串的開頭位置。在以下格式的函式中可以對pos使用一個負值。

MySQL字串大小寫轉換

deMySQL 字串大小寫轉化函式有兩對: lower(), uppper()和 lcase(), ucase()

mysql>select lower('DDD');

結果:ddd

mysql> select upper('ddd');

結果:DDD

mysql> select lcase('DDD');

結果:ddd

mysql> select ucase('ddd');

結果:DDD

通常情況下,我選擇 lower(), upper() 來轉換字串大小寫,因為這和其他資料庫中函式相相容。

  五、時間獲取相關操作

declare _LastWeekStartTime date; -- 上週開始時間

declare _LastWeekEndTime date; -- 上週結束時間

-- 因系統為星期日為每星期第一天 所以需要減1

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

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

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

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

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

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

set _LastWeekEndTime = ( date_add(subdate( @C,date_format( @C,'%w')-1), interval 6 day));

-- 獲取當前日期是星期幾 1為週日2為週一 以此類推

SET @nowdate=(select DAYOFWEEK(NOW()));

SET @NowD=case @nowdate

when 1 then '星期日'

when 2 then '星期一'

when 3 then '星期二'

when 4 then '星期三'

when 5 then '星期四'

when 6 then '星期五'

when 7 then '星期六'

end;

-- 週日時間需要重新計算

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

SET @curmon=(SELECT SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')+6));

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

else

-- 開始時間(週一)

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

-- 結束時間(週日)

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

  六、排序技巧

CREATE TABLE test (

id INT PRIMARY KEY not null auto_increment,

val nvarchar (50) NOT NULL

);

INSERT INTO test(val) VALUES ('0');

INSERT INTO test(val) VALUES ('1');

INSERT INTO test(val) VALUES ('2');

INSERT INTO test(val) VALUES ('3');

SELECT * from test;

-- 根據 id為 2 0 1 3的優先順序自定義排序

SELECT * FROM test ORDER BY instr('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函式(摘自百度百科instr函式語法)

instr( string1, string2, start_position,nth_appearance )

string1:源字串,要在此字串中查詢。

string2:要在string1中查詢的字串 。

start_position:代表string1 的哪個位置開始查詢。此引數可選,如果省略預設為1. 字串索引從1開始。如果此引數為正,從左到右開始檢索,如果此引數為負,從右到左檢索,返回要查詢的字串在源字串中的開始索引。

nth_appearance:代表要查詢第幾次出現的string2. 此引數可選,如果省略,預設為 1.如果為負數系統會報錯。

注意:

位置索引號從1開始。

如果String2在String1中沒有找到,instr函式返回0。

示例:

SELECT instr('syranmo','s') FROM dual; -- 返回 1

SELECT instr('syranmo','ra') FROM dual; -- 返回 3

SELECT instr('syran mo','at',1,2) FROM dual; -- 返回 0

標籤: MYSQL
  • 文章版權屬於文章作者所有,轉載請註明 https://xuewengu.com/zh-tw/flhy/shujuku/yoyvp.html