MySQL-查询语句（1）

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

​ 下面是通过help帮助查看到的select语法格式

Name: 'SELECT'
Description:
Syntax:
SELECT
    select_expr [, select_expr] ...
      [FROM table_references]
    [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}]

基本查询

​ 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<字段>，该子句告诉MySQL按什么样的顺序显示查询出来的数据，可以进行的排序有：升序（asc）、降序（desc）。

5. [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

单表查询

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

查询所有字段

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)

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)

查询指定字段

语法格式：

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)

查询指定记录

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

​ 语法格式：

select 字段1，字段2....字段n from 表名 where 查询条件;

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)

带in关键字的查询

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

mysql> select s_id,f_name,f_price 
    -> from fruits
    -> where s_id in (101,102)
    -> order by f_name;
+------+------------+---------+
| s_id | f_name     | f_price |
+------+------------+---------+
|  101 | apple      |    5.20 |
|  102 | banana     |   10.30 |
|  101 | blackberry |   10.20 |
|  101 | cherry     |    3.20 |
|  102 | grape      |    5.30 |
|  102 | orange     |   11.20 |
+------+------------+---------+
6 rows in set (0.00 sec)

带between and的范围查询

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

mysql> select f_name,f_price
    -> from fruits
    -> where f_price between 2.00 and 10.20;
+------------+---------+
| f_name     | f_price |
+------------+---------+
| apple      |    5.20 |
| apricot    |    2.20 |
| blackberry |   10.20 |
| berry      |    7.60 |
| xxxx       |    3.60 |
| melon      |    8.20 |
| cherry     |    3.20 |
| lemon      |    6.40 |
| xbabay     |    2.60 |
| coconut    |    9.20 |
| grape      |    5.30 |
| xbababa    |    3.60 |
+------------+---------+
12 rows in set (0.00 sec)

带like的字符匹配查询

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

1. 百分号（%）通配符，匹配任意长度的字符，甚至包括零字符

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)

2. 下划线（__)通配符，一次只能匹配任意一个字符

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)

查询空值

​ 数据表创建的时候，设计者可以指定某列中是否可以包含空值（NULL)。空值不同于0，也不同于空字符串。空值一般表示数据未知、不适用或将在以后添加数据。

​ 创建customers表并插入数据

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

mysql> insert into customers(c_id,c_name,c_address,c_city,c_zip,c_contact,c_email)
    ->   values(10001,'RedHook','200Street','Tianjin','300000','LiMing','LMing@163.com'),
    ->   (10002,'Stars','333 FromageLane','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

​ 在select语句中使用IS NULL子句，可以查询某字段内容为空的记录。

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)

带and的多条件查询

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

mysql> select f_id,f_price,f_name
    -> from fruits
    -> where s_id = '101' and f_price >=5;
+------+---------+------------+
| f_id | f_price | f_name     |
+------+---------+------------+
| a1   |    5.20 | apple      |
| b1   |   10.20 | blackberry |
+------+---------+------------+
2 rows in set (0.00 sec)

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

查询结果不重复

语法格式：select distinct 列名 from 表名;

​ 现在查看fruits表s_id列可以看到很多重复的行

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)

​ 添加关键字再次查看s_id列

mysql> select distinct s_id from fruits;
+------+
| s_id |
+------+
| 101 |
| 103 |
| 104 |
| 107 |
| 102 |
| 105 |
| 106 |
+------+
7 rows in set (0.00 sec)

对查询结果排序

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.01 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,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)

​ 注：与DESC相反ASC是升序

分组查询

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

​ 基本语法形式为：group by 字段 1、创建分组Group by 关键字通常和集合函数一起使用。

​ 1、根据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.01 sec)

​ 根据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)

2、使用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)

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

mysql> select s_id,count(*) as total
    -> from fruits
    -> group by s_id with rollup;
+------+-------+
| s_id | total |
+------+-------+
|  101 |     3 |
|  102 |     3 |
|  103 |     2 |
|  104 |     2 |
|  105 |     3 |
|  106 |     1 |
|  107 |     2 |
| NULL |    16 |
+------+-------+
8 rows in set (0.00 sec)

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

mysql> select * from fruits group by f_id,f_name;
+------+------+------------+---------+
| 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)

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.03 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) as ordertotal
 -> from orderitems
 -> group by o_num
 -> having sum(quantity*item_price) >= 100;
+-------+------------+
| o_num | ordertotal |
+-------+------------+
| 30001 |     268.80 |
| 30003 |    1000.00 |
| 30004 |     125.00 |
| 30005 |     236.85 |
+-------+------------+
4 rows in set (0.00 sec)

使用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)

​ 查看从第四行开始，到第三行结束（前面的4为第几行，后面的3为数量）

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)