mysql常用技巧总结.docx

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 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 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() mysqlselect 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(XX,id); - 原理样例 SET ORDBYD=XX; 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.