删除数据库 drop database database-name
创建新表格
- create table tablename (col1 type1 [not null] [primary key], col2 type2 [not null], ...)
- select top 0 * into tablenew from tableold or select * into b from a where 1<>1 - copy the schema without data
- select * into tablenew from tableold or insert into b(a,b,c) select d,e,f from b - copy the schema with data
增加一列 alter tablename add columnname type
设置主键 alert tablename add primary key (columnname)
创建视图 create view viewname select statement
删除视图 drop view viewname
选择 select * from tablename where criteria
select a,b,c from tablename where a in (select d from tableb) or select a,b,c from tablename where a in (1,2,3)
select a.title, a.username, b.adddate from tablea, (select max(adddate) adddate from table where table.title=a.title) b
select * from tablename where a [not] in (value1, value2, value3...)
select top 10 * from tablename
插入 insert into tablename(col1, col2, ...) values (value1, value2, ...)
更新 update tablename set col1=value1 where criteria
查找 select * from tablename where col1 like '%value1%'
排序 select * from tablename order by col1, col2 [desc] ASC 升序，DESC降序
总数 select count(*) as totalcount from tablename
求和 select sum(col1) as sumvalue from tablename
平均 select avg(col1) as avgvalue from tablename
最大 select max(col1) as maxvalue from tablename
最小 select min(col1) as minvalue from tablename
between
select * from tablename where time between a and b
select * from tablename where time not between a and b
删除主表中已经在副表中没有的信息
delete from table1 where not exists (select * from table2 where table1,field1=table2.field2)
自增列
Create table Northwind
(CategoryID int identity(1,1) not null primary key,
CategoryName Nvarchar not null,
Description NvarChar not null,
Picture nvarchar)