01 基础操作

1.1 SELECT

DISTINCT

DISTINCT的返回结果会去掉相同的值。

mysql> SELECT vend_id FROM products;
+---------+
| vend_id |
+---------+
| 1001 |
| 1001 |
| 1001 |
| 1002 |
| 1002 |
| 1003 |
| 1003 |
| 1003 |
| 1003 |
| 1003 |
| 1003 |
| 1003 |
| 1005 |
| 1005 |
+---------+
14 rows in set (0.00 sec)
mysql> SELECT DISTINCT vend_id FROM products;
+---------+
| vend_id |
+---------+
| 1001 |
| 1002 |
| 1003 |
| 1005 |
+---------+
4 rows in set (0.00 sec)

但是注意DISTINCT不能对部分列使用,而会对它后面跟着的所有列起作用。而跟上多个列之后,相同指的是多个列的组合相同。

mysql> SELECT DISTINCT vend_id, prod_name FROM products;
+---------+----------------+
| vend_id | prod_name |
+---------+----------------+
| 1001 | .5 ton anvil |
| 1001 | 1 ton anvil |
| 1001 | 2 ton anvil |
| 1003 | Detonator |
| 1003 | Bird seed |
| 1003 | Carrots |
| 1002 | Fuses |
| 1005 | JetPack 1000 |
| 1005 | JetPack 2000 |
| 1002 | Oil can |
| 1003 | Safe |
| 1003 | Sling |
| 1003 | TNT (1 stick) |
| 1003 | TNT (5 sticks) |
+---------+----------------+
14 rows in set (0.01 sec)

LIMIT

显示显示的数量。如果后面是形如 LIMIT x,y; 则x是从0开始的。也就是说要显示第一行结果,用LIMIT 0,1;

1.2 排序检索数据

下面的语句会根据prod_price排序。如果prod_price相同,则再按prod_name排序。

mysql> SELECT prod_id, prod_price, prod_name
-> FROM products
-> ORDER BY prod_price, prod_name;

默认是升序排序,如果要降序排序,可以在ORDER BY的后面加DESC。DESC只对一列起作用,如果要多列都降序,则需要再每一列的后面都加DESC。下面的语句只会对prod_price降序排序,prod_name还是升序。

mysql> SELECT prod_id, prod_price, prod_name
-> FROM products
-> ORDER BY prod_price DESC, prod_name;

与DESC相反的是ASC,但是默认就是升序,所以ASC没太大用。

1.3 过滤数据

这一小节讲WHERE的用法

BETWEEN

mysql> SELECT prod_name, prod_price
-> FROM products
-> WHERE prod_price BETWEEN 5 AND 10;
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| Bird seed | 10.00 |
| Oil can | 8.99 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
5 rows in set (0.00 sec)

空值检查 IS NULL

注意NULL和任何值比较都是false,包括NULL=NULL也是false。

mysql> SELECT cust_id
-> FROM customers
-> WHERE cust_email IS NULL;
+---------+
| cust_id |
+---------+
| 10002 |
| 10005 |
+---------+
2 rows in set (0.00 sec)

AND OR

AND的优先级高于OR

mysql> SELECT prod_id, prod_price, prod_name
-> FROM products
-> WHERE vend_id = 1003 AND prod_price <= 10;
+---------+------------+----------------+
| prod_id | prod_price | prod_name |
+---------+------------+----------------+
| FB | 10.00 | Bird seed |
| FC | 2.50 | Carrots |
| SLING | 4.49 | Sling |
| TNT1 | 2.50 | TNT (1 stick) |
| TNT2 | 10.00 | TNT (5 sticks) |
+---------+------------+----------------+
5 rows in set (0.01 sec)

IN

mysql> SELECT prod_name, prod_price
-> FROM products
-> WHERE vend_id IN (1002, 1003)
-> ORDER BY prod_name;

另外可加NOT对IN进行取反。NOT可以对IN、BETWEEN和EXISTS进行使用。

mysql> SELECT prod_name, prod_price
-> FROM products
-> WHERE vend_id NOT IN (1002, 1003)
-> ORDER BY prod_name;

1.4 通配符

LIKE pattern。通配符搜索的处理性能较差。

  • %通配符,匹配任何字符出现任意次数

注意%可以匹配任何东西,但是不能匹配NULL

mysql> SELECT prod_id, prod_name
-> FROM products
-> WHERE prod_name LIKE 'jet%';
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| JP1000 | JetPack 1000 |
| JP2000 | JetPack 2000 |
+---------+--------------+
2 rows in set (0.01 sec)
  • _通配符,匹配单个字符

mysql> SELECT prod_id, prod_name
-> FROM products
-> WHERE prod_name LIKE '_ ton anvil';
+---------+-------------+
| prod_id | prod_name |
+---------+-------------+
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
+---------+-------------+
2 rows in set (0.00 sec)

1.5 正则表达式

注意通配符是完全匹配,而正则表达式是部分匹配。

mysql> SELECT prod_name
-> FROM products
-> WHERE prod_name REGEXP '1000'
-> ORDER BY prod_name;
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
+--------------+
1 row in set (0.02 sec)

匹配任意一个字符 .

mysql> SELECT prod_name
-> FROM products
-> WHERE prod_name REGEXP '.000'
-> ORDER BY prod_name;
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.01 sec)

进行OR匹配 |

mysql> SELECT prod_name
-> FROM products
-> WHERE prod_name REGEXP '1000|2000'
-> ORDER BY prod_name;
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.01 sec)

匹配几个字符之一 []

mysql> SELECT prod_name
-> FROM products
-> WHERE prod_name REGEXP '[123] Ton'
-> ORDER BY prod_name;
+-------------+
| prod_name |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.00 sec)

在字符集合里可以用\^来表示否定,[\^123]匹配除123之外的其他字符。

在字符集合中还可以使用-表示范围,[0-9]匹配数字,[a-z]匹配字母。

mysql> SELECT prod_name
-> FROM products
-> WHERE prod_name REGEXP '[1-5] Ton'
-> ORDER BY prod_name;
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
+--------------+
3 rows in set (0.01 sec)

mysql中的转义\\

mysql中要对. [] |等特殊字符转义,需要使用两个反斜杠。

一些预定义的字符集

这个表格只是说明写法,其他还有很多字符集,使用的时候查阅即可。

说明

[:alnum:]

任意字母或数字

[:alpha:]

任意字母

[:digit:]

任意数字

匹配多个

字符

说明

*

0或多个匹配

+

1或多个匹配(等于{1,})

?

0或1个匹配(等于{0,1})

{n}

匹配n个

{n,}

匹配不少于n个

{n,m}

匹配n到m个,m不超过255

s后面加?,使得s变成可选的。

mysql> SELECT prod_name
-> FROM products
-> WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
-> ORDER BY prod_name;
+----------------+
| prod_name |
+----------------+
| TNT (1 stick) |
| TNT (5 sticks) |
+----------------+
2 rows in set (0.01 sec)

匹配4个数字

mysql> SELECT prod_name
-> FROM products
-> WHERE prod_name REGEXP '[:digit:]{4}'
-> ORDER BY prod_name;
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.01 sec)

定位符 \^ $

匹配数字或者以小数点开始

mysql> SELECT prod_name
-> FROM products
-> WHERE prod_name REGEXP '^[0-9\\.]'
-> ORDER BY prod_name;
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
+--------------+
3 rows in set (0.00 sec)

注意\^有两个含义。这里是指定开始。则字符集合中则表示取反。

测试正则表达式

可以用SELECT来测试,成功匹配返回1,否则返回0;

mysql> SELECT 'hello' REGEXP '[0-9]';
+------------------------+
| 'hello' REGEXP '[0-9]' |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.00 sec)
mysql> SELECT 'hello0' REGEXP '[0-9]';
+-------------------------+
| 'hello0' REGEXP '[0-9]' |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)