Oracle数据库的基本操作

发表于 | 更新于: | 分类于 |
字数统计: 2.2k字 | 阅读时长 ≈ 9分钟

数据库和数据表的基本操作

创建数据表

创建表——主键约束
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
create table tb_emp2
{
	id	number(11) PRIMARY KEY,
	name	varchar(25),
	dept	number(11),
	salary	number(9,2)
};

# 联合主键
create table tb_emp2
{
	name	varchar(25),
	deptId	number(11),
	salary	number(9,2)
	PRIMARY KEY(name,deptId)
};

# 添加主键
ALTER TABLE tb_emp2
ADD CONSTRAINT pk_id PRIMARY KEY KEY(id);
# 移除主键
ALTER TABLE tb1_emp1
DROP CONSTRAINT pk_id;
查看数据表是否创建成功
1
2
DESC tb_empl;
DESCRIBE tb_empl;
非空约束、唯一性约束、默认约束、检查约束、属性自增
1
2
3
4
5
6
7
8
9
create table tb_emp2
{
	id	number(11) generated by default as identity,
	name	varchar(25) NOT NULL UNIQUE,
	dept	number(11) DEFAULT 1111,
	salary	number(9,2),
	gender	varchar2(2)
	CONSTRAINT CHK_GENDER	CHECK(gender='男' or gender='女')
};
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 非空约束
AlTER TABLE tb_emp5
MODIFY name NOT NULL;
AlTER TABLE tb_emp5
MODIFY name NULL;
# 唯一性约束
ALTER TABLEtb_emp5
ADD CONSTRAINT unq_name UNIQUE(name);
ALTER TABLE 
DROP CONSTRAINT unq_name
# 检查约束
ALTER TABLE tb_emp2
ADD CONSTRAINT chk_gender CHECK(gender='男' or gender='女');
ALTER TABLE tb_emp5
DROP CONSTRAINT chk_gender

互联网开发中为什么尽量不用外键

  1. 避免使用外键，可以在插入数据时通过程序维持约束关系。
  2. 使用外键约束缺点：
    • 有额外开销，每次插入数据都需要在两个表中查询判断
    • 在高并发大流量的场景，使用外键更容易造成死锁
    • 删除主键表的数据时，需先删除外键表的数据
    • 修改外键表字段时，需重建外键约束
  3. 扩展性问题：
    • 做平台迁移方便，从Mysql迁移到Oracle，像触发器、外键这种东西，都可以利用框架本身的特性来实现，而不用依赖于数据库本身的特性，做迁移更加方便。
    • 分库分表方便，在水平拆分和分库的情况下，外键是无法生效的。将数据间关系的维护，放入应用程序中，为将来的分库分表省去很多的麻烦。

修改数据库表

修改表名

1
ALTER TABLE <tablename> RENAME TO <newname>

修改字段

1
ALTER TABLE <tablename> MODIFY <fieldname> <datatype>

修改字段名

1
ALTER TABLE <tablename> RENAME COLUMN <fieldname> TO <newfieldname>

添加字段

1
ALTER TABLE <tablename> ADD <fieldname> <datatype>

删除字段

1
ALTER TABLE <tablename> DROP COLUMN <fieldname>

视图的操作

视图概述

视图(view)，也称虚表, 不占用物理空间，这个也是相对概念，因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候, 只是重新执行SQL。

视图看上去非常象数据库的物理表，对它的操作同任何其它的表一样。当通过视图修改数据时，实际上是在改变基表中的数据；相反地，基表数据的改变也会自动反映在由基表产生的视图中。由于逻辑上的原因，有些Oracle视图可以修改对应的基表，有些则不能（仅仅能查询）。

视图的作用
  1. 简单化
    • 看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
  2. 安全性
    • 通过视图用户只能查询和修改他们所能见到的数据。数据库中的其它数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定的列上。通过Oracle视图，用户可以被限制在数据的不同子集上
  3. 逻辑数据独立性
    • 视图可帮助用户屏蔽真实表结构变化带来的影响。

创建视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 简单视图
CREATE VIEW view_t2 AS
	SELECT quantity, price 
	FROM tmp_12;

# 多表创建视图
CREATE VIEW stu_glasee(id,name,glass)
AS 
	SELECT student.s_id,student.name,stu_info.glass
	FROM student,stu_info 
	WHERE student.s_id = stu_info.s_id;

#视图上的视图
CREATE OR REPLACE VIEW stu_gl_glass
AS
	SELECT stu_glass_id, stu_glass.name
	FROM stu_glass;

查看视图

1
DESCRIBE view_t;

修改视图

1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE VIEW view_t
AS SELECT * FROM t;

# 为QUTY添加唯一约束，约束名T_UNQ，NOVALIDATE表示此前数据和以后数据都不检查
ALTER VIEW view_t
ADD CONSTRAINT T_UNQ UNIQUE (QUTY)
DISABLE NOVALIDATE;

ALTER VIEW view_t
DROP CONSTRAINT T_UNQ;

更新视图

1
UPDATE view_t SET quty=5

更新视图view_t后，基本表t的内容也更新了，基本表更新后，另一个视图的view_t2中的内容也会更新。

视图的删除操作最终也是通过删除基本表的记录实现的。

删除视图

1
DROP VIEW <view_name>

限制视图的数据操作

设置视图的只读属性
1
2
3
CREATE OR REPLACE VIEW view_tt AS
SELECT quantity,price FROM t
WITH READ ONLY;
设置视图的检查属性
1
2
3
4
5
CREATE OR REPLACE VIEW view_tc AS
SELECT quantity,price FROM t
WHERE price>10
WITH CHECK OPTION;
# 创建完成后，插入、更新、 删除操作会受到检查条件的限制

视图的应用

创建表：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE stu{
	s_id	NUMBER(11) PRIMARY KEY,
	s_name	VARCHAR2(20)	NOT NULL,
	addr	VARCHAR2(50)	NOT NULL,
	tel	VARCHAR2(50)	NOT NULL
}

# Oracle插入操作和MySQL不同
INSERT INT stu VALUES
(1,'XiaoWang','Henan','0371-12345678');
INSERT INTO stu VALUES
(2,'XiaoLi','Hebei','13889072345');
INSERT INTO stu VALUES
(3,'XiaoTian','Henan','0371-12345670');
1
2
3
4
5
6
7
8
9
10
11
12
13
create table sign(
	s_id	number(11)	primary key,
  s_name	varchar2(20)	not null,
  s_sch	varchar2(50)	not null,
  s_sign_sch	varchar(50)	not null
)

insert into sign values
(1,'XiaoWang','Middle School1','Peking University');
insert into sign values
(2,'XiaoLi','Middle School2','Tsinghua University');
insert into sign values
(3,'XiaoTian','Middle School3','Tsinghua University');
1
2
3
4
5
6
7
8
9
10
11
12
create table stu_mark(
	s_id number(11)	primary key,
  s_name varchar2(20)	not null,
  mark number(11) not null
);

insert into stu_mark values
(1,'XiaoWang',80);
insert into stu_mark values
(2,'XiaoLi',71);
insert into stu_mark values
(3,'XiaoaTian',70);
创建北京大学的学生视图
1
2
3
4
5
6
create view view_peking as
	select stu_mark.s_id,stu_mark.s_name,stu_mark.mark,sign.s_sign_sch
	from stu_mark,sign
	where sign.s_sign_sch = 'Peking University'
	and stu_mark.s_id = sign.s_id
	and stu_mark.mark>=41;
创建考上清华的学生视图
1
2
3
4
5
6
create view view_tsinghua as
	select stu_mark.s_id,stu_mark.s_name,stu_mark.mark,sign.s_sign_sch
	from stu_mark,sign
	where sign.s_sign_sch = 'Tsinghua University'
	and stu_mark.s_id = sign.s_id
	and stu_mark.mark>=40;
更正XiaoTian的成绩
1
2
update stu_mark set mark = mark-50 
where stu_mark.s_name = 'XiaoTian'

视图和表中的数据都同时更改了

总结

Oracle中视图和表的区别以及联系是什么？

区别：

  1. 视图是已经编译好的sql语句。而表不是
  2. 视图没有实际的物理记录。而表有。
  3. 表是内容，视图是窗口表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时四对它进行修改，但视图只能有创建的语句来修改
  4. 表是内模式，试图是外模式
  5. 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。
  6. 表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
  7. 视图的建立和删除只影响视图本身，不影响对应的基本表。

联系：

视图（view）是在基本表之上建立的表，它的结构（即所定义的列）和内容（即所有数据行）都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。

什么时候视图不能进行更新操作？
  1. 视图中不包含基表被定义为非空的列
  2. 在定义是图的SELECT语句后的字段列表中使用了数学表达式
  3. 在定义是图的SELECT语句后的字段列表中使用了集合函数
  4. 在定义视图的SELECT语句中使用了DISTINCT、UNION、TOP、GROUP BY、HAVING子句

插入、更新、删除数据

插入数据

1
2
3
4
5
6
7
8
# 一条记录，可指定字段
insert into person(age,name,id,info) values (22,'Suse',2,dancer)

# 多条记录插入时，使用多条的INSERT语句插入效率高

# 插入查询结果到表中
insert into person(id,name,age,info)
select id,name,age,info from person_old;

更新数据

1
2
update person set age = 15,name = 'LiMing' where id = 11;
update person set info='student' where age between 19 and 22;

删除数据

1
2
delete from person where id = 11
delete from person where age between 19 and 22;

综合实例

update
1
2
update books set price=price+5 where note = 'novel';
update books set price=40,note='drama' where name = 'EmmaT';
0%