Oracle的增删改查语句

首先要了解DML语句（insert,update,delete）,默认是不提交的,需要手动commit;

Oracle设置保存点,可按如下操作:

insert into test2 values(1,'first');//执行一条插入语句

savepoint a;//设置一个保存点a

update test2 set id = 2,name ='second';//执行一条更新语句

savepoint b;//设置一个保存点b

delete from test2;//删除所有表内容

select * from test2;//此时查看一下，显示未选定行

rollback to b;//回滚到b

select * from test2;//此时查看可以看到id=2,name='second'的记录

rollback to a;//回滚到a

select * from test2;//此时查看可以看到id=1,name='first'的记录

commit;//即可在要想提交时提交，此时不能再回滚了

注意:回滚顺序不能颠倒，比如先回滚到a点，就不能在执行rollback to b操作了

Oracle的查询语句:

基本语法:

SELECT column, group_function 
FROM table [WHERE condition] 
[GROUP BY group_by_expression] 
[HAVING group_condition] 
[ORDER BY column];

简单查询:

select * from 表名; //通常不建议查询*,最好是写出具体的列名，即使要查找全部内容

分组查询：

select avg(salary),max(salary),min(salary),sum(salary) from s_emp;//查询s_emp中薪水的平均，最大，最小，总数

//按照部门id进行分组，并按部门id升序排序(默认升序asc,降序为desc)显示，注意查询中有dept_id,dept_id在这里不是组函数，必须出现在gruop by中，否则会报dept_id不是组函数

select avg(salary),max(salary),min(salary),sum(salary),dept_id from s_emp group by dept_id order by dept_id;

select dept_id,count(*) from s_emp where dept_id =41;//错误的写法

//查询title,薪水总数并取个别名为PAYROLL,条件为title不是'VP%',%指一个或多个字符（按照title分组，总薪水排序）

select title,sum(salary) PAYROLL from s_emp where title not like 'VP%'group by title order by sum(salary);

--条件查询中条件要用到组函数时要用having，而不能用where

select dept_id,avg(salary) from s_emp where avg(salary) >2000 group by dept_id;//错误的写法 

//查询部门薪水平均值大于1000，并按薪水升序排列

select dept_id,avg(salary) from s_emp having avg(salary) >1000 group by dept_id order by avg(salary);//having在group by前，可以使用，但是不建议

select dept_id,avg(salary) from s_emp group by dept_id  having avg(salary) >1000 order by avg(salary);

//按照title分组，条件为2条记录以上,查找title,12*avg(salary),count(*) 

select title,12*avg(salary),count(*) from s_emp group by title having count(*) > 2;

//多条件分组:dept_id,title都相同时分为一组

select dept_id,title,count(*) from s_emp group by dept_id,title order by dept_id;