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
select top ÿҳÊýÁ¿ * id
from @t a
where id not in
(select top (µÚ¼¸Ò³-1)*ÿҳÊýÁ¿ id
from @t b
)
*/
declare @lcSqlCommand nvarchar(100)
declare @t table (id int IDENTITY,orderDate datetime)
insert into @t
select orderDate
&nb ......
Ë÷ÒýÀàÐÍ
ΨһË÷Òý£ºÎ¨Ò»Ë÷Òý²»ÔÊÐíÁ½ÐоßÓÐÏàͬµÄË÷ÒýÖµ
Ö÷¼üË÷Òý£ºÎª±í¶¨ÒåÒ»¸öÖ÷¼ü½«×Ô¶¯´´½¨Ö÷¼üË÷Òý£¬Ö÷¼üË÷ÒýÊÇΨһË÷ÒýµÄÌØÊâÀàÐÍ¡£Ö÷¼üË÷ÒýÒªÇóÖ÷¼üÖеÄÿ¸öÖµÊÇΨһµÄ£¬²¢ÇÒ²»ÄÜΪ¿Õ
¾Û¼¯Ë÷Òý(Clustered)£º±íÖи÷ÐеÄÎïÀí˳ÐòÓë¼üÖµµÄÂß¼£¨Ë÷Òý£©Ë³ÐòÏàͬ£¬Ã¿¸ö±íÖ»ÄÜÓÐÒ»¸ö
·Ç¾Û¼¯Ë÷Òý(Non-clustered)£º·Ç¾Û ......
--desc ±íÃû ÃèÊö±íµÄÄÚÈÝ
desc emp;
--¼ÓÉÏÊýѧ±í´ïʽºÍÁÐÃû ""±£³Ö¸ñʽ
select ename "name space", sal*12 year_sal from emp;
select 2*3 from dual;
select sysdate from dual;
--¿ÕÖµµÄÊýѧ±í´ïʽ ½á¹û¶¼ÊÇ¿ÕÖµ
select ename, sal*12 + comm from emp;
- ......
×¢'svw'Ϊ³öÎÊÌâµÄÊý¾Ý¿â,´Ë·½Ê½¶Ôsql7.0ÒÔÉϰ汾ÓÐЧ,ÆäËüµÍ°æ±¾Îª²âÊÔ
sp_configure 'allow',1
go
reconfigure with override
go
update sysdatabases set status=32768 where name='svw'
go
dbcc rebuild_log('svw','D:\mssql7\data ......
ÉÏÉϸöÐÇÆÚ£¬ÓÐÈË·´À¡£¬CSDNÓÐSQL×¢Èë¶¶´£¬º¹ÑÕ£¬¼¸ÄêǰΪSQL×¢Èë¶¶´£¬²¿ÃÅרÃŶÔËùÓдúÂë×ö¹ýÒ»´Î·Ç³£´óµÄ¼ì²é£¬¾¹È»ÄǴμì²é»¹ÓÐÒÅ©µÄµØ·½¡£×î½üÕ⼸¸öÐÇÆÚ£¬¾ÍÊÇÒ»Ö±ÔÙ¶Ô´úÂë×öÔٴθ´²é£¬¿´ÓÐûÓÐSQL×¢Èë¶¶´¡£
´æÔÚSQL×¢Èë¶¶´£¬¾ÍÒòΪÄãµÄSQLÓï¾äÊÇ×Ô¼ºÆ´´ ......