Vsoapmac
文章
103
分类
17
评论
0

oracle数据库的基本应用

| 数据库 •
字数总计 19621 | 阅读时长 49分钟 | 阅读量 25

Oracle Database，又名Oracle RDBMS，或简称Oracle。是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说Oracle数据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小微机环境。它是一种高效率的、可靠性好的、适应高吞吐量的数据库方案。

表空间概念

永久表空间

存储数据库中需要永久化存储的对象，比如二维表、视图、存储过程、索引。

临时表空间

存储数据库的中间执行过程，如：保存order by数据库排序，分组时产生的临时数据。操作完成后存储的内容会被自动释放。临时表空间是通用的，所的用户都使用TEMP作为临时表空间。一般只有tmp一个临时表空间，如果还需要别的临时表空间时，可以自己创建。

undo表空间

保存数据修改前的副本。存储事务所修改的旧址，即被修改之前的数据。当我们对一张表中的数据进行修改的同时会对修改之前的信息进行保存，为了对数据执行回滚、恢复、撤销的操作。

Oracle在创建数据库与mysql创建数据库的区别

Mysql存在数据库的概念，一个项目对应一个单独的database，为数据库创建独立的用户。

Oracle一个数据库就是一个服务，存在table space概念（表空间），一个项目对应一个单独的表空间，创建一个用户，具有表空间使用的权限。

登录oracle

1、运行SQLPLUS工具

C:\Users\wd-pc>sqlplus

2、直接进入SQLPLUS命令提示符

C:\Users\wd-pc>sqlplus /nolog

3、以admin身份连接

C:\Users\wd-pc>sqlplus / as sysdba 或
​
SQL>connect / as sysdba

4、普通用户登录

C:\Users\wd-pc>sqlplus scott/123456　或
​
SQL>connect scott/123456 或
​
SQL>connect scott/123456@servername

5、以管理员登录

C:\Users\wd-pc>sqlplus sys/123456 as sysdba
​
SQL>connect sys/123456 as sysdba

6、切换用户

SQL>conn hr/123456
​
注：conn同connect

7、退出

exit

操作示例

1. 创建表空间

语法：

CREATE TABLESPACE 表空间名
DATAFILE '数据文件路径' SIZE 大小
[AUTOEXTEND ON] [NEXT 大小]
[MAXSIZE 大小];

说明：

[ ]里面内容可选项，数据文件路径中若包含目录需先创建：

  • SIZE 为初始表空间大小，单位为K或者M

  • AUTOEXTEND 是否自动扩展，值为ON或OFF

  • NEXT 为文件满了后扩展大小

  • MAXSIZE 为文件最大大小，值为数值或UNLIMITED(表示不限大小)

示例：

CREATE TABLESPACE ts_test
DATAFILE 'D:\oracle_tablespace\ts_test.dbf' SIZE 20M
AUTOEXTEND ON;

2. 查询表空间

示例：

--管理员角色查看表空间
SELECT file_name, tablespace_name, bytes, autoextensible
FROM dba_data_files
WHERE tablespace_name = 'ts_test';

3. 修改表空间

语法：

ALTER TABLESPACE 表空间名
ADD DATAFILE '文件路径' SIZE 大小
[AUTOEXTEND ON] [NEXT大小]
[MAXSIZE 大小];

示例：

ALTER TABLESPACE ts_test
ADD DATAFILE 'D:\oracle_tablespace\ts_test2.dbf 'SIZE 5M
AUTOEXTEND ON;

4. 删除表空间

语法：

--仅删除表空间
DROP TABLESPACE 表空间名;
--删除表空间及数据文件
DROP TABLESPACE 表空间名 INCLUDING CONTENTS AND DATAFILES;

示例：

--仅删除表空间
DROP TABLESPACE ts_test;
--删除表空间及数据文件
DROP TABLESPACE ts_test INCLUDING CONTENTS AND DATAFILES;

用户

用户是一个用于连接表空间的账号，oracle数据库里面的各个表需要指定的账号密码进入才能保证其身份的正确，每个用户都有它自己所属的权限，它们要被oracle管理员授予它们充当的角色才能继续使用，这些角色是oracle提供的三种标准角色（role），分为:connect/resource和dba.

  1. connect role(连接角色)

临时用户，特指不需要建表的用户，通常只赋予他们connect role.

connect是使用oracle简单权限，这种权限只对其他用户的表有访问权限，包括select/insert/update和delete等。 拥有connect role 的用户还能够创建表、视图、序列（sequence）、簇（cluster）、同义词(synonym)、回话（session）和其他 数据的链（link)。

  1. resource role(资源角色)

更可靠和正式的数据库用户可以授予resource role。

resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。

  1. dba role(数据库管理员角色)

dba role拥有所有的系统权限,包括无限制的空间限额和给其他用户授予各种权限的能力。

下面操作都在sys空间里面实现

创建用户

create user homework identified by jiaqi123456 default tablespace homework;

修改用户

alter user user1 identified by 234556;

撤销用户

alter user user1;

为用户授权

grant connect, resource to homework;

撤销对用户赋予的权限：

revoke connect, resource from user;

表空间总结

oracle与mysql的思维模式完全不同，mysql的数据库是一个对象，而oracle的数据库是一个集合，一个容器。mysql的数据库只需要在mysql的空间里面创建一个数据库即可，而oracle需要首先创建数据库实例(集装箱),在这个实例里面由管理员创建表空间(纸箱子),添加用户(锁)和密码(钥匙)并赋予权限，完毕后便可以使用表空间进行表的操作和使用了。整个结构很复杂工整，所以oracle是大型数据库。

整个创建表空间的实例：

create tablespace homework datafile 'D:\oracle\oradata\oracle\homework.dbf' size 10m;

create user homework identified by jiaqi123456 default tablespace homework;

grant connect, resource to homework;

表操作

新增表

注意:Oracle没有这个”auto_increment”属性，所以它没法像MySQL般在表内定义自增主键。

    create table user (
    　　id number(6) primary key,  ---主键
    　　name varchar(50) not null,  ---姓名 不为null
    　　sex varchar2(6) default '男' check ( sex in ('男','女'))  ---性别 默认'男'
    );

修改表

  1. 修改字段名

alter table user rename column course to newcourse;

  1. 修改字段

alter table user modify((age number(8));

  1. 删除字段

alter table user drop column course;

  1. 添加新字段

alter table user add(age number(6));

alter table user add (course varchar2(30) default '空' not null);

  1. 修改表名

rename user to newuser;

删除表

drop table 表名；

drop删除所有数据，会删除表结构。
truncate table 表名；

truncate是一次性删掉所有数据，不删除表结构。注意：如果表中有identity产生的自增id列，truncate后，会恢复初始值。
delete from 表名；

delete删除数据是一条一条的删除数据，后面可以添加where条件，不删除表结构。注意：如果表中有identity产生的自增id列，delete from后仍然从上次的数开始增加。

查看表

select table_name from user_tables;       //查看当前用户拥有的表
select table_name from all_tables;          //查看所有用户的表
select table_name from dba_tables;       //查看所有用户的表包括系统表，此命令应该在sys系统表上运行
直接输入,不需要做任何修改

说明：
user_tables：
 
table_name,tablespace_name,last_analyzed等
 
dba_tables：
 
ower,table_name,tablespace_name,last_analyzed等
 
all_tables：
 
ower,table_name,tablespace_name,last_analyzed等
 
all_objects：
 
ower,object_name,subobject_name,object_id,created,last_ddl_time,timestamp,status等

获取表字段

select * from user_tab_columns where Table_Name='TEST'; 
select * from all_tab_columns where Table_Name='TEST'; 
select * from dba_tab_columns where Table_Name='TEST'; 

此处的数据表名称应该大写

数据操作

插入数据

insert into 表名 values(所有列的值);

insert into TEST values(1,'zhangsan',20);

insert into 表名(列) values(对应的值);

insert into TEST(id,name) values(2,'lisi');

删除一行数据

delete from TEST where ID = '1';

列名大写

更改一行数据

update TEST set NAME = '李四' where ID = '1';

查看数据

select * from TEST;

select * from TEST where ID = '1';

视图

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

视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。

授予用户创建视图的权限

-- 创建视图权限，只授权第一个是无法创建 视图的，需要三个权限都授予
grant create view to breakfast;

-- 授予查询权限
grant select any table to breakfast;

-- 授予查询任何字典
grant select any dictionary to breakfast;

此代码需要在sys中运行

创建简单视图

create view vw_menu as select product_id,product_name,product_price,product_num from menu;

查看视图

select * from vw_menu where product_id = '1';

存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。

简单的说就是专门干一件事一段sql语句。

可以由数据库自己去调用，也可以由java程序去调用。

在oracle数据库中存储过程是procedure。

为什么要写存储过程

  1. 效率高

存储过程编译一次后，就会存到数据库，每次调用时都直接执行。而普通的sql语句我们要保存到其他地方（例如：记事本上），都要先分析编译才会执行。所以想对而言存储过程效率更高。

  1. 降低网络流量

存储过程编译好会放在数据库，我们在远程调用时，不会传输大量的字符串类型的sql语句。

  1. 复用性高

存储过程往往是针对一个特定的功能编写的，当再需要完成这个特定的功能时，可以再次调用该存储过程。

  1. 可维护性高

当功能要求发生小的变化时，修改之前的存储过程比较容易，花费精力少。

  1. 安全性高

完成某个特定功能的存储过程一般只有特定的用户可以使用，具有使用身份限制，更安全。

删除存储过程

drop procedure Proc_menu;

创建存储过程

存储过程类似一个方法,procedure 后面的是方法名，括号里面是形参，begin-end里面执行方法

create or replace procedure Proc_menu
(
--定义输入、输出参数--
prod_name in varchar,
prod_num in integer
)
begin   
    update menu set product_name = prod_name where product_id = prod_num;
end;

执行存储过程

无形参

exec Proc_menu;

begin
  Proc_menu;
end;

有形参

exec Proc_menu('番茄炒蛋');

begin
  Proc_menu('番茄炒蛋');
end;

触发器

触发器的定义就是说某个条件成立的时候，触发器里面所定义的语句就会被自动的执行。因此触发器不需要人为的去调用，也不能调用。

在触发器中有一个触发事件，触发器是通过这个“触发事件”来执行的（而存储过程的调用或执行是由用户或应用程序进行的）。能够引起触发器运行的操作被称为“触发事件”，如执行DML语句（使用INSERT、UPDATE、DELETE语句对表或视图执行数据处理操作）；执行DDL语句（CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）；引发数据库系统事件（如系统启动或退出、产生异常错误等）；引发用户事件（如登录或退出数据库操作）。

创建触发器

此代码仅供参考

create or replace trigger auth_goods_money after insert
on goods
for each row --行级触发器
begin
    update goods set goods.sum_money = 
    (select menu.product_price from menu where menu.product_id = goods.product_id) where goods.product_id = :new.product_id;
END;

行级触发器：执行(after)之后的触发器，带有for each row,加for each row是为了使用 :old 对象或者 :new 对象（一行记录），其含义如下表。

触发语句

:old

:new

insert

所有字段都是空（null）

将要插入的数据

update

更新以前该行的值

更新后的值

delete

删除以前该行的值

所有字段都为空（null）

语句级触发器：不包含有for each row的触发器

删除触发器

DROP TRIGGER auth_goods_money;
文章作者： Vsoapmac
本文链接：
版权声明： 本站所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 soap的会员制餐厅！
数据库 关系型数据库
喜欢就支持一下吧