Oracle

Oracle

SQL

SQL语句本身不区分大小写,但是出于可读性的目的,我们通常会将SQL中的关键字全部大写,非关键字全部小写.

SELECT SYSDATE FROM dual

DDL 数据定义语言

DDL是对数据库对象进行操作的语言.
数据库对象包括:表,视图,索引,序列

创建表

CREATE TABLE employee(
  id NUMBER(4),
  name VARCHAR2(20),
  gender CHAR(1),
  birth DATE,
  salary NUMBER(6,2),
  job VARCHAR(30),
  deptno NUMBER(2)
)

查看表结构

DESC employee

删除表

DROP TABLE employee

注意：

• 数据库中所有数据类型的默认值都是NULL
• 在创建表的时候可以使用DEFAULT为某个字段单独指定一个默认值.
• 数据库中的字符串字面量是使用单引号的
• 虽然SQL语句本身不区分大小写,但是字符串的值是区分大小写的!

CREATE TABLE employee(
	id NUMBER(4),  
	name VARCHAR(20) NOT NULL,
	gender CHAR(1) DEFAULT 'M',
	birth DATE,    
	salary NUMBER(6,2), 
	job VARCHAR(30),
	deptno NUMBER(2)
)
DESC employee

修改表

1. 修改表名
2. 修改表结构

• 修改表名

RENAME employee TO myemp
DESC myemp

• 修改表结构

  1. 添加新的字段
  2. 修改现有字段
  3. 删除现有字段

• 添加新字段

ALTER TABLE myemp
ADD(
	hiredate DATE DEFAULT SYSDATE
)
DESC myemp

• 删除字段

ALTER TABLE myemp
DROP(hiredate)

• 修改字段

1. 可以修改字段的类型,长度,默认值,是否为空
2. 修改表结构都应当避免在表中有数据以后进行.
3. 若表中有数据,修改表中字段时尽量不要修改类型,若修改长度尽量增大避免缩小,否则可能导致失败.

ALTER TABLE myemp
MODIFY(
	job VARCHAR(40) DEFAULT 'CLERK'
)
Desc myemp

DML语句

DML是对表中的数据进行的操作
DML伴随事物控制(TCL)
DML包含操作:增,删,改.

INSERT语句

• 向表中插入数据

INSERT INTO myemp
(id,name,salary,deptno)
VALUES
(1,'jack',5000,10)
select * from myemp
COMMIT

• 使用自定义日期格式插入记录

INSERT INTO myemp
(id,name,job,birth)
VALUES
(1003,'donna','MANAGER',
TO_DATE('2009-09-01','YYYY-MM-DD')
)
SELECT * FROM myemp
COMMIT

UPDATE语句

修改表中数据
修改表中数据要使用WHERE添加过滤条件,这样才会只将满足条件的记录进行修改,否则是全表所有数据都修改

UPDATE myemp
SET salary=7000,gender='F',name='rose'
WHERE ID=1
SELECT * FROM myemp

DELETE语句

删除表中数据,删除数据通常也要添加WHERE语句来限定要删除数据的条件,否则就是清空表操作!

DELETE FROM myemp
WHERE name='rose'
SELECT * FROM myemp

TRUNCATE:删除表数据,保留表结构，没有回滚的余地.

SELECT语句

用于查询表中数据
SELECT子句后面跟的是要查询的字段,可以包括表中的具体字段,函数或者表达式.
FROM子句用来指定数据来源的表
WHERE子句用来添加过滤条件
这样做的结果是只将满足条件的记录查询出来

• 查看emp表中的数据

SELECT empno,ename,JOB,sal 
FROM emp

• SELECT子句中使用表达式
  查看每个员工的年薪?

SELECT ename,sal*12
FROM emp

字符串函数

• CONCAT()函数,用来连接字符串

SELECT CONCAT(ename,sal)
FROM emp

SELECT CONCAT(CONCAT(ename,','),sal)
FROM emp

SELECT ename||','||sal
FROM emp

• SUBSTR截取字符串
  数据库中的下标都是从1开始的

SELECT 
	SUBSTR('thinking in java',13,4)
FROM dual

第三个参数不指定则是截取到末尾,指定的长度
若超过实际可以截取的内容也是截取到末尾

SELECT
	SUBSTR('thinking in java',10)
FROM dual

截取的位置可以是负数,若是则表示从倒数第几个字符开始截取

SELECT
	SUBSTR('thinking in java',-4,4)
FROM dual

• LENGTH函数,查看字符串长度

SELECT ename,LENGTH(ename)
FROM emp

• UPPER,LOWER,INITCAP
  将字符串转换为全大写,全小写以及首字母大写
  对于INITCAP而言,可以使用空格隔开多个单词,那么每个单词首字母都会大写.

SELECT UPPER('helloword'),
       LOWER('HELLOWORD'),
       INITCAP('HELLO WORD')
FROM dual

伪表:dual
当查询的内容不和任何表中数据有关系时,可以使用伪表,伪表只会查询一条记录.

• TRIM,LTRIM,RTRIM
  去掉当前字符串中两边的指定重复字符,LTRIM仅去除左侧的,RTRIM则仅去除右侧的.

SELECT TRIM('e' FROM 'eeeliteeee') 
FROM dual

SELECT LTRIM('esrrerseseliteeee','esr')
FROM dual

• LPAD,RPAD补位函数

SELECT LPAD(sal,5,'$')
FROM emp

• INSTR(char1,char2[,n,m])函数
  查找char2在char1中的位置
  n为从第几个字符开始检索
  m为第几次出现
  n,m不写则默认都是1

SELECT
	INSTR('thinking in java','in',4,1)
FROM dual

数字函数

• ROUND(n,m)四舍五入

SELECT ROUND(45.68,2) FROM DUAL
SELECT ROUND(45.678,0) FROM DUAL
SELECT ROUND(55.678,-1) FROM DUAL
SELECT ROUND(55.678,-2) FROM DUAL

• TRUNC(n,m)截取数字

SELECT TRUNC(45.678,2) FROM DUAL
SELECT TRUNC(45.678,0) FROM DUAL
SELECT TRUNC(45.678,-1) FROM DUAL

• MOD(m,n)求余数

SELECT ename,sal,MOD(sal,1000)
FROM emp

• CEIL,FLOOR
  向上取整和向下取整

SELECT CEIL(45.678) FROM DUAL
SELECT FLOOR(45.678) FROM DUAL

查看jone员工的信息？

SELECT ename,sal,deptno
FROM emp
WHERE ename=UPPER('jone')

查看名字只有5个字母的员工的名字,工资?
部门号

SELECT ename,sal,deptno
FROM emp
WHERE LENGTH(ename)=5

查看第三个字母是A的员工信息?

SELECT ename,sal,deptno
FROM emp
WHERE INSTR(ename,'A')=3

SELECT ename,sal,deptno
FROM emp
WHERE SUBSTR(ename,3,1)='A'

日期类型

SYSDATE,SYSTIMESTAMP
SYSDATE对应数据库一个内部函数,该函数返回一个表示当前系统时间的DATE类型值.
SYSTIMESTAMP返回的是一个表示当前系统时间的时间戳类型的值

SELECT SYSDATE FROM dual
SELECT SYSTIMESTAMP FROM dual

• TO_DATE函数
  可以将字符串按照给定的日期格式解析为一个DATE类型的值

在日期格式字符串中凡不是英文,符号,数字的其他字符,都需要使用双引号括起来.

SELECT
	TO_DATE('2008年08月08日 20:08:08','YYYY"年"MM"月"DD"日" HH24:MI:SS')
FROM dual

• 日期的计算
  日期可以与一个数字进行加减法,这相当于加减指定的天
  两个日期可以进行减法,差为相差的天.

查看每个员工至今入职多少天了?

SELECT ename,SYSDATE-hiredate
FROM emp

输入自己生日:1992-08-02
查看到今天为止活了多少天?

SELECT SYSDATE-TO_DATE('1992-08-02','YYYY-MM-DD')
FROM dual

• TO_CHAR():可以将DATE按照给定的格式转换为字符串

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

SELECT 
	TO_CHAR(
		TO_DATE('49-07-12','RR-MM-DD'),'YYYY-MM-DD'
	)
FROM dual

• LAST_DAY(date)
  返回给定日期所在月的月底日期

查看当月底?

SELECT LAST_DAY(SYSDATE)
FROM dual

• ADD_MONTHS(date,i)
  对给定日期加上指定的月,若i为负数则是减去.

查看每个员工入职20周年纪念日

SELECT
	ename,ADD_MONTHS(hiredate,12*20)
FROM emp

• MONTHS_BETWEEN(date1,date2)
  计算两个日期之间相差的月,计算是根据date1-date2得到的

查看每个员工至今入职多少个月了？

SELECT 
	ename,MONTHS_BETWEEN(SYSDATE,hiredate)
FROM emp

• NEXT_DAY(date,i)
  返回给定日期的第二天开始一周之内的指定周几的日期
  i:1表示周日,2表示周一,以此类推

SELECT NEXT_DAY(SYSDATE,7)
FROM dual

• LEAST,GREATEST
  求最小值与最大值,除了日期外,常用的数字也可以比较大小

SELECT 
	LEAST(SYSDATE,TO_DATE('2008-08-05','YYYY-MM-DD'))
FROM dual

• EXTRACT()提取给定日期中指定时间分量的值

SELECT EXTRACT(YEAR FROM SYSDATE)
FROM dual

查看1980年入职的员工

SELECT ename,hiredate
FROM emp
WHERE EXTRACT(YEAR FROM hiredate)=1980

NULL

1. 插入NULL值

CREATE TABLE student
(id NUMBER(4),name CHAR(20),gender CHAR(1));
INSERT INTO student VALUES(1000,'李莫愁','F');
INSERT INTO student VALUES(1000,'林平之',NULL);
INSERT INTO student(id,name)VALUES(1002,'张无忌');
SELECT * FROM student

2. 更新为NULL

UPDATE student 
SET gender=NULL
WHERE id=1000

3. 判断字段的值是否为NULL
   判断要使用IS NULL或IS NOT NULL

DELETE FROM student
WHERE gender IS NULL

4. NULL值的运算操作
   NULL与任何数字运算结果还为NULL
   NULL与字符串拼接等于什么都没干

查看每个员工的收入:

SELECT ename,sal,comm,sal+comm
FROM emp

空值函数

• NVL(arg1,arg2)
  当arg1为NULL,函数返回arg2的值
  若不为NULL,则返回arg1本身.
  所以该函数的作用是将NULL值替换为一个非NULL值.

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

查看每个人的绩效情况,即:
有绩效的,显示为"有绩效"
绩效为NULL的,则显示为"没有绩效"

• NVL2(arg1,arg2,arg3)
  当arg1不为NULL,则函数返回arg2
  当arg1为NULL,则函数返回arg3
  该函数是根据一个值是否为NULL来返回两个不同结果.

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

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

DQL查询语句

• SELECT子句中可以使用函数或表达式,那么结果集中对应的该字段名就是这个函数或表达式,可读性差,为此可以为这样的字段添加别名,那么结果集会以这个别名作为该字段的名字别名本身不区分大小写,而且不能含有空格.若希望别名区分大小写或含有空格,那么可以在别名上使用双引号括起来.

SELECT ename,sal*12 AS "sal"
FROM emp

查看工资高于1000的职位是
CLERK和SALESMAN

SELECT ename,sal,job
FROM emp
WHERE sal>1000
AND (JOB='SALESMAN'
OR job='CLERK')

• AND的优先级高于OR,可以通过括号来提高OR的优先级

• LIKE用于模糊匹配字符串,支持两个通配符:
  _:单一的一个字符
  %:任意个字符

–查看名字第二个字母是A最后一个字母是N的?

SELECT ename
FROM emp
WHERE ename LIKE '_A%N'

• IN和NOT IN
  判断是否在列表中或不在列表中

SELECT ename,job
FROM emp
WHERE job IN('MANAGER','CLERK');

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

IN和NOT IN常用来判断子查询的结果

• BETWEEN…AND…
  判断在一个区间范围内

工资在1500到3000之间的员工

SELECT ename,sal
FROM emp
WHERE sal BETWEEN 1500 AND  3000

• ANY,ALL
  ANY和ALL是配合>,>=,<,<=一个列表使用的.
  >ANY(list):大于列表中最小的
  >ALL(list):大于列表中最大的
  <ANY(list):小于列表中最大的
  <ALL(list):小于列表中最小的
  ANY和ALL常用于子查询.

SELECT empno,ename,job,sal,deptno
FROM emp
WHERE sal>ANY(3500,4000,4500);

• 使用函数或者表达式作为过滤条件

SELECT ename,sal,job
FROM emp
WHERE ename = UPPER('ward');

SELECT ename,sal,job
FROM emp
WHERE sal*12>50000;

• DISTINCT关键字
  对结果集中指定字段值重复的记录进行去重
  查看公司有哪些职位?

SELECT DISTINCT job
FROM emp

多字段去重,是对这些字段值的组合进行去重

SELECT DISTINCT JOB,deptno
FROM emp

• 排序
  ORDER BY子句
  ORDER BY可以根据其后指定的字段对结果集
  按照该字段的值进行升序排序或者降序排序.
  ASC:升序,不写默认就是升序
  DESC:降序.

SELECT ename,sal
FROM emp
ORDER BY sal DESC

ORDER BY按照多个字段排序
ORDER BY首先按照第一个字段的排序方式对结果集进行排序,
当第一个字段有重复值时才会按照第二个字段排序,以此类推.
每个字段都可以单独指定排序方式.

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

排序的字段中含有NULL值,NULL被认作为最大值.

SELECT ename,comm
FROM emp
ORDER BY comm DESC

• 聚合函数
  聚合函数又叫多行函数,分组函数
  聚合函数是对结果集某些字段的值进行统计的.

MAX,MIN
求给定字段的最大值与最小值

查看公司的最高工资与最低工资是多少?

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

AVG,SUM
求平均值和总和

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

• COUNT函数
  COUNT函数不是对给定的字段的值进行统计的,而是对给定字段不为NULL的记录数统计的.
  实际上所有聚合函数都忽略NULL值统计.

SELECT COUNT(ename)
FROM emp

通常查看表的记录数可以使用COUNT(*)

SELECT COUNT(*) FROM emp

查看平均绩效

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

• 分组
  GROUP BY
  GROUP BY可以将结果集按照其后指定的字段值相同的记录看作一组,然后配合聚合函数进行更细分的统计工作.
  查看每个部门的平均工资?

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

查看每个职位的最高工资？

SELECT MAX(sal),job
FROM emp
GROUP BY job

GROUP BY也可以根据多个字段分组
分组原则为这几个字段值都相同的记录看做一组

查看同部门同职位的平均工资?

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

当SELECT子句中包含聚合函数时,那么凡不在聚合函数中的其他单独字段都必须出现在GROUP BY子句中,反过来则不是必须的.

查看部门的平均工资,前提是该部门的平均工资高于2000

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

查看平均工资高于2000的部门的最高工资和最低工资分别是多少?

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

WHERE中不能使用聚合函数作为过滤条件,原因是过滤时机不对.
WHERE是在数据库检索表中数据时,对数据逐条过滤以决定是否查询出该数据时使用的，所以WHERE用来确定结果集的数据.
使用聚合函数的结果作为过滤条件,那一定是数据从表中查询完毕
(WHERE在查询过程中发挥作用)得到结果集，并且分组完毕才进行聚合函数统计结果,
得到后才可以对分组进行过滤,由此可见,这个过滤时机是在WHERE之后进行的.
聚合函数的过滤条件要在HAVING子句中使用
HAVING必须跟在GROUP BY子句之后.HAVING是用来过滤分组的.

关联查询

从多张表中查询对应记录的信息
关联查询的重点在于这些表中的记录的对应关系,这个关系也称为连接条件

查看每个员工的名字和其所在部门的名字？

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

当两张表有同名字段时,SELECT子句中必须明确指定该字段来自哪张表.
在关联查询中,表名也可以添加别名,这样可以简化SELECT语句的复杂度.

关联查询要添加连接条件,否则会产生笛卡尔积
笛卡尔积通常是一个无意义的结果集,它的记录数是所有参与查询的表的记录数乘积的结果.
要避免出现,数据量大时极易出现内存溢出等现象.
N张表关联查询要有至少N-1个连接条件

查看在NEW YORK工作员工?

SELECT e.ename,d.deptno
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND d.loc='NEW YORK'

查看工资高于3000的员工的名字、工资、部门名以及所在地?

SELECT e.ename,e.sal,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND sal>3000

查看员工的名字以及所在部门的名字

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

内连接

内连接也是用来完成关联查询的

SELECT e.ename,d.dname
FROM emp e JOIN dept d
ON e.deptno=d.deptno
WHERE d.dname='SALES'

不满足连接条件的记录是不会在关联查询中被查询出来的.

外连接

外连接除了会将满足条件的记录查询出来之外,还会将不满足连接条件的记录也查询出来.
外连接分为:
左外连接:以JOIN左侧表作为驱动表(所有数据都会被查询出来),
那么当该表中的某条记录不满足连接条件时来自右侧表中的字段全部填NULL.
右外连接
全外连接

SELECT e.ename,d.dname
FROM emp e 
  LEFT|RIGHT|FULL OUTER JOIN 
    dept d
ON e.deptno=d.deptno

SELECT e.ename,d.dname
FROM emp e 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,m.deptno,d.loc
FROM emp e,emp m,dept d
WHERE e.mgr=m.empno
AND m.deptno=d.deptno
AND e.ename='SMITH'

SELECT e.ename,m.ename,m.deptno,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'

子查询

子查询是一条SELECT语句,但它是嵌套在其他SQL语句中的,
为的是给SQL提供数据以支持其执行操作.

查看谁的工资高于WARD?

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

查看与WARD同职位的员工?

SELECT ename,job FROM emp
WHERE job=(SELECT job FROM emp
           WHERE ename='WARD')

查看与WORD同部门的员工?

SELECT ename,deptno FROM emp
WHERE deptno=(SELECT deptno FROM emp
              WHERE ename='WARD')

在DDL中使用子查询

可以根据子查询的结果集快速创建一张表

创建表employee,表中字段为:
empno,ename,JOB,sal,deptno,dname,loc

CREATE TABLE employee
AS
SELECT e.empno,e.ename,e.job,e.sal,
       e.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno

DESC employee
SELECT * FROM employee

DROP TABLE employee

创建表时若子查询中的字段有别名则该表对应的字段就使用该别名,
当子查询中一个字段含有函数或表达式,那么该字段必须给别名.

CREATE TABLE employee
AS
SELECT e.empno id,e.ename name,e.job,e.sal*12 salary,
       e.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno(+)

SELECT name,salary
FROM employee

DML中使用子查询

将WARD所在部门的所有员工删除

DELETE FROM employee
WHERE deptno=(SELECT deptno FROM employee
              WHERE name='WARD')
SELECT * FROM employee

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

SELECT deptno,ename,sal
FROM emp e
WHERE sal>(SELECT AVG(sal) FROM emp)

子查询根据查询结果集的不同分为:
单行单列子查询:常用于过滤条件,可以配合=,>,>=,<,<=使用
多行单列子查询:常用于过滤条件,由于查询出多个值,在判断=时要用IN,判断>,>=等操作要配合ANY,ALL
多行多列子查询:常当做一张表看待.

查看与SALESMAN同部门的其他职位员工：

SELECT ename,job,deptno
FROM emp
WHERE deptno IN(SELECT deptno 
                FROM emp
                where job='SALESMAN')
AND job<>'SALESMAN'

查看比职位是CLERK和SALESMAN工资都高的员工

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

EXISTS关键字
EXISTS后面跟一个子查询,当该子查询可以查询出至少一条记录时,则WXISTS表达式成立并返回true

SELECT deptno,dname FROM dept d
WHERE 
  EXISTS(SELECT * FROM emp e
         WHERE e.deptno=d.deptno)

查看每个部门的最低薪水是多少?
前提是该部门的最低薪水要高于4号部门的最低薪水

SELECT MIN(sal),deptno
FROM emp
GROUP BY deptno
HAVING MIN(sal)>(SELECT MIN(sal)
                 FROM emp
                 WHERE deptno=4)

子查询在FROM子句中的使用

当一个子查询是多列子查询,通常将该子查询的结果集当做一张表看待并给予它二次查询.

查看比自己所在部门平均工资高的员工?

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

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

在SELECT子句中使用子查询,可以将查询的结果当做外层查询记录中的一个字段值显示.

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

分页查询

分页查询是将查询表中数据分段查询,而不是一次性将所有数据查询出来.
有时查询的数据量非常庞大,这会导致系统资源消耗大,响应速度慢,数据冗余严重.
为此当遇到这种情况时一般使用分页查询解决.
数据库基本都支持分页,但是不同数据库语法不同(方言).
ORACLE中的分页是基于伪列ROWNUM实现的.
ROWNUM不存在于任何一张表中,但是所有的表都可以查询该字段.
该字段的值是随着查询自动生成的,方式是:每当可以从表中查询出一条记录时,
该字段的值即为该条记录的行号,从1开始逐次递增.
在使用ROWNUM对结果集进行编号的查询过程中不能使用ROWNUM做>1以上的数字判断,
否则将查询不出任何数据.

SELECT *
FROM(SELECT ROWNUM rn,empno,ename,sal,job
     FROM emp)
WHERE rn BETWEEN 4 AND 6 

查看公司工资排名的2-5

SELECT *
FROM(SELECT ROWNUM rn,t.*
     FROM(SELECT empno,ename,sal,job
                FROM emp
                ORDER BY sal DESC) t)
WHERE rn BETWEEN 2 AND 5 

SELECT *
FROM(SELECT ROWNUM rn,t.*
     FROM(SELECT empno,ename,sal
          FROM emp
          ORDER BY sal DESC)t
    WHERE ROWNUM<=5)
WHERE rn>=2

计算区间公式
pageSize:每页显示的条目数
page:页数
star:(page-1)pageSize+1
end:pageSizepage

DECODE函数,可以实现分支效果的函数

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

SELECT ename,job,sal,
  CASE job WHEN 'CLERK' THEN sal*1.2
           WHEN 'SALESMAN' THEN sal*1.1
           ELSE sal END
  bonus
FROM emp

DECODE在GROUP BY分组中的应用可以将字段值不同的记录看做一组.
统计人数,将职位是"CLERK","SALESMAN"看做一组,其余职业看做另一组分别统计人数.

SELECT COUNT(*),DECODE(job,
                       'CLERK','OTHER',
                       'SALESMAN','OTHER',
                       'VIP')
FROM emp
GROUP BY DECODE(job,
                'CLERK','OTHER',
                'SALESMAN','OTHER',
                'VIP')

SELECT deptno,dname,loc
FROM dept
ORDER BY
  DECODE(dname,
         'OPERATIONS',1,
         'SALES',2,
         'RESEARCH',3)

排序函数

排序函数允许对结果集按照指定的字段分组
在组内再按照指定的字段排序,最终生成组内编号.

• ROW_NUMBER()函数生成组内连续且唯一的数字
  查看每个部门的工资排名?

SELECT ename,sal,deptno,
  ROW_NUMBER() OVER(
   PARTITION BY deptno
   ORDER BY sal DESC
  ) rank
FROM emp

• RANK函数,生成组内不连续也不唯一的数字,
  同组内排序字段值一样的记录,生成的数字也一样.

SELECT ename,sal,deptno,
  RANK() OVER(
   PARTITION BY deptno
   ORDER BY sal DESC
  ) rank
FROM emp

• DENSE_RANK函数生成组内连续但不唯一的数字.

SELECT ename,sal,deptno,
  DENSE_RANK() OVER(
   PARTITION BY deptno
   ORDER BY sal DESC
  ) rank
FROM emp

SELECT ename,job,sal FROM emp
WHERE job = 'MANAGER'
UNION 
SELECT ename,job,sal FROM emp
WHERE sal>2500

SELECT ename,job,sal FROM emp
WHERE job='MANAGER'
UNION ALL
SELECT ename,job,sal FROM emp
WHERE sal>2500

SELECT ename,job,sal FROM emp
WHERE job='MANAGER'
INTERSECT
SELECT ename,job,sal FROM emp
WHERE sal>2500

SELECT ename,job,sal FROM emp
WHERE job = 'MANAGER'
MINUS
SELECT ename,job,sal FROM emp
WHERE sal>=2500

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 year_id,month_id,day_id,sales_value
FROM sales_tab
ORDER BY year_id,month_id,day_id

查看每天营业额?

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

高级分组函数

高级分组函数用在GROUP BY子句中,每个高级分组函数都有一套分组策略.

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

• CUBE():每种组合分一次组
  分组次数:2的参数个数次方

GROUP BY CUBE(a,b,c)
abc
ab
bc
ac
a
b
c
全表

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

• GROUPING SETS:每个参数是一种分组方式,然后将这些分组统计后并在一个结果集显示.

仅查看每天与每月营业额?

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

视图

视图是数据库对象之一
所有数据库对象名字不能重复,所以视图名字一般是以"v_"开头

视图在SQL语句中体现的角色与表相同但是视图并不是一张真实存在的表,而只是对应一个SELECT语句的查询结果集,并将其当做表看待而已.
使用视图的目的是简化SQL语句的复杂度,重用子查询,限制数据访问.

创建视图

该视图包含的数据为1号部门的员工信息

CREATE VIEW v_emp_1
AS
SELECT empno,ename,sal,deptno
FROM emp
WHERE deptno=1

查看视图数据:

SELECT * FROM v_emp_1

视图对应的子查询中的字段若含有函数或者表达式,那么该字段必须指定别名.
当视图对应的子查询中放入字段使用了别名,那么视图中该字段就用别名来命名.

修改视图

由于视图仅对应一个SELECT语句,所以修改视图就是替换该SELECT语句而已.

CREATE OR REPLACE VIEW v_emp_1
AS
SELECT empno id,ename name,sal salary,deptno
FROM emp
WHERE deptno=1

SELECT * FROM v_emp_1
DESC v_emp_1

视图分为简单视图与复杂视图
简单视图:对应的子查询中不含有关联查询,查询的字段不包含函数,表达式等,没有分组,
没有去重.反之则是复杂视图.

对视图进行DML操作
仅能对简单视图进行DML操作.
对视图进行DML操作就是对视图数据来源的基础表进行的操作.

INSERT INTO v_emp_1
(id,name,salary,deptno)
VALUES
(1001,'JACK',20000,1)

SELECT * FROM V_emp_1
SELECT * FROM emp

UPDATE v_emp_1
SET salary=30000
WHERE id=1001

SELECT * FROM V_emp_1
SELECT * FROM emp

DELETE FROM v_emp_1
WHERE name='JACK'

SELECT * FROM V_emp_1
SELECT * FROM emp

对视图的DML操作就是对基表操作,那么操作不当可能对基表进行数据污染.

INSERT INTO v_emp_1
(id,name,salary,deptno)
VALUES
(1002,'ROSE',30000,2)

视图对ROSE不可见

SELECT * FROM V_emp_1
SELECT * FROM emp

更新同样存在更新后对数据不可控的情况

UPDATE v_emp_1
SET deptno=2

删除不会对基表产生数据污染

DELETE FROM v_emp_1
WHERE deptno=2

为视图添加检查选项,可以保证对视图的DML操作后视图对其可见,
否则不允许进行该DML操作,这样就避免了对基表进行数据污染.

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

为视图添加只读选项,那么该视图不允许进行DML操作.

CREATE OR REPLACE VIEW v_emp_1
AS
SELECT empno id,ename name,sal salary,deptno
FROM emp
WHERE deptno=1
WITH READ ONLY

SELECT object_name
FROM user_objects
WHERE object_type = 'VIEW'
AND object_name LIKE '%_luchunfang'

SELECT TEXT,view_name
FROM user_views

SELECT table_name
FROM user_tables

复杂视图

创建一个含有公司部门工资情况的视图,
内容为:部门编号,部门名称,部门的最高,最低,平均,以及工资总和信息.

CREATE VIEW v_dept_sal
AS
SELECT d.deptno,d.dname,
       MIN(e.sal) min_sal,
       MAX(e.sal) max_sal,
       AVG(e.sal) avg_sal,
       SUM(e.sal) sum_sal
FROM dept d,emp e
WHERE d.deptno=e.deptno
GROUP BY d.deptno,d.dname

SELECT * FROM v_dept_sal
DESC v_dept_sal

查看谁比自己所在部门平均工资高?

SELECT e.ename,e.sal,e.deptno
FROM emp e,v_dept_sal v
WHERE e.deptno=v.deptno
AND e.sal>v.avg_sal

删除视图

DROP VIEW v_emp_1

删除视图本身并不会影响基表数据.
但是删除视图数据会对应将基表数据删除.

序列

序列也是数据库对象之一.
作用是生成一系列数字.
序列常用于为某张表的主键字段提供值使用.

CREATE SEQUENCE seq_emp_id
START WITH 1
INCREMENT BY 1

序列支持两个伪劣:
NEXTVAL:获取序列下一个值
若是新创建的序列,那么第一次调用返回的是START WITH指定的值,
以后每次调用都会得到当前序列值加上步长后的数字.
NEXTVAL会导致序列发生步进,且序列不能回退.
CURRVAL:获取序列当前值,即:最后一次调用NEXTVAL后得到的值,
CURRVAL不会导致步进.但是新创建的序列至少调用一次NEXTVAL后才可使用CURRVAL.

SELECT seq_emp_id.NEXTVAL
FROM dual

SELECT seq_emp_id.CURRVAL
FROM dual

使用序列为EMP表中新插入的数据提供主键字段的值

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

SELECT * FROM emp

删除序列

DROP SEQUENCE seq_emp_id

索引
索引是数据库对象之一
索引是为了提高查询效率

索引的统计用于应用是数据库自动完成的
只要数据库认为可以使用某个已创建的索引时就会自动应用.

CREATE INDEX idx_emp_ename ON emp(ename)

CREATE INDEX idx_emp_job_sal ON emp(job,sal)

CREATE INDEX emp_ename_upper_idx ON emp(UPPER(ename))

重建索引

ALTER INDEX idx_emp_ename REBUILD

删除索引

DROP INDEX idx_emp_ename

CREATE TABLE employees(
  eid NUMBER(6),
  name VARCHAR(30) NOT NULL,
  salary NUMBER(7,2),
  hiredate DATE CONSTRAINT employees_hiredate_nn NOT NULL
)

ALTER TABLE employees
MODIFY (eid NUMBER(6) NOT NULL)

DESC employees

ALTER TABLE employees
MODIFY (eid NUMBER(6) NULL)

DESC employees

约束

唯一性约束
唯一性约束可以保证表中该字段的值任何一条记录都不可以重复,NULL除外.

CREATE TABLE employees1(
  eid NUMBER(6) UNIQUE,
  name VARCHAR2(30),
  email VARCHAR(50),
  salary NUMBER(7,2),
  hiredate DATE,
  CONSTRAINT employees_email_uk UNIQUE(email)
)

INSERT INTO employees1
(eid,name,email)
VALUES
(1,'JACK','JACK@QQ.COM')

INSERT INTO employees1
(eid,name,email)
VALUES
(2,'JACK2','JACK2@QQ.COM')

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

SELECT * FROM employees1

DELETE FROM employees1

ALTER TABLE employees1
ADD CONSTRAINT employees_name_uk UNIQUE(name)

主键约束

CREATE TABLE employees2(
  eid NUMBER(6) PRIMARY KEY,
  name VARCHAR(30),
  email VARCHAR(50),
  salary NUMBER(7,2),
  hiredate DATE
)

INSERT INTO employees2
(eid,name)
VALUES
(2,'JACK')

SELECT * FROM employees2

检查约束

ALTER TABLE employees2
ADD CONSTRAINT employees_salary_check
CHECK (salary>2000)

INSERT INTO employees2
(eid,name,salary)
VALUES
(1236,'donna noble',2500)

SELECT * FROM employees2

UPDATE employees2 SET salary = 1500
WHERE eid = 1236