Oracle数据库之——入门

发表于 更新于 分类于 阅读次数： 本文字数： 8.6k 阅读时长 ≈ 8 分钟

oracle介绍

ORACLE 数据库系统是美国 ORACLE 公司（甲骨文）提供的以分布式数据库为核心的一组
软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或 B/S 体系结构的数据库之一。比如
SilverStream 就是基于数据库的一种中间件。ORACLE 数据库是目前世界上使用最为广泛的数据
库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，
它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。但它的所有知识，只
要在一种机型上学习了 ORACLE 知识，便能在各种类型的机器上使用它。

Oracle 数据库是数据的物理存储。这就包括（数据文件 ORA 或者 DBF、控制文件、联机日志、参数文件）。
其实 Oracle 数据库的概念和其它数据库不一样
这里的数据库是一个操作系统只有一个库。
可以看作是 Oracle 就只有一个大数据库。

实例

一个 Oracle 实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构
（Memory Structures)组成。一个数据库可以有 n 个实例。

用户

用户是在实例下建立的。不同实例可以建相同名字的用户。

表空间

表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件）的逻辑映射。一个数
据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每
个数据库至少有一个表空间(称之为 system 表空间)。
每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件
只能属于一个表空间。

Oracle数据库结构

Oracle数据库结构

创建表空间

什么是表空间？ ORACLE 数据库的逻辑单元。 数据库—表空间 一个表空间可以与多个数据文件（物理结构）关联

  • 一个数据库下可以建立多个表空间
  • 一个表空间可以建立多个用户
  • 一个用户下可以建立多个表
1
2
3
4
5
create tablespace itcast
datafile 'c:\itcast.dbf'
size 100m
autoextend on
next 10m
  • itcast：为表空间名称
  • datafile：指定表空间对应的数据文件
  • size：后定义的是表空间的初始大小
  • autoextend on：自动增长 ，当表空间存储都占满时，自动增长
  • next：后指定的是一次自动增长的大小。

删除表空间

1
drop tablespace itheima;

用户

创建用户

1
2
3
create user 用户名
identified by 用户密码
default tablespace 表空间名称

oracle 数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户下的。

用户赋权限

新创建的用户没有任何权限，登陆后会有提示

Oracle 中已存在三个重要的角色：connect 角色，resource 角色，dba 角色。

  • CONNECT 角色： –是授予最终用户的典型权利，最基本的
    • ALTER SESSION –修改会话
    • CREATE CLUSTER –建立聚簇
    • CREATE DATABASE LINK –建立数据库链接
    • CREATE SEQUENCE –建立序列
    • CREATE SESSION –建立会话
    • CREATE SYNONYM –建立同义词
    • CREATE VIEW –建立视图
  • RESOURCE 角色： –是授予开发人员的
    • CREATE CLUSTER –建立聚簇
    • CREATE PROCEDURE –建立过程
    • CREATE SEQUENCE –建立序列
    • CREATE TABLE –建表
    • CREATE TRIGGER –建立触发器
    • CREATE TYPE –建立类型
  • DBA 角色：拥有全部特权，是系统最高权限，只有 DBA 才可以创建数据库结构，并且系统权限也需要 DBA 授出，且 DBA 用户可以操作全体用户的任意基表，包括删除
1
grant dba to 用户名称

进入 system 用户下给用户赋予 dba 权限，否则无法正常登陆

Oracle数据类型

  • Varchar，varchar2：表示一个字符串
  • NUMBER：
    • NUMBER(n)：表示一个整数，长度是 n
    • NUMBER(m,n)：表示一个小数，总长度是 m，小数是 n，整数是 m-n
  • DATA：表示日期类型
  • CLOB：大对象，表示大文本数据类型，可存 4G
  • BLOB：大对象，表示二进制数据，可存 4G

表的管理

建表

语法：

1
2
3
4
5
6
Create table 表名(
字段 1 数据类型 [default 默认值],
字段 2 数据类型 [default 默认值],
...
字段 n 数据类型 [default 默认值]
);

例子：创建 person 表，并且插入一条数据

1
2
3
4
5
6
7
8
create table person(
	pid number(10),
	name varchar2(10),
	gender number(1) default 1,
	birthday date
);
insert into person(pid, name, gender, birthday)
values(1, '张三', 1, to_date('1999-12-22', 'yyyy-MM-dd'));

表删除

语法：DROP TABLE 表名

表的修改

在 sql 中使用 alter 可以修改表

  • 添加语法：ALTER TABLE 表名称 ADD(列名 1 类型 [DEFAULT 默认值]，列名 1 类型[DEFAULT 默认值]…)
  • 修改语法：ALTER TABLE 表名称 MODIFY(列名 1 类型 [DEFAULT 默认值]，列名 1 类型[DEFAULT 默认值]…)
  • 修改列名: ALTER TABLE 表名称 RENAME 列名 1 TO 列名 2

例子：在 person 表中增加列 address

1
alter table person add(address varchar2(10));

例子：把 person 表的 address 列的长度修改成 20 长度

1
alter table person modify(address varchar2(20));

—删除一列

1
alter table person drop column sex;

—修改列类型

1
alter table person modify gender char(1);

数据库表中数据的增删改

INSERT（增加）

  • 标准写法：
    1
    INSERT INTO 表名[(列名 1，列名 2，...)]VALUES(值 1，值 2，...)
  • 简单写法（不建议）
    1
    INSERT INTO 表名 VALUES(值 1，值 2，...)
    注意：使用简单的写法必须按照表中的字段的顺序来插入值，而且如果有为空的字段使用 null
    1
    2
    insert into person (pid, pname) values (1, '小明');
    commit;

UPDATE（修改）

  • 全部修改：UPDATE 表名 SET 列名 1=值 1，列名 2=值 2，….
  • 局部修改：UPDATE 表名 SET 列名 1=值 1，列名 2=值 2，….WHERE 修改条件；
1
2
update person set pname = '小马' where pid = 1;
commit;

DELETE（删除）

  • 语法 : DELETE FROM 表名 WHERE 删除条件;

在删除语句中如果不指定删除条件的话就会删除所有的数据
因为 oracle 的事务对数据库的变更的处理，我们必须做提交事务才能让数据真正的插入到数
据库中，在同样在执行完数据库变更的操作后还可以把事务进行回滚，这样就不会插入到数据
库。如果事务提交后则不可以再回滚。

  • 提交：commit
  • 回滚：rollback
1
2
3
4
5
6
7
8
9
10
11
12
--删除表中全部记录
delete from person;


--删除表结构
drop table person;


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

序列

在很多数据库中都存在一个自动增长的列,如果现在要想在 oracle 中完成自动增长的功能,
则只能依靠序列完成,所有的自动增长操作,需要用户手工完成处理。

—-序列不真的属于任何一张表，但是可以逻辑和表做绑定。
—-序列：默认从1开始，依次递增，主要用来给主键赋值使用。

  • 语法：CREATE SEQUENCE 序列名
    [INCREMENT BY n]
    [START WITH n]
    [{MAXVALUE/ MINVALUE n|NOMAXVALUE}]
    [{CYCLE|NOCYCLE}]
    [{CACHE n|NOCACHE}];

  • 例子:创建一个 seqpersonid 的序列,验证自动增长的操作

1
2
3
4
5
6
7
8
9
10
11
12
CREATE SEQUENCE seqpersonid;


序列创建完成之后,所有的自动增长应该由用户自己处理,所以在序列中提供了以下的两种操作：
nextval :取得序列的下一个内容
currval :取得序列的当前内容

select seqpersonid.nextval from dual;
select seqpersonid.currval from dual;
在插入数据时需要自增的主键中可以这样使用

insert into person values(seqpersonid.nextval, "张三", 1, null, "江西省");
  • 例子2
    1
    2
    3
    4
    5
    6
    7
    ----dual：虚表，只是为了补全语法，没有任何意义。
    create sequence s_person;
    select s_person.nextval from dual;
    ----添加一条记录
    insert into person (pid, pname) values (s_person.nextval, '小明');
    commit;
    select * from person;

在实际项目中每一张表会配一个序列，但是表和序列是没有必然的联系的，一个序列被哪
一张表使用都可以，但是我们一般都是一张表用一个序列。
序列的管理一般使用工具来管理。

Scott用户

Scott用户下的表结构

Scott用户结构1
Scott用户结构2

Scott用户的使用

Scott用户的默认密码为tiger

  • 解锁scott用户
    1
    alter user scott account unlock;
  • 解锁scott用户的密码【此句也可以用来重置密码】
    1
    alter user scott identified by tiger;

单行函数

字符函数

  • 把小写的字符转换成大小的字符
    1
    select upper('yes') from dual;--结果为YES
  • 把大写字符变成小写字符
    1
    select lower('YES') from dual;--结果为yes

数值函数

1
2
3
4
select round(56.16, -2) from dual;	---四舍五入，后面的参数表示保留的位数
select trunc(56.16, -1) from dual;	---直接截取，不在看后面位数的数字是否大于5.
select mod(10, 3) from dual;	---求余数

日期函数

获得两个时间段中的月数：MONTHS_BETWEEN()

  • 查询出emp表中所有员工入职距离现在几天。
    1
    select sysdate-e.hiredate from emp e;
  • 算出明天此刻
    1
    select sysdate+1 from dual;
  • 查询出emp表中所有员工入职距离现在几月。
    1
    select months_between(sysdate,e.hiredate) from emp e;
  • 查询出emp表中所有员工入职距离现在几年。
    1
    select months_between(sysdate,e.hiredate)/12 from emp e;
  • 查询出emp表中所有员工入职距离现在几周。
    1
    select round((sysdate-e.hiredate)/7) from emp e;

转换函数

  • 日期转字符串
    1
    select to_char(sysdate, 'fm yyyy-mm-dd hh24:mi:ss') from dual;
  • 字符串转日期
    1
    select to_date('2018-6-7 16:39:50', 'fm yyyy-mm-dd hh24:mi:ss') from dual;
    ps:24为24小时制

通用函数

条件表达式的通用写法，mysql和oracle通用

ps：oracle中除了起别名，都用单引号。

  • 给emp表中员工起中文名

    1
    2
    3
    4
    5
    6
    7
    8
    select e.ename, 
           case e.ename
             when 'SMITH' then '曹贼'
               when 'ALLEN' then '大耳贼'
                 when 'WARD' then '诸葛小儿'
                   --else '无名'
                     end
    from emp e;

  • 判断emp表中员工工资，如果高于3000显示高收入，如果高于1500低于3000显示中等收入，其余显示低收入

    1
    2
    3
    4
    5
    6
    7
    select e.sal, 
           case 
             when e.sal>3000 then '高收入'
               when e.sal>1500 then '中等收入'
                   else '低收入'
                     end
    from emp e;

  • oracle专用条件表达式（不用这个，用通用的）

    1
    2
    3
    4
    5
    6
    7
    select e.ename, 
            decode(e.ename,
              'SMITH',  '曹贼',
                'ALLEN',  '大耳贼',
                  'WARD',  '诸葛小儿',
                    '无名') "中文名"             
    from emp e;

空值处理 nvl

例子：查询所有的雇员的年薪

我们发现很多员工的年薪是空的，原因是很多员工的奖金是 null，null 和任何数值计算都是
null，这时我们可以使用 nvl 来处理。

1
select ename, nvl(comm, 0), sal * 12 + nvl(comm, 0) from emp;

多行函数【聚合函数】

作用于多行，返回一个值。

1
2
3
4
5
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后面，必须加上聚合函数。
聚合函数有一个特性，可以把多行记录变成一个值。

1
2
3
select e.deptno, avg(e.sal)--, e.ename
from emp e
group by e.deptno;

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

1
2
3
4
select e.deptno, avg(e.sal) asal
from emp e
group by e.deptno
having avg(e.sal)>2000;

所有条件都不能使用别名来判断。
比如下面的条件语句也不能使用别名当条件

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

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

1
2
3
4
select e.deptno, avg(e.sal) asal
from emp e
where e.sal>800
group by e.deptno;

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

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

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

1
2
3
4
5
select e.deptno, avg(e.sal) asal
from emp e
where e.sal>800
group by e.deptno
having avg(e.sal)>2000;

多表查询

笛卡尔积概念

  • 首先我们查询员工和部门表
    1
    2
    select *
    from emp e, dept d;

我们发现产生的记录数是 56 条，我们还会发现 emp 表是 14 条，dept 表是 4 条，
56 正是 emp表和 dept 表的记录数的乘积，我们称其为笛卡尔积。

如果多张表进行一起查询而且每张表的数据很大的话笛卡尔积就会变得非常大，对性能造
成影响，想要去掉笛卡尔积我们需要关联查询。

在两张表中我们发现有一个共同的字段是 deptno，deptno（部门） 就是两张表的关联的字段，我们可
以使用这个字段来做限制条件，两张表的关联查询字段一般是其中一张表的主键，另一张表的外键。

关联之后我们发现数据条数是 14 条，不在是 56 条

等值连接

1
2
3
select *
from emp e, dept d
where e.deptno=d.deptno;

内连接

1
2
3
select *
from emp e inner join dept d
on e.deptno = d.deptno;

查询出所有部门，以及部门下的员工信息。【外连接】

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

查询所有员工信息，以及员工所属部门

1
2
3
select *
from emp e left join dept d
on e.deptno=d.deptno;

oracle中专用外连接(一般不用)

1
2
3
4
5
select *
from emp e, dept d
where e.deptno(+) = d.deptno;

select * from emp;

查询出员工姓名，员工领导姓名

  • 自连接：自连接其实就是站在不同的角度把一张表看成多张表。
1
2
3
select e1.ename, e2.ename
from emp e1, emp e2
where e1.mgr = e2.empno;

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

1
2
3
4
5
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一样的员工信息
    1
    2
    select * from emp where sal in
    (select sal from emp where ename = 'SCOTT')

子查询返回一个集合

  • 查询出工资和10号部门任意员工一样的员工信息
    1
    2
    select * from emp where sal in
    (select sal from emp where deptno = 10);

子查询返回一张表

  • 查询出每个部门最低工资，和最低工资员工姓名，和该员工所在部门名称
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    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开始，依次递增，不能跳着走。

  • 排序操作会影响rownum的顺序

    1
    2
    3
    4
    5
    select rownum, e.* from emp e order by e.sal desc;

    查出来是123
    排序后行号位置也跟着变
    可能就变成了231

  • 如果涉及到排序，但是还要使用rownum的话，我们可以再次嵌套查询。

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

  • 例子：emp表工资倒叙排列后，每页五条记录，查询第二页。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    select * from(
        select rownum rn, tt.* from(
              select * from emp order by sal desc
        ) tt where rownum<11
    ) where rn>5



    此处rn>5不能写在里面先判断，因为

    select rownum rn, tt.* from(
              select * from emp order by sal desc
        ) tt where rownum<11

    上面这个语句中，Oracle是先执行条件判断然后再把rownum + 1的，当我们里面写上rownum>5时，如果当时rownum为1，那么条件不成立，我们就达不到预期的目的了