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, Email From TestTable where name = 'Zhang 3' (1) Select the list selection list (select_list) Indicates the query column, which can be a list of list, asterisk, expression, variable (including local variables and global) Variables, etc. 1. Select all columns, for example, the following statement displays all the columns in the TestTable table: Select * from testtable 2, select the part column and specify the order of the 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, Email from TestTable 3, Change column 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 = Email from testTable 4, delete the repeated line SELECT statement to display all rows or delete data lines in the table or delete the duplicate data lines in the table, and delete the duplicate data lines. 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 TestTable SELECT TOP 20 Percent * from testtable (2) from clause From clause specifying a SELECT statement query and a 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, there is a CITYID column in the userTable and the CityTable table. When you query the cityID in the two tables, you should use the following statement format to limit: select username, citytable.cityid from userTable, CityTable where usertable.cityId = citytable.cityID in from The following two formats can be specified in the following format: Table name AS Alias table name (2) The FROM clause from the SELECT statement query and the table or view associated with the 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, there is a CITYID column in the userTable and the CityTable table. When you query the cityID in the two tables, you should use the following statement format to limit: select username, citytable.cityid from userTable, CityTable where usertable.cityId = citytable.cityID in from The following two formats can be specified in the following formats to specify an alias 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.cityid from userTable a, citytable b where a.cityID = B .cityID SELECT can not only retrieve data from the table or view, but also query data from the result collection returned from other query statements. For example: SELECT A.AU_FNAME A.AU_LNAME FROM AUTHORS A, TITLEAUTHOR TA (SELECT TIMTLE_ID, TIM TIM TIM WHERE YTD_SALES> 10000) AS T I A.AU_ID = TA.AU_ID AND TA.TID = TA.AU_ID AND TA.TITLE_ID = T.TITLE_ID This example, Give a collected result of the result returned to the selection t, then retrieve data. (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 data of age greater than 20: Select * from userTable Where "20 WHERE clauses can include various conditional operators: comparison operators (size comparison):>,> =, =, <, <=, < >,!>,!
For example: limits the end of Publishing, using the Like '% Publishing' to limit A starting: Like '[a]%' limit: Like '[^ a]%' 4, null value judgment case where agn is NULL 5. Logical operators: Priority is NOT, AND, or (4) Sort by the resulting 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 userTable Order 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_statement Union [all] selectStatement [union [all] selectStatement] [... n] where selectStatement is the 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, information is queried by the connection operation to save the information stored in multiple tables. Connection operations bring great flexibility to users, 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 SELECT statement from the FROM clause or the WHERE clause, which is similar to that in the FROM clause indicates that the connection will help to distinguish the connection operation with 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 indicates the table name of the connection operation, the connection can be operated on the same table, or you can operate multiple tables , The connection to the same table is also called self-connection. Join_type indicates that the connection type can be divided into three: internal connections, external connections, and cross-connect.
Inner Join Using the comparative operator to perform a comparison operation of the data (some) 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 the connection operation indicates the connection condition, which is constructed by the columns and comparison operators, logical operators, and the like in the connected table. No matter which connection can be directly connected to Text, NText, and Image Data Type columns, it can be indirectly connected to these three columns. 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) Internal Connection Inquiry Inquiry Operation List and Connection The conditionally matched data line, which compares the column values of the column using the comparison operator. 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>,> =, <=, <,!>,!
Connect the forum content and author information as the left outer connection: SELECT A. *, B. * From luntan left join uteteable as b on a.username = B.username The following uses all the author in the city table And all the authors in the User table, as well as their city: SELECT A. *, B. * From city as a full outer join user as b on a.username = b.username (3) cross-connect cross-connect without WHERE The clause, it returns the two tables of the two tables that are connected to the Cartesian, returning to the number of data lines in the result collection equal to the number of data rows that meet the query conditions in the first table, multiplied by the query in the second table. Conditional data line number. In the case, there are 6 types of books in the Titles table, and 8 publishers in the Publishers table, the number of records retrieved in the following cross-connects will be equal to 6 * 8 = 48 lines. SELECT TYPE, PUB_NAME from Titles Cross Join Publishers ORDER BY TYPE [POST = 0] [/ Post] ----------------------------- ----------------------------
It is often turned online, often seeing some people in order to seek some SQL statements, now I especially collect some of the more classic SQLs you collect and share it with you.
1. Layer conversion - ordinary assumptions include Zhang Xuecheng's transcript (CJ) Name Subject Result Zhang Sanyang 80 sheets three mathematics 90 sheets three physics 85 Li Si language 85 Li Fudi Mathematics 92 Li Si physics 82 want to become a name Chinese mathematics Physical Zhang San 80 90 85 Li Si 85 92 82Declare @SQL VARCHAR (4000) SET @SQL = 'SELECT NAME'SELECT @SQL = @SQL ', SUM (Case Subject when '' Subject '' 'Then Result End [' Subject '] 'from (Select Distinct Subject from CJ) AS ASELECT @SQL = @ SQL ' from test group by name'exec (@SQL) 2. Ramature conversion - combined with table A, ID PID 1 1 1 2 1 3 2 1 2 2 3 1 How to make a table B: ID PID 1 1, 2, 3 2 1, 2 3 1 Create a merged function crete Ferg (@ID int) returns varchar (8000) asbegindeclare @str VARCHAR (8000) set @str = '' SELECT @ Str = @ Str ',' Cast (PID as varchar) from table a where id = @ idset @ Str = Right (@ Str, Len (@STR) -1) Return (@str) endgo - Call the custom function to get the result Select Distinct ID, DBO.fmerge (ID) from the FROM table A3. How to get all the column names of a data table is as follows: First get the data table from the SystemObject system table SYSTEMID, then then all column names of the data table in the syscolumn table.
SQL statement is as follows: declare @objid int, @ objname char (40) set @objname = 'tablename'select @objid = id from sysobjects where id = object_id (@objname) select' Column_name '= name from syscolumns where id = @objid Is it too simple to order by colid? Oh, I often use A.4. Change the user's password to modify others through the SQL statement, need sysadmin role exec sp_password null, 'newpassword', 'user' If the account is SA executes Exec sp_password null, 'newpassword', sa 5. How to determine which fields do not allow empty? Select column_name from information_schema.columns where is_null = 'no' and table_name = TABLENAME 6. How do I find a table with the same field in the database? a. Syrics known to the column name SELECT B.NAME AS TABLENAME, A.NAME As ColumnName from syscolumns a.id = B.ID and B.TYPE = 'u' and a.name = ' Your field name 'b. Unknown column names All column name Select O.Name as Tablename, S1.Name As ColumnName from syscolumns s1, sysobjects o where s1.id = o.id and o.type = 'U' and exists (SELECT 1 from s2.name and s1.name = s2.name and s1.id <> s2.id) 7. Query the XXX line data hypothesis the ID is the primary key: select * from (SELECT TOP XXX * From YourTable) aa where not exists (SELECT 1 from (SELECT TOP XXX-1 * from YourTable) bb Where aa.id =
