Oracle数据管理

第一章：Oracle数据管理第一节

1、Oracle数据库的概述

系统可移植性好，使用方便，功能性强，适用于大中小微机环境中。他是一种高效率、可靠性好适应高吞吐量的数据库解决方案；

2、如何启动和登录Oracle数据库

数据库一经打开就会消耗电脑的cpu和内存；数据库的启动关闭时日常的常规工作；

必须启动的OracleService服务，就可以满足数据处理的大部分需求

OracleorcltnsListener需要远程连接的情况下，需要启动此服务，一般情况下不启动此服务；

使用客户端连接数据库：必须启动监听服务；否则无法启动客户端；

1、OracleDBConsoleorcl：（非必须启动） 是Oracle的管理控制台（一般情况下开发者用不到）

2、OracleOraDbllg_home1TNSListener：（非必须启动，但在使用可视化界面plsql工具时必须启动，属于远程访问）数据库监听程序（监听器接收客户端发出请求–>将请求传递给服务器。以此来建立客户与服务器之间的连接）；（一个数据库只有一个监听）

本地连接不需要启动此服务（端口号：1521、1526）；

3、OracleServiceOF：

4、**OracleServiceORCL：**必须启动，服务所占资源比较大，一般设置为手动启动（ORCL为实例，是指运行中的内存块）；

5、OracleJobSchedulerORCL：（非必须启动）

注意：将所有的服务调整为手动；一般安装之后默认是自动的

启动方法：

1. 使用服务启动数据库：单击【此电脑】-【服务和应用程序】-【服务】-【OracleServiceORCL服务】-【启动】

2. 使用黑窗口启动服务：win+R输入cmd在弹出窗口内输入服务命令：启动服务的格式：net start Oracle 服务名称；；关闭服务的格式 net stop Oracle 服务名称；

3. 用文件格式打开服务器：

   创建一个TXT文件，里面写上启动服务的格式：net start Oracle 服务名称，再把后缀改成.bat格式，使用管理员身份运服务即可启动服务。关闭同理；

Oracle数据库的登录：

1. SYS用户：

   是数据库中的超级用户，主要用于维护系统信息和管理实例，数据库中数据字典的所有表和视图都储存在SYS模式中；【一般开发人员用不到SYS用户；了解即可】

2. SYSTEM用户：

   默认的管理员，他拥有DAB权限。该用户拥有数据库管理工具的内部表和试图，通常通过SYSTEM用户管理Oracle数据可的用户、权限和储存等‘

3. SCOTT用户:（实例账号; 一般刚开始Scott用户被锁定路径，要用system用户登录　　用以下语句解锁用户操作 Alter user Scott account unlock。含有四张默认表，用来学习使用；

4. 常用的有SYSTEM用户，SCOTT用户；

两种登录数据库的方式:

1. 使用SQL Plus 登录到Oracle数据库,SQL Plus 是Oracle 数据库内置的客户端 控制台管理的开发工具;要打开SQL Plus管理工具,可以选择【开始】-【Oracle-OraDbllg_home1’】-【SQL Plus】这个登录页面或者快捷键win+R 输入“sqlplus”命令运行登录页面。
2. 使用客户端【PL/SQL】连接数据库；

3、使用SQL命令管理数据表

SQL语言的分类：

1. 数据定义（Date Definition Language ,DDL）；

   用于创建、修改和删除数据库对象，如CREATE TABLE , ALTER TABLE ,DROP TABLE 等。DDL 语句会自动提交事务

2. 数据操纵 语言（Date Manipulation language ,DML）

   用于操纵数据库，包括 INSERT ,UPDATE, DELETE,SELECT等。DML语句不会自动提交事务；

3. 数据控制语言（Data Control Language ,DCL）

   用于执行授权和撤销权限的操作，包括GRANT（授予权限）RECOKE(撤销授权)

4. 事务控制语言（Transactional Control Language,TCL)

   用于维护数据的一致性，包括COMMIT(提交事务)，ROLLBACK（滚回事务)和SAVEPOINT（设置保存点）三条语句；

数据库建表方式

create table 表名（

列名1 数据类型 （约束）

列名2 数据类型 （约束）
.......
//约束可以统一写在建的表后面
Constraint 约束名称 约束类型......

）
与MySQL语句相比：语法相同，但是数据类型不同，约束的写法不同；

Oracle常用数据类型

• 字符类型：

  ​ Char 固定长度（浪费空间），最大长度2000字节。例如：char(10)，只用了2位，剩余的8位，用空格补全。

  ​ Varchar2() 可变长度的字符串类型，最大长度为4000字节，例如：varchar2(20)只用了6位，剩余会正常利用，避免资源浪费。使用长度与字符集的选择有很大的关系；

  一个utf-8的字符占据四个字节；4000自己只能存储1000个汉字；

• 数值类型：

  • Number§ 表示整形，长度由实际业务需求而定
  • Number(p,s) 表示小数类型，p表示精度（即数字的总长度），也就是小数的总长度，s表示小数点后精确到几位，例如：number(8,3)总长度位8,3表示精确到小数点后3位。

• 日期类型

  • Date 长度为7 个字节，分别为，世纪，年，月，日，时，分，秒。一般该类型就能满足所有的需求
  • Timpstamp 秒能够精确到小数点后6位，默认是6位；

• 大对象类型

  • Clob当长度大于4000，就可以用clob类型，最大容量为4G。
  • Blob存放二进制大对象数据类型，例如：图片，视频，音频等的二进制代码；

创建一个班级表：

-- 查询字节数；
select vsize('————') from dual ;-- dual 为一个虚表，数据库中没有表的时候也能查询表；
-- 查询表
select * from dept;
-- 创建一个班级数据表；
create table tab_class(
       class_no Number(6) primary key,
       class_name varchar2(200)
)
-- 创建学生表
create table tab_student(
       student_no Number(6) primary key,
       student_name varchar2(400),-- 字符类型与MySQL的不同"number","varchar","varchar2" ,"char"
       age number(3),
       sex char(2),
       class_id Number(6),
       constraint pk_classno foreign key(class_id) references tab_class(class_no),-- 创建外键约束
       constraint ckeck_sex check(sex='男' or sex = '女')-- 与MySQL不同的是含有字节约束
)

修改表中的内容 “ALTER TABLE命令”

向已经创建好的表中新增一列

alter table 表名 add 列名 数据类型；

修改表中指定列的数据类型和类型长度；

alter table 表名  列名 数据类型；

删除表中的指定列

alter table 表名 drop column 列名；

修改表的名字

alter table 原来表名 rename to 新表名

修改列的名字

alter table 表名 rename column 原来的列名 to 新的列名

给表添加约束类型

1、添加唯一约束 unique 或者主键约束 primary key

alter table 表名 add constrain 约束名 约束的类型（列名）。

2、添加外键约束 foreign key

alter table 表名	add constraint 约束名 foreign key (列名) reference 表名 （主键列）{关联表的主键}

3、删除表

drop table 数据表名;

4、删除外键约束：

alter table 表名 drop constraint 约束名;

Oracle数据库表中主键没有自增：

所有增删改事务是不会自动提交的，需要手动提交

4、使用SQL命令管理序列

**序列：（sequence）**是个命名的顺序编号生成器，他能以串行的方式生成一系列序列整数， 最大38位的整数序列数；

**语法：**create sequence 序列名 序列相关词语（序列与表之间没有任何关系）；

缓冲池在数据库断开时可能断号；

不同的对象都要有固定前缀，方便区分；

**查看或者使用序列的值：**需要用到序列的两个位列NEXTVAL（返回序列的下一个值）和CURRVAL（返回当前序列值）要引入一个虚表dual才能查看；

创建格式序列

create sequence 命名（seq_名字）
start with 1
increment by 1
=============================================
-- 如果nextval没有执行，那么currval也不能执行；
select seq_名字.currval from dual;
select seq_名字.nextval from dual;

5、使用SQL命令完成数据增删改查

添加一条数据

inset into table(列名，列名) values('zhi'..)

一次添加多条数据(根据查询结果集插入到表中)

insert into table
select 值，值 from dual
union
select 值，值 from dual
......

======================
insert into table select

新建一张表并且一次性转移数据

create table 表名 as select * from 表名（）

修改

update 表名 set 列名=列值 where 条件 （无条件修改这个列的所有值）

删除数据

delete 表名 where 条件
truncate table 表名 （清除表，无条件）

Oracle数据库查询

查询数据表中最大的数据（匹配多行单列）

select max(列名) from 表名;
select * from 表名 where 列=(select max(列名) from 表名);

查询某个条件中的最大或者最小（匹配多行单列）

select max(列名) from 表名 where 条件;
-- 查询出完整信息
select * from 表名 where 列值>(select max(列名) from 表名 where 条件);
或者
select * from 表名 where 列值>all(select 列名 from 表名 where 条件);

匹配多行多列

select * from 表名 where (列名，列名) in (
    select 列名，min(列名) as min别名
    from 表名
    group by 列名
);
-- 子查询进行检索
select b.* from 表名 b
join(
	select 列名，min(列名) as min别名
    from 表名
    group by 列名
) a on a.列名 = b.列名 and a.列名 = b.列名;

exists是指引用了父查询中的某些列的子查询，一般可以在select或者where语句中使用相关的子查询；

select * from A 
where exists (
	select * from B where 条件（必须和A表有关联；即：A.列值=B.列值）
)

分页

**rownum：**在Orac中代表行号，一般用作于分页

使用rownum进行分页，每一页五条数据

select * from (
	select a.*,rownum as rn from (
		select a.*
		from 表名 a
		order by 列名
	) a
) b
where rn>1 and rn<=5;

where语句中使用相关的子查询；

select * from A 
where exists (
	select * from B where 条件（必须和A表有关联；即：A.列值=B.列值）
)

分页

**rownum：**在Orac中代表行号，一般用作于分页

使用rownum进行分页，每一页五条数据

select * from (
	select a.*,rownum as rn from (
		select a.*
		from 表名 a
		order by 列名
	) a
) b
where rn>1 and rn<=5;