Oracle 常用SQL

1.创建表

CREATE TABLE emp_test (ID NUMBER(5,2) PRIMARY KEY, NAME VARCHAR2(20) UNIQUE, BIRTHDAY DATE);

--修改表名

ALTER TABLE emp_test RENAME TO TEST1；

--修改表列名

Alter TABLE emp_test RENAME COLUMN NAME TO NAME1；

--修改字段类型

ALTER TABLE emp_test MODIFY NAME1 NUMBER(20);

--添加表列

ALTER TABLE emp_test ADD ADDRESS VARCHAR2(40) ;

--删除表列

ALTER TABLE emp_test DROP NAME CASCADECONSTRAINTS ;

--删除表

DROP TABLE emp_test ；

--删除表中数据，保留表结构

TRUNCATE TABLE TableName;

2.select

SELECT * FROM DBA_TABLES; --数据库所有表，DBA权限
SELECT * FROM USER_TABLES;--自己创建的所有表
SELECT * FROM ALL_TABLES;--自己可以用的表?
SELECT * FROM DBA_TABLESPACES;--表空间，DBA权限
SELECT * FROM DBA_DATA_FILES;--表空间物理文件，DBA
SELECT * FROM DBA_USERS; --用户所属表空间，DBA权限

SELECT A.ENAME NAME
      ,A.JOB   JOB
   ,CASE WHEN A.SAL <=800 THEN 'L'
         WHEN A.SAL BETWEEN 801 AND 2999 THEN 'M'
    ELSE 'H'
    END SAL_LEVEL
FROM EMP A;

SELECT SYSDATE FROM DUAL;

--rowid是物理结构上的，在每条记录insert到数据库中时，都会有一个唯一的物理记录，
SELECT ROWID,A.* FROM EMP A;

--rownum是根据sql查询出的结果给每行分配一个逻辑编号，所以你的sql不同也就会导致最终rownum不同
SELECT ROWNUM,A.* FROM EMP A ORDER BY A.SAL DESC;

SELECT * FROM EMP WHERE DEPTNO='20' AND ROWNUM=1;

--如果A.JOB ='MANAGER' ,返回good， if = 'Clerk' ,返回 NOR ，else 返回bad
SELECT A.ENAME,DECODE(A.JOB,'MANAGER','GOOD','CLERK','NOR','BAD') FROM EMP A;

--如有有奖金，返回奖金值，如果没有，返回0
SELECT A.ENAME,A.SAL,NVL(A.COMM,0) FROM EMP A; --EXISTS

SELECT NVL(ID,0) FROM CLX_TEST WHERE NAME =''; --NOT EXISTS

SELECT NVL(ID,0) FROM CLX_TEST WHERE NAME IS NULL; --EXISTS

SELECT length(NAME) FROM CLX_TEST WHERE ID=322.

SELECT TRIM(TO_CHAR(12345.89,'999,999,999,99.99')) FROM DUAL;

SELECT JOB
,SUM(SAL)
FROM EMP
WHERE JOB <> 'CLERK'
GROUP BY JOB
HAVING SUM(SAL) > 5500;

//按照部门分组后，按照sal排序，第一列显示排序后的顺序编号

SELECT ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL),
       ENAME,
       SAL,
       DEPTNO
FROM EMP;

--按照部门分组后每个部门的工资排名

select rank() over(partition by deptno order by sal desc) rank_number,t.* from EMP t ;

--用法一致，与上面sql 不同的是，序号增加的方式不同

SELECT DENSE_RANK() OVER(partition by deptno ORDER BY SAL),
       ENAME,
       SAL,
    DEPTNO
FROM EMP;

--ROLLUP，是GROUP BY子句的一种扩展，可以为每个分组返回小计记录以及为所有分组返回总计记录。

SELECT ENAME,
SUM(SAL),
DEPTNO
FROM EMP
GROUP BY ROLLUP(DEPTNO,ENAME)
ORDER BY DEPTNO;

2.insert

INSERT INTO emp_test VALUES (123.45,NULL,SYSDATE);

3.update

UPDATE emp_test SET name = '200' WHERE ID = 1;
4.delete

DELETE FROM emp_test WHERE ID = 1;

5.创建序列

create sequence TB_CODE_SEQUENCE ---创建一个名为TB_CODE_SEQUENCE的序列。
minvalue 1 ---最小值为1
maxvalue ---最大值，可对maxvalue设置任意正整数值。当不设任何值时，默认为999999999999999999999999999
start with 11---从第11条记录开始，当表中没有记录，则从1开始
increment by 1---增量值为1，每次自增1
cache 10 ---CACHE(缓冲)定义存放序列的内存块的大小，默认为20。