1 类型转换 Cast(10.947 AS INT) 或Convert(INT,10.947)
2 经常用到的函数
newid() 用于创建GUID值，round(price,2) 四舍五入
isnull(summary,0) 如果summary为空，则返回0
substring(‘abcdefg’,2,3) 取子字符串
replace(‘abcdefg’,’abc’,’123’) 替换字符串
rand() 求0-1之间的随机数
dateAdd(day, 21, getdate()) 在向指定日期加上一段时间的基础上，返回新的 datetime 值
datePart(month, GETDATE()) 返回代表指定日期的指定日期部分的整数
3 动态构建SQL
exec (‘ SELECT [job_id], [job_desc] FROM [pubs].[dbo].[jobs] ‘)
或 exec sp_executesql N’SELECT [job_id], [job_desc] FROM [pubs].[dbo].[jobs]’
字段名，表名，数据库名作变量时，必须用动态SQL
declare @fname varchar(20)
set @fname='job_id'
exec (' SELECT '+ @fname+ ' FROM [pubs].[dbo].[jobs] ')
4 在数据导入导出功能时，如果是SQL Server 之间相互拷贝数据，并且表的字段类型含有identity值，则应该使用下面的脚本关闭自增，导入导出完毕后再打开
set identity_insert 表名 on --关闭表的identity属性作用
set identity_insert 表名 off --打开表的identity属性作用
5 CASE语句的写法
SELECT Price=CASE WHEN Price IS NULL THEN ‘not yeat’
WHEN Price<10 THEN ‘Cheap’
WHEN Price>10 THEN ‘Expensive’
END
或是把需要比较的列值放到when的后面。
SELECT Gender=
CASE 1 THEN ‘男’
CASE 0 THEN ‘女’
ELSE ‘not yet’
END
CASE 语句常用于行列转换
SELECT 部门,
[材料1]= SUM(CASE 材料 WHEN ‘材料1’ THEN 数量 ELSE 0 END) ，
[材料2]= SUM(CASE 材料 WHEN ‘材料2’ THEN 数量 ELSE 0 END)
FROM 部门耗材
GROUP BY 部门
6 分页 查询第X页,每页Y条记录
如果表中有主键
select top y * from 表 where 主键 not in(select top (x-1)*y 主键 from 表)
如果表中无主键,可以用临时表,加标识字段解决
select id=identity(int,1,1),* into #tb from 表
select * from #tb where id between (x-1)*y and x*y-1
7 EXISTS的用法
SELECT DISTINCT pub_name FROM publishers
WHERE EXISTS
(SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type = 'business')
8 流水号生成
生成长度为8的编号，编号以BH开头，其余6位为流水号
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO
--在表中应用函数
CREATE TABLE tb(
BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)
9
生成的编号长度为12，前6位为日期信息，格式为YYMMDD，后6位为流水号
--创建得到当前日期的视图
CREATE VIEW v_GetDate AS
SELECT dt=CONVERT(CHAR(6),GETDATE(),12) GO
--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(12)
AS
BEGIN
DECLARE @dt CHAR(6)
SELECT @dt=dt FROM v_GetDate
RETURN(
SELECT @dt+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6)
FROM tb WITH(XLOCK,PAGLOCK)
WHERE BH like @dt+'%')
END
GO
--在表中应用函数
CREATE TABLE tb(
BH char(12) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)
9 选择不重复的字符串，表的数据如下，假设列名为ID,表名为tbl
aaa
bbb
ccc
aaa
1) DISTINCT SELECT DISTINCT ID FROM tbl
2) 分组 SELECT ID FROM tbl GROUP BY ID Having COUNT(*)>1
10 字符串用在where语句中判断是否相等的时候，提示cannot resolve collection conflict for equal to operation,
应该加上database default
11 SQL Server 字符串类型是大小写不敏感的，aa,AA是同样的含义。
有时候确需要实现大小写字符敏感，比如密码。aa,AA表示不同的密码。
为实现字符串大小写敏感，可以把字符串转话为二进制后再作比较。
CAST(Password AS varbinary)
12 SQL 错误处理
检测@@error变量的值，发生错误时，该值不为0
if @@error<>0
print '发生错误1'
不是严重的错误,所以SQL会执行下去;
属于严重的错误,所以SQL没有执行下去;
被调用的存储过程发生严重错误时,调用它的存储过程可以捕获错误,并可以继续执行下去;
SQL Server 2005的用法
BEGIN TRY
DELETE FROM IPR WHERE TransationID= 1003
END TRY
BEGIN CATCH
END CATCH
13 子查询
求工资最高的员工的姓名
SELECT name FROM Employee
WHERE wage=(SELECT MAX(wage) FROM Employee)
求评论最多的文章
SELECT Title FROM Post
WHERE Reply=(SELECT MAX(Reply) FROM Post)