oracle表操作语句

renle0216

于 2023-02-01 16:31:38 发布

阅读量156

点赞数

文章标签： oracle 数据库 dba

本文链接：https://blog.csdn.net/u012132164/article/details/128836401

版权

本文介绍了SQL中对数据库表的操作，包括创建、删除和重命名表，以及插入数据。还讨论了如何添加、修改和删除字段，以及创建和删除主键、索引和视图。此外，详细阐述了分区表的概念，如范围、列表和哈希分区，并展示了如何管理分区。最后，文章提到了序列的创建和使用，以及各种约束（如非空、主键、外键、唯一和检查约束）的定义和管理。

摘要由CSDN通过智能技术生成

3. 表操作（sql）

1. 基础操作

# 创建表
create table student("id" char(6),"name" varchar2(10),"sex" char(1),"age" integer,"phone" varchar2(12)) tablespace test_db;

# 创建一个表，指定passwd为不可见字段
SQL>create table invisible_t(id int,name varchar2(20),passwd varchar2(20) invisible);
# 向表中插入数据
SQL>insert into invisible_t values(1,'andy',1);
ERROR at line 1:
ORA-00913: too many values
SQL> insert into invisible_t(id,name,passwd) values(1,'andy',1);

# 查看表
# 在Oracle中查看所有的表:
select * from tab/dba_tables/dba_objects/cat;

# 看用户建立的表 :
select table_name from user_tables;  //当前用户的表

select table_name from all_tables;  //所有用户的表

select table_name from dba_tables;  //包括系统表

select * from user_indexes //可以查询出所有的用户表索引


# 删除表
drop table tabname;

# 重命名表
alter table tablename rename to newtablename;

# 增加字段
alter table tablename add (ID varchar2(30) default '空' not null);


# 修改字段
alter table tablename modify (ID number(4));

# 重名字段
alter table tablename rename column ID to newID;

# 删除字段
alter table tablename drop column ID;

# 添加主键
alter table tabname add primary key(col)

# 删除主键
alter table tabname drop primary key(col)

# 创建索引
create [unique] index idxname on tabname(col….)

# 创建位图索引
create bitmap index ssex_bitmap_index on zhou.student(ssex);

# 删除索引
注：索引是不可更改的，想更改必须删除重新建。 
drop index idxname

# 创建视图
create view viewname as select statement

# 删除视图
drop view viewname

2. 分区表

分区类型

# 使用id进行分区
create table p_test(id number(7), type varchar(1), op_time date) 
PARTITION BY RANGE(id) 
(partition t_operate_log_1 values less than (100), 
partition t_operate_log_2 values less than (200), 
partition t_operate_log_3 values less than (300));

# 添加分区
ALTER TABLE p_test ADD PARTITION p4 VALUES LESS THAN(400); 

# 删除分区
ALTER TABLE p_test DROP PARTITION p4; 

# 插入数据
SQL> insert into p_test values(100, '1');

1 row created.

# 插入的数据无法匹配分区范围时
SQL> insert into p_test values(200, '1');
insert into p_test values(200, '1')
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition


# 查询数据
SQL> select * from p_test;

	ID T
---------- -
	 1 1
       100 1

SQL> select * from p_test partition(p1);

	ID T
---------- -
	 1 1

SQL> select * from p_test partition(p2);

	ID T
---------- -
       100 1

# 基于时间分区
create table t_operate_log 
( 
  id   number(7), 
  type varchar(1), 
  op_time date 
) 
PARTITION BY RANGE(op_time) 
( 
 partition t_operate_log_1 values less than (to_date('2019-01-17','yyyy-MM-dd')), 
 partition t_operate_log_2 values less than (to_date('2020-01-17','yyyy-MM-dd')), 
 partition t_operate_log_3 values less than (maxvalue) 
)


# 基于列表分区
create table t_operate_log 
( 
  id   number(7), 
  type varchar2(10), 
  op_time date 
) 
PARTITION BY list(type) 
( 
 partition t_operate_log_add values('add'), 
 partition t_operate_log_delete values('delete'), 
 partition t_operate_log_edit values('edit'), 
 partition t_operate_log_query values('query') 
) 

# 给列表分区增加元素
ALTER TABLE t_operate_log MODIFY PARTITION t_operate_log_query ADD VALUES('select'); 
1.
# 给列表分区删除元素
ALTER TABLE t_operate_log MODIFY PARTITION t_operate_log_query DROP VALUES('select'); 


# HASH分区
create table t_operate_log 
( 
  id   number(7), 
  type varchar2(10), 
  op_time date 
) 
PARTITION BY hash(id) 
( 
 partition t_operate_log_1, 
 partition t_operate_log_2, 
 partition t_operate_log_3, 
 partition t_operate_log_4 
)

给现有表创建分区

1.创建表
create table t_operate_log 
( 
  id   number(7), 
  type varchar(1), 
  op_time date 
) 

2.创建一个分区表，只有1个分区
create table t_operate_log_p 
 
( 
 
id number(7), 
 
type varchar(1), 
 
op_time date 
 
) 
 
PARTITION BY RANGE(id) 
 
( 
 
partition t_operate_log_1 values less than (101) 
 
) 

3.把原表数据抽取到分区表
ALTER TABLE t_operate_log_p 
  EXCHANGE PARTITION t_operate_log_1 
  WITH TABLE t_operate_log 
  WITHOUT VALIDATION; 

4.删除原有表，把新的表改名为原来的表
DROP TABLE t_operate_log; 
RENAME t_operate_log_p TO t_operate_log; 

5.把新表拆分成多个分区表
ALTER TABLE t_operate_log SPLIT PARTITION t_operate_log_1 AT (id) 
INTO (PARTITION t_operate_log_2, 
      PARTITION t_operate_log_3);

3. 约束

# 查看约束信息
SELECT * FROM DBA_CONSTRAINTS;

SELECT * FROM ALL_CONSTRAINTS;

SELECT * FROM USER_CONSTRAINTS;


SELECT * FROM DBA_CONS_COLUMNS;

SELECT * FROM ALL_CONS_COLUMNS;

SELECT * FROM USER_CONS_COLUMNS;



# 示例
一、非空约束（只能在列级设置，不能在表级设置）
1、在创建表时设置非空约束
CREATE TABLE table_name(column_name datatype NOT NULL);

2、在修改表时添加非空约束
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
 
3、在修改表时去除非空约束
ALTER TABLE table_name MODIFY column_name datatype NULL;
 

二、主键约束
1、在创建表时设置主键约束
在列级设置主键约束

CREATE TABLE table_name(column_name datatype PRIMARY KEY);
 

在表级设置主键约束

CONSTRAINT constraint_name PRIMARY KEY(column_name1)
 

使用desc user_constraints可以查看约束的名字等信息

2、在修改表时添加主键约束
ADD CONSTRAINT constraint_name PRIMARY KEY(column_name1);
 

3、更改约束的名称
RENAME CONSTRAINT old_name TO new_name;
 

4、删除主键约束
禁用主键约束：

DISABLE|ENABLE CONSTRAINT constraint_name;
 

彻底删除主键约束：

DROP CONSTRAINT constraint_name;
 

DROP PRIMARY KEY[CASCADE]
 

三、外键约束（涉及两个表之间的关系）
1、在创建表时设置外键约束
设置外键约束时，主表的字段必须是主键
主从表中相应的字段必须是同一个数据类型
从表中外键字段的值必须来自主表中的相应字段的值，或者为null值
CREATE TABLE table1
(column_name datatype REFERENCES
table2(column_name),……);
table1位从表，table2位主表

 

在表级设置外键约束
CONSTRAINT constraint_name FOREIGN
KEY(column_name)REFERENCES
table_name(column_name)[ON DELETE CASCADE];
 

2、在修改表时添加外键约束
ADD CONSTRAINT constraint_name FOREIGN
KEY(column_name) REFERENCES 
table_name(column_name)[ON DELETE CASCADE];
 

3、删除外键约束
禁用外键约束：
DISABLE|ENABLE CONSTRAINT constraint_name;
 
彻底删除外键约束：

DROP CONSTRAINT constraint_name;
 

四、唯一约束
唯一约束和主键约束的区别：

主键字段值必须是非空的，唯一约束允许有一个空值
主键在每张表中只能有一个，唯一约束在每张表中可以有多个
唯一约束要求每个字段不能有重复值，可以有空值，但是空值只能有一个
1、在创建表时设置唯一约束
在列级设置唯一约束

CREATE TABLE table_name
(column_name datatype QUNIQUE,……);
 

在表级设置唯一约束

CONSTRAINT constraint_name UNIQUE(column_name);
 

2、在修改表时添加唯一约束
ADD CONSTRAINT constraint_name
UNIQUE(column_name);
 

3、删除唯一约束 
DISABLE|ENABLE CONSTRAINT constraint_name;
禁用唯一约束

彻底删除唯一约束

DROP CONSTRAINT constraint_name;
 

五、检查约束
1、在创建表时设置检查约束
在列级设置检查约束：

CREATE TABLE table_name(
column_name datatype CHECK(expressions),……);
 

表级设置检查约束：

CONSTRAINT constraint_name CHECK(expressions);
 

2、在修改表时添加检查约束
ADD CONSTRAINT constraint_name
CHECK(expressions);
 

3、删除检查约束
禁用检查约束：

DISABLE|ENABLE CONSTRAINT constraint_name;
 

彻底删除检查约束：

DROP CONSTRAINT constraint_name;

4. 序列

# 创建序列需要CREATE SEQUENCE系统权限。序列的创建语法如下：

　　CREATE SEQUENCE 序列名

　　[INCREMENT BY n]  # INCREMENT BY用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表Oracle序列的值是按照此步长递减的。

　　[START WITH n]  # START WITH 定义序列的初始值(即产生的第一个值)，默认为1。

　　[{MAXVALUE/ MINVALUE n| NOMAXVALUE}]  # MAXVALUE/MINVALUE定义序列生成器能产生的最大/最小值。

　　[{CYCLE|NOCYCLE}]   # CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。

　　[{CACHE n| NOCACHE}]; # CACHE(缓冲)定义存放序列的内存块的大小，默认为20。改善性能


# 创建，指定初始和步长
SQL> create sequence t1_seq increment by 1 start with 1;

SYS@orclasm > create sequence t1_seq increment by -1 start with  -5;



# 获取序列当前与下一个值，第一次使用nextval为当前值
SYS@orclasm > select t1_seq.currval,t1_seq.nextval from dual;


   CURRVAL    NEXTVAL
---------- ----------
        -5         -5
        
# 将序列与表的字段结合
SQL> create table t1(id number,qq number,ww number);

SQL> insert into t1 values(t1_seq.nextval,1,1);

# 修改序列
SQL> alter sequence t1_seq increment by 2 maxvalue 40 cycle;

# 查询序列
SQL> select sequence_name,increment_by,cycle_flag,cache_size from user_sequences;