sql server¸ß¼¶²éѯ
1) ͳ¼Æ¸÷¸öϵµÄѧÉúÐÅÏ¢
select count(Sname) ×ÜÈËÊý,Sdept from Student group by Sdept
2) ²éѯÐŹÜϵѧÉúµÄ×î´óÄêÁäºÍ×îСÄêÁä
select MAX(Sage) ×î´óÄêÁä,MIN(Sage) ×îСÄêÁä from Student where
Sdept='ÐŹÜϵ'
3) ²éѯÐŹÜϵ×î´óÄêÁäºÍ×îСÄêÁäµÄѧÉúµÄÐÕÃû
select Sname from Student where Sdept = 'ÐŹÜϵ' and
(Sage in (select max(Sage)from Student )
or Sage in (select min(Sage)from Student where Sdept = 'ÐŹÜϵ')
4) ͳ¼ÆÑ¡ÐÞc01¿Î³ÌµÄѧÉúµÄ×î¸ß·Ö£¬×îµÍ·Ö£¬×ܳɼ¨ºÍƽ¾ù·Ö
select MAX(Grade) ×î¸ß·Ö,MIN(Grade) ×îµÍ·Ö,SUM(Grade) ×ܳɼ¨,AVG(Grade) ƽ¾ù·Ö
from SC WHERE Cno='c01'
5) ²éѯËùÓÐѧÉúµÄÑ¡¿ÎÐÅÏ¢£¬ÒªÇóÁгöѧÉúѧºÅ¡¢ÐÕÃû¡¢¿Î³ÌÃûºÍ³É¼¨
select Student.Sno,Sname,Cname,Grade from Student,SC,Course
where Student.Sno=SC.Sno and Course.Cno=SC.Cno
6) ͳ¼ÆÿÃſγ̵ÄÑ¡ÐÞÈËÊý
select count(Sno),Cno from SC group by Cno
7) ͳ¼Æÿ¸öѧÉúÑ¡Ð޵ĿγÌÃÅÊý¼°×ܳɼ¨
select Sno,count(Cno) Ñ¡Ð޿γÌÊý,SUM(Grade) ×ܳɼ¨ from SC group by Sno
8) ²éѯÄÄЩ¿Î³ÌûÓÐÈËÑ¡ÐÞ£¬ÒªÇóÁгö¿Î³ÌÃû¡¢¿Î³ÌºÅ
select Cno,Cname from Course where Cno not in(select Cno from SC)
9) ²éѯ¸÷¿Æƽ¾ù³É¼¨³¬¹ý80·ÖµÄѧÉúÐÕÃû
select Sname,SC.Sno ,avg(Grade) ƽ¾ù³É¼¨
from Student,SC
where Student.Sno=SC.Sno group by SC.Sno,Sname having avg(Grade)>80
10)²éѯѡÐÞÁËc03ºÅ¿Î³ÌµÄͬѧËùÔÚµÄϵ¼°¸ÃͬѧµÄÐÕÃû£¨ÓÃÁ½ÖÖ·½·¨ÊµÏÖ£©
select Student.Sname,Sdept from Student where
Sno in (select Sno from SC where Cno='c03')
select Student.Sname,Sdept from Student,SC where
Student.Sno =SC.Sno and Cno ='c03'
11)²éѯ¡°Êý¾Ý¿â»ù´¡¡±ÕâÃſεijɼ¨ÔÚ80·ÖÒÔÉϵÄѧÉúÐÕÃû
Select Sname from Student,SC,Course
where SC.Sno=Student.Sno and SC.Cno=Course.Cno and
Course.Cname='Êý¾Ý¿âÔÀí'and Grade>80;
12)ͳ¼Æƽ¾ù³É¼¨´óÓÚ70·ÖµÄ¿Î³ÌÃû
select Cname,AVG(Grade)from SC,Course where SC.Cno=Course.Cno
GROUP by SC.Cno,Cname having AVG(Grade)>70
13)ͳ¼Æƽ¾ù³É¼¨´óÓÚ70·Öµ
Ïà¹ØÎĵµ£º
ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃ棬ºóÃæ ......
ÊÓ¹Û±í (Views) ¿ÉÒÔ±»µ±×÷ÊÇÐéÄâ±í¸ñ¡£Ëü¸ú±í¸ñµÄ²»Í¬ÊÇ£¬±í¸ñÖÐÓÐʵ¼Ê´¢´æ×ÊÁÏ£¬¶øÊÓ¹Û±íÊǽ¨Á¢ÔÚ±í¸ñÖ®ÉϵÄÒ»¸ö¼Ü¹¹£¬Ëü±¾Éí²¢²»Êµ¼Ê´¢´æ×ÊÁÏ¡£
½¨Á¢Ò»¸öÊÓ¹Û±íµÄÓï·¨ÈçÏ£º
CREATE VIEW "VIEW_NAME" AS "SQL Óï¾ä"
"SQL Óï¾ä" ¿ÉÒÔÊÇÈκÎÒ»¸öÎÒÃÇÔÚÕâ¸ö½Ì²ÄÖÐÓÐÌáµ½µÄ SQL¡£
À´¿´Ò»¸öÀý×Ó¡£¼ÙÉèÎÒÃÇÓÐÒÔϵıí¸ñ£ ......
ÔÚ±í¸ñ±»½¨Á¢ÔÚ×ÊÁÏ¿âÖкó£¬ÎÒÃdz£³£»á·¢ÏÖ£¬Õâ¸ö±í¸ñµÄ½á¹¹ÐèÒªÓÐËù¸Ä±ä¡£³£¼ûµÄ¸Ä±äÈçÏ£º
¼ÓÒ»¸öÀ¸Î»
ɾȥһ¸öÀ¸Î»
¸Ä±äÀ¸Î»Ãû³Æ
¸Ä±äÀ¸Î»µÄ×ÊÁÏÖÖÀà
ÒÔÉÏÁгöµÄ¸Ä±ä²¢²»ÊÇËùÓпÉÄܵĸı䡣ALTER TABLE Ò²¿ÉÒÔ±»ÓÃÀ´×÷ÆäËûµÄ¸Ä±ä£¬ÀýÈç¸Ä±äÖ÷¼ü¶¨Òå¡£
ALTER TABLE µÄÓï·¨ÈçÏ£º
ALTER TABLE "table_name"
......
µ½Ä¿Ç°ÎªÖ¹£¬ÎÒÃÇѧµ½Á˽«ÈçºÎ°Ñ×ÊÁÏÓɱí¸ñÖÐÈ¡³ö¡£µ«ÊÇÕâЩ×ÊÁÏÊÇÈç¹û½øÈëÕâЩ±í¸ñµÄÄØ£¿ Õâ¾ÍÊÇÕâÒ»Ò³ (INSERT INTO) ºÍÏÂÒ»Ò³ (UPDATE) ÒªÌÖÂ۵ġ£
»ù±¾ÉÏ£¬ÎÒÃÇÓÐÁ½ÖÖ×÷·¨¿ÉÒÔ½«×ÊÁÏÊäÈë±í¸ñÖÐÄÚ¡£Ò»ÖÖÊÇÒ»´ÎÊäÈëÒ»±Ê£¬ÁíÒ»ÖÖÊÇÒ»´ÎÊäÈëºÃ¼¸±Ê¡£ ÎÒÃÇÏÈÀ´¿´Ò»´ÎÊäÈëÒ»±ÊµÄ·½Ê½¡£
ÒÀÕÕ¹ßÀý£¬ÎÒÃÇÏȽéÉÜÓï·¨¡£Ò»´ÎÊäÈ ......
ÔÚÕâÒ»Ò³ÖУ¬ÎÒÃÇÁгöËùÓÐÔÚÕâ¸öÍøÕ¾ÓÐÁгö SQL Ö¸ÁîµÄÓï·¨¡£ÈôÒª¸üÏ꾡µÄ˵Ã÷£¬ÇëµãÑ¡Ö¸ÁîÃû³Æ¡£
ÕâÒ»Ò³µÄÄ¿µÄÊÇÌṩһ¸ö¼ò½àµÄ SQL Óï·¨×öΪ¶ÁÕ߲ο¼Ö®Óá£ÎÒÃǽ¨ÒéÄúÏÖÔھͰ´ Control-D ½«±¾Ò³¼ÓÈëÄúµÄ¡ºÎÒµÄ×î°®¡»¡£
Select
SELECT "À¸Î»" from "±í¸ñÃû"
Distinct
SELECT DISTINCT "À¸Î»"
from "±í¸ñÃû"
......