Oracle综合运用

一.DDL数据库操作语言

创建表空间

表空间是 ORACLE 数据库的逻辑单元。 数据库–>表空间–>数据文
件（物理结构）
关联一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立多个表
- 对表空间的操作必须要在安装Oracle的计算机中进行,如果是在虚拟机中安装Oraclename就要在虚拟机中搞

--选择表空间(逻辑上的)
CREATE TABLESPACE SPC_CUIXIAOMING_001
--指定表空间对应的数据文件(物理上的)
DATAFILE 'E:\dataFiles\CUIXIAOMING001' 
--指定文件的大小
SIZE 30M   
--是否自动增加大小,学习的时候一定要选OFF!!!!不然把你硬盘填满!!!!!
AUTOEXTEND OFF;  

--如果选自增的话要指定每次自增的大小
--比如autoextend on next 10M

用户

1.创建用户

CREATE USER cuixiaoming001
-- 指定用户密码
IDENTIFIED BY orcl   
-- 指定用户对应的表空间
DEFAULT TABLESPACE SPC_CUIXIAOMING_001;  

• oracle 数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户下的。
  2.给用户赋权限
• 新创建的用户没有任何的权限
• Oracle 中已存在三个重要的角色：connect 角色，resource 角色，dba 角色
  
  • CONNECT 角色： –是授予最终用户的典型权利，最基本的
    ALTER SESSION –修改会话
    CREATE CLUSTER –建立聚簇
    CREATE DATABASE LINK –建立数据库链接
    CREATE SEQUENCE –建立序列
    CREATE SESSION –建立会话
    CREATE SYNONYM –建立同义词
    CREATE VIEW –建立视图
  • RESOURCE 角色： –是授予开发人员的
    CREATE CLUSTER –建立聚簇
    CREATE PROCEDURE –建立过程
    CREATE SEQUENCE –建立序列
    CREATE TABLE –建表
    CREATE TRIGGER –建立触发器
    CREATE TYPE –建立类型
  • DBA 角色：拥有全部特权，是系统最高权限，只有 DBA 才可以创建数据库结构，并且系统
    权限也需要 DBA 授出，且 DBA 用户可以操作全体用户的任意基表，包括删除,但是DBA权限太高,非常危险,所以几乎不会使用它

GRANT CONNECT,RESOURCE TO cuixiaoming001; 

创建表

数据类型

CREATE TABLE PERSON(
PID NUMBER(8),
NAME VARCHAR2(10),
GENDER NUMBER(1) DEFAULT 1,
BIRTHDAY DATE
);

下面是增删改,和MYSQL一样

-- 插入测试数据
INSERT INTO PERSON (PID,NAME,GENDER,BIRTHDAY) 
VALUES 
(1,'张三',1,date'1991-06-20');
-- 切记,Oracle操作信息之后一定要手动COMMIT
COMMIT;

-- 修改表结构
-- 为表添加字段
ALTER TABLE PERSON ADD(ADDRESS VARCHAR2(10));

-- 修改表中字段的长度
ALTER TABLE PERSON MODIFY(ADDRESS VARCHAR2(20));

-- 删除表
DROP TABLE PERSON;

下面是约束,和Mysql也是一样一样滴,先是手动添加约束

-- 为表添加主键约束
ALTER TABLE PERSON ADD CONSTRAINT PK_PERSION_PID PRIMARY KEY(PID);

-- 为表添加非空约束
ALTER TABLE PERSON MODIFY(NAME NOT NULL);

-- 为表添加唯一约束
ALTER TABLE PERSON MODIFY(NAME UNIQUE);

-- 为表添加检查约束
ALTER TABLE PERSON ADD CONSTRAINT CK_PERSOPN_GENDER CHECK(GENDER IN (1,2));

-- 外键约束
-- 创建订单表
CREATE TABLE ORDERS(
ORDER_ID NUMBER(10) NOT NULL PRIMARY KEY,
TOTAL_PRICE NUMBER(8,2),
ORDER_TIME DATE
)

如果想在创建表的时候就把约束搞了,可以这样来

-- 创建订单详细表
CREATE TABLE ORDER_DETAIL(
DETAIL_ID NUMBER(10) NOT NULL PRIMARY KEY,
ORDER_ID NUMBER(10),
ITEM_NAME VARCHAR2(20),
QUANTITY NUMBER(10)
)
-- 添加外键约束
ALTER TABLE ORDER_DETAIL ADD CONSTRAINT FK_ORDERDETAIL_ORDERID FOREIGN KEY(ORDER_ID) REFERENCES ORDERS(ORDER_ID);

DML数据操纵语言

这个和Mysql也是一样的,不多说了,直接上代码

-- 添加数据
INSERT INTO PERSON 
(PID,NAME,GENDER,BIRTHDAY,ADDRESS) 
VALUES (2,'李四',2,date'1998-02-15','北京市海淀区');
-- 记得要提交
COMMIT;

-- 简写方案(不推荐,因为必须把所有的信息按照指定顺序,都写上)
INSERT INTO PERSON VALUES 
(3,'王五',1,date'1998-02-15','北京市昌平区');

-- 更新数据
-- 更新 ID 为 2 的姓名为王小花
UPDATE PERSON SET NAME = '王小花' WHERE PID = 2;

-- 更新 ID 为 3 的姓名为孙建国
UPDATE PERSON SET NAME = '孙建国' WHERE PID = 3;

-- 更新 ID 为 1 的姓名为赵文明 地址为北京市朝阳区
UPDATE PERSON SET NAME = '赵文明',ADDRESS='北京市朝阳区' WHERE PID = 1; 

-- 删除数据

-- 删除全表数据 两种方案

-- 1.直接删除表的物理结构,再重建 简单粗暴 误删不可恢复 
-- 危险但是速度快
-- 复制一张表 然后删除
CREATE TABLE PERSON1 AS SELECT * FROM PERSON;
-- 删除复制表
TRUNCATE TABLE PERSON1;

-- 2.删除数据 , 会产生事务 速度慢 但是安全 可恢复
DELETE FROM PERSON1;

-- 删除指定数据
-- 最常用 按ID删除
DELETE FROM PERSON WHERE PID = 1;

事务点:Oracle中可以在很多SQL语句中间插入事务点,当某个部分报错时,可以回滚到上一个事务点,事务点之前的提交,之后的回滚

-- 添加测试数据
INSERT INTO PERSON 
(PID,NAME,GENDER,BIRTHDAY,ADDRESS) 
VALUES (4,'测试数据',1,date'1998-02-15','北京市海淀区');

INSERT INTO PERSON 
(PID,NAME,GENDER,BIRTHDAY,ADDRESS) 
VALUES (5,'测试数据1',1,date'1998-02-15','北京市海淀区');
-- 保存事务点
SAVEPOINT a;
INSERT INTO PERSON 
(PID,NAME,GENDER,BIRTHDAY,ADDRESS) 
VALUES (6,'测试数据2',1,date'1998-02-15','北京市海淀区');
ROLLBACK TO SAVEPOINT a;
-- 回滚 或者提交
ROLLBACK;

结果:4,5添加成功,6没有添加

序列

Oracle无法直接设置主键自增,需要借助序列的力量
序列的功能就是产生一连串的数字,主要作用就是帮你填充主键编号

CREATE SEQUENCE seq_person --序列名
START WITH 5        --从几开始
INCREMENT BY 1      --每次增加多少
MINVALUE 5          --最小取值
MAXVALUE 99999999   --最大取值
NOCYCLE             --不循环       
NOCACHE             --不缓存 

-- 使用序列 直接用  序列名.nextval 填充主键
INSERT INTO PERSON  VALUES (SEQ_PERSON.NEXTVAL,'赵文明3',1,date'1992-12-05','北京市昌平区');

索引

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而提高数
据访问性能
1 单列索引
- 单列索引是基于单个列所建立的索引，比如:

CREATE index 索引名 on 表名(列名)

2 复合索引
- 复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同,比如：

Create index emp_idx1 on emp(ename,job);
Create index emp_idx1 on emp(job,ename);

范例：给 person 表的 name 建立索引
create index pname_index on person(name);
范例：给 person 表创建一个 name 和 gender 的索引
create index pname_gender_index on person(name, gender);

一定要注意,一开始没有数据不要加索引,没意义;数据量大了,查询速度慢了之后再加!!!

同义词

可以把从其他表引入的表起个别名,缩短了对象名字的长度,据说还有一些安全的考虑,这个不是很清楚,另外要想使用同义词,需要一些权限

--授权
grant create synonym to cuixiaoming001
--起名
CREATE PUBLIC SYNONYM EMP1 FOR SCOTT.EMP;