Shulou(Shulou.com)06/01 Report--

This article mainly explains the use of the complete select sentence in mysql, the content is clear, interested friends can learn, I believe it will be helpful after reading it.

Full syntax:

First give me the complete grammar, which will be explained one by one later.

Basic syntax: select field list from data source

Complete syntax: select deduplicates field list [as field aliases] from data source [where clause] [group by clause] [having clause] [order by clause] [limit clause]

Deduplicated option:: deduplicated option is whether to deduplicate the exact same record in the result (all field data are the same): all: do not deduplicate distinct: deduplicated syntax: select deduplicated option field list from table name

Example:

Before going to the heavy duty:

After removing the weight

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; add: note: de-duplicates are for queried records, not records stored in the table. If you are only querying certain fields, then you re-target those fields. Field alias: the field alias is another name for the field in the query result. The field alias will only take effect in the current query result. Field aliases are generally used to help you understand the meaning of the field (for example, the name we define is name, and the result we want to return to the user is displayed as a 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 "name", gender as "gender" from student; data source: in fact, the source of the query may not be a "table name", but only a two-dimensional table. Then the data source can be a select result. The data source can be single table data source, multi-table data source, and query statement single table: select field list from table name; multi-table: select field list from table name 1, table name 2, … [when querying multiple tables, the result is composed of x records in each table and y records in another table, and the number of records in the result is x records] [can be called Cartesian product] query statement: select field list fromr (select statement) as table alias [this is to use a query result as the target two-dimensional table of a query, and the query result needs to be defined as a table alias before it can be used as a data source]-- example select name from (select * from student) as where clause: the where clause is used to filter qualified results.

Several grammars for where:

Based on the value: =: where field = value; find the record where the corresponding field is equal to the corresponding value. (similar, = is greater than or equal to the corresponding value,! = is not equal to), for example: where name = 'lilei'

The like:where field like value; the function is similar to =, but you can use fuzzy matching to find the results. For example: where name like 'li%'

Value-based range: in: where field in range; find the record whose value of the corresponding field is in the specified range. For example: where age in (18, 19, 20)

Not in: the not in range of the where field; find the records whose values of the corresponding fields are not in the specified range. For example: where age not in (18, 19, 20)

Between x and y: the where field between x and y; finds the records whose values of the corresponding fields are in the range of the closed interval [xQuery]. For example: where age between 18 and 20.

Condition composition: or: where condition 1 or condition 2... Find 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 criteria. The function of & & is the same as that of and; | | similar to or! The function is similar to not. Add: where is filtered when it fetches data from disk. So some of the things that are only available in memory where cannot be used. (field aliases are not originally "data on disk" (defined when running in memory), so where cannot be used and generally relies 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 query results according to fields. Syntax: select field list from table name group by field; [there can be multiple fields, which is actually a second grouping]-- example select name,gender,count (name) as "team member" from student as d group by name;select name,gender,count (name) as "team member" from student as d group by name,gender; adds: in fact, the role of group by is mainly statistics (there are many usage scenarios, such as counting someone's total score, the number of women in the student. ), so it is generally used in conjunction with some statistical functions: count (x): count the number of records in each group, x represents the number of records when x is *, max (x) represents the number of statistical field data (excluding NULL) max (x): statistical maximum, x is the field name min (x): statistical minimum, 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 also be followed by asc or desc, indicating whether to sort by field after grouping. Having clause: having function is similar to where, except that conditional judgment of having occurs when the data is in memory, so you can use data that occurs in memory, such as "grouping", "field aliases", and so on. Syntax: select field list from table name having condition; [operators can refer to where, but only some filter criteria in "memory" are added]-example select name as n, gender from student having n = "lilei"; select name,gender,count (*) as "team member" from student as d group by name,gender having count (*) > 2 -- only grouped order by clauses with the number of records > 2 are shown here: order by allows query results to be sorted by a certain field syntax: select field list from table name order by field [asc | desc] There can be multiple fields, from left to right, and the following sort is based on the previous, (for example, first sort by name, then sort by gender, and the latter gender sort is for the same data as the previous name sort) asc represents that the sort is incremental, desc represents decreasing, or you can specify the sorting method for a field, such as the first field is incremented and the second is decremented. You just need to add asc or desc after each field (although not incrementing by default, it is clearer). -- 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 in conjunction with where\ having, etc., you can limit the matching results. But you can use limit whenever quantity is involved (just emphasize the role of limit here, don't overunderstand it) syntax: select field list from table name limit [offset,] count Count is the quantity offset is the starting position, offset starts from 0, it can be said to be the index number of each record-example select * from student limit 1 select * from student limit 3 Magi 1 select * from student where name = "lilei" limit 1 position select * from student where name = "lilei" limit 3 Magi 1; after reading the above, whether you have a better understanding of the use of the complete select sentence in mysql, if you want to learn more, welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.