SQL³£ÓÃ實Àý£¨轉£©
50¸öSqlÓï¾ä£¬½ö¹©Ñ§Ï°£¡
Student(S#,Sname,Sage,S***) ѧÉú±í
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”¿
Ïà¹ØÎĵµ£º
ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃ棬ºóÃæ ......
SQL*Loader ÓÃÓÚ½«´óÁ¿Êý¾Ý×°ÈëÊý¾Ý¿â¡£
¢Å¡¢¶¨¿íÊý¾Ý
´´½¨Êý¾ÝÎļþcontrol.txt£º
aaa,bbb
ccc,ddd
eee,fff
´´½¨¿ØÖÆÎļþcontrol.ctl£º
load data
infile 'c:\loader.txt'
append
into table tester.mm(
m1 position(1:3) char,
m2 position(5:7) char)
ÅúÁ¿¼ÓÔØÊý¾Ý£º
sqlldr tester/test control=c:\loade ......
×îÏÈÏ£ÍûʹÓÃdatatable.columnsÖÖËùº¬ÓеÄÊôÐÔÖ±½Ó»ñÈ¡Ïà¹ØÐÅÏ¢£¬¿É²âÊÔºó·¢ÏÖ£¬³ýÁË×Ö¶ÎÃûºÍ×Ö¶ÎÀàÐÍ£¬ÆäËûÊý¾ÝÒ»¸ÅÕÒ²»µ½¡£
GOOGLEÍøÉϺܶàÖÖ·½·¨£¬ÆäÖÐ×îÖ±½ÓµÄÊÇÖ±½Ó²éѯSQLSERVERµÄinformation_schema.columsÊÓͼ£¬´ÓÖÐÖ±½Ó»ñÈ¡ÐÅÏ¢£¬²»¹ýºóÀ´ÓÖ·¢ÏÖ£¬ËäÈ»Êý¾ÝʱȫÁË£¬¿ÉÊÇÐèҪͨ¹ýºÜ¸´Ôӵķ½·¨£¨sys.all_columns sys ......
ÎÊÌ⣺
ÓÐÒ»¸ö·ÖÊý±í
id classid,score
1 01 120
2 01 128
3 02 98
4 04 134
5 04 78
ÏÖÔÚҪͳ¼Æ ¸÷°àscore >120,ºÍ´óÓÚ90·ÖµÄÈËÊý
´ïµ½ÈçÏÂЧ¹û
classid >120 >90
01 &nb ......
Ê×ÏÈ£º·ÖÀëÊý¾Ý¿â
ÆóÒµ¹ÜÀíÆ÷--ÓÒ¼üsuspectµÄÊý¾Ý¿â--ËùÓÐÈÎÎñ--·ÖÀëÊý¾Ý¿âÈ»ºó±¸·ÝÄãµÄsuspectÊý¾Ý¿âµÄÎļþ,ÔÙ°´ÏÂÃæµÄ²½Öè´¦Àí:
1.н¨Ò»¸öͬÃûµÄÊý¾Ý¿â
2.ÔÙÍ£µôsql server
3.ÓÃsuspectÊý¾Ý¿âµÄÎļþ¸²¸ÇµôÕâ¸öн¨µÄͬÃûÊý¾Ý¿â
4.ÔÙÖØÆôsql server
5.´Ëʱ´ò¿ªÆóÒµ¹ÜÀíÆ÷ʱн¨µÄͬÃûÊý¾Ý¿â»á³öÏÖÖÃÒÉ£¬ÏȲ» ......