数据库

select * from EMP t
-- 7369向7499转账200 
insert into emp(empno,ename,sal)values(100,'老王',1000);
SAVEPOINT mark1;
update emp set sal = sal-200 where empno=7369;
update emp set sal = sal+200 where empno=7499;
SAVEPOINT mark2;
rollback to savepoint mark1;
commit;
 
插入数据
try{
更新数据
}catch(){
   回滚
}
提交

select empno, ename, sal, comm, sal + comm total, sal * (1 + 0.3) other
  from emp

--:查询员工号是7788,7900的员工信息
select t.*
from emp t where t.empno in(7788,7900);
-- 查询员工号大于等于7788且小于等于7900的员工信息
select t.*
from emp t where t.empno between 7788 and 7900;
select t.*
from emp t where t.empno>=7788 and t.empno<=7900;

--查询员工名字包含S字符的员工信息
select t.*
from emp t where t.ename like '%S%';
--查询员工名字以S字符开始的员工信息
select t.*
from emp t where t.ename like 'S%';
--查询员工名字以S字符结束的员工信息
select t.*
from emp t where t.ename like '%S';

  --查询三个部门的最低工资
select deptno,min(sal) 
from emp group by deptno 
--查询三个部门的最低工资员工信息
select * 
from emp where
sal< ANY(select min(sal) 
from emp group by deptno)

-- 查询奖金为空的员工信息
select * from emp where comm is null;

--7、 找出收取佣金的员工的不同工作；
select distinct job from emp where comm is not null;

--21、 找出在2月份受雇的员工
select * from
emp where extract(month from hiredate)=2;

select * from
emp where to_char(hiredate,'MM')='02'

--20、算出每位员工的日薪
select t.empno,t.ename,trunc(t.sal/30,0) persal
from emp t 

--10、 找出早于2012年前的受雇员工
select * 
from emp where 
to_char(hiredate,'yyyy-mm-dd HH24:MI:SS')<'1981-01-01 00:00:00'

select *
 from emp where hiredate<date'2012-01-01';
--12、 显示正好为5个字符的员工姓名；
select * from emp where length(ename)=5;

--分析函数 dense_rank() row_number() rank()
SELECT e.deptno,
       e.ename,
       e.sal,
       DENSE_RANK() OVER(PARTITION BY e.deptno ORDER BY e.sal DESC) AS pm
  FROM emp e
  
  --多表查询
  select * from emp;
  select * from dept;
  select * from emp,dept;
  
  select * from emp e,dept t where e.deptno=t.deptno
  
  --insert into emp(empno,ename,deptno) values(2,'曾伟',60);
  --内连接(等值连接)
  select e.*,t.dname
  from emp e  join dept t on e.deptno=t.deptno;
  select e.*,t.dname
  from dept t  join emp e on e.deptno=t.deptno;
  --等价于 where条件查询
   select * from emp e,dept t where e.deptno=t.deptno
   
--左外连接
   select e.*,t.dname
   from emp e 
   left join  dept t on e.deptno = t.deptno;
   --交换匹配表和驱动表位置
     select e.*,t.dname
   from dept t 
   left join  emp e on e.deptno = t.deptno;
   
--右外连接
 select e.*,t.dname
   from emp e 
   right join  dept t on e.deptno = t.deptno;
   --交换匹配表和驱动表位置
     select e.*,t.dname
   from dept t 
   right join  emp e on e.deptno = t.deptno;
   
 

转载于:https://my.oschina.net/u/3836064/blog/1821225