1、说明:创建数据库 CREATE DATABASE database-name
2 R4 w o! w: |, { 2、说明:删除数据库
/ p+ s2 R1 {! u9 W$ o$ Q drop database dbname
7 s h' P7 R1 W1 | 3、说明:备份sql server
# F' ?6 \3 Q! f4 ]3 w --- 创建 备份数据的 device; W, d3 c1 p. S' G0 F, H
USE master
. O+ H3 K4 Q4 ?1 H' U0 \ EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
3 u/ J. r: }- _' Z; B: @. w --- 开始 备份' c( @, V G2 h& w- H
BACKUP DATABASE pubs TO testBack. s( ^3 z" u- H" @$ D: R L1 I* S
4、说明:创建新表) M' Q' t ~% S& P8 L, T6 `6 y
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
& N5 Z) C2 ?* Z# ]4 g5 S6 Y, o 根据已有的表创建新表:) p% L& |; o4 D. i# r& }
A:create table tab_new like tab_old (使用旧表创建新表)' `* q2 | _, R; v
B:create table tab_new as select col1,col2… from tab_old definition only$ S% d! C3 D, h( K
5、说明:删除新表 z$ q: J: H( a$ Q" W
drop table tabname# C6 e8 V2 E7 h3 g
6、说明:增加一个列
6 m% z9 ~2 P: K& o9 ]5 w1 E/ i6 @ Alter table tabname add column col type
* Z$ S& D; i B1 Z* F 注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。4 A( p, X( ^3 B! j
7、说明:添加主键: Alter table tabname add primary key(col)9 `9 M( H4 u$ _8 T; E: p' r
说明:删除主键: Alter table tabname drop primary key(col) d0 }: f' H, S( c0 g
8、说明:创建索引:create [unique] index idxname on tabname(col….)5 C& {0 ~& a0 x! L8 C
删除索引:drop index idxname
- b9 j) t! O& E 注:索引是不可更改的,想更改必须删除重新建。6 i2 e- G9 x0 \+ X2 g
9、说明:创建视图:create view viewname as select statement
- C J* c# j& D! P) b! h4 f! Q; L 删除视图:drop view viewname) W* z' b1 N( p. y7 o y
10、说明:几个简单的基本的sql语句
5 D1 L$ r a! a0 I$ k$ |! l 选择:select * from table1 where 范围
2 _4 N U; s% E 插入:insert into table1(field1,field2) values(value1,value2)
2 U, G* \/ X$ c0 F) y% g b 删除:delete from table1 where 范围% }/ ?' m/ S: G" H O0 Z
更新:update table1 set field1=value1 where 范围
1 X9 f3 c, L2 t8 W7 V& p 查找:select * from table1 where field1 like ’%value1%’ ---like的语法很精妙,查资料!
& L( J' T5 g3 F' ~4 y 排序:select * from table1 order by field1,field2 [desc]
& H& U: X8 F) S, O! u, x 总数:select count as totalcount from table1% |% J4 z/ \/ n1 n1 G9 W7 f' E
求和:select sum(field1) as sumvalue from table1
+ k7 N/ N2 P5 K2 K8 m5 \3 ~ 平均:select avg(field1) as avgvalue from table1: A4 u) e+ @ c0 W3 a @7 c
最大:select max(field1) as maxvalue from table1- V/ I( v- ?' [1 A; H& E. ?
最小:select min(field1) as minvalue from table1 |