table

1、表能够有多达1000 个列，并且必须符合标准的数据库对象命名约定。

2、Oracle 设计了大约 6 种数据的存储格式，详见下表：

 

 

 3、表和列的命名规则

  • 必须以字母开头
  • 长度不能超过 30 个字符（最多 30 个字符）
  • 不能使用 oracle 的保留字
  • 只能使用如下字符 A-Z，a-z，0-9，$，#，_等
  • 在同一用户下的对象不能同名，即使是不同的对象类型

  表和列名是大小写不敏感的。eMPloyees 或 eMpLOYEES 是算做同一张表。

SELECT * FROM v$reserved_words;   ---所有的保留字

4、创建、管理表

CREATE TABLE scott.emp_bk parallel 8 nologging AS SELECT * FROM scott.emp;    ---CAST方式建表，并行优化
   ---默认值（default）不能是另一个列的名字或伪列，例如 NEXTVAL 或CURRVAL

alter table lhr2 add(age number,address varchar2(255)); ---新的字段只能被加到整个表的最后。
alter table xs modify(djsm default '奖金 800') ---修改字段的类型、大小和默认值（不能有数据）
ALTER TABLE student DROP COLUMN sal; ---删除列

Oracle 中是没有删除一列的数据的方法的，我们只能通过别的办法去实现，是把这一列中的数据都设置成null 值，看起来就像删除一列的数据了。
update lhr_str l set l.sal=null;
update lhr_str l set l.sal='';



alter table departments set unused (test_column);
alter table departments drop unused columns;   




alter table t rename to s; ---只能修改当前用户下自己的表，即使是数据库管理员也不可以。
rename student to stu;           ---执行 RENAME 语句改变表, 视图, 序列, 或同义词的名称
drop table dept80 purge;            ---删除表
TRUNCATE TABLE table;              ---截断表




SELECT 'alter table ' || T.TABLE_NAME || ' move tablespace SDH_DATA;' FROM user_tables t WHERE t.tablespace_name = 'D_SVS_01' AND t.table_name LIKE '%LHR%'; ---批量移动表空间




COMMENT ON TABLE SCOTT.EMP2 IS 'SCOTT 用户的备份表';
COMMENT ON COLUMN SCOTT.EMP2.EMPNO IS '备份表的 empno 列';
COMMENT ON COLUMN SCOTT.EMP2.JOB IS '备份表的 job';
SELECT * FROM DBA_TAB_COMMENTS D WHERE D.table_name='EMP2';         ---表注释
SELECT * FROM DBA_COL_COMMENTS D WHERE D.table_name='EMP2';         ---列注释


select * from user_catalog;           ---查看本用户所拥有的表、视图、同义词和序列：









在用子查询创建表时，LONG 列不会被复制。LONG 列不能包括在 GROUP BY 或 ORDER BY 子句中。在每个表中只能有一个 LONG 列。


在 LONG 列上不能定义约束。通常用情况下使用 CLOB 列而不是 LONG 列。Oracle8 引入了大对象 (LOB)数据类型，它可以存储大的和非结构化的数据，例如文本、图象、视频和空间数据，最大 4G。LONG 列可以容易地移动到 LOB 列.




DATE，时间数据类型。日期和时间值，从公元前 4712.1.1 到公元 9999.12.31。
TIMESTAMP，允许带小数秒的时间被作为日期存储。有一些变异的数据类型。它是 DATE 数据类型的一种扩展

create table TS_TEST
(
dt DATE,
ts TIMESTAMP(6),
ts1 TIMESTAMP(7),
ts2 TIMESTAMP(6) WITH TIME ZONE,
ts3 TIMESTAMP(6) WITH LOCAL TIME ZONE
);


INTERVALYEAR [(year_precision)] TO MONTH
year_precision 是在 YEAR 日期时间字段中数字的数目，年精度的默认值是 2。
前面的部分要大于后面的部分，例如：INTERVAL '0-1' MONTH TO YEAR 是无效的，必须写成：INTERVAL'0-1' YEAR TO MONTH。

INTERVAL DAY TO SECOND 根据天、小时、分和秒存储一段时间。用 INTERVAL DAY TO SECOND 来表示两个日期时间值精确的差




 5、操作数据




insert into Departments(Department_Id, Department_Name, Manager_Id, date) Values (100, 'Finance', Null, sysdate);     ---插入空值，特殊sysdate等
insert into departments select 999 'TEST_PART', (select 400 From Dual), 4422 From Dual;         ---dual插入子查询

insert into
(select Employee_Id, Last_Name, Email, Hire_Date,Job_Id, Salary, Department_Id From Employees Where Department_Id = 50)
Values
(99999, 'Taylor','DTAYLOR', To_Date('07-6 月-1999', 'DD-MON-RR'), 'ST_CLERK', 5000, 50);               ---子查询作为列






update Employees set (Job_Id,Salary) = (select Job_Id ,Salary From Employees Where Employee_Id = 205) Where Employee_Id = 114;     ---子查询
update lhr_str l set l.sal='';       ---列别名

UPDATE lhr2 l2
SET l2.name =
(SELECT l1.name FROM lhr1 l1 WHERE l1.id = l2.id)
WHERE exists (SELECT 1 FROM lhr1 l1 WHERE l1.id=l2.id);        -- 这句中的 1 可以为 *，多表关联，并把一个表的字段值更新到另一个表中的字段去


这是由于 set 那里的子查询查出了多行数据值，oracle 规定一对一更新数据，所以提示出错。要解决这样必须保证查出来的值一一对应
SELECT id,COUNT(1) 记录条数 FROM u1 GROUP BY id HAVING COUNT(1) > 1;    ---查找重复值见随笔篇








MERGE 合并语句语法（相当于插入与更新语句的综合）
SQL的扩展包括了MERGE语句，使用该语句，你可以有条件地更新或插入行到表中，这样能避免多重UPDATE语句。是执行对目的表的更新操作还是执行对目的表的插入操作，取决于基于 ON 子句中的条件



MERGE 有以下特点：避免分散更新，增进性能和易用性，在数据仓库应用中有用。


---MERGE 的语法：
MERGE INTO table_name table_alias
USING (table|view|sub_query) alias
ON ( join condition )
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1, col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list) VALUES (column_values);


    

  • INTO 子句 指定你正在更新或插入的目的表。
    • 

  • USING 子句 指定数据源要被更新或插入的数据的源；可以是一个表、视图或者字查询。
    • 

  • ON 子句 是一个条件，在此条件上 MERGE 操作即可以更新也可以插入。
    • 

  • WHEN MATCHED | 通知服务器怎样响应连接条件的结果。
    • 

  • WHEN NOT MATCHED 
    • 





举例：


MERGE INTO test2
USING test1
ON(test1.eid = test2.eid )
WHEN matched THEN
UPDATE SET name = test1.name, birth = test1.birth, salary = test1.salary
WHEN NOT matched THEN
INSERT (eid, name, birth, salary) VALUES(test1.eid, test1.name, test1.birth,
test1.salary);






6、海量数据DML 语句优化的方法




 


 


 7、DELETE、DROP 和 TRUNCATE 的区别是什么？






（1）TRUNCATE 是一个 DDL 语句，DELETE 是 DML 语句。
（2）TRUNCATE将被隐式提交，不能对TRUNCATE使用ROLLBACK命令，而对DML语句可以执行ROLLBACK命令来撤销未提交的事务。
（3）TRUNCATE 由于是在底层修改了数据字典，所以在各种表上无论是大表的还是小表操作都非常快。DELETE 操作需要写日志，而 TRUNCATE 不需要写日志，所以对于大表进行 DELETE 全表操作非常慢。
（4）TRUNCATE 将重置表的高水位线，但 DELETE 不会重置表的高水位线，在宏观上表现为 TRUNCATE操作后，表的大小变为初始化的大小，而 DELETE 后表的大小并不会因此而改变，所以在对整个表进行全表扫描时，
经过 TRUNCATE 操作后的表比 DELETE 操作后的表要快得多，另外需要注意的是，无论 TRUNCATE 还是DELETE 操作，表上的索引都会自动维护。
（5）TRUNCATE 操作后的表不能通过闪回特性来找回，但 DELETE 后的表可以通过闪回特性来找回数据。
（6）TRUNCATE 不能加条件删除数据；DELETE 可以加条件删除。
（7）不能 TRUNCATE 一个带有 ENABLE 外键的表，会报错 ORA-02266。可以 DELETE 一个带有 ENABLE外键的表




TRUNCATE TABLE恢复系列二：PL/SQL恢复


 https://www.modb.pro/db/14957


Delete删除千万以上普通堆表数据的方法


https://www.cnblogs.com/jyzhao/p/3879590.html







		

		
posted @ 
2021-08-31 16:54 
harrison辉 
阅读(724) 
评论(0) 
 
收藏 
举报