sql的一些用法整理

---常用语法

--SQL语句先前写的时候，很容易把一些特殊的用法忘记，我特此整理了一下SQL语句操作。

--一、基础

--1、说明：创建数据库

Create DATABASE database-name 

--2、说明：删除数据库

drop database dbname

--3、说明：备份sql server

--- 创建备份数据的device

USE master

EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'

---- 开始备份

BACKUP DATABASE pubs TO testBack 

--4、说明：创建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

--根据已有的表创建新表：

--A：create table tab_new like tab_old (使用旧表创建新表)

--B：create table tab_new as select col1,col2…from tab_old definition only

--5、说明：删除新表

drop table tabname 

--6、说明：增加一个列

Alter table tabname add column col type

--注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

--7、说明：添加主键：Alter table tabname add primary key(col) 

--说明：删除主键：Alter table tabname drop primary key(col) 

--8、说明：创建索引：create [unique] index idxname on tabname(col….) 

--删除索引：drop index idxname

--注：索引是不可更改的，想更改必须删除重新建。

--9、说明：创建视图：create view viewname as select statement 

--删除视图：drop view viewname

--10、说明：几个简单的基本的sql语句

--选择：select * from table1 where 范围

--插入：insert into table1(field1,field2) values(value1,value2)

--删除：delete from table1 where 范围

--更新：update table1 set field1=value1 where 范围

--查找：select * from table1 where field1 like ’%value1%’---like的语法很精妙，查资料!

--排序：select * from table1 order by field1,field2 [desc]

--总数：select count as totalcount from table1

--求和：select sum(field1) as sumvalue from table1

--平均：select avg(field1) as avgvalue from table1

--最大：select max(field1) as maxvalue from table1

--最小：select min(field1) as minvalue from table1

--11、说明：几个高级查询运算词

--A：UNION 运算符

--UNION 运算符通过组合其他两个结果表（例如TABLE1 和TABLE2）并消去表中任何重复行而派生出一个结果表。当ALL 随UNION 一起使用时（即UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自TABLE1 就是来自TABLE2。

--B：EXCEPT 运算符

--EXCEPT 运算符通过包括所有在TABLE1 中但不在TABLE2 中的行并消除所有重复行而派生出一个结果表。当ALL 随EXCEPT 一起使用时(EXCEPT ALL)，不消除重复行。

--C：INTERSECT 运算符

--????INTERSECT 运算符通过只包括TABLE1 和TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当ALL 随INTERSECT 一起使用时(INTERSECT ALL)，不消除重复行。

--注：使用运算词的几个查询结果行必须是一致的。

--12、说明：使用外连接

--A、left outer join：-

--左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。

--SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

--B：right outer join: 

--右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。

--C：full outer join：

--全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

--二、提升

--1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)

--法一：select * into b from a where 1<>1

--法二：select top 0 * into b from a

--2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)

insert into b(a, b, c) select d,e,f from b;

--3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)

insert into b(a, b, c) select d,e,f from b in ‘具体数据库’where 条件

--例子：..

from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..

--4、说明：子查询(表名：a 表名：b)

select a,b,c from a where a IN (select d from b ) --或者:-- 

select a,b,c from a where a IN (1,2,3)

--5、说明：显示文章、提交人和最后回复时间

--select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

--6、说明：外连接查询(表名：a 表名：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

--7、说明：在线视图查询(表名：a )

select * from (Select a,b,c FROM a) T where t.a > 1;

--8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括

select * from table1 where time between time1 and time2

select a,b,c, from table1 where a not between 数值and 数值

--9、说明：in 的使用方法

select * from table1 where a [not] in (‘值’,’值’,’值’,’值’)

--10、说明：两张关联表，删除主表中已经在副表中没有的信息

delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

--11、说明：四表联查问题：

select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

--12、说明：日程安排提前五分钟提醒

SQL: select * from 日程安排where datediff('minute',f开始时间,getdate())>5

--13、说明：一条sql 语句搞定数据库分页

select top 10 b.* from (select top 20 主键字段,排序字段from 表名order by 排序字段desc) a,表名b where b.主键字段= a.主键字段order by a.排序字段

--14、说明：前条记录

select top 10 * form table1 where 范围

--15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)

select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

--16、说明：包括所有在TableA 中但不在TableB和TableC 中的行并消除所有重复行而派生出一个结果表

(select a from tableA ) except (select a from tableB) except (select a from tableC)

--17、说明：随机取出条数据

select top 10 * from tablename order by newid()

---18、说明：随机选择记录

select newid()

--19、说明：删除重复记录

Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

--20、说明：列出数据库里所有的表名

select name from sysobjects where type='U' 

---21、说明：列出表里的所有的列

select name from syscolumns where id=object_id('TableName')

--22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。

select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type

--显示结果：

type vender pcs

--电脑A 1

--电脑A 1

--光盘B 2

--光盘A 2

--手机B 3

--手机C 3

--23、说明：初始化表table1

TRUNCATE TABLE table1

--24、说明：选择从到的记录

select top 5 * from (select top 15 * from table order by id asc) table_别名order by id desc

--三、技巧

--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

--小记存储过程中经常用到的本周，本月，本年函数

Dateadd(wk,datediff(wk,0,getdate()),-1) 

Dateadd(wk,datediff(wk,0,getdate()),6) 

Dateadd(mm,datediff(mm,0,getdate()),0) 

Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) 

Dateadd(yy,datediff(yy,0,getdate()),0) 

Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) 

--上面的SQL代码只是一个时间段

Dateadd(wk,datediff(wk,0,getdate()),-1) 

Dateadd(wk,datediff(wk,0,getdate()),6) 

--就是表示本周时间段. 

--下面的SQL的条件部分,就是查询时间段在本周范围内的: 

Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) 

--而在存储过程中

select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) 

select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) ------------------------------------------全局变量------------------------------------------@@SERVERNAME    : 返回运行SQL Server 2000本地服务器的名称。

@@REMSERVER       : --返回登录记录中记载的远程SQL Server服务器的名称。

@@CONNECTIONS : --返回自上次启动SQL Server以来连接或试图连接的次数，用其可让管理人员方便地了解今天所有试图连接服务器的次数。

@@CURSOR_ROWS : --返回最后连接上并打开的游标中当前存在的合格行的数量。

@@ERROR                 :-- 返回最后执行的Transact-SQL语句的错误代码。

@@ROWCOUNT       : --返回受上一语句影响的行数，任何不返回行的语句将这一变量设置为。

@@VERSION             : --返回SQL Server当前安装的日期、版本和处理器类型。

@@CPU_BUSY          : --返回自SQL Server最近一次启动以来CPU的工作时间其单位为毫秒。

@@DATEFIRST         : --返回使用SET DATEFIRST命令而被赋值的DATAFIRST参数值。SET DATEFIRST命令用来指定每周的第一天是星期几。

@@DBTS                    : --返回当前数据库的时间戳值必须保证数据库中时间戳的值是惟一的。

@@FETCH_STATUS : --返回上一次FETCH语句的状态值。

@@IDENTITY            : --返回最后插入行的标识列的列值。

@@IDLE                     : --返回自SQL Server最近一次启动以来CPU处于空闭状态的时间长短，单位为毫秒。

@@IO_BUSY             : --返回自SQL Server最后一次启动以来CPU执行输入输出操作所花费的时间(毫秒)。

@@LANGID               : --返回当前所使用的语言ID值。

@@LANGUAGE         : --返回当前使用的语言名称。

@@LOCK_TIMEOUT: --返回当前会话等待锁的时间长短其单位为毫秒。

@@MAX_CONNECTIONS : --返回允许连接到SQL Server的最大连接数目。

@@MAX_PRECISION : --返回decimal 和numeric数据类型的精确度。

@@NESTLEVEL         : --返回当前执行的存储过程的嵌套级数，初始值为。

@@OPTIONS              : --返回当前SET选项的信息。

@@PACK_RECEIVED : --返回SQL Server通过网络读取的输入包的数目。

@@PACK_SENT         : --返回SQL Server写给网络的输出包的数目。

@@PACKET_ERRORS : --返回网络包的错误数目。

@@PROCID                 : --返回当前存储过程的ID值。

@@SERVICENAME   : --返回SQL Server正运行于哪种服务状态之下：如MS SQLServer、MSDTC、SQLServerAgent。

@@SPID                       : --返回当前用户处理的服务器处理ID值。

@@TEXTSIZE             : --返回SET语句的TEXTSIZE选项值SET语句定义了SELECT语句中text或image。数据类型的最大长度基本单位为字节。

@@TIMETICKS          : --返回每一时钟的微秒数。

@@TOTAL_ERRORS : --返回磁盘读写错误数目。

@@TOTAL_READ      : --返回磁盘读操作的数目。

@@TOTAL_WRITE    : --返回磁盘写操作的数目。

@@TRANCOUNT      : --返回当前连接中处于激活状态的事务数目。

-----------------------------------------

函数列表

-----------------------------------------

--一.聚合函数

AVG  --返回组中值的平均值。空值将被忽略

BINARY_CHECKSUM  --返回对表中的行或表达式列表计算的二进制校验值。BINARY_CHECKSUM 可用于检测表中行的更改

CHECKSUM  --返回在表的行上或在表达式列表上计算的校验值。CHECKSUM 用于生成哈希索引

CHECKSUM_AGG  --返回组中值的校验值。空值将被忽略

COUNT  --返回组中项目的数量

COUNT_BIG  --返回组中项目的数量。COUNT_BIG 的使用与COUNT 函数相似。它们之间的唯一差别是它们的返回值：COUNT_BIG 总是返回bigint 数据类型值，而COUNT 则总是返回int 数据类型值

GROUPING  --"是一个聚合函数,它产生一个附加的列，当用CUBE 或ROLLUP 运算符添加行时，附加的列输出值为，当所添加的行不是由CUBE 或ROLLUP 产生时，附加列值为。

--仅在与包含CUBE 或ROLLUP 运算符的GROUP BY 子句相联系的选择列表中才允许分组"

MAX  --返回表达式的最大值

MIN  --返回表达式的最小值

SUM  --返回表达式中所有值的和，或只返回DISTINCT 值。SUM 只能用于数字列。空值将被忽略

STDEV  --返回给定表达式中所有值的统计标准偏差

STDEVP  --返回给定表达式中所有值的填充统计标准偏差

VAR  --返回给定表达式中所有值的统计方差。

VARP  --返回给定表达式中所有值的填充的统计方差。

--二.数学函数

ABS  --返回给定数字表达式的绝对值

ACOS  --返回以弧度表示的角度值，该角度值的余弦为给定的float 表达式；本函数亦称反余弦。

ASIN  --返回以弧度表示的角度值，该角度值的正弦为给定的float 表达式；亦称反正弦

ATAN  --返回以弧度表示的角度值，该角度值的正切为给定的float 表达式；亦称反正切

ATN2  --返回以弧度表示的角度值，该角度值的正切介于两个给定的float 表达式之间；亦称反正切

CEILING  --返回大于或等于所给数字表达式的最小整数

COS  --一个数学函数，返回给定表达式中给定角度（以弧度为单位）的三角余弦值

COT  --一个数学函数，返回给定float 表达式中指定角度（以弧度为单位）的三角余切值

DEGREES  --当给出以弧度为单位的角度时，返回相应的以度数为单位的角度

EXP  --返回所给的float 表达式的指数值

FLOOR  --返回小于或等于所给数字表达式的最大整数

LOG  --返回给定float 表达式的自然对数

LOG10  --返回给定float 表达式的以10 为底的对数

PI  --返回PI 的常量值

POWER  --返回给定表达式乘指定次方的值

RADIANS  --对于在数字表达式中输入的度数值返回弧度值

RAND  --返回0 到之间的随机float 值

ROUND  --返回数字表达式并四舍五入为指定的长度或精度

SIGN  --返回给定表达式的正(+1)、零(0) 或负(-1) 号

SIN  --以近似数字(float) 表达式返回给定角度（以弧度为单位）的三角正弦值

SQUARE  --返回给定表达式的平方

SQRT  --返回给定表达式的平方根

TAN  --返回输入表达式的正切值

--三.日期函数

DATEADD  --在向指定日期加上一段时间的基础上，返回新的datetime 值。

DATEDIFF  --返回跨两个指定日期的日期和时间边界数

DATENAME  --返回代表指定日期的指定日期部分的字符串

DATEPART  --返回代表指定日期的指定日期部分的整数

DAY  --返回代表指定日期的天的日期部分的整数

GETDATE  --按datetime 值的Microsoft® SQL Server™ 标准内部格式返回当前系统日期和时间

GETUTCDATE  --返回表示当前UTC 时间（世界时间坐标或格林尼治标准时间）的datetime 值

MONTH  --返回代表指定日期月份的整数

YEAR  --返回表示指定日期中的年份的整数

--四.系统函数

APP_NAME  --返回当前会话的应用程序名称（如果应用程序进行了设置）。

CASE --表达式 计算条件列表并返回多个可能结果表达式之一（case column when value then value）

CAST 和CONVERT  --将某种数据类型的表达式显式转换为另一种数据类型（case():convert()）

COALESCE  --返回其参数中第一个非空表达式

COLLATIONPROPERTY  --返回给定排序规则的属性

CURRENT_TIMES**P  --返回当前的日期和时间。此函数等价于GETDATE()

CURRENT_USER  --返回当前的用户。此函数等价于USER_NAME()

DATALENGTH  --返回任何表达式所占用的字节数

@@ERROR  --返回最后执行的Transact-SQL 语句的错误代码

fn_helpcollations  返回Microsoft® SQL Server™ 2000 --支持的所有排序规则的列表。

fn_servershareddrives  --返回由群集服务器使用的共享驱动器名称

fn_virtualfilestats  --返回对数据库文件（包括日志文件）的I/O 统计

FORMATMESSAGE  --从sysmessages 现有的消息构造消息。FORMATMESSAGE 与RAISERROR 语句的功能相似；但RAISERROR 立即输出消息而FORMATMESSAGE 返回编辑后的信息供进一步处理

GETANSINULL  --返回会话的数据库的默认为空性。

HOST_ID  --返回工作站标识号。

HOST_NAME  --返回工作站名称。

IDENT_CURRENT  --返回为任何会话和任何作用域中的指定表最后生成的标识值。

IDENT_INCR  --返回增量值（返回形式为numeric(@@MAXPRECISION,0)），该值是在带有标识列的表或视图中创建标识列时指定的

IDENT_SEED  --返回种子值（返回形式为numeric(@@MAXPRECISION,0)），该值是在带有标识列的表或视图中创建标识列时指定的

@@IDENTITY  --返回最后插入的标识值

IDENTITY--（函数） 只用在带有INTO table 子句的SELECT 语句中，以将标识列插入到新表中。尽管类似，但是IDENTITY 函数不是与CREATE TABLE 和ALTER TABLE 一起使用的IDENTITY 属性。（自动采番时可以起到作用）

ISDATE  --确定输入表达式是否为有效的日期

ISNULL  --使用指定的替换值替换NULL

ISNUMERIC  --确定表达式是否为一个有效的数字类型

NEWID  --创建uniqueidentifier 类型的唯一值

NULLIF  --如果两个指定的表达式相等，则返回空值。

PARSENAME  --返回对象名的指定部分。可以检索的对象部分有对象名、所有者名称、数据库名称和服务器名称

PERMISSIONS  --返回一个包含位图的值，表明当前用户的语句、对象或列权限。

@@ROWCOUNT  --返回受上一语句影响的行数

ROWCOUNT_BIG  --返回受执行的最后一个语句影响的行数。该函数的功能与@@ROWCOUNT 一样，除非ROWCOUNT_BIG 的返回类型是bigint。

SCOPE_IDENTITY  --返回插入到同一作用域中的IDENTITY 列内的最后一个IDENTITY 值。

SERVERPROPERTY  --返回有关服务器实例的属性信息

SESSIONPROPERTY  --返回会话的SET 选项设置

SESSION_USER  --是一个niladic 函数，允许在未指定默认值时将系统为当前会话的用户名提供的值插入到表中。还允许在查询、错误信息等中使用用户名

STATS_DATE  --返回最后一次更新指定索引统计的日期

SYSTEM_USER  --返回当前系统用户名

@@TRANCOUNT  --返回当前连接的活动事务数

USER_NAME  --返回给定标识号的用户数据库用户名

-五.字符函数

ASCII  返回字符表达式最左端字符的ASCII代码值。

CHAR  将intASCII代码转换为字符的字符串函数。

CHARINDEX  返回字符串中指定表达式的起始位置

DIFFERENCE  以整数返回两个字符表达式的SOUNDEX值之差。

LEFT  返回字符串中从左边开始指定个数的字符。

LEN  返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格

LOWER  将大写字符数据转换为小写字符数据后返回字符表达式。

LTRIM  删除起始空格后返回字符表达式

NCHAR  根据Unicode标准所进行的定义，用给定整数代码返回Unicode字符

PATINDEX  返回指定表达式中某模式第一次出现的起始位置；如果在全部有效的文本和字符数据类型中没有找到该模式，则返回零

REPLACE  用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式

QUOTENAME  返回带有分隔符的Unicode字符串，分隔符的加入可使输入的字符串成为有效的Microsoft®SQLServer™分隔标识符。

REPLICATE  以指定的次数重复字符表达式

REVERSE  返回字符表达式的反转。

RIGHT  返回字符串中从右边开始指定个数的字符

RTRIM  截断所有尾随空格后返回一个字符串

SOUNDEX  返回由四个字符组成的代码(SOUNDEX)以评估两个字符串的相似性

SPACE  返回由重复的空格组成的字符串

STR  由数字数据转换来的字符数据

STUFF  删除指定长度的字符并在指定的起始点插入另一组字符

SUBSTRING  返回字符、binary、text或image表达式的一部分

UNICODE  按照Unicode标准的定义，返回输入表达式的第一个字符的整数值

UPPER  返回将小写字符数据转换为大写的字符表达式

-----------------------------------------

常用函数用法

-------------------------

判断删除临时表

if OBJECT_ID('tempdb..'+'#TEMP_Product') is not null

DROP TABLE #TEMP_Product 

转换数据为指定位数的值.转换后没有多余的位数

select convert(numeric(10,0),1231.5343)

两个数字做除法保留位数

select convert(numeric(10,3),cast(154 as float)/1000)

日期比较使用DataDiff

1:ASCII(varchar,输入),UNICODE(nvarchar,输入)

 说明：得到字符的ASCII码的整数值和UNIDODE码的整数值

 select ascii('a') 

 /*结果

   97

 */

2：Char(int,输入)，NChar(int,输入)

  说明：由字符得到相应的ASCII码和UNICODE字符，当输入的整数找不到相应的字符时返回NULL

   select char(97)

  /*结果

   a

  */

3:CharIndex(字符串，字符串)

 说明：返回字符串在字符串中的位置,如果字符串不在中，则返回

 select charindex('d','abcd')

 /*结果

   4

 */

 select charindex('cd','abcd')

 /*结果

   3

 */

 select charindex('dd','abcd')

 /*结果

   0

 */

4:left(string,int)

 说明：返回字符串左边的整数个字符，如果字符串的长度小于输入的整数，则返回所有字符串

 select left('abcdef',3)

 /*结果

   abc

 */

5:right(string,int)

 说明：返回字符串右边的整数个字符，如果字符串的长度小于输入的整数，则返回所有字符串

 select right('abcdef',3)

 /*结果

   def

 */

6:len(string)

 说明：返回字符串中字符的个数

 select len('abcd')

 /*结果

   4

 */

7:lower(string)

 说明：将字符串中大写字符转换为小写字符

 select lower('SDSF')

 /*结果

   sdsf

 */

8:upper(string)

 说明：将字符串中小写字符转换为大写字符

 select lower('sdsf')

 /*结果

   SDSF

 */

9：rtrim(string)

说明：删除字符串右边开始的所有空格

 select rtrim('abcd  ')

 /*结果

   'abcd'

 */

10:ltrim(string)

说明：删除字符串左边开始的所有空格

 select rtrim('   abcd')

 /*结果

   'abcd'

 */

11：patindex('%string1%',string2)

  说明：string1在string2中第一次出现的起始位置

  select patindex('%de%','abcdef')

  /*结果

   4

 */

12:replace(string1,string2,string3)

 说明：用string3替换string1中的string2,如果string2不是string1的子字符串的话，最终会返回string1

 select replace('abcdef','bcd','xxx')

 /*结果

   'axxxef'

 */

13:stuff(string1,int1,int2,string2)

  说明：用string2替换string1中int1到int2中的这部分字符

  select stuff('abcdef',1,3,'xxx')

  /*结果

   'xxxdef'

  */  

14：substring(sting,int1,int2)

   说明：返回string中int1到int2中的这部分字符

   select substring('abcdef',2,4)

   /*结果

   'bcde'

   */ 

15：space(int) 

   说明：返回int个空格组成的字符串

   select space(10)

   /*结果

   '          '

   */ 

16:reverse(string)

  说明：将string反转

   select reverse('abcdef')

   /*结果

     fedcba

   */ 

--数据操作

　　Select --从数据库表中检索数据行和列

　　　　　　Insert --向数据库表添加新数据行

　　　　　　Delete --从数据库表中删除数据行

　　　　　　Update --更新数据库表中的数据

　　--数据定义

　　Create TABLE --创建一个数据库表

　　　　　　Drop TABLE --从数据库中删除表

　　　　　　Alter TABLE --修改数据库表结构

　　　　　　Create VIEW --创建一个视图

　　　　　　Drop VIEW --从数据库中删除视图

　　　　　　Create INDEX --为数据库表创建一个索引

　　　　　　Drop INDEX --从数据库中删除索引

　　　　　　Create PROCEDURE --创建一个存储过程

　　　　　　Drop PROCEDURE --从数据库中删除存储过程

　　　　　　Create TRIGGER --创建一个触发器

　　　　　　Drop TRIGGER --从数据库中删除触发器

　　　　　　Create SCHEMA --向数据库添加一个新模式

　　　　　　Drop SCHEMA --从数据库中删除一个模式

　　　　　　Create DOMAIN --创建一个数据值域

　　　　　　Alter DOMAIN --改变域定义

　　　　　　Drop DOMAIN --从数据库中删除一个域

　　--数据控制

　　GRANT --授予用户访问权限

　　　　　　DENY --拒绝用户访问

　　　　　　REVOKE --解除用户访问权限

　　--事务控制

　　COMMIT --结束当前事务

　　　　　　ROLLBACK --中止当前事务

　　　　　　SET TRANSACTION --定义当前事务数据访问特征

　　--程序化SQL 

　　DECLARE --为查询设定游标

　　　　　　EXPLAN --为查询描述数据访问计划

　　　　　　OPEN --检索查询结果打开一个游标

　　　　　　FETCH --检索一行查询结果

　　　　　　CLOSE --关闭游标

　　　　　　PREPARE --为动态执行准备SQL 语句

　　　　　　EXECUTE --动态地执行SQL 语句

　　　　　　DESCRIBE --描述准备好的查询　　

　　---局部变量

　　declare @id char(10) 

　　　　　　--set @id = '10010001' 

　　　　　　select @id = '10010001'

　　---全局变量

　　---必须以@@开头　　

　　--IF ELSE 

　　declare @x int @y int @z int 

　　　　　　select @x = 1 @y = 2 @z=3 

　　　　　　if @x > @y 

　　　　　　print 'x > y' --打印字符串'x > y' 

　　　　　　else if @y > @z 

　　　　　　print 'y > z' 

　　　　　　else print 'z > y' 

　　　　　　--CASE 

　　　　　　use pangu 

　　　　　　update employee 

　　　　　　set e_wage = 

　　　　　　case 

　　　　　　when job_level = ’’then e_wage*1.08 

　　　　　　when job_level = ’’then e_wage*1.07 

　　　　　　when job_level = ’’then e_wage*1.06 

　　　　　　else e_wage*1.05 

　　　　　　end 

　　　　　　--WHILE CONTINUE BREAK 

　　　　　　declare @x int @y int @c int 

　　　　　　select @x = 1 @y=1 

　　　　　　while @x < 3 

　　　　　　begin 

　　　　　　print @x --打印变量x 的值

　　　　　　while @y < 3 

　　　　　　begin 

　　　　　　select @c = 100*@x + @y 

　　　　　　print @c --打印变量c 的值

　　　　　　select @y = @y + 1 

　　　　　　end 

　　　　　　select @x = @x + 1 

　　　　　　select @y = 1 

　　　　　　end 

　　　　　　--WAITFOR 

　　--例等待小时分零秒后才执行Select 语句

　　waitfor delay ’:02:03’

　　　　　　select * from employee 

　　　　--例等到晚上点零分后才执行Select 语句

　　　　waitfor time ’:08:00’

Select

　　select *(列名) from table_name(表名) where column_name operator value ex宿主) 

　　　　select * from stock_information where stockid = str(nid) 

　　　　stockname = 'str_name' 

　　　　stockname like '% find this %' 

　　　　stockname like '[a-zA-Z]%' --------- ([]指定值的范围) 

　　　　stockname like '[^F-M]%' --------- (^排除指定范围) 

　　　　--------- 只能在使用like关键字的where子句中使用通配符) 

　　　　or stockpath = 'stock_path' 

　　　　or stocknumber < 1000 

　　　　and stockindex = 24 

　　　　not stocksex = 'man' 

　　　　stocknumber between 20 and 100 

　　　　stocknumber in(10,20,30) 

　　　　order by stockid desc(asc) --------- 排序，desc-降序，asc-升序

　　　　order by 1,2 --------- by列号

　　　　stockname = (select stockname from stock_information where stockid = 4) 

　　　　--------- 子查询

　　　　--------- 除非能确保内层select只返回一个行的值

　　　　--------- 否则应在外层where子句中用一个in限定符

　　　　select distinct column_name form table_name 

　　--------- distinct指定检索独有的列值，不重复

　　　　select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name 

　　　　select stockname , "stocknumber" = count(*) from table_name group by stockname 

　　　　--------- group by 将表按行分组,指定列中有相同的值

　　　　having count(*) = 2 --------- having选定指定的组

　　select * 

　　　　from table1, table2 

where table1.id *= table2.id -------- 左外部连接，table1中有的而table2中没有得以null表示

　　　　table1.id =* table2.id -------- 右外部连接

　　　　select stockname from table1 

　　　　union [all] -------- union合并查询结果集，all-保留重复行

　　　　select stockname from table2

　　insert

　　insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx" 

　　value (select Stockname , Stocknumber from Stock_table2) 

　　-------value为select语句　　

　　update

　　update table_name set Stockname = "xxx" [where Stockid = 3] 

　　Stockname = default 

　　Stockname = null 

　　Stocknumber = Stockname + 4

　　delete

　　delete from table_name where Stockid = 3 

　　truncate table_name --------- 删除表中所有行，仍保持表的完整性

　　drop table table_name --------- 完全删除表　

　　alter table -------- 修改数据库表结构　　

　　alter table database.owner.table_name add column_name char(2) null .. 

　　sp_help table_name -------- 显示表已有特征

　　create table table_name (name char(20), age smallint, lname varchar(30)) 

　　insert into table_name select -------- 实现删除列的方法（创建新表）

　　alter table table_name drop constraint Stockname_default 

　　--------- 删除Stockname的default约束

常用函数(function)

转换函数

convert(数据类型,值,格式)

　　统计函数

　　AVG --求平均值

　　COUNT --统计数目

　　MAX --求最大值

　　MIN --求最小值

　　SUM --求和　

　　AVG 

　　use pangu 

　　select avg(e_wage) as dept_avgWage 

　　from employee 

　　group by dept_id

　　MAX 

　　--求工资最高的员工姓名

　　use pangu 

　　select e_name 

　　from employee 

　　where e_wage = 

　　(select max(e_wage) 

　　from employee)

　　STDEV() 

　　--STDEV()函数返回表达式中所有数据的标准差

　　--STDEVP() 

　　--STDEVP()函数返回总体标准差　　

　　VAR() 

　　--VAR()函数返回表达式中所有值的统计变异数　　

　　VARP() 

　　--VARP()函数返回总体变异数　　

　　算术函数　　

　　三角函数

　　SIN(float_expression) --返回以弧度表示的角的正弦

　　COS(float_expression) --返回以弧度表示的角的余弦

　　TAN(float_expression) --返回以弧度表示的角的正切

　　COT(float_expression) --返回以弧度表示的角的余切

　　反三角函数

　　ASIN(float_expression) --返回正弦是FLOAT 值的以弧度表示的角

　　ACOS(float_expression) --返回余弦是FLOAT 值的以弧度表示的角

　　ATAN(float_expression) --返回正切是FLOAT 值的以弧度表示的角

　　ATAN2(float_expression1,float_expression2) 

　　------返回正切是float_expression1 /float_expres-sion2的以弧度表示的角

　　DEGREES(numeric_expression) 

　　------把弧度转换为角度返回与表达式相同的数据类型可为

　　------INTEGER/MONEY/REAL/FLOAT 类型

　　RADIANS(numeric_expression) 

------把角度转换为弧度返回与表达式相同的数据类型可为

　　------INTEGER/MONEY/REAL/FLOAT 类型

　　EXP(float_expression) --返回表达式的指数值

　　LOG(float_expression) --返回表达式的自然对数值

　　LOG10(float_expression)--返回表达式的以为底的对数值

　　SQRT(float_expression) --返回表达式的平方根

　　取近似值函数

　　CEILING(numeric_expression) 

-------返回>=表达式的最小整数返回的数据类型与表达式相同可为

　　-------INTEGER/MONEY/REAL/FLOAT 类型

　　FLOOR(numeric_expression) 

-------返回<=表达式的最小整数返回的数据类型与表达式相同可为

　　-------INTEGER/MONEY/REAL/FLOAT 类型

　　ROUND(numeric_expression) 

-------返回以integer_expression 为精度的四舍五入值返回的数据

　　-------类型与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型

　　ABS(numeric_expression) 

-------返回表达式的绝对值返回的数据类型与表达式相同可为

　　-------INTEGER/MONEY/REAL/FLOAT 类型

　　SIGN(numeric_expression) 

-------测试参数的正负号返回零值正数或-1 负数返回的数据类型

　　-------与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型

　　PI() -------返回值为π即.1415926535897936 

　　RAND([integer_expression]) 

-------用任选的[integer_expression]做种子值得出-1 间的随机浮点数

字符串函数

　　ASCII() ------函数返回字符表达式最左端字符的ASCII 码值

　　CHAR() ------函数用于将ASCII 码转换为字符

　　------如果没有输入~ 255 之间的ASCII 码值CHAR 函数会返回一个NULL 值

　　LOWER() ------函数把字符串全部转换为小写

　　UPPER() ------函数把字符串全部转换为大写

　　STR() ------函数把数值型数据转换为字符型数据

　　LTRIM() ------函数把字符串头部的空格去掉

　　RTRIM() ------函数把字符串尾部的空格去掉

　　LEFT(),RIGHT(),SUBSTRING() --函数返回部分字符串

　　CHARINDEX(),PATINDEX() --函数返回字符串中某个指定的子串出现的开始位置

　　SOUNDEX() ------函数返回一个四位字符码

　　------SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字均只返回值

　　DIFFERENCE() ------函数返回由SOUNDEX 函数返回的两个字符表达式的值的差异

　　------0 两个SOUNDEX 函数返回值的第一个字符不同

　　------1 两个SOUNDEX 函数返回值的第一个字符相同

　　------2 两个SOUNDEX 函数返回值的第一二个字符相同

　　------3 两个SOUNDEX 函数返回值的第一二三个字符相同

　　------4 两个SOUNDEX 函数返回值完全相同同

　　QUOTENAME() ------函数返回被特定字符括起来的字符串

　　/**//*select quotename('abc', '{') quotename('abc') 

　　运行结果如下

　　{ 

　　{abc} [abc]*/ 

　　REPLICATE() ------函数返回一个重复character_expression 指定次数的字符串

　　/**//*select replicate('abc', 3) replicate( 'abc', -2) 

　　运行结果如下

　　abcabcabc NULL*/ 

　　REVERSE() ------函数将指定的字符串的字符排列顺序颠倒

　　REPLACE() ------函数返回被替换了指定子串的字符串

　　/**//*select replace('abc123g', '123', 'def') 

　　运行结果如下

　　abcdefg*/

　　SPACE() ------函数返回一个有指定长度的空白字符串

　　STUFF() ------函数用另一子串替换字符串指定位置长度的子串　　

　　数据类型转换函数

　　CAST() 函数语法如下

　　CAST() ( AS [ length ]) 

　　CONVERT() 函数语法如下

　　CONVERT() ([ length ], [, style]) 

　　select cast(100+99 as char) convert(varchar(12), getdate()) 

　　运行结果如下

　　Jan 15 2000

　　日期函数

　　DAY() ------函数返回date_expression 中的日期值

　　MONTH() ------函数返回date_expression 中的月份值

　　YEAR() ------函数返回date_expression 中的年份值

　　DATEADD( , ,) 

　　-----函数返回指定日期date 加上指定的额外日期间隔number 产生的新日期

　　DATEDIFF( , ,) 

　　-----函数返回两个指定日期在datepart 方面的不同之处

　　DATENAME( , ------函数以字符串的形式返回日期的指定部分

　　DATEPART( , ------函数以整数值的形式返回日期的指定部分

　　GETDATE() ------函数以DATETIME 的缺省格式返回系统当前的日期和时间　　

　　系统函数

　　APP_NAME() ------函数返回当前执行的应用程序的名称

　　COALESCE() -----函数返回众多表达式中第一个非NULL 表达式的值

　　COL_LENGTH(<'table_name'>, <'column_name'> ----函数返回表中指定字段的长度值

　　COL_NAME(, ----函数返回表中指定字段的名称即列名

　　DATALENGTH() -----函数返回数据表达式的数据的实际长度

　　DB_ID(['database_name']) ------函数返回数据库的编号

　　DB_NAME(database_id) ------函数返回数据库的名称

　　HOST_ID() -----函数返回服务器端计算机的名称

　　HOST_NAME() -----函数返回服务器端计算机的名称

　　IDENTITY([, seed increment]) [AS column_name]) 

　　--IDENTITY() 函数只在Select INTO 语句中使用用于插入一个identity column列到新表中

　　/**//*select identity(int, 1, 1) as column_name 

　　into newtable 

　　from oldtable*/ 

　　ISDATE() ----函数判断所给定的表达式是否为合理日期

　　ISNULL(, --函数将表达式中的NULL 值用指定值替换

　　ISNUMERIC() ----函数判断所给定的表达式是否为合理的数值

　　NEWID() ----函数返回一个UNIQUEIDENTIFIER 类型的数值

　　NULLIF(, 

　　----NULLIF 函数在expression1 与expression2 相等时返回NULL 值若不相等时则返回xpression1 的值

日期处理

1、日期格式化处理

DECLARE @dt datetime

SET @dt=GETDATE()

--1．短日期格式：yyyy-m-d

SELECT REPLACE(CONVERT(varchar(10),@dt,120),N'-0','-')

--2．长日期格式：yyyy年mm月dd日

--A. 方法

SELECT STUFF(STUFF(CONVERT(char(8),@dt,112),5,0,N'年'),8,0,N'月')+N'日'

--B. 方法

SELECT DATENAME(Year,@dt)+N'年'+DATENAME(Month,@dt)+N'月'+DATENAME(Day,@dt)+N'日'

--3．长日期格式：yyyy年m月d日

SELECT DATENAME(Year,@dt)+N'年'+CAST(DATEPART(Month,@dt) AS varchar)+N'月'+DATENAME(Day,@dt)+N'日'

--4.完整日期+时间格式：yyyy-mm-dd hh:mi:ss:mmm

SELECT CONVERT(char(11),@dt,120)+CONVERT(char(12),@dt,114)

2、日期推算处理

DECLARE @dt datetime

SET @dt=GETDATE()

DECLARE @number int

SET @number=3

--1．指定日期该年的第一天或最后一天

--A. 年的第一天

SELECT CONVERT(char(5),@dt,120)+'1-1'

--B. 年的最后一天

SELECT CONVERT(char(5),@dt,120)+'12-31'

--2．指定日期所在季度的第一天或最后一天

--A. 季度的第一天

SELECT CONVERT(datetime,

CONVERT(char(8),

DATEADD(Month,

DATEPART(Quarter,@dt)*3-Month(@dt)-2,

@dt),

120)+'1')

--B. 季度的最后一天（CASE判断法）

SELECT CONVERT(datetime,

CONVERT(char(8),

DATEADD(Month,

DATEPART(Quarter,@dt)*3-Month(@dt),

@dt),

120)

+CASE WHEN DATEPART(Quarter,@dt) in(1,4)

THEN '31'ELSE '30' END)

--C. 季度的最后一天（直接推算法）

SELECT DATEADD(Day,-1,

CONVERT(char(8),

DATEADD(Month,

1+DATEPART(Quarter,@dt)*3-Month(@dt),

@dt),

120)+'1')

--3．指定日期所在月份的第一天或最后一天

--A. 月的第一天

SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')

--B. 月的最后一天

SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')

--C. 月的最后一天（容易使用的错误方法）

SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))

--4．指定日期所在周的任意一天

SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)

--5．指定日期所在周的任意星期几

--A.  星期天做为一周的第天

SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)

--B.  星期一做为一周的第天

SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)