Oracle数据库学习笔记

Oracle数据库是由甲骨文公司研发的一款关系数据库管理系统。

day01

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

SQL分类：

1>数据定义语言(DDL)：Data Definotion Language

CREATE：创建表或其他对象的结构；

ALTER：修改表或其他对象的结构；

DROP：删除表或其他对象的结构；

TRUNCATE：删除表数据，保留表结构。

2>数据操纵语言(DML)：Data Manipulation Language

INSERT：将数据插入到数据表中；

UPDATE：更新数据表中已经存在的数据；

DELETE：删除数据表中的数据。

3>事物控制语言(TCL)：Transaction Control Language

COMMIT：提交，确定已经进行的数据改变；

ROLLBACK：回滚，取消已经进行的数据改变；

SAVEPOINT：保存点，使当前的事物可以回退到指定的保存点，便于取消部分改变。

4>数据查询语言(DQL)：Data Query Language

SELECT：查询语句。

5>数据控制语言(DCL)：Data Control Language

GRANT：授予，用于给用户或角色授予权限；

REVOKE：用于收回用户或角色已有的权限；

CREATE USER：创建用户。

创建表：

CREATE TABLE employee(

id NUMBER(4),

name VARCHAR2(20),

gender CHAR(1),

birth DATE,

salary NUMBER(6,2),

job VARCHAR2(30),

deptno NUMBER(2)

);

注意：

1>查看表结构：DESC employee；

2>在数据库中，所有类型的默认值都是NULL。即:当插入数据时，若某个字段没有指定值时，默认就将NULL作为值插入。可以在创建表的时候通过使用DEFAULT关键字来单独指定默认值；

3>字符串的字面量是使用单引号括起来的，这一点与java不同。

删除表：

DROP TABLE employee

CREATE TABLE employee(

id NUMBER(4),

name VARCHAR2(20),

gender CHAR(1) DEFAULT 'M',

birth DATE,

salary NUMBER(6,2),

job VARCHAR2(30),

deptno NUMBER(2)

);

NOT NULL约束：

当一个字段使用NOT NULL约束后，该字段在任何情况下值不允许为空。

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),

deptno NUMBER(2)

);

修改表：

1>修改表名：

RENAME old_name TO new_name；

将employee表改名为myemp：RENAME employee TO myemp；

2>修改表结构：

2.1:添加列：

为myemp表添加一列hiredate：

ALTER TABLE myemp ADD(

hiredate DATE DEFAULT SYSDATE

)；

2.2:删除现有列：

删除myemp表中的hiredate列：

ALTER TABLE myemp DROP(hiredate)；

2.3:修改现有列:

可以修改列的类型，长度，以及默认值或非空当表中存在数据后，尽量不修改字段类型，若

修改长度尽量不要减小，否则可能修改失败：

ALTER TABLE myemp MODIFY(

job VARCHAR2(40) DEFAULT 'CLERK')；

DML语句：

1>DML可以对表中的数据进行操作，分为INSERT,UPDATE,DELETE，DML是伴随事务使用的。

INSERT INTO myemp

(id,name,salary,job)

VALUES

(1,'jack',5000,'CLERK')；

2>插入数据时可以不指定具体列，若这样做则是全列插入,所有列都要给值：

INSERT INTO myemp

VALUES

(1,'JACK',5000,'CLERK',...)；

3>插入一个日期的值

INSERT INTO myemp

(id,name,birth)

VALUES

(2,'rose',TO_DATE('2008-08-08','YYYY-MM-DD')

)；

UPDATE语句：

修改表中现有数据

UPDATE myemp

SET gender='F',salary=6000

WHERE name='jack'；

删除数据：

DELETE语句，通常应当使用WHERE添加条件，否则是清空表的操作。

DELETE FROM myemp

WHERE name='jack'；

day02

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

查询语句 DQL：

1>SELECT语句用于查询数据 ;

2>查看emp表中所有字段以及所有记录：

SELECT * FROM emp;

3>查看某几个字段的值：

SELECT ename,job,sal,deptno

FROM emp;

4>SELECT子句可以查看:字段，函数，表达式。查看每个员工的年薪：

SELECT ename,sal*12

FROM emp;

5>SELECT语句也可以使用WHERE子句，查看20号部门的员工信息：

SELECT ename,sal,job,deptno

FROM emp

WHERE deptno=20;

SELECT ename,sal,job,deptno

FROM emp

WHERE job='CLERK';

字符串函数：

1>CONCAT(p1,p2)：拼接字符串：

SELECT CONCAT(ename,sal)

FROM emp;

SELECT CONCAT(CONCAT(ename,':'),sal)

FROM emp;

2>SQL语句中可以使用"||"连接字符串：

SELECT ename||':'||sal

FROM emp;

3>LENGTH(p):获取字符串长度w：

SELECT ename,LENGTH(ename)

FROM emp;

4>LOWER,UPPER,INITCAP:小写，大写，首字母大写；

SELECT LOWER('HELLOWORLD'),

UPPER('helloworld'),

INITCAPk,('HELLO WORLD')

FROM dual;

5>伪表：dual。当查询的内容与任何一张表无关时，为了满足FROM子句要求，可以使用伪表。伪表只会查询出一条记录。

SELECT ename,sal,job

FROM emp

WHERE ename=UPPER('mary');

6>TRIM,LTRIM,RTRIM:去除字符串两边内容：

SELECT TRIM('e' FROM 'eeeeliteeee')

FROM emp;

SELECT LTRIM('esesesliteee','es')

FROM emp;

7>LPAD,RPAD：补位函数。可以将指定内容显示指定位数，不足时则补充若干个指定的单一字符以达到位数。

SELECT LPAD(sal,6,'$')

FROM emp;

8>SUBSTR(str,m[,n])函数:截取字符串。截取str字符串从m处开始，连续截取n个,若不指定n,则是连续截取到字符串末尾。n若超过实际可截取的长度，也是到字符串末尾。需要注意:数据库中下标是从1开始的。

SELECT SUBSTR('thinking in java',10,2)

FROM dual;

9>INSTR(str1,str2[,n[,m]])查找位置。查找str2在str1中的位置。若指定了位置n:从n处开始查找第一次出现的位置；若指定了m:则是查找第m次出现的位置。

SELECT INSTR('thinking in java','in',4,2)

FROM dual;

数字函数：

1>ROUND(n[,m])：对n进行四舍五入。m是保留到小数点后多少位。若m为0或不指定，则保留到整数。若m为负数，则是保留小数点前的位数。

SELECT ROUND(45.678,2),

ROUND(45.678),

ROUND(55.678,-2)

FROM dual;

2>TRUNC(n[,m])：截取数字：

SELECT TRUNC(45.678,2),

TRUNC(45.678),

TRUNC(55.678,-1)

FROM dual;

3>MOD(n,m):求余数。若m为0，则直接返回n。

SELECT MOD(13,4)

FROM dual;

4>CEIL,FLOOR:向上取整，向下取整。

SELECT CEIL(45.678),FLOOR(45.678)

FROM dual;

日期类型：

1>DATE与TIMESTAMP。时间戳的前面7个字节与DATE一致，多出的4个字节保存一个整数(int)，可以存秒以下的精度。所以可以精确到纳秒。

2>SYSDATE:表示一个内部函数，返回一个表示当前系统时间的DATE类型的值。

SELECT SYSDATE FROM dual;

INSERT INTO emp (empno,ename,hiredate,sal,job,deptno)

VALUES (1001,'JACK',SYSDATE,5000,'CLERK',20);

SELECT * FROM emp;

3>SYSTIMESTAMP:返回时间戳类型当前系统时间

SELECT SYSTIMESTAMP FROM dual;

4>日期类型可以比大小，越晚的越大。日期可以进行加减运算，加上一个数字等同于加上指定的天数，减法，同理。两个日期间也可以相减，差为相差的天数。

SELECT SYSDATE + 1 FROM dual;

5>查看每个员工到今天为止入职多少天了：

SELECT ename,TRUNC(SYSDATE - hiredate)

FROM emp;

6>查看自己到今天为止活了多少天：

SELECT SYSDATE-TO_DATE('1992-07-21','YYYY-MM-DD')

FROM dual;

8>TO_DATE()函数：可以将一个字符串按照指定的日期格式转换为DATE。RR:两位数字表示年，会自动判定世纪。

TO_DATE('05-07-21','RR-MM-DD');

9>查看1982年以后入职的员工:

SELECT ename, hiredate

FROM emp

WHERE

hiredate > TO_DATE('1982-01-01', 'YYYY-MM-DD');

10>TO_CHAR():可以将日期按照指定日期格式转换为字符串

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')

FROM dual ;

11>日期格式字符串中，出现的字符凡不是英文与符号，其他字符全部要使用双引号括起来。

SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日"' )

FROM dual ;

SELECT TO_CHAR(TO_DATE('50-02-04','RR-MM-DD'), 'YYYY-MM-DD')

FROM dual;

12>LAST_DAY(date):返回给定日期所在月的月底日期。当月月底是哪天：

SELECT LAST_DAY(SYSDATE)

FROM dual;

13>ADD_MONTHS(date,i)：对指定日期加上指定的月，查看每个员工入职20周年的纪念日：

SELECT ename,ADD_MONTHS(hiredate,12*20)

FROM emp;

14>MONTHS_BETWEEN(date1,date2)：返回两个日期之间相差的月，计算是根据date1-date2计算的结果。查看每个员工距今已经入职多少个月了：

SELECT ename, MONTHS_BETWEEN(SYSDATE,hiredate)

FROM emp;

15>NEXT_DAY(date,i)：返回给定日期之后一周内的周几对应的日期，i表示周几，1为周日，2为周一，以此类推。

SELECT NEXT_DAY(SYSDATE,4)

FROM dual;

16>LEAST、GREATEST函数求最小值与最大值：

SELECT LEAST(SYSDATE, TO_DATE('2008-10-10','YYYY-MM-DD'))

FROM dual;

17>查看每个员工的入职时间，若是82年以后入职的，则显示其入职时间，若是82年以前入职的则显示为1982-01-01。

SELECT ename,GREATEST(hiredate,TO_DATE('1982-01-01','YYYY-MM-DD'))

FROM emp;

18>EXTRACT函数：提取一个日期中指定时间分量对应的值。

SELECT EXTRACT(YEAR FROM SYSDATE)

FROM dual;

19>查看1982年入职的员工：

SELECT ename,hiredate

FROM emp

WHERE EXTRACT(YEAR FROM hiredate)=1982;

空值操作：

1>CREATE TABLE student (id NUMBER(4), name CHAR(20), gender CHAR(1));

2>插入NULL值:

INSERT INTO student VALUES(1000, '李莫愁', 'F');

INSERT INTO student VALUES(1001, '林平之', NULL);

INSERT INTO student(id, name) VALUES(1002, '张无忌');

SELECT * FROM student;

3>更新NULL值：

UPDATE student SET gender=NULL

WHERE id=100;

4>NULL作为条件进行过滤时：不可以用"="判断NULL。

SELECT * FROM student

WHERE gender IS NOT NULL;

5>NULL的运算：NULL与字符串连接等于什么也没干。NULL与数字运算结果还是NULL。

SELECT ename,sal,comm

FROM emp;

SELECT ename,sal,comm,sal+comm

FROM emp;

6>NVL(p1,p2)：当p1为NULL时，函数返回p2,若p1不为NULL，则函数返回p1自身，所以是将NULL替换为一个非NULL值。

SELECT ename,sal,comm,sal+NVL(comm,0)

FROM emp;

8>NVL2(p1,p2,p3)：若p1不为NULL，则函数返回p2，若p1为NULL，则函数返回p3。

SELECT ename,comm,NVL2(comm,'有奖金','没有奖金')

FROM emp;

SELECT ename,sal,comm,NVL2(comm,sal+comm,sal)

FROMemp;

day03

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

基本查询语句：

1>当一个SELECT子句中查询的内容是一个函数或者表达式，那么在结果集对应的该字段的字段名就是这个函数或表达式，可读性差。为此应当为该列添加别名。别名不区分大小写，若希望区分大小写，或

在别命中含有空格，可以使用双引号括起来。

SELECT ename,sal*12 "sal"

FROM emp;

查询条件：

1>SELECT ename, sal, job

FROM emp

WHERE sal > 1000

AND job = 'CLERK';

2>查看工资高于1000的CLERK与SALESMAN：

SELECT ename, sal, job

FROM emp

WHERE sal>1000 AND (job='SALESMAN' OR job='CLERK');

3>AND的优先级高于OR，所以可以通过添加括号来提高OR的优先级。

4>LIKE用于模糊匹配字符串。两个通配符:"%"表示0到多个字符，"_"表示一个字符。查看名字第二个字符是A的员工：

SELECT ename,job,sal

FROM emp

WHERE ename LIKE '_A%';

5>IN(list),NOT IN(list)：判断在列表中和不在列表中，IN与NOT IN常用于子查询中。

SELECT ename,job,sal

FROM emp

WHERE job IN ('MANAGER','CLERK');

6>ANY,ALL:用于判断诸如>,>=,<,<=一个列表中的内容。ALL(list):大于列表中所有的(大于最大的)；ANY(list):大于列表中之一即可(大于最小的)。

SELECT empno, ename, job, sal, deptno

FROM emp

WHERE sal > ANY (3500,4000,4500);

7>ANY和ALL的列表通常不会给确定值，这样没有意义，通常使用在判断一个子查询的结果。可以使用函数或者表达式的结果作为过滤条件。

SELECT ename, sal, job

FROM emp

WHERE ename = UPPER('scott');

SELECT ename, sal, job

FROM emp

WHERE sal * 12 >50000;

8>DISTINCT关键字：去除后面指定字段的重复行。DISTINCT必须紧跟在SELECT关键字之后。查看公司总共有多少种职位：

SELECT DISTINCT job

FROM emp;

9>DISTINCT后面可以跟多个字段：这些字段值的组合没有重复的。

SELECT DISTINCT job,deptno

FROM emp;

排序：

1>ORDER BY子句，可以根据后面指定的字段对结果集进行升序或者降序排列。其中ASC是升序，DESC是降序。通常ASC可以不写，不写默认就是升序。ORDER BY子句只能写在SELECT语句中的

最后一个子句上。查看公司工资的排名:

SELECT ename,sal

FROM emp

ORDER BY sal DESC;

2>ORDER BY可以按照多个字段排序，排序是有优先级的，首先按照第一个字段的排序规则排序，当第一个字段有重复值时，才按照第二个字段排序。

SELECT ename,deptno,sal

FROM emp

ORDER BY deptno,sal DESC;

聚合函数：

1>聚合函数又称为多行函数，分组函数。聚合函数可以将条记录进行统计，然后得出一个结果。所以聚合函数是用来统计使用的。

2>MAX(),MIN()：用来统计最大值与最小值。查看公司的最高工资是多少：

SELECT MAX(sal),MIN(sal)

FROM emp;

3>AVG(),SUM()：求平均值与总和。

SELECT AVG(sal),SUM(sal)

FROM emp;

4>COUNT()：统计记录总数。

SELECT COUNT(ename)

FROM emp;

5>聚合函数忽略NULL值。

SELECT AVG(comm),SUM(comm)

FROM emp;

SELECT AVG(NVL(comm,0))

FROM emp;

6>统计表中总数据量：

SELECT COUNT(*)

FROM emp;

7>查看SALESMAN和CLERK员工的名字，职位，部门，然后按照入职时间从早到晚排序。

SELECT ename,job,deptno

FROM emp

WHERE job IN ('SALESMAN','CLERK')

ORDER BY hiredate;

8>查看82年以前入职的职位是MANAGER并且工资高于2500的员工的名字，职位，工资

以及所在部门：

SELECT ename,job,sal,deptno

FROM emp

WHERE job='MANAGER'

AND sal>2500

AND hiredate<TO_DATE('1982-01-01','YYYY-MM-DD');

分组：

1>GROUP BY子句：GROUP BY可以将结果集按照指定的字段值相同的记录看做一组，配合聚合函数使用可以对不同分组的记录分别进行统计然后得到结果。查看每个部门最高工资与最低工资：

SELECT MAX(sal),MIN(sal),deptno

FROM emp

GROUP BY deptno;

2>在SELECT当中若使用了聚合函数，那么不在聚合函数中的其他单独字段必须出现在GROUP BY子句中。反过来不是必须的。

SELECT AVG(sal),SUM(sal),job

FROM emp

GROUP BY job;

3>GROUP BY也可以按照多个字段进行分组。分组原则是这些字段值的组合相同的看做一组。查看每个部门每种职位的平均工资：

SELECT AVG(sal),deptno,job

FROM emp

GROUP BY deptno,job;

4>查看部门的平均工资，前提是该部门平均工资高于2000。

SELECT AVG(sal),deptno

FROM emp

WHERE AVG(sal)>2000

GROUP BY deptno;

5>执行上述SQL会出现：此处不允许使用分组函数的错误，原因在于AVG(sal)>2000不应当定义在WHERE中。过滤的时机不对。WHERE的过滤时机：WHERE是在查询表中每一条数据时进行过滤的，只会将满足WHERE条件的记录查询出来。

6>HAVING子句:HAVING子句必须紧跟在GROUP BY子句之后，作用是添加条件来过滤不同的分组。HAVING可以使用聚合函数作为过滤条件。

SELECT AVG(sal),deptno

FROM emp

GROUP BY deptno

HAVING AVG(sal)>2000;

7>查看部门平均工资高于2000的这些部门的最高工资和最低工资：

SELECT MAX(sal),MIN(sal),deptno

FROM emp

GROUP BY deptno

HAVING AVG(sal)>2000;

关联查询：

1>关联查询是建立在多张表上进行联合查询。查询的结果集中每一条记录中的字段可能来自于不同表。重点是:找到表与表的记录之间的对应关系。查看每个员工的信息以及其所在部门的信息。

SELECT e.ename,e.job,e.sal,e.deptno,d.dname,d.loc

FROM emp e,dept d

WHERE e.deptno=d.deptno;

2>上述SQL中:e.deptno=d.deptno条件是用来联系emo,dept的数据关系的，这样的条件称为:连接条件。在关联查询中，必须要添加连接条件，N张表关联查询至少要添加N-1个连接条件。不添加连接条件会产生笛卡尔积。实际开发中要避免笛卡尔积的出现。这是一个无意义的结果集。

SELECT e.ename,d.dname

FROM emp e,dept d;

3>在关联查询中，连接条件应当与过滤条件同时成立。查看SALES部门的员工。

SELECT e.ename,e.job,e.sal,e.deptno,d.dname

FROM emp e,dept d

WHERE e.deptno=d.deptno AND d.dname='SALES';

4>内连接：内连接是关联查询的另一种写法。

SELECT e.ename,d.dname

FROM emp e,dept d

WHERE e.deptno=d.deptno;

SELECT e.ename,d.dname

FROM emp e JOIN dept d ON e.deptno=d.deptno

WHERE d.dname='SALES';

5>查看每个员工以及所在部门信息:

SELECT e.ename,e.sal,d.dname,d.loc

FROM emp e JOIN dept d ON e.deptno=d.deptno;

6>SCOTT没有被查询出来，原因是他的部门号是50，不满足连接条件。若想将不满足连接条件的记录也列出来，需要使用外连接。外连接分为，左外连接和右外连接。左外连接是以JOIN左侧的表为驱动表，该表所有记录都要显示出来，那么当某条记录不满足连接条件时，那么来自JOIN右侧表中的字段的值全部显示为NULL。

7>全外连接:

SELECT e.ename,e.sal,d.dname,d.loc

FROM emp e LEFT[RIGHT|FULL] OUTER JOIN dept d

ON e.deptno=d.deptno;

8>关联查询也可以实现外连接效果，(+)定义在连接条件上，定义在哪边，哪边就补NULL。但是不能实现全外连接效果。

SELECT e.ename,d.dname

FROM emp e,dept d

WHERE e.deptno(+)=d.deptno;

9>自连接：当前表的一条记录对应当前表自己的多条记录，这种设计就是自连接。自连接是用来解决数据内容相同，但是数据间又存在上下级关系的树状结构的情况。查看每个员工以及其上司的名字。

SELECT e.ename,m.ename

FROM emp e,emp m

WHERE e.mgr=m.empno;

day04

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

子查询：

1>子查询就是一条查询语句，它是嵌套在其他SQL语句中的。目的是为实际的SQL语句提供数据。子查询可以应用在DQL,DDL,DML中。

a>查看比CLARK工资高的员工信息：

SELECT ename,sal

FROM emp

WHERE sal>(SELECT sal

FROM emp

WHERE ename='CLARK');

b>查看和CLARK相同部门的员工：

SELECT ename,deptno

FROM emp

WHERE deptno=(SELECT deptno

FROM emp

WHERE ename='CLARK')

AND ename <> 'CLARK';

c>查看高于公司平均工资的员工信息：

SELECT ename,sal,deptno,job

FROM emp

WHERE sal>(SELECT AVG(sal) FROM emp);

2>子查询在DDL中的应用:可以根据查询的结果集快速创建一张表，创建一张表myemployee含有字段:

empno,ename,sal,job,deptno,dname,loc。数据来自emp与dept表。

CREATE TABLE myemployee AS

SELECT e.empno,e.ename,e.sal,e.job,e.deptno,d.dname,d.loc

FROM emp e JOIN dept d

ON e.deptno=d.deptno;

3>DML中使用子查询：将CLARK所在部门所有员工的工资增加500。

UPDATE emp SET sal=sal+500

WHERE deptno=(SELECT deptno

FROM emp

WHERE ename='CLARK');

4>子查询根据查询结果不同，分为：

单行单列子查询：常用作过滤条件；

多行单列子查询：常用作过滤条件；

多行多列子查询：常当做表看待；

a>查看与职位是SALESMAN同部门的其他员工。

SELECT ename,job,deptno

FROM emp

WHERE deptno IN(SELECT deptno

FROM emp

WHERE job='SALESMAN')

AND job <> 'SALESMAN';

b>查看比所有职位是CLERK和SALESMAN工资都高的员工信息。

SELECT ename,job,sal

FROM emp

WHERE sal > ALL(SELECT sal

FROM emp

WHERE job IN ('CLERK','SALESMAN'));

c>查看部门的最低薪水，前提是该部门的最低薪水要高于30号部门的最低薪水。

SELECT MIN(sal),deptno

FROM emp

GROUP BY deptno

HAVING MIN(sal)>(SELECT MIN(sal)

FROM emp

WHERE deptno=30);

d>查看高于自己所在部门平均工资的员工。

SELECT e.ename,e.sal,e.deptno

FROM emp e,(SELECT AVG(sal) avg_sal

FROM emp

GROUP BY deptno) s

WHERE e.deptno=s.deptno

AND e.sal>s.avg_sal;

e>查看4人以上(含4人)的部门员工的工资都是多少。

SELECT ename,sal,deptno

FROM emp

WHERE deptno IN (SELECT deptno

FROM emp

GROUP BY deptno

HAVING COUNT(*)>=4);

SELECT e.ename,e.sal,e.deptno

FROM emp e,(SELECT COUNT(*) p_count,deptno

FROM emp

GROUP BY deptno) t

WHERE e.deptno=t.deptno AND t.p_count>=4;

5>EXISTS关键字：该关键字后面跟一个子查询，用于在WHERE中作为过滤条件使用。是用来判断其后的子查询是否可以查询出数据，若可以则EXSITS返回真，否则返回假。查看有员工的部门。

SELECT d.deptno,d.dname,d.loc

FROM dept d

WHERE EXISTS(SELECT * FROM emp e

WHERE e.deptno=d.deptno);

6>子查询在SELECT子句中:

SELECT e.ename, e.sal, (SELECT d.dname

FROM dept d

WHERE d.deptno=e.deptno) dname

FROM emp e;

分页查询：

1>分页查询是分段查询数据，在查询的数据量非常大的时候尤其重要。分页可以减少系统资源消耗，响应速度快。不同的数据库分页的语法不一致。

2>ORACLE中使用ROWNUM解决分页。ROWNUM是一个伪列。不存在与任何一张表中，但是每张表都可以查询该字段。在查询某张表时，只要可以查询出一条记录，该字段的值就是这条记录的行号，从1开始。生成的过程在查询过程中进行。

SELECT ROWNUM,ename,sal,deptno

FROM emp

WHERE ROWNUM BETWEEN 6 AND 10;

3>以上查询不出任何数据。因为编号是在查询的过程中进行的，所以不能用ROWNUM在编号的过程中进行大于1以上的数字的判断，否则查询不出数据。

SELECT *

FROM(SELECT ROWNUM rn,ename,sal,deptno FROM emp)

WHERE rn BETWEEN 6 AND 10;

4>查看公司工资排名的第6-10名。

SELECT *

FROM(SELECT ROWNUM rn,ename,sal,deptno FROM emp)

WHERE rn BETWEEN 6 AND 10;

5>若在分页中有排序需求时，一定要先排序，因为排序总是在SQL最后执行。

SELECT *

FROM(SELECT ROWNUM rn,t.*

FROM(SELECT ename,sal,deptno

FROM emp

ORDER BY sal DESC) t)

WHERE rn BETWEEN 6 AND 10;

6>计算分页公式。page:页号，pageSize:每页的条目数。

start:(page-1)*pageSize+1；

end:pageSize*page；

SELECT *

FROM(SELECT ROWNUM rn,t.*

FROM(SELECT ename,sal,deptno

FROM emp

ORDER BY sal DESC) t

WHERE ROWNUM <=10)

WHERE rn>=6;

7>DECODE函数，可以实现分支结构。

SELECT ename, job, sal,

DECODE(job,

'MANAGER', sal * 1.2,

'ANALYST', sal * 1.1,

'SALESMAN', sal * 1.05,

sal) bonus

FROM emp;

8>CASE语句，和DECODE函数功能相似。

SELECT ename,job,sal,

CASE job WHEN 'MANAGER' THEN sal*1.2

WHEN 'ANALYST' THEN sal*1.1

WHEN 'SALESMAN' THEN sal*1.05

ELSE sal END bonus

FROM emp;

9>将MANAGER与ANALYST看做一组，其他职位员工看做另一组分别统计人数。

SELECT COUNT(*),DECODE(job,

'MANAGER','VIP',

'ANALYST','VIP',

'OTHER');

FROM emp

GROUP BY DECODE(job,

'MANAGER','VIP',

'ANALYST','VIP',

'OTHER');

排序函数：

1>排序函数允许按照指定字段分组，再按照指定字段排序，然后生成组内编号。ROW_NUMBER:生成组内连续且唯一的数字。查看每个部门工资排名:

SELECT ename,sal,deptno,

ROW_NUMBER() OVER(

PARTITION BY deptno ORDER BY sal DESC

) rank

FROM emp;

2>RANK函数:生成组内不连续也不唯一的数字

SELECT ename,sal,deptno,

RANK() OVER(

PARTITION BY deptno ORDER BY sal DESC

) rank

FROM emp;

3>DENSE_RANK函数:生成组内连续但不唯一的数字。

SELECT ename,sal,deptno,

DENSE_RANK() OVER(

PARTITION BY deptno ORDER BY sal DESC

) rank

FROM emp;

4>新建数据表准备：

CREATE TABLE sales_tab (

year_id NUMBER NOT NULL,

month_id NUMBER NOT NULL,

day_id NUMBER NOT NULL,

sales_value NUMBER(10,2) NOT NULL

);

INSERT INTO sales_tab

SELECT TRUNC(DBMS_RANDOM.value(2010, 2012)) AS year_id,

TRUNC(DBMS_RANDOM.value(1, 13)) AS month_id,

TRUNC(DBMS_RANDOM.value(1, 32)) AS day_id,

ROUND(DBMS_RANDOM.value(1, 100), 2) AS sales_value

FROM dual

CONNECT BY level <= 1000;

SELECT * FROM sales_tab

ORDER BY year_id,month_id,day_id;

5>查看每天的营业额。

SELECT year_id,month_id,day_id,

SUM(sales_value)

FROM sales_tab

GROUP BY year_id,month_id,day_id

ORDER BY year_id,month_id,day_id

6>查看每月营业额。

SELECT year_id,month_id,

SUM(sales_value)

FROM sales_tab

GROUP BY year_id,month_id

ORDER BY year_id,month_id

7>每年营业额。

SELECT year_id,

SUM(sales_value)

FROM sales_tab

GROUP BY year_id

ORDER BY year_id

8>所有营业额。

SELECT SUM(sales_value)

FROM sales_tab;

9>查看每天，每月，每年，以及总额。

SELECT year_id,month_id,day_id,

SUM(sales_value)

FROM sales_tab

GROUP BY year_id,month_id,day_id

UNION ALL

SELECT year_id,month_id,NULL,

SUM(sales_value)

FROM sales_tab

GROUP BY year_id,month_id

UNION ALL

SELECT year_id,NULL,NULL,

SUM(sales_value)

FROM sales_tab

GROUP BY year_id

UNION ALL

SELECT NULL,NULL,NULL,

SUM(sales_value)

FROM sales_tab;

高级分组函数:

1>ROLLUP：GROUP BY ROLLUP(a,b,c)，等同于：

GROUP BY a,b,c

UNION ALL

GROUP BY a,b

UNION ALL

GROUP BY a

UNION ALL

全表：

SELECT year_id,month_id,day_id,SUM(sales_value)

FROM sales_tab

GROUP BY ROLLUP(year_id,month_id,day_id);

2>CUBE(a,b,c)：CUBE分组方式是全方位的。分组方式是2的参数个数次方：

(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、(c)，(null,null,null)；

全表：

SELECT year_id,month_id,day_id,SUM(sales_value)

FROM sales_tab

GROUP BY CUBE(year_id,month_id,day_id)

ORDER BY year_id,month_id,day_id;

3>查看每月与每年营业额。GROUPING SETS，允许指定不同的分组方式，然后将这些分组统计的结果并在一个结果集中。

SELECT year_id,month_id,SUM(sales_value)

FROM sales_tab

GROUP BY GROUPING SETS((year_id,month_id),year_id)

ORDER BY year_id,month_id;

day05

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

视图：

1>视图是数据库对象之一，数据库对象有：表，视图，索引，序列。

2>视图在SQL中体现的角色与表相同，但是并非真实的表，它不包含任何数据。它只对应一个查询语句的结果集。所以查询一张视图时，会先执行该视图对应的查询语句，得到结果集，然后在根据这个

结果集进行实际的查询工作。

CREATE VIEW v_emp_dept10

AS

SELECT empno, ename, sal*12, deptno

FROM emp

WHERE deptno = 10;

SELECT * FROM v_emp_dept10;

DESC v_emp_dept10;

3>创建视图为字段起别名。当视图对应的查询语句中字段含有函数或者表达式时，该字段必须给别名。

CREATE OR REPLACE VIEW v_emp_dept10

AS

SELECT empno id,ename name , sal salary,deptno

FROM emp

WHERE deptno = 10;

4>对视图进行DML操作。仅能对简单视图进行DML操作，复杂视图不允许进行DML操作。简单视图:视图数据来自单一的一张表且不含有函数或表达式。复杂视图:查询语句含有函数，表达式，分组等。连接视图:视图数据来自多张表。算作复杂视图的一种。

5>对简单视图进行DML虽然可以，但是也要注意不能违反基表的约束条件。基表:视图数据来源的表。对视图进行DML操作就是对基表进行DML操作。

INSERT INTO v_emp_dept10

(id,name,salary,deptno)

VALUES

(1001,'JACK',5000,10);

UPDATE v_emp_dept10 SET salary=9000

WHERE id=1001;

6>对视图进行某些DML操作，可能导致视图自身对该记录不可见，但是依然能影响基表记录，这种操作是对基表的"污染"。

INSERT INTO v_emp_dept10

(id,name,salary,deptno)

VALUES

(1002,'ROSE',5000,20);

7>DELETE不会出现该问题。由于删除不掉视图数据，所以也不会对基表数据删除。

DELETE FROM v_emp_dept10

WHERE deptno=20;

8>对视图添加检查选项，来避免对基表污染。添加了检查选项的视图要求对视图进行DML操作后视图必须对其可控。INSERT后视图必须可见，UPDATE后视图也必须对其可见。DELETE没有该问题。

CREATE OR REPLACE VIEW v_emp_dept10

AS

SELECT empno id,ename name ,sal salary,deptno

FROM emp

WHERE deptno = 10

WITH CHECK OPTION;

9>对视图添加只读选项，添加后，该视图不能进行DML操作。

CREATE OR REPLACE VIEW v_emp_dept10

AS

SELECT empno id,ename name ,sal salary,deptno

FROM emp

WHERE deptno = 10

WITH READ ONLY;

10>数据字典：数据字典(user_objects、user_views、user_update_conlumns)也是一系列的表，这些表中的数据由数据库自身维护。是用来记录用户曾经创建过的数据库对象的清单。当需要了解时，可以查看这些数据字典。

11>user_objects：记录了所有数据库对象。查看所有创建过的视图：

SELECT object_name

FROM user_objects

WHERE object_type = 'VIEW';

12>记录用户创建的所有表的数据字典。

SELECT table_name

FROM user_tables;

13>记录用户创建的所有视图的数据字典。

SELECT view_name,text

FROM user_views;

14>创建复杂视图：创建一个部门工资情况的视图。

CREATE VIEW v_salInfo

AS

SELECT e.deptno,d.dname

AVG(e.sal) avg_sal,

SUM(e.sal) sum_sal,

MAX(e.sal) max_sal,

MIN(e.sal) min_sal,

FROM emp e,dept d

WHERE e.deptno=d.deptno

GROUP BY e.deptno,d.dname;

15>查看比自己所在部门平均工资高。

SELECT e.ename,e.sal,e.deptno

FROM emp e,v_salInfo v

WHERE e.deptno=v.deptno AND e.sal>v.avg_sal;

16>删除视图：DROP VIEW v_emp_dept10

17>删除视图并不会对基表有任何影响。

序列：

1>另一个数据库对象用来生成一系列数字的。通常序列生成的数字是为某张表的主键字段(id)提供值。

CREATE SEQUENCE seq_emp_empno

START WITH 100

INCREMENT BY 10;

2>序列支持两个伪列：

NEXTVAL:使序列生成下一个数字，新创建的序列第一次生成是START WITH指定的数字，以后就是上次生成的数字加上步长得到的。序列不能后退，所以生成新数字后就不能得到以前的数字了。

CURRVAL:返回序列最后生成的数字。新创建的序列至少调用一次NEXTVAL以后才可以使CURRVAL。

SELECT seq_emp_empno.NEXTVAL

FROM dual;

SELECT seq_emp_empno.CURRVAL

FROM dual;

INSERT INTO emp

(empno,ename,job,sal,deptno)

VALUES

(seq_emp_empno.NEXTVAL,'rose','CLERK',1000,10);

3>删除序列：DROP SEQUENCE seq_emp_empno。

索引：

1>索引是一种允许直接访问数据表中某一数据行的树形结构，为了提高查询效率而引入，是独立于表的对象，可以存放在与表不同的表空间(TABLESPACE)中。

2>索引中存有索引关键字和指向表中数据的指针(地址)。

3>索引一旦建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引，table表示表明。

4>索引是一种提高查询效率的机制。

5>创建索引语法：可以建立单列索引或复合索引，unique表示唯一索引。

CREATE [UNIQUE] INDEX index_name

ON table(column[,column...]);

6>复合索引也叫多列索引，是基于多个列的索引。

7>如果经常在索引列上执行DML操作，需要定期重建索引，提高索引的空间利用率。修改索引的语法:

ALTER INDEX index_name REBUILD;

8>当一个表上有不合理的索引，会导致操作性能下降，删除索引的语法：

DROP INDEX index_name;

9>为经常出现在WHERE子句中的列建立所索引。

10>为经常出现在ORDER BY、DISTINCT后面的字段建立索引。如果建立的复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致。

11>为经常作为表的连接条件的列上建立索引。

12>不要在经常做DML操作的表上建立索引。

13>不要在小表上建立索引。

14>限制表上的索引条数目，索引并不是越多越好。

15>删除很少被使用的、不合理的索引。

约束：

1>约束(CONSTRAINT)的全称是约束条件，也称作完整性约束条件。

2>约束条件包括：

- 非空约束(Not Null)，简称NN；

- 唯一性约束(Unique)，简称UK；

- 主键约束(Primary Key)，简称PK；

- 外键约束(Foreign Key)，简称FK；

- 检查约束(Check)，简称CK；

3>NOT NULL非空约束：非空约束也是列级约束，只能在修改表中某个字段的同时修改该约束。

4>修改表时添加非空约束：

ALTER TABLE employees

MODIFY(eid NUMBER(6) NOT NULL);

5>取消非空约束：

ALTER TABLE employees

MODIFY(eid NUMBER(6) NULL);

唯一性约束：

1>UNIQUE：唯一性约束可以保证指定字段的值在整张表中没有重复的，NULL除外。当给表中的某个列定义了唯一性约束，该列的值不允许重复，但允许时NULL值。

2>添加唯一性约束：

CREATE TABLE employees1 (

eid NUMBER(6) UNIQUE,

name VARCHAR2(30),

email VARCHAR2(50),

salary NUMBER(7, 2),

hiredate DATE,

CONSTRAINT employees_email_uk UNIQUE(email)

);

3>在建表之后添加唯一性约束条件：

ALTER TABLE employees1

ADD CONSTRAINT employees_name_uk UNIQUE(name);

主键约束：

1>每张表通常都要有主键(id)，该字段的值在整张表中是非空且唯一的。这样才能用主键的值唯一确定表中的一条数据。

2>主键字段可以时单字段或多字段组合，即：在主键约束下的单字段或多字段组合上不允许有空值，也不允许有重复值。

3>主键选取原则：

- 主键应对系统无意义的数据；

- 永远都不要更新主键，让主键除了唯一标识一行之外，再无其他的用途；

- 主键不应该包含动态变化的数据，如时间戳；

- 主键应自动生成，不要人为干预，以免使它带有除了唯一标识一行以外的意义；

- 主键尽量建立在单列上。

4>在建表时添加主键约束条件：

CREATE TABLE employees (

eid NUMBER(6) PRIMARY KEY,

name VARCHAR2(30),

email VARCHAR2(50),

salary NUMBER(7, 2),

hiredate DATE

);

5>建表后创建主键约束条件，并定义约束条件名称：

CREATE TABLE employees (

eid NUMBER(6),

name VARCHAR2(30),

email VARCHAR2(50),

salary NUMBER(7, 2),

hiredate DATE

);

INSERT TABLE employees

ADD CONSTRAINT employees_eid_pk PRIMARY KEY(eid);

外键约束：

1>外键约束条件定义在两张表的字段或一个表的两个字段上，用于保证相关两个字段的关系。

2>先建表，在建表后建立外键约束条件：

CREATE TABLE employees (

eid NUMBER(6),

name VARCHAR2(30),

email VARCHAR2(50),

salary NUMBER(7, 2),

hiredate DATE

);

ALTER TABLE employees

ADD CONSTRAINT employees_deptno_fk

FOREIGN KEY(deptno) REFERENCES dept(deptno);

3>如果在一个频繁DML操作的表上建立外键，每次DML操作，都将导致数据库自动对外键所关联的对应表做检查，产生开销，如果已在程序中控制逻辑，这些判断将添加额外负担，可以省去。

检查约束：

1>检查(Check)约束条件用来强制在字段上的每个值都要满足Check中定义的条件。

2>当定义了Check约束的列新增或修改数据时，数据必须符合Check约束中定义的条件。

3>添加检查约束：

ALTER TABLE employees

ADD CONSTRAINT employees_salary_check

CHECK(salary>2000);

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