逻辑运算
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)