从此烟雨落金陵,一人撑伞两人行

MySQL高级查询

在实际的业务需求中,简单查询往往解决问题的能力有限。所以需要功能更为强大的查询语句,而MySQL对业务需求也提供更高级的查询方式,共分为:连接查询、联合查询、子查询三种,基本可以囊括所有的业务需求。

1、数据库环境准备

在进行实现之前,需要我们拥有一个关系型数据库的模型。建立三张表user、book、orders分别表示用户表,书籍表,以及用户与书籍之间的关系表。

user表的建表语句:

CREATE TABLE user (
  id int(10) UNSIGNED  NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name varchar(20) NOT NULL,
  adress varchar(20) NOT NULL,
  balance decimal(10,2) DEFAULT NULL,
  phone bigint DEFAULT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

为该表添加四条记录:

+----+--------+--------------+---------+-------------+
| id | name   | adress       | balance | phone       |
+----+--------+--------------+---------+-------------+
|  1 | 唐僧   | 湖北省黄冈市 |  346.89 | 13297964701 |
|  2 | 猪八戒 | 湖北省武汉市 |  345.67 | 13728429899 |
|  3 | 孙悟空 | 湖北省黄冈市 |  345.67 | 13737829449 |
|  4 | 沙悟净 | 湖北省随州市 |  345.67 | 18739829449 |
+----+--------+--------------+---------+-------------+

book表的键表语句:

 CREATE TABLE book(
  id int NOT NULL AUTO_INCREMENT,
  name varchar(20) NOT NULL,
  price decimal(10,2) NOT NULL,
  author varchar(20) NOT NULL,
  words int DEFAULT NULL,
  PRIMARY KEY (id),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

为book表中插入四条记录:

+----+----------+--------+--------+--------+
| id | name     | price  | author | words  |
+----+----------+--------+--------+--------+
|  1 | 三国演义 |  89.50 | 罗贯中 | 800000 |
|  2 | 水浒传   | 103.50 | 施耐庵 | 960000 |
|  3 | 西游记   | 163.50 | 吴承恩 | 860000 |
|  4 | 红楼梦   |  73.50 | 曹雪芹 | 730000 |
+----+----------+--------+--------+--------+

orders表建表语句:

 CREATE TABLE orders (
  id int unsigned NOT NULL AUTO_INCREMENT,
  user_id int NOT NULL,
  book_id int NOT NULL,
  PRIMARY KEY (id),
  KEY user_id (user_id),
  KEY book_id (book_id),
  CONSTRAINT `FK_BOOK_ID` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`),
  CONSTRAINT `FK_USER_ID` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

向orders表中插入数据:

+----+---------+---------+
| id | user_id | book_id |
+----+---------+---------+
|  1 |       1 |       3 |
|  2 |       1 |       2 |
|  3 |       2 |       1 |
|  4 |       3 |       2 |
|  5 |       4 |       1 |
|  6 |       4 |       4 |
+----+---------+---------+

2、连接查询

连接查询是将多张表按照某个指定的条件进行记录的连接,通常用在用户需要查看的数据来自多张表

连接查询的使用方式:

/*连接查询使用join关键字,on关键字用来表示表连接的顺序 */
select * from user 
join orders
on orders.user_id=user.id

连接查询的分类

上述SQL语句的就是基础的连接查询的使用方法,除此之外MySQL还提供更高级的连接查询的用法,分为:内连接、外连接、自然连接和交叉连接

  • 内连接

    从左表中取出每一条记录去右表中与所有记录进行匹配,匹配必须是某个条件在左表中与右表中相同最终才会保留结果,否则不会保留。

    基本语法:

    --将orders表中数据与user表中的数据根据user_id=id连接在一起
    select * from user 
    inner join orders
    on orders.user_id=user.id;
    

    查询的结果为:

    +----+--------+--------------+---------+-------------+----+---------+---------+
    | id | name   | adress       | balance | phone       | id | user_id | book_id |
    +----+--------+--------------+---------+-------------+----+---------+---------+
    |  1 | 唐僧   | 湖北省黄冈市 |  346.89 | 13297994701 |  1 |       1 |       3 |
    |  1 | 唐僧   | 湖北省黄冈市 |  346.89 | 13297994701 |  2 |       1 |       2 |
    |  2 | 猪八戒 | 湖北省武汉市 |  345.67 | 13728429899 |  3 |       2 |       1 |
    |  3 | 孙悟空 | 湖北省黄冈市 |  345.67 | 13737829449 |  4 |       3 |       2 |
    |  4 | 沙悟净 | 湖北省随州市 |  345.67 | 18739829449 |  5 |       4 |       1 |
    |  4 | 沙悟净 | 湖北省随州市 |  345.67 | 18739829449 |  6 |       4 |       4 |
    +----+--------+--------------+---------+-------------+----+---------+---------+

    内连接可以没有连接条件,即关键字ON之后的内容,这个时候系统会保留所有的结果,称之为笛卡尔积。

  • 外连接

    以某张表为主,取出里面所有的记录,然后与另外一种表进行连接,不管能不能匹配上条件,最终都会保留;能匹配,正确保留;不能匹配,其他表的字段都置为空NULL。

    基本用法:

    --外连接分为两种,一种是以左表为主表,用关键字left join表示
    select * from user
    left join orders
    on orders.user_id=user.id;
    --另一种以右表为主表,用关键字right join表示select
    select * from user
    right join orders
    on user.id=orders.user_id

    查询结果为:

    +----+--------+--------------+---------+-------------+------+---------+---------+
    | id | name   | adress       | balance | phone       | id   | user_id | book_id |
    +----+--------+--------------+---------+-------------+------+---------+---------+
    |  1 | 唐僧   | 湖北省黄冈市 |  346.89 | 13297994701 |    1 |       1 |       3 |
    |  1 | 唐僧   | 湖北省黄冈市 |  346.89 | 13297994701 |    2 |       1 |       2 |
    |  2 | 猪八戒 | 湖北省武汉市 |  345.67 | 13728429899 |    3 |       2 |       1 |
    |  3 | 孙悟空 | 湖北省黄冈市 |  345.67 | 13737829449 |    4 |       3 |       2 |
    |  4 | 沙悟净 | 湖北省随州市 |  345.67 | 18739829449 |    5 |       4 |       1 |
    |  4 | 沙悟净 | 湖北省随州市 |  345.67 | 18739829449 |    6 |       4 |       4 |
    +----+--------+--------------+---------+-------------+------+---------+---------+

    内连接与外连接的区别:

    在连接条件不匹配的情况下,内连接将返回空表,而外连接则不会,则会将主表的数据填充,副表的字段全部置为空。

    1、演示内连接条件匹配失败

    select * from user 
    inner join orders 
    on orders.user_id=user.id and user.id=5;

    查询结果为:

    Empty set (0.00 sec)

    2、演示外连接条件匹配失败

    左连接匹配失败时,左表作为主表数据将会被填充,右表作为副表所有字段将置为空NULL。

    select * from user
    left join orders
    on orders.user_id=user.id and user.id=5;

    查询结果为:

    +----+--------+--------------+---------+-------------+------+---------+---------+
    | id | name   | adress       | balance | phone       | id   | user_id | book_id |
    +----+--------+--------------+---------+-------------+------+---------+---------+
    |  1 | 唐僧   | 湖北省黄冈市 |  346.89 | 13297994701 | NULL |    NULL |    NULL |
    |  2 | 猪八戒 | 湖北省武汉市 |  345.67 | 13728429899 | NULL |    NULL |    NULL |
    |  3 | 孙悟空 | 湖北省黄冈市 |  345.67 | 13737829449 | NULL |    NULL |    NULL |
    |  4 | 沙悟净 | 湖北省随州市 |  345.67 | 18739829449 | NULL |    NULL |    NULL |
    +----+--------+--------------+---------+-------------+------+---------+---------+

    右连接匹配失败时,右表作为主表数据将会被填充,左表作为副表所有字段将置为空NULL。

    select * from user
    right join orders
    on orders.user_id=user.id and user.id=5;

    查询结果为:

    +------+------+--------+---------+-------+----+---------+---------+
    | id   | name | adress | balance | phone | id | user_id | book_id |
    +------+------+--------+---------+-------+----+---------+---------+
    | NULL | NULL | NULL   |    NULL |  NULL |  1 |       1 |       3 |
    | NULL | NULL | NULL   |    NULL |  NULL |  2 |       1 |       2 |
    | NULL | NULL | NULL   |    NULL |  NULL |  3 |       2 |       1 |
    | NULL | NULL | NULL   |    NULL |  NULL |  4 |       3 |       2 |
    | NULL | NULL | NULL   |    NULL |  NULL |  5 |       4 |       1 |
    | NULL | NULL | NULL   |    NULL |  NULL |  6 |       4 |       4 |
    +------+------+--------+---------+-------+----+---------+---------+
  • 交叉连接

    从一张表中循环取出每一条记录,每条记录都去另外一张表进行匹配,没有条件匹配。

    基本用法:

    --corss join关键字表示交叉连接,没有连接条件
    select  * from user corss join orders

    查询结果为:

    +----+--------+--------------+---------+-------------+----+----------+--------+--------+--------+
    | id | name   | adress       | balance | phone       | id | name     | price  | author | words  |
    +----+--------+--------------+---------+-------------+----+----------+--------+--------+--------+
    |  1 | 唐僧   | 湖北省黄冈市 |  346.89 | 13297994701 |  1 | 三国演义 |  89.50 | 罗贯中 | 800000 |
    |  2 | 猪八戒 | 湖北省武汉市 |  345.67 | 13728429899 |  1 | 三国演义 |  89.50 | 罗贯中 | 800000 |
    |  3 | 孙悟空 | 湖北省黄冈市 |  345.67 | 13737829449 |  1 | 三国演义 |  89.50 | 罗贯中 | 800000 |
    |  4 | 沙悟净 | 湖北省随州市 |  345.67 | 18739829449 |  1 | 三国演义 |  89.50 | 罗贯中 | 800000 |
    |  1 | 唐僧   | 湖北省黄冈市 |  346.89 | 13297994701 |  2 | 水浒传   | 103.50 | 施耐庵 | 960000 |
    |  2 | 猪八戒 | 湖北省武汉市 |  345.67 | 13728429899 |  2 | 水浒传   | 103.50 | 施耐庵 | 960000 |
    |  3 | 孙悟空 | 湖北省黄冈市 |  345.67 | 13737829449 |  2 | 水浒传   | 103.50 | 施耐庵 | 960000 |
    |  4 | 沙悟净 | 湖北省随州市 |  345.67 | 18739829449 |  2 | 水浒传   | 103.50 | 施耐庵 | 960000 |
    |  1 | 唐僧   | 湖北省黄冈市 |  346.89 | 13297994701 |  3 | 西游记   | 163.50 | 吴承恩 | 860000 |
    |  2 | 猪八戒 | 湖北省武汉市 |  345.67 | 13728429899 |  3 | 西游记   | 163.50 | 吴承恩 | 860000 |
    |  3 | 孙悟空 | 湖北省黄冈市 |  345.67 | 13737829449 |  3 | 西游记   | 163.50 | 吴承恩 | 860000 |
    |  4 | 沙悟净 | 湖北省随州市 |  345.67 | 18739829449 |  3 | 西游记   | 163.50 | 吴承恩 | 860000 |
    |  1 | 唐僧   | 湖北省黄冈市 |  346.89 | 13297994701 |  4 | 红楼梦   |  73.50 | 曹雪芹 | 730000 |
    |  2 | 猪八戒 | 湖北省武汉市 |  345.67 | 13728429899 |  4 | 红楼梦   |  73.50 | 曹雪芹 | 730000 |
    |  3 | 孙悟空 | 湖北省黄冈市 |  345.67 | 13737829449 |  4 | 红楼梦   |  73.50 | 曹雪芹 | 730000 |
    |  4 | 沙悟净 | 湖北省随州市 |  345.67 | 18739829449 |  4 | 红楼梦   |  73.50 | 曹雪芹 | 730000 |
    +----+--------+--------------+---------+-------------+----+----------+--------+--------+--------+

    book表中有4条数据,user表中也有4条数据,所以查询的结果就是16条数据。

  • 自然连接

    通过MySQL自己的判断完成连接过程,不需要指定连接条件。MySQL会使用表内的相同字段作为连接条件。能匹配上就正常匹配,匹配不上的就将其字段填充为NULL

    基本用法:

    --自然连接分为内连接和外连接,外连接分为左外连接和右外连接
    -- 自然连接内连接使用natural join表示
    select * from users natural join book;

    查询结果为:

    +----+--------+--------------+---------+-------------+---------+---------+
    | id | name   | adress       | balance | phone       | user_id | book_id |
    +----+--------+--------------+---------+-------------+---------+---------+
    |  1 | 唐僧   | 湖北省黄冈市 |  346.89 | 13297994701 |       1 |       3 |
    |  2 | 猪八戒 | 湖北省武汉市 |  345.67 | 13728429899 |       1 |       2 |
    |  3 | 孙悟空 | 湖北省黄冈市 |  345.67 | 13737829449 |       2 |       1 |
    |  4 | 沙悟净 | 湖北省随州市 |  345.67 | 18739829449 |       3 |       2 |
    +----+--------+--------------+---------+-------------+---------+---------+

    因为在user表与orders表中只有id字段名称是相同,所以自然连接会以id字段为条件进行匹配。

    --自然连接左外连接,使用natural left join关键字表示
    select * from user natural left join orders;

    查询结果:

    +----+--------+--------------+---------+-------------+---------+---------+
    | id | name   | adress       | balance | phone       | user_id | book_id |
    +----+--------+--------------+---------+-------------+---------+---------+
    |  1 | 唐僧   | 湖北省黄冈市 |  346.89 | 13297994701 |       1 |       3 |
    |  2 | 猪八戒 | 湖北省武汉市 |  345.67 | 13728429899 |       1 |       2 |
    |  3 | 孙悟空 | 湖北省黄冈市 |  345.67 | 13737829449 |       2 |       1 |
    |  4 | 沙悟净 | 湖北省随州市 |  345.67 | 18739829449 |       3 |       2 |
    +----+--------+--------------+---------+-------------+---------+---------+
    --自然连接右外连接,使用natural right join关键字表示
    select * from user natural right join orders

    查询结果:

    +----+---------+---------+--------+--------------+---------+-------------+
    | id | user_id | book_id | name   | adress       | balance | phone       |
    +----+---------+---------+--------+--------------+---------+-------------+
    |  1 |       1 |       3 | 唐僧   | 湖北省黄冈市 |  346.89 | 13297994701 |
    |  2 |       1 |       2 | 猪八戒 | 湖北省武汉市 |  345.67 | 13728429899 |
    |  3 |       2 |       1 | 孙悟空 | 湖北省黄冈市 |  345.67 | 13737829449 |
    |  4 |       3 |       2 | 沙悟净 | 湖北省随州市 |  345.67 | 18739829449 |
    |  5 |       4 |       1 | NULL   | NULL         |    NULL |        NULL |
    |  6 |       4 |       4 | NULL   | NULL         |    NULL |        NULL |
    +----+---------+---------+--------+--------------+---------+-------------+

总结:一般内连接和外连接用的比较多,而自然连接和交叉连接用的比较少。

3、联合查询

联合查询是将多次查询的结果进行拼接,并不会增加字段的数量。例如,在某一个张表中记录了男生和女生的信息,然后第一次查询需要男生的身高按照从高到低排列,第二次查询需要女生身高按高到底排列。

基本用法:

--联合查询使用关键字UNION对其进行表示
--实际上就是第一查询id为偶数的book,第二次查询id为奇数的book,将两次查询的结果拼接起来
select * from book where id%2=0 
union 
select * from book where id%2=1;

查询结果为:

+----+----------+--------+--------+--------+
| id | name     | price  | author | words  |
+----+----------+--------+--------+--------+
|  2 | 水浒传   | 103.50 | 施耐庵 | 960000 |
|  4 | 红楼梦   |  73.50 | 曹雪芹 | 730000 |
|  1 | 三国演义 |  89.50 | 罗贯中 | 800000 |
|  3 | 西游记   | 163.50 | 吴承恩 | 860000 |
+----+----------+--------+--------+--------+

关于UNION中有两种用法:一种如上述sql一样的用法,直接使用UNION关键字;另外一种用法是使用UNION ALL关键字;这两种用法的区别在于UNION关键字会压缩多个结果集中重复的内容,而UNION ALL会将所有的记录都显示出来。

演示UNION与UNION ALL的区别:

  1. 使用UNION

    select * from book
    union 
    select * from book

    查询结果为:

    +----+----------+--------+--------+--------+
    | id | name     | price  | author | words  |
    +----+----------+--------+--------+--------+
    |  1 | 三国演义 |  89.50 | 罗贯中 | 800000 |
    |  2 | 水浒传   | 103.50 | 施耐庵 | 960000 |
    |  3 | 西游记   | 163.50 | 吴承恩 | 860000 |
    |  4 | 红楼梦   |  73.50 | 曹雪芹 | 730000 |
    +----+----------+--------+--------+--------+

    因为第一次查询与第二次查询的结果是重复的,所以重复的记录只会显示一次。

  2. 使用UNION ALL

    select * from book
    union all
    select * from book

    查询结果为:

    +----+----------+--------+--------+--------+
    | id | name     | price  | author | words  |
    +----+----------+--------+--------+--------+
    |  1 | 三国演义 |  89.50 | 罗贯中 | 800000 |
    |  2 | 水浒传   | 103.50 | 施耐庵 | 960000 |
    |  3 | 西游记   | 163.50 | 吴承恩 | 860000 |
    |  4 | 红楼梦   |  73.50 | 曹雪芹 | 730000 |
    |  1 | 三国演义 |  89.50 | 罗贯中 | 800000 |
    |  2 | 水浒传   | 103.50 | 施耐庵 | 960000 |
    |  3 | 西游记   | 163.50 | 吴承恩 | 860000 |
    |  4 | 红楼梦   |  73.50 | 曹雪芹 | 730000 |
    +----+----------+--------+--------+--------+

联合查询最常用的方式就是与Order By关键字联合使用,在于Order By关键字联合使用的时候需要注意的是:每一次查询需要用括号括起来;其次必须加上limit关键字,否则order by将不会生效

--查询id为奇数的book,按照price进行降序排列
--查询id为偶数的book,按照id进行升序排列
(select * from book where id%2=0 order by price  desc limit 999)
union 
(select * from book where id%2=1 order by id asc limit 999);

查询结果为:

+----+----------+--------+--------+--------+
| id | name     | price  | author | words  |
+----+----------+--------+--------+--------+
|  2 | 水浒传   | 103.50 | 施耐庵 | 960000 |
|  4 | 红楼梦   |  73.50 | 曹雪芹 | 730000 |
|  1 | 三国演义 |  89.50 | 罗贯中 | 800000 |
|  3 | 西游记   | 163.50 | 吴承恩 | 860000 |
+----+----------+--------+--------+--------+

从查询结果可以看到,前面两条是第一次查询的结果,其id为偶数,按照price字段进行降序排列;后面两条是第二次查询的结果,其id为奇数,按照id的升序排列(默认就是按照id属性升序排列)。

4、子查询

子查询是在某个查询的结果之上进行的,即一条select语句中包含另一select语句。

子查询有两种分类的方式,一种是按照位置进行分类;另外一种是按照结果进行分类。

  • 按照位置进行分类
    1. From子查询:子查询跟在FROM之后
    2. Where子查询:子查询跟在Where之后
    3. Exists字查询:子查询出现在Exists之后
  • 按照结果进行分类
    1. 标量子查询:子查询得到的结果是一行一列
    2. 列子查询:子查询得到的结果是一列多行
    3. 行子查询:子查询得到的记过是一行多列
    4. 表子查询:子查询得到的结果是多行多列

标量子查询

--标量子查询,用()表示子查询
--查询id为2的用户所订购的书籍,先在orders表中查询user_id为2所关联的book_id,然后再去按照子查询出来book_id去book中查询具体的book
select * from book where book.id=(select book_id from orders where orders.user_id=2);

查询结果为:

--其中子查询的结果为1
+----+----------+-------+--------+--------+
| id | name     | price | author | words  |
+----+----------+-------+--------+--------+
|  1 | 三国演义 | 89.50 | 罗贯中 | 800000 |
+----+----------+-------+--------+--------+

列子查询

--列子查询
--查询id为1的用户所订购的书籍,子查询的结果为book_id=2、3
select * from book where book.id in (select book_id from orders where orders.user_id=1);

查询结果为:

+----+--------+--------+--------+--------+
| id | name   | price  | author | words  |
+----+--------+--------+--------+--------+
|  2 | 水浒传 | 103.50 | 施耐庵 | 960000 |
|  3 | 西游记 | 163.50 | 吴承恩 | 860000 |
+----+--------+--------+--------+--------+

行子查询

--行子查询,查询id为2的用户的姓名以及它所订购的书籍
select book.*,user.name as username from book,user where (user.id,book.id)=(select user_id,book_id  from orders where user_id=2);

查询结果为:

+----+----------+-------+--------+--------+----------+
| id | name     | price | author | words  | username |
+----+----------+-------+--------+--------+----------+
|  1 | 三国演义 | 89.50 | 罗贯中 | 800000 | 猪八戒   |
+----+----------+-------+--------+--------+----------+

表子查询

--表子查询,将子查询得出的结果作为一张临时的表
select * from book,(select * from orders where orders.book_id=1) s where book.id=s.book_id;

查询结果为:

+----+----------+-------+--------+--------+----+---------+---------+
| id | name     | price | author | words  | id | user_id | book_id |
+----+----------+-------+--------+--------+----+---------+---------+
|  1 | 三国演义 | 89.50 | 罗贯中 | 800000 |  3 |       2 |       1 |
|  1 | 三国演义 | 89.50 | 罗贯中 | 800000 |  5 |       4 |       1 |
+----+----------+-------+--------+--------+----+---------+---------+

5、模糊查询

模糊查询在MySQL中的用法也非常常见的,模糊查询其实就是使用占位符来实现MySQL的模糊查询。

  • 关于模糊查询SQL提供了四种匹配模式:%_[][^]

%

  • 表示任意0个或多个字符。可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示;
SELECT * FROM [user] WHERE u_name LIKE '%三%';/*查询user表中的u_name为“张三”,“张猫三”,“唐三藏”等包含“三”的记录*/
SELECT * FROM [user] WHERE u_name LIKE '%三%' AND u_name LIKE '%猫%';/*查询user表中u_name中包含“三”和“猫”的所有的记录*/
SELECT * FROM [user] WHERE u_name LIKE '%三%猫'; /*可以搜出“三脚猫”,但是搜不出“张三猫”*/

_

  • 表示任意单个字符。匹配单个任意字符,它常用来限制表达式的字符长度语句:
SELECT * FROM [user] WHERE u_name LIKE "_三_";/*查询名字是三个字的,且中间是三的记录,例如“唐三藏”*/
SELECT * FROM [user] WHERE u_name LIKE "三__";/*查询名字是三个字的,且第一个字是三的记录,例如“三脚猫”*/

[]

  • 表示括号内所列字符中的一个(类似正则表达式)。指定一个字符、字符串或范围,要求所匹配对象为它们中的任一个。
SELECT * FROM [user] WHERE u_name LIKE '[张李王]三';/*查询出“张三”,“李三”,“王三”*/
SELECT * FROM [user] WHERE u_name LIKE '老[1-9]';/*查询出“老1”,“老2”,“老3”,......,“老9”*/

[^]

  • 表示不再括号所列之内的单个字符。其取值和[]相同,但它要求所匹配对象为指定字符外任一个字符。
SELECT * FROM [user] WHERE u_name LIKE '[^张李王]';/*查询出不姓“张”,“李”,“王”的叫“玩”的人,例如“赵三”*/
SELECT * FROM [user] WHERE u_name LIKE '老[^1-4]';/*查询出“老5”到“老9”*/