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

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”¿


Ïà¹ØÎĵµ£º

Oracle SQL*LoaderÊý¾Ý×°ÔØ

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 ......

ÐÔÄÜ¼à¿ØÖ®¼à¿ØSQLÓï¾ä(ZT)

1.¼à¿Øµ±Ç°Êý¾Ý¿âË­ÔÚÔËÐÐʲôSQLÓï¾ä
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
 
2.·ÖÎö±í
analyze table tablename compute statistics for all indexes;
analyze table tablename compute statistics for all indexed col ......

SQL Server

²éѯ card µÄ¼Ç¼ΪÁ½´ÎÒÔÉ쵀 card£¬¼Ç¼Êý:
select count(card), card from TableName group by card having count(card) > 1
¼¶Áª¸üУ¬¼¶ÁªÉ¾³ý:
ColumnName type not null constraint FK_Name foreign key(ColumnName) references PrimaryTable(ColumnName) on update/delete cascade
×Ô¶¯¼ÆËãÁÐ:
create tabl ......

ÔÚSQLÖÐʹÓÃnow()µ¼ÖµÄÐÔÄÜϽµ(MySQL)

×öÊý¾Ý¿âµ÷ÓÅ£¬Ò»¸ö¼òµ¥µÄtip¾ÍʹÐÔÄÜÌáÉýÒ»´ó¿éʱ£¬±»°ïÖúµÄÈË×ÔÈ»ÊǸßÐ˶øÇҸм¤£¬ÎÒÒ²ÊÇÂúÐÄ»¶Ï²¡£ÓÃËùѧ°ïÖúÁËËûÈË£¬Ò²ËµÃ÷»¹ÊÇÓÐÒ»¼¼Ö®³¤µÄ£¬ÓÐÒ»ÃÅÄܹ»Ñø»î×Ô¼ºµÄÊÖÒÕ¡£Ò²ËãÊÇÊÖÒÕÈ˰¡¡£
ͬÊÂÈÃÎÒ°ïÖúÓÅ»¯£¬ÔÚslow query logÀï·¢ÏÖÓÐÁ½¸öSQLÖ´ÐеĴÎÊý×î¶à£¬²¢ÇÒÿ´Î¶¼ÔÚÁ½ÃëÒÔÉÏ¡£ÓÃexplain¿´ÁË£¬Ò²Î´·¢ÏÖË÷ÒýʹÓ÷ ......

ÂÛ̳Àï¿´µ½µÄÒ»¸öSQLÎÊÌâ¼°½â´ð

ÎÊÌ⣺
ÓÐÒ»¸ö·ÖÊý±í
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 ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ