MYSQL--DQL data Query Language standard syntax

DQL

Basic Query

1. field (column) control

1) Query all columns: SELECT * from table name ; "*" means querying all columns

2) Query the specified column: Select column 1, column 2, column N from table name ;

3) complete duplicate records only once

SELECT DISTINCT column name from table name ;

When the multiline record in the query results is identical, only one row is displayed.

This is rarely the case when querying all columns, but this is a big possibility when querying only one column.

4) column Operations

1. Column of quantity type can do subtraction operation
2. String type can do connection operation: 1, concat (' A ', ' B ') 2, Mysql cannot use ' + ' connection string

3. Convert null values

A) ifnull ( column value , conversion value)

b) Sometimes the column has a null value, the result of the operation or null, you want to operate as a different value

4. Alias list

A) after the column is calculated, the column name in the result set is not good-looking, you need to give the column name an alias

b) using as aliases, as can be omitted

2. Condition control

(1) conditional query: The Select statement can also use the where clause to control the record

(2) fuzzy query

① ' _ ' denotes any one character, and an underscore represents one character

② '% ' represents 0 to more characters

③ fuzzy lookups need to use the operator:like

3. Sorting

1. Ascending ASC
2. Descending desc
3. sort by using the keyword ORDER by

(1) syntax:ORDER BY condition one , condition two , condition n asc/desc

Aggregation functions

1. An aggregate function does a vertical operation on a column, and a null item does not participate in the Operation
2. COUNT (*) number of calculations
3. Max () Max value
4. Min () min value
5. SUM () and
6. AVG () average

Group queries

1. A group query is the grouping of records using a column, and then querying group information
2. Group query is a set of information, not with personal information
3. Group information

(1) grouping columns

(2) aggregation function

1. grouping queries using the GROUP by keyword
2. group Condition: The condition after grouping is used having , the condition before grouping uses where

Execution order

Select

From

where

GROUP BY

Having

ORDER BY

Limit clause

Limit is used to limit the starting line of the query result and the total number of rows

start line Calculation Formula ( current Page -1) * number of records per page

MYSQL--DQL data Query Language standard syntax