mysql的算术运算符和比较运算符
来源:互联网 发布:葛优演技 知乎 编辑:程序博客网 时间:2024/06/06 01:28
mysql 的算术部分
mysql> create table tmpl4(num int);Query OK, 0 rows affected (0.14 sec)mysql> insert into tmpl4 values(64);Query OK, 1 row affected (0.08 sec)mysql> select num,num+10,num-3+5,num+5-3,num+36.5 from tmpl4;+------+--------+---------+---------+----------+| num | num+10 | num-3+5 | num+5-3 | num+36.5 |+------+--------+---------+---------+----------+| 64 | 74 | 66 | 66 | 100.5 |+------+--------+---------+---------+----------+1 row in set (0.00 sec)mysql> select num,num*2,num/2,num/3,num%3 from tmpl4;+------+-------+---------+---------+-------+| num | num*2 | num/2 | num/3 | num%3 |+------+-------+---------+---------+-------+| 64 | 128 | 32.0000 | 21.3333 | 1 |+------+-------+---------+---------+-------+1 row in set (0.00 sec)mysql> select num,num/0,num%0 from tmpl4;+------+-------+-------+| num | num/0 | num%0 |+------+-------+-------+| 64 | NULL | NULL |+------+-------+-------+1 row in set (0.00 sec)mysql>
mysql 的比较运算部分
---+----------+1 row in set (0.00 sec)mysql> select 1<=>0,'2'<=>2,2<=>2,'0.02'<=>0,'b'<=>'b',(1+3) <=> (2+1),NULL<=>NULL,1<=>NULL\G*************************** 1. row *************************** 1<=>0: 0 '2'<=>2: 1 2<=>2: 1 '0.02'<=>0: 0 'b'<=>'b': 1(1+3) <=> (2+1): 0 NULL<=>NULL: 1 1<=>NULL: 01 row in set (0.00 sec)mysql> <> or != 用于判断数字,字符串,表达式不相等的判断,如果不相等,返回值为1,否则返回值为0.这两个运算符不能用于判断空值NULLmysql> Ctrl-C -- exit!mysql> select 'good' <> 'god',1<>2,4!=4,5!=5,(1+3)!=(2+1),NULL <>NULL;+-----------------+------+------+------+--------------+-------------+| 'good' <> 'god' | 1<>2 | 4!=4 | 5!=5 | (1+3)!=(2+1) | NULL <>NULL |+-----------------+------+------+------+--------------+-------------+| 1 | 1 | 0 | 0 | 1 | NULL |+-----------------+------+------+------+--------------+-------------+1 row in set (0.00 sec)mysql> select 'good' <= 'god',1<=2,4<=4,5.5<5,(1+3)<=(2+1),NULL<=NULL;+-----------------+------+------+-------+--------------+------------+| 'good' <= 'god' | 1<=2 | 4<=4 | 5.5<5 | (1+3)<=(2+1) | NULL<=NULL |+-----------------+------+------+-------+--------------+------------+| 0 | 1 | 1 | 0 | 0 | NULL |+-----------------+------+------+-------+--------------+------------+1 row in set (0.00 sec)mysql> select 'good'<'god',1<2,4<4,5.5<5,(1+3)<(2+1),NULL<NULL;+--------------+-----+-----+-------+-------------+-----------+| 'good'<'god' | 1<2 | 4<4 | 5.5<5 | (1+3)<(2+1) | NULL<NULL |+--------------+-----+-----+-------+-------------+-----------+| 0 | 1 | 0 | 0 | 0 | NULL |+--------------+-----+-----+-------+-------------+-----------+1 row in set (0.00 sec)mysql> select 'good' >='god',1>=2,4>=4,5.5>5,(1+3)>=(2+1),NULL>=NULL;+----------------+------+------+-------+--------------+------------+| 'good' >='god' | 1>=2 | 4>=4 | 5.5>5 | (1+3)>=(2+1) | NULL>=NULL |+----------------+------+------+-------+--------------+------------+| 1 | 0 | 1 | 1 | 1 | NULL |+----------------+------+------+-------+--------------+------------+1 row in set (0.00 sec)mysql> select NULL is NULL,ISNULL(NULL),ISNULL(10),10 is NOT NULL;+--------------+--------------+------------+----------------+| NULL is NULL | ISNULL(NULL) | ISNULL(10) | 10 is NOT NULL |+--------------+--------------+------------+----------------+| 1 | 1 | 0 | 1 |+--------------+--------------+------------+----------------+1 row in set (0.00 sec)mysql> select 4 between 4 and 6,4 between 4 and 6,12 between 9 and 10;+-------------------+-------------------+---------------------+| 4 between 4 and 6 | 4 between 4 and 6 | 12 between 9 and 10 |+-------------------+-------------------+---------------------+| 1 | 1 | 0 |+-------------------+-------------------+---------------------+1 row in set (0.00 sec)mysql> select 'x' between 'f' and 'g','b' between 'a' and 'c';+--------------------------+-------------------------+| 'x' between 'f' and 'g' | 'b' between 'a' and 'c' |+--------------------------+-------------------------+| 0 | 1 |+--------------------------+-------------------------+1 row in set (0.00 sec)mysql> select least(2,0),least(20.0,3.0,100.5),least('a','c','b'),least(0,NULL);+------------+-----------------------+--------------------+---------------+| least(2,0) | least(20.0,3.0,100.5) | least('a','c','b') | least(0,NULL) |+------------+-----------------------+--------------------+---------------+| 0 | 3.0 | a | NULL |+------------+-----------------------+--------------------+---------------+1 row in set (0.00 sec)mysql> select greatest(2,0),greatest(20.0,3.0,100.5),greatest('a','c','b'),greatest(10,NULL);+---------------+--------------------------+-----------------------+-------------------+| greatest(2,0) | greatest(20.0,3.0,100.5) | greatest('a','c','b') | greatest(10,NULL) |+---------------+--------------------------+-----------------------+-------------------+| 2 | 100.5 | c | NULL |+---------------+--------------------------+-----------------------+-------------------+1 row in set (0.00 sec)mysql> select greatest(2,0),greatest(20.0,3.0,100.5),greatest('a','c','b'),greatest(10,NULL)\G*************************** 1. row *************************** greatest(2,0): 2greatest(20.0,3.0,100.5): 100.5 greatest('a','c','b'): c greatest(10,NULL): NULL1 row in set (0.00 sec)mysql> select 1 in(1,3,5,'thks'),'thks' in (1,3,5,'thks');+--------------------+--------------------------+| 1 in(1,3,5,'thks') | 'thks' in (1,3,5,'thks') |+--------------------+--------------------------+| 1 | 1 |+--------------------+--------------------------+1 row in set, 1 warning (0.01 sec)mysql> select 2 not in (1,3,5,'thks'),'thks' not in(1,3,5,'thks');+-------------------------+-----------------------------+| 2 not in (1,3,5,'thks') | 'thks' not in(1,3,5,'thks') |+-------------------------+-----------------------------+| 1 | 0 |+-------------------------+-----------------------------+1 row in set, 2 warnings (0.00 sec)mysql> select null in (1,3,5,'thks'),10 in (1,3,NULL,'thks');+------------------------+-------------------------+| null in (1,3,5,'thks') | 10 in (1,3,NULL,'thks') |+------------------------+-------------------------+| NULL | NULL |+------------------------+-------------------------+1 row in set, 1 warning (0.00 sec)mysql> select 'stud' like 'stud','stud' like 'stu_','stud' like '%d','stud' like 't___','s' like NULL;+--------------------+--------------------+------------------+--------------------+---------------+| 'stud' like 'stud' | 'stud' like 'stu_' | 'stud' like '%d' | 'stud' like 't___' | 's' like NULL |+--------------------+--------------------+------------------+--------------------+---------------+| 1 | 1 | 1 |0 | NULL |+--------------------+--------------------+------------------+--------------------+---------------+1 row in set (0.00 sec)mysql> select 'stud' like 'stud','stud' like 'stu_','stud' like '%d','stud' like 't___','s' like NULL\G*************************** 1. row ***************************'stud' like 'stud': 1'stud' like 'stu_': 1 'stud' like '%d': 1'stud' like 't___': 0 's' like NULL: NULL1 row in set (0.00 sec)mysql> select 'ssky' regexp '^s','ssky' regexp 'y$','ssky' regexp '.sky','ssky'regexp '[ab]';+--------------------+--------------------+----------------------+----------------------+| 'ssky' regexp '^s' | 'ssky' regexp 'y$' | 'ssky' regexp '.sky' | 'ssky' regexp '[ab]' |+--------------------+--------------------+----------------------+----------------------+| 1 | 1 | 1 | 0 |+--------------------+--------------------+----------------------+----------------------+1 row in set (0.01 sec)mysql> select 'ssky' regexp '^s','ssky' regexp 'y$','ssky' regexp '.sky','ssky'regexp '[ab]'\G*************************** 1. row *************************** 'ssky' regexp '^s': 1 'ssky' regexp 'y$': 1'ssky' regexp '.sky': 1'ssky' regexp '[ab]': 01 row in set (0.00 sec)mysql> select not 10,not(1-1),not-5,not NULL,not !1+1;+--------+----------+-------+----------+----------+| not 10 | not(1-1) | not-5 | not NULL | not !1+1 |+--------+----------+-------+----------+----------+| 0 | 1 | 0 | NULL | 0 |+--------+----------+-------+----------+----------+1 row in set (0.00 sec)mysql> select 1 and -1,1 and 0,1 and NULL,0 and NULL;+----------+---------+------------+------------+| 1 and -1 | 1 and 0 | 1 and NULL | 0 and NULL |+----------+---------+------------+------------+| 1 | 0 | NULL | 0 |+----------+---------+------------+------------+1 row in set (0.00 sec)mysql> select 1 && -1,1 && 0,1 && NULL,0 && NULL;+---------+--------+-----------+-----------+| 1 && -1 | 1 && 0 | 1 && NULL | 0 && NULL |+---------+--------+-----------+-----------+| 1 | 0 | NULL | 0 |+---------+--------+-----------+-----------+1 row in set (0.00 sec)mysql> select 1 or -1 or 0,1 or 2,1 or null,0 or null,null or null;+--------------+--------+-----------+-----------+--------------+| 1 or -1 or 0 | 1 or 2 | 1 or null | 0 or null | null or null |+--------------+--------+-----------+-----------+--------------+| 1 | 1 | 1 | NULL | NULL |+--------------+--------+-----------+-----------+--------------+1 row in set (0.01 sec)mysql> select 1 || -1 || 0,1 || 2,1 || NULL,0 || NULL,NULL || NULL;+--------------+--------+-----------+-----------+--------------+| 1 || -1 || 0 | 1 || 2 | 1 || NULL | 0 || NULL | NULL || NULL |+--------------+--------+-----------+-----------+--------------+| 1 | 1 | 1 | NULL | NULL |+--------------+--------+-----------+-----------+--------------+1 row in set (0.00 sec)mysql> select 1 xor 1,0 xor 0,1 xor 0,1 xor null,1 xor 1 xor 1;+---------+---------+---------+------------+---------------+| 1 xor 1 | 0 xor 0 | 1 xor 0 | 1 xor null | 1 xor 1 xor 1 |+---------+---------+---------+------------+---------------+| 0 | 0 | 1 | NULL | 1 |+---------+---------+---------+------------+---------------+1 row in set (0.00 sec)mysql> select 10 | 15;+---------+| 10 | 15 |+---------+| 15 |+---------+1 row in set (0.00 sec)mysql> select 9|4|2;+-------+| 9|4|2 |+-------+| 15 |+-------+1 row in set (0.00 sec)mysql> select 10 & 15;+---------+| 10 & 15 |+---------+| 10 |+---------+1 row in set (0.00 sec)mysql> select 10^15;+-------+| 10^15 |+-------+| 5 |+-------+1 row in set (0.00 sec)mysql> select 12<<2 -> ;+-------+| 12<<2 |+-------+| 48 |+-------+1 row in set (0.00 sec)mysql> select 12<<1;+-------+| 12<<1 |+-------+| 24 |+-------+1 row in set (0.00 sec)mysql> select 1>>1;+------+| 1>>1 |+------+| 0 |+------+1 row in set (0.00 sec)mysql> select 16 >> 2;+---------+| 16 >> 2 |+---------+| 4 |+---------+1 row in set (0.00 sec)mysql> select 5 & ~1;+--------+| 5 & ~1 |+--------+| 4 |+--------+1 row in set (0.00 sec)mysql>
0 0
- mysql的算术运算符和比较运算符
- 5 算术运算符和比较运算符
- 算术运算符的运算
- 算术运算符、赋值运算符、比较运算符
- MySQL学习----MySQL 算术运算符----07MySQL 比较运算符
- 扩展的算术运算符(++和--)
- Swift的算术运算符和赋值运算符
- 6、C#的算术运算符和算术表达式
- c的部分算术运算符和算术表达式:
- 变量和算术运算符
- 算术运算符和表达式
- 算术和关系运算符
- 算术运算符的优先级
- Matlab的算术运算符
- 算术运算符的运用
- 算术运算符的应用
- 算术运算符的应用
- Java学习笔记(运算符/Java的算术运算符/赋值运算符/比较运算符/逻辑运算符)
- Android 实现适配器中的子控件与Activity通信
- MySQL5.6安装步骤(windows7/8_64位)
- django-admin.py startproject 无法创建项目
- Android Studio 安装
- R语言包_gbm
- mysql的算术运算符和比较运算符
- HashMap源码分析
- 字符串处理相关
- 『细节』是什么
- Java设计模式-1-概述
- Py2exe打包时添加自定义库搜索目录
- cocos2d-x内存管理(1)
- POJ 3617 字典序最小问题
- 第0课第4节 刚接触开发板之使用vmwae和预先做好的ubuntu