数据库ms SQL常用语句二

-------------转换函数---------------------------------------------------------
print cast(123 as varchar(20))+'abc'

print convert(varchar(20),123)+'abc'

print str(123)+'abc'


语法
使用 CAST：

CAST ( expression AS data_type )

使用 CONVERT：

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

参数
expression

是任何有效的 Microsoft? SQL Server? 表达式。有关更多信息，请参见表达式。

data_type

目标系统所提供的数据类型，包括 bigint 和 sql_variant。不能使用用户定义的数据类型。有关可用的数据类型的更多信息，请参见数据类型。

length

nchar、nvarchar、char、varchar、binary 或 varbinary 数据类型的可选参数。

style

日期格式样式，借以将 datetime 或 smalldatetime 数据转换为字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型）；或者字符串格式样式，借以将 float、real、money 或 smallmoney 数据转换为字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型）。

SQL Server 支持使用科威特算法的阿拉伯样式中的数据格式。

在表中，左侧的两列表示将 datetime 或 smalldatetime 转换为字符数据的 style 值。给 style 值加 100，可获得包括世纪数位的四位年份 (yyyy)。




使用带有 LIKE 子句的 CAST
下面的示例将 int 列（ytd_sales 列）转换为 char(20) 列，以便使用 LIKE 子句。

USE pubs
GO
SELECT title, ytd_sales
FROM titles
WHERE CAST(ytd_sales AS char(20)) LIKE '15%'
AND type = 'trad_cook'
GO


SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CONVERT(char(20), ytd_sales) LIKE '3%'


-----------------case和select语句结合（注意没有逗号）------------------------------------------------------------
select 别名=case 列名
when '值' then '新值，哈哈'
when '值2' then '我是值2'
else '我什么都是不是'
end
from table


综合例子：
SELECT Category = CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking' ELSE 'Not yet categorized' END,
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type




--B. 使用带有简单 CASE 函数和 CASE 搜索函数的 SELECT 语句

USE pubs
GO
SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price WHEN price >= 10 and price ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO
--C. 使用带有 SUBSTRING 和 SELECT 的 CASE 函数
USE pubs
SELECT SUBSTRING((RTRIM(a.au_fname) + ' '+
RTRIM(a.au_lname) + ' '), 1, 25) AS Name, a.au_id, ta.title_id,
Type =
CASE
WHEN SUBSTRING(ta.title_id, 1, 2) = 'BU' THEN 'Business'
WHEN SUBSTRING(ta.title_id, 1, 2) = 'MC' THEN 'Modern Cooking'
WHEN SUBSTRING(ta.title_id, 1, 2) = 'PC' THEN 'Popular Computing'
WHEN SUBSTRING(ta.title_id, 1, 2) = 'PS' THEN 'Psychology'
WHEN SUBSTRING(ta.title_id, 1, 2) = 'TC' THEN 'Traditional Cooking'
END
FROM titleauthor ta JOIN authors a ON ta.au_id = a.au_id

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


---------goto语句---------------------------------
declare @sum int, @count int
select @sum=0, @count=1
label_1:
select @sum=@sum+@count
select @count=@count+1
if @count goto label_1
select 计数器的值=@count ,和的值=@sum

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



-----------------某段存储过程欣赏-----------------------------------------------
Create Procedure update_title @title char(20),@title_id varchar(20)='tid111'
With encryption,recompile
as
Update titles set Title=@title Where title_id=@title_id
-----------------------------------------------------------------




------------------某段触发器欣赏-------------------------------------------------
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'employee_insupd' AND type = 'TR')
DROP TRIGGER employee_insupd
GO
CREATE TRIGGER employee_insupd ON employee FOR INSERT, UPDATE
AS
DECLARE @min_lvl tinyint,
@max_lvl tinyint,
@emp_lvl tinyint,
@job_id smallint
SELECT @min_lvl = min_lvl, @max_lvl = max_lvl, @emp_lvl = i.job_lvl, @job_id = i.job_id
FROM employee e INNER JOIN inserted i
ON e.emp_id = i.emp_id JOIN jobs j ON j.job_id = i.job_id

IF (@job_id = 1) and (@emp_lvl 10)
BEGIN
print 'Job id 1 expects the default level of 10.'
ROLLBACK TRANSACTION
END
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
BEGIN
print 'The level for job_id:'+cast(@job_id as varchar(5))+' should be between '+cast(@min_lvl as varchar(5))+' and '+cast(@max_lvl as varchar(5))
ROLLBACK TRANSACTION
END
/*/////////////////////////////////////////////////////////*/
--(c)在视图CustomersView上创建一个INSTEAD OF触发器：
CREATE TRIGGER Customers_Update2 ON CustomersView
INSTEAD OF UPDATE
AS
DECLARE @Country nvarchar(15)
SET @Country = (SELECT Country FROM Inserted)
IF @Country = 'Germany'
BEGIN
UPDATE CustomersGer
SET CustomersGer.Phone = Inserted.Phone
FROM CustomersGer JOIN Inserted
ON CustomersGer.CustomerID = Inserted.CustomerID
END
ELSE
IF @Country = 'Mexico'
BEGIN
UPDATE CustomersMex
SET CustomersMex.Phone = Inserted.Phone
FROM CustomersMex JOIN Inserted
ON CustomersMex.CustomerID = Inserted.CustomerID
END
-------------------------------------------------------------------


create trigger 触发器名 on 视图名
INSTEAD OF insert as print '视图执行了insert操张'
--在视图上创建触发器，用instead of

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

sp_helptrigger abc --查看触发器abc的相关信息

-------------------------------------------------------------------------
执行存储过程的二种方式：
exec GetOrderDetails2
@enddate='1998-5-30',
@startdate='1997-7-1',
@country='USA'
--或：
exec GetOrderDetails2
'1997-7-1','1998-5-30','USA'
----------------------------------------------------------------------------

**************游标cursor**********************************************

SQL Server支持四种服务器游标类型：
(a) static 基本上不监测变化
(b) dynamic 可监测变化
(c) forward 只可以取后面的数据
(d) keyset 介于static 和 dynamic之间

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

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

----------------------------------------------------------------------------
DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]

----------定义游标的一个实例------------------------------------------------------------------
DECLARE Employee_Cursor
CURSOR FOR -----SQL语句
SELECT au_lname, au_fname, phone
FROM authors
WHERE au_lname LIKE 'Ring%'
OPEN Employee_Cursor ---打开游标
FETCH NEXT FROM Employee_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor

--------------------------------------------------------------------
FETCH?[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar }?| RELATIVE { n | @nvar }]?FROM]{ { [ GLOBAL ] cursor_name } | @cursor_variable_name } [ INTO @variable_name [ ,...n ] ]
从 Transact-SQL 服务器游标中检索特定的一行

--------------------------------------------------------------------------
declare aa cursor scroll for select * from cursorabc --定义一个可以上下移动的scroll游标
open aa --打开游标


while @@fetch_status=0 --用while循行把所有记录集读取出来
fetch next from aa


fetch absolute 5 from aa --定位到记录集第五行
fetch next from aa --定位到下一行
fetch prior from aa --定位到上一行
fetch first from aa --定位到第一行
fetch last from aa --定位到最后一行
fetch relative -4 from aa --相对后退四条记录




if @@FETCH_STATUS =0
print '返回0，说明读取记录正常，并没有到最后一条或第一条记录'
else
print '不是返回0，说明指针到了最后或在最开始，或没有记录集'

print '该记录集的总行数为：'+cast(@@cursor_rows as varchar(5)) --该记录集的总行数


close aa
deallocate aa

----------------------------------------------------------------------------------------------
DECLARE @price money
DECLARE @get_price CURSOR
SET @get_price = CURSOR FOR SELECT price FROM titles
OPEN @get_price
FETCH NEXT FROM @get_price INTO @price
SELECT price FROM titles
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @Price UPDATE titles SET price =
(@price + (@price * .1))
WHERE CURRENT OF @get_price
ELSE
UPDATE titles SET price =
(@price + (@price * .05))
WHERE CURRENT OF @get_price
FETCH NEXT FROM @get_price INTO @price
END
SELECT price FROM titles
CLOSE @get_price
DEALLOCATE @get_price
-------------------------------------------------------------------------------------------------
SET NOCOUNT ON
DECLARE @au_id varchar(11),
@au_fname varchar(20),
@au_lname varchar(40),
@message varchar(80),
@title varchar(80)
PRINT '---California Authors report ---'
DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname FROM authors
WHERE state = 'CA' ORDER BY au_id

OPEN authors_cursor
FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ''
SELECT @message = '----- Books by Author: ' + @au_fname + ' ' + @au_lname
PRINT @message
-- Declare an inner cursor based
-- on au_id from the outer cursor.
DECLARE titles_cursor CURSOR FOR SELECT t.title FROM titleauthor ta, titles t
WHERE ta.title_id = t.title_id AND ta.au_id = @au_id
-- Variable value from the outer cursor
OPEN titles_cursor
FETCH NEXT FROM titles_cursor INTO @title
IF @@FETCH_STATUS 0
PRINT 'No Books'
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message ='' + @title
PRINT @message
FETCH NEXT FROM titles_cursor INTO @title
END
CLOSE titles_cursor
DEALLOCATE titles_cursor
-- Get the next author.
FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lname
END
CLOSE authors_cursor
DEALLOCATE authors_cursor
GO
------------------------------------------------------------------------------------------------

--------------借助临时表实现和用游标同样的效果--------------------------------------------------------
--定义临时表时，只要在表名前加#井字符即可，如：select * into #temptable from tablename

use pubs
go
select * into titles2 from titles
select * into #temp1 from titles2 where price select * into #temp2 from titles2 where price>=20
go
UPDATE #temp1 SET price = price+(price*0.1) WHERE price UPDATE #temp2 SET price = price +(price * 0.05) WHERE price >= 20
go
delete from titles2
insert into titles2 select * from #temp1
insert into titles2 select * from #temp2
go
select * from titles
go
select * from titles2