Interview may be asked

ASC Sort in ascending order
Desc Sort in descending order

The following statements are:MSSQLStatement.Access.

SQLCategory:
DDL-Data Definition Language(Create,Alter,Drop,Declare)
DML-Data manipulation language(Select,Delete,Update,Insert)
DCL-Data Control Language(Grant,Revoke,Commit,Rollback)

First , Brief introduction to basic statements:
1 Description: creates a database.
Create Database database-name
2 Description: deletes a database.
Drop database dbname
3 Description: Backup SQL Server
--- Create Backup Data Device
Use master
Exec sp_addumpdevice 'disk', 'testback', 'c: \ mssql7backup \ mynwind_1.dat'
--- Start Backup
Backup database pubs to testback
4 Description: Creates a new table.
Create Table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],...)
Create a new table based on an existing table:
A : Create Table tab_new like tab_old ( Use the old table to create a new table )
B : Create Table tab_new as select col1, col2... From tab_old definition only
5 Description: delete a new table. Drop table tabname
6 , Note: Add a column
Alter table tabname add column Col type
Note: Columns cannot be deleted after they are added. DB2 The data type cannot be changed when columns are added. The only change is increase. Varchar Type length.
7 Note: Add a primary key: Alter table tabname add primary key (COL)
Note: To delete a primary key: Alter table tabname drop primary key (COL)
8 Note: Create an index: Create [unique] index idxname on tabname (COL ....)
Delete An index: Drop index idxname
Note: The index cannot be changed. To change the index, you must delete it and recreate it.
9 Note: Create a view: Create view viewname as select statement
Delete View: Drop view viewname
10 Notes: a few simple and basic SQL Statement
Select: Select * From Table1 where Range
Insert: Insert into Table1 (field1, field2) values (value1, value2)
Delete: Delete from Table1 where Range
Update: Update Table1 set field1 = value1 where Range
Search: Select * From Table1 where field1 like '% value1 %' --- like The syntax is exquisite. !
Sort: Select * From Table1 order by field1, field2 [DESC]
Total: Select count as totalcount from Table1
Sum: Select sum (field1) as sumvalue from Table1
Average: Select AVG (field1) as avgvalue from Table1
Maximum: Select max (field1) as maxvalue from Table1
Minimum: Select min (field1) as minvalue from Table1
11 Description: several advanced query Operators
A : Union Operator
Union The operator combines two other result tables (for example Table1 And Table 2 ) And removes any duplicate rows from the table to generate a result table. When All With Union When used together (that is Union all ). Duplicate rows are not eliminated. In either case, each row of the derived table is not from Table1 Is from Table 2 .
B : Except Operator
Except By including all Table1 But not Table 2 And eliminate all duplicate rows to derive a result table. When All With Except When used together (Snapshot t all) . Duplicate rows are not eliminated.
C : Intersect Operator
Intersect Only include Table1 And Table 2 All rows in the table and all duplicate rows are eliminated to derive a result table. When All With Intersect When used together (Intersect all) . Duplicate rows are not eliminated.
Note: The query results of several computation words must be consistent.
12 Note: use external connections
A , Left Outer Join :
Left Outer Join (left join): the result set contains the matched rows in the connected table, and all rows in the left connected table.
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
B : Right outer join:
Outer right connection ( Right join ) : The result set includes both the matched rows in the connected table and all rows in the right connected table.
C : Full outer join :
Full outer join: includes not only matching rows in the symbolic join table, but also all records in the two join tables.

Secondly, let's take a look at some goodSQLStatement
1Description: copy a table.(Copy only Structure,Source Table Name:ANew table name:B) (AccessAvailable)

Method 1:Select * into B from a where 1 <> 1
Method 2:Select top 0 * into B from
2Description: copy a table.(Copy Data,Source Table Name:ATarget Table Name:B) (AccessAvailable)
Insert into B (a, B, c) Select D, E, F from B;

3Description: Table Copying across Databases(Absolute path for specific data) (AccessAvailable)
Insert into B (a, B, c) Select D, E, F from B in'Specific Database'WhereCondition
Example:.. From B in '"& server. mappath (". ") &" \ data. mdb "&" 'where ..

4Description: subquery(Table Name1:ATable Name2:B)
Select a, B, c from a where a in (select D from B)Or: Select a, B, c from a where a in (1, 2, 3)

5Description: DisplayArticle, Submitted and last reply time
Select a. Title, A. username, B. adddate from Table A, (select max (adddate) adddate from table where table. Title = A. Title) B

6Description: Query external connections(Table Name1:ATable Name2:B)
Select a. a, a. B, A. C, B. C, B. D, B. F from a left out join B on A. A = B. C

7Description: Online View query(Table Name1:A)
Select * from (select a, B, c from a) t where T. A> 1;

8Note:BetweenUsage,The boundary value is included in the data range query., NotNot included
Select * From Table1 where time between time1 and time2
Select a, B, c, from Table1 where a notValue1 andValue2

9Note:InUsage
Select * From Table1 where a [not] In ('Value1 ','Value2 ','Value4 ','Value6 ')

10Description: two associated tables are used to delete information that is not in the secondary table.
Delete from Table1 where not exists (select * From Table2 where table1.field1 = table2.field1)

11Notes: four table join query problems:
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 .....

12Note: Five minutes ahead of schedule reminder
SQL: Select * fromScheduleWhere datediff ('minute ', FStart Time, Getdate ()> 5

13Description: OneSQLStatement processing database Paging
Select top 10 B. * from (select top 20Primary key field,Sorting FieldFromTable NameOrderSorting FieldDESC),Table NameB Where B.Primary key field=.Primary key fieldOrder by.Sorting Field

14Note: before10Records
Select top 10 * Form Table1 whereRange

15, Description: select in each groupBTheAAll information of the largest record(This method can be used for monthly rankings of forums.,Analysis of monthly hot selling products,Rank by subject score,And so on..)
Select a, B, c from tablename Ta where a = (select max (a) from tablename TB where TB. B = TA. B)

16, Note: including allTableaBut notTablebAndTablecAnd eliminate all duplicate rows to derive a result table.
(Select a from tablea) Before t (select a from tableb) Before t (select a from tablec)

17Description: Random Removal10Data entries
Select top 10 * From tablename order by newid ()

18Description: randomly selected records
Select newid ()

19Description: delete duplicate records
Delete from tablename where id not in (select max (ID) from tablename group by col1, col2 ,...)

20Description: Lists All table names in the database.
Select name from sysobjects where type = 'U'