数据类型

我们用和同样的例子来显示出 union all 和 union
的不同。同样假设我们有以下两个表格,

————-转换函数———————————————————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。不能使用用户定义的数据类型。有关可用的数据类型的更多信息,请参见数据类型。
lengthnchar、nvarchar、char、varchar、binary 或 varbinary
数据类型的可选参数。 style日期格式样式,借以将 datetime 或 smalldatetime
数据转换为字符数据;或者字符串格式样式,借以将 float、real、money 或
smallmoney 数据转换为字符数据。SQL Server
支持使用科威特算法的阿拉伯样式中的数据格式。在表中,左侧的两列表示将
datetime 或 smalldatetime 转换为字符数据的 style 值。给 style 值加
100,可获得包括世纪数位的四位年份 (yyyy)。使用带有 LIKE 子句的
CAST下面的示例将 int 列转换为 char(20) 列,以便使用 LIKE 子句。USE
pubsGOSELECT title, ytd_salesFROM titlesWHERE CAST(ytd_sales AS
char(20)) LIKE ‘15%’ AND type = ‘trad_cook’GOSELECT SUBSTRING(title, 1,
30) AS Title, ytd_salesFROM titlesWHERE CONVERT(char(20), ytd_sales)
LIKE
‘3%’—————–case和select语句结合————————————————————select
别名=case 列名 when ‘值’ then ‘新值,哈哈’when ‘值2’then ‘我是值2’else
‘我什么都是不是’ endfrom 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 PriceFROM titles WHERE price IS NOT NULL ORDER BY type, price COMPUTE
AVG(price) BY type –B. 使用带有简单 CASE 函数和 CASE 搜索函数的 SELECT
语句USE pubsGOSELECT ‘Price Category’ = CASE WHEN price IS NULL THEN
‘Not yet priced’ WHEN price 10 THEN ‘Very Reasonable Title’ WHEN price =
10 and price 20 THEN ‘Coffee Table Title’ ELSE ‘Expensive book!’ END,
CAST(title AS varchar(20)) AS ‘Shortened Title’FROM titlesORDER BY
priceGO–C. 使用带有 SUBSTRING 和 SELECT 的 CASE 函数USE pubsSELECT
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’ENDFROM
titleauthor ta JOIN authors a ON ta.au_id =
a.au_id—————————————————————————————goto语句———————————declare@sum
int, @count intselect @sum=0,
@count=1label_1:select@sum=@sum+@countselect@count=@count+1if
@count=5gotolabel_1select计数器的值=@count,和的值=@sum——————————————————————某段存储过程欣赏———————————————–Create
Procedure update_title @title char(20),@title_id
varchar(20)=’tid111’With encryption,recompileasUpdate titles set
Title=@titleWhere
title_id=@title_id———————————————————————————–某段触发器欣赏————————————————-IF
EXISTS (SELECT name FROM sysobjects WHERE name = ’employee_insupd’ AND
type = ‘TR’) DROP TRIGGER employee_insupd GOCREATE 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 iON e.emp_id =
i.emp_id JOIN jobs j ON j.job_id = i.job_id IF (@job_id = 1) and
(@emp_lvl 10)BEGINprint ‘Job id 1 expects the defaultlevel of 10.’
ROLLBACK TRANSACTION END ELSEIF 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_Update2ON CustomersViewINSTEAD 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 ENDELSEIF @Country =
‘Mexico’ BEGIN UPDATE CustomersMex SET CustomersMex.Phone =
Inserted.Phone FROM CustomersMex JOIN Inserted ON
CustomersMex.CustomerID =
Inserted.CustomerIDEND——————————————————————-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_CursorWHILE @@FETCH_STATUS =
0BEGINFETCH NEXT FROM Employee_Cursor END CLOSE
Employee_CursorDEALLOCATE 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
aafetch 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,说明读取记录正常,并没有到最后一条或第一条记录’elseprint
‘不是返回0,说明指针到了最后或在最开始,或没有记录集’print
‘该记录集的总行数为:’+cast(@@cursor_rows as
varchar(5))–该记录集的总行数close aadeallocate
aa———————————————————————————————-DECLARE
@price moneyDECLARE @get_price CURSORSET @get_price = CURSOR FORSELECT
price FROM titlesOPEN @get_priceFETCH NEXT FROM @get_price INTO
@priceSELECT price FROM titlesWHILE (@@FETCH_STATUS = 0) BEGINIF @Price
20 UPDATE titles SET price = (@price + (@price * .1)) WHERE CURRENT OF
@get_priceELSEUPDATE titles SET price = (@price + (@price * .05))
WHERE CURRENT OF @get_priceFETCH NEXT FROM @get_price INTO
@priceENDSELECT price FROM titlesCLOSE @get_priceDEALLOCATE
@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 + ‘ ‘

代码如下复制代码 CREATE TABLE [dbo].[testchar] ([a] [int]
IDENTITY (1, 1) NOT NULL ,[b] [char] (200) COLLATE
Chinese_PRC_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE
[dbo].[testvarchar] ([a] [int] IDENTITY (1, 1) NOT NULL ,[b]
[varchar] (200) COLLATE Chinese_PRC_CI_AS NULL) ON
[PRIMARY]GO–truncate table testvarchar–truncate table
testchardeclare @i as intset @i=0while @i50000begininsert into
testvarchar values(cast(@i as varchar(200)))set @i=@i+1if
@i=50000breakenddeclare @i as intset @i=0while @i50000begininsert into
testchar values(cast(@i as char(200)))set @i=@i+1if @i=50000breakend

union all 这个指令的目的也是要将两个 sql 语句的结果合并在一起。 union
all 和 union 不同之处在于 union all
会将每一笔符合条件的资料都列出来,无论资料值有无重复。

  • @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 tWHERE 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 @titleIF
    @@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_cursorDEALLOCATE
    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 tablenameuse pubsgoselect * into titles2 from
    titlesselect * into #temp1 from titles2 where price20select * into
    #temp2 from titles2 where price=20goUPDATE #temp1 SET price =
    price+(price*0.1) WHERE price 20UPDATE #temp2 SET price = price
    +(price * 0.05) WHERE price = 20godelete from titles2insert into
    titles2 select * from #temp1insert into titles2 select * from
    #temp2goselect * from titles goselect * from titles2

9188

[sql 语句 1]union all[sql 语句 2]

实验结果:

1 create table employee(2 id int,3 name nvarchar (10),4 salary int,5
start_date datetime,6 city nvarchar (10),7 region char (1))8 go12
insert into employee (id, name, salary, start_date, city, region)3
values (1, ‘jason’, 40420, ’02/01/94′, ‘new york’, ‘w’)4 go

(1 rows affected)1 insert into employee (id, name, salary,
start_date, city, region)2 values (2, ‘robert’,14420, ’01/02/95′,
‘vancouver’,’n’)3 go

(1 rows affected)1 insert into employee (id, name, salary,
start_date, city, region)2 values (3, ‘celia’, 24020, ’12/03/96′,
‘toronto’, ‘w’)3 go

(1 rows affected)1 insert into employee (id, name, salary,
start_date, city, region)2 values (4, ‘linda’, 40620, ’11/04/97′,
‘new york’, ‘n’)3 go

(1 rows affected)1 insert into employee (id, name, salary,
start_date, city, region)2 values (5, ‘david’, 80026, ’10/05/98′,
‘vancouver’,’w’)3 go

(1 rows affected)1 insert into employee (id, name, salary,
start_date, city, region)2 values (6, ‘james’, 70060, ’09/06/99′,
‘toronto’, ‘n’)3 go

(1 rows affected)1 insert into employee (id, name, salary,
start_date, city, region)2 values (7, ‘alison’,90620, ’08/07/00′,
‘new york’, ‘w’)3 go

(1 rows affected)1 insert into employee (id, name, salary,
start_date, city, region)2 values (8, ‘chris’, 26020, ’07/08/01′,
‘vancouver’,’n’)3 go

(1 rows affected)1 insert into employee (id, name, salary,
start_date, city, region)2 values (9, ‘mary’, 60020, ’06/09/02′,
‘toronto’, ‘w’)3 go

(1 rows affected)12 select * from employee3 goid name salary
start_date city region———– ———- ———–
———————– ———- —— 1 jason 40420 1994-02-01
00:00:00.000 new york w 2 robert 14420 1995-01-02 00:00:00.000
vancouver n 3 celia 24020 1996-12-03 00:00:00.000 toronto w 4 linda
40620 1997-11-04 00:00:00.000 new york n 5 david 80026 1998-10-05
00:00:00.000 vancouver w 6 james 70060 1999-09-06 00:00:00.000 toronto
n 7 alison 90620 2000-08-07 00:00:00.000 new york w 8 chris 26020
2001-07-08 00:00:00.000 vancouver n 9 mary 60020 2002-06-09
00:00:00.000 toronto w

(9 rows affected)123 select id, name from employee where name like
‘c%’4 union all5 select id, name from employee where name like ‘a%’6
goid name———– ———- 3 celia 8 chris 7 alison

(3 rows affected)12 drop table employee3 go1

数据类型

union all 的语法如下:

22250

char

2000000

44186

Testchar:73.94MTestvarchar:7.94M

  1. char 固定长度,最长n个字符。

  2. varchar 最大长度为n的可变字符串。

如果我们将长度设为10:char(10), varchar(10), 然后我们将值设为:
apple。Char(10) 存储的值会是:apple .Varchar(10) 存储的值会是:apple.

duration

可以存储 unicode

varchar

When the collation code page uses double-byte characters, the storage
size is still n bytes. Depending on the character string, the storage
size of n bytes may be less than n characters.

Fixed-length (char) or variable-length (varchar) character data types.

4156

Char 和 Varchar 不能存储 Unicode 字符。

发表评论

电子邮件地址不会被公开。 必填项已用*标注