Advanced summary of MySQL single table query

This article brings you relevant knowledge about mysql, which mainly introduces the relevant content about single table query. Let’s take a look at it together. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

When we operate on data, query is undoubtedly crucial, query The operation is flexible and changeable. We can design efficient query operations according to development needs and display the data stored in the database to users.

Query is a crucial part of data operations. For example, if you want to find all products with prices within a specified range among all products, you want to put the data in the database on the client When displayed to users, query operations are generally performed.

In actual development, we have to decide how to query based on different needs and consider the efficiency of the query. Before learning the query, you can take a look at the complete syntax of the query:

SELECT
	字段列表FROM
	表名列表WHERE
	条件列表GROUP BY
	分组字段HAVING
	分组后条件ORDER BY
	排序字段LIMIT
	分页限定

Based on For the keywords in the complete syntax of the query, we will learn basic query, conditional query, sorting query, grouping query and paging query respectively.

We use the following case to learn single table query:

-- 删除stu表
drop table if exists stu;
-- 创建stu表
CREATE TABLE stu (
id int, -- 编号
name varchar(10), -- 姓名
age int, -- 年龄
gender varchar(5), -- 性别
math double(5,2), -- 数学成绩
english double(5,2) -- 英语成绩

);
-- 添加数据
INSERT INTO stu(id,name,age,gender,math,english)
VALUES
(1,'小张',23,'男',66,78),
(2,'小李',20,'女',98,87),
(3,'小陈',55,'男',56,77),
(4,'小樊',20,'女',76,65),
(5,'小马',20,'男',86,NULL),
(6,'小赵',57,'男',99,99);

Select SQL execution in Navicat:

1. Basic query

1.1 Basic query syntax

Query multiple fields:

select 字段列表 from 表名;

Query all fields:

select * from 表名;

Removing duplicate records:

select distinct 字段列表 from 表名;

Aliasing operation:

select 字段名 别名 from 表名;

1.2 Basic query exercise

We use the case in the preface to perform basic query Exercise:

Exercise of querying multiple fields:

select name,math from stu;

Exercise of alias operation:

select name,english 英语成绩 from stu;

2. Conditional query

2.1 Conditional query syntax

General syntax:

select 字段列表 from 表名 where 条件列表;

Conditional query generally matches the operator To proceed, the following are several common operators:

Operator	Function Description
>	Greater than less than equal to not equal to
between…and…	Within this range
in(…)	Choose one more
is null / is not null	is null / is not null
and or&&	and
or or||	or

##2.2 Conditional Query Exercise

We use the case in the preface for conditional query exercise:

Query information about students older than 20:

select * from stu where age>20;

Query information about students whose age is equal to 18 years old, or whose age is equal to 20 years old, or whose age is equal to 21 years old:

select * from stu where age in(18,20,21);

Fuzzy query uses the like keyword, and wildcards can be used for placeholders:

_: Represents a single arbitrary character
• %: Represents any number of numeric characters

Query the information of students whose names contain Zhang:

select * from stu where name like '%张%';

3. Sorting query

3.1 Sorting query syntax

select 字段列表 from 表名 order by 排序字段名1 [排序方式]...;

Note: There are two sorting methods: ascending ASC and descending DESC. The default is ascending ASC.

3.2 Sorting Query Exercise

We use the case in the preface for sorting query exercise:

4. Aggregation function

4.1 Aggregation function syntax

What is an aggregate function? When performing query operations, we often need to perform operations on an entire column. For example, if we can calculate the average of an entire column of performance data, we must use an aggregate function. The following are common aggregate functions:

Function nameFunctioncount (Column name)Statistical quantity (generally choose a column that is not null)max(Column name)Maximum valuemin(column name)Minimum valuesum(column name)Sum avg(column name)average

一般语法：

select 聚合函数 from 表名;

注：NULL值不参与聚合函数运算。

4.2 聚合函数练习

我们使用前言中的案例进行聚合函数的练习：

统计该表中一共有几个学生：

select count(id) from stu;

上面我们使用某一字段进行运算，这样做可能面临的问题是某一个值可能是NULL，所以我们一般使用 * 进行运算，因为一行中不可能所有的字段都是NULL。

select count(*) from stu;

查询数学成绩的平均分:

select avg(math) from stu;

5. 分组查询

5.1 分组查询语法

select 字段列表 from 表名 [where 分组前的条件限定] group by 分组字段名 [having 分组后的条件过滤]

注：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义。

5.2 分组查询练习

我们使用前言中的案例进行分组查询练习：

查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组:

select gender, avg(math),count(*) from stu where math > 70 group by gender;

查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2个的:

select gender, avg(math),count(*) from stu where math > 70 group by gender having count(*) > 2;

注：where 和 having 执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。所以，where 不能对聚合函数进行判断，having 可以。

6. 分页查询

6.1 分页查询语法

在大家的印象中，网页在展示大量的数据时，往往不是把数据一下全部展示出来，也是用分页展示的形式，其实就是对数据进行分页查询的操作，即每次只查询一页的数据展示到页面上。

select 字段列表 from 表名 limit 查询起始索引,查询条目数;

在 limit 关键字中，查询起始索引这个参数是从0开始的。

5.2 分页查询练习

我们使用前言中的案例进行分页查询练习：

从0开始查询，查询3条数据：

select * from stu limit 0,3;

起始索引 = (当前页码 - 1) * 每页显示的条数

推荐学习：mysql视频教程

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