Oracle数据库（Oracle 11g）基础
Oracle数据库（Oracle 11g）
数据库原理
数据库(DB): 是按照数据结构来组织，存储和管理数据的仓库
数据库管理系统(DBMS)：管理数据库的软件
主流关系型数据库(RDBMS)：由关系、数据、数据之间的约束三者所组成的数据模型
-
Oracle：是世界上第一个上商品化的关系型数据库管理系统，采用标准SQL(结构化查询语句)，支持多种数据类型，提供面向对象的数据支持，且第四代语言开发工具支持Unix、Windows。OS/2等多种平台。
-
DB2：是IBM公司的关系型数据库
-
Sybase:服务器数据库
-
SQL Server:微软的产品，运行在Windows NT服务器上
-
Mysql:现属Oracle公司，成本低，规模小，用于中小型网站，开放源码。
表(table)
一个关系型数据库的由多个数据表组成，数据表是关系型数据库的基本存储结构，是二维的，由行和列组成
- 行(记录)
- 列(字段)
- 表与表之间有关联：一对一 一对多 多对多
SQL：结构化查询语言
SQL 是在关系型数据库上执行的数据操作、检索及维护所使用的标准语言，可用来查询数据，操纵数据、定义数据、控制数据
分类
-
数据定义语言(DDL):用于建立、修改、删除数据库对象(
creatalterdroptruncate) -
数据操作语言(DML):用于改变数据表中的数据(持久)(
insertupdatedelete) -
事务控制语言(TCL):用来维护数据一致性的语句,和事务相关，执行完成后需经过事务控制语句提交后才能真正改变应用到数据库中(
commitrollbacksavepoint) -
数据查询语言(DQL):用于查询所需的数据(
SELECT 语句) -
数据控制语言(DCL):用于执行权限的授予和回收操作(
grantrevokecreate user)
sql(不区分大小写)
Oracle数据类型
NUMBER(数字类型)
NUMBER(p,s)
p:数字总位数，取值1~38；s:小数点后的位数,精度，默认为0
sal NUMBER(6,2); --最大取值9999.99
CHAR(固定长度的字符类型)
CHAR(N)
N:表示占用的字节数，最大长度为2000字节
VARCHAR(表变长字符类型)，最大长度4000字节,(Oracle中使用VARCHAR2).与CHAR相比:查询效率低，但省空间
DATE(定义时间的数据):长度是一个字节，默认日-月-年(DD-MM-RR/DD-MM-yyyy:不存在世纪问题).
【注】精确到秒，考虑世纪问题使用-RR
例子：若系统时间为2015年，计算1999年,根据表格可以得出1999年是上世纪
15->sys 0-49
99->user 50-99
创建表
create 语句:创建表
-- CREATE TABLE table_name(); 创建table：employee
CREATE TABLE employees
(
id NUMBER(4),
name VARCHAR2(20),
gender CHAR(1),
birth DATE,
salary NUMBER(6,2),
job VARCHAR2(30),
deptno NUMBER(2)
);
CREATE TABLE emp
(
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
CREATE TABLE dept
(
deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13)
);
desc语句：查看表结构
--DESC table_name 查看表结构
DESC employees
default语句：给字段指定默认值
default关键字：数据库中，无论表的字段是什么类型，在插入数据时只要没有指定该字段对应的值，默认都是插入null，若希望指定特殊默认值，可使用default.
【注】：数据库中的字符串是用单引号括起来的，而且内容区分大小写，但SQL语句本身不区分大小写
-- 这里指定 gender 的默认值为 M
CREATE TABLE emptest
(
id NUMBER(4),
name VARCHAR2(20),
gender CHAR(1) DEFAULT 'M'
);
NOT NULL :一种约束条件，用于确保字段值不为空【注】:与default不能同时使用
-- name 字段不能为空
CREATE TABLE emptest
(
id NUMBER(4),
name VARCHAR2(20) NOT NULL,
gender CHAR(1) DEFAULT 'M'
);
查找表：
-- SELECT * FROM table_name
SELECT * FROM employees t
drop语句：删除给定的表
-- DROP table_name 语句用于删除表（表的结构、属性以及索引也会被删除）
DROP emptest
rename语句:修改表名
-- rename old_table_name to new_table_name:修改表名
RENAME employees TO employee;
ALTER语句:
增加列(字段)
-- ALTER TABLE table_name ADD(); 增加列，只能在表的末尾追加新的字段
-- 增加一列 hiredate 默认当前日期
ALTER TABLE employee ADD(
hiredate DATE DEFAULT sysdate
);
删除一个字段
-- ALTER TABLE table_name DROP(column); 删除列
ALTER TABLE employee DROP (hiredate);
修改字段 【注】:若表中有数据，类型尽量不改变，而且长度最好变长不要变小
-- ALTER TABLE table_name MODIFY(); 修改列
ALTER TABLE employee MODIFY(
job VARCHAR2(40) DEFAULT 'CLERK'
);
DML语句(数据操作语句)
INSER语句：向表中插入数据
【注】：VALUES后给出的值应当与上面列出的列的顺序、类型一一对应；若不指定字段，则是全列插入
【建议】：就算全列插入，也把列都列出来.
--INSERT INTO table_name()VALUES(); 给数据表增加记录
INSERT INTO employee
(id,name,job,salary)
VALUES
(1001,'rose','PROGRAMMER',5500);
-- 全列插入
-- INSERT INTO employee VALUES (这里所有字段的值都要给);
--INSER 插入时间，需要考虑格式：
--1、使用默认日期格式插入记录 'DD-MON-RR'
INSERT INTO employee
(id,name,job,birth)
VALUES
(1002,'martha','ANALYST','01-9月-03');
--使用自定义日期格式插入记录 'YYYY-MM-DD'
INSERT INTO employee
(id,name,job,birth)
VALUES
(1003,'donna','MANAGER',TO_DATE('2018-03-28','YYYY-MM-DD'));
UPDATE语句：更新表中记录(行)
【注】:如果没有WHERE子句,则全表数据都会被更新
--UPDATE table_name SET column=value WHERE condition:更新表中的记录
--更改职员rose的薪水为8500
UPDATE employee SET salary=8500 WHERE name='rose';
delete语句：删除表中的记录(行)
【注】:如果没有WHERE子句,则全表数据都会被删除
在DDL(数据定义语言)TRUNCATE语句也有删除表中数据的作用，
区别：1. delete是有条件的删除，truncate将表数据全部删除
2 .delete是DML语句可回退，truncate语句是DDL语句立即生效，无法回退
3 .如果是删除全部记录，且数据量大，DELETE语句的效率比TRUNCATE语句低
--DELETE [FROM] table_name [WHERE condtion] 删除表中记录
--删除职位是空的员工记录
DELETE FROM employee WHERE job is null;
-- TRUNCATE TABLE table_name;
COMMIT
--COMMIT table_name 提交table
COMMIT employee
查询语句执行顺序
- from 子句：从后往前，从右往左 数据量较少的表尽量放在后面
- where 子句：自上而下，从右往左 将能过滤最大数量记录的条件写在where子句的最右
- group by 子句：从左往右分组 最好在使用group by之前使用where将不需要的记录在group by之前过滤掉
- having 子句 ：消耗资源，尽量避免使用；having会在检索时检索出所有的记录之后，才会对结果进行过滤，需要排序等操作
- select 子句 ：少用
*号，尽量采用字段名称，Oracle在解析过程中没通过查询数据字典将*一次转换成所有的列名，消耗时间 - oeder by 子句：从左往右执行，消耗资源
oracle 字符串操作
字符串类型
char和varchar2类型
表字符串数据类型，用来在表中存放字符串信息
char和varchar2类型对比：
-
CHAR:存放定长字符，即存不满补空格 （浪费空间，节省时间）
-
VARCHAR2:存放边长字符，存多少占多少（节约空间，浪费时间）
char和varchar2类型存储编码：默认单位是字节，可指定为字符
-- 每个英文字符占一个字节，每个中文字符安按编码不同占用2-4个字节 UTF-8:2-4bytes GBK:2bytes
char(10);-- char(10 byte)
char(10 char);-- char(20 byte)
char和varchar2类型最大长度
-
CHAR:2000字节【最多保存2000个英文字符，1000个汉字(GBK)】
-
VARCHAR2:4000字节【char可以不指定长度，默认为1，varchar2必须制定长度】
long 和clob类型
LONG:VARCHAR2加长版，存边长字符串，多达2GB
CLOB:存储定长或变长字符串，多达4GB
detail CLOB;
【注】:long 有诸多限制：
* 每个表只能有一个long类型列
* 不能作为主键
* 不能建立索引
* 不能出现在查询条件中
【建议】:Oracle建议开发中使用clob替代long类型
字符串函数
concat() ||：连接
--concat(char1,char2):
--等价操作：等价操作符 “||”
--返回两个字符创连接后的结果，如果char1和char2任何一个为null，相当于连接一个空格
SELECT CONCAT(CONCAT(ename,':'),sal) FROM emp
--多个字符串连接，用||更直观
SELECT ename||':'||sal FROM emp
length():返回字符串的长度
--length(char) 用于返回字符串的长度
--如果字符类型为varchar2，返回字符的实际长度
--如果字符类型是char，长度还要包括后补的空格
SELECT ename,LENGTH(ename) FROM emp
upper() lower() initcap():字母大小写转换
--upper(char)用于将字符转换为大写形式
--lower(char)用于将字符转换为小写形式
--initcap(char)用于将字符串中每个单词首字符大写，其他字符小写，单词之间用空格和非字母字符分隔
--如果输入的参数是null值，仍然返回null值
SELECT UPPER('hello word'),LOWER('HELllo woRLD'),INITCAP('HELllo woRLD') FROM DUAL
-- 查看 Tom 的信息
SELECT * FROM emp WHERE LOWER(ename)=LOWER('Tom');
trim() Rtrim() Ltrim():去除字符串两边的指定字符
【注】:截取集 c2 只能有一个字符,如果没有 c2 ，就去除空格
--TRIM(c2 FROM c1) 从c1的前后截去c2
--LTRIM(c1,C2) 从c1的左边截去c2
--RTRIM(c1,C2) 从c1的右边截去c2
SELECT TRIM('e' FROM 'elite') AS "t1",-- lit
LTRIM('elite','e') AS "t2", -- lite
RTRIM('elite','e') AS "t3" FROM DUAL; -- elit
lpad rpad:补位函数
--补位函数，用于在字符串char1的左端或右端用char2补足到n位，char2可重复多次
--LPAD(char1,n,char2) 左补位函数
--RPAD(char1,n,char2) 右补位函数
SELECT ename,LPAD(sal,6,'$') AS "salary" FROM emp;
substr():截取字符串
--substr(char,m,length) 用于获取字符串的子串，返回char中从m位开始取length个字符
--m=0:从首字符开始，m<0从尾部开始
--如果没有设置length，或者length的长度超过了char的长度，则取到字符串末尾为止
--字符串的首位计数从1开始
SELECT
SUBSTR('Doctor Who travels in TARDIS',8,25) --Who travels in TAR
FROM DUAL
instr():查找位置
--instr(char1,char2,n,m) 返回子串char2在源字符串char1中从n开始第m次出现的位置，m,n不取值则默认为1
--如果在char1中没有找到子串char2则返回0
-- 8
SELECT
INSTR('Doctor Who','Who') as "words"
FROM DUAL
-- 10
SELECT
INSTR('thinking in java',4,2)
FROM DUAL
Oracle数值操作
数值类型
NUMBER(p,s):p:数字总长度，取值1~38；s:精度，默认为0
数值函数
round() ：四舍五入
--round(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 --50
trunc():截取
--trunc(n.m) 用于截取，按截取的方式处理数字n
SELECT TRUNC(45.678,2) FROM DUAL -- 45.67
SELECT TRUNC(45.678,0) FROM DUAL -- 45
SELECT TRUNC(45.678,-1) FROM DUAL -- 40
mod():取模(余)
--mod(n,m)返回n除以m后的余数
--m为0，则直接返回n
SELECT ename,sal,MOD(sal,1000) FROM emp
ceil() floor():向上、下取整
--ceil(n) 向上取整
--floor(n) 向下取整
SELECT CEIL(45.678) FROM DUAL
SELECT FLOOR(45.678) FROM DUAL
SELECT CEIL(-45.678) FROM DUAL -- -45
SELECT FLOOR(-45.678) FROM DUAL -- -46
Oracle日期操作
日期类型
date
- DATE 用来保存日期和时间 7byte
- 世纪+100、年、月、日、天、小时+1、分+1、秒+
timestamp:时间戳
- timestamp 内部使用7byte 或者11byte,最高精度可以达到ns
- 1-7byte 世纪+100、年、月、日、天、小时+1、分+1、秒+1
- 8-11byte 纳秒，采用4byte存储，内部运算类型为整型
sysdate 、systimestamp:系统时间
- sysdate 返回当前系统的时间，精确到秒，默认格式 DD-MON-RR
- systimestamp 返回当前系统日期和时间，精确到毫秒
-- 15-3月 -17
SELECT sysdate FROM DUAL
-- 15-3月 -17 06.28.11.990000000 下午 +08:00
SELECT systimestamp FROM DUAL
日期转换函数
to_data()
-
日期之间可以做减法操作，差为相差的天数.
-
同样一个日期加上一个数字，该数字表示累加的天数.
--to_date('date',fmt,nlparams) 将字符串按照定制格式转换为日期类型
--fmt：格式 nlparams:指定日期语言
SELECT
ename,hiredate
from emp
WHERE hiredate > TO_DATE('1986-01-01','YYYY-MM-DD');
SELECT TRUNC(sysdate - hiredate) FROM emp;
to_char()
--to_char(date,fmt,nlparams) 将日期类型数据date按照fmt的格式输出字符串
--fmt：格式 nlparams:指定日期语言
SELECT
ename,TO_CHAR(hiredate,'YYYY"年"MM"月"DD"日"')
FROM emp
日期常用函数
last_day()
--last_day(date) 返回日期date所在月的最后一天
--在按照自然月计算某些业务逻辑，或者安排月末周期性活动时很有用处
SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT LAST_DAY('20-2月-09') FROM DUAL;
add_months(date,i)
--add_months(date,i) 返回日期date 加上i个月后的日期值
-- i 可以是任何数字 i：小数，将会截取整数后再参与运算 i：负数：则获得的是减去i个月后的日期值
SELECT
ename,ADD_MONTHS(hiredate,20*12) AS "20周年"
FROM emp
months_between()
-- months_between(date1,date2) 计算date1和date2来两个日期之间间隔多少个月(date1-date2) 会得到小数位的结果
SELECT
ename,MONTHS_BETWEEN(SYSDATE,hiredate) AS "hiredate"
FROM emp
next_day()
--next_day(date,char) 返回date日期数据的下一个周几，周几由参数char来决定 周日-周六：1-7
--查询下个周三是几号
SELECT NEXT_DAY(SYSDATE,4) AS "NEXT_WEDN" FROM DUAL;
least() greatest() :比较函数
--least(expr1,expr2,...) 返回结果是参数列表的最大值
--greatest(expr1,expr2,...) 返回结果是参数列表的最小值
SELECT LEAST(SYSDATE,'10-10月-08') FROM DUAL;
extract(date FROM datetime)
--extract(date FROM datetime) 从参数datetime中提取参数date指定的数据，比如提取年、月、日
SELECT EXTRACT(YEAR FROM SYSDATE) current_year FROM DUAL;
SELECT EXTRACT(HOUR FROM TIMESTAMP '2008-10-10 10:10:10') FROM DUAL;
空值操作
null的含义
空值、任何数据类型均可取值null
null的操作
插入null
INSERT INTO student ( id,name,gender) values(1001,'Li',null);
更新成null
-- 这种更新只有此字段没有非空约束时才能操作
UPDATE student set gender= null WHERE name='Li';
null 条件查询
【注】：null不等于任何值.
--当判断一个字段的值是否为空的时候要用is null / is not null
DELETE FROM student WHERE gender is not null;
非空约束
gender char(1) not null;
空值函数
NVL(expr1,expr2)
【注】:expr1,expr2数据类型必须一致
--NVL(expr1,expr2) 将NULL转换为非NULL值
--如果expr1为NULL。则取值expr2
SELECT
ename,sal,comm,sal+NVL(comm,0) as "salary"
FROM emp
NVL2(expr1,expr2,expr3)
--NVL2(expr1,expr2,expr3) 将NULL转换为非NULL值
--如果expr1不为NULL。则取值expr2,如果是NULL值，则取值expr3
SELECT
ename,sal,comm,NVL2(comm,sal+comm,sal) as "salary"
FROM emp
null与字符串拼接、与数字运算
- null与字符串拼接等于什么也没有做
- null 与数字运算结果等于null
--若 sal=5000,comm=null 结果也为null
SELECT sal+comm FROM emp;
sql(基础查询)
基本查询语句
from子句
SELECT * FROM dept;
使用别名
当查询的字段中含有表达式或函数，那么该字段在查询出来的结果集中的字段名就是该表达式或函数，这样的可读性很差，为此可用别名来改善
SELECT ename, sal*2 as "年薪" FROM emp
--as 和 "" 可省略，若区分大小写，必须用双引号引起来
SELECT ename, sal*2 年薪 FROM emp
where子句
--和数字比较，可使用 '',也可不使用
WHERE deptno=10;
--和字符及日期比较，必须用单引号
WHERE job='salesman';
select子句
SELECT ename FROM emp
查询条件
使用> 、<、 >= 、<=、 !=、 <> 、=
SELECT * FROM employee WHERE deptno <> 10;
使用 and 、or关键字
SELECT * FROM employee WHERE sal>1000 and job='clerk';
-- 由于and优先级高于or，可以使用括号来提高优先级
SELECT * FROM employee WHERE sal>1000 and (job='clerk' or job='salesman');
使用like(模糊查询)
--like 比较操作符用来做模糊查询，需要借助两个通配符
-- %: 表示0到多个字符
-- _: 标识单个字符
SELECT
ename,job
FROM emp
WHERE ename LIKE '_A%';
in(list)
--in(list) 用来取出符合列表范围中的数据
SELECT
ename,job
FROM emp
WHERE job IN('MANAGER','CLERK');
not in(list)
--not in(list) 取出不符合此列表中的数据记录
SELECT
ename,job
FROM emp
WHERE deptno NOT IN(10,20);
between...and ...
--between...and ... 查询符合某个值域范围条件的数据
SELECT
ename,sal
FROM emp
WHERE sal BETWEEN 1500 AND 3000;
使用any 和 all :不能单独使用，要配合单行比较操作符>、>=、<、<=一起使用
-- >any :大于最小 <any: 小于最大 >all: 大于最大 <all:小于最小
SELECT * FROM employee WHERE sal > any(3500,4000,4500);
distinct 过滤重复
--过滤部门重复
SELECT DISTINCT deptno FROM emp
--多字段去重，这些字段的值的组合没有重复
SELECT DISTINCT deptno,job FROM emp
排序
order by
【注】:必须写在select语句的最后一个子句上，查询出现结果集后才进行的排序
--order by asc/desc 对数据按一定规则进行排序操作 NULL值视为最大
--asc 升序，缺省时为asc
--desc 降序
SELECT
ename,deptno
FROM emp
ORDER BY deptno
多个列排序
当以多列作为排序标准时，首先按照第一列排序，如果第一列数据相同，再以第二列排序，以此类推，优先级自左向右。
【注】:多列排序时，不管升序还是降序，每个列需单独设置排序方式
--多个列排序
SELECT
ename,deptno,sal
FROM emp
ORDER BY deptno DESC,sal DESC;
聚合函数
数据统计，将多个值进行运算得出一个结果
【注】:聚合函数忽略null值
定义
将表的全部数据划分为几组数据，每组数据统计一个结果，因为是多行数据参与运算返回一行结果，也称分组函数、多行函数、集合函数。
max() min()
--max() 用来获取列或表达式的最大值
--min() 用来获取列或表达式的最小值
SELECT
MAX(hiredate) max_hiredate,MIN(hiredate) min_hiredate
FROM emp;
AVG() SUM()
--只能操作数字类型
--忽略NULL值
SELECT
AVG(sal) avg_sal,SUM(sal) sum_sal
FROM emp;
count()
--count() 用来计算表中的记录条数
--忽略NULL值
SELECT COUNT(*) total_num FROM emp; --14
SELECT COUNT(comm) total_comm FROM emp; --4
聚合函数忽略NULL值
--聚合函数对NULL值的处理
--聚合函数忽略NULL值
SELECT AVG(comm) avg_comm FROM emp;
SELECT AVG(NVL(COMM,0)) avg_comm FROM emp;
分组函数
定义：将某个字段的值相同的记录看做一组，然后用于统计数据使用，为聚合函数服务.
--分组查询
--select column...
--from table_name
--where condition
--group by group_by_expression
--having group_condition
--order by column asc/desc
group by子句
不在聚合函数的其他字段必须在group by 里
--group by 子句
SELECT job,AVG(NVL(COMM,0)) avg_comm FROM emp GROUP BY job;
having子句
对分组后的结果进一步限制
--having 子句
--查询每个部门的最高薪水，只有最高薪水>4000的记录才会被输出
SELECT
deptno,MAX(sal) max_sal
FROM emp
GROUP BY deptno
HAVING MAX(sal)>4000;
where 和 having 的区别
-
where是在取数据的过程中过滤，having是取完数据后再次过滤
-
where是第一次检索时过滤，having是分完组后过滤
-
having子句必须跟在group by 后面，不能单独存在
sql (关联查询)
关联基础
概念
连接查询：查询两个或两个以上数据表或视图的查询叫连接查询；连接查询通常建立在存在相互关系的父子表之间.
【注】:关联查询不指定连接条件就会出现笛卡尔积
--关联查询
--select table1.column,table2.column,...
--from table1,table2
--where table1.column=teble2.column
笛卡尔积
做关联操作的每个表的每一行都与其他表的每一行做组合，假设两个表的记录条数分别是 **X **和 **Y **，笛卡尔积将返回 X*Y 条记录。
--笛卡尔积
--假设两个表的记录分别是X和Y，笛卡尔积将返回X*Y条记录
SELECT COUNT(*) FROM emp; --14
SELECT COUNT(*) FROM dept; --4
SELECT e.ename,d.dname FROM emp e,dept d --56
等值连接
连接查询时，最常见的一种，通常是在有主外键关联关系的表间建立，并将连接条件设定为有关系的列，使用”=”连接相关的表.
--等值连接
SELECT
e.ename,e.job,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno;
关联查询
内连接
--内连接 from table1 join table2 on table1.column1=table.column2
--返回所有满足连接条件的记录
SELECT
e.ename,d.dname
FROM emp e JOIN dept d ON(e.deptno=d.deptno);
外连接
--外连接 from table1 left|right|full [outer] join table2 on table1.column1=table.column2
--不仅返回满足连接条件的记录，还将返回不满足连接条件的记录
--emp做驱动表
SELECT
e.ename,d.dname
FROM emp e LEFT OUTER JOIN dept d ON(e.deptno=d.deptno);
--dept做驱动表
SELECT
e.ename,d.dname
FROM emp e RIGHT OUTER JOIN dept d ON(e.deptno=d.deptno);
--用where：哪边补null,哪边用(+)
SELECT
e.ename,d.dname
FROM emp e,dept d
where e.deptno(+)=d.deptno;
全外连接
--全外连接 from table1 full [outer] join table2 on table1.column1=table.column2
--是左外连接和右外连接的总和
SELECT
e.ename,d.dname
FROM emp e FULL OUTER JOIN dept d ON(e.deptno=d.deptno);
自连接
-
一种特殊的连接查询，数据来源是一个表，即关联关系来自于单表中的多个列，表中的列对照同一个表中的其他列的情况称作自参照表
-
自连接是通过将表用别名虚拟成两个表的方式实现，可以是等值或不等值连接
-
这样自连接的表：列保存的数据相同，但数据间又存在上下级关系，而且层级不确定
--自连接 数据来源同一个表
--查出每个职员的经理名字，以及他们的职员编码
SELECT
w.empno w_empno,w.ename w_ename,m.empno m_empno,m.ename m_ename
FROM emp w JOIN emp m ON(w.mgr=m.empno);
sql(高级查询)
子查询
嵌入在其他SQL语句中的查询语句，通常是为外层SQL提供数据支持的，大部分出现在where子句中
-
为了给查询提供数据而首先执行的查询语句叫子查询
-
子查询嵌入的语句称为主查询或父查询
-
根据返回的结果不同分为：单行单列子查询、多行单列子查询、多行多列子查询
-
如果子查询返回多行，主查询中要使用多行比较操作符（in,any,all）
-- 在数据定义语言(DDL)中建表使用子查询
--创建一张表emp10 保存所有10号部门的员工
CREATE TABLE emp10 AS SELECT * FROM EMP WHERE deptno=10;
--在数据操作语言(DML)中使用子查询
--如果子查询返回多行，主查询中要使用多行比较操作符（in,any,all）
SELECT ename，job,deptno
FROM emp
WHERE deptno in (SELECT deptno FROM emp WHERE job='MANAGER');
SELECT ename
FROM emp
WHERE sal>all(SELECT sal FROM emp WHERE job='MANAGER')
子查询在where子句中
--1.1子查询结果单行单列
SELECT
e.ename,e.job
FROM emp e
WHERE e.job=(SELECT job FROM emp WHERE ename='SCOTT');
--1.2子查询结果多行单列
SELECT empno,ename,job,sal,deptno
FROM emp
WHERE deptno IN (SELECT deptno FROM emp WHERE job='SALESMAN')
AND job<>'SALESMAN';
--1.3子查询结果多行多列
--在子查询中需要引用到主查询的字段数据，使用exists关键字
--exists 后面的子查询至少返回一行数据，则整个条件返回true
--列出来那些有员工的部门的信息
SELECT
deptno,dname
FROM dept d
WHERE EXISTS
(SELECT * FROM emp e WHERE d.deptno=e.deptno);
子查询在having子句中
--查询列出最低薪水高于部门30的最低薪水的部门信息
SELECT deptno,MIN(sal) min_sal
FROM emp
GROUP BY deptno
HAVING MIN(sal)>(SELECT MIN(sal) FROM emp WHERE deptno=30);
子查询在from部分
-
多列子查询常被看做一张表，出现在from子句中
-
如果要在一个子查询的结果中继续查询，则子查询出现在from子句中，这个子查也称为行内视图或匿名视图（内视图）
-
把子查询当作视图对待，但视图没有名字，只能在当前的SQL中有效
--这个子查询也被称作行内视图或者匿名视图
--查询出薪水比本部门平均薪水高的员工信息
SELECT e.deptno,e.ename,e.sal
FROM emp e,(SELECT deptno,AVG(sal) avg_sal FROM emp GROUP BY deptno) x
WHERE e.deptno=x.deptno
AND e.sal>x.avg_sal
ORDER BY e.deptno;
子查询在select部分
--可认为是外连接的另一种表现形式，使用更灵活
SELECT
e.ename,e.sal,(SELECT d.deptno FROM dept d WHERE d.deptno=e.deptno )deptno
FROM emp e;
分页查询（不同数据库不同）
rownum
被称为伪列，用于返回标识行数数据顺序的数字，只能从1 计数，不能从结果集中直接截取(查到1条数据，rownum长1)
--错误
SELECT rownum,ename FROM emp WHERE rownum>3;
--利用rownum截取结果集中的部分数据，需要用到行内视图
--利用rownum 截取结果集中的部分数据 8，9，10
SELECT * FROM
(SELECT ROWNUM rn,e.* FROM emp e)
WHERE rn BETWEEN 8 AND 10;
使用子查询进行分页
【分页策略】：每次只取一页的数据，每次换页，取下一页数据，在Oracle中利用rownum进行分页
由于使用rownum编号的过程中不能使用>判断，所以可以先编号然后将查询到的结果集当作一张表看待，再进行按照范围取数据.
--按照薪水倒叙排列，取出结果集中第8到10条的记录
SELECT * FROM
(SELECT ROWNUM rn,t.* FROM
(SELECT empno,ename,sal FROM emp ORDER BY sal DESC) t
)
WHERE rn BETWEEN 8 AND 10;
分页与order by
若在分页的过程中有排序的需求时，由于编号时在查询过程中做的，而排序实在查询后做的，这会导致行号出现混乱，所以应当先进行一次排序，在排序的结构基础上再进行编号，就可以解决问题.
--ORACLE中的分页查询是靠两次子查询完成的，先排序，再编号，最后取范围
SELECT *
FROM ( SELECT rownum rn,t.*
FROM (SELECT * FROM emp ORDER BY sel DESC) t
)
WHERE rn BETWEEN 6 AND 10;
分页范围算法:
【pageN： (n-1)pageSize+1 ... npageSize】
pageSize:每页的条目数 page:第几页
min:(page-1)pageSize+1 max:pagepageSize
decode函数
decode(expr,search1,result1,search2,result2,...,...,default)
decode函数基本语法
decode 用于比较参数expr的值，如果匹配到那一个search，就返回对应的result结果，可以有很多组search和result的对应关系，如果任何一个search条件都米有匹配到，则返回最后的default值，若无default值，返回null.
--decode用于比较参数expr，匹配到那一个search就返回对应的result，没有就返回default，default缺省，就返回NULL
SELECT ename,job,sal,
DECODE(job,
'MANAGER',sal*1.2,
'ANALYST',sal*1.2,
'SALESMAN',sal*1.05,
sal
) bonus
FROM emp;
与decode功能相似的有case语句：实现if-else操作
--case 和decode函数功能相似
SELECT ename,job,sal,
CASE job WHEN'MANAGER' THEN sal*1.2
WHEN 'ANALYST' THEN sal*1.2
WHEN 'SALESMAN' THEN sal*1.05
ELSE sal END
bonus
FROM emp;
decode在分组查询中的应用
按字段内容分组
--decode 在分组查询中的应用
SELECT DECODE(job,
'ANALYST','VIP',
'MANAGER','VIP',
'OPERATION') job,COUNT(1) job_cnt
FROM emp
GROUP BY DECODE(job,'ANALYST','VIP','MANAGER','VIP','OPERATION');
按字段内容排序
--没有赋值的返回null值，null最大
SELECT deptno,dname,loc
FROM dept
ORDER BY DECODE(dname,'oprations','1','sales','2');
排序函数
row_number()
--row_number() over(partition by col1 order by col2)
--表示根据col1分组，在分组的内部根据col2排序
--此函数计算的值就表示每组内部排序后的顺序编号，组内连续且唯一
--可以直接从结果集中取出子集
SELECT deptno,ename,empno,
ROW_NUMBER()
OVER(PARTITION BY deptno ORDER BY empno) AS emp_id
FROM emp;
rank()
--rank()over(partition by col1 order by col2)
----表示根据col1分组，在分组的内部根据col2给予等级标识
--等级标识即排名，相同的数据返回相同的排名，跳跃排序 2 2 4
SELECT deptno,ename,comm,
RANK()OVER(PARTITION BY deptno ORDER BY sal DESC,comm) "Rank_ID"
FROM emp;
dense_rank()
--dense_rank()over(partition by col1 order by col2)
----表示根据col1分组，在分组的内部根据col2给予等级标识
--等级标识即排名，相同的数据返回相同的排名，连续排序 2 2 3 4
SELECT d.dname,e.ename,e.sal,
DENSE_RANK()OVER(PARTITION BY e.deptno ORDER BY e.sal) AS drank
FROM emp e JOIN dept d ON e.deptno=d.deptno;
集合操作
select statement1 [union|union all|intersect|minus] select statement2
【注】：order by 子句只能放在最后的一个查询语句中
union 并
--union 并 会自动去掉合并后的重复记录，对查询结果排序
SELECT ename,job,sal FROM emp WHERE job='MANAGER'
UNION
SELECT ename,job,sal FROM emp WHERE sal>2500;
union all 并
--union all 并 返回两个结果集中的所有行，包括重复的行，对查询结果不排序
SELECT ename,job,sal FROM emp WHERE job='MANAGER'
UNION ALL
SELECT ename,job,sal FROM emp WHERE sal>2500;
intersect 交
--intersect 交
--只有同时存在于两个结果集中的数据才会被显示输出，结果集会以第一列的数据作升序排列
SELECT ename,job,sal FROM emp WHERE job='MANAGER'
INTERSECT
SELECT ename,job,sal FROM emp WHERE sal>2500;
minus 差
--minus 差
SELECT ename,job,sal FROM emp WHERE job='MANAGER'
MINUS
SELECT ename,job,sal FROM emp WHERE sal>2500;
高级分组函数
-
rollup\cube\grouping sets 是group by 子句的扩展，可以生成与使用union all来组合单个分组查询时相同的结果集
-
用来简化和高效的实现统计查询
rollup(a,b,c)
对rollup的列从右到左以一次少一列的方式进行分组知道所有的列都去掉的分组，即全表分组，对你个参数的rollup,有n+1次分组
--group by rollup(a,b,c) a,b,c\a,b,null\a,null,null\null,null,null
--对于n个参数的rollup，有n+1次分组
SELECT year_id,month_id,COUNT(*) AS num_row,SUM(sales_value) AS sales_value
FROM sales_tab
GROUP BY ROLLUP(year_id,month_id)
ORDER BY year_id,month_id;
--等价于下面几个表的union all
SELECT SUM(sales_value) AS sales_value FROM sales_tab;
union all
SELECT year_id,COUNT(*) AS num_row,SUM(sales_value) AS sales_value
FROM sales_tab
GROUP BY year_id
ORDER BY year_id;
union all
SELECT year_id,month_id,COUNT(*) AS num_row,SUM(sales_value) AS sales_value
FROM sales_tab
GROUP BY year_id,month_id
ORDER BY year_id,month_id;
cube（a,b,c）
【注】：等价于 union all 只是方便理解，其内部运行机制并不相同，其效率远高于 union all
--group by cube（a,b,c） a,b,c\a,b\a,c\a\b,c\b\c\(null,null,null)
--对于n个参数的cube，有2^n次分组
SELECT year_id,month_id,COUNT(*) AS num_row,SUM(sales_value) AS sales_value
FROM sales_tab
GROUP BY CUBE(year_id,month_id)
ORDER BY year_id,month_id;
grouping sets((a,b),c)
--group by grouping sets((a,b),c) (a,a)--》允许两次对a 进行group by
--可生成与使用单个的group by或cube生成的结果集相同的结果集
--如果不需要获得由完备的group by或cube运算符生成的全部分组，则可以使用grouping sets仅指定所需的分组
--grouping sets列表可以包含重复的分组
SELECT year_id,month_id,SUM(sales_value) AS sales_value
FROM sales_tab
GROUP BY CUBE(year_id,month_id)
ORDER BY 1，2;
SELECT year_id,month_id,SUM(sales_value) AS sales_value
FROM sales_tab
GROUP BY GROUPING SETS((year_id),(month_id)
ORDER BY 1，2;
视图(View)、序列（sequence）、索引(index)
新建测试表和数据
--新建测试数据
--新建一个student表
DROP TABLE student;
CREATE TABLE student(
s_id NUMBER(4,0),
Sname VARCHAR2(20),
Score NUMBER(4,1),
class_id NUMBER(4,0)
);
INSERT INTO student(s_id,Sname,Score,class_id)VALUES(1001,'marry',55.0,101);
INSERT INTO student(s_id,Sname,Score,class_id)VALUES(1002,'jerry',66.0,101);
INSERT INTO student(s_id,Sname,Score,class_id)VALUES(1003,'tom',74.0,101);
INSERT INTO student(s_id,Sname,Score,class_id)VALUES(1004,'jim',88.0,90);
INSERT INTO student(s_id,Sname,Score,class_id)VALUES(1005,'allen',100.0,90);
INSERT INTO student(s_id,Sname,Score,class_id)VALUES(1006,'terry',66,101);
SELECT * FROM student;
--新建一个class表
DROP TABLE class;
CREATE TABLE class（
cid NUMBER(4,0),
Cname VARCHAR2(20)
);
INSERT INTO class(cid,Cname)VALUES(101,'java');
INSERT INTO class(cid,Cname)VALUES(90,'php');
视图view
create [or replace] view view_name as subquery
- 视图（view）也被称为虚表，是一组数据的逻辑表示
- 视图对应一条select语句，结果集被赋予一个名字，即视图名字
- 视图本身不包含任何数据，只包含映射到基表的一个查询语句，当基表的数据发生变化，视图数据也随之变化
- grant create view to user ;授权给user能创建视图的权限，这时user能创建视图
--view_name V_tablename_col
--基表：select empno from emp
CREATE OR REPLACE view v_emp_10 AS SELECT empno FROM emp
【注】:用户必须有创建视图的系统权限才能创建视图
【作用】： 1、 简化复杂查询 2、限制数据访问（使用别名.对基表的其他列起到安全和保密的作用）
根据视图对应的子查询种类分为几种类型
简单视图
select 语句基于单表建立，且不包含任何函数运算、表达式、分组函数，此时视图是基表的子集，可进行增、删、改操作.
--简单视图
CREATE VIEW v_emp_10
AS
SELECT empno,ename,sal,deptno
FROM emp
WHERE deptno=10;
CREATE VIEW v_emp_11
AS
SELECT empno id,ename name,sal salary,deptno
FROM emp
WHERE deptno=10;
复杂视图
select 语句基于单表建立，但包含单行函数、表达式、分组函数、group by子句
- 必须为子查询中的表达式或函数式定义别名
- 复杂视图不允许DML操作
--复杂视图
CREATE VIEW v_emp_salary
AS
SELECT d.dname,AVG(sal) avg_sal,SUM(sal) sum_sal,MAX(sal) max_sal,MIN(sal) min_sal
FROM emp e JOIN dept d ON e.deptno=d.deptno
GROUP BY d.dname;
SELECT * FROM v_emp_salary;
连接视图 :select 语句基于多个表的,不进行DML操作
视图操作
desc view_name：查看视图结构
--desc view_name 查看视图结构
DESC v_emp_10;
查询视图
--select * from view_name 查询视图和查询表一样
--和创建视图的列名一致，列名不一定和基表一样
SELECT * FROM v_emp_11;
对视图进行insert操作
--视图本身并不包含数据，只是基表数据的逻辑映射
--当对视图进行DML操作时，实际上是对基表的DML操作，简单视图可以通过DML操作影响到基表数据
INSERT INTO v_emp_10 VALUES(1234,'DOCTOR',4000,10);
SELECT * FROM v_emp_10;
drop view view_name; 删除视图
【注】：删除视图的数据会影响基表数据
--对视图的删除不会导致基表数据的丢失，不会影响基表数据
DROP VIEW v_emp_11;
创建具有 check option约束的视图
--v_emp_10看不到jacke的信息，因为插入的是部门20，但基表上有插入，对基表有污染
INSERT INTO v_emp_10 VALUES(1001,'jack',5000,20)
--create [or replace] view view_name ... as subquery with check option; 创建具有 check option约束的视图
--with check option :通过视图所做的修改必须在视图的可见范围内
CREATE OR REPLACE VIEW v_emp_12
AS
SELECT empno id,ename name,sal salary,deptno
FROM emp
WHERE deptno=10
WITH CHECK OPTION;
INSERT INTO v_emp_12 VALUES(1008,'donna',5500,20);--插入失败，部门20 不在视图可见范围内
对视图DML操作的原则
-
简单视图能够执行DML操作，下列情况除外：
- 在基表中定义了非空列，但简单视图对应的select语句并没有包含这个非空列，导致这个非空列对视图不可见，这时无法对视图进行insert操作
-
delete 操作时，只能删除现有视图能查到的记录
-
如果视图定义中包含了函数表达式、分组语句、distinct关键字或rownum伪列，不允许DML
-
DML不能违反其表的约束条件
只读
不执行DML，保证视图对应的基表数据不会被非法修改
CREATE OR REPLACE VIEW v_tablename_col AS subquery WITH READ ONLY
和视图相关的数据字典
user_objects ：在数据字典ser_objects中查询所有视图名称
SELECT object_name FROM user_objects
WHERE object_type='VIEW';
user_views : 在数据字典user_views中查询指定视图
SELECT text FROM user_views
WHERE view_name='V_EMP_10';
user_updatable_columns : 在数据字典user_update_columns中查询视图
SELECT column_name,insertable,updatable,deletable
FROM user_updatable_columns
WHERE table_name='V_EMP_10';
--user_tables 曾经创建过的所有表
--select table_name from user_tables;
序列(sequence)
序列(sequence):主键的生成机制
定义
-
是一种用来生成唯一数字值的数据库对象
-
序列的值由oracle程序按照递增或递减顺序自动生成，通常用来自动残生表的主键值，是一种高效率获得唯一键值的途径
-
序列是独立的数据库对象，和表是独立的对象，序列并不依附于表
-
通常情况下，一个序列为一个表提供主键值，但一个序列也可以为多个表提供主键值(非空且唯一)
创建序列
-
序列的第一个有序值是
i,步进是j,j为正数，表递增；为负数表递减 -
序列可生成的最大值为
m，最小值为n，若没有设置任何可选参数，序列第1个值为1，步进为1. -
oracle 在增至最大值或递减至最小值之后是否重用序列，若是递减并有m，从m开始，若是递增并有n，若没有，从 i 开始，默认是nucycle
--create sequence [schema.] sequence_name --sequence_name 是序列名，将创建在schema方案下
--[start with i][increment by j] --序列的第一个值是i,步进j，缺省则i,j=1
--[maxvalue m|nomaxvalue][minvalue n|nominvalue]
--[cycle|nocycle] --默认nocycle
--[cache p|nocache] --用来指定先预取p个数据在缓存中，提高序列生成效率，默认值是20
CREATE SEQUENCE emp_seq
START WITH 100 INCREMENT BY 10;
使用序列
【注】:序列创建以后，必须先执行一次nextval，之后才能使用currval
--序列中有两个伪列 ：
--nextval ：获取序列下个值(不可逆，会导致表的主键值不连续)
--currval : 获取序列当前值
-- SELECT seq.nextval from dual (运行一次值改变一次，切不可逆)
-- SELECT seq.currval from dual (运行多次值不变)
--[注意]序列创建以后，必须先执行一次nextval，之后才能使用currval
SELECT emp_seq.NEXTVAL FROM DUAL; --会浪费一个序列值，导致主键值不连续
INSERT INTO emp(empno,ename) VALUES(emp_seq.NEXTVAL,'donna');
SELECT empno,ename FROM emp WHERE ename='donna'; --110 donna
--此时查询当前值
SELECT emp_seq.CURRVAL FROM DUAL; --110 不会浪费序列值
删除序列
-- drop sequence sequence_name; 删除序列
DROP SEQUENCE emp_seq;
索引(index)
【作用】：加快检索速度
原理
-
索引是一种允许直接访问数据库中某一数据的树形结构，为了提高查询效率而引入
-
索引是独立于表的对象，可以存放在与表不同的表空间中
-
索引记录中存有索引关键字和指向表中数据的指针(地址)
-
索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引
-
索引是一种提高插叙效率的机制
合理使用索引提高查询效率
- 为经常出现在where 子句中的列创建索引
- 为经常出现在order by ,distinct后面的字段建立索引，如果建立的是复合索引，索引的字段的顺序要和这些关键字后面的字段顺序一致
- 为经常作为表的连接条件的列上创建索引
- 不要在经常做DML操作的列上建立索引
- 不要在小表上建立索引
- 限制表上的索引数目，索引并不是越多越好
- 删除很少被使用的，不合理的索引
创建索引
CREATE INDEX index_emp_name ON emp (ename);
--复合索引（多列索引）:是基于多个列的索引，若经常在order by 子句中使用，job和sal作为排序依据，可创建复合索引
CREATE INDEX index_emp_job_sal ON emp(job,sal);
-- 基于函数的索引
CREATE INDEX index_ename_upper_idx ON emp (UPPER(ename));
修改和删除索引
--定期重建索引
ALTER INDEX idx REBUILD;
--删除索引
DROP INDEX idx;
约束(constraint)
约束的概述
约束的全称是约束条件，也叫完整性约束条件；约束实在数据上强制执行的一些数据检验规则，当执行DML操作时，数据必须符合这些规则，若不符合无法执行.
【作用】: 约束条件可保证表中数据完整性，保证数据间商业逻辑
约束的类型
-
非空约束(NN) :not null
-
唯一性约束(UK) :unique
-
主键约束(PK): primary key
-
外键约束(FK): foreign key
-
检查约束(CK): check
非空约束
【作用】：确保字段的值不为空.
【列级约束】：表已建好，定义列的同时加约束。
执行insert时：必须提供这个列的数据
执行update时，不能给此列值设为null
hiredate date CONSTRAINT emp.nn not null;
name varchar2(30) not null;
--修改表时添加非空约束
--ALTER TABLE table_name MODIFY (col not null);
ALTER TABLE emp MODIFY (eid number(6) not null);
唯一性约束
【作用】：确保字段或字段组合不会出现重复值
当给表的某个列定义了唯一约束性条件，该列的值不允许重复，但允许是null值
【表级约束】可在建表同时添加约束，也可在建表之后再添加约束
--建表同时建立
eid number(6) unique;
--在建表之后添加唯一性约束
--ALTER TABLE table_name ADD constraint constraint_name unique(col);
ALTER TABLE emp ADD constraint emp_email_uk unique(name);
主键约束
非空(not null )且唯一(unique)的组合
-
主键字段可以是单字段或多字段组合，即在主键约束下的单字段或多字段组合下不允许有空值，也不允许有重复值
-
主键可用来在表中唯一的确定一行数据
-
一个表中只允许建立一个主键，而其他约束条件没有明确的个数限制
【主键选取的原则】：
-
主键应是对系统无意义的数据
-
永远不要更新主键，让主键除了唯一标识一行之外，再无其他用途
-
主键不应包含动态变化的数据，如时间戳
-
主键应自动生成不要人为干预【select sys_guid() from dual】，以免使它带有除唯一标识一行以外的意义
-
主键尽量建立在单例上
eid number(6) primary key;
--在建表之后添加主键
ALTER TABLE employees ADD constraint emp_pk primary key(eid);
-- UUID：32位不重复字符串（英文加字符串）
SELECT sys_guid() FROM dual; -- 13172FEF4ACF430D8481BA1E702CDD74
--java 中：java.util.UUID (36位)
--String uuid = UUID.randomUUID().toString();
外键约束
【作用】：保证相关两个字段的关系
【注】：外键会对性能降低
【外键约束条件包括】：
-
从表上定义的外键的列值，必须从主表被参照的列值中选取或为null
-
当主表参照列的值被从表参照时，主表的该记录不允许被删除
--先建表，再建外键约束
ALTER TABLE emp ADD constraint emp_fk foreign key(deptno) REFERENCES dept(deptno);
检查约束
【作用】：用来强制在字段上的每个值都要满足check中定义的条件
ALTER TABLE emp ADD constraint emp_ck check(salary > 2000);
删除约束
-- ALTER TABLE table_name DROP CONSTRAINT constraint_name;
ALTER TABLE emp DROP CONSTRAINT emp_ck check;
浙公网安备 33010602011771号