Database Knowledge-sql Query Statement essence use Brief

The Essence | data | database | Statement One, simple query simple Transact-SQL query only includes select list, FROM clause and WHERE clause. 　　They describe the query column, the table or view of the query, and the search criteria, and so on. 　　For example, the following statement queries the nickname field and the email field whose name is "John" in the TestTable table. Select Nickname,email from TestTable WHERE name= ' John ' (a) Picklist selection list (select_list) indicates the queried column, which can be a list of column names, asterisks, expressions, variables (including local variables and 　　Global variables) and so on. 　　1. Select all columns for example, the following statement shows the data for all columns in the TestTable table: SELECT * FROM TestTable 2, select some columns and specify their display order the data in the query result collection is the same as the column names specified in the select list. For example: Select Nickname,email from TestTable 3, change column headings in the select list, you can specify the column headings again. 　　The definition format is: column heading = column Name list title if you specify a column heading that is not a standard identifier format, use a quotation mark delimiter, for example, the following statement uses Chinese characters to display column headings: SELECT nickname =nickname, e-mail =email from TestTable 4. Delete duplicate rows Select statements use the all or distinct option to display all rows in the table that meet the criteria or to delete duplicate rows of data, default to all. 　　When you use the DISTINCT option, only one row is left in the result set returned by SELECT for all duplicate rows of data. 　　5, limit the number of rows returned use the top n [PERCENT] option to limit the number of rows returned, top N to return n rows, and Top n PERCENT to indicate that n is a percentage, specifying that the number of rows returned equals the total number of lines in the row. For example: SELECT Top 2 * from TestTable Select PERCENT * from TestTable (ii) The FROM clause specify a SELECT statement query and a query-related table or View. 　　You can specify up to 256 tables or views in the FROM clause, separated by commas. When you specify multiple tables or views at the same time in the FROM clause, if you selectlist, you should use object names to qualify the tables or views to which these columns belong. For example, Cityid columns exist in both usertable and citytable tables and should be qualified with the following statement format when querying the Cityid in two tables:  select Username,citytable.cityid from 　　usertable,citytable where Usertable.cityid=citytable.cityid can specify aliases for a table or view in the following two formats in the FROM clause: Table name as alias table name alias (ii) FROM clause The FROM clause specifies the SELECT statement query and the table or view associated with the query. 　　You can specify up to 256 tables or views in the FROM clause, separated by commas. When you specify multiple tables or views at the same time in the FROM clause, you should use the object name to qualify the table or view to which the columns belong if the same names exist in the selection list. For example, Cityid columns exist in both usertable and citytable tables, and should be qualified with the following statement format when querying for Cityid in two tables: SELECT Username,citytable.cityid from Usertable, 　 citytable where Usertable.cityid=citytable.cityid can specify an alias for a table or view in the following two formats in the FROM clause: Table name as alias table name alias, for example, the preceding statement can be represented as an alias format for a table: Select Username,b.cityid from usertable a,citytable b WHERE A.cityid=b.cityid Select Not only retrieves data from a table or view, it can also return results from other query statements 　　Query data in the collection. 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 case, give the result set returned by the Select an alias t and retrieve the data from it.(iii) Use the WHERE clause to set the query criteria WHERE clause to set the query criteria to filter out unwanted rows of data. For example, the following statement queries for data older than 20: SELECT * from usertable where age>20 WHERE clause can include various conditional operators: comparison operators (size comparison):>, >=, =, <, < =, <>,!>,!< range operator (expression value is in the specified range): BETWEEN ... 　　And ... Not BETWEEN ... 　　And ... 　　List operator (determines whether an expression is a specified item in a list): In (item 1, item 2 ...) 　　Not in (item 1, item 2 ...) Pattern-matching character (determines whether the value matches the specified character wildcard format): Like, not-like null-value judge (to determine whether an expression is empty): Is null, not-is null logical operator (for multiple-condition logical joins): not, and, or 1, range operator Example: AG E BETWEEN and 30 are equivalent to age>=10 and age<=30 2, List operators: Country in (' Germany ', ' ') 3, pattern-matching characters: Often used in fuzzy lookup, it determines whether the column value and the specified character string format matches. 　　Can be used for types of queries such as char, varchar, text, ntext, datetime, and smalldatetime. 　　You can use the following wildcard characters: percent percent: can match any type and length of the character, if it is Chinese, please use two percent sign is%. 　　Underline _: Matches a single arbitrary character, which is often used to limit the length of the character of an expression. square brackets []: Specifies a character, string, or range, requiring that the matched object be any of them. 　　[^]: The value is also [] the same, but it requires that the matched object be any character other than the specified character. For example: The limit ends with Publishing, using the like '%publishing ' limit to start with a: the ' [a]% ' restriction begins with a: ' [^a]% ' 4, null-value-for-example where, is null 5, logical shipping Operator: Precedence for not, and, or (four) query result sort use the ORDER BY clause to sort the results returned by a query by one or more columns. The ORDER BY clause is in the form of the following syntax: column_name[asc| DESC]} [,... N] where ASC represents ascending, the default value, and DESC is descending. 　　Order by cannot be sorted by ntext, text, and image data types. 　　For example:  select * from Usertable, Desc,userid ASC In addition, you can sort by an expression. The Union query Union operator can combine the query result sets of two or more SELECT statements into a single result set, that is, to execute a federated query. The syntax format of the Union is:  select_statement Union [all] selectstatement [union [all] SELECTSTATEMENT][...N] where selectstatement 　　is a SELECT query statement to be joined. The all option means that all rows are merged into the result collection. 　　When this item is not specified, the duplicate rows in the Federated query result collection are persisted to only one row. In a federated query, the column headings of the query results are the column headings of the first query statement. Therefore, to define a column heading must be defined in the first query statement. 　　To sort federated query results, you must also use the column name, column heading, or column ordinal in the first query statement. When using the union operator, you should ensure that there are the same number of expressions in the select list for each federated query statement, and that each query selection expression should have the same data type, or you can automatically convert them to the same data type. 　　In the case of automatic conversion, the system converts a low precision data type to a high precision data type for numeric types. In a union statement that includes multiple queries, the order of execution is from left to right, and parentheses can be used to change the order of execution. For example: Query 1 Union (query 2 Union query 3) Three, the connection query through the concatenation operator can implement multiple table queries. 　　Connection is the main feature of relational database model, and it is also a symbol distinguishing from other types of database management system. In the relational database management system, the relationship between the data in the table is not determined, and all the information of an entity is stored in a table. When retrieving data, queries the information of different entities that are stored in multiple tables through a JOIN operation. Connection operations give users a lot of flexibility, and they can add new data types at any time. 　　Create a new table for different entities, and then query by connection. A connection can be established in the FROM clause or a WHERE clause of a SELECT statement, and paradoxically in the FROM clause to help distinguish the join operation from the search condition in the WHERE clauseSpread 　　Therefore, the use of 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)] where join_table indicates the name of the table participating in the join operation, and the connection can 　　The same table operation, or multiple table operations, the same table operation of the connection is called a self connection. Join_type indicates the type of connection, which can be divided into three types: inner, outer, and Cross joins. The INNER join (INNER join) uses the comparison operator to compare operations of some (some) column data between tables and lists the rows of data in those tables that match the join condition. According to the comparison method used, the inner connection is divided into equivalent connection, natural connection and unequal connection three kinds. An outer join is divided into three kinds of left outer joins (either the right OUTER join or the Ieft join), the right-hand outer join (the OUTER join, or the OK join), and the full outer join (either a fully OUTER join or a complete join). 　　Unlike an inner connection, the outer join lists not only the rows that match the join condition, but all the rows of data that match the search criteria in the left table (when left outer), right (when the right outer join), or two tables (when all outer joins). 　　A cross join (CROSS JOIN) does not have a WHERE clause that returns the Cartesian product of all rows of data in the join table, with the number of rows in the result set equal to the number of rows in the first table that match the query criteria multiplied by the number of rows of data in the second table that match the query criteria 　　The ON (join_condition) clause in a JOIN operation indicates the join condition, which consists of columns and comparison operators, logical operators, and so on in the connected table. No connection can be directly connected to the text, ntext, and image data type columns, but the three types of columns may be indirectly connected. 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) =da Talength (P2.pr_info) (i) Inner JOIN query operation lists the rows of data that match the join condition, which compares the column values of the connected columns using comparison operators. 　　The inner connection is divided into three kinds: 1. Equivalent connection: Use the equals sign (=) operator in the join condition to compare the column values of the connected columns, and the query results list all the columns in the connected table, including the repeating columns. 2, unequal connection: In connection with the use of other conditionsComparison operators other than operators to compare the column values of the connected columns. 　　These operators include >, >=, <=, <,!>,!<, and <>. 　　3. Natural connection: Use the Equals (=) operator in the join condition to compare the column values of the connected columns, but it uses a select list to indicate the columns included in the query result collection, and deletes the duplicate columns from the attached table. For example, the following uses an equivalent connection 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.cit Y also, if you are using a natural connection, delete the duplicate columns (city and state) in the authors and Publishers tables in the SELECT 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 (ii) outer joins, only rows that meet the query criteria (WHERE search conditions or having conditions) and join conditions are returned in the query result collection. When an outer join is used, it returns to the query result collection not only for rows that meet the join criteria, but also for all data rows in the left table (when the left outer join), right table (when the right outer join), or two side tables (full outer joins). Connect the forum content to the author information as follows using the left OUTER join: SELECT a.*,b.* from Luntan to usertable as B on A.username=b.username use the full outer join to place the city table 　　All authors in, and all authors in the user table, and the city in which they reside:  select a.*,b.* from cities as a full OUTER JOIN user as B on a.username=b.username (iii) Cross-connect cross-connect without a WHERE clause, it returns the Cartesian product of all data rows of the connected two tables, and the number of rows returned to the result set equals the number of data rows in the first table that match the query criteria multiplied by the number of rows of data in the second table that match the query criteria. 　　For example, there are 6 categories of books in the titles table, and there are 8 publishers in the publishers table, the number of records retrieved by the following cross joins equals 6*8=48 rows. SELECT TypE,pub_name from titles CROSS JOIN Publishers order by type [Post=0][/post]