柳永法常用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'

--批量收缩所有数据库
declare cur cursor for select name from Master..SysDatabases where name<>'master' and name<>'model' and name<>'msdb' and name<>'Northwind' and name<>'pubs'
declare @tb sysname 

open cur 
fetch next from cur into @tb 
while @@fetch_status=0 
begin 
  exec ('dump transaction ['+@tb+'] with no_log') 
  exec ('backup log ['+@tb+'] with no_log') 
  exec ('dbcc shrinkdatabase(['+@tb+'])') 
  fetch next from cur into @tb 
end 
close cur 
deallocate cur
--------------------------数据库操作--------------------------


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

--通用建表结构
Create Table [dbo].[tablename] (
   [ID] int primary key identity(1,1),--ID，主键，自动号
   [txtTitle] varchar(255),--标题
   [txtContent] text,--内容
   [PutTime] datetime Default (getdate()),--提交时间
   [ModiTime] datetime Default (getdate()),--修改时间
   [Hits] int Default (0),--点击数
   [Flags] int Default (0) ,--标识
   [OrderID] int Default (0),--排序号
  )

--ACCESS使用SQL语句建表
CREATE TABLE tablename (
   [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)
)

--重命名表
sp_rename '表名', '新表名', 'object'
----------------------------表操作----------------------------


---------------------------字段操作---------------------------
--添加字段
alter table [表名] add [字段名] [varchar] (50)

--删除字段
alter table [表名] drop [字段名]

--修改字段
alter table [表名] alter column [字段名] varchar(50)

--添加约束
alter table [表名] add constraint [约束名] check ([约束字段] <= '2009-1-1')

--删除约束
alter table [表名] drop constraint [约束名]

--添加默认值
alter table [表名] add constraint [默认值名] default 'http://www.yongfa365.com/' for [字段名]

--删除默认值
alter table [表名] drop constraint [默认值名]

--让SQL Server 自动编号ID从1开始
dbcc checkident('表名',reseed,0)

---------------------------字段操作---------------------------


---------------------------数据操作---------------------------
--插入数据
insert into [表名] (字段1,字段2) values (100,'http://www.yongfa365.com/')

--删除数据
delete from [表名] where [字段名]>100

--删除重复记录(效率低，适合少量数据操作,极不适合ACCESS使用)
delete from [表名] where id not in (select max(id) from [表名] group by txtTitle,txtContent)

--NOT IN 效率太低，20000条数据都让access死掉了。可行的方法：建个临时表，在需要判断是否重复的字段上加主键，用insert into InfoTemp select * from Info将原表的数据导入临时表， 数据库可以自动筛去重复数据，delete * from Info 清空原表，再用insert into Info select * from InfoTemp 将临时表中数据导回原表

--更新数据
update [表名] set [字段1] = 200,[字段2] = 'http://www.yongfa365.com/' where [字段三] = 'haiwa'

--多表，用一个表更新另一个表(SQL Server版)
update 表一
set 表一.a = 表二.b
from 表一,表二
where 表一.id = 表二.id

--多表，用一个表更新另一个表(ACCESS版)
update 表一 
inner join 表二
on 表一.id = 表二.id
set 表一.a = 表二.b 

--查询结果存储到新表
select * into [新表表名] from [表名]

--从table 表中取出从第 m 条到第 n 条的记录：(not in 版本)
select top n-m+1 * from table where (id not in (select top m-1 id from table ))

--例：取出第31到第40条记录
select top 10 * from table where id not in (select top 30 id from table)

--随机取10条数据,newid()是SQL数据库里的一个函数，跟数据库里的ID没关
select top 10 * from table order by newid() 

--随机取10条数据,如果是ACCESS数据库用order by rnd(数字字段)
select top 10 * from table order by rnd(id)

--连接查询left join
select * from Article left join Category on Article.CategoryID=Category.ID



--查询结果插入到另一个表的相关字段里（可以插入一个表的字段，也可以是一个数字常量）
insert into desttbl(fld1, fld2) select fld1, 5 from srctbl

--把当前表里的数据再复制一份到这个表里
insert into [表名] select * from [表名]


--向数据库中添加5000条数据
declare @i int
set @i=1
while @i<=5000
begin
    insert into users(userid,username) values(@i,'username' + convert(varchar(255),@i))
    set @i=@i+1
end
go


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