SQL Server Advanced SQL Summary

----------------------------------------------------------------------Select--------------------------------------------- -----------------------------------------------------------------------

1. Query all the data in the table

-----------select * FROM table name

-----------Select all Id,name from table name

-----------Select Id,name from table name

2. Query the exact matching data in the table

-----------SELECT * FROM Table A, table B where table a.id= table b.ID

----------SELECT * FROM Table a INNER JOIN table B on table a.id= table b.ID

3. Data filtering "full Filter/Field filter"

-----------SELECT DISTINCT * FROM Table A

-----------SELECT DISTINCT (field) from Table A

4. Data statistics

-----------Select COUNT (*) from Table A

-----------Select COUNT (1) from Table A

-----------Select count (field) from Table A

-----------Select COUNT (distinct field) from Table A

5, query the first n data

-----------Select top N * FROM Table A

-----------Select TOP (n) * from C ORDER BY id DESC

6, in and not in the query with exists and NOT exists

----------SELECT * FROM Table A where NOT EXISTS (SELECT * from table B where id = table a.id)

----------SELECT * FROM Table A where exists (SELECT * from table B where id = table a.id)

---------SELECT * FROM table a where ID in (select id from table B)

---------SELECT * FROM table A where ID not in (SELECT ID from table B)

7. Usage of and and or in the database

--------SELECT * FROM Table A where field a= ' and field b= '

--------SELECT * FROM Table A where field a= ' or field b= '

8, Union, and Union all do union operations

--------Select ID, name from table A where field like '% ' union select ID, field from table A where id = 4---------------------Union does not repeat

--------SELECT * FROM Table a where name like '% ' union ALL SELECT * FROM Table a---------------------union duplicate

---------SELECT * FROM Table A where field like '% ' intersect select * from Table a--------------------------intersection (same part)

----------SELECT * FROM Table A where field like '% ' except select * from Table A where field like '% '-------------removed (same part)

9. Aggregation function

---------Select max (field) Table A

---------Select min (field) Table A

---------Select count (field) Table A

---------Select AVG (field) Table A

---------Select SUM (field) Table A

---------Select var (field) Table A

10. Date function

-------Select DATEADD (Day, 3, GetDate ())

-------Select DATEADD (year, 3, GetDate ())

-------Select DATEADD (Hour, 3, getDate ())

---------------------Calculate the return data----------------

----------Select DateDiff (Day, ' 2016-01-01 ', GetDate ())

----------Select DateDiff (Second, ' 2016-01-01 00:00:00 ', GetDate ())

----------Select DateDiff (Hour, ' 2016-01-01 00:00:00 ', GetDate ())

----------Select Datename (Month, getDate ())

----------Select Datename (minute, getDate ())

----------Select Datename (Weekday, getDate ())

---------Select DatePart (Month, getDate ())

---------Select DatePart (Weekday, getDate ())

---------Select DatePart (Second, getDate ())

---------Select Day (getDate ())

---------Select Day (' 2016-01-01 ')

--------Select Month (getDate ())

--------Select month (' 2016-01-01 ')

--------Select Year (GetDate ())

--------Select year (' 2016-01-01 ')

--------Select GetDate ()

--------Select getUTCDate ()

11. Mathematical Functions

-------Select Pi ()

-------Select Rand, RAND (), RAND (), Rand ()

-------Select round (rand (), 3), round (rand (100), 5)

-------Select Round (123.456, 2), round (254.124,-2)

-------Select Round (123.4567, 1, 2)

12. String

------Select Len (String)

------Select Reverse (String)

------Select Left (String, 4)

------Select Right (string, 4)

------Select LOWER (' abc '), Lower (' abc ')

------Select UPPER (' abc '), Upper (' abc ')

13. Create a Database

-------CREATE DATABASE name

14. Delete Database

------DROP Database Name

15. Backup SQL

-----Use master EXEs sp_addumpdevice ' database ', ' Backup name ', ' Backup address '

16. Start Backup

-----BACKUP Database Pubs to ' Back Up name '

17. Create a table

------CREATE TABLE Table name

18. Delete a table

------DROP TABLE Name

19. Add Columns

------ALTER TABLE name add column col column name

20. Create a View

-------CREATE VIEW View name

21. Delete View

-----Drop View Name

22. Copy Table Data

-------SELECT * into table B from table A

------Select top 0 into table B from table A

23, the use of between

------SELECT * FROM Table a where time between time1 and time2

-----SELECT * FROM table A where ID between ID1 and ID2

SQL Server Advanced SQL Summary