数据库入门（MySQL）：mySQL运算符与mySQL常用函数

• MySQL运算符
• MySQL常用函数

 一、MySQL运算符

1.1MySQL支持的数值计算运算符：加减乘除取余（+ - * / %）；

SELECT 6+4 加法操作，
    6-4 减法操作，
    6*4 乘法操作,
    6/4 除法操作,
    6 DIV 4 除法操作,
    6 % 4 取余操作,
    6 MOD 4 取余操作；

示例：

 

1.2MySQL支持的比较运算符：大于、小于、等于、不等于、大于等于、小于等于（> 、<、= 、= 或 <=> 、!= 或 <> 、>= 、<=）、特殊比较运算符。

需要注意的null值的相等比较，其他与数学比较运算基本无异。

 

相等比较运算符判断结果为1或0，分别表示真假（true、false）比较结果。注意相等比较可以使用（<=>）进行比较，这因为（=）不能比较null。

 

除了数学类的比较运算符，还有一些特殊比较运算符：

BETWEEN AND 存在于指定范围
IS NULL     为空
IN          存在于指定集合
LINK        通配符匹配
REGEXP      正则表达式匹配

BETWEEN value1 AND value2：用来比较匹配指定value1与value2两个值之间的值，比较值可以是数字、文本、时间。

select now() between '1999-01-01 00:00:00' and '2029-12-31 23:59:59';

IS NULL ：用来比较值示为为空。

select null is null;

IN ：用来比较（匹配）值是否在指定的集合中。

select "a" in ("a","b","c");

LINK ：用来比较（匹配）字符是否符合指定模式的字符。

select "abcd" like "a%";

REGEXP ：用来做正则匹配。更多参考：正则表达式基于JavaScript的入门详解

select 'cjg' regexp '[a-zA-a]';

通常比较运算符都用在where关键字后面，作为查询条件，匹配结果为1时，即结果为真（true）获取对应的数据记录。

1.3MySQL支持的逻辑运算符：与或非异或（AND(&&) 、OR（||）、NOT(!)、XOR）。更多可以参考：https://www.cnblogs.com/wisdom-jie/p/7732940.html

1.4MySQL支持的位运算符：按位与、按位或、按位取反、按位异或、按位左移、按位右移（&、|、~、^、<<、>>）。更多参考：https://www.cnblogs.com/top5/archive/2011/02/07/1949615.html

 二、MySQL常用函数

2.1字符串函数

CANCAT(str1,str2,...strn)：连接字符串str1，str2，...strn为一个完整的字符串，其中任意参数包含null则直接返回null。

select concat('My','SQL'); #‘MySQL’
select concat('abc',null,'efg');#合并参数中带有null值时合并结果为null值
select concat(curdate(),12.34); #可以合并非字符串参数，最终得到一个字符串的值。（示例:日期+浮点数值 2020-2-1412.34）

CANCAT_WS(sep,s1,s2,...sn)：将第一个值作为字符串的间隔字符，后面的参数以该字符作为间隔字符逐个拼接。

select concat_ws('-',029,88461234);#使用间隔字符实现拼接带区号的电话，注意0开头的值必须使用字符串，不然拼接时会不好含0
select concat_ws('-','a','b','c');#'a-b-c'
select concat_ws('-','e',null,'f',null,'g');#concat_ws中带有null会忽略null值进行拼接：‘e-f-g’

STRCMP(str1,str2)：比较字符串大小,当str1大于str2时返回1，否在返回-1。该函数实际上比较的是两个字符串ASCII码的大小，并且是从前到后逐个比较，除非是比较同一个字符一次对比就可以确定比较结果。所以要注意字符长度不对这一比较规律产生影响。

select strcmp('ab','ba');# -1
select strcmp('aca','bb');# -1

LENGTH()和CHAR_LENGTH()：获取字符串长度。通常情况下使用length获取英文字符串长度，char_length获取中文字符长度，因为length获取的中文字符串长度为3，因为它实际上获取的是字节长度。

select length('abc');# 3
select char_length("他乡踏雪");# 4
select length('他乡踏雪');#12

UPPER()和UCASE()：将字符串中的字母转成大写。

select upper("abc");# ABC
select ucase("abc");# ABC
select upper("字母abc");# 字母ABC
select ucase("字母abc");# 字母ABC

LOWER()和LCASE()：将字符串中的字母转换成小写。

select lower('ABC');# abc
select lcase('ABC');# abc
select lower("字母ABC") ,lcase("字母ABC"); # 字母abc 字母abc

FIND_IN_SET(str,strlist)：返回字符串str在由N子链组成的字符串列表strlist中的位置，返回值范围1到N之间，如果strlist中没有str则返回0；所谓strlist字符串列表就是由“,”间隔的字符串，str必须是每个间隔开的完整子字符串才能查找到。

select find_in_set("mysql","oracle,sql server,mysql"); #3
select find_in_set("mysql","oracle,sql server,mysql-5.7");# 0

FIELD(str,str1,str2...,strn)：返回字符串str在str1,str2...strn中的位置。如果str1,str2...,strn中包含str返回值位置1至n，如果不包含则返回0。

select field('mysql','oracle','sql server','mysql');# 3

LOCATE(str,str1)、POSITION(str IN str1)、INSTR(str1,str)：这三个函数都是用来返回str在str1的开始位置。如果str1中包含str则返回1到n的值，不包含则返回0。

select locate('sql','mysql'); #3
select position('sql' in 'mysql'); #3
select instr('mysql','sql'); #3

ELT(n,str1,str2...,strn)：返回指定位置的字符串，严格来说是返回指定位置的字符串参数，因为n指的是后面参数的位置。可以吧str1,str2..,strn理解为一个数组，n为索引值。但要注意n的取值为1至n，如果取值为0返回null。

select elt(0,'mysql','oracle','sql server');# null
select elt(1,'mysql','oracle','sql server');# mysql

MAKE_SET(num,str1,str2...,strn)：根据二进制数num匹配筛选str1,str2...,strn，对应每一位的二进制数值为1时则筛选该值。

select bin(5) , make_set(5,'mysql','oracle','sql server'); # 101 mysql,sql server
select bin(7) , make_set(7,'mysql','oracle','sql server','sql');# 111 mysql,oracle,sql server

LEFT(str,num)、RIGHT(str,num)：从左边或右边截取num字符长度的字符串。

select left('mysql',2); # my
select right('mysql',3); # sql

SUBSTRING(str,num,len)、MID(str,num,len)：从字符串str中的num位置开始截取长度为len的字符串。注意num在str按照从1到n取值。num表示截取字符串长度的起始位置。

select substring('oraclemysql',7,5); # mysql
select mid('oraclemysql',7,5); # mysql

LTRIM(str)：去除字符串开始处空格。

select ltrim("   ab c de  "); # 'ab c de  '

RTRIM(str)：去除字符串结束处的空格。

select rtrim("   ab c de  "); # '   ab c de'

TRIM(str)：去除字符串的首位空格。

select trim("   ab c de  "); # 'ab c de'

INSERT(str,pos,len,newstr)：将str中以第pos个字符起始，len长度的字符串替换成newstr字符串。

select insert("这是Oracle数据库系统",3,6,'MySQL');

REPLACE(str,substr,newstr)：将str中所有的substr字符串替换成newstr。

select insert("这是Oracle数据库系统",3,5,'MySQL');

2.2数值函数

ABS(x)：返回数值x的绝对值；

CELL(x)：返回大于或等于x的最大整数值；

FLOOR(x)：返回小于或等于x的最大整数值；

MOD(x,y)：返回x除以y的余数；

RAND()：返回0~1内的随机数；

ROUND(x,y)：返回数值x的四舍五入后有y位小数的数值；

TRUNCATE(x,y)：返回数值x且截断为y位小数的值。

2.3日期和时间函数

CURDATE()：获取当前日期；

CURTIME()：获取当前时间；

NOW()：获取当前的日期和时间；

UNIX_TIMESTAMP(date)：获取日期date的unix时间戳；

FROM_UNIXTIME()：获取UNIX时间戳的日期值；

WEEKOFYEAR(date)：获取日期为date对应一年中的第几周；

WEEK(date)：返回日期为date的对应的星期几，对应的通常星期值（取值：1~7）；

DAYOFWEEK()：同上（取值：1~7）；

WEEKDAY()：返回日期为date对应的星期几的计数值（取值：0~6）；

YEAR(date)：返回日期为date的年份；

MONTHNAME(date)：返回日期date的月份值；

DAYOFMONTH(date)：返回日期date的日（号）值。

HOUR(time)：返回时间为time的小时值；

MINUTE(time)：返回时间为time的分钟值；

SECOND(time)：返回时间time的秒值；

 

获取日期指定部分值的函数：

EXTRACT(type FROM date);

type用来指定去哪部分的日期值，可取值为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。

 

TO_DAYS(date)：计算日期为date与0000年1月1日相隔的天数。

FROM_DAYS(number)：通过距离0000年1月1日相隔天数数值计算改天的日期。

select now(), to_days(now()),from_days(to_days((now())));

 

ADDDATE(date,n)：获取日期为date的n天后的日期；

SUBDATE(date,n)：获取日期为date的n天前的日期；

更多可以了解这个手册：https://www.runoob.com/mysql/mysql-functions.html