oracle学习随笔
数据库基础-------------------------*************************--------------------------------
数据库基本概念
数据库（Database, DB）
数据库管理系统（Database Management System, DBMS）
数据库管理员（ Database Administrator, DBA）
数据库系统（ Database System, DBS ）
关系型数据库
目前都有哪些主流的关系型数据库
Oracle Oralce、IBM DB2、MS SQL /Server、SyBase SyBase、IBM Informix、MySQL
SQL简介 ---------------------------*************************--------------------------------
SQL 全名是结构化查询语言（Structured Query Language），是用于数据库中的标准数据查询语言，IBM 公司最早使用在其开发的数据库系统中。
1986年10月，美国 ANSI 对 SQL 进行规范后，以此作为关系式数据库管理系统的标准语言 (ANSI X3. 135-1986)，1987年得到国际标准组织的支持下成为国际标准。
不过各种通行的数据库系统在其实践过程中都对 SQL 规范作了某些编改和扩充。所以，实际上不同数据库系统之间的SQL语言不能完全相互通用
DML语句（数据操作语言）
Insert //添加
Update //修改
Delete //删除
Select //查询
DDL语句（数据定义语言）
Create //创建数据库表
Alter //更改表结构、添加、删除、修改列长度
Drop //删除表
CREATE INDEX： //在表上建立索引
DROP INDEX： //删除索引
DCL语句（数据控制语言）
GRANT： //授予访问权限
REVOKE： //撤销访问权限
COMMIT： //提交事务处理
ROLLBACK： //事务处理回退
SAVEPOINT： //设置保存点
LOCK： //对数据库的特定部分进行锁定
事务控制语句Commit 、Rollback、Savepoint
oracle入门-------------------------*************************------------------------------
Oracle （甲骨文）：
世界上第一个支持SQL语言的数据库
Oracle特点：
全球化、跨平台的数据库
支持多用户、高性能的事务处理
强大的安全性控制和完整性控制
支持分布式数据库和分布处理
Oracle版本：
Oracle8i: I internet 表示Oracle公司要开始正式进入互联网
Oracle9i:与Oracle8i相关，性能方面更佳， 管理更人性化
Oracle10g: g(grid)网格技术
Oracle11g: g(grid)网格技术
oracle的安装和配置----------------*************************-------------------------------
安装也没啥说的：
注：安装的时候，一定要关掉防火墙。否则可能造成安装不成功
windows用户最好以管理员身份运行，以免有坑
安装目录不要有中文，以免有坑
这玩意删了重装贼讨厌
oracle的常见数据类型详解----------------*************************--------------------------
1、字符类型
CHAR：一个定长字符串，当位数不足自动用空格填充来达到其最大长度。如非NULL的CHAR(12)总是包含12字节信息。CHAR字段最多可以存储2,000字节的信息
VARCHAR2：目前这也是VARCHAR 的同义词。这是一个变长字符串，与CHAR 类型不同，它不会用空格填充至最大长度。VARCHAR2(12)可能包含0～12字节的信息。VARCHAR2最多可以存储4,000 字节的信息
2、数字类型
NUMBER：该数据类型能存储精度最多达38位的数字。每个数存储在一个变长字段中，其长度在0～22字节之间。Oracle的NUMBER类型精度很高， 远远高于许多编程语言中常规的FLOAT和DOUBLE类型。
NUMBER( p,s ) p表示精度（总长度） s表示小数位置且四舍五入
NUMBER(10,3) 10是总长度，3是小数位数的长度
3、日期类型
DATE：一个7字节的定宽日期/时间数据类型。其中总包含7个属性，包括：世纪、世纪中哪一年、月份、月中的哪一天、小时、分钟和秒。
TIMESTAMP：一个7 字节或12.字节的定宽日期/时间数据类型。它与DA
4、二进制及大文本数据
BLOB： (binary large object)在Oracle9i及以前的版本中， 这种数据类型允许存储最多4GB的数据， 在Oracle 10g及以后的版本中允许存储最多（4GB）×（数据库块大小）字节的数据。
BLOB包含不需要进行字符集转换的“二进制“数据，如果要存储电子表格、字处理文档、图像文件等就很适合采用
CLOB：（Character Large Object）在Oracle9i及以前的版本中， 这种数据类型允许存储最多4GB的数据， 在Oracle 10g及以后的版本中允许存储最多（4GB）×（数据库块大小）字节的数据。
CLOB包含要进行字符集转换的信息。这种数据类型很适合存储纯文本信息。
基本的select 语句----------------*************************-------------------------------
1:基本查询
SELECT //标识 选择哪些列
*|{[DISTINCT] column|expression [alias],...} //列 或者函数
FROM //标识从哪个表中选择
table; //表名
*注意：1:SQL 语言大小写不敏感
2:SQL 可以写在一行或者多行
3:关键字不能被缩写也不能
4:各子句一般要分行写
5:使用缩进提高语句的可读
2：算术运算符
数字和日期(日期只能加减操作)使用的算术运算符
+ 加
- 减
* 乘
/ 除
运算符优先级 * / + -
1：乘除的优先级高于加减
2：同一优先级运算符从左网游执行
3：括号()内的运算优先执行
3：列的别名
select PERSON_NAME AS PN from hr_person
重命名一个列
便于计算
关键字 as(也可以省略)
4：连接符
select PERSON_NAME ||'-'|| PERSON_AGE from hr_person //康睿-25
把列与列，列与字符连接在一起
用 ‘||’表示
可以用来‘合成’列
5:去重关键字 ‘DISTINCT’
在 SELECT 子句中使用关键字 ‘DISTINCT’ 删除重复行。
数据的过滤和排序----------------*************************-------------------------------
1：过滤
使用WHERE 子句，将不满足条件的行过滤掉
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM
table
[WHERE condition(s)];
2：比较运算
= 等于
> 大于
>= 大于等于
< 小于
<= 小于等于
<> 不等于
BETWEEN...ADN... 在两个值之间
IN(SET) 等于值列表中的一个
LIKE '%xxx%' 模糊查询
IS NULL 空值
ESCAPE 回避特殊符号的：使用转义符。例如：将[%]转为[\%]、[_]转为[\_]，然后再加上[ESCAPE ‘\’] 即可
3:逻辑运算
AND 逻辑并
OR 逻辑或
NOT 逻辑否
4：关键字优先级 （可以使用括号改变优先级顺序）
1：算术运算符
2：连接符
3：比较符
4：IS [NOT] NULL, LIKE, [NOT] IN
5：[NOT] BETWEEN
6：NOT
7：AND
8：OR
5：ORDER BY 排序子句
使用 ORDER BY 子句排序
- ASC（ascend）: 升序
- DESC（descend）: 降序
ORDER BY 子句在SELECT语句的结尾
多个列排序逗号（,）隔开即可
单行函数-------------------*************************-------------------------------
1：字符串函数
大小写控制函数
LOWER('SQL Course') sql course
UPPER('SQL Course') SQL COURSE
INITCAP('SQL Course') Sql Course
字符串控制函数
CONCAT('Hello', 'World') HelloWorld
SUBSTR('HelloWorld',1,5) Hello
LENGTH('HelloWorld') 10
INSTR('HelloWorld', 'W') 6
LPAD(salary,10,'*') *****24000
RPAD(salary, 10, '*') 24000*****
TRIM('H' FROM 'HelloWorld') elloWorld
REPLACE(‘abcd’,’b’,’m’) amcd
2：数字函数
ROUND: 四舍五入
ROUND(45.926, 2) 45.93
TRUNC: 截断
TRUNC(45.926, 2) 45.92
MOD: 求余
MOD(1600, 300) 100
3：日期函数
MONTHS_BETWEEN 两个日期相差的月数
MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') 19.6774194
ADD_MONTHS 向指定日期中加上若干月数
ADD_MONTHS ('11-JAN-94',6) '11-JUL-94'
NEXT_DAY 指定日期的下一个星期 * 对应的日期
NEXT_DAY ('01-SEP-95','FRIDAY') '08-SEP-95
LAST_DAY 本月的最后一天
LAST_DAY('01-FEB-95') '28-FEB-95'
ROUND 日期四舍五入
TRUNC 日期截断
主要常用的两个：
to_date,to_cha
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; //日期转化为字符串
select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual //字符串转日期
yyyy 年
mm 月
dd 日
day 星期 hh 小时 mi 分钟 ss 秒
4：通用函数
1：NVL 函数：将空值转换成一个已知的值可以使用的数据类型有日期、字符、数字。
函数的一般形式:
NVL(commission_pct,0)
NVL(hire_date,'01-JAN-97')
NVL(job_id,'No Job Yet')
2：NVL2 函数（有点类似于三元运算符）
NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。
3：NULLIF 函数
NULLIF (expr1, expr2) : 相等返回NULL，不等返回expr1
4：COALESCE 函数
COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。
如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE
SELECT last_name,
COALESCE(commission_pct, salary, 10) comm
FROM employees
ORDER BY commission_pct;
5：条件表达式 sql中的 if-else
1:CASE 表达式
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary
END "REVISED_SALARY"
FROM employees;
解释：如果job_id = 'IT_PROG' REVISED_SALARY就是THEN运算后的值
2:DECODE 函数
SELECT last_name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary)
REVISED_SALARY
FROM employees;
多表查询-------------------*************************-------------------------------
Oracle 连接：
1：内连接：合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
2：外链接：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。
没有匹配的行时, 结果表中相应的列为空(NULL). 外连接的 WHERE 子句条件类似于内部连接, 但连接条件中没有匹配行的表的列后面要加外连接运算符,
即用圆括号括起来的加号(+).
外连接语法：使用外连接可以查询不满足连接条件的数据，外连接的符号是 (+)。
左外连：
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column(+) = table2.column;
右外连：
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column = table2.column(+);
3：自连接：
自己连自己，比如菜单的json可以使用自连接 所有的信息保存在一张表
4：叉 集（了解一下就好了）
使用CROSS JOIN 子句使连接的表产生叉集。
叉集和笛卡尔集是相同的。
SELECT last_name, department_name
FROM employees
CROSS JOIN departments ;
5：自然连接（根据类名得到的交集）
NATURAL JOIN 子句，会以两个表中具有相同名字的列为条件创建等值连接
在表中查询满足等值条件的数据
如果只是列名相同而数据类型不同，则会产生错误。
当然join 后面可以跟有on
6：LEFT JOIN ON
RIGHT JOIN ON
FULL JOIN ON
分组函数-------------------*************************-------------------------------
组函数类型
AVG 平均值 AVG(salary)
COUNT 计数
MAX 最大 MAX(salary)
MIN 最小 MIN(salary)
STDDEV 标准差 （可用作聚集和分析函数）
SUM 求和 SUM(salary)
什么是分组函数
分组函数作用于一组数据，并对一组数据返回一个值。
select
empName //这里只能存在被分组的列或者是组函数
from
hr_person
group by empName having age > 18
注：不能在 WHERE 子句中使用组函数
可以在 HAVING 子句中使用组函数
子查询-------------------*************************-------------------------------
子查询语法
SELECT select_list
FROM
table
WHERE
expr operator
(SELECT select_list
FROM
table);
子查询 (内查询) 在主查询之前一次执行完成
子查询的结果被主查询(外查询)使用
注：1:子查询要包含在括号内
2:将子查询放在比较条件的右侧
3:单行操作符对应单行子查询，多行操作符对应多行子查询
子查询类型:
单行子查询：只返回一行
使用单行比较操作符：
=
>
>=
<
<=
<>
多行子查询：返回多行
使用多行比较操作符:
IN 等于列表中的任意一个
ANY 和子查询返回的某一个值比较 ANY和ALL 类似于一个短路和非短路的样子
ALL 和子查询返回的所有值比较
创建和管理表-------------------*************************-------------------------------
常见的数据库对象：
表：基本的数据存储集合，由行和列组成
视图：从表中抽出的逻辑上相关的数据集合
序列：提供有规律的数值
索引：提高查询的效率
同义词：给对象起别名
Oracle 数据库中的表：
用户定义的表：
-用户自己创建并维护的一组表
-包含了用户所需的信息
如：SELECT * FROM user_tables;查看用户创建的表
数据字典：
由 Oracle Server 自动创建的一组表
包含数据库信息
查询数据字典：
查看用户定义的表：SELECT table_name FROM user_tables ;
查看用户定义的各种数据库对象：SELECT DISTINCT object_type FROM user_objects ;
查看用户定义的表, 视图, 同义词和序列：SELECT * FROM user_catalog
命名规则：
表名和列名:
1：必须以字母开头
2：必须在 1–30 个字符之间
3：必须只能包含 A–Z, a–z, 0–9, _, $, 和 #
4：必须不能和用户定义的其他对象重名
5：必须不能是Oracle 的保留字
创建表：
CREATE TABLE //关键字
dept //表名
(deptno NUMBER(2), //类名及类型
dname VARCHAR2(14),
loc VARCHAR2(13));
修改表：
使用 ALTER TABLE 语句可以:
追加新的列
ALTER TABLE dept80 ADD job_id VARCHAR2(9));
修改现有的列，可以修改列的数据类型, 尺寸和默认值
ALTER TABLE dept80 MODIFY (last_name VARCHAR2(30));
为新追加的列定义默认值
ALTER TABLE dept80 MODIFY (salary number(9,2) default 1000);
删除一个列
ALTER TABLE dept80 DROP COLUMN job_id
重命名表的一个列名
ALTER TABLE dept80 RENAME COLUMN job_id TO id
删除表：
数据和结构都被删除
所有正在运行的相关事务被提交
所有相关索引被删除
DROP TABLE 语句不能回滚
ps:DROP TABLE dept80;
清空表：
TRUNCATE TABLE 语句:
删除表中所有的数据
释放表的存储空间
TRUNCATE语句不能回滚
改变对象的名称：
RENAME dept TO detail_dept
数据处理-------------------*************************-------------------------------
INSERT 语句语法：
INSERT INTO 表名 （列名，...） VALUES (值，...); //基本插入语法，如果是所有列都插入 values前面的列名可以不写
INSERT INTO emp2 SELECT * FROM employees WHERE department_id = 90 //从其它表中拷贝数据
UPDATE 语句语法
UPDATE 表名 SET 列名=值 WHERE 条件
DELETE 语句：
DELETE FROM 表名 WHERE 条件
数据库事务：
事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态
数据库事务由以下的部分组成：
一个或多个DML 语句
一个 DDL(Data Definition Language – 数据定义语言) 语句
一个 DCL(Data Control Language – 数据控制语言) 语句
开始及结束：
以第一个 DML 语句的执行作为开始
以下面的其中之一作为结束:
1：COMMIT 或 ROLLBACK 语句
2：DDL 语句（自动提交）
3：用户会话正常结束
4：系统异常终止
COMMIT和ROLLBACK语句的优点：
1：确保数据完整性
2：数据改变被提交之前预览
3：将逻辑上相关的操作分组
事物的控制：
回滚到保留点：
使用 SAVEPOINT 语句在当前事务中创建保存点
UPDATE...
SAVEPOINT update_done
使用 ROLLBACK TO SAVEPOINT 语句回滚到创建的保存点
INSERT...
ROLLBACK TO update_done
commit;
rollback;
数据库的约束-------------------*************************-------------------------------
什么是约束：
约束是表级的强制规定
有以下五种约束:
NOT NULL //非空
UNIQUE //非空
PRIMARY KEY //主键
FOREIGN KEY //外键
CHECK //检查
添加约束：
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk
FOREIGN KEY(manager_id)
REFERENCES employees(employee_id);
删除约束：
ALTER TABLE employees DROP CONSTRAINT emp_manager_fk;
无效化约束：
ALTER TABLE employees DISABLE CONSTRAINT emp_emp_id_pk
激活约束：
ALTER TABLE employees ENABLE CONSTRAINT emp_emp_id_pk
数据库视图-------------------*************************-------------------------------
视图：
视图是一种虚表
视图建立在已有表的基础上, 视图赖以建立的这些表称为基表
向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句
视图向用户提供基表数据的另一种表现形式
为什么使用视图：
控制数据访问
简化查询
避免重复访问相同的数据
创建视图：
CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;
修改视图：
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS SELECT employee_id, first_name || ' ' || last_name,
salary, department_id
FROM employees
WHERE department_id = 80;
数据库序列-------------------*************************-------------------------------
序列: 可供多个用户用来产生唯一数值的数据库对象
自动提供唯一的数值
共享对象
主要用于提供主键值
将序列值装入内存可以提高访问效率
CREATE SEQUENCE 语句：
CREATE SEQUENCE sequence
[INCREMENT BY n] --每次增长的数值
[START WITH n] --从哪个值开始
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}] --是否需要循环
[{CACHE n | NOCACHE}]; --是否缓存登录
示例sql:
Create sequence seq;
Select seq.nextval from dual;
NEXTVAL 和 CURRVAL 伪列:
NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用
CURRVAL 中存放序列的当前值
NEXTVAL 应在 CURRVAL 之前指定 ，否则会报CURRVAL 尚未在此会话中定义的错误
使用序列:
将序列值装入内存可提高访问效率
序列在下列情况下出现裂缝:
回滚
系统异常
多个表同时使用同一序列
如果不将序列的值装入内存(NOCACHE), 可使用表 USER_SEQUENCES 查看序列当前的有效值
删除序列:
使用 DROP SEQUENCE 语句删除序列
删除之后，序列不能再次被引用
DROP SEQUENCE dept_deptid_seq
数据库索引-------------------*************************-------------------------------
索引:
一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中
索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度
索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引。用户不用在查询语句中指定使用哪个索引
在删除一个表时,所有基于该表的索引会自动被删除
通过指针加速 Oracle 服务器的查询速度
通过快速定位数据的方法，减少磁盘 I/O
创建索引：
自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引
手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询
ps:
在表 EMPLOYEES的列 LAST_NAME 上创建索引
CREATE INDEX emp_last_name_idx ON employees(last_name);
什么时候创建索引:
以下情况可以创建索引:
列中数据值分布范围很广
列经常在 WHERE 子句或连接条件中出现
表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%
什么时候不要创建索引:
下列情况不要创建索引:
表很小
列不经常作为连接条件或出现在WHERE子句中
查询的数据大于2%到4%
表经常更新
查询索引:
可以使用数据字典视图 USER_INDEXES 和 USER_IND_COLUMNS 查看索引的信息
SELECT ic.index_name, ic.column_name,
ic.column_position col_pos,ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = 'EMPLOYEES';
删除索引；
使用DROP INDEX 命令删除索引
DROP INDEX index;
同义词-synonym:
使用同义词访问相同的对象:
方便访问其它用户的对象
缩短对象名字的长度
ps；
CREATE SYNONYM e FOR employees;
select * from e;
删除同义词：
DROP SYNONYM e;
SQL优化-------------------*************************-------------------------------
举个例子：
删除一张表的重复记录（ID是自增唯一主键，重复记录：其他字段都是一样）
表名：T
Id name age
1 louis 20
2 louis 20
3 jimmy 30
4 louis 20
做法一：
Delete from t where id not in (Select min(id) from t Group by name,age);
做法二：
delete from t where id in(Select distinct a2.id from t a1,t a2 where a1.id>a2.id and a1.name=a2.name and a1.age=a2.age);
做法三：
delete from t a1 where not exists(select *from t a2
where a1.id>a2.id and a1.name=a2.name and a1.age=a2.age);
1、尽量少用IN操作符
基本上所有的IN操作符都可以用EXISTS代替，在选择IN或EXIST操作时，要根据主子表数据量大小来具体考虑
2、尽量用NOT EXISTS或者外连接替代NOT IN操作符
因为NOT IN不能应用表的索引
3、尽量不用“<>”或者“!=”操作符
不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。比如：a<>0 改为 a>0 or a<0
4、在设计表时，把索引列设置为NOT NULL
判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。
5、尽量不用通配符“%”或者“_”作为查询字符串的第一个字符
当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用。比如用T表中Column1 LIKE „%5400%‟ 这个条件会产生全表扫描，
如果改成Column1 ‟X5400%‟ OR Column1 LIKE ‟B5400%‟ 则会利用Column1的索引进行两个范围的查询，性能肯定大大提高。
6、Where子句中避免在索引列上使用计算
7、用“>=”替代“>”
8、使用表的别名，并将之作为每列的前缀
当在Sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀。这样可以减少解析时间
9、进行了显式或隐式的运算的字段不能进行索引
数据库基础-------------------------*************************--------------------------------数据库基本概念数据库（Database, DB）数据库管理系统（Database Management System, DBMS）数据库管理员（ Database Administrator, DBA）数据库系统（ Database System, DBS ）关系型数据库目前都有哪些主流的关系型数据库Oracle Oralce、IBM DB2、MS SQL /Server、SyBase SyBase、IBM Informix、MySQL
SQL简介 ---------------------------*************************--------------------------------SQL 全名是结构化查询语言（Structured Query Language），是用于数据库中的标准数据查询语言，IBM 公司最早使用在其开发的数据库系统中。1986年10月，美国 ANSI 对 SQL 进行规范后，以此作为关系式数据库管理系统的标准语言 (ANSI X3. 135-1986)，1987年得到国际标准组织的支持下成为国际标准。不过各种通行的数据库系统在其实践过程中都对 SQL 规范作了某些编改和扩充。所以，实际上不同数据库系统之间的SQL语言不能完全相互通用DML语句（数据操作语言）Insert //添加Update //修改Delete //删除Select //查询DDL语句（数据定义语言）Create //创建数据库表Alter //更改表结构、添加、删除、修改列长度Drop //删除表CREATE INDEX：//在表上建立索引DROP INDEX： //删除索引DCL语句（数据控制语言）GRANT：//授予访问权限REVOKE：//撤销访问权限COMMIT：//提交事务处理ROLLBACK：//事务处理回退SAVEPOINT：//设置保存点LOCK：//对数据库的特定部分进行锁定
事务控制语句Commit 、Rollback、Savepointoracle入门-------------------------*************************------------------------------Oracle （甲骨文）：世界上第一个支持SQL语言的数据库Oracle特点：全球化、跨平台的数据库支持多用户、高性能的事务处理强大的安全性控制和完整性控制支持分布式数据库和分布处理Oracle版本：Oracle8i: I internet 表示Oracle公司要开始正式进入互联网Oracle9i:与Oracle8i相关，性能方面更佳， 管理更人性化Oracle10g: g(grid)网格技术Oracle11g: g(grid)网格技术oracle的安装和配置----------------*************************-------------------------------安装也没啥说的：注：安装的时候，一定要关掉防火墙。否则可能造成安装不成功windows用户最好以管理员身份运行，以免有坑安装目录不要有中文，以免有坑这玩意删了重装贼讨厌
oracle的常见数据类型详解----------------*************************--------------------------1、字符类型CHAR：一个定长字符串，当位数不足自动用空格填充来达到其最大长度。如非NULL的CHAR(12)总是包含12字节信息。CHAR字段最多可以存储2,000字节的信息VARCHAR2：目前这也是VARCHAR 的同义词。这是一个变长字符串，与CHAR 类型不同，它不会用空格填充至最大长度。VARCHAR2(12)可能包含0～12字节的信息。VARCHAR2最多可以存储4,000 字节的信息2、数字类型NUMBER：该数据类型能存储精度最多达38位的数字。每个数存储在一个变长字段中，其长度在0～22字节之间。Oracle的NUMBER类型精度很高， 远远高于许多编程语言中常规的FLOAT和DOUBLE类型。NUMBER( p,s ) p表示精度（总长度） s表示小数位置且四舍五入NUMBER(10,3) 10是总长度，3是小数位数的长度3、日期类型DATE：一个7字节的定宽日期/时间数据类型。其中总包含7个属性，包括：世纪、世纪中哪一年、月份、月中的哪一天、小时、分钟和秒。TIMESTAMP：一个7 字节或12.字节的定宽日期/时间数据类型。它与DA4、二进制及大文本数据BLOB： (binary large object)在Oracle9i及以前的版本中， 这种数据类型允许存储最多4GB的数据， 在Oracle 10g及以后的版本中允许存储最多（4GB）×（数据库块大小）字节的数据。BLOB包含不需要进行字符集转换的“二进制“数据，如果要存储电子表格、字处理文档、图像文件等就很适合采用 CLOB：（Character Large Object）在Oracle9i及以前的版本中， 这种数据类型允许存储最多4GB的数据， 在Oracle 10g及以后的版本中允许存储最多（4GB）×（数据库块大小）字节的数据。CLOB包含要进行字符集转换的信息。这种数据类型很适合存储纯文本信息。基本的select 语句----------------*************************-------------------------------1:基本查询SELECT//标识 选择哪些列*|{[DISTINCT] column|expression [alias],...} //列 或者函数FROM//标识从哪个表中选择table;//表名*注意：1:SQL 语言大小写不敏感 2:SQL 可以写在一行或者多行 3:关键字不能被缩写也不能 4:各子句一般要分行写 5:使用缩进提高语句的可读2：算术运算符数字和日期(日期只能加减操作)使用的算术运算符+加-减*乘/除运算符优先级 * / + -1：乘除的优先级高于加减2：同一优先级运算符从左网游执行3：括号()内的运算优先执行3：列的别名select PERSON_NAME AS PN from hr_person重命名一个列便于计算关键字 as(也可以省略)4：连接符select PERSON_NAME ||'-'|| PERSON_AGE from hr_person //康睿-25把列与列，列与字符连接在一起用 ‘||’表示 可以用来‘合成’列5:去重关键字 ‘DISTINCT’在 SELECT 子句中使用关键字 ‘DISTINCT’ 删除重复行。
数据的过滤和排序----------------*************************-------------------------------1：过滤使用WHERE 子句，将不满足条件的行过滤掉SELECT*|{[DISTINCT] column|expression [alias],...}FROMtable[WHEREcondition(s)];2：比较运算=等于>大于>=大于等于<小于<=小于等于<>不等于BETWEEN...ADN...在两个值之间IN(SET)等于值列表中的一个LIKE '%xxx%'模糊查询IS NULL 空值ESCAPE 回避特殊符号的：使用转义符。例如：将[%]转为[\%]、[_]转为[\_]，然后再加上[ESCAPE ‘\’] 即可3:逻辑运算AND逻辑并OR逻辑或NOT逻辑否4：关键字优先级 （可以使用括号改变优先级顺序）1：算术运算符2：连接符3：比较符4：IS [NOT] NULL, LIKE, [NOT] IN5：[NOT] BETWEEN6：NOT 7：AND8：OR5：ORDER BY 排序子句使用 ORDER BY 子句排序- ASC（ascend）: 升序- DESC（descend）: 降序ORDER BY 子句在SELECT语句的结尾多个列排序逗号（,）隔开即可单行函数-------------------*************************-------------------------------1：字符串函数大小写控制函数LOWER('SQL Course')sql courseUPPER('SQL Course')SQL COURSEINITCAP('SQL Course')Sql Course字符串控制函数CONCAT('Hello', 'World')HelloWorldSUBSTR('HelloWorld',1,5)HelloLENGTH('HelloWorld')10INSTR('HelloWorld', 'W')6LPAD(salary,10,'*')*****24000RPAD(salary, 10, '*')24000*****TRIM('H' FROM 'HelloWorld')elloWorldREPLACE(‘abcd’,’b’,’m’)amcd
2：数字函数ROUND: 四舍五入ROUND(45.926, 2)45.93TRUNC: 截断TRUNC(45.926, 2)45.92MOD: 求余MOD(1600, 300)1003：日期函数MONTHS_BETWEEN两个日期相差的月数MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') 19.6774194ADD_MONTHS向指定日期中加上若干月数ADD_MONTHS ('11-JAN-94',6)'11-JUL-94'NEXT_DAY指定日期的下一个星期 * 对应的日期NEXT_DAY ('01-SEP-95','FRIDAY') '08-SEP-95LAST_DAY本月的最后一天LAST_DAY('01-FEB-95')'28-FEB-95'ROUND日期四舍五入 TRUNC 日期截断主要常用的两个：to_date,to_chaselect to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; //日期转化为字符串 select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual //字符串转日期yyyy 年mm 月dd 日day 星期 hh 小时 mi 分钟 ss 秒
4：通用函数1：NVL 函数：将空值转换成一个已知的值可以使用的数据类型有日期、字符、数字。函数的一般形式:NVL(commission_pct,0)NVL(hire_date,'01-JAN-97')NVL(job_id,'No Job Yet')2：NVL2 函数（有点类似于三元运算符）NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。3：NULLIF 函数NULLIF (expr1, expr2) : 相等返回NULL，不等返回expr1 4：COALESCE 函数COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE SELECT last_name,COALESCE(commission_pct, salary, 10) commFROM employeesORDER BY commission_pct;5：条件表达式 sql中的 if-else1:CASE 表达式SELECT last_name, job_id, salary, CASE job_id WHEN 'IT_PROG' THEN 1.10*salary WHEN 'ST_CLERK' THEN 1.15*salary WHEN 'SA_REP' THEN 1.20*salary ELSE salary END "REVISED_SALARY"FROM employees;解释：如果job_id = 'IT_PROG' REVISED_SALARY就是THEN运算后的值2:DECODE 函数SELECT last_name, job_id, salary, DECODE(job_id, 'IT_PROG', 1.10*salary, 'ST_CLERK', 1.15*salary, 'SA_REP', 1.20*salary,salary) REVISED_SALARYFROM employees;
多表查询-------------------*************************-------------------------------Oracle 连接：1：内连接：合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
2：外链接：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL). 外连接的 WHERE 子句条件类似于内部连接, 但连接条件中没有匹配行的表的列后面要加外连接运算符, 即用圆括号括起来的加号(+). 外连接语法：使用外连接可以查询不满足连接条件的数据，外连接的符号是 (+)。左外连：SELECTtable1.column, table2.columnFROMtable1, table2WHEREtable1.column(+) = table2.column;右外连：SELECTtable1.column, table2.columnFROMtable1, table2WHEREtable1.column = table2.column(+);3：自连接：自己连自己，比如菜单的json可以使用自连接 所有的信息保存在一张表4：叉 集（了解一下就好了）使用CROSS JOIN 子句使连接的表产生叉集。叉集和笛卡尔集是相同的。 SELECT last_name, department_nameFROM employeesCROSS JOIN departments ;5：自然连接（根据类名得到的交集）NATURAL JOIN 子句，会以两个表中具有相同名字的列为条件创建等值连接在表中查询满足等值条件的数据如果只是列名相同而数据类型不同，则会产生错误。当然join 后面可以跟有on 6：LEFT JOIN ON RIGHT JOIN ON FULL JOIN ON分组函数-------------------*************************-------------------------------组函数类型AVG 平均值 AVG(salary)COUNT 计数 MAX 最大 MAX(salary)MIN 最小 MIN(salary)STDDEV标准差 （可用作聚集和分析函数）SUM求和 SUM(salary)什么是分组函数分组函数作用于一组数据，并对一组数据返回一个值。select empName //这里只能存在被分组的列或者是组函数from hr_person group by empName having age > 18注：不能在 WHERE 子句中使用组函数可以在 HAVING 子句中使用组函数子查询-------------------*************************-------------------------------子查询语法SELECTselect_listFROMtableWHEREexpr operator(SELECTselect_listFROMtable);子查询 (内查询) 在主查询之前一次执行完成子查询的结果被主查询(外查询)使用 注：1:子查询要包含在括号内2:将子查询放在比较条件的右侧3:单行操作符对应单行子查询，多行操作符对应多行子查询子查询类型:单行子查询：只返回一行使用单行比较操作符：=>>=<<=<>多行子查询：返回多行使用多行比较操作符:IN等于列表中的任意一个ANY和子查询返回的某一个值比较 ANY和ALL 类似于一个短路和非短路的样子 ALL和子查询返回的所有值比较
创建和管理表-------------------*************************-------------------------------常见的数据库对象：表：基本的数据存储集合，由行和列组成视图：从表中抽出的逻辑上相关的数据集合序列：提供有规律的数值索引：提高查询的效率同义词：给对象起别名Oracle 数据库中的表：用户定义的表：-用户自己创建并维护的一组表-包含了用户所需的信息如：SELECT * FROM user_tables;查看用户创建的表数据字典：由 Oracle Server 自动创建的一组表包含数据库信息查询数据字典：查看用户定义的表：SELECT table_name FROMuser_tables ;查看用户定义的各种数据库对象：SELECT DISTINCT object_type FROM user_objects ;查看用户定义的表, 视图, 同义词和序列：SELECT * FROMuser_catalog 命名规则：表名和列名:1：必须以字母开头2：必须在 1–30 个字符之间3：必须只能包含 A–Z, a–z, 0–9, _, $, 和 #4：必须不能和用户定义的其他对象重名5：必须不能是Oracle 的保留字创建表：CREATE TABLE //关键字dept //表名 (deptno NUMBER(2),//类名及类型dname VARCHAR2(14),loc VARCHAR2(13));修改表：使用 ALTER TABLE 语句可以:追加新的列ALTER TABLE dept80 ADDjob_id VARCHAR2(9));修改现有的列，可以修改列的数据类型, 尺寸和默认值ALTER TABLEdept80 MODIFY(last_name VARCHAR2(30));为新追加的列定义默认值ALTER TABLEdept80 MODIFY(salary number(9,2) default 1000);删除一个列ALTER TABLE dept80 DROP COLUMN job_id重命名表的一个列名ALTER TABLE dept80 RENAME COLUMN job_id TO id删除表：数据和结构都被删除所有正在运行的相关事务被提交所有相关索引被删除DROP TABLE 语句不能回滚ps:DROP TABLE dept80;清空表：TRUNCATE TABLE 语句:删除表中所有的数据释放表的存储空间TRUNCATE语句不能回滚改变对象的名称：RENAME dept TO detail_dept数据处理-------------------*************************-------------------------------INSERT 语句语法：INSERT INTO表名 （列名，...） VALUES(值，...); //基本插入语法，如果是所有列都插入 values前面的列名可以不写INSERT INTO emp2 SELECT * FROM employees WHERE department_id = 90 //从其它表中拷贝数据UPDATE 语句语法UPDATE 表名 SET 列名=值 WHERE 条件DELETE 语句：DELETE FROM 表名 WHERE 条件数据库事务：事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态数据库事务由以下的部分组成：一个或多个DML 语句一个 DDL(Data Definition Language – 数据定义语言) 语句一个 DCL(Data Control Language – 数据控制语言) 语句开始及结束：以第一个 DML 语句的执行作为开始以下面的其中之一作为结束:1：COMMIT 或 ROLLBACK 语句2：DDL 语句（自动提交）3：用户会话正常结束4：系统异常终止COMMIT和ROLLBACK语句的优点：1：确保数据完整性2：数据改变被提交之前预览3：将逻辑上相关的操作分组事物的控制：回滚到保留点：使用 SAVEPOINT 语句在当前事务中创建保存点UPDATE...SAVEPOINT update_done使用 ROLLBACK TO SAVEPOINT 语句回滚到创建的保存点INSERT...ROLLBACK TO update_donecommit;rollback;数据库的约束-------------------*************************-------------------------------什么是约束：约束是表级的强制规定有以下五种约束:NOT NULL//非空UNIQUE //非空PRIMARY KEY//主键FOREIGN KEY//外键CHECK//检查添加约束：ALTER TABLE employeesADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);删除约束：ALTER TABLE employees DROP CONSTRAINT emp_manager_fk;无效化约束：ALTER TABLEemployees DISABLE CONSTRAINTemp_emp_id_pk激活约束：ALTER TABLEemployees ENABLE CONSTRAINTemp_emp_id_pk数据库视图-------------------*************************-------------------------------视图：视图是一种虚表视图建立在已有表的基础上, 视图赖以建立的这些表称为基表向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句视图向用户提供基表数据的另一种表现形式为什么使用视图：控制数据访问简化查询避免重复访问相同的数据创建视图：CREATE VIEW empvu80 AS SELECT employee_id, last_name, salaryFROM employeesWHERE department_id = 80;修改视图：CREATE OR REPLACE VIEW empvu80 (id_number, name, sal, department_id)AS SELECT employee_id, first_name || ' ' || last_name, salary, department_id FROM employees WHERE department_id = 80; 数据库序列-------------------*************************-------------------------------序列: 可供多个用户用来产生唯一数值的数据库对象自动提供唯一的数值共享对象主要用于提供主键值将序列值装入内存可以提高访问效率CREATE SEQUENCE 语句：CREATE SEQUENCE sequence [INCREMENT BY n] --每次增长的数值 [START WITH n] --从哪个值开始 [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] --是否需要循环 [{CACHE n | NOCACHE}]; --是否缓存登录示例sql:Create sequence seq;Select seq.nextval from dual;NEXTVAL 和 CURRVAL 伪列:NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用CURRVAL 中存放序列的当前值 NEXTVAL 应在 CURRVAL 之前指定 ，否则会报CURRVAL 尚未在此会话中定义的错误使用序列:将序列值装入内存可提高访问效率序列在下列情况下出现裂缝:回滚系统异常多个表同时使用同一序列如果不将序列的值装入内存(NOCACHE), 可使用表 USER_SEQUENCES 查看序列当前的有效值删除序列:使用 DROP SEQUENCE 语句删除序列删除之后，序列不能再次被引用DROP SEQUENCE dept_deptid_seq数据库索引-------------------*************************-------------------------------索引:一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引。用户不用在查询语句中指定使用哪个索引在删除一个表时,所有基于该表的索引会自动被删除通过指针加速 Oracle 服务器的查询速度通过快速定位数据的方法，减少磁盘 I/O创建索引：自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询ps:在表 EMPLOYEES的列 LAST_NAME 上创建索引CREATE INDEX emp_last_name_idx ON employees(last_name);什么时候创建索引:以下情况可以创建索引:列中数据值分布范围很广列经常在 WHERE 子句或连接条件中出现表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%什么时候不要创建索引:下列情况不要创建索引:表很小列不经常作为连接条件或出现在WHERE子句中查询的数据大于2%到4%表经常更新查询索引:可以使用数据字典视图 USER_INDEXES 和 USER_IND_COLUMNS 查看索引的信息SELECTic.index_name, ic.column_name,ic.column_position col_pos,ix.uniquenessFROMuser_indexes ix, user_ind_columns icWHEREic.index_name = ix.index_nameANDic.table_name = 'EMPLOYEES';删除索引；使用DROP INDEX 命令删除索引DROP INDEX index;同义词-synonym:使用同义词访问相同的对象:方便访问其它用户的对象缩短对象名字的长度ps；CREATE SYNONYM e FOR employees;select * from e;删除同义词：DROP SYNONYM e;
SQL优化-------------------*************************-------------------------------举个例子：删除一张表的重复记录（ID是自增唯一主键，重复记录：其他字段都是一样）表名：TId name age1 louis 202 louis 203 jimmy 304 louis 20做法一：Delete from t where id not in (Select min(id) from t Group by name,age);做法二： delete from t where id in(Select distinct a2.id from t a1,t a2 where a1.id>a2.id and a1.name=a2.name and a1.age=a2.age);做法三：delete from t a1 where not exists(select *from t a2where a1.id>a2.id and a1.name=a2.name and a1.age=a2.age);1、尽量少用IN操作符基本上所有的IN操作符都可以用EXISTS代替，在选择IN或EXIST操作时，要根据主子表数据量大小来具体考虑2、尽量用NOT EXISTS或者外连接替代NOT IN操作符因为NOT IN不能应用表的索引3、尽量不用“<>”或者“!=”操作符不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。比如：a<>0 改为 a>0 or a<04、在设计表时，把索引列设置为NOT NULL判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。5、尽量不用通配符“%”或者“_”作为查询字符串的第一个字符当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用。比如用T表中Column1 LIKE „%5400%‟ 这个条件会产生全表扫描，如果改成Column1 ‟X5400%‟ OR Column1 LIKE ‟B5400%‟ 则会利用Column1的索引进行两个范围的查询，性能肯定大大提高。6、Where子句中避免在索引列上使用计算7、用“>=”替代“>”8、使用表的别名，并将之作为每列的前缀当在Sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀。这样可以减少解析时间9、进行了显式或隐式的运算的字段不能进行索引
