data retrieval

DQL Realize data retrieval ： adopt SELECT Statement to retrieve data from one or more tables

SELECT The data retrieval format of the statement ：
SELECT  [ALL|DISTINCT]   Field name  
FROM   Table name 
[WHERE  Conditional expression ]
[GROUP BY  Field name | Location  [ASC|DESC] [HAVING  Conditional expression ]]
[ORDER BY  Field name | expression | Location [ASC|DESC]]
[LIMIT  M[,N]];

1）FROM： Specify the query object （ Basic table or view ）

2）WHERE： Specify a conditional expression to filter the data

3）GROUP BY： Group the query results according to the specified field values （ Tuples with equal attribute column values are a group ）

4）ORDER BY： Sort the query results in ascending or descending order according to the specified field values

5）LIMIT： Limit the number of rows displayed in search results

//HAVING： And GROUP BY Statement combination , Filter out groups that meet the specified criteria

// The order of the above statements cannot be changed , Otherwise, an error message will be generated

SELECT Retrieve the execution order ：

1） First from FROM Clause start （ Look it up from that table ）;

2） according to SELECT Clause determines which columns are returned （ Determine column name ）;

3） according to WHERE Clause to filter （ Query qualified data ）;

4） In turn GROUP BY、ORDER BY、LIMIT;

Simple search

Such as ： retrieval products In the table prod_name All the information about

1）SELECT When followed by multiple fields , The retrieved fields represent multiple data （ Comma separated ）;

Such as ： retrieval products In the table prod_id、prod_name and prod_price All the information about

2）SELECT Heel “*”, Representative retrieves all fields in the table ;

Such as ： retrieval products All information of all fields in the table ;

3）SELECT The specified field is followed by “AS Alias “ Represents setting an alias for a field ;

// among “AS” Omission , Directly following an alias also means setting an alias

Such as ： Will retrieve prod_name and prod_price Fields are set to aliases name and price

4）SELECT Heel “DISTINCT” Stands for removing duplicate lines and displaying ;

// The default is “ALL” Show all （ Omission ）

// If specified DISTINCT/ALL, Means that all fields need to remove duplicate lines

// And specify multiple fields , The Cartesian product of multiple fields can be removed without repetition

Such as ： retrieval products In the table vend_id Field , And remove duplicate lines

Such as ： retrieval products In the table vend_id and prod_price Field , And remove duplicate lines

WHERE

WHERE： Specify a conditional expression to filter the data

Conditional expression	Operator
Compare conditional retrieval	=、>、<、>=、<=、<>
Scope search	BETWEEN AND、NOT BETWEEN AND
Collection retrieval	IN、NOT IN
Fuzzy search	LIKE、NOT LIKE
Null search	IS NULL、IS NOT NULL
Multiple conditional retrieval	AND（&&）、OR（||）、XOR

Logical operators （Logical Operator）： coupling WHERE The condition in clause

Logical operators	explain
AND	Logic and computation
OR	Logic or operation

1） If there are multiple conditions and / Or operations , Then each condition needs to use AND coupling

2）AND Priority over OR（ When calling both at the same time, you need “（）” Prioritize ）

//（） The highest priority

Compare conditional retrieval

Such as ： retrieval products In the table prod_price The field values for 2.50 The line of

Such as ： retrieval products In the table prod_name The field values for fuses The line of ;

// When comparing strings, use single quotation marks （ The value does not need to ）

Such as ： retrieval products In the table vend_id The field value is not equal to 1003 The line of ;

Multiple conditional retrieval

Such as ： retrieval products In the table vend_id by 1003,prod_price Less than 10 The line of

Such as ： retrieval products In the table vend_id The field values for 1002 or 1003 The line of

Such as ： retrieval products In the table vend_id by 1002 or 1003 And prod_price Greater than or equal to 10 The line of

Scope search

Such as ： retrieval products In the table prod_price The field value is in 5 To 10 Between the lines ;

//BETWEEN M AND N The representative value is M~N Between （ Include M and N） All match
//NOT BETWEEN AND No boundary value

Null search

NULL： Represents a row in a field that does not contain a value of data type

1）NULL differ 0、 Empty strings and spaces

2） Out of commission “= NULL”（NULL Is an uncertain value ）

Such as ： retrieval customers In the table cust_email Whether the field value is NULL;

Collection retrieval

IN： Determine whether the value of the field is in the collection

1） Collections generally use “（）” Express

2） When there is any matching value in the field value table, this row is returned

3）IN You can use OR transformation ,NOT IN You can use AND transformation

//IN Compare with OR advantage ： Faster execution 、 May include other SELECT sentence （ Subquery ）

Such as ： retrieval products In the table vend_id yes 1002 and 1005 Of prod_name;

Such as ： retrieval products In the table vend_id No 1002 and 1003 Of prod_name;

Fuzzy search

LIKE： Use wildcards and some known information for fuzzy matching

wildcard ： Special characters that match part of the value

wildcard	explain
%（ Percent sign ）	representative 0、1 The length of one or more strings
_（ Underline ）	representative 1 The length of strings

1） Wildcards can be used anywhere in a string , And there is no quantitative limit （ Can't match NULL）

2） When querying a string containing wildcards , An escape character is required （\\） Escaping wildcards

// The escape character precedes the wildcard to be escaped

Such as ： retrieval products In the table prod_name The value of the field is jet Beginning line

Such as ： retrieval products In the table prod_name The field value contains anvil The line of

Such as ： retrieval products In the table prod_name The field value contains ton A line preceded by a single character

GROUP BY

GROUP BY： Group the query results according to the specified field values （ Easy to call aggregate function operation ）

1） grouping ： Data according to 1 Group one or more fields （ Those with the same field value are divided into a group ）

2）GROUP BY When selecting fields, you should select fields that are conducive to the use of aggregation functions

// Grouping refines aggregate functions （ After querying and grouping, the function will be applied to each group , And return a value for each group ）

Use GROUP BY Provisions for grouping ：

1）GROUP BY Can contain any number of fields , Can nest groups ;

2）GROUP BY If a group is nested , The data will be aggregated on the final defined group ;

3）GROUP BY The fields contained must be SELECT Fields retrieved / expression ;

4） If the group contains NULL, be NULL Separate as a group ;

//GROUP BY Expressions in cannot use aliases

Such as ： retrieval products Each of the tables vend_id Rows of owned data

Such as ： retrieval products Each table vend_id Rows of owned data , And summarize

//WITH ROLLUP Options ： Summarize all groups
// The title of the row where the summary is located is... By default NULL

Such as ： retrieval orders In the table cust_id The number of identical values in the field is greater than 2 Of cust_id That's ok

//HAVING Conditional expression ： Filter the grouped data with conditional expression

//HAVING Be similar to WHERE also HAVING Support WHERE All operators and syntax in

difference	WHERE Clause	HAVING Clause
Main use	Filter data lines	Filter grouping
Aggregate functions	Cannot contain	Can contain
Use your time	Filter before data grouping	Filter after data grouping

Such as ： retrieval products Table contains 2 individual prod_price Greater than 10 Of vend_id

Such as ： retrieval orderitems In the table quantity and item_price The product of the field is greater than 50 Of order_num And its quantity and item_price The product of the field , And sort by product in descending order

//GROUP BY Medium ASC/DESC Sorting is to sort the grouped data

GROUP_CONCAT() Functions can be used with GROUP BY Clause is used together to realize ： The field value after grouping the search results （NULL Except value ） Use commas to connect them under the same line

Such as ： retrieval products Each of the tables vend_id The... Corresponding to the field prod_name Field

ORDER BY

ORDER BY： The search results are in ascending order of the specified field values / Descending sort shows

1） The retrieval data results are displayed in the order saved in the underlying table by default （ Is not the only ）

2） When using multiple fields to specify sorting , Use... Between field names “,” Division ;

3） You can add... After each field ASC/DESC Specify ascending order / null ;

Such as ： retrieval products In the table prod_name Field , And in accordance with the prod_price Sorting shows ;

//ORDER BY The specified field is SELECT Specified fields , It can also be other fields in the table

Such as ： retrieval prod_id、prod_price and prod_name, call prod_price and prod_name Sort

// Sort order from left to right

// If the first field supports the completion of sorting （ No same value ）, Other fields will not be called for sorting

Such as ： retrieval vend_id and prod_price, call vend_id Descending sum prod_price Ascending sort

// Default to ASC（ Ascending ） Sort , And the sorting direction is from top to bottom

// If each field needs to be specified separately ASC/DESC（ASC Omission ）

LIMIT

LIMIT： Limit the number of rows displayed

1） If the specified number of rows to be displayed exceeds the number of rows in the search result , All rows will be displayed by default

2）LIMIT There is no operation in the statement （ If variable operation is required , stay LIMIT Execute before statement ）

(1）LIMIT M： Represents only the first... Of the search results M That's ok ;

1) Display from the first line of the search result （ Including the first line ）

Such as ： retrieval products In the table prod_name Field , And only the front 5 That's ok

(2）LIMIT M,N; Represents the search results from M Before the line starts to display N That's ok ;

1) The search result does not contain the M That's ok

2)LIMIT M,N Equivalent to LIMIT N OFFSET M

Such as ： retrieval products In the table prod_name Field , Show only 2 To 5 That's ok （ Show 4 Row content ）

Regular expressions

REGEXP： Represents the use of regular expressions to retrieve data in a table

// Regular expressions ： Match a special string in the text （ Case insensitive ）

1）“.” Wildcards in regular expressions can match any number of characters （ It doesn't contain 0 individual ）;

Such as ： retrieval products In the table prod_name The field value contains 000 The line of

2）“|” Represent or operate in regular expressions （OR）;

Such as ： retrieval products In the table prod_name The field value contains 1000 or 2000 The line of

3）“[ ]” In regular expressions, you can define “ Or set ”;

// Can be understood as “[ ]” yes “|” Another form of

Such as ： retrieval products In the table prod_name The field value contains 1 Ton or 2 Ton or 3 Ton The line of

// Not equivalent “1|2|3 Ton”, Its representatives 1 or 2 or 3 Ton The line of

4）“^” In regular expressions, it represents NOT;

// In regular expressions “^” In a different position , Its meaning is also different

Such as ： retrieval products In the table prod_name The field value does not contain 1 Ton or 2 Ton or 3 Ton The line of

5）“-” The specification of a range in a regular expression ;

Such as ： retrieval products In the table prod_name The field value contains 1 Ton or 2 Ton or 3 Ton The line of

6） When retrieving a string containing special characters , You need to add an escape character before a special character “\\”;

// Escape character matching backslash is “\\\”

Such as ： retrieval products In the table prod_name The field value contains “.” The line of

7） Using character class and metacharacter to retrieve the string or repeated string at the specified position ;

Character class ： Predefined character set

Character class	explain
[:alnum:]	Any character and number （ Same as [a-zA-Z0-9]）
[:alpha:]	Any character （ Same as [a-zA-Z]）
[:blank:]	Spaces and tabs （ Same as [\\t]）
[:cntrl:]	ASCII Control characters （ASCII Of 0 To 31 and 127）
[:digit:]	Arbitrary number （ Same as [0-9]）
[:graph:]	Same as [:printL] But no spaces
[:lower:]	Any lowercase character （[a-z]）
[:print:]	Any printable character
[:punct:]	Do not belong to [:alnum:] and [:cntrl:] The characters in
[:space:]	Spaces and any white space characters （ Same as [\\f\\n\\r\\t\\v]）
[:upper:]	Any capital letter （ Same as [A-Z]）
[:xdigit:]	Any hexadecimal number （ Same as [a-fA-F0-9]

Metacharacters ： Before and after characters , Represents the number and position of possible characters

Metacharacters	explain
*	0 One or more
+	1 One or more （ Same as {1,}）
？	0 Or 1 individual （ Same as {0,1}）
{n}	n individual
{n,}	No less than n individual
{n,m}	stay n To m Between （ Including boundaries ,m No more than 255）
^	The beginning of the string
$	The end of the string
[[:<:]]	The beginning of a word
[[:>:]]	Ending of words

Such as ： retrieval products In the table prod_name The field contains stick or sticks The line of

Such as ： retrieval products In the table prod_name The field contains 4 A line of numbers

Such as ： retrieval products In the table prod_name Field contains “.” Or lines beginning with numbers

Splicing field

Splicing field ： Combine the retrieved values of multiple fields to form a single field value
1） The splice field is SELET Use in statement CONCAT（） Function implementation

Such as ： retrieval vendor In the table vend_name and vend_country Field , And spliced into a single field

Field operation

Field operation ： Calculate the retrieved field value

1） You must set an alias for it

The operator	explain
+	Add
-	reduce
*	ride
/	except

// To ensure the order of operations , Callable parentheses （ The highest priority ） Process calculation order

Such as ： retrieval orederitmes In the table quantity and item_price The product of the field

Aggregate functions

Aggregate functions ： A function that evaluates data and returns a single value

1） Aggregate functions are often used in SELECT after , Cannot appear in WHERE clause ;

2） When there is no grouping , The aggregate function acts on the whole table to meet WHERE All records of conditions ;

Aggregate functions	explain
COUNT（*）	Returns the number of rows in the specified table （ Whether the line is NULL）
COUNT（[distinct|all] Name ）	Returns the value of the specified column NULL Number of rows
AVG（[distinct|all] Name ）	Returns the value of the specified column NULL The average of the values
SUM（[distinct|all] Name ）	Returns the value of the specified column NULL Sum of values
MAX（ Name ）	Returns the value of the specified column NULL The maximum value of
MIN（ Name ）	Returns the value of the specified column NULL The minimum value of

//ALL（ Show all the results ）、DISTINCT（ After de duplication, it shows ）, And the default for ALL
//ALL/DISTINCT It can also be used for MAX（） and MIN function , But it has no practical significance

Such as ： retrieval customers Number of rows in the table

Such as ： retrieval customers The table has non NULL Number of rows

Such as ： retrieval products In the table prod_price Field , Calculate the average

Such as ： retrieval orderitems In the table quantity The sum of the fields

Such as ： retrieval products In the table prod_price The row with the largest value in the field

Such as ： retrieval products In the table prod_price The row with the lowest value in the field