DMsql

DMsql

⚫ SQL分类

DML:select、insert、update、delete
DDL: create、drop、alter、truncate
DCL:revoke、grant
TCL：commit、rollback、savepoint

Delete 和 truncate 的区别：
Delete:DML 语句、产生 redo 和 undo、可以回滚，速度相对慢，可以删除部分数据；
Truncate: DDL 语句，不产生 redo，不能回滚，速度更快，可以降低表的水位线。
⚫ 多表连接

select * from dmhr.employee natural join dmhr.department;

select * from dmhr.employee a join dmhr.department b
using(department_id);

select a.employee_id, a.employee_name, b.department_name from
dmhr.employee a join dmhr.department b on a.department_id =
b.department_id where a.department_id = 1001;

左关联：左表的数据都全部列出：

update dmhr.employee a set a.department_id = null where a.employee_id = 1001;

select a.employee_id, a.employee_name, a.department_id,
b.department_id, b.department_name from dmhr.employee a left join
dmhr.department b on a.department_id = b.department_id;

右关联：

select a.employee_id, a.employee_name, a.department_id, b.department_id, b.department_name from dmhr.employee a right join dmhr.department b on a.department_id = b.department_id;

全关联：

select a.employee_id, a.employee_name, a.department_id,
b.department_id, b.department_name from dmmhr employeea full outer
join dmhr departmentb on a.departement_id = b.department_id;

注意外关联时on条件和where条件的区别。

select a.employee_id, a.employee_name, a.department_id,
b.department_id, b.department_name from dmhr.employee a full outer
join dmhr.department b on a.department_id = b.department_id and
a.department_id is not null;

select a.employee_id, a.employee_name, a.department_id,
b.department_id, b.department_name from dmhr.employee a full outer
join dmhr.department b on a.department_id = b.department_id where
a.department_id is not null;

GROUP BY和HAVING的用法：

select b.department_name, sum(a.salary) from dmhr.employee
a,dmhr.department b where a.department_id = b.department_id group by
b.department_name having sum(a.salary)>100000;

select * from (select b.department_name, sum(a.salary) sum_salary
from dmhr.employee a,dmhr.department b where a.department_id =
b.department_id group by b.department_name) where sum_salary >100000;