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("blog.csdn.net"，"."，2)as abstract from my_content_t

结果：

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()

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相关内容感兴趣的读者可查看本站专题：《mysql查询技巧大全》、《mysql事务操作技巧汇总》、《mysql存储过程技巧大全》、《mysql数据库锁相关技巧汇总》及《mysql常用函数大汇总》

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