做网站如何适应分辨率,咨询公司的企业文化,网站模板放哪,企业建站程序推荐文章目录 往期回顾前言1. 表的创建2. 表的修改3. 表中数据的增删改查#xff08;1#xff09;插入数据#xff08;2#xff09;删除数据#xff08;3#xff09;更新数据 4. 表的Merge5. 表的删除6. 表的重命名7. 表的索引#xff08;1#xff09;B树索引#xff08;2… 文章目录 往期回顾前言1. 表的创建2. 表的修改3. 表中数据的增删改查1插入数据2删除数据3更新数据 4. 表的Merge5. 表的删除6. 表的重命名7. 表的索引1B树索引2位图索引3函数索引4全文索引 8. 表的约束1主键约束2外键约束3唯一约束4CHECK约束 9. dual表 往期回顾
【Oracle】Oracle系列–Oracle数据类型【Oracle】Oracle系列之二–Oracle数据字典【Oracle】Oracle系列之三–Oracle字符集【Oracle】Oracle系列之四–用户管理【Oracle】Oracle系列之五–Oracle表空间【Oracle】Oracle系列之六–Oracle表分区
前言
表是Oracle数据库中最基础的存储对象用于存储数据。本文主要介绍了Oracle表的创建与管理包括表的创建、修改、删除、重命名表的索引、约束以及表中数据的增、删、改、查等基本操作。
1. 表的创建
在Oracle数据库中创建表需要使用CREATE TABLE语句
CREATE TABLE table_name
(column1 datatype [ NULL | NOT NULL ],column2 datatype [ NULL | NOT NULL ],...columnN datatype [ NULL | NOT NULL ]
);其中table_name是要创建的表的名称column1 ~ columnN是表中的列名datatype是数据类型NULL和NOT NULL表示该列是否允许为空。
如创建一个名为student的表包含学生的姓名、性别、年龄和学号四个字段使用以下语句
CREATE TABLE student
(name VARCHAR2(50) NOT NULL,gender CHAR(1) NOT NULL,age NUMBER(3),num VARCHAR2(20) PRIMARY KEY
);上述语句创建了一个名为student的表包含四个字段name姓名、gender性别、age年龄和num学号。其中name和gender字段不允许为空age字段允许为空num字段为主键用于唯一标识每个学生。
创建表的其他参数
PCTFREE 保留空间PCTUSED 从表中删除数据使得数据块空间不断减少减少至40%时可再次插入数据PCTFREE与PCTUSED之和越接近100%数据块空间利用率越高。INITRANS 初始事务数量MAXTRANS 最大的事务并发数量CACHE 指定将表中的数据放在数据库高速缓存中默认NOCACHE。对于较小、访问频繁的表使用CACHE在用户第一次访问表中数据时整个表被加载到数据库高速缓存。LOGGING 默认情况下用户在表上执行DDL和DML都会产生重做日志。如果不希望产生重做日志使用NOLOGGING子句。COMPRESS 使用该子句一个数据块中两行完全相同的数据将被压缩为一行。
创建表的时候插入数据 create table tt as select idname from table1; create table t1 parallel 8 nologging compress as select * from table1; 创建临时表
临时表为事务级事务提交或回滚时数据即被删除
create global temporary table t1(name varchar(10)) on commit delete rows;临时表为会话级表中数据一致保留直到当前会话结束。
create global temporary table t1(name varchar(10)) on commit preserve rows;2. 表的修改
实际开发中可能需要对已有的表进行修改如添加新的列、删除已有的列、修改列的数据类型等。Oracle提供了ALTER TABLE语句来实现这些操作
ADD用于添加新的列
ALTER TABLE table_name
ADD (column_name datatype [ NULL | NOT NULL ],column_name datatype [ NULL | NOT NULL ],...);DROP COLUMN用于删除已有的列
ALTER TABLE table_name DROP COLUMN column_name;MODIFY用于修改列的数据类型
ALTER TABLE table_name
MODIFY (column_name datatype [ NULL | NOT NULL ]);如对于之前创建的student表添加一个新的字段address
ALTER TABLE student
ADD (address VARCHAR2(100) NULL);上述语句在student表中添加了一个新的字段address数据类型为VARCHAR2长度为100允许为空。
3. 表中数据的增删改查
1插入数据
使用INSERT语句向表中插入数据
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);其中table_name是要插入数据的表的名称column1、column2、column3等是表中的列名value1、value2、value3等是要插入的数据值。
例如向student表中插入一条数据可以使用以下语句
INSERT INTO student (name,gender, age,num)
VALUES ( John,M,20,01);
该语句将向student表中插入一条数据包含name为’John’、gender为’M’、age为20、num为01的记录。
向目标表插入源表数据
插入数据时如果源表和目标表字段名、字段数量、字段顺序都相同
insert into table1 select * from table2;否则 使用
insert into table1(col1 col2 ... coln) select col1 col2 ... coln from table2;2删除数据
使用DELETE语句从表中删除数据
DELETE FROM table_name WHERE condition;其中table_name是要删除数据的表的名称condition是删除记录的条件。
例如从student表中删除age大于等于25的记录可以使用以下语句
DELETE FROM student WHERE age 25;3更新数据
使用UPDATE语句更新表中的数据
UPDATE table_name
SET column1 value1, column2 value2, ...
WHERE condition;其中table_name是要更新数据的表的名称column1、column2等是要更新的列名value1、value2等是要更新的数据值condition是更新记录的条件。
例如将student表中id为1的记录的age更新为21可以使用以下语句
UPDATE student
SET age 21
WHERE id 1;4查询数据
使用SELECT语句从表中检索数据例如查询student表中i所有年龄大于26岁的学生信息
SELECT * FROM student WHERE age 20;4. 表的Merge
Merge用于需要使用SQL语句同时进行Insert/Update的操作也就是说当存在记录时就更新(Update)不存在数据时就插入(Insert)。
执行Merge前
执行以下语句
Merge Into products t
Using newproducts s
On (t.product_ids.product_id)
When Matched Then
Update Set t.product_names.product_namet.Categorys.Category
When Not Matched Then
Insert Values(s.product_ids.product_names.Category)其中insert和update是可选的UPDATE 和INSERT后面可以跟WHERE子句UPDATE子句后面可以跟delete来去除一些不需要的行。在ON条件中可以使用常量来insert所有的行到目标表中不需要连接到源表和目标表。
5. 表的删除
使用TRUCATE TABLE语句清除表中所有内容保留结构 truncate table table_name; 使用DROP TABLE语句删除某个表
DROP TABLE table_name;例如删除之前创建的student表
DROP TABLE student;上述语句将删除名为student的表及其所有数据。在执行DROP TABLE语句前应确保该表不再被其他对象所使用否则会抛出错误。
使用purge彻底删除表
6. 表的重命名
使用RENAME语句修改表名
RENAME table_name TO new_table_name;例如将之前创建的student表重命名为new_student
RENAME student TO new_student;1
7. 表的索引
数据库索引的作用是减少读数据所需的磁盘访问次数加快数据访问速度提高数据库性能。
索引提供指向存储在表的指定列中的数据值的指针可快速定位需要的数据从而提高查询效率。如果没有索引查询需要扫描整个表将花费大量时间和资源。
Oracle索引由一系列存储在磁盘上的索引项组成索引项第一列是索引键(keyval)第二列是行指针(ROWID)。
ROWID由数据库自动生成包含18个字符串
OOOOOO/FFF/BBBBBB/RRR对应数据库对象编号/数据文件编号/数据块号/行号。
Oracle数据库中有多种类型的索引包括B树索引、位图索引、函数索引、全文索引等。
1B树索引
Oracle默认为B树是Oracle数据库中最常用的索引类型。它是一种平衡树结构可以快速定位需要的数据。B树索引通常由一个或多个列组成这些列存储了数据库表中的数据。当查询需要访问这些数据时B树索引可以帮助快速定位它们。
B树索引的优点是查询速度快适用于大多数查询场景。但B树索引维护成本高当表中的数据发生变化时需要更新索引这将影响数据库的性能。
2位图索引
位图索引是一种用于处理大量重复数据的索引类型。它将每个索引值映射到一个位图每个位图表示一个索引值是否存在。当查询需要访问这些数据时位图索引可以帮助快速定位它们。
位图索引的优点是适用于处理大量重复数据的查询场景可以显著提高查询效率。但是位图索引不适用于处理不重复的数据且索引维护成本高。
3函数索引
函数索引是一种用于处理函数表达式的索引类型。它将函数表达式的计算结果存储在索引中当查询需要访问这些数据时函数索引可以帮助快速定位它们。
函数索引的优点是适用于处理函数表达式的查询场景可以显著提高查询效率。但是函数索引的缺点是索引维护成本高且只适用于特定的函数表达式。
4全文索引
全文索引是一种用于处理文本数据的索引类型。它将文本数据分解为单词并将每个单词映射到一个索引值。当查询需要访问这些数据时全文索引可以帮助快速定位它们。
全文索引的优点是适用于处理文本数据的查询场景可以显著提高查询效率。但是全文索引的缺点是索引维护成本高且需要特定的全文搜索引擎。
当字段取值较多时如证件号码则应使用B-Tree索引 当字段值取值较少的情况下如性别应使用位图索引 在Oracle中可以为表中的列创建索引。例如在student表的num列上创建一个名为“idx_num”的索引
CREATE INDEX idx_num ON student(num);8. 表的约束
数据库中约束是用来保证数据的完整性和一致性的规则。Oracle数据库通过使用约束来限制表中数据的输入和更改使得数据库中存储的数据是结构正确的并且可以被其他程序和用户正确地共享和查询。
1主键约束
主键约束是用来唯一标识表中每个记录的一种方式即主键必须在表中具有唯一性不能为NULL且只能定义一个主键约束。主键通常用来建立关系型数据库之间的连接。
建立主键约束
ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2);如对于student表建立主键约束
Alter Table student Add Constraint pk_num Primary Key (num);2外键约束
外键约束用于在表之间建立一对多或多对多的关系它指明了一个表中的某个字段必须引用另一个表中的主键约束。外键值必须在引用表中存在或者为空值。
创建外键约束
ALTER TABLE table_name
ADD CONSTRAINT constraint_name FOREIGN KEY (column1) REFERENCES other_table(column2);3唯一约束
唯一约束用于确保某个字段或一组字段中的值是唯一的。与主键不同唯一约束允许 null 值。
创建唯一约束
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2);4CHECK约束
创建CHECK约束
Alter Table nn Add Constraint ck_emp_n2 Check(n2男 Or n2女)9. dual表
dual是一个虚拟表用来构成select的语法规则。
查看表结构 desc dual; oracle保证dual里面永远只有一条记录查询表数据
select * from dual; Dual属于SYS schema以PUBLIC SYNONYM的方式供其他数据库USER使用
SQL select owner object_name object_type from dba_objects where object_name like %DUAL%; OWNER OBJECT_NAME OBJECT_TYPE常见用途如下
select user from dual; #查看当前用户
select SYS_CONTEXT(USERENVTERMINAL) from dual; #调用系统函数
SQL select your_sequence.nextval from dual; #得到序列下一个值
SQL select your_sequence.currval from dual; #得到序列当前值
SQL select 7*9 from dual; #计算器
