oracle基础语句

名称定义

数据表空间：TBS_USER1_DATA
索引表空间：TBS_USER1_INDEX
用户：USER_1
表：table_T

一、用户和权限

1. 创建表空间

-- 创建数据表空间
create tablespace TBS_USER1_DATA datafile '/u01/app/oracle/oradata/orcl/TBS_USER1_DATA2021.9.9.dbf'  size 10m autoextend on next 500m maxsize unlimited extent management local uniform size 8m;
-- 创建索引表空间
create tablespace TBS_USER1_INDEX datafile '/u01/app/oracle/oradata/orcl/TBS_USER1_INDEX2021.9.9.dbf'  size 10m autoextend on next 500m maxsize unlimited extent management local uniform size 8m;

2. 创建用户

需要在dba角色的用户下创建

-- 创建用户USER_1，密码为USER_1
create user USER_1 identified by "USER_1";
-- 用户关联表空间
create user USER_1 identified by "USER_1" default tablespace TBS_USER1_DATA QUOTA UNLIMITED ON TBS_USER1_DATA ;

3. 授予用户对于索引表空间的使用权限

需要在dba角色的用户下创建

alter user USER_1 quota unlimited on TBS_USER1_INDEX ;

二、表结构相关

1. 新增

（1）创建表

-- 创建表table_T
CREATE TABLE table_T (
id VARCHAR2 ( 32 BYTE ) VISIBLE DEFAULT ' ' NOT NULL,
-- 字段id VARCHAR2类型，默认空字符串，不能为空（去掉NOT NULL 可为空）
phone NUMBER ( 11 ) VISIBLE DEFAULT 0 NOT NULL,
-- 字段phone 数字类型，默认0，不能为空
amt NUMBER ( 18, 2 ) VISIBLE DEFAULT 0 NOT NULL 
-- 字段amt 数字类型保存2位小数（金额可用）
);

备注：
VARCHAR2() ----0-4000,可变长度
char() ----0-2000,固定长度,用空格在数据的右边补到固定长度
number(6,2) —6位整数、2位小数
number(2) --2位整数

（2）添加表注释

COMMENT ON TABLE table_T IS '表注释';

（3）添加字段备注

添加、修改字段备注都是此sql

COMMENT ON COLUMN table_T.id IS '编号';

（4）添加主键约束

-- 添加主键约束
alter table table_T add CONSTRAINT PK_table_T primary key (id);
-- 添加主键约束（TABLESPACE TBS_USER1_INDEX 表放在TBS_USER1_INDEX表空间）
ALTER TABLE table_T ADD CONSTRAINT PK_table_T PRIMARY KEY (id) USING INDEX TABLESPACE TBS_USER1_INDEX;

（5）创建索引

-- 创建索引
CREATE INDEX N1_table_T ON table_T (phone ASC,amt ASC) TABLESPACE TBS_USER1_INDEX;
-- 创建唯一索引
CREATE UNIQUE INDEX UI1_table_T ON table_T (phone) TABLESPACE TBS_USER1_INDEX;

不加表空间的话，去掉TABLESPACE TBS_USER1_INDEX

（6）添加表字段

ALTER TABLE table_T ADD name VARCHAR2(32) DEFAULT ' ';-- 添加表字段name
COMMENT ON COLUMN table_T.name IS '名字';-- 添加字段备注

（7）备份表

create table table_T1 as select * from table_T;
create table table_T1 as select * from table_T where 1=2;-- 复制结构，不要数据

2. 修改

（1）修改表字段名

alter table table_T1 rename column 旧字段 to 新字段;

（2）修改字段备注

添加、修改字段备注都是此sql

COMMENT ON COLUMN table_T.id IS '编号';

（3）修改字段长度

• 修改一个列

alter table 表名 modify 字段 varchar2(60);

• 修改多个列

alter table 表名 modify (字段1 varchar2(10),字段2 varchar2(10));

（4）修改字段类型

• 如果表中没有数据

alter table 表名 modify(字段 number(4));

• 如果原表中存在数据

--修改原字段名  
ALTER TABLE 表名 RENAME COLUMN 字段1 TO 字段名2;

--添加一个和原字段同名的字段  
ALTER TABLE 表名 ADD 字段1 VARCHAR2(20); 

--将原来的数据更新到新字段中,这是要注意，一定要显示进行数据类型转换（不同于MSSQL）  
UPDATE 表名 SET 字段1 = CAST(字段名2 AS VARCHAR2(20)); 

--删除原来的备份字段  
ALTER TABLE 表名 DROP COLUMN 字段名2;

3. 删除

（1）删除表

drop table table_T;

（2）删除表字段

alter table table_T drop column name;

（3）删除主键

ALTER TABLE table_T DROP CONSTRAINT 主键名;

（4）删除索引

drop index 索引名;

3. 查询

（1）查询包含某个字段的表

-- 查询包含某个字段的表（字段要大写）
select table_name from user_tab_cols where column_name = 'ID';

（2）查询当前用户下的所有表

select table_name from user_tables;

（3）查询当前用户下的所有索引

select table_name,index_name from user_indexes;

三、授权

授予test用户下的table_1表权限给logadm

GRANT ALL PRIVILEGES ON test.table_1 TO logadm;

将test用户下的table_1表的查询权限赋予logadm用户

grant select on test.table_1 to logadm;

授予test用户下的所有表权限给logadm（需要在test用户下）

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA test TO logadm;

授予test用户下的所有表查询权限给logadm（需要在test用户下）

GRANT select ON ALL TABLES IN SCHEMA test TO logadm;

四、表空间相关

1. 查询相关

（1）查询当前用户下所有表的所属空间

-- 查询固定表可以加条件 where table_name = ''
select table_name,tablespace_name from user_tables;

(2) 查询当前用户下的索引表空间

select table_name,index_name,tablespace_name from user_indexes;

2. 修改相关

（1）修改表的空间

alter table  TABLE_NAME move tablespace TABLE_SPACE_NAME;

（2）修改表的索引的空间

alter index INDEX_NAME rebuild tablespace TABLE_SPACE_NAME;

五、表数据相关

1. 插入数据

INSERT INTO table_T(id,phone,amt) VALUES ('001','13200000000',100);

2. 删除数据

DELETE FROM table_T WHERE id = '001';

3. 修改数据

UPDATE table_T SET amt = 200 WHERE id = '001';

批量更新

update table_T1 t1 set (a,b,c)=( select a,b,c from table_T2 t2 where t1.a=t2.a)
where exists(select 1 from table_T2 t2 where t1.a=t2.a); 

4. 查询数据

SELECT * FROM table_T WHERE id = '001';

5. 清空表数据

delete from table_T;
truncate table table_T;

区别：

1. 条件删除
   DELETE是可以带WHERE的，所以支持条件删除；而TRUNCATE只能删除整个表；
2. 事务回滚
   由于DELETE是数据操作语言（DML），操作时原数据会被放到 rollback segment中，可以被回滚；而TRUNCATE是数据定义语言（DDL)，操作时不会进行存储，不能进行回滚。
3. 清理速度
   在数据量比较小的情况下，DELETE和TRUNCATE的清理速度差别不是很大。但是数据量很大的时候，TRUNCATE不需要支持回滚，所以使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项，所以会慢，但是相对来说也较安全。
4. 高水位重置
   随着不断地进行表记录的DML操作，会不断提高表的高水位线（HWM），DELETE操作之后虽然表的数据删除了，但是并没有降低表的高水位，随着DML操作数据库容量也只会上升，不会下降。所以如果使用DELETE，就算将表中的数据减少了很多，在查询时还是很和DELETE操作前速度一样。
   而TRUNCATE操作会重置高水位线，数据库容量也会被重置，之后再进行DML操作速度也会有提升。

五、分区表

查询分区表

select partition_name from user_tab_partitions where table_name='table_T';

select * from ALL_TAB_PARTITIONS where table_name='table_T';