【Mysql 学习】算术运算及字符串，数值函数

逻辑运算

not 表示 非逻辑！not null 返回 的仍是null
mysql> select not 1,not 0, not null;
+-------+-------+----------+
| not 1 | not 0 | not null |
+-------+-------+----------+
|     0 |     1 |     NULL |
+-------+-------+----------+
1 row in set (0.00 sec)

 and 与 逻辑  任何 与null的逻辑运算的结果都是 null！

mysql> select (1 and 0) ,( 0 and 1 ) ,( 2 and 2 ) ,( 1 and null );
+-----------+-------------+-------------+----------------+
| (1 and 0) | ( 0 and 1 ) | ( 2 and 2 ) | ( 1 and null ) |
+-----------+-------------+-------------+----------------+
|         0 |           0 |           1 |           NULL |
+-----------+-------------+-------------+----------------+
1 row in set (0.00 sec)

mysql> select (1 and 0) ,( 0 and 1) ,(2 and 2), (1 and null) ,(null and null);
+-----------+------------+-----------+--------------+-----------------+
| (1 and 0) | ( 0 and 1) | (2 and 2) | (1 and null) | (null and null) |
+-----------+------------+-----------+--------------+-----------------+
|         0 |          0 |         1 |         NULL |            NULL |
+-----------+------------+-----------+--------------+-----------------+
1 row in set (0.00 sec)

or 逻辑或 运算

mysql> select (1 or 0) ,( 0 or 1) ,(2 or 2), (1 or  null) ,(null or null);
+----------+-----------+----------+--------------+----------------+
| (1 or 0) | ( 0 or 1) | (2 or 2) | (1 or  null) | (null or null) |
+----------+-----------+----------+--------------+----------------+
|        1 |         1 |        1 |            1 |           NULL |
+----------+-----------+----------+--------------+----------------+
1 row in set (0.01 sec)

 xor 异或运算。

mysql> select 1 xor 1 ,0 xor 1 ,0 xor  0 , 1 xor null,0 xor null;
+---------+---------+----------+------------+------------+
| 1 xor 1 | 0 xor 1 | 0 xor  0 | 1 xor null | 0 xor null |
+---------+---------+----------+------------+------------+
|       0 |       1 |        0 |       NULL |       NULL |
+---------+---------+----------+------------+------------+
1 row in set (0.01 sec)

mysql> select 2&5; 0010&0101
+-----+
| 2&5 |
+-----+
|   0 |
+-----+
1 row in set (0.01 sec)

mysql> select 5&5;
+-----+
| 5&5 |
+-----+
|   5 |
+-----+
1 row in set (0.01 sec)

mysql> select 2|3;  或 运算或运算对操作数的二进制做或运算
+-----+
| 2|3 |
+-----+
|   3 |
+-----+
1 row in set (0.01 sec)

或运算 对操作数的二进制做与运算

mysql> select 2&3&4; 0010&0011&0100
+-------+
| 2&3&4 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

mysql> select 2^3;  位异或运算 对操作数的二进制做异或
+-----+
| 2^3 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

mysql> select ~1; 位 取反！
+----------------------+
| ~1                   |
+----------------------+
| 18446744073709551614 |
+----------------------+
1 row in set (0.00 sec)

mysql> select 1000 >>3; -右移位操作！除以2的3次方
+----------+
| 1000 >>3 |
+----------+
|      125 |
+----------+
1 row in set (0.01 sec)

mysql> select 1000 <<3;左移位操作！乘以2的3次方
+----------+
| 1000 <<3 |
+----------+
|     8000 |
+----------+
1 row in set (0.00 sec)
--常用函数。
在默认状态下, 在函数和紧随其后的括号之间不得存在空格。这能帮助  MySQL 分析程序区分一些同函数名相同的函数调用以及表或列。不过，函数自变量周围允许有空格出现。
--当前使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)
--当前的数据库版本
mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.1.7-beta-log |
+----------------+
1 row in set (0.02 sec)
-当前的登录用户
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.01 sec)
--返回主机ip地址的数字
mysql> select inet_aton('192.168.12.128');
+-----------------------------+
| inet_aton('192.168.12.128') |
+-----------------------------+
|                  3232238720 |
+-----------------------------+
1 row in set (0.00 sec)
--返回数字代表的ip
mysql> select inet_ntoa(3232238720);
+-----------------------+
| inet_ntoa(3232238720) |
+-----------------------+
| 192.168.12.128        |
+-----------------------+
1 row in set (0.00 sec)
---返回字符串密码的加密版
mysql> select password('12356');
+-------------------------------------------+
| password('12356')                         |
+-------------------------------------------+
| *DC594838253636AA6E73A5366878F6F0502BDC5D |
+-------------------------------------------+
1 row in set (0.00 sec)
--字符串函数：
字符串指用单引号(‘'’)或双引号(‘"’)引起来的字符序列。例如：
'a string'
"another string"
如果SQL服务器模式启用了NSI_QUOTES，可以只用单引号引用字符串。用双引号引用的字符串被解释为一个识别符。
--连接字符
mysql> select concat('aa','bb','yangqilong'),concat('yangql' ,null);
+--------------------------------+------------------------+
| concat('aa','bb','yangqilong') | concat('yangql' ,null) |
+--------------------------------+------------------------+
| aabbyangqilong                 | NULL                   |
+--------------------------------+------------------------+
1 row in set (0.00 sec)
---insert(str,x，y ，insrt)将字符串从第x位置开始，y个字符替换为instr
mysql> select insert ('beijing2008iloveyou',12,3,'me');
+------------------------------------------+
| insert ('beijing2008iloveyou',12,3,'me') |
+------------------------------------------+
| beijing2008meveyou                       |
+------------------------------------------+
1 row in set (0.00 sec)
--转换大小写
mysql> select lower('YANGQILONG') ,upper('yangqilong');
+---------------------+---------------------+
| lower('YANGQILONG') | upper('yangqilong') |
+---------------------+---------------------+
| yangqilong          | YANGQILONG          |
+---------------------+---------------------+
1 row in set (0.00 sec)
--left(str,x)返回字符串最左边的x个字符
--right(str,x)返回字符串最右边的x个字符
mysql> select left('yangqlloveMysql',6), left ('yangqlloveMysql',null),right('yangqlloveMysql',9);
+---------------------------+-------------------------------+----------------------------+
| left('yangqlloveMysql',6) | left ('yangqlloveMysql',null) | right('yangqlloveMysql',9) |
+---------------------------+-------------------------------+----------------------------+
| yangql                    |                               | loveMysql                  |
+---------------------------+-------------------------------+----------------------------+
1 row in set (0.00 sec)
--字符填充函数 lpad('yangql',11,'mysql')
mysql> select lpad('yangql',11,'mysql') ,rpad('mysql', 11,'yangql');
+---------------------------+----------------------------+
| lpad('yangql',11,'mysql') | rpad('mysql', 11,'yangql') |
+---------------------------+----------------------------+
| mysqlyangql               | mysqlyangql                |
+---------------------------+----------------------------+
1 row in set (0.00 sec)

--去掉str 左边的空格！
mysql> select ltrim(' |yangqlmysql') ,rtrim('yangqlmysql|   ');
+------------------------+--------------------------+
| ltrim(' |yangqlmysql') | rtrim('yangqlmysql|   ') |
+------------------------+--------------------------+
| |yangqlmysql           | yangqlmysql|             |
+------------------------+--------------------------+
1 row in set (0.00 sec)
--repeat(str,N)重复 str N 次！
mysql> select repeat('yangql ',2);
+---------------------+
| repeat('yangql ',2) |
+---------------------+
| yangql yangql       |
+---------------------+
1 row in set (0.00 sec)
--比较字符串的大小
mysql> select strcmp('a','b') ,strcmp('a','a'),strcmp('c','b');
+-----------------+-----------------+-----------------+
| strcmp('a','b') | strcmp('a','a') | strcmp('c','b') |
+-----------------+-----------------+-----------------+
|              -1 |               0 |               1 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)
--substring(STR,X,Y)返回从字符串str x 位置起y个字符长度的字符串！
mysql> select substring('yangql mysql',1,6) ,substring('yangql mysql',7,12);
+-------------------------------+--------------------------------+
| substring('yangql mysql',1,6) | substring('yangql mysql',7,12) |
+-------------------------------+--------------------------------+
| yangql                        |  mysql                         |
+-------------------------------+--------------------------------+
1 row in set (0.00 sec)
--去掉 str 两边的空格，如果中间有空格，则不去掉！
mysql> select trim(' | yangql mysql |');
+---------------------------+
| trim(' | yangql mysql |') |
+---------------------------+
| | yangql mysql |          |
+---------------------------+
1 row in set (0.00 sec)
--数值函数
--ABS(X) 返回x的绝对值。
mysql> select abs(-0.5),abs(0.5);
+-----------+----------+
| abs(-0.5) | abs(0.5) |
+-----------+----------+
|       0.5 |      0.5 |
+-----------+----------+
1 row in set (0.01 sec)
--CEIL(X) :返回大于x的最小整数。
mysql> select ceil(-0.5),ceil(0.5);
+------------+-----------+
| ceil(-0.5) | ceil(0.5) |
+------------+-----------+
|          0 |         1 |
+------------+-----------+
1 row in set (0.00 sec)
--FLOOR(X):返回小于X的最大整数，和CEIL 相反！
mysql> select floor(-0.5),floor(0.5);
+-------------+------------+
| floor(-0.5) | floor(0.5) |
+-------------+------------+
|          -1 |          0 |
+-------------+------------+
1 row in set (0.00 sec)
--随机值函数：返回0--1 之间的数值，不重复！
mysql> select rand(),rand();
+------------------+-------------------+
| rand()           | rand()            |
+------------------+-------------------+
| 0.65191487563021 | 0.031441814295428 |
+------------------+-------------------+
1 row in set (0.00 sec)
--利用ceil 和rand 可以产生指定范围内的随机数
mysql> select ceil(100*rand()),ceil(100*rand());
+------------------+------------------+
| ceil(100*rand()) | ceil(100*rand()) |
+------------------+------------------+
|               21 |               92 |
+------------------+------------------+
1 row in set (0.00 sec)
--ROUND(X,Y):返回x的四舍五入的有y位 小数的值！
mysql> select round(1.25),round(1.25,1),round(1.25,2);
+-------------+---------------+---------------+
| round(1.25) | round(1.25,1) | round(1.25,2) |
+-------------+---------------+---------------+
|           1 |           1.3 |          1.25 |
+-------------+---------------+---------------+
1 row in set (0.00 sec)