Mysql基礎教程 mysql進階

Mysql比較運算符



比較運算符形式


符號

形式

作用

=

X1=X2

判斷是否相等

<>或!=

X1<>X2或 X1!=X2

判斷是否不相等

<=>

X1<=>X2

判斷是否相等,可以判斷是否相等NULL

>、>=

X1>X2 、X1>=X2

判斷是否大于等于

<、<=

X1<X2 、X1<=X2

判斷是否小于等于

IS NULL或IS NOT

 NULL

X1 IS NULL

X1 IS NOT NULL

判斷是否等于NULL

BETWEEN AND或

NOT BETWEEN

X1 BETWEEN m

AND n

判斷是否在范圍內

IN 或NOT IN

X1 IN(值1…)

判斷是否在,某一個固定范圍內

LIKE 或NOT LIKE

X1 LIKE 表達式

判斷是否匹配

REGEXP

X1 REGEXP 正則

判斷是否正則匹配



示例


比較運算符的結果不是為真就是假。


比較運算符“1=1”


mysql> SELECT 1=1;
+-----+
| 1=1 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)


比較運算符1=1,1='1'


mysql> SELECT 1=1,1='1';
+-----+-------+
| 1=1 | 1='1' |
+-----+-------+
|   1 |     1 |
+-----+-------+
1 row in set (0.00 sec)


比較運算符1=1,1='1',1=2


mysql> SELECT 1=1,1='1',1=2;
+-----+-------+-----+
| 1=1 | 1='1' | 1=2 |
+-----+-------+-----+
|   1 |     1 |   0 |
+-----+-------+-----+
1 row in set (0.00 sec)


查看學員表查詢用戶名,查看是否為真(=)


mysql> SELECT username,username='king' FROM student;
+----------+-----------------+
| username | username='king' |
+----------+-----------------+
| king     |               1 |
| king1    |               0 |
| king2    |               0 |
| king3    |               0 |
| king4    |               0 |
| king5    |               0 |
| king6    |               0 |
| king7    |               0 |
| king8    |               0 |
+----------+-----------------+
9 rows in set (0.00 sec)


查看學員表查詢用戶名,查看是否為真(!=)


mysql> SELECT username,username!='king' FROM student;
+----------+------------------+
| username | username!='king' |
+----------+------------------+
| king     |                0 |
| king1    |                1 |
| king2    |                1 |
| king3    |                1 |
| king4    |                1 |
| king5    |                1 |
| king6    |                1 |
| king7    |                1 |
| king8    |                1 |
+----------+------------------+
9 rows in set (0.00 sec)


查看用戶表的記錄


mysql> SELECT * FROM cms_user;
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password| email       | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  1 | 張三 | zhangsan| [email protected] | 1419811708 | user.jpg |     1 |   21 | 男     |
|  2 | 張三豐 | zhangsanfeng | [email protected] | 1419812708 | user.jpg |  2 |31 | 女     |
|  3 | 章子怡 | zhangsan| [email protected] | 1419813708 | user.jpg | 3 |   43 | 男     |
|  4 | long      | long | [email protected] | 1419814708 | user.jpg |     4 |   41 | 女     |
|  5 | ring      | ring  | [email protected] | 1419815708 | user.jpg |     2 |    9 | 男     |
|  6 | queen | queen | [email protected] | 1419861708 | user.jpg |     3 |   77 | 女     |
|  8 | blek      | blek  | [email protected] | 1419818708 | user.jpg |     1 |   85 | 女     |
|  9 | rose      | rose | [email protected] | 1419821708 | user.jpg |     2 |    9 | 男     |
| 10 | lily      | lily  | [email protected] | 1419831708 | user.jpg |     2 |   39 | 女     |
| 11 | john      | john | [email protected] | 1419841708 | user.jpg |     2 |   72 | 保密   |
| 12 | test1     | test1 | [email protected] | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2     | TEST2 | [email protected] | 1381203974 | user.jpg |    20 |   18 | NULL   |
| 14 | lll       | lll    | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
| 15 | ttt       | lll  | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
| 16 | ooo       | lll   | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
15 rows in set (0.00 sec)


查看用戶表的id,username,age,sex,age=null的字段


mysql> SELECT id,username,age,sex,age=null FROM cms_user;
+----+-----------+------+--------+----------+
| id | username  | age  | sex    | age=null |
+----+-----------+------+--------+----------+
|  1 | 張三      |   21 | 男     |     NULL |
|  2 | 張三豐    |   31 | 女     |     NULL |
|  3 | 章子怡    |   43 | 男     |     NULL |
|  4 | long      |   41 | 女     |     NULL |
|  5 | ring      |    9 | 男     |     NULL |
|  6 | queen     |   77 | 女     |     NULL |
|  8 | blek      |   85 | 女     |     NULL |
|  9 | rose      |    9 | 男     |     NULL |
| 10 | lily      |   39 | 女     |     NULL |
| 11 | john      |   72 | 保密   |     NULL |
| 12 | test1     | NULL | 保密   |     NULL |
| 13 | TEST2     |   18 | NULL   |     NULL |
| 14 | lll       |   18 | NULL   |     NULL |
| 15 | ttt       |   18 | NULL   |     NULL |
| 16 | ooo       |   18 | NULL   |     NULL |
+----+-----------+------+--------+----------+
15 rows in set (0.00 sec)


查看用戶表的id,username,age,sex,age<=>null的字段


mysql> SELECT id,username,age,sex,age<=>null FROM cms_user;
+----+-----------+------+--------+------------+
| id | username  | age  | sex    | age<=>null |
+----+-----------+------+--------+------------+
|  1 | 張三      |   21 | 男     |          0 |
|  2 | 張三豐    |   31 | 女     |          0 |
|  3 | 章子怡    |   43 | 男     |          0 |
|  4 | long      |   41 | 女     |          0 |
|  5 | ring      |    9 | 男     |          0 |
|  6 | queen     |   77 | 女     |          0 |
|  8 | blek      |   85 | 女     |          0 |
|  9 | rose      |    9 | 男     |          0 |
| 10 | lily      |   39 | 女     |          0 |
| 11 | john      |   72 | 保密   |          0 |
| 12 | test1     | NULL | 保密   |          1 |
| 13 | TEST2     |   18 | NULL   |          0 |
| 14 | lll       |   18 | NULL   |          0 |
| 15 | ttt       |   18 | NULL   |          0 |
| 16 | ooo       |   18 | NULL   |          0 |
+----+-----------+------+--------+------------+
15 rows in set (0.03 sec)


查看用戶表的id,username,age,sex, ,sex<=>NULL的字段


mysql> SELECT id,username,age,sex,sex<=>NULL FROM cms_user;
+----+-----------+------+--------+------------+
| id | username  | age  | sex    | sex<=>NULL |
+----+-----------+------+--------+------------+
|  1 | 張三      |   21 | 男     |          0 |
|  2 | 張三豐    |   31 | 女     |          0 |
|  3 | 章子怡    |   43 | 男     |          0 |
|  4 | long      |   41 | 女     |          0 |
|  5 | ring      |    9 | 男     |          0 |
|  6 | queen     |   77 | 女     |          0 |
|  8 | blek      |   85 | 女     |          0 |
|  9 | rose      |    9 | 男     |          0 |
| 10 | lily      |   39 | 女     |          0 |
| 11 | john      |   72 | 保密   |          0 |
| 12 | test1     | NULL | 保密   |          0 |
| 13 | TEST2     |   18 | NULL   |          1 |
| 14 | lll       |   18 | NULL   |          1 |
| 15 | ttt       |   18 | NULL   |          1 |
| 16 | ooo       |   18 | NULL   |          1 |
+----+-----------+------+--------+------------+
15 rows in set (0.00 sec)


查看用戶表的id,username, score,score>=70的字段(取得獎學金的學員)


mysql> SELECT id,username,score,score>=70 FROM student;
+----+----------+-------+-----------+
| id | username | score | score>=70 |
+----+----------+-------+-----------+
|  1 | king     |    95 |         1 |
|  2 | king1    |    35 |         0 |
|  3 | king2    |    45 |         0 |
|  4 | king3    |    55 |         0 |
|  5 | king4    |    65 |         0 |
|  6 | king5    |    75 |         1 |
|  7 | king6    |    80 |         1 |
|  8 | king7    |    90 |         1 |
|  9 | king8    |    25 |         0 |
+----+----------+-------+-----------+
9 rows in set (0.00 sec)


看用戶表的id,username, ,age,age IS NULL的字段(檢測是否為空)


mysql> SELECT id,username,age,age IS NULL FROM cms_user;
+----+-----------+------+-------------+
| id | username  | age  | age IS NULL |
+----+-----------+------+-------------+
|  1 | 張三      |   21 |          0 |
|  2 | 張三豐    |   31 |           0 |
|  3 | 章子怡    |   43 |           0 |
|  4 | long      |   41 |           0 |
|  5 | ring      |    9 |           0 |
|  6 | queen     |   77 |           0 |
|  8 | blek      |   85 |           0 |
|  9 | rose      |    9 |           0 |
| 10 | lily      |   39 |           0 |
| 11 | john      |   72 |           0 |
| 12 | test1     | NULL |           1 |
| 13 | TEST2     |   18 |           0 |
| 14 | lll       |   18 |           0 |
| 15 | ttt       |   18 |           0 |
| 16 | ooo       |   18 |           0 |
+----+-----------+------+-------------+
15 rows in set (0.00 sec)


查看用戶表的id,username, ,age,age IS NULL的字段(檢測是否為空)


mysql> SELECT id,username,age,age IS NOT NULL FROM cms_user;
+----+-----------+------+-----------------+
| id | username  | age  | age IS NOT NULL |
+----+-----------+------+-----------------+
|  1 | 張三      |   21 |               1 |
|  2 | 張三豐    |   31 |               1 |
|  3 | 章子怡    |   43 |               1 |
|  4 | long      |   41 |               1 |
|  5 | ring      |    9 |               1 |
|  6 | queen     |   77 |               1 |
|  8 | blek      |   85 |               1 |
|  9 | rose      |    9 |               1 |
| 10 | lily      |   39 |               1 |
| 11 | john      |   72 |               1 |
| 12 | test1     | NULL |               0 |
| 13 | TEST2     |   18 |               1 |
| 14 | lll       |   18 |               1 |
| 15 | ttt       |   18 |               1 |
| 16 | ooo       |   18 |               1 |
+----+-----------+------+-----------------+
15 rows in set (0.00 sec)


判斷年齡在10到30是否在范圍內


mysql> SELECT id,username,age,age BETWEEN 10 AND 30 FROM cms_user;
+----+-----------+------+-----------------------+
| id | username  | age  | age BETWEEN 10 AND 30 |
+----+-----------+------+-----------------------+
|  1 | 張三      |   21 |                     1 |
|  2 | 張三豐    |   31 |                     0 |
|  3 | 章子怡    |   43 |                     0 |
|  4 | long      |   41 |                     0 |
|  5 | ring      |    9 |                     0 |
|  6 | queen     |   77 |                     0 |
|  8 | blek      |   85 |                     0 |
|  9 | rose      |    9 |                     0 |
| 10 | lily      |   39 |                     0 |
| 11 | john      |   72 |                     0 |
| 12 | test1     | NULL |                  NULL |
| 13 | TEST2     |   18 |                     1 |
| 14 | lll       |   18 |                     1 |
| 15 | ttt       |   18 |                     1 |
| 16 | ooo       |   18 |                     1 |
+----+-----------+------+-----------------------+
15 rows in set (0.02 sec)


檢測年齡是否有(21,31,41,51)內的人


mysql> SELECT id,username,age,age IN(21,31,41,51) FROM cms_user;
+----+-----------+------+---------------------+
| id | username  | age  | age IN(21,31,41,51) |
+----+-----------+------+---------------------+
|  1 | 張三      |   21 |                   1 |
|  2 | 張三豐    |   31 |                   1 |
|  3 | 章子怡    |   43 |                   0 |
|  4 | long      |   41 |                   1 |
|  5 | ring      |    9 |                   0 |
|  6 | queen     |   77 |                   0 |
|  8 | blek      |   85 |                   0 |
|  9 | rose      |    9 |                   0 |
| 10 | lily      |   39 |                   0 |
| 11 | john      |   72 |                   0 |
| 12 | test1     | NULL |                NULL |
| 13 | TEST2     |   18 |                   0 |
| 14 | lll       |   18 |                   0 |
| 15 | ttt       |   18 |                   0 |
| 16 | ooo       |   18 |                   0 |
+----+-----------+------+---------------------+
15 rows in set (0.00 sec)


查看在1(1,2,3)內的


mysql> SELECT 1 IN (1,2,3);
+--------------+
| 1 IN (1,2,3) |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)


查看在11(1,2,3)內的


mysql> SELECT 11 IN (1,2,3);
+---------------+
| 11 IN (1,2,3) |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)


查詢用戶名s為1的(錯的)


mysql> SELECT s LIKE '_';
ERROR 1054 (42S22): Unknown column 's' in 'field list'


查詢用戶名s為1的(字符串)


mysql> SELECT 's' LIKE '_';
+--------------+
| 's' LIKE '_' |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)


查詢用戶名sD為1的(字符串)


mysql> SELECT 'sD' LIKE '_';
+---------------+
| 'sD' LIKE '_' |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)


查看用戶表的id,username,username LIKE '____'


mysql> SELECT id,username,username LIKE '____' FROM cms_user;
+----+-----------+----------------------+
| id | username  | username LIKE '____' |
+----+-----------+----------------------+
|  8 | blek      |                    1 |
| 11 | john      |                    1 |
| 10 | lily      |                    1 |
| 14 | lll       |                    0 |
|  4 | long      |                    1 |
| 16 | ooo       |                    0 |
|  6 | queen     |                    0 |
|  5 | ring      |                    1 |
|  9 | rose      |                    1 |
| 12 | test1     |                    0 |
| 13 | TEST2     |                    0 |
| 15 | ttt       |                    0 |
|  1 | 張三      |                    0 |
|  2 | 張三豐    |                    0 |
|  3 | 章子怡    |                    0 |
+----+-----------+----------------------+
15 rows in set (0.00 sec)


查看用戶表的id,username,username REGEXP '^t'


mysql> SELECT id,username,username REGEXP '^t' FROM cms_user;
+----+-----------+----------------------+
| id | username  | username REGEXP '^t' |
+----+-----------+----------------------+
|  8 | blek      |       、            0 |
| 11 | john      |                    0 |
| 10 | lily      |                    0 |
| 14 | lll       |                    0 |
|  4 | long      |                    0 |
| 16 | ooo       |                    0 |
|  6 | queen     |                    0 |
|  5 | ring      |                    0 |
|  9 | rose      |                    0 |
| 12 | test1     |                    1 |
| 13 | TEST2     |                    1 |
| 15 | ttt       |                    1 |
|  1 | 張三      |                    0 |
|  2 | 張三豐    |                    0 |
|  3 | 章子怡    |                    0 |
+----+-----------+----------------------+
15 rows in set (0.00 sec)



【本文由麥子學院獨家原創,轉載請注明出處并保留原文鏈接】

logo
? 2012-2016 www.jvbprd.live
蜀ICP備13014270號-4 Version 5.0.0 release20160127

免費領取價值1888元求職寶典!

客服熱線 400-862-8862

回到頂部

彩票安徽25选5