Summary of some simple but commonly used items in SQL

In: equivalent join, used to find records with the same fields in multiple tables
Not in: Non-equivalent join, used to find records that do not exist
Inner join: inner join, mainly used to find records that all meet the conditions
Left join: left join. It is mainly used to find the left join and null expression is used for the right join.
Right join: Right join. It is mainly used to find the right join. null is used to express the result that is not on the left.
Order by: ascending ASC descending DESC
Group by: group by field (for example, by class), use a set function.
Having: determines the grouping conditions. Make sure that each field uses a set function.
Compute by: the clause allows you to view both the detail row and the summary row with the same SELECT statement. You can calculate the summary value of the sub-group or the summary value of the entire result set.
The name of the row aggregate function. For example, sum, AVG, Min, Max, or count.
Insert into: insert the query statement to the new table.

Where XX in ('A', 'bb ', 'cc ')
Equivalent to where xx = 'A' or xx = 'bb 'or xx = 'cc'

 

Usage comparison of in and exists in SQL this article was updated on: February 22, October 31. Source: This site | category: sqlserver
As a subquery:
In:
1. Select * from family where ID in (select [ID] From p_info)
The returned result is the paples_info result set containing all IDs in the p_info table.
Exists:
2. Select * from family where exists (select [ID] From p_info)
The returned result is p_info. The family result set with the same data exists. (The same column is not specified)
3. Select * from family where exists (select [ID] From p_info where family. [ID] = p_info. [ID])
The returned result is the same as (1), which specifies the result of ID column Association.
As the primary query Condition
In:
Select * from family where ID in ('20140901', '20160901 ')
Exists:

 

Query some classic SQL statements found in the as usage in SQL
Keywords: SQL
I would like to summarize some SQL statements used or encountered in my work and share them with you. I also hope you can provide more exquisite SQL statements .....
1. Delete Table1 from (select * From table2) as T2 where table1.id = t2.id
2. truncate table Table1 (not recorded in the transaction log, which is faster than the delete table but cannot activate the trigger)
3. Update Table1 set column = column + 1 where id = (select ID from table2)
4. Update Table1 set column = column + 1 from Table1, Table2 where table1.id = table2.id
5. Select Top N [percent] * From table1' output percentage record
6. Select ID, column1 * column2 as column from Table1 'can be used to understand the usage of.
7. Select * From Table1 where column1 like 'SQL # _ g_o 'escape' # ''single match
8. Select table1.id from Table1 where not exists (select table2.id from Table2 where table1.id = table2.id) 'should be faster than not in

Some
9. Select table1.id from Table1, Table2 where table1.id <> table2.id 'to view the composite query Mechanism
10. Select table1.id from Table1, Table2, (select ID from table3) as T3 where table1.id = table2.id and table2.id = t3.id 'are somewhat similar

[1 ......
11. Select * From Table1 where column1 like '[a] %' or like '[^ B] %'
12. Select @ column1 = column1 from Table1; select @ column1 as column1 'stored in custom Variables
13. Select * From Table1 where contains (column1, 'char1 or char2 * ')' Full-text index
14. Select * From Table1 where contains (column1, 'near and near ')
15. Select * From Table1 where contains (column1, 'formsof (inflectional, go) 'is derived
16. Select * From Table1 where contains (description, 'isabout (Apple weight (. 9), boy weight (. 8), China weight (. 7) 'weight
17. Select * From Table1 where freetext (column1, 'Char ')' only supports text and does not support expression search
18. insert into Table1 select column1, count (column1) from Table2 group by column1 'Statistics

Bytes -----------------------------------------------------------------------------------------
1 Description: copy a table (only copy structure, source table name: a new table name: B)
SQL: Select * into B from a where 1 <> 1
2 Description: copy a table (copy data, source table name: A target table name: B)
SQL: insert into B (a, B, c) Select D, E, F from B;

3 Note: displays the article, Submitter, and last reply time
SQL: select a. Title, A. username, B. adddate from Table A, (select max (adddate) adddate from table where table. Title = A. Title) B

4 Description: External join query (table name 1: Table A name 2: B)
SQL: select a. a, a. B, A. C, B. C, B. D, B. F from a left out join B on A. A = B. C

5 Note: Five minutes ahead of schedule reminder
SQL:Select * from Schedule where datediff ('minute ', F Start Time, getdate ()> 5

6 Note: two associated tables are used to delete information that is not in the primary table.
SQL:
Delete from info where not exists (select * From infobz where info. INFID = infobz. INFID)

7 Note:
Go to the telephone fee statistics of each unit from the database for one year (two sources of telephone quota and telephone bill)
SQL:
Select a. userper, A. Tel, A. standalone, to_char (A. telfeedate, 'yyyy') as telyear,
     Sum (decode (to_char (A. telfeedate, 'mm'), '01', A. factration) as Jan,
     Sum (decode (to_char (A. telfeedate, 'mm'), '02', A. factration) as Fri,
     Sum (decode (to_char (A. telfeedate, 'mm'), '03', A. factration) as MAR,
     Sum (decode (to_char (A. telfeedate, 'mm'), '04 ', A. factration) as Apr,
     Sum (decode (to_char (A. telfeedate, 'mm'), '05 ', A. factration) as may,
     Sum (decode (to_char (A. telfeedate, 'mm'), '06', A. factration) as Jue,
     Sum (decode (to_char (A. telfeedate, 'mm'), '07 ', A. factration) as Jul,
     Sum (decode (to_char (A. telfeedate, 'mm'), '08 ', A. factration) as Agu,
     Sum (decode (to_char (A. telfeedate, 'mm'), '09', A. factration) as SEP,
     Sum (decode (to_char (A. telfeedate, 'mm'), '10', A. factration) as Oct,
     Sum (decode (to_char (A. telfeedate, 'mm'), '11', A. factration) as Nov,
     Sum (decode (to_char (A. telfeedate, 'mm'), '12', A. factration) as Dec
From (select a. userper, A. Tel, A. standalone, B. telfeedate, B. factration
       From telfeestand A, telstmb
       Where a. Tel = B. telfax)
Group by A. userper, A. Tel, A. standalone, to_char (A. telfeedate, 'yyyy ')

8 notes: four table join query problems:
SQL: Select * from a left inner join B on A. A = B. B right inner join C on A. A = c. cInner join D on A. A = D. D where .....

9 Note: Obtain the smallest unused ID number in the table.
SQL:
Select (case when exists (select * From handle B where B. handleid = 1) Then min (handleid) + 1 else 1 end) as handleid
FromHandle
Where not handleid in (select a. handleid-1 from handle)

10 Description: fuzzy query and single-word matching (the dash represents the content to be matched)

Select * from table where field1 like 'A _ B _c'

11. Usage of

Select ID, column1 * column2 as column from Table1

 

SQL convert usage

Function
Returns the expression converted to the provided data type.

Syntax
Convert (data type, expression [, format-style])

Parameters
Data Type  The data type that the expression will convert.

Expression  The expression to be converted.

Format-style  Format-style is the style code that describes the date format string to be used for converting the string to the date or time data type and the opposite conversion process.

The value of the format-style parameter has the following meanings: Century (yy) is not included)Century included (YYYY)Output
-0 or 100Mmm dd yyyy hh: NN: SS: SSS am (or pm)
1101Mm/DD/yy [YY]
2102[YY] yy. Mm. dd
3103Dd/mm/yy [YY]
4104Dd. mm. yy [YY]
5105DD-mm-yy [YY]
6106Dd Mmm YY [YY]
7107Mmm DD, YY [YY]
8108Hh: NN: SS
-9 or 109Mmm dd yyyy hh: NN: SS: sssam (or pm)
10110Mm-dd-yy [YY]
11111[YY] YY/MM/dd
12112[YY] yymmdd
13113Dd Mmm YYY hh: NN: SS: SSS (24-hour system, European default time + millisecond, 4-digit year)
14114Hh: NN: SS: SSS (in 24-hour format)
20120Yyyy-mm-dd hh: NN: SS: SSS (24-hour system, ODBC specification, 4-digit year)
21121Yyyy-mm-dd hh: NN: Ss. Sss (24-hour system, ODBC standard plus millisecond, 4-digit year)

 

Use convert:

Convert (data_type [(length)], expression [, style])

Select convert (varchar, getdate (), 120)
11:06:08

Select Replace (replace (convert (varchar, getdate (), 120 ),"'-"',"'"'),"'"', "'"'),"':"',"'"')
20040912110608

Select convert (varchar (12), getdate (), 111)
2004/09/12

Select convert (varchar (12), getdate (), 112)
20040912

Select convert (varchar (12), getdate (), 102)
2004.09.12

Select convert (varchar (12), getdate (), 101)
09/12/2004

Select convert (varchar (12), getdate (), 103)
12/09/2004

Select convert (varchar (12), getdate (), 104)
12.09.2004

Select convert (varchar (12), getdate (), 105)
12-09-2004

Select convert (varchar (12), getdate (), 106)
12 09 2004

Select convert (varchar (12), getdate (), 107)
09 12,200 4

Select convert (varchar (12), getdate (), 108)
11:06:08

Select convert (varchar (12), getdate (), 109)
09 12, 2004 1

Select convert (varchar (12), getdate (), 110)
09-12-2004

Select convert (varchar (12), getdate (), 113)
12 09 2004 1

Select convert (varchar (12), getdate (), 114)
11:06:08. 177

Convert convert (INT, RID) to int type