SQL
ÌâÄ¿1:
ÎÊÌâÃèÊö:
S (SNO,SNAME) ѧÉú¹ØÏµ¡£SNO ΪѧºÅ£¬SNAME ΪÐÕÃû
C (CNO,CNAME,CTEACHER) ¿Î³Ì¹ØÏµ¡£CNO Ϊ¿Î³ÌºÅ£¬CNAME Ϊ¿Î³ÌÃû£¬CTEACHER ΪÈονÌʦ
SC(SNO,CNO,SCGRADE) Ñ¡¿Î¹ØÏµ¡£SCGRADE Ϊ³É¼¨
1.£¨1£©ÕÒ³öÑ¡ÐÞ¹ý“ÀîÃ÷”ÀÏʦ½²Êڿγ̵ÄËùÓÐѧÉúÐÕÃû
--ʵÏÖ´úÂë:·¨Ò»
Select *
from SC,C,S
Where SC.CNO=C.CNO AND SC.SNO=S.SNO AND CTEACHER='ÀîÃ÷'
---·¨¶þ£º-------------
Select *
from S
Where Sno IN( Select Sno
from C,SC
Where C.[Cno]=SC.[Cno] AND CTEACHER='ÀîÃ÷')
£¨2£©ÕÒ³öûÓÐÑ¡ÐÞ¹ý“ÀîÃ÷”ÀÏʦ½²Êڿγ̵ÄËùÓÐѧÉúÐÕÃû
--ʵÏÖ´úÂë:·¨Ò»
Select SName
from S
Where [Sno] NOT IN( Select SC.[Sno]
from SC,C
Where SC.CNO=C.CNO AND CTEACHER='ÀîÃ÷')
£¨3£©ÕÒ³öûÓÐÑ¡ÐÞ¹ý¿Î³ÌºÅΪ"1"µÄËùÓÐѧÉúÐÕÃû
--ʵÏÖ´úÂë
Select SName
from S
Where [Sno] NOT IN( Select [Sno] from SC Where CNO='1')
------------------------------------------------------------------------------------------------------------------------------------------
2. ÁгöÓжþÃÅÒÔÉÏ(º¬Á½ÃÅ)²»¼°¸ñ¿Î³ÌµÄѧÉúÐÕÃû¼°Æäƽ¾ù³É¼¨
--ʵÏÖ´úÂë:
Select S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)
from S,SC,( Select SNO
from SC
Where SCGRADE<60
GROUP BY SNO
&nb
Ïà¹ØÎĵµ£º
OracleµÄsql*plusÊÇÓëoracle½øÐн»»¥µÄ¿Í»§¶Ë¹¤¾ß¡£ÔÚsql*plusÖУ¬¿ÉÒÔÔËÐÐsql*plusÃüÁîÓësql*plusÓï¾ä¡£
¡¡¡¡
¡¡¡¡ÎÒÃÇͨ³£Ëù˵µÄDML¡¢DDL¡¢DCLÓï¾ä¶¼ÊÇsql*plusÓï¾ä£¬ËüÃÇÖ´ÐÐÍêºó£¬¶¼¿ÉÒÔ±£´æÔÚÒ»¸ö±»³ÆÎªsql bufferµÄÄÚ´æÇøÓòÖУ¬²¢ÇÒÖ»Äܱ£´æÒ»Ìõ×î½üÖ´ÐеÄsqlÓï¾ä£¬ÎÒÃÇ¿ÉÒÔ¶Ô±£´æÔÚsql bufferÖеÄsql Óï¾ä½ø ......
1.Ò»µÀSQLÓï¾äÃæÊÔÌ⣬¹ØÓÚgroup by
±íÄÚÈÝ£º
2005-05-09 ʤ
2005-05-09 ʤ
2005-05-09 ¸º
2005-05-09 ¸º
2005-05-10 ʤ
2005-05-10 ¸º
2005-05-10 ¸º
Èç¹ûÒªÉú³ÉÏÂÁнá¹û, ¸ÃÈçºÎдsqlÓï¾ä?
ʤ ¸º
2005-05-09 2 2
2005-05-10 ......
(1)char¡¢varchar¡¢textºÍnchar¡¢nvarchar¡¢ntext
charºÍvarcharµÄ³¤¶È¶¼ÔÚ1µ½8000Ö®¼ä£¬ËüÃǵÄÇø±ðÔÚÓÚcharÊǶ¨³¤×Ö·ûÊý¾Ý£¬¶øvarcharÊDZ䳤×Ö·ûÊý¾Ý¡£Ëùν¶¨³¤¾ÍÊdz¤¶È¹Ì¶¨ µÄ£¬µ±ÊäÈëµÄÊý¾Ý³¤¶ÈûÓдﵽָ¶¨µÄ³¤¶Èʱ½«×Ô¶¯ÒÔÓ¢ÎĿոñÔÚÆäºóÃæÌî³ä£¬Ê¹³¤¶È´ïµ½ÏàÓ¦µÄ³¤¶È£»¶ø±ä³¤×Ö·ûÊý¾ÝÔò²»»áÒÔ¿Õ¸ñÌî³ä¡£ text´ ......
ÔÎÄ´«ËÍÃÅ£ºhttp://topic.csdn.net/u/20091010/14/FC7737C1-D60B-43F1-A8B5-A9EEF2DE4426.html
¼ÙÈçÏÖÔÚÓÐÁ½ÕÅ±í£º
1.±ístuinfo
sid sname subs
1 jack |1|2|
2 marry |1|4|
3 tom |3|
2.±ísubinfo
subid subname
1 physics
2 &n ......