ORACLE学习（十）

--表的重名名

RENAME 旧名 TO 新名

RENAME STU TO TT;

--清空表数据

TRUNCATE TABLE 表

TRUNCATE TABLE TT;

SELECT * FROM TT;

--删除表

DROP TABLE 表名 [PURGE]

加上purge表示物理删除 直接就没了

不加表示逻辑删除 把表放进了回收站

DROP TABLE TT;

DROP TABLE EMP_NULL PURGE;

--查看回收站

SELECT * FROM USER_RECYCLEBIN;

--从回收站闪回表

FLASHBACK TABLE 表名 TO BEFORE DROP [RENAME TO 新表名]

FLASHBACK TABLE TT TO BEFORE DROP RENAME TO STU_NEW;

--视图：

定义：是一张虚拟的表 其内容有查询语句定义

创建：CREATE [OR REPLACE] VIEW 视图名 AS SELECT 语句[WITH READ ONLY]

加上or replace如果视图名存在 会进行覆盖

不加or replace如果视图名存在 会报错（当前名称由现有对象占用）

加上with read only表示只读视图 无法修改数据

不加with read only表示普通视图 可以修改数据

--创建视图v_20 内容是emp中20部门的数据

CREATE OR REPLACE VIEW V_20 AS SELECT * FROM EMP WHERE DEPTNO=20;

SELECT * FROM V_20;

--创建视图 内容是emp和dept关联之后的数据

CREATE OR REPLACE VIEW V_DE AS SELECT E.*,DNAME,LOC FROM EMP E

,DEPT D WHERE E.DEPTNO=D.DEPTNO;

SELECT * FROM V_DE;

注：视图的数据来源表叫基表

视图不存储数据 只存储定义内容的语句 内容由基表提供

当基表的数据发生改变之后 视图的内容跟着改变

修改视图的数据就是在修改基表的数据

视图的优缺点：优点 不占空间 安全

缺点 性能差 修改受限制

--删除视图

DROP VIEW 视图名

DROP VIEW V_20;

--序列 SEQUENCE

数据库中用来存放等差数据列的表

创建序列的语法：

CREATE SEQUENCE 序列名

[START WITH 数] --从几开始 默认是1

[MAXVALUE 数] --最大到几 默认。。。。

[MINVALUE 数] --最小是几 默认是1

[INCREMENT BY 数] --间隔/步长 默认是1

[CACHE 数] --缓冲值 默认是20

[CYCLE] --是否循环

CREATE SEQUENCE S1;

--序列的使用

序列.NEXTVAL --获取新值（初始化）

序列.CURRVAL --回去当前值

SELECT S1.NEXTVAL FROM DUAL;

关闭延迟

alter systerm set deferred列的删除

DROP SEQUENCE 序列名

DROP SEQUENCE S1;

DML语句 数据库操作语言

插入 insert

更新 update

删除 delete

合并 merge

--插入数据

批量插入

手动插入

批量：INSERT INTO 表名[列。。。] select 语句;

--创建一张空表 emp_n

CREATE TABLE EMP_N AS SELECT * FROM EMP WHERE 1=2;

SELECT * FROM EMP_N;

--把emp中10部门的数据插入到emp_n中

INSERT INTO EMP_N SELECT * FROM EMP WHERE DEPTNO=10;

--把emp中20部门的姓名 职位 工资 部门编号插入

INSERT INTO EMP_N(ENAME,JOB,SAL,DEPTNO) SELECT ENAME,JOB,SAL,DEPTNO

FROM EMP WHERE DEPTNO=20;

--把dept中的dname loc deptno 插入到emp_n的ename job deptno中

INSERT INTO EMP_N(ENAME,JOB,DEPTNO) SELECT DNAME,LOC,DEPTNO

FROM DEPT;

手动插入

INSERT INTO 表名[列。。] VALUES(值。。。)

INSERT INTO EMP_N(EMPNO,ENAME,SAL) VALUES(1234,'OLDSIX',3000);

INSERT INTO EMP_N VALUES(2234,'zhangmou','OLDDRIVE',NULL,

SYSDATE,2000,0,40);

SELECT * FROM EMP_N;

--把序列当做主键值插入到表中

CREATE SEQUENCE S5;

INSERT INTO EMP_N(EMPNO,ENAME)

VALUES(S5.NEXTVAL,'神舟'||S5.CURRVAL||'号');

--更新数据

UPDATE 表 SET 列1=值1,列2=值2,。。。[WHERE]

不加where条件会对全表的数据进行更新

--创建emp_1数据同emp

CREATE TABLE EMP_1 AS SELECT * FROM EMP;

SELECT * FROM EMP_1;

--把emp_1中10部门的员工职位改为小写 工资+500

UPDATE EMP_1 SET JOB=LOWER(JOB),SAL=SAL+500 WHERE DEPTNO=10;

--把emp_1中所有人的姓名改为首字母大写

UPDATE EMP_1 SET ENAME=INITCAP(ENAME);

--新建emp_1 更新10部门呢的工资为原来的一半 名字转小写

--20部门 工资减半再减半 名字为首字母大写

--30部门工资为原来的5分之一 名字左边+@

--所有人的奖金+5000

update emp_1 set COMM=NVL(COMM,0)+5000;

--删除数据

DELETE FROM 表 [WHERE 条件]

不加where会把全表的数据进行删除

SELECT * FROM EMP_1;

--删除1981年入职的员工

DELETE FROM EMP_1 WHERE TO_CHAR(HIREDATE,'YYYY')=1981;

DELETE FROM EMP_1;

TRUNCATE 和 DELETE 的区别？

truncate是ddl语句 不能回滚 delete是dml语句 可以撤销

truncate不能加条件 delete可以加条件

truncate速度快 delete速度慢

truncate可以降低高水位线 delete不能降低高水位线

--rowid 伪列 行地址（每一条数据会有一个也全局唯一的行地址）

去重重复的数据

SELECT E.*,ROWID

FROM EMP_1 E

--制造一些重复数据

INSERT INTO EMP_1 SELECT * FROM EMP WHERE DEPTNO=20;

--如何查看重复的数据

SELECT *

FROM EMP_1

WHERE EMPNO IN(SELECT EMPNO FROM EMP_1 GROUP BY EMPNO

HAVING COUNT(1)>1);

--如何去重表中重复的数据

一、

1、创建一个备份表 数据同emp_1中去重之后的数据

CREATE TABLE EMP_1_BACK AS SELECT DISTINCT * FROM EMP_1;

2、清空原来的表

TRUNCATE TABLE EMP_1;

3、把备份表中的数据重新插回到原表中

INSERT INTO EMP_1 SELECT * FROM EMP_1_BACK;

4、删除备份表

DROP TABLE EMP_1_BACK;

二、

DELETE FROM EMP_1 WHERE ROWID NOT IN(SELECT MAX(ROWID) FROM EMP_1

GROUP BY EMPNO);

注：在进入表中的数据rowid最小

--合并数据

MERGE INTO 表A --进行插入和更新的表

USING 表B --参考表

ON 条件

WHEN MATCHED TEHN --匹配成功

UPDATE SET A.列=B.列|值

WHEN NOT MATCHED THEN --没有匹配上

INSERT(A.列。。。) VALUES(B.列|值)

创建两张表 empa empb

a中插入emp中的empno ename deptno

b中插入emp中empno 小写的ename deptno(10变成40)

CREATE TABLE EMPA AS SELECT EMPNO,ENAME,DEPTNO FROM EMP;

CREATE TABLE EMPB AS SELECT EMPNO,LOWER(ENAME) ENAME,DECODE(DEPTNO,10,40,

DEPTNO) DEPTNO FROM EMP;

SELECT * FROM EMPA;

SELECT * FROM EMPB;

参考b对a进行更新

MERGE INTO EMPA A

USING EMPB B

ON(A.EMPNO=B.EMPNO AND A.DEPTNO=B.DEPTNO)

WHEN MATCHED THEN

UPDATE SET A.ENAME=B.ENAME

WHEN NOT MATCHED THEN

INSERT VALUES(B.EMPNO,B.ENAME,B.DEPTNO);

[[补充DDL

--外键约束

外键约束需要两张表 创建外键的表叫子表 参考表是父表

创建外键约束的列叫外键列 参考表的参考列必须是父表的主键列

作用：保证存入外键列中的值一定是父表的主键列中存在的

语法：CONSTRAINT 约束名 FOREIGN KEY(外键列) REFERENCES 父表(主键列)

[ON DELETE CASCADE |ON DELETE SET NULL]

建立父表

CREATE TABLE DEPT1 AS SELECT * FROM DEPT;

ALTER TABLE DEPT1 ADD PRIMARY KEY(DEPTNO);

drop table emp_1;

建立子表

CREATE TABLE EMP_1 AS SELECT EMPNO,ENAME,DEPTNO FROM EMP WHERE 1=2;

SELECT * FROM EMP_1;

在子表中设立外键

ALTER TABLE EMP_1 ADD CONSTRAINT FK_1 FOREIGN KEY(DEPTNO) REFERENCES

DEPT1(DEPTNO) on delete set null;

INSERT INTO EMP_1 SELECT EMPNO,ENAME,DEPTNO FROM EMP;

INSERT INTO EMP_1 VALUES(1234,'LLLLL',40);

INSERT INTO EMP_1 VALUES(2234,'L22LL',50);

不指定级联删除（先删子表在删父表）

DELETE FROM EMP_1 WHERE DEPTNO=10; --删除子表数据没限制

DELETE FROM DEPT1 WHERE DEPTNO=10; --直接删除因为子表有数据不能执行

指定级联删除

DELETE FROM DEPT1 WHERE DEPTNO=10;--可以直接删除父表数据 子表随着删除

指定级联删除为空

DELETE FROM DEPT1 WHERE DEPTNO=10;--可以直接删除父表数据 子表相关项还再

--只是外键列的值变为空

不指定级联删除 no action --啥也不写 默认

级联删除 on delete cascade

指定级联删除为空 ON DELETE SET NULL

注：外键列中的值可以为空 也可以不唯一

删除子表的外键列中值不受影响

删除父表主键的数据的时候

如果没有指定级联删除 要先删除子表的相关项 在去删除父表对应的内容

如果指定可级联删除 子表的数据和父表会一同被删除

如果指定级联删除为空 父表数据删除之后 子表数据还存在 只是外键列的

值变为了空

插入/更新到子表外键列中的值 要在父表的主键列中存在

插入/更新到父表主键列中的值 没有限制（只要满足主键约束即可）