一、基础(标红部分均为库/表/索引/视图名)
创建删除有关:
创建数据库: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'