sql:结构化查询语言
数据定义DDL 数据定义语句
create alter drop truncate--清空表,只是数据的清空 grant--数据权限 revoke--回收权限... 其中ddl是不能回滚的
数据操作DML数据操作语言
select insert update delete lock table,...这些语句是可以回滚的,
数据控制DCL数据控制语句
commit savepoint rollback
这里还有个隐式提交,比如说我执行一个ddl,那么上一条语句的dml就会被隐式提交
这里的回滚就比如说我在执行一条dml的时候我设置一个savepoint保存点,那么我下次rollback的时候就会回到这里
测试表:
select * from scott.emp;
select * from scott.dept;
数据定义:
create table dept
(deptno number(2), dname varchar(14), loc varchar(13));
create table emp as select * from scott.emp
create table emp2 as select * from scott.emp where I=2;
drop table emp3;
数据操作语句:
insert:
insert into dept values('50','HR',Los angeles');
insert into emp (empno,ename,deptno) values('7999','Kitty','90')
insert into dept values (&1,&2,&3);
然后你就·数据就行了。。。
insert into dept select * from scott.dept;
将a表里面的某一行数据查到b表里边,很简单,直接insert就可以了。。
update:
update dept set dname='MARKETING' where deptno=50;
update emp set sal=sal+1000;
update dept set dentno=&1 where dname=&2;
update emp set deptno=50 where deptno in
(select deptno from dept2 where dname='SALES');
drop操作是删除一个表,而delect操作是删除表中的数据
delete from emp2 where deptno=20;
delete from emp2 where empno=&1
delect from emp2;
提交和回退:
commit; 提交,若未提交就退出则默认为提交
rollback; 回退
savepoint A 设置保存点
rollback to savepoint A 回退到指定保存点
查询:
select empno lower(ename),'New Salary:' ,sal+1000 from emp;
select empno Num,'New Salary:' "New Sal",sal+1000 "New",upper(ename) name from emp;
select ename,(select ename from emp where empno=e.mgr) as mgr from emp e;
select '员工号'||empno||':'||ename from emp;
统计:
select cout(*) from emp;
select count(distinct job) from emp;
select max(sal) from emp;
select min(sal) from emp;
select sum(sal) from emp where deptno=10; 十号部门的工资总数
select avg(sal) from emp where deptno=10;
decode是替换,比如下面就是10号部门替换成会计,20号部门替换成研究,30号部门替换成销售
select deptno,decode(deptno,10,'会计',20,'研究',30,'销售','其他') from dept;
排序和分组
select ename,sal from emp where deptno='20' order by sal DESC;
工资相同的按员工号排序:
select * from emp order by sal, empno DESC;
select deptno, count(*) from emp group by deptno having count(*) > 3;