SQL常用语句一览

统计价格,得到字段A名,B字段为统计值

SELECT 字段A,SUM(OrderPrice) FROM Orders GROUP BY 字段A

得到当前数据库的名称

SELECT NAME From Master..SysDataBases Where DbId=(Select Dbid From Master..SysProcesses Where Spid = @@spid)

(1)数据记录筛选：

sql="select*from数据表where字段名=字段值orderby字段名[desc]"

sql="select*from数据表where字段名like’’%字段值%’’orderby字段名[desc]"

sql="selecttop10*from数据表where字段名orderby字段名[desc]"

sql="select*from数据表where字段名in(’’值1’’,’’值2’’,’’值3’’)"

sql="select*from数据表where字段名between值1and值2"

(2)更新数据记录：

sql="update数据表set字段名=字段值where条件表达式"

sql="update数据表set字段1=值1,字段2=值2……字段n=值nwhere条件表达式"

(3)删除数据记录：

sql="delete from 数据表 where条件表达式"

sql="delete from数据表"(将数据表所有记录删除)

(4)添加数据记录：

sql="insert into数据表(字段1,字段2,字段3…)values(值1,值2,值3…)"

sql="insert into目标数据表select*from源数据表"(把源数据表的记录添加到目标数据表)

时间值用法values(TO_DATE(now(),YYYY-MM-DD HH24:MI:SS))

(5)数据记录统计函数：

AVG(字段名)得出一个表格栏平均值

COUNT(*|字段名)对数据行数的统计或对某一栏有值的数据行数统计

MAX(字段名)取得一个表格栏最大的值

MIN(字段名)取得一个表格栏最小的值

SUM(字段名)把数据栏的值相加

引用以上函数的方法：

sql="select sum(字段名)as别名from数据表where条件表达式"

setrs=conn.excute(sql)

用rs("别名")获取统的计值，其它函数运用同上。

(5)数据表的建立和删除：

CREATETABLE数据表名称(字段1类型1(长度),字段2类型2(长度)……)

例：CREATETABLEtab01 (namevarchar (50), datetimedefaultnow ())

DROPTABLE数据表名称(永久性删除一个数据表)

4.记录集对象的方法：

rs.movenext将记录指针从当前的位置向下移一行

rs.moveprevious将记录指针从当前的位置向上移一行

rs.movefirst将记录指针移到数据表第一行

rs.movelast将记录指针移到数据表最后一行

rs.absoluteposition=N将记录指针移到数据表第N行

rs.absolutepage=N将记录指针移到第N页的第一行

rs.pagesize=N设置每页为N条记录

rs.pagecount根据pagesize的设置返回总页数

rs.recordcount返回记录总数

rs.bof返回记录指针是否超出数据表首端，true表示是，false为否

rs.eof返回记录指针是否超出数据表末端，true表示是，false为否

rs.delete删除当前记录，但记录指针不会向下移动

rs.addnew添加记录到数据表末端

rs.update更新数据表记录

datediff(day,getdate(),onlinetime)>=0

where year(时间字段)="& Request("xyear") &" and month(时间字段)="& Request("xmonth") &"

SQL语句的添加、删除、修改虽然有如下很多种方法，但在使用过程中还是不够用，不知是否有高手把更多灵活的使用方法贡献出来？

添加、删除、修改使用db.Execute(Sql)命令执行操作

╔----------------╗

☆ 数据记录筛选 ☆

╚----------------╝

注意：单双引号的用法可能有误(没有测式)

Sql = "Select Distinct 字段名 From 数据表"

Distinct函数，查询数据库存表内不重复的记录

select *, count(distinct name) from table group by name

cn.execute("select Num, count(Distinct(Num)) from GoldPrice where serverID=" & GameIDFirst & " and Sshow=1 group by ID,ServerID,Sshow,Num,Pai order by Pai,Num desc,ID")

Sql = "Select Count(*) From 数据表 where 字段名1>#18:0:0# and 字段名1< #19:00# "

count函数,查询数库表内有多少条记录，“字段名1”是指同一字段

例：

set rs=conn.execute("select count(id) as idnum from news")

response.write rs("idnum")

sql="select * from 数据表 where 字段名 between 值1 and 值2"

Sql="select * from 数据表 where 字段名 between #2003-8-10# and #2003-8-12#"

SQL2000中的用法(Btime between '" & Request("S1") & "' and '" & Request("S2") & "')

在日期类数值为2003-8-10 19:55:08 的字段里查找2003-8-10至2003-8-12的所有记录，而不管是几点几分。

(dd17 between "& "#" & Request("a5") & "#" &" and "& "#" & Request("a6") & "#" &")

select * from tb_name where datetime between #2003-8-10# and #2003-8-12#

字段里面的数据格式为：2003-8-10 19:55:08，通过sql查出2003-8-10至2003-8-12的所有纪录，而不管是几点几分。

year(IDDate)=" & Y & " and month(IDDate)=" & i + 1 & "

Sql="select top 10 * from 数据表 where 字段名 order by 字段名 [desc]"

查找数据库中前10记录

Sql="select top n * form 数据表 order by newid()"

随机取出数据库中的若干条记录的方法

top n，n就是要取出的记录数

Sql="select * from 数据表 where 字段名 in (’’值1’’,’’值2’’,’’值3’’)"

╔----------------╗

☆ 模糊查询 ☆

╚----------------╝

Sql="select * from 数据表 where 字段名=字段值 order by 字段名 [desc]"

Sql="select * from 数据表 where 字段名 like ’’%字段值%’’ order by 字段名 [desc]"

ACCESS: ShopName like '%" & Request("key") &"%'

等效ACCESS InStr(1,LCase(ShopName),LCase('" & Request("key") & "'),0)<>0

等效SQL: CHARINDEX(lower('" & Request("key") & "'),lower(ShopName))<>0

╔----------------╗

☆ 添加数据记录 ☆

╚----------------╝

sql="insert into 数据表 (字段1,字段2,字段3 …) values (值1,值2,值3 …)"

sql="insert into 数据表 values (值1,值2,值3 …)"

不指定具体字段名表示将按照数据表中字段的顺序，依次添加

sql="insert into 目标数据表 select * from 源数据表"

把源数据表的记录添加到目标数据表

╔----------------╗

☆ 更新数据记录 ☆

╚----------------╝

Sql="update 数据表 set 字段名=字段值 where 条件表达式"

Sql="update 数据表 set 字段1=值1,字段2=值2 …… 字段n=值n where 条件表达式"

Sql="update 数据表 set 字段1=值1,字段2=值2 …… 字段n=值n "

没有条件则更新整个数据表中的指定字段值

╔----------------╗

☆ 删除数据记录 ☆

╚----------------╝

Sql="delete from 数据表 where 条件表达式"

Sql="delete from 数据表"

没有条件将删除数据表中所有记录)

╔--------------------╗

☆ 数据记录统计函数 ☆

╚--------------------╝

AVG(字段名) 得出一个表格栏平均值

COUNT(*|字段名) 对数据行数的统计或对某一栏有值的数据行数统计

MAX(字段名) 取得一个表格栏最大的值

MIN(字段名) 取得一个表格栏最小的值

SUM(字段名) 把数据栏的值相加

引用以上函数的方法：

sql="select sum(字段名) as 别名 from 数据表 where 条件表达式"

set rs=conn.excute(sql)

用 rs("别名") 获取统的计值，其它函数运用同上。

╔----------------------╗

☆ 数据表的建立和删除 ☆

╚----------------------╝

CREATE TABLE 数据表名称(字段1 类型1(长度),字段2 类型2(长度) …… )

例：CREATE TABLE tab01(name varchar(50),datetime default now())

DROP TABLE 数据表名称 (永久性删除一个数据表)

╔--------------------╗

☆ 记录集对象的方法 ☆

╚--------------------╝

rs.movenext 将记录指针从当前的位置向下移一行

rs.moveprevious 将记录指针从当前的位置向上移一行

rs.movefirst 将记录指针移到数据表第一行

rs.movelast 将记录指针移到数据表最后一行

rs.absoluteposition=N 将记录指针移到数据表第N行

rs.absolutepage=N 将记录指针移到第N页的第一行

rs.pagesize=N 设置每页为N条记录

rs.pagecount 根据 pagesize 的设置返回总页数

rs.recordcount 返回记录总数

rs.bof 返回记录指针是否超出数据表首端，true表示是，false为否

rs.eof 返回记录指针是否超出数据表末端，true表示是，false为否

rs.delete 删除当前记录，但记录指针不会向下移动

rs.addnew 添加记录到数据表末端

rs.update 更新数据表记录

╔--------------------╗

☆ 收缩数据库 ☆

╚--------------------╝

--重建索引

DBCC REINDEX

DBCC INDEXDEFRAG

--收缩数据和日志

DBCC SHRINKDB

DBCC SHRINKFILE

╔--------------------╗

☆ 压缩数据库 ☆

╚--------------------╝

dbcc shrinkdatabase(dbname)

╔--------------------╗

☆ 转移数据库 ☆

╚--------------------╝

转移数据库给新用户以已存在用户权限

exec sp_change_users_login 'update_one','newname','oldname'

go

╔--------------------╗

☆ 检查备份集 ☆

╚--------------------╝

RESTORE VERIFYONLY from disk='E:\dvbbs.bak'

╔--------------------╗

☆ 修复数据库 ☆

╚--------------------╝

Alter DATABASE [dvbbs] SET SINGLE_USER

GO

DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK

GO

Alter DATABASE [dvbbs] SET MULTI_USER

GO

╔--------------------╗

☆ 日志清除 ☆

╚--------------------╝

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'

╔------------------------------------------------╗

☆ 增加字段、修改字段名、删除字段、修改表名 ☆

╚------------------------------------------------╝

增加字段： alter table [表名] add 字段名 smallint default 0 增加数字字段，整型，缺省值为0

alter table 表名 add 字段名 varchar(50) 增加字段

sp_rename 'tb.name','name','column' 修改字段名

修改变长文本型字段的大小：alter table [表名] alter 字段名 varchar(N)

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

alter table tb drop column name 删除字段

sp_rename 'tb','tbs' 修改表名

创建表CREATE TABLE 表名(ID bigint IDENTITY(1,1) primary key,Item_Number nvarchar(50) DEFAULT (N''))

创建表：

sql="CREATE TABLE [表名] ([字段1,并设置为主键] int IDENTITY (1, 1) NOT NULL CONSTRAINT PrimaryKey PRIMARY KEY,"&_

"[字段2] varchar(50),"&_

"[字段3] single default 0,"&_

"[字段4] varchar(100) null,"&_

"[字段5] smallint default 0,"&_

"[字段6] int default 0,"&_

"[字段7] date default date(),"&_

"[字段8] int default 1)"

conn.execute sql

sql 确认表名，字段名是否存在2010-11-10 10:03:17| 分类： 数据库&事务等相 | 标签： |字号大中小 订阅 .

select * from sys.tables where name='import'//查找表名

select name from sys.columns where object_id in (select object_id from sys.tables where name='import')//指定表里查找字段名

if exists (select name from sys.columns where object_id in (select object_id from sys.tables where name='import') and name='retain_classification')

alter table import add retain_classification bit default 0

if (NOT exists (

select * from dbo.syscolumns where name = '字段名' and id in (select id from dbo.sysobjects where id = object_id(N'[dbo].表名') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

))

分组

select ClassA,count(ID)

from 表名

where ClassMaxID=3 '先筛选后分组

group by ClassA

having avg(score)>60 '分组后再筛选

得到sql字段的说明

SQL2005用些方法 Dim sqlstrpp As String = "select a.name,b.value,a.id from syscolumns a left outer join sys.extended_properties b on a.id=b.major_id and a.colid=b.minor_id WHERE a.id = OBJECT_ID('" & dataTableName & "')"

SQL2000用些方法 Dim sqlstrpp As String = "SELECT TOP 100 PERCENT b.name AS FieldsName, c.[value] AS description FROM sysobjects a INNER JOIN syscolumns b ON a.id = b.id LEFT OUTER JOIN sysproperties c ON c.id = b.id AND b.colid = c.smallid WHERE (a.xtype = 'u') AND (a.id = OBJECT_ID('" & dataTableName & "')) ORDER BY a.xtype,description"

删除表中的字段

EXEC( 'alter table Flie_Data_Property DROP COLUMN '+@PropertyName+'')

查询字段是否存在

select * from syscolumns where name=@PropertyNameOld and id in (select id from sysobjects where id = object_id(N'Flie_Data_Property') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

if @@rowcount<=0

EXEC( 'alter table Flie_Data_Property add '+@PropertyName+' nvarchar(50)')--不存在，增加字段名称

else

EXEC( 'sp_rename [Flie_Data_Property.'+@PropertyNameOld+'], '+@PropertyName+', ''COLUMN''')--存在修改字段名称

end

字段值合并成字符串

ALTER PROCEDURE [dbo].[File_Type_Select_Str]

@ID int,

@StrOutput nvarchar(4000) output

as

begin

declare @str nvarchar(4000)

set @str=''

select @str=@str+','+FileType from File_Type order by id

select @str

set @StrOutput=@str

end

'将多行合并成一行，并做分组统计

'data=STUFF((SELECT ','+[MakeName] FROM Item_Make_People t FOR XML PATH('')), 1, 1, ''),

SELECT code, data=STUFF((SELECT ','+[values] FROM test t WHERE code=t1.code FOR XML PATH('')), 1, 1, ''),sum([count]) as [count]

FROM test t1

GROUP BY code

ds1 = SqlNetData.QueryDatabase("SELECT data=STUFF((SELECT ','+[RatingText] FROM Item_Make_People_Rating t where PeopleID=" & ds.Rows(i)(0) & " FOR XML PATH('')), 1, 1, '') FROM Item_Make_People_Rating where PeopleID=" & ds.Rows(i)(0) & "")

查看所有表名：

select name from sysobjects where type='U'

查询表的所有字段名：

Select name from syscolumns Where ID=OBJECT_ID('表名')

--获取表字段名称，属性

SELECT

表名 = case when a.colorder=1 then d.name else '' end,

表说明 = case when a.colorder=1 then isnull(f.value,'') else '' end,

字段序号 = a.colorder,

字段名 = a.name,

标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,

主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (

SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,

类型 = b.name,

占用字节数 = a.length,

长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),

小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),

允许空 = case when a.isnullable=1 then '√'else '' end,

默认值 = isnull(e.text,''),

字段说明 = isnull(g.[value],'')

FROM

syscolumns a

left join

systypes b

on

a.xusertype=b.xusertype

inner join

sysobjects d

on

a.id=d.id and d.xtype='U' and d.name<>'dtproperties'

left join

syscomments e

on

a.cdefault=e.id

left join

sysproperties g

on

a.id=g.id and a.colid=g.smallid

left join

sysproperties f

on

d.id=f.id and f.smallid=0

where

d.name='要查询的表' --如果只查询指定表,加上此条件

order by

a.id,a.colorder

//在查询前加上一个字段为自动编号

(SELECT top 900 ROW_NUMBER() OVER(order by Sort,Item_Number desc,ID) as A,* FROM Item_Basic

//二个表合并成一个表

select '款号'=a... ,'商品'=.., '单位'=... , '购入'= ... , '金额'= ..., 0 , 0

from 表1

union

select '款号'=b... ,'商品'=.., '单位'=... , 0 , 0 , '购入'= ... , '金额'= ...

from 表2

ALTER PROCEDURE [dbo].[Config_PZ_Check]

@WhereStr nvarchar(4000),

@@TotalCount INT OUTPUT

as

begin

CREATE TABLE #aa(aa bigint)

INSERT INTO #aa exec('select count(1) a from View_Config_PZ WHERE ' + @WhereStr)

select @@TotalCount=aa from #aa

DROP TABLE #aa

end