ʵÑéÎå SQL²éѯ£¨ÅÅÐò¡¢¼¯º¯ÊýºÍ·Ö×é×Ó¾äµÄÓ¦Óã©
ÊÔÑéÄ¿µÄ:
Ò»¡¢Ñ§Ï°²éѯ½á¹ûµÄÅÅÐò
¶þ¡¢Ñ§Ï°Ê¹Óü¯º¯ÊýµÄ·½·¨£¬Íê³Éͳ¼Æ
µÈ²éѯ¡£
Èý¡¢Ñ§Ï°Ê¹Ó÷Ö×é×Ó¾ä
Ò»¡¢Ñ§Ï°²éѯ½á¹ûµÄÅÅÐò
1¡¢²éѯȫÌåѧÉúÐÅÏ¢£¬½á¹û°´ÕÕÄêÁä½µ
ÐòÅÅÐò
select *
from student
order by sage desc
2¡¢²éѯѧÉúÑ¡ÐÞÇé¿ö£¬½á¹ûÏȰ´ÕտγÌ
ºÅÉýÐòÅÅÐò£¬ÔÙ°´³É¼¨½µÐòÅÅÐò
select *
from sc
order by cno,grade desc
˼¿¼Ì⣺²éѯѧÉúÐÕÃû£¬¼°ÆäÑ¡Ð޵ĿÎ
³ÌÃûºÍ³É¼¨£¬½á¹ûÏȰ´ÕտγÌÃûÉýÐòÅÅ
Ðò£¬ÔÙ°´³É¼¨½µÐòÅÅÐò
1¡¢ÅÅÐòÊǶԲéѯ½á¹ûµÄÅÅÐò£¬ÅÅÐò·½·¨
ÓÐÁ½ÖÖ£¬ÉýÐò£¨Ä¬ÈÏ asc£©ºÍ½µÐò
£¨desc£©¡£
2¡¢µ±ÅÅÐòµÄÊôÐÔÓжà¸öʱ£¬ÓÅÏÈÅÅÐòµÄ
·ÅÔÚÇ°Ãæ£¬ÒÀ´ÎÁгö£¬ÖмäÓöººÅ¸ô¿ª
¡£
3¡¢µ±²éѯ½á¹ûÊǶà¸ö±íʱ£¬ÀûÓÃÁ¬½Ó²é
ѯ£¨where Ìõ¼þÄڰѹ²Í¬ÁÐÏàµÈ£©£¬È»
ºó¶Ô²éѯ½á¹ûÅÅÐò¡£
¶þ¡¢¼¯º¯ÊýµÄÓ¦ÓÃ
1¡¢²éѯstudent±íÄÚѧÉúÈËÊý
select count(sno)
from student
select count(*)
from student
2¡¢²éѯÓÐÑ¡Ð޿γ̵ÄѧÉúÈËÊý
select count(distinct sno)
from sc
3¡¢²éѯ3ºÅ¿Î³ÌµÄ³É¼¨×Ü·Ö
select sum(grade)
from sc
where cno='3'
4¡¢²éѯѧÉúÄêÁäºÍ
select sum(sage)
from student
5¡¢²éѯѧÉú200215122ËùÑ¡Ð޵Ŀγ̵Ä
ƽ¾ù·Ö
select avg(grade)
from sc
where sno='200215122'
6¡¢Çó3ºÅ¿Î³ÌµÄ×î¸ß·Ö
select max(grade)
from sc
where cno = '3'
Á·Ï°£º
1¡¢²éѯÓм¸ÃÅÑ¡Ð޿α»Ñ¡ÐÞ
select count(distinct cno)
from sc
2¡¢²éѯѧÉúÖÐÄÐÉúÈËÊý
select count(sno)
from student
where ssex = 'ÄÐ'
3¡¢²éѯѧÉú200215121ËùÑ¡Ð޵Ŀγ̳É
¼¨×Ü·Ö
select sum(grade)
from sc
where sno='200215121'
4¡¢²éѯ3ºÅ¿Î³ÌµÄƽ¾ù·Ö
select avg(grade)
from sc
where cno='3'
˼¿¼Ì⣺
1¡¢²éѯѧÉúÖи÷ÄêÁä¶ÎÈËÊý
select sage,count(sno)
from student
group by sage
2¡¢²éѯ3ºÅ¿Î³ÌµÄƽ¾ù·ÖºÍ×î¸ß·Ö
select avg(grade) ƽ¾ù·Ö,max(grade)
×î¸ß·Ö
from sc
where cno='3'
×ܽ᣺
1¡¢¼¯º¯Êýcount(ÊôÐÔÃû)¼ÆËã¸öÊý£¬
×¢Òâdistinct ÊôÐÔÃûµÄÓ¦Óã¬È¡ÏûÖØ¸´
µÄÐС£ÊôÐÔÃûÁбí¿ÉÒÔÊǶà¸öÓÃ×Ö·û´®
Á¬½Ó£¨+£©¡£
2¡¢sum(),avg()ÕâÁ½¸öÊǶÔÊýÖµÐÍÊô
ÐÔ½øÐеÄͳ¼Æ²Ù×÷¡£
3¡¢max(),min()¸ù¾ÝÊôÐÔ£¨ÊýÖµÐ͵Ä
»ò×Ö·ûÐ͵ͼ¿ÉÒÔ£©Ñ¡Ôñ×î´óµÄ»ò×îС
µÄ¡£
Èý¡¢·Ö×égroup by×Ó¾äµÄÓ¦ÓÃ
1¡¢²éѯ¿Î³ÌºÅ¼°ÏàÓ¦µÄÑ¡ÐÞÈËÊý
select cno,count(*)
f
Ïà¹ØÎĵµ£º
²éѯËÙ¶ÈÂýµÄÔÒòºÜ¶à£¬³£¼ûÈçϼ¸ÖÖ£º
ûÓÐË÷Òý»òÕßûÓÐÓõ½Ë÷Òý(ÕâÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)¡£
I/OÍÌÍÂÁ¿Ð¡£¬ÐγÉÁËÆ¿¾±Ð§Ó¦¡£
ûÓд´½¨¼ÆËãÁе¼Ö²éѯ²»ÓÅ»¯¡£
ÄÚ´æ²»×ã¡£
ÍøÂçËÙ¶ÈÂý¡£
²éѯ³öµÄÊý¾ÝÁ¿¹ý´ó(¿ÉÒÔ²ÉÓöà´Î²éѯ£¬ÆäËûµÄ·½·¨½µµÍÊý¾ÝÁ¿)¡£
Ëø»òÕßËÀËø(ÕâÒ²ÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊÇ³Ì ......
±í½á¹¹ÈçÏÂ:
id int 4
EntryID int 4
BlogID int 4
ÏÖÔÚÒªÇóÔÚ²åÈëʱ£¬²»ÔÊÐí²åÈëEntryIDÓëBlogID¶¼ÏàͬµÄ¼Ç¼£¬¼´±íÖв»ÔÊÐíÈÎÒâÁ½Ìõ¼Ç¼µÄEntryIDÓëBlogID¶¼Ïàͬ£¬EntryIDÓëBlogID¹¹³É¼Ç¼µÄΨһ±êʶ¡£
ÒÔÇ ......
sqlÓï¾ä´óÈ«:³£Óà SQL Óï¾ä´óÈ«
¡¡¡¡Óï ¾ä ¹¦ ÄÜ
¡¡¡¡--Êý¾Ý²Ù×÷
¡¡¡¡SELECT --´ÓÊý¾Ý¿â±íÖмìË÷Êý¾ÝÐкÍÁÐ
¡¡¡¡INSERT --ÏòÊý¾Ý¿â±íÌí¼ÓÐÂÊý¾ÝÐÐ
¡¡¡¡DELETE --´ÓÊý¾Ý¿â±íÖÐɾ³ýÊý¾ÝÐÐ
¡¡¡¡UPDATE --¸üÐÂÊý¾Ý¿â±íÖеÄÊý¾Ý
¡¡¡¡--Êý¾Ý¶¨Òå
¡¡¡¡CREATE TABLE --´´½¨Ò»¸öÊý¾Ý¿â±í
¡¡¡¡DROP TABLE --´ÓÊý¾Ý¿âÖÐɾ³ý±í ......
¶ÔÓÚSQLÎÒҲѧϰÁË£¬µ«ÊÇÕæÕýÓõ½µÄʱºò²ÅÖªµÀºÜ¶à¶«Î÷»¹²»Ì«Àí½â£¬ÔÚÍøÉÏ¿´ÁËһϣ¬ÕâÆªÎÄÕÂÖн²µÄºÜºÃ£¬ËùÒÔÄÃÀ´ºÍ´ó¼Ò·ÖÏíһϣº
SQL JOIN µÄÓ÷¨
¹ØÓÚsqlÓï¾äÖеÄÁ¬½Ó£¨join£©¹Ø¼ü×Ö£¬ÊǽÏΪ³£ÓöøÓÖ²»Ì«ÈÝÒ×Àí½âµÄ¹Ø¼ü×Ö£¬ÏÂÃæÕâ¸öÀý×Ó¸ø³öÁËÒ»¸ö¼òµ¥µÄ½âÊÍ --½¨±ítable1,table2£º
create table table1(id int,n ......