oracle 笔记 第一部分

create table student(
    xh number(4),
    xm varchar2(20),
    sex char(2),
    birthday date,
    sal number(7,2)
    );
   
  create table class(
      classId number(2),
      cname varchar2(40)
  );
 
  alter table student add(classId number(2));
 
 insert into student values(1,'xiaoming','男','12-1月-1997',123.67,23);
 
  alter session set nls_date_format ='yyyy-mm-dd';
   insert into student values(1,'xiaoming','男','1997-12-09',123.67,23);

  --想要插入自己想要的日期格式的数据，用to_date('****','yyyy-mm-dd')格式;
  insert into student values(2,'xiaohong','女',to_date('1989-09-09','yyyy-mm-dd'),123.45,24);
  
   savepoint aa; --设置保存点，在删除数据之前
   delete from student; --表的结构还在，但是数据没有了，但oracle有日志记录，所以数据可以恢复
   rollback to aa;--回滚到保存点。
  
   select * from student;
   savepoint my;
   select * from emp;
  
  
   set timing on; --显示所用时间
   select ename from emp;
  
   select count(*)from student;--显示行数
  
   --显示表的结构
   desc dept;
   --如何取消重复行
   select distinct deptno, job from emp;
   --年工资，包含奖金为空项
   select sal*13+comm*13 "年工资", ename from emp;
   select sal*13+nvl(comm,0) "年工资", ename from emp;--这样显示就正确了，nvl(comm,0),如果comm为空，则设置为0，否则正常
   --sal>3000
   select ename , sal from emp where sal >3000;
   --找出1982年1月一号以后的员工,如果日期没有设置nsl_date_fromat,则要写成'1-1月-1982'；
   select ename, hiredate from emp where hiredate >'1982-01-01';
   --如何显示工资在200-2500的员工
   select ename ,sal from emp where sal >=2000 and sal<=2500;
   --like，%：0—到多个字符； ？：
   select ename,sal from emp where ename like 'S%';
   --第三个字符为大写的o的员工
   select ename,sal from emp where ename like '__O%';
   --显示empno为123,234,345，
   select * from emp where empno in(7844,234,345);
   --如何显示没有上级的员工
   select * from emp where mgr is null;
  
   --查询工资高于500或者是高位时manager的员工，同时满足他们的姓名手写字母大写为J
   select * from emp where (sal>500 or job='mgr' ) and ename like 'J%';
  
   --order by
   select * from emp order by sal ;
   select * from emp order by sal desc;--从高到低
   --order by deptno 升序，sal降序排列
   select * from emp order by deptno,sal desc ;
   --如果按照两个参照物，那么前后顺序不一样会有不同的结果
   select * from emp order by deptno,hiredate desc;
   select * from emp order by hiredate desc,deptno;
  
   --使用列别名排序
   --按照年薪排序
   select  ename,(sal+nvl(comm,0))*12 "年薪" from emp order by "年薪";
  
   --分页查询？？？？以后讲
  
  
   --较复杂的select 语句
   --max,min,avg,sum,count;
   --如何显示员工中最高工资、最低工资
   select max(sal),min(sal) from emp;
   --把最高工资的那个人的名字查出来***********用子查询
   select ename,sal from emp where sal =(select max(sal) from emp);
   select ename,sal from emp where sal in (select max(sal) from emp);
   --????
   select count(*) form emp;
   --查询比平均工资低的员工
   select * from emp where sal<(select avg(sal) from emp);
  
   --group by ：分组统计 ； having:限制分组显示结果
   --按照部分分组,deptno必须出现在查询字段中，因为按照deptno分组
   select avg(sal), max(sal),deptno from emp group by deptno ;
   --显示每个部分的每中岗位的平均工资和最低工资?????
   select avg(sal),max(sal),deptno ,job from emp group by deptno , job；
   --显示avg(sal)<2000的部门号和它的平均工资,写上order by 就可以再进行排序，解决上面的问题；group by ->having ->order by->ok
   select avg(sal), max(sal),deptno from emp group by deptno having avg(sal)>100 order by deptno desc;
  
   --多表查询,规定：表表查询的条件是至少不能少于表的个数-1；
   --显示员工名、员工工资，以及所在部分的名字
   select emp.ename, emp.sal,dept.dname from emp ,dept where dept.deptno = emp.deptno;
   --如何显示部门号为10的部门的名称、员工名和员工工资？,一般会取个别名
   select a1.dname "部门名称", a2.ename "员工名称", a2.sal "工资" from emp a2, dept a1 where a1.deptno = a2.deptno and a1.deptno=10;
   --如何显示员工的名字、工资、工资的级别 between...and...
   select a1.ename,a1.sal, a2.grade from emp a1, salgrade a2 where a1.sal between a2.losal and a2.hisal;
   --显示员工名字、工资、所在部分的名字、并按部分排序,多表排序
   select a1.ename, a1.sal ,a2.dname from emp a1, dept a2 where a1.deptno = a2.deptno order by a1.deptno;
  
   --自连接，对一个表的操作，把一张表看成多个表的操作
   --显示某个员工的上级领导的名字
   select a1.ename "员工", a2.ename "老板" from emp a1 ,emp a2 where a1.mgr = a2.empno and a1.ename ='FORD';
  
   --子查询
   --单行子查询，只返回一行查询结果
   --如何显示和SMITH同一部分的员工(从左到右进行扫描，然后从右到左进行执行语句)
   select ename from emp where deptno =( select deptno from emp where ename = 'SMITH');
   --多行字查询，返回多行
   --如何查询和部门10的工作相同的员工的名字、岗位、工资、部门号
   select ename, job, sal, deptno from emp where job in (select distinct job from emp where deptno = 10);
   --多行子查询中使用all操作符
   --如何显示工资比部门30所有员工最高工资还高的员工的姓名,薪水，部门号
   select ename, sal , deptno from emp where sal >(select max(sal) from emp where deptno=30); --效率高
   select ename, sal, deptno  from emp where sal>all(select sal from emp where deptno = 30);
   select max(sal) from emp where deptno=30;
  
   --any操作符，在多子查询中使用
   --显示工资比部分30的任何一个员工的工资高就可以了。
   select ename, sal, deptno from emp where sal>(select min(sal) from emp where deptno=30);
  
   --查找与simth的部门和岗位都相同的员工,前后字段要一致
   select * from emp where (deptno , job )= (select deptno , job from emp where ename='SMITH');
  
   --如何显示高于自己部门平均工资的员工的信息
   select deptno, avg(sal) mysal from emp group by deptno;
   --把上面的查询看成是一张字表而存在，看成是独立的一张表进行比较操作。***-》内嵌视图
   --在from 中使用子查询的时候必须要设置别名，不能加as
   select a1.ename,a1.sal ,a1.deptno ,a2.mysal from emp a1,(select deptno, avg(sal) mysal from emp group by deptno) a2 where a1.deptno = a2.deptno and a1.sal>a2.mysal;
  
   --分页查询
   --oracle 分页有三种方式。
   --@1：rownum分页 ;select * from emp;
   --2: 显示rownum [oracle 分配的]; select a1.* ,rownum rn from (select * from emp);
   --3: 指定查询列，只需要修改最里面的select 即可
   select * from (select a1.* ,rownum rn from  (select * from emp) a1 where rownum<=10)where rn >=6;
   select * from (select a1.* ,rownum rn from  (select ename, sal  from emp) a1 where rownum<=10)where rn >=6;
  
   --如何排序,也是修改最里面的select的语句。
   select * from (select a1.* ,rownum rn from  (select ename, sal  from emp order by sal) a1 where rownum<=10)where rn >=6;

   --显示第4条到第9条的记录
    select * from (select a1.* ,rownum rn from  (select ename, sal  from emp order by sal) a1 where rownum<=9)where rn >=4;
  --查询共有多少行
    select count(*) from emp;
   
    --用查询结果创建新表
    create table myemp (id,name,sal,job,deptno) as select empno,ename,sal,job,deptno  from emp;
    desc myemp;
    select * from myemp;
   
    --合并查询
    --1：union,取得两个结果集的并集，并自动去掉重复项。
    --2: union all,取得的记录不去掉重复记录。
    --3：intersect,取两个集合的交集。
    --4：minus ,取差集。
    select ename ,sal ,job from emp where sal>2500 union
    select ename, sal, job from emp where job='MANAGER';
   
    select ename ,sal ,job from emp where sal>2500 union all
    select ename, sal, job from emp where job='MANAGER';
   
    --创建数据库