²»´íµÄ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
Ïà¹ØÎĵµ£º
distinctÕâ¸ö¹Ø¼ü×ÖÓÃÀ´¹ýÂ˵ô¶àÓàµÄÖØ¸´¼Ç¼ֻ±£ÁôÒ»Ìõ£¬µ«ÍùÍùÖ»ÓÃËüÀ´·µ»Ø²»Öظ´¼Ç¼µÄÌõÊý£¬¶ø²»ÊÇÓÃËüÀ´·µ»Ø²»ÖؼǼµÄËùÓÐÖµ¡£ÆäÔÒòÊÇdistinctÖ»ÓÐÓöþÖØÑ»·²éѯÀ´½â¾ö£¬¶øÕâÑù¶ÔÓÚÒ»¸öÊý¾ÝÁ¿·Ç³£´óµÄÕ¾À´Ëµ£¬ÎÞÒÉÊÇ»áÖ±½ÓÓ°Ï쵽ЧÂʵġ£
ÏÂÃæÏÈÀ´¿´¿´Àý×Ó£º
table±í
×Ö¶Î1 ×Ö¶ ......
ÎÊÌâÃèÊö£º
±¾ÌâÓõ½ÏÂÃæÈý¸ö¹ØÏµ±í£º
CARD ½èÊ鿨¡£ CNO ¿¨ºÅ£¬NAME ÐÕÃû£¬CLASS °à¼¶
BOOKS ͼÊé¡£ BNO ÊéºÅ£¬BNAME ÊéÃû,AUTHOR ×÷Õߣ¬PRICE µ¥¼Û£¬QUANTITY ¿â´æ²áÊý
BORROW ½èÊé¼Ç¼¡£ CNO ½èÊ鿨ºÅ£¬BNO ÊéºÅ ......
Óöµ½MS SQL Server 2005 ÎÞ·¨É¾³ýÊý¾Ý¿â×÷Òµ¡£
¡¡¡¡Ê×ÏÈ£¬ÒÔsaÕË»§µÇ¼£¬²é¿´ÒÔÏÂ2¸ö±íµÄÊý¾Ý£¬Èç¹û²»Îª¿Õ¾ÍÓÐÎÊÌâ¡£
¡¡¡¡Sql´úÂë
¡¡¡¡select¡¡*¡¡from¡¡msdb.dbo.sysmaintplan_subplans
¡¡¡¡select¡¡*¡¡from¡¡msdb.dbo.sysmaintplan_log
¡¡¡¡Èç¹û²»Îª¿Õ£¬Ö´ÐÐÒÔϲÙ×÷£¬»ù±¾Éϲ»Óÿ¼ÂÇɾ³ýÄÄÒ»Ìõ¼Ç¼£¬ÓÐÎÊÌâµÄ×÷Òµ¾Í ......
ÍøÂç±ðÃû
-----------------------------------------------------------------------------
ÓÃ;£ºÎªÔ¶³Ì·þÎñÆ÷µÄÒÑÓбíÌí¼ÓÊý¾Ý
-----------------------------------------------------------------------------
²½Ö裺
SQL Server2000/2005
1. ÔËÐпͻ§¶ËÍøÂçʵÓù¤¾ß£¨
£¨1£© SQL Server2000-Client Network Ut ......
Óï·¨£º
CREATE [Ë÷ÒýÀàÐÍ] INDEX Ë÷ÒýÃû³Æ
ON ±íÃû(ÁÐÃû)
WITH FILLFACTOR = Ìî³äÒò×ÓÖµ0~100
GO
/*ʵÀý*/
USE ¿âÃû
GO
IF EXISTS
(SELECT * from SYSINDEXES WHERE NAME='IX_TEST_TNAME')--¼ì²âÊÇ·ñÒѾ´æÔÚIX_TEST_TNAMEË÷Òý
DROP INDEX
TEST.IX_TEST_TNAME--Èç¹û´æÔÚÔòɾ³ý
--´´½¨Ë÷Òý
CREATE NONCLUSTER ......