oracle详解

Oracle 数据库管理系统

1. 数据库简介

数据仓库，数据管理软。Oracle通过用户的不同权限管理数据库
优点：
支持数据类型
文件储存安全
支持多用户访问
支持数据量大
服务：（以实例orcl）
OracleServiceOrcl Oracle核心服务
OracleORCLTNSLinstener Oracle接受外部访问的服务
Oracle常用客户端：
①SQL PLUS
②iSQL PLUS （http://localhost:1158/em）
③第三方客户端 例如：PLSQL 、Navicat

2. sql – 简单查询、去重、排序、条件查询

Structure Query Language
①. 简单查询
名词：表（Table）, 字段（Column）, 行（Row）, 主键（Primary key）, 用户（Account）
语法：select 列名1，列名2，… from 表;

a, 查询部分列：
select 字段1，字段2 from 表;

b, 查询所有列：
select 字段1，… 字段n from 表; (实际开发应用，可读性好)
select * from 表；

c, 查询结果的字段重命名（别名）
select 列名1 as 别名1，列名2 as 别名2 from 表;

d, 对查询结果进行字符串拼接（‘||’）
select first_name || ‘·’ || last_name from employees;

e, 对查询的结果做算术运算（+ - * /）
select salary * 10 from employees;

f, 增加筛选条件
select salary form employees where employee_id = 100;
②. 去重
select distinct 列名 from 表;
③. 排序
select * from 表 order by 字段1 asc/desc，字段2 asc/desc; （先对字段1排序，如果相同，再对字段2排序）
④. 条件查询
a, 等值查询
select * form employees where salary = 7000;

b, 多条件查询
	select * form employees where employee_id = 100 and salary = 7000;
	select * form employees where salary = 2300 or salary = 7000;

c, 不等值查询（逻辑判断：>  <  >=  <=  <>不等于  !=不等于）
	select * from employees where salary > 1000;		

d, 区间查询（ 闭区间 [临近小值， 临近大值] ）
	select * from employees where salary >= 1000 and salary <= 10000;
	select * from employees where salary between 1000 and 10000;

e, is null 、is not null
	select * form employees where commission_pct is null;

select * form employees where commission_pct is not null;

f, 枚举查询（in）
	select * from employees where employee_id in(100, 103, 104);
	也可以用or语句
g, 模糊查询 （like 、 not like ） 、（‘_’占位符， ‘%’通配符）
	select * from employees where first_name is not like ‘%L%’; （名字不含L）

select * from employees where first_name is like ‘_ _ _ _%’; （名字长度至少是4）

3. sql – dual、sysdate、systimestamp、单行(组)函数、sql书写(执行)顺序

①. 特殊关键字dual、sysdate、systimestamp
dual:虚表，一行一列的表
1.站在数据的角度，无意义
2.作用是维护sql语句的完整性

	例如：
	select 1000*1000 from dual;

sysdate: 当前系统时间(年 月 日 时 分 秒)
	select sysdate from dual;
systimestamp: 时间戳，也是当前系统时间 (年 月 日 时 分 秒 毫秒)
	select systimestamp form dual;

②. 函数-单行函数
对查询的每条数据进行一条函数的处理

常用：
	to_char( 被转化的日期，’日期格式关键词’); 			将日期转化为字符串
	to_date( ‘被转化的日期字符串’，’日期格式关键词’);	将字符串转化为日期

日期格式关键词：yyyy（年），mm（月），dd（日），hh24（时，24小时制），mi（分），ss（秒），day（星期）

用法：
	select to_char(sysdate, ‘yyyy-mm-dd’) from dual; 		查询日期转化为日期字符串：年 月 日
	select to_date(‘2001-3-23’, ‘yyyy-mm-dd’) from dual;  	将日期字符串转化为日期
	select to_char(to_date(‘2000-12-2’, ‘yyyy-mm-dd’), ‘day’) form dual; 	将日期字符串最后转为日期：星期

③. 函数-组函数
查询表数据进行分组、对每组数据处理产生一条结果

常见组函数：
	max(字段)			所查询字段的最大值
	min(字段)			所查询字段的最小值
	avg(字段)				所查询字段的平均值
	sum(字段)			所查询字段的总和
	count(字段 或 *)		所查询字段的行数
注意：组函数对null不做任何统计，自动忽略

分组：
	语法：group by 字段名1，字段名2;
	注意：	①group by 在where条件筛选之后
			②select后可以查询分组的依据字段，组函数的统计（其他不能查询）

分组的过滤：
	语法：group by 字段 having 条件;
	注意：	①where是在分组前对数据过滤
			②having是在分组后对数据惊醒过滤
			③如果where和having同时满足查询要求时，优先选择where

④. sql语句顺序
①书写顺序：select…from…where…group by…having…order by [asc/desc]
②执行顺序：from(确定表) ---> where(分组前过滤) ---> group by(分组) ---> having(分组后过滤) ---> select(查询) ---> order by(排序)

4. sql – 子查询、伪列、分页查询、表连接

①. 子查询
子查询可代替表、属性值

①子查询结果是1行1列
例：查询工资最高的员工信息
select * from employees where salary = (select max(salary) from employees);
②子查询结果是n行1列
例：查询和“king”一个姓的并且在同一部门的员工的信息
select * from employees where department_id in(select department_id from employees where last_name = ‘king’);
③子查询结果是n行n列
例：查询工资前五位到前十位之间的员工信息
select * from (select employee_id, first_name, last_name,rownum as rn from (select * from employees order by salary desc)) where rn >= 5 and rn <= 10
②. 伪列、分页查询
伪列：
rowid 行的物理地址，不存在于任何表，可查询
rownum 对查询满足条件的结果数据进行编号（从1开始）
用法：
select rowid,rownum,其他字段 from 表；
分页：
利用子查询，将查询所有表数据再加上序列（rownum）作为一个临时表

例如：
①查询薪资从第五到第十位的员工信息
**先按工资降序排序，在加上序号字段形成临时表
select * from (select employees_id, first_name, last_name, salary, rownum as rn from (select * from employees order by salary desc)) where rn >= 5 and rn <= 10

②对表加序号
select employees.*, rownum as rn from employees;
③. 表连接

4. 将多张表连接为一张表进行处理（放在from后）

1. 内连接
   关键词：…（inner）join … on inner可以省略
   语法：表1 （inner）join 表2 on 表1.xxx = 表2.xxx
   特点：①合并表1存在且表2也与之对应存在的数据②不保留表1存在且在表2中对应不存在的元组数据（与外连接对比）
2. 外连接
   ①左外连
   关键词：left (outer) join … on outer可以省略
   语法：表1 left join 表2 on 表1.xxx = 表2.xxx
   特点：合并表1和表2，并且会保留表1存在且表2对应不存在的元组，并对其补充为空
   ②右外连
   语法：表1 right join 表2 on 条件
3. 扩展
   如果有两张以上的表进行连接时，先连接两个后成为一个表，再连接下一个，依次连接

5. 表(数据类型、约束)、修改表结构

表的创建：create table 表名（字段名 数据类型 [约束]）；
表的删除：drop table 表名；

oracle表包括： 表名、字段、数据类型、[约束]
其中：
（1）数据类型
①数字
number 默认长度为38，小数长度为0
number（n） 最大长度为n
number（n，m） 最大长度为n，其中小数部分长度为m
integer 相当于number（38）
②字符串
char（n） 字符串长度固定为n，不足的用空白字符补充
varchar2（n） 字符串最大长度为n，长度为填入的字符串长度
注：char的数据管理效率高，但空白字符浪费空间，varchar2节省空间
例：名字 varchar2
手机号 char（11） （因为不作运算和比较）
邮箱 varchar2
③日期
date 精确到秒
例：insert into 表(日期属性) values(to_date(‘1999-8-9’, ‘yyyy-mm-dd’));

（2）约束
①主键约束 primary key
（非空、唯一）

②唯一约束	unique
	（表明列值不重复，例如手机号）

③非空约束	not null
	（表明列值不为空）

④检查约束	check（约束语法）
	例如：	--- phone_num char(11) check(length(phone_num) = 11)
			--- email varchar2(100) check(email like ‘%@%’)
			--- sex char(2) check(sex in(‘男’, ‘女’))
	
⑥外键约束(forienkey)		references 其他表名（字段名）
	例如：  --- stu_class char(20) references classes(class_id),

（3）修改表结构：
①编辑表的字段：
语法:ALTER TABLE 表名 MODIFY(列名,数据类型);
　 eg1a:alter table skate_test modify (author number(10,0) )
在修改列的长度时候,只能编辑比现有字段实际存的长度还要大
eg2:alter table skate_test modify (author varchar2(10) )
在修改列的数据类型的时候,所修改的列必须为空
②增加一个列：
语法: ALTER TABLE 表名 ADD(列名,数据类型);
eg1：ALTER TABLE skate_test ADD(author NUMBER(38,0) not null);
③改列名：
语法:ALTER TABLE 表名 RENAME COLUMN 当前列名 TO 新列名;
eg1：ALTER TABLE skate_test RENAME COLUMN author TO authorer_new
④删除一个列：
语法:ALTER TABLE 表名 DROP COLUMN 列名;
eg1：alter table skate_test drop column author
⑤改表名：
语法:ALTER TABLE 当前表名 RENAME TO 新表名;
eg1：alter table skate_test rename to test_sakte
⑥给表加注释：
comment column on 表名.列名 is '注释内容'; //修改表的列的注释
COMMENT ON TABLE MOVO_NEW.TEST_SAKTE IS '注释内容'; //修改表的注释

6. 视图、序列、索引

删除：drop view/sequence/index 视图名/序列名/索引

① 视图：将查询结果作为视图，供查询使用，虚拟表
创建视图：create view 视图名 as select语句;
查看视图：select 列名 from 视图名;
优点：①简化SQL语句 ②安全性，可以实现对开发人员屏蔽原表名和字段名
注意：视图不会提高查询效率，本质是存储了select语句，基本不占用硬盘空间

② 序列：oracle提供的工具，产生一系列连续且不重复的值；
创建序列：create sequence 序列名 [start with 数字] // 不写start，默认从1开始；
获取序列值：序列名.nextval

例如：
	create sequence seq_keyValue start whith 100001;
	…
	insert into employee(id, name) values(seq_keyValue.nextval, ‘xyz’);

③ 索引：
创建索引：create index 索引名 on 表名(字段名)；
使用序列：不需要手动使用，默认会使用索引的字段条件查询，此时的数据会自动使用索引查找数据
特点：
①本身会占用磁盘空间
②对于增、删、改，会有维护，降低DML效率
⑤主键(primary key)和唯一(unique) 数据库会自动添加索引

7. 事务

概念：是数据库的最小单元，有多个sql语句组成的一个整体
执行结果：对个sql语句执行一个功能，要么全部成功，要么全部失败
成功结束：commit 提交
失败结束：rollback 回滚
实现原理：
①数据库会为每个客户端都分配一个独立的回滚段（临时空间- rollback seagment）
②commit是将回滚段中的sql执行结果一次性同步到数据库中；rollback是将回滚段里的结果舍弃

事务特性：
A(Atomic) 原子性：对个sql为一个整体
C(Consistency) 一致性：无论执行成功与否，数据都会合理
I(Isolation) 隔离性：事务与事务之间是相互独立的
D(Durability) 持久性：事务结束，对数据修改是永久性的

8. 复制表结构、批量添加设计、数据复制

（1）复制表结构
create table newtable as select * from tablename where 1=2;
（2）批量添加设计
insert into 表名 select 序列名.nextval , t.* from
(select 字段1, 字段2,… from dual
union select 字段1, 字段2,… from dual
union select 字段1, 字段2,… from dual
…
) t;
补充：将t_text1表中的数据复制到t_text2表中
insert into t_text2 (id2,name2,sex2) select id,name,sex from t_text1;