MySQL query data: single table query

Single table query refers to querying the required data from one table of data.
(1)Query all fields
(2)Query specified fields
(3)Query specified records
(4)Query with in keyword
(5)Range with between and Query
(6) Character matching query with like
(7) Query for null value
(8) Multi-condition query with and
(9) Multi-condition query with or
(10) Query results are not repeated
(11) Sort query results
(12) Group query
(13) Use limit to limit the number of query results

---

(Free learning recommendation: mysql video tutorial)

(1) Query all fields

1. select * from fruits
2.select f_id,s_id,f_name,f_price from fruits;

The results after the execution of the above two statements are to query all Field:

mysql> select * from fruits;+------+------+------------+---------+| f_id | s_id | f_name     | f_price |+------+------+------------+---------+| 12   |  104 | lemon      |    6.40 || 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 || 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 | xbabay     |    3.60 |+------+------+------------+---------+16 rows in set (0.00 sec)

(2) Query the specified field

1. Query a single field:select column name from table name;

[Example] Query All fruit names in the f_name column in the fruits table, the SQL statement is as follows:

mysql> select f_name from fruits;+------------+| f_name     |+------------+| lemon      || apple      || apricot    || blackberry || berry      || xxxx       || orange     || melon      || cherry     || mango      || xbabay     || xxtt       || coconut    || banana     || grape      || xbabay     |+------------+16 rows in set (0.00 sec)

2. Query multiple fields: select field name 1, field name 2,...field name n from table name;
[Example] Obtain the two columns named f_name and f_price from the fruits table. The SQL statement is as follows:

mysql> select f_name,f_price from fruits;+------------+---------+| f_name     | f_price |+------------+---------+| lemon      |    6.40 || apple      |    5.20 || apricot    |    2.20 || blackberry |   10.20 || berry      |    7.60 || xxxx       |    3.60 || orange     |   11.20 || melon      |    8.20 || cherry     |    3.20 || mango      |   15.70 || xbabay     |    2.60 || xxtt       |   11.60 || coconut    |    9.20 || banana     |   10.30 || grape      |    5.30 || xbabay     |    3.60 |+------------+---------+16 rows in set (0.00 sec)

(3) Query the specified record

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

[Example 1] Query The name of the fruit with a price of 10.2 yuan, the SQL statement is as follows:

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)

[Example 2] Find the price of the fruit with the name "apple", the SQL statement is as follows:

mysql> select f_name,f_price    -> from fruits    -> where f_name = 'apple';+--------+---------+| f_name | f_price |+--------+---------+| apple  |    5.20 |+--------+---------+1 row in set (0.00 sec)

[Example 3] Query the price For fruit names less than 10, the SQL statement is as follows:

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

(4) Query with in keyword

• The in operator is used to query records that meet the conditions within the specified range. Use the in operator, enclose all search conditions in parentheses, and separate the search conditions with commas. As long as a value within the condition range is met, it is a match.

[Example 1] For records with s_id 101 and 102, the SQL statement is as follows;

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)

[Example 2] Query all records with s_id neither equal to 101 nor equal to 102, The SQL statement is as follows:

mysql> select s_id,f_name,f_price    -> from fruits    -> where s_id not in (101,102)
    -> order by f_name;+------+---------+---------+| s_id | f_name  | f_price |+------+---------+---------+|  103 | apricot |    2.20 ||  104 | berry   |    7.60 ||  103 | coconut |    9.20 ||  104 | lemon   |    6.40 ||  106 | mango   |   15.70 ||  105 | melon   |    8.20 ||  105 | xbabay  |    2.60 ||  107 | xbabay  |    3.60 ||  105 | xxtt    |   11.60 ||  107 | xxxx    |    3.60 |+------+---------+---------+10 rows in set (0.00 sec)

(5) Range query with between and

• between and is used to query values ​​within a certain range. This operator requires two parameters, That is, the start value and end value of the range.

[Example 1] Query the name and price of fruits whose price ranges from 2.00 yuan to 10.20 yuan. The SQL statement is as follows:

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

[Example 2] Query the price between 2.00 yuan and 10.20 yuan. For fruit names and prices other than 10.20 yuan, the SQL statement is as follows;

mysql> select f_name,f_price    -> from fruits    -> where f_price not between 2.00 and 10.20;+--------+---------+| f_name | f_price |+--------+---------+| orange |   11.20 || mango  |   15.70 || xxtt   |   11.60 || banana |   10.30 |+--------+---------+4 rows in set (0.00 sec)

(6) Character matching query with like

1.% wildcard, matching characters of any length, even including zero character.
[Example 1] Find all fruits starting with the letter "b", the SQL statement is as follows:

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)

[Example 2] In the fruits table, query the records containing the letter "g" in f_name, SQL The statement is as follows:

mysql> select f_id,f_name    -> from fruits    -> where f_name like '%g%';+------+--------+| f_id | f_name |+------+--------+| bs1  | orange || m1   | mango  || t2   | grape  |+------+--------+3 rows in set (0.00 sec)

[Example 3] Query the names of fruits starting with "b" and ending with 'y', the SQL statement is as follows:

mysql> select f_id,f_name    -> from fruits    -> where f_name like 'b%y';+------+------------+| f_id | f_name     |+------+------------+| b1   | blackberry || b2   | berry      |+------+------------+2 rows in set (0.00 sec)

2._Wildcard, only one at a time Can match any character.

[Example] In the fruits table, the query ends with the letter 'y', and there are only 4 letters in front of 'y'. The SQL statement is as follows:

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) Query empty Value

• When the data table is created, you can specify whether a column can contain the null value null. The null value is different from 0, and it is also different from the empty string. A null value generally indicates that the data location is not used or that the data will be added later. Using the is null clause in the select statement, you can query the records with empty content in a certain field.

Create the data table customers below to demonstrate:

create table customers(
	c_id int not null auto_increment,
	c_name char(50) not null,
	c_address char(50) null,
	c_city char(50) null,
	c_zip char(10) null,
	c_contact char(50) null,
	c_email char(255) null,
	primary key (c_id)
	);

Insert the following statement:

mysql> insert into customers(c_id,c_name,c_address,c_city,c_zip,c_contact,c_email)
    -> values
    -> (10001,'redhool','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.06 sec)Records: 4  Duplicates: 0  Warnings: 0

[Example 1] Query the c_id of the record whose c_email is empty in the customers table , c_name and c_email field values, the SQL statement is as follows:

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)

[Example 2] Query the c_id, c_name, and c_email field values ​​of the records where c_email is not empty in the customers table, the SQL statement is as follows:

mysql> select c_id,c_name,c_email    -> from customers    -> where c_email is not null;+-------+---------+-------------------+| c_id  | c_name  | c_email           |+-------+---------+-------------------+| 10001 | redhool | LMing@163.com     || 10002 | Stars   | Jerry@hotmail.com || 10004 | JOTO    | sam@hotmail.com   |+-------+---------+-------------------+3 rows in set (0.00 sec)

(8) Multi-condition query with and

[Example 1] Query the price and name of fruits with s_id=101 and f_price greater than 5 in the fruits table. The SQL statement is as follows:

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)

[Example 2] Query the price and name of fruits with s_id=101 or 102, f_price greater than 5, and f_name='apple' in the fruits table. The SQL statement is as follows:

mysql> select f_id ,f_price , f_name    -> from fruits    -> where s_id in ('101','102') and f_price >=5 and f_name = 'apple';+------+---------+--------+| f_id | f_price | f_name |+------+---------+--------+| a1   |    5.20 | apple  |+------+---------+--------+1 row in set (0.05 sec)

(9) with Multi-condition query for or

[Example 1] Query the f_price and f_name of the fruit supplier with s_id=101 or s_id=102. The SQL statement is as follows:

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)

[Example 2] Query s_id= 101 or the f_price and f_name of the fruit supplier with s_id=102, the SQL statement is as follows:

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

• Note
  or can be used together with and, but pay attention to the priority of the two, because and The priority of is higher than or. The operands on both sides of and are first operated, and then combined with the operands in or.

(10) Query results are not repeated

• Syntax format: select distinct field name from table name;

[Example] Query the value of the s_id field in the fruits table, return the s_id field value and must not be repeated, the SQL statement is as follows;

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

(11) Sort the query results

1.Single column sortorder by

[Example] Query the f_name field value of the fruits table and sort it. The SQL statement is as follows:

mysql> select f_name from fruits order by f_name;+------------+| f_name     |+------------+| apple      || apricot    || banana     || berry      || blackberry || cherry     || coconut    || grape      || lemon      || mango      || melon      || orange     || xbabay     || xbabay     || xxtt       || xxxx       |+------------+16 rows in set (0.00 sec)

2. Multi-column sorting

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

【例】查询fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序，SQL语句如下：

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 || xbabay     |    2.60 || xbabay     |    3.60 || xxtt       |   11.60 || xxxx       |    3.60 |+------------+---------+16 rows in set (0.00 sec)

3.指定排序方向desc

• desc是降序排列，与之对应的是asc升序排列，但asc是默认的，可以不加。

【例1】查询fruits表中的f_name和f_price字段，对结果按f_price降序方式排序，SQL语句如下：

mysql> select f_name,f_price    -> from fruits    -> order by f_price desc;+------------+---------+| f_name     | f_price |+------------+---------+| mango      |   15.70 || xxtt       |   11.60 || orange     |   11.20 || banana     |   10.30 || blackberry |   10.20 || coconut    |    9.20 || melon      |    8.20 || berry      |    7.60 || lemon      |    6.40 || grape      |    5.30 || apple      |    5.20 || xxxx       |    3.60 || xbabay     |    3.60 || cherry     |    3.20 || xbabay     |    2.60 || apricot    |    2.20 |+------------+---------+16 rows in set (0.00 sec)

【例2】查询fruits表，先按f_price降序排序，再按f_name字段升序排序，SQL语句如下：

mysql> select f_price,f_name    -> from fruits    -> order by f_price desc,f_name;+---------+------------+| f_price | f_name     |+---------+------------+|   15.70 | mango      ||   11.60 | xxtt       ||   11.20 | orange     ||   10.30 | banana     ||   10.20 | blackberry ||    9.20 | coconut    ||    8.20 | melon      ||    7.60 | berry      ||    6.40 | lemon      ||    5.30 | grape      ||    5.20 | apple      ||    3.60 | xbabay     ||    3.60 | xxxx       ||    3.20 | cherry     ||    2.60 | xbabay     ||    2.20 | apricot    |+---------+------------+16 rows in set (0.00 sec)

(12)分组查询

• 分组查询是对数据按照某个或多个字段进行分组，MySQL中使用group by 关键字对数据进行分组，基本语法形式为：[group by 字段][having<条件表达式>]
• 字段值为进行分组时所依据的列名称，"having<条件表达式>"指定满足表达式限定条件的结果将被显示。

1.创建分组

【例1】根据s_id对fruits表中的数据进行分组，SQL语句如下;

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

可以看到group by 子句按照s_id排序并对数据分组。

• 如果需要查看每个供应商提供的水果种类名称，可以在group by子句中使用group_concat()函数，将每个分组中各个字段的值显示出来。

【例2】根据s_id对fruits表中的数据进行分组，将每个供应商的水果名称显示出来，SQL语句如下：

mysql> select s_id,group_concat(f_name) as Names    -> from fruits    -> group by s_id;+------+-------------------------+| s_id | Names                   |+------+-------------------------+|  101 | apple,blackberry,cherry ||  102 | orange,banana,grape     ||  103 | apricot,coconut         ||  104 | lemon,berry             ||  105 | melon,xbabay,xxtt       ||  106 | mango                   ||  107 | xxxx,xbabay             |+------+-------------------------+7 rows in set (0.05 sec)

2.使用having过滤分组

• groub by可以和having一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。
• having和where都是用来过滤数据的，having在数据分组之后进行过滤来选择分组，而where在分组之前用来选择记录。where排除的记录不再包括在分组中。

【例】根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息，SQL语句如下：

mysql> select s_id,group_concat(f_name) as Names    -> from fruits    -> group by s_id having count(f_name) >1;+------+-------------------------+| s_id | Names                   |+------+-------------------------+|  101 | apple,blackberry,cherry ||  102 | orange,banana,grape     ||  103 | apricot,coconut         ||  104 | lemon,berry             ||  105 | melon,xbabay,xxtt       ||  107 | xxxx,xbabay             |+------+-------------------------+6 rows in set (0.00 sec)

3.在group by 子句中使用with rollup

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

【例】根据s_id对fruits表中的数据进行分组，并显示记录数量，SQL语句如下：

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

4.多字段分组

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

【例】根据s_id和f_name字段对fruits表中的数据进行分组，SQL语句如下：

mysql> select * from fruits group by s_id,f_name;+------+------+------------+---------+| f_id | s_id | f_name     | f_price |+------+------+------------+---------+| 12   |  104 | lemon      |    6.40 || 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 || 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 | xbabay     |    3.60 |+------+------+------------+---------+16 rows in set (0.00 sec)

5.group by 和order by一起使用

• 某些情况下需要对分组进行排序，order by用来对查询的记录排序，如果和group 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.13 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.06 sec)Records: 11  Duplicates: 0  Warnings: 0

【例】查询订单价格大于100的订单号和总订单价格，SQL语句如下;

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)

可以看到orderTotal列的总订单价格并没有按照一定的顺序显示，接下来使用order by关键字按总订单价格排列显示结果，SQL语句如下：

mysql> select o_num,sum(quantity * item_price) as orderTotal    -> from orderitems    -> group by o_num    -> having sum(quantity * item_price)>=100
    -> order by orderTotal;+-------+------------+| o_num | orderTotal |+-------+------------+| 30004 |     125.00 || 30005 |     236.85 || 30001 |     268.80 || 30003 |    1000.00 |+-------+------------+4 rows in set (0.00 sec)

可以看到，group by 子句按订单号对数据进行分组，sum()函数便可以返回总的订单价格，having子句对分组数据进行过滤，使得只返回总价格大于100的订单，最后使用order by子句排序输出。

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

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

limit [位置偏移量，] 行数

• 第一个"位置偏移量"参数只是MySQL从哪一行开始显示，是一个可选参数，如果不指定"位置偏移量",将会从表中的第一条记录开始(第一条记录的位置偏移量是0,第二条记录的位置偏移量是1,以此类推);第二个参数"行数指示返回的记录条数"。

【例1】显示fruits表查询结果的前4行，SQL语句如下：

mysql> select * from fruits limit 4;+------+------+------------+---------+| f_id | s_id | f_name     | f_price |+------+------+------------+---------+| 12   |  104 | lemon      |    6.40 || a1   |  101 | apple      |    5.20 || a2   |  103 | apricot    |    2.20 || b1   |  101 | blackberry |   10.20 |+------+------+------------+---------+4 rows in set (0.00 sec)

【例2】在fruits表中，使用limit子句，返回从第5个记录开始的、行数长度为3的记录，SQL语句如下：

mysql> select * from fruits limit 4,3;+------+------+--------+---------+| f_id | s_id | f_name | f_price |+------+------+--------+---------+| b2   |  104 | berry  |    7.60 || b5   |  107 | xxxx   |    3.60 || bs1  |  102 | orange |   11.20 |+------+------+--------+---------+3 rows in set (0.00 sec)

也可以使用"limit 4 offset 3"也是获取从第5条记录开始后面的3条记录。

更多相关免费学习推荐：mysql教程(视频)

The above is the detailed content of MySQL query data: single table query. For more information, please follow other related articles on the PHP Chinese website!