Oracle笔记：关于Oracle数据库的一些基本操作

• 创建表空间

create tablespace itheima
datafile 'C:\OracleData\itheima.dbf'
size 100m
autoextend on---自动扩展
next 10m;

• 删除表空间

drop tablespace itheima;

• 创建用户

create user itheima
identified by itheima--密码
default tablespace itheima;--默认表空间

• 给用户授权
  oracle数据库中常见的角色：
  connect：连接角色，基本角色；
  resource：开发者角色；
  dba：超级管理员角色。

grant dba to itheima;--给itheima用户授予dba角色

• 创建一个person表

create table person(
       pid number(20),
       pname varchar2(10)
);

• 修改表结构

--添加一列
alter table person add (gender number(1));
--修改列类型
alter table person modify gender char(1);
--修改列名称
alter table person rename column gender to sex;
--删除一列
alter table person drop column sex;

• 数据的增删改查
  其中，truncate table person的效果等同于删除表中的全部记录。在数据量大的情况下，尤其是表中带有索引的情况下，该操作的效率非常高。索引可以提供查询效率，但是会影响增删改的效率。

--查询表中记录
select * from person
--添加一条记录
insert into person (pid,pname) values (2,'小红');
--修改一条记录
update person set pname = '小白' where pid = 1;
--三个删除
delete from person;--删除表中全部记录
drop table person; --删除表结构
truncate table person;--先删除表，再次创建表。

• 序列
  序列不真的属于一张表，但是可以逻辑和表做绑定。序列默认都是从1开始，依次递增，主要是来给主键赋值使用。dual：虚表，只是为了补全语法，没有任何实际意义。因为Oracle数据库的查询语句必须有from

create sequence s_person; --为当前表创建一个序列
select s_person.nextval from dual;--查询序列下一个值
select s_person.currval from dual;--查询序列的当前值
insert into person (pid,pname) values (s_person.nextval,'小明');--使用序列插入数据

• scott用户

alter user scott account unlock;--解锁scott用户
alter user scott identified by tiger;--解锁scott用户的密码[此语句也可以用来重置密码]

• 单行函数：作用于一行，返回一个值

--字符函数
select upper('yes') from dual;--把小写转换成大写
select lower('YES') from dual;--把大写转成小写
--数值函数
select round(56.12,1) from dual;--四舍五入，后面的参数表示保留的位数，可以是负数
select trunc(56.16,-1) from dual;--直接截取
--日期函数
--查询出emp表中的所有员工入职距离现在几天
select sysdate-e.hiredate from emp e;
--算出来明天此刻
select sysdate+1 from dual;
--查询出emp表中所有员工入职距离现在几月
select months_between(sysdate,emp.hiredate) from emp;
--查询出emp表中所有员工入职距离现在几年
select months_between(sysdate,emp.hiredate)/12 from emp;
--查询出emp表中所有员工入职距离现在周
select round((sysdate-emp.hiredate)/7) from emp;
--转换函数
--日期转字符串
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--2019-05-13 08:28:22
select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss') from dual;-- 2019-5-13 8:28:52
--字符串转日期
select to_date('2019-5-13 8:28:52','fm yyyy-mm-dd hh24:mi:ss') from dual;
--通用函数
--算出emp表中所有员工的年薪
--注意，null值和任意数字做算术运算，结果都是null值，使用nvl()函数，当第一个值是null时，使用第二个值
select emp.sal*12+emp.comm from emp;
select emp.sal*12+nvl(emp.comm,0) from emp;

• 条件表达式

--给emp表中的员工起中文名字
select e.ename,
       case e.ename
         when 'SMITH' then '曹操'
           when 'ALLEN' then '诸葛亮'
             when 'WARD' then '刘备'
               else '无名'
                 end
                 from emp e;
--判断emp表中员工工资，如果高于3000显示高收入，如果高于1500低于3000显示中收入，其余显示低收入
select e.sal,
     case  
       when e.sal>3000 then '高收入'
         when e.sal>1500 then '中等收入'
           else '低收入'
             end
             from emp e;
--oracle专用表达式
--oracle中除了起别名，都用单引号
select e.ename,
       decode( e.ename,
          'SMITH','曹操',
            'ALLEN','诸葛亮',
              'WARD','刘备',
                '无名') "中文名"
                 from emp e;

• 多行函数：作用于多行，返回一个值

select count(1) from emp; --查询总数量
select sum(sal) from emp; --查询工资总和
select max(sal) from emp; --查询最多工资
select min(sal) from emp; --查询最低工资
select avg(sal) from emp; --查询平均工资

• 分组查询
  分组查询中，出现在group by 后面的原始列，才能出现在select后面。没有出现在group by后面的列，想要在select后面，必须加上聚合函数。 --所有条件都不能使用别名。
  where是过滤分组前的数据，having是过滤分组后的数据，表现形式：where必须在group by之前，having是在group by之后。

--查询出每个部门的平均工资
select e.deptno,avg(e.sal) from emp e group by e.deptno;
--查询出平均工资大于2000的部门信息
select e.deptno,avg(e.sal) from emp e group by e.deptno having avg(e.sal)>2000;
--查询出每个部门工作高于1500的员工的平均工资
select e.deptno,avg(e.sal) from emp e where e.sal>1500 group by e.deptno;

• 多表查询

--笛卡尔积
select * from emp,dept;
--等值连接
select * from emp e,dept d where e.deptno = d.deptno
--内连接
select * from emp e inner join dept d on e.deptno = d.deptno;
--查询出所有部门，以及部门下的员工信息。（右外链接）
select * from emp e right join dept d on e.deptno = d.deptno;
--查询出所有员工，以及员工所属部门（左外链接）
select * from emp e left join dept d on e.deptno = d.deptno;
--oracle中专用的外链接
select * from emp e ,dept d where e.deptno(+) = d.deptno;
--查询出员工姓名，员工领导姓名（自连接：自连接其实就是站在不同的角度把一张表看成多张表）
select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno

• 子查询

--查询出工资和scott一样的员工信息（子查询返回一个值）
select * from emp where sal = (select sal from emp where ename = 'SCOTT' )
--查询出工资和10号部门任意员工一样的员工信息（子查询返回一个集合）
select * from emp where sal in (select sal from emp where deptno = 10)
--查询出每个部分最低工资，和最低工资员工的姓名，和改员工所在部门名称（子查询返回一张表）
--1.先查询每个部门的最低工资
select deptno,min(sal) msal from emp group by deptno;
--2.三表联查，得到最终结果
select t.deptno,t.msal,e.ename,d.dname
from (select deptno,min(sal) msal from emp group by deptno) t,emp e,dept d
where t.msal = e.sal and t.deptno = e.deptno and e.deptno = d.deptno

• oracle中的分页
  rownum行号：当我们做select操作的时候，每查询出一行记录，就会在该行上加一个行号，行号从1开始，依次递增，不能跳着走。

--排序操作会影响rownum的顺序
select rownum,e.* from emp e order by e.sal desc;
--如果涉及到排序，但是还要使用rownum的话，我们可以再次嵌套查询
select rownum,t.* from (select rownum,e.* from emp e order by e.sal desc) t;
--mep表工资倒叙排列后，每页五条记录，查询第二页。
select * from (
       select rownum rn,t.* from (
              select rownum,e.* from emp e order by e.sal desc
       ) t where rownum <11
)where rn>5

• 视图
  视图的概念：视图就是提供一个查询的窗口，所有数据来自于原表。
  视图的作用？
  1.视图可以屏蔽掉一些敏感字段。
  2.保证总部和分部数据及时统一。

--创建视图[必须要有dba权限]
create view v_emp as select ename,job from emp;
--查询视图
select * from v_emp;
--修改视图[不推荐]
update v_emp set job='CLERK' where ename = 'ALLEN';
--创建只读视图
create view v_empl as select ename,job from emp with read only;

• 索引
  索引的概念：索引就是在表的列上构建一个二叉树，以达到大幅度提高查询效率的目的，但是索引会影响增删改的效率。

--创建单列索引
create index idx_ename on emp(ename);
--单列索引触发规则，条件必须是索引列中的原始值。
--单行函数，模糊查询都会影响索引的触发。
--创建复合索引
create index idx_enamejob emp(ename,job);
--复合索引中第一列为优先检索列
--如果要触发复合索引，必须包含有优先检索列中的原始值，
select * from emp where ename = 'SCOTT' and job='xx';--触发复合索引
select * from emp where ename = 'SCOTT' or job='xx';--不触发索引
select * from emp where ename = 'SCOTT';--触发单列索引