SQLÊý¾Ý¿âÖ®¶þ
l INNER JOIN
ÄÚÁ¬½ÓÊÇ×î³£¼ûµÄÒ»ÖÖÁ¬½Ó£¬ËüÒ³±»³ÆΪÆÕͨÁ¬½Ó£¬¶øE.FCodd×îÔç³Æ֮Ϊ×ÔÈ»Á¬½Ó¡£
ÏÂÃæÊÇANSI SQL£92±ê×¼
select * from t_institution i
inner join t_teller t
on i.inst_no = t.inst_no //˵Á½¸ö±íÖ®¼äµÄ¹ØϵÓÃON
where i.inst_no = "5801"
ÆäÖÐinner¿ÉÒÔÊ¡ÂÔ¡£µÈ¼ÛÓÚÔçÆÚµÄÁ¬½ÓÓï·¨
select * from t_institution i, t_teller t
where i.inst_no = t.inst_no
and i.inst_no = "5801"
SELECT *
from Ã÷ÈÕ¹¤×ʱí AS a INNER JOIN ²¿Ãűí AS b
ON a.²¿ÃÅÃû³Æ=b.²¿ÃÅÃû³Æ
WHERE ¹¤×ÊÔ·Ý='3'
l LEFT OUTER JOIN---1
select * from t_institution i //±íÔÚfromÖеĶ¨Òå±ðÃû²»ÐèÒªAS
left outer join t_teller t
on i.inst_no = t.inst_no
ÆäÖÐouter¿ÉÒÔÊ¡ÂÔ¡£
SELECT a.²¿ÃűàºÅ,a.²¿ÃÅÃû³Æ,a.¸ºÔðÈË,
b.ÈËÔ±±àºÅ,b.ÈËÔ±ÐÕÃû,b.²¿ÃÅÃû³Æ,
b.ѧÀú,b.¼¼ÊõÖ°³Æ
from Ã÷ÈÕ²¿Ãűí a LEFT OUTER JOIN Ã÷ÈÕÈËÔ±±í b
ON a.²¿ÃÅÃû³Æ=b.²¿ÃÅÃû³Æ
l LEFT OUTER JOIN---2
USE pubs //¶¨ÒåҪʹÓõÄÊý¾Ý¿â
SELECT a.au_fname, a.au_lname, p.pub_name
from authors a LEFT OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
l RIGHT OUTER JOIN
select * from t_institution i
right outer join t_teller t
on i.inst_no = t.inst_no
SELECT *
from ²¿Ãűí a RIGHT OUTER JOIN Ã÷ÈÕ¹¤×ʱí b
ON a.²¿ÃÅÃû³Æ=b.²¿ÃÅÃû³Æ
WHERE b.¹¤×ÊÔ·Ý='10'
l FULL OUTER
È«ÍâÁ¬½Ó·µ»Ø²ÎÓëÁ¬½ÓµÄÁ½¸öÊý¾Ý¼¯ºÏÖеÄÈ«²¿Êý¾Ý£¬ÎÞÂÛËüÃÇÊÇ·ñ¾ßÓÐÓëÖ®ÏàÆ¥ÅäµÄÐС£ÔÚ¹¦ÄÜÉÏ£¬ËüµÈ¼ÛÓÚ¶ÔÕâÁ½¸öÊý¾Ý¼¯
Ïà¹ØÎĵµ£º
1.Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)¡¡¡¡
¡¡¡¡ SQLSERVERµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬Òò´Ëfrom×Ó¾äÖÐдÔÚ×îºóµÄ±í£¨»ù´¡±ídriving table£©½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏ£¬±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í£¬µ±SQLSERVER´¦Àí¶à¸ö±íʱ£¬»áÔËÓÃÅÅÐò¼°ºÏ²¢µÄ·½Ê½Á ......
--MyDBΪÐÞ¸´µÄÊý¾ÝÃû
USE MASTER
GO
SP_CONFIGURE 'ALLOW UPDATES',1 RECONFIGURE WITH OVERRIDE
GO
ALTER DATABASE MyDB SET EMERGENCY
GO
sp_dboption 'MyDB', 'single user', 'true'
GO
DBCC CHECKDB('MyDB','REPAIR_ALLOW_DATA_LOSS')
GO
ALTER DATABASE MyDB SET ONLINE
GO
sp_configure 'allow updates ......
ÓÃPL/SQL Deleveloperµ¼³öcsvÎļþ¸ñʽÊý¾Ý£¬ÓÃexcel´ò¿ªÊÇÂÒÂ룬ÓüÇʱ¾´ò¿ªÕý³££¬Ôõô»ØÊ£¿
ÂíÉÏGoogle£¬ÔÀ´µ¼³öµÄÎļþµÄ±àÂë¸ñʽÊÇUTF-8ºÍ¶øexcelĬÈÏ´ò¿ªÎļþµÄ±àÂëÊÇunicode£¬ÓÚÊÇ£º
1¡¢ÓüÇʱ¾´ò¿ªÎļþ£¬È»ºóÁí´æΪ£¬ÌîдÎļþÃû£¬Ñ¡Ôñ±àÂë¸ñʽΪunicode
2¡¢ÓÃexcel´ò¿ªÐµÄÎļþ£¬Õý³£ÏÔʾ
µ«ÊdzöÏÖÁíÍâµÄÎÊÌ⣠......
£¨18£©ÓÃEXISTSÌæ»»DISTINCT£º
µ±Ìá½»Ò»¸ö°üº¬Ò»¶Ô¶à±íÐÅÏ¢(±ÈÈ粿ÃűíºÍ¹ÍÔ±±í)µÄ²éѯʱ,±ÜÃâÔÚSELECT×Ó¾äÖÐʹÓÃDISTINCT¡£Ò»°ã¿ÉÒÔ¿¼ÂÇÓÃEXISTÌæ»», EXISTS ʹ²éѯ¸üΪѸËÙ,ÒòΪRDBMSºËÐÄÄ£¿é½«ÔÚ×Ó²éѯµÄÌõ¼þÒ»µ©Âú×ãºó,Á¢¿Ì·µ»Ø½á¹û¡£Àý×Ó£º
(µÍЧ):
SELECT DISTINCT DEPT_NO,DEPT_NAME  ......
thunder:
1.MYSQLʵÏÖ
mysql> select * from user;
+----+----------+----------+-----------------+
| ID | username | password | email |
+----+----------+----------+-----------------+
| 1 | admin | admin &nb ......