ããOracleæ°æ®åºåºæ¬æä½
ãã1.æ¦è¿°
ããOracleæ°æ®åºå®¢æ·ç«¯ä¸è¬éè¦å®è£
å¨æå¡å¨ä¸ï¼å¯ä»¥å¨æå¡å¨ç«¯æä½ï¼ä¸è¬æ们å¯ä»¥ç¨sql developerå·¥å
·è¿ç¨è¿æ¥å°æ°æ®åºï¼å
è¡å»ºç«æ°æ®åºï¼ç¶å对表è¿è¡å¢å æ¹æ¥ãä¹å¯ä»¥ä½¿ç¨MyEclispseçDB Broswerå·¥å
·è¿è¡è¿æ¥æ°æ®åºå¹¶è¿è¡ç®åçå¢å æ¹æ¥ã
ãã2ï¼SQLè¯å¥
ããData Definition Languageï¼DDLï¼ï¼
ãã主è¦ç¨äºå»ºç«ãä¿®æ¹ãå é¤æ°æ®åºå¯¹è±¡ï¼è¡¨ï¼ï¼ä¸éè¦äºå¡çåä¸CREATE:å建表
ããCREATE TABLE emp(
ããid NUMBER(10),
ããname VARCHAR2(20),
ããgender CHAR(1),
ããbirth DATE,
ããsalary NUMBER(6,2),
ãã);
ããDESC :æ¥è¯¢è¡¨ç»æ
ããDESC emp;
ããRENAME:éå½å表å
ããRENAME emp TO employee;
ããDROPï¼å é¤è¡¨
ããDROP TABLE emp;
ããALTER:åï¼å段ï¼æä½
ããADDï¼å¢å å段
ããALTER TABLE employee ADD (birth DATE DEFAULT sysdate);MODITYï¼ä¿®æ¹å段
ããALTER TABLE employee MODIFY (name VARCHAR2(40) DEFAULT 'CLERK' );DROPï¼å é¤å段
ããALTER TABLE employee DROP (birth);
ããData Manipulation Languageï¼DMLï¼
ããç¨äºå¯¹æ°æ®è®°å½è¿è¡æä½ï¼å
æ¬æå
¥ï¼å é¤ï¼ä¿®æ¹ãéè¦commitæè½çæ£ç¡®è®¤æä½ï¼å¦æéè¦æ¤éårollbackã
ããINSERT INTOï¼æå
¥æ°æ®
ããINSERT INTO employee(id, name, salary) VALUES(1001, 'rose', 5500);UPDATEâ¦SET..ï¼æ´æ°æ°æ®
ããUPDATE employee SET salary = 8500 WHERE name = 'ROSE';DELETE FROMï¼å é¤è®°å½
ããDELETE FROM employee WHERE job is null;
ããTransaction Control Languageï¼TCLï¼
ããäºå¡æ§å¶è¯è¨ï¼å
æ¬commitæ交ï¼rollbackåæ»ï¼savepointä¿åç¹ï¼å¯ä»¥åéå°æå®ä¿åç¹ï¼ãå¨sql developerä¸ä¸ºå¾æ ã
ããData Query Languageï¼DQLï¼
ããSQLåºç¡æ¥è¯¢ï¼
ããæ¥è¯¢è¯å¥çæ§è¡é¡ºåº
ããFROM åå¥ï¼æ§è¡é¡ºåºä¸ºä»åå¾åãä»å³å°å·¦ãæ°æ®éè¾å°ç表尽éæ¾å¨åé¢ã
ããWHEREåå¥ï¼æ§è¡é¡ºåºä¸ºèªä¸èä¸ãä»å³å°å·¦ãå°è½è¿æ»¤ææ大æ°éè®°å½çæ¡ä»¶åå¨WHERE åå¥çæå³ã
ããGROUP BYï¼æ§è¡é¡ºåºä»å·¦å¾å³åç»ï¼æ好å¨GROUP BYå使ç¨WHEREå°ä¸éè¦çè®°å½å¨GROUP BYä¹åè¿æ»¤æã
ããHAVING åå¥ï¼æ¶èèµæºãå°½éé¿å
使ç¨ï¼HAVING ä¼å¨æ£ç´¢åºææè®°å½ä¹åæ对ç»æéè¿è¡è¿æ»¤ï¼éè¦æåºçæä½ã
ããSELECTåå¥ï¼å°ç¨*å·ï¼å°½éåå段å称ãORACLE å¨è§£æçè¿ç¨ä¸, éè¿æ¥è¯¢æ°æ®åå
¸å°*å·ä¾æ¬¡è½¬æ¢æææçåå, æ¶èæ¶é´ã
ããORDER BYåå¥ï¼æ§è¡é¡ºåºä¸ºä»å·¦å°å³æåºï¼æ¶èèµæºã
ããä¹å°±æ¯ä»åªå¼ 表ä¸ä»¥ä»ä¹æ ·çéå¶æ¡ä»¶ç¡®å®æ°æ®å
容ï¼åéè¿åç»ä¸è¿ä¸æ¥éå¶åç»æ¡ä»¶å¾å°ä¸ä¸ªå¤çè¿çæ°æ®ï¼æåéæ©åºæ¥ï¼å¦æéè¦ï¼å对æåéæ©çæ°æ®è¿è¡æåºã
ããFROMåå¥ï¼
ããFromåæ¥è¡¨åï¼fromåå¯ä»¥å¯ä»¥æ¯*ï¼ä½æ¯ä¸å»ºè®®ï¼æ¥è¯¢æçä½ï¼ï¼ä¸è¬æ¥è¦æ¥è¯¢çå段åãåç»å½æ°ï¼å段å表åé½å¯ä»¥ä½¿ç¨å«åï¼ä¸å å¼å·æ°æ®åºä¸ä¸ºå¤§åï¼å å¼å·å«åå¯ä»¥ç¨ç©ºæ ¼å°åçã
ããWHEREåå¥ï¼
ããå½æ¥è¯¢æ¡ä»¶ä¸åæ°åæ¯è¾ï¼å¯ä»¥ä½¿ç¨åå¼å·å¼èµ·ï¼ä¹å¯ä»¥ä¸ç¨ï¼å½åå符åæ¥æç±»åçæ°æ®æ¯è¾ï¼åå¿
é¡»ç¨åå¼å·å¼èµ·ã
ãã使ç¨æ¡ä»¶å¤æï¼>ï¼<ï¼<=ï¼<=ï¼!=ï¼<>ï¼=ã
ããSELECT ename, sal FROM emp WHERE sal< 2000;SELECT ename, sal, job FROM emp WHERE job = 'SALESMAN';SELECT ename, sal, hiredate FROM emp
ããWHERE hiredate>todate('2002-1-1','YYYY-MM-DD');使ç¨AND/ORå
³é®å
ããå¦æå¸æè¿åçç»æå¿
须满足å¤ä¸ªæ¡ä»¶ï¼åºè¯¥ä½¿ç¨ANDé»è¾æä½ç¬¦è¿æ¥è¿äºæ¡ä»¶å¦æå¸æè¿åçç»æ满足å¤ä¸ªæ¡ä»¶ä¹ä¸å³å¯ï¼åºè¯¥ä½¿ç¨ORé»è¾æä½ç¬¦è¿æ¥è¿äºæ¡ä»¶SELECT ename, sal, job FROM emp
ããWHERE sal> 1000 AND job = 'CLERK';
ãã使ç¨LIKEæ¡ä»¶
ããSELECT ename, job FROM emp WHERE ename LIKE '_A%';ï¼ç¬¬äºä¸ªåæ¯ä¸ºAï¼â_A%â_表示å ä½ä¸ä¸ªå符ï¼%表示0å°å¤ä¸ªå符ã
ãã使ç¨IN/NOT IN
ããå¨WHEREåå¥ä¸å¯ä»¥ç¨æ¯è¾æä½ç¬¦IN(list)æ¥ååºç¬¦åå表èå´ä¸çæ°æ®ãå
¶ä¸çåæ°list表示å¼å表ï¼å½åæ表达å¼å¹é
äºå表ä¸çä»»ä½ä¸ä¸ªå¼æ¶ï¼æ¡ä»¶ä¸ºTRUEï¼è¯¥æ¡è®°å½å被æ¾ç¤ºåºæ¥ã
ããSELECT ename, job FROM emp WHERE job IN ('MANAGER', 'CLERK');使ç¨BETWEEN 1 AND 2
ããç¨æ¥æ¥è¯¢ç¬¦å(1ï¼2)å¼åèå´ä¹å
çæ°æ®ï¼é常æ¥è¯¢æ°ååæ¥æç±»åçæ°æ®èå´ã
ããSELECT ename, sal FROM emp
ããWHERE sal BETWEEN 1500 AND 3000;
ãã使ç¨IS NULLåIS NOT NULL
ããæ¡ä»¶çéå¼ä¸ºç©ºçè®°å½ï¼ç©ºå¼ä¸è½ç¨â=âæ¥å¤æã
ãã使ç¨ANYåALLæ¡ä»¶
ããå¨æ¯è¾è¿ç®ç¬¦ä¸ï¼å¯ä»¥åºç°ALLåANYï¼è¡¨ç¤ºâå
¨é¨âåâä»»ä¸âï¼> ANY : 大äºæå°< ANYï¼å°äºæ大> ALLï¼å¤§äºæ大< ALLï¼å°äºæå°SELECT empno, ename, job, sal, deptno
ããFROM emp WHERE
ããsal> ANY (SELECT sal FROM emp WHERE job = 'SALESMAN');æ¥è¯¢æ¡ä»¶ä¸ä½¿ç¨ç®æ¯è¡¨è¾¾å¼
ããå½æ¥è¯¢éè¦å¯¹éåºçå段è¿è¡è¿ä¸æ¥è®¡ç®ï¼å¯ä»¥å¨æ°ååä¸ä½¿ç¨ç®æ¯è¡¨è¾¾å¼(+ã-ã*ã/)ã表达å¼ç¬¦åååè¿ç®çé»è®¤ä¼å
级ï¼å¦æè¦æ¹åä¼å
级å¯ä»¥ä½¿ç¨æ¬å·ã
ããSELECT ename, sal, job FROMempWHERE ename = UPPER('rose');SELECT ename, sal, job FROM empWHERE sal * 12 >100000;使ç¨DISTINCTè¿æ»¤éå¤
ããDISTINCTå¿
须紧è·SELECTï¼åé¢å¯ä»¥æå¤ä¸ªå段ï¼è¡¨ç¤ºè¿æ»¤æå¤ä¸ªå段é½éå¤çé项ï¼ç¬¬äºä¾ä¸ä¸æ¾ç¤ºéå¤çdeptnoåjobç¸åçè®°å½ã
ããSELECT DISTINCT deptno FROM emp;
ããSELECT DISTINCT deptno, job FROM emp;
ãã使ç¨ORDER BYåå¥
ãã对æ¥è¯¢æ°æ®æç
§ä¸å®çè§åæåºï¼å使ç¨order byåå¥ï¼order byåå¥å¿
须为selectåå¥çæåä¸ä¸ªåå¥ãé»è®¤ååºæåï¼ä»å°å°å¤§ï¼ASC表示ååºãDESC表示éåºï¼ä»å¤§å°å°ã
ããSELECT ename, sal FROM emp
ããORDER BY sal DESC;
ããOrder byå¤ä¸ªåï¼æ¯ä¸ªåéè¦åç¬æå®æåºè§åï¼å
æç
§ç¬¬ä¸ä¸ªå段æåºï¼å½ç¬¬ä¸ä¸ªå段çå¼ç¸åæ¶ï¼åæç
§ç¬¬äºä¸ªå段çå¼æåºã
ããèåå½æ°åç»å½æ°
ããå½éè¦ç»è®¡çæ°æ®å¹¶ä¸è½å¨è¡¨éç´è§ååºï¼èæ¯éè¦æ ¹æ®ç°æçæ°æ®è®¡ç®å¾å°ç»æï¼è¿ç§åè½å¯ä»¥ä½¿ç¨èåå½æ°æ¥å®ç°ã
ããå 为æ¯å¤è¡æ°æ®åä¸è¿ç®è¿åä¸è¡ç»æï¼ä¹ç§°ä½åç»å½æ°ãå¤è¡å½æ°ãéåå½æ°ãç¨å°çå
³é®åï¼GOURP BY æä»ä¹åç»ï¼HAVINGè¿ä¸æ¥éå¶åç»ç»æMAXåMIN
ããç¨æ¥åå¾åæè
表达å¼çæ大æå°å¼ï¼å
æ¬æ°åï¼å符åæ¥æã
ããSELECT MAX(sal) max_sal, MIN(sal) min_sal FROM emp;AVGåSUM
ããç¨æ¥ç»è®¡åæè
表达å¼çå¹³åå¼åæ±åï¼åªè½å¤çæ°åç±»ï¼å¹¶ä¸å¹³åå¼å¿½ç¥NULLã
ããSELECT AVG(sal) avg_sal, SUM(sal) sum_sal FROM emp;COUNT
ããç¨æ¥è®¡ç®è¡¨ä¸çè®°å½æ¡æ°ï¼åæ ·å¿½ç¥NULLã
ããSELECT COUNT(job) total_num FROM emp;
ãã空å¼çæä½
ããNVL(expr1, expr2)ï¼å°NULL转å为éNULLå¼ãå¦æexpr1为NULLï¼ååå¼expr2, expr2æ¯é空å¼ã
ããNVL2(expr1, expr2, expr3)ï¼åNVLå½æ°åè½ç±»ä¼¼ï¼é½æ¯å°NULL转å为é空å¼ãNVL2ç¨æ¥å¤æexpr1æ¯å¦ä¸ºNULLï¼å¦æä¸æ¯NULLï¼è¿åexpr2ï¼å¦ææ¯NULLï¼è¿åexpr3ã
ããGROUP BYåå¥
ããGroup byæ¯è¡¨ç¤ºå¯¹è¡¨ä¸æ个å段è¿è¡åç»ï¼å¼ç¸å为ä¸ç»ï¼èåç»å½æ°çææåæ¯å¯¹è¿æ¯ä¸ä¸ªç»è¿è¡è®¡ç®ï¼å¹³åå¼ææ¯æ大æå°å¼ã
ããHAVINGåå¥
ããæ¯å¯¹åç»åçç»æè¿è¡è¿ä¸æ¥çéå¶ï¼HAVINGåå¥å¿
须紧è·å¨GROUP BYåå¥åï¼ä¸è½åç¬åå¨ãéå¶åç»æ¡ä»¶ä¸è½æ¾å¨WHEREåå¥ä¸ã
ããSELECT deptno, MAX(sal) max_sal FROM emp
ããGROUP BY deptno HAVING MAX(sal) >4000;
ããSQLå
³èæ¥è¯¢ï¼
ããæ¦è¿°ï¼å¨å®é
åºç¨ä¸ï¼å¾å¾æ们æéè¦çæ°æ®æ¯åå¸å¨ä¸åç表ä¸çï¼æ们æ³è¦è·åæ°æ®å¿
é¡»è·¨è¡¨æ ¼æ¥è¯¢ã
ããå
³èæ两ç§æ¹å¼ï¼
ããSELECT table1.column, table2.column
ããFROM table1, table2
ããWHERE table1.column1 = table2.column2;
ããSELECT table1.column, table2.column
ããFROM table1JOIN table2
ããON(table1.column1 = table2.column2);
ããå
³èæ¥è¯¢æä¸ç§è¿æ¥æ¹å¼
ããå
è¿æ¥è¿åå
³è表ä¸ææ满足æ¡ä»¶çè®°å½ï¼ä¹ç§°çå¼è¿æ¥ï¼å¤è¿æ¥è¿åä¸äºä¸æ»¡è¶³æ¡ä»¶çè®°å½ï¼å¤è¿æ¥æå·¦å¤è¿æ¥ï¼å³å¤è¿æ¥åå
¨å¤è¿æ¥ä¸ç§å½¢å¼å
¶ä¸å·¦å¤è¿æ¥è¿å左边ææè®°å½èä¸å¿
管å³è¾¹æ¯å¦å¹é
ï¼å¦ä¸å¹é
å为NULLå¼ï¼å³å¤è¿æ¥è¿åå³è¾¹ææè®°å½èä¸å¿
管左边çè®°å½æ¯å¦å¹é
ï¼å¦ä¸å¹é
åé»è®¤NULLï¼å
¨å¤è¿æ¥è¿å左边åå³è¾¹ææçæ°æ®ï¼å·¦å³è¾¹ä¸å¹é
çæ°æ®ï¼å¯¹åºçå
¶ä»å段å¼ä¸ºNULLï¼æ ¼å¼å¦ä¸
ããJOIN ONå¼
ããSELECT table1.column, table2.column
ããFROM table1 [LEFT | RIGHT | FULL] JOIN table2ON table1.column1 = table2.column2;
ããWHEREå¼
ããSelect * from dave a,bl b where a.id=b.id(+);+å·çä½ç½®åæä¹
ããâ+âå·å å¨åªä¸ªè¡¨ï¼åªä¸ªè¡¨å°±æ¯å
³è表ï¼å¦ä¸å¼ 表就æ¯åºè¡¨ãåºè¡¨å
¨é¨æ¾ç¤ºï¼å
³è表å¹é
æ¾ç¤ºã+å¨å·¦è¾¹å°±æ¯å³å¤è¿æ¥ï¼+å¨å³è¾¹å°±æ¯å·¦å¤è¿æ¥ã
ããèªè¿æ¥ï¼
ãã表示æ°æ®çæ¥æºæ¯åä¸å¼ 表çå
容ï¼å³å°ä¸å¼ 表ä¸çä¸ååè¿è¡è¿æ¥ï¼å¯ä»¥æ¯çå¼æè
ä¸çå¼ãå®ç°æ¹å¼æ¯å©ç¨å«åå°ä¸å¼ 表çä½ä¸¤å¼ 表ã
ããSELECT worker.empnow_empno,worker.enamew_ename, manager.empnom_empno, manager.enamem_enameFROM emp worker join emp manager
ããON worker.mgr = manager.empno;
ããSQLé«çº§æ¥è¯¢ï¼
ããåæ¥è¯¢ï¼
ããåæ¥è¯¢ç¨å¨WHEREé
ããå¨SELECTä¸ï¼å¾å¾WHEREçéå¶æ¡ä»¶å¹¶ä¸æ¯ä¸ä¸ªç¡®å®çå¼ï¼èæ¯æ¥æºäºå¦ä¸ä¸ªæ¥è¯¢ç»æï¼å³éè¦å¨å¦ä¸ä¸ªæ¥è¯¢ç»æçåºç¡ä¸è¿è¡æ¥è¯¢ï¼è¿ä¸ªæ¶å为å¦ä¸ä¸ªæ¥è¯¢æä¾æ°æ®çæ¥è¯¢å°±å«ååæ¥è¯¢ã
ããSELECT e.ename, e.job FROM emp e
ããWHERE e.job = (SELECT job FROM emp WHERE ename = 'SCOTT');å¯ä»¥ä¸å¤è¡/åè¡æ¯è¾æä½ç¬¦æ··å使ç¨ã
ããåæ¥è¯¢ç¨å¨HAVINGåå¥
ããSELECT deptno, MIN(sal) min_sal FROM emp GROUP BY deptnoHAVING MIN(sal) > (SELECT MIN(sal) FROM emp WHERE deptno = 30);表示åç»æ¡ä»¶éè¦æ»¡è¶³çæ¡ä»¶ãå¯ä»¥æåæ¥è¯¢å½æä¸ä¸ªç»æã
ããåæ¥è¯¢ç¨å¨FROMé¨å
ããåæ¥è¯¢ç¨å¨FROMåå¥ä¸ï¼åæ¥è¯¢å¯ç§°ä¸ºè¡å
è§å¾æè
å¿åè¯å¾ï¼å¯ä»¥æå®å½æä¸å¼ åç¬ç表ï¼ç¨å«åè¿è¡æ è¯ã
ããåæ¥è¯¢ç¨å¨SELECTåå¥ä¸
ããå¯ä»¥è®¤ä¸ºæ¯å¤è¿æ¥çä¸å¼ 表ç°
ããå页æ¥è¯¢ï¼
ããROWNUM
ãã伪åï¼è¿åæ è¯è¡æ°æ®é¡ºåºçæ°åï¼ä¼ªå并ä¸æ¯çæ£çåæ°æ®ï¼åªæ¯ç¨æ¥æ¾ç¤ºè¡æ°ï¼å¹¶ä¸è½åç¬ä½ä¸ºä¸åè¿è¡åç»æä½ã
ããROWNUMçç»æä»ç¬¬ä¸è¡æ°æ®ä¹ä¸å¼å§ï¼æ¯æ¥è¯¢å°ä¸æ¡æ°æ®åæéä¸ç§»ä¸ä¸ªï¼æ以åªææ¥è¯¢å°æ°æ®åæè½å¤åºç°ä¼ªåæ°åï¼å æ¤ä¸è½ç´æ¥ç¨å¨whereéï¼å¦æè¦å©ç¨ROWNUMæªåç»æéçé¨åæ°æ®ï¼å¯ä»¥å°å«æROWNUM伪åçSELECTåå¥æ¾å¨FROMå
ï¼ä½ä¸ºè§å¾ï¼ä¾å¤é¨çSELECTè¯å¥ä½¿ç¨ï¼æ¤æ¶ç伪å已形æ顺åºæ°æ®ï¼å¯ä»¥è¿è¡åç»å½æ°æä½ã
ããä¹å°±æ¯å°ROWNUMå
ä½ä¸ºè¡å
è§å¾çä¸ä¸ªåï¼å¨ä¸»æ¥è¯¢ä¸å°±å¯ä»¥ä½¿ç¨è¿ä¸ªåå¼ä½ä¸ºæ¡ä»¶ã
ããSELECT * FROM (SELECT ROWNUMrn , e.* FROM emp e )WHERE rn BETWEEN 8 AND 10;
ãã使ç¨åæ¥è¯¢åROWNUMè¿è¡å页
ããå页æä½éè¦æä¸ä¸ªä½ä¸ºå页æ åçæ°æ®ï¼è¯¥æ°æ®çº¿è¿è¡æåºï¼æåºçç»æä½ä¸ºè§å¾è¢«ç¶æ¥è¯¢ç¨ä¼ªåæ è¯é¡ºåºæ°åï¼ç¶åå¨æå¤é¢çç·ç·æ¥è¯¢å¯¹ä¼ªåè¿è¡å页ã
ããå¦ï¼
ããSELECT * FROM
ãã(SELECT ROWNUMrn , t.* FROM
ãã(SELECT empno,ename,sal FROM emp
ããORDER BY sal DESC) t
ãã)
ããWHERE rn BETWEEN 8 AND 10;
ããDECODEå½æ°
ããDECODE (expr, search1, result1[, search2, result2â¦][, default])å®ç¨äºæ¯è¾åæ°exprçå¼ï¼å¦æå¹é
å°åªä¸ä¸ªsearchæ¡ä»¶ï¼å°±è¿å对åºçresultç»æï¼å¯ä»¥æå¤ç»searchåresultç对åºå
³ç³»ï¼å¦æä»»ä½ä¸ä¸ªsearchæ¡ä»¶é½æ²¡æå¹é
å°ï¼åè¿åæådefaultçå¼ãdefaultåæ°æ¯å¯éçï¼å¦æ没ææä¾defaultåæ°å¼ï¼å½æ²¡æå¹é
å°æ¶ï¼å°è¿åNULLã
ããSELECT ename, job, sal,
ããDECODE(job, 'MANAGER', sal * 1.2,
ãã'ANALYST', sal * 1.1,
ãã'SALESMAN', sal * 1.05,
ããsal) bonus
ããFROM emp;
ããSELECT deptno, dname, loc
ããFROM dept
ããORDER BY
ããDECODE(dname, 'ç åé¨',1,'å¸åºé¨',2,'éå®é¨',3), loc;åç»å½æ°
ããROW_NUMBER
ããROW_NUMBER()
ããOVER (PARTITION BY deptno ORDER BY empno)æ ¹æ®deptnoåç»ï¼å¨åç»å
æ ¹æ®empnoå
æåºï¼æ¯ROWNUMåè½æ´å¼ºãå¯ä»¥ç´æ¥ä»ç»æéä¸ååºåéRANK
ããRANK() OVER( PARTITION BY col1 ORDER BY col2)åè½ä¸ä¸ç¸åï¼ä¸åå¨äºåå¨å¹¶åï¼å¹¶å第äºè·³è¿ç¬¬ä¸ç´æ¥ç¬¬åçè§åã
ããDENSE_RANK
ããå¦ææ并å第äºï¼ä¸ä¸ä¸ªæåºå°æ¯ä¸ã
ããé«çº§åç»å½æ°
ããROLLUP
ããå¦æ对两个å段rollupï¼é£ä¹ç¬¬ä¸ä¸ªå段ç¸åçå¼ä¼å½æä¸ç»ï¼å¦ææåç»å½æ°æ±åï¼å°±ä¼å¯¹ç¬¬ä¸å段ææçæ°æ®æ±åï¼åç¬æè¡ãæ¤æ¶ç¬¬äºå段å¼ä¸ºç©ºãå¯ä»¥ç¨äºç»è®¡å¹´åº¦éå®ï¼å·¥èµï¼åã
ããCUBE
ããCUBEå½æ°å¯¹å段è¿è¡æåç»è®¡ï¼æ¯å¦ä¸ä¸ªå段çCUBEè¿ç®ï¼å°ä¸ä¸ªå段æåæ6ç§æ
åµå
·ä½éè¦ä½¿ç¨çæ¶åååèç¾åº¦ã
ããGROUPINGï¼ï¼
ããGROUPINGåªè½å¨ä½¿ç¨ROLLUPæCUBEçæ¥è¯¢ä¸ä½¿ç¨ãå½éè¦å¨è¿å空å¼çå°æ¹æ¾ç¤ºæ个å¼æ¶ï¼GROUPING()å°±é常æç¨ãæ¡ä¾æ
åµå¤æï¼å
·ä½ä½¿ç¨æ¶å说ã
ããéåæä½
ããUNIONãUNION ALLãINTERSECTãMINUS
ããUnion表示åå¤æ¬¡SELECTç»æç并éï¼å¦æå»æéå¤çæ°æ®ã
ããUnion Allä¸unionåè½ä¸æ ·ï¼åªæ¯å®ä¸ä¼å»æéå¤çæ°æ®ï¼ä¼å
¨é¨æ¾ç¤ºã
ããIntersect表示ç¸äº¤ï¼å¤æ¬¡æ¥è¯¢åå»ç¸åçæ°æ®ï¼å³åæ¶æ»¡è¶³ä¸¤ä¸ªæ¥è¯¢æ¡ä»¶çæ°æ®Minus表示差éï¼å³å°ç¬¬ä¸ä¸ªç»æéä¸çæ°æ®ï¼åå»ç¬¬äºä¸ªç»æéçæ°æ®ãå³æ»¡è¶³ç¬¬ä¸ä¸ªæ¥è¯¢æ¡ä»¶ï¼èä¸æ»¡è¶³ç¬¬äºæ¥è¯¢æ¡ä»¶çæ°æ®ã
ããData Control Languageï¼DCLï¼:
ããç¨äºæ§è¡æéçæäºåæ¶åæä½ãå建ç¨æ·çï¼å
æ¬æäº(GRANT)è¯å¥ï¼æ¶å(REVOKE)è¯å¥ï¼CREATE USERè¯å¥ï¼å
¶ä¸GRANTç¨äºç»ç¨æ·æè§è²æäºæéï¼ REVOKEç¨äºæ¶åç¨æ·æè§è²å·²æçæéãDCLè¯å¥ä¹ä¸éè¦äºå¡çåä¸ï¼æ¯èªå¨æ交çã
ããGRANT CREATE VIEW TO tarena;
ãã3.è§å¾ãç´¢å¼ãåºåã约æ
ããè§å¾
ããè§å¾æ¬è´¨ä¸æ¯ä¸æ¡SELECTè¯å¥ï¼å½SELECTåæ¥è¯¢å¨fromåå¥ä¸ï¼å¯ä»¥æSELECTåå¥çç»æå½ä½ä¸ä¸ªè§å¾ãè§å¾æ¬èº«åªæ¯åºè¡¨çæ å°ï¼åªæ¯æåºè¡¨ä¸çæ°æ®æ¾ç¤ºåºæ¥ï¼å¯ä»¥æè§å¾å½æ表çå¾
ï¼æææä½é½ä¸è¡¨æä½æå
¶ç¸ä¼¼ã
ããè§å¾æä¸ç§ï¼ç®åè§å¾ä¸å
æ¬å½æ°ï¼å¤æè§å¾å
å«äºåç»å½æ°çéå çå
容ï¼è¿æ¥è§å¾æ¯åºäºå¤ä¸ªè¡¨çã
ããè§å¾çä¼å£ç¹ï¼
ããå¦æéè¦ç»å¸¸æ§è¡æ项å¤ææ¥è¯¢ï¼å¯ä»¥åºäºè¿ä¸ªå¤ææ¥è¯¢å»ºç«è§å¾ï¼æ¤åæ¥è¯¢æ¤è§å¾å³å¯ï¼ç®åå¤ææ¥è¯¢ï¼è§å¾æ¬è´¨ä¸å°±æ¯ä¸æ¡SELECTè¯å¥ï¼æ以å½è®¿é®è§å¾æ¶ï¼åªè½è®¿é®å°æ对åºçSELECTè¯å¥ä¸æ¶åå°çåï¼å¯¹åºè¡¨ä¸çå
¶å®åèµ·å°å®å
¨åä¿å¯çä½ç¨ï¼å¯ä»¥éå¶æ°æ®è®¿é®ã
ããåæ¶å 为è§å¾æ¬è´¨æ¯åºè¡¨çæ°æ®ï¼æ以对è§å¾çæä½ä¼å½±åå°åºè¡¨ä»èä¸å®å
¨ãè¿éå¯ä»¥ä½¿ç¨WITH READ ONLYæ¥éå¶å¯¹è§å¾çDMLè¯è¨ã
ããCREATE OR REPLACE VIEW v_emp_10
ããAS
ããSELECT empno, ename, sal, deptno FROM empWHERE deptno = 10
ããWITH READ ONLY;
ãã使ç¨DCLè¯å¥å¯ä»¥ææç¨æ·å建è§å¾çæéï¼
ããGRANT CREATE VIEW TO tarena;
ããå建ç®åè§å¾
ããCREATE VIEW v_emp_10
ããAS
ããSELECT empno, ename, sal, deptno
ããFROM emp
ããWHERE deptno = 10;
ããæ¥è¯¢è§å¾
ããSELECT id, name, salary FROM v_emp_10;
ãã对è§å¾çDMLæä½
ããç®åè§å¾å¯ä»¥è¿è¡DMLæä½ï¼ä½æ¯æä½ç对象å¿
é¡»æ¯åºè¡¨éè§å¾å
å«çå段ï¼å³å¯¹è§å¾å¯è§ãèä¸ç®åè§å¾çDMLæä½ä¼å¯¹å½±ååºè¡¨æ°æ®ã
ããWITH CHECK OPTIONçè¯è¡¨ç¤ºï¼éè¿è§å¾æåçä¿®æ¹ï¼å¿
é¡»å¨è§å¾çå¯è§èå´å
ï¼æ 论æ¯INSERT UPDATE DELETEæä½é½å¿
é¡»å¨è§å¾èå´å
ï¼è¶
è¿è§å¾èå´ä¸å¯ç¨ã
ããCREATE [OR REPLACE] VIEW view_name[(alias[, aliasâ¦])]
ããAS subquery
ãã[WITH CHECK OPTION];
ããå é¤è§å¾
ããDROP VIEW v_emp_10;
ãã对è§å¾çå é¤ä¸ä¼å¯¼è´åºè¡¨æ°æ®ç丢失ï¼ä¸ä¼å½±ååºè¡¨æ°æ®ã
ããåºå
ããåºå(SEQUENCE)æ¯ä¸ç§ç¨æ¥çæå¯ä¸æ°åå¼çæ°æ®åºå¯¹è±¡ãåºåçå¼ç±Oracleç¨åºæéå¢æéå顺åºèªå¨çæï¼é常ç¨æ¥èªå¨äº§ç表ç主é®å¼ï¼æ¯ä¸ç§é«æçè·å¾å¯ä¸é®å¼çéå¾ã
ããåºåæ¯ç¬ç«çæ°æ®åºå¯¹è±¡ï¼å表æ¯ç¬ç«ç对象ï¼åºå并ä¸ä¾éäºè¡¨ã
ããé常æ
åµä¸ï¼ä¸ä¸ªåºå为ä¸ä¸ªè¡¨æä¾ä¸»é®å¼ï¼ä½ä¸ä¸ªåºåä¹å¯ä»¥ä¸ºå¤ä¸ªè¡¨æä¾ä¸»é®å¼ã
ããCREATE SEQUENCE [schema.]sequence_name
ãã[ START WITH i ] [ INCREMENT BY j ]
ãã[ MAXVALUE m | NOMAXVALUE ]
ãã[ MINVALUE n | NOMINVALUE ]
ãã[ CYCLE | NOCYCLE ][ CACHE p | NOCACHE ]
ããå建ä¸ä¸ªåºåï¼èµ·å§æ°æ®æ¯100ï¼æ¥è¿æ¯10ï¼
ããCREATE SEQUENCE emp_seq
ããSTART WITH 100
ããINCREMENT BY 10;
ããNEXTVALï¼è·ååºåçä¸ä¸ªå¼
ããCURRVALï¼è·ååºåçå½åå¼
ããå½åºåå建以åï¼å¿
é¡»å
æ§è¡ä¸æ¬¡NEXTVALï¼ä¹åæè½ä½¿ç¨CURRVALã
ããè·ååºåç第ä¸ä¸ªå¼ï¼å¹¶ä¸ä½¿ç¨åºåå¼ä¸ºEMP表æå
¥æ°çè®°å½ï¼
ããå é¤åºå
ããDROP SEQUENCE emp_seq;
ããç´¢å¼
ããå建索å¼
ããCREATE [UNIQUE] INDEX index_name
ããON table(column[, columnâ¦]);
ããindex_name表示索å¼å称
ããtable表示表å
ããcolumn表示ååï¼å¯ä»¥å»ºç«ååç´¢å¼æå¤åç´¢å¼UNIQUE表示å¯ä¸ç´¢å¼
ããCREATE INDEX idx_emp_job_sal ON emp(job, sal);å¯ä»¥å¢å å½æ°
ããCREATE INDEX emp_ename_upper_idx
ããON emp(UPPER(ename));
ããé建索å¼ï¼æé«ç´¢å¼ç©ºé´å©ç¨ç
ããALTER INDEX index_name REBUILD;
ããå é¤ç´¢å¼
ããDROP INDEX idx_emp_ename;
ãã为æåæ¥è¯¢æçï¼å建å使ç¨ç´¢å¼çååï¼
ãã1.为ç»å¸¸åºç°å¨WHEREåå¥ä¸çåå建索å¼
ãã2.为ç»å¸¸åºç°å¨ORDER BYãDISTINCTåé¢çå段建ç«ç´¢å¼ãå¦æ建ç«çæ¯å¤åç´¢å¼ï¼ç´¢å¼çå段顺åºè¦åè¿äºå
³é®ååé¢çå段顺åºä¸è´3.为ç»å¸¸ä½ä¸ºè¡¨çè¿æ¥æ¡ä»¶çåä¸å建索å¼
ãã4.ä¸è¦å¨ç»å¸¸åDMLæä½ç表ä¸å»ºç«ç´¢å¼
ãã5.ä¸è¦å¨å°è¡¨ä¸å»ºç«ç´¢å¼
ãã6.éå¶è¡¨ä¸çç´¢å¼æ°ç®ï¼ç´¢å¼å¹¶ä¸æ¯è¶å¤è¶å¥½
ãã7.å é¤å¾å°è¢«ä½¿ç¨çãä¸åççç´¢å¼
ãã约æ
ããCONSTRAINT
ããé空约æ(Not Null)ï¼ç®ç§°NN
ããå建表æ¶æ·»å 约æ
ããCONSTRAINT employees_hiredate_nn NOT NULLä¿®æ¹è¡¨æ¶å¢å é空约æ
ããALTER TABLE employees
ããMODIFY (eid NUMBER(6) NOT NULL);
ããä¿®æ¹è¡¨æ¶åæ¶é空约æ
ããALTER TABLE employees
ããMODIFY (eid NUMBER(6));
ããå¯ä¸æ§çº¦æ(Unique)ï¼ç®ç§°UK
ãã
温馨提示:答案为网友推荐,仅供参考