SQL ѧϰ±Ê¼ÇÖ®SelectÍêÕûÓï·¨¼°Ö´Ðйý³ÌÏê½â
SELECTÓï¾äµÄÍêÕûÓ﷨Ϊ£º
SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
from tableexpression[,…][IN externaldatabase]
[WHERE…]
[GROUP BY…]
[HAVING…]
[ORDER BY…]
[WITH OWNERACCESS OPTION]
×¢Òâ Where£¬GroupBy£¬Having£¬OrderBy ˳Ðò¡£
Ö´Ðв½Öè
1£©ÏÈ´Ófrom×Ö¾äÒ»¸ö±í»ò¶à¸ö±í´´½¨¹¤×÷±í
2£©½«whereÌõ¼þÓ¦ÓÃÓÚ1£©µÄ¹¤×÷±í£¬±£ÁôÂú×ãÌõ¼þµÄÐÐ
3£©GroupBy ½«2£©µÄ½á¹û·Ö³É¶à¸ö×é
4£©Having ½«Ìõ¼þÓ¦ÓÃÓÚ3£©×éºÏµÄÌõ¼þ¹ýÂË£¬Ö»±£Áô·ûºÏÒªÇóµÄ×é¡£
5£©Order By¶Ô½á¹û½øÐÐÅÅÐò¡£
ʹÓÃDISTINCTÈ¥³ýÖظ´µÄÐÅÏ¢
Ö´ÐУº
select * from student
·µ»Ø½á¹û
-stuName -stuno
Tom Sawyer 1
Andy Ke 2
Angelia Jolie 3
Celine Dion 4
alex 5
alex 6
Ö´ÐУº
select distinct stu_name from student
½á¹û£º
alex
Andy Ke
Angelia Jolie
Celine Dion
Tom Sawyer
¼´È¥µôÁËÖظ´Öµ£¬ÓÖ¶Ô²éѯ½á¹û½øÐÐÁËÅÅÐò¡£Êµ¼ÊÉÏDBMSµÄ²Ù×÷¹ý³ÌÊÇÏȶԲéѯ½á¹ûÅÅÐòÔÚ´Ó½á¹ûÖÐÈ¥³ýÖظ´µÄ
Ïà¹ØÎĵµ£º
ÊìϤSQL SERVER 2000µÄÊý¾Ý¿â¹ÜÀíÔ±¶¼ÖªµÀ£¬ÆäDTS¿ÉÒÔ½øÐÐÊý¾ÝµÄµ¼Èëµ¼³ö£¬Æäʵ£¬ÎÒÃÇÒ²¿ÉÒÔʹÓÃTransact-SQLÓï¾ä½øÐе¼Èëµ¼³ö²Ù×÷¡£ÔÚTransact-SQLÓï¾äÖУ¬ÎÒÃÇÖ÷ҪʹÓÃOpenDataSourceº¯Êý¡¢OPENROWSET º¯Êý£¬¹ØÓÚº¯ÊýµÄÏêϸ˵Ã÷£¬Çë²Î¿¼SQLÁª»ú°ïÖú¡£ÀûÓÃÏÂÊö·½·¨£¬¿ÉÒÔÊ®·ÖÈÝÒ×µØʵÏÖSQL SERVER¡¢ACCESS¡¢EXC ......
ÄÚÈÝÕªÒª£ºÔÚPL/SQL¿ª·¢¹ý³ÌÖУ¬Ê¹ÓÃSQL£¬PL/SQL¿ÉÒÔʵÏִ󲿷ݵÄÐèÇ󣬵«ÊÇÔÚijЩÌØÊâµÄÇé¿öÏ£¬ÔÚPL/SQLÖÐʹÓñê×¼µÄSQLÓï¾ä»òDMLÓï¾ä²»ÄÜʵÏÖ×Ô¼ºµÄÐèÇ󣬱ÈÈçÐèÒª¶¯Ì¬½¨±í»òij¸ö²»È·¶¨µÄ²Ù×÷ÐèÒª¶¯Ì¬Ö´ÐС£Õâ¾ÍÐèҪʹÓö¯Ì¬SQLÀ´ÊµÏÖ¡£±¾ÎÄͨ¹ý¼¸¸öʵÀýÀ´ÏêϸµÄ½²½â¶¯Ì¬SQLµÄʹÓᣡ¡¡¡
¡¡¡¡±¾ÎÄÊÊÒ˶ÁÕß·¶Î§£ºOracle³ ......
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
¡¡¡¡ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
¡¡¡¡ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
¡¡¡¡SQL_TEXT
¡¡¡¡from V$SQLAREA
¡¡¡¡WHERE EXECUTIONS>0
¡¡¡¡AND BUFFER_GETS > 0
¡¡¡¡AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS ......
µÚÒ»ÖÖ·½·¨£ºÊ¹ÓÃNVLº¯Êý´¦ÀíNULLÖµ¡£
ÆäÓï·¨¸ñʽÊÇNVL(exp1,exp2)¡£ÆäÖвÎÊýexp1ºÍexp2¿ÉÒÔʹÈÎÒâÊý¾ÝµÄÀàÐÍ£¬µ«Á½ÕßÊý¾ÝÀàÐͱØÐëÆ¥Å䡣ʾÀý£ºselect ename,sal,comm,sal+nvl(comm,0) as salary from emp;
µÚ¶þÖÖ·½·¨£ºÊ¹ÓÃNVL2º¯Êý´¦ÀíNULLÖµ¡£
ÆäÓï·¨¸ñʽÊÇNVL2(exp1,exp2,exp3)¡£ÕâÊÇoracle9iÐÂÔö¼ÓµÄº¯Êý¡£Èç¹ûexp1 ......