2009-05-21--05-31 MySQL学习笔记03

1.关于NULL

普通的比较运算符用于NULL，返回的结果都是NULL。

mysql> select 0 = null, 1 <> null, 2 > null, 3 < null, 4 >= null, 5 <= null;
+----------+-----------+----------+----------+-----------+-----------+
| 0 = null | 1 <> null | 2 > null | 3 < null | 4 >= null | 5 <= null |
+----------+-----------+----------+----------+-----------+-----------+
| NULL | NULL | NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+-----------+-----------+
1 row in set (0.00 sec)

[@more@]要判断一个值是否为NULL，应该使用IS NULL、IS NOT NULL或<=>（NULL安全地等于）等运算符。

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

mysql> select 0 is not null, null is not null;
+---------------+------------------+
| 0 is not null | null is not null |
+---------------+------------------+
| 1 | 0 |
+---------------+------------------+
1 row in set (0.00 sec)

mysql> select 0 <=> null, null <=> null;
+------------+---------------+
| 0 <=> null | null <=> null |
+------------+---------------+
| 0 | 1 |
+------------+---------------+
1 row in set (0.00 sec)


在MySQL中，NULL不同于空值。

mysql> select '' IS NULL;
+------------+
| '' IS NULL |
+------------+
| 0 |
+------------+
1 row in set (0.00 sec)


2.REGEXP

REGEXP运算符可以执行较复杂的字符串比较运算，这主要通过正则表达式来实现。正则表达式由标准字符和专门定义匹配模式的元字符混合组成，下表列出了正则表达式中经常使用的元字符：

元字符 作用
+ 匹配1个或更多个前面字符的值
* 匹配0个或更多个前面字符的值
? 匹配0个或1前面字符的值
. 匹配任意字符
^ 匹配字符串的开始部分
$ 匹配字符串的末尾部分
s 匹配单个空白空间字符，包括制表符合换行符
S 匹配空白空间字符以外的一切字符
d 匹配0到9之间的数字
w 匹配字母、数字和下滑线字符
W 匹配用w不能匹配的任意字符


mysql> select 'google' regexp 'go+ogle', 'google' regexp 'go*ogle', 'google' reg
exp 'go?ogle';
+---------------------------+---------------------------+-----------------------
----+
| 'google' regexp 'go+ogle' | 'google' regexp 'go*ogle' | 'google' regexp 'go?og
le' |
+---------------------------+---------------------------+-----------------------
----+
| 1 | 1 |
1 |
+---------------------------+---------------------------+-----------------------
----+
1 row in set (0.00 sec)

mysql> select 'google' regexp 'go+gle', 'google' regexp 'go*gle', 'google' regex
p 'go?gle';
+--------------------------+--------------------------+-------------------------
-+
| 'google' regexp 'go+gle' | 'google' regexp 'go*gle' | 'google' regexp 'go?gle'
|
+--------------------------+--------------------------+-------------------------
-+
| 1 | 1 | 0
|
+--------------------------+--------------------------+-------------------------
-+
1 row in set (0.00 sec)

mysql> select 'google' regexp 'gooo+gle', 'google' regexp 'gooo*gle', 'google' r
egexp 'gooo?gle';
+----------------------------+----------------------------+---------------------
-------+
| 'google' regexp 'gooo+gle' | 'google' regexp 'gooo*gle' | 'google' regexp 'goo
o?gle' |
+----------------------------+----------------------------+---------------------
-------+
| 0 | 1 |
1 |
+----------------------------+----------------------------+---------------------
-------+
1 row in set (0.00 sec)

mysql> select 'google' regexp '^goo', 'google' regexp 'goo$';
+------------------------+------------------------+
| 'google' regexp '^goo' | 'google' regexp 'goo$' |
+------------------------+------------------------+
| 1 | 0 |
+------------------------+------------------------+
1 row in set (0.00 sec)

mysql> select 'google' regexp '^gle', 'google' regexp 'gle$';
+------------------------+------------------------+
| 'google' regexp '^gle' | 'google' regexp 'gle$' |
+------------------------+------------------------+
| 0 | 1 |
+------------------------+------------------------+
1 row in set (0.00 sec)

mysql> select 'fifi' regexp '^fi', 'fifi' regexp 'fi$', 'fifi' regexp '^fi$', 'f
ifi' regexp '^fifi$';
+---------------------+---------------------+----------------------+------------
------------+
| 'fifi' regexp '^fi' | 'fifi' regexp 'fi$' | 'fifi' regexp '^fi$' | 'fifi' rege
xp '^fifi$' |
+---------------------+---------------------+----------------------+------------
------------+
| 1 | 1 | 0 |
1 |
+---------------------+---------------------+----------------------+------------
------------+
1 row in set (0.00 sec)


3.系统信息函数

下面列举一些常用的系统信息函数：

user()或system_user() 返回当前登陆用户名
connection_id() 返回当前用户的连接ID
database() 返回当前数据库名
version() 返回MySQL服务器的版本

mysql> select user(), connection_id(), database(), version();
+----------------+-----------------+------------+------------------+
| user() | connection_id() | database() | version() |
+----------------+-----------------+------------+------------------+
| root@localhost | 2 | ggyy | 5.1.34-community |
+----------------+-----------------+------------+------------------+
1 row in set (0.40 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11662464/viewspace-1030653/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11662464/viewspace-1030653/