Oracle SQLÓï¾ä
ORDER BY ÅÅÐò
ASC ÉýÐò(ĬÈÏ)
DESC ½µÐò
select * from s_emp order by dept_id , salary desc
²¿ÃźÅÉýÐò£¬¹¤×ʽµÐò
¹Ø¼ü×ÖdistinctÒ²»á´¥·¢ÅÅÐò²Ù×÷¡£
select * from employee order by 1; //°´µÚÒ»×Ö¶ÎÅÅÐò
NULL±»ÈÏΪÎÞÇî´ó¡£order by ¿ÉÒÔ¸ú±ðÃû¡£
select table_name from user_tables where table_name='S_EMP'; ²éij¸ö¾ßÌå±íÃûʱ£¬±íÃûµÄ×Ö·û´®±ØÐëҪΪ´óд
»òÕß²ÉÓà upper(table_name)
select * from user_talbes where table_name like ‘s\_%’ escape ‘\’;
ʹÓÃתÒå×Ö·û¶Ô¹Ø¼ü×Ö½øÐÐתÒå¡£
concat Á¬½Ó×Ö·û´® select concat(first_name , last_name) from s_emp;µÈЧÓÚ||
substr Çó×Ó´® select substr('tarenasd0603' ,1,6) from dual; (È¡Ç°Áù¸ö×Ö·û) select substr('tarenasd0603',-2) from dual; (È¡ºóÁ½¸ö×Ö·û)
length Çó×Ö·û³¤¶È
select length('zhonghua') from dual;
from dualµÄÒâ˼
Ðé±í£¨dual£©ÊÇoracleÌṩµÄ×îСµÄ¹¤×÷±í£¬Ëü½ö°üº¬Ò»ÐÐÒ»ÁС£¶ÔÓÚÐé±í£¨dual£©À´Ëµ£¬ÆäÖеÄÁÐÍùÍùÊDz»Ïà¹ØµÄ»òÎ޹ؽôÒªµÄ¡£
È磺²éѯµ±Ç°µÄϵͳÈÕÆÚ
SQL> select sysdate from dual;
SYSDATE
-------------------
2004/04/28 08:49:41
round º¯Êý(ËÄÉáÎåÈë) select round(45.935, 2) from dual; ²»´ø²ÎÊýʱĬÈÏΪ0λСÊý
trunc º¯Êý(½ØÈ¡£¬²»¹ÜºóÃæµÄÊý×Ö) select trunc(45.995, 1) from dual;
×麯Êý
group by ·Ö×é×Ó¾ä ¶Ô·Ö×éºóµÄ×Ó¾ä½øÐйýÂË»¹¿ÉÒÔÓÃhaving Ìõ¼þ ¶Ô·Ö×éºóµÄÌõ¼þ½øÐйýÂË where ÊǶԼǼ½øÐйýÂË
ÓÐÓ¶½ðÈËÊýµÄ°Ù·Ö±È
select count( commission_pct )count(*) from s_emp;
select count(dept_id) from s_emp;
select count(distinct dept_id) from s_emp;//Çø·ÖÏàͬµÄdept_id
Çó¸÷¸ö²¿ÃŵÄƽ¾ù¹¤×Ê£ºgroup by ×Ó¾äÒ²»á´¥·¢ÅÅÐò
select dept_id , avg(salary) aa from s_emp group by dept_id order by aa ; //¶Ôƽ¾ù¹¤×ÊÅÅÐò
select dept_id , avg(salary) aa from s_emp group by dept_id;
ÄÄЩ²¿ÃŵÄƽ¾ù¹¤×ʱÈ2000¸ß:
select dept_id, avg(salary) aa from s_emp group by (dept_id) having avg(salary)>2000;
³ýÁË42²¿ÃÅÒÔÍâµÄ²¿ÃŵÄƽ¾ù¹¤×Ê:
select dept_id , avg(salary) fr
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
ÎÒÔÚÉè¼ÆÊý¾Ý¿âµÄʱºò»á¿¼Âǵ½ÄÄЩÊý¾Ý×ֶν«À´¿ÉÄܻᷢÉú±ä¸ü¡£±È·½Ëµ£¬ÐÕÊϾÍÊÇÈç´Ë£¨×¢ÒâÊÇÎ÷·½È˵ÄÐÕÊÏ£¬±ÈÈçÅ®ÐÔ½á»éºó´Ó·òÐյȣ©¡£ËùÒÔ£¬ÔÚ½¨Á¢ÏµÍ³´æ´¢¿Í»§ÐÅϢʱ£¬ÎÒÇãÏòÓÚÔÚµ¥¶ÀµÄÒ»¸öÊý¾Ý±íÀï´æ´¢ÐÕÊÏ×ֶΣ¬¶øÇÒ»¹¸½¼ÓÆðʼÈÕºÍÖÕÖ¹ÈÕµÈ×ֶΣ¬ÕâÑù¾Í¿ÉÒÔ¸ú×ÙÕâÒ»Êý¾ÝÌõÄ¿µÄ±ä»¯¡£
²ÉÓÃÓÐÒâÒåµÄ×Ö¶ÎÃû
ÓÐÒ»»ØÎÒ ......
SQLÔÚ½¨Á¢Óë·þÎñÆ÷µÄÁ¬½Óʱ³ö´íµÄ½â¾ö·½
ÔÚ½¨Á¢Óë·þÎñÆ÷µÄÁ¬½Óʱ³ö´í¡£ÔÚÁ¬½Óµ½ SQL Server 2005 ʱ£¬ÔÚĬÈϵÄÉèÖÃÏ SQL Server ²»ÔÊÐí½øÐÐÔ¶³ÌÁ¬½Ó¿ÉÄܻᵼÖ´Ëʧ°Ü¡£ (provider: ÃüÃû¹ÜµÀÌṩ³ÌÐò, error: 40 - ÎÞ·¨´ò¿ªµ½ SQL Server µÄÁ¬½Ó)
¸Õ¸Õ°²×°µÄÊý¾Ý¿âϵͳ£¬°´ÕÕÄ ......
Ò»¡¢ÔÚDelphi7ÖÐÁ¬½ÓMS SQL Server 2000µÄ·½·¨¡£
¸Õ¿ªÊ¼Ê±½çÃæÈçÏ£ºÌí¼Ó4¸ö¿Ø¼þ¡£
ÉèÖÿؼþÊôÐÔ¹ý³Ì£º
1¡¢ADOConnection1ÉèÖÃ
1£©Ë«»÷ADOConnection1£¬½øÐÐÉèÖÃÁ¬½Ó×Ö·û´®£¨×÷ÓÃÊÇ£ºÑ¡È¡Á¬½ÓÇý¶¯·½Ê½ºÍÁ¬½ÓµÄÊý¾Ý¿âÉèÖã©¡£¹ý³ÌÈçÏÂͼËùʾ£º
2¡¢ADOQuery1ÉèÖãº
1£©ADOQuery1.connectionÊôÐÔΪADOConnection1; ......
Sql Server ÖÐÒ»¸ö·Ç³£Ç¿´óµÄÈÕÆÚ¸ñʽ»¯º¯Êý
Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GE ......