1、SQL 语法分类
DML(Data Mannipulation Language)数据操纵语言：
查询、操纵数据表资料行，包含 SELECT、INSERT、UPDATE、DELETE，MERGE。
默认情况下，DML 不自动提交，需要手工提交（commit）和回滚（rollback）；对数据的操作
会产生 REDO 和 UNDO；
DDL(Data Definition Language)数据定义语言：
建立、修改、删除数据库中数据表对象，包含 CREATE、ALTER、DROP、TRUNCATE、
COMMENT。默认情况下，DDL 自动提交。
DCL(Data Control Language)数据控制语言：
用于执行权限授予与收回操作，包含 GRANT、REVOKE。
TCL(Transactional Control Language)事物控制语言：
维护数据的一致性，包含 COMMIT、ROLLBACK、SAVEPOINT。
DELETE 和 TRUNCATE 的区别：
DELETE：DML 语言，产生 redo 和 undo，删除大量数据会很慢；可以带 where 条件，支持
回滚，delete 的数据支持闪回查询、不立即释放空间（DM 中系统定时自动清理回滚页，时
长由参数 undo_retention 指定）。
truncate：DDL 语句，不产生 redo 和 undo，只能删除整张表的数据，且立即释放空间，
不支持回滚，不支持 where 条件，truncate 表很快（大表也依然很快）。TRUNCATE 可以降
低表的水位线。
SELECT：查询列，聚合函数、distinct 去重
FROM：查询的表
WHERE：查询条件
GROUP by：分组列
Having：聚合的过滤条件（配合 group by 使用）
OREDER by ：排序，默认 asc, 倒序排 desc ， NULL 默认排在最前面（ 由 参 数
ORDER_BY_NULLS_FLAG，oracle 默认排在最后面）
DISTINCT 只去重，不排序。
UNION 只去重，不排序。
DM8的单表查询：
select t.EMPLOYEE_ID, t.EMPLOYEE_NAME, t.EMAIL, t.PHONE_NUM
from dmhr.employee t;
select t.EMPLOYEE_ID, t.EMPLOYEE_NAME, t.EMAIL, t.PHONE_NUM
from dmhr.employee t
where t.DEPARTMENT_ID = 101;
DMSQL语法与Oracle的区别
DM中union和distinct只去重，不排序。
Oracle中union和distinct去重且排序。
group by和having的用法：
select a.DEPARTMENT_ID, count(*) as empnum
from dmhr.employee a
group by a.DEPARTMENT_ID
having count(*) >=30;
select a.DEPARTMENT_ID, a.JOB_ID, count(*) as empnum
from dmhr.employee a
group by cube(a.DEPARTMENT_ID, a.JOB_ID)
having count(*) >=20;
select a.DEPARTMENT_ID, a.JOB_ID, count(*) as empnum
from dmhr.employee a
group by rollup(a.DEPARTMENT_ID, a.JOB_ID)
having count(*) >=20;
order by 用法：
DM 中空值排序，默认排在最前面。可以指定排在最后面。
select a.DEPARTMENT_ID, count(*) as empnum
from dmhr.employee a
group by a.DEPARTMENT_ID
having count(*) >=30
order by empnum desc;
update dmhr.employee t set t.salary=null where t.employee_id=1100;
commit;
select t.EMAIL, t.EMPLOYEE_ID, t.EMPLOYEE_NAME, t.DEPARTMENT_ID, t.SALARY
from dmhr.employee t
where t.DEPARTMENT_ID = 101
order by t.SALARY desc NULLS LAST ;
CUBE 与 ROLLUP
达梦的 GROUP BY 语句除了最基本的语法外，还支持 ROLLUP 和 CUBE 语句。
ROLLUP(A, B, C): 首先会对(A、B、C)进行 GROUP BY，然后对(A、B)进行 GROUP BY，
然后是(A)进行 GROUP BY，最后对全表进行 GROUP BY 操作。
CUBE(A, B, C)：首先会对(A、B、C)进行 GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行 GROUP BY 操作。
CUBE 和 ROLLUP 之间的区别在于：
CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。
多表连接
内连接：返回两张表中满足连接条件的记录。
外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不
满足条件的行，这种连接称为左（或右）外连接
两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行，
这种连接称为全外连接。
表的连接方式：nested loop join、HASH JOIN 等
两张小表关联可以使用 nested loop join，连接列上可以创建索引。
Hash join:主要用于两张大表的连接。以较小的表为驱动表，连接效率更快。
[执行语句1]:
select a.DEPARTMENT_ID, b.DEPARTMENT_NAME, count(*) as empnum
from dmhr.employee a join dmhr.DEPARTMENT b
on a.DEPARTMENT_ID = b.DEPARTMENT_ID
group by a.DEPARTMENT_ID
having count(*) >=30
order by empnum desc;
执行失败(语句1)
第6 行附近出现错误[-4080]:
不是 GROUP BY 表达式
内连接案例：
select a.DEPARTMENT_ID, b.DEPARTMENT_NAME, count(*) as empnum
from dmhr.employee a, dmhr.DEPARTMENT b
where a.DEPARTMENT_ID = b.DEPARTMENT_ID
group by a.DEPARTMENT_ID, b.DEPARTMENT_NAME
having count(*) >=30
order by empnum desc;
select a.DEPARTMENT_ID, b.DEPARTMENT_NAME, count(*) as empnum
from dmhr.employee a join dmhr.DEPARTMENT b
on a.DEPARTMENT_ID = b.DEPARTMENT_ID
group by a.DEPARTMENT_ID, b.DEPARTMENT_NAME
having count(*) >=30
order by empnum desc;
左外连接：
构造数据：
update dmhr.employee a
set a.DEPARTMENT_ID = null
where a.DEPARTMENT_ID = 101;
commit;
select a.DEPARTMENT_ID, b.DEPARTMENT_NAME, count(a.employee_id) as
empnum
from dmhr.employee a, dmhr.DEPARTMENT b
where a.DEPARTMENT_ID = b.DEPARTMENT_ID(+)
group by a.DEPARTMENT_ID, b.DEPARTMENT_NAME
-- having count(*) >=30
--order by empnum desc;
select a.DEPARTMENT_ID, b.DEPARTMENT_NAME, count(a.employee_id) as
empnum
from dmhr.employee a left join dmhr.DEPARTMENT b
on a.DEPARTMENT_ID = b.DEPARTMENT_ID
group by a.DEPARTMENT_ID, b.DEPARTMENT_NAME
-- order by empnum desc;
右外关联：
insert into dmhr.DEPARTMENT(DEPARTMENT.DEPARTMENT_ID,
DEPARTMENT.DEPARTMENT_NAME)
values(888888,'Test');
commit;
select a.DEPARTMENT_ID, b.DEPARTMENT_id, b.DEPARTMENT_NAME,
count(a.employee_id) as empnum
from dmhr.employee a, dmhr.DEPARTMENT b
where a.DEPARTMENT_ID(+) = b.DEPARTMENT_ID
group by a.DEPARTMENT_ID, b.DEPARTMENT_id, b.DEPARTMENT_NAME;
select a.DEPARTMENT_ID, b.DEPARTMENT_NAME, count(a.employee_id) as
empnum
from dmhr.employee a right join dmhr.DEPARTMENT b
on a.DEPARTMENT_ID = b.DEPARTMENT_ID
group by a.DEPARTMENT_ID, b.DEPARTMENT_NAME;
全外关联：
select a.DEPARTMENT_ID, b.DEPARTMENT_NAME, count(a.employee_id) as
empnum
from dmhr.employee a full join dmhr.DEPARTMENT b
on a.DEPARTMENT_ID = b.DEPARTMENT_ID
group by a.DEPARTMENT_ID, b.DEPARTMENT_NAME;
