Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö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 Serverº¯Êý´óÈ«

  --¾ÛºÏº¯Êý
use pubs
go
select avg(distinct price)  --Ëãƽ¾ùÊý
from titles
where type='business'
go 
use pubs
go
select max(ytd_sales)  --×î´óÊý
from titles
go 
use pubs
go
select min(ytd_sales)& ......

SQlÊý¾ÝÀàÐÍ( _ ѧSQL¾Í´ÓÊý¾ÝÀàÐÍ¿ªÊ¼°É£¡)

SQlÊý¾ÝÀàÐÍ(-_-ѧSQL¾Í´ÓÊý¾ÝÀàÐÍ¿ªÊ¼°É£¡)
bit    ÕûÐÍ
ÆäÖµÖ»ÄÜÊÇ0¡¢1»ò¿ÕÖµ¡£
ÕâÖÖÊý¾ÝÀàÐÍÓÃÓÚ´æ´¢Ö»ÓÐÁ½ÖÖ¿ÉÄÜÖµµÄÊý¾Ý£¬ÈçYes »òNo¡¢True »òFalse ¡¢On »òOff.
int    ÕûÐÍ 4¸ö×Ö½Ú
´Ó- 2^31(-2147483648)µ½2^31 (2147483 647)Ö®¼äµÄÕûÊý¡£
´æ´¢µ½Êý¾Ý¿âµÄ¼¸ºõËùÓÐÊýÖµÐ͵ÄÊý¾ ......

SQLÓï¾äµÃµ½´æ´¢¹ý³Ì¹ØÁªÄÄЩ±íÃû

SELECT DISTINCT '['+user_name(b.uid)+'].['+b.name+']' AS ¶ÔÏóÃû,b.type AS ÀàÐÍ
from sysdepends a,sysobjects b
WHERE b.id=a.depid
    AND a.id=OBJECT_ID('¹ý³ÌÃû');
 
 
EXEC SP_DEPENDS '¹ý³ÌÃû'; ......

¹ØÓÚSQLʱ¼äÀàÐ͵ÄÄ£ºý²éѯ

½ñÌìÓÃtime Like '2008-06-01%'Óï¾äÀ´²éѯ¸ÃÌìµÄËùÓÐÊý¾Ý£¬±»ÌáʾÓï¾ä´íÎó¡£²éÁËһϲŷ¢ÏÖ¸ÃÄ£ºý²éѯֻÄÜÓÃÓÚStringÀàÐ͵Ä×ֶΡ£
×Ô¼ºÒ²²éÔÄÁËһЩ×ÊÁÏ¡£¹ØÓÚʱ¼äµÄÄ£ºý²éѯÓÐÒÔÏÂÈýÖÖ·½·¨£º
 
1.Convertת³ÉString,ÔÚÓÃLike²éѯ¡£
select * from table1   where c ......

¡¾×ª¡¿ PL/SQL×î²îʵ¼ù

PL/SQL×î²îʵ¼ù
http://benben.javaeye.com/blog/278164
1. ³¬³¤µÄPL/SQL´úÂë
           Ó°Ï죺¿Éά»¤ÐÔ£¬ÐÔÄÜ
           Ö¢×´£º
            ÔÚ¸´ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ