SQL ServerʾÀý²éѯ
Student(S#,Sname,Sage,Ssex) ѧÉú±í
Course(C#,Cname,T#) ¿Î³Ì±í
SC(S#,C#,score) ³É¼¨±í
Teacher(T#,Tname) ½Ìʦ±í
ÎÊÌ⣺
1¡¢²éѯ“001”¿Î³Ì±È“002”¿Î³Ì³É¼¨¸ßµÄËùÓÐѧÉúµÄѧºÅ£»
select a.S# from (select s#,score from SC where C#='001') a,(select s#,score
from SC where C#='002') b
where a.score>b.score and a.s#=b.s#;
2¡¢²éѯƽ¾ù³É¼¨´óÓÚ60·ÖµÄͬѧµÄѧºÅºÍƽ¾ù³É¼¨£»
select S#,avg(score)
from sc
group by S# having avg(score) >60;
3¡¢²éѯËùÓÐͬѧµÄѧºÅ¡¢ÐÕÃû¡¢Ñ¡¿ÎÊý¡¢×ܳɼ¨£»
select Student.S#,Student.Sname,count(SC.C#),sum(score)
from Student left Outer join SC on Student.S#=SC.S#
group by Student.S#,Sname
4¡¢²éѯÐÕ“ÀÄÀÏʦµÄ¸öÊý£»
select count(distinct(Tname))
from Teacher
where Tname like 'Àî%';
5¡¢²éѯûѧ¹ý“Ҷƽ”ÀÏʦ¿ÎµÄͬѧµÄѧºÅ¡¢ÐÕÃû£»
select Student.S#,Student.Sname
from Student
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='Ҷƽ');
6¡¢²éѯѧ¹ý“001”²¢ÇÒҲѧ¹ý±àºÅ“002”¿Î³ÌµÄͬѧµÄѧºÅ¡¢ÐÕÃû£»
select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');
7¡¢²éѯѧ¹ý“Ҷƽ”ÀÏʦËù½ÌµÄËùÓпεÄͬѧµÄѧºÅ¡¢ÐÕÃû£»
select S#,Sname
from Student
where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='Ҷƽ' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname='Ҷƽ'));
8¡¢²éѯ¿Î³Ì±àºÅ“002”µÄ³É¼¨±È¿Î³Ì±àºÅ“001”¿Î³ÌµÍµÄËùÓÐͬѧµÄѧºÅ¡¢ÐÕÃû£»
Select S#,Sname from (select Student.S#,Student.Snam
Ïà¹ØÎĵµ£º
ÔÚ°²×°Visual Studio+SQL ServerµÄ¿ª·¢»·¾³µÄʱºòÍùÍù»áÓкܶà´íÎ󣬯äÖÐ×îÂé·³µÄÒ»Ìõ¾ÍËÀ°²×°Íê³ÉÖ®ºóûÓÐÊý¾Ý¿â¹ÜÀí¹¤¾ß£¬¼´Ã»ÓÐSQL Server 2005 Management Studio¡£³öÏÖÕâÑùµÄÎÊÌâÖ÷ÒªÓÐÁ½¸öÔÒò£¬Ò»ÊÇϵͳÒѾ´æÔÚÆäËû°æ±¾µÄSQLÊý¾Ý¿â»òÕßExpress°æ±¾£¬¶þÊǰ²×°»·¾³µÄʱºòÏñÎÒÒ»ÑùÏȰ²×°ÁËVisual Studio 2005/2008£¬È» ......
Æô¶¯ÓëÅäÖòÎÊý ¸ü¸ÄϵͳԪÊý¾ÝºÍ²ÎÊýÅäÖà ¶¯Ì¬ÊÓͼ ´æ´¢µÄÎïÀíºÍÂß¼½á¹¹ Æô¶¯ºÍ¹Ø±Õ
topÆô¶¯ÓëÅäÖòÎÊý
ÔÚSQL Server°²×°µÄʱºò£¬Í¬Ê±Ò²»áÍùWindows×¢²á±íÀïÃæÌí¼ÓһЩ¼Ç¼£¬ÕâЩע²á±í¼üÖµÖ¸¶¨ÁËʵÀýËùÐèÒªµÄ¸÷ÖÖ²ÎÊý¡£±ÈÈ磬ÓеÄ×¢²á±í¼üÖµÖ¸¶¨ÁË´íÎóÈÕÖ¾µÄ±£´æÄ¿Â¼£¬ÁíÍâµÄÖ¸¶¨ÁËĬÈϵı¸·ÝĿ¼µÈµÈ¡£SQL ServerÔÚÔ ......
Ë®»·¾³¼à²âÊý¾Ý´æ´¢½á¹¹ÖÐÓÐÒ»ÖÖģʽ½Ð×ö"Êú±íģʽ"£¬¼´ÔÚ¼à²âÊý¾Ý±íÖУ¬Ä³¸öµãλÔÚij¸öʱ¼äµãÉϸ÷¼à²âÏîÄ¿µÄŨ¶È²â¶¨ÖµÔÚÎïÀí±íÖд洢ÔÚ¶àÌõ¼Ç¼ÖУ¬³ÊÊú×´·Ö²¼¡£¼à²âÊý¾Ý±íÖаüº¬²âµã´úÂë¡¢¼à²âʱ¼ä¡¢¼à²âÏîÄ¿´úÂ롢Ũ¶È²â¶¨ÖµµÈ£¬ËùÓмà²âÏîÄ¿µÄŨ¶È²â¶¨Öµ¶¼´æ´¢ÔÚͬһ¸ö×Ö¶ÎÖУ¬¼ÓÒÔ¼à²âÏîÄ¿´úÂë×÷ÎªÇø·Ö×ֶΡ£
"Êú ......
Ëø»úÖÆ
NOLOCKºÍREADPASTµÄÇø±ð¡£
1. ¿ªÆôÒ»¸öÊÂÎñÖ´ÐвåÈëÊý¾ÝµÄ²Ù×÷¡£
BEGIN TRAN t
INSERT INTO Customer
SELECT 'a','a'
2. Ö´ÐÐÒ»Ìõ²éѯÓï¾ä¡£
SELECT * from Customer WITH (NOLOCK)
½á¹ûÖÐÏÔʾ"a"ºÍ"a"¡£µ±1ÖÐÊÂÎñ»Ø¹öºó£¬ÄÇôa½«³ÉΪÔàÊý¾Ý¡£(×¢:1ÖеÄÊÂÎñδÌá½») ¡£NOLOCK±íÃ÷ûÓжÔÊý¾Ý±íÌí¼Ó¹²Ï ......
µ¼³ö±í½á¹¹:
Tools-->Export User Objects -->Ñ¡ÔñÒªµ¼³öµÄ±í(°üÀ¨SequenceµÈ)-->.sqlÎļþ
µ¼³ö±íÊý¾Ý:
Tools-->Export Tables-->Ñ¡Ôñ±í,Ñ¡ÔñSQL Inserts-->.sqlÎļþ
Ö´ÐÐÕâЩ.sqlÎļþʱ,ҪʹÓÃн¨Command WindowÀ´Ö´ÐÐ. ......