1. Description: database creation tutorial
Create database-name
2. Description: delete a database.
Drop database dbname
3. Description: Back up SQL server
--- Create a device for the backup data
Use master
Exec sp_addumpdevice 'disk', 'testback', 'c: mssql7backupmynwind_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 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 the new table drop table tabname
6. Description: Add a column.
Alter table tabname add column col type
Note: Columns cannot be deleted after they are added. After columns are added to db2, the data type cannot be changed. The only change is to increase the length of the varchar type.
7. Description: add a primary key: alter table tabname add primary key (col)
Delete a primary key: alter table tabname drop primary key (col)
8. Description: create an index: create [unique] index idxname on tabname (col ....)
Delete index: drop index idxname
Note: The index cannot be changed. To change the index, you must delete it and recreate it.
9. Description: create view viewname as select statement
Delete view: drop view viewname
10. Description: several simple basic SQL statements
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 %' --- the like syntax is very subtle, query information!
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
Max: select max (field1) as maxvalue from table1
Min: select min (field1) as minvalue from table1 1 2 3 4