Mysql学习MySQL笔记之运算符使用详解
《Mysql学习MySQL笔记之运算符使用详解》要点: Mysql可以通过运算符来对表中数据进行运算,比如通过出生日期求年龄等MYSQL实例 运算符包括四类,分别是:算数运算符、比较运算符、逻辑运算符和位运算符MYSQL实例
mysql> select a,a+5,a*2 from t1; +------+------+------+ | a??? | a+5? | a*2? | +------+------+------+ |?? 24 |?? 29 |?? 48 | +------+------+------+ ?row in set (0.00 sec) 这里的原值为24,后面也可以使用混合运算,只需要注意优先级即可
mysql> select a,a/3,a div 3,a%5,mod(a,5) from t1; +------+--------+---------+------+----------+ | a??? | a/3??? | a div 3 | a%5? | mod(a,5) | +------+--------+---------+------+----------+ |?? 24 | 8.0000 |?????? 8 |??? 4 |??????? 4 | +------+--------+---------+------+----------+ ?row in set (0.00 sec) 此处 / 和 div 代表整除,% 和 mod 代表取模 要注意的是,如果被除数为0,那么计算结果是NULLMYSQL实例
mysql> select a,a=24,a<12,a>40,a>=24,a<=24,a!=24,a<>24,a<=>24 from t1; +------+------+------+------+-------+-------+-------+-------+--------+ | a??? | a=24 | a<12 | a>40 | a>=24 | a<=24 | a!=24 | a<>24 | a<=>24 | +------+------+------+------+-------+-------+-------+-------+--------+ |?? 24 |??? 1 |??? 0 |??? 0 |???? 1 |???? 1 |???? 0 |???? 0 |????? 1 | +------+------+------+------+-------+-------+-------+-------+--------+ ?row in set (0.00 sec) 这里的1代表真,0代表假,需要说明的是<>和<=> <>代表不等于,等同于!=MYSQL实例 <=>代表等于,等同于=MYSQL实例 此外,等于和不等于不仅可以比较数值,还能比较字符串MYSQL实例
mysql> select a,a='24','ha'<>'ha','xa'='xa','b'!='b' from t1; +------+--------+------------+-----------+----------+ | a??? | a='24' | 'ha'<>'ha' | 'xa'='xa' | 'b'!='b' | +------+--------+------------+-----------+----------+ |?? 24 |????? 1 |????????? 0 |???????? 1 |??????? 0 | +------+--------+------------+-----------+----------+ ?row in set (0.00 sec) is null 和is not null mysql> select a,a is null,a is not null from t1; +------+-----------+---------------+ | a??? | a is null | a is not null | +------+-----------+---------------+ |?? 24 |???????? 0 |???????????? 1 | +------+-----------+---------------+ ?row in set (0.00 sec) 这里可以判断是否为空,NULL也可以跟NULL比较
mysql> select a,a between 15 and 30,a not between 15 and 30 from t1; +------+---------------------+-------------------------+ | a??? | a between 15 and 30 | a not between 15 and 30 | +------+---------------------+-------------------------+ |?? 24 |?????????????????? 1 |?????????????????????? 0 | +------+---------------------+-------------------------+ ?row in set (0.00 sec) between and 和not between and可以判断数值是否在某一区间内
mysql> select s,s like 'beijing',s like 'b%g',s like 'bei____',s like '%jing' from t2; +---------+------------------+--------------+------------------+----------------+ | s?????? | s like 'beijing' | s like 'b%g' | s like 'bei____' | s like '%jing' | +---------+------------------+--------------+------------------+----------------+ | beijing |??????????????? 1 |??????????? 1 |??????????????? 1 |????????????? 1 | +---------+------------------+--------------+------------------+----------------+ ?row in set (0.00 sec) ike可以用来匹配字符串,_代表单个字符,%代表多个字符MYSQL实例
mysql> select 2&&2,2&&null,2 and 3,2 and 2; +------+---------+---------+---------+ | 2&&2 | 2&&null | 2 and 3 | 2 and 2 | +------+---------+---------+---------+ |??? 1 |??? NULL |?????? 1 |?????? 1 | +------+---------+---------+---------+ ?row in set (0.00 sec) 这里&&和and意思一样
mysql> select 2||2,2||null,2 or 3,2 or 0; +------+---------+--------+--------+ | 2||2 | 2||null | 2 or 3 | 2 or 0 | +------+---------+--------+--------+ |??? 1 |?????? 1 |????? 1 |????? 1 | +------+---------+--------+--------+ ?row in set (0.00 sec) 这里||和or的意思一样
mysql> select !1,!2,!null; +----+----+-------+ | !1 | !2 | !null | +----+----+-------+ |? 0 |? 0 |? NULL | +----+----+-------+ ?row in set (0.00 sec) 此外还有位运算,目前还没用到,等用到的时候再补上 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |