1、1 SQL2scott2.1 emp12EMPNo. 1 EMPNO NUMBER(4)2 ENAME VARCHAR2(10) 3 JOB VARCHAR2(9) 4 MGR NUMBER(4)5 HIREDATE DATE 6 SAL NUMBER(7,2)7 COMM NUMBER(7,2)8 DEPTNO NUMBER(2)2.2 deptdeptNo. 1 DEPTNO NUMBER(2)2 DNAME VARCHAR2(14) 3 LOC VARCHAR2(13) 2.3 SALGRADESALGRADENo. 1 GRADE NUMBER2 LOSAL NUMBER3 HISAL
2、 NUMBER2.4 BONUS3BONUSNo. 1 ENAME VARCHAR2(10) 2 JOB VARCHAR2(9) 3 SAL NUMBER4 COMM NUMBER33.11 Oracle2 sqlplusw3.212 DISTINCT345 Oracle4SQL SQL4.1 SQLSQL Structured Query Language SQLANSI SQLSQLDML Data Manipulation LanguageDDL Data Definition LanguageDCL Data Control Language4.2SELECT * | FROM ; S
3、ELECT * FROM emp ; SELECT empno,ename,job FROM emp ; 4SELECT empno , ename , job FROM emp ; jobSELECT job FROM emp ; 5DISTINCTSELECT DISTINCT * | FROM ; SELECT DISTINCT empno,job FROM emp ; 7369 SMITH CLERK6Oracle |SELECT | empno | | ename | | job FROM emp ; SELECT ename , sal * 12 FROM emp ; sal*12
4、SELECT ename , sal * 12 income FROM emp ; + - * /74.3 WHERESELECT DISTINCT * | FROMWHERE (s)1500sal1500 SELECT * FROM emp WHERE sal1500 ; commnullIS NOT NULL SELECT * FROM emp WHERE comm IS NOT NULL ; 8comm null IS NULL SELECT * FROM emp WHERE comm IS NULL ; 1500ANDSELECT * FROM emp WHERE sal1500 AN
5、D comm IS NOT NULL ; 1500ORSELECT * FROM emp WHERE sal1500 OR comm IS NOT NULL ; 9NOT1500SELECT * FROM emp WHERE NOT (sal1500 AND comm IS NOT NULL) ; 1500 3000SELECT * FROM emp WHERE sal1500 AND sal 1500 sal =1500 AND sal = = !=73697369 ; !=SELECT * FROM emp WHERE empno!=7369 ; 164.4 ORDER BYSQL ORD
6、ER BYSELECT * FROM emp ; ORDER BYSELECT DISTINCT * | FROMWHERE (s)ORDER BY 1, 2 ASC|DESC ASC DESCSELECT * FROM emp ORDER BY sal ; 17SELECT * FROM emp ORDER BY sal ASC ; DESCSELECT * FROM emp ORDER BY sal DESC ; 1018SELECT * FROM emp WHERE deptno=10 ORDER BY sal DESC,hiredate ASC ; SQL4.5function_nam
7、e(column|expression,arg1,arg2,) function_namecolumnexpressionarg1 arg2NVL DECODE4.5.119SELECT UPPER(smith) FROM DUAL ; upperSELECT * FROM emp WHERE ename=UPPER(Smith) ; lower()SELECT LOWER(HELLO WORLD) FROM dual ; initcap()SELECT INITCAP(HELLO WORLD) FROM dual ; 20SELECT INITCAP(ename) FROM emp ; |
8、CONCAT()SELECT CONCAT(hello ,world) FROM DUAL ; 21|substr()length()replace()SELECT substr(hello,1,3) , length(hello) , replace(hello,l,x) FROM DUAL ; substr()Oracle substr() 0 1|- 0 1 Oracle2SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp ; substr()SELECT ename,SUBSTR(ename,-3,3) FROM emp ; 224.
9、5.2ROUND() TRUNC()MODSELECT ROUND(789.536) FROM dual ; ROUND()SELECT ROUND(789.536,2) FROM dual ; ROUND()SELECT ROUND(789.536,-2) FROM dual ; 23TRUNC() ROUND() TRUNC()TRUNC()SELECT TRUNC(789.536) FROM DUAL ; TRUNC()SELECT TRUNC(789.536,2) FROM DUAL ; SELECT TRUNC(789.536,-2) FROM DUAL ; 24MOD()SELEC
10、T MOD(10,3) FROM DUAL ; 4.5.3Oracle = + = = 10OraclesysdateSELECT SYSDATE FROM DUAL ; = / 7 = SELECT empno,ename,ROUND(SYSDATE-hiredate)/7) FROM emp ; 25OracleMONTHS_BETWEEN()ADD_MONTHS()NEXT_DAY()LAST_DAY()MONTHS_BETWEEN()SELECT empno,ename,MONTHS_BETWEEN(sysdate,hiredate) FROM emp ; ROUND()26ADD_M
11、ONTHS()SELECT ADD_MONTHS(SYSDATE,4) FROM DUAL ; NEXT_DAY()SELECT NEXT_DAY(SYSDATE, ) FROM DUAL ; LAST_DAY()SELECT LAST_DAY(SYSDATE) FROM DUAL ; 274.5.4TO_CHAR()TO_NUMBER()TO_DATE()SELECT empno,ename,hiredate FROM emp ; TO_CHAR()y yyyym mmd ddSELECT empno,ename,TO_CHAR(hiredate,yyyy) year , TO_CHAR(h
12、iredate,mm) months , TO_CHAR(hiredate,dd) day FROM emp ; to_char()Oracle 19-4 -87 1987-04-19 SELECT empno,ename,TO_CHAR(hiredate,yyyy-mm-dd) FROM emp ; 285 05 0 00 fm 0to_char()SELECT empno,ename,sal FROM emp ; , to_char()9SELECT empno,ename,TO_CHAR(sal,99,999) FROM emp ; 29$L LocalSELECT empno,ename,TO_CHAR(sal,$99,999) FROM emp ; TO_NUMBERSELECT TO_NUMBER(123) + TO_NUMBER(123) FROM DUAL ; TO_DATE() DATE2009-2-16 DATESELECT TO_DATE(2009-02-16,yyyy-mm-dd) FROM DUAL ; 30
