Oracle学习笔记（三）Oracle建表、查询、插入、更新、删除

ITPro进化论 2021-12-26

114

一、Oracle字段数据类型

常用的Oracle列字段的数据类型如下：

数据类型	类型解释
VARCHAR2(length)	字符串类型：存储可变的长度的字符串，length:是字符串的最大长度，默认不填的时候是1，最大长度不超过4000。
CHAR(length)	字符串类型：存储固定长度的字符串，length:字符串的固定长度大小，默认是1，最大长度不超过2000。
NUMBER(a,b)	数值类型：存储数值类型，可以存整数，也可以存浮点型。a代表数值的最大位数：包含小数位和小数点，b代表小数的位数。例子： number(6,2)，输入123.12345，实际存入：123.12 。 number(4,2)，输入12312.345，实际存入：提示不能存入，超过存储的指定的精度。
DATE	时间类型：存储的是日期和时间，包括年、月、日、时、分、秒。例子：内置函数sysdate获取的就是DATE类型
TIMESTAMP	时间类型：存储的不仅是日期和时间，还包含了时区。例子：内置函数systimestamp获取的就是timestamp类型
CLOB	大字段类型：存储的是大的文本，比如：非结构化的txt文本，字段大于4000长度的字符串。
BLOB	二进制类型：存储的是二进制对象，比如图片、视频、声音等转换过来的二进制对象

二、create table语句

案例：创建stuinfo（学生信息表）

-- Create table
create table victor.stuinfo
(
stuid varchar2(11) not null,--学号：'S'+班号（7位数）+学生序号（3位数）(not null 表示学号字段（stuid）不能为空。)
stuname varchar2(50) not null,--学生姓名
sex char(1) not null,--性别
age number(2) not null,--年龄
classno varchar2(7) not null,--班号：'C'+年级（4位数）+班级序号（2位数）
stuaddress varchar2(100) default '地址未录入',--地址 (default 表示字段stuaddress不填时候会默认填入‘地址未录入’值。)
grade char(4) not null,--年级
enroldate date,--入学时间
idnumber varchar2(18) default '身份证未采集' not null--身份证
)
tablespace USERS --(表示表stuinfo存储的表空间是users，storage表示存储参数：区段(extent)一次扩展64k，最小区段数为1，最大的区段数不限制。)
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table victor.stuinfo --(comment on table 是给表名进行注释。)
is '学生信息表';
-- Add comments to the columns
comment on column victor.stuinfo.stuid -- (comment on column 是给表字段进行注释。)
is '学号';
comment on column victor.stuinfo.stuname
is '学生姓名';
comment on column victor.stuinfo.sex
is '学生性别';
comment on column victor.stuinfo.age
is '学生年龄';
comment on column victor.stuinfo.classno
is '学生班级号';
comment on column victor.stuinfo.stuaddress
is '学生住址';
comment on column victor.stuinfo.grade
is '年级';
comment on column victor.stuinfo.enroldate
is '入学时间';
comment on column victor.stuinfo.idnumber
is '身份证号';

通过上面crate table命令创建了stuinfo学生信息表后，还可以给表添加相应的约束来保证表数据的准确性。比如：学生的年龄不能存在大龄的岁数，可能是错误数据、性别不能填入不是1（男）、2（女）之外的数据等。

-- Create/Recreate primary, unique and foreign key constraints 
alter table victor.STUINFO
  add constraint pk_stuinfo_stuid primary key (STUID);
  --把stuid当做主键，主键字段的数据必须是唯一性的（学号是唯一的）
  
-- Create/Recreate check constraints 
alter table victor.STUINFO
  add constraint ch_stuinfo_age
  check (age>0 and age<=50);--给字段年龄age添加约束，学生的年龄只能0-50岁之内的
  
alter table victor.STUINFO
  add constraint ch_stuinfo_sex
  check (sex='1' or sex='2');
  
alter table victor.STUINFO
  add constraint ch_stuinfo_GRADE
  check (grade>='1900' and grade<='2999');

三、Oracle查询（select）

简单查询是利用SELECT命令从表中进行提取数据，

1、SELECT命令结构：

select *|列名|表达式 from 表名 where 条件 order by 列名

案例：查询学生信息表（stuinfo）中“张三”同学的基本信息：

select * from victor.STUINFO t where t.stuname = '张三';

案例：查询“李四”同学的学号、班级、年级和地址：

select t.stuid,t.classno,t.stuaddress,t.grade from victor.STUINFO t where t.stuname = '李四';

案例：查询班级“C202101”所有同学信息，按年龄进行升序展示：

select t.*  from victor.STUINFO t where t.classno = 'C202101' ORDER BY T.AGE ASC

语法解析：

1、“t”代表stuinfo的别名。

2、 "*" 代表所有字段。

3、表达式可以是函数（列名）、常数、连接词“||”等组成的表达式。

4、where子语句是查询语句的条件。

5、order by :查询结果按某个字段进行排序，默认是升序，desc是降序。

2、备份查询数据：

Oracle进行表数据备份时，可以利用create table（建表）的方式对select查询的结果进行快速备份。

备份查询数据命令结构：

create table 表名 as select 语句

案例：备份学生信息表（stuinfo）的数据：

create table victor.stuinfo_2021 as select * from victor.stuinfo ;


select * from victor.stuinfo_2021;

四、Oracle插入（insert into）

Oracle对表数据的插入是使用insert命令来执行的。

1、insert 命令结构：

insert into 表名（列名1,列名2,列名3.....）values(值1,值2,值3.....);

语法解析：

1、列名可以省略，当列名不填时，默认的是表中的所有列，列的顺序是按照建表的顺序进行排列的。

2、列名的数量和值的数量要一致，并且值的类型要和列的类型一一对应。

3、当表当中某些字段设置了某些约束的情况下，必须按照字段的约束来进行该值的插入，例如：学生信息表（STUINFO）当中设置有主键（主键字段是STUID）,因此该字段必须具有唯一性，不能和原有的数据重复。age、stuname、calassno等字段是必填字段，因此是必须有值的。

案例：向学生信息表（stuinfo）插入一条数据：

insert into victor.STUINFO (STUID, STUNAME, SEX, AGE, CLASSNO, STUADDRESS, GRADE, ENROLDATE, IDNUMBER)
values ('SC202001005', '王二', '1', 27, 'C202101', '山东省青岛市XXX号', '2021', to_date('01-09-2021', 'dd-mm-yyyy'),
 '3702821996XXXXXXXX');
 
select * from victor.stuinfo t where t.stuid='SC202001005';

2、insert插入一个select的结果集：

语法结构如下：

INSERT INTO 表 SELECT 子句

案例：把表stuinfo_2021的数据一次插入表stuinfo当中：

delete  from victor.stuinfo t where t.stuid in (select b.stuid from victor.stuinfo_2021 b );
insert into victor.stuinfo select * from victor.stuinfo_2021;


select * from victor.stuinfo;

提示！

数据操纵语言（DML）包括 INSERT（插入）命令、 UPDATE（更新）命令、 DELETE（删除）命令、 SELECT … FOR UPDATE（查询）等。只有提交（commit）后才能持久化到数据库。

五、Oracle更新（update）

Oracle对表数据的更新是使用update命令来执行的。

update命令结构：

update 表名 set 列名1=值1,列名2=值2,列名3=值3..... where 条件

案例：更新学生“张三”的年龄和身份证信息：

update victor.stuinfo t
   set t.age = '25', t.idnumber = '350302200011111111'
 where t.stuname = '张三';
commit;


select * from victor.stuinfo t where t.stuname='张三';

update 利用另外一张表关联更新本表数据的命令结构如下：

update 表1 set 列名=（select 列名 from 表2 where 表1.列名=表2.列名） 
       where exists (select 1 from 表2 where 表1.列名=表2.列名)

案例：利用备份表stuinfo_2021更新回学生“张三”的年龄和身份证：

update victor.stuinfo t
   set (age, idnumber) =
       (select age, idnumber from victor.stuinfo_2021 b where b.stuid = t.stuid)
 where exists (select 1
          from victor.stuinfo_2021 b
         where b.stuid = t.stuid
           and b.stuname = '张三');
           
select *from victor.stuinfo t where t.stuname='张三';

六、Oracle删除（delete）

Oracle中对表数据的删除是利用delete命令进行的。

1、delete命令结构：

delete from 表名 where 条件

命令解析：

1、当delete from不加where条件时，表示是把表中的数据全部删除。

案例：删除学生信息表（stuinfo）中学生“张三”的数据：

delete  from stuinfo t where t.stuname='张三';

2、truncate命令

truncate命令也是数据删除命令，他是直接把Oracle表数据一次删除的命令，truncate命令是一个DDL命令，不同于delete是DML命令。

truncate命令结构：

truncate table 表名；

truncate和delete都能删除表中的数据，他们的区别：

1、TRUNCATE 是 DDL 命令，命令执行完就提交，删除的数据不能恢复； DELETE 命令是 DML 命令，命令执行完需提交后才能生效，删除后的数据可以通过日志文件恢复。

2、如果表中的数据量较大，TRUNCATE的速度比DELETE速度快很多。

3、truncate删除将重新设置表索引的初始大小，而delete不能。

4、delete能够触发表上相关的delete触发器，而truncate则不会触发。

5、delete删除的原理是一次一条从表中删除数据，并将删除操作当做事物记录在数据库的日志当中，以便进行数据回滚。而truncate是一次性进行数据页的删除，因此执行速度快，但是不能回滚。

总结：truncate命令是属于DDL命令，一次性删除表中所有数据，并且数据不能恢复，在实际开发过程当中truncate命令慎用。

文章转载自ITPro进化论，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。