（五）MySQL查询数据

数据库管理系统的一个最重要的功能就是数据查询，数据查询不应只是简单查询数据库中存储的数据，还应该根据需要对数据进行筛选，以及确定数据以什么样的格式显示。MySQL提供了功能强大、灵活的语句来实现这些操作。

一、基本查询语句

mysql从数据表中查询数据的基本语句为select语句。select语句的基本格式是：

SELECT {* | <字段列表>} [ FROM <表1>, <表2>.... [ where <表达式> ] [ group by ] [ having ] [ order by<..> ] [ limit <...> ] {*|<字段列表>}：包含星号通配符选择字段列表，表示查询的字段，其中字段列至少包含一个字段名称，如果要查询多个字段，多个字段之间用逗号隔开，最后一个字段后不要加逗号。FROM<表1>,<表2>…：表1和表2表示查询数据的来源，可以是单个或多个。WHERE子句：是可选项，如果选择该项，将限定查询必须满足的查询条件。GROUP BY<字段>：该子句告诉MySQL按什么样的顺序显示查询出来的数据，可以进行的排序有：升序（asc）、降序（desc）。[limit]：该子句告诉mysql每次显示查询出来的数据条款。 mysql> help select; Name: 'SELECT' Description: Syntax: SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr] ... [into_option] [FROM table_references [PARTITION partition_list]] [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [into_option] [FOR UPDATE | LOCK IN SHARE MODE] into_option: { INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name] ... } SELECT is used to retrieve rows selected from one or more tables, and can include UNION statements and subqueries. See [HELP UNION], and https://dev.mysql.com/doc/refman/5.7/en/subqueries.html. The most commonly used clauses of SELECT statements are these: o Each select_expr indicates a column that you want to retrieve. There must be at least one select_expr. o table_references indicates the table or tables from which to retrieve rows. Its syntax is described in [HELP JOIN]. o SELECT supports explicit partition selection using the PARTITION with a list of partitions or subpartitions (or both) following the name of the table in a table_reference (see [HELP JOIN]). In this case, rows are selected only from the partitions listed, and any other partitions of the table are ignored. For more information and examples, see https://dev.mysql.com/doc/refman/5.7/en/partitioning-selection.html. SELECT ... PARTITION from tables using storage engines such as MyISAM that perform table-level locks (and thus partition locks) lock only the partitions or subpartitions named by the PARTITION option. For more information, see https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-lock ing.html. o The WHERE clause, if given, indicates the condition or conditions that rows must satisfy to be selected. where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause. In the WHERE expression, you can use any of the functions and operators that MySQL supports, except for aggregate (summary) functions. See https://dev.mysql.com/doc/refman/5.7/en/expressions.html, and https://dev.mysql.com/doc/refman/5.7/en/functions.html. SELECT can also be used to retrieve rows computed without reference to any table. URL: https://dev.mysql.com/doc/refman/5.7/en/select.html mysql>

示例：

mysql> use test2; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed 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.09 sec) mysql> desc fruits; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | f_id | char(10) | NO | PRI | NULL | | | s_id | int(11) | NO | | NULL | | | f_name | char(255) | NO | | NULL | | | f_price | decimal(8,2) | NO | | NULL | | +---------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 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.7'), -> ('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.00 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql>

二、单表查询

单表查询是指从一张表数据中查询所需的数据。主要有：查询所有字段、查询指定字段、查询指定记录、查询空值、多条件的查询、对查询结果进行排序等方式。

1.查询所有字段

（1）在select语句中使用星号×通配符查询所有字段。

select查询记录最简单的形式是从一个表中检索所有记录，实现的方法是使用星号×通配符指定查找所有列的名称。

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.70 | | 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) mysql>

（2）在select语句中指定所有字段

根据前面select语句的格式，select关键字后面的字段名为将要查询的数据，因此可以将表中所有字段的名称跟在select子句后面，如果忘记了字段名称，可以使用DESC命令查看表的结构。有时候，由于表中的字段多，不一定能记住所有的字段名称。因此很不方便，不建议使用。

mysql> select f_id,s_id,f_name,f_price 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.70 | | 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) mysql>

2.查询指定字段

（1）查询指定字段

语法：

select 字段名 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>

（2）查询多个字段

使用select声明，可以获取多个字段下的数据，只需要在关键字select后面指定要查询的字段的名称，不同字段名称之间用逗号分隔，最后一个字段后面不需要加逗号。

语法：

select 字段1，字段2，字段3 ....，字段n from 表名；

应用：

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.70 | | 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) mysql>

3.查询指定记录

数据库中包含大量的数据，根据特殊要求可能只需要查询表中的指定数据，相当于对数据的过滤。在select语句中，通过where子句可以对数据进行过滤。

语法：

select 字段1，字段2....字段n from 表名 where 查询条件； 操作符说明=相等<>,!=不相等<小于<=小于等于>大于>=大于等于BETWEEN位于两端之间

应用：

mysql> select * from fruits where f_name="cherry"; +------+------+--------+---------+ | f_id | s_id | f_name | f_price | +------+------+--------+---------+ | c0 | 101 | cherry | 3.20 | +------+------+--------+---------+ 1 row in set (0.00 sec) mysql> select * from fruits where f_price>10; +------+------+------------+---------+ | f_id | s_id | f_name | f_price | +------+------+------------+---------+ | b1 | 101 | blackberry | 10.20 | | bs1 | 102 | orange | 11.20 | | m1 | 106 | mango | 15.70 | | m3 | 105 | xxtt | 11.60 | | t1 | 102 | banana | 10.30 | +------+------+------------+---------+ 5 rows in set (0.00 sec) mysql> select * from fruits where f_price between 7 and 10; +------+------+---------+---------+ | f_id | s_id | f_name | f_price | +------+------+---------+---------+ | b2 | 104 | berry | 7.60 | | bs2 | 105 | melon | 8.20 | | o2 | 103 | coconut | 9.20 | +------+------+---------+---------+ 3 rows in set (0.00 sec) 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>

4.带in关键字的查询

in操作符用来查询满足指定范围内的条件的记录，使用IN操作符，将所有检索条件用括号括起来，检索条件之间用逗号分隔开，只要满足条件范围内的一个值即为匹配项。

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) 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.03 sec) mysql>

5.带between and的范围查询

Between and用来查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值，如果字段值满足指定的范围查询条件，则这些记录被返回。

mysql> select f_name,f_price from fruits where f_price between 6.00 and 8.00; +--------+---------+ | f_name | f_price | +--------+---------+ | berry | 7.60 | | lemon | 6.40 | +--------+---------+ 2 rows in set (0.00 sec) mysql>

6.带like的字符匹配查询

通配符是一种在SQL的where条件子句中拥有特殊意思的字符，SQL语句中支持多种通配符，可以和like一起使用的通配符有‘%’和‘_’。

（1）百分号（%）通配符

匹配任意长度的字符，甚至包括零字符

mysql> select f_id,f_name from fruits where f_name like 'cherry'; +------+--------+ | f_id | f_name | +------+--------+ | c0 | cherry | +------+--------+ 1 row in set (0.00 sec) 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>

（2）下划线（__)通配符

一次只能匹配任意一个字符

mysql> select f_id,f_name from fruits where f_name like 'b_rr_'; +------+--------+ | f_id | f_name | +------+--------+ | b2 | berry | +------+--------+ 1 row in set (0.00 sec) mysql>

7.查询空值

数据表创建的时候，设计者可以指定某列中是否可以包含空值（NULL)。空值不同于0，也不同于空字符串。空值一般表示数据未知、不适用或将在以后添加数据。在select语句中使用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(50) null, -> c_contact char(50) null, -> c_email char(50) null, -> primary key(c_id)); Query OK, 0 rows affected (0.02 sec) mysql> desc customers; +-----------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+----------+------+-----+---------+----------------+ | c_id | int(11) | NO | PRI | NULL | auto_increment | | c_name | char(50) | NO | | NULL | | | c_address | char(50) | YES | | NULL | | | c_city | char(50) | YES | | NULL | | | c_zip | char(50) | YES | | NULL | | | c_contact | char(50) | YES | | NULL | | | c_email | char(50) | YES | | NULL | | +-----------+----------+------+-----+---------+----------------+ 7 rows in set (0.00 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.04 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from customers; +-------+----------+---------------------+---------+--------+-----------+-------------------+ | c_id | c_name | c_address | c_city | c_zip | c_contact | c_email | +-------+----------+---------------------+---------+--------+-----------+-------------------+ | 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 | +-------+----------+---------------------+---------+--------+-----------+-------------------+ 4 rows in set (0.00 sec) mysql> select * from customers where c_email is null; #查询空值 +-------+----------+---------------+---------+--------+-----------+---------+ | c_id | c_name | c_address | c_city | c_zip | c_contact | c_email | +-------+----------+---------------+---------+--------+-----------+---------+ | 10003 | Netbhood | 1 Sunny Place | Qingdao | 266000 | LuoCong | NULL | +-------+----------+---------------+---------+--------+-----------+---------+ 1 row in set (0.00 sec) mysql>

8.带and的多条件查询

使用select查询时，可以增加查询的限制条件，这样可以使查询的结果更加精确。MySQL在where子句中使用and操作符限定只有满足所有查询条件的记录才会被返回。可以使用and连接两个甚至多个查询条件，多个条件表达式之间用and分开。

mysql> select * from fruits where s_id="101"; +------+------+------------+---------+ | f_id | s_id | f_name | f_price | +------+------+------------+---------+ | a1 | 101 | apple | 5.20 | | b1 | 101 | blackberry | 10.20 | | c0 | 101 | cherry | 3.20 | +------+------+------------+---------+ 3 rows in set (0.00 sec) mysql> select * from fruits where s_id="101" and f_name="cherry"; +------+------+--------+---------+ | f_id | s_id | f_name | f_price | +------+------+--------+---------+ | c0 | 101 | cherry | 3.20 | +------+------+--------+---------+ 1 row in set (0.00 sec) mysql>

9.带or的多条件查询

与and相反，在where声明中使用or操作符，表示只需要满足其中一个条件的记录即可返回。or也可以连接两个甚至多个查询条件，多个条件表达式之间用or分开。

mysql> select * from fruits where s_id="101" or s_id="102"; +------+------+------------+---------+ | f_id | s_id | f_name | f_price | +------+------+------------+---------+ | a1 | 101 | apple | 5.20 | | b1 | 101 | blackberry | 10.20 | | bs1 | 102 | orange | 11.20 | | c0 | 101 | cherry | 3.20 | | t1 | 102 | banana | 10.30 | | t2 | 102 | grape | 5.30 | +------+------+------------+---------+ 6 rows in set (0.00 sec) mysql> select * from fruits where s_id="101" or s_id="999"; +------+------+------------+---------+ | f_id | s_id | f_name | f_price | +------+------+------------+---------+ | a1 | 101 | apple | 5.20 | | b1 | 101 | blackberry | 10.20 | | c0 | 101 | cherry | 3.20 | +------+------+------------+---------+ 3 rows in set (0.00 sec) mysql> select * from fruits where s_id="999" or s_id="102"; +------+------+--------+---------+ | f_id | s_id | f_name | f_price | +------+------+--------+---------+ | bs1 | 102 | orange | 11.20 | | t1 | 102 | banana | 10.30 | | t2 | 102 | grape | 5.30 | +------+------+--------+---------+ 3 rows in set (0.00 sec) mysql>

10.查询结果不重复distinct

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>

11.对查询结果排序

（1）单列排序

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_price; #按水果价格升序排列 +------------+---------+ | f_name | f_price | +------------+---------+ | apricot | 2.20 | | xbabay | 2.60 | | cherry | 3.20 | | xxxx | 3.60 | | xbababa | 3.60 | | apple | 5.20 | | grape | 5.30 | | lemon | 6.40 | | berry | 7.60 | | melon | 8.20 | | coconut | 9.20 | | blackberry | 10.20 | | banana | 10.30 | | orange | 11.20 | | xxtt | 11.60 | | mango | 15.70 | +------------+---------+ 16 rows in set (0.00 sec) mysql>

（2）多列排序

在多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

#先按价格排序，价格相同再按水果名称排序 mysql> select f_name,f_price from fruits order by f_price,f_name; +------------+---------+ | f_name | f_price | +------------+---------+ | apricot | 2.20 | | xbabay | 2.60 | | cherry | 3.20 | | xbababa | 3.60 | | xxxx | 3.60 | | apple | 5.20 | | grape | 5.30 | | lemon | 6.40 | | berry | 7.60 | | melon | 8.20 | | coconut | 9.20 | | blackberry | 10.20 | | banana | 10.30 | | orange | 11.20 | | xxtt | 11.60 | | mango | 15.70 | +------------+---------+ 16 rows in set (0.00 sec) mysql>

（3）指定排序方向

默认情况下，查询数据按字母升序进行排序（从A~Z)，但数据的排序并不仅限于此，还可以使用order by对查询结果进行降序排序（从Z~A)，这可以通过关键字DESC实现。

与DESC降序相反ASC是升序，默认是ASC，可不写ASC

mysql> select f_name,f_price from fruits order by f_name,f_price desc; +------------+---------+ | 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.70 | | 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>

12.分组查询

分组插叙是对数据按照某个或多个字段进行分组，MySQL中使用group by关键字对数据进行分组

基本语法形式为：group by 字段

（1）创建分组

Group by 关键字通常和集合函数一起使用，例如：MAX()、MIN()、COUNT()、SUM()、AVG()。

#根据s_id订单号对fruits表中的数据进行分组，并统计每组有几种水果 mysql> select s_id,count(*) from fruits group by s_id; +------+----------+ | s_id | count(*) | +------+----------+ | 101 | 3 | | 102 | 3 | | 103 | 2 | | 104 | 2 | | 105 | 3 | | 106 | 1 | | 107 | 2 | +------+----------+ 7 rows in set (0.00 sec) #根据s_id订单号对fruits表中的数据进行分组，并统计后的数量起别名 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) #根据s_id订单号对fruits表中的数据进行分组，并显示出每组订单中最贵的水果 mysql> select s_id,max(f_price) from fruits group by s_id; +------+--------------+ | s_id | max(f_price) | +------+--------------+ | 101 | 10.20 | | 102 | 11.20 | | 103 | 9.20 | | 104 | 7.60 | | 105 | 11.60 | | 106 | 15.70 | | 107 | 3.60 | +------+--------------+ 7 rows in set (0.00 sec) #根据s_id订单号对fruits表中的数据进行分组，并显示出每组订单需要消费多少 mysql> select s_id,sum(f_price) from fruits group by s_id; +------+--------------+ | s_id | sum(f_price) | +------+--------------+ | 101 | 18.60 | | 102 | 26.80 | | 103 | 11.40 | | 104 | 14.00 | | 105 | 22.40 | | 106 | 15.70 | | 107 | 7.20 | +------+--------------+ 7 rows in set (0.00 sec) mysql>

（2）group_concat

将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

#根据s_id对fruits表中的数据进行分组，将每个供应商的水果名称显示出来 mysql> select s_id,group_concat(f_name) as name from fruits group by s_id; +------+-------------------------+ | s_id | name | +------+-------------------------+ | 101 | apple,blackberry,cherry | | 102 | orange,banana,grape | | 103 | apricot,coconut | | 104 | berry,lemon | | 105 | melon,xbabay,xxtt | | 106 | mango | | 107 | xxxx,xbababa | +------+-------------------------+ 7 rows in set (0.00 sec) mysql>

（3）使用having过滤分组

# 根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息 mysql> select s_id,group_concat(f_name) as name from fruits group by s_id having count(f_name) > 1; +------+-------------------------+ | s_id | name | +------+-------------------------+ | 101 | apple,blackberry,cherry | | 102 | orange,banana,grape | | 103 | apricot,coconut | | 104 | berry,lemon | | 105 | melon,xbabay,xxtt | | 107 | xxxx,xbababa | +------+-------------------------+ 6 rows in set (0.00 sec) #将106 mango这条记录去掉了 #查询每组订单的平均价格 mysql> select s_id,avg(f_price) from fruits group by s_id; +------+--------------+ | s_id | avg(f_price) | +------+--------------+ | 101 | 6.200000 | | 102 | 8.933333 | | 103 | 5.700000 | | 104 | 7.000000 | | 105 | 7.466667 | | 106 | 15.700000 | | 107 | 3.600000 | +------+--------------+ 7 rows in set (0.00 sec) #查询每组订单的平均价格，并过滤出平均价格大于6的记录 mysql> select s_id,avg(f_price) as f_p from fruits group by s_id having f_p>6; +------+-----------+ | s_id | f_p | +------+-----------+ | 101 | 6.200000 | | 102 | 8.933333 | | 104 | 7.000000 | | 105 | 7.466667 | | 106 | 15.700000 | +------+-----------+ 5 rows in set (0.00 sec) mysql>

（4）在group by 子句中使用with rollup

使用with rollup关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

mysql> select s_id,count(*) from fruits group by s_id; +------+----------+ | s_id | count(*) | +------+----------+ | 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,count(*) from fruits group by s_id with rollup; +------+----------+ | s_id | count(*) | +------+----------+ | 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 coalesce(s_id,'total'),count(*) from fruits group by s_id with rollup; +------------------------+----------+ | coalesce(s_id,'total') | count(*) | +------------------------+----------+ | 101 | 3 | | 102 | 3 | | 103 | 2 | | 104 | 2 | | 105 | 3 | | 106 | 1 | | 107 | 2 | | total | 16 | #给统计的数据加个total值 +------------------------+----------+ 8 rows in set (0.00 sec) #coalesce(s_id,'total')：如果有s_id值，则写入，如果没有则加入total这个值 mysql>

coalesce(x,y)函数：如果x值为真则返回x值，如果x值为假，则返回y值

（5）多字段分组

使用group by可以对多个字段进行分组，group by关键字后面跟需要分组的字段，MySQL根据多字段的值来进行层次分组，分组层次从左到右，即先按第1个字段分组，然后在第1个字段值相同的记录中，再根据第2个字段的值进行分组，以此类推。

mysql> select * from fruits group by s_id,f_name; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test2.fruits.f_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by #报错原因：此语句适合版本5.5或5.6，但此实验环境为5.7 #这个sql_mode,简而言之就是：它定义了你MySQL应该支持的sql语法，对数据的校验等等。 mysql> show variables like 'sql_mode'; #查看当前数据库使用的sql_mode： +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.53 sec) #更改sql_mode模式，修改主配置文件，在文件最后添加，注意将ONLY_FULL_GROUP_BY去掉 [root@MySQL ~]# vim /etc/my.cnf [mysqld] basedir=/usr/local/mysql datadir=/usr/local/mysql/data pid-file=/usr/local/mysql/data/mysqld.pid socket=/tmp/mysql.sock log-error=/usr/local/mysql/data/mysql.err sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION #只需要添加最后一行即可 [root@MySQL ~]# systemctl restart mysqld #修改完配置文件需要重启一次 [root@MySQL ~]# mysql> show variables like 'sql_mode'; #此时再次查看sql_mode，已经没有ONLY_FULL_GROUP_BY了 +---------------+------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+------------------------------------------------------------------------------------------------------------------------+ | sql_mode | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from fruits group by s_id,f_name; #先按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.70 | | t4 | 107 | xbababa | 3.60 | | b5 | 107 | xxxx | 3.60 | +------+------+------------+---------+ 16 rows in set (0.00 sec) mysql>

（6）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.52 sec) mysql> desc orderitems; +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | o_num | int(11) | NO | PRI | NULL | | | o_item | int(11) | NO | PRI | NULL | | | f_id | char(10) | NO | | NULL | | | quantity | int(11) | NO | | NULL | | | item_price | decimal(8,2) | NO | | NULL | | +------------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 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.00 sec) Records: 11 Duplicates: 0 Warnings: 0 mysql>

查询价格大于100的订单号和总价订单价格

mysql> select * from orderitems; +-------+--------+------+----------+------------+ | o_num | o_item | f_id | quantity | item_price | +-------+--------+------+----------+------------+ | 30001 | 1 | a1 | 10 | 5.20 | | 30001 | 2 | b2 | 3 | 7.60 | | 30001 | 3 | bs1 | 5 | 11.20 | | 30001 | 4 | bs2 | 15 | 9.20 | | 30002 | 1 | b3 | 2 | 20.00 | | 30003 | 1 | c0 | 100 | 10.00 | | 30004 | 1 | o2 | 50 | 2.50 | | 30005 | 1 | c0 | 5 | 10.00 | | 30005 | 2 | b1 | 10 | 8.99 | | 30005 | 3 | a2 | 10 | 2.20 | | 30005 | 4 | m1 | 5 | 14.99 | +-------+--------+------+----------+------------+ 11 rows in set (0.00 sec) #先查看每组订单的总价和 mysql> select o_num,sum(quantity*item_price) from orderitems group by o_num; +-------+--------------------------+ | o_num | sum(quantity*item_price) | +-------+--------------------------+ | 30001 | 268.80 | | 30002 | 40.00 | | 30003 | 1000.00 | | 30004 | 125.00 | | 30005 | 236.85 | +-------+--------------------------+ 5 rows in set (0.00 sec) #再查询价格大于100的订单号和总价订单价格 mysql> select o_num,sum(quantity*item_price) from orderitems group by o_num having sum(quantity*item_price) >= 100; +-------+--------------------------+ | o_num | sum(quantity*item_price) | +-------+--------------------------+ | 30001 | 268.80 | | 30003 | 1000.00 | | 30004 | 125.00 | | 30005 | 236.85 | +-------+--------------------------+ 4 rows in set (0.00 sec) mysql>

13.使用limit限制查询结果的数量

select返回所有匹配的行，有可能是表中所有的行，如仅仅需要返回第一行或者前几行，使用limit关键字

语法格式如下：

limit [位置偏移量] 行数

应用：

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.70 | | 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) mysql> select * from fruits limit 4; #显示前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 3,5; #跳3个，从第4行开始显示，显示5行 +------+------+--------+---------+ | f_id | s_id | f_name | f_price | +------+------+--------+---------+ | 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 | +------+------+--------+---------+ 5 rows in set (0.00 sec) mysql>

三、使用聚合函数查询

函数作用AVG()返回某列的平均值COUNT()返回某列的行数MAX()返回某列的最大值MIN()返回某列的最小值SUM()返回某列的和

1.count()函数：统计

Count()函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数。 Count(*)计算表中总的函数，不管某列有数列或者为空值 Count(字段名)计算指定列下总的行数，计算时将忽略空值的行

mysql> select * from customers; +-------+----------+---------------------+---------+--------+-----------+-------------------+ | c_id | c_name | c_address | c_city | c_zip | c_contact | c_email | +-------+----------+---------------------+---------+--------+-----------+-------------------+ | 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 | +-------+----------+---------------------+---------+--------+-----------+-------------------+ 4 rows in set (0.00 sec) mysql> select count(*) from customers; #统计customers表有几行 +----------+ | count(*) | +----------+ | 4 | +----------+ 1 row in set (0.00 sec) mysql> select count(c_email) from customers; #统计customers表的c_email列有几行，null是不计数的 +----------------+ | count(c_email) | +----------------+ | 3 | +----------------+ 1 row in set (0.00 sec) mysql> select * from orderitems; +-------+--------+------+----------+------------+ | o_num | o_item | f_id | quantity | item_price | +-------+--------+------+----------+------------+ | 30001 | 1 | a1 | 10 | 5.20 | | 30001 | 2 | b2 | 3 | 7.60 | | 30001 | 3 | bs1 | 5 | 11.20 | | 30001 | 4 | bs2 | 15 | 9.20 | | 30002 | 1 | b3 | 2 | 20.00 | | 30003 | 1 | c0 | 100 | 10.00 | | 30004 | 1 | o2 | 50 | 2.50 | | 30005 | 1 | c0 | 5 | 10.00 | | 30005 | 2 | b1 | 10 | 8.99 | | 30005 | 3 | a2 | 10 | 2.20 | | 30005 | 4 | m1 | 5 | 14.99 | +-------+--------+------+----------+------------+ 11 rows 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) mysql>

2.sum()函数：求和

sum()是一个求总和的函数，返回指定列值得总和。

注意：sum()函数在计算时，忽略列值为NULL的行。

mysql> select sum(quantity) from orderitems; +---------------+ | sum(quantity) | +---------------+ | 215 | +---------------+ 1 row in set (0.00 sec) mysql> select sum(quantity) from orderitems where o_num = 30005; +---------------+ | sum(quantity) | +---------------+ | 30 | +---------------+ 1 row in set (0.01 sec) mysql> select o_num,sum(quantity) from orderitems group by o_num; +-------+---------------+ | o_num | sum(quantity) | +-------+---------------+ | 30001 | 33 | | 30002 | 2 | | 30003 | 100 | | 30004 | 50 | | 30005 | 30 | +-------+---------------+ 5 rows in set (0.00 sec) mysql>

3.avg()函数：平均值

avg()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。

mysql> select avg(f_price) from fruits where s_id=103; +--------------+ | avg(f_price) | +--------------+ | 5.700000 | +--------------+ 1 row in set (0.00 sec) mysql> select s_id,avg(f_price) from fruits group by s_id; +------+--------------+ | s_id | avg(f_price) | +------+--------------+ | 101 | 6.200000 | | 102 | 8.933333 | | 103 | 5.700000 | | 104 | 7.000000 | | 105 | 7.466667 | | 106 | 15.700000 | | 107 | 3.600000 | +------+--------------+ 7 rows in set (0.00 sec) mysql>

4.max()函数：最大值

max()返回指定列中的最大值

mysql> select max(f_price) as max_price from fruits; +-----------+ | max_price | +-----------+ | 15.70 | +-----------+ 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.70 | | 107 | 3.60 | +------+-----------+ 7 rows in set (0.00 sec) mysql>

5.min()函数：最小值

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 max_price from fruits group by s_id; +------+-----------+ | s_id | max_price | +------+-----------+ | 101 | 3.20 | | 102 | 5.30 | | 103 | 2.20 | | 104 | 6.40 | | 105 | 2.60 | | 106 | 15.70 | | 107 | 3.60 | +------+-----------+ 7 rows in set (0.00 sec) mysql>

四、连接查询

连接是关系数据库模型的主要特点。连接查询是关系数据库中最主要的查询，主要包括内连接、外连接。通过连接运算符可以实现多个表查询。在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当查询数据时，通过连接操作查询出存放在多个表中的不同实体的信息。当两个或多个表现中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询。

1.内连接查询

（1）内连接

内连接（inner join）使用比较运算符进行表间某些列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新纪录，也就是说，在内连接查询中，只有满足条件的记录才能出现在结果关系中。

案例一：

mysql> create table class1( -> id int primary key auto_increment, -> name varchar(20), -> sex char, -> tel int, -> addr varchar(100)); Query OK, 0 rows affected (0.67 sec) mysql> desc class1; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | tel | int(11) | YES | | NULL | | | addr | varchar(100) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> insert into class1(name,sex,tel,addr) -> values ("zhao","1",123456,"beijing"),("qian","1",234567,"tianjin"); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> create table score( -> id int primary key auto_increment, -> name varchar(20), -> scores int, -> appraise varchar(10)); Query OK, 0 rows affected (0.52 sec) mysql> desc score; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | scores | int(11) | YES | | NULL | | | appraise | varchar(10) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> insert into score(name,scores,appraise) -> values("zhao",80,"good"),("qian",50,"bad"); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from class1; +----+------+------+--------+---------+ | id | name | sex | tel | addr | +----+------+------+--------+---------+ | 1 | zhao | 1 | 123456 | beijing | | 2 | qian | 1 | 234567 | tianjin | +----+------+------+--------+---------+ 2 rows in set (0.00 sec) mysql> select * from score; +----+------+--------+----------+ | id | name | scores | appraise | +----+------+--------+----------+ | 1 | zhao | 80 | good | | 2 | qian | 50 | bad | +----+------+--------+----------+ 2 rows in set (0.00 sec) mysql> select score.name, score.scores, score.appraise, class1.tel, class1.addr from class1,score where class1.name=score.name; +------+--------+----------+--------+---------+ | name | scores | appraise | tel | addr | +------+--------+----------+--------+---------+ | zhao | 80 | good | 123456 | beijing | | qian | 50 | bad | 234567 | tianjin | +------+--------+----------+--------+---------+ 2 rows in set (0.00 sec) mysql> select score.name, score.scores, score.appraise, class1.tel, class1.addr -> from class1 inner join score -> on class1.name=score.name; #内连接查询 +------+--------+----------+--------+---------+ | name | scores | appraise | tel | addr | +------+--------+----------+--------+---------+ | zhao | 80 | good | 123456 | beijing | | qian | 50 | bad | 234567 | tianjin | +------+--------+----------+--------+---------+ 2 rows in set (0.00 sec) #可只加第一个表的表名，后面的省略，即使tel和addr的表名不一样也可 mysql> select score.name, scores, appraise, tel, addr -> from class1 inner join score -> on class1.name=score.name; +------+--------+----------+--------+---------+ | name | scores | appraise | tel | addr | +------+--------+----------+--------+---------+ | zhao | 80 | good | 123456 | beijing | | qian | 50 | bad | 234567 | tianjin | +------+--------+----------+--------+---------+ 2 rows in set (0.00 sec) mysql>

案例二：

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> desc suppliers; +--------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+----------------+ | s_id | int(11) | NO | PRI | NULL | auto_increment | | s_name | char(50) | NO | | NULL | | | s_city | char(50) | YES | | NULL | | | s_zip | char(10) | YES | | NULL | | | s_call | char(50) | NO | | NULL | | +--------+----------+------+-----+---------+----------------+ 5 rows in set (0.00 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 mysql> select * from suppliers; +------+----------------+-----------+--------+--------+ | s_id | s_name | s_city | s_zip | s_call | +------+----------------+-----------+--------+--------+ | 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 | +------+----------------+-----------+--------+--------+ 7 rows in set (0.00 sec) 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.70 | | 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) 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.70 | | 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>

（2）自连接

如果在一个连接查询中，涉及的两个表都是同一个表，这种查询称为自连接查询。自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。

案例一：

mysql> select c1.name, c1.tel #注意此处的写法 -> from class1 as c1, class1 as c2 -> where c1.name=c2.name and c2.addr='beijing'; +------+--------+ | name | tel | +------+--------+ | zhao | 123456 | +------+--------+ 1 row in set (0.00 sec) mysql>

案例二：

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>

2.外连接查询

外连接查询将将查询多个表中相关联的行，内连接时，返回查询结果集合中的仅是符合查询条件和连接条件的行。但有时候需要包含没有关联的行中数据，即返回查询结果集合中的不仅包含符合连接条件的行，而且还包含左表（左外连接或左连接）、右表（右外连接或右连接）或两个连接表（全外连接）中的所有数据行。外连接分为左外连接或左连接和右外连接或右连接。

案例一：

mysql> insert into class1(name,sex,tel,addr) values("sun","2",345678,"heibei"); Query OK, 1 row affected (0.01 sec) mysql> select * from class1; +----+------+------+--------+---------+ | id | name | sex | tel | addr | +----+------+------+--------+---------+ | 1 | zhao | 1 | 123456 | beijing | | 2 | qian | 1 | 234567 | tianjin | | 3 | sun | 2 | 345678 | heibei | +----+------+------+--------+---------+ 3 rows in set (0.00 sec) mysql> select * from score; +----+------+--------+----------+ | id | name | scores | appraise | +----+------+--------+----------+ | 1 | zhao | 80 | good | | 2 | qian | 50 | bad | +----+------+--------+----------+ 2 rows in set (0.00 sec) mysql> select score.name, scores, appraise, tel, addr from class1 inner join score on class1.name=score.name; +------+--------+----------+--------+---------+ | name | scores | appraise | tel | addr | +------+--------+----------+--------+---------+ | zhao | 80 | good | 123456 | beijing | | qian | 50 | bad | 234567 | tianjin | #没有查到sun的记录，因为sun再score表中没有记录 +------+--------+----------+--------+---------+ 2 rows in set (0.00 sec) mysql>

案例二：

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.01 sec) mysql> desc orders; +--------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+----------------+ | o_num | int(11) | NO | PRI | NULL | auto_increment | | o_date | datetime | NO | | NULL | | | c_id | int(11) | NO | | NULL | | +--------+----------+------+-----+---------+----------------+ 3 rows in set (0.00 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.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from customers; +-------+----------+---------------------+---------+--------+-----------+-------------------+ | c_id | c_name | c_address | c_city | c_zip | c_contact | c_email | +-------+----------+---------------------+---------+--------+-----------+-------------------+ | 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 | +-------+----------+---------------------+---------+--------+-----------+-------------------+ 4 rows in set (0.00 sec) mysql> select * from orders; +-------+---------------------+-------+ | o_num | o_date | c_id | +-------+---------------------+-------+ | 30001 | 2008-09-01 00:00:00 | 10001 | | 30002 | 2008-09-12 00:00:00 | 10003 | | 30003 | 2008-09-30 00:00:00 | 10004 | | 30004 | 2008-10-03 00:00:00 | 10005 | | 30005 | 2008-10-08 00:00:00 | 10001 | +-------+---------------------+-------+ 5 rows in set (0.00 sec) mysql>

（1）左连接

Left join（左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。

案例一：

mysql> select score.name, appraise, tel, addr -> from class1 left outer join score #依据左表class1查询 -> on class1.name=score.name; +------+----------+--------+---------+ | name | appraise | tel | addr | +------+----------+--------+---------+ | zhao | good | 123456 | beijing | | qian | bad | 234567 | tianjin | | NULL | NULL | 345678 | heibei | +------+----------+--------+---------+ 3 rows in set (0.00 sec) mysql> select score.name, appraise, tel, addr -> from score left outer join class1 #依据左表score查询 -> on class1.name=score.name; +------+----------+--------+---------+ | name | appraise | tel | addr | +------+----------+--------+---------+ | zhao | good | 123456 | beijing | | qian | bad | 234567 | tianjin | +------+----------+--------+---------+ 2 rows in set (0.00 sec) mysql>

案例二：

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 | | 10003 | 30002 | | 10004 | 30003 | | 10001 | 30005 | | 10002 | NULL | +-------+-------+ 5 rows in set (0.00 sec) mysql>

（2）右连接

Right join（右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。

案例一：

mysql> insert into score(name,scores,appraise) values("li",100,"excellent"); Query OK, 1 row affected (0.00 sec) mysql> select * from class1; +----+------+------+--------+---------+ | id | name | sex | tel | addr | +----+------+------+--------+---------+ | 1 | zhao | 1 | 123456 | beijing | | 2 | qian | 1 | 234567 | tianjin | | 3 | sun | 2 | 345678 | heibei | +----+------+------+--------+---------+ 3 rows in set (0.00 sec) mysql> select * from score; +----+------+--------+-----------+ | id | name | scores | appraise | +----+------+--------+-----------+ | 1 | zhao | 80 | good | | 2 | qian | 50 | bad | | 3 | li | 100 | excellent | +----+------+--------+-----------+ 3 rows in set (0.00 sec) mysql> select score.name, appraise, tel, addr -> from class1 right outer join score #依据右表score查询 -> on class1.name=score.name; +------+-----------+--------+---------+ | name | appraise | tel | addr | +------+-----------+--------+---------+ | zhao | good | 123456 | beijing | | qian | bad | 234567 | tianjin | | li | excellent | NULL | NULL | +------+-----------+--------+---------+ 3 rows in set (0.00 sec) mysql> select score.name, appraise, tel, addr -> from score right outer join class1 #依据右表class1查询 -> on class1.name=score.name; +------+----------+--------+---------+ | name | appraise | tel | addr | +------+----------+--------+---------+ | zhao | good | 123456 | beijing | | qian | bad | 234567 | tianjin | | NULL | NULL | 345678 | heibei | +------+----------+--------+---------+ 3 rows in set (0.00 sec) mysql>

案例二：

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) mysql>

3.复合条件连接查询

复合条件连接查询是在连接查询的过程中，通过添加过滤条件，限制查询的结果，使查询的结果更加准确。

案例一：

mysql> select score.name, scores, appraise, tel, addr -> from class1 inner join score -> on class1.name=score.name and class1.addr="beijing"; +------+--------+----------+--------+---------+ | name | scores | appraise | tel | addr | +------+--------+----------+--------+---------+ | zhao | 80 | good | 123456 | beijing | +------+--------+----------+--------+---------+ 1 row in set (0.00 sec) mysql>

案例二：

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) mysql>

案例三：

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 | orange | 11.20 | | 102 | LT Supplies | banana | 10.30 | | 102 | LT Supplies | grape | 5.30 | | 103 | acme | apricot | 2.20 | | 103 | acme | coconut | 9.20 | | 104 | fnk inc. | berry | 7.60 | | 104 | fnk inc. | lemon | 6.40 | | 105 | good set | melon | 8.20 | | 105 | good set | xbabay | 2.60 | | 105 | good set | xxtt | 11.60 | | 106 | just eat ours | mango | 15.70 | | 107 | dk inc. | xxxx | 3.60 | | 107 | dk inc. | xbababa | 3.60 | +------+----------------+------------+---------+ 16 rows in set (0.00 sec) mysql>

五、子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从mysql4.1开始引入。在select子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

子查询中常用的操作符有any（some）、all、in、exists。

子查询可以添加到select、update和delete语句中，而且可以进行多层嵌套。

子查询中也可以使用比较运算符，如“<”，“<=”，“>”，“>=”和“!=”等。

1.带any、some关键字的子查询

any和some关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。

mysql> create table tb1(num1 int not null); Query OK, 0 rows affected (0.09 sec) mysql> create table tb2(num2 int not null); Query OK, 0 rows affected (0.01 sec) mysql> insert into tb1 values(1),(5),(13),(27); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into tb2 values(6),(14),(11),(20); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from tb1; +------+ | num1 | +------+ | 1 | | 5 | | 13 | | 27 | +------+ 4 rows in set (0.00 sec) mysql> select * from tb2; +------+ | num2 | +------+ | 6 | | 14 | | 11 | | 20 | +------+ 4 rows in set (0.00 sec) mysql> select num1 from tb1 where num1 > any (select num2 from tb2); +------+ | num1 | #只要num1中的值大于num2中的任意一个，即输出 +------+ | 13 | | 27 | +------+ 2 rows in set (0.01 sec) mysql> select num1 from tb1 where num1 > some (select num2 from tb2); #同any +------+ | num1 | +------+ | 13 | | 27 | +------+ 2 rows in set (0.00 sec) mysql>

2.带all关键字的子查询

all关键字与any和some不同，使用all时需要同时满足所有内层查询的条件。

mysql> select num1 from tb1 where num1 > all (select num2 from tb2); +------+ | num1 | #num1中的值必须大于num2中的所有值，才能输出 +------+ | 27 | +------+ 1 row in set (0.00 sec) mysql>

3.带exists关键字的子查询

exists关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么exists的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么exists返回的结果是false，此时外层语句将不进行查询。

mysql> select * from suppliers; +------+----------------+-----------+--------+--------+ | s_id | s_name | s_city | s_zip | s_call | +------+----------------+-----------+--------+--------+ | 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 | +------+----------------+-----------+--------+--------+ 7 rows in set (0.00 sec) 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.70 | | 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) #exists后面的执行结果为真（有这条记录），则执行前面的查询 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.70 | | 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) #exists后面的执行结果为假（没有这条记录），则不执行前面的查询 mysql> select * from fruits where exists (select s_name from suppliers where s_id=109); Empty set (0.00 sec) mysql>

4.带in关键字的子查询

in关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。

mysql> select * from orders; +-------+---------------------+-------+ | o_num | o_date | c_id | +-------+---------------------+-------+ | 30001 | 2008-09-01 00:00:00 | 10001 | | 30002 | 2008-09-12 00:00:00 | 10003 | | 30003 | 2008-09-30 00:00:00 | 10004 | | 30004 | 2008-10-03 00:00:00 | 10005 | | 30005 | 2008-10-08 00:00:00 | 10001 | +-------+---------------------+-------+ 5 rows in set (0.00 sec) mysql> select * from orderitems; +-------+--------+------+----------+------------+ | o_num | o_item | f_id | quantity | item_price | +-------+--------+------+----------+------------+ | 30001 | 1 | a1 | 10 | 5.20 | | 30001 | 2 | b2 | 3 | 7.60 | | 30001 | 3 | bs1 | 5 | 11.20 | | 30001 | 4 | bs2 | 15 | 9.20 | | 30002 | 1 | b3 | 2 | 20.00 | | 30003 | 1 | c0 | 100 | 10.00 | | 30004 | 1 | o2 | 50 | 2.50 | | 30005 | 1 | c0 | 5 | 10.00 | | 30005 | 2 | b1 | 10 | 8.99 | | 30005 | 3 | a2 | 10 | 2.20 | | 30005 | 4 | m1 | 5 | 14.99 | +-------+--------+------+----------+------------+ 11 rows in set (0.00 sec) mysql> select c_id,o_num from orders where o_num in -> (select o_num from orderitems where f_id = 'c0'); +-------+-------+ | c_id | o_num | #先找出in后面的查询结果，再将结果作为in前面的查询语句的条件 +-------+-------+ | 10004 | 30003 | | 10001 | 30005 | +-------+-------+ 2 rows in set (0.00 sec) mysql>

5.带比较运算符的子查询

mysql> select * from suppliers; +------+----------------+-----------+--------+--------+ | s_id | s_name | s_city | s_zip | s_call | +------+----------------+-----------+--------+--------+ | 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 | +------+----------------+-----------+--------+--------+ 7 rows in set (0.00 sec) 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.70 | | 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) 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 | #先找出'tianjin'对应的s_id=106，再找出s_id大于106对应的f_name +------+------------+ | 101 | apple | | 101 | blackberry | | 101 | cherry | +------+------------+ 3 rows in set (0.00 sec) mysql> select s_id,f_name from fruits where s_id > (select s1.s_id from suppliers as s1 where s1.s_city='beijing'); +------+---------+ | s_id | f_name | #先找出'beijing'对应的s_id=101，再找出101对应的f_name +------+---------+ | 107 | xxxx | | 107 | xbababa | +------+---------+ 2 rows in set (0.00 sec) mysql>

六、合并查询结果

利用union关键字，可以给出多条select语句，并将它们的结果组合成单个结果集。

合并时，两个表对应的列数和数据类型必须相同。

各个select语句之间使用union或union all关键字分隔。

union不使用关键字all，执行的时候删除重复的记录，所有返回的行都是唯一的；使用关键字all的作用是不删除重复行也不对结果进行自动排序。

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.70 | | 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) mysql> select s_id,f_name,f_price from fruits where f_price < 9.0; #找出小于9.0 +------+---------+---------+ | 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 | +------+---------+---------+ 10 rows in set (0.00 sec) mysql> select s_id,f_name,f_price from fruits where s_id in (101,103); #找出s_id是101和103的 +------+------------+---------+ | s_id | f_name | f_price | +------+------------+---------+ | 101 | apple | 5.20 | | 103 | apricot | 2.20 | | 101 | blackberry | 10.20 | | 101 | cherry | 3.20 | | 103 | coconut | 9.20 | +------+------------+---------+ 5 rows in set (0.00 sec) 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) mysql> select s_id,f_name,f_price from fruits where f_price < 9.0 -> union #将两个表去重连接 -> 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 | blackberry | 10.20 | | 103 | coconut | 9.20 | +------+------------+---------+ 12 rows in set (0.00 sec) mysql>

**union和union all的区别：**使用union all的功能是不删除重复行，加上all关键字语句执行时所需要的资源少，所以尽可能地使用它，因此知道有重复行但是想保留这些行，确定查询结果中不会有重复数据或者不需要去掉重复数据的时候，应当使用union all以提高查询效率。

七、为表和字段取别名

前面介绍了分组查询、聚合函数查询和嵌套子查询，取别名使用关键字as为查询结果中的某一列指定一个特别的名字。可以为字段或者表分别取别名，在查询时，使用别名替代指定的内容。

1.为表取别名

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) mysql>

2.为字段取别名

mysql> select 3+3, 3+3 as sum; +-----+-----+ | 3+3 | sum | +-----+-----+ | 6 | 6 | +-----+-----+ 1 row in set (0.00 sec) mysql> select f1.f_name as fruits_name ,f1.f_price as fruits_price -> from fruits as f1 -> where f1.f_price < 8; +-------------+--------------+ | fruits_name | fruits_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) mysql>

八、使用正则表达式查询

正则表达式通常被用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串。例如从一个文本文件中提取电话号码，查找一篇文章中重复的单词或者替换用户输入的某些敏感词语等等，这些地方都可以使用正则表达式。正则表达式强大且灵活，可以应用于非常复杂的查询。

mysql中使用regexp关键字指定正则表达式的字符匹配模式。

选项说明例子匹配值实例^匹配文本的开始字符^b’匹配以字母b开头的字符串book，big，bike$匹配文本的结束字符st$'匹配以st结尾的字符串test，resist.匹配任何单个字符b.t’匹配任何b和t之间有一个字符bit，bat，but*匹配零个或者多个在它前面的字符f*n’匹配字符n前面有任意个字符fn，fan，faaan+匹配前面的字符1次或多次ba+'匹配以b开头后面紧跟至少有一个aba，bay，bare，battle<字符串>匹配包含指定的字符串的文本fa’ fan,afa,faad[字符集合]匹配字符集合中的任何一个字符[xz]'匹配x或者zdizzy，zabra，extra[^]匹配不在括号中的任何字符[^abc]'匹配任何不包括a、b 或c的字符串desk，fox，f8ke字符串{n,}匹配前面的字符串至少n次b{2}匹配2个或者更多的bbbb,bbbbb字符串{n,m}匹配前面的字符串至少n次，至多m次。如果n为0，则省略b{2,4}匹配最少2个，最多4个bbb,bbb,bbbb

1.查询以特定字符或字符串开头的记录

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.70 | | 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) mysql> select * from fruits where f_name like '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) 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) mysql>

2.查询以特定字符或字符串结尾的记录

mysql> select * from fruits where f_name like '%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) 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.01 sec) mysql>

3.用符合**‘.’**来代替字符串中的任意一个字符

mysql> select * from fruits where f_name like '%a%g%'; +------+------+--------+---------+ | f_id | s_id | f_name | f_price | +------+------+--------+---------+ | bs1 | 102 | orange | 11.20 | | m1 | 106 | mango | 15.70 | +------+------+--------+---------+ 2 rows in set (0.00 sec) 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.70 | +------+------+--------+---------+ 2 rows in set (0.00 sec) mysql>

4.使用**“*”和“+”**来匹配多个字符

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) 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) 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) mysql> select * from fruits where f_name regexp '^ba+'; # +匹配前面的字符1次或多次 +------+------+--------+---------+ | f_id | s_id | f_name | f_price | +------+------+--------+---------+ | t1 | 102 | banana | 10.30 | +------+------+--------+---------+ 1 row in set (0.00 sec) mysql>

5.匹配指定字符串

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) mysql>

6.匹配指定字符中的任意一个

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.70 | | m3 | 105 | xxtt | 11.60 | | o2 | 103 | coconut | 9.20 | +------+------+---------+---------+ 7 rows in set (0.00 sec) 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.70 | | m2 | 105 | xbabay | 2.60 | | m3 | 105 | xxtt | 11.60 | +------+------+--------+---------+ 6 rows in set (0.00 sec) mysql> select * from fruits where s_id regexp '[3-8]'; +------+------+---------+---------+ | f_id | s_id | f_name | f_price | +------+------+---------+---------+ | a2 | 103 | apricot | 2.20 | | b2 | 104 | berry | 7.60 | | b5 | 107 | xxxx | 3.60 | | bs2 | 105 | melon | 8.20 | | l2 | 104 | lemon | 6.40 | | m1 | 106 | mango | 15.70 | | m2 | 105 | xbabay | 2.60 | | m3 | 105 | xxtt | 11.60 | | o2 | 103 | coconut | 9.20 | | t4 | 107 | xbababa | 3.60 | +------+------+---------+---------+ 10 rows in set (0.00 sec) mysql>

7.匹配指定字符以外的字符

mysql> select * from fruits where f_id regexp '[^a-e1-2]'; #此表达式要注意 +------+------+---------+---------+ | f_id | s_id | f_name | f_price | +------+------+---------+---------+ | b5 | 107 | xxxx | 3.60 | #先查找^a-e，虽然b属于a-e，应是不输出，但是mysql会继续向下查询，5不属于1-2，故结果是输出该行 | bs1 | 102 | orange | 11.20 | | bs2 | 105 | melon | 8.20 | | c0 | 101 | cherry | 3.20 | | l2 | 104 | lemon | 6.40 | | m1 | 106 | mango | 15.70 | | 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) mysql> select * from fruits where f_id regexp '^[^a-e1-2]'; +------+------+---------+---------+ | f_id | s_id | f_name | f_price | +------+------+---------+---------+ | l2 | 104 | lemon | 6.40 | | m1 | 106 | mango | 15.70 | | 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 | +------+------+---------+---------+ 8 rows in set (0.00 sec) mysql>

8.使用{n,}或者{n,m}来指定字符串连续出现的次数

mysql> select * from fruits where f_name regexp 'x{2}'; #查找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) mysql> select * from fruits where f_name regexp 'x{2,}'; #查找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) mysql> select * from fruits where f_name regexp 'ba{1,3}'; #查找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) mysql>

这只是我的一些浅薄的见解，望多指教！