Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

sqlÓï¾ä¼¯ºÏ

A¡£
SQLÓï¾äµÄ²¢¼¯UNION£¬½»¼¯JOIN(ÄÚÁ¬½Ó£¬ÍâÁ¬½Ó)£¬½»²æÁ¬½Ó(CROSS
JOINµÑ¿¨¶û»ý)£¬²î¼¯(NOT IN)
1.
a. ²¢¼¯UNION
SELECT column1, column2 from table1
UNION
SELECT column1, column2 from table2
b. ½»¼¯JOIN
SELECT * from table1 AS a JOIN table2 b ON a.name=b.name
c. ²î¼¯NOT IN
SELECT * from table1 WHERE name NOT IN(SELECT name from table2)
d. µÑ¿¨¶û»ý
SELECT * from table1 CROSS JOIN table2
Óë
SELECT * from table1,table2Ïàͬ
2. SQLÖеÄUNION
UNIONÓëUNION ALLµÄÇø±ðÊÇ£¬Ç°Õß»áÈ¥³ýÖØ¸´µÄÌõÄ¿£¬ºóÕß»áÈԾɱ£Áô¡£
a. UNION
SQL Statement1
UNION
SQL Statement2
b. UNION ALL
SQL Statement1
UNION ALL
SQL Statement2
3. SQLÖеĸ÷ÖÖJOIN
SQLÖеÄÁ¬½Ó¿ÉÒÔ·ÖΪÄÚÁ¬½Ó£¬ÍâÁ¬½Ó£¬ÒÔ¼°½»²æÁ¬½Ó
(¼´Êǵѿ¨¶û»ý)
a. ½»²æÁ¬½ÓCROSS JOIN
Èç¹û²»´øWHEREÌõ¼þ×Ӿ䣬Ëü½«»á·µ»Ø±»Á¬½ÓµÄÁ½¸ö±íµÄµÑ¿¨¶û»ý£¬·µ»Ø½á¹ûµÄÐÐÊýµÈÓÚÁ½¸ö±íÐÐÊýµÄ³Ë»ý£»
¾ÙÀý
SELECT * from table1 CROSS JOIN table2
µÈͬÓÚ
SELECT * from table1,table2
Ò»°ã²»½¨ÒéʹÓø÷½·¨£¬ÒòΪÈç¹ûÓÐWHERE×Ó¾äµÄ»°£¬ÍùÍù»áÏÈÉú³ÉÁ½¸ö±íÐÐÊý³Ë»ýµÄÐеÄÊý¾Ý±íÈ»ºó²Å¸ù¾ÝWHEREÌõ¼þ´ÓÖÐÑ¡Ôñ¡£

Òò´Ë£¬Èç¹ûÁ½¸öÐèÒªÇ󽻼ʵıíÌ«´ó£¬½«»á·Ç³£·Ç³£Âý£¬²»½¨ÒéʹÓá£
b. ÄÚÁ¬½ÓINNER JOIN
Èç¹û½ö½öʹÓÃ
SELECT * from table1 INNER JOIN table2
ûÓÐÖ¸¶¨Á¬½ÓÌõ¼þµÄ»°£¬ºÍ½»²æÁ¬½ÓµÄ½á¹ûÒ»Ñù¡£
µ«ÊÇͨ³£Çé¿öÏ£¬Ê¹ÓÃINNER JOINÐèÒªÖ¸¶¨Á¬½ÓÌõ¼þ¡£
-- µÈÖµÁ¬½Ó(=ºÅÓ¦ÓÃÓÚÁ¬½ÓÌõ¼þ, ²»»áÈ¥³ýÖØ¸´µÄÁÐ)
SELECT * from table1 AS a INNER JOIN table2 AS b on a.column=b.column
-- ²»µÈÁ¬½Ó(>,>=,<,<=,!>,!<,<>)
ÀýÈç
SELECT * from table1 AS a INNER JOIN table2 AS b on
a.column<>b.column
-- ×ÔÈ»Á¬½Ó(»áÈ¥³ýÖØ¸´µÄÁÐ)
c. ÍâÁ¬½ÓOUTER JOIN
Ê×ÏÈÄÚÁ¬½ÓºÍÍâÁ¬½ÓµÄ²»Í¬Ö®´¦£º
ÄÚÁ¬½ÓÈç¹ûûÓÐÖ¸¶¨Á¬½ÓÌõ¼þµÄ»°£¬ºÍµÑ¿¨¶û»ýµÄ½»²æÁ¬½Ó½á¹ûÒ»Ñù£¬µ«ÊDz»Í¬Óڵѿ¨¶û»ýµÄµØ·½ÊÇ£¬Ã»Óеѿ¨¶û»ýÄÇô¸´ÔÓÒªÏÈÉú³ÉÐÐÊý³Ë
»ýµÄÊý¾Ý±í£¬ÄÚÁ¬½ÓµÄЧÂÊÒª¸ßÓڵѿ¨¶û»ýµÄ½»²æÁ¬½Ó¡£
Ö¸¶¨Ìõ¼þµÄÄÚÁ¬½Ó£¬½ö½ö·µ»Ø·ûºÏÁ¬½ÓÌõ¼þµÄÌõÄ¿¡£
ÍâÁ¬½ÓÔò²»Í¬£¬·µ»ØµÄ½á¹û²»½ö°üº¬·ûºÏÁ¬½ÓÌõ¼þµÄÐУ¬¶øÇÒ°üÀ¨×ó±í(×óÍâÁ¬½Óʱ),
ÓÒ±í(ÓÒÁ¬½Óʱ)»òÕßÁ½±ßÁ¬½Ó(È«ÍâÁ¬½Óʱ)µÄËùÓÐÊý¾ÝÐС£
1)×óÍâÁ¬½ÓLEFT [OUTER] JOI


Ïà¹ØÎĵµ£º

ͨ¹ýÁ½¸öÀý×Ó½²½âPIVOT/UNPIVOTµÄÓ÷¨_SQL¼¼ÇÉ

ʹÓùýSQL Server 2000µÄÈ˶¼ÖªµÀ£¬ÒªÏëʵÏÖÐÐÁÐת»»,±ØÐë×ÛºÏÀûÓþۺϺ¯ÊýºÍ¶¯Ì¬SQL£¬¾ßÌåʵÏÖÆðÀ´ÐèÒªÒ»¶¨µÄ¼¼ÇÉ£¬¶øÔÚSQL Server 2005ÖУ¬Ê¹ÓÃÐÂÒý½øµÄ¹Ø¼ü×ÖPIVOT/UNPIVOT£¬Ôò¿ÉÒÔºÜÈÝÒ×µÄʵÏÖÐÐÁÐת»»µÄÐèÇó¡£
ÔÚ±¾ÎÄÖÐÎÒÃǽ«Í¨¹ýÁ½¸ö¼òµ¥µÄÀý×ÓÏêϸ½²½âPIVOT/UNPIVOTµÄÓ÷¨¡£
PIVOTµÄÓ÷¨£º
Ê×ÏÈ´´½¨²âÊÔ±í£¬È» ......

sql ʱ¼ä¸ñʽת»»

Óï¾ä¼°²éѯ½á¹û£º
SELECT CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
SELECT CONVERT(varchar(100), GETDATE(), 1): 05/16/06
SELECT CONVERT(varchar(100), GETDATE(), 2): 06.05.16
SELECT CONVERT(varchar(100), GETDATE(), 3): 16/05/06
SELECT CONVERT(varchar(100), GETDATE(), 4): 16.05.06
SE ......

¼õÉÙSQLÈÕÖ¾Îļþ´óС

Ò»¡¢ÓÃÈçϲ½×öÁË£º
1¡¢DUMP¡¡TRANSACTION¡¡¿âÃû¡¡WITH¡¡no_log
2¡¢dbcc
shrinkfile(logfilename)
3¡¢ÊÕËõÊý¾Ý¿â
4¡¢É趨×Ô¶¯ÊÕËõ¡£
¡¡¡¡¶þ¡¢·ÖÀëÊý¾Ý¿â,ɾ³ýÈÕÖ¾
Îļþ,ÔÙ¸½¼Ó,OK!ÓÒ»÷Êý¾Ý¿â£­£­ËùÓÐÈÎÎñ£­£­·ÖÀëor ¸½¼Ó
¡¡¡¡Èý¡¢1¡¢backup
log¡¡¿âÃû¡¡WITH¡¡no_log£¬2¡¢dbcc shrinkfile(logfilename)£¬3¡ ......

SQL SERVERÖн»ºÍ²îµÄʵÏÖ

create table aaa
(
id int primary key ,
name varchar(30) not null
)
create table bbb
(
id int primary key ,
name varchar(30) not null
)
--½»
select * from aaa
where exists
(
select * from bbb where aaa.id=bbb.id and aaa.name = bbb.name
)
--²î
select *
from bbb
where not exists
( ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ