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
Ïà¹ØÎĵµ£º
Êý¾Ý¿âsqlÓï¾äϵÁж¼ÊÇÒÔÏÂÃæµÄ±íΪ»ù´¡µÄ
1. д³ö²½Öè4Öеĸ÷Ïî²Ù×÷µÄSQLÓï¾ä¡£
¢Ù ¸øÑ§Éú±íÔö¼ÓÒ»ÊôÐÔNation£¨Ãñ×壩£¬Êý¾ÝÀàÐÍΪVarchar£¨20£©£»
alter table Student add Nation varchar(20);
¢Ú ɾ³ýѧÉú±íÖÐÐÂÔöµÄÊôÐÔNation£»
alter table Student DR ......
select datediff(month,'2009-11-02','2009-12-01')
²»ÖªµÀÄãÒªÔõô¸öÔ²îÒì
ÉÏÃæµÄÃüÁî½á¹ûÒ²ÊÇ1
µ«ÊÇÆäʵ°´ÕÕ³£Ê¶²îÒì²»ÊÇÒ»¸öÔ£¬ÊDzîÒ»ÌìÒ»¸öÔÂ
ÕâÓжÎÏֳɵĴúÂë¿ÉÒÔ°ïÖúÄãËã³ö³£Ê¶ÉϵIJîÒì
SQL code
declare @t table(a datetime,b datetime);
insert @t
select '2009-11-02','2009-12-01' UNION ......
¡¡×î½üΪÊý¾Ý¿â·þÎñÆ÷Ôö¼ÓÁËÄڴ棬´ïµ½ÁË×î´óÖ§³ÖµÄ8G£¬Êý¾Ý¿âÓõÄÊÇmssql 2005 £¬Ö®Ç°ÄÚ´æÒ»Ö±ÊÇ4GµÄ£¬²»´æÔÚÄÚ´æ´óºÍ32λ²Ù×÷ϵͳ³åÍ»µÄÊÂÇ飬32λ²Ù×÷ϵͳµ¥½ø³Ì×î´óÖ§³Ö2GµÄÄڴ棬ÕâÑù×ӵϰÄÚ´æ¾Í°×¼ÓÁË£¬Ôõô°ìÄØ?
¡¡¡¡ÍøÉÏËÑË÷Á˺ܶà×ÊÁÏ£¬·¢ÏÖ΢ÈíÌṩÁËÒ»¸öËãÊÇÁÙʱµÄ½â¾ö·½°¸°É£¬Ê¹ÓÃAWEÀ´·ÖÅäÄڴ棬ÕâÑù×Ósqlse ......
ÎÊÌâÒ»¡¢Íü¼ÇÁ˵ǼMicrosoft SQL Server 2005 µÄsaµÄµÇ¼ÃÜÂë
½â¾ö·½·¨£ºÏÈÓÃwindowsÉí·ÝÑéÖ¤µÄ·½Ê½µÇ¼½øÈ¥£¬È»ºóÔÚ‘°²È«ÐÔ’-‘µÇ¼’-ÓÒ¼üµ¥»÷‘sa’-‘ÊôÐÔ’£¬ÐÞ¸ÄÃÜÂëµã»÷È·¶¨¾Í¿ÉÒÔÁË¡£
ÎÊÌâ¶þ¡¢Òѳɹ¦Óë·þÎñÆ÷½¨Á¢Á¬½Ó£¬µ«ÊÇÔڵǼ¹ý³ÌÖз¢Éú´íÈ¡¡££¨provider:¹ ......