流水不争先,争的是滔滔不绝

MySQL数据库常用操作技巧

IM安全 macgrady 110℃

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

IM即时通讯服务,致力打造最稳定的即时云通信平台,让你的APP即刻拥有私聊、群聊、聊天室等通讯能力。聊天对话支持全类型消息收发。群聊人数支持横向扩展,聊天室人数无上限。通讯方案采取消息必达策略,收发消息回执。IM系统经过多年技术积累,多项测试下安全稳定,团队核心多年开发经验,6年IM领域研发,可按需求定制开发。APP有DEMO版进行畅聊测试,详情咨询:340554038

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

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

二、查询记录总数

1SELECT SQL_CALC_FOUND_ROWS * FROM TABLE WHERE 1=1;

即可得出总数据行数

1SET @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(被截取字段,截取长度)

例:

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

2、从右开始截取字符串

right(str, length)

说明:right(被截取字段,截取长度)

例:

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

3、截取字符串

substring(str, pos)

substring(str, pos, length)

说明:

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

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

例:

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

例:

1select substring_index(“blog.csdn.net”,”.”,2)as abstract from my_content_t

结果:

1blog.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 8mysql>select lower(‘DDD’); 结果:ddd mysql> select upper(‘ddd’); 结果:DDD mysql> select lcase(‘DDD’); 结果:ddd mysql> select ucase(‘ddd’); 结果:DDD

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

五、时间获取相关操作

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

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

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

— 算出本周星期一的日期

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

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

1 2 3set @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 10SET @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 4if (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

— 开始时间(周一)

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

— 结束时间(周日)

1SET @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 20CREATE 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 3SELECT instr(‘syranmo’,’s’) FROM dual; — 返回 1 SELECT instr(‘syranmo’,’ra’) FROM dual; — 返回 3 SELECT instr(‘syran mo’,’at’,1,2) FROM dual; — 返回 0

原文链接:https://www.jb51.net/article/136897.htm 转载请标注原文地址,谢谢配合

版权声明:部分文章、图片等内容为用户发布或互联网整理而来,仅供学习参考。如有侵犯您的版权,请联系我们,将立刻删除。
点击这里给我发消息