Post Jobs

SQL常用语句二,SQL下JOIN和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 + ‘ ‘

文章利用多个实例和举例说明了关于在sql中的JOIN和UNION用法,然后总结了join
是两张表做交连后里面条件相同的部分记录产生一个记录集,union是产生的两个记录集(字段要一样的)并在一起,成为一个新的记录集

?php //多文件上载系统完整版 include(“../include/common.inc”); $title =
“多个文件的上载程序”; include(“../include/header.inc”);
//定义允许上载文件的数目 define(“UPLOAD_NO”, 10); echo(“p
align=’center’font size=’4′
color=’#000080’欢迎您!br一次可以最多上载”.UPLOAD_NO.”个文件brHRn”);
if($REQUEST_METHOD!=”POST”){ echo(“form enctype=”multipart/form-data”
method=postn”); echo(“INPUT TYPE=”hidden” name=”MAX_FILE_SIZE”
value=”3000000″n”); for($i=1;$i=UPLOAD_NO;$i ){ echo(“input type=file
name=infile$i “); if($i%2==0) echo(“brn”); } echo(“brbrinput
type=”checkbox” name=”overload” value=”ON”/font font
color=’#ff0000’是否覆盖已经存在的文件?/font”); echo(“brbrinput
type=submit value=上载/formn”); } else{ //处理上载 $noinput = true;
for($i=1;$noinput($i=UPLOAD_NO);$i ){ if(${“infile”.$i}!=”none”)
$noinput = false; } if($noinput){ echo(“font size=’4′
color=’#000080’没有选定的文件,返回重试/font”); exit(); } echo(“p
align=’center’font size=’4′
color=’#000080’您选中的文件已经成功地上载到服务器的临时目录!/fontbr”);
echo(“table border=’1′ width=’84%’ height=’52’
bordercolorlight=’#008080′ bordercolordark=’#008080′ tr td width=’14%’
bgcolor=’#008000′ height=’21’font color=’#FFFFFF’文件号/font/td td
width=’52%’ bgcolor=’#008000′ height=’21’font
color=’#FFFFFF’文件名称/font/td td width=’34%’ bgcolor=’#008000′
height=’21’font color=’#FFFFFF’文件大小/font/td /tr”);
for($i=1;$i=UPLOAD_NO;$i ){ $just=${“infile”.$i.”_size”};
$fp_size[i] = $just;

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

1.JOIN和UNION区别join
是两张表做交连后里面条件相同的部分记录产生一个记录集,union是产生的两个记录集(字段要一样的)并在一起,成为一个新的记录集
。左向外联接的结果集包括 LEFT OUTER
子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值。

RIGHT JOIN 或 RIGHT OUTER JOIN。
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。

FULL JOIN 或 FULL OUTER JOIN。
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。

sql中union

请注意,UNION 内部的 SELECT
语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT
语句中的列的顺序必须相同。

sql写法内连接inner join:SELECT msp.name, party.nameFROM msp JOIN party
ON party=code或SELECT msp.name, party.nameFROM msp inner JOIN party ON
party=code左连接left join :SELECT msp.name, party.nameFROM msp LEFT
JOIN party ON party=code右连接right join :SELECT msp.name,
party.nameFROM msp RIGHT JOIN party ON msp.party=party.code

全连接(full join):SELECT msp.name, party.nameFROM msp FULL JOIN party
ON msp.party=party.code

UNION运算符将两个或更多查询的结果集组合为单个结果集,该结果集包含联合查询中的所有查询的全部行。UNION的结果集列名与UNION运算符中第一个Select语句的结果集的列名相同。另一个Select语句的结果集列名将被忽略。其中两种不同的用法是UNION和UNION
ALL,区别在于UNION从结果集中删除重复的行。如果使用UNION ALL
将包含所有行并且将不删除重复的行。

UNION和UNION ALL的区别:union 检查重复union all 不做检查比如 select ‘a’
union select ‘a’ 输出就是一行 a比如 select ‘a’ union all select ‘a’
输出就是两行 a

  1. 通过下面的例子,可以清晰的看出和理解2者的区别实例1 典型的二表连接演示

假定有两个表Table1和Table2,其包含的列和数据分别如表1.1和表1.2所示。

表1.1 Table1数据库表

ColumnA

ColumnB

ColumnC

X1

Y1

Z1

X2

Y2

Z2

X3

Y3

Z3

表1.2 Table2数据库表

ColumnA

ColumnD

ColumnE

X1

D1

E1

X2

D2

E2

X3

D3

E3

发表评论

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

相关文章

网站地图xml地图