Oracle入门笔记

–创建表空间

create tablespace zwan 
datafile 'F:\oracle\datafile\zwan.dbf'
size 100m
autoextend on
next 10m;

–删除表空间

drop tablespace zwan;

–创建用户

create user zwan
identified by beatles0404
default tablespace zwan;

–给用户授权
–oracle数据库中常用角色
connect–连接角色，基本角色
resource–开发者角色
dba–超级管理员角色
–给zwan用户授予dbajues

grant dba to zwan;

–创建一个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;

–添加一条记录

insert into person(pid,pname) values (1,'小明');
commit;

–修改一条数据

update person set pname = '小马' where pid=1;
commit;

select userenv('language') from dual;
select * from V$NLS_PARAMETERS;
select * from person;

–三个删除
–删除表中全部记录

delete from person;

–删除表结构

drop table person;

–先删除表，再次创建表。效果等同删除表中全部记录。
–在数据量大的情况下，尤其在表中带有索引的情况下，该操作效率高。

truncate table person;

–序列：默认从1开始依次递增，主要用来给主键赋值。
–序列不真的始于任何一张表，但可以逻辑和表做绑定。
–dual:虚表，只是用来不全语法，无实际意义。
–回滚时，序列值只要增加过就不会回滚到上一值。

create sequence s_person;
select s_person.nextval from dual;

–使用序列添加一条记录

insert into person (pid,pname) values (s_person.nextval,'张三');
commit;

–scott用户，密码tiger。
–解锁scott用户

alter user scott account unlock;

–解锁scott用户的密码(此句也可用来重置密码)

alter user scott identified by tiger;

–单行函数：作用与一行，返回一个值。、
–字符函数

select upper('yes') from dual;--YES
select upper('YES') from dual;--yes

–数值函数

sekect round(26.18,1) from dual;**--四舍五入**
select trunc(56.16,1) from dual;**--直接截取**
select mod(10,3) from dual;**--求余数**

–日期函数
–查询出emp表中所有员工入职距离现在几天。

select sysdate-e.hiredate from emp e;

–算出明天此刻

select sysdate+1 from dual;

–查询出emp表中所有员工入职距离现在几月。

select months_between(emp.hiredate,sysdate) from emp;

–转换函数
–日期转字符串

select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss') from dual;

–字符串转日期

select to_date('2020-1-3 13:45:6','fm yyyy-mm-dd hh24:mi:ss')from dual;

–通用函数
–算出emp表中所有员工的年薪
–奖金里面有null值，如果null值和任意数字做算术运算结果都是null

select e.sal*12+nvl(e.comm,0) from emp e;

–条件表达式
–给emp表中员工起中文名

select e.ename,
       case e.ename
         when 'SMITH' then '张三'
            else '无名'
                 end
from emp e;

–给员工排收入等级

select e.sal,
       (case
         when e.sal>3000 then '高收入'
           when e.sal>1500 then '中收入'
             else '低收入'
               end) 收入等级
from emp e;

–多行函数：作用与多行，返回一个值。

select count(1) from emp;

sum()
max()
min()
avg()

–分组查询
–查询出每个部门的评价工资
–分组查询中，出现在group by后面的原始列，才能出现在select后面
–没有出现在group by后面的列，像出现在select后面，必须加上聚合函数
–分组查询的是一组数据的共同特性

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;

–所有的条件都不可用别名判断
–因为where或having优先级高于select，故不知别名为何。

–查询每个部门工资高于800的员工的平均工资

select e.deptno,avg(e.sal)
from emp e
where e.sal > 800
group by e.deptno;

–where过滤分组前的数据，having过滤分组后的数据。

–查询每个部门工资高于800的员工的平均工资，再查询出平均工资高于2000的部门

select e.deptno,avg(e.sal)
from emp e
where e.sal>800
group by e.deptno
having avg(e.sal)>2000;

select * from emp;

–查询出所有部门，以及部门下的员工信息（使用外连接）

select *
from emp e right join dept d
on e.deptno=d.deptno;

–会查询右边dept中的所有数据，因此空40部门也会显示出来
–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;

–查询出员工姓名，员工部门名称，员工领导姓名，领导部门名称

select e1.ename,d1.dname,e2.ename,d2.dname
from emp e1,emp e2,dept d1,dept d2
where e1.mgr=e2.empno 
and e1.deptno=d1.deptno 
and e2.deptno=d2.deptno;

–oracle中的分页
–rownum行号：当我们做select操作的时候，没查询出一行记录，就会在该行上加上一个行号，行号从1开始，一次递增不能跳着走。
–emp表工资倒叙排列后，每页五条记录，查询第二页。
–排序操作会影响rownum的顺序

select rownum,t.* from(
       select rownum,e.* from emp e order by e.sal desc) t;

select rownum,t.* from(
       select e.* from emp e order by e.sal desc) t;
       

–rownum不能写上大于一个正数，因为rownum不能跳，而如果从1开始不大于此正数则rownum也不会增加

select rownum,t.* from(
       select e.* from emp e order by e.sal desc) t where rownum > 5;
              

–分页查询

select * from(
  select rownum ro,t.* from(
         select e.* from emp e order by e.sal desc
         ) t where rownum < 11
)where ro>5;

–视图
–创建视图（必须要有dba权限）

create view v_emp as select ename,job,from emp;

–查询视图

select * from v_emp;

–视图可以屏蔽掉某些敏感数据，同时还可以确保数据的统一

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

create index idx_ename on emp(ename);

–单列索引触发规则，条件必须是索引列中的原始值。
–单行函数，模糊查询，都会影响索引的触发

select * from emp where ename='SCOTT';

–复合索引

create index idx_enamejob on emp(ename,job);

–复合索引中第一列为优先检索列。如果要触发复合索引，必须包含有优先检索列中的原始值。