【基础系列十九】--Oracle

前言

本文主要是MySQL和Oracel基础操作及一些小例子，大佬可直接跳过。

DBMS-数据库管理系统

数据库管理系统的特点：
	1. 客户端--服务端。
	2. 负责数据的储存，检索，管理，处理。
	3. 主要分为有：
	    1. 关系型数据库：oracle mysql sql-server db2 sysbases
	    2. 非关系型数据库:MongoDB

SQL语句（ORACLE）

SQL是结构化查询语句，操作数据库需要向数据库发送SQL语句，数据库会理解SQL语句的含义并执行。SQL语句不区分大小写，但应当养成一个好习惯，通常将关键字与非关键字用大小写区分开。

SQL执行顺序

(1)from 
(3) join 
(2) on 
(4) where 
(5)group by(开始使用select中的别名，后面的语句中都可以使用)
(6) avg,sum.... 
(7)having 
(8) select 
(9) distinct 
(10) order by 
(11) limit

FROM 子句：执行顺序为从后往前、从右到左。数据量较少的表尽量放在后面。
WHERE子句：执行顺序为自下而上、从右到左。将能过滤掉最大数量记录的条件写在WHERE 子句的最右。
GROUP BY：执行顺序从左往右分组，最好在GROUP BY前使用WHERE将不需要的记录在GROUP BY之前过滤掉。
HAVING 子句：消耗资源。尽量避免使用，HAVING 会在检索出所有记录之后才对结果集进行过滤，需要排序等操作。
SELECT子句：少用*号，尽量取字段名称。ORACLE 在解析的过程中, 通过查询数据字典将*号依次转换成所有的列名, 消耗时间。

DDL(数据定义语言)

用来操作数据库对象数据库对象:表，视图，索引，序列

创建表
CREATE TABLE emp(
    id Number(4),
    name varchar2(20),
    gender char(1),
    birth DATE,
    salary Number(6,2),
    job VARCHAR2(30),
    deptno NUMBER(2)
);

查看表结构
DESC emp;

删除表
DROP TABLE emp;

修改表名
RENAME emp TO employee
修改表结构
    添加新字段:新字段只能追加到表的最后一行，不能再现有字段中间插入新行

 为该表增加新字段：hiredate 数据类型为DATE

  ALTER TABLE emp
  ADD(hiredate DATE)

  删除表字段

  ALTER TABLE emp
  DROP(hiredate)

  修改表字段类型
      可以修改数据字段的类型，长度，添加约束或者指定默认值。若表中已经存在数据，不能修改数据类型，长度尽可能只增不减，否则可能会修改失败。
  ALTER TABLE emp
  MODIFY(job VARCHAR(20) )

字段默认值，数据库无论什么类型，默认值都是NULL，可以使用DEFAULT为字段指定默认值。

创建表的时候指定默认值
CREATE TABLE emp(
    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 emp(
    id NUMBER(4) NOT NULL,
    name VARCHAR2(20),
    gender CHAR(1) NOT NULL,
    bith DATE,
    salary NUMBER(6,2)  
)

DML(数据操作语言)

用来操作表中数据，用来 增 删 改 表中的数据。DML总是伴随着TCL的。

INSERT INTO 语句：向表中插入数据

INSERT INTO emp(id,name,gender,birth,salaty) VALUES(1,"张三","男",SYSDATE,5000.2);
插入日期类的时候，建议用转换函数TO_DATE将指定字符串按照指定格式转换为DATE类型。
INSERT INTO emp(id,name,bith) VALUES(2,"啦",TO_DATE("1998-8-12","yyyy-MM-dd"));

UPDATE 语句：注意修改语句一般要添加WHERE语句，否则为全更新。
UPDATE emp SET salary=3000,gender="男" WHERE name="啦";

DELETE FROM emp语句：删除表中的数据该语句一定要慎用，一旦删除就不能恢复，一定要注意WHERE条件。

DELETE FROM emp WHERE gender="男";

TCL(事务控制语言):维护数据操作一致性
TCL维护DML操作。

DQL(数据查询语言)

查询表中数据

字段的别名
SELECT子句中查询的内容若是函数或者表达式，那么
在结果集中对应的该字段的名字就是这个函数或表达式，
可读性会变得比较差，为此可以为这样的字段添加别名。
这样在结果集中该字段的名字就是这个字段的别名。

别名不区分大小写，若希望区分大小写或者含有空格，那么
需要使用双引号将别名括起来。
SELECT ename name,sal*12 "salary"
FROM emp

查询条件

AND,OR可以连接多个条件，但是需要注意的是AND的优先级是高于OR的。所以可以通过括号提高OR的优先级。
SELECT ename,job,sal
FROM emp
WHERE sal>1000 
AND (job='SALESMAN'
OR job='CLERK')

IN(LIST)与NOT IN(LIST)判断在列表中或不在列表中。IN,NOT IN常用在子查询中。
SELECT ename, job FROM emp  
WHERE job IN ('MANAGER', 'CLERK');

SELECT ename, job FROM emp 
WHERE deptno NOT IN (10, 20);

BETWEEN...AND...判断在一个区间范围内
SELECT ename,sal,job
FROM emp
WHERE sal BETWEEN 1500 AND 3000

DISTINCT关键字：DISTINCT可以对结果集中指定字段的重复记录去除
SELECT DISTINCT job FROM emp

DISTINCT对多个字段去重，去重不再保证每个字段一定没有重复值，去重的原则是这些字段值的组合没有重复记录。
SELECT DISTINCT job,deptno FROM emp

ORDER BY

ORDER BY子句：ORDER BY子句是对结果集按照指定的字段的值进行升序或者降序排列。ORDER BY 支持ASC,DESCASC，为升序，从小到大，不写默认就是升序DESC为降序。需要注意，ORDER BY只能定义在DQL的最后一个子句上。
SELECT ename,job,sal
FROM emp
ORDER BY sal DESC

ORDER BY按照多个字段排序时，排序存在优先级首先将结果集按照第一个字段的排序方式进行排序，当第一个字段有重复值的时候，第一个字段值相同的记录之间再按照第二个字段的方式排序，依此类推。

SELECT ename,deptno,sal
FROM emp
ORDER BY deptno DESC,sal DESC

聚合函数

聚合函数，又称为:多行函数，分组函数聚合函数是用来对结果集进行统计的。其中有4个针对值的统计：MAX,MIN,AVG,SUM 还有一个是针对记录数的统计:COUNT
查看公司的最高工资?
SELECT MAX(sal)FROM emp

SELECT
 MAX(sal),MIN(sal),SUM(sal),AVG(sal) 
FROM emp

COUNT是统计指定字段不为NULL的记录共多少条?
查看公司员工人数:
SELECT COUNT(ename) FROM emp

SELECT COUNT(*) FROM emp
SELECT COUNT(1) FROM emp

聚合函数忽略NULL值
SELECT COUNT(comm) FROM emp
SELECT SUM(comm),AVG(comm) FROM emp

SELECT AVG(NVL(comm,0)) FROM emp

分组

分组：GROUP BY子句可以将结果集按照指定的字段值一样的记录进行分组，配合聚合函数可以进行组内统计的工作。
查看每个部门的平均工资?

SELECT AVG(sal),deptno
FROM emp
GROUP BY deptno

当SELECT中含有聚合函数时，凡不在聚合函数中的单独字段，都需要出现在GROUP BY子句中，GROUP BY按照多个字段分组的原则:这些字段值都一样的记录被划分为一组。

查看同部门同职位的员工各多少人?
SELECT COUNT(*),deptno,job
FROM emp
GROUP BY deptno,job


查看每个部门的平均工资，前提是该部门的
平均工资高于2000
SELECT AVG(sal),deptno
FROM emp
WHERE AVG(sal)>2000
GROUP BY deptno
上述SQL会报错，WHERE中是不能使用聚合函数的
WHERE不能使用聚合函数作为过滤条件，原因是过滤时机不对。
WHERE是在查询表中数据的时候逐行进行过滤，将满足条件的记录
形成结果集。
而使用聚合函数的结果进行过滤的前提是分组统计，分组是建立在
结果集上，而WHERE是用来形成结果集时的过滤。所以使用聚合
函数过滤应当是在WHERE之后进行的。

HAVING子句，HAVING子句可以使用聚合函数作为过滤条件。HAVING必须跟在GROUP BY子句后面(不定义GROUP BY不能单独定义HAVING)。HAVING是用来添加过滤条件以去除不满足条件的分组的
SELECT AVG(sal),deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal)>2000


SELECT MAX(sal),deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal)>2000

关联查询

关联查询
关联查询是指关联多张表联合查询记录，结果集中的字段可能来自
多张表。
关联查询的关键点在于连接条件，数据库是根据连接条件找到这些
表中记录之间的对应关系，从而从这些记录中获取要查询的字段来
构成结果集中的各个字段。

当某个字段在查询中发现多个表都存在时，要求必须指明该字段
所属哪张表。可以通过:表名.字段名  的形式标注，也可以为
表添加别名，然后通过:表别名.字段名 的形式标注，这样可以
降低编写SQL语句的复杂度，增加可读性。

SELECT e.ename,e.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno

关联查询中要求所有过滤条件必须与关联条件同时成立。
SELECT e.ename,e.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND d.loc='NEW YORK'

N张表关联查询，至少要有N-1个连接条件。

不指定连接条件或者连接条件无效时，会产生
笛卡尔积，这通常是一个无意义的结果集，应当
避免。

SELECT e.ename,d.dname
FROM emp e,dept d

内连接

内连接，内连接也是关联查询的一种
SELECT e.ename,d.dname
FROM emp e JOIN dept d
ON e.deptno=d.deptno
WHERE d.loc='NEW YORK'

关联查询中，不满足连接条件的记录不会被查询出来。

外连接

外连接在进行关联查询时，除了可以将满足连接条件的记录
查询出来之外，还可以将不满足连接条件的记录也查询出来。
外连接分为:左外连接，右外连接，全外连接。

左外连接

以JOIN左侧表作为驱动表，该表中的所有记录都会
查询出来，当某条记录不满足连接条件时，结果集中该条记录
中来自JOIN右侧表的字段的值全部为NULL。

SELECT e.ename,e.sal,e.deptno,d.dname,d.loc
FROM emp e FULL OUTER JOIN dept d
ON e.deptno=d.deptno

自连接

当前表的一条记录可以对应当前表自己的多条记录。
自连接的设计是为了解决数据属性相同，但是数据间又存在
上下级关系的树状结构数据时使用

查看每个员工以及其上司的名字?

SELECT e.ename,m.ename
FROM emp e,emp m
WHERE e.mgr=m.empno

查看SMITH上司在哪个城市工作?

SELECT e.ename,m.ename,d.loc
FROM emp e JOIN emp m
ON e.mgr=m.empno
JOIN dept d
ON m.deptno = d.deptno
WHERE e.ename='SMITH'
查看平均工资高于2000的那些部门名字以及所在城市?

SELECT AVG(e.sal),d.dname,d.loc
FROM emp e JOIN dept d
ON e.deptno=d.deptno
GROUP BY d.dname,d.loc
HAVING AVG(e.sal)>2000
在NEW YORK工作的员工有多少人?

SELECT COUNT(1)
FROM emp e JOIN dept d
ON e.deptno=d.deptno
WHERE d.loc='NEW YORK'
在DALLAS工作的员工的平均工资是多少?

SELECT AVG(e.sal)
FROM emp e JOIN dept d
ON e.deptno=d.deptno
WHERE d.loc='DALLAS'
查看谁的工资高于CLARK?

SELECT ename,sal
FROM emp
WHERE sal>(SELECT sal FROM emp
           WHERE ename='CLARK')

复制表

DDL中使用子查询，可以将一个子查询的结果集当做一张表快速创建出来。
CREATE TABLE employee
AS
SELECT e.empno,e.ename,e.sal,e.job,
       e.deptno,d.dname,d.loc
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno=d.deptno

单行单列子查询

单列子查询常用在过滤条件中
SELECT ename,deptno
FROM emp
WHERE deptno IN(SELECT deptno FROM emp
                WHERE job='SALESMAN')
                
查看比职位是CLERK和SALESMAN工资都高的员工?

SELECT ename,sal
FROM emp
WHERE sal>ALL(SELECT sal FROM emp
              WHERE job IN ('CLERK','SALESMAN'))

多行多列子查询

多列子查询常当做表使用。
查看每个部门最高工资是谁?列出该员工名字，职位，工资，部门号
SELECT e.ename,e.sal,e.deptno
FROM emp e,(SELECT AVG(sal) avg_sal,deptno
            FROM emp
            GROUP BY deptno) t
WHERE e.deptno=t.deptno
AND e.sal>t.avg_sal

多行单列子查询

多行单列子查询，在进行过滤判断是要配合IN,ANY,ALL使用

分页查询

将一个查询语查询的数据分批分段查询出来。这样做的好处在于，当一个查询语句可以查询的结果集非常大时，有效的减少网络传输的数据量，提高响应速度，降低系统开销。

ROWNUM

ROWNUM是一个伪列，实际不存在于任何表中，但是每张表都 可以查询该字段，该字段的值为查询的结果集中每条记录的行号。
该字段的值是伴随查询的过程中动态生成的，只要可以从表中查询 出一条记录，那么该字段的值就是该记录在当前结果集中的行号， 行号从1开始递增。

查看第6-10行的记录?

SELECT ROWNUN,ename,sal,job,deptno
FROM emp
WHERE ROWNUM >1

上面的查询得不到任何结果
在使用ROWNUM字段为结果集编行号的过程中，不要使用ROWNUM>1以上数字的判断为过滤条件，否则将得不到任何结果。
SELECT *
FROM (SELECT ROWNUM rn,ename,sal,job,deptno
      FROM emp)
WHERE rn BETWEEN 6 AND 10

查看公司工资的排名6-10:

SELECT *
FROM(SELECT ROWNUM rn,t.*
     FROM(SELECT ename,sal,job,deptno
          FROM emp
          ORDER BY sal DESC) t)
WHERE rn BETWEEN 6 AND 10

SELECT *
FROM(SELECT ROWNUM rn,t.*
           FROM(SELECT ename,sal,job,deptno
                      FROM emp
                      ORDER BY sal DESC) t
           WHERE ROWNUM<=10
           )
WHERE rn >=6


SELECT ename, job, sal,
     DECODE(job,  
           'MANAGER', sal * 1.2,
           'ANALYST', sal * 1.1,
           'SALESMAN', sal * 1.05,
           sal
     ) bonus
FROM emp


SELECT COUNT(1),job
FROM emp
GROUP BY job


SELECT COUNT(1),
 DECODE(job,
        'MANAGER','VIP',
        'ANALYST','VIP',
        'OTHER')
FROM emp
GROUP BY DECODE(job,
        'MANAGER','VIP',
        'ANALYST','VIP',
        'OTHER') 

排序函数

排序函数可以将结果集按照指定的字段分组，组内再按照指定的
字段排序，然后生成组内编号

ROW_NUMBER:生成组内连续且唯一的数字

查看每个部门的工资排名:
SELECT 
 ename,deptno,sal,
 ROW_NUMBER() OVER(
   PARTITION BY deptno
   ORDER BY sal DESC
 ) rank
FROM emp

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

查看每个部门的工资排名:
SELECT 
 ename,deptno,sal,
 RANK() OVER(
   PARTITION BY deptno
   ORDER BY sal DESC
 ) rank
FROM emp
DENSE_RANK:生成组内连续但不唯一的数字

查看每个部门的工资排名:
SELECT 
 ename,deptno,sal,
 DENSE_RANK() OVER(
   PARTITION BY deptno
   ORDER BY sal DESC
 ) rank
FROM emp

高级分组函数

查看每天营业额?

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
查看每月营业额?

SELECT year_id,month_id,SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id
ORDER BY year_id,month_id
查看每年营业额?

SELECT year_id,SUM(sales_value)
FROM sales_tab
GROUP BY year_id
ORDER BY year_id
总共的营业额？

SELECT SUM(sales_value)
FROM sales_tab

查看每天，每月，每年以及总共的营业额?

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)
ORDER BY year_id,month_id,day_id
2:CUBE:参数的每种组合都进行一次分组

分组次数为:2的参数个数次方
SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab
GROUP BY GROUPING SETS(
  (year_id,month_id,day_id),(year_id,month_id)
)
ORDER BY year_id,month_id,day_id
同部门同职位，同职位，以及所有员工的工资总和

SELECT SUM(sal),job,deptno
FROM emp
GROUP BY ROLLUP(job,deptno)

SELECT SUM(sal),job,deptno
FROM emp
GROUP BY CUBE(job,deptno)

分别查看同部门同职位和同职位的员工的工资总和

SELECT SUM(sal),job,deptno
FROM emp
GROUP BY GROUPING SETS((job,deptno),job)
查看公司最高工资的员工的名字以及所在部门名称

SELECT e.ename,e.sal,d.dname
FROM emp e JOIN dept d
ON e.deptno=d.deptno
WHERE e.sal=(SELECT MAX(sal) FROM emp)
查看每个部门的最高工资的员工名字

SELECT e.ename,e.sal,e.deptno
FROM emp e,(SELECT MAX(sal) max_sal,deptno
            FROM emp
            GROUP BY deptno) t
WHERE e.deptno=t.deptno
AND e.sal=t.max_sal
查看有下属的员工信息?

SELECT m.empno,m.ename
FROM emp m
WHERE EXISTS(
  SELECT * FROM emp e
  WHERE e.mgr = m.empno
)

视图

视图是数据库对象之一
视图在SQL语句中体现的角色与表一致，但它不是一张真实存在的表，
只是对应了一个查询语句的结果集。

创建视图:

CREATE VIEW v_emp_10
AS
SELECT empno, ename, sal, deptno 
FROM emp 
WHERE deptno = 10;
查看视图的结构

DESC v_emp_10
查看视图数据

SELECT * FROM v_emp_10
删除视图

DROP VIEW v_emp_10

当视图对应的子查询中含有函数或者表达式时，那么必须指定别名。

CREATE VIEW v_emp_10
AS
SELECT empno id, ename name, sal*12 sal, deptno 
FROM emp 
WHERE deptno = 10
视图根据对应的子查询分为:

简单视图，复杂视图，连接视图
简单视图:对应的子查询不含有函数，表达式，去重，分组。
复杂视图:不是简单视图的都是复杂视图
连接视图:对应的子查询有多表关联查询，连接视图算作复杂视图

简单视图可以进行DML操作，但是复杂视图不允许进行DML操作。


修改视图:视图的结构取决于对应的子查询，所以修改视图就是替换
对应的子查询。
CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id, ename name, sal, deptno 
FROM emp 
WHERE deptno = 10


对视图进行DML操作就是对视图数据来源的基础表进行的操作。虽然可以对简单视图进行DML操作，但是DML操作也不能违反基表的约束。
INSERT INTO v_emp_10
(id,name,sal,deptno)
VALUES
(1001,'JACK',2000,10)

SELECT * FROM v_emp_10
SELECT * FROM emp

UPDATE v_emp_10
SET sal=3000
WHERE id=1001

DELETE FROM v_emp_10
WHERE id=1001

会污是简单视图的不当DML操作染基础表数据:

对简单视图进行DML操作后，视图对基础表做对应操作，但是影响的数据视图对其
不可见时，就是对基表的数据污染。

INSERT INTO v_emp_10
(id,name,sal,deptno)
VALUES
(1001,'jack',2000,20)

UPDATE v_emp_10
SET deptno=20

SELECT * FROM v_emp_10
SELECT * FROM emp

为视图添加检查选项可以避免对基础表产生的数据污染。检查选项要求对视图进行的DML操作后该记录视图对其可见，否则不允许操作。

WITH CHECK OPTION

CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id, ename name, sal, deptno 
FROM emp 
WHERE deptno = 10
WITH CHECK OPTION

数据字典

数据字典是一系列的表，这些表的数据由数据库自行维护，记录的
是一些清单信息。方便随时查看。

USER_OBJECTS:记录用户创建的所有数据库对象
USER_VIEWS:记录用户创建的所有视图
USER_TABLES:记录用户创建的所有表


SELECT object_name 
FROM user_objects
WHERE object_type='VIEW'


创建一个含有各个部门工资情况的视图
CREATE VIEW v_emp_dept_sal
AS
SELECT MAX(e.sal) max_sal,
       MIN(e.sal) min_sal,
       AVG(e.sal) avg_sal,
       SUM(e.sal) sum_sal,
       d.deptno,d.dname
FROM emp e JOIN dept d
ON e.deptno = d.deptno
GROUP BY d.deptno,d.dname

序列

序列也是数据库对象之一，序列的作用是根据指定的规则生成一些列
数字。序列通常是为某张表的主键字段提供值使用。
主键:通常每张表都会有主键字段，该字段的值要求非空且唯一，使用
该字段来确定表中的每一条记录使用。

NEXTVAL:获取序列的下一个数字，第一次获取时返回的是START 
WITH指定的数字，以后则是最后获取的数字加上步长得到的。
NEXTVAL会导致序列发生步进，且不可回退。

CREATE SEQUENCE seq_emp_id
START WITH 1
INCREMENT BY 1

CURRVAL:
获取序列当前数字，即:最后一次生成的数字。新创建的
序列需要至少调用一次NEXTVAL以后才可以使用。CURRVAL不会
发生序列的步进。


SELECT seq_emp_id.CURRVAL
FROM dual


INSERT INTO emp
(empno,ename,sal,job,deptno)
VALUES
(seq_emp_id.NEXTVAL,'JACK',3000,'CLERK',10)

SELECT * FROM emp

删除序列
DROP SEQUENCE seq_emp_id

索引

索引是数据库对象之一，作用是加快查询效率
索引的创建与使用是数据库自行完成的。
经常出现在WHERE中的字段，或者去重，排序，关联条件的字段可以添加索引。

CREATE INDEX idx_emp_ename ON emp(ename);

约束

唯一性约束
唯一性约束要求该字段在整张表中每条记录的值都不允许重复，NULL除外。

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

INSERT INTO employees1
(eid,name,email)
VALUES
(NULL,'jack',NULL)

SELECT * FROM employees1

主键约束
主键约束要求字段非空且唯一，且一张表只能有一个主键约束
CREATE TABLE employees2 (
  eid NUMBER(6) PRIMARY KEY,
  name VARCHAR2(30),
  email VARCHAR2(50),
  salary NUMBER(7, 2),
  hiredate DATE
);

INSERT INTO employees2
(eid,name)
VALUES
(NULL,'jack')

下一篇：【基础系列二十】–JDBC