常用sql语句

create database yongfa365_com
on
( name = yongfa365_comt,
  filename = 'd:\yongfa365_com.mdf',
  size = 4,
  maxsize = 10,
  filegrowth = 1
)


--删数据库
drop database yongfa365_com

--备份数据库
backup database yongfa365_com to disk='d:\yongfa365_com.bak'

--批量收缩所有数据库  适用SQL Server 2000/2005
DECLARE cur CURSOR FOR SELECT name FROM Master..SysDatabases WHERE name NOT IN ('master','model','msdb','Northwind','pubs','tempdb')
DECLARE @tb SYSNAME

OPEN cur
FETCH NEXT FROM cur INTO @tb
WHILE @@fetch_status = 0
    BEGIN
        DUMP TRANSACTION  @tb  WITH NO_LOG
        BACKUP LOG  @tb WITH NO_LOG
        DBCC shrinkdatabase(@tb)
        FETCH NEXT FROM cur INTO @tb
    END
CLOSE cur
DEALLOCATE cur


--批量收缩所有数据库  适用所有SQL Server
DECLARE cur CURSOR FOR SELECT name FROM Master..SysDatabases WHERE name NOT IN ('master','model','msdb','Northwind','pubs','tempdb')
DECLARE @tb SYSNAME

OPEN cur
FETCH NEXT FROM cur INTO @tb
WHILE @@fetch_status = 0
    BEGIN
        EXEC('ALTER DATABASE '+@tb+' SET RECOVERY SIMPLE;')
        DBCC shrinkdatabase (@tb);
        EXEC('ALTER DATABASE '+@tb+' SET RECOVERY FULL;')

        FETCH NEXT FROM cur INTO @tb
    END
CLOSE cur
DEALLOCATE cur


--删除数据库日志文件（有时能删几十G）
DBCC ERRORLOG
GO 6

--------------------------数据库操作--------------------------


----------------------------表操作----------------------------
--删除表
drop table movies

--SQL Server通用建表结构
Create Table [test] (
  [Id] int primary key identity(1,1),--ID，主键，自动号
  [txtTitle] nvarchar(255),--标题
  [txtContent] nvarchar(MAX),--内容
  [Adder] nvarchar(20),--添加人
  [AddTime] datetime Default (getdate()),--提交时间
  [ModiTime] datetime Default (getdate()),--修改时间
  [Hits] int Default (0),--点击数
  [Flags] int Default (0) ,--标识
  [SortID] int Default (0)--排序号
 )


--SQLite建表
Create Table [test] (
  [Id] integer Primary key not null,
  [txtTitle] text null,--标题
  [txtContent] text null,--内容
  [Adder]  text null,--添加人
  [AddTime]  text DEFAULT (datetime('now','localtime')) not null,--提交时间
  [ModiTime]  text DEFAULT (datetime('now','localtime')) not null,--修改时间
  [Hits] integer Default (0)  not null,--点击数
  [Flags] integer Default (0)  not null ,--标识
  [SortID] integer Default (0)  not null--排序号
 )

--ACCESS使用SQL语句建表
CREATE TABLE 表名 (
  [Id] AutoIncrement NOT NULL ,
  [AddTime] Date NULL ,
  [Hits] Integer NULL ,
  [Title] Text (255) NULL ,
  [Remark] Memo NULL ,
  [RMB] Currency NULL ,
  [Flags] bit NULL ,

  PRIMARY KEY (ID)
)