SQL query statement essence _ MySQL

1. simple query simple Transact-SQL queries only include the select list, FROM clause, and WHERE clause. They indicate the queried columns, the queried tables or views, and the search conditions. For example, the following statement queries the nick name and email fields in the testtable table with the name "zhang san. SELECTnickname, emailFROMtesttableWHEREna 1. simple query
A simple Transact-SQL query only includes the select list, FROM clause, and WHERE clause. They respectively describe the queried columns and the queried
Tables or views, and search conditions.
For example, the following statement queries the nick name and email fields in the testtable table with the name "zhang san.
SELECT nickname, email
FROM testtable
WHERE name = 'zhangsan'

(1) select a list
Select_list indicates the queried column. it can be a list of column names, asterisks, expressions, and variables (including local variables ).
And global variables.

1. select all columns
For example, the following statement shows the data of all columns in the testtable table:
SELECT *
FROM testtable

2. select some columns and specify their display order
The data in the query result set is arranged in the same order as the column name in the selected list.
For example:
SELECT nickname, email
FROM testtable

3. change the column title
In the selection list, you can re-specify the column title. Definition format:
Column title = column name
Column name column title
If the specified column title is not in the standard identifier format, use the quotation mark ("). For example, the following statement uses Chinese characters to display columns
Title:
SELECT nickname = nickname, email = email
FROM testtable

4. delete duplicate rows
Use the ALL or DISTINCT option in the SELECT statement to display ALL rows that meet the conditions in the table or delete duplicate data rows. the default value is
Is ALL. When the DISTINCT option is used, only one row is retained for all duplicate data rows in the result set returned by the SELECT statement.

5. limit the number of returned rows
Use the TOP n [PERCENT] option to limit the number of returned data rows. TOP n indicates that n rows are returned, while TOP n indicates that n is
Indicates the one hundred score, and the number of returned rows is equal to a few percent of the total number of rows.
For example:
Select top 2 *
FROM testtable
Select top 20 PERCENT *
FROM testtable

(2) FROM Clause
The FROM clause specifies the tables or views related to the query of SELECT statements. Up to 256 tables or views can be specified in the FROM clause,
They are separated by commas.
When the FROM clause specifies multiple tables or views at the same time, if the same column exists in the selected list, use the object name to limit these columns.
The table or view. For example, if the cityid column exists in both the usertable and citytable tables
Use the following statement format to limit:
SELECT username, citytable. cityid
FROM usertable, citytable
WHERE usertable. cityid = citytable. cityid
You can specify aliases for tables or views in the FROM clause in the following two formats:
Table name as Alias
Table name alias

(2) FROM Clause
The FROM clause specifies the tables or views related to the query of SELECT statements. Up to 256 tables or views can be specified in the FROM clause,
They are separated by commas.
When the FROM clause specifies multiple tables or views at the same time, if the same column exists in the selected list, use the object name to limit these columns.
The table or view. For example, if the cityid column exists in both the usertable and citytable tables
Use the following statement format to limit:
SELECT username, citytable. cityid
FROM usertable, citytable
WHERE usertable. cityid = citytable. cityid
You can specify aliases for tables or views in the FROM clause in the following two formats:
Table name as Alias
Table name alias
For example, the alias format of the table available in the preceding statement is as follows:
SELECT username, B. cityid
FROM usertable a, citytable B
WHERE a. cityid = B. cityid
SELECT can not only retrieve data from tables or views, but also query data from the result set returned by other query statements.
For example:
SELECT a. au_fname a. au_lname
FROM authors a, titleauthor ta
(SELECT title_id, title
FROM titles
WHERE ytd_sales> 10000
) AS t
WHERE a. au_id = ta. au_id
AND ta. title_id = t. title_id
In this example, the result set returned by SELECT is given an alias t, and then the data is retrieved.

(3) use the WHERE clause to set query conditions
The WHERE clause sets query conditions to filter out unwanted data rows. For example, the following statement queries data older than 20:
SELECT *
FROM usertable
WHERE age> 20
The WHERE clause can contain various conditional operators:
Comparison Operators (size comparison):>, >=, =, <, <=, <>,!> ,! <
Range operator (whether the expression value is in the specified range):... AND...
Not... AND...
List operator (determines whether the expression is a specified item IN the list): IN (item 1, Item 2 ......)
Not in (item 1, Item 2 ......)
Pattern match character (determine whether the value matches the specified character wildcard format): LIKE, NOT LIKE
NULL value identifier (whether the expression is null): is null, NOT IS NULL
Logical operators (for multi-condition logical connections): NOT, AND, OR
1. range operator example: age BETWEEN 10 AND 30 is equivalent to age> = 10 AND age <= 30
2. list operator example: country IN ('Germany ', 'China ')
3. pattern matching example: it is often used for fuzzy search to determine whether the column value matches the specified string format. Can be used for char,
Queries of varchar, text, ntext, datetime, and smalldatetime types.
You can use the following wildcard characters:
Percent sign %: it can match characters of any type and length. if it is Chinese, use two percent signs (%.
Underline _: matches any character. it is often used to limit the character length of an expression.
Square brackets []: specifies a character, string, or range. the matching object must be any of them.
[^]: The value is [] the same, but the matching object must be any character other than the specified character.
For example:
End with Hing, use LIKE '% Hing'
It must start with A: LIKE '[A] %'
Restrict the outer class starting with A: LIKE '[^ A] %'
4. NULL value judgment operator example WHERE age IS NULL
5. logical operators: the priority is NOT, AND, OR
(4) sorting of query results
Use the order by clause to sort the query results BY one or more columns. The syntax format of the order by clause is:
Order by {column_name [ASC | DESC]} [,… N]
ASC indicates ascending order, which is the default value and DESC indicates descending order. Order by cannot be sorted BY ntext, text, or image data type
.
For example:
SELECT *
FROM usertable
Order by age desc, userid ASC
In addition, you can sort data by expressions.

2. joint query
The UNION operator can combine the query result sets of two or more SELECT statements into one result set for display.
Query. The syntax format of UNION is:
Select_statement
UNION [ALL] selectstatement
[UNION [ALL] selectstatement] [… N]
Selectstatement is the SELECT query statement to be combined.
The ALL option combines ALL rows into the result set. If this item is not specified, only one repeat row is retained in the Union query result set.
Line.
During a joint query, the column title of the query result is the column title of the first query statement. Therefore, to define a column title, the column title must be in the first query language.
Sentence definition. To sort the union query results, you must also use the column name, column title, or column number in the first query statement.
When using the UNION operator, make sure that each UNION query statement has the same number of expressions in the selection list, and each query Selects
The expressions should have the same data type, or they can be automatically converted to the same data type. During automatic conversion
The system converts low-precision data types to high-precision data types.
In UNION statements that contain multiple queries, the execution sequence is from left to right. brackets can be used to change the execution sequence. For example:
Query 1 UNION (query 2 UNION query 3)

III. connection query
You can use the join operator to query multiple tables. Connection is the main feature of the relational database model and is also different from other types.
A sign of the database management system.
In the relational database management system, the relationship between data does not have to be determined when a table is created, and all information of an object is often stored in
A table. When retrieving data, you can use the join operation to query information about different entities in multiple tables. Connection operation
With great flexibility, they can add new data types at any time. Create a new table for different entities and then connect to the table.
Query.
The connection can be established in the FROM clause or WHERE clause of the SELECT statement. it may be helpful to indicate the connection in the FROM clause.
Separate the join operation from the search condition in the WHERE clause. Therefore, this method is recommended in Transact-SQL.
The connection syntax format for the FROM clause defined by the SQL-92 standard is:
FROM join_table join_type join_table
[ON (join_condition)]
Join_table indicates the name of the table involved in the join operation. the join operation can be performed on the same table or on multiple tables.
Table operations.
Join_type indicates the connection type, which can be divided into three types: internal connection, external connection, and cross connection. Inner join usage ratio
Compare data in one or more columns between tables using comparison operators, and list data rows that match the connection conditions in these tables. Based on
Internal connections are classified into three types: equivalent connections, natural connections, and unequal connections.
Outer join is divided into left outer join (left outer join or left join), right outer join (right outer join or right join)
Full outer join or full join. Different from the internal connection, the external connection not only lists the matches with the connection conditions
To list all the rows that meet the search criteria in the left table (left outer join), right table (right outer join), or two tables (full outer join ).
Data rows.
Cross join does not have a WHERE clause. it returns the Cartesian product of all data rows in the JOIN table.
The number of rows equals to the number of rows that meet the query conditions in the first table multiplied by the number of rows that meet the query conditions in the second table.
The ON (join_condition) clause in the join operation specifies the join condition, which is composed of columns in the connected table, comparison operators, and logic
Operator.
No connection can be used to directly connect columns of the text, ntext, or image data type, but these columns can be indirectly connected.
Connection. For example:
SELECT p1.pub _ id, p2.pub _ id, p1.pr _ info
FROM pub_info AS p1 inner join pub_info AS p2
On datalength (p1.pr _ info) = DATALENGTH (p2.pr _ info)

(1) inner connection
The inner join query operation lists the data rows that match the connection condition. it uses the comparison operator to compare the column values of the connected columns. Internal connection score
Three types:
1. equijoin: Use the equal sign (=) operator in the connection condition to compare the column values in the connected column. the joined column values are listed in the query results.
All columns in the table, including duplicate columns.
2. unequal join: Use a comparison operator other than the equal operator to compare the column values of the connected columns. These
Operators include >,>=, <=, <,!> ,! <和<> .
3. Natural join: Use the equal to (=) operator in the connection condition to compare the column value of the connected column, but it uses the selection list to indicate the query.
The columns included in the result set, and the duplicate columns in the connection table are deleted.
For example, the following uses equijoin to list authors and publishers in the same city in the authors and publishers tables:
SELECT *
FROM authors AS a inner join publishers AS p
ON a. city = p. city
If you use a natural connection, delete the duplicate columns (city and state) in the authors and publishers tables in the selection list ):
SELECT a. *, p. pub_id, p. pub_name, p. country
FROM authors AS a inner join publishers AS p
ON a. city = p. city
(2) external connection
When the connection is in, only the WHERE search condition or HAVING condition and the connection condition in the returned query result set are met.
. When an external connection is used, it returns to the query result set that contains not only rows that meet the connection conditions, but also the left table (left outer ).
All data rows in the connected table, right table (right outer join), or two edge tables (all outer join.
For example, use the left outer link to connect the Forum content to the author information:
SELECT a. *, B. * FROM luntan left join usertable as B
ON a. username = B. username
Next we will use a full outer join to list all the authors in the city table, all the authors in the user table, and their cities:
SELECT a. *, B .*
FROM city as a full outer join user as B
ON a. username = B. username

(3) cross join
The cross join clause does not contain the WHERE clause. it returns the Cartesian product of all data rows in the two joined tables and the number returned to the result set.
The number of rows equals to the number of rows that meet the query conditions in the first table multiplied by the number of rows that meet the query conditions in the second table.
For example, if there are 6 types of books in the titles table, and there are 8 publishers in the publishers table, the number of records retrieved by the following crossover will be
Rows 6*8 = 48.
SELECT type, pub_name
FROM titles cross join publishers
Order by type