Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

50Ìõ³£ÓÃsqlÓï¾ä

×¢£º³ö´¦ÒѲ»¿É¿¼£¬²»¹ý£¬ÆäÖв»ÉÙÓï¾ä»¹ÊÇÂù¾«ÃîµÄ¡£
Student(S#(ѧºÅ),Sname(ÐÕÃû),Sage(ÄêÁä),Ssex(ÐÔ±ð)) ѧÉú±í
Course(C#(¿Î³Ì±àºÅ),Cname(¿Î³ÌÃû³Æ),T#(ÀÏʦ±àºÅ)) ¿Î³Ì±í
SC(S#(ѧºÅ),C#(¿Î³Ì±àºÅ),score(·ÖÊý)) ³É¼¨±í
Teacher(T#(ÀÏʦ±àºÅ),TnameSname(ÐÕÃû)) ½Ìʦ±í
ÎÊÌ⣺
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¡¢²éѯ¿


Ïà¹ØÎĵµ£º

SQLµÄÓÅ»¯

´ó¼Ò¶¼ÔÚÌÖÂÛ¹ØÓÚÊý¾Ý¿âÓÅ»¯·½ÃæµÄ¶«¶«£¬¸ÕºÃ²ÎÓ뿪·¢ÁËÒ»¸öÊý¾Ý²Ö¿â·½ÃæµÄÏîÄ¿£¬ÒÔϵÄÒ»µã¶«Î÷ËãÊÇÊý¾Ý¿âÓÅ»¯·½ÃæµÄѧϰ+ʵսµÄһЩÐĵÃÌå»áÁË£¬ÄóöÀ´´ó¼Ò¹²Ïí¡£»¶Ó­ÅúÆÀÖ¸Õý°¢£¡
SQLÓï¾ä£º
ÊǶÔÊý¾Ý¿â(Êý¾Ý)½øÐвÙ×÷µÄΩһ;¾¶£»
ÏûºÄÁË70%~90%µÄÊý¾Ý¿â×ÊÔ´£»¶ÀÁ¢ÓÚ³ÌÐòÉè¼ÆÂß¼­£¬Ïà¶ÔÓÚ¶Ô³ÌÐòÔ´´úÂëµÄÓÅ»¯£¬¶ÔSQ ......

ÒÆÖ²SQL serverÊý¾Ý¿â¶ÔÏóµ½OracleµÄ²Ù×÷˵Ã÷

ÒÔÏÂÊÇÕª×ÔOracle¹ÙÍø:
¢ñ Oracle SQL Developer ÊÇÒ»¸öÃâ·ÑµÄͼÐλ¯Êý¾Ý¿â¿ª·¢¹¤¾ß¡£Ê¹Óà SQL Developer£¬Äú¿ÉÒÔä¯ÀÀÊý¾Ý¿â¶ÔÏó¡¢ÔËÐÐ SQL Óï¾äºÍ SQL ½Å±¾£¬²¢ÇÒ»¹¿ÉÒԱ༭ºÍµ÷ÊÔ PL/SQL Óï¾ä¡£Äú»¹¿ÉÒÔÔËÐÐËùÌṩµÄÈκÎÊýÁ¿µÄ±¨±í£¬ÒÔ¼°´´½¨ºÍ±£´æÄú×Ô¼ºµÄ±¨±í¡£SQL Developer ¿ÉÒÔÌá¸ß¹¤×÷ЧÂʲ¢¼ò»¯Êý¾Ý¿â¿ª·¢ÈÎÎñ¡£ ......

SQLÓï¾äÖÐ N ºó½ÓÐø×Ö·û´®µÄ×÷ÓÃ

È磺SET @sql=N'SELECT * from stu'
ÆäÖГN”µÄ×÷ÓÃÊDZíʾºóÃæµÄ±àÂë¸ñʽΪunicode±àÂë
DECLARE @rst INT,@sql NVARCHAR(4000)
SET @sql=N'SELECT * from tb '
EXEC sp_executesql @sql,N'@rst INT OUTPUT',@rst OUTPUT
SELECT @rst
......

SQL·ÖÒ³Óï¾ä

±àÂë¹ý³ÌÖÐÓöµ½µÄSQL·ÖÒ³Çé¿ö£¬×ܽ᣺
´ÓÊý¾Ý¿â±íÖеÚMÌõ¼Ç¼¿ªÊ¼¼ìË÷NÌõ¼Ç¼
MySQL£º
ÏȲéѯ·ÖÒ³£¬È»ºóÅÅÐò£º
select * from (select * from student  limit 5,2) pageTable order by id desc  ;
ÏÈÅÅÐò£¬È»ºó²éѯ·ÖÒ³£ºselect * from student order by id desc limit 5,2  ;
Oracle£º
SELECT * fro ......

sqlÍêÈ«½âÎö

1¡¢¼òµ¥²éѯ
Çó³öÔÚ1988ÄêÒÔÇ°±»¹ÍÓ¶µÄÏúÊÛÈËÔ±
SELECT NAME
 from SALESREPS
WHERE HIRE_DATE<'01-JAN-88'
ÁгöÆäÏúÊÛÁ¿µÍÓÚÏúÊÛÄ¿±êµÄ80%µÄÏúÊÛµã
SELECT CITY,SALES,TAGET
from SALESPEPS
WHERE SALES<0.8*TAGET ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ