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: 0
6.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)