*SQL中的保留关键字
action add aggregate all alter after and as asc avg avg_row_length auto_increment between bigint bit binary blob bool both by cascade case char character
change check checksum column columns comment constraint create cross current_date current_time current_timestamp data database databases date datetime day day_hour day_minute
day_second dayofmonth dayofweek dayofyear dec decimal default delayed delay_key_write delete desc describe distinct distinctrow double drop
*得出SQL语句的执行时间的方法
例如:
declare @d datetime
set @d=getdate() select * from ycdata1 select [alltime]=datediff(ms,@d,getdate())
红色为我们要执行的sql语句,结果为 执行花费时间
*清空数据库中所有表的数据(已经测试过,可以正常运行)
declare crsr cursor
for SELECT [name] FROM DBO.SYSOBJECTS
WHERE OBJECTPROPERTY(ID,N'IsTable')=1 and type = 'U' and [name] <> 'dtproperties' --and crdate...
open crsr
declare @tblName sysname
fetch crsr into @tblName
EXEC('truncate table '+@tblName)
while @@fetch_status=0
fetch next from crsr into @tblName
EXEC('truncate table '+@tblName)
close crsr
deallocate crsr //--删除以释放游标//可以为crdate字段指定表的创建日期
*常用SQL语句扩展和例子
一 数据库筛选记录:
SQL= select * from tb1 where field1 =’value1’ order by id desc
Select * from tb1 where field1 like ‘% value1%’ order by id desc
Select top 10 * from tb1 where field1 =’ value1’ order by id,age desc
Select * from tb1 where field1 in(‘value1’, ‘value2’, ‘value3’,)
Select * from tb1 where field1 between value1 and value2
select * from tb1, tb2 where tb1.id *= tb2.id
select a,b,c from tb1 where a IN (select d from b ) //子查询
select * from tb1 where id not in(select id from tb1 where 表达式) order by id desc
更多:
说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
说明：Distinct查询数据库存表内不重复的记录
Select Distinct field1 From tb1
说明：count函数,查询数库表内有多少条记录，“field1”是指同一字段
"Select Count(*) From tb1 where field1>#18:0:0# and field1< #19:00# "
说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1<>1
法二：select top 0 * into b from a
说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in '"&Server.MapPath(".")&""data.mdb" &"' where..
说明：外连接查询(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
说明：列出数据库里所有的表名
select name from sysobjects where type='U'
说明：选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
二 数据库更新记录
sql= update tb1set field1=字段值 where 条件表达式
update tb1 set field1= value1, field2= value2……fieldn= valuen where条件表达式
三 删除记录
sql= deletefrom tb1 where 条件表达式
deletefrom tb1" //(将数据表所有记录删除)
四 添加记录
sql=insertinto数据表(field1, field1, field2…)values(value1, value2, value3…)"
insertinto目标数据表select*from源数据表"(把源数据表的记录添加到目标数据表)
五 统计等
AVG(字段名)得出一个表格栏平均值
COUNT(*|字段名)对数据行数的统计或对某一栏有值的数据行数统计
MAX(字段名)取得一个表格栏最大的值
MIN(字段名)取得一个表格栏最小的值
SUM(字段名)把数据栏的值相加
引用以上函数的方法：
sql="select sum(filed) as 别名from 数据表where条件表达式" //返回一个统计值
六 建立表和创建表
CREATETABLE tb1 filed1类型1(长度), filed1 类型2(长度)……)
例：CREATETABLEtab01 (namevarchar (50), datetimedefaultnow ())
DROPTABLE数据表名称(永久性删除一个数据表)
七 创建数据库
CREATE DATABASE database-name
八 删除数据库
drop database dbname
*其它技巧
1、1=1，1=2的使用，在SQL语句组合时用的较多
“where 1=1” 是表示选择全部 “where 1=2”全部不选，
如：
if @strWhere !=''
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere
end
else
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
我们可以直接写成
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere
2、收缩数据库
--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收缩数据和日志
DBCC SHRINKDB
DBCC SHRINKFILE
3、压缩数据库
dbcc shrinkdatabase(dbname)
4、转移数据库给新用户以已存在用户权限
exec sp_change_users_login 'update_one','newname','oldname'
go
5、检查备份集
RESTORE VERIFYONLY from disk='E:"dvbbs.bak'
6、修复数据库
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO
7、日志清除
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
USE tablename -- 要操作的数据库名
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 你想设定的日志文件的大小(M)
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log')
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF
8、说明：更改某个表
exec sp_changeobjectowner 'tablename','dbo'
9、存储更改全部表
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO
10、SQL SERVER中直接循环写入数据
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end
