SQL有关指令总汇

一、基础(标红部分均为库/表/索引/视图名)

创建删除有关:

创建数据库:creat database dbname

删除数据库:drop database dbname

创建新表: create table tbname(col1 type1[not null][primary key],col2 type2[not null],...)

根据已有的表创建新表:

完全相同:create table tb_new like tb_old

部分相同:create tabke tb_new as select col1,col2...from tb_old

删除表: drop table tbname

增加一个列: alter table tbname add column col type

添加主键:alter table tbname add primary key(col)

删除主键:alter table tbname drop primary key(col)

创建索引:creat [unique] index idxname on tbname(col ...)

删除索引:drop index idxname    (注:索引是不可更改的,要修改必须删除重建)

创建视图:creat view viewname as select statement

删除视图:drop view viewname

基础语句:

选择: select * from table1 where (范围)

插入:insert into table1(field1,field2)values(value1,value2...)

删除:delete from table1 where (范围)

更新:update table1 set field1 = value1 where (范围)

查找:select * from table1 where field1 like ...

排序:select * from table1 order by field1 ,field2[desc]//如果不用关键字desc默认为升序

总数:select count as totalcount from table1

求和:select sum(field1) as sumvalue from table1

平均:select avg(fied1) as avgvalue from table1

最大:select max (field1) as maxvalue from table1

最小:select min (field1) as minvalue from table1

分离数据库: sp_detach_db+表名

附加数据库:sp_attach_db+完整的路径名

修改数据库的名称: sp_renamedb 'old_name','new_name'