sql¶à±íÁ¬½Ó²éѯ
inner join,full outer join,left join,right jion
ÄÚ²¿Á¬½Ó inner join Á½±í¶¼Âú×ãµÄ×éºÏ
full outer È«Á¬ Á½±íÏàͬµÄ×éºÏÔÚÒ»Æð£¬A±íÓУ¬B±íûÓеÄÊý¾Ý£¨ÏÔʾΪnull£©,ͬÑùB±íÓÐ
A±íûÓеÄÏÔʾΪ(null)
A±í left join B±í ×óÁ¬,ÒÔA±íΪ»ù´¡£¬A±íµÄÈ«²¿Êý¾Ý£¬B±íÓеÄ×éºÏ¡£Ã»ÓеÄΪnull
A±í right join B±í ÓÒÁ¬,ÒÔB±íΪ»ù´¡£¬B±íµÄÈ«²¿Êý¾Ý£¬A±íµÄÓеÄ×éºÏ¡£Ã»ÓеÄΪnull
²éѯ·ÖÎöÆ÷ÖÐÖ´ÐУº
Sql´úÂë
--½¨±ítable1,table2£º
create table table1(id int,name varchar(10))
create table table2(id int,score int)
insert into table1 select 1,'lee'
insert into table1 select 2,'zhang'
insert into table1 select 4,'wang'
insert into table2 select 1,90
insert into table2 select 2,100
insert into table2 select 3,70
--½¨±ítable1,table2£º
create table table1(id int,name varchar(10))
create table table2(id int,score int)
insert into table1 select 1,'lee'
insert into table1 select 2,'zhang'
insert into table1 select 4,'wang'
insert into table2 select 1,90
insert into table2 select 2,100
insert into table2 select 3,70
Èç±í
-------------------------------------------------
table1|table2|
-------------------------------------------------
idname|idscore|
1lee|190|
2zhang|2100|
4wang|370|
-------------------------------------------------
ÒÔϾùÔÚ²éѯ·ÖÎöÆ÷ÖÐÖ´ÐÐ
Ò»¡¢ÍâÁ¬½Ó
1.¸ÅÄ°üÀ¨×óÏòÍâÁª½Ó¡¢ÓÒÏòÍâÁª½Ó»òÍêÕûÍⲿÁª½Ó
2.×óÁ¬½Ó£ºleft join »ò left outer join
(1)×óÏòÍâÁª½ÓµÄ½á¹û¼¯°üÀ¨ LEFT OUTER ×Ó¾äÖÐÖ¸¶¨µÄ×ó±íµÄËùÓÐÐУ¬¶ø²»½ö½öÊÇÁª½ÓÁÐËùÆ¥ÅäµÄÐС£Èç¹û×ó±íµÄijÐÐÔÚÓÒ±íÖÐûÓÐÆ¥ÅäÐУ¬ÔòÔÚÏà¹ØÁªµÄ½á¹û¼¯ÐÐÖÐÓÒ±íµÄËùÓÐÑ¡ÔñÁбíÁоùΪ¿ÕÖµ(null)¡£
(2)sqlÓï¾ä
Sql´úÂë
select * from table1 left join table2 on table1.id=table2.id
select * from table1 left join tab
Ïà¹ØÎĵµ£º
1.Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)¡¡¡¡
¡¡¡¡ SQLSERVERµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬Òò´Ëfrom×Ó¾äÖÐдÔÚ×îºóµÄ±í£¨»ù´¡±ídriving table£©½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏ£¬±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í£¬µ±SQLSERVER´¦Àí¶à¸ö±íʱ£¬»áÔËÓÃÅÅÐò¼°ºÏ²¢µÄ·½Ê½Á ......
''' <summary>
''' SQLÎÄ執ÐÐ結¹û¤ÎEXCEL³öÁ¦
''' </summary>
''' <param name="connString">OLEDB½Ó続ÎÄ×ÖÁÐ</param>
''' <param name="sqlString">SQLÎÄ</param>
''' <param name="savePath">³öÁ¦¥Õ¥¡¥¤¥ë¥Ñ¥¹</param>
......
--sql structured query language
--DML--Data Manipulation Language--Êý¾Ý²Ù×÷ÓïÑÔ
query information (SELECT),
add new rows (INSERT),
modify existing rows (UPDATE),
delete existing rows (DELETE),
perform a conditional update or insert operation (MERGE),
see an execution plan of SQL (EXPLA ......
ÒÔÏÂΪ±¾ÈËÕ⼸ÌìÖØÐÂѧϰSQLSERVER2000µÄѧϰ±Ê¼Ç£¬Ä¿µÄÊÇÖØÐÂÈÏʶ¼°¹®¹Ì֪ʶµã£º
Ò»¡¢¼òµ¥µÄSELECTÓï¾äµÄÓï·¨ÈçÏ£º
SELECT |ALL|DISTINCT| <Ä¿±ê±í´ïʽ>|,<Ä¿±ê±í´ïʽ>|...
from<±í»òÊÓͼÃû>|,<±í»òÊÓͼÃû>|
[WHERE<Ìõ¼þ±í´ïʽ>]
[GROUP BY <ÁÐÃû1>|HAVING<Ìõ¼þ±í´ïʽ>| ......
·½·¨Ò»£ºÊ¹ÓÃÓαê
declare @ProductName nvarchar(50)
declare pcurr cursor for select ProductName from Products
open pcurr
fetch next from pcurr into @ProductName
while (@@fetch_status = 0)
begin
print (@ProductName)
fetch next from pcurr into @ProductName
end
close pcurr
deallocate pcurr ......