SQL query statement. Very classic

1. Simple query simple Transact-SQL query only includes the selection list, from clause, and WHERE clause. They illustrate the table or view of the query column, query, and search criteria, etc. For example, the following statement queries the Nickname field and the Email field named "Zhang San" in the TestTable table. Select Nickname, EmailFrom TestTableWhere Name = 'Zhang 3' (1) Select the list selection list (select_list) Indicates the query column, which can be a list of column names, an asterisk, expression, variable (including local variables and global variables) The composition is constituted. 1. Select all the columns, for example, the following statement displays the data in the TestTable table: select * from testtable2, select a section column and specify the order of data in the order query result collection and the column name specified in the selection list The order is the same. For example: select nickname, emailfrom testtable3, changing columns Title In the selection list, you can re-specify the column header. Define formats: Column Title = Column Name Column Title If the specified column header is not a standard identifier format, you should use the quotation margin delimiter, for example, the following statement uses Chinese characters to display column headings: SELECT nickname = Nickname, email = EmailFrom TestTable4, delete the repeated line SELECT statement using all or the distinct option to display all rows in the table or delete the duplicate data lines in the table, default is ALL. When using the Distinct option, only one row is retained in the result collection of all duplicate data rows in SELECT. 5, the number of rows returned using the top n [percent] option limit the number of data rows returned, and the top n describes the N row, and Top N Percent, indicating that N is a binary, specifying the number of rows of returned, etc. A few percent. For example: SELECT TOP 2 * from TestTableSelect Top 20 Percent * from testtable (2) from clause from clause From clause Specify SELECT statement query and table or view related to query. In the FROM clause, you can specify up to 256 tables or views, and they are separated by commas between them. When you specify multiple tables or views at the FROM clause, if there is a list of columns in the selection list, you should use the object name to qualify the table or view of these columns. For example, in the userTable and the CityTable table, there is a CITYID column, and if the CITYID in the two tables should be used to limit the following statement format: select username, citytable.cityIdfrom useertable, citytablewhere userTable.cityId = citytable.cityID in the from clause, in the FROM clause of the FROM clause, for example, in the origin of the FROTABLE, CITYTABLE.CITABLE.CIM The following two formats can be specified for the table or view as a table or view: Table name AS alias table name, such as the above statement, the alias format of the available table is expressed as: select username, b.cityidfrom useertable a, citytable bwhere a.cityid = B.CITYDSELECT can not only Retrieve data from the table or view, it is also possible to query data from the result collection returned from other query statements. For example: SELECT A.AU_FNAME A.AU_LNAMEFROM Authors A, Titleauthor Ta (SELECT TITLE_ID, TITLEFROM TITLESWHER YTD_SALES> 10000) AS Twhere A.AU_ID = TA.AU_IDAND TA.TID = TA.AU_IDAND TA.TITLE_ID = T.TITLE_ID This example, the result returned to select The collection gives a alias T and then retrieves data from it. (3) Setting the WHERE clause to set the query condition where clause set the query condition, filter out the unwanted data line.

For example, the following statement queries the data of the age greater than 20: Select * from userTableWhere Age> 20where clause can include various conditional operators: comparison operator (size comparison):>,> =, =, <, <=, <>, !>,! = 10 and age <= 302, list operator : Country in ('Germany', 'CHINA') 3, Mode Matching: Commonly used in the fuzzy lookup, it is determined if the column value matches the specified string format. Can be used for type inquiry such as char, varchar, text, ntext, datetime, and smalldatetime. You can use the following wild features: 100%: You can match the characters of any type and length. If it is Chinese, use two percent signs to %%. Underline _: Match a single arbitrary character, it is often used to limit the character length of the expression. Square brackets []: Specify a character, string, or range that requires the matching object to be any of them. [^]: The value is also [], but it requires the matching object to be any of the characters other than the specified character. For example, limited to the end of Publishing, using the Like '% Publishing' to the beginning: Like '[A]%' limit is outside the beginning: Like '[^ a]%' 4, null value judgment case where agn is NULL5 , Logical operators: Priority is NOT, AND, or (4) Sort by the results of the order using the ORDER BY clause to sort the results returned by the query. The syntax format of the Order By clause is: Order by {color} [, ... n] where the ASC represents ascending, the default value, DESC is descending. ORDER BY cannot be sorted by NTEXT, TEXT, and Image data types. For example: Select * from userTableOrder By Age DESC, Userid ASC Addition can be sorted according to the expression. Second, the joint query Union operator can combine the query results of two or more SELECT statements into a result set display, which is the joint query. The syntax format of Union is: select_statementunion [all] selectStatement [union [all] selectstatement] [... n] where selectStatement is a SELECT query statement to be combined. The all option represents all rows into the result collection. When this item is not specified, the repeat line of the joint query result will only be retained. When the query is combined, the column of the query result is the topic title of the first query statement. Therefore, to define the column headings must be defined in the first query statement. To sort the joint query results, you must also use the column name, column header or column sequence number in the first query statement. When using the UNION operator, there should be the same number of expressions in the selection list of each joint query statement, and each query selection expression should have the same data type, or can automatically convert them to the same data. Types of.

When automatic conversion, for numeric types, the system converts low-precision data types to high-precision data types. In the Union statement including multiple queries, the execution order is from left to right, and the use of parentheses can change this execution order. For example: Query 1 Union (Query 2 Union Query 3) Third, the connection query can implement multiple table queries through the connection operator. Connection is the main feature of the relational database model, and it is also a sign of other types of database management systems. In the relational database management system, the relationship between each data does not have to be determined when the table is established, and all information about one entity is often stored in a table. When retrieving data, the information stored in multiple tables is queried when the data is retrieved. Connection * For users to bring great flexibility, they can add new data types at any time. Create a new table for different entities, and then query by the connection. The connection can be established in the FROM clause or WHERE clause of the SELECT statement, which is similar to that in the FROM clause indicates that the connection is partially distinguished from the search criteria in the WHERE clause. So, this method is recommended in Transact-SQL. The connection 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 to the table name of the connection *, the connection can be made to the same table *, you can also Table *, the connection made by the same table * is also called it. Join_type indicates that the connection type can be divided into three: internal connections, external connections, and cross-connect. Inner Join Using the comparison operator to compare the comparison * of the column data, and list the data lines matching the connection conditions in these tables. According to the comparative method used, the inner connection is divided into equivalent connection, natural connection, and not equal connection. The outer connection is divided into left-way connections (Right Outer Join, Right Outer Join or Right Join, and three full-end connections (Full Outer Join or Full Join). Unlike internal connections, the external connection does not only list rows matching the connection condition, but is listed on the left table (left external connection), the right table (right-external connection) or two tables (all external connection All data lines in line with search criteria are in line. Cross Join does not have a WHERE clause, it returns the Cartesian of all data lines in the connection table, and the number of data lines in the result set is equal to the number of data lines that meet the query conditions in the first table. The number of data lines in the table is in line with the query conditions. The on (join_condition) clause in connection * indicates the connection condition, which is composed of a column and comparison operator in the connected table, a logical operator, and the like. No matter which connection can be directly connected, these three columns can be indirectly connected. For example: select p1.pub_id, p2.pub_id, p1.pr_infofrom pub_info as p1 inner join pub_info as p2on datalength (p1.pr_info) = DATALENGTH (P2.PR_INFO) (1) Interconnect connection query * List and connection conditions Matching data lines, which uses comparison operators to compare the column values ​​of the columns. Internal connections, three types: 1, equivalence connection: Use the column value of the connected column to compare the column value in the connection condition, listed in the query results, including all columns in the connected table, including the repetition Column. 2, inequality: Use the column values ​​of the columns connected to the listed columns that are connected to other comparison operators other than operators. These operators include>,> =, <=, <,!>,! .