DM7 模式对象管理、DMSQL

模式对象管理

模式是所有对象的集合（表、视图、索引，序列，同义词，自增列）。DM在创建用户的时候，会默认的创建一个同名的模式。

表的管理

达梦支持那些表：
默认的表是索引组织表，支持堆表，临时表，分区表，外部表等等。

如何去规划表：

1. 命名：字母开头，a-z,0-9,$#_
2. 数据类型：int char varchar date clob blob number等等。
3. 存储位置：自已规划的表空间。
4. 约束（5大约束）非空约束，唯一约束，主键，检查，外键
5. 注释：comment;
   遵循3范式。

案例1：规划一张学员信息表。
表名：STU
学号：id char(10)
姓名：sname varchar(20) not null
性别：sex char(1）
年龄：age int
电话：tel varchar(15) not null
家庭住址：address varchar(50)
表空间：STU
约束  主键列----学号，非空---姓名和电话。
备注：student info

CREATE TABLE "TEST"."STU"
(
"ID" CHAR(10) NOT NULL,
"SNME" VARCHAR(20) NOT NULL,
"SEX" CHAR(1),
"AGE" INT,
"TEL" VARCHAR(15) NOT NULL,
"ADDRESS" VARCHAR(50),
CLUSTER PRIMARY KEY("ID")) STORAGE(ON "STU", CLUSTERBTR) ;
COMMENT ON TABLE "TEST"."STU" IS 'STUDENT INFO';

# 创建表的时候指定约束
SQL> create table test.t2(id int);
SQL> alter table test.t2 modify id int not null;
# 唯一约束
SQL> create table test.t3(id int unique);
# 唯一约束遇到null,忽略，可录入多个。
# 主键约束（一张表只能有一个主键）
SQL> create table test.t4(id int primary key);
SQL> create table test.t5(id int);
SQL> alter table test.t5 add constraint t5_pri primary key(id);
# 检查约束
SQL> create table test.t6(id int check(id>=5));

# 外键约束（可以有多个外键，外键是另一张表的主键）
SQL> create table test.t7(sid int primary key,pid int);
SQL> create table test.t8(id int primary key,sid int foreign key references test.t7(sid));

# 对列加备注：
SQL> comment on column test.t7.sid is '编号';

导入数据到表中：

1. insert into (...);
2. 通过脚本把数据导入
3. 利用数据迁移工具来导入

SQL> start /home/dmdba/a.sql

如何维护表：

1. 重命名：

SQL> alter table test.t1 rename to TT;

2. 增加删除列

SQL> alter table test.tt add name varchar(10) default 'aaaa';

SQL> alter table test.tt drop name;

SQL> select * from test.tt;

启用和禁用约束

SQL> select table_name,constraint_name,constraint_type from dba_constraints where table_name='T6';
SQL> alter table test.t6 disable constraint CONS134218842;
SQL> alter table test.t6 enable constraint CONS134218842;
SQL> select table_name,constraint_name,constraint_type from dba_constraints where table_name='T6';

删除表：

SQL> drop table test.tt;

如何去查看表结构

sp_tabledef(‘模式名’，‘表名’)

SQL> sp_tabledef('TEST','T8');

DBMS_METADATA.GET_DDL(类型，表名，模式名）

SQL> select DBMS_METADATA.GET_DDL('TABLE','STU','TEST');

视图

视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制，它简化了用户数据模型，提供了逻辑数据独立性，实现了数据共享和数据的安全保密。
视图是数据库技术中一个十分重要的功能。从系统实现的角度讲，视图是从一个或几个基表（或视图）导出的表，但它是一个虚表，即数据字典中只存放视图的定义（由视图名和查询语句组成），而不存放对应的数据，这些数据仍存放在原来的基表中。
当对一个视图进行查询时，视图将查询其对应的基表，并且将所查询的结果以视图所规定的格式和次序进行返回。
因此当基表中的数据发生变化时，从视图中查询出的数据也随之改变了。
从用户的角度来讲，视图就像一个窗口，透过它可以看到数据库中用户感兴趣的数据和变化。
当用户所需的数据是一张表的部分列、或部分行，或者数据是分散在多个表中，那么就可以创建视图来将这些满足条件的行和列组织到一个表，而不需要修改表的属性、甚至创建新的表。
这样不仅简化了用户的操作，还可以提高数据的逻辑独立性，实现数据的共享和保密。

视图分类：简单视图，复杂视图，物化视图

注意：简单视图和复杂视图不占磁盘空间，物化视图占磁盘空间。

简单视图
创建create view() as select () from () where ()

SQL> grant select on dmhr.employee to test;
SQL> create view test.v1 as select * from dmhr.employee;

相关视图 Dba_views;

删除视图：

SQL> drop view test.v1;

索引管理

索引是与表相关的可选的结构（聚簇索引除外），它能使对应于表的 SQL 语句执行得更
快，因为有索引比没有索引能更快地定位信息。DM7 索引能提供访问表的数据的更快路径，
可以不用重写任何查询而使用索引，其结果与不使用索引是一样的，但速度更快。

DM7 提供了几种最常见类型的索引，对不同场景有不同的功能，它们是：

1. 聚集索引：每一个普通表有且只有一个聚集索引；
2. 唯一索引：索引数据根据索引键唯一；
3. 函数索引：包含函数/表达式的预先计算的值；
4. 位图索引：对低基数的列创建位图索引；
5. 位图连接索引：针对两个或者多个表连接的位图索引，主要用于数据仓库中；
6. 全文索引：在表的文本列上而建的索引。

使用下面的准则来决定何时创建索引：

1. 如果需要经常地检索大表中的少量的行，就为查询键创建索引；
2. 为了改善多个表的连接的性能，可为连接列创建索引；
3. 主键和唯一键自动具有索引，在外键上很多情况下也创建索引；
4. 小表不需要索引。

不适合建索引的情况

1. 列上有大量的null
2. 列上的数据有限（例如性别）;

选取表中的索引列时可以考虑以下几点：

1. 列中的值相对比较唯一 ；
2. 取值范围大，适合建立索引；
3. CLOB 和 TEXT 只能建立全文索引、BLOB 不能建立任何索引。

# 创建索引表空间：
SQL> create tablespace index1 datafile '/dm7/data/DAMENG/index101.dbf' size 32;

# 建索引：
SQL> create table test.emp as select * from dmhr.employee;
SQL> select  table_name,index_name from dba_indexes where table_name='EMP';

SQL> explain select * from test.emp where employee_id<20;

# 没有走索引，统计信息是旧的，需要重新收集
# SP_CREATE_SYSTEM_PACKAGES(1);

begin
  dbms_stats.gather_table_stats('TEST','EMP');
END;

SQL> explain select * from test.emp where employee_id<20;

创建索引，删除，重建索引，收集统计信息的时候，不要在业务高峰去做。

维护索引

# 重建
SQL> alter index test.ind_emp rebuild;
SQL> alter index test.ind_emp rebuild online;

# 删除索引：
SQL> drop index test.ind_emp;

序列

预分配一组内存空间，可以将序列作为自增列。

创建：

CREATE SEQUENCE "TEST"."S1"
            INCREMENT BY 1  ----自增多少
            START WITH 1   ---起始值
            MAXVALUE 5    ---最大值
            MINVALUE 1   --最小值
            NOCYCLE   ----是否循环
            NOCACHE    ---是否缓存
            NOORDER     ---是否顺序
            ;

应用：

SQL> create table test.t10(id int primary key);
SQL> insert into test.t10 values(test.s1.nextval);
SQL> insert into test.t10 values(test.s1.nextval);
SQL> insert into test.t10 values(test.s1.nextval);
SQL> insert into test.t10 values(test.s1.nextval);
SQL> insert into test.t10 values(test.s1.nextval);
SQL> insert into test.t10 values(test.s1.nextval);

修改序列：

SQL> alter sequence test.s1 maxvalue 10;
SQL> select test.s1.nextval;
行号     NEXTVAL
---------- --------------------
1          6
SQL> select sequence_name,max_value from dba_sequences where sequence_name='S1';

# 删除：
SQL> drop sequence test.s1;

同义词

同义词(Synonym)让用户能够为数据库的一个模式下的对象提供别名。
同义词通过掩盖一个对象真实的名字和拥有者，并且对远程分布式的数据库对象给予了位置透明特性以此来提供了一定的安全性。
同时使用同义词可以简化复杂的 SQL 语句。同义词可以替换模式下的表、视图、序列、函数、存储过程等对象。

同义词：公共同义词和普通同义词。

• 公共同义词：所有用户可以使用，使用时不需要加任何的模式限定。
• 普通同义词：引用的时候需要加上模式名。

创建：

# 普通同义词：
SQL> create synonym test.sy1 for dmhr.employee;

# 公共同义词：
SQL> create public synonym sy2 for dmhr.employee;

# 查同义词：
SQL> select table_name,synonym_name from dba_synonyms where synonym_name in ('SY1','SY2');

# 删除同义词：
SQL> drop synonym test.sy1;
SQL> drop public synonym sy2;

自增列

在表中创建一个自增列。该属性与 CREATE TABLE 语句一起使用，一个表只能有一个自增列。

语法格式
IDENTITY [ (种子, 增量) ]

参数

1．种子 装载到表中的第一个行所使用的值；
2．增量 增量值，该值被添加到前一个已装载的行的标识值上。增量值可以为正数或负数，但不能为 0。

CREATE TABLE "TEST"."TABLE_1"
(
"ID" INT IDENTITY(1, 1) NOT NULL,
CLUSTER PRIMARY KEY("ID")) STORAGE(ON "TEST", CLUSTERBTR) ;

DMSQL

DMSQL 基于 sql92,部分基于 sql99
SQL:

• DDL:定义 create drop alter truncate
• DML:管理 select update delete insert
• DCL:控制 grant revoke
• TCL:事务控制 commit rollback

简单查询

语法：select () from ()
第一个括号：*， column_name, alias,expr ||  distinct
第二个括号：table_name
Select * from dmhr.city
Select city_name cn from dmhr.city
SQL> select employee_name||'的工资是：'||salary as desc1  from dmhr.employee limit 10;
SQL> select distinct department_id from dmhr.employee;

过滤查询

# Where 子句常用的查询条件由谓词和逻辑运算符组成，谓词指一个条件，结果为一个布尔值，真，假或是未知。
# 逻辑运算符：and or  not
# 比较谓词：<  >  <=  >=  <>  =
# BETWEEN IN LIKE NULL EXISTS
# LIIKE %  _
SQL> select employee_name,salary from dmhr.employee where salary between 20000 and 30000;
SQL> select employee_name,salary from dmhr.employee where salary>=20000 and salary <=30000;

集函数：

1. count(*);
2. 相异集函数： AVG|MAX|MIN|SUM|COUNT(DISTINCT<列名>);
3. 完全集函数：AVG|MAX|MIN|count|sum([ALL]<值表式>);
4. 方差集函数：var_pop,var_samp,variance,stddev_pop
5. 协方差集函数：covar_pop ,conver_samp,corr;
6. 首行函数：first_vlaue
7. 求区间范围内最大值：area_max
8. 字符串集函数 listagg/listagg2 LOWER() UPPER() RTRIM()

# 求各个部门的最高工资：
SQL> select department_id,max(salary) from dmhr.employee group by department_id;
# 排序：desc 降序  asc升序
SQL> select department_id,max(salary) ss from dmhr.employee group by department_id order by ss desc;
SQL> select department_id,max(salary) ss from dmhr.employee group by department_id order by ss ;
# 按照部门分组，求各部门的平均工资，找出部门平均工资大于10000.
SQL> select department_id,avg(salary) from dmhr.employee group by department_id having avg(salary)>10000;

注意：having表示分组后的数据进行过滤，having不能单独使用，一定是和group by 一起使用的。

Select 后出现的列（聚合函数除外），一定要出现在group by 之后。

多表联接查询

• 语法：select () from () join () on()
• 第三个括号：表名
• 第四括号：关联字段。

1. 内连接：结果集显示全部满足连接条件的记录。

SQL> select employee_name,department_name from dmhr.employee e join dmhr.department d on e.department_id=d.department_id limit 10;
SQL> select employee_name,department_name from dmhr.employee e join dmhr.department d using(department_id) limit 10;

外连接：

# 左外连接：
# 把写left join 左边的全部显示，右边的只显示满足条件的，不满足条件的用null代替。
SQL> select e.employee_name,d.department_name from dmhr.employee e left join dmhr.department d on e.department_id=d.department_id limit 10;
SQL> update dmhr.employee set department_id=null where employee_name='马学铭';

# 右外连接：
# 把写在right join右边的全部显示出来，左边的只显示满足条条件，不满足条件的用null代替。
SQL> select e.employee_name,d.department_name from dmhr.employee e right join dmhr.department d on e.department_id=d.department_id limit 10;

# 全外连接
# 返回所有的记录，包括不满足条件的。
SQL> select e.employee_name,d.department_name from dmhr.employee e full join dmhr.department d on e.department_id=d.department_id limit 10;

全外连接=左外连接 union 右外连接

总结：

1. 查询两个表的关联列相关的数据用内连接。
2. Col_1是col_r的子集的时候用右外连接
3. Col_R 是col_L的子集的时候用左外连接
4. Col_r 和col_L彼此有交集的时候，但是彼此不互为子集的时候用全外连接。

子查询

子查询是一个查询sql,嵌套在主查询中，并且其结果做为主查询的条件。子查询行于主查询运行。

1. 结果集返回值是唯一的：

Select () from () where()=(子查询结果)

SQL> select employee_name,department_id from dmhr.employee where department_id=(select department_id from dmhr.employee where employee_name='陈仙');

2. 返回值是多行的

Select() from () where () >|any|all(子查询)
ALL:>ALL(MAX) <ALL(MIN)
ANY:>ANY(MIN) <ANY(MAX)

# 找出比104部门工资都高的人。
SQL> select employee_name,department_id,salary from dmhr.employee where salary >all(select salary from dmhr.employee where department_id=104);

# 找出比104部门任意一人工资都高的人。
SQL> select employee_name,department_id,salary from dmhr.employee where salary >any(select salary from dmhr.employee where department_id=104);
IN  EXISTS
SQL> select employee_name,salary from dmhr.employee where employee_name in('刘瑛','陈伟 婷','薛辉明','于倩文','陈珂');

SQL> select employee_name,salary from dmhr.employee where exists (select employee_name from dmhr.employee where department_id='104000');
#  IN  :把子查询运行完，再运行主查询
# Exists :先运行子查询，如果有满足条件，再运主查询。