Classic SQL statements

Usage of as in SQL and some classic SQL statements 2008-08-21 21:55 summarize some of the SQL statements used or encountered in the work, hope to share with you, but also hope that you can provide more sophisticated SQL statements .....
1. Delete table1 from (SELECT * from table2) as T2 where table1.id=t2.id
2. TRUNCATE TABLE table1 (does not record in transaction log, faster than delete table, but cannot activate 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 percent record
6, select Id,column1 * column2 as column from table1 ' can be counted to understand the use of AS
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) ' This should be faster than not '
9. Select Table1.id from table1,table2 where table1.id<>table2.id ' see Compound 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 ' somewhat similar [1 ] ...
11. Select * FROM table1 where column1 like ' [a]% ' or like ' [^b]% '
12. Select @column1 =column1 from Table1;select @column1 as Column1 ' store to custom variable
13. Select * FROM table1 where contains (column1, ' char1 or char2* ') ' Full-text index
14. Select * FROM table1 where contains (Column1, ' before there is near after there is ')
15. Select * FROM table1 where contains (Column1, ' formsof (inflectional,go) ') ' derives
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 support text does not support expression search
18. Insert INTO table1 select Column1,count (column1) from table2 GROUP by Column1 ' statistics

-----------------------------------------------------------------------------------------
1 Description: Copy table (copy structure only, source table name: A new table name: b)
Sql:select * to B from a where 1<>1
2 Description: Copy table (copy data, source table name: A target table name: b)
Sql:insert to B (A, B, c) select d,e,f from B;

3 Description: Displays the article, the author, and the 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: Outer join query (table name 1:a table name 2:b)
Sql:select a.a, A.B, A.C, B.C, B.D, B.f from a left off JOIN b on a.a = B.C

5 Description: Schedule Five minutes advance reminder
Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5

6 Description: Two associated tables to delete information that is not already in the secondary table in the main table
Sql:
Delete from info where NOT EXISTS (SELECT * from Infobz where Info.infid=infobz.infid)

7 Description:
Statistics on telephone charges for each unit from the database for one year (two table sources for telephone fee quotas and phone bills)
Sql:
SELECT A.userper, A.tel, A.standfee, To_char (a.telfeedate, ' yyyy ') as Telyear,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' A.factration ") as JAN,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' A.factration ") as FRI,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' A.factration ") as MAR,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' "A.factration") as APR,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' a.factration ") as May,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' ", A.factration)" as Jue,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' "," a.factration ") as JUL,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' AGU ', a.factration)
SUM (Decode (To_char (a.telfeedate, ' mm '), ' a.factration ') as SEP,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' ten ', a.factration)) as OCT,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' one ', a.factration)) as NOV,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' m ', a.factration)) as DEC
From (SELECT a.userper, A.tel, A.standfee, B.telfeedate, b.factration
From Telfeestand A, Telfee b
WHERE A.tel = B.telfax) A
GROUP by A.userper, A.tel, A.standfee, To_char (a.telfeedate, ' yyyy ')

8 Description: Four table linked questions:
Sql:select * from a left inner join B in a.a=b.b right inner join C on A.A=C.C inner join D on A.A=D.D where ...

9 Description: Get the smallest unused ID number in the table
Sql:
Select (Case if EXISTS (SELECT * from Handle b WHERE b.handleid = 1) then MIN (Handleid) + 1 ELSE 1 END) as Handleid
From Handle
WHERE not Handleid in (SELECT a.handleid-1 from Handle a)

10 Description: Fuzzy Query, single match (the dash represents what you want to match)

SELECT * FROM table where field1 like ' A_b_c '

11 Description: Usage of AS

Select Id,column1 * column2 as column from table1

Classic SQL statements