MySQL数据库常用操作技巧总结

本文实例总结了MySQL数据库常用操作技巧。分享给大家供大家参考，具体如下：

一、查询不同表中同名字段(表连接查询条件神器)

?

1 2	use information_schema; select * from columns where column_name= '字段名' ;

二、查询记录总数

?

1	SELECT SQL_CALC_FOUND_ROWS * FROM TABLE WHERE 1=1;

即可得出总数据行数

?

1	SET @RowCount=found_rows();

三、存储过程数据查询分页

预定义变量：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

/*错误代码*/ 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(被截取字段，截取长度)

例：

?

1	select left (content,200) as abstract from my_content_t

2、从右开始截取字符串

right(str, length)

说明：right(被截取字段，截取长度)

例：

?

1	select right (content,200) as abstract from my_content_t

3、截取字符串

substring(str, pos)

substring(str, pos, length)

说明：

substring(被截取字段，从第几位开始截取)

substring(被截取字段，从第几位开始截取，截取长度)

例：

?

1 2	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(被截取字段，关键字，关键字出现的次数)

例：

?

1	select substring_index( "blog.csdn.net" ， "." ，2) as abstract from my_content_t

结果：

?

1

blog.csdn

(注：如果关键字出现的次数是负数 如-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()

?

1 2 3 4 5 6 7 8

mysql> select lower ( 'DDD' ); 结果：ddd mysql> select upper ( 'ddd' ); 结果：DDD mysql> select lcase( 'DDD' ); 结果：ddd mysql> select ucase( 'ddd' ); 结果：DDD

通常情况下，我选择 lower(), upper() 来转换字符串大小写，因为这和其他数据库中函数相兼容。

五、时间获取相关操作

?

1 2	declare _LastWeekStartTime date ; -- 上周开始时间 declare _LastWeekEndTime date ; -- 上周结束时间

-- 因系统为星期日为每星期第一天 所以需要减1

?

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

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

?

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

-- 因系统为星期日为每星期第一天 所以需要 减2获取上星期最后一天(上星期星期六)

?

1 2 3

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为周一 以此类推

?

1 2 3 4 5 6 7 8 9 10

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 ;

-- 周日时间需要重新计算

?

1 2 3 4

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

-- 开始时间(周一)

?

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

-- 结束时间(周日)

?

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

六、排序技巧

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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。

示例：

?

1 2 3

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数据库计有所帮助。

原文链接：http://blog.csdn.net/z369702770/article/details/70236782