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

Ò»¸öÌâÄ¿Éæ¼°µ½µÄ50¸öSqlÓï¾ä

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


Ïà¹ØÎĵµ£º

35Ìõ»ù±¾SQLÓï¾ä

 1¡¢²éÕÒÔ±¹¤µÄ±àºÅ¡¢ÐÕÃû¡¢²¿ÃźͳöÉúÈÕÆÚ£¬Èç¹û³öÉúÈÕÆÚΪ¿ÕÖµ£¬ÏÔʾÈÕÆÚ²»Ïê,²¢°´²¿ÃÅÅÅÐòÊä³ö,ÈÕÆÚ¸ñʽΪyyyy-mm-dd¡£
select
emp_no,emp_name,dept,isnull(convert(char(10),birthday,120),'ÈÕÆÚ²»Ïê') birthday
from employee
order by dept
 
¡¡¡¡
2¡¢²éÕÒÓëÓ÷×ÔÇ¿ÔÚͬһ¸öµ¥Î»µÄÔ±¹¤ÐÕÃû¡¢ÐÔ±ð¡ ......

SQL Server 2005 CTEµÄÓ÷¨

if object_id('[tb]') is not null
drop table [tb] 
go
create table [tb]([id] int,[col1] varchar(8),[col2] int) 
insert [tb] 
select 1,'ºÓ±±Ê¡',0 union all
 select 2,'ÐĮ̈ÊÐ',1 union all
 select 3,'ʯ¼ÒׯÊÐ',1 union all
 select 4,'ÕżҿÚÊÐ',1 union all
&n ......

ÈçºÎÓÃSQL Óï¾ä¶ÁÈ¡DÅÌÄÚÈÝ

master..xp_dirtree   'D:\',1,1        µÚÒ»¸ö1ÊÇÉî¶È£¬µÚ¶þ¸ö1ÊÇÎļþ
1.   Ö´ÐÐ   master..xp_dirtree   'c:\',1,1,ÕâÑù¿ÉÒÔ»ñÈ¡c:\ϵÄËùÓÐÎļþºÍÎļþ¼Ð,²»°üÀ¨×ÓÎļþ¼Ð¼°Îļþ   
   
2.   ÏÔʾÔÚtreeviewÖÐ,ÓñêÖ ......

MS SQL SERVER º£Á¿Êý¾ÝµÄ²éѯÓÅ»¯¼°·ÖÒ³Ëã·¨


̽ÌÖÈçºÎÔÚÓÐ×Å1000ÍòÌõÊý¾ÝµÄMS SQL SERVERÊý¾Ý¿âÖÐʵÏÖ¿ìËÙµÄÊý¾ÝÌáÈ¡ºÍÊý¾Ý·ÖÒ³¡£ÒÔÏ´úÂë˵Ã÷ÁËÎÒÃÇʵÀýÖÐÊý¾Ý¿âµÄ“ºìÍ·Îļþ”Ò»±íµÄ²¿·ÖÊý¾Ý½á¹¹£º
CREATE TABLE [dbo].[TGongwen] (    --TGongwenÊǺìÍ·Îļþ±íÃû
   [Gid] [int]&nb ......

SQL Server³£ÓÃϵͳ´æ´¢¹ý³Ì

--ÁгöSQL ServerʵÀýÖеÄÊý¾Ý¿â
sp_databases
--·µ»ØSQL Server¡¢Êý¾Ý¿âÍø¹Ø»ò»ù´¡Êý¾ÝÔ´µÄÌØÐÔÃûºÍÆ¥ÅäÖµµÄÁбí
sp_server_info
--·µ»Øµ±Ç°»·¾³ÖеĴ洢¹ý³ÌÁбí
sp_stored_procedures
--·µ»Øµ±Ç°»·¾³Ï¿ɲéѯµÄ¶ÔÏóµÄÁÐ±í£¨ÈκοɳöÏÖÔÚ from ×Ó¾äÖеĶÔÏó£©
sp_tables
select * from sysobjects
---Ìí¼Ó»ò¸ü¸Ä ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ