mysql 匿名块,达梦7入门技术总结-

说明：

1)该实验所有过程均是本人亲自敲命令完成，所有代码运行正确

2)安装过程使用的是suse11 sp3操作系统，后续的实验过程换成了麒麟中标，因此部分路径可能存在差异

3)安装过程使用了命令行安装，图形界面简单，因此本文没有介绍

4)job部分命令行操作太繁琐，建议使用图形界面操作，因此本文也跳过了此内容

正文

1. 安装

1.1 创建安装用户组

#groupadd dinstall

1.2 创建安装用户

#useradd -g dinstall -m -d /home/dmdba -s /bin/bash dmdba

#useradd -g dinstall dmdba

1.3 初始化用户密码

#passwd dmdba

1.4 修改系统限制

#vi /etc/security/limits.conf

dmdba soft nofile 4096

dmdba hard nofile 65536

1.5 挂载镜像文件

#mkdir /dmdb

#mount -t iso9660 -o loop /root/dm7_setup_rh6_64_ent_7.6.0.197_20190917.iso /dmdb

1.6 更改权限

#chown dmdba.dinstall -R /dmdb

#chmod 755 -R /dmdb

1.7 修改环境变量

#su - dmdba

#vi .bashrc

export DM_HOME=/home/dmdba/dmdbms

export PATH=$DM_HOME/bin:$DM_HOME/tool:$PATH

1.8 采用命令行模式安装

#su - dmdba

#cd /dmdb

#./DMInstall.bin -i

1)Please select the installer"s language (E/e:English C/c:Chinese) [E/e]:e

2)Whether to input the path of Key File? (Y/y:Yes N/n:No) [Y/y]:n

3)Whether to Set The TimeZone? (Y/y:Yes N/n:No) [Y/y]:y

4)Please Select the TimeZone [21]:21

5)Installation Type:

1 Typical

2 Server

3 Client

4 Custom

Please Input the number of the Installation Type [1 Typical]:1

6)Please Input the install path [/home/dmdba/dmdbms]:

7)Please Confirm the install path(/home/dmdba/dmdbms)? (Y/y:Yes N/n:No) [Y/y]:y

8)Confirm to Install? (Y/y:Yes N/n:No):y

9)Please execute the commands by root:

/home/dmdba/dmdbms/script/root/root_installer.sh

1.9 初始化数据

#/home/dmdba/dmdbms/bin

#./dminit

input system dir: /home/dmdba/dmdbms

input db name: dmdb01

input port num: 5236

input page size(4, 8, 16, 32): 8

input extent size(16, 32): 16

input sec priv mode(0, 1): 0

input time zone(-12:59,+14:00): +08:00

string case sensitive? ([Y]es, [N]o): n

which charset to use? (0[GB18030], 1[UTF-8], 2[EUC-KR]): 1

length in char? ([Y]es, [N]o): y

enable database encrypt? ([Y]es, [N]o): n

page check mode? (0/1/2): 0

input elog path: /home/dmdba/dmdbms/log

auto_overwrite mode? (0/1/2): 2

1.10 启动数据库服务

#cd /home/dmdba/dmdbms/script/root

./dm_service_installer.sh -t dmserver -p dmdb01 -i /home/dmdba/dmdbms/dmdb01/dm.ini

提示信息：

Move the service script file(/home/dmdba/dmdbms/bin/DmServicedmdb01 to /etc/init.d/DmServicedmdb01)

insserv: warning: current stop runlevel(s) (empty) of script `DmServicedmdb01" overwrites defaults (2 3 4 5).

insserv: Service network is missed in the runlevels 4 to use service mysql

DmServicedmdb01 0:off 1:off 2:on 3:on 4:on 5:on 6:off

Finished to create the service (DmServicedmdb01)

#service DmServicedmdb01 start

1.11 连接验证(默认密码)

#/home/dmdba/dmdbms/bin/disql

username:sysdba

password:SYSDBA

2.通过vnc调出管理工具

#xhost +

#su - dmdba

#export DISPLAY=127.0.0.1:1.0

#/home/dmdba/dmdbms/tool/manager

3.修改参数(v$parameter)

3.1 参数类型

0 Sys/session 动态参数，同时修改内存和配置文件

1 Read only 在数据库运行状态时，不能修改。

2 In file 静态参数，修改后，重启服务生效。

示例：

SQL> Select para_name,para_value from v$dm_ini where para_name="BUFFER_POOLS";

LINEID para_name para_value

---------- ------------ ----------

1 BUFFER_POOLS 3

3.2 命令

SP_PARA_SET_VALUE(SCOPE,PARA_NAME,PARA_VALUE);

SCOPE值:

1: 修改配置文件和内存

2：只修改配置文件

示例：

SQL> SP_SET_PARA_VALUE(2,’BUFFER’,1000)；

4.重做日志

4.1 视图

v$rlogfile

v$rlog

SQL> select * from v$rlogfile;

说明：这里查出来的rlog_size大小单位是bit，因此再下面添加日志大小的时候需要除以两次1024转成M

4.2 添加日志文件

SQL> alter database add logfile "/home/dmdba/dmdbms/dmdb01/dmdb0103.log" size 256;

说明：默认单位是M，不需要加单位。并且需要和原来的日志文件大小一致

4.3 修改日志文件大小

SQL> alter database resize logfile "/home/dmdba/dmdbms/dmdb01/dmdb0103.log" to 300;

说明：所有的日志文件要大小一致

5.表空间

5.1视图

dba_tablespaces

v$tablespace

v$huge_tablespace

5.2 表空间说明

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

System表空间： 数据字典和全局的系统数据。

ROLL表空间：存放了数据库运行过程中产生的回滚记录。

TEMP表空间：临时表空间。

MAIN表空间：数据库默认的表空间，创建数据对象时，如果不指定存储位置，默认存放到该表空间。

HMAIN表空间：huge 表空间

5.3 查询所有表空间信息

SQL> Select name from v$tablespace union select name from v$huge_tablespace;

5.4 创建表空间

表空间初始文件大小是页大小的4096倍

示例1：表空间初始大小50M，每次扩展1M，最大100M

SQL> create tablespace tbs1 datafile "/dm7/data/dm01/tbs01.dbf" size 50 autoextend on next 1 maxsize 100;

示例2：表空间初始50M,表空间由2个数据文件组成，分别存储到不同的磁盘上，每次扩展1m,每个数据文件最大100M.

SQL> create tablespace tbs2 datafile "/dm7/data/dm01/tbs02_1.dbf" size 50 autoextend on next 1 maxsize 100,"/dm7/data/tbs02_2.dbf" size 50 autoextend on next 1 maxsize 100;

5.5 维护表空间

最常用的方式就是增加数据文件

SQL> alter tablespace tbs2 add datafile "/dm7/data/dm01/tbs02_3.dbf" size 50 autoextend on next 1 maxsize 100;

5.6 更换文件存储位置

注意：SYSTEM,ROLL, TEMP 不能offline;

SQL> alter tablespace tbs2 offline;

SQL> alter tablespace tbs2 rename datafile "/dm7/data/tbs02_2.dbf" to "/dm7/data/dm01/tbs02_2.dbf";

SQL> alter tablespace tbs2 online;

5.7 删除表空间

SQL> drop tablespace tbs2;

如果表空间存有数据，不允许直接删除！

6.用户管理

6.1 视图

dba_users

6.2 内置用户

sys --- 达梦数据库的内置管理用户，不能登录数据库，数据库使用的大部分的数据字典和动态性能视图

Sysdba --- 数据库的管理员

Sysauditor --- 审计用户

Syssso --- 安全用户

sysdbo --- 数据操作员(安全版有，四权分立)

6.3 命名规则

字母开头，a-z,0-9,$#_

6.4 口令策略

用户密码最长为48个字节，创建用户语句使用 password policy 子句来指定口令策略(DM.INI PWD_POLICY).

系统支持的口令策略：

0 无策略

1 禁止与用户名相同

2 口令长度不小于9

4 至少包含一个大写字母(A-Z)

8 至少包含一个数字(0-9)

16 至少包含一个标点符号(英文状态输入，除”和空格外)

口令可以单独使用，也可以组合使用，比如需要应策略1和2，则设置口令策略为：3

密码尝试登录次数 FAILED_LOGIN_ATTEMPS

密码失败锁定时间 PASSWORD_LOCK_TIME

密码过期时间 PASSWORD_LIFE_TIME

6.5 用户权限

系统权限: create drop alter 能够对数据库做什么操作。

对象权限(表，视图，过程等等): select delete update insert

角色: dba、public等

6.6 查看用户权限：

SQL> select grantee,granted_role from dba_role_privs where grantee="USER1";

行号 GRANTEE GRANTED_ROLE

---------- ------- ------------

1 USER1 PUBLIC

6.7 查看角色拥有的权限

SQL> select grantee,privilege from dba_sys_privs where grantee="PUBLIC";

行号 GRANTEE PRIVILEGE

---------- ------- ----------------

1 PUBLIC INSERT TABLE

2 PUBLIC UPDATE TABLE

3 PUBLIC DELETE TABLE

4 PUBLIC SELECT TABLE

5 PUBLIC REFERENCES TABLE

6 PUBLIC GRANT TABLE

.....

6.8 示例

示例1：为数据库设置一个用户，该用户可以创建自己的表，有属于自己的独立表空间，用户密码要求每60天变更一次。

SQL> create tablespace tbs3 datafile "/dm7/data/dm01/tbs03.dbf" size 32;

SQL> create user user1 identified by user1user1 limit password_life_time 60 default tablespace tbs3;

示例2：创建一个用户，该用户每60天变更一次密码，密码尝试连接2次失败，账户锁定5分钟，用户还可以查询dmhr.employee表

SQL> create user user2 identified by user2user2 limit password_life_time 60, FAILED_LOGIN_ATTEMPS 2 ,PASSWORD_LOCK_TIME 5 default tablespace tbs3;

SQL> grant select on dmhr.employee to user2;

示例3：企业招聘一批录入人员，权限是固定的，只有录入CITY表的权限

角色：一类权限的集合

SQL> create role role1;

SQL> grant insert on dmhr.city to role1;

SQL> grant references any table to role1;

SQL> grant role1 to user2;

6.9 用户维护

1)撤销权限

SQL> revoke select on dmhr.employee from user2;

2)解锁用户

SQL> alter user user2 account unlock;

3)修改密码

SQL> alter user user2 identified by user3user3;

4)删除用户

SQL> drop user user2;

5)删除用户(级联删除)

SQL> drop user user2 cascade;

7.dmsql

说明：由于SQL查询内容比较复杂，并且作为数据管理型DBA SQL开发也不是重点，所以这里只总结了大概内容。

7.1 常识

dmsql主要基于sql92,部分sql99

SQL：结构化查询语言

DDL: 定义 create drop alter truncate

DML: 管理 select update delete insert

DCL:控制 grant revoke

TCL:事务控制：commit rollback

7.2 select

简单查询：select () from ();

过滤查询：select () from () where ();

多表链接：select() from () join() on();

分组查询：select 聚合函数() from () group by () having ()；

子查询：

7.3 insert into

7.4 delete

7.5 update

8.表的管理

8.1 达梦数据库支持的表

索引组织表(默认的表)、堆表、临时表、分区表、外部表等。

8.2 创建表时包含的内容

命名：字母开头 a-z,0-9,$#_

数据类型：int char varchar date clob blob number等等。

存储位置：自已的规划的表空间。

约束(5大约束): 非空约束，唯一约束，主键，检查，外键

注释：comment

遵循3范式

8.3 示例

1)要求

表名：STU

学号(id char(10)),

姓名(sname varchar(20) not null),

性别 (sex char(1))

年龄(age int)

电话(tel varchar(15) not null)

家庭住址：(address varchar(50))

表空间：TBS2

约束：主键列---学号 非空---姓名，电话

备注：学员信息表

2)SQL语句

create table USER2.STU

(

ID CHAR(10) not null ,

SNAME VARCHAR(20) not null ,

SEX CHAR(1),

AGE INT,

TEL VARCHAR(15) not null ,

ADDRESS VARCHAR(50),

primary key(ID)

) storage (on TBS2);

comment on table USER2.STU is "学员信息表";

comment on column USER2.STU.ID is "学号";

comment on column USER2.STU.SNAME is "姓名";

comment on column USER2.STU.SEX is "性别";

comment on column USER2.STU.AGE is "年龄";

comment on column USER2.STU.TEL is "电话";

comment on column USER2.STU.ADDRESS is "家庭住址";

8.4 查看表结构

注意：用户名、表名都要大写

SQL> sp_tabledef("USER2","STU");

行号 COLUMN_VALUE

---------- -------------------------------------------------------------------

1 CREATE TABLE "USER2"."STU" (

"ID" CHAR(10) NOT NULL,

"SNAME" VARCHAR(20) NOT NULL,

"SEX" CHAR(1), "AGE" INT,

"TEL" VARCHAR(15) NOT NULL,

"ADDRESS" VARCHAR(50),

CLUSTER PRIMARY KEY("ID"))

STORAGE(ON "TBS2", CLUSTERBTR) ;

或者：

SQL> select dbms_metadata.get_ddl("TABLE","STU","USER2");

8.5 查看表所属表空间

SQL> select table_name,tablespace_name from dba_tables where table_name="STU";

行号 TABLE_NAME TABLESPACE_NAME

---------- ---------- ---------------

1 STU TBS2

8.6 查看表有哪些约束

SQL> select table_name,constraint_name,constraint_type from dba_constraints where table_name="STU";

行号 TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE

---------- ---------- --------------- ---------------

1 STU CONS134218774 P

8.7 创建表时指定约束

1)非空约束

SQL> create table user2.t1(id int);

SQL> alter table user2.t1 modify id int not null;

或者：

SQL> create table user2.t2(id int not null);

2)唯一约束

SQL> create table user2.t3(id int unique);

3)主键约束

SQL> create table user2.t4(id int primary key);

4)检查约束

SQL> create table user2.t5(id int check(id>=5));

5)外键约束

SQL> create table user2.t6(id int primary key ,sid int foreign key references user2.t4(id));

说明：作为外键约束的列必须是另外一个表的主键

8.8 表的注释

SQL> comment on column user2.t1.id is "编号";

8.9 导入数据到表中

1)编写a.sql脚本:

insert into user2.t1(id) values(1);

insert into user2.t1(id) values(2);

insert into user2.t1(id) values(3);

insert into user2.t1(id) values(4);

commit;

2)然后：

SQL> start /tmp/a.sql

3)也可以用图形界面的DTS工具去做。

8.10 维护表

1)重命名

SQL> alter table user2.t6 rename to tt6;

2)增加删除列

SQL> alter table user2.tt6 add name varchar(10);

SQL> alter table user2.tt6 drop name;

3)启用禁用约束

SQL> select table_name,constraint_name,constraint_type from dba_constraints where table_name="T4";

行号 TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE

---------- ---------- --------------- ---------------

1 T4 CONS134218776 P

SQL> alter table user2.t4 disable constraint CONS134218776;

SQL> alter table user2.t4 enable constraint CONS134218776;

9.视图管理

9.1 视图分类

简单视图，复杂视图，物化视图

注：简单视图和复杂视图不占磁盘空间

物化视图占磁盘空间

9.2 创建视图

create view () as select () from () where ();

SQL> create view v1 as select * from user2.t1;

SQL> select * from v1;

行号 ID

---------- -----------

1 1

2 2

3 3

4 4

9.3 查看视图

SQL> select view_name,text from dba_views where view_name="V1";

行号 VIEW_NAME TEXT

---------- --------- ---------------------------------

1 V1 SELECT USER2.T1.ID FROM USER2.T1

9.4 修改视图

SQL> create or replace view v1 as select * from user2.t2;

SQL> select * from v1;

未选定行

10.序列管理

10.1 创建序列

SQL> create sequence user2.s1

start with 1 ----序号起始

increment by 1 --自增多少

maxvalue 50 ---最大值

nocache ---是否缓存

nocycle; ---是否循环

10.2 序列的应用

SQL> insert into user2.t1 values(user2.s1.nextval);

SQL> insert into user2.t1 values(user2.s1.nextval);

向表中插入两条记录。

SQL> select * from user2.t1;

行号 ID

---------- -----------

1 1

2 2

3 3

4 4

5 1

6 2

6 rows got

11.同义词管理

11.1 创建同义词

公共：SQL> create public synonym ss1 for dmhr.employee;

普通：SQL> create synonym ss2 for dmhr.employee;

SQL> select count(*) from ss1;

行号 COUNT(*)

---------- --------------------

1 856

11.2 修改同义词

SQL> create or replace synonym ss2 for dmhr.city;

11.3 删除同义词

SQL> drop synonym ss2;

SQL> drop public synonym ss1;

12.索引管理

12.1 达梦支持的索引

二级索引、位图索引、唯一索引、复合索引、函数索引、分区索引等。

默认的表是索引组织表，是利用rowid创建一个默认的索引，所以创建的索引称为二级索引。

12.2 查看表的索引

SQL> select table_name,index_name from dba_indexes where table_name="T1";

行号 TABLE_NAME INDEX_NAME

---------- ---------- -------------

1 T1 INDEX33555470

12.3 索引的作用

加快表的查询，对数据库做DML操作的时候，数据库会自动维护索引。索引是一棵倒置的树，使用索引，就是对这棵索引树做遍历。

12.4 建立索引的规则

1)适合建立索引的情况

经常查询的列

接条件列

谓词经常出现的列(where)

查询是返回表的一小部分数据

2)不适合做索引的情况：

列上有大量的null

列上的数据有限(例如性别)

12.5 创建索引

规划索引表空间

表的数据是无序的，索引的数据是有序的

示例：在Emp中的employee_id 建立索引。

1)建立表

SQL> create table user2.emp as select * from dmhr.employee;

2)建立索引表空间

SQL> create tablespace tbs_idx1 datafile "/dm7/data/dm01/tbs_idx101.dbf" size 32;

3)建立索引：

SQL> create index ind_emp on user2.emp(employee_id) tablespace tbs_idx1;

4)查询索引：

SQL> select table_name,index_name from dba_indexes where table_name="EMP";

行号 TABLE_NAME INDEX_NAME

---------- ---------- -------------

1 EMP INDEX33555479

2 EMP IND_EMP

12.6 重建索引

SQL> alter index user2.IND_EMP rebuild;

13.备份还原

13.1 备份分类

物理备份、逻辑备份

13.2 物理备份

1)工具简介

冷备：(dmap服务打开的状态下，数据库是关闭的)

热备：(dmap服务一定是打开的，数据库也是打开的，数据库要开归档)

导入导出：dexp dimp

集群：数据守护(dw),dsc(rac)

达梦支持第三方的备份软件：爱数，鼎甲

2)物理备份过程(冷备)

第一步：创建归档日志存放路径

#mkdir /dm7/data/dm01/arch

第二步：开归档

SQL> alter database mount;

SQL> alter database add archivelog "type=local,dest=/dm7/data/dm01/arch,file_size=64,space_limit=0";

SQL> alter database archivelog;

SQL> alter database open;

SQL> select ARCH_MODE,STATUS$ from v$database;

行号 ARCH_MODE STATUS$

---------- --------- -----------

1 Y 4

第三步：停止数据库服务

[dmdba@localhost dm01]$ service DmServicedm01 stop

第四步：使用dmrman备份

说明：dmrman 命令必须在命令所在的bin目录下执行才行

[dmdba@localhost bin]$ pwd

/dm7/bin

[dmdba@localhost bin]$ ./dmrman

RMAN> backup database "/dm7/data/dm01/dm.ini";

根据提示信息找到备份文件位置

3) 使用命令行备份(热备)

使用命令行备份，同样需要开归档，但是不需要停数据库服务

#mkdir /dm7/data/backup

SQL> backup database full backupset "/dm7/data/backup/full_bak";

增量备份

SQL> insert into user2.t1 values(user2.s1.nextval);

SQL> select checkpoint(1);

SQL> backup database increment backupset "/dm7/data/backup/incr_bak";

13.3 还原数据

1)选择实验用户和实验表空间

SQL> select username,default_tablespace from dba_users where username="USER2";

行号 USERNAME DEFAULT_TABLESPACE

---------- -------- ------------------

1 USER2 TBS3

2)模拟表空间文件损坏

#mv tbs03.dbf tbs03.dbf.bak

3)重启服务

#service DmServicedm01 stop

#service DmServicedm01 start

4)手动打开数据库

SQL> alter database open;

5)查看表空间状态

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where tablespace_name="TBS3";

行号 TABLESPACE_NAME STATUS

---------- --------------- -----------

1 TBS3 1

6)恢复数据

SQL> restore tablespace tbs3 from backupset "/dm7/data/backup/full_bak";

7)online表空间

SQL> alter tablespace tbs3 online;

8)查看表空间状态

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where tablespace_name="TBS3";

行号 TABLESPACE_NAME STATUS

---------- --------------- -----------

1 TBS3 0

9)查询数据

SQL> select * from user2.t1;

行号 ID

---------- -----------

1 1

2 2

3 3

4 4

5 1

6 2

7 3

8 4

8 rows got

13.4 逻辑导入导出

1)工具简介

Dexp 逻辑导出 dimp 逻辑导入

Dexp和dimp是DM自带的工具，分为四种级别：

数据库级，用户级、模式级和表级。四种级别独立互斥，不能同时存在。四种级别所提供的功能：

数据库级(full):导出或导入整个数据库中的访问对象。

用户级(owner):导出或导入一个或多个用户所拥有的所有对象。

模式级(schemas):导出或导入一个或多个模式下的所有对象。

表级(table):导出或导入一个或多个指定的表或表分区。

2)实验过程

第一步:备份user2下的数据

[dmdba@localhost bin]$ pwd

/dm7/bin

[dmdba@localhost bin]$./dexp sysdba/SYSDBA file=/dm7/data/backup/user2.dmp log=/dm7/data/backup/user2.log owner=user2

第二步：恢复数据到user1下

[dmdba@localhost bin]$ pwd

/dm7/bin

[dmdba@localhost bin]$./dimp sysdba/SYSDBA file=/dm7/data/backup/user2.dmp log=/dm7/data/backup/user1.log fromuser=user2 touser=user1

第三步：数据确认(两次SQL语句是导入数据前后的对比)

SQL> select * from user1.t1;

select * from user1.t1;

[-2106]:Error in line: 1

Invalid table or view name [t1].

used time: 1.538(ms). Execute id is 0.

SQL> select * from user1.t1;

LINEID id

---------- -----------

1 1

2 2

3 3

4 4

5 1

6 2

6 rows got

14.达梦SQL开发

14.1 配置ODBC(linux下)

1)安装(root用户)

#tar -xvf unixODBC-2.3.0.tar.gz

#cd unixODBC-2.3.0

# ./configure --enable-gui=no

#make

#make install

# odbc_config --version

2.3.0

#odbc_config --odbcini

/usr/local/etc/odbc.ini

#odbcinst -j

unixODBC 2.3.0

DRIVERS............: /usr/local/etc/odbcinst.ini

SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini

FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources

USER DATA SOURCES..: /root/.odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

2)修改配置文件

#cd /usr/local/etc

#vi odbc.ini

[dm7]

Desription=DM ODBC DSND

Driver = DM7 ODBC DRIVER

SERVER = localhost

UID = SYSDBA

PWD = Abcd.1234

TCP_PORT = 5236

#vi odbcinst.ini

[DM7 ODBC DRIVER]

Description = ODBC DRIVER FOR DM7

Driver = /dm7/bin/libdodbc.so

3)修改文件权限

#chmod 775 odbc.ini

#chmod 775 odbcinst.ini

4)测试

#su - dmdba

#isql dm7

+---------------------------------------+

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

+---------------------------------------+

14.2 存储过程

1)匿名块

SQL> set serveroutput on

SQL> begin

print("hello world");

end;

/

2)命名块(存储过程)

SQL> CREATE OR REPLACE PROCEDURE USER2.P1

AS

BEGIN

SELECT * from USER2.T1;

END;

/

SQL> call user2.p1;

LINEID id

---------- -----------

1 1

2 2

3 3

4 4

5 1

6 2

6 rows got