iyiw oracle_oracle详解

Oracle 数据库管理系统

1. 数据库简介

数据仓库，数据管理软。Oracle通过用户的不同权限管理数据库

优点：

支持数据类型

文件储存安全

支持多用户访问

支持数据量大

服务：(以实例orcl)

OracleServiceOrclOracle核心服务

OracleORCLTNSLinstenerOracle接受外部访问的服务

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后)

内连接

关键词：…(inner)join … on inner可以省略

语法：表1 (inner)join 表2 on 表1.xxx = 表2.xxx

特点：①合并表1存在且表2也与之对应存在的数据②不保留表1存在且在表2中对应不存在的元组数据(与外连接对比)

外连接

①左外连

关键词：left (outer) join … on outer可以省略

语法：表1 left join 表2 on 表1.xxx = 表2.xxx

特点：合并表1和表2，并且会保留表1存在且表2对应不存在的元组，并对其补充为空

②右外连

语法：表1 right join 表2 on 条件

扩展

如果有两张以上的表进行连接时，先连接两个后成为一个表，再连接下一个，依次连接

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;