Basics

Create database

CREATE DATABASE database-name 

Delete database

drop database dbname

Backup sql server

1. establish Backup data device

    USE master
   EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
   

2. Start Backup

   BACKUP DATABASE pubs TO testBack 
   

Create 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

Delete new table

drop table tabname 

Add a column

Alter table tabname add column col type

 notes ： Columns cannot be deleted after they are added .DB2 The data type cannot be changed after the middle column is added , The only thing that can change is to increase varchar The length of the type .

Add primary key

Alter table tabname add primary key(col)

Delete primary key

Alter table tabname drop primary key(col)

Create index

create [unique] index idxname on tabname(col….)

Delete index

drop index idxname

 notes ： The index is immutable , If you want to change it, you must delete and rebuild .

Create view

create view viewname as select statement

Delete view

drop view viewname