DM数据库基本操作

小蜗牛_

于 2024-09-23 12:36:05 发布

阅读量656

点赞数 30

文章标签：数据库 sql

本文链接：https://blog.csdn.net/qq_40268666/article/details/142259460

版权

一、配置数据库服务自启动

为了能够方便连接虚拟机上的数据库，先配置虚拟机的数据库自启动：

重启虚拟机后，查看数据库服务状态

二、Manager连接数据库

将Linux系统内的防火墙关闭，并打开数据库服务

在tool目录下，打开数据库Manager工具（windows或linux环境均可）

在Manager工具“对象导航”中点击“新建连接”，输入虚拟机IP、用户名、口令，即可连接。

三、数据库基础操作

计划分别使用TEST1、TEST2实操图形化界面和命令行逐步完成基础操作。

3.1创建表空间

3.1.1新建表空间

1、图形化

定义表空间名，并添加数据文件，直接在“文件路径”项中输入对应的数据文件名或者存放路径即可。（若文件路径只填写文件名，则存放路径默认为所在数据库实例的路径。）

亦可设置加密算法和加密密码（图形化只可在创建时设置）

注：添加的数据文件大小最小为 4096*页大小，如页大小为 8K，则可添加的文件最小值为 40968k=32M。实操中文件最小为 128 MB，对应页大小为 32 KB。

2、命令行

使用命令创建表空间 TEST2，指定数据库文件为 'TEST2.DBF'，初始大小为 128M，其它默认

CREATE TABLESPACE "TEST2" DATAFILE 'TEST2.DBF' SIZE 128;

更新下表空间即可看到新创建的表空间。

3.1.2修改表空间参数

根据项目需要，修改表空间文件、路径、大小、是否自动扩充、扩充上限等信息。打开自动扩展（自动扩展默认为打开状态，每次自动扩展大小和扩展上限请根据实际业务需求设置），每次自动扩展 100M ，扩展上限 10240M。

1、图形化

2、命令行

ALTER TABLESPACE "TEST2" DATAFILE 'TEST2.DBF' AUTOEXTEND ON NEXT 100 MAXSIZE 10240;

3.1.3设置加密算法、密码

可以根据项目需要，在新建表空间时，设置加密算法、加密密码等信息，也可设置为空。（仅首次创建时可以设置，创建后无法修改添加）

1、图形化

2、命令行

CREATE TABLESPACE "TEST2" DATAFILE 'TEST2.DBF' SIZE 128 CACHE = NORMAL ENCRYPT WITH DES_ECB_NOPAD BY "123456";

ENCRYPT WITH DES_ECB_NOPAD:表示采用的加密算法为DES_ECB_NOPAD BY"123456":表示加密密码为123456。

3.1.4删除表空间

注：删除表空间操作不可逆

1、图形化

2、命令行

DROP TABLESPACE TEST1;

3.2创建用户

3.2.1创建用户并关联表空间

1、图形化

输入用户名，输入密码（密码默认长度不少于9位），配置表空间和索引表空间，点击【确认】即可。

2、命令行

CREATE USER "TEST2" IDENTIFIED BY "TEST2345678" HASH WITH SHA512`
ENCRYPT BY "123456"
DEFAULT TABLESPACE TEST2
DEFAULT INDEX TABLESPACE TEST2;

grant "PUBLIC","SOI" to "TEST2";

创建用户 TEST2 ，密码 “TEST2345678”，使用散列算法 SHA512 ，使用存储加密密钥为 “123456”，指定表空间为 TEST2，索引表空间为 TEST2，授予 “PUBLIC” 和 “SOI” 权限。

3.2.2授予权限

1、图形化

在用户“所属角色”中，授予用户 DBA 权限、RESOURCE 权限。

2、命令行

grant "DBA","RESOURCE" to "TEST2";

3.2.3用户与模式的关系

每个用户有一个默认的同名的模式，访问自己模式下的表、视图等，不需要加模式名，访问其他模式下的对象需要拥有访问权限，访问时还需加上模式名。

1、查询当前用户模式下的表，无需加对象名

以 TEST 用户为例，当查询自己模式下的表时，可以直接进行查询，无需加对象名，如下图所示：

Copyselect * from test；

右键表空间模块，点击新建表空间

2、查询其他用户模式下的表，需要加对象名

以 TEST 用户为例，查询 DBTEST 用户模式下的表 TABLE_TEST，如不加对象名，则查询会报错“无效的表名或者视图名[TABLE_TEST]”，如下图所示：

Copyselect * from TABLE_TEST;

加上对象名后，没有授予查询权限，则查询会报错“没有对象[DBTEST.TABLE_TEST]的查询权限”，如下图所示：

Copyselect * from DBTEST.TABLE_TEST;

这时需要授予 TEST 用户查询表 TABLE_TEST 的权限，如下图所示：

--使用 root 用户授予 TEST 用户查询表 TABLE_TEST 的权限
grant SELECT on "DBTEST"."TABLE_TEST" to "TEST";
--使用 TEST 用户登录数据库查询表 TABLE_TEST
select * from DBTEST.TABLE_TEST;

3.3创建数据库对象

3.3.1创建表

-- 创建表CITY，并插入数据
CREATE TABLE TEST1.city
(
 city_id CHAR(3) NOT NULL,
 city_name VARCHAR(40) NULL,
 region_id INT NULL
);

INSERT INTO TEST1.city(city_id,city_name,region_id) VALUES('BJ','北京',1);
INSERT INTO TEST1.city(city_id,city_name,region_id) VALUES('SJZ','石家庄',1);
INSERT INTO TEST1.city(city_id,city_name,region_id) VALUES('SH','上海',2);
INSERT INTO TEST1.city(city_id,city_name,region_id) VALUES('NJ','南京',2);
INSERT INTO TEST1.city(city_id,city_name,region_id) VALUES('GZ','广州',3);
INSERT INTO TEST1.city(city_id,city_name,region_id) VALUES('HK','海口',3);
INSERT INTO TEST1.city(city_id,city_name,region_id) VALUES('WH','武汉',4);
INSERT INTO TEST1.city(city_id,city_name,region_id) VALUES('CS','长沙',4);
INSERT INTO TEST1.city(city_id,city_name,region_id) VALUES('SY','沈阳',5);
INSERT INTO TEST1.city(city_id,city_name,region_id) VALUES('XA','西安',6);
INSERT INTO TEST1.city(city_id,city_name,region_id) VALUES('CD','成都',7);


-- 查询插入数据结果
SELECT * FROM TEST1.CITY;

3.3.2创建视图

-- 创建一个视图，保存region_id小于4的数据
CREATE VIEW TEST1.v_city AS
SELECT
    city_id,
    city_name,
    region_id
FROM
    TEST1.CITY
WHERE
    REGION_ID < 4;

--查询视图数据结果
SELECT * FROM TEST1.V_CITY;

3.3.3.创建存储过程

-- 创建存储过程。入参数据类型为 INT，变量 B 赋予初始值 10，输出变量 A 的值为输入的变量 A 值与变量 B 值之和。
CREATE PROCEDURE TEST1.proc_1
    (a IN OUT INT)
AS
    b INT:=10;
BEGIN
    a:=a+b;
    PRINT'TEST1.PROC_1调用结果：'||a；
END;

-- 调用存储结果
CALL TEST1.proc_1(3);

3.3.4创建函数

-- 创建函数。该函数的返回类型为 INT 类型。两个参数 A、B 的和赋给了变量 S，RETURN 语句则将变量 S 的值作为函数的返回值返回。
CREATE 
    FUNCTION TEST1.fun_1(a INT,b INT)
        RETURN INT
    AS
        s INT;
    BEGIN
        s:=a+b;
        RETURN s;
    END;

-- 调用函数
SELECT TEST1.fun_1(4,5);

3.3.5创建序列

-- 创建序列。序列起始值为 5，增量值为 2，最大值为 200。
CREATE SEQUENCE TEST1.seq_quantity START WITH 5 INCREMENT BY 2 MAXVALUE 200;


-- 查询序列下一个值
SELECT TEST1.seq_quantity.nextval FROM DUAL;

这里解释下，首次执行获取下一个值nextval，结果是5，是因为如果第一次对序列使用该函数，则返回序列当前值；

再执行一次，就能如愿获取下一个值nextval。

3.3.6创建触发器

-- 创建触发器。当对表 CITY 进行更新操作时，定义的触发器 TRG_UPD 将被激发。
CREATE TRIGGER TEST1.trg_upd AFTER
    UPDATE on TEST1.city
    FOR EACH ROW
        BEGIN
        PRINT'Update Operation on city!!！';
        END;

-- 对CITY表数据进行更新操作，测试触发器的结果
UPDATE TEST1.CITY SET CITY.REGION_ID=8 WHERE CITY.CITY_ID='XA';