Oracal 入门基础知识

Oracal 入门基础知识

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 VARCHAR2(30),
  deptno NUMBER(2)
)

查看表结构

DESC employee_xxxx

数据库中，无论字段是什么类型，默认值都是NULL。当我们向某张表插入数据时，有的字段没有给定值时就会将该字段的默认值插入。在创建表时可以使用DEFAULT关键字为指定的字段单独定义默认值。

在数据库中，字符串的字面量使用的是单引号，并且字符串内容是区分大小写的

删除表:

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

修改表
1:修改表名

RENAME employee TO myemp
DESC myemp

2:修改表结构
2.1:添加字段
向表中添加字段只能被添加到表的最后，不能在现有的字段之间插入一个字段。

ALTER TABLE myemp
ADD(
  hiredate DATE DEFAULT SYSDATE
)

DESC myemp

2.2:删除表中现有字段

ALTER TABLE myemp
DROP(hiredate)

DESC myemp

2.3:修改现有字段对表结构的修改尽量在表中没有数据的时候进行。当表中已有数据时，修改字段应当注意:尽量不修改字段类型，若修改长度不应当缩小，因为缩小可能导致与该字段现有数据的长度冲突而导致失败

ALTER TABLE myemp
MODIFY(
 job VARCHAR2(40) DEFAULT 'CLERK'
)
DESC myemp

DML语句

1:INSERT语句
用于向表中插入数据插入数据时，可以不指定字段，若不指定这是全列插入，在VALUES后面要将所有
字段对应的值全部指定。建议指定字段，增加可读性。

INSERT INTO myemp
(id, name, job, salary) 
VALUES
(1001,'rose','PROGRAMMER',5500)

SELECT * FROM myemp

插入日期建议使用TO_DATE()函数进行

INSERT INTO myemp
(id,name,birth,deptno)
VALUES
(2,'jack',
 TO_DATE('1992-02-04',
         'YYYY-MM-DD'),20)

SELECT * FROM myemp

2:修改表中数据
UPDATE语句
在UPDATE语句中通常要使用WHERE添加过滤条件，这样只会将满足条件的记录进行修改，若不添加，则是整张表所有记录都修改!

UPDATE myemp
SET gender='F',id=1002
WHERE name='jack'

3:删除语句
DELETE语句
删除语句同样要使用WHERE，否则是清空表操作!

DELETE FROM myemp
WHERE name='rose'

SELECT * FROM myemp

DQL:数据查询语言

至少包含两部分:
1:SELECT子句
2:FROM子句
SELECT之后指定要查询的字段，可以使用"*"查询所有字段，也可以单独指定具体的字段，或者一个函数，表达式。FROM之后指定要查询的表

SELECT ename,job,sal,deptno 
FROM emp

查看函数或表达式的结果

SELECT ename,sal*12
FROM emp

字符串函数

1:CONCAT(char1,char2)
连接字符串

SELECT CONCAT(ename,sal)
FROM emp

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

可以使用"||"的形式连接字符串

SELECT 
 ename||':'||sal
FROM 
 emp

2：LENGTH(char)函数
统计指定字符串的长度对CHAR统计长度是固定的，通常对VARCHAR2类型字段统计。

SELECT ename, LENGTH(ename) 
FROM emp

3：UPPER(),LOWER(),INITCAP()
将字符串转换为全大写，全小写以及首字母大写

4.dual:伪表，当查询的内容与现实的任何一张表没有关系时，可以使用伪表来填充FROM子句，伪表只会查询出一条记录

SELECT 
  UPPER('helloworld'),
  LOWER('HELLOWORLD'),
  INITCAP('hello world')
FROM dual

5.TRIM,LTRIM,RTRIM
去除字符串两边的指定字符

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

SELECT 
 LTRIM('etstesteliteseseses','set')
FROM dual

6.LPAD,RPAD补位函数

SELECT 
  RPAD(sal,5,' ')
FROM 
  emp

7.SUBSTR(str,m,n)
截取当前字符串，从m指定的位置开始连续截取n个字符。n可以不指定，不指定则截取到字符串末尾，n若超过该字符串可以截取的最大值时，也是截取到字符串末尾。

需要注意：数据库下标从1开始！！！！

SELECT 
 SUBSTR('thinking in java',1,5)
FROM 
 dual

INSTR(char1,char2,n,m)

查看char2在char1中的位置
n:可以不指定，不指定默认值为1
表示从第几个字符开始检索
m:可以不指定，默认值为1
表示第几次出现

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

数值函数:

ROUND(n,m)
四舍五入，保留n的小数点后m位。若m不指定或为0则表示保留到整数为，负数则是十位以上的数字。

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

TRUNC()
参数与ROUND一致，截取数字，不进行四舍五入操作

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(n),FLOOR(n)
向上取整与向下取整

SELECT CEIL(45.678) FROM dual
SELECT FLOOR(45.678) FROM dual

日期相关函数

SYSDATE,SYSTIMESTAMP
这两个关键字表示两个内部函数返回的都是当前系统时间，只不过一个以DATE型返回，一个以时间戳
类型返回。

SELECT SYSDATE FROM dual
SELECT SYSTIMESTAMP FROM dual

TO_DATE函数
可以将一个字符串按照指定的日期格式解析为一个DATE类型的值日期格式字符串中出现了除英文，数字，符号以外的其他字符时都要使用双引号括起来。

SELECT
 TO_DATE('1992年08月05日 22:12:44',
         'YYYY"年"MM"月"DD"日" HH24:MI:SS' )
FROM 
 dual

日期可以比较大小与计算时间越晚的越大两个日期相减，差为相差的天数对一个日期加减一个数字等于加减指定的天数

SELECT SYSDATE+1 FROM dual

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

SELECT ename,SYSDATE-hiredate
FROM emp

查看自己到今天为止活了多少天？

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

查看82年以后入职的员工?

SELECT ename,hiredate
FROM emp
WHERE hiredate>TO_DATE('1982-01-01','YYYY-MM-DD')

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

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

RR在日期格式中也是2位数字表示的年，但是与YY不同，它可以在解析字符串的年时判定世纪

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

LAST_DAY(date)
返回给定日期所在月的月底查看每个员工入职所在月的月底？

SELECT ename,LAST_DAY(hiredate)
FROM emp

ADD_MONTHS(date,i)

对指定的日期加上指定的月若i为负数，则是减去。

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

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

MONTHS_BETWEEN(date1,date2)
计算两个日期之间相差的月

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

SELECT 
 ename,MONTHS_BETWEEN(SYSDATE,hiredate)
FROM 
 emp

NEXT_DAY(date,i)
返回给定日期第二天起一周之内的周几

SELECT NEXT_DAY(SYSDATE,4)
FROM dual

LEAST、GREATEST
求最小值与最大值,参数一个以上即可。
常用与日期，数字的求值

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

EXTRACT()

提取一个日期的指定时间分量对应的值
1982年入职的员工?

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

NULL值操作

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

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

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

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


UPDATE student
SET gender = NULL
WHERE id=1000

SELECT * FROM student

删除性别为NULL的记录
判断一个字段的值是否为空，要使用IS NULL或IS NOT NULL 不能使用"="判断NULL

DELETE FROM student
WHERE gender IS NOT NULL

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

NULL与字符串连接等于什么也没做
NULL与数字运算记过还为NULL

查看每个员工的收入情况:

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

空值函数:

NVL(f1,f2)
当f1为NULL时，函数返回f2,若不为NULL，函数返回f1自身。所以NVL函数的作用是将NULL值替换为非NULL值。
查看每个员工的收入情况:

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

查看每个人的奖金情况，有奖金的则显示为"有奖金",为NULL的则显示为"没有奖金"
NVL2(f1,f2,f3)
当f1不为NULL时，函数返回f2
当f1为NULL时，函数返回f3

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

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

字段的别名

当SELECT子句中查询的字段含有函数或者表达式时，查询出来的结果集对应的该字段名字就是这个函数或表达式，可读性差，所以可以指定别名。若希望别名区分大小写或者含有空着，那么需要使用双引号。

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

使用AND和OR连接多个过滤条件AND的优先级高于OR

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

LIKE用于模糊匹配字符串支持两个通配符:

%:0-多个字符
_:一个字符
查看第二个字母是A的员工?

SELECT ename,sal,job
FROM emp
WHERE ename LIKE '_A%'

IN(list)和NOT IN(list)
等于列表其一和不能等于列表中任何一个。
IN还常用于判断一个子查询的结果集

查询职位是MANAGER或者CLERK的员工

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

查询不是部门10或20的员工

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

ANY和ALL也是用于判断列表的，
配合>,>=,<,<=使用

ANY(list):大于列表之一
ALL(list):大于列表所有
<ANY(list):小于列表之一
<ALL(list):小于列表所有
列表不会使用确定值，通常是在
子查询中使用。

DISTINCT关键字

去除指定字段值重复的行(记录)DISTINCT必须紧跟在SELECT关键字之后，并且后面可以指定多个字段。若指定多个字段，则是对这些字段值的组合去重查看公司的部门都有哪些?

SELECT DISTINCT deptno FROM emp

多列去重:

SELECT DISTINCT job,deptno FROM emp

ORDER BY 排序结果集

ORDER BY 必须写在SELECT语句的最后一个子句上。可以按照给定的字段值的升序或者降序排列。
ASC:升序，可以不写，默认就是升序
DESC:降序。

查看公司的工资排名:

SELECT ename,sal
FROM emp
ORDER BY sal DESC

按照多字段排序:
排序具有优先级，先按照第一个字段排序规则对结果集排序，当第一个字段有相同值的时候才按照第二个字段排序，以此类推。

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

若排序的字段中含有NULL值，NULL被
认定为最大值

聚合函数

又名为分组函数，多行函数聚合函数用来统计使用。
聚合函数统计有:
求最大值，最小值，平均值，总和。
还有一个是统计记录总数。
统计最高工资与最低工资:

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

统计工资总和与平均工资:

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

查看20号部门的平均工资？

SELECT AVG(sal)
FROM emp
WHERE deptno=20

COUNT函数用于统计记录总数:

SELECT COUNT(sal)
FROM emp

通常统计一张表的记录数使用:

COUNT(*)
SELECT COUNT(*) FROM emp

聚合函数忽略NULL值
查看公司平均奖金?

SELECT AVG(comm) FROM emp

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

GROUP BY 分组

GROUP BY子句允许将结果集按照给定的字段值相同的记录进行分组，配合聚合函数可以进行更细致的统计工作。
GROUP BY子句后面若使用多个字段进行分组，那么分组原则为这些字段值的组合相同的记录看做一组。

查询时需要注意:

当SELECT中出现了聚合函数，那么凡不在
聚合函数中的其他字段必须出现在GROUP BY子句中，反过来不是必须的。

查看每个部门的平均工资?

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

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

同部门同职位的员工的平均工资各多少?

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

查看平均工资高于2000的部门的平均
工资具体是多少?

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

WHERE不能使用聚合函数作为过滤条件，原因在于过滤时机不对。 WHERE是在查询表的过程中决定哪条数据可以被查询出来(确定结果集数据量的)

HAVING子句

HAVING也是用于添加过滤条件的，它必须跟在GROUP BY子句之后使用。HAVING的过滤条件是用来确定结果集分组后那些分组满足条件。HAVING中可以使用聚合函数作为过滤条件。

查看平均工资高于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

关联查询

查询的结果集中的字段可能来自多张表。那么联合多张表查询数据就是关联查询。关联查询中必须要指定连接条件。否则会产生笛卡尔积，这通常是一个无意义的结果集，开销巨大，尽量避免出现。

查看每个员工的信息以及所在部门的信息？

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

不写连接条件出现笛卡尔积

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

过滤条件要与连接条件同时成立:
查看RESEARCH部门都有谁?

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

SELECT * FROM dep

NEW YORK工作的工资高于1500的员工?

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

内连接

内连接也能完成关联查询工作

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

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

使用内连接查询在NEW YORK工作的员工?

SELECT e.ename,e.sal,
       e.deptno,d.dname
FROM emp e JOIN dept d
ON e.deptno=d.deptno
WHERE d.loc = 'NEW YORK'

关联查询忽略不满足条件的记录。
外连接
外连接除了可以将满足连接条件的记录显示出来以外还可以将不满足连接条件的记录也显示出来，那么
不满足连接条件的记录中来自另一张表的字段值以NULL补充
外连接分为:
左外连接:以JOIN左侧的表为驱动表，该表的所有记录都要显示出来，那么当有不满足连接条件的记录时，该记录中来自JOIN右侧表中的字段值全部补NULL显示
右外连接
全外连接

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

自连接
自连接通常被用来解决数据相同，但是之间又存在上下级关系的"树状结构"使用。

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

SELECT e.ename,m.ename
FROM emp e JOIN emp m
ON e.mgr=m.empno

查看KING的下属?

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

子查询

子查询是一条嵌入在其他SQL语句中的查询语句。子查询的查询结果是为了其嵌套的SQL语句提供数据，以帮助该SQL执行实际操作。

查看比CLARK工资高的员工?

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

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

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

查看工资高于公司平均工资的员工?

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

分页查询

当查询结果集过大时，通常我们会使用分页查询。
分页就是对结果集分批的显示。
好处:
加快服务端的响应时间，减少网络传输。减少系统资源消耗。
分页在标准的SQL中没有定义，所以不同的数据库分页的SQL语句并不相同(方言)
ORACLE中是基于行号(ROWNUM)来完成分页查询的。
ROWNUM:伪列
该列不存在于任何一张表中，但是任何一张表都可以查询该字段，而该字段的值就是结果集中每一条数据的行号。该字段的值是在查询表的过程中产生的，每当可以从表中查询出一条记录，那么该字段就为这条记录编号从1开始，自动递增。

SELECT ROWNUM,ename,sal,job,deptno
FROM emp

在使用ROWNUM为结果集编行号时，不要在WHERE中使用ROWNUM做大于1以上的数字进行判断，否则查询不到任何数据。

取第6行-10行的记录

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

当有排序需求时，分页需要三次查询来完成，先排序，后编号，最后取范围

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


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

pageSize:每页显示的条目数 page:页数

start:(page-1)pageSize+1 end:pageSizepage

在DDL中使用子查询

可以基于一个查询结果集快速创建一张表。

创建一张表myemployee 该表包含empno,ename,job,sal,deptno,dname,loc这些字段。
并且该表中的数据与emp,dept数据一致

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

DESC myemployee
SELECT * FROM myemployee

DML中也可以使用子查询
删除myemployee中与CLARK相同部门的所有员工
DELETE FROM myemployee

WHERE deptno=(SELECT deptno
              FROM myemployee
              WHERE ename='CLARK')

子查询根据结果不同，分为单列和多列子查询
单列有细分为:单行单列，多行单列
单列子查询一般用在WHERE中作为过滤条件
多列子查询一般当做一张临时的表看待

查看和职位是SALESMAN相同部门的其他职位员工?

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

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

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

EXISTS关键字

EXISTS用在WHERE中作为过滤条件，其后跟一个子查询，只要该子查询可以查询出至少一条记录，那么EXISTS就返回TRUE.

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

不准用分组含数，求薪水的最高值

select distinct sal from emp 
where sal not in (
select distinct e1.sal from 
emp e1 join emp e2 on e1.sal<e2.sal
)

求平均薪水最高的部门的部门编号

select deptno ,avg_sal from
(select avg(sal) avg_sal,deptno from emp group by 
deptno) 
where avg_sal = (select max(avg_sal) from
  (select avg(sal) avg_sal,deptno from emp group by 
deptno) )

或者

select deptno ,avg_sal from 
(select avg(sal) avg_sal ,deptno from emp group by 
deptno) 
where avg_sal = (
  select max(avg(sal)) from emp group by deptno
);

求平均薪水的等级最低的部门的部门名称

select dname,t.deptno ,grade ,avg_sal from 
(
 select deptno,grade ,avg_sal  from 
	(select deptno ,avg(sal) avg_sal from emp group by deptno) t 
	join salgrade s on (t.avg_sal between s.losal and s.hisal)
	) t1
	join dept on (t1.deptno = dept.deptno)
where t1.grade = 
(
	select min(grade) from 
	(
		select deptno ,grade ,avg_sal from 
		(
			select deptno ,avg(sal) avg_sal from emp group by deptno 
		) t join salgrade s on (t.avg_sal between s.losal and s.hisal)
	)
);

conn sys/change_on_install as sysdba; 连接到超级管理员
grant create table ,creaet view to scott; 授权scott用户有创建表，
视图的权限
conn scott/tiger 连接到scott用户

create view view_dept_avg_sal as 
select deptno ,grade ,avg_sal from 
		(
			select deptno ,avg(sal) avg_sal from emp group by deptno 
		) t join salgrade s on (t.avg_sal between s.losal and s.hisal);

查看最低薪水高于30号部门最低薪水的部门最低薪水

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

多列子查询，通常当做一张临时的表使用。
查看比自己所在部门平均工资高的员工?

SELECT e.ename,e.sal,e.deptno
FROM emp e,
(SELECT AVG(sal) avg_sal,deptno
 FROM emp
 GROUP BY deptno) s
WHERE e.deptno=s.deptno
AND e.sal>s.avg_sal


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

DECODE函数，可以实现简单的分支结构。

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

将职位是MANAGER与ANALYST看作一组剩下职位看作另一组，分别统计人数?

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

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

排序函数

排序函数可以对结果集按照指定的字段分组，在组内按照指定的字段排序，然后生成一个组内的行号

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

总共营业额?

SELECT SUM(sales_value)
FROM sales_tab

高级分组函数用在GROUP BY子句中高级分组函数内置了几种分组原则，可以将这些分组方式全部进行并将所有结果并在一个结果集中显示。

高级分组函数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()
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

高级分组函数3:

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

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

CREATE VIEW v_emp_10
AS
SELECT empno, ename, sal, deptno 
FROM emp 
WHERE deptno = 10;

SELECT * FROM v_emp_10

创建一个显示所有部门平均工资的视图:

CREATE VIEW v_emp_avgsal
AS
SELECT AVG(sal) avg_sal, deptno
FROM emp
GROUP BY deptno

SELECT * FROM v_emp_avgsal

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

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

查看视图的结构

DESC v_emp_10

视图根据其对应的子查询分为:
简单视图:子查询的字段不含有函数，表达式，
去重，分组。
复杂视图:反之则是复杂视图
连接视图:视图中的数据来自多张表,连接视图
算作复杂视图的一种。
简单视图可以进行DML，但是复杂视图不行!

视图对应的子查询可以使用别名，这样视图对应的字段名即给定的别名，若子查询的字段是一个函数或者表达式，那么该字段必须给别名。

CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id,ename name, 
       sal salary,deptno 
FROM emp 
WHERE deptno = 10;

对视图进行DML
对视图进行DML，就是对视图的数据来源的基表进行的。
只能对简单视图进行，并且还不能违反基表的约束。

INSERT INTO v_emp_10
VALUES
(1001,'JACK',4000,10)

SELECT * FROM v_emp_10
SELECT * FROM emp

向视图插入数据可能会对基表造成数据污染，即:数据是通过视图插入进去的，但是视图对其不可见。

INSERT INTO v_emp_10
VALUES
(1002,'ROSE',3000,20)

SELECT * FROM v_emp_10
SELECT * FROM emp

更新也存在污染情况:
正常更新:

UPDATE v_emp_10
SET sal=6000
WHERE empno=1001
SELECT * FROM v_emp_10
SELECT * FROM emp

非正常：

UPDATE v_emp_10
SET deptno=20
SELECT * FROM v_emp_10
SELECT * FROM emp

删除不会出现上面的情况
正常删除：

DELETE FROM v_emp_10

SELECT * FROM v_emp_10
SELECT * FROM emp

非正常:

DELETE FROM v_emp_10
WHERE deptno=20

为视图添加检查选项，以避免视图对基表的污染。

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

INSERT INTO v_emp_10
VALUES
(1002,'ROSE',2000,20)

UPDATE v_emp_10
SET deptno=20

只读选项，视图不能进行DML操作。

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

在数据字典USER_OBJECTS中查询所有视图名称

SELECT object_name FROM user_objects 
WHERE object_type = 'VIEW';

SELECT object_name,object_type 
FROM user_objects 

查看视图的数据字典

SELECT view_name,text 
FROM user_views

查看表的数据字典

SELECT table_name FROM user_tables
SELECT * FROM v_emp_avgsal
CREATE VIEW v_emp_salary
AS
SELECT d.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 JOIN dept d
ON e.deptno = d.deptno
GROUP BY d.dname,d.deptno;

查看每个部门的最高工资是谁?

SELECT e.ename,e.sal,e.deptno
FROM emp e,v_emp_salary v
WHERE e.deptno=v.deptno
AND e.sal=v.max_sal

删除视图
删除视图不会影响基表数据。
DROP VIEW v_emp_10

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

CREATE SEQUENCE seq_emp_id
START WITH 1000
INCREMENT BY 1

序列支持两个伪列:
1:NEXTVAL,使序列生成下一个数字
2:CURRVAL,获取序列最后生成的数字

SELECT seq_emp_id.NEXTVAL
FROM dual

SELECT seq_emp_id.CURRVAL
FROM dual

序列名字不能与其他数据库对象重名，所以通常以seq开头。新创建的序列至少在执行一次NEXTVAL
后才可以使用CURRVAL获取最后生成的数字。NEXTVAL会导致步进，并且不可逆。
使用序列为emp的新数据提供主键值:

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

SELECT * FROM emp

删除序列:

DROP SEQUENCE seq_emp_id

非空约束

非空约束是一个列级约束。
所谓列级约束，指的是修改约束必须在修改对应字段时进行，创建表时也只能在定义列的过程中进行。

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

INSERT INTO employees1
(eid,name,email,salary)
VALUES
(4,NULL,NULL,5000)

SELECT * FROM  emp


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

创建新户

backup  scott 备份导出

exp

create user 
create user lb identified lb 
default tablespace users quota 10M on users;
 
grant create session,create table ,create view to lb ;
 
import the data