Oracle入门文档(一)

--创建表空间

create tablespace varlor

datafile 'c:\varlor.dbf'

size 100m·

autoextend on

next 10m;

--删除表空间

drop tablespace varlor;

--创建用户

create user varlor

identified by root

default tablespace varlor;

 

--给用户授权

--oracle数据库中常用角色

connect --连接角色，基本角色

resource--开发者角色

dba--超级管理员角色

--给varlor用户授予dba角色

grant dba to varlor;

 

--切换到varlor用户下

--顶栏session选项卡log off退出，再进入session选项卡log on登录varlor用户

 

--查看Oracle数据库编码

select userenv('language') from dual;

--修改表结构

--添加一列

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;

--查询表中记录

select * from person;

--添加一条记录

insert into person (pid,pname) values (1,'韩美娟');

commit;

--三个删除

--删除表中全部记录

delete from person;

--删除表结构

drop table person;

--先删除表，再次创建表，效果等于删除表中全部记录

--在数据量大的情况下，尤其是表中带有索引的情况下，该操作效率高

--索引可以提高查询效率，但是会影响增删改效率

truncate table person;

 

--序列不是真的属于任何一张表，但是可以逻辑和表做绑定

--序列，默认从1开始，依次递增，主要用来对主键赋值使用

--dual：虚表，只是为了补全语法，没有任何意义。

create sequence s_person;

select s_person.currval from dual;

--添加一条记录

insert into person (pid,pname) values (s_person.nextval,'韩美娟');

select * from person;

 

 

--scott用户，口令为tiger。

--解锁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(26.18) from dual;--四舍五入，后面的参数表示保留的位数，正数表示往后保留的位数，负数表示往前保留的位数

select round(26.18,1) from dual;

select trunc(56.16) from dual;--直接截取，不再看后面位数的数字是否大于5

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(sysdate,e.hiredate) from emp e;

----查询出EMP表中所有员工入职距离现在几年

select months_between(sysdate,e.hiredate)/12 from emp e;

----查询出EMP表中所有员工入职距离现在几周(整数)

select round((sysdate-e.hiredate)/7) from emp e;

----转化函数

----日期转字符串

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

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

----字符串转日期

select to_date('2020-3-19 0:51:1','fm yyyy-mm-dd hh24:mi:ss') from dual;

----通用函数

----算出EMP表中所有员工的年薪

----奖金中有NULL值，如果NULL和任意数值进行算数计算，结果都是NULL

----nvl函数，如果数值不是null值，就用第一个参数的值，如果数值是null值，就用第二个参数的值

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

 

----条件表达式

----条件表达式通用写法，Oracle和Mysql通用

----给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 count(*) 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后面出现必须加上聚合函数

----聚合函数可以把多行记录变成一个值

select e.deptno,avg(e.sal)

from emp e

group by e.deptno;

 

----查询出平均工资高于2000的部门

select e.deptno,avg(e.sal) asal

from emp e

group by e.deptno

having avg(e.sal)>2000;

 

----所有条件都不能使用别名来判断,以下语句错误

select ename,sal s from emp where s>1500;

 

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

select e.deptno,avg(e.sal) asal

from emp e

where e.sal>800

group by e.deptno;

----where是过滤分组前的数据，having过滤分组后的数据

----表现形式：where必须在group by之前，having是在之后。

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

----然后再查询出平均工资高于2000的部门

select e.deptno,avg(e.sal) asal

from emp e

where e.sal>800

group by e.deptno

having avg(e.sal)>2000;

 

 

----多表查询中的概念

----笛卡尔积

select *

from emp e,dept d;

----等值连接

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;

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

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;

----子查询

----子查询返回一个值

------查询出工资和scott一样的员工信息

select * from emp where sal=(select sal from emp where ename='SCOTT');

 

----子查询返回一个集合

------查询出工资和10号部门任意员工一样的员工信息(建议使用in而不是等号)

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.deptno=e.deptno

and t.msal=e.sal

and e.deptno=d.deptno;

 

----oracle中的分页

----rownum行号：当我们做select操作的时候

----每查询出一行记录就会在该行上加上一个行号，

----行号从1开始，不能跳着走，依次递增

-----emp表工资倒序排列后

-----排序操作会影响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;

 

-----emp表工资倒序排列后，每页五条记录，查询第二页

-----rownum行号不能写上大于一个正数

select * from(

       select rownum rn,e.*from(

              select * from emp order by sal desc)

                     e where rownum<11)

                             where rn>5;

 

 

---视图

---视图的概念：视图就是提供一个查询的窗口，所有数据来自于原表

 

---查询语句创建表

create table emp as select * from scott.emp;

select * from emp;

---创建视图【必须有dba权限】

create view v_emp as select ename,job from emp;

---查询视图

select * from v_emp;

---修改视图【不推荐】

update v_emp set job='CLERK' where ename='ALLEN';

commit;

---创建只读视图

create view v_emp as select ename,job from emp with read only;

---视图的作用

---第一：视图可以屏蔽掉敏感字段。

---第二：保证总部和分布数据及时统一。

 

---索引

---索引的概念：就是在表的列上构建一个二叉树

---达到大幅度提高查询效率的目的，但是索引会影响增删改的效率。

---单列索引

----创建单列索引

create index idx_ename on emp(ename);

----单列索引触发规则，条件必须是索引列中的原始值。

----单行函数，模糊查询，都会影响索引的触发

select * from emp where ename='SCOTT';

----复合索引

----创建复合索引

create index idx_enamejob on 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';----触发单列索引