Oracle 基础 语句大全

-- 查询(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连接，忽略null
SELECT NULL || 'Hello' FROM DUAL;

-- 连接
SELECT ENAME || ' is ' || SAL AS INFO FROM EMP;

-- distinct
SELECT DISTINCT JOB FROM EMP;
SELECT DISTINCT JOB, SAL FROM EMP;

-- desc
DESC 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 and
SELECT * FROM EMP WHERE SAL BETWEEN 1000 AND 2000;

-- in (deptno = 10 or deptno =20)
SELECT * FROM EMP WHERE DEPTNO IN (10, 20);

-- like
SELECT *
  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';

-- concat
SELECT 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;

-- length
SELECT 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 /rpad
SELECT LPAD('abcd', 10, '*') FROM DUAL;
SELECT LPAD('abcd', 2, '*') FROM DUAL;

-- trim
SELECT TRIM('  abc  123 ') FROM DUAL;
SELECT TRIM('a' FROM 'aabbcc123aa') FROM DUAL;

-- round
SELECT 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;

--练习

--1

SELECT D.DNAME, AVG(E.SAL)
  FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO
 GROUP BY D.DNAME
HAVING AVG(E.SAL) > 2000;

--2

SELECT JOB, AVG(E.SAL)
  FROM EMP E
 WHERE E.JOB NOT LIKE 'SA\_%' ESCAPE '\'
 GROUP BY E.JOB
HAVING 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;

-------------

--update

UPDATE DEPT SET DNAME = 'AA', LOC = 'BEIJING' WHERE DEPTNO >= 80;

UPDATE DEPT SET DEPTNO = 90 WHERE DEPTNO = 81;

--delete

DELETE DEPT WHERE DEPTNO = 90;

DELETE * FROM DEPT WHERE DEPTNO = 80; --错误

--ROWID

SELECT 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 DEPT
VALUES
  ('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 table

CREATE 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);

--check

CONSTRAINT table3_age_fk CHECK(age >= 0);


--视图

CREATE OR REPLACE VIEW dept_view
AS
SELECT deptno,dname FROM dept;

SELECT * FROM dept_view;

INSERT INTO dept_view VALUES('92','ff');

--不允许DML操作的视图

CREATE OR REPLACE  VIEW emp_vu
AS
SELECT 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_vu2
AS
SELECT deptno,dname FROM dept WHERE  dname = 'AA'
WITH CHECK OPTION 

--操作的数据必须符合where条件

INSERT INTO dept_vu2 VALUES(93,'AA')


CREATE OR REPLACE VIEW dept_vu3
AS
SELECT deptno,dname FROM dept
WITH 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 and

SELECT * FROM dept WHERE ROWNUM >=6 AND ROWNUM <=10

--from where select order by
SELECT * FROM 
(
SELECT e.*,ROWNUM r FROM
(SELECT * FROM dept ORDER BY deptno) e 
WHERE ROWNUM <=8 
)WHERE r>=5

--创建序列

CREATE SEQUENCE dept2_seq
INCREMENT BY 1
START WITH 100

SELECT dept2_seq.nextval FROM dual;

--由序列生成主键

INSERT INTO dept2 VALUES(dept2_seq.nextval,'aa','bb');
  
DROP SEQUENCE dept2_seq