Subtle SQL Statement Collection

When the SQL statement was written earlier, it was easy to forget some special usage, I hereby tidy up the SQL statement operation, convenient to write SQL convenient for myself, want to paste up, see together, at the same time hope everyone can make a lot of comments, also give me some better couplets, organize a "Subtle SQL Quick Search Handbook", Don't hesitate to enlighten me!

First, the basis

1, Description: Create a database


CREATE DATABASE database-name


2, Description: Delete Database


drop Database dbname


3, Description: Backing up SQL Server


---Create device
for backup data

Use master


EXEC sp_addumpdevice ' disk ', ' testback ', ' C:/mssql7backup/mynwind_1.dat '


---start Backup


BACKUP DATABASE pubs to Testback


4, Description: Create a new table


CREATE TABLE TabName (col1 type1 [NOT NULL] [primary key],col2 type2 [NOT NULL],..)


Create a new table from an existing table:


a:create table tab_new like Tab_old (Create a new table using the old table)


b:create table tab_new as Select Col1,col2 ... from tab_old definition only


5, Description: Delete new table


drop table TabName


6, Description: Add a column


Alter table tabname Add column col type


Note: The column will not be deleted after it has increased. The data type can not be changed when the column in the DB2 is added, and the only change is the length of the varchar type.


7, Description: Add primary key: Alter table TabName Add primary key (COL)


Description: Delete primary key: Alter table tabname drop primary key (COL)


8, Description: CREATE INDEX: Create [unique] index idxname on tabname (col ...)


Delete index: Drop index Idxname


Note: The index is not to be changed and you want to change the rebuild that must be deleted.


9, Description: Creating view: Create VIEW viewname as SELECT statement


Delete view: Drop view viewname


10, Description: A few simple basic SQL statements


selection: SELECT * from table1 where scope


insert: INSERT INTO table1 (field1,field2) VALUES (value1,value2)


Delete: Delete from table1 where scope


Update: Update table1 set field1=value1 where scope


Lookup: SELECT * FROM table1 where field1 like '%value1% '---the syntax of like is very subtle, look for information!


Sort: SELECT * from table1 ordered 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


Max: Select Max (field1) as MaxValue from table1


min: select min (field1) as MinValue from table1


11, Description: Several advanced query arithmetic words


a:union operator

The
UNION operator derives a result table by combining the other two result tables (such as TABLE1 and TABLE2) and eliminating any duplicate rows in the table. When all is used with union (that is, union ALL), duplicate rows are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2.


b:except operator

The
EXCEPT operator derives a result table by including all rows that are in TABLE1 but not in TABLE2, and all duplicate rows are eliminated. When all is used with EXCEPT (EXCEPT all), duplicate rows are not eliminated.


C:intersect operator

The
INTERSECT operator derives a result table by including only the rows in TABLE1 and TABLE2 and eliminates all duplicate rows. When all is used with INTERSECT (INTERSECT all), duplicate rows are not eliminated.


Note: Several query result rows that use an operator must be consistent.


12, Description: Using external connection


A, LEFT outer join:


left OUTER join (left connection): The result set includes a matching row for the join table and all rows of the left-attached 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:


Right outer join (right connection): The result set includes both matching connection rows for the join table and all rows of the right join table.


c:full outer join:


full outer join: Includes not only matching rows for symbolic join tables, but also all records in two connected tables.

Second, the promotion

1, Description: Replication table (only copy structure, source table name: A new table name: B) (Access available)
Law one: SELECT * into B from a where 1<>1
Method Two: Select top 0 * into B from a

2, Description: Copy table (copy data, source table name: A target table name: B) (Access available)
Insert into B (A, B, c) select d,e,f from B;

3, note: cross-database copy of the table (specific data using absolute path) (Access available)
Insert into B (A, B, c) Select d,e,f from B in ' specific database ' where condition
Example:.. From B in ' "&server.mappath (". ") & "/data.mdb" & "where.

4, Description: Subquery (table name 1:a table name 2:b)
Select A,b,c from a where a in (select D from B) or: Select A,b,c from a where a where a (1,2,3)

5. Description: Display article, author and final reply time
Select A.title,a.username,b.adddate from Table A, (select Max (adddate) adddate from table where Table.title=a.title) b

6, Description: External connection query (table name 1:a table name 2: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

7, Description: Online view query (table name 1:a)
SELECT * FROM (select A,b,c from a) T where t.a > 1;

8, Description: Between use, between limit the query data range includes the boundary value, not between does not include
SELECT * FROM table1 where time between time1 and time2
Select A,b,c, from table1 where a is not between numeric 1 and value 2