oracle数据库常用操作

一、oracle数据类型

NUMBER表示数据类型，经常被定义成NUMBER(P,S)的形式，其中：

-P表示数字的总位数

-S表示小数点后面的位数

如工资这个字段的定义：salary NUMBER(6,2)表示salary列中的数据，整数位最大为4位，小数位最大数为2位，也就是最大取值：9999.99

CHAR表示固定长度的字符类型，经常被定义成CHAR(N)的形式，N表示占用的字节数，可以不用指定长度，默认为1，最大长度是2000字节

如姓名这个字段的定义：name CHAR(10)表示name列中最多可存储10个字节的字符串，并且占用的空间是固定的10个字节

VARCHAR2 表示可变的字符类型，经常被定义成VARCHAR2(N)的形式，N表示最多可占用的字节数，最大长度是4000字节，必须指定N的大小

如工作这个字段的定义：job VARCAHR2(100);表示job这个列中最多可以存储100个字节的字符串，根据其保存的数据长度，占用的空间是变化的，最大占用空间为100个字节

CHAR和VARCAHR2默认单位是字节，可指定为字符如：

-CHAR(10),等价于CAHR(10 BYTE) 10个字节

-指定单位为字符：CHAR(10 CHAR) 20个字节

LONG和CLOB类型

LONG：VARCHAR2加长版，存储变长字符串，最多达2GB的字符串数据，LONG有很多限制：每个表只能有一个LONG类型列，不能作为主键，不能建立索引，不能出现在查询条件中。

CLOB：存储定长或边长字符串，最多达4GB的字符串数据，建议开发中使用CLOB替代LONG类型

DATE 表示日期时间的数据，长度为7个字节，默认格式是：DD-MM-RR如：11-ARP-71

二、表和列操作

1,创建employee表

CREATE TABLE EMPLOYEE(

ID NUMBER(4),

NAME VARCHAR2(20) NOT NULL,

GENDER CHAR(1) DEFAULT 'M',

BIRTH DATE,

SALARY NUMBER(6,2),

JOB VARCHAR2(30),

MANAGER NUMBER(4),

DEPTNO NUMBER(2)

);

1.1删除employee表

DROP TABLE EMPLOYEE;

2,修改表名(将employee表修改成myemp)

RENAME EMPLOYEE MYEMP;

3,显示表结构

DESC MYEMP;

4,增加列(给myemp表增加一列hiredate,并设置默认值为当期日期，列只能增加在最后，不能插入到现有列的中间)

ALTER TABLE MYEMP ADD(HIREDATE DATE DEFAULTSYSDATE);

5,修改myemp表中的job列的长度为40，并增加默认值得设置，默认值为'clerk'(建表后可以改变表中列的数据类型、长度和默认值，但这种修改仅对以后插入的数据有效，另外，如果表中已经有数据的情况下，把长度由大改小有可能修改不会成功，比如：原来的类型是varchar2(100),其中已经存放了100个字节长度的数据，如果要改为varchar2(80)，则不会修改成功)

ALTER TABLE MYEMP MODIFY(JOB VARCHAR2(40)DEFAULT 'CLERK');

6,删除列（删除myemp表中的hiredate列）

ALTER TABLE MYEMP DROP(HIREDATE);

7,插入数据

INSERT INTO MYEMP(ID,NAME,JOB,SALARY)VALUES(1001,’ROSE’,’PROGRAMMER’,5500);

8,插入日期格式的数据（oracle中的日期格式默认为’DD-MON-RR’使用TO_DATE函数转换为日期类型的数据）

INSERT INTO MYEMP(ID,NAME,JOB,BIRTH)VALUES(1003,’DONNA’,’MANAGER’,TO_DATE(‘1995-09-01’,’YYYY-MM-DD’));

9,更改表中ID为1003的员工工资和职位(如果没有where子句则全表的数据都会被修改)

UPDATE MYEMP SET SALARY=6500，JOB=’ANALYST’WHERE ID=1003;

10,删除员工数据删除表中名字为rose的员工(在(DDL)数据定义语句中的CRUNCATE语句，同样有删除表数据的作用，和delete语句的区别是：

-delete可以有条件删除，truncate将表数据全部删除，保留表结构

-delete是(DML)数据操作语句，可以回退，truncate是(DDL)语句，立即生效无法回退

-如果是删除全部表记录且数据量较大，delete语句效率比truncate语句低)

DELETE FROM MYEMP WHERE NAME=’ROSE’;

三、字符串操作函数

1，CONCAT和“||”连接字符串

返回两个字符串连接后的结果，两个参数char1,char2是要连接的两个字符串，等价操作：连接操作符：“||”如果CHAR1和CHAR2任何一个为NULL,相当于连接了一个空格

SELECT CONCAT(CONCAT(ename,’:’),salary)FROM MYEMP;

多个字符串连接，用||更直观

SELECT ename ||’:’|| salary FROM MYEMP;

2,LENGTH 返回字符串的长度

LENGTH(CHAR) 用于返回字符串的长度，如果字符串类型是VARCHAR2，返回字符的实际长度，如果字符类型是CHAR，长度还要包括后补的空格

SELECT ename,LENGTH(ename) FROM MYEMP;

3,UPPER,LOWER和INITCAP字符串大小写转换

大小写转换函数，UPPER(char)用于将字符转换为大写形式，LOWER(char)用于将字符串转换为小写形式，INITCAP(char)用于将字符串中每个单词的首字符大写，其他字符小写，单词之间用空格或非字符字符分隔，如果输入的参数是NULL值，仍然返回NULL值

SELECT UPPER(‘hello world’),LOWER(‘HELLOWORLD’),INITCAP(‘hello world’) FROM DUAL;(这里的DUAL是伪表)

4，TRIM、LTRIM、RTRIM截去字符串

作用：截去子串

语法形式：

-TRIM(c2 FROM c1) 从c1的两边截去c2;

-LTRIM(c1[,c2]) 从c1的左边截去c2;

-RTRIM(c1[,c2]) 从c1的右边截去c2;

如果没有c2，就去出空格

TRIM经常用来去除字符串前后的空格

SELECT TRIM(‘E’ from ‘elite’) AS “t1”,LTRIM(‘elite’,’e’)AS “t2”,RTRIM (‘e’,”elite”) AS “t3” FROM DUAL;

5，LPAD、RPAD补位函数

补位函数，用于在字符串char1的左端或右端用char2补足到n位，char2可重复提交多次

-LPAD(char1,n,char2) 左补位函数

-RPAD(char1,n,char2) 右补位函数

在MYEMP表中使用做补位，将sal用$补齐6位

SELECT ENAME,LPAD(sal,6,’$’) AS “salary”FROM MYEMP;

6，SUBSTR获取字符串的子串

SUBSTR(cahr,[m[,n]])用于获取字符串的子串，返回char中从m位开始取n个字符，如果m=0,则从首字符开始，如果m取负值，则从尾部开始，如果没有设置n，如果n的长度超过了char的长度，则取到字符串末尾为止,字符串的首位计数从1开始

SELECT SUBSTR (‘DOCTOR WHO TRACELS INTARDIS’,8，25) FROM DUAL;

7，INSTR(char1,char2[,n[,m]]):返回子串char2在字符串char1中的位置

参数：-从n的位置开始搜索，没有指定n，从第1个字符开始搜索

-m用于指定子串的第m次出现次数，如果不指定取值1

-如果在char1中没有找到子串char2,返回0，下面的AS是通过使用列的别名改变标题的显示样式，或者表示计算结果的含义，AS可加可不加，如果需要别名中间区分大小写字符，或者别名中包含字符或空格，则必须用双引号引起来

SELECT INSTR(‘DOCTOR WHO’,’WHO’) AS “WORDS”FROM DUAL;

8，TO_DATE:将字符串按照指定格式转换成日期类型

如：查询2002年以后入职的员工

SELECT ename,hiredate FROM MYEMP WHEREHIREDATE > TO_DATE(‘2002-01-01’,’YYYY-MM-DD’);

9，TO_CHAR:将其它类型的数据按指定格式转换成字符串类型

如：查询指定格式的入职时间

SELECT ENAME TO_CHAR(HIREDATE,’YYYY”年”MM”月”DD”日”) FROMMYEMPY;

10，NVL(expr1,expr2):将NULL转变为非NULL值

-如果expr1位NULL，则取值expr2,expr2是实际值

-expr1和expr2可以是任何数据类型，但两个参数的数据类型必须是一致的

如：计算员工月收入

SELECT ename,sal,comm,sal+nvl(comm,0) AS “SALARY”FROM MYEMPY;

11,NVL2(expr1,expr2,expr3):和NVL函数功能类似，都是将NULL转变为实际值，NVL2用来判断expr1是否为NULL,如果不是为NULL，返回expr2如果是空则返回expr3

SELECTENAME,SAL,COMM,NVL2(COMM,SAL+COMM,SAL) AS “SALARY” FROM MYEMPY;

四、基础查询语句

1，使用like条件（模糊查询）

当用户在执行查询是，不能完全确定某些信息的查询条件或者只知道信息的一部分，可以借助like来实现，like需要借助两个通配符：

%：表示0到多个字符

_：标识单个字符

这两个通配符可以配合使用，构造灵活的匹配条件

如：查询雇员名中第二个字母包含”A”字母的雇员姓名和工作

SELECT ename,job FROM MYEMPY WHERE enameLIKE ‘_A%’;

2，使用IN和NOT IN

比较操作符IN(list)用来去除符合列表list范围中的数据，当列或表达式匹配于list中的任何一个值，条件为TRUE,则该条记录则被显示出来，IN也可以理解为一个范围比较操作符，只不过这个范围是一个指定的值列表，NOT IN(list)取出不符合此列表中的数据记录

如：查询职位是MANAGE或者是CLERK的员工

SELECT ENAME,JOB FROM MYEMPY WHERE JOB IN(‘MANAGE’,’CLERK’);

3，使用ANY和ALL条件

ALL和ANY不能单独使用，需要配合单行比较操作符>,<,>=,<= 一起使用

>ANY: 大于最小的     <ANY:小于最大的    >ALL:大于最大的     <ALL:小于最小的

如：SELECT empno,ename,job,sal,deptno FROM MYEMPY WHEREsal>ANY(3500,4000,4500);

4，使用DISTINCT函数过滤重复

数据表中有可能存储相同数据的行，当执行查询操作默认情况会显示所有行，不管查询结果是否有重复数据，当重复数据没有实际意义，经常会需要去掉重复值，使用DISTINCT实现

如：查询员工部门编号并去掉重复

SELECT DISTINCT deptno FROM MYEMPY;

5，ORDER BY排序，一般配合ASC(升序排序)默认选项和DESC(降序排序)

-NULL值视作最大，则在升序排序中排在最后，在降序排序中排在最前面

如：SELECT empno,deptno,ename,salary FROM MYEMPY WHERE deptno=10 ORDERBY deptno ASC, salary DESC;

6，GROUP BY和HAVING字句

GROUP BY:分组查询,执行顺序从左到右，跟随在where字句后面

HAVING:用来对分组后的结果进一步限制，比如按部门分组后，得到每个部门的最高薪水可以继续限制输出结果，必须跟在GROUP BY后面，不能单独使用

如：查询每个部门的最高薪水，只有最高薪水大于10000的记录才被输出显示

SELECT deptno,MAX(sal)max_sal FROM MYEMPYGROUP BY deptno HAVING MAX(sal)>10000;

五、高级查询

一、子查询

在select查询中，在where查询条件中的限制条件不是一个确定的值，而是来自另外一个查询的结果，为了给查询提供数据而首先执行的查询语句叫做子查询，子查询是嵌入在其他SQL语句中的select语句，大部分时候出现在where字句中，子查询嵌入的语句称作主查询或父查询，主查询可以是select语句，也可以是其他类型的语句（DML）数据操作语句，也可以是（DDL）数据定义语句

1，  子查询在WHERE子句中

如：查找薪水比整个机构平均薪水高的员工

SELECT deptno,ename,sal FROM MYEMPY E WHEREsal>(SELECT AVG(sal) FROM MYEMPY);

2,子查询在HAVING子句中

如：查询列出最低薪水高于部门编号为30的最低薪水的部门信息

SELECT deptno,MIN(sal) min_sal FROM MYEMPYGROUP BY deptno HAVING (SELECT MIN(sal) min_sal FROM MYEMPY WHERE deptno=30);

3,子查询在FROM部分

如：查询出薪水比本部门平均薪水高的员工信息(子查询当做表一样使用)

SELECT E.DEPTNO,E.ENAME,E.SAL FROM MYEMPY E,(SELECT DEPTNO,AVG(SAL) AVG_SAL FROM MYEMPY GROUP BY DEPTNO) X WHERE E.DEPTNO =X.DEPTNO AND E.SAL>X.AVG_SAL ORDER BY E.DEPTNO;

4,子查询在SELECT部分

把子查询放在SELECT字句部分，可以认为是外连接的另一种形式表现，使用更灵活

SELECT E.ENAME,E.SAL,(SELECT D.DEPTNO FROM MYEMPY D WHERE E.DEPTNO=D.DEPTNO) FROM MYEMPY E;

分页和分组函数

1，ROWNUM:用于返回标识行数句顺序的数字

SELECT ROWNUM,ENPNO,ENAME,SAL FROM MYEMPY;

利用ROWNUM截取结果集中的部分数据，需要用到行内视图

SELECT * FROM (SELECT ROWNUM RN E.* FROM MYEMPY E) WHERE RN BETWEEN 8 AND 10;

SELECT * FROM (SELECT RUWNUM RN E.* FROM MYEMPY E) WHERE RN BETWEEN ((n-1)*pageSize+1) AND (n*pageSize);

2，  DECODE函数

DECODE(expr,search1,result1[,search2,reslut2…..][,default])DECODE用于比较参数expr的值，如果匹配到哪一个search条件，就返回对应的result结果，可以有多组search和result的对应关系，如果任何一个search条件都没有匹配到，则返回最后default的值，default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返回NULL

如：查询志愿表，根据职员的职位计算奖励金额，当职位分别是’MANAGER’,’ANALYST’,’SALESMAN’时奖励金额分别是薪水的1.2倍，1.1倍，1.05倍，如果不是这三个职位，则奖励金额取薪水值

SELECT ENAME,JOB,SAL, DECODE(JOB,’MANAGER’,SAL*1.2,’ANALYST’,SAL*1.1,’SALESMAN’,SAL*1.05,SAL)bonus FROM MYEMPY;

3，  ROW_NUMBER函数

ROW_NUMBER() OVER(PARTITION BY col1 ORDERBY col2):表示根据col1分组，在分组内部根据col2排序，此函数计算的值就是表示每组内部排序后的顺序编号，组内连续且唯一

如：按照部门编码分组显示，每组内按职员编码排序并赋予组内编码

SELECT deptno,ename,emptno,ROW_NUMBER()OVER (PARTITION BY deptno ORDER BY empno) AS EMP_ID FROM MYEMPY;

4，  RANK函数

RANK() OVER(PARTITION BY col1 ORDER BYcol2):表示根据col1分组，在分组内部根据col2给予等级标识，等级标识即排名，相同的数据返回相同排名，如果有相同的数据，则排名相同，比如并列第二则两行数据都标记为2，但下一位将是第四名，和ROW_NUMBER的区别是：RANK有重复值而ROW_NUMBER没有

如：按照部门编码分组，同组内按薪水倒叙排序，相同薪水则按奖金数正序排序，并给予组内等级用RANK_ID表示

SELECT deptno,ename,sal,comm,RANK() OVER(PARTITION BY deptno ORDER BY sal DESC,comm) “RANK_ID” FROM MYEMPY;

5，  DENSE_RANK函数

DENSE_RANK() OVER(PARTITION BY col1 ORDERBY col2)表示根据col1分组，在分组内部根据col2给予等级标识，相同的数据返回相同的排名，连续排序，如果有并列第二，下一个排序将是第三，这一点和RANK函数的不同点，RANK是跳跃排序

如：关联emp和dept表，按照部门编码分组，每组内按照员工薪水排序列出员工的部门名字，姓名和薪水

SELECT D.DNAME,E.ENAME,E.SAL,DENSE_RANK()OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL) AS drank FROM EMP E JOIN DEPT D ONE.DEPTNO=D.DEPTNO;

二、集合查询

为了合并多个SELECT语句的结果，可以使用集合操作符，实现集合的并、交、差，集合操作符包括UNION、UNION ALL、INTERSECT和MINUS，多条件集合操作的SELECT语句的列的个数和数据类型必须匹配

1，  UNION和UNION ALL

用来获取两个或两个以上的结果集的并集

UNION操作符会自动去掉合并后的重复记录，并对查询结果进行排序

UNION ALL返回两个结果集中的所有行，包括重复行，不对结果排序

如：合并职位是‘MANAGER’的员工和薪水大于2500的员工集合，查看两种方式的结果差别

SELECT ename,job,sal FROM MYEMPY WHERE JOB= ‘MANAGER’ UNION SELECT ename,job,sal FROM MYEMPY WHERE sal>2500;

SELECT ename,job,sal FROM MYEMPY WHERE JOB= ‘MANAGER’ UNION ALL SELECT ename,job,sal FROM MYEMPY WHERE sal>2500;

2,INTERSECT

获的两个结果集的交集，只有同时存在于两个结果集中的数据，才会被显示输出，使用INTERSECT操作符后的结果集会以第一列的数据做升序排列

如：显示职位是‘MANAGER’的员工和薪水大于2500的员工的交集

SELECT ename,job,sal FROM MYEMPY WHERE JOB= ‘MANAGER’ INTERSECT SELECT ename,job,sal FROM MYEMPY WHERE sal>2500;

2，  MINUS

获取两个结果集的差集，只有在第一个结果集中存在，在第二个结果集中不存在的数据，才能够被显示出来，也就是结果集一减去结果集二的结果

如：列出职位是MANAGER但薪水低于2500的员工记录

SELECT ename,job,sal FROM MYEMPY WHERE JOB= ‘MANAGER’ MINUS SELECT ename,job,sal FROM MYEMPY WHERE sal>=2500;

高级分组函数

ROLLUP，CUBE和GROUPINGSETS运算符是GROUP BY字句的扩展，可以生成与使用UNION ALL来组合单个分组查询时相同的结果集，用来简化和高效的实现统计查询

1，  ROLLUP函数

假设有表test，有a,b,c,d四个列

SELECT a,b,c,SUM(d) FROM test GROUP BYROLLUP(a,b,c)等价于：

SELECT a,b,c,SUM(d) FROM test GROUP BYa,b,c UNION ALL

SELECT a,b,NULL,SUM(d) FROM test GROUP BYa,b,c UNION ALL

SELECT a,NULL,NULL,SUM(d) FROM test GROUPBY a,b,c UNION ALL

SELECT NULL,NULL,NULL,SUM(d) FROM testGROUP BY a,b,c FROM test;

对ROLLUP的列从右到左以一次少一列的方式进行分组知道所有列都去掉后的分组(也就是全表查询)，对n个参数的ROLLUP有n+1次分组

如：2010-2011年每月每天的销售额

年份	月	日	销售额
2010	1	1	58.82
2010	1	2	47.91
2010	1	3	84.57
……	……	……	……
2011	12	29	76.92
2011	12	30	51.93
2011	12	31	78.11

创建SALES_TAB表：CREATE TABLE SALES_TAB(YEAR_ID NUMBER NOT NULL,MONTH_ID NUMBER NOTNULL,DAY_ID NUMBER NOT NULL,SALES_VALUE NUMBER(10，2) NOT NULL);

分组查询(按YEAR_ID)：SELECT YEAR_ID, COUNT(*) AS NUM_ROWS, SUM(SALES_VALUE) ASSALES_VALUE FROM SALES_TAB GROUP BY YEAR_ID ORDER BY YEAR_ID;

分组查询(按YEAR_ID和MONTH_ID)：SELECT YEAR_ID,MONTH_ID, COUNT(*) AS NUM_ROWS, SUM(SALES_VALUE) ASSALES_VALUE FROM SALES_TAB GROUP BY YEAR_ID ,MONTH_ID ORDER BY YEAR_ID,MONTH_ID;

分组查询(使用ROLLUP函数查询显示出2010年和2011年每个月份以及每个年份和整张表的总和)：SELECT YEAR_ID,MONTH_ID,SUM(SALES_VALUE) AS SALES_VALUE FROM SALES_TAB GROUP BY ROLLUP(YEAR_ID,MONTH_ID)  ORDER BY YEAR_ID,MONTH_ID;

分组查询(使用ROLLUP函数查询则可以显示出每天、每个年份、2010和2011年每个月份以及整张表的总和)：SELECTYEAR_ID,MONTH_ID, DAY_ID,SUM(SALES_VALUE) AS SALES_VALUE FROM SALES_TAB GROUPBY ROLLUP(YEAR_ID ,MONTH_ID,DAY_ID) ORDER BY YEAR_ID,MONTH_ID,DAY_ID;

2，  CUBE函数

GROUP BY CUBE(a,b,c)对CUBE的每个参数，都可以理解为取值是参与分组和不参与分组两个值的一个维度，所有维度取值组合的集合就是分组后的集合，对于n各参数的CUBE，有2 ^n次分组GROUP BY CUBE(a,b,c),首先对(a,b,c)进行GROUP BY,然后依次是(a,b),(a,c),(b,c),(a),(b),(c),最后对全表进行GROUP BY操作一共是2^3=8次分组

分组查询(使用CUBE函数查询显示每个年份，分不同年份的每个月份，不分年份的每个月份以及全表的总销售额)：SELECT YEAR_ID, MONTH_ID,SUM(SALES_VALUE)AS SALES_VALUE FROM SALES_TAB GROUP BY CUBE(YEAR_ID ,MONTH_ID)  ORDER BY YEAR_ID,MONTH_ID;

3，  GROUPING SETS函数

GROUPING SETS 运算符可以生成与使用单个GROUPBY,ROLLUP或CUBE运算符所生成的结果集相同的结果集，如果不需要获的有完备的ROLLUP或CUBE运算符生成的全部分组，则可以使用GROUPING SETS仅指定所需的分组，GROUPING SETS（自定义罗列出分组的方案）

分组示例：

-使用GROUP BY GROUPINGSETS(a,b,c),则对(a),(b),(c)进行GROUP BY

-使用GROUP BY GROUPINGSETS((a,b),c),则对(a,b),(c)进行GROUP BY

-使用GROUP BY GROUPINGSETS(a,a),则对(a)进行2次GROUP BY，GROUPING SETS的参数允许重复

分组查询(使用GROUPING SETS函数查询)：SELECTYEAR_ID,MONTH_ID, DAY_ID,SUM(SALES_VALUE) AS SALES_VALUE FROM SALES_TAB GROUPBY GROUPING SETS((YEAR_ID) ,(MONTH_ID))  ORDERBY 1YEAR_ID,MONTH_ID;