[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Ò»)
±¾ÏµÁÐÎÄÕµ¼º½
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Ò»)--sum()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(¶þ)--max()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Èý)--row_number() /rank()/dense_rank()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(ËÄ)--lag()/lead()
ʵ¼ÊÓ¦ÓÃÖÐÎÒÃÇ¿ÉÒÔͨ¹ýsum()ͳ¼Æ³ö×éÖеÄ×ܼƻòÕßÊÇÀÛ¼ÓÖµ£¬¾ßÌåʾÀýÈçÏ£º
1.´´½¨ÑÝʾ±í
create table emp
as
select * from scott.emp;
alter table emp
add constraint emp_pk
primary key(empno);
create table dept
as
select * from scott.dept;
alter table dept
add constraint dept_pk
primary key(deptno);
2. sum()Óï¾äÈçÏ£º
select deptno,
ename,
sal,
¡¡¡¡--°´ÕÕ²¿ÃÅнˮÀÛ¼Ó£¨order by¸Ä±äÁË·ÖÎöº¯ÊýµÄ×÷Óã¬Ö»¹¤×÷ÔÚµ±Ç°ÐкÍǰһÐУ¬¶ø²»ÊÇËùÓÐÐУ©
sum(sal) over (partition by deptno order by sal) CumDeptTot,
sum(sal) over (partition by deptno) SalByDept, --ͳ¼ÆÒ»¸ö²¿ÃŵÄнˮ
sum(sal) over (order by deptno,sal) CumTot, --ËùÓйÍÔ±µÄнˮһÐÐÒ»ÐеÄÀÛ¼Ó
sum(sal) over () TotSal --ͳ¼Æ×Üнˮ
from emp
order by deptno, sal
3. ½á¹ûÈçÏ£º
10 MILLER 1300.00 1300 8750 1300 29025
10 CLARK 2450.00 3750 8750 3750 29025
10 KING 5000.00 8750 8750 
Ïà¹ØÎĵµ£º
----start
Ç°Ãæ£¬ÎÒÃǽéÉÜÁË ¾¡Á¿±ÜÃâÔÚSQLÓï¾äµÄWHERE×Ó¾äÖÐʹÓú¯Êý£¬ÒòΪÕâÑù×ö»áʹ¸Ã×Ö¶ÎÉϵÄË÷ÒýʧЧ£¬Ó°ÏìSQLÓï¾äµÄÐÔÄÜ¡£»ùÓÚͬÑùµÄµÀÀí£¬ÎÒÃÇÒ²Ó¦¸Ã±ÜÃâʹÓÃLIKE¡£¿¼ÂÇÏÂÃæµÄÇé¿ö£º
CREATE TABLE USER
(
NAME VARCHAR(20) NOT NULL,---ÐÕÃû
MYNUMBER VARCHAR(18)---Éí·ÝÖ¤ºÅÂë
);&nb ......
ÔÚExcelÖУ¬ÎÒÃÇʱ³£»áÅöµ½ÕâÑùµÄ×Ö¶Î(×î³£¼ûµÄ¾ÍÊǵ绰ºÅÂë)£¬¼´Óд¿Êý×ÖµÄ(ÈçûÓдøÇøºÅµÄµç»°ºÅÂë)£¬ÓÖÓÐÊý×ÖºÍÆäËü×Ö·û»ìºÏ (Èç“ÇøºÅ-µç
»°ºÅÂë”)µÄÊý¾Ý£¬ÔÚµ¼ÈëSQLServer¹ý³ÌÖУ¬»á·¢ÏÖҪô´¿Êý×ÖµÄÊý¾Ýµ¼¹ýÈ¥Ö®ºó±ä³ÉÁËNULL£¬ÒªÃ´¾ÍÊÇÊý×ÖºÍÆäËü×Ö·û»ìºÏµÄÊý¾Ýµ¼¹ýÈ¥Ö®ºó±ä³É
ÁËNULL¡£
&n ......
½ñÌìÓöµ½Ò»¸öÎÊÌ⣺
ÔÚÆ´Ò»Ìõ¶¯Ì¬SqlÓï¾äʱ£¬ÓÉÓÚÊÇÓÃ×ֶεÄÖµ×öÁÐÃû£¬ËùÒÔÁÐÃûÓõ½Á˺º×Ö¡£
Ò»ÔËÐУ¬±¨´í£ºÔÚ“¡¢”¸½¼þÓÐÓï·¨´íÎó¡£
ºÜÄÉÃÆ£¬ÄѵÀ³¬³¤ÁË£¿
³¢ÊÔÔÚÆóÒµ¹ÜÀíÆ÷ÖÐÌí¼ÓÕâ¸öÁÐÃû£¬·¢ÏÖÌí¼ÓÍêÖ®ºóϵͳ×Ô¶¯Ìí¼ÓÁËÒ»¶ÔÖÐÀ¨ºÅ£¬¶ÔÓÚ"aa¡¢bb","aa£¬bb"Ò²¶¼Ìí¼ÓÁËÖÐÀ¨ºÅ¡£
¿´À´ÊÇ΢ÈíΪÁËÒÔ·ÀÍòÒ»£¬Á¬ ......
SQL×¢Èë¼òµ¥·ÖÎö
ʾÀýÓï¾ä£º
select * from admintable where adminName like '%a%'
ÔÚ²éѯÖÐÎÒÃÇÒ»°ãÔÚaÕâ¸öµØ·½ÓɽçÃæ´«È벻ͬµÄÖµ£¬µ±ÎÒÃÇÔÚaÕâÀï´«ÈëµÄֵΪ“'”µ¥ÒýºÅʱ£¬Æ´´Õ³ÉµÄSQLÓï¾ä¾ÍÈçÏ£º
select * from admintable where adminName like '%'%'
Ö´ÐÐÕâ¾äÓï¾äÎÒÃǻᷢÏÖ³öÏÖÒÔÏÂÒì³££º
......
2008µÄSSMS±È2005°æÒª¶àÏûºÄÒ»±¶×óÓÒµÄÄڴ棬¶øÇÒËÆºõ²»»á×Ô¼ºÊÍ·Å£¬ÖÁÉÙÒ²ÊÇÄÚ´æ¹ÜÀí²»ÊǺܺÏÀí£¬ÍùÍù´ò¿ª¼¸¸ö²éѯ´°¿Ú½øÐвéѯºóÄÚ´æ¾Í»áÉýµ½ÄÑÒÔ200MBµ½300MB£¬ÇҹصôºóÄÚ´æ²»»áÊÍ·Å£¬¶ø2005µÄSSMSÒ»°ãÖ»ÊÇÔÚ100MB×óÓÒ¡£¶ÔÓµÓдóÄÚ´æµÄµçÄÔÀ´ËµÕâ¿ÉÄܲ»Ëãʲô£¬µ«¶ÔÄÚ´æÖ»ÓÐ1G»ò¸üÉÙµÄÓû§À´Ëµ£¬Õ⼸ºõÊDz»¿ÉÈÝÈ̵ģ¬ÒòÎ ......