MYSQL相关运算符、函数

一、算数运算符

SELECT 1+1,1-1,2*4,3/8;

#支持数据转换
SELECT 1+'3MAIZI';
#计算结果是4，但是会有一条警告

#除法
SELECT 3/0;
SELECT 3 DIV 0;

#取余数
SELECT 8 MOD 3;
SELECT 8%3; 

注意：对null做运算，结果都是null
SELECT NULL+1; SELECT NULL+NULL;
结果都是null

二、比较运算符

结果只能为真或假

SELECT 1=1,1='1',1=2;

结果为1表示真，结果为0表示假

SELECT username,username='king' FROM student;

SELECT username,username!='king' FROM student;
SELECT username,username<>'king' FROM student;

检测null值
查看年龄等于为空的用户的编号，名称，年龄和性别

检测null值用到符号是<=>如果仅仅是=没有效果

SELECT id,username,age,sex,age<=>NULL FROM cms_user;
SELECT id,username,age,sex,age<=>NULL FROM cms_user;SELECT id,username,age,sex,age IS NULL FROM cms_user;

在学生表中查询获得奖学金的人的详情，这种方法相当于是吧WHERE条件提前，并将条件作为一个新的字段来查询真假

SELECT id,username,score,score>=ANY(SELECT level FROM scholarship) FROM student;

SELECT id,username,age,age BETWEEN 10 AND 30 FROM cms_user;

SELECT id,username,age,age IN(21,31,41,51) FROM cms_user;
SELECT id,username,age,age IN(21,31,41,51) FROM cms_user;
SELECT 's' LIKE '_';
SELECT 'SD' LIKE '_';
#会报错
SELECT id,username,username LIKE '____' FROM student;
#查看用户名称是四位数的情况

SELECT id,username,username REGEXP '^t' FROM cms_user;
#检测用户名是t开头的记录

三、逻辑运算符

&表示且，|表示或

SELECT 2&&2,2&&0,2&&NULL,1||1,1||0,1||NULL,0||NULL;

在这里0表示假，不为0的表示真，1或者NULL由一个为真结果为真，0或者NULL由于0为假，执行到NULL结果为NULL

取反：SELECT !1,!0,!NULL;

XOR两个值不同为真假的时候为真，同时为真或假得时候为假
运算符的优先级

四、数学函数库

CEIL/CEILING()是取整函数

SELECT CEIL(1.2),CEILING(1.2);

--对小数取整
SELECT num1,CEIL(num2),CEILING(num3) FROM test.test4;

SELECT FLOOR(3.14);取整

取幂：pow，power

--取2的三次方和3的三次方
SELECT POW(2,3),POWER(3,3);

或者是截断，仅仅是截取对应的位数，不进行四舍五入

SELECT TRUNCATE(3.1456789,2);

ABS取绝对值

SELECT ABS(-132);

SELECT PI();

#取随机数，0-1之间
 SELECT RAND();
 SELECT * FROM cms_user ORDER BY RAND(id);

#想要一个固定值的时候
SELECT RAND(1);

#取符号
SELECT SIGN(12),SIGN(0),SIGN(-19);
#1代表整数，无符号位0，-1代表负数

计算的是指数函数的幂次方

SELECT EXP(3);

五、字符串函数库

SELECT CHAR_LENGTH('LITTLE STAR'),LENGTH('LITTLE STAR');

SELECT CHAR_LENGTH('你好'),LENGTH('你好');

中文一个字长度为3，字符串长度为1

#concat函数将字符串进行拼接
SELECT CONCAT('hello','world');

但是在拼接的时候有null值，结果是null

SELECT CONCAT('a','b',NULL);

#CONCAT_WS让指定的分隔符来连接字符串
SELECT CONCAT_WS('^_^','A','B','C','D');
#指定的符号在最前面，后面将需要连接的字符串依次排列

SELECT CONCAT_WS('','A','B','C','D');
#如果以空字符串连接，字符之间是连接的

#如果以null连接，结果是null
SELECT CONCAT_WS(NULL,'A','B','C','D');

#但如果连接的内容为Null的话不影响结果为null

 SELECT CONCAT_WS('^_^','A','B','C',NULL);

#返回大写字符串
SELECT UPPER('this is a test');
SELECT UCASE('this is a test');

SELECT LOWER('HELLO WORLD');
SELECT LCASE('HELLO WORLD');

填充

SELECT LPAD('A',5,'?');

LPAD表示在字符串左边进行填充

将字符串A用?填充到5个字符

SELECT RPAD(‘A’,5,’!’);

 SELECT TRIM(' ABC ');
#TRIM去掉空格

#LTRIM 去掉左边的空格

#RTRIM 去掉右边的空格

SELECT TRIM('A' FROM 'ABCA');
#从ABCA中去掉两端的A

SELECT REPEAT('hello',5);
#将字符串重复5次

#space返回n个空格
SELECT CONCAT('_',SPACE(5),'_');

SELECT REPLACE('ABCDA','A','_');
#将字符串中的A替换成下划线_

需要注意的是，在这里会区分大小写，严格按照字符串中的大小写来查询

SELECT STRCMP('A','A'),STRCMP('A','a'),STRCMP('A','B');

比较字符串的大小，用前面的字符出跟后面的字符串进行比较

a比b小所以返回的是-1

在比较的时候忽略大小写

 SELECT SUBSTRING('ABCDEF',2,2);

SUBSTRING截取字符串，从第二个位置开始截取两个字符串

SELECT REVERSE('ABC');

六、日期时间函数

SELECT CURDATE(),CURRENT_DATE();
#得到当前日期

SELECT CURTIME(),CURRENT_TIME();
#得到当前时间

SELECT NOW();
#得到当前的日期和时间

SELECT MONTH('2015-1-30');
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());
SELECT DAYNAME(NOW());
#得到星期几
SELECT DAYOFWEEK(NOW());
SELECT WEEKDAY(NOW());
SELECT WEEK(NOW());
#返回的是一年中的第几周

SELECT YEAR(NOW());
#返回年份

SELECT HOUR(NOW());
#返回小时

SELECT SECOND(NOW());
#返回秒数

SELECT DATEDIFF(CURRENT_DATE,'19940719');
#返回两个时间之间的天数间隔

七、条件判断函数

SELECT id,username,score,IF(score>=60,'及格','不及格') FROM student;

SELECT id,age,username,IFNULL(age,'100') FROM cms_user;

如果age字段为空的话，填充为100

相当于python里面的if条件语句

SELECT id,username,score,CASE WHEN score>60 THEN '不错哦，继续努力' WHEN score=60 THEN '太险了，刚刚及格' ELSE '继续努力哦' END FROM student;

八、系统函数

九、加密函数

SELECT MD5('ADMIN');
#返回的是32位的字符串

USE mysql;
SHOW TABLES;
#查看其中的user表

SELECT * FROM user \G;
#有两个用户，一个localroot，一个root

SELECT PASSWORD('localroot'),PASSWORD('root');

SELECT FORMAT(3.1415698,2);
#将小数进行格式化，保留两位小数

SELECT ASCII('ABC');
#只能得到第一个字符串的ASC码值

SELECT BIN(5),HEX(5),OCT(5);

SELECT CONV(35,10,16);

#将35从8进制转换为16进制

SELECT CONV(35,10,8);

 SELECT INET_ATON('127.0.0.1');

SELECT INET_NTOA(2130706433);

SELECT GET_LOCK('KING',10);
#使用名为king的锁

SELECT IS_FREE_LOCK('KING');
#判断这个锁是否在使用中，或者是否空闲

#答案为0证明锁已经被使用了
SELECT RELEASE_LOCK('KING');
#释放锁

得到答案1 证明释放成功

重新建一个锁

SELECT GET_LOCK('xiaoxingxing',10);

SELECT IS_FREE_LOCK('xiaoxingxing');
#得到的答案为0说明这个锁已经被用了

#再次重建一个锁
SELECT IS_FREE_LOCK('xiaoxingxing');

#然后再查询，xiaoxingxing的锁已经被释放了
SELECT IS_FREE_LOCK('xiaoxingxing');