This article uses an example to illustrate the complete usage of the select statement in MySQL learning notes. Share with you for your reference, the details are as follows:

In this article:

• Full syntax
• Deduplication options
• Field aliases
• Data Source
• where
• group by
• having
• order by
• limit

Release date: 2018-04-11

Full syntax:

First give the complete grammar, and then explain them one by one.

Basic syntax: select field list from data source;

Complete syntax: select deduplication option field list [as field alias] from data source [where clause] [group by clause] [having clause] [order by clause] [limit clause];

Deduplication options:

• The deduplication option is whether to deduplicate identical records (all field data are the same) in the results:
  • all: no duplicate removal
  • distinct: remove duplicates
• Syntax: select deduplication option field list from table name;

Example:

Before deduplication: , after deduplication

create table student(name varchar(15),gender varchar(15));
insert into student(name,gender) values("lilei","male");
insert into student(name,gender) values("lilei","male");
select * from student;
select distinct * from student;

Replenish:

• Note: Deduplication is performed on the records retrieved from the query, not on the records stored in the table. If only certain fields are queried, then deduplication will target these fields.

Field aliases:

• A field alias is a name given to a field in the query results.
• The field alias will only take effect in the current query results.
• Field aliases are generally used to help understand the meaning of the field (for example, if we define the name as name, we hope that the result returned to the user will be displayed as name), abbreviated field name
• Syntax: select field as field alias from table name;

Example:

Before use: , after use

create table student(name varchar(15),gender varchar(15));
insert into student(name,gender) values("lilei","male");
insert into student(name,gender) values("lilei","male");
select * from student;
select name as "姓名",gender as "性别" from student;

Data Source:

• In fact, the source of the query does not need to be a "table name", it only needs to be a two-dimensional table. Then the data source can be a select result.
• The data source can be a single-table data source, a multi-table data source, or a query statement.
• • Single table: select field list from table name;
  • Multiple tables: select field list from table name 1, table name 2, ...; [When querying multiple tables, x records in each table are combined with y records in another table to form a result, and the number of records in the result is x*y] [It can be called Cartesian product]
  • • 
  • Query statement: select field list fromr (select statement) as table alias; [This is to use a query result as a two-dimensional table as the target of a query. The query result needs to be defined as a table alias to be used as a data source]

  • -- Example: select name from (select * from student) as d;

where clause:

• The where clause is used to filter the results that meet the conditions.

Where several syntax:

• Based on value:
  • = : where field=value; find the records where the corresponding field is equal to the corresponding value. (Similarly, < is less than the corresponding value, <= is less than or equal to the corresponding value, > is greater than the corresponding value, >= is greater than or equal to the corresponding value, and != is not equal to), for example: where name = 'lilei'
    
  • like:where field like value; functions similarly to =, but can use fuzzy matching to find results. For example: where name like 'li%'
    
• Based on the range of values:
  • in: where field in range; find records whose values ​​of the corresponding field are in the specified range. For example: where age in (18,19,20)
    
  • not in: where field not in range; find records where the value of the corresponding field is not in the specified range. For example: where age not in (18,19,20)
    
  • between x and y: where field between x and y; finds records whose values ​​of the corresponding fields are in the closed interval [x,y]. For example: where age between 18 and 20.
    
• Conditional compound:
  • or: where condition 1 or condition 2…; find the records that meet condition 1 or condition 2.
  • and: where condition 1 and condition 2…; Find the records that meet condition 1 and condition 2.
  • not: where not condition 1; find all records that do not meet the condition.
  • The function of && is the same as and; the function of || is similar to or, and the function of ! is similar to not.

Replenish:

• where is the filtering when getting data from disk. So some things that are only in memory cannot be used there. (Field aliases are not originally "data on disk" (they are defined when running in memory), so where cannot be used, and generally rely on having to filter).

select name as n ,gender from student where name ="lilei";
-- select name as n ,gender from student where n ="lilei"; -- error select name as n ,gender from student having n ="lilei";

group by clause:

• group by can group the query results according to the fields.
• Syntax: select field list from table name group by field;
  • [There can be multiple fields, which is actually secondary grouping]

-- Example: select name,gender,count(name) as "group member" from student as d group by name;
select name,gender,count(name) as "group member" from student as d group by name,gender;

Replenish:

• In fact, the main function of group by is statistics (there are many scenarios, such as counting someone's total score, the number of female students, etc.), so it is usually used with some statistical functions:
  • count(x): counts the number of records in each group. When x is *, it represents the number of records. When x is a field name, it represents the number of field data (excluding NULL)
  • max(x): Statistical maximum value, x is the field name
  • min(x): minimum statistical value, x is the field name
  • avg(x): statistical average, x is the field name
  • sum(x): statistical sum, x is the field name
• The group by field can be followed by asc or desc to indicate whether the data should be sorted according to the field after grouping.

having clause:

• The function of having is similar to where, but the conditional judgment of having occurs when the data is in memory, so data that only occurs in memory can be used, such as "grouping", "field alias", etc.
• Syntax: select field list from table name having condition; [operators and the like can refer to where, and only some filtering conditions in "memory" are added]

-- Example: select name as n, gender from student having n = "lilei";
select name,gender,count(*) as "组員" from student as d group by name,gender having count(*) >2 ;-- Only groups with >2 records are displayed here

order by clause:

• order by can sort the query results according to a certain field
• Syntax: select field list from table name order by field [asc|desc];
  • There can be multiple fields, from left to right, the latter sorting is based on the former, (for example: sort by name first, then sort by gender, the latter gender sorting is for the data with the same name as the previous name sorting)
  • asc means the sorting is ascending
  • desc represents decreasing
  • You can also specify the sorting method for a certain field, such as increasing the first field and decreasing the second field. Just add asc or desc after each field (although the default is incrementing, it is still clearer to add it).

-- Example: select * from student order by name;
select * from student order by name,gender;
select * from student order by name asc,gender desc;

Limit Clause:

• Limit is used to limit the number of results. When used with where\having, etc., you can limit the matching results. Limit can be used whenever quantity is involved (this is just to emphasize the role of limit, don't over-understand it)
• Syntax: select field list from table name limit [offset,] count;
  • count is the number
  • Offset is the starting position. Offset starts from 0 and can be said to be the index number of each record.

-- Example select * from student limit 1;
select * from student limit 3,1;
select * from student where name ="lilei" limit 1;
select * from student where name = "lilei" limit 3,1;

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.