oracle基本操作

oracle笔记:

• 账户

--oracle自带账户：
      -- system 管理账户
         --特点：具备大部分oracle的操作权限，主要用来管理普通账户及oralce的数据
         --使用人：oracle数据维护工作人员
      -- sys 超级管理员账户
         --特点：具备system的所有权限，同时又具备其他的权限
         --使用人：oracle攻城狮
    --创建账户
         --使用system账户，并使用dba身份，登录oracle管理系统
         --创建用户
            create user fly identified by fly;         
    --维护账户
            --赋予权限  grant 权限或者角色名 to 用户名
            
             grant connect to fly;--给用户赋予登录权限
             grant resource to fly;--给用户资源操作权限
             grant dba to fly;--给用户赋予dba权限
             select * from scott.emp--查看其它用户的表 使用用户名.表名  
             
            --删除权限 revoke 权限或者角色名 from 用户名
            revoke dba from fly;
    --删除账户 drop user 用户名
            drop user fly;

• 别名:

select ename 姓名,job as "工作",mgr as MGR,mgr"MGR1" from emp;

• 连接符

select ename||'的工作是:'||job as mess from emp;

• 去重

select distinct job from emp;

• 字符运算

select sal*0.8 from emp;

• oracle默认的日期格式为 日-月-年，示例'03-1月-1981'

select * from emp where hiredate>='01-12月-1981' order by hiredate;

• 转义字符 escape 定义'/'为转义符号 '/'将有转义功能

select * from emp where ename like '%/_%' escape '/'

• 单行函数:
  • 字符转小写

  select lower(ename),lower('ABCD') from emp;
  select lower('ABC') from emp;
  

  • 数值函数,使用伪表

select abs(-1),ceil(2.2),floor(3.3),power(2,3),mod(5,2),round(4.55),trunc(10/3,2) from dual
--         1     3 向上取整   3向下取整    8 幂   1取余       5四舍五入   3.33截断

- 日期函数：

          select months_between('01-1月-2018','01-1月-2017') from dual --返回两个日期间的月份数  
          select add_months('01-4月-2018'，-4) from dual --返回指定月数后的日期
          select next_day('19-3月-2019','星期二') from dual--查询最近的星期的日期
          select last_day('19-3月-2019') from dual--返回当月的最后一天的日期
          select round(to_date('20-3月-2019'),'DAY') from dual--按照星期进行四舍五入

• 多行函数(max,min,avg,sum,count)

     select max(sal),sal from emp--多行函数不能和字段直接混用，除非分组。
     select lower(ename)，max(sal) from emp--多行函数 不能和单行函数混用，除非分组
     select count(distinct job) from emp
     select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp 

• 转换函数

--转换函数：
    --to_number(数值类型的字符):将字符转换为数值
    --to_char(数值或者是日期):将数值或者日期转换为字符
    --to_date(日期格式的字符)：将字符转换为日期
----------------数值和字符的互转-----------------------
--字符转换为数字char---->number
    select to_number('123')+2  from dual
--数字转换字符number--->char
    --指定显示格式:
           --9表示位置占位，例如999,999,999会将数字按照三个一组使用逗号隔开。
           --L表示人民币符号，$表示美元符号
           --0可以进行占位分组，但是如果真实数据位数不足，会使用0进行补位。
            select to_char(12345,'$999,999,999') from dual
            select to_char(12345,'L999,999,999') from dual
            select to_char(12345678,'000,000,000,000.000') from dual
    --查询工资大于2000的员工信息
            --数值和字符之间的转换可以隐式转换。to_number可以省略不写.
            select * from emp where sal>'2000';
             select * from emp where sal>to_number('2000');
---------------日期和字符的互转---------------------------
--一般使用方式：新增数据使用to_date(),查询数据使用to_char()
--字符转换为日期 char--->date
    --使用to_date('要转换的字符',日期格式)函数将字符转换为日期
         --注意1：字符必须符合日期格式
         --注意2:oralce默认的转换格式为日月年，例如'01-1月-2018' oracle认为是一个日期
         --常用日期格式：
               --    yyyy-mm-dd
               --    yyyy/mm/dd
    --查询员工入职日期在82年后的信息
    select * from emp where hiredate >to_date('1982-01-01','yyyy-mm-dd')
    select * from emp where hiredate >to_date('1982/01/01','yyyy/mm/dd')      
    select * from emp where to_char(hiredate,'yyyy-mm-dd') >'1982-01-01'
--日期转换为字符  date--->char
   --使用to_char('要转换的日期',转换格式)
   --注意1：如果不指名转换格式，则使用默认格式，日月年例如:'01-1月-81'
   --常用转换格式：
         -- yyyy-mm-dd
         -- yyyy/mm/dd
         --'yyyy"年"mm"月"dd"日"'
   select to_char(hiredate) from emp--使用默认格式将日期转换为字符
   select to_char(hiredate,'yyyy-mm-dd') from emp--使用指定格式  yyyy-mm-dd
   select to_char(hiredate,'yyyy/mm/dd') from emp--使用指定格式 yyyy/mmm/dd
   select to_char(hiredate,'yyyy"年"mm"月"dd"日"') from emp--使用指定格式 'yyyy"年"mm"月"dd"日"'
----------------------------------------------------------------------------------------------------- 

-其他函数：

   --nvl()：nvl(字段名，新的值)
          --如果字段值不为null，则返回该字段的值。如果为null则返回新的值
   --nvl2()：nvl2(字段名，处理1，处理2)
          --如果字段值不为null，则执行处理1，为null执行处理2
   --decode()：decode(字段名,值1，处理1，值2，处理2，值3，处理3,...,公共处理)
          --如果字段的值和decode中的条件值相同则执行对象的处理。如果都没有则执行公共处理
   ---查询员工的工资信息
   select ename,job,sal from emp
   --查询员工的薪水信息
   select comm,sal+nvl(comm,0),sal+comm,sal from emp
                     800                     800.0
          300.00     1900         1900      1600.00
          500.00     1750        1750        1250.00 
   select ename,job,nvl2(comm,sal+comm,sal) from emp
   --显示员工的职称
   select ename,job,decode(job,'MANAGER','经理','PRESIDENT','董事长','SALESMAN','销售','普通员工') from emp

• 分组

     --关键字：group by 分组字段名,分组字段名....
         --注意1：使用了分组后，在select语句中只允许出现分组字段和多行函数。
         --注意2：如果是多字段分组，则先按照第一字段分组，然后每个小组继续按照第二个字段继续分组，以此类推。
         --注意3：在where子句中不允许出现多行函数。
     --分组筛选
         --关键字：having
              --作用：针对分组进行分组后的数据筛选，允许使用多行函数。
              --注意：having关键必须和分组结合使用。不允许单独使用。   
              --where和having的比较：
                 --where子句不允许出现多行函数，having允许出现多行函数
                 --where子句和having都可以使用普通字段直接进行筛选，但是where的效率高于having
                    --where执行顺序: from--->where--->group by-->select-->order by
                    --having执行顺序:from--->group by-->select--->having--->order by
               --结论：在分组语句中，使用where进行字段级别的筛选，使用having进行多行函数的筛选。   
			   
	 --查询部门号大于10的不同部门的不同工作岗位的人数
          --使用having关键字
          select deptno ,lower(job),count(*) from emp group by deptno,job  having deptno>10  order by deptno
          --使用where关键字
          select deptno,job,count(*) from emp where deptno>10 group by deptno,job  order by deptno

• 增加数据

    --insert into 表名(字段名，字段名，...)values(值1，值2，值3....);
    insert into dept(deptno,dname,loc)values(60,'xxx','北京');
    insert into dept values(70,'xxx','北京'); -- 全字段插入，可以省略字段名部分

• 删除数据

  --删除数据
       --delete from 表名 删除表中的所有记录
           --truncate table 表名  删除表中的所有记录，但是效率高于delete
       --delete from 表名 where 条件 删除指定的数据,只要符合条件就会删除

• 更新字段

  --更新数据
       --update 表名 set 字段名=新的值,字段名=新的值...(会将字段的值全部改为新的值)
       --update 表名 set 字段名=新的值,字段名=新的值... where 条件(将符合条件的数据的字段改为新的值)

• 数据备份

  --数据的备份
       --注意：只会备份表结构和表的数据，约束不会备份。
       --表级别备份
           --全部备份:create table 新的表名 as select * from 备份表名
           --部分备份: create table 新的表名 as select 字段名，字段名，...from  备份表名
       --数据整体插入
           --insert into 插入表名 select * from 表名
           --注意:查询语句结果的字段数据必须和插入表名的字段数量一致，类型要一致。
       create table deptBak as select * from dept--全部备份
       create table deptBak2 as select deptno,dname from dept-- 部分备份
       select * from deptBak2
       insert into deptBak2 select deptno,dname from dept

---

• 联合查询

--sql92多表联合查询
--自连接:
	--查询员工姓名，工作，薪资，及上级领导的名字
SELECT E1.ENAME,E1."JOB",E1.SAL,E2.ENAME FROM EMP E1,EMP E2 WHERE E1.MGR=E2.EMPNO;
--外连接
	--左外连接
		--查询员工姓名，工作，薪资，部门名称及没有部门的员工信息(+)表示没值也显示出来。
		SELECT * FROM EMP e,DEPT d WHERE e.DEPTNO=d.DEPTNO(+);
	--查询员工姓名，工作，薪资，部门名称及没有员工信息的部门。
		SELECT * FROM EMP e,DEPT d WHERE e.DEPTNO(+)=d.DEPTNO;
	--右外连接
--SQL99多表查询
    --注意1：依然可以给表添加别名
    --注意2：如果使用on或者usering关键对结果进行筛选，必须使用inner join作用表与表的连接，其中inner可以省略
    --注意3:外连接的 outer关键字可以省略不写
    --注意4：依然可以继续使用分组，having ，排序等
    --笛卡尔积:使用cross join 关键字
            ---select 内容 from 表名 cross join 
             select * from emp cross join dept
    --筛选
         --查询员工姓名，工作，薪资，部门名称
         --自然连接：使用关键字 natural join
            --使用：select 内容 from 表名 natural join 表名
            --特点1：底层先笛卡尔积，然后按照所有的同名同值字段自动进行等值筛选。
            --问题1：如果只想按照部分字段结果筛选怎么办？
            --问题2：如果想按照字段名不同，但是值相同进行等值筛选怎么办？
            select * from emp natural join dept
             --解决1：使用using关键字
                  --作用1：指明使用指定的字段对联合查询的结果进行等值筛选
                  --注意：指明的字段必须是两表的同名同值字段
                  --使用:select 内容 from 表名 inner join 表名 using(字段名，字段名,....)
                  select * from emp inner join dept using(deptno)
            --解决2：使用on关键字进行自定义连接条件筛选(等值筛选，不等值筛选)
                  --注意：普通筛选条件使用where进行筛选，不要使用on进行。好处：SQL语句的阅读性变强。
                  --使用：select 内容 from 表名 inner join 表名 on 连接条件 where 普通筛选条件
                  select * from emp inner join dept on emp.deptno=dept.deptno where sal>2000
        --外连接：
             --左外连接:select 内容 from 表名 left outer join 表名 on 连接条件 
                 --查询员工姓名，工作，薪资，部门名称及没有部门的员工信息
                 select * from emp e left outer  join dept d on e.deptno=d.deptno
             --右外连接:select 内容 from 表名 right outer join 表名 on 连接条件 
                  --查询员工姓名，工作，薪资，部门名称及没有员工的部门信息
                  select * from emp e right outer  join dept d on e.deptno=d.deptno
             --全外连接：select 内容 from 表名 full outer join 表名 on 连接条件 
                  select * from emp e full  outer join dept d on e.deptno=d.deptno
       --自连接:
             --查询员工及其上级领导姓名
             select  e1.*,e2.ename from emp e1 inner join emp e2 on e1.mgr=e2.empno
			 
			 
	 --完成三表联合查询
	--SQL92实现：查询员工信息及部门名称及所在城市名称并且员工的工资大于2000或者有奖金
	    --特点：易于书写，难于阅读
	    --缺点：92的SQL语句结构不清晰
	    --用法：
	         --select  内容 (别名，连接符，去除重复，oracle函数，逻辑运算)
	         --from  表名1，表名2，表名3...
	         --where  条件(连接条件，普通筛选条件，where子句关键字)
	         --group by 分组字段
	         --having 多行函数筛选
	         --order by 排序字段
	    select e.*,d.dname,c.cname 
	    from emp e,dept d,city c
	    where (e.deptno=d.deptno and d.loc=c.cid and sal>2000) or (e.deptno=d.deptno and d.loc=c.cid and comm is not null) 
	    order by e.sal 
   --SQL99实现:查询员工信息及部门名称及所在城市名称并且员工的工资大于2000或者有奖金
        --特点：难于书写，易于阅读
        --使用：
              --select 内容 from 表名1
              -- inner join 表名2
              -- on 连接条件
              --inner join 表名3
              --on 连接条件
              --where  普通筛选条件
              --group by 分组
              --having 多行函数筛选
              --order by 排序
      select * from emp e 
      inner join dept d 
      on e.deptno = d.deptno 
      inner join city c 
      on d.loc =c.cid
      where e.sal>2000 or e.comm is not null
      order by e.sal
	  
	  --单表查询：
  --当需要的数据在一张表中，考虑使用单表查询
--多表联合查询：
  --当需要查询的数据分布在多张表中，考虑使用多表联合
--子查询学习：
  --使用时机：当查询的筛选条件不明确时，考虑使用子查询。
  --单行子查询
  --多行子查询
----------------------------------------------------------------
--单行子查询:
  --使用时机：筛选条件不明确需要执行一次查询，并且查询结果一个字段并值只有一个
  --注意：where子句中允许出现查询语句，该查询语句称为子查询
  --使用：select 内容 from 表名 where 字段名 比较运算符 子查询语句
    --查询所有比雇员“CLARK”工资高的员工信息
    select * from emp where sal>(select sal from emp where ename ='CLARK')
    --查询工资高于平均工资的员工的名字和工资
    select ename,sal from emp where sal>(select avg(sal) from emp )
    --查询和soctt属于同一部门且工资比他低的员工资料
    select * from emp where deptno=(select deptno from emp where ename='SCOTT') and sal<(select sal from emp where ename='SCOTT')
    --查询工资最高的员工资料
    select * from emp where sal=(select max(sal) from emp)
    --查询职务和scott相同，雇佣时间早的员工信息
    select * from emp where job=(select job from emp where ename='SCOTT') and hiredate <(select hiredate from emp where ename='SCOTT')
    --查询工资比scott高或者雇佣时间早的员工编号和名字
    select empno,ename from emp where job=(select job from emp where ename='SCOTT') or hiredate <(select hiredate from emp where ename='SCOTT')
----------------------------------------------------------------------------
----多行子查询：
     --使用:子查询的结果只有一个字段但是字段有n个值，考虑使用多行子查询，其实就是使用关键字
       --关键字1:any 任意
            --select 内容 from 表名 where 字段名 比较运算符 any 子查询语句
       --关键字2：all 所有
            --select 内容 from 表名 where 字段名 比较运算符 all 子查询语句
       --关键字3：in 表示任意存在,相当于 = any  
            --select 内容 from 表名 where 字段名 in 子查询语句   
            --select 内容 from 表名 where 字段名 not in 子查询语句   
    --查询工资高于任意一个CLERK的所有员工信息
    select * from  emp where sal> any (select sal from emp where job='CLERK')
    --查询工资高于所有SALESMAN的员工信息
    select * from emp where sal> all (select sal from emp where job='SALESMAN')
    --查询部门20中同部门10的雇员工作一样的雇员信息
    select job from emp where deptno=10
    select *from emp where (job='MANAGER' or job='PRESIDENT' or job='CLERK') and deptno=20
    select * from emp where job  in (select job from emp where deptno=10) and deptno=20
    select * from emp where job = any (select job from emp where deptno=10) and deptno=20

• 约束

  --创建表:
    --使用：create table 表名(字段名 类型，字段名 类型，....);
    --数据类型：
        --number类型
              --数值类型
                   --整数类型 number(a)   总长度为a
                   --浮点数类型 number(a,b) 总长度为a，小数位长度为b，小数位可以不写。
        --varchar2类型
                --字符类型 varchar2(ln) ln表示字符的最大长度，实际存储内存长度是根据字符大小来分配，但是最大不能超过ln 
                --特点：动态分配存储空间，节省空间
        --char类型
                --字符类型 char(ln) 不管字符数据长度是多大，直接开辟ln大小的空间存储数据
                --特点：存储效率高于varchar2
        --date类型
    create table student(
      sno number(10) primary key, --主键约束
      sname varchar2(100) not null,
      sage number(3) check(sage<150 and sage>0), --检测约束
      ssex char(100),
      sfav varchar2(500) --unique --唯一约束,
      sbirth date
	  --, constraints pk_student_sno primary key(sno) --添加主键约束
	  --, constraints ck_student_sname check(sname is not null) --添加非空约束
	  --, cid number(10) references clazz(cno)  --外键映射
    )
	--修改表，添加主键约束
	alter table student add constraints pk_student_sno primary key(sno);
	--删除主键约束
	alter table student drop constraints pk_student_sno;
	--修改可以级联删除
	alter table student add foreign key(cid) references clazz(cno) on delete cascade;
 --添加测试数据
    insert into student values(1,'柳岩',18,'女','拍电影'，'01-1月-1985');
    insert into student values(2,'古力娜扎',20,'女','拍电影'，to_date('1990-01-01','yyyy-mm-dd'));
select * from student
  --使用外键:
      --作用：当在子表中插入的数据在父表中不存在，则会自动报错。
      --概念：当一张表的某个字段的值需要依赖另外一张表的某个字段的值，则使用外键约束。
             --其中主动依赖的表称为子表，被依赖的表称为父表。外键加在子表中。
      --使用： 
             --在子表中的字段后直接使用   references 父表名(字段) 例如： cid number(10) references clazz(cno)
             --在创建表语句的最后面使用  constraints fk_子表名_字段名 foreign key(字段名) references 父表名(字段名)
             --在创建表后使用：alter table 表名 add constraints fk_子表名_字段名 foreign key(字段名) references 父表名(字段名)
            --删除外键：alter table 表名 drop constraints 外键约束名
      --外键选取：
            --一般选取父表的主键作为子表的外键。
      --外键的缺点：
            --无法直接删除父表数据，除非级联删除
            --级联删除：在添加外键约束时，使用关键字 on delete cascade
                     --使用：当删除父表数据时，自动删除子表相关所有数据。
                     --缺点：无法保留子表历史数据。
                     --使用关键字 on delete set null
                           --删除父表数据时，将子表中的依赖字段的值设置为null。
                           --注意：子表依赖字段不能添加非空约束。
						   
	--二维表的维护
	--添加新的字段:
	    --alter table 表名 add 字段名 类型
	     alter table student add sphone number(11)--在学生表中添加新的字段
	--修改原有字段
	     --修改字段类型
	         --alter table 表名 modify 字段名 新的类型
	         alter table  student modify sphone varchar2(11)
	     --修改字段名
	         --alter table 表名 rename column 字段名 to 新的字段名
	         alter table student rename column sphone to phone 
	     --删除字段
	         --alter table 表名 drop column 字段名
	         alter table student drop column phone
	--修改表名
	     --rename 原有表名 to 新的表名
	     rename student to student2
	     rename student2 to student
	--删除表
	    --drop table 表名
	    drop table student 
		--oracle的序列的学习

• 序列

	--创建序列
	  --使用 create sequence 序列名
	  --特点1：默认开始是没有值的，也就是指针指在了没有值的位置。
	  --特点2：序列名.nextval每次执行都会自增一次，默认步长为1
	  --特点3：序列名.currval查看当前序列的值。开始是没有的。
	  --作用：作为主键使用,动态的获取之间的值，这样新增数据的时候极大的避免了主键冲突
	       --使用的是 序列名.nextval作为主键
	  --注意：主键是非空唯一就可以，不需要主键的值是连续的值。
	       --创建默认序列
	         create sequence cc;--创建序列cc
	         select cc.currval from dual--查看序列当前值
	         select cc.nextval from dual--查看序列的自增后的值。
	       --创建自定义序列
	          create sequence aa--创建序列
	          start with 5      --设置开始位置
	          increment by 2    --设置步长
	          select aa.currval from dual 
	          select aa.nextval from dual
	   --创建测试表
	       create table teacher(
	            tid number(10) primary key,
	            tname varchar(100) not null
	       )
	       insert into teacher values(cc.nextval,'张三');
	       insert into teacher values(cc.nextval,'张三');
	        
	       select * from teacher
	--删除序列
	       --drop sequence 序列名
	       drop sequence aa

• 索引

 --索引学习：
     --作用：提升查询效率
     --使用索引：
         --创建
           create index 索引名 on 表名(字段名)
         --删除索引
           drop index 索引名
     --特点：
         --显示的创建，隐式的执行
     --注意：
         --oracle会自动给表的主键创建索引。
      
     create index index_teacher_tname on teacher(tname)--创建索引
     drop index index_teacher_tname--删除索引
     select * from teacher where tname='张三'
     select * from teacher where tid=8

• 视图

--视图学习:
      --使用视图：
          --创建视图
          create view 视图名 as select 对外提供的内容 from 真实表名
          --删除视图
          drop view 视图名
      --视图特点：
         --特点1：保护真实表，隐藏重要字段的数据。保护数据。
         --特点2：在视图中的操作会映射执行到真实表中
         --特点3：可以手动开启只读模式 使用关键字 with read only
      --注意：视图的创建必须拥有dba权限
      create view stu as select sno,sname,sage from fly.student
      create view stu2 as select sno,sname,sage from student with read only 
      drop view stu
      select * from student
      select * from stu
      update stu2 set sname='wollo' where sno=1
      grant dba to fly
	  
	  -----oracle的分页查询
   --问题：当一个表中的数据量特别大的时候，如果一次性全部显示给用户，则造成页面过于庞大，体验极差。
   --解决：使用分页查询
   --使用：
      --rownum关键字：oracle对外提供的自动给查询结果编号的关键字，与每行的数据没有关系。
        --注意：rownum关键字只能做< <=的判断，不能进行> >=的判断
      select rownum ,e.* from emp e;
      --查询员工信息的前5条数据 第一页数据
      select rownum r,e.* from emp e where rownum <=5;
      select * from (select rownum r,e.* from emp e where rownum <=5) t where r>0;
      --查询员工信息的6-10条数据 第二页数据
      select rownum r,e.* from emp e where rownum <=10;
      select rownum,t.* from (select rownum r,e.* from emp e where rownum <=10) t where r>5;
      --查询员工信息的11-15条数据 第三页数据
      select rownum r,e. * from emp e where rownum<=15;
      select * from (select rownum r,e. * from emp e where rownum<=15) t where r>10;
      --分页规律总结：每页显示m条数据，查询第n页数据
      select * from (select rownum r,e. * from 要分页的表 e where rownum<=m*n) t where r>m*n-m ;
             --要分页的表既可以是真实的表，也可以是一个查询语句
      --分页查询员工信息按照工资排序
      select * from (select rownum r,t.* from (select * from emp  order by sal) t where rownum<=10 ) where r>5
      
      select * from t
      
      select * from emp