oracle
流行的数据库都属于关系型数据库：1、数据存在表中 2、表和表之间有关联
流行数据库：
- MySQL 小型数据库 oracle 免费
- oracle 大型数据库 oracle 学习、开发阶段免费，上线收费
- SQLSever 中大型数据库 微软
- DB2 IBM
- 两个服务：数据库实例、数据库监听服务
SQL语言的分类：
- DB Database 数据库
- DBMS DatabaseManagement System 数据库管理系统
- DBA Database Administrator 数据库管理员
- SQL Structured Query Language结构化查询语言
- DDL Data Definition Language 数据定义语言
- DML Data manipulation language 数据操纵语言
- TCL Transaction Control Language事务控制语言
- CRUD Create、Retrieve、Update、Delete增删改查
日期类型、字符串类型的值都需要用单引号”括起来。
每句语句用分号;结束
主键和外键：
- 主键（PRIMARY KEY）：表中每一行记录的唯一标识，一般以一个字段作为组件，也可以两个或两个以上作为联合主键，主键值不能重复。
- 外键(FOREIGN KEY)：引用另一张表主键信息。
主表和子表：被引用的表是主表，引用别人的是子表。
查询
不会改变表中数据，执行查询语句后会得到一个结果集。
全部数据： SELECT * FROM 表名;
某些字段： SELECT 字段1, 字段2… FROM 表名;
去重复： DISTINCT
起别名： SELECT 字段 [AS] 别名 FROM 表名; 别名中有空格要用”“括起来。
连接符: SELECT ename || “的” || (sal + NVL(comm, 0)) *12 年薪 FROM emp;
条件查询：SELECT 字段1, 字段2… FROM emp WHERE 条件;
中文系统下日期默认格式： hiredate = “2-5**月**-1994”;
表名、字段名、关键字不区分大小写，表格中数据内容区分大小写。
运算符：>、 <、 >=、 <=、 =、 !=、 <>、 ^=、 AND、 OR、 NOT、IS NULL、 IS NOT NULL、 IN、 ANY、 ALL、 BETWEEN…AND…
模糊查询： WHERE 字段 LIKE ‘_value%’;
通配符： _ 一个字符 % 零或多个字符
排序： ORDER BY 字段1, 字段2 [{DESC | ASC}]
默认不写按升序排列，先按字段1排序，重复部分再按字段2排序，以此类推
DESC降序 ASC升序
SELECT ename, job, sal s FROM emp ORDER BY sal/3/s DESC, ename ASC;
空值排序问题：ASC NULL在最后， DESC NULL在最前， （NULL最大）
伪列
- rowid: 数据插入时Oracle自动分配的一个固定的唯一地址。
- rownum： 对采集结果进行编号，不是固定的，可以用来分页。
函数：
- 字符函数
CONCAT(x, y)、 INITCAP(x)、 LOWER(x)、 UPPER(x)、 LPAD(x, width [,pad_string])、 RPAD(x, width [,pad_string])、 LENGTH(x)、 LTRIM(x [,trim_string])、 RTRIM(x [,trim_string])、 TRIM(x [,trim_string])、 INSTR(x, find_string [,start] [,occurrence])、 SUBSTR(x, start, [,length])、 NVL(x, value)、 - 数值函数
ABS(x)、 CEIL(x)、 FLOOR(x)、 ROUND(x)、 MOD(x, y)、 POWER(x, y)、 SQRT(x)、 TRUNC(x [,y])、 - 日期函数
SYSDATE、 ADD_MONTHS(x, y)、 MONTHS_BETWEEN(x, y)、 - 转换函数
TO_CHAR(x [,format])、 TO_DATE(x [,format])、 TO_NUMBER(x [,format])、 - 聚合函数
AVG(x)、 COUNT(x)、 MAX(x)、 MIN(x)、 SUM(x)、 正则表达式函数
REGEXP_LIKE(x, pattern [,math_option])、
REGEXP_INSTR()、
REGEXP_REPLACE()、
REGEXP_SUBSTR()、
REGEXP_COUNT()、SELECT enamel, hiredate FROM emp WHERE hiredate REGEXP_LILE(TO_CHAR(hiredate, 'YYYY'), '^198[12]$')
聚合函数使用注意事项：
1. 如果不使用分组，SELECT后不能字段和聚合函数一起使用。
2. 聚合函数不能应用在WHERE字句中。
分组： GROUP BY 字段1, 字段2…
在GROUP BY后面多写字段可以多查询信息
分组时一般按哪些字段分组就把哪些字段一起查询出来，一般分组和聚合函数搭配使用。
HAVING:对分组后的内容进行过滤。
WHERE和HAVING对比：
- 都能实现条件过滤。
WHERE条件作用于表对字段进行过滤,HAVING条件作用于分组后的结果，一般条件中使用聚合函数。
SELECT deptno, COUNT(ROWID) FROM emp WHERE job = ‘SALESMAN’ GROUP BY deptno HAVING COUNT(ROWID) > 2;
DECODE
DECODE(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
该函数的含义如下：
IF 条件=值1 THEN
RETURN(返回值1)
ELSIF 条件=值2 THEN
RETURN(返回值2)
……
ELSIF 条件=值n THEN
RETURN(返回值n)
ELSE
RETURN(缺省值)
END IFDECODE(字段或字段的运算，值1，值2，值3）
这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多
CASE WHEN
- 简单Case函数
CASE 条件
WHEN 值1 THEN 返回值1
WHEN 值2 THEN 返回值2
…
ELSE 缺省返回值 END - Case搜索函数
CASE
WHEN 条件= 值1 THEN 返回值1
WHEN 条件= 值2 THEN 返回值2
ELSE 缺省返回值 END
--简单CASE函数
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
--CASE搜索函数
CASE
WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END PIOVT
行转列函数：
PIVOT(
聚合函数（value_column）
FOR pivot_column
IN(<column_list>)
)例子：取自这篇文章
select * from tb
--------------------------------结果------------------------------------------------------------------------------------
姓名 课程 分数
---------- ---------- -----------
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
(6 行受影响)
现在的问题是：我想根据姓名统计这个人的三门成绩，即：姓名 语文 数学 物理
首先看看使用case when end结构的时候：
select 姓名,
max(case 课程 when '语文' then 分数 else 0 end)语文,
max(case 课程 when '数学'then 分数 else 0 end)数学,
max(case 课程 when '物理'then 分数 else 0 end)物理
from tb
group by 姓名
--------------------------------结果------------------------------------------------------------------------------------
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
(2 行受影响)
这个结果就是我们想要的，然后再看看使用pivot：
select * from tb pivot(max(分数) for 课程 in (语文,数学,物理))a
--------------------------------结果------------------------------------------------------------------------------------
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
(2 行受影响)
哇，结果一模一样！这个就是我想用的结果。UNPIOVT
列转行函数：
UNPIVOT(
value_column
FOR pivot_column
IN(<column_list>)
)查询过程中每一条记录中的字段都可以拿来使用，比如当做查询条件
select dwid,dwmc,dwdm,sjdw,dwlx,(select count(*) from kzjn_sys_dw where sjdw=m.dwid) cnt from kzjn_sys_dw m where 1=1 order by px asc连表查询
交叉连接
SELECT * FROM emp, dept;
结果集记录数 = emp表记录数 * dept表记录数
笛卡尔积
等值连接
表连接时有关联条件
SELECT e.ename, e.sal, d,dname FROM emp WHERE e.deptno = d.deptno;
内连接和外连接
内连接：连表时将相匹配的记录查询出来
SELECT 字段 FROM 表1, 表2... WHERE 连接条件1 AND 连接条件2...
SELECT 字段 FROM 表1 INNER JOIN 表2 ON 连接条件1 INNER JOIN 表3 ON 连接条件2...
外连接：匹不匹配的记录都查询出来
- 左外连接：LEFT JOIN 左表中匹不匹配的记录都查询出来
- 右外连接：RIGHT JOIN 右表中匹不匹配的记录都查询出来
- 全连接：FULL OUTER JOIN 两边表中匹不匹配的记录都查询出来
oracle中对左外连接和右外连接提供了简化条件： (+)，“释放等号另一边”。
右外连接:
SELECT e.ename, d.dname FROM emp e, dept d WHERE e.deptno (+)= d.deptno;
左外连接：
SELECT e.ename, d.dname FROM emp e, dept d WHERE e.deptno =(+) d.deptno;
自连接：
SELECT e.ename, m.ename FROM emp e, emp m WHERE e.mgr = m.empno;
子查询
一个查询作为另一个查询的条件。里面的查询–子查询，外面的查询–夫查询。
SELECT * FROM emp WHERE job = (SELECT job FROM emp WHERE ename = 'SCOTT');
单行子查询：>、 <、 =、 ！=…
多行子查询：IN、 NOT IN、 ANY、 ALL、
多列子查询：
SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SCOTT');
子查询结果集作为临时表，可用作表连接。
临时表要起别名，若临时表中字段有聚合函数，则那个字段必须起别名。
分页查询
对rownum进行条件过滤时，系统从第一条数据（rownum = 1）开始匹配，只要遇到不匹配的记录就停止匹配。所以直接写rownum > 2查询不到任何记录。
解决办法：将rownum num，* 结果集作为一张临时表，此时num作为临时表的普通字段而不是系统自动添加的，所以可以使用任意条件过滤。
两层过滤：
SELECT t.* FROM (SELECT rownum num, e.* FROM emp WHERE rownum <=5) t WHERE t.num >= 3;
三层过滤：
第一层： 条件过滤、连表、分组、排序等问题。
第二层： rownum <= 大值。
第三层： rownum(别名) >= 小值。
SELECT *
FROM
(SELECT rownum num, t.*
FROM (SELECT * FROM emp WHERE sal >= 2000 GROUP BY deptno HAVING COUNT(rowid) >= 2) t
WHERE rownum <= 5)
WHERE num >= 2;
合并查询
纵向连接，两张表字段数要匹配。
UNION： 合并结果后去重复。
UNION ALL： 合并结果后不去重复。
INTERSECT： 交集。
MINUS： 差集；
DDL语句
创建表：
CREATE TABLE
oracle中数据类型：
- 数值类型
NUMBER(precision, scale) 精度自动处理(四舍五入)
INTEGER - 字符类型
CHAR(length)、 VARCHAR(length)、 VARCHAR2(length)、 NCHAR(length)、 NVARCHAR(length)、 LONG - 其他类型
DATE、 TIMESTAMPE、 BLOB
添加数据
非空字段必须赋值
INSERT INTO 表名 VALUES (值1, 值2...)
--采用这种赋值方式,表中所有字段都要插入数据
INSERT INTO 表名 (字段1, 字段2...) VALUES (值1, 值2...)
INSERT INTO 表名 SELECT语句
INSERT INTO 表名（字段1, 字段...）SELECT 语句
注意： 先创建主表在创建子表，添加数据时先添加主表数据；删除时先删除子表数据或先删除子表再删除主表或主表。
字段特征（约束）：
PRIMARY KEY、 NOT NULL、 DEFAULT、 UNIQUE、 CHECK(自定义约束)、 外键、
NOT NULL和DEFAULE同时出现时，DEFAULT必须放在NOT NULL之前。
外键约束： 字段名 字段类型 REFERENCES 引用表名(字段)
表级约束： CONSTRAINT 约束名 PRIMARY KEY / CHECK()…
添加字段： ALTER TABLE 表名 ADD (字段名 字段类型 约束)
修改字段： ALTER TABLE 表名 MODIFY (字段名 字段类型 约束)
删除字段： ALTER TABLE 表名 DROP COLUMN 字段名
ALTER TABLE 表名 DROP (字段1, 字段2…)
修改表名： RENAME 旧表名 TO 新表名
复制表： CREATE TABLE 表名 AS SELECT查询语句 字段约束不复制
CREATE TABLE 表名 (字段名1, 字段名2…) AS SELECT查询语句
修改记录： UPDATE 表名 SET 字段1 = 值1, 字段2 = 值2… [WHERE条件]
删除记录： DELETE FROM 表名 [WHERE条件]
截断表： TRUNCATE TABLE 表名
删除表和截断表的异同：
相同点：都能达到清空表中数据的功能，表结构还在。
不同点：删除表能定点删除，删除全部，截断表只能删除全部； 删除表能通过ROLLBACK回滚事务，截断表不能，但是截断表的速度更快。
对表中的操作：
增删改： 会改变表中数据，返回结果是成功操作的行数
查询：不会修改表中数据，返回结果是一个结果集。
事务
事务机制的特性通常被概括为“ACID”原则：原子性、一致性、隔离性、持续性。
提交： COMMIT
回滚： ROLLBACK
什么时候需要使用事务处理：执行多条SQL语句，希望要么一次成功，要么一次失败（银行转账问题）。
设置保存点： SAVEPOINT 保存点名
回滚到保存点： ROLLBACK TO [SAVEPOINT] 保存点名
DDL语句不能手动控制事务，DML语句可以。
视图
视图属于一种数据对象，作用于表。
好处： 保护表中数据、 方便查询
创建视图：
CREATE [OR REPLACE] [{FORCE | NOFORCE}] VIEW view_name
[(alias_name[, alias_name…])] AS subquery
[{WITH CHECK OPTION | WITH READ ONLY} CONSTRANT constraint_name];
默认scott用户不具备创建视图的权限，需要登录到管理员给scott赋予创建视图的权利。
切换用户：
CONN/CONNECT 用户名/密码[@数据库实例 AS 身份];
查看当前登录用户： SHOW USER;
授予权限： GRANT 权限 TO 用户
给scott用户授予创建视图的权限（由dba完成）：
GRANT CREATE VIEW TO scott；
视图和表的区别：
创建表时，结构数据占用存储空间。
视图作用于表，创建视图时将语句存储在数据字典中，查询视图时现提取语句，执行查询生成结果集。
可以对视图进行增、删、改操作，会影响原表（通过触发器）。
WITH READ ONLY： 只读视图
WITH CHECK OPTION： 创建视图后对数据进行DML操作时进行检查，必须符合条件才能操作。
创建视图时，聚合函数的字段必须起别名，不能有重名字段。
视图不提供ALTER功能，修改视图只能用CREATE OR REPLACE VIEW…来替换原视图。
数据字典
数据字典是oracle数据库的核心，用于描述数据库及其对象。数据字典由一些列只读表和视图组成，这些表和视图属于sys用户拥有，由oracle负责维护，用户可以用SELECT语句进行访问。
数据字典作用： 可以通过数据字典中的表及视图获取用户的数据对象等信息（数据字典中的信息经过解密和其他处理后，以数据视图的方式显示给用户）。
数据字典视图：
- user_ : 当前用户的对象信息
- all_ : 当前用户可以访问的对象信息
- dba_ : 所有对象包含的信息
序列
序列是一种数据库项，可以生成整数序列。序列生成的整数通常用来填充数值主键。
创建序列
CREATE SEQUENCE sequence_name
[START WITH start_num]
[INCREAMENT BY incrent_num]
[MAXVALUE maximum_num | NOMAXVALUE]
[MINVALUE minimum_num | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE | NOCACHE]
[ORDER | NOORDER]
序列生成一系列数字。序列中包含两个“伪列”CURRVAL和NEXTVAL，分别用来获取序列当前值和下一个值。
序列名.CURRVAL 序列名.NEXTVAL
在检查序列当前值之前必须通过检索序列下一个值对序列进行初始化。
在修改递增序列的最大值是不能小于当前值，修改递减的最小值不能大于当前值。
修改序列
ALTER SEQUENCE …;
序列的初始值不能改。
删除序列
DROP SEQUENCE 序列名;
用户
对象–> 表空间（TABLESPACE）–> 数据文件（DATAFILE）
创建用户
CREATE USER user_name IDENTIFIED BY password
[DEFAULT TABLESPACE default_tablespace]
[TRMPORARY TABLESPACE temporary_tablespace]
权限：
系统权限：在系统级控制数据库的存取和使用的机制。
系统权限：在用户级控制数据库的存取和使用的机制。
系统权限
常用的系统权限：CREATE SESSION、 CREATE SEQUENCE、 CREATE SYNONYM、 CREATE TABLE、 DROP TABLE、 CREATE PROCEDURE、 CREATE USER、 DROP USER、 CREATE VIEW、
授予系统权限：
GRANT 权限1, 权限2… TO 用户 [WITH ADMIN OPTION];
收回系统权限（不联动收回系统权限）：
REVOKE 权限1, 权限2… FROM 用户;
检查授予用户的系统特权：
SLECT * FROM user_sys_privs;
删除用户： DROP USER 用户;
对象权限
常用对象特权： SELECT、 INSERT、 UPDATE、 DELETE、 EXECUTE、 INDEX、 REFERENCES、 ALTER、
授予对象权限：
GRANT 权限1, 权限2 ON 数据库对象 TO 用户;
回收对象权限：
REVOKE 权限1, 权限2 ON 数据库 FROM 用户;
检查用户已授予的对象权限：
user_tab_privs_made、 user_tab_privs_read
角色
角色就是相关权限的命令集合，使用命令的主要目的是为了简化权限的管理。
预定义角色： CONNECT、 RESOURCE、 DBA
自定义角色：一般由dba建立，如果用普通用户建立，则需要具有CREATE ROLE的系统权限。
如果角色是公用的，可以采用不验证的方式创建：
CREATE ROLE 角色名;
角色授权：
GRANT 系统权限 TO 角色;
GRANT 对象权限 ON 数据库对象 TO 角色;
分配角色给某个用户：
将角色从用户收回(不会连带收回角色)：
REVOKE 角色名 FROM 用户名;
GRANT 角色名 TO 用户 [WITH ADMIN OPTION];
删除角色：
DROP ROLE 角色名;
表空间
创建表空间：
CREATE TABLESPACE 表空间名
DATAFILE ‘文件路径及文件名.dbf’ SIZE {K | M}
REUSE AUTOEXTEND ON NEXT 大小 MAXSIZE UNLIMITED;
路径中包含为创建的文件夹时，系统不会自定创建层级目录，会报错。
创建用户时可以指定默认的表空间，当该用户创建表时不指定表空间则使用默认表空间；若不指定用户默认表空间则使用系统提供的users表空间。
修改表空间的大小：
ALTER DATABASE DATAFILE
’D:\DATA\mytablespace.bdf’
RESIZE 新大小;
删除表空间：
DROP TABLESPACE 表空间;
索引
数据对象，提高搜索效率。
使用原则：
1、为数据量大的表使用索引。
2、为经常查询操作的字段设置索引。
不要乱用索引，这只不当反而影响查询效率。
创建B-树索引：
CREATE [UNIQUE] INDEX index_name ON
table_name (column_name [, column_name…])
TABLESPACE table_sapce;
建表时有PRIMARY KEY、 UNIQUE的字段，系统自动为这些字段加上唯一索引。
多列索引：
CREATE INDEX index_name ON myemp (ename, job);
--应用索引
... WHERE ename = '';
--应用索引
... WHERE ename = '' OR job = '';
--不应用索引
... WHERE job = '';
修改索引(只有重命名一种修改方法)：
ALTER INDEX 索引名 RENAME TO 新名;
删除索引：
DROP INDEX 索引名;
同义词
同义词(SYNONYM)是为数据对象起的别名，可以是表、视图、序列、过程、函数和包等。
同义词有两种：
- 公有同义词：创建时必须有系统权限 CREATE PUBLIC SYNONYM。
- 私有同义词：创建时必须有系统权限 CREATE PUBLIC SYNONYM。
访问公有同义词时只能使用同义词，不能使用对象.同义词；
访问私有同义词时同义词所有者可以直接使用同义词，也可以对象.同义词，非所有者只能通过对象.同义词访问。
创建同义词：
CREATE [PUBLIC] SYNONYM 同义词名 FOR 数据对象;
可以为一个数据对象设置多个同义词，使用时可使用数据对象也可以使用同义词，同义词不能重名。
查看用户拥有的同义词：
SELECT object_name FROM user_objects WHERE object_type = 'SYNONYM';
用户建立的私有同义词自己有权限删除，建立的公有同义词只能由管理员删除。
数据库编程
PL/SQL(Procedual lanuage/SQL过程化语言)是oracle在标准SQL语言上的扩展。
块（block）是pl/sql的基本编程单元，不会存储在数据库中，运行后就没了。
块结构：
[DECLARE
declare_statements
]
BEGIN
execute_statements
[EXCEPTION
exception_handing_statements
]
END;
/
打开服务器输出：
SET SERVEROUTPUT ON;
变量赋值：
变量名 数据类型 {:= 初值 | DEFAULT 初值};
SELECT 字段 INTO 变量 FROM 表名 WHERE 条件;
v_ename emp.ename %TYPE;
定义常量：
常量名 CONSTANT 数据类型 {:= 初始值 | DEAUFT 初始值};
定义常量时必须赋值，并且不能重新赋值。
异常处理：
EXCEPTION
WHEN 异常名称1 THEN
异常处理1
WHEN 异常名称2 THEN
异常处理2
条件逻辑
IF condition1 THEN
statements1
ELSIF condition1 THEN
statements2
ELSE
statements3
END IF;
简单循环：
LOOP
statements
END LOOP;
WHILE循环：
WHILE condition LOOP
statements
END LOOP;
FOR循环：
FOR loop_variable IN [REVERSE] lower_bound.. upper_bound LOOP
statemens
END LOOP
过程
过程包含一组SQL和PL/SQL语句。过程可将业务逻辑集中在数据库中，任何能够访问数据库的程序都可以使用过程。
创建过程：
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name[IN | OUT | IN OUT] type [,…])] {IS | AS}
BEGIN
procedure_body
END[procedure_name];
/
调用过程：
EXECUTE / CALL procedure_name(parameter);
在块里调用过程时，不能使用EXEC / CALL ，直接写名字即可。
特殊情况：既是IN又是OUT参数，必须有初始值。
函数
函数用于返回特定的数据，当建立函数时，在函数头部必须包含return字句。而在函数体内必须包含return语句返回数据。
创建函数：
CREATE [OR REPLACE] FUNCTION function_name
[(paremeter_name[IN | OUT | IN OUT] type [,…])]
RETURN type
{IS | AS}
BEGIN
function_body
END [function_name];
/
调用函数：和使用系统函数一样。
删除函数：
DROP FUNCTION 函数名;
过程与函数：
相同点：都是oracle数据对象，作用都是事先某些功能。
不同点：过程 参数IN、OUT、IN OUT（可以传入参数、传出参数），参数可有多个。在程序中、PL/SQL块中实现调用。
函数： 必须有且只有一个返回值。可以直接在SELECT字句中调用函数。
数据库的设计
良好的数据：库设计节省数据存储空间，能够保证数据的完整性、方便数据应用系统的开发。
糟糕的数据库设计：数据冗余，浪费存储空间、内存空间浪费、数据更新和插入的异常。
标识对象（实体-Entity）、实体的属性（Attribute）、标识对象之间的关系（Relationship）、E-R实体关系图。
三大范式：
第一范式（确保每列的原子性）：每列都是不可再分的最小数据单元。 1NF
第二范式（每个表只描述一件事情）：满足1NF，并且除主键以外的列，都依赖该主键（不能只依赖主键的一部分）。 2NF
第三范式（字段和主键直接相关）：满足2NF，并且除主键以外的其他列都不传递依赖该主键。
解决方法记录：
查询不到记录希望返回0
直接使用NVL函数查询时，有这条记录但是某一字段为null时才返回0，如果查询不到记录没有返回数据。
SELECT NVL(field, 0) FROM TABLE WHERE condition...解决办法：
SELECT NVL(SUM(field), 0) FROM TABLE WHERE condition...
907
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
