Oracle 基础 语句大全
来源：互联网 发布：微谱数据库免费入口 编辑：程序博客网 时间：2024/06/05 19:24
-- 查询(dual虚表)-- 直接量SELECT 100 FROM DUAL;SELECT 'hello, world' FROM DUAL;SELECT * FROM DUAL;SELECT * FROM DEPT;-- 算术运算SELECT * FROM EMP;SELECT 12 * SAL FROM EMP;SELECT EMPNO, HIREDATE + 100 FROM EMP;-- null参与运算，结果一定为null-- 别名出现空格，区分大小写，特殊字符，需要““SELECT EMPNO, ENAME, 14 * (SAL + NVL(COMM, 0)) AS "nian xin" FROM EMP;-- null连接，忽略nullSELECT NULL || 'Hello' FROM DUAL;-- 连接SELECT ENAME || ' is ' || SAL AS INFO FROM EMP;-- distinctSELECT DISTINCT JOB FROM EMP;SELECT DISTINCT JOB, SAL FROM EMP;-- descDESC EMP;-- 练习SELECT EMPNO, ENAME, 12 * (SAL + 100) FROM EMP;SELECT '姓名' || ENAME FROM EMP;-- 比较运算SELECT * FROM EMP WHERE SAL >= 1000;SELECT * FROM EMP WHERE SAL <> 1000;SELECT * FROM EMP WHERE SAL != 1000;-- 字符类型区分大小写SELECT * FROM EMP WHERE ENAME = 'smith';-- 日期类型格式敏感SELECT * FROM EMP WHERE HIREDATE >= '1-1月-81' AND HIREDATE <= '31-12月-81';-- between andSELECT * FROM EMP WHERE SAL BETWEEN 1000 AND 2000;-- in (deptno = 10 or deptno =20)SELECT * FROM EMP WHERE DEPTNO IN (10, 20);-- likeSELECT * FROM EMP WHERE ENAME LIKE 'a%' OR ENAME LIKE 'A%';SELECT * FROM EMP WHERE ENAME LIKE '%王%';SELECT * FROM EMP WHERE ENAME LIKE '_A%';SELECT * FROM EMP WHERE ENAME LIKE '\_%' ESCAPE '\';-- null(参与比较运算，结果false)SELECT * FROM EMP WHERE COMM = NULL;SELECT * FROM EMP WHERE COMM <> NULL;SELECT * FROM EMP WHERE COMM IS NULL;SELECT * FROM EMP WHERE COMM IS NOT NULL;-- not(sal < 1000 or sal > 2000)
SELECT * FROM EMP WHERE SAL NOT BETWEEN 1000 AND 2000;-- order by ( from / where / select / order by )SELECT * FROM EMP ORDER BY SAL DESC;SELECT * FROM EMP ORDER BY COMM;SELECT * FROM EMP ORDER BY HIREDATE;SELECT * FROM EMP ORDER BY ENAME;SELECT * FROM EMP ORDER BY JOB, SAL DESC;SELECT ENAME FROM EMP ORDER BY SAL;SELECT ENAME, SAL FROM EMP ORDER BY 1;SELECT ENAME N FROM EMP ORDER BY N;-- 练习SELECT * FROM EMP WHERE ENAME = 'Chen';SELECT * FROM EMP WHERE HIREDATE > '9-7月-97' AND JOB <> 'IT_PROG';SELECT * FROM EMP WHERE ENAME LIKE '__a%';SELECT * FROM EMP WHERE DEPTNO = 20 ORDER BY SAL DESC, ENAME ASC;SELECT * FROM EMP WHERE MGR IS NULL;-- 字符串函数SELECT UPPER('abcd123') FROM DUAL;SELECT * FROM EMP WHERE LOWER(JOB) = 'salesman';-- concatSELECT CONCAT('a', '123') FROM DUAL;-- substr(第一个参数: 字符串; 第二参数: 起始位置; 第三个参数: 长度)-- 起始位置：从1开始SELECT SUBSTR('汉子abcdef', 2) FROM DUAL;SELECT SUBSTR('abcdef', -2) FROM DUAL;SELECT SUBSTR('汉子abcdef', 2, 2) FROM DUAL;-- lengthSELECT LENGTH('abc在') FROM DUAL;SELECT EMPNO, ENAME, LENGTH(ENAME) FROM EMP ORDER BY LENGTH(ENAME) DESC;-- instr( 子串开始的位置, indexOf())SELECT INSTR('abcdef', 'bc') FROM DUAL;SELECT INSTR('abcdef', 'bc', 3) FROM DUAL; -- 3代表从第3个字符查找SELECT INSTR('abcdef', 'bc', 3, 1) FROM DUAL; -- 1代表第一次出现的位置SELECT * FROM EMP WHERE INSTR(HIREDATE, '81') > 0;-- lpad /rpadSELECT LPAD('abcd', 10, '*') FROM DUAL;SELECT LPAD('abcd', 2, '*') FROM DUAL;-- trimSELECT TRIM(' abc 123 ') FROM DUAL;SELECT TRIM('a' FROM 'aabbcc123aa') FROM DUAL;-- roundSELECT ROUND(1.2345, 3) FROM DUAL;SELECT ROUND(1.2345) FROM DUAL;SELECT ROUND(1.2345, -1) FROM DUAL;-- trunc(截断)-- mod (%)SELECT MOD(-5, -3) FROM DUAL;SELECT EMPNO, ENAME, ROUND(SAL / 22, 2) FROM EMP;-- 日期SELECT SYSDATE FROM DUAL;SELECT EMPNO, ENAME, ROUND((SYSDATE - HIREDATE) / 365, 1) FROM EMP;SELECT EMPNO, ENAME, MONTHS_BETWEEN(SYSDATE, HIREDATE) / 12 FROM EMP;SELECT ADD_MONTHS(SYSDATE, 6) FROM DUAL;SELECT NEXT_DAY(SYSDATE, '星期一') FROM DUAL;SELECT NEXT_DAY(SYSDATE, 2) FROM DUAL;-- 当前月份的第一天SELECT ADD_MONTHS(LAST_DAY(SYSDATE) + 1, -1) FROM DUAL;-- 拆分日期类型SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;SELECT * FROM EMP WHERE EXTRACT(YEAR FROM HIREDATE) = '1981';-- 转换SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;SELECT TO_DATE('2013-1-1', 'yyyy-mm-dd') FROM DUAL;SELECT EMPNO, ENAME, TO_CHAR(HIREDATE, 'yyyy-mm-dd hh24:mi:ss') FROM EMP;-- 通用SELECT NVL(NULL, 'a') FROM DUAL;SELECT NVL2(NULL, 'a', 'b') FROM DUAL;SELECT EMPNO, ENAME, 12 * (SAL + NVL2(COMM, COMM, 0)) 年薪 FROM EMP;
-- 返回第一个不为null的表达式SELECT COALESCE(NULL, 'a', 'b') FROM DUAL;SELECT EMPNO, ENAME, JOB, SAL, CASE JOB WHEN 'SALESMAN' THEN SAL * 1.2 WHEN 'MANAGER' THEN SAL * 1.1 ELSE SAL END NEWSAL FROM EMP;SELECT EMPNO, ENAME, JOB, SAL, CASE WHEN JOB = 'SALESMAN' THEN SAL * 1.2 WHEN JOB = 'MANAGER' THEN SAL * 1.1 ELSE SAL END NEWSAL FROM EMP;SELECT EMPNO, ENAME, JOB, SAL, DECODE(JOB, 'SALESMAN', SAL * 1.2, 'MANAGER', SAL * 1.1, SAL) FROM EMP;--练习题SELECT ((EXTRACT(YEAR FROM SYSDATE) - 2000) * 12 + EXTRACT(MONTH FROM SYSDATE) - 1) AS D FROM DUAL;SELECT ROUND((SYSDATE - TO_DATE('2000-1-1', 'yyyy-mm-dd')) / 7) FROM DUAL;SELECT ((SYSDATE - TO_DATE('2000-1-1', 'yyyy-mm-dd'))) FROM DUAL;--多表查询SELECT * FROM EMP;SELECT * FROM DEPT;--笛卡尔积的形式SELECT * FROM EMP, DEPT; -- 14 * 4--等值连接SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;SELECT * FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;SELECT E.*, D.DNAME, D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;-- 不等值连接SELECT * FROM SALGRADE;SELECT E.EMPNO, E.ENAME, E.SAL, G.GRADE FROM EMP E, SALGRADE G WHERE E.SAL BETWEEN G.LOSAL AND G.HISAL;--外连接SELECT E.*, D.* FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO(+);SELECT * FROM EMP E, DEPT D WHERE E.DEPTNO(+) = D.DEPTNO;--SELECT DEPTNO, COUNT(*) FROM EMP WHERE DEPTNO IS NOT NULL GROUP BY DEPTNO;SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO HAVING DEPTNO IS NOT NULL;--练习--1SELECT D.DNAME, AVG(E.SAL) FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO GROUP BY D.DNAMEHAVING AVG(E.SAL) > 2000;--2SELECT JOB, AVG(E.SAL) FROM EMP E WHERE E.JOB NOT LIKE 'SA\_%' ESCAPE '\' GROUP BY E.JOBHAVING AVG(E.SAL) > 0 ORDER BY AVG(E.SAL) DESC --3 SELECT D.DNAME, MIN(SAL), MAX(SAL) FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO GROUP BY D.DNAME HAVING COUNT(E.EMPNO) >= 4;--子查询( 在 where 语句中不允许出现分组函数 )--求哪一个员工工资最低？SELECT * FROM EMP WHERE SAL = (SELECT MIN(SAL) FROM EMP);SELECT * FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE SAL = (SELECT MIN(SAL) FROM EMP));--每个部门的最低工资?(多行，多列)SELECT * FROM EMP WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MIN(SAL) FROM EMP GROUP BY DEPTNO HAVING DEPTNO IS NOT NULL);--in. any. all--查询所有是经理的员工信息SELECT * FROM EMP WHERE EMPNO IN (SELECT MGR FROM EMP)-- >any : 大于最小值 ; <any : 小于最大值 SELECT * FROM EMP WHERE SAL < ANY (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);SELECT * FROM EMP WHERE SAL > ANY (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);SELECT * FROM EMP WHERE SAL = ANY (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);-- > all : 大于最大值 < all: 小于最小值 = all : 同时等于SELECT * FROM EMP WHERE SAL < ALL (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);SELECT * FROM EMP WHERE SAL > ALL (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);SELECT * FROM EMP WHERE SAL = ALL (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);--特殊情况(子查询中不返回结果，> all, = all, < all 会查询出所有的数据)SELECT * FROM EMP WHERE EMPNO IN (SELECT 'a' FROM DUAL WHERE 1 = 2) SELECT * FROM EMP WHERE SAL < ANY (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO HAVING MIN(SAL) < 0);SELECT * FROM EMP WHERE SAL < ALL (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO HAVING MIN(SAL) < 0);SELECT * FROM EMP WHERE SAL = ALL (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO HAVING MIN(SAL) < 0);SELECT * FROM EMP WHERE SAL > ALL (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO HAVING MIN(SAL) < 0);--^ 默认查询所有的数据--单行多列--查询与SMITH 相同 job，deptno 的同事信息SELECT * FROM EMP WHERE (JOB) = (SELECT JOB FROM EMP WHERE ENAME = 'SMITH') AND DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH') AND ENAME <> 'SMITH';--多行多列--每个job最高工资的员工信息SELECT * FROM EMP WHERE (JOB, SAL) IN (SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB)--练习--1 SELECT EMPNO, ENAME, SAL FROM EMP WHERE (SAL, JOB) IN (SELECT SAL, JOB FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE EMPNO = 113) AND JOB = (SELECT JOB FROM EMP WHERE EMPNO = 102)) --2 SELECT ENAME, SAL FROM EMP WHERE SAL >= ALL (SELECT SAL FROM EMP) --3 ---wrong-SELECT d.deptno,d.dname,MIN(sal) FROM emp e,dept d WHERE sal > (SELECT MIN(sal) FROM emp WHERE deptno = 20) GROUP BY d.deptno,d.dname --4 SELECT EMPNO, ENAME, SAL FROM EMP WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MIN(SAL) FROM EMP GROUP BY DEPTNO HAVING DEPTNO IS NOT NULL) --5 SELECT ENAME, SAL FROM EMP WHERE MGR = (SELECT EMPNO FROM EMP WHERE ENAME = 'KING') --6 SELECT ENAME, SAL, HIREDATE FROM EMP WHERE HIREDATE > (SELECT HIREDATE FROM EMP WHERE ENAME = 'JONES') --insert SELECT * FROM DEPT;INSERT INTO DEPT VALUES (80, NULL, '');SELECT * FROM DEPT WHERE LOC IS NULL;INSERT INTO DEPT (DEPTNO) VALUES (81);INSERT INTO DEPT VALUES (82, 'HR', 'DALIAN');-------------INSERT INTO DEPT2 VALUES (83, '1-1月-1992');INSERT INTO DEPT2 VALUES (83, TO_DATE('2013-2-1', 'yyyy-mm-dd'));INSERT INTO DEPT2 SELECT DEPTNO, BIRTH FROM DEPT;---------------updateUPDATE DEPT SET DNAME = 'AA', LOC = 'BEIJING' WHERE DEPTNO >= 80;UPDATE DEPT SET DEPTNO = 90 WHERE DEPTNO = 81;--deleteDELETE DEPT WHERE DEPTNO = 90;DELETE * FROM DEPT WHERE DEPTNO = 80; --错误--ROWIDSELECT DEPT.*, ROWID FROM DEPT;--事务操作INSERT INTO DEPT (DEPTNO) VALUES (90);SAVEPOINT A;INSERT INTO DEPT (DEPTNO) VALUES (91);SAVEPOINT B;INSERT INTO DEPT (DEPTNO) VALUES (92);ROLLBACK TO B;COMMIT;-- 自动加锁UPDATE DEPT SET LOC = 'dalian' WHERE DEPTNO = 90;-- 手动加锁（行）SELECT * FROM DEPT FOR UPDATE;INSERT INTO DEPTVALUES ('50', '人力资源', '大连')SAVEPOINT A;SELECT * FROM DEPT;DELETE FROM DEPT WHERE DEPTNO > 89 SAVEPOINT B;SELECT * FROM DEPT;UPDATE DEPT SET DEPTNO = 99 WHERE DEPTNO = 80 ROLLBACK TO B;COMMIT;SELECT * FROM DEPT;--create tableCREATE TABLE table1(ID NUMBER(6) PRIMARY KEY,NAME VARCHAR2(50),PASSWORD VARCHAR2(32),age NUMBER(3) DEFAULT 0,birth DATE)DROP TABLE table1;SELECT * FROM scott.emp;ALTER TABLE table1 DROP (PASSWORD);--约束--not null(列级约束)NAME VARCHAR2(50) NOT NULL,--表级约束--唯一约束CONSTRAINT table3_name_uk UNIQUE(NAME),--主键约束CONSTRAINT table3_id_pk PRIMARY KEY(ID),--外键约束CONSTRAINT table3_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno);--checkCONSTRAINT table3_age_fk CHECK(age >= 0);--视图CREATE OR REPLACE VIEW dept_viewASSELECT deptno,dname FROM dept;SELECT * FROM dept_view;INSERT INTO dept_view VALUES('92','ff');--不允许DML操作的视图CREATE OR REPLACE VIEW emp_vuASSELECT deptno,COUNT(*) AS cnt FROM emp GROUP BY deptno;SELECT * FROM emp_vu;INSERT INTO emp_vu VALUES(50,10);--带有约束的视图CREATE OR REPLACE VIEW dept_vu2ASSELECT deptno,dname FROM dept WHERE dname = 'AA'WITH CHECK OPTION --操作的数据必须符合where条件INSERT INTO dept_vu2 VALUES(93,'AA')CREATE OR REPLACE VIEW dept_vu3ASSELECT deptno,dname FROM deptWITH READ ONLY;--只读INSERT INTO dept_vu3 VALUES(94,'a');--删除视图DROP VIEW dept_vu3--分页SELECT ROWNUM ,dept.* FROM dept;SELECT ROWNUM ,dept.* FROM dept ORDER BY deptno;--rownum 只能使用<,<=,不能使用>,>=,中间数据的 between andSELECT * FROM dept WHERE ROWNUM >=6 AND ROWNUM <=10--from where select order bySELECT * FROM (SELECT e.*,ROWNUM r FROM(SELECT * FROM dept ORDER BY deptno) e WHERE ROWNUM <=8 )WHERE r>=5--创建序列CREATE SEQUENCE dept2_seqINCREMENT BY 1START WITH 100SELECT dept2_seq.nextval FROM dual;--由序列生成主键INSERT INTO dept2 VALUES(dept2_seq.nextval,'aa','bb'); DROP SEQUENCE dept2_seq
- Oracle 基础 语句大全
- Oracle基础语句大全
- SQL语句大全[基础]
- SQL 基础语句大全
- Oracle 语句大全
- Oracle常用语句大全
- Oracle 语句大全
- oracle查询语句大全
- Oracle常用语句大全
- oracle查询语句大全
- oracle 语句大全
- Oracle语句大全
- Oracle 语句大全
- oracle语句大全
- oracle 语句大全
- oracle 语句大全
- Oracle SQL语句大全
- oracle查询语句大全
- Android OTA 升级(五)：updater .
- hdoj 1863 畅通工程 最小生成树---prime算法
- java设计模式（一）：Singleton（单例模式）
- 自增运算a++和++b（1）
- 【rmzt】单车&&青春xp主题
- Oracle 基础 语句大全
- win7 远程桌面 连接ubuntu
- HDU-2095
- as3与javaweb结合开发属于个人的类YY的网站(一)
- hdu 2822 Dogs（优先队列）
- SEO者固步自封的势态越发严重是谁的错
- 人生若只如初见
- 无向图 双连通分量的模板
- 锁定表操作
