Oracle数据库Sql语句详解.pptVIP

级联删除 CREATE TABLE child( id number primary key, p_id number references parent(id) on delete cascade ); CREATE TABLE child( id number primary key, p_id number references parent(id) on delete set null ); 子查询创建表 CREATE TABLE emp_41 as ( select * from s_emp where dept_id =41 ); 应用举例 SQL SELECT e.dept_id, max(d.name),avg(e.salary),sum(salary) 2 FROM s_emp e , s_dept d 3 WHERE e.dept_id = d.id and e.dept_id !=41 4 GROUP BY dept_id 5 ORDER BY sum(salary); 查询s_emp表中除41部门以外的部门员工的平均工资,工资总和及部门名称并按照工资总和排序： SQL SELECT e.dept_id, max(d.name),avg(e.salary),sum(salary) 2 FROM s_emp e , s_dept d 3 WHERE e.dept_id = d.id 4 GROUP BY dept_id 5 HAVING e.dept_id !=41 6 ORDER BY sum(salary); 应用举例 求平均工资高于1500的部门的工资总和，最高工资，最低工资： SQL SELECT e.dept_id, max(d.name),avg(e.salary),sum(e.salary) 2 FROM s_emp e , s_dept d 3 WHERE e.dept_id = d.id 4 GROUP BY dept_id 5 HAVING avg(e.salary)1500 6 ORDER BY avg (e.salary); 课堂练习 求不以“VP”开头职位的，各个职位中工资总和大于5000的职位及工资总和，并按工资总和排序： SQL SELECT title, SUM(salary) PAYROLL 2 FROM s_emp 3 WHERE title NOT LIKE VP% 4 GROUP BY title 5 HAVING SUM(salary) 5000 6 ORDER BY SUM(salary); 小结 SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column]; 第六章 子查询 子查询 查询s_emp表中工资最低的员工的姓名： SQL SELECT min（salary） 2 FROM s_emp SQL SELECT last_name 2 FROM s_emp 3 WHERE salary = 最小工资（上一条的运行结果） SQL SELECT last_name 2 FROM s_emp 3 WHERE salary = (SELECT min(salary) FROM s_emp) 子查询 查询s_emp表中平均工资低于32部门的部门ID： SQL SELECT avg（salary） 2 FROM s_emp 3 WHERE dept_id = 32; SQL