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£¬È» ......
Êý¾Ý¿âʵÀý½á¹¹ ÊÂÎñÒ»ÖÂÐÔ(Transactional Consistency)ºÍ»ùÓÚʱ¼äµãµÄ»Ö¸´(Point-in-time Recovery) ϵͳԪÊý¾Ý
topÊý¾Ý¿âʵÀý½á¹¹
µ±OracleʵÀýÆô¶¯Ö®ºó£¬Ëù¿´µ½µÄ¾ÍÊÇÔÚ·þÎñÆ÷ÄÚ´æÉϵÄÒ»¸ö¸ö²»Í¬ÄÚ´æ¿é¼ÓÉϲúÉúµÄÓëÕâЩÄÚ´æ½»»¥µÄºǫ́½ø³Ì¡£OracleÎĵµ½«ÕâЩÄÚ´æ½á¹¹ºÍ½ø³ÌÊյĺÜÏêϸ¡£
ÓÉOracleʵÀýËùÕ¼ÓõÄÄÚ´æ¿é³ ......
ÐÐÁе¹ÖÃÔÚsql serverÖÐÊÇÒ»Öֺܳ£¼ûµÄ¼¼ÇÉ£¬ÔÚ×öÓ¦ÓÃϵͳµÄʱºò£¬¾³£ÐèÒª×öһЩͳ¼Æ¹¦ÄܱÜÃâ²»ÁËʹÓÃÐÐÁе¹ÖÃÕâ¸ö¼¼ÇÉ£¬ÎÒССµÄ×öÁËÒ»ÏÂ×ܽ᣺
µÚÒ»ÖÖ£ºsql server 2000ÖÐʹÓÃcase½øÐÐÐÐÁе¹ÖÃ
create table RowCellConvertTest
(
grade varchar(50),
sex varchar(50),
studentCount int
)
......
µ¼³ö±í½á¹¹:
Tools-->Export User Objects -->Ñ¡ÔñÒªµ¼³öµÄ±í(°üÀ¨SequenceµÈ)-->.sqlÎļþ
µ¼³ö±íÊý¾Ý:
Tools-->Export Tables-->Ñ¡Ôñ±í,Ñ¡ÔñSQL Inserts-->.sqlÎļþ
Ö´ÐÐÕâЩ.sqlÎļþʱ,ҪʹÓÃн¨Command WindowÀ´Ö´ÐÐ. ......
´ÓTable ±íÖÐÈ¡³öµÚ m Ìõµ½µÚ n ÌõµÄ¼Ç¼£º(Not In °æ±¾)
SELECT TOP n-m+1 *
from Table
WHERE (id NOT IN (SELECT TOP m-1 id from Table ))
--´ÓTABLE±íÖÐÈ¡³öµÚmµ½nÌõ¼Ç¼ (Exists°æ±¾)
SELECT TOP n-m+1 * from TABLE AS a WHERE Not Exists
(Select * from (Select Top m-1 * from TABLE orde ......