Oracle入门绝学

一、Oracle的那些年

1、Oracle是什么

• Oracle也称甲骨文，是世界级首批兴起的互联网公司之一，这里指的oracle是该公司的振兴之作Oracle数据库
• Oracle Database，又名Oracle RDBMS，或简称Oracle，它是在数据库领域一直处于领先地位的产品
• Oracle数据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小微机环境。
• 它是一种特大型数据存储、高效率的、可靠性好的、适应高吞吐量的数据库方案。

2、Oracle数据库的特点

• 数据的大量性
• 数据的保存的持久性
• 数据的共享性
• 数据的可靠性
• 完备关系的产品
  • 信息准则—-关系型DBMS的所有信息都应在逻辑上用一种方法，即表中的值显式地表示；
  • 保证访问的准则
  • 视图更新准则—-只要形成视图的表中的数据变化了，相应的视图中的数据同时变化
  • 数据物理性和逻辑性独立准则
  • 分布式处理功能：
• 用ORACLE能轻松的实现数据仓库的操作
  • 可用性强
  • 可扩展性强
  • 数据安全性强
  • 稳定性强
• 逻辑结构强大
  • 数据段：用来存放表数据；
  • 索引段：用来存放表索引；
  • 临时段：用来存放中间结果；
  • 回滚段：用于出现异常时，恢复事务；

2、为什么要学Oracle数据库

• Oracle是世界级流行的关系型数据库，应用广泛
• 可用性、安全性高，前期开发免费，后期专员维护，但维护付费
• 数据安全性高，数据支撑性强

二、Oracle保姆级安装

2.1、准备Oracle安装包

• 该教程以Oracle11g为例

2.1.1、下载Oracle安装包

• Oracle迅雷官方下载地址集合
  Oracle11gr2

2.1.2、上传虚拟机

• 在root目录下创建新的文件夹用于存放Oracle压缩包
• 上传方式一 ：通过XFtp将文件上传至虚拟机指定位置
• 上传方式二 ：使用lrzsz -y 直接将文件拖拽到XShell目录所在文件下

2.1.3、解压Oracle安装包

• 下载unzip配置

  [root@hg14150 /]# yum list | grep zip/unzip
  [root@hg14150 /]# yum install zip
  [root@hg14150 /]# yum install unzip

• 解压Oracle安装包

  [root@hg14150 soft]# unzip linux.x64_11gR2_database_1of7.zip
  [root@hg14150 soft]# unzip linux.x64_11gR2_database_2of7.zip

  

2.2、安装详细步骤

1、将解压的文件移动到root/oracle目录下

[root@localhost soft]# mv database/ /software/
[root@localhost soft]# cd /software
[root@localhost oracle]# ll

2、关闭selinux

• 设置SELINUX=disabled

  [root@localhost oracle]# vim /etc/selinux/config

  
  

3、关闭防火墙

#停止防火墙
systemctl stop firewalld
#关闭防火墙
systemctl disable firewalld
#重启防火墙
systemctl restart firewalld
#查看防火墙状态
systemctl status firewalld
#查看防火状态
firewall-cmd --state

4、安装Oracle 11g依赖包

[root@localhost oracle]# yum install gcc make binutils gcc-c++ compat-libstdc++-33elfutils-libelf-devel elfutils-libelf-devel-static ksh libaio libaio-develnumactl-devel sysstat unixODBC unixODBC-devel pcre-devel –y

5、添加安装用户

[root@localhost oracle]# groupadd oinstall
[root@localhost oracle]# groupadd dba

6、添加用户信息

[root@localhost oracle]# useradd -g oinstall -G dba oracle
[root@localhost oracle]# passwd oracle

[root@localhost oracle]# id oracle #查看oracle uid信息

7、修改内核参数配置文件

[root@localhost oracle]# vim /etc/sysctl.conf
-----在sysctl.conf末尾添加以下内容
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 1073741824
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576 
#kernel.shmmax = 1073741824为本机物理内存（2G）的一半，单位为byte 1*1024*1024*1024
#查看命令是否写入成功
[root@localhost oracle]# sysctl -p

8、修改用户限制文件

[root@localhost oracle]# vim /etc/security/limits.conf
#在limits.conf末尾添加以下内容
oracle           soft    nproc           2047
oracle           hard    nproc           16384
oracle           soft    nofile          1024
oracle           hard    nofile         65536
oracle           soft    stack           10240 
#修改/etc/pam.d/login文件：
[root@localhost oracle]# vim /etc/pam.d/login
#在login末尾添加以下内容
session required  /lib64/security/pam_limits.so
session required   pam_limits.so 
#修改/etc/profile文件
[root@localhost oracle]# vim /etc/profile
#在profile的末尾添加以下内容
if [ $USER = "oracle" ]; then
  if [ $SHELL = "/bin/ksh" ]; then
      ulimit -p 16384
      ulimit -n 65536
  else
      ulimit -u 16384 -n 65536
  fi
fi

9、创建安装目录

[root@localhost oracle]# mkdir -p /data/oracle/product/11.2.0
[root@localhost oracle]# mkdir /data/oracle/oradata
[root@localhost oracle]# mkdir /data/oracle/inventory
[root@localhost oracle]# mkdir /data/oracle/fast_recovery_area

10、设置文件权限

[root@localhost oracle]# chown -R oracle:oinstall /data/oracle
[root@localhost oracle]# chmod -R 775 /data/oracle

11、设置Oracle环境变量

[root@localhost oracle]# su -l oracle
[root@localhost oracle]# vim .bash_profile
#在bash_profile的末尾添加以下内容
ORACLE_BASE=/data/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.2.0
ORACLE_SID=orcl
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH
[root@localhost oracle]# source .bash_profile

ORACLE_SID=orcl

加粗处必须与创建的数据库实例名称一致，否则数据库启动后无法访问。第一次配置完记得source一下

2.3、Oracle可视化安装

• 使用XManager连接Oracle

1、在linux上安装Xmanager4

yum groupinstall "X Window System"
yum groupinstall Desktop #好像不用安装也可以
yum install xterm
yum install xclock #测试用，好像可以不用安装

2、使用Oracle用户登陆linux

[root@localhost /]# su -l oracle

3、启动Xstart

4、进入database目录下./runInstaller启动

[oracle@localhost /]$ cd /software/database #进入database目录
[oracle@localhost /]$ ./runInstaller

5、进入界面

1、86%处出现报错 Error in invoking target ‘agent nmhs’ of makefile

2、解决方案：

• 在makefile中添加链接libnnz11库的参数
• 将$(MK_EMAGENT_NMECTL)修改为：
  $(MK_EMAGENT_NMECTL) -lnnz11

• -lnnz11：前面是字母l，后面是数字1

  [oracle@localhost ~]$ cd $ORACLE_HOME/sysman/lib
  [oracle@locaohost lib]$ cp ins_emagent.mk ins_emagent.mk.bak
  [oracle@locaohost lib]$ vi ins_emagent.mk

  

• :wq之后点击Retry

6、配置监听器

7、创建数据库

数据库密码就是安装时设置的密码

8、设置Oracle开机启动

[root@localhost /] vim /etc/oratab
oracle:/data/oracle/product/11.2.0/dbs:Y
[root@localhost /] vim /etc/rc.d/rc.local
su - oracle -c "/data/oracle/product/11.2.0/dbs/bin/dbstart"
su - oracle -c "/data/oracle/product/11.2.0/dbs/bin/lsnrctl start"

9、重启linux，测试连接

三、连接Oracle

3.1、Navicat连接Oracle

1、oracle身份登陆

#进入Oracle
[root@localhost /]# su -l oracle

2、开启监听

开启监听
[oracle@localhost ~]# lsnrctl start 
--------------------其他操作----------------------
#开启服务
[oracle@localhost ~]# net start oracleserviceorcl
#关闭监听
[oracle@localhost ~]# lsnrctl stop
#关闭服务
[oracle@localhost ~]# net stop oracleserviceorcl

3、使用sqlpus连接oracle数据库

#进入Oracle数据库的几种方式
[oracle@localhost ~]$ sqlplus / as sysdba
[oracle@localhost ~]$ sqlplus sys/root as sysdba 或 sqlplus /nolog
SQL>connect sys/root as sysdba

4、启动数据库

SQL> startup;

5、创建用户

#查询当前实例
SQL> select instance_name from v$instance;
#创建数据库表空间目录
[root@localhost /]# mkdir dbspace
#创建数据库表空间
SQL> create tablespace oracle_tb_space datafile '/dbspace/oracle_tb_space.dbf' size 500m;
#创建用户
SQL> SQL> create user oracle identified by oracle; 
#用户授权
SQL> grant dba to oracle;
SQL> grant select, update, insert on product to oracle;
SQL> grant all on product to oracle;
SQL> grant all on product to public;

6、以新用户角色登陆Oracle数据库

#连接数据库
[oracle@localhost ~]$ sqlplus /nolog
#以oracle登陆数据库
SQL> conn oracle/oracle@oracle
#查询模拟数据库
SQL> select * from dual;

7、使用Navicat连接测试

3.2、Oracle入门操作

3.2.1、Navicat可视化操作

1.1、创建用户

• 常规配置填写
• 获取最高权限
• 配额设置

1.2、创建数据库表

1.3、可视化curd

3.2.2、常规命令操作

1、创建数据库表

#创建teacher表
create table "tea_info" (
    tea_id number(5) not null primary key,
    tea_name varchar2(30),
    age number(3) default(18) ,
    gender char(3) default('男'),
    tea_depart varchar2(30)
);
#添加注释
comment  on  column  "tea_info".TEA_ID  is  '教师工号';
comment  on  column  "tea_info".TEA_NAME is  '教师姓名';
comment  on  column  "tea_info".AGE  is  '教师年龄';
comment  on  column  "tea_info".GENDER  is  '教师性别';
comment  on  column  "tea_info".TEA_DEPART  is  '所在部门';

2、SQL语句crud

#添加数据
INSERT into "tea_info" VALUES(
    1001,'王耀庆',32,'男','图书馆'
);

#删除数据
DELETE FROM "tea_info" WHERE tea_id=1003;

#修改数据
UPDATE "tea_info" SET tea_name='二狗' WHERE tea_id=1001;

#查询数据
SELECT * FROM "tea_info";

3.3、SpringBoot连接Oracle

3.3.1、搭建SpringBoot环境

• 搭建教程地址如下
  https://blog.csdn.net/weixin_44800625/article/details/115798193

3.3.2、导入Oracle连接依赖

                #导入oracle连接驱动    
                <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <scope>runtime</scope>
        </dependency>
                #导入sql连接驱动
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

3.3.3、配置连接

spring:
  datasource:
         #配置连接驱动
    driver-class-name: oracle.jdbc.driver.OracleDriver
    #配置连接地址
    url: jdbc:oracle:thin:@192.168.56.10:oracle
    #配置用户名、密码
    username: oracle
    password: oracle

3.3.4、测试连接

四、Oracle基础命令

4.1、DML(数据库操作语言)

• Data Manipulation Language
• DML主要针对数据的操作

4.1.1、insert：插入

insert into 表名（列名1,列名2,列名3.....）values(值1,值2,值3.....);
--示例
INSERT into "tea_info" VALUES(1001,'王耀庆',32,'男','图书馆');

4.1.2、delete：删除

--delet只删除字段内数据，不删除主键占用
delete from 表名 where 条件
--示例
delete  from stu_info where stuname='张三';
--truncate不是DML命令，但是也是删除命令，用于删除整表数据
truncate table "表名"；
--示例
truncate table "stu_info";
------truncate和delete的区别
1、truncate是DDL命令，命令执行即提交，数据删除后不能恢复
2、delete是DML命令，命令执行完需提交才能生效，删除后数据可以通过日志文件恢复
3、如果表中的数据量较大，truncate的速度比delete速度快很多
4、truncate删除将重新设置表索引的初始大小，而delete不能
5、delete能够触发表上相关的delete触发器，而truncate则不会触发

4.1.3、updata：修改

--单表修改
update 表名 set 列名1=值1,列名2=值2,列名3=值3..... where 条件
--示例
update student.stu_info 
   set age = '24', idnumber = '3503021994'
 where stuname = '张三';
 --多表关联修改
 update 表1 set 列名=（select 列名 from 表2 where 表1.列名=表2.列名） 
       where exists (select 1 from 表2 where 表1.列名=表2.列名)
-- 示例
   update student.stu_info t
   set (age, idnumber) =
       (select age, idnumber from student.stuinfo_18 b where b.stuid = t.stuid)
 where exists (select 1
          from student.stuinfo_18 b
         where b.stuid = t.stuid
           and b.stuname = '张三');

4.1.4、select：查询

select *|列名|表达式 from 表名 where 条件 order by 列名
--示例
select * from STUDENT.STUINFO  where stuname = '李四';
select * from student;
--语法解析
1、“t”代表st "*"  代表所有字段uinfo的别名
2、 "*"  代表所有字段
--联表查询
select * from table1,table2 where table1.colum1=table2.colum1; --关联table1和table2查询colum1字段数据
--示例
select s.stu_id,t.tea_id s.collage from stu_info,tea_info where s.stu_id=t.tea_id;
--语法分析
1、关联查询的字段数据类型要相同
2、关联表之间数据格式要相同
--备份查询数据
create table 表名 as select 语句
--示例
create table student.stuinfo_18 as select * from student.stuinfo ;
select * from student.stuinfo_18;

4.2、DDL(数据库定义语言)

• Data Definition Language
• DDL主要是针对整表的操作
• Oracle常用数据类型
  | 数据类型 | 类型解释 | | —- | —- | | varchar2(n) | 存储长度可变的字符串类型，n是字符串最大长度，默认是1，最大不超过4000 | | char(n) | 固定长度字符串类型，n是字符串最大长度，默认是1，最大不超过2000 | | number(a,b) | 存储数值类型，可以存整数和浮点型。a是最大位数：包含小数位和小数点，b是小数的位数，number(6,2)，输入123.12345，实际存入：123.12 | | data | 存储时间类型，存储的是日期和时间，包括年、月、日、时、分、秒。 | | timestamp | 时间类型：存储的不仅是日期和时间，还包含了时区。内置函数systimestamp获取的就是timestamp类型 | | CLOB | 大字段类型：存储的是大的文本，比如：非结构化的txt文本，字段大于4000长度的字符串。 | | BLOB | 二进制类型：存储的是二进制对象，比如图片、视频、声音等转换过来的二进制对象 |

4.2.1、create：创建

--Oracle表是Oracle存储的核心，表的创建就是极为重要的一个操作
--先删除存在的表在进行创建
DROP TABLE "student"."stu_info";
--创建表的基础框架
CREATE TABLE "student"."stu_info" (
  "stu_id" NUMBER(32,1) NOT NULL,
  "stu_name" VARCHAR2(200 BYTE),
  "stu_num" NUMBER(32),
  "stu_grade" VARCHAR2(200 BYTE),
  "stu_college" VARCHAR2(200 BYTE) 
)
--配置表空间和配额
TABLESPACE "SYSTEM"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
--区段(extent)一次扩展64k，最小区段数为1，最大的区段数为2147483645
STORAGE (
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  FREELISTS 1
  FREELIST GROUPS 1
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT;
--添加字段注释
COMMENT ON COLUMN "student"."stu_info"."stu_id" IS '学生学号';
COMMENT ON COLUMN "student"."stu_info"."stu_name" IS '学生姓名';
COMMENT ON COLUMN "student"."stu_info"."stu_num" IS '联系电话';
COMMENT ON COLUMN "student"."stu_info"."stu_grade" IS '学生年级';
COMMENT ON COLUMN "student"."stu_info"."stu_college" IS '所在学院';
--语法解析
comment on table "表名" is 'xxxx'; --给表加注释
comment on column "表名"."字段名" is 'xxxx'; --给字段加注释

4.2.2、drop：删除

drop table "stu_info";
--drop是针对表结构的操作
--执行drop后系统同步删除这个表的主键、索引、trigger等，不会删除sequence
--drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器
--drop语句删除表结构及所有数据，并将表所占用的空间全部释放
--在速度上，一般来说，drop> truncate > delete
--drop删除数据后可以恢复，但慎重使用

4.2.3、alter：修改

--alter用于后期对数据库表中的追加或修改
--向表中追加列
alter table "表名" add (列名,数据类型,....);
--示例
Alter table Employ Add (weight Number(38,0)) ;
--删除表中字段
alter table "表名" drop column "列名";
--示例
ALTER TABLE emp DROP COLUMN weight_new ;
--修改表中字段
alter table "表名" modify (列名,数据类型,....);
--示例
Alter Table Employ Modify (weight Number(13,2)) ;
--字段名重命
alter table "表名" rename column "当前列名" to "新列名";
--示例
Alter Table Emp Rename Cloumn weight To weight_new ;
--追加索引
alter index indexname;
--重建索引
alter index indexname rebuild;
alter index indexname rebuild online;
--alter index rebuild 和alter index rebuil online的区别
1、rebuild online 执行表扫描获取数据，有排序的操作;
2、rebuild以index fast full scan（or table full scan）方式读取原索引中的数据来构建一个新的索引，有排序的操作;

4.2.4、rename：重命

rename "当前列名" to "新列名";
alter table "表名" rename  "当前列名" to "新列名";

4.2.5、truncate：截断

--删除整表数据
truncate table "表名";
--删除操作针对表级，且删除过程不可逆
--效率比较：drop> truncate > delete

4.2.6、约束条件

• 约束是数据库用来确保数据满足业务规则的手段，在开发中，除了主键约束这类具有强需求的约束，像外键约束，检查约束更多时候仅仅出现在数据库设计阶段，真实环境却很少应用，更多是放到程序逻辑中去进行处理。
• 在Oracle数据库中约束有以下几种：

1、主键约束（PRIMARY KEY）

• 主键是定位表中单个行的方式，可唯一确定表中的某一行，关系型数据库要求所有表都应该有主键
• 键列必须必须具有唯一性，且不能为空，其实主键约束 相当于 UNIQUE+NOT NULL
• 一个表只允许有一个主键
• 主键所在列必须具有索引（主键的唯一约束通过索引来实现），如果不存在，将会在索引添加的时候自动创建

  --添加主键
  alter table emp add constraint emp_id_pk primary key(id);

2、 唯一性约束（UNIQUE)

• 唯一性约束用在单列或多列上，对于这些列或列组合，唯一性约束保证每一行的唯一性。
• 对于unique约束来讲，索引是必须的。如果不存在，就自动创建一个（unique唯一性是通过索引来保证的）
• unique允许null值，unique约束的列可存在多个null。因为unique唯一性通过btree索引来实现，而btree索引中不包含null。当然，这也造成了在where语句中用null值进行过滤会造成全表扫描。

  --添加唯一索引
  alter table emp add constraint emp_code_uq unique(code);

3、 非空约束（NOT NULL)

• 非空约束作用的列也叫强制列。
• 强制键列中必须有值，建表时候可以用default关键字指定了默认值。

  --添加非空索引
  alter table emp modify ename not null;

4、 外键约束（FOREIGN KEY)

• 外键约束定义在有父子关系的子表中，外键约束使子表中的列对应父表的主键列，用以维护数据库的完整性。
• 外键约束属于物理外键，一般不建议使用，当然这种东西你可以不用，但是你必须会。
• 外键约束的子表中的列和对应父表中的列数据类型必须相同，列名可以不同
• 对应的父表列必须存在主键约束（PRIMARY KEY）或唯一约束（UNIQUE）
• 外键约束列允许NULL值，对应的行就成了孤行了
• 添加外键之后表与表之间的关联会变得及其的强，容易导致数据删除失败
• 方法一：在创建外键时，通过 on delete cascade 子句指定该外键列可级联删除：

  --添加外键
  alter table emp add constraint emp_deptno_fk foreign key(deptno) references dept (deptno) on delete cascade;

• 方法二：删除父表中的对应行，会将对应子表中的所有匹配行的外键约束列置为NULL，通过 on delete set null 子句实施：

  --添加外键
  alter table emp add constraint emp_deptno_fk foreign key(deptno) references dept(deptno) on delete set null;

5、 检查约束（CHECK)

• 检查约束可用来实施一些简单的规则，比如列值必须在某个范围内。检查的规则必须是一个结果为true或false 的表达式

  --检查约束
  alter table emp modify constraint emp_ename_nn enable novalidate;

4.3、DQL(数据库查询语言)

• Data Query Language

4.3.1、基础查询

--单表无条件查询
select * from "表名";
--示例
select * from "stu_info";

4.3.2、去重、排序、别名查询

--去重查询
1、distinct 单列
select distinct "列名" from "表名";
2、distinct 多列
select distinct "列名1","列名2","列名3" from "表名";
select "列名1","列名2","列名3" from "表名"; group by "列名1","列名2","列名3";
--单表排序查询
select "查询字段" from "表名" group by "查询字段 条件" order by "查询字段" asc/desc;   
--示例:查询平均工资低于2000的部门的最大工资和平均工资
select deptno,max(sal),avg(sal) from emp group depnt having avg(sal) <2000 order by deptno asc;
--asc  升序
--desc 降序
--起别名查询
select colName n from tableName ;
select colName as n from tableName;

4.3.3、条件查询、模糊查询

--where条件查询
where是所有条件查询的切入关键字
--查询分数大于等于90的所有同学名字
select stu_name from "stu_info" where stu_fraction>=90;
--查询分数在80到90之间的同学
select stu_namefrom "stu_info" where stu_fraction>=80 and stu_fraction <90;
select stu_namefrom "stu_info" where stu_fraction beteween '80' and '90'
--模糊查询
1、%：表示零个或者多个任意字符
2、_：代表一个任意字符
3、\：指转义字符，“\%”在字符串中表示一个字符“%”
--查询班级里姓张的同学
select * from STUINFO where stuname like '张%';
--查询班级里姓张且单名的同学
select * from STUINFO where stuname like '张_';
--分页查询
--查询员工表中第三条到第五条数据 
select * from (select emp.* rownum  rn from emp where rownum <= 5) where rn > 2;

4.3.4、嵌套子查询

--单行子查询
--查询薪资高于平均工资的员工信息 
select * from emp where sal > (select avg(sal) from emp);
--多行子查询
--查询与smith部门和岗位完全相同的员工
select * from emp where (deptno, job) = (select deptno, job from emp where ename = 'SMITH'); 
--查询比部门30所有员工的工资都要高的员工信息 
select * from emp where sal > all (select sal from emp where deptno = 30); 
--查询比部门10中员工的工资高的员工信息 
select * from emp where sal > any (select sal from emp where deptno = 10); 
--查询部门10和部门20的员工信息 
select * from emp where deptno in (10,20); 
--查询在dept表中存在的部门员工信息 
select ename,sal,deptno from emp where exists (select 1 from dept  
where dept.deptno = emp.deptno);

4.3.5、多表查询

• 使用内连接时，只要写上连接条件就不会产生笛卡尔积，重点，面试必问！！！

--内连接，只要写上连接条件就不会产生笛卡尔积 
--内连接的两种写法，只返回符合条件的结果 
select * from emp inner join dept on emp.deptno = dept.deptno; 
select * from emp, dept where emp.deptno = dept.deptno; 
--外连接，左外连接、右外连接、满外连接
--左外连接的两种写法： 
select * from emp, dept where emp.deptno = dept.deptno(+); 
select * from emp left outer join dept on emp.deptno = dept.deptno; 
--右外连接的两种写法 
select * from emp ,dept where emp.deptno(+) = dept.deptno;  
select * from emp right join dept on emp.deptno = dept.deptno; 
--满外连接的写法 
select * from emp full outer join dept on emp.deptno = dept.deptno; 
--综合条件查询
--列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级 
select a.ename, a.sal, dname, b.ename, grade 
  from emp a, emp b, dept d, salgrade s 
     where a.deptno = d.deptno(+) 
   and a.mgr = b.empno 
   and a.sal between losal and hisal 
   and a.sal > (select avg(sal) from emp);

4.4、DCL(数据库控制语言)

• Data Conrtroller Language
• DCL是针对数据库用户的操作

4.4.1、grant/revoke：授权与撤销

--给数据库用户授权，用于增删改查
GRANT privilege[, ...] ON object[, ...] TO { PUBLIC | GROUP group| username}
privilege：
    select：查询
    insert：插入
    update：更新
    delete：删除
    rule：
    all：所有
--示例
--给所有用户授予查询、插入、更新stu_info表的权限
grant select,insert,update on "stu_info" to public;
--收回所有用户查询、插入、更新stu_info表的权限
revoke select,insert,update on tablename from public
object:
    table：表
    view：视图
    sequence：序列
    index：索引
grant select,insert,update on tablename,viewname,sequencename,indexname to public;
--public:对所有用户开放权限
--GROUP groupname:对该组所有用户开放权限
--username：对指定用户开放权限
--数据库用户授权，用户查看并操作系统表
grant dba to username;
--回收用户权限
revoke dba from username;
--给用户连接权限，用于新建用户提供服务/SID连接数据库示例
grant connect to username;
--撤销用户连接权限
revoke connect to username;
--给用户授予创建权限，用于新建用户表
grant resource to username with admin option;
grant unlimited tablespace to username with admin option;
--撤销用户创建权限
revoke resource from username;

4.4.2、commit/rollback：事务提交与回滚

--事务遵循ACID原则
1、A - atomicity    原子性: 不可分割, 要么成功要么全失败
2、C - Consistency  一致性: 事务前后数据状态要保持一致, 总数一致
3、I - Isolation    隔离性: 多个事务不能看到对方的中间状态(提交或者回滚之前的状态)
4、D - Duration     持久性: 事务完成后数据要持久化(事务的影响要反映在物理存储上)
--一个事务开始，在出现以下情况时，事务结束。
1、commit 语句 或 rollback 语句时，将提交或回滚事务
2、当用户退出Oracle工具时
3、当机器失效或系统奔溃时
--操作方法
begin; -开始事物
commit; - 提交事物
Python 默认是取消自动提交的
rollback- 回撤操作, 只要操作没有执行 commit 就可以进行回滚操作, 撤回
--模拟事务从开启到关闭的过程
create table tb_account(
accid char(4) not null,
uname varchar(20) not null,
balance float default 0)
insert into tb_account values
('1111', '张明禄', 1200.99),
('2222', '王大锤', 500);
--开启一个事务 start transaction
begin;
update tb_account set balance=balance-1000
where accid='1111';
update tb_account set balance=balance+1000
where accid='2222';
commit;   -- 提交 才能改变
rollback;  -- 撤销
begin;
delete from  tb_account;  -- 没有commmit 不会删除表
rollback;

4.4.3、select：数据库控制查询

--查看表空间的名称及大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
--查看表空间文件路径的名称及大小
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
--查看回滚段名称及大小
select segment_name, tablespace_name, r.status, 
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent, 
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;
--擦好看控制文件
select name from v$controlfile;
--查看日志文件
select member from v$logfile;
--查看表空间的使用情况
select sum(bytes)/(1024*1024) as free_space,tablespace_name 
from dba_free_space
group by tablespace_name;
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
--查看数据库对象
select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;
--查看数据库版本
Select version FROM Product_component_version 
Where SUBSTR(PRODUCT,1,6)='Oracle';
--查看数据库的创建日期和归档方式
Select Created, Log_Mode, Log_Mode From V$Database;
--查看系统有那些用户
select * from all_users; 
--查看指定用户的系统权限
select * from dba_role_privs where grantee=upper('username');
--查看指定用户的对象权限
select * from dba_tab_privs where grantee=upper('username');

4.5、运算与伪列

4.5.1、运算符

--运算符包括+ - * / 其中/运算符预算结果是浮点数
SELECT
    c.COURSENAME,
    SUM( s.SCORE ) / COUNT( 1 ) 
FROM
    COURSE c,
    SCORE s 
WHERE
    s.COURSEID = c.COURSEID 
    AND c.COURSEID = '101' 
GROUP BY
    c.COURSENAME;
--逻辑运算符 and/or/not(!)
SELECT
    st.STUNAME,
    c.COURSENAME,
    s.SCORE 
FROM
    STUINFO st,
    COURSE c,
    SCORE s 
WHERE
    s.SCORE BETWEEN 85 
    AND 95 
    AND c.COURSEID = s.COURSEID 
    AND s.STUID = st.STUID 
ORDER BY
    SCORE ASC;
--字符串连接符
SELECT
    '姓名：' || st.STUNAME || ',课程：' || c.COURSENAME || ',成绩：' || s.SCORE || '分' AS sxcj 
FROM
    COURSE c,
    SCORE s,
    STUINFO st 
WHERE
    s.COURSEID = c.COURSEID 
    AND s.STUID = st.STUID;

4.5.2、集合运算

--Oracle集合运算就是把多个查询结果组合成一个查询结果
1、intersect(交集)，返回两个查询共有的记录
select * from stuinfo intersect select * from stuinfo_2018;
2、union all(并集重复)，返回各个查询的所有记录，包括重复记录
select * from stuinfo union all select * from stuinfo_2018;
3、union(并集不重复)，返回各个查询的所有记录，不包括重复记录 （重复的记录只取一条）
select * from stuinfo union select * from stuinfo_2018;
4、minus(补集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录
select * from stuinfo minus select * from stuinfo_2018;

4.5.3、伪列

--伪列是Oracle表在存储的过程中或查询的过程中，表会有一些附加列，称为伪列
1、rowidROWID 是伪列返回的就是该行的物理地址，可以唯一的标识表中的一行
select t.*,t.rowid from stuinfo t ;
select t.*,t.rowid from stuinfo t where t.rowid='AAAShjAAEAAAAEFAAD';
2、rowunm表示的Oracle查询结果集的顺序，ROWNUM为每个查询结果集的行标识一个行号，第一行返回1，第二行返回2，依次顺序递增。
select t.stuid,t.stuname,t.sex,t.classno,t.stuaddress ,rownum  from stuinfo t ;
--rowunm 与 rowid 不同， rowid 是插入记录时生成， rowid 是查询数据时生成。rowid 标识的是行的物理地址。 rowunm 标识的是查询结果中的行的次序。
3、ROWNUM经常用来限制查询的结果返回的行数，求前几行或前几名的数据。
--返回学生信息表中（stuinfo）中学生年龄最低的前四位同学
select * from (select t.stuid, t.stuname, t.sex, t.classno, t.stuaddress, t.age, rownum
  from stuinfo t
 order by t.age asc) where rownum <=4;

五、Oracle函数应用

5.1、字符型函数

5.2、日期型函数

5.2.1、系统时区与时间的修改

1、方案一：

• 查看当前时区

  root@localhost ~]# date -R

  
  

• 时区不对，使用tzselect命令实现

  [root@localhost ~]# tzselect

  
  
  
  
  

• 执行命令TZ=’Asia/Shanghai’; export TZ；

  TZ='Asia/Shanghai'; export TZ

  
  

• 再次查看当前时区

  [root@localhost ~]# date -R

  
  

2、方案二：

• 查看当前时区

  [root@localhost ~]# timedatectl status

  
  

• 设置时区为东八区

  [root@localhost ~]# timedatectl list-timezones  | grep "Asia/S"
  [root@localhost ~]# timedatectl set-timezone "Asia/Shanghai"

  
  

• 查看当前的时区状态

  [root@localhost ~]# timedatectl status

3、设置系统当前时间

• 设置系统当前北京时间

  //设置时间为此刻北京时间
  [root@localhost ~]# date -s "2019-05-19 23:51:46"

• 查看修改情况

  [root@localhost ~]# date

• 查看当前硬件时间

  [root@localhost ~]# hwclock -r

• 同步硬件时间

  [root@localhost ~]# hwclock --hctosys

• 再次查看当前时间

  [root@localhost ~]# date

  
  

5.2.2、系统时间函数

--查询系统当前时间
select sysdate from dual; 
--带参数的时间函数
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
--获取年份
select to_char(sysdate,'yyyy') as nowYear from dual
--获取月份
select to_char(sysdate,'mm') as nowMonth from dual;
--获取日期
select to_char(sysdate,'dd') as nowDay from dual;   
--获取小时
select to_char(sysdate,'hh24') as nowHour from dual;
--获取分钟
select to_char(sysdate,'mi') as nowMinute from dual;
--获取秒钟
select to_char(sysdate,'ss') as nowSecond from dual;  
--获取星期几
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;     
--systimestamp:函数没有参数，可以得到系统的当前时间，该时间包含时区信息，精确到微秒。
select systimestamp from dual;
--DBTIMEZONE函数：该函数没有输入参数，返回数据库时区
select dbtimezone from dual;
--获取小时数
select sysdate ,to_char(sysdate,'hh') from dual;   
--获取一年的天数
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual ;  
--获取一年中的第几天
select TO_CHAR(SYSDATE,'DDD'),sysdate from dual     ;
--日期截取函数：ROUND（r[,f]）将日期r按f的格式进行四舍五入。如果f不填，则四舍五入到最近的一天。
select sysdate, --当前时间
       round(sysdate, 'yyyy') as year, --按年
       round(sysdate, 'mm') as month, --按月
       round(sysdate, 'dd') as day, --按天
       round(sysdate) as mr_day, --默认不填按天
       round(sysdate, 'hh24') as hour --按小时
  from dual;
--日期截取函数：TRUNC（r[,f]）将日期r按f的格式进行截取。如果f不填，则截取到当前的日期。  
select sysdate, --当前时间
       trunc(sysdate, 'yyyy') as year, --按年
       trunc(sysdate, 'mm') as month, --按月
       trunc(sysdate, 'dd') as day, --按天
       trunc(sysdate) as mr_day, --默认不填按天
       trunc(sysdate, 'hh24') as hour --按小时
  from dual;
--获取当前月得最后一天
select last_day(sysdate) from dual;
--获取当前最新时间和系统时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime,sysdate from dual;

5.3、数值型函数

• 在操作数值过程中，常用以下函数

5.4、转换型函数

5.5、聚合型函数

• AVG平均函数

  select avg(t.age) from stuinfo t;

  
  

• COUNT计数函数

  select count(*) from stuinfo;

  
  

• MIN/MAX最值函数

  select min(age), max(age) from stuinfo;

  
  

• SUM求和函数

  select classno, sum(age), count(*), avg(age)
  from stuinfo 
  where age < 30
  group by classno;

  
  

六、Oracle进阶

6.1、视图与序列

6.1.1、视图

• 什么是视图
  • 视图(View)实际上是一张或者多张表上的预定义查询，这些表称为基表
• 为什么要使用视图
  • 可以限制用户只能通过视图检索数据。这样就可以对最终用户屏蔽建表时底层的基表。
  • 可以将复杂的查询保存为视图。可以对最终用户屏蔽一定的复杂性。
  • 限制某个视图只能访问基表中的部分列或者部分行的特定数据。这样可以实现一定的安全性。
  • 从多张基表中按一定的业务逻辑抽出用户关心的部分，形成一张虚拟表。
• 语法使用
  • 创建视图 ```sql —语法 CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[, alias]…)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY] create view “视图名称” as select “列名1”,”列名2” from “表名”

create view students as select “stu_id”,”stu_name” from “stu_info”;

   -  **关键字解释** 
```sql
OR REPLACE:若所创建的试图已经存在，则替换旧视图;
FORCE:不管基表是否存在ORACLE都会自动创建该视图;
NOFORCE:如果基表不存在，无法创建视图，该项是默认选项;
alias:为视图产生的列定义的别名;
subquery:一条完整的SELECT语句，可以在该语句中定义别名;

• 删除视图

  drop view "视图名称";

• force强制创建视图
  • 创建视图

    --force无表强制创建视图
    create or replace force view视图名称 as 查询语句|关系运算

• 添加force

  create or replace force view vw_customer as  select customer_id, customer_code, customer_name, contact_telephone  
  from customer;

• 查询视图

  select * from customer;

• 视图总结

6.1.2、序列

• 什么是序列
  • 序列: Sequence 是oracle提供的用于产生一系列唯一数字的数据库对象。
  • oracle中没有自增列方法，所以在oracle数据库中主要用序列来实现主键自增
• 如何使用序列
  • 创建序列 ```sql —创建序列 CREATE SEQUENCE s //创建序列名称 [INCREMENT BY n] //递增的序列值是 n 如果 n 是正数就递增,如果是负数就递减 默认是 1 [START WITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue [{MAXVALUE n | NOMAXVALUE}] //最大值
    [{MINVALUE n | NOMINVALUE}] //最小值 [{CYCLE | NOCYCLE}] //循环/不循环 [{CACHE n | NOCACHE}];//分配并存入到内存中

—调用序列 NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用 CURRVAL 中存放序列的当前值 NEXTVAL 应在 CURRVAL 之前指定 ，二者应同时有效

—语法解析

CREATE SEQUENCE SEQNAME //序列名字
INCREMENT BY 1 //每次自增1， 也可写非0的任何整数，表示自增，或自减
START WITH 1 //自增初始值 MAXVALUE 1E20 //最大值；设置NOMAXVALUE表示无最大值
MINVALUE 1 //最小值；设置NOMINVALUE表示无最大值
CYCLE or NOCYCLE //设置到最大值后是否循环；
CACHE 20 //指定可以缓存 20 个值在内存里；如果设置不缓存序列，则写 ORDER or NOORDER //设置是否按照请求的顺序产生序列

Cycle 循环 nocycle 不循环 Cache 缓存 Cache<maxvalue-minvalue/increment by//一般不采用缓存 Nextvalue 下一个 Currval 当前值

—删除序列 drop SEQUENCE 序列名;

   -  使用序列 
```sql
//序列调用 产生一个新的序列
select seq_test.nextval from dual
//查看当前序列的值
select seq_test.currval from dual

• 序列示例 推荐方式二
  • 准备一张空表 ```sql CREATE TABLE “depts”( “dept_id” NUMBER(11) not null, “dept_name” VARCHAR2(50), “dept_num” NUMBER(20) );

COMMENT on table “depts” is ‘员工信息表’; COMMENT on COLUMN “depts”.”dept_id” is ‘员工编号’; COMMENT on COLUMN “depts”.”dept_name” is ‘员工姓名’; COMMENT on COLUMN “depts”.”dept_num” is ‘员工联系’; ALTER TABLE “depts” add primary key(“dept_id”);

   -  **id自增长方式一：** 
```sql
--序列化+触发器
--第一步，创建序列
create sequence seq_t_dept
minvalue 1
maxvalue 99999999
start with 1001
increment by 1
cache 50
--第二步，创建触发器
create or replace trigger "dept_trig"
    before insert on dept_p
    referencing old as old new as new for each row
declare
begin
    select seq_t_dept.nextval into :new.dept_sort from dual;
end dept_trig;
--第三步，插入数据，查看自增情况
insert into dept_p values('001', '安保部', '000', 1);
select * from dept_p;

• id自增方式二： ```sql —序列化+显示调用

—第一步，创建序列 drop sequence dept_id; create sequence dept_id increment by 1 start with 1001 nomaxvalue nocycle nocache;

—第二步，显示调用序列 insert into “depts” values(dept_id.nextval,’张三’,’1563459876’); insert into “depts” values(dept_id.nextval,’李四’,’1987651234’); insert into “depts” values(dept_id.nextval,’王五’,’1858547124’); insert into “depts” values(dept_id.nextval,’刘六’,’1738541234’);

—第三步，查询进行情况 select * from depts;

—查看序列当前值和下一个值的查看方式 select dept_id.currval from dual;

<a name="68ef8644"></a>
#### 6.2、索引
<a name="d92b55da"></a>
##### 6.2.1、索引介绍
- 索引是数据对象之一，用于加速数据查询，提高检索效率
- 索引是建立在表上面的可选对象，在逻辑和物理层面上都与表和表中数据无关，创建和删除操作都不影响表
- 索引一旦创建，对表进行DML操作时，oracle会自动管理索引
- 索引对用户是透明的，无论表是否又索引，sql语句用法不会改变
<a name="ed9769b6"></a>
##### 6.2.2、索引原理
- 若没有索引，搜索某个记录时（例如查找name='wish'）需要搜索所有的记录，因为不能保证只有一个wish，必须全部搜索一遍
- 若在name上建立索引，oracle会对全表进行一次搜索，将每条记录的name值会升序排列，然后构建索引条目（name和rowid），存储到索引段中，查询name为wish时即可直接查找对应地方
- 创建了索引并不一定就会使用，oracle自动统计表的信息后，决定是否使用索引，表中数据很少时使用全表扫描速度已经很快，没有必要使用索引
<a name="b989d670"></a>
##### 6.2.3、索引使用
-  创建索引 
```sql
CREATE [UNIQUE] | [BITMAP] INDEX index_name  --unique表示唯一索引
ON table_name([column1 [ASC|DESC],column2    --bitmap，创建位图索引
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]                                 --指定索引在数据块中空闲空间
[STORAGE (INITIAL n2)]
[NOLOGGING]                                  --表示创建和重建索引时允许对表做DML操作，默认情况下不应该使用
[NOLINE]
[NOSORT];                                    --表示创建索引时不进行排序，默认不适用，如果数据已经是按照该索引顺序排列的可以使用
create index index_stuinfo on "stu_info"("stu_id","stu_name");

• 修改索引

  alter index "当前索引" rename to "新索引";
  alter index index_01 rename to index_02;

• 删除所有 ```sql drop index “索引名称”; drop index index_stuinfo ;

—重构索引 alter index “索引名称” rebuild; alter index index_sno rebuild;

-  查看索引 
```sql
select index_name,index-type, tablespace_name, uniqueness from all_indexes where table_name ='tablename';
 --示例    
create index index_sno on student('name');
select * from all_indexes where table_name='student';

6.2.4、索引的使用原则

• 需求多次进行查询检索的字段需要创建索引
• 数据量大的查询需要创建索引
• 热点数据需要创建索引
• 索引不能与not连用，oracle碰到not会停止使用索引，采用全表扫描
• 模糊查询或者通配符查询不能建立索引，通配符会导致索引检索失效

6.3、备份与恢复

1、备份方式

• oracle备份可分为物理备份和逻辑备份
• 物理备份：
  • 冷备份(脱机备份)
  • 热备份(联机备份)
• 逻辑备份：
  • 导入
  • 导出
• 备份优势分析
  | | 导入导出 | 冷备份 | 热备份 | | —- | —- | —- | —- | | 优点 | 针对性的备份，能够跨平台实施备份操作并迁移，数据库可不关闭 | 备份和恢复速度，低维护，高安全的执行效果 | 可根据日志回溯到上一秒的操作，备份和恢复可以更为精准，不需要关闭数据库 | | 缺点 | 导出方式不能保护介质失效，只能躲到逻辑上的备份 | 单独使用，只能提供到某一时间点上的恢复，不能按表和用户恢复，必须关闭数据库进行操作 | 热备份过程比较复制，存放归档文件需要占用一定的物理空间，操作不允许失误，否则备份失败 | | 使用 | 日常所有备份都适用 | 数据库可以暂时关闭，或者需要和热备份配合使用 | 数据访问量小，需要实现表空间及库文件的备份 |

2、备份详解

• 逻辑备份之EXP/IMP： ```sql —exp数据导出

—创建备份存储文件 [root@localhost /]# mkdir /home/oracle/oracle_bak

—“full=y”，全量导出数据库 exp system/oracle full=y;

—备份数据及日志 用户权限 数据文件名称 文件对应地址 导出等级 日志文件名称 exp sys/oracle@oracle dumpfile=expdp.dmp directory=data_dir full=y logfile=expdp.log

—schemas按用户导出 exp user/passwd@oracle schemas=user dumpfile=expdp.dmp directory=data_dir logfile=expdp.log

—按表空间导出 expdp sys/passwd@oracle tablespace=system dumpfile=expdp.dmp directory=data_dir logfile=expdp.log

—导出表 expdp user/passwd@oracle tables=system dumpfile=expdp.dmp directory=data_dir logfile=expdp.log

—按条件导出 expdp user/passwd@oracle tables=table1=’where number=1234’ dumpfile=expdp.dmp directory=data_dir logfile=expdp.log

—imp数据导入

—“full=y”，全量导入数据库； imp user/passwd directory=data_dir dumpfile=expdp.dmp full=y

—同名用户导入，从用户A导入到用户A imp A/passwd schemas=A directory=data_dir dumpfile=expdp.dmp logfile=impdp.log;

—从A用户中把表table1和table2导入到B用户中 imp B/passwd tables=A.table1,A.table2 remap_schema=A:B directory=data_dir dumpfile=expdp.dmp logfile=impdp.log;

—导入表空间 imp sys/passwd tablespaces=tbs1 directory=data_dir dumpfile=expdp.dmp logfile=impdp.log

—追加数据 imp sys/passwd directory=data_dir dumpfile=expdp.dmp schemas=system table_exists_action=replace logfile=impdp.log; —table_exists_action:导入对象已存在时执行的操作。有效关键字:SKIP,APPEND,REPLACE和TRUNCATE

—并行操作 exp oracle/oracle tables=CASES directory=DPDATA1 dumpfile=expCASES_%U.dmp parallel=4 job_name=Cases_Export

—数据监控 select sid, serial# from v$session s, dba_datapump_sessions d where s.saddr = d.saddr; ```

• 物理备份参考文档
  https://www.cnblogs.com/lcword/p/11775657.html