oracle数据库笔记

SELECT SYSDATE FROM dual

SELECT SYSDATE FROM dual

SQL语句
SQL:结构化查询语句，是用来操作数据库的语言
所有的数据库都支持表示准的SQL语句
SQL语句包含:
DDL,DML,TCL,DQL,DCL这几类语句

DDL语句
DDL语句是用于增删改数据库对象的
数据库对象:表，视图，索引，序列

创建表:
CREATE TABLE employee_xxx(
id NUMBER(4),
name VARCHAR2(20),
gender CHAR(1),
birth DATE,
salary NUMBER(6,2),
job VARCHAR2(30),
deptno NUMBER(2)
)

查看表结构
DESC employee

删除表:
DROP TABLE employee

SQL语句是不区分大小写的，但是字符串的
值(直接量)是区分大小写的，字符串的直接量
是使用单引号括起来的。
数据库中所有数据类型的默认值都是NULL，
在创建表时，可以使用DEFAULT为字段单独
指定默认值。
CREATE TABLE employee(
id NUMBER(4),
name VARCHAR2(20),
gender CHAR(1) DEFAULT ‘M’,
birth DATE,
salary NUMBER(6,2) DEFAULT 5000,
job VARCHAR2(30) DEFAULT ‘CLERK’,
deptno NUMBER(2)
);

DESC employee

非空约束
当一个字段被NOT NULL修饰后，该字段
在任何情况下值不能为NULL。
CREATE TABLE employee(
id NUMBER(4),
name VARCHAR2(20) NOT NULL,
gender CHAR(1) DEFAULT ‘M’,
birth DATE,
salary NUMBER(6,2) DEFAULT 5000,
job VARCHAR2(30) DEFAULT ‘CLERK’,
deptno NUMBER(2)
);

非空约束可以在查看表结构中体现出来
DESC employee

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

修改表名:
RENAME old_name TO new_name

将employee表改名为myemp
RENAME employee TO myemp

DESC myemp

修改表结构:
1:添加新字段
向表myemp中添加字段hiredate

ALTER TABLE myemp
ADD(
hiredate DATE
)
DESC myemp

删除表中现有字段
将myemp表中的hiredate字段删除
ALTER TABLE myemp
DROP(hiredate)

修改表中现有字段
修改字段可以修改字段的类型，长度，默认值
非空约束。
但是表中若已经存在数据，那么修改字段的时候
尽量不修改类型，若修改长度尽量不要缩小，否
则可能导致修改失败。

ALTER TABLE myemp
MODIFY(
job VARCHAR2(40) DEFAULT ‘CLERK’
)

DESC myemp

DML语句
DML语句用来对表中数据进行相关操作，包括:
增，删，改。

1:插入数据
INSERT INTO myemp
(id,name,salary,deptno)
VALUES
(1,‘JACK’,3000,10)

SELECT * FROM myemp

插入数据时，忽略字段名则是全列插入
INSERT INTO myemp
VALUES
(2,‘ROSE’,‘F’,SYSDATE,5000,
‘MANAGER’,20)

插入日期时，使用TO_DATE函数
INSERT INTO myemp
(id,name,birth)
VALUES
(3,‘JACKSON’,
TO_DATE(‘1992-08-02’,‘YYYY-MM-DD’))

SELECT * FROM myemp

2:修改数据
UPDATE语句用于修改表中数据，需要使用
WHERE添加条件以修改满足条件的记录，若
不添加WHERE则是全表所有数据修改!

将ROSE的工资改为6000,部门号改为30
UPDATE myemp
SET salary=6000,deptno=30
WHERE name=‘ROSE’

3:删除表中数据
DELETE语句用于删除表中记录，通常需要
使用WHERE添加条件来删除满足条件的记录
若不添加WHERE是清空表操作!
DELETE FROM myemp
WHERE name=‘ROSE’

DQL语句
DQL语句用于查询数据库中的数据

DQL必须包含两个子句:
SELECT,FROM
SELECT子句用来指定要查询的字段，可以
是表中的字段，函数和表达式
FROM子句用来指定数据来源的表

查看emp表中的数据
SELECT * FROM emp

查看ename,job,sal,deptno
SELECT ename,job,sal,deptno
FROM emp

查看20号部门的员工信息?
DQL中也可以使用WHERE子句来添加过滤
条件，这样只会将满足条件的记录查询出来
SELECT ename,job,sal,deptno
FROM emp
WHERE deptno=20

SELECT子句中也可已使用函数或表达式
查看公司每个员工的年薪是多少?
SELECT ename,sal,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)
返回指定字符串的长度
SELECT ename,LENGTH(ename)
FROM emp

伪表:dual
伪表不是一张真是存在的表，当查询的内容
与任何表数据无关时，可以使用伪表。
SELECT SYSDATE FROM dual

3:UPPER,LOWER,INITCAP
将字符串转换为大写，小写，首字母大写
SELECT
UPPER(‘helloworld’),
LOWER(‘HELLOWORLD’),
INITCAP(‘HELLO WORLD’)
FROM dual

查看scott的信息?
SELECT ename,sal,job,deptno
FROM emp
WHERE ename=UPPER(‘scott’)

4:TRIM,LTRIM,RTRIM
去除字符串两端的指定字符
SELECT TRIM(‘e’ FROM ‘eeeliteee’)
FROM dual

SELECT LTRIM(‘eddsdsesliteee’,‘esd’)
FROM dual

SELECT RTRIM(‘eeeliteddsdses’,‘esd’)
FROM dual

5:LPAD,RPAD补位函数
将指定字符串显示指定长度，当不足
时补充若干个指定字符以达到该长度
SELECT ename,RPAD(sal,5,’$’)
FROM emp

6:SUBSTR(char,m[,n])
截取指定字符串，从m处开始连续截取n个字符
n若不指定或超过实际可截取的长度，则都
是截取到字符串末尾
m若为负数，则是从倒数位置开始截取
数据库中下标都从1开始
SELECT
SUBSTR(‘thinking in java’,-7,2)
FROM
dual

7:INSTR(char1,char2[,m[,n]])
查看char2在char1中的位置
m为从哪里开始查找，不写默认为1
n为第几次出现，不写默认为1
SELECT
INSTR(‘thinking in java’,‘in’,4,2)
FROM
dual

数字函数
1:ROUND(n,m)
四舍五入保留n小数点后m位
若m不写或0则表示保留到个位
若是负数则是保留到十位以上的数字

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

2:TRUNC函数
与ROUND参数意义一致，作用是截取数字。
SELECT TRUNC(45.678, 2) FROM DUAL
SELECT TRUNC(45.678, 0) FROM DUAL
SELECT TRUNC(55.678, -1) FROM DUAL

3:MOD(m,n)函数
求余数。n为0则直接返回m
SELECT ename,sal,MOD(sal,1000)
FROM emp

4:CEIL,FLOOR
向上取整与向下取整
SELECT CEIL(45.678) FROM DUAL; --46?
SELECT FLOOR(45.678) FROM DUAL;–45

日期类型
两个常用关键字
SYSDATE:对应数据库一个内置函数，返回
一个DATE类型数据，表示当前系统时间

SYSTIMESTAMP:返回一个时间戳类型
的当前系统时间。

SELECT SYSDATE FROM dual

INSERT INTO emp
(empno,ename,hiredate)
VALUES
(1,‘jack’,SYSDATE)

SELECT SYSTIMESTAMP FROM dual

日期转换函数
1:TO_DATE()
可以将给定字符串按照指定的日期格式
转换为DATE类型值。
SELECT
TO_DATE(‘1992-08-03 15:22:33’,
‘YYYY-MM-DD HH24:MI:SS’)
FROM
dual

在日期格式字符串中凡不是英文，符号的其他
字符都需要使用双引号括起来
SELECT
TO_DATE(‘1992年08月03日 15时22分33秒’,
‘YYYY"年"MM"月"DD"日" HH24"时"MI"分"SS"秒"’)
FROM
dual

TO_CHAR函数
可以将DATE按照给定的日期格式转换为字符串
SELECT
TO_CHAR(SYSDATE,‘YYYY-MM-DD HH24:MI:SS’)
FROM
dual

SELECT
TO_CHAR(TO_DATE(‘58-08-03’,‘RR-MM-DD’),
‘YYYY-MM-DD’)
FROM
dual

日期类型是可以计算的
对一个日期加减一个数字等同于加减天数
两个日期做减法，差为相差的天数。

查看明天的日期?
SELECT SYSDATE+1 FROM dual

查看每个员工入职至今多少天了?
SELECT ename,SYSDATE-hiredate
FROM emp

日期函数:
1:LAST_DAY(date)
返回给定日期所在月的月底日期

查看当月月底?
SELECT LAST_DAY(SYSDATE)
FROM dual

2:ADD_MONTHS(date,i)
对指定日期加上指定月
若i为负数，则是减去指定的月数

查看每个员工的转正日期?
SELECT ename,ADD_MONTHS(hiredate,3)
FROM emp

3:MONTHS_BETWEEN(date1,date2)
计算两个指定日期之间相差的月
查看每个员工入职至今多少个月?
SELECT
ename,MONTHS_BETWEEN(SYSDATE,hiredate)
FROM emp

4:NEXT_DAY(date,i)
返回给定日期第二天开始一周内指定
周几的日期
i可以是1-7，分别表示周日，周一…周六

SELECT NEXT_DAY(SYSDATE,5)
FROM dual

5:LEAST,GREATEST
求最小值与最大值
对于日期而言，最大值为最晚的日期
最小值为最早的日期
SELECT
LEAST(SYSDATE,
TO_DATE(‘1998-08-06’,
‘YYYY-MM-DD’)
)
FROM
DUAL;

6:EXTRACT函数
获取一个日期中指定时间分量的值
查看今年是哪年？
SELECT EXTRACT(YEAR FROM SYSDATE)
FROM dual

查看1980年入职的员工?
SELECT ename,hiredate
FROM emp
WHERE EXTRACT(YEAR FROM hiredate)=1980

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

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

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

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

更新NULL
UPDATE student
SET gender=null
WHERE id=1000

SELECT * FROM student

判断是否为NULL
判断要用IS NULL或IS NOT NULL
DELETE FROM student
WHERE gender IS NULL

NULL的运算
NULL与字符串连接等于什么都没做
NULL与数字运算结果还是NULL

SELECT ename||NULL
FROM emp

查看每个员工的收入(工资+绩效)
SELECT ename,sal,comm,sal+comm
FROM emp

空值函数
1:NVL(arg1,arg2)
当arg1为NULL时，函数返回arg2的值
否则返回arg1自身
该函数意义:将NULL值替换为非NULL值

查看每个员工的收入(工资+绩效)
SELECT
ename,sal,comm,
sal+NVL(comm,0)
FROM emp

2:NVL2(arg1,arg2,arg3)
当arg1不为NULL时，函数返回arg2
若为NULL，则函数返回arg3

查看每个员工是否有绩效，即：
有绩效的显示"有绩效"，为NULL的则
显示为"没有绩效"
SELECT
ename,comm,
NVL2(comm,‘有绩效’,‘没有绩效’)
FROM
emp

列别名
当一个SELECT子句中包含函数或者表达式
时，查询的结果集对应的该字段就是使用
这个函数或者表达式作为字段名，可读性
差。为此可以为这样的字段添加别名。
若别名使用双引号，那么别名就可以区分
大小写并且包含空格。
SELECT ename,sal*12 sal
FROM emp

AND的优先级高于OR
SELECT ename,job,sal
FROM emp
WHERE sal>1000
AND (job=‘SALESMAN’
OR job=‘CLERK’)

LIKE关键字
LIKE用于模糊匹配字符串，它支持
两个通配符比较:
_:表示单一的一个字符
%:表示任意个字符(0-多个)

查看名字第二个字母是A的第四个字母是T的员工
SELECT ename
FROM emp
WHERE ename LIKE ‘_A_T%’

IN(list)与NOT IN(list)
判断在列表中或不在列表中
IN和NOT IN常用在子查询的判断中

查看职位是CLERK或SALESMAN的员工?
SELECT ename,job,deptno
FROM emp
WHERE job IN (‘CLERK’,‘SALESMAN’)

BETWEEN…AND…
判断在一个范围内
查看工资在1500到3000之间的员工?
SELECT ename,job,sal
FROM emp
WHERE sal BETWEEN 1500 AND 3000

ANY(list)和ALL(list)
ANY与ALL是配合>,>=,<,<=使用的

ANY(list):大于列表之一
ALL(list):大于列表所有
<ANY(list):小于列表之一
<ALL(list):小于列表所有
常用在子查询中

DISTINCT关键字
去除重复行

查看公司有哪些职位?
SELECT DISTINCT job FROM emp

多字段去重，这几个字段值的组合没有重复行
SELECT DISTINCT job,deptno FROM emp

排序结果集
ORDER BY子句用来对结果集按照指定
的字段排序。
排序有两种方式:
升序(ASC):不写默认就是升序
降序(DESC):从大到小，需要单独指定
ORDER BY子句必须写在DQL的最后一个子句上

查看公司中工资的排名:
SELECT ename,sal,deptno
FROM emp
ORDER BY sal DESC

多字段排序，有优先级，首先按照第一个
字段排序，当第一个字段有重复值时才
按照第二个字段排序，依次类推。
SELECT ename,deptno,sal
FROM emp
ORDER BY deptno DESC,sal DESC

NULL被认作为最大值
SELECT ename,comm
FROM emp
ORDER BY comm DESC

聚合函数
聚合函数又称为:多行函数，分组函数.
作用是对结果集的指定字段进行统计然后
得出一个结果.

查看公司的最高工资与最低工资?
SELECT MAX(sal),MIN(sal)
FROM emp

查看公司的平均工资与工资总和?
SELECT AVG(sal),SUM(sal)
FROM emp

COUNT函数是对记录数的统计
查看公司共多少人?
SELECT COUNT(ename) FROM emp

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

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

分组
GROUP BY 子句
GROUP BY可以将结果集按照给定字段
值一样的记录进行分组，配合聚合函数
可以对不同的分组分别统计结果。

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

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

多字段分组
这些字段值都一样的记录看做一组
同部门，同职位的员工的平均工资?
SELECT AVG(sal),deptno,job
FROM emp
GROUP BY deptno,job

查看每个部门的最低工资是多少?
前提是该部门的最低工资要高于1000
SELECT MIN(sal),deptno
FROM emp
WHERE MIN(sal)>1000
GROUP BY deptno

上面的SQL语句会报错:此处不允许使用分组函数

HAVING子句
HAVING子句必须跟在GROUP BY 子句之后，作
用是添加过滤条件来过滤GROUP BY的分组，它
可以将不满足条件的分组去除。HAVING子句
可以使用聚合函数作为过滤条件。
SELECT MIN(sal),deptno
FROM emp
GROUP BY deptno
HAVING MIN(sal)>1000

查看平均工资高于2000的部门的最低工资?
SELECT MIN(sal),deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal)>2000

查看最低工资高于1000的那些职位的平均工资?
SELECT AVG(sal),job
FROM emp
GROUP BY job
HAVING MIN(sal)>1000

关联查询
查询数据是从多张表中关联查询一个结果集
关联查询的重点是添加连接条件。
连接条件的作用是告知数据库表与表之间的
数据是怎样对应的。
关联查询通常都要添加连接条件，否则会产
生笛卡尔积，通常是一个无意义的结果集。

查看每个员工的名字以及其所在部门的名字?
SELECT e.ename,e.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno

当关联查询的表中有同名字段，需要通过表名
或表别名来指定该字段所属表。

在关联查询中过滤条件必须与连接条件
同时成立。
查看RESEARCH部门的员工信息?
SELECT e.ename,e.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND d.dname=‘RESEARCH’

不加链接条件会产生笛卡尔积
SELECT e.ename,d.dname
FROM emp e,dept d

内连接
内连接也是关联查询的一种
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=‘RESEARCH’

关联查询忽略不满足连接条件的记录

外链接
外链接在关联查询时还可以将不满足连接
条件的记录也查询出来。
外链接分为:
左外连接，右外连接，全外连接
左外连接:以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,m.ename
FROM emp e,emp m
WHERE e.mgr=m.empno

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

查看SMITH的上司是谁?他在哪个城市工作?

子查询
子查询是一条查询语句，它是嵌套在其他
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=‘SMITH’)

查看谁的工资高于公司平均工资?
SELECT ename,sal
FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp)

在DDL中使用子查询
可以根据一个查询结果集快速构建一张表
empno,ename,sal,job,deptno,dname,loc

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

SELECT * FROM employees

DML中使用子查询
将SMITH所在部门的员工工资上浮10%
UPDATE emp
SET sal=sal*1.1
WHERE deptno=(SELECT deptno FROM emp
WHERE ename=‘SMITH’)

SELECT * FROM emp

DELETE FROM emp
WHERE deptno=(SELECT deptno FROM emp
WHERE ename=‘CLARK’)

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

当子查询为多行单列时，那么在用作判断
条件中时要搭配IN,ANY,ALL使用
查看与职位是SALESMAN同部门的其他职位员工?

SELECT ename,sal,deptno,job
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关键字后面跟一个子查询，当该
子查询可以查询出至少一条记录时，EXISTS
条件成立，并返回true

查看有员工的部门有哪些?
SELECT d.deptno,d.dname,d.loc
FROM dept d
WHERE EXISTS(
SELECT * FROM emp e
WHERE e.deptno=d.deptno
)

查看哪些人是别人的领导?
SELECT empno,ename,job,deptno
FROM emp m
WHERE EXISTS(
SELECT * FROM emp e
WHERE e.mgr=m.empno
)

查看部门的最低薪水，前提是该部门的最低
薪水要高于30号部门的最低薪水?
SELECT MIN(sal),deptno
FROM emp
GROUP BY deptno
HAVING
MIN(sal)>(SELECT MIN(sal) FROM emp
WHERE deptno=30)

在FROM子句中使用子查询通常是将子查询的结果
当做一张表看待，基于该查询结果进行二次查询
使用。
查看谁的工资高于其所在部门的平均工资?
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
e.ename, e.sal,
(SELECT d.dname FROM dept d
WHERE d.deptno = e.deptno) dname
FROM emp e

分页查询
分页查询就是将数据分段查询出来，一次
只查询数据的一部分。
这样做可以减少系统资源开销，减少数据
量可以提高网络传输速度。

分页在不同的数据库中的SQL语句是不同的。

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

由于ROWNUM是在查询表的过程中进行编号
的，所以在使用ROWNUM对结果集编行号的
查询过程中不要使用ROWNUM做大于1以上数
字的判断，否则结果集没有任何数据。
SELECT *
FROM (SELECT
ROWNUM rn,empno,ename,
sal,deptno
FROM emp)
WHERE rn BETWEEN 6 AND 10

查看公司工资排名的第6-10名
SELECT *
FROM(SELECT ROWNUM rn,t.*
FROM(SELECT empno,ename,
sal,deptno
FROM emp
ORDER BY sal DESC) t)
WHERE rn BETWEEN 6 AND 10

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

pageSize(每页显示的条目数)
page(页数)

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

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

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(a[,b,c…])
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()
CUBU的分组策略为每个参数的组合
进行一次分组
GROUP BY CUBE(a,b,c)
等同于
a,b,c
a,b
b,c
a,c
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

视图
视图也是数据库对象之一
在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

视图根据对应的SQL语句不通，分为:
简单视图，复杂视图，连接视图
连接视图算作复杂视图的一种。

当对应的子查询不含有函数，表达式，分组，
去重，关联查询的视图称为简单视图，相反
就是复杂视图。连接视图指子查询使用了关联
查询。

视图对应的子查询的查询字段可以使用别名，
那么该字段的名字就是这个别名。若字段含有
函数或表达式，那么该字段必须指定别名。

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

DESC v_emp_10
SELECT * FROM v_emp_10

对视图进行DML操作
对视图进行DML就是对视图数据来源的基础表
进行的操作。
只能对简单视图进行DML操作。复杂视图不可以

INSERT INTO v_emp_10
(empno,ename,sal,deptno)
VALUES
(1001,‘JACK’,2000,10)

SELECT * FROM v_emp_10
SELECT * FROM emp

UPDATE v_emp_10
SET sal=3000
WHERE empno=1001

DELETE FROM v_emp_10
WHERE empno=1001

对视图进行DML操作不当会污染基础表数据。
INSERT INTO v_emp_10
(empno,ename,sal,deptno)
VALUES
(1002,‘JACKSON’,2000,10)

SELECT * FROM emp
SELECT * FROM v_emp_10

UPDATE v_emp_10
SET deptno=20

可以为视图添加检查选项，来保证对视图
进行DML操作时不会对基表数据污染。
WITH CHECK OPTION
当视图添加了检查选项后，视图要求对视图
中数据进行DML操作后，视图必须对该记录
可见，否则不允许操作。
CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno,ename,sal,deptno
FROM emp
WHERE deptno=10
WITH CHECK OPTION

为视图添加只读选项
当一个视图添加了只读选项后，该视图不能
进行DML操作。
WITH READ ONLY

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

查看数据字典，有助于了解曾经创建过
的数据库对象
SELECT * FROM user_objects
WHERE object_name LIKE ‘%_FANCQ’

SELECT * FROM user_views

SELECT * FROM user_tables

复杂视图
复杂视图不能进行DML操作。

创建一个部门工资信息的视图:
CREATE VIEW v_dept_sal
AS
SELECT MIN(e.sal) min_sal,
MAX(e.sal) max_sal,
AVG(e.sal) avg_sal,
SUM(e.sal) sum_Sal,
d.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.deptno,d.dname

SELECT * FROM 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_10
删除视图不会影响基表数据。

序列
序列也是数据库对象之一
作用是根据指定的规则生成一系列数字
一般用于为表的每一条记录的主键字段提供值

创建序列
CREATE SEQUENCE seq_emp_id
START WITH 1
INCREMENT BY 1

序列支持两个伪列:
NEXTVAL:获取序列下一个数字，序列会根据
序列最后生成的数字加上步进来得到。NEXTVAL
会导致序列发生步进，序列是不能回退的。
CURRVAL:获取序列最后一次生成的数字。需要
注意的是，新创建的序列必须在使用NEXTVAL生成
一个数字后才可以使用CURRVAL。
SELECT seq_emp_id.NEXTVAL
FROM dual
SELECT seq_emp_id.CURRVAL
FROM dual

SELECT * FROM emp

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

删除序列
DROP SEQUENCE seq_emp_id

索引
索引是数据库对象之一
索引是加快查询效率的机制
索引的建立以及应用是数据库自行完成的。

约束

唯一性约束
唯一性约束要求该字段每条记录的值不能
重复，NULL除外。

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

SELECT * FROM employees1

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

主键约束
主键约束要求该字段的值为空且唯一
主键约束只能在一张表的一个字段上建立。
主键:使用该字段的值可以唯一定位表中的
一条记录。
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’)