Common Classic SQL Statement Daquan (Basic)

First, the basic

1. Description: Create Database

CREATE DATABASE Database-name

2. Description: Delete Database

Drop Database dbname

3. Description: Back up SQL Server

---Create a backup data

Device 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 (Col1type1 [NOT NULL] [primary key],col2 type2 [NOT NULL],..)

To create a new table from an existing table:

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

B:create table Tab_new asselect col1,col2 ... from tab_old definition only

5. Description: Delete new table

drop table TabName

6. Description: Add a column

Alter table tabname addcolumn col type

Note : columns cannot be deleted after they are added. DB2 the column plus the data type can not be changed, the only change is to increase 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:dropindex idxname

Note: The index is immutable and you must remove the rebuild if you want to change it.

9. Description: Create a View :

CREATE VIEW viewname AS SELECT statement

Delete View : Drop Viewviewname

10. Description: A few simple basic SQL statements

Select:select * FROM table1 where range

insert: INSERT INTOtable1 (field1,field2) VALUES (value1,value2)

Delete:delete from table1 where range

updated : Update table1 set field1=value1 where range

Find : SELECT * fromtable1 where field1 like '%value1% '---the syntax of like is very subtle, check the information!

sort : Select *from table1 ORDER by FIELD1,FIELD2 [DESC]

Total : Select Count Astotalcount from table1

summation : Selectsum (field1) as Sumvalue from table1

average : Selectavg (field1) as Avgvalue from table1

Maximum : Selectmax (field1) as MaxValue from table1

min : selectmin (field1) as MinValue from table1

11. Description: Several advanced query operation words

A:union operator

The UNION operator derives a result table by combining the other two result tables (for example, TABLE1 and TABLE2) and eliminating any duplicate rows in the table. When all is used with the 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 in TABLE1 but not in TABLE2 and eliminating all duplicate rows. 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 rows in TABLE1 and TABLE2 and eliminating all duplicate rows. When all is used with INTERSECT (INTERSECT all), duplicate rows are not eliminated. Note: Several query result rows that use an operation word must be consistent.

12. Description: Use external connection

A, left(outer) Join:

Left OUTER join (left JOIN): The result set includes a matching row for the join table and all rows of the left join table.

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

B:right (outer) Join:

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

C:full/cross (outer) Join:

Full outer joins: Includes not only the matching rows of the symbolic join table, but also all the records in the two join tables.

12, Group: Groupby:

A table, once the grouping is complete, you can only get group-related information after the query.

Group-related information:(statistical information) Count,sum,max,min,avg grouping criteria)

When grouping in SQL Server: You cannot use fields of type Text,ntext,image as a field in the selecte statistic function, and cannot be combined with normal fields;

13, the operation of the database:

separate database : sp_detach_db;

Additional database :sp_attach_db followed by indicates that the append requires a full path name

14. How to modify the name of the database:

Sp_renamedb ' Old_name ', ' new_name '

Common Classic SQL Statement Daquan (Basic)