达梦数据库(DM8)基本使用

安装之前：请检查系统配置，使用ulimit -a命令查看参数（主要注意参数如下）设置文件打开数 open files（临时方法，仅针对会话生效 ulimit -n）：[root@localhost opt]# ulimit -n 102400[root@localhost opt]# ulimit -n102400设置文件打开数（修改配置文件，永久生效）：[root@localhost opt]#

ZoraSourit

2600人浏览 · 2022-05-06 16:51:47

ZoraSourit · 2022-05-06 16:51:47 发布

安装之前：请检查系统配置，使用ulimit -a命令查看参数（主要注意参数如下）

设置文件打开数 open files（临时方法，仅针对会话生效 ulimit -n）：
[root@localhost opt]# ulimit -n 102400
[root@localhost opt]# ulimit -n
102400
设置文件打开数（修改配置文件，永久生效）：
[root@localhost opt]# vim /etc/security/limits.conf
增加
dmdba soft nofile 10240
dmdba hard nofile 65536

安装用户组和安装用户（数据库推荐由一个专门dba用户统一管理，而不是root）
```
[root@localhost opt]# groupadd dinstall
[root@localhost opt]# useradd -g dinstall dmdba
```

创建安装目录及安装操作

[root@localhost opt]# mkdir /dm8                  #用于安装数据库的目录
[root@localhost /]# chown dmdba:dinstall /dm8     #切换属主属组
[root@localhost /]# chmod 755 /dm8                #更改目录权限
挂载 iso 镜像文件：
[root@localhost /]#mkdir /mnt/dm
[root@localhost /]# mount /opt/dm8_20210203_x86_rh6_64_ent_8.1.1.172.iso /mnt/dm
mount: /dev/loop0 写保护，将以只读方式挂载
[root@localhost /]# su - dmdba
[dmdba@localhost ~]$ cd /mnt/dm
[dmdba@localhost dm]$ ./DMInstall.bin             #执行可视化安装

唤醒图形化界面修改方式

出现上述错误是图形化界面参数未设置好。使用下列方法设置图形化界面：
图形化界面设置（root 执行 xhost +，并查看 DISPLAY 变量）：
[root@localhost 桌面]# xhost +
access control disabled, clients can connect from any host
[root@localhost 桌面]# echo $DISPLAY
:0
[root@localhost 桌面]#
设置 dmdba 用户的 DISPLAY（仅对当前会话生效）
[dmdba@localhost dm]$ echo $DISPLAY
[dmdba@localhost dm]$ export DISPLAY=:0
[dmdba@localhost dm]$ ./DMInstall.bin

必须使用root执行!

创建数据库及数据库示例管理（./dbca.sh）使用root命令执行

[root@KylinDCA03 桌面]# /dm8/script/root/root_installer.sh
移动 /dm8/bin/dm_svc.conf 到/etc 目录
修改服务器权限
创建 DmAPService 服务
Created symlink /etc/systemd/system/multi-user.target.wants/DmAPService.service →
/usr/lib/systemd/system/DmAPService.service.
创建服务(DmAPService)完成
启动 DmAPService 服务
安装完成后主要使用的工具汇总：（主要在/dm8/tool的路径下）
./manager启动DM管理工具
./dbca.sh 启动DM数据库配置助手  初始化
./dmservice.sh 启动DM服务查看器root才能修改   
./console 控制台工具 备份还原
./nca.sh 网络服务配置
./dts 数据迁移工具
[root@KylinDCA03 network-scripts]# mv /dm8/bin/DmServiceDMSERVER.service 
/usr/lib/systemd/system/DmServiceDMSERVER.service
[root@KylinDCA03 network-scripts]# systemctl enable DmServiceDMSERVER.service
Created symlink /etc/systemd/system/multi-user.target.wants/DmServiceDMSERVER.service →/usr/lib/systemd/system/DmServiceDMSERVER.service.
[root@KylinDCA03 network-scripts]# systemctl start DmServiceDMSERVER.service

表空间管理

示例：

#创建表空间，默认自动扩展了
 Create tablespace tbsprod DATAFILE '/dm8/data/DMTEST3/tbs/tbsprod01.dbf' size 32;  
  
#修改表空间的名字
 alter tablespace TBSPROD rename to TBSPRODTEST; 
     
#更改表空间文件大小
 alter tablespace "TBSPROD" resize datafile '/dm8/data/DMTEST3/tbs/tbsprod02.dbf' to 64;
 alter TABLESPACE TBSPROD ADD DATAFILE '/dm8/data/DMTEST3/tbs/tbsprod02.dbf'size 32;  

#添加数据文件
 alter tablespace "TBSPROD" offline;
 alter tablespace "TBSPROD" rename datafile '/dm8/data/DMTEST3/tbs/tbsprod01.dbf' to  '/dm8/data/DMTEST3/tbsprod01.dbf';
 alter tablespace "TBSPROD" online;
#修改表空间数据文件的位置
alter TABLESPACE TBSPROD DATAFILE '/dm8/data/DMTEST3/tbs/tbsprod02.dbf' AUTOEXTEND off;   #修改为不自动扩展
alter TABLESPACE TBSPROD DATAFILE '/dm8/data/DMTEST3/tbs/tbsprod02.dbf' AUTOEXTEND on NEXT 2 maxsize 1024;   #开启自动扩展，指定范围
select * from DBA_DATA_FILES;      #查看表空间的数据文件
select * from v$tablespace;        #查看存在的表空间
select * from v$huge_tablespace;   #查看大表空间

select * from dba_tablespaces;
select * from DBA_DATA_FILES;
select * from v$tablespace;
select * from v$datafile;
select * from DBA_FREE_SPACE; --数据文件剩余空间

开启/关闭归档

SQL> alter database mount;
SQL> alter database archivelog;
SQL> select arch_mode from v$database;
行号 ARCH_MODE
---------- ---------
1 Y
SQL> alter database add archivelog 'type=local,dest=/dm8/arch,file_size=64,space_limit=10240';
SQL> alter database open;
SQL> select * from v$dm_arch_ini;
---------------------------------------------------------
SQL> alter database mount;.
SQL> alter database noarchivelog;
SQL> alter database delete archivelog 'type=local,dest=/dm8/arch';.
SQL> alter database open;
SQL> select arch_mode from v$database;
行号 ARCH_MODE
---------- ---------
1 N
SQL> select * from v$dm_arch_ini;

用户管理

示例

#创建用户
  create user "USERTEST" identified by "dameng123" default tablespace "TBSPROD";
  Alter user USERTEST identified by .... 修改用户密码 
  alter user USERTEST ACCOUNT lock(unlock);  用户锁定解锁
  alter user USERTEST DEFAULT TABLESPACE dmtbs; 修改用户的默认表空间
  select b.USERNAME,a.* from sysusers a, dba_users b where a.id = b.USER_ID; 查看用户详细
  SELECT GRANTEE,PRIVILEGE,TABLE_NAME FROM DBA_TAB_PRIVS WHERE GRANTEE='aaa';查看用户权限信息
  REVOKE CREATE TABLE FROM TEST; revoke VTI FROM aaa; 撤销权限

  DROP USER if EXISTS USERTEST;       删除用户  
#创建角色
     CREATE ROLE ROLE1;
     GRANT CREATE TABLE TO ROLE1; 
     GRANT UPDATE ON SYSDBA.EMP TO ROLE1;  给角色分配权限
     GRANT ROLE1 to USERTEST;
赋予对象权限增加 with grant option 说明权限可以转授，回收时要增加 cascade 关键字级联回收权限。
     revoke select on dmhr.employee from r2 CASCADE;

模式对象管理：

DM 中一个用户可以拥有多个模式，一个模式只能归属于一个用户；Oracle 用户和模式是一对一，DM 可以一对多；

创建用户，默认会创建一个同名的模式，也可以单独创建模式。默认情况下，用户查询同名模式下的对象。

创建表：
create table t_usertest(
userid int,
username varchar(20) )tablespace tbs;
修改字段：
alter table t_usertest modify username not null;
添加字段：
alter table t_usertest add phonenumber varchar(30) unique ;
alter table t_usertest add sex bit;
alter table t_usertest add email varchar(20);
删除字段：
alter table t_usertest drop column email;
外键(引用的是另一张表的主键)：
create table t_emp
(emp_id int constraint pk_emp_empid primary key,
emp_name varchar(20),
dept_id int );

备份还原

BAK_PATH 为数据库默认备份目录，生产环境建议修改此目录，避免磁盘损坏，备份文件

和源库数据文件同时损坏的情况。

（./dmservice.sh root 打开！！！）

保证DmAPService开启，需备份还原的数据库服务关闭！

（./console tool目录下 dmdba用户打开）

DQL学习：

非结构化的数据 —— 没有特点不便于检索
半结构化的数据 —— 数据之间有相同的存储结构、每一条数据的属性可以不同
结构化数据
创建在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据
数据之间有着相同的存储结构
同一个数据模型中每一条数据的属性都是相同的，但是值可能有不同，这些数据非常便于存储和管理
于是针对这些数据，专门设计一套数据存储的软件

数据分组

按照某一个条件进行分组，每一组返回对应的结果
group by可以对指定的列进行分组，列尽量有相同的
having可以对分组之后的数据进行过滤，所以能出现在having中的比较项一定是被分组的列或者组函数
底层
where 称之为行级过滤，处理的是表中每一行数据的过滤
having称之为组级过滤，处理的是分组之后的每一组数据
能够使用where的，尽量不要使用having
举例:
 查询每个部分的工资平均值并且不算工资超过3000   
SELECT deptno,avg(sal) from emp WHERE 'sal' < 3000 GROUP BY deptno
查工资平均值大于2200的部分，平均值不包含工资小于2000的
SELECT deptno,avg(sal) from emp WHERE 'sal' < 2000 GROUP BY deptno HAVING AVG(sal) > 2200
查询部门每个月津贴超过1000
SELECT deptno,SUM(IFNULL(comm,0))  from  emp GROUP BY deptno HAVING sum(comm) > 1000 
查询20和30部分的平均薪资
select deptno,avg(sal) from emp where deptno in (20,30) GROUP BY deptno;推荐
select deptno,avg(sal) from emp GROUP BY deptno HAVING deptno in (20,30);不推荐
查询带有字母A的员工平均薪资
select ename, avg(sal)  from emp WHERE  ename LIKE '%A%' GROUP BY ename
查询10/20部门中，并且在五月份入职员工中，每个部门中平均薪资高于1500的工作是什么，并按照部门平均工资进行排序
SELECT
	deptno,
	job,
	ename,
	avg( sal ) 
FROM
	emp 
WHERE
	deptno IN ( 20, 30 ) 
	AND EXTRACT( MONTH FROM hiredate ) = 5 
GROUP BY
	deptno,
	job ,
	ename
HAVING
	avg( sal ) > 1500

单行函数练习

找出各月倒数第三天受雇的所有员工
select * from emp where (LAST_DAY(hiredate)-hiredate)=2
找出早于12年前受雇的员工
select * from emp where (EXTRACT(YEAR FROM SYSDATE()) - EXTRACT(YEAR FROM hiredate)) > 12
以首字母大写的方式显示所有员工的姓名
select CONCAT(SUBSTR(ename,1,1),LOWER(SUBSTR(ename,2,5)))from emp
显示正好为5个字符的员工姓名
select ename from emp where LENGTH(ename) =5
显示不带有"R"的员工姓名
select ename from emp where ename not like '%[rR]'
显示所有员工姓名的前三个字符
SELECT SUBSTR(ename,1,3) FROM emp
显示所有员工姓名的后三个字符
SELECT SUBSTR(ename,-3,3) FROM emp
显示所有员工的姓名，用a替换所有"A"
SELECT REPLACE(ename,'A','a') FROM emp
显示满10年服务年限的员工姓名和受雇日期
select ename,hiredate FROM emp WHERE (EXTRACT(YEAR FROM SYSDATE())-EXTRACT(YEAR FROM hiredate)) > 10
显示所有员工的详细资料，按姓名排序
SELECT * from emp ORDER BY ename
显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面
SELECT ename,hiredate from emp ORDER BY hiredate
显示员工的姓名、工资和薪金，按工资的降序排序，若工资相同则按薪金排序
SELECT ename,sal,comm from emp order by sal desc,comm
显示所有员工的姓名、加入公司的年份月份，按照受雇日期所在的月排序，若月份相同则将最早年份的员工排在最前面
SELECT ename,EXTRACT(YEAR FROM hiredate),EXTRACT(MONTH FROM hiredate) FROM emp ORDER BY  EXTRACT(MONTH FROM hiredate)
找出在(任何年份的)2月受雇的所有员工
SELECT ename,hiredate FROM emp WHERE EXTRACT(MONTH FROM hiredate) = 2
对于每个员工，显示其加入公司的天数
SELECT ename,DATEDIFF(SYSDATE(),hiredate) FROM emp
显示姓名字段的任何位置都包含A的所有员工姓名
SELECT ename from emp where ename like '%A%'

多行函数练习

列出至少有一个员工的所有部门
SELECT deptno,COUNT(deptno)deptno FROM emp GROUP BY deptno
列出薪金比'Smith'多的所有员工(大于最大薪水SMITH员工)
select * from emp where sal > (select sal from emp WHERE ename = 'SMITH')
列出所有员工的姓名及其直接上级的员工
select m.ename, m.hiredate from emp e join emp m on(e.empno = m.mgr)
列出受雇时间早于直接上级的员工
select m.ename, m.hiredate from emp e join emp m on(e.empno = m.mgr) WHERE m.hiredate < e.hiredate
列出部门名称和这些部门的员工信息，包括哪些没有员工的部门
select * from emp e RIGHT JOIN dept d USING(deptno)
列出所有JOB为'CLERK'的姓名及其部门名称
select ename,dname,job from emp e RIGHT JOIN dept d USING(deptno) WHERE job = 'CLERK'
列出最低薪金大于1500的各种工作
select job from emp where sal > 1500 GROUP BY job
列出在部门'sale'工作的员工姓名,假定不知道销售部的部门编号 
select * from emp e JOIN dept d USING(deptno) WHERE d.dname='SALES'
列出薪金高于公司平均薪金的所有员工
select ename,sal from emp where sal > (select AVG(sal) from emp)
列出与"SCOTT"从事相同工作的所有员工
SELECT ename,job from emp where job=(select job from emp where ename='SCOTT') and ename != 'SCOTT'