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;