DMSQL语句列举

DMSQL

⚫SQL 分类

DML：数据操纵语言：SELECT、INSERT、UPDATE、DELETE DDL：数据定义语言：CREATE、DROP、TRUNCATE、ALTER TCL：事务控制语言：ROLLBACK、COMMIT、SAVEPOINT
DCL：权限控制语言：GRANT、REVOKE

⚫查询
select a.department_id, b.DEPARTMENT_NAME, avg(A.SALARY)
from dmhr.emp a, dmhr.dept b
where a.department_id = b.department_id
group by a.department_id, b.DEPARTMENT_NAME
having avg(A.SALARY) > 12000;

⚫ 表的连接内连接：
create table dmhr.emp as select * from dmhr.employee;
create table dmhr.dept as select * from dmhr.DEPARTMENT;

select a.EMPLOYEE_NAME, b.DEPARTMENT_NAME
from dmhr.emp a, dmhr.dept b
where a.department_id = b.department_id;

外连接:左外连接、右外连接、全外连接左外连接（增加测试数据，员工没有部门）：
select a.EMPLOYEE_NAME, b.DEPARTMENT_NAME
from dmhr.emp a, dmhr.dept b
where a.department_id = b.department_id(+);

select a.EMPLOYEE_NAME, b.DEPARTMENT_NAME
from dmhr.emp a left outer join dmhr.dept b
on a.department_id = b.department_id;

右外连接（增加测试数据，新增一个部门，没有员工）：

select a.EMPLOYEE_NAME, b.DEPARTMENT_NAME
from dmhr.emp a, dmhr.dept b
where a.department_id(+) = b.department_id;

select a.EMPLOYEE_NAME, b.DEPARTMENT_NAME
from dmhr.emp a right outer join dmhr.dept b
on a.department_id = b.department_id;

全外关联：
select a.EMPLOYEE_NAME, b.DEPARTMENT_NAME
from dmhr.emp a full outer join dmhr.dept b
on a.department_id = b.department_id;