导航：首页 > 数据库 >

六、MySQL查询数据

发表于：2024-11-25 作者：千家信息网编辑

千家信息网最后更新 2024年11月25日，6.1、基本查询语句MySQL从数据表中查询数据的基本语句为SELECT语句，其基本格式为：SELECT {* | <字段列表>} [ FROM <表1>,<表2>...

千家信息网最后更新 2024年11月25日六、MySQL查询数据

6.1、基本查询语句

MySQL从数据表中查询数据的基本语句为SELECT语句，其基本格式为：

SELECT    {* | <字段列表>}    [      FROM <表1>,<表2>...       [WHERE 表达式        [GROUP BY  ]        [HAVING   [{ }...]]        [ORDER BY  ]        [LIMIT [,]  ]      ]    ]    SELECT [字段1,字段2,...,字段n]  FROM [表或视图]    WHERE [查询条件]

各条子句的含义：

{* | <字段列表>}：包含星号通配符选择字段列表，表示查询的字段，其字段列至少包含一个字段名称。

FROM <表1>,<表2>...：表1，表2表示查询数据的来源

WHERE 表达式：可选项，限定查询必须满足的查询条件

GROUP BY <字段>：告诉MySQL如何显示查询出来的数据，并按照指定字段分组

ORDER BY <字段>：告诉MySQL按什么顺序显示查询的数据，可进行的排序ASC,DESC

LIMIT [,] ：告诉MySQL每次显示查询出来的数据条数

为了方便演示SELECT语句的用法，首先创建一张数据表,并向表中插入数据。

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: 0mysql> 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: 0mysql> 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: 0mysql> 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_idmysql>  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 table1UNION [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_titlemysql>  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)

很赞哦！