Mysql学习笔记

一：mysql常用函数整理

1，selectCONCAT(null,' chen',' zhi'); //null

2，select CONCAT('a','b','c') //abc

实战示例：如果项目中由于某些原因无法使用搜索引擎进行全文检索，比如，solr和elasticsearch等，那么可以使用该函数来解决，但是注意无法达到搜索引擎那种可以分词检索的效果

需求：要求关键字keyword在表中（这里讲单表，多表原理一样）的A字段，B字段，C字段只要有包含关键字keyword就查询出来，平时我们只模糊单个字段查询，但是现在如何做到多字段多关键字模糊查询呢？我们可以拼接ABC三个字段，然后将拼接好后的整体去模糊匹配关键字

即select * from tableName where id > 12 and CONCAT(A,B,C) LIKE '%keyword%';

但是如果ABC中有任何一个字段为null，那么该函数返回值为null，则匹配不了，解决方案：使用IFNULL（）;

IFNULL('A','') --如果A字段的值为null，则用空串代替

所以综上：select * from tableName where id > 12 and CONCAT(IFNULL(A,''),IFNULL(B,''),IFNULL(C,'')) LIKE '%keyword%';

----要是使用搜索引擎请忽略这一段。

2，selectCONCAT_WS('-','2018','9','10');

3，selectSTRCMP('edad','eadaddadad'); //比较字符串大小，是按字母排序顺序来排序的，>返回1,等于返回0，小于返回-1，注意：

如果第一个字母相同，就按第二个字母排序顺序来，以此类推即可

4, SELECT* from systemtype WHERECHAR_LENGTH(SystemTypeName) > 15; //CHAR_LENGTH统计str的字符个数，中文英文都算一个字符，注意不是统计长度

5, selectFIND_IN_SET('mysql','oracle,mysql,sqlserver,java,php'); //返回第一个字符串在第二个字符串中出现的位置，忽略大小写，第二个字符串中每个字符串之间的大小用,分割,返回的索引位置是从1开始，返回0代表未找到，该sql语句返回2，表示mysql在第二个位置出现

6, SELECT*FROM systemtype where FIND_IN_SET(SystemTypeID,'1,2,4') >0;//另一种用法，相当于in，>0是in，=0表示not in

7, SELECT FIELD('mysql','java','Mysql','php'); 返回值：2，表示第一个字符串在后面出现的字符串中出现的位置

8, select LOCATE('Mysql','hello,Im cz good aftertoon mysql') as locate_1,//locate函数表示第一个字符串在第二个字符串中第一次出现的位置，该语句返回22，按字符数数的POSITION('Mysql' IN 'hello,Im cz goodaftertoon mysql') as position_2, //同上，返回22

INSTR('hello,Im cz good aftertoonmysql','Mysql') as instr_3;//跟上面检测顺序反过来了，但是还是返回22，是拿后面的字符串跟前面的找

 

9，selectELT(3,'good','hello','mysql','name');返回指定位置的字符串

10， SUBSTRING(str,num,len)，截取从第num位置开始截取，截取长度为len长，索引都是从1开始的

 

11, INSERT(str,pos,len,newstr)  ,返回，从str的第pos位置开始，一直到len个长度，都用newstr来代替，

 

12， REPLACE(str,substr,newstr)  //功能类似上面，将str中的substr字符串用newstr来替换

 

 

二：使用数值函数

 

2.1， SELECT RAND();//返回0-1的随机数

2.2，ROUND(x,y)；//返回数值x的四舍五入后有y位小数的数值，注意有四舍五入

SELECT ROUND(2.6266,3);//2.627

2.3，TRUNCATE(x,y)//返回数值x，留y位小数，没有四舍五入

SELECT TRUNCATE(2.6264,3); //2.626

三：日期函数:

3.1 当前日期now（），当前时间戳UNIX_dTIMESTAMP（时间日期）---》毫秒数，

将时间戳转为普通格式：FROM_UNIXTIME(时间戳)à日期格式

 

3.2

 

selectYEAR('2018-6-18') '年',

                     QUARTER('2018-6-18') '季度',

      MONTH('2018-6-18') '月',

      WEEK('2018-6-18') '星期(是一年中的第几个星期)',

      aDAYOFMONTH('2018-6-18') '天(一个月中第几天)';

 

3.3关于星期的函数：

 

       1）select WEEKDAY('2018-6-18') '星期';注意：返回值是在0-6, 0表示星期1，1表示星期二，以此类推，即得出的数加1就是当前星期几

       2）select DAYOFWEEK('2018-6-18') '星期'; 注意:返回值是在1-7, 1表示星期日, 2表示星期三, 以此类推，掌握规律

3.4 关于天的函数

      1）selectDAYOFMONTH('2018-6-18') 当月的第几天,DAYOFYEAR('2018-6-18') 当年的第几天; 需要注意的是老外和我们的天数不一定是一样的。

3.5 关于日期计算的函数

SELECT'2018-6-18',ADDDATE('2018-6-18',5) 加上5天后的日期,SUBDATE('2018-6-18',5) 减去5天后的日期;

-------------------------------更新 -------------------

日期中FROM_UNIXTIME(时间戳)可以设置格式，

如：FROM_UNIXTIME(datetime,'%Y-%m-%d') from 表名 这样他会按照该格式显示日期，即年-月-日显示 如果只写 Y 则只会

显示哪一年

注意了：没有格式则会显示所有时间格式，包括时分秒

3.4 获取当前时间的几种函数的区别：

NOW(),CURDATE(),CURRENT_DATE(),CURRENT_TIME(),CURRENT_TIMESTAMP()

测试看结果区别：

 SELECT NOW(),CURDATE(),CURRENT_DATE(),CURRENT_TIME(),CURRENT_TIMESTAMP() FROM repairs_record;

结论：now()显示日期精确到秒，CURDDATE() 精确到日，CURRENT_DATE()同理，CURRENT_TIME()是只查时分秒，而CURRENT_TIMESTAMP()是与now()效果一样

3.5 在数据库获得当前时间或者指定时间的年 月 日 时 分 秒 

   1）获得当前时间的年月日

    SELECT YEAR(NOW()),MONTH('2018-9-10'),DAY('2018-9-10'),HOUR(NOW()),MINUTE(NOW()) FROM repairs_record;

     

     结论：当前日期较为简单 只要传进去的日期格式是正确 类似于：2018-8-10 15:26:03 或者 2018-8-10 

    2) 获取表中时间字段的中的年月日 时分秒

     注意：如果你的时间字段存储的时间格式与上面格式类似 则只需要将函数中的参数换成该字段即可

                当存储的字段是以时间戳形式 则 需要先将时间戳转为指定的日期格式 比如时间戳为：1533744000 ，那么可以使用

FROM_UNIXTIME('1533744000') 先转为日期格式 ---》2018-8-9 然后再次在外面嵌套使用YEAR或者MONTH等相似的函数即可

例如：YEAR(FROM_UNIXTIME('1533744000'))