溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
6.1、基本查詢語句
MySQL從數據表中查詢數據的基本語句為SELECT語句，其基本格式為：
SELECT
{* | <字段列表>}
[
FROM <表1>,<表2>...
[WHERE 表達式
[GROUP BY <group by definition>]
[HAVING <expression> [{<operator> <expression>}...]]
[ORDER BY <order by definition>]
[LIMIT [<offset>,] <row count> ]
]
]
SELECT [字段1,字段2,...,字段n]
FROM [表或視圖]
WHERE [查詢條件]各條子句的含義：
{* | <字段列表>}：包含星號通配符選擇字段列表，表示查詢的字段，其字段列至少包含一個字段名稱。 FROM <表1>,<表2>...：表1，表2表示查詢數據的來源 WHERE 表達式：可選項，限定查詢必須滿足的查詢條件 GROUP BY <字段>：告訴MySQL如何顯示查詢出來的數據，并按照指定字段分組 ORDER BY <字段>：告訴MySQL按什么順序顯示查詢的數據，可進行的排序ASC,DESC LIMIT [<offset>,] <row count>：告訴MySQL每次顯示查詢出來的數據條數 |
mysql> CREATE TABLE fruits
-> (
-> f_id char(10) NOT NULL,
-> s_id INT NOT NULL,
-> f_name char(255) NOT NULL,
-> f_price decimal(8,2) NOT NULL,
-> PRIMARY KEY(f_id)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO fruits (f_id, s_id, f_name, f_price)
-> VALUES('a1', 101,'apple',5.2),
-> ('b1',101,'blackberry', 10.2),
-> ('bs1',102,'orange', 11.2),
-> ('bs2',105,'melon',8.2),
-> ('t1',102,'banana', 10.3),
-> ('t2',102,'grape', 5.3),
-> ('o2',103,'coconut', 9.2),
-> ('c0',101,'cherry', 3.2),
-> ('a2',103, 'apricot',2.2),
-> ('l2',104,'lemon', 6.4),
-> ('b2',104,'berry', 7.6),
-> ('m1',106,'mango', 15.6),
-> ('m2',105,'xbabay', 2.6),
-> ('t4',107,'xbababa', 3.6),
-> ('m3',105,'xxtt', 11.6),
-> ('b5',107,'xxxx', 3.6);
Query OK, 16 rows affected (0.09 sec)
Records: 16 Duplicates: 0 Warnings: 06.2、單表查詢
查詢所有字段
語法： SELECT * FROM 表名
mysql> SELECT * FROM fruits; +------+------+------------+---------+ | f_id | s_id | f_name | f_price | +------+------+------------+---------+ | a1 | 101 | apple | 5.20 | | a2 | 103 | apricot | 2.20 | | b1 | 101 | blackberry | 10.20 | | b2 | 104 | berry | 7.60 | | b5 | 107 | xxxx | 3.60 | | bs1 | 102 | orange | 11.20 | | bs2 | 105 | melon | 8.20 | | c0 | 101 | cherry | 3.20 | | l2 | 104 | lemon | 6.40 | | m1 | 106 | mango | 15.60 | | m2 | 105 | xbabay | 2.60 | | m3 | 105 | xxtt | 11.60 | | o2 | 103 | coconut | 9.20 | | t1 | 102 | banana | 10.30 | | t2 | 102 | grape | 5.30 | | t4 | 107 | xbababa | 3.60 | +------+------+------------+---------+ 16 rows in set (0.00 sec)
查詢指定字段
語法： SELECT 字段名1,字段名2,... FROM 表名
mysql> SELECT f_name FROM fruits; +------------+ | f_name | +------------+ | apple | | apricot | | blackberry | | berry | | xxxx | | orange | | melon | | cherry | | lemon | | mango | | xbabay | | xxtt | | coconut | | banana | | grape | | xbababa | +------------+ 16 rows in set (0.00 sec) mysql> SELECT f_name,f_price FROM fruits; +------------+---------+ | f_name | f_price | +------------+---------+ | apple | 5.20 | | apricot | 2.20 | | blackberry | 10.20 | | berry | 7.60 | | xxxx | 3.60 | | orange | 11.20 | | melon | 8.20 | | cherry | 3.20 | | lemon | 6.40 | | mango | 15.60 | | xbabay | 2.60 | | xxtt | 11.60 | | coconut | 9.20 | | banana | 10.30 | | grape | 5.30 | | xbababa | 3.60 | +------------+---------+ 16 rows in set (0.00 sec)
查詢指定記錄
語法： SELECT 字段名1,字段名2,... FROM 表名 WHERE 查詢條件
WHERE條件判斷符
| 操作符 | 說明 |
| = | 相等 |
| <>,!= | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| BETWEEN | 位于兩值之間 |
mysql> SELECT f_name, f_price -> FROM fruits -> WHERE f_price = 10.2; +------------+---------+ | f_name | f_price | +------------+---------+ | blackberry | 10.20 | +------------+---------+ 1 row in set (0.00 sec) mysql> SELECT f_name, f_price -> FROM fruits -> WHERE f_name = 'apple'; +--------+---------+ | f_name | f_price | +--------+---------+ | apple | 5.20 | +--------+---------+ 1 row in set (0.00 sec) mysql> SELECT f_name, f_price -> FROM fruits -> WHERE f_price < 10; +---------+---------+ | f_name | f_price | +---------+---------+ | apple | 5.20 | | apricot | 2.20 | | berry | 7.60 | | xxxx | 3.60 | | melon | 8.20 | | cherry | 3.20 | | lemon | 6.40 | | xbabay | 2.60 | | coconut | 9.20 | | grape | 5.30 | | xbababa | 3.60 | +---------+---------+ 11 rows in set (0.00 sec)
帶IN關鍵字的查詢
IN操作符用來查詢滿足指定范圍內的條件記錄，使用IN操作符，將所有檢索條件用括號括起來，檢索條件之間用逗號分隔開，只要滿足條件范圍內的一個值即可。
mysql> SELECT s_id,f_name, f_price -> FROM fruits -> WHERE s_id IN (101,102) -> ORDER BY f_name; +------+------------+---------+ | s_id | f_name | f_price | +------+------------+---------+ | 101 | apple | 5.20 | | 102 | banana | 10.30 | | 101 | blackberry | 10.20 | | 101 | cherry | 3.20 | | 102 | grape | 5.30 | | 102 | orange | 11.20 | +------+------------+---------+ 6 rows in set (0.00 sec) mysql> SELECT s_id,f_name, f_price -> FROM fruits -> WHERE s_id NOT IN (101,102) -> ORDER BY f_name; +------+---------+---------+ | s_id | f_name | f_price | +------+---------+---------+ | 103 | apricot | 2.20 | | 104 | berry | 7.60 | | 103 | coconut | 9.20 | | 104 | lemon | 6.40 | | 106 | mango | 15.60 | | 105 | melon | 8.20 | | 107 | xbababa | 3.60 | | 105 | xbabay | 2.60 | | 105 | xxtt | 11.60 | | 107 | xxxx | 3.60 | +------+---------+---------+ 10 rows in set (0.00 sec)
帶BETWEEN AND的范圍查詢
BETWEEN AND用來查詢某個范圍內的值，該操作符需要兩個參數，即范圍的開始和結束，如果字段滿足指定的范圍查詢條件，則這些記錄被返回。
mysql> SELECT f_name, f_price -> FROM fruits -> WHERE f_price BETWEEN 2.00 AND 10.20; +------------+---------+ | f_name | f_price | +------------+---------+ | apple | 5.20 | | apricot | 2.20 | | blackberry | 10.20 | | berry | 7.60 | | xxxx | 3.60 | | melon | 8.20 | | cherry | 3.20 | | lemon | 6.40 | | xbabay | 2.60 | | coconut | 9.20 | | grape | 5.30 | | xbababa | 3.60 | +------------+---------+ 12 rows in set (0.00 sec) mysql> SELECT f_name, f_price -> FROM fruits -> WHERE f_price NOT BETWEEN 2.00 AND 10.20; +--------+---------+ | f_name | f_price | +--------+---------+ | orange | 11.20 | | mango | 15.60 | | xxtt | 11.60 | | banana | 10.30 | +--------+---------+ 4 rows in set (0.00 sec)
帶LIKE的字符匹配查詢
%通配符，匹配任意長度任意字符
mysql> SELECT f_id, f_name -> FROM fruits -> WHERE f_name LIKE 'b%'; +------+------------+ | f_id | f_name | +------+------------+ | b1 | blackberry | | b2 | berry | | t1 | banana | +------+------------+ 3 rows in set (0.00 sec) mysql> SELECT f_id, f_name -> FROM fruits -> WHERE f_name LIKE '%g%'; +------+--------+ | f_id | f_name | +------+--------+ | bs1 | orange | | m1 | mango | | t2 | grape | +------+--------+ 3 rows in set (0.00 sec)
_通配符：匹配單個字符
mysql> SELECT f_id, f_name FROM fruits WHERE f_name LIKE '____e'; +------+--------+ | f_id | f_name | +------+--------+ | a1 | apple | | t2 | grape | +------+--------+ 2 rows in set (0.00 sec)
查詢空值
數據表創建的時候，可以指定某列中可以包含空值（NULL），空值不同于0，也不同于空字符串。空值一般表示數據未知、不適用或將在以后添加數據。使用IS NULL子句可以查詢某字段內容為空的記錄。
mysql> CREATE TABLE customers -> ( -> c_id int NOT NULL AUTO_INCREMENT, -> c_name char(50) NOT NULL, -> c_address char(50) NULL, -> c_city char(50) NULL, -> c_zip char(10) NULL, -> c_contact char(50) NULL, -> c_email char(255) NULL, -> PRIMARY KEY (c_id) -> ); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO customers(c_id, c_name, c_address, c_city, -> c_zip, c_contact, c_email) -> VALUES(10001, 'RedHook', '200 Street ', 'Tianjin', -> '300000', 'LiMing', 'LMing@163.com'), -> (10002, 'Stars', '333 Fromage Lane', -> 'Dalian', '116000', 'Zhangbo','Jerry@hotmail.com'), -> (10003, 'Netbhood', '1 Sunny Place', 'Qingdao', '266000', -> 'LuoCong', NULL), -> (10004, 'JOTO', '829 Riverside Drive', 'Haikou', -> '570000', 'YangShan', 'sam@hotmail.com'); Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NULL; +-------+----------+---------+ | c_id | c_name | c_email | +-------+----------+---------+ | 10003 | Netbhood | NULL | +-------+----------+---------+ 1 row in set (0.00 sec) mysql> SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NOT NULL; +-------+---------+-------------------+ | c_id | c_name | c_email | +-------+---------+-------------------+ | 10001 | RedHook | LMing@163.com | | 10002 | Stars | Jerry@hotmail.com | | 10004 | JOTO | sam@hotmail.com | +-------+---------+-------------------+ 3 rows in set (0.00 sec)
帶AND的多條件查詢
mysql> SELECT f_id, f_price, f_name FROM fruits WHERE s_id = '101' AND f_price >=5;
+------+---------+------------+
| f_id | f_price | f_name |
+------+---------+------------+
| a1 | 5.20 | apple |
| b1 | 10.20 | blackberry |
+------+---------+------------+
2 rows in set (0.00 sec)
mysql> SELECT f_id, f_price, f_name FROM fruits
-> WHERE s_id IN('101', '102') AND f_price >= 5 AND f_name = 'apple';
+------+---------+--------+
| f_id | f_price | f_name |
+------+---------+--------+
| a1 | 5.20 | apple |
+------+---------+--------+
1 row in set (0.00 sec)帶OR的多條件查詢
mysql> SELECT s_id,f_name, f_price -> FROM fruits -> WHERE s_id = 101 OR s_id = 102; +------+------------+---------+ | s_id | f_name | f_price | +------+------------+---------+ | 101 | apple | 5.20 | | 101 | blackberry | 10.20 | | 102 | orange | 11.20 | | 101 | cherry | 3.20 | | 102 | banana | 10.30 | | 102 | grape | 5.30 | +------+------------+---------+ 6 rows in set (0.00 sec) mysql> SELECT s_id,f_name, f_price -> FROM fruits -> WHERE s_id IN(101,102); +------+------------+---------+ | s_id | f_name | f_price | +------+------------+---------+ | 101 | apple | 5.20 | | 101 | blackberry | 10.20 | | 102 | orange | 11.20 | | 101 | cherry | 3.20 | | 102 | banana | 10.30 | | 102 | grape | 5.30 | +------+------------+---------+ 6 rows in set (0.00 sec)
OR操作符和IN操作符使用后結果一樣，可以實現相同的功能。但是使用IN操作符使檢索語句更加簡明，并且IN執行的速度更快于OR。
查詢結果不重復
MySQL中使用DISTINCT關鍵字消除重復的記錄，其語法為：
SELECT DISTINCT 字段名 FROM 表名
mysql> SELECT s_id FROM fruits; +------+ | s_id | +------+ | 101 | | 103 | | 101 | | 104 | | 107 | | 102 | | 105 | | 101 | | 104 | | 106 | | 105 | | 105 | | 103 | | 102 | | 102 | | 107 | +------+ 16 rows in set (0.00 sec) mysql> SELECT DISTINCT s_id FROM fruits; +------+ | s_id | +------+ | 101 | | 103 | | 104 | | 107 | | 102 | | 105 | | 106 | +------+ 7 rows in set (0.00 sec)
對查詢的結果排序
單列排序
mysql> SELECT f_name FROM fruits; +------------+ | f_name | +------------+ | apple | | apricot | | blackberry | | berry | | xxxx | | orange | | melon | | cherry | | lemon | | mango | | xbabay | | xxtt | | coconut | | banana | | grape | | xbababa | +------------+ 16 rows in set (0.00 sec) mysql> SELECT f_name FROM fruits ORDER BY f_name; +------------+ | f_name | +------------+ | apple | | apricot | | banana | | berry | | blackberry | | cherry | | coconut | | grape | | lemon | | mango | | melon | | orange | | xbababa | | xbabay | | xxtt | | xxxx | +------------+ 16 rows in set (0.00 sec)
多列排序
mysql> SELECT f_name, f_price FROM fruits ORDER BY f_name, f_price; +------------+---------+ | f_name | f_price | +------------+---------+ | apple | 5.20 | | apricot | 2.20 | | banana | 10.30 | | berry | 7.60 | | blackberry | 10.20 | | cherry | 3.20 | | coconut | 9.20 | | grape | 5.30 | | lemon | 6.40 | | mango | 15.60 | | melon | 8.20 | | orange | 11.20 | | xbababa | 3.60 | | xbabay | 2.60 | | xxtt | 11.60 | | xxxx | 3.60 | +------------+---------+ 16 rows in set (0.00 sec)
指定方向排序
mysql> SELECT f_name, f_price FROM fruits ORDER BY f_price DESC; +------------+---------+ | f_name | f_price | +------------+---------+ | mango | 15.60 | | xxtt | 11.60 | | orange | 11.20 | | banana | 10.30 | | blackberry | 10.20 | | coconut | 9.20 | | melon | 8.20 | | berry | 7.60 | | lemon | 6.40 | | grape | 5.30 | | apple | 5.20 | | xxxx | 3.60 | | xbababa | 3.60 | | cherry | 3.20 | | xbabay | 2.60 | | apricot | 2.20 | +------------+---------+ 16 rows in set (0.00 sec) mysql> SELECT f_price, f_name FROM fruits ORDER BY f_price DESC, f_name; +---------+------------+ | f_price | f_name | +---------+------------+ | 15.60 | mango | | 11.60 | xxtt | | 11.20 | orange | | 10.30 | banana | | 10.20 | blackberry | | 9.20 | coconut | | 8.20 | melon | | 7.60 | berry | | 6.40 | lemon | | 5.30 | grape | | 5.20 | apple | | 3.60 | xbababa | | 3.60 | xxxx | | 3.20 | cherry | | 2.60 | xbabay | | 2.20 | apricot | +---------+------------+ 16 rows in set (0.00 sec)
分組查詢
分組查詢是對數據按照某個或多個字段進行分組，MySQL中使用GROUP BY 關鍵字對數據進行分組，基本語法形式為：
[GROUP BY 字段] [HAVING <條件表達式>]
創建分組
GROUP BY關鍵字通常和集合函數一起使用，如MAX()、MIN()、COUNT()、SUM()、AVG()。
mysql> SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id; +------+-------+ | s_id | Total | +------+-------+ | 101 | 3 | | 102 | 3 | | 103 | 2 | | 104 | 2 | | 105 | 3 | | 106 | 1 | | 107 | 2 | +------+-------+ 7 rows in set (0.00 sec) mysql> SELECT s_id, GROUP_CONCAT(f_name) AS Names FROM fruits GROUP BY s_id; +------+-------------------------+ | s_id | Names | +------+-------------------------+ | 101 | apple,blackberry,cherry | | 102 | grape,banana,orange | | 103 | apricot,coconut | | 104 | lemon,berry | | 105 | xbabay,xxtt,melon | | 106 | mango | | 107 | xxxx,xbababa | +------+-------------------------+ 7 rows in set (0.00 sec)
使用HAVING過濾分組
mysql> SELECT s_id, GROUP_CONCAT(f_name) AS Names -> FROM fruits -> GROUP BY s_id HAVING COUNT(f_name) > 1; +------+-------------------------+ | s_id | Names | +------+-------------------------+ | 101 | apple,blackberry,cherry | | 102 | grape,banana,orange | | 103 | apricot,coconut | | 104 | lemon,berry | | 105 | xbabay,xxtt,melon | | 107 | xxxx,xbababa | +------+-------------------------+ 6 rows in set (0.00 sec)
在GROUP BY 子句中使用WITH ROLLUP，顯示查詢出的所有記錄總和
mysql> SELECT s_id, COUNT(*) AS Total -> FROM fruits -> GROUP BY s_id WITH ROLLUP; +------+-------+ | s_id | Total | +------+-------+ | 101 | 3 | | 102 | 3 | | 103 | 2 | | 104 | 2 | | 105 | 3 | | 106 | 1 | | 107 | 2 | | NULL | 16 | +------+-------+ 8 rows in set (0.00 sec)
多字段分組
mysql> SELECT * FROM fruits group by s_id,f_name; +------+------+------------+---------+ | f_id | s_id | f_name | f_price | +------+------+------------+---------+ | a1 | 101 | apple | 5.20 | | b1 | 101 | blackberry | 10.20 | | c0 | 101 | cherry | 3.20 | | t1 | 102 | banana | 10.30 | | t2 | 102 | grape | 5.30 | | bs1 | 102 | orange | 11.20 | | a2 | 103 | apricot | 2.20 | | o2 | 103 | coconut | 9.20 | | b2 | 104 | berry | 7.60 | | l2 | 104 | lemon | 6.40 | | bs2 | 105 | melon | 8.20 | | m2 | 105 | xbabay | 2.60 | | m3 | 105 | xxtt | 11.60 | | m1 | 106 | mango | 15.60 | | t4 | 107 | xbababa | 3.60 | | b5 | 107 | xxxx | 3.60 | +------+------+------------+---------+ 16 rows in set (0.00 sec)
GROUP BY 和ORDER BY一起使用
mysql> CREATE TABLE orderitems -> ( -> o_num int NOT NULL, -> o_item int NOT NULL, -> f_id char(10) NOT NULL, -> quantity int NOT NULL, -> item_price decimal(8,2) NOT NULL, -> PRIMARY KEY (o_num,o_item) -> ) ; Query OK, 0 rows affected (0.09 sec) mysql> INSERT INTO orderitems(o_num, o_item, f_id, quantity, item_price) -> VALUES(30001, 1, 'a1', 10, 5.2), -> (30001, 2, 'b2', 3, 7.6), -> (30001, 3, 'bs1', 5, 11.2), -> (30001, 4, 'bs2', 15, 9.2), -> (30002, 1, 'b3', 2, 20.0), -> (30003, 1, 'c0', 100, 10), -> (30004, 1, 'o2', 50, 2.50), -> (30005, 1, 'c0', 5, 10), -> (30005, 2, 'b1', 10, 8.99), -> (30005, 3, 'a2', 10, 2.2), -> (30005, 4, 'm1', 5, 14.99); Query OK, 11 rows affected (0.08 sec) Records: 11 Duplicates: 0 Warnings: 0 mysql> SELECT o_num, SUM(quantity * item_price) AS orderTotal -> FROM orderitems -> GROUP BY o_num -> HAVING SUM(quantity*item_price) >= 100; +-------+------------+ | o_num | orderTotal | +-------+------------+ | 30001 | 268.80 | | 30003 | 1000.00 | | 30004 | 125.00 | | 30005 | 236.85 | +-------+------------+ 4 rows in set (0.00 sec) mysql> SELECT o_num, SUM(quantity * item_price) AS orderTotal -> FROM orderitems -> GROUP BY o_num -> HAVING SUM(quantity*item_price) >= 100 -> ORDER BY orderTotal; +-------+------------+ | o_num | orderTotal | +-------+------------+ | 30004 | 125.00 | | 30005 | 236.85 | | 30001 | 268.80 | | 30003 | 1000.00 | +-------+------------+ 4 rows in set (0.00 sec)
使用LIMIT限制查詢結果數量
使用LIMIT關鍵字，可以限制查詢結果的數量，語法格式為：
LIMIT [位置偏移量,] 行數
mysql> SELECT * From fruits LIMIT 4; +------+------+------------+---------+ | f_id | s_id | f_name | f_price | +------+------+------------+---------+ | a1 | 101 | apple | 5.20 | | a2 | 103 | apricot | 2.20 | | b1 | 101 | blackberry | 10.20 | | b2 | 104 | berry | 7.60 | +------+------+------------+---------+ 4 rows in set (0.00 sec) mysql> SELECT * From fruits LIMIT 4, 3; +------+------+--------+---------+ | f_id | s_id | f_name | f_price | +------+------+--------+---------+ | b5 | 107 | xxxx | 3.60 | | bs1 | 102 | orange | 11.20 | | bs2 | 105 | melon | 8.20 | +------+------+--------+---------+ 3 rows in set (0.00 sec)
6.3、使用聚合函數查詢
有時不需要返回實際表中的數據，而只是針對數據進行總結。MySQL提供一些查詢功能，可以獲取數據進行分析和報告。
| 函數 | 作用 |
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行數 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列值的和 |
COUNT()函數
mysql> SELECT COUNT(*) AS cust_num -> FROM customers; +----------+ | cust_num | +----------+ | 4 | +----------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(c_email) AS email_num -> FROM customers; +-----------+ | email_num | +-----------+ | 3 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT o_num, COUNT(f_id) -> FROM orderitems -> GROUP BY o_num; +-------+-------------+ | o_num | COUNT(f_id) | +-------+-------------+ | 30001 | 4 | | 30002 | 1 | | 30003 | 1 | | 30004 | 1 | | 30005 | 4 | +-------+-------------+ 5 rows in set (0.00 sec)
SUM()函數
mysql> SELECT SUM(quantity) AS items_total -> FROM orderitems -> WHERE o_num = 30005; +-------------+ | items_total | +-------------+ | 30 | +-------------+ 1 row in set (0.00 sec) mysql> SELECT o_num, SUM(quantity) AS items_total -> FROM orderitems -> GROUP BY o_num; +-------+-------------+ | o_num | items_total | +-------+-------------+ | 30001 | 33 | | 30002 | 2 | | 30003 | 100 | | 30004 | 50 | | 30005 | 30 | +-------+-------------+ 5 rows in set (0.00 sec)
AVG()函數
mysql> SELECT AVG(f_price) AS avg_price -> FROM fruits -> WHERE s_id = 103; +-----------+ | avg_price | +-----------+ | 5.700000 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT s_id,AVG(f_price) AS avg_price -> FROM fruits -> GROUP BY s_id; +------+-----------+ | s_id | avg_price | +------+-----------+ | 101 | 6.200000 | | 102 | 8.933333 | | 103 | 5.700000 | | 104 | 7.000000 | | 105 | 7.466667 | | 106 | 15.600000 | | 107 | 3.600000 | +------+-----------+ 7 rows in set (0.00 sec)
MAX()函數
mysql> SELECT MAX(f_price) AS max_price FROM fruits; +-----------+ | max_price | +-----------+ | 15.60 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT s_id, MAX(f_price) AS max_price -> FROM fruits -> GROUP BY s_id; +------+-----------+ | s_id | max_price | +------+-----------+ | 101 | 10.20 | | 102 | 11.20 | | 103 | 9.20 | | 104 | 7.60 | | 105 | 11.60 | | 106 | 15.60 | | 107 | 3.60 | +------+-----------+ 7 rows in set (0.00 sec) mysql> SELECT MAX(f_name) FROM fruits; +-------------+ | MAX(f_name) | +-------------+ | xxxx | +-------------+ 1 row in set (0.00 sec)
MIN()函數
mysql> SELECT MIN(f_price) AS min_price FROM fruits; +-----------+ | min_price | +-----------+ | 2.20 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT s_id, MIN(f_price) AS min_price -> FROM fruits -> GROUP BY s_id; +------+-----------+ | s_id | min_price | +------+-----------+ | 101 | 3.20 | | 102 | 5.30 | | 103 | 2.20 | | 104 | 6.40 | | 105 | 2.60 | | 106 | 15.60 | | 107 | 3.60 | +------+-----------+ 7 rows in set (0.00 sec)
6.4、連接查詢
連接是關系數據庫模型的主要特點。連接查詢是關系數據庫中最主要的查詢。主要包括內連接、外連接等。通過連接元算符可以實現多個表查詢。在關系數據庫管理系統中，表建立時各個數據之間關系不確定，常把一個實體的所有信息存在一個表中。當查詢時，通過連接操作查詢出存在多個表中的不同實體的信息。
內連接查詢
內連接使用比較運算符進行表間某些列數據的比較操作，并列出這些表中與連接條件相匹配的數據行，組合成新記錄。
首先創建表與插入數據：
mysql> CREATE TABLE suppliers -> ( -> s_id int NOT NULL AUTO_INCREMENT, -> s_name char(50) NOT NULL, -> s_city char(50) NULL, -> s_zip char(10) NULL, -> s_call CHAR(50) NOT NULL, -> PRIMARY KEY (s_id) -> ) ; Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO suppliers(s_id, s_name,s_city, s_zip, s_call) -> VALUES(101,'FastFruit Inc.','Tianjin','300000','48075'), -> (102,'LT Supplies','Chongqing','400000','44333'), -> (103,'ACME','Shanghai','200000','90046'), -> (104,'FNK Inc.','Zhongshan','528437','11111'), -> (105,'Good Set','Taiyuang','030000', '22222'), -> (106,'Just Eat Ours','Beijing','010', '45678'), -> (107,'DK Inc.','Zhengzhou','450000', '33332'); Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0
在fruits表和suppliers表之間使用內連接查詢
mysql> SELECT suppliers.s_id, s_name,f_name, f_price -> FROM fruits ,suppliers -> WHERE fruits.s_id = suppliers.s_id; +------+----------------+------------+---------+ | s_id | s_name | f_name | f_price | +------+----------------+------------+---------+ | 101 | FastFruit Inc. | apple | 5.20 | | 103 | ACME | apricot | 2.20 | | 101 | FastFruit Inc. | blackberry | 10.20 | | 104 | FNK Inc. | berry | 7.60 | | 107 | DK Inc. | xxxx | 3.60 | | 102 | LT Supplies | orange | 11.20 | | 105 | Good Set | melon | 8.20 | | 101 | FastFruit Inc. | cherry | 3.20 | | 104 | FNK Inc. | lemon | 6.40 | | 106 | Just Eat Ours | mango | 15.60 | | 105 | Good Set | xbabay | 2.60 | | 105 | Good Set | xxtt | 11.60 | | 103 | ACME | coconut | 9.20 | | 102 | LT Supplies | banana | 10.30 | | 102 | LT Supplies | grape | 5.30 | | 107 | DK Inc. | xbababa | 3.60 | +------+----------------+------------+---------+ 16 rows in set (0.00 sec) mysql> SELECT suppliers.s_id, s_name,f_name, f_price -> FROM fruits INNER JOIN suppliers -> ON fruits.s_id = suppliers.s_id; +------+----------------+------------+---------+ | s_id | s_name | f_name | f_price | +------+----------------+------------+---------+ | 101 | FastFruit Inc. | apple | 5.20 | | 103 | ACME | apricot | 2.20 | | 101 | FastFruit Inc. | blackberry | 10.20 | | 104 | FNK Inc. | berry | 7.60 | | 107 | DK Inc. | xxxx | 3.60 | | 102 | LT Supplies | orange | 11.20 | | 105 | Good Set | melon | 8.20 | | 101 | FastFruit Inc. | cherry | 3.20 | | 104 | FNK Inc. | lemon | 6.40 | | 106 | Just Eat Ours | mango | 15.60 | | 105 | Good Set | xbabay | 2.60 | | 105 | Good Set | xxtt | 11.60 | | 103 | ACME | coconut | 9.20 | | 102 | LT Supplies | banana | 10.30 | | 102 | LT Supplies | grape | 5.30 | | 107 | DK Inc. | xbababa | 3.60 | +------+----------------+------------+---------+ 16 rows in set (0.00 sec)
如果在一個連接查詢中，設計的兩個表是同一張表，這種查詢稱為字連接查詢。例如：查詢供應f_id= ‘a1’的水果供應商提供的其他水果種類。
mysql> SELECT f1.f_id, f1.f_name -> FROM fruits AS f1, fruits AS f2 -> WHERE f1.s_id = f2.s_id AND f2.f_id = 'a1'; +------+------------+ | f_id | f_name | +------+------------+ | a1 | apple | | b1 | blackberry | | c0 | cherry | +------+------------+ 3 rows in set (0.00 sec)
外間接查詢
外連接查詢將查詢多個表中相關聯的行，其分為左外連接和右外連接
左外連接：返回包括左表中的所有記錄和右表中連接字段相等的記錄
首先創建表和數據：
mysql> CREATE TABLE orders -> ( -> o_num int NOT NULL AUTO_INCREMENT, -> o_date datetime NOT NULL, -> c_id int NOT NULL, -> PRIMARY KEY (o_num) -> ) ; Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO orders(o_num, o_date, c_id) -> VALUES(30001, '2008-09-01', 10001), -> (30002, '2008-09-12', 10003), -> (30003, '2008-09-30', 10004), -> (30004, '2008-10-03', 10005), -> (30005, '2008-10-08', 10001); Query OK, 5 rows affected (0.02 sec) Records: 5 Duplicates: 0 Warnings: 0 #在customers表和orders表中，查詢所有客戶，包括沒有訂單的客戶 mysql> SELECT customers.c_id, orders.o_num -> FROM customers LEFT OUTER JOIN orders -> ON customers.c_id = orders.c_id; +-------+-------+ | c_id | o_num | +-------+-------+ | 10001 | 30001 | | 10001 | 30005 | | 10002 | NULL | | 10003 | 30002 | | 10004 | 30003 | +-------+-------+ 5 rows in set (0.00 sec)
右外連接：返回包括右表中的所有記錄和左表中連接字段相等的記錄
#在customers表和orders表中，查詢所有訂單，包括沒有客戶的訂單 mysql> SELECT customers.c_id, orders.o_num -> FROM customers RIGHT OUTER JOIN orders -> ON customers.c_id = orders.c_id; +-------+-------+ | c_id | o_num | +-------+-------+ | 10001 | 30001 | | 10003 | 30002 | | 10004 | 30003 | | NULL | 30004 | | 10001 | 30005 | +-------+-------+ 5 rows in set (0.00 sec)
復合條件連接查詢
復合條件連接查詢實在連接查詢的過程中，通過添加過濾條件，限制查詢的結果，使查詢的結果更加準確。
在customers表和orders表中，使用INNER JOIN語法查詢customers表中ID為10001的客戶的訂單信息 mysql> SELECT customers.c_id, orders.o_num -> FROM customers INNER JOIN orders -> ON customers.c_id = orders.c_id AND customers.c_id = 10001; +-------+-------+ | c_id | o_num | +-------+-------+ | 10001 | 30001 | | 10001 | 30005 | +-------+-------+ 2 rows in set (0.00 sec) 在fruits表和suppliers表之間，使用INNER JOIN語法進行內連接查詢，并對查詢結果排序 mysql> SELECT suppliers.s_id, s_name,f_name, f_price -> FROM fruits INNER JOIN suppliers -> ON fruits.s_id = suppliers.s_id -> ORDER BY fruits.s_id; +------+----------------+------------+---------+ | s_id | s_name | f_name | f_price | +------+----------------+------------+---------+ | 101 | FastFruit Inc. | apple | 5.20 | | 101 | FastFruit Inc. | blackberry | 10.20 | | 101 | FastFruit Inc. | cherry | 3.20 | | 102 | LT Supplies | grape | 5.30 | | 102 | LT Supplies | banana | 10.30 | | 102 | LT Supplies | orange | 11.20 | | 103 | ACME | apricot | 2.20 | | 103 | ACME | coconut | 9.20 | | 104 | FNK Inc. | lemon | 6.40 | | 104 | FNK Inc. | berry | 7.60 | | 105 | Good Set | xbabay | 2.60 | | 105 | Good Set | xxtt | 11.60 | | 105 | Good Set | melon | 8.20 | | 106 | Just Eat Ours | mango | 15.60 | | 107 | DK Inc. | xxxx | 3.60 | | 107 | DK Inc. | xbababa | 3.60 | +------+----------------+------------+---------+ 16 rows in set (0.00 sec)
6.5、子查詢
子查詢指一個查詢語句嵌套在另一個查詢語句內部的查詢。
帶ANY、SOME關鍵字的子查詢
ANY和SOME關鍵字是同義詞，表示滿足其中任一條件，它們允許創建一個表達式對子查詢的返回值列表進行比較，只要滿足內層子查詢中的任何一個比較條件，就返回一個結果作為外層查詢的條件。
首先創建兩張表，插入數據：
mysql> CREATE table tbl1 ( num1 INT NOT NULL); Query OK, 0 rows affected (0.02 sec) mysql> CREATE table tbl2 ( num2 INT NOT NULL); Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO tbl1 values(1), (5), (13), (27); Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> INSERT INTO tbl2 values(6), (14), (11), (20); Query OK, 4 rows affected (0.05 sec) Records: 4 Duplicates: 0 Warnings: 0 返回tbl2表的所有num2列，然后將tbl1中的num1的值與之進行比較，只要大于num2的任何1個值， 即為符合查詢條件的結果。 mysql> SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2); +------+ | num1 | +------+ | 13 | | 27 | +------+ 2 rows in set (0.00 sec)
帶ALL關鍵字的子查詢
ALL關鍵字與ANY不同，使用ALL時需要同時滿足所有內層查詢的條件。
返回tbl1表中比tbl2表num2 列所有值都大的值，SQL語句如下： mysql> SELECT num1 FROM tbl1 WHERE num1 > ALL (SELECT num2 FROM tbl2); +------+ | num1 | +------+ | 27 | +------+ 1 row in set (0.00 sec)
帶EXISTS關鍵字的子查詢
EXISTS關鍵字后面的參數是一個任意的子查詢，系統對子查詢進行運算以判斷它是否返回行，如果至少返回一行，那么EXISTS的結果為TRUE，此時外層查詢語句將進行查詢。
查詢suppliers表中是否存在s_id=107的供應商，如果存在，則查詢fruits表中的記錄 mysql> SELECT * FROM fruits -> WHERE EXISTS -> (SELECT s_name FROM suppliers WHERE s_id = 107); +------+------+------------+---------+ | f_id | s_id | f_name | f_price | +------+------+------------+---------+ | a1 | 101 | apple | 5.20 | | a2 | 103 | apricot | 2.20 | | b1 | 101 | blackberry | 10.20 | | b2 | 104 | berry | 7.60 | | b5 | 107 | xxxx | 3.60 | | bs1 | 102 | orange | 11.20 | | bs2 | 105 | melon | 8.20 | | c0 | 101 | cherry | 3.20 | | l2 | 104 | lemon | 6.40 | | m1 | 106 | mango | 15.60 | | m2 | 105 | xbabay | 2.60 | | m3 | 105 | xxtt | 11.60 | | o2 | 103 | coconut | 9.20 | | t1 | 102 | banana | 10.30 | | t2 | 102 | grape | 5.30 | | t4 | 107 | xbababa | 3.60 | +------+------+------------+---------+ 16 rows in set (0.00 sec) 查詢suppliers表中是否存在s_id=107的供應商，如果存在，則查詢fruits表中的f_price大于10.20 的記錄 mysql> SELECT * FROM fruits -> WHERE f_price>10.20 AND EXISTS -> (SELECT s_name FROM suppliers WHERE s_id = 107); +------+------+--------+---------+ | f_id | s_id | f_name | f_price | +------+------+--------+---------+ | bs1 | 102 | orange | 11.20 | | m1 | 106 | mango | 15.60 | | m3 | 105 | xxtt | 11.60 | | t1 | 102 | banana | 10.30 | +------+------+--------+---------+ 4 rows in set (0.00 sec) 查詢suppliers表中是否存在s_id=107的供應商，如果不存在則查詢fruits表中的記錄 mysql> SELECT * FROM fruits -> WHERE NOT EXISTS -> (SELECT s_name FROM suppliers WHERE s_id = 107); Empty set (0.00 sec)
帶IN關鍵字的子查詢
IN 關鍵字進行子查詢時，內層查詢語句僅僅返回一個數據列，這個數據列里的值將提供給外層查詢語句進行比較。
在orderitems表中查詢f_id為c0的訂單號，并根據訂單號查詢具有訂單號的客戶c_id mysql> SELECT c_id FROM orders WHERE o_num IN -> (SELECT o_num FROM orderitems WHERE f_id = 'c0'); +-------+ | c_id | +-------+ | 10004 | | 10001 | +-------+ 2 rows in set (0.00 sec)
帶比較運算符的子查詢
在suppliers表中查詢s_city等于“Tianjin”的供應商s_id，然后在fruits表中查詢所有該供應商提 供的水果的種類 mysql> SELECT s_id, f_name FROM fruits -> WHERE s_id = -> (SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin'); +------+------------+ | s_id | f_name | +------+------------+ | 101 | apple | | 101 | blackberry | | 101 | cherry | +------+------------+ 3 rows in set (0.00 sec) 在suppliers表中查詢s_city等于“Tianjin”的供應商s_id，然后在fruits表中查詢所有非該供應 商提供的水果的種類 mysql> SELECT s_id, f_name FROM fruits -> WHERE s_id <> -> (SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin'); +------+---------+ | s_id | f_name | +------+---------+ | 103 | apricot | | 104 | berry | | 107 | xxxx | | 102 | orange | | 105 | melon | | 104 | lemon | | 106 | mango | | 105 | xbabay | | 105 | xxtt | | 103 | coconut | | 102 | banana | | 102 | grape | | 107 | xbababa | +------+---------+ 13 rows in set (0.00 sec)
6.6、合并查詢結果
利用UNION關鍵字，可以給出多條SELECT語句，并將它們的結果組成單個結果集。合并時，兩個表對應列的列數和數據類型必須相同。使用ALL關鍵字可以不刪除重復行也不對結果進行排序。其基本語法為：
SELECT column,... FROM table1 UNION [ALL] SELECT column,... FROM table2
查詢所有價格小于9的水果的信息，查詢s_id等于101和103所有的水果的信息，使用UNION連接查詢結果 mysql> SELECT s_id, f_name, f_price -> FROM fruits -> WHERE f_price < 9.0 -> UNION ALL -> SELECT s_id, f_name, f_price -> FROM fruits -> WHERE s_id IN(101,103); +------+------------+---------+ | s_id | f_name | f_price | +------+------------+---------+ | 101 | apple | 5.20 | | 103 | apricot | 2.20 | | 104 | berry | 7.60 | | 107 | xxxx | 3.60 | | 105 | melon | 8.20 | | 101 | cherry | 3.20 | | 104 | lemon | 6.40 | | 105 | xbabay | 2.60 | | 102 | grape | 5.30 | | 107 | xbababa | 3.60 | | 101 | apple | 5.20 | | 103 | apricot | 2.20 | | 101 | blackberry | 10.20 | | 101 | cherry | 3.20 | | 103 | coconut | 9.20 | +------+------------+---------+ 15 rows in set (0.00 sec) 查詢所有價格小于9的水果的信息，查詢s_id等于101和103的所有水果的信息，使用UNION ALL連接查詢結果 mysql> SELECT s_id, f_name, f_price -> FROM fruits -> WHERE f_price < 9.0 -> UNION ALL -> SELECT s_id, f_name, f_price -> FROM fruits -> WHERE s_id IN(101,103); +------+------------+---------+ | s_id | f_name | f_price | +------+------------+---------+ | 101 | apple | 5.20 | | 103 | apricot | 2.20 | | 104 | berry | 7.60 | | 107 | xxxx | 3.60 | | 105 | melon | 8.20 | | 101 | cherry | 3.20 | | 104 | lemon | 6.40 | | 105 | xbabay | 2.60 | | 102 | grape | 5.30 | | 107 | xbababa | 3.60 | | 101 | apple | 5.20 | | 103 | apricot | 2.20 | | 101 | blackberry | 10.20 | | 101 | cherry | 3.20 | | 103 | coconut | 9.20 | +------+------------+---------+ 15 rows in set (0.00 sec)
6.7、為表和字段取別名
為表取別名
當表名字很長或者執行一些特殊查詢時，為了方便可以為表指定別名，其語法格式為：
表名 [AS] 表別名
為orders表取別名o，查詢30001訂單的下單日期 mysql> SELECT * FROM orders AS o -> WHERE o.o_num = 30001; +-------+---------------------+-------+ | o_num | o_date | c_id | +-------+---------------------+-------+ | 30001 | 2008-09-01 00:00:00 | 10001 | +-------+---------------------+-------+ 1 row in set (0.00 sec) 為customers和orders表分別取別名，并進行連接查詢 mysql> SELECT c.c_id, o.o_num -> FROM customers AS c LEFT OUTER JOIN orders AS o -> ON c.c_id = o.c_id; +-------+-------+ | c_id | o_num | +-------+-------+ | 10001 | 30001 | | 10001 | 30005 | | 10002 | NULL | | 10003 | 30002 | | 10004 | 30003 | +-------+-------+ 5 rows in set (0.00 sec)
為字段取別名
為字段取別名的語法格式為：
列名 [AS] 列別名
查詢fruits表，為f_name取別名fruit_name，f_price取別名fruit_price，為fruits表取別名f1，
查詢表中f_price < 8的水果的名稱
mysql> SELECT f1.f_name AS fruit_name, f1.f_price AS fruit_price
-> FROM fruits AS f1
-> WHERE f1.f_price < 8;
+------------+-------------+
| fruit_name | fruit_price |
+------------+-------------+
| apple | 5.20 |
| apricot | 2.20 |
| berry | 7.60 |
| xxxx | 3.60 |
| cherry | 3.20 |
| lemon | 6.40 |
| xbabay | 2.60 |
| grape | 5.30 |
| xbababa | 3.60 |
+------------+-------------+
9 rows in set (0.00 sec)
查詢suppliers表中字段s_name和s_city，使用CONCAT函數連接這兩個字段值，并取列別名為suppliers_title
mysql> SELECT CONCAT(TRIM(s_name) , ' (', TRIM(s_city), ')')
-> AS suppliers_title
-> FROM suppliers
-> ORDER BY s_name;
+--------------------------+
| suppliers_title |
+--------------------------+
| ACME (Shanghai) |
| DK Inc. (Zhengzhou) |
| FastFruit Inc. (Tianjin) |
| FNK Inc. (Zhongshan) |
| Good Set (Taiyuang) |
| Just Eat Ours (Beijing) |
| LT Supplies (Chongqing) |
+--------------------------+
7 rows in set (0.00 sec)6.8、使用正則表達式查詢
正則表達式通常被用來檢索或替換那些符合某個模式的文本內容，根據指定的匹配模式匹配文中符合要求的特殊字符串。
| 選項 | 說明 | 例子 | 匹配值示例 |
| ^ | 匹配文本的開始字符串 | '^b' | book,big,bike |
| $ | 匹配文本的結束字符串 | 'st$' | test,persist |
| . | 匹配任何單個字符 | 'b.t' | bit,bat,but |
| * | 匹配零個或多個前面的字符 | 'f*n' | fn,ffn,fffn |
| + | 匹配前面的字符1次或多次 | 'ba+' | baa,baaaa |
| <字符串> | 匹配包含指定的字符串文本 | 'fa' | fan,afa,faad |
| [字符集合] | 匹配字符集合中任意一個字符 | '[xz]' | dizzy,zebra,x-sd |
| [^] | 匹配不在括號中的任何一個字符 | '[^abc]' | qwe,ret,ryrty |
| 字符串{n,} | 匹配前面的字符至少n次 | 'b{2,}' | bbb,bbbb,bbbbbbb |
| 字符串{n,m} | 匹配前面的字符串至少n次，至多m次 | 'b{2,4}' | bb,bbb,bbbb |
在fruits表中，查詢f_name字段以字母’b’開頭的記錄
mysql> SELECT * FROM fruits WHERE f_name REGEXP '^b';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| t1 | 102 | banana | 10.30 |
+------+------+------------+---------+
3 rows in set (0.00 sec)
在fruits表中，查詢f_name字段以“be”開頭的記錄
mysql> SELECT * FROM fruits WHERE f_name REGEXP '^be';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b2 | 104 | berry | 7.60 |
+------+------+--------+---------+
1 row in set (0.00 sec)
在fruits表中，查詢f_name字段以字母’y’結尾的記錄
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'y$';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| c0 | 101 | cherry | 3.20 |
| m2 | 105 | xbabay | 2.60 |
+------+------+------------+---------+
4 rows in set (0.00 sec)
在fruits表中，查詢f_name字段以字符串“rry”結尾的記錄
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'rry$';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| c0 | 101 | cherry | 3.20 |
+------+------+------------+---------+
3 rows in set (0.00 sec)
在fruits表中，查詢f_name字段值包含字母’a’與’g’且兩個字母之間只有一個字母的記錄
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'a.g';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| bs1 | 102 | orange | 11.20 |
| m1 | 106 | mango | 15.60 |
+------+------+--------+---------+
2 rows in set (0.00 sec)
在fruits表中，查詢f_name字段值以字母’b’開頭，且’b’后面出現字母’a’的記錄
mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ba*';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| t1 | 102 | banana | 10.30 |
+------+------+------------+---------+
3 rows in set (0.00 sec)
在fruits表中，查詢f_name字段值以字母’b’開頭，且’b’后面出現字母’a’至少一次的記錄
mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ba+';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| t1 | 102 | banana | 10.30 |
+------+------+--------+---------+
1 row in set (0.00 sec)
在fruits表中，查詢f_name字段值包含字符串“on”的記錄
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'on';
+------+------+---------+---------+
| f_id | s_id | f_name | f_price |
+------+------+---------+---------+
| bs2 | 105 | melon | 8.20 |
| l2 | 104 | lemon | 6.40 |
| o2 | 103 | coconut | 9.20 |
+------+------+---------+---------+
3 rows in set (0.00 sec)
在fruits表中，查詢f_name字段值包含字符串“on”或者“ap”的記錄
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'on|ap';
+------+------+---------+---------+
| f_id | s_id | f_name | f_price |
+------+------+---------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| bs2 | 105 | melon | 8.20 |
| l2 | 104 | lemon | 6.40 |
| o2 | 103 | coconut | 9.20 |
| t2 | 102 | grape | 5.30 |
+------+------+---------+---------+
6 rows in set (0.00 sec)
在fruits表中，使用LIKE運算符查詢f_name字段值為“on”的記錄
mysql> SELECT * FROM fruits WHERE f_name LIKE 'on';
Empty set (0.00 sec)
在fruits表中，查找f_name字段中包含字母’o’或者’t’的記錄
mysql> SELECT * FROM fruits WHERE f_name REGEXP '[ot]';
+------+------+---------+---------+
| f_id | s_id | f_name | f_price |
+------+------+---------+---------+
| a2 | 103 | apricot | 2.20 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
+------+------+---------+---------+
7 rows in set (0.00 sec)
在fruits表，查詢s_id字段中數值中包含4、5或者6的記錄
mysql> SELECT * FROM fruits WHERE s_id REGEXP '[456]';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b2 | 104 | berry | 7.60 |
| bs2 | 105 | melon | 8.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.60 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
+------+------+--------+---------+
6 rows in set (0.00 sec)
在fruits表中，查詢f_id字段包含字母a~e和數字1~2以外的字符的記錄
mysql> SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';
+------+------+---------+---------+
| f_id | s_id | f_name | f_price |
+------+------+---------+---------+
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.60 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+---------+---------+
12 rows in set (0.00 sec)
在fruits表中，查詢f_name字段值出現字母’x’至少2次的記錄
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b5 | 107 | xxxx | 3.60 |
| m3 | 105 | xxtt | 11.60 |
+------+------+--------+---------+
2 rows in set (0.00 sec)
在fruits表中，查詢f_name字段值出現字符串“ba”最少1次，最多3次的記錄
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}';
+------+------+---------+---------+
| f_id | s_id | f_name | f_price |
+------+------+---------+---------+
| m2 | 105 | xbabay | 2.60 |
| t1 | 102 | banana | 10.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+---------+---------+
3 rows in set (0.00 sec)免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
