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

2023-09-15 03:03:05 73

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

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

useinformation_schema;
select*fromcolumnswherecolumn_name='字段名';

二、查询记录总数

SELECTSQL_CALC_FOUND_ROWS*FROMTABLEWHERE1=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*fromtablewhere1=1';
入参：arg_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;

四、字符串相关操作

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

(注：如果位数是负数如-5则是从后倒数位数，到字符串结束或截取的长度)

4、按关键字截取字符串

substring_index(str,delim,count)

说明：substring_index(被截取字段，关键字，关键字出现的次数)

例：

selectsubstring_index("blog.csdn.net"，"."，2)asabstractfrommy_content_t

结果：

blog.csdn

(注：如果关键字出现的次数是负数如-2则是从后倒数，到字符串结束)

函数简介：

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

不带有len参数的格式从字符串str返回一个子字符串，起始于位置pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置pos。使用FROM的格式为标准SQL语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos字符，而不是字符串的开头位置。在以下格式的函数中可以对pos使用一个负值。

MySQL字符串大小写转换

deMySQL字符串大小写转化函数有两对：lower(),uppper()和lcase(),ucase()

mysql>selectlower('DDD');
结果：ddd
mysql>selectupper('ddd');
结果：DDD
mysql>selectlcase('DDD');
结果：ddd
mysql>selectucase('ddd');
结果：DDD

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

五、时间获取相关操作

declare_LastWeekStartTimedate;--上周开始时间
declare_LastWeekEndTimedate;--上周结束时间

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

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

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

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

--因系统为星期日为每星期第一天所以需要减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));

--获取当前日期是星期几1为周日2为周一以此类推

SET@nowdate=(selectDAYOFWEEK(NOW()));
SET@NowD=case@nowdate
when1then'星期日'
when2then'星期一'
when3then'星期二'
when4then'星期三'
when5then'星期四'
when6then'星期五'
when7then'星期六'
end;

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

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

--开始时间(周一)

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

--结束时间(周日)

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;
--根据id为2013的优先级自定义排序
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函数(摘自百度百科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。

示例：

SELECTinstr('syranmo','s')FROMdual;--返回1
SELECTinstr('syranmo','ra')FROMdual;--返回3
SELECTinstr('syranmo','at',1,2)FROMdual;--返回0

更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL查询技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》、《MySQL数据库锁相关技巧汇总》及《MySQL常用函数大汇总》

希望本文所述对大家MySQL数据库计有所帮助。

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

热门推荐

随机推荐