Oracle数据库

Oracle数据库是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小、微机环境。Oracle数据库的主要内容包括：表空间、用户权限、四种约束等基础概念，DDL操作数据库和表，DML操纵表数据，多表查询、分页查询，子查询等查询语句学习。

一、Oracle基础知识

1、启动、关闭数据库

打开“服务”窗口，启动相应的Oracle服务

OracleService<SID>：数据库服务

OracleOraDb11g_home1TNSListener ：数据库监听服务

OracleDBConsole<SID>：企业管理器服务

2、oracle网络服务配置

服务器端配置监听listener.ora 直接修改文件或者net manager工具配置

客户端配置网络服务名  tnsnames.ora

3、连接数据库

命令行：SQL*Plus方式连接

可视化工具：PL/SQL Developer方式连接

4、表空间

表空间里ORACLE中的一个逻辑概念，它用于存放数据库文件，而数据库文件是存放在磁盘的数据文件，是物理概念，表空间里是后台的一个内存区域，通过它来连接到数据库文件，表空间只是一个虚拟的空间，它没有实际的大小，表空间是恢复数据库的最小单元，表空间不能单独存在，它至少应该包涵一个数据库文件，可以把具有相同数据结构的数据库文件同时放到一个表空间里，而表空间用来逻辑的加以管理这些文件，可以想象成ORACLE是一个柜子，而表空间就是柜子上的空抽屉，而数据库文件就是文件夹，它放在抽屉里

表：最小的逻辑单位

表空间：最大的逻辑单位

表空间作用

方便存储管理、提高I/O性能、备份和恢复

表空间分类：永久性、临时性、撤销表空间

创建表空间：

create tablespace t40 datafile ‘e:\app\Visant\oradata\orcl\t4001.DBF’size 50m autoextend on NEXT 32M MAXSIZE UNLIMITED；

删除表空间 Drop tablespace t40 including contents and datafiles;

5、用户 

创建：create user t40 identified by t40 default tablespace t40;

角色（管理员和用户）是具有名称的一组权限的组合

常用系统预定义角色：

CONNECT：临时用户

RESOURCE：更为可靠和正式的用户

DBA：数据库管理员角色，拥有管理数据库的最高权限

授权命令 grant connect,resource to t40;

grant all privileges to t40;

撤销权限：revoke privileges or role from user;

6、常用数据类型

字符型：char\varchar2\long（注意long不是数值型）

数值型：number(p,s)

日期：date\timestamp

Lob类型：blob（二进制对象） clob（字符格式大型对象）

Bfile（二进制数据存储到操作系统里）

7、数据库表的操作(DDL语言)

创建表：

create table teacher (tno number(4) not null)；

修改表：

SQL> alter table teacher add( #添加列

sal number(7,2),hirdate date,wechat varchar2(30));

SQL> alter table teacher modify(tname varchar2(30));  #修改列

SQL> alter table teacher rename column gendar to gender#修改列名

SQL> alter table teacher drop column wechat;  #删除列

查看表：SQL> desc teacher;

添加表数据：

insert into teacher(tno,tname,tid,gender)values(1,’zs’,’111’,’男’)

commit; #必须提交才有效

8、四种完整性约束

实体完整性：唯一约束（列唯一，可为空，但只能有一个空值）

主键约束（主键列数据唯一，不能为空）

域完整性：限制数据类型、检查约束、外键约束、非空约束

引用完整性：外键约束

自定义完整性：规则、存储过程、触发器

9、约束命令

添加四种约束

SQL> alter table teacher add constraint pk_teacher primary key(tno);

SQL> alter table teacher add constraint uk_teacher_tname unique(tname);

SQL> alter table teacher add constraint ck_teacher_gender check(gender in(‘男’,’女’));

SQL> alter table teacher add constraint fk_teaccher_deptno_dept foreign key(deptno) references dept(deptno);

删除约束

alter table teacher drop constraint ck_gendar;

10、备份与恢复

EXP命令导出数据：

exp t40/t40 file=” E:\app\Visant\oradata\orcl\t40exp.dmp”

IMP命令导入数据：

imp t40/t40 file=” E:\app\Visant\oradata\orcl\t40imp.exp.”

导入导出数据库对象

四种模式：完全数据库、表空间、表、用户

二、SQL编程

1、分为四类：

DDL定义\DML(DQL)操纵、查询\DCL数据控制\TCL事务控制

DDL关键字：CREATE、DROP、ALTER、TRUNCATE

DML关键字：INSERT、UPDATE、DELETE、SELECT

TCL关键字：COMMIT、ROLLBACK、SAVEPOINT;

数据库操作：创建create、查看show、使用use、删除drop

表操作：创建create、查看show\desc、修改alter set（字段修改add\modify\change\drop）

删除drop

表数据操作：添加insert、修改update、删除delete\truncate

查询select

2、DML操作：

建序列，添加数据。修改数据。删除数据。查询数据。Commit！or Rollback!

create sequence sq_teacher start with 1004 increment by 1 nomaxvalue;   #创建序列，并添加数据

select sq_teacher.nextval from dual; #初始化序列（先获取下一序列）

select sq_teacher.currval from dual;   #获得当前序列

insert into teacher(tno,tname,gendar,sal)

values(sq_teacher.currval,’王老师’,’男’，5000);  commit;  #添加数据

#修改数据

update teacher set sal=sal+2000 where tname in(‘陈老师’，’韩老师’);  commit;  #修改数据

drop sequence sq_name;  #删除序列

rollback； #回退

#删除数据

delete from dept;

truncate table dept; 一旦主键被引用，就无法删除。一旦删除，无法恢复

drop table teacher;

#查询数据

select * from teacher where job=’研发’ order by sal desc#降序排列

#字符串拼接

select ‘insert into dept values(‘||deptno||’,”’|| dname

||”’,”’||loc||”’);’ from  dept

#字符串引号，语句中用双引号(相当于结果中的1个单引号)

3、子查询

select t.tname,

t.deptno,

(select dname from dept where dept.deptno = t.deptno) as deptname    #子查询在列里

from teacher t

where t.deptno in (select deptno  #子查询在条件里

from dept

where dept.deptno = t.deptno

and dept.dname in (‘招生部’, ‘人力部’))

4、多表连接查询

内连接（等值连接、不等值连接、自然连接）、外连接（左连接、右连接）

自连接、交叉连接

–招生部门所有男老师的姓名（内连接）

Select t.Tname， t.Deptno ，d.Dname From Teacher t

Inner Join Dept d  On t.Deptno = d.Deptno

Where d.Dname = ‘招生部’ And t.Gender = ‘男’

–所有老师姓名、部门（外连接）

Select t.Tname， t.Deptno ，d.Dname From Teacher t

left Join Dept d On t.Deptno = d.Deptno

–所有老师的姓名以及其导师姓名（自连接）

Select T1.Tname, T2.Tname As 导师 From Teacher T1

Left Join Teacher T2 On T1.Mgrno = T2.Tno

5、SQL函数  执行特定操作的函数

分类：单行函数、聚合函数、分析函数

单行函数：查询的每一行只返回一个值。

分类：数字、字符、日期、转换、其他函数

–四舍五入函数

Select Round (sal,1) From teacher

–字符函数(substr函数从1开始，截取2位 trim去空格)

Select Substr(Tid, 1, 2),  Substr(Tid, 0, 2) ，substr(Tid, 1),

Length(Trim(Tid)) From Teacher

–日期函数(dual 哑表)（通过计算获得想要的时间）

Select Sysdate+1/4  From dual

–转换函数

Select To_Char(111111,’999,999’),

To_Number(‘112’),

To_Date(‘1986-12-12’, ‘yyyy-mm-dd’),

to_char (Sysdate，’yyyy-mm-dd hh24:mi:ss’)

From Dual

–nvl转换函数(nvl2类似于条件表达式)(decode类似于java中的switch)

Select t.Sal, t.Comm,

(t.Sal + Nvl(t.Comm, 0)) 工资，

(t.Sal + Nvl2(t.Comm，comm+1000， 0 )) 工资2，

decode(t.Comm，null，0 ，2300 ，2500 ，t.comm ) 工资3

From Teacher t

聚合函数：基于一组行（一列行）来返一个结果

常用：avg、min、max、sum、count

子句关键字：GROUP BY子句 HAVING子句

–查看部门员工超过10人以上的数据(having 后面必须跟聚合函数)

select deptno ,Sum (sal),Avg(sal),Count(0),Max(sal),Min(sal)

From teacher

Group By deptno

having count(*) >10     –sal>1000(错误的)

分析函数：根据一组行来计算聚合值。用于计算完成聚集的累计排名等

–分析函数（三个函数排名的不同）

select tname,deptno,

rank() over(partition by deptno order by sal) rank,–rank名

dense_rank() over(partition by deptno order by sal) dense_rank,

row_number() over(partition by deptno order by sal) row_number

from teacher

6、联合查询：集合操作符将两个查询的结果组合成一个结果

INTERSECT：返回两个查询共有的记录

UNION\UNION ALL:返回各个查询的所有记录 union可以去重

MINUS:返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录

7、分页查询：

伪列：伪列就像一个表列，但是它并没有存储在表中。伪列可以从表中查询，但不能插入、更新和删除它们的值

ROWID 是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用ROWID 伪列快速地定位表中的一行

ROWNUM 是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数

–查询前五名(rownum基于结果集的查询，从1开始)

select rownum,tname from teacher where rownum<=5

–查询第五名到第十名（用到子查询）

select * from

(select  rownum  as rn ,tname from teacher where rownum<=10) t

where t.rn >=5

–获取教师表中薪水排名第五的教师信息

select *from

(select teacher.*, dense_rank() over(order by sal desc) rank from teacher)

where rank = 5;（注意是分析函数）

–分页思路

–第一层：加限制条件，得出结果集

–第二层：给结果集过滤最大的范围

–第三层：给结果集过滤出最小的范围（从第几条开始）

select t1 from

(select t.*,rownum rn from

(select tname,gender,sal from teacher order by sal desc) t

where rownum<=pageIndex*pageSize and gender=’男’) t1 where t1.rn>(pageIndex-1)*pageSize