A wonderful summary of SQL query methods

A simple Transact-SQL query only includes a select list, FROM clause and WHERE clause. They respectively describe the queried column, the queried table or view, and the search conditions.

1. Simple query

A simple Transact-SQL query only includes a select list, FROM clause and WHERE clause. They respectively describe the queried column, the queried table or view, and the search conditions.
For example, the following statement queries the nickname field and email field named "Zhang San" in the testtable table.

SELECT nickname,email
FROM testtable
WHERE name='Zhang San'

(1) Selection list

The selection list (select_list) indicates the queried column, which can be a set of column name lists, asterisks, expressions, variables (including local variables and global variables), etc.

1. Select all columns

For example, the following statement displays the data of all columns in the testtable table:

SELECT *
FROM testtable

　2. Select some columns and specify their display order

The order of the data in the query result set is the same as the order of the column names specified in the selection list.
For example:

SELECT nickname,email
FROM testtable

3. Change the column title

In the selection list, you can re- Specify column headers. The definition format is:
Column title = Column name
Column name Column title
If the specified column title is not a standard identifier format, quotation mark delimiters should be used. For example, the following statement uses Chinese characters to display columns Title:

SELECT Nickname=nickname, email=email
FROM testtable

4. Delete duplicate rows

Use ALL in the SELECT statement Or use the DISTINCT option to display all rows in the table that meet the criteria or delete duplicate data rows. The default is ALL. When using the DISTINCT option, only one row is retained in the result set returned by SELECT for all duplicate data rows.

5. Limit the number of rows returned

Use the TOP n [PERCENT] option to limit the number of data rows returned. TOP n means n rows will be returned, and when TOP n PERCENT, n means Percent, specifies what percentage of the total number of rows the number of rows returned equals.
For example:

SELECT TOP 2 *
FROM testtable
SELECT TOP 20 PERCENT *
FROM testtable

)FROM clause

The FROM clause specifies the SELECT statement query and the tables or views related to the query. Up to 256 tables or views can be specified in the FROM clause, separated by commas.
When the FROM clause specifies multiple tables or views at the same time, if there are columns with the same name in the selection list, the object names should be used to qualify the tables or views to which these columns belong. For example, if the cityid column exists in both the usertable and citytable tables, the following statement format should be used to qualify the cityid in the two tables:

SELECT username,citytable.cityid
FROM usertable, citytable
WHERE usertable.cityid=citytable.cityid

The following two formats can be used to specify an alias for a table or view in the FROM clause:
Table name as alias
Table name Alias ​​
(2) FROM clause

The FROM clause specifies the SELECT statement query and the tables or views related to the query. Up to 256 tables or views can be specified in the FROM clause, separated by commas.
When the FROM clause specifies multiple tables or views at the same time, if there are columns with the same name in the selection list, the object names should be used to qualify the tables or views to which these columns belong. For example, the cityid column exists in both the usertable and citytable tables. When querying the cityid in the two tables, the following statement format should be used to qualify it:

　SELECT username,citytable.cityid
　FROM usertable,citytable
WHERE usertable.cityid=citytable.cityid

The following two formats can be used to specify aliases for tables or views in the FROM clause:
Table name as alias
Table name alias
For example, above The statement can be expressed in the alias format of the table as:

SELECT username,b.cityid
FROM usertable a,citytable b
WHERE a.cityid=b.cityid

SELECT can not only Retrieve data from a table or view. It can 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, Then retrieve the data from it.

　 (3) Use the WHERE clause to set query conditions

The WHERE clause sets query conditions and filters out unnecessary data rows. For example, the following statement queries data whose age is greater than 20:

SELECT *
FROM usertable
WHERE age>20

The WHERE clause can include various conditional operators:
Comparison operators (size comparison): >, >=, =, <, <=, <>, !>, !<
Range operator (whether the expression value is within the specified range): BETWEEN…AND…
NOT BETWEEN…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 matching character (to determine whether the value matches the specified character wildcard format): LIKE, NOT LIKE
Null value judgment operator (to judge whether the expression is empty): IS NULL, NOT IS NULL
Logical operators (used for logical connection of multiple conditions): 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 operator Example: Often used for fuzzy search, it determines whether the column value matches the specified string format. Can be used for char, varchar, text, ntext, datetime and smalldatetime types of queries.
The following wildcard characters can be used:
Percent sign %: can match characters of any type and length. If it is Chinese, please use two percent signs, namely %%.
Underscore_: matches a single arbitrary character, which is often used to limit the character length of expressions.
Square brackets []: Specify a character, string or range, requiring the matched object to be any one of them. [^]: Its value is the same as [], but it requires that the matched object is any character other than the specified character.
For example:
If the limit ends with Publishing, use LIKE '%Publishing'
If the limit starts with A: LIKE '[A]%'
If the limit does not start with A: LIKE '[^A]% '
4. Example of null value judgment symbol WHERE age IS NULL

5. Logical operators: priority is NOT, AND, OR

(4) Query result sorting

Use the ORDER BY clause to sort the results returned by the query by one or more columns. The syntax format of the ORDER BY clause is:
ORDER BY {column_name [ASC|DESC]} [,...n]
Where ASC means ascending order, which is the default value, and DESC means descending order. ORDER BY cannot sort by ntext, text and image data types
.
　For example:

　SELECT *
　FROM usertable
　ORDER BY age desc,userid ASC

　In addition, you can sort based on expressions.

2. Union Query

The UNION operator can combine the query result sets of two or more SELECT statements into one result set for display, that is, perform a union Inquire. The syntax format of UNION is:

select_statement
UNION [ALL] selectstatement
[UNION [ALL] selectstatement][…n]

where selectstatement is the SELECT query statement to be combined .

The ALL option means to merge all rows into the result set. When this item is not specified, only one row of duplicate rows will be retained in the combined query result set.

When performing a joint query, the column headings of the query results are the column headings of the first query statement. Therefore, column headers must be defined in the first query statement. When sorting joint query results, the column name, column title or column number in the first query statement must also be used.
When using the UNION operator, you should ensure that the selection list of each union query statement has the same number of expressions, and each query selection expression should have the same data type, or they can be automatically converted to same data type. During automatic conversion, for numeric types, the system converts low-precision data types into high-precision data types.

In a UNION statement that includes multiple queries, the execution order is from left to right. You can change this execution order by using parentheses. For example:

Query 1 UNION (Query 2 UNION Query 3)

3. Connection query

Multiple table queries can be realized through the connection operator . Connections are the main feature of the relational database model and a sign that distinguishes it from other types of database management systems.

In a relational database management system, the relationship between each data does not need to be determined when the table is created. All information of an entity is often stored in one table. When retrieving data, information about different entities stored in multiple tables is queried through join operations. Join operations give users great flexibility, and they can add new data types at any time. Create new tables for different entities and then query them through joins.
The connection can be established in the FROM clause or the WHERE clause of the SELECT statement. Plausibly indicating the connection in the FROM clause helps to distinguish the connection operation from the search conditions in the WHERE clause. Therefore, this method is recommended in Transact-SQL.
The join syntax format of the FROM clause defined by the SQL-92 standard is:

FROM join_table join_type join_table
[ON (join_condition)]

Where join_table points out the name of the table participating in the connection operation. The connection can operate on the same table or on multiple tables. The connection on the same table is also called a self-join.

join_type indicates the connection type, which can be divided into three types: inner join, outer join and cross join. Inner JOIN uses comparison operators to compare certain column(s) of data between tables, and lists the data rows in these tables that match the join conditions. According to the different comparison methods used, inner joins are divided into three types: equal joins, natural joins and unequal joins. Outer joins are divided into three types: left outer join (LEFT OUTER JOIN or LEFT JOIN), right outer join (RIGHT OUTER JOIN or RIGHT JOIN) and full outer join (FULL OUTER JOIN or FULL JOIN). Different from inner joins, outer joins not only list the rows that match the join conditions, but also list the left table (when using a left outer join), the right table (when using a right outer join), or both tables (when using a full outer join). ) that match the search criteria.
Cross join (CROSS JOIN) does not have a WHERE clause. It returns the Cartesian product of all data rows in the connected table. The number of data rows in the result set is equal to the number of data rows in the first table that meet the query conditions multiplied by The number of data rows in the second table that meet the query conditions.
The ON (join_condition) clause in the join operation indicates the join condition, which consists of columns in the joined table, comparison operators, logical operators, etc.
No matter what kind of connection, text, ntext and image data type columns cannot be directly connected, but these three columns can be connected indirectly. 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 join
The inner join query operation lists the data rows that match the join condition. It uses comparison operators to compare the column values ​​of the joined columns. There are three types of inner joins:
1. Equivalent join: Use the equal sign (=) operator in the join condition to compare the column values ​​of the joined columns. The query results list all columns in the joined table, including Duplicate columns in it.
　2. Unequal connection: Use other comparison operators except the equal operator in the connection condition to compare the column values ​​of the connected columns. These operators include >, >=, <=, <, !>, !<, and <>.
　3. Natural join: Use the equal (=) operator in the join condition to compare the column values ​​of the joined columns, but it uses a select list to indicate the columns included in the query result set and delete duplicate columns in the joined table. .
Example, the following uses an equijoin to list the authors and publishers located 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
Another example is to use natural joins to delete 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) Outer join
When joining inner join, return the query result set Only rows that meet the query conditions (WHERE search conditions or HAVING conditions) and join conditions. When an outer join is used, the query result set returned not only contains rows that meet the join conditions, but also includes the left table (when left outer join), the right table (when right outer join) or two edge tables (full all data rows in the outer join). As shown below, use a left outer join to connect the forum content and author information:

SELECT a.*,b.* FROM luntan LEFT JOIN usertable as b
ON a.username=b.username

The following uses a full outer join to list all the authors in the city table and all the authors in the user table, as well as the cities where they are located:

SELECT a.*,b.*
FROM city as a FULL OUTER JOIN user as b
ON a.username=b.username

(3) Cross join
Cross join does not have a WHERE clause, it returns all data rows of the two tables being connected Cartesian product, the number of data rows returned in the result set is equal to the number of data rows in the first table that meet the query conditions multiplied by the number of data rows in the second table that meet the query conditions. For example, there are 6 categories of books in the titles table, and 8 publishers in the publishers table, then the number of records retrieved by the following cross connection will be equal to 6*8=48 rows.

SELECT type,pub_name

FROM titles CROSS JOIN publishers
ORDER BY type
[Post=0][/Post]

The above is the detailed content of A wonderful summary of SQL query methods. For more information, please follow other related articles on the PHP Chinese website!