一、Group by字句
可以按照某一个字段分组,也可以按照多个字段的组合进行分组;
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;
SELECT 查询语句中同时选择分组计算函数表达式和其他独立字段时 ,其他字段必须出现在Group By 子
句中,否则不合法;
SELECT department_id, count(last_name) FROM employees
GROUP BY department_id;
不能在Where 条件中使用分组计算函数表达式,当出现这样的需求的时候,使用Having 子句;
SELECT department_id, AVG(salary) FROM employees
GROUP BY department_id
HAVING AVG(salary) > 8000;
分组计算函数也可嵌套使用
SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;
二、子查询
单行比较必须对应单行子查询(返回单一结果值的查询); 比如= , >
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees);
多行比较必须对应多行子查询(返回一个数据集合的查询);比如 IN , > ANY, > ALL 等
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
三、DML语句
1、INSERT语句
* insert into table values(column,...);
* insert into table;
* INSERT INTO table [ column (, column) ] subquery;从另一个表中copy一行
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';
* 使用子查询作为插入目标
INSERT INTO
(SELECT employee_id, last_name,
email, hire_date, job_id, salary, department_id
FROM employees
WHERE department_id = 50)
VALUES (99999, 'Taylor', 'DTAYLOR', TO_DATE('07-JUN-99', 'DD-MON-RR'),
'ST_CLERK', 5000, 50);
2、UPDATE语句
* 更新符合条件的行中某些列为具体的值
UPDATE employees SET department_id = 70
WHERE employee_id = 113;
* 使用子查询的结果作为更新后的值
UPDATE employees
SET job_id = (SELECT job_id
FROM employees
WHERE employee_id = 205),
salary = (SELECT salary
FROM employees
WHERE employee_id = 205)
WHERE employee_id = 114;
存在约束时,更新可能会失败
3、delete语句
4、MERGE语句(整合语法)
四、事务控制
Commit、Rollback
隐式执行commit
1、数据定义语句被执行的时候,比如新建一张表:Create Table …
2 、数据控制语句被执行的时候,比如赋权 GRANT …( 或者 DENY)
3 、正常退出 iSQL*Plus 或者PLSQL DEVELOPER, 而没有显式的执行 COMMIT 或者 ROLLBACK 语句 。
隐式执行Rollback
非正常 退出 iSQL*Plus , PLSQL DEVELOPER, 或者发生系统错误