Usage of As in SQL and some classic SQL statements

Usage of As in SQL and some classic SQL statements

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

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 'is somewhat similar to [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 = B. B right inner join C on. A = C. C inner join D on. 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

From handle

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