达梦数据库DCA学习笔记

卸载和安装

麒麟系统修改分辨率：
:::info
xrandr -s 1920x1440
:::
启用图形界面
:::info
xhost +
如果命令报错 ： **unable to open display **需要编辑bash_profile
vi ~/.bash_profile
################
export DISPLAY=:0.0
################
source ~/.bash_profile
:::

卸载Dm8

:::info
停止数据库服务
systemctl stop DmServiceDMSERVER.service
跳转到dm8根目录下执行卸载脚本
./uninstall.sh -i
清空目录
rm -fr *
删除用户和组
userdel dmdba
groupdel dinstall
:::

安装DM8

:::info
关闭防火墙
systemctl stop firewalld
关闭selinux
vim /etc/selinux/config
将 SELINUX=enforcing 改为 SELINUX=disabled
setenforce 0
创建达梦数据库用户和用户组
groupadd dinstall
useradd -g dinstall dmdba
passwd dmdba
“输入密码”
:::
设置用户可打开最大文件数量
:::info
vi /etc/security/limits.conf

dmdba soft nofile 65536
dmdba hard nofile 65536

:::
创建安装目录,并修改权限
:::info
mkdir -p /dm8
chonw -R dmdba.dinstall /dm8
:::
挂载安装介质
:::info
cd /stage
mount -o loop dm8_20201107_x86_rh7_64_ent_8.1.1.144.iso /mnt
:::
登录root用户，启用图形化界面
:::info
su - root
xhost +
su - dmdba
:::
编辑bash_profile
:::info
export DISPLAY=:0.0
export LD_LIBRARY_PATH="$L{D}_{L}IBRAR{Y}_{P}ATH:/dm8/bin"exportD{M}_{H}OME="/dm8"exportPATH=$PATH:$D{M}_{H}OME/bin:$DM_HOME/tool
:::
调用安装gui界面
:::info
cd /mnt
./DMInstall.bin
(如果使用命令行安装，要在后面加一个 -i )
:::
安装完成后切换到root执行脚本
:::info
./dm8/script/root/root_installer.sh
:::

安装完成后GUI界面会自动弹出创建数据库界面， 默认现在初始化数据库，点击初始化， 或者在dmdba用户下， 运行dbca.sh.

验证数据库创建成功
:::info
查看后台进程
ps -ef |grep dmserver
登录数据库
disql sysdba/密码
:::
:::info
dbca.sh 达梦数据库配置助手
analyzer 审计分析
console
dts
manager 数据库管理
monitor

:::
使用命令行创建达梦数据库

$DM_HOME/bin/dminit path=/dm8/data DB_NAME=dameng02 instance_name=TEST port_num=5238
su - root
cd /dm8/script/root/
./dm_service_installer.sh -t dmserver -p TEST -dm_ini /dm8/data/dameng02/dm.ini
systemctl start DmServiceTEST
su - dmdba
disql sysdba/SYSDBA:5238  (SYSDBA是默认密码)

DMSQL 查询语句

DML(Data Mannipulation Language)数据操纵语言：
查询、操纵数据表资料行
DDL(Data Definition Language)数据定义语言：
建立、修改、删除数据库中数据表对象
DCL(Data Control Language)数据控制语言：
用于执行权限授予与收回操作
TCL(Transactional Control Language)事物控制语言：
维护数据的一致性

简单查询

--选择全列
select * from dmhr.department;

--使用数学运算
select employee_name,salary,salary+1000 from dmhr.employee;

--使用列别名
select employee_name,salary,salary+1000 as "TOL" from dmhr.employee;

--去重
select distinct department_id from dmhr.employee;

--使用连接符
select department_id,employee_name||'工资是：'||salary from dmhr.employee;

--使用比较运算
select employee_id,employee_name,salary from dmhr.employee where salary >=25000 and salary <=30000;

--与值列表的任一值匹配
select employee_id,employee_name,salary from dmhr.employee where salary in (15000,30000);

--选择类似值(找出所有姓马的人)
select * from dmhr.employee where employee.employee_name like '马%';

--空值判断
select employee_id,employee_name,department_id from dmhr.employee where department_id is not null;

--逻辑运算
select employee_name,salary from dmhr.employee where (employee_id=1001 or employee_id=1002) and salary >20000;

--排序  asc从小到大 desc从大到小
select employee_id,employee_name,salary from dmhr.employee order by salary desc,employee_id asc;

单行函数

--大小写处理函数
LOWER('SQL Course')
	-> sql course
UPPER('SQL Course')
	-> SQL COURSE
INITCAP('SQL Course')
	-> Sql Course

--字符处理函数
CONCAT('Hello','World')
 -> HelloWorld
SUBSTR('HelloWorld')
 -> Hello
LENGTH('HelloWorld')
 -> 10
INSTR('HelloWorld','W')
 -> 6
LPAD(salary,10,'*')
 -> *****24000
RPAD(salary,10,'*')
 -> 24000*****
TRIM('H' FROM 'HelloWorld')
 -> elloWorld

 --数字函数
ROUND(49.36,1)
 -> 49.4 --数值舍入到指定小数位
TRUNC(49.36,1)
 -> 49.3 --截取数值到指定小数位
MOD(10,3)
 -> 1 --取余

分组函数

--avg（）取平均值
select employee_name from dmhr.employee where salary > (select avg(salary) from dmhr.employee); --列出工资大于平均数的人

--having()  WHERE 关键字无法与聚合函数一起使用。HAVING 子句可以让我们筛选分组后的各组数据。
select employee_name,sum(salary) from dmhr.employee 
where job_id=32 group by employee_name,salary 
having sum(salary) > 50000; --找出总工资大于5万的项目经理

--cube() 列出所有组合
select job_id,job_title from dmhr.job group by CUBE(job_id,job_title);

--rollup() 聚合函数
select job_id,SUM(salary) from dmhr.employee 
group by ROLLUP(job_id); --统计每个职位的工资支出和公司总支出

--grouping（） 对是否为分组聚合数据进行标注
select job_id,SUM(salary)，GROUPING(job_id) grp_job_id from dmhr.employee group by ROLLUP(job_id);

--grouping set() 指定聚合分类
select job_id,department_id,SUM(salary) from dmhr.employee
group by GROUPING SET((job_id),(department_id),()); --分别查询各岗位工资支出，各科室工资支出和总工资支出

连接查询

--内连接 INNER JOIN ON  将多个表的数据组合起来
select 
employee.employee_id,employee.employee_name,employee.salary,job.job_title,department.department_name,location.street_address 
from dmhr.employee
inner join dmhr.job on employee.job_id = job.job_id
inner join dmhr.department on employee.department_id = department.department_id
inner join dmhr.location on department.location_id = location.location_id
order by job.job_id; --查询每个人的工资并显示其职位，科室，工作地点

--交叉连接cross join 两个表生成笛卡尔积
--若两个表都有10条数据，那么cross join之后的结果有 10*10=100条

--natural join 自然连接 匹配两个表中的相同元素，并组合
select employee.employee_id,employee.employee_name,employee.salary,job.job_title
from dmhr.employee 
natural join dmhr.job;

--外连接
--左外连接 left join on：返回左表中的所有行，如果左表中行在右表中没有匹配行，则结果中右表中的列返回空值。

--右外连接 right join on：与左连接相反，返回右表中的所有行，如果右表中行在左表中没有匹配行，则结果中左表中的列返回空值。

--全连接 full join on: 返回左表和右表中的所有行。当某行在另一表中没有匹配行，则另一表中的列返回空值。

子查询

--单行子查询
select employee_name from dmhr.employee where department_id = (select department_id from dmhr.employee where employee_name = '马学铭'); --查询马学铭科室的同事

--多行子查询
-- IN 等值列表中任意一个
-- ANY 将值与子查询返回的任意一个值进行比较
-- ALL 将值与子查询返回的每个值进行比较
select employee_name,salary from dmhr.employee where salary > all(select salary from dmhr.employee where department_id=108);

--EXISTS 
--EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。
--EXISTS 可以与 NOT 一同使用，查找出不符合查询语句的记录
SELECT EMPLOYEE_ID ,EMPLOYEE_NAME FROM DMHR.EMPLOYEE WHERE EXISTS( SELECT * FROM DMHR.JOB_HISTORY WHERE EMPLOYEE.EMPLOYEE_ID = JOB_HISTORY.EMPLOYEE_ID );

DML语句

--INSERT 插入
INSERT INTO DMHR.EMPLOYEE VALUES(11146,'吴健','210102196202303000','wujian@dameng.om','1531248223','2016-05-30','11',30000.00,0,1001,101);

--UPDATE 修改
update dmhr.employee set salary=20000 where employee_name='马学铭';
--用子查询更新
update dmhr.employee set salary=(select salary from dmhr.employee where employee_name='金纬') where employee_name='马学铭';

--DELETTE 删除
--删除一行
delete from dmhr.employee where employee_name='马学铭';
--清空表数据
delete from dmhr.job_history;

--MERGE合并 按照指定的条件执行插入或更新操作，按照指定的条件执行插入或更新操作
把T1表中C1值为2的记录行中的C2列，更新为表T2中C3值为2的记录中C4列的值，同时把T2中C3列为4的记录行插入到了T1中
MERGE INTO T1 USING T2 ON (T1.C1=T2.C3) 
WHEN MATCHED THEN UPDATE SET T1.C2=T2.C4 
WHEN NOT MATCHED THEN INSERT (C1,C2) VALUES(T2.C3,T2.C4)；

表空间管理

达梦数据库没有database的概念，表空间tablespace是数据存放的位置，模式schema是给用户指定的工作范围，表table是存在模式下的数据结构。
表∈模式∈表空间
一个用户可以拥有多个表空间

查询数据库中的表空间

SELECT * FROM SYS.DBA_TABLESPACES;

查询模式及表

SELECT OWNER, OBJECT_NAME FROM SYS.DBA_OBJECTS;
OWNER是模式
OBJECT_NAME是表

创建表空间

create tablespace "ZZY" datafile '/dm8/data/DAMENG/ZZY.dbf' size 32 autoextend on next 1 maxsize 1000 CACHE=NOMAL

create tablespace "ZZY"  --创建名字为“ZZY”表空间
datafile '/dm8/data/....' --指定表空间文件地址
size 32 --表空间初始大小，单位MB
autoextend on --开启自动扩容
next 1 --每次扩容大小，MB
maxsize 1000 --扩容上限
CACHE=NORMAL --缓存模式

查看表空间

select * from sys."v$tablespace";

修改表空间状态

alter tablespace zzy offline; --关闭表空间 status = 1
alter tablespace zzy online; --打卡表空间 status = 0

删除表空间

drop tablespace zzy;

扩展表空间

alter tablespace "ZZY" add datafile '/dm8/data/DAMENG/ZZY02.dbf' size 32;

更换数据文件存储位置

alter tablespace DMHR offline; --先关闭表空间
alter tablespace "DMHR" rename datafile 'DMHR.dbf' to '/dm8/data/dmhr_new.dbf';
alter tablespace DMHR online;

调整重做日志文件大小

安装默认的重做日志文件大小为256M,如果OLTP系统中,重做日志文件过小,会频繁的做日志切换,影响数据库的性能,建议在安装时,根据实际需求扩大日志文件的大小和增加日志文件

alter database resize logfile 'DAMENG01.log' to 500;
alter database resize logfile 'DAMENG02.log' to 500;

增加重做日志文件

alter database add logfile '/dm8/data/DAMENG03.LOG' size 500;

用户管理

预定义用户：
SYS , SYSDBA , SYSAUDITOR , SYSSO , SYSDBO

创建用户

create user TEST indentified by  "dameng123";
--创建用户TEST 密码 dameng123

修改用户

alter user TEST indentified by "Dameng123";

删除用户

drop user TEST;

授予系统权限

:::info
CREATE TABLE 创建表
ALTER DATABASE 修改数据库
CREATE TABLESPACE 创建表空间
ALTER TABLESPACE 修改表空间
DROP TABLESPACE 删除表空间
CREATE USER 创建用户
CREATE VIEW 创建视图
CREATE PROCEDURE 创建存储过程/函数
CREATE ROLE 创建角色
CREATE SCHEMA 创建模式
:::

grant CREATE TABLE to TEST;

撤销系统权限

revoke CREATE TABLE from TEST；

授予对象权限

grant SELECT on dmhr.city to TEST;

撤销对象权限

revoke SELECT on dmhr.city from TEST;

角色
角色是一组权限的组合，使用角色的目的是使
权限管理更加方便

创建角色

--创建角色R1
create role R1;
--赋予R1权限
grant CREATE TABLE to R1;
--指定角色给用户TEST
grant R1 to TEST;

启用和禁用角色

--禁用
SP_SET_ROLE('R1',0);
--启用
SP_SET_ROLE('R1',1);

删除角色

drop role R1；

模式管理

模式介绍

· 模式定义
模式是一个特定的对象集合，在概念上可将其看作是包含表、视图、索引等若干对象的对象集
· 模式对象:
表、视图、约束、索引、序列、触发器、存储过程/函数、包、同义词、类、域

模式和用户的关系

当系统建立一个用户时，会自动生成一个对应的模式用户还可以建立其他模式

创建模式

create schema DaMeng authorization SYSDBA;
--创建模式并指定拥有者

设置当前模式

set schema DaMeng;
--SYSDBA用户将当前的模式从SYSDBA换到DAMENG模式

删除模式

drop schema DaMeng；

表约束和管理

创建表

create table dmhr.test (ID int,NAME char(1));

给表增加一列

alter table dmhr.test add column (AGE int);

删除列

alter table dmhr.test drop column AGE;

删除表

drop table dmhr.test;

常见约束类型
:::info
–非空约束
NOT NULL
–唯一性约束
UNIQUE
–主键约束
PRIMARY KEY
–外键约束
FOREIGN KEY
–检查约束
CHECK
:::

增加约束

alter table dmhr.test add constraint TEST_UNI unique（ID）；

禁用和启用约束

--禁用约束
alter table dmhr.test disable constraint TEST_UNI;
--启用约束
alter table dmhr.test enable constraint TEST_UNI;

视图

:::info
• 从用户角度来看，一个视图是从一个特定的角度来查看数据库中的数据
• 从数据库系统内部来看，一个视图是由SELECT语句组成的查询定义的虚拟表
:::

创建视图

create view dmhr.personinfo as select dmhr.department.department_name,dmhr.employee_name from dmhr.department,dmhr.employee where dmhr.employee.department_id = dmhr.department.department_id;

修改视图

create or replace view dmhr.view1 as select `````;

删除视图

drop view dmhr.view1;

创建索引

create index job_ind on dmhr.job(job_id) tablespace dmhr;

重建索引

alter index dmhr.job_ind rebuild;

删除索引

drop index dmhr.job_ind;

数据库备份与还原

连接ODBC

配置 odbcinst.ini 文件

[dm8]
Description	= dm8
Driver 			= /dm8/drivers/odbc/libdodbc.so

配置 odbc.ini 文件

[dm8]
Description	= dm8 dsn
Driver			= dm8  
SERVER			= localhost # sql服务器地址 #
UID					= SYSDBA # 账号 #
PID					= zzy930821 # 密码 #
TCP_PORT		= 5236 # 端口 #

连接 odbc

isql dm8