Oracle——数据处理（增删改查），创建表与管理表，视图，索引，同义词

SQL的类型

1、DML(Data Manipulation Language 数据操作语言): select insert update delete
2、DDL(Data Definition Language 数据定义语言): create table,alter table,truncate table,drop table,create/drop view,sequnece,index,synonym(同义词)
3、DCL(Data Control Language 数据控制语言): grant(授权) revoke(撤销权限)

DML

插入数据

insert into emp(empno,ename,sal,deptno) values(1001,'Tom',3000,10);

-- 或 采用地址符 &

insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno);
输入 empno 的值:  1002
输入 ename 的值:  'Mary'
输入 sal 的值:  2000
输入 deptno 的值:  20
原值    1: insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno)
新值    1: insert into emp(empno,ename,sal,deptno) values(1002,'Mary',2000,20)

-- 批量添加数据

--一次性将emp中，所有10号部门的员工插入到emp10中
insert into emp10 select * from emp where deptno=10;

海量插入数据

1、数据泵（PLSQL程序）

　　dbms_datapump(程序包)

2、SQL*Loader

3、外部表

delete和truncate的区别

1、delete逐条删除；truncate先摧毁表再重建

2、delete是DML truncate是DDL

(可以回滚) （不可以回滚）

执行DDL类型的命令会提交事务

3、delete不会释放空间 truncate会

4、delete会产生碎片 truncate不会

碎片：在Oracle中有一个指针（高水位线）记录着下次insert的位置，如果我们在数据的中间执行delete命令就会产生碎边（数据不连续）

5、delete可以闪回(flashback) truncate不可以

闪回：还原数据

6、delete操作比truncate操作快（原因：可以还原数据）

Oracle中的事务

1、起始标志: 事务中的第一条DML语句

2、结束标志：提交：显式 commit 隐式：正常退出 DDL DCL

　　　　　　回滚：显式 rollback 隐式：非正常退出掉电宕机

创建和管理表（DDL）

1、创建表

CREATE TABLE test1 (
    tid NUMBER,
    tname VARCHAR2 ( 20 )
);

-- 或直接copy别的表结构

CREATE TABLE emp20 AS SELECT
* 
FROM
	EMP 
WHERE
	DEPTNO = 20;-- where条件有效，复制数据，无效，只复制表结构

2、查看表结构

desc test1　　-- SQLplus

3、增加新列

ALTER TABLE test1 ADD photo blob; 　　-- blob:二进制大对象，是一个可以存储二进制文件的容器

4、修改列

ALTER TABLE test1 MODIFY tname VARCHAR2 ( 40 );　　-- 修改tname列的varchar长度为40

5、删除列

ALTER TABLE test1 DROP COLUMN photo;　　-- 删除photo字段

6、重命名列

ALTER TABLE test1 RENAME COLUMN tname TO username;　　-- 修改列名tname为username

7、重命名表

RENAME test1 TO test2;

8、删除表

DROP TABLE test2;　　-- 表并没有完全被删除，只是别放进了回收站中

回收站

注意：管理员没有回收站

可以使用select查询出（回收站中）表中的内容

查看回收站

show recyclebin;

清空回收站

purge recyclebin;

闪回删除

flashback table test2 to before drop;

9、约束（数据完整性由各种约束保证）

1）检查约束

create table test3(
	tid number,
	tname varchar2(20),
	gender varchar2(2) check (gender in ('男','女')),	-- 检查约束
	sal  number check (sal > 0)
);

SQL> insert into test3 values(1,'Tom','男',2000);

已创建 1 行。

SQL> insert into test3 values(2,'Mike','*啊*',2000);
insert into test3 values(2,'Mike','啊',2000)
*
第 1 行出现错误: 
ORA-02290: 违反检查约束条件 (SCOTT.SYS_C005393)

2）各种约束

CREATE TABLE student (
	sid NUMBER constraint student_pk primary key,	-- 主键约束，constraint字段表示使用约束
	sname VARCHAR2 ( 20 ) constraint student_name_notnull NOT NULL,		-- 非空约束
	gender VARCHAR2 ( 2 ) constraint student_gender CHECK (gender IN ( '男', '女' )),	-- 检查约束
	email VARCHAR2 ( 40 ) constraint student_email_unique UNIQUE 	-- 唯一约束
                        constraint student_email_notnull NOT NULL,	-- 一个字段设置两个约束
	deptno NUMBER constraint student_fk references dept ( deptno ) ON DELETE SET NULL 	-- 外键约束，设置级联置空
);

视图

视图就是封装了一条复杂查询的语句。视图是一个虚表，它最大的优点就是简化复杂查询，所以不建议对视图进行其他的操作。

普通用户默认是不能创建视图的，需要管理员进行授权

数据库在解释查询语句的时候会查找视图的定义，视图中不存放数据（除了物化视图，物化视图是快照，真正的存放数据，需要刷新）。视图每次查询的时候都要进行数据的查找。

管理员登录

> sqlplus sys/123 as sysdba　　-- 用户名和密码可以随便写

SQL> show user

USER 为 “SYS”

为scott用户授权创建视图

grant create view to scott；

创建视图

CREATE 【OR REPLACE】 VIEW empinfoview AS SELECT　　-- CREATE OR REPLACE表示存在替换，不存在创建
	e.empno,
	e.ename,
	e.sal,
	e.sal * 12 annsal,
	d.dname 
FROM
	emp e,
	dept d 
WHERE
	e.deptno = d.deptno;