Oracle数据库-增删改查
一、创建表
语法:
CREATE TABLE [schema.]table_name( column_name datatype [default default_value] [column_constaint], …… [table_constaint] ) |
a、table_name——表名
b、column_name——字段名
Oracle表名与字段名命名规则:
i、必须以字母开始
ii、必须是1到30 个字符长度
iii、只能包含字母、数字、下划线“_”、美元符“$”和井号“#”
Ⅳ、不能使用Oracle的关键字
Ⅴ、同一个用户所拥有的对象之间不能重名
c、datatype——数据类型
Oracle常用数据类型:
i、char(size):存储固定长度的字符串。参数size指定了长度,如果存储的字符串长度小于size,用空格填充,默认长度为1,最长不超过2000个字节;
ii、varchar2(size):存储可变长度的字符串。参数size指定了该字符串的最大长度。默认长度为1,,最长不超过4000个字节;
iii、number(p,s):存储变长的数字。既可以存储浮点数,也可以存储整数。p表示数字的最大位数(如果是小数包括整数部分、小数部分,不包括小数点,p默认是38位),s指小数位数
Ⅳ、timestamp:和DATE类型大致相同,不过timestamp精确到了秒后6位小数
Ⅴ、clob:存储单字节字符大数据。和varchar2数据类型相似,最大可以存储4G的数据,可以用来存储非结构化的XML文档
ⅶ、blob:存储无结构的二进制大数据。最大可以存储4G的数据,可以用来存储图像、视频、音频等信息;
d、[default default_value]——设定字段默认值
create table student( sex number(1) default '0' ) |
e、约束
Oracle数据库通过约束(constraints)防止无效的数据进入到表中,以保护数据的实体完整性。为了便于约束的使用,往往需要为约束起一个有意义的名字(例如student_id_pk表示student表id列为主键);如果不显式命名约束,Oracle数据库将以SYS_Cn的格式为约束命名,这里n是唯一的整数。约束分为字段级别约束和表级别约束:字段级约束:只为单个字段添加约束;表级约束:为一个或多个字段添加约束
①、not null:指定字段不能为空,只能定义为字段级约束
create table user_info(
user_name varchar2(10) not null,--约束名由Oracle指定,格式为SYS_Cn
password varchar2(15) constraint student_password_nn not null--自定义约束名
)
注意:没有显式命名的非空约束,使用PL/SQL Developer在Checks约束选项卡中是看不到的。
②、unique:指定字段的值(或字段组合的值)对于表中所有的行必须是唯一的。对于无非空约束的字段,唯一键约束允许输入空值,且包含空值的行可以有多个。可以为字段级别约束,也可以为表级别约束,表级约束时可以定义复合唯一键。主键是表中每行数据的唯一标识。
create table user_info(
user_name varchar2(30) unique
)
create table user_info(
user_name varchar2(10),
password varchar2(15),
unique(user_name,password)--user_name和password 联合做唯一键
)
③、primary key:指定主键。该约束强制字段或字段组合必须具有唯一性且每个字段不能为空。可以为字段级别约束,也可以为表级别约束。
create table user_info(
id char(36) primary key,
user_name varchar2(10)
)
或
create table user_info(
id char(36) constraint user_info_id_pk primary key,
user_name varchar2(10)
)
或
create table user_info(
id char(36),
user_name varchar2(10) ,
constraint user_info_id_pk primary key(id)
)
create table user_info( id char(36), user_name varchar(10), mobile char(11), primary key(id,user_name)--多列做主键 ) insert into user_info (id,user_name) values ('f4fc1a54-390b-4284-ae1d-004fbe0adf55',null);--做主键的多列,每列都不允许为null |
注意:每个表只能创建一个主键
④、foreign key:指定一个字段或字段组合作为一个外键(即外来的主键或唯一键),该外键和另一个表的主键或唯一键简历起一个关系。外键值必须匹配父表中存在的值;如果父表主键值或唯一键值是子表的外键值,则父表中的这条数据不能删除,Oracle数据库中外键的值可以为null。
需求:如何将京东fuliuqingfeng的用户信息及其多个邮寄商品地址保存到数据库中
方案1:
create table user_info( id char(36) primary key, user_name varchar2(30) not null, password varchar2(30) not null, real_name varchar2(8), mobile char(11), address varchar2(150) ) insert into user_info (id,user_name,password,real_name,mobile,address) values ('51b28fe1-4ebf-41ac-a17b-d5e276861fd0','fuliuqingfeng','123456','张三','18920120206','河南安阳'); insert into user_info (id,user_name,password,real_name,mobile,address) values ('cc95772b-75a2-4702-bd99-4c3b0322d606','fuliuqingfeng','123456','李四','18617297545','北京海淀'); insert into user_info (id,user_name,password,real_name,mobile,address) values ('c63028fd-cf8d-4dac-a278-b5cc8fd61e3c','fuliuqingfeng','123456','王五','17694976949','山西大同'); commit; |
|
问题:这种表结构存在严重的字段冗余,如果个人信息字段比较多这一问题表现的越严重。
方案2:
create table user_info( id char(36) primary key, user_name varchar2(30) not null, password varchar2(30) not null ) insert into user_info (id,user_name,password) values ('51b28fe1-4ebf-41ac-a17b-d5e276861fd0','fuliuqingfeng','123456'); commit; create table address( id char(36) primary key, user_info_id char(36), real_name varchar2(8) not null, mobile char(11) not null, address varchar2(150) not null ) insert into address (id,user_info_id,real_name,mobile,address) values ('bfb9472a-7911-4e6f-a479-3b719454ebab','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','高焕杰','12345678901','河南'); insert into address (id,user_info_id,real_name,mobile,address) values ('5227c6b9-45a2-44aa-8ac0-1f63a38d3b65','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','高焕杰','12345678901','北京'); insert into address (id,user_info_id,real_name,mobile,address) values ('30b8584b-aa6a-4516-a623-03f487058586','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','高焕杰','12345678901','山西'); commit; |
问题:这种表结构消除了字段冗余,但由于只是逻辑上的“外键”关系,所以依然无法保证数据完整性,例如可以将user_info中id为51b28fe1-4ebf-41ac-a17b-d5e276861fd0的数据删除,但此时地址表中数据将不再完整——找不到这些地址属于哪个用户;再例如也可以向address表中添加一条user_info_id不存在的地址信息(如:insert into address (id,user_info_id,real_name,mobile,address) values ('7da42cc6-36a6-4ad5-9998-71dbc30c8e17','ddc376dd-f8b3-42a6-b42a-db22abed1941','张三','18338970095','北京东城区');)——同样,该条数据并不完整,依然找不到这些地址属于哪个用户
方案3:
create table user_info( id char(36) primary key, user_name varchar2(30) not null, password varchar2(30) not null ) insert into user_info (id,user_name,password) values ('51b28fe1-4ebf-41ac-a17b-d5e276861fd0','fuliuqingfeng','123456'); commit; create table address( id char(36) primary key, user_info_id char(36) constraint address_user_info_id_fk references user_info(id), real_name varchar2(8) not null, mobile char(11) not null, address varchar2(150) not null, ) --创建address表语句可为如下脚本: create table address( id char(36) primary key, user_info_id char(36), real_name varchar2(8) not null, mobile char(11) not null, address varchar2(150) not null, constraint address_user_info_id_fk foreign key(user_info_id) references user_info(id) ) insert into address (id,user_info_id,real_name,mobile,address) values ('bfb9472a-7911-4e6f-a479-3b719454ebab','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','高焕杰','12345678901','河南'); insert into address (id,user_info_id,real_name,mobile,address) values ('5227c6b9-45a2-44aa-8ac0-1f63a38d3b65','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','高焕杰','12345678901','北京'); insert into address (id,user_info_id,real_name,mobile,address) values ('30b8584b-aa6a-4516-a623-03f487058586','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','高焕杰','12345678901','山西'); commit; |
说明:这种方案为user_info_id添加了外键,指向user_info表的主键,该约束起到了保护数据完整性的作用:如果删除的用户信息id已经在address表中使用,则该条数据无法删除;无法向address表中添加用户id不存在的地址信息。
注意:
1、设置表中字段为外键时,该字段对应父表字段必须是父表的主键或唯一键,否则创建失败,例如:
|
create table class_info( id char(36) ) create table student( id char(36) primary key, name varchar2(8) not null, class_id char(36), foreign key(class_id) references class_info(id)--由于class_info表中id不是主键,亦不是唯一键,所以该外键创建失败 ) |
2、外键数据类型要和父表对应字段数据类型一致,否则会出错;另外字段间长度也建议一致;
create table class_info( id char(36) unique ) create table student( id char(36) primary key, name varchar2(8) not null, class_id varchar2(36), foreign key(class_id) references class_info(id)--由于本表class_id字段与class_info表中id字段数据类型不一致,所以该外键创建失败 ) |
|
⑤、check:指定列值必须满足check中条件,否则无法进行添加或修改;一个字段可以定义多个check约束。
create table people(
age number(3) not null check(age>0 and age<150)-- --年龄不能为空并且范围在0~150
)
可以通过视图查看某个表有哪些约束,例如
select * from user_constraints where table_name='STUDENT';--查询student表上的约束,注意:表名必须大写 |
二、修改表
1、修改表字段:
a、添加字段
alter table student
add (sex char(2) default '1' constraint student_sex_type check(sex='1' or sex='2'))
注意:新添加的字段在表的最后一列,不能指定字段的位置,这与MySQL数据库不同(MySQL数据库可以通过after设定添加字段的先后顺序)
b、修改字段
alter table student
modify(name varchar2(12))
注意:字段的修改包括修改数据类型(只有对应列为空指才可以修改)、大小和默认值,其中默认值的修改只会影响后来插入表的数据,对之前的数据不会产生影响。
c、删除字段
alter table student
drop(sex)
注意:
①、一次只能删除一个字段;
②、一个表至少要保留一个字段;
③、如果所删除列(如publisher表中id列)是另一个表的外键(book表publisher_id)则该列(publisher表中id列)无法删除,如下图所示:
|
2、修改表字段约束:
a、添加约束
alter table student
add constraint student_id_pk primary key(id);--将student表id列设为主键
注意:
1、可以一次添加多个约束,如下:
create table student( age number(3) )
alter table student add constraint student_age_0_to_150 check (age>=0 and age<=150)--等效于如下SQL
alter table student add constraint student_age_from_0 check (age>=0) add constraint student_age_to_150 check (age<=150)
|
2、不能通过添加约束为某个字段添加not null约束,只能通过修改字段为其指定not null,如下:
alter table user_info add password varchar2(2) not null--不能通过添加约束为password添加not null约束 alter table user_info modify (NAME not null)--通过修改字段为name字段指定not null
|
b、删除约束
alter table student
drop constraint student_sex_type;--依据约束名删除约束
alter table student
drop primary key--依据约束类型删除约束
alter table student
drop unique(id) --依据约束类型删除约束
注意:drop子句后面可以使用cascade,意为删除约束的同时与其相依赖的约束也一同删除,例如:
create table class_info( id char(36) unique ) create table student( id char(36) primary key, name varchar2(8) not null, class_id char(36), constraint student_class_id_fk foreign key(class_id) references class_info(id) ) alter table class_info drop unique(id) cascade--不仅会删除class_info表唯一键约束,还会将与之相依赖的student表外键约束一并删掉。注意:如果没有cascade,则会报如下错误信息: |
|
c、启用或禁用约束
禁用约束:
alter table student
disable constraint student_sex_type;--禁用约束
注意:disable子句后面可以使用cascade,意为禁用约束的同时与其相依赖的约束也一同禁用,例如:
create table class_info( id char(36) primary key ) |
create table student( id char(36) primary key, name varchar2(8) not null, class_id char(36), constraint student_class_id_fk foreign key(class_id) references class_info(id) ) alter table class_info disable primary key cascade--不仅会禁用class_info表主键约束,还会将与之相依赖的student表外键约束一并禁用掉。注意:如果没有cascade,则会报如下错误信息: |
|
启用约束:
alter table student
enable constraint student_sex_type;--启用约束,注意:enable后面不能使用cascade关键字
注意:约束无法修改,只能通过先删除再创建的方式间接修改字段约束。
三、表重命名
rename 旧名字 to 新名字
四、截断表
truncate table 表名——不用执行commit就可以将表数据删除
delete from 表名——必须执行commit才可以将表数据删除
delete、drop、truncate的区别
五、删除表
drop table 表名
六、注释
表注释
comment on table 表名 is '注释内容';
字段注释
comment on column 表名.字段名 is '注释内容';