一、基本查询语句
数据库管理系统的一个最重要的功能就是数据查询，数据查询不只是简单查询数据库中存储的数据，还可以根据需要对数据进行筛选，以及确定数据以什么样的格式显示。MySQL提供了功能强大、灵活的语句来实现这些操作。
1.基本查询语句
mysql从数据表中查询数据的基本语句为select语句。
select语句的基本格式是：
SELECT {* | <字段列表>} [ FROM <表1>, <表2>.... [ where <表达式> ] [ group by ] [ having ] [ order by <..> ] [ limit <...> ]
(1){*|<字段列表>}：包含星号通配符选择字段列表，表示查询的字段，其中字段列至少包含一个字段名称，如果要查询多个字段，多个字段之间用逗号隔开，最后一个字段后不要加逗号;
(2)FROM<表1>,<表2>...：表1和表2表示查询数据的来源，可以是单个或多个;
(3)WHERE子句：可选项，如果选择该项，将限定查询必须满足的查询条件;
(4)GROUP BY <字段>： 依据某个字段进行分组;
(5)ORDER BY <字段>：该子句告诉MySQL按什么样的顺序显示查询出来的数据;
可以进行的排序有:升序(asc)、降序(desc);
(6)[limit]:该子句告诉mysql每次显示查询出来的数据条款;
创建表并插入数据
mysql> create table fruits
-> (
-> f_id char(10) not null,
-> s_id int not null,
-> f_name char(255) not null,
-> f_price decimal(8,2) not null,
-> primary key(f_id)
-> );
Query OK, 0 rows affected (0.02 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.02 sec)
Records: 16 Duplicates: 0 Warnings: 0
二、单表查询
单表查询指从一张表数据中查询所需的数据。
主要有：查询所有字段、查询指定字段、查询指定记录、查询空值、多条件的查询、对查询结果进行排序等方式。
注意：如果一个SQL同时存在多个子句，则子句执行顺序是
where --> group by --> having --> order by --> limit
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)
根据前面select语句的格式，select关键字后面的字段名为将要查询的数据，因此可以将表中所有字段的名称跟在select子句后面，如果忘记了字段名称，可以使用DESC命令查看表的结构。有可能表中的字段比较多，不一定能记住所有的字段名称。因此很不方便，不建议使用。
select f_id,s_id,f_name,f_price from fruits;
2.查询指定字段
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)
查询多个字段使用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)
3.查询指定记录
数据库中包含大量的数据，根据特殊要求可能只需要查询表中的指定数据，相当于对数据的过滤。
在select语句中，通过where子句对数据进行过滤。如果有多个过滤条件时需要注意顺序，AND优先于OR，可以加括号来改变优先级。
select 字段1,字段2,...,字段n from 表名 where 查询条件;
| 操作符 |
说明 |
| = |
相等 |
| <>,!= |
不相等 |
| < |
小于 |
| <= |
小于等于 |
| > |
大于 |
| >= |
大于等于 |
| between and |
位于…之间 |
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)
4.带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)
5.带between and的范围查询
between and用来查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值，如果字段值满足指定的范围查询条件，则这些记录被返回(包含边界值)。
mysql> select f_name,f_price from fruits where f_price between 5.2 and 10.2;
+------------+---------+
| f_name | f_price |
+------------+---------+
| apple | 5.20 |
| blackberry | 10.20 |
| berry | 7.60 |
| melon | 8.20 |
| lemon | 6.40 |
| coconut | 9.20 |
| grape | 5.30 |
+------------+---------+
7 rows in set (0.00 sec)
6.带like的字符匹配查询
通常和where一起使用，实现字符串的模糊匹配(非字符串类型不行)。需要注意如果%出现在匹配条件的最前面，不会走索引。
通配符是一种在SQL的where条件子句中拥有特殊意思的字符，SQL语句中支持多种通配符，可以和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 '____y';
+------+--------+
| f_id | f_name |
+------+--------+
| b2 | berry |
+------+--------+
1 row in set (0.00 sec)
7.查询空值
数据表创建的时候，可以指定某列中是否可以包含空值。空值不同于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.01 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.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select c_id,c_name,c_email from customers where c_email IS NULL;
+-------+----------+---------+
| c_id | c_name | c_email |
+-------+----------+---------+
| 10003 | Netbhood | NULL |
+-------+----------+---------+
1 row in set (0.00 sec)
8.带and的多条件查询
使用select查询时，可以增加查询的限制条件，这样可以使查询的结果更加精确。
MySQL在where子句中使用and操作符限定只有满足所有查询条件的记录才会被返回。可以使用and连接两个甚至多个查询条件，多个条件表达式之间用and分开。
mysql> select s_id,f_name,f_price from fruits where s_id=101 and f_price>=5;
+------+------------+---------+
| s_id | f_name | f_price |
+------+------------+---------+
| 101 | apple | 5.20 |
| 101 | blackberry | 10.20 |
+------+------------+---------+
2 rows in set (0.00 sec)
9.带or的多条件查询
与and相反，在where声明中使用or操作符，表示只需要满足其中一个条件的记录即可返回。or也可以连接两个甚至多个查询条件，多个条件表达式之间用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)
10.查询结果不重复
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)
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.01 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)
(2)多列排序
在多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。
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.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)
(3)指定排序方向
默认情况下，查询数据按字母升序进行排序（从A-Z)，但数据的排序并不仅限于此，还可以使用order by对查询结果进行降序排序（从Z-A)，这可以通过关键字DESC实现。
mysql> select f_name from fruits order by f_name desc;
+------------+
| f_name |
+------------+
| xxxx |
| xxtt |
| xbabay |
| xbababa |
| orange |
| melon |
| mango |
| lemon |
| grape |
| coconut |
| cherry |
| blackberry |
| berry |
| banana |
| apricot |
| apple |
+------------+
16 rows in set (0.00 sec)
mysql> select f_name from fruits order by f_name asc;
+------------+
| 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)
12.分组查询
分组查询是对数据按照某个或多个字段进行分组，MySQL中使用group by关键字对数据进行分组。
基本语法形式为：group by 字段
(1)创建分组
通常和聚合函数一起使用，例如：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,group_concat(f_name) from fruits group by s_id;
+------+-------------------------+
| s_id | group_concat(f_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)
(2)使用having过滤分组
对group by分组后的数据进行筛选，类似where，只不过优先级在where之后。
根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息。
mysql> select s_id,group_concat(f_name) from fruits group by s_id having count(f_name)>1;
+------+-------------------------+
| s_id | group_concat(f_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)
(3)在group by子句中使用with rollup
使用with rollup关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
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)
(4)多字段分组
使用group by可以对多个字段进行分组，group by关键字后面跟需要分组的字段，MySQL根据
多字段的值来进行层次分组，分组层次从左到右，即先按第1个字段分组，然后在第1个字段值相同的记录中，再根据第2个字段的值进行分组，以此类推。
mysql> select s_id,f_name from fruits group by s_id,f_name;
+------+------------+
| s_id | f_name |
+------+------------+
| 101 | apple |
| 101 | blackberry |
| 101 | cherry |
| 102 | banana |
| 102 | grape |
| 102 | orange |
| 103 | apricot |
| 103 | coconut |
| 104 | berry |
| 104 | lemon |
| 105 | melon |
| 105 | xbabay |
| 105 | xxtt |
| 106 | mango |
| 107 | xbababa |
| 107 | xxxx |
+------+------------+
16 rows in set (0.00 sec)
(5)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.01 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
查询价格大于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)
13.使用limit限制查询结果的数量
select返回所有匹配的行，有可能是表中所有的行，如仅仅需要返回第一行或者前几行，使用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)
三、常用函数查询
注意：不同的函数可嵌套组合使用，但是聚合函数不可嵌套。
字符函数
CONCAT()：实现字段拼接，函数中没有引号的属于表中字段。
#拼接id和name字段
mysql> select concat(id,name) from student1;
+-----------------+
| concat(id,name) |
+-----------------+
| 1zj |
| 2li |
| 3zj |
| 4li |
+-----------------+
4 rows in set (0.00 sec)
#将user字段和host字段拼接，中间自定义了一个@符作为分割。
mysql> select concat(user,'@',host) from mysql.user;
+-------------------------+
| concat(user,'@',host) |
+-------------------------+
| mysql.session@localhost |
| mysql.sys@localhost |
| root@localhost |
+-------------------------+
3 rows in set (0.00 sec)
聚合函数
| 函数 |
作用 |
| avg() |
返回某列的平均值 |
| count() |
返回某列的行数 |
| max() |
返回某列的最大值 |
| min() |
返回某列的最小值 |
| sum() |
返回某列的和 |
1.count函数
统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数。
count(*)计算表中总的函数，不管某列有数值或者为空值，count(字段名)计算指定列下总的行数，计算时将忽略空值的行。
mysql> select count(*) from customers;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
mysql> select count(c_email) from customers;
+----------------+
| count(c_email) |
+----------------+
| 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.01 sec)
2.sum()函数
sum()是一个求总和的函数，返回指定列值得总和。
mysql> select sum(quantity) from orderitems where o_num=30005;
+---------------+
| sum(quantity) |
+---------------+
| 30 |
+---------------+
1 row in set (0.00 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)
注意：sum()函数在计算时，忽略列值为NULL的行。
3.avg()函数
avg()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。
mysql> select s_id,avg(f_price) from fruits where s_id=103;
+------+--------------+
| s_id | avg(f_price) |
+------+--------------+
| 103 | 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)
4.max()函数
max()返回指定列中的最大值。
mysql> select max(f_price) from fruits;
+--------------+
| max(f_price) |
+--------------+
| 15.70 |
+--------------+
1 row in set (0.00 sec)
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)
5.min()函数
min()返回查询列中的最小值。
mysql> select min(f_price) from fruits;
+--------------+
| min(f_price) |
+--------------+
| 2.20 |
+--------------+
1 row in set (0.00 sec)
mysql> select s_id,min(f_price) from fruits group by s_id;
+------+--------------+
| s_id | min(f_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)
四、连接查询
连接是关系数据库模型的主要特点。连接查询是关系数据库中最主要的查询，主要包括内连接、外连接。通过连接运算符可以实现多个表查询。在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当查询数据时，通过连接操作查询出存放在多个表中的不同实体的信息。当两个或多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询。
1.内连接查询
内连接（inner join）使用比较运算符进行表间某些列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新纪录，也就是说，在内连接查询中，只有满足条件的记录才能出现在结果关系中。
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.02 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.01 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> 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.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> 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.01 sec)
2.外连接查询
查询多个表中相关联的行，内连接时，返回查询结果集合中的仅是符合查询条件和连接条件的行。但有时候需要包含没有关联的行中数据，即返回查询结果集合中的不仅包含符合连接条件的行，而且还包含左表（左外连接或左连接）、右表（右外连接或右连接）或两个连接表（全外连接）中的所有数据行。外连接分为左外连接或左连接和右外连接或右连接。
left join(左连接)：返回包括左表中的所有记录和右表中连接字段相等的记录。
right join(右连接)：返回包括右表中的所有记录和左表中连接字段相等的记录。
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.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.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
(1)左连接
列出左表的全部记录+右边表部分数据
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 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)
(2)右连接
列出右表的全部记录+左边表部分数据
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)
3.复合条件连接查询
复合条件连接查询是在连接查询的过程中，通过添加过滤条件，限制查询的结果，使查询的结果更加准确。
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> 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)
4.自然连接(natural join)
取多张表的交集
mysql> select * from student1;
+------+------+
| id | name |
+------+------+
| 1 | zj |
| 2 | li |
| 3 | zj |
| 4 | li |
+------+------+
4 rows in set (0.00 sec)
mysql> select * from student2;
+------+------+
| id | name |
+------+------+
| 3 | zj |
| 4 | li |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from student1 natural join student2;
+------+------+
| id | name |
+------+------+
| 3 | zj |
| 4 | li |
+------+------+
2 rows in set (0.00 sec)
五、子查询
指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从mysql 4.1开始引入。
在select子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。子查询可以添加到select、update和delete语句中，而且可以进行多层嵌套。
| 操作符 |
说明 |
| any(some) |
某些 |
| all |
全部 |
| in |
在…中 |
| exists |
存在 |
| 比较运算符 |
说明 |
| < |
小于 |
| <= |
小于等于 |
| > |
大于 |
| >= |
大于等于 |
| != |
不等于 |
1.带any、some关键字的子查询
any和some关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。
mysql> create table tb1(num1 int not null);
Query OK, 0 rows affected (0.01 sec)
mysql> create table tb2(num2 int not null);
Query OK, 0 rows affected (0.00 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 num1 from tb1 where num1>any(select num2 from tb2);
+------+
| num1 |
+------+
| 13 |
| 27 |
+------+
2 rows in set (0.00 sec)
mysql> select num1 from tb1 where num1>some(select num2 from tb2);
+------+
| num1 |
+------+
| 13 |
| 27 |
+------+
2 rows in set (0.00 sec)
2.带all关键字的子查询
all关键字与any和some不同，使用all时需要同时满足所有内层查询的条件。
mysql> select num1 from tb1 where num1>all(select num2 from tb2);
+------+
| num1 |
+------+
| 27 |
+------+
1 row in set (0.00 sec)
3.带exists关键字的子查询
exists关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么exists的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么exists返回的结果是false，此时外层语句将不进行查询。
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)
4.带in关键字的子查询
内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。
mysql> select c_id from orders where o_num in (select o_num from orderitems where f_id='c0');
+-------+
| c_id |
+-------+
| 10004 |
| 10001 |
+-------+
2 rows in set (0.00 sec)
5.带比较运算符的子查询
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)
六、合并查询结果
利用union关键字，可以给出多条select语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同。各个select语句之间使用union或union all关键字分隔。
union不使用关键字all，执行的时候删除重复的记录，所有返回的行都是唯一的；
union使用关键字all的作用是不删除重复行也不对结果进行自动排序。
mysql> select s_id,f_name,f_price from fruits
-> where f_price<9
-> 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
-> 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)
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)
2.为字段取别名
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中使用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开头后面紧跟至少有一个a |
ba、bay、bare、battle |
| <字符串> |
匹配包含指定的字符串的文本 |
fa’ fan,afa,faad |
|
| <字符集合> |
匹配字符集合中的任何一个字符 |
[xz]'匹配x或者z |
dizzy、zabra、extra |
| <^> |
匹配不在括号中的任何字符 |
[^abc]'匹配任何不包括a、b或c的字符串 |
desk、fox、f8ke |
| 字符串{n,} |
匹配前面的字符串至少n次 |
b{2}匹配2个或者更多的b |
bbb,bbbbb |
| 字符串{n,m} |
匹配前面的字符串至少n次，至多m次。如果n为0，则省略 |
b{2,4}匹配最少2个，最多4个b |
bb,bbb,bbbb |
1.查询以特定字符或字符串开头的记录
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)
2.查询以特定字符或字符串结尾的记录
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)
3.用符合‘.’来代替字符串中的任意一个字符
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)
4.使用“*”和“+”来匹配多个字符
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+';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| t1 | 102 | banana | 10.30 |
+------+------+--------+---------+
1 row in set (0.00 sec)
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> select * from fruits where f_name like 'on';
Empty set (0.00 sec)
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)
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 |
| 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)
8.使用{n,}或者{n,m}来指定字符串连续出现的次数
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)
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)
