01—Oracle

最新推荐文章于 2024-05-17 15:47:31 发布

嗨饼干

最新推荐文章于 2024-05-17 15:47:31 发布

阅读量841

点赞数 20

本文链接：https://blog.csdn.net/weixin_45161115/article/details/134976408

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

概述

Oracle通常应用于大型系统的数据库产品。Oracle是目前世界上使用最为广泛的数据库管理系统。作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系型数据库，它是一个完备关系的产品；作为分布式数据库它实现了 分布式 处理功能

Oracle数据库具备以下特点

支持多用户、大事务量的事务处理
数据安全性和完整性控制
支持分布式数据处理
可移植性

Oracle的存储结构分为逻辑存储结构和物理存储结构

逻辑存储结构

数据块
- 数据块是Oracle逻辑存储结构中最小的逻辑单位，一个数据库块对应一个或多个物理块，大小由参数DB_BLOCK_SIZE决定，数据块的结构包括块头和存储区两部分
数据区
- 数据区是由连续的数据块结合而成，是Oracle存储分配的最小单位
段
- 数据段。存储表中所有数据
- 索引段。存储表上最佳查询的所有索引数据
- 临时段。存储表排序操作期间建立的临时表的数据
- 回滚段。存储修改之前的位置和值
表空间
- 表空间是数据库的最大逻辑划分区域
- 一个表空间由一个或多个数据文件组成，一个数据文件只属于一个表空间，表空间的大小是它所对应的数据文件大小的总和
- 默认创建的表空间：系统表空间、辅助表空间、撤销表空间、用户表空间

物理存储结构

控制文件 .CTL
- 控制文件是数据库中最小的文件
- 控制文件是数据库中最重要的文件
数据文件（包括数据字典）.DBF
- 一个表空间可以包含几个数据文件，一个数据文件只能对应一个表空间
- 种类有系统数据文件、回滚数据文件、用户数据文件、临时数据文件
日志文件 .LOG
- 重做日志文件。用于记录数据变化，提供恢复机制
- 归档日志文件。是重做日志文件的历史备份
其他文件

数据库操作

创建表空间

创建表空间语法格式

-- 创建表空间
CREATE TABLESPACE 表空间名称
datafile 文件位置
size 初始大小
autoextend on
next 扩容大小;

datafile：用于设置物理文件名称

size：用于设置表空间的初始大小

autoextend on：用于设置自动增长，如果存储量超过初始大小，则开始扩容

next：用于设置扩容的空间大小

创建用户和用户赋权

创建用户语法格式

-- 创建用户
CREATE USER 用户名
identified by 密码
default tablespace 表空间名称;

identified by：用于设置用户的密码

default tablespace：用于指定默认表空间名称

用户创建完毕后无法登录，需要给用户赋权

-- 给用户赋DBA权限
grant dba to 用户名;

创建表

常见数据类型

① 字符型

CHAR：固定长度的字符类型，最多存储2000个字节
VARCHAR2：可变长度的字符类型，最多存储4000个字节
LONG：大文本类型，最大可以存储2G

② 数值型

NUMBER：数值类型，NUMBER(5)最大可以存的数是99999；NUMBER(5,2)最大可以存储的数是999.99

③ 日期型

DATE：日期时间型，精确到秒
TIMESTAMP：精确到秒的小数点后9位

④ 二进制(大数据类型)

CLOB：存储字符，最大可以存储4G
BLOB：存储图像、声音、视频等二进制数据，最多可以存储4G

创建表的语法格式

create table 表名(
	字段名 数据类型 primary key ,
    字段名 数据类型,
    ...
);

建表SQL

-- 创建业主类型表
create table T_OWNERTYPE(
       ID NUMBER PRIMARY KEY,
       NAME VARCHAR2(30) 
);
-- 价格表
create table T_PRICETABLE(
       ID NUMBER PRIMARY KEY,
       PRICE NUMBER(10,2),
       OWNERTYPEID NUMBER,
       MINNUM NUMBER(10,2),
       MAXNUM NUMBER(10,2)
);
-- 区域表
create table T_AREA(
       ID NUMBER PRIMARY KEY,
       NAME VARCHAR2(30)
);
-- 收费员表
create table T_OPERATOR(
       ID NUMBER PRIMARY KEY,
       NAME VARCHAR2(30)
);
-- 地址表
create table T_ADDRESS(
     ID NUMBER PRIMARY KEY,
     NAME VARCHAR2(30),
     AREAID NUMBER ,
     OPERATORID NUMBER  
);
-- 业主表
create table T_OWNERS(
       ID NUMBER PRIMARY KEY,
       NAME VARCHAR2(30),
       ADDRESSID NUMBER,
       HOUSENUMBER VARCHAR2(30),
       WATERMETER VARCHAR2(30),
       ADDDATE DATE,
       OWNERTYPEID NUMBER
);
-- 收费台账
create table T_ACCOUNT(
       ID NUMBER PRIMARY KEY,
       OWNERID NUMBER ,
       OWNERTYPEID NUMBER,
       AREAID NUMBER,
       YEAR CHAR(4),
       MONTH CHAR(2),
       NUM0 NUMBER,
       NUM1 NUMBER,
       USENUM NUMBER,
       METERUSERID NUMBER,
       MONEY NUMBER(10,2),
       ISFEE CHAR(1),
       FEEDATE DATE,
       FEEUSERID NUMBER
);

修改表

① 增加字段

ALTER TABLE 表名称 ADD(列名 类型 [DEFAULT 默认值],列名 类型 [DEFAULT 默认值]...)

-- 追加字段
ALTER TABLE T_OWNERS ADD(
	REMARK VARCHAR(20),
    OUTDATE DATE
);

② 修改字段

ALTER TABLE 表名称 MODIFY(列名 类型 [DEFAULT 默认值],列名 类型 [DEFAULT 默认值]...)

-- 修改字段
ALTER TABLE T_OWNERS MODIFY(
	REMARK CHAR(20),
    OUTDATE TIMESTAMP
);

③ 修改字段名

ALTER TABLE 表名称 RENAME COLUMN 原列名 TO 新列名

④ 删除字段名

ALTER TABLE 表名称 DROP COLUMN 列名

⑤ 删除表

DROP TABLE 表名称

数据增删改

① 插入

插入数据语法格式

insert into 表名 (列名1,列名2,...) values (值1,值2,...);
commit;

注意：使用SQL命令行执行INSERT语句后，数据并没有插入到数据表中，需要执行COMMIT语句提交事务才可以完成数据插入

插入数据实例

insert into T_OWNERTYPE(ID,NAME) values(1,'居民');
commit;

② 修改

修改语句语法格式

update 表名 set 列名1=值,列名2=值,...,列名n=值 where 条件;
commit;

注意：更新语句如果不加条件，会将表中所有的数据进行更改

更新数据实例

update T_OWNERS set ADDDATE = ADDDATE-3 where ID = 1;
commit;

③ 删除

删除语句语法格式

delete from 表名 where 条件;
commit;

注意：删除语句如果不加条件，会将表中所有数据进行清除

删除数据实例

delete from T_OWNERS where ID = 1;
commit;

还可以使用truncate语句删除数据

truncate table T_OWNERTYPE;

delete和truncate的区别

delete删除的数据如果没有commit提交，可以进行回滚
delete删除可能产生碎片，并且不释放空间
truncate是先摧毁表结构，再重构表结构，清空表中所有数据，无法回滚

JDBC连接

IDEA创建一个Java工程，在工程下创建一个lib目录，将oracle的驱动包复制一份到lib目录下

oracle驱动包可以百度下载，或者到oracle在本机安装目录下去获取

编写JDBC工具类用于获取连接和关闭连接

package com.spark.util;

import java.sql.*;

/**
 * JdbcUtil class
 * description: 获取数据库连接，关闭资源
 *
 * @author Administrator
 * @date 2023/10/21
 */
public class JdbcUtil {

    static{
        // 加载驱动
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    // 获取数据库连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521/orcl",
                "wateruser","root");
    }

    // 关闭连接
    public static void closeConnection(ResultSet rs, Statement st,Connection conn){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

编写实体类用于存储数据表中的数据

package com.spark.entity;

import java.util.Date;

/**
 * Owners class
 * description: Owners
 *
 * @author Administrator
 * @date 2023/10/21
 */
public class Owners {

    private Long id; // 业主编号
    private String name; // 业主姓名
    private Long addressId; // 地址编号
    private String houseNumber; // 门牌号
    private String waterMeter; // 水表编号
    private Date addDate; // 增加日期
    private Long ownerTypeId; // 业主类型编号

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Long getAddressId() {
        return addressId;
    }

    public void setAddressId(Long addressId) {
        this.addressId = addressId;
    }

    public String getHouseNumber() {
        return houseNumber;
    }

    public void setHouseNumber(String houseNumber) {
        this.houseNumber = houseNumber;
    }

    public String getWaterMeter() {
        return waterMeter;
    }

    public void setWaterMeter(String waterMeter) {
        this.waterMeter = waterMeter;
    }

    public Date getAddDate() {
        return addDate;
    }

    public void setAddDate(Date addDate) {
        this.addDate = addDate;
    }

    public Long getOwnerTypeId() {
        return ownerTypeId;
    }

    public void setOwnerTypeId(Long ownerTypeId) {
        this.ownerTypeId = ownerTypeId;
    }

    @Override
    public String toString() {
        return "Owners{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", addressId=" + addressId +
                ", houseNumber='" + houseNumber + '\'' +
                ", waterMeter='" + waterMeter + '\'' +
                ", addDate=" + addDate +
                ", ownerTypeId=" + ownerTypeId +
                '}';
    }
}

编写数据访问类，操作数据库

package com.spark.dao;

import com.spark.entity.Owners;
import com.spark.util.JdbcUtil;

import java.sql.*;

/**
 * OwnersDao class
 * description: OwnersDao
 *
 * @author Administrator
 * @date 2023/10/21
 */
public class OwnersDao {

    // 添加数据
    public static void insertData(Owners owners){
        Connection con = null;
        PreparedStatement ps = null;

        // 获取数据库连接
        try {
            con = JdbcUtil.getConnection();
            ps = con.prepareStatement("insert into T_OWNERS(ID,NAME,ADDRESSID,HOUSENUMBER,WATERMETER,ADDDATE,OWNERTYPEID)" +
                    "VALUES(?,?,?,?,?,?,?)");
            ps.setLong(1,owners.getId());
            ps.setString(2,owners.getName());
            ps.setLong(3,owners.getAddressId());
            ps.setString(4,owners.getHouseNumber());
            ps.setString(5,owners.getWaterMeter());
            ps.setDate(6,new Date(owners.getAddDate().getTime()));
            ps.setLong(7,owners.getOwnerTypeId());
            ps.execute();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            // 关闭连接
            JdbcUtil.closeConnection(null,ps,con);
        }
    }

    // 查询数据
    public static Owners getById(Long id){
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Owners owner=null;
        // 获取连接
        try {
            con = JdbcUtil.getConnection();
            ps = con.prepareStatement("select * from T_OWNERS where ID = ?");
            ps.setLong(1,id);
            rs = ps.executeQuery();
            while(rs.next()){
                owner = new Owners();
                owner.setId(rs.getLong("id"));
                owner.setName(rs.getString("name"));
                owner.setAddressId(rs.getLong("addressId"));
                owner.setHouseNumber(rs.getString("houseNumber"));
                owner.setWaterMeter(rs.getString("waterMeter"));
                owner.setAddDate(rs.getDate("addDate"));
                owner.setOwnerTypeId(rs.getLong("ownerTypeId"));
            }
            return owner;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }finally {
            JdbcUtil.closeConnection(rs,ps,con);
        }
    }
}

编写测试方法调用

package com.spark.test;

import com.spark.dao.OwnersDao;
import com.spark.entity.Owners;

import java.util.Date;

/**
 * Test class
 * description: Test
 *
 * @author Administrator
 * @date 2023/10/21
 */
public class Test {
    public static void main(String[] args) {
        Owners owners = new Owners();
        owners.setId(1L);
        owners.setName("张三");
        owners.setAddressId(123456L);
        owners.setHouseNumber("1-1");
        owners.setWaterMeter("1-1-001");
        owners.setAddDate(new Date());
        owners.setOwnerTypeId(1L);
        // 添加数据
        OwnersDao.insertData(owners);
        // 查询数据
        System.out.println(OwnersDao.getById(1L));
    }
}

数据导入和导出

使用cmd命令窗口输入命令

① 整库导入和导出

整库导出命令

exp system/root full=y

exp：导出命令

system/root：数据库的用户名和密码

full=y：整库导出

执行命令后会在当前目录下生成一个叫EXPDAT.DMP，此文件为备份文件

如果想指定备份文件的名称，则添加file参数即可，命令如下

exp system/root file=文件名.DMP full=y

整库导入命令

imp system/root full=y

此命令如果不指定file参数，默认用备份文件EXPDAT.DMP进行导入

如果指定file参数，则按照file指定的备份文件进行恢复

imp system/root full=y file=文件名.DMP

② 按用户进行导入和导出

按用户导出命令

exp system/root owner=wateruser file=wateruser.DMP

owner：指定用户名称

file：指定导出的DMP文件名

按用户导入命令

imp system/root fromuser=wateruser file=wateruser.DMP

fromuser：指定导入的用户名称

file：指定导入的DMP文件名

③ 按表进行导入和导出

按表导出命令

exp wateruser/root file=a.DMP tables=t_owners,t_ownertype

按表导出需要使用表所在的用户

file指定导出的文件名

tables指定要导出的表，如果有多张表中间用，分隔

按表导入命令

imp wateruser/root file=a.DMP tables=t_owners,t_ownertype

file指定导入的文件名

tables指定要导入的表，可以自行选择导入哪张表

数据库查询

删除所建的七张表，执行下面的SQL重新创建和填充数据


--建立价格区间表
create  table t_pricetable
(
id number primary key,
price number(10,2),
ownertypeid number,
minnum number,
maxnum number
);


--业主类型
create table t_ownertype
(
id number primary key,
name varchar2(30)
);

--业主表
create table t_owners
(
id number primary key,
name varchar2(30),
addressid number,
housenumber varchar2(30),
watermeter varchar2(30),
adddate date,
ownertypeid number
);



--区域表
create table t_area
(
id number,
name varchar2(30)
);

--收费员表
create table t_operator
(
id number,
name varchar2(30)
);


--地址表
create table t_address
(
id number primary key,
name varchar2(100),
areaid number,
operatorid number
);


--账务表--
create table t_account 
(
id number primary key,
owneruuid number,
ownertype number,
areaid number,
year char(4),
month char(2),
num0 number,
num1 number,
usenum number,
meteruser number,
meterdate date,
money number(10,2),
isfee char(1),
feedate date,
feeuser number
);


create sequence seq_account;

--业主类型
insert into t_ownertype values(1,'居民');
insert into t_ownertype values(2,'行政事业单位');
insert into t_ownertype values(3,'商业');

--地址信息--
insert into t_address values( 1,'明兴花园',1,1);
insert into t_address values( 2,'鑫源秋墅',1,1);
insert into t_address values( 3,'华龙苑南里小区',2,2);
insert into t_address values( 4,'河畔花园',2,2);
insert into t_address values( 5,'霍营',2,2);
insert into t_address values( 6,'回龙观东大街',3,2);
insert into t_address values( 7,'西二旗',3,2);

--业主信息
insert into t_owners values(1,'范冰',1,'1-1','30406',to_date('2015-04-12','yyyy-MM-dd'),1 );
insert into t_owners values(2,'王强',1,'1-2','30407',to_date('2015-02-14','yyyy-MM-dd'),1 );
insert into t_owners values(3,'马腾',1,'1-3','30408',to_date('2015-03-18','yyyy-MM-dd'),1 );
insert into t_owners values(4,'林小玲',2,'2-4','30409',to_date('2015-06-15','yyyy-MM-dd'),1 );
insert into t_owners values(5,'刘华',2,'2-5','30410',to_date('2013-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(6,'刘东',2,'2-2','30411',to_date('2014-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(7,'周健',3,'2-5','30433',to_date('2016-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(8,'张哲',4,'2-2','30455',to_date('2016-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(9,'昌平区中西医结合医院',5,'2-2','30422',to_date('2016-10-11','yyyy-MM-dd'),2 );
insert into t_owners values(10,'美廉美超市',5,'4-2','30423',to_date('2016-10-12','yyyy-MM-dd'),3 );


--操作员
insert into t_operator values(1,'马小云');
insert into t_operator values(2,'李翠花');



--地区--
insert into t_area values(1,'海淀');
insert into t_area values(2,'昌平');
insert into t_area values(3,'西城');
insert into t_area values(4,'东城');
insert into t_area values(5,'朝阳');
insert into t_area values(6,'玄武');


--价格表--

insert into t_pricetable values(1,2.45,1,0,5);
insert into t_pricetable values(2,3.45,1,5,10);
insert into t_pricetable values(3,4.45,1,10,null);

insert into t_pricetable values(4,3.87,2,0,5);
insert into t_pricetable values(5,4.87,2,5,10);
insert into t_pricetable values(6,5.87,2,10,null);

insert into t_pricetable values(7,4.36,3,0,5);
insert into t_pricetable values(8,5.36,3,5,10);
insert into t_pricetable values(9,6.36,3,10,null);

--账务表--
insert into t_account values( seq_account.nextval,1,1,1,'2012','01',30203,50123,0,1,sysdate,34.51,'1',to_date('2012-02-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','02',50123,60303,0,1,sysdate,23.43,'1',to_date('2012-03-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','03',60303,74111,0,1,sysdate,45.34,'1',to_date('2012-04-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','04',74111,77012,0,1,sysdate,52.54,'1',to_date('2012-05-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','05',77012,79031,0,1,sysdate,54.66,'1',to_date('2012-06-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','06',79031,80201,0,1,sysdate,76.45,'1',to_date('2012-07-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','07',80201,88331,0,1,sysdate,65.65,'1',to_date('2012-08-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','08',88331,89123,0,1,sysdate,55.67,'1',to_date('2012-09-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','09',89123,90122,0,1,sysdate,112.54,'1',to_date('2012-10-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','10',90122,93911,0,1,sysdate,76.21,'1',to_date('2012-11-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','11',93911,95012,0,1,sysdate,76.25,'1',to_date('2012-12-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','12',95012,99081,0,1,sysdate,44.51,'1',to_date('2013-01-14','yyyy-MM-dd'),2 );

insert into t_account values( seq_account.nextval,2,1,3,'2012','01',30334,50433,0,1,sysdate,34.51,'1',to_date('2013-02-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','02',50433,60765,0,1,sysdate,23.43,'1',to_date('2013-03-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','03',60765,74155,0,1,sysdate,45.34,'1',to_date('2013-04-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','04',74155,77099,0,1,sysdate,52.54,'1',to_date('2013-05-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','05',77099,79076,0,1,sysdate,54.66,'1',to_date('2013-06-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','06',79076,80287,0,1,sysdate,76.45,'1',to_date('2013-07-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','07',80287,88432,0,1,sysdate,65.65,'1',to_date('2013-08-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','08',88432,89765,0,1,sysdate,55.67,'1',to_date('2013-09-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','09',89765,90567,0,1,sysdate,112.54,'1',to_date('2013-10-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','10',90567,93932,0,1,sysdate,76.21,'1',to_date('2013-11-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','11',93932,95076,0,1,sysdate,76.25,'1',to_date('2013-12-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','12',95076,99324,0,1,sysdate,44.51,'1',to_date('2014-01-14','yyyy-MM-dd'),2 );

insert into t_account values( seq_account.nextval,100,1,3,'2012','12',95076,99324,0,1,sysdate,44.51,'1',to_date('2014-01-01','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,101,1,3,'2012','12',95076,99324,0,1,sysdate,44.51,'1',to_date('2015-01-01','yyyy-MM-dd'),2 );

update t_account set usenum=num1-num0;
update t_account set money=usenum*2.45;
commit;

单表条件查询

① 单一条件查询

语法格式

-- 根据某一条件查询所有列名内容
select * from 表名 where 条件 = 条件值;
-- 根据某一条件查询指定列名内容
select 列名1,列名2,列名3,... from 表名 where 条件 = 条件值;

需求：查询水表编号为30408的业主记录

select * from T_OWNERS where watermeter = '30408';

② 模糊查询

语法格式

-- 模糊查询，包含指定内容
select * from 表名 where 条件 like '%内容%';
-- 模糊查询，以指定内容开始
select * from 表名 where 条件 like '内容%';
-- 模糊查询，以指定内容结束
select * from 表名 where 条件 like '%内容';

需求：查询业主名称包含"刘"的业主记录

select * from T_OWNERS where name like '%刘%';

③ and运算符

语法格式

-- 多个条件查询，需同时成立
select * from 表名 where 条件1 = 条件值 and 条件2 = 条件值 and ...;

需求：查询业主名称包含"刘"的并且门牌号包含5的业主记录

select * from T_OWNERS where name like '%刘%' and housenumber like '%5%';

④ or运算符

语法格式

-- 多个条件查询，任意一条成立
select * from 表名 where 条件1 = 条件值 or 条件2 = 条件值 or ...;

需求：查询业主名称包含"刘"的或者门牌号包含5的业主记录

select * from T_OWNERS where name like '%刘%' or housenumber like '%5%';

⑤ and和or运算符混合使用

需求：查询业主名称包含“刘”的或者门牌号包含5的业主记录，并且地址编号为3的记录

select * from T_OWNERS where (name like '%刘%' or housenumber like '%5%') and addressid = 3;

使用()将前面的条件包裹起来是因为and优先级大于or，如果不使用()包裹，sql语句执行会成为下面这条语句

select * from T_OWNERS where name like '%刘%' or (housenumber like '%5%' and addressid=3);

先查询后面的and条件再查询前面的or条件

⑥ 范围查询

需求：查询台账记录中用水字数大于等于10000，并且小于等于20000的记录

-- 使用 >= 和 <= 来实现
select * from T_ACCOUNT where usenum >=10000 and usenum<=20000;
-- 使用between
select * from T_ACCOUNT where usenum between 10000 and 20000;

⑦ 空值查询

需求：查询T_PRICETABLE表中MAXNUM为空的记录

-- 查询为空的记录
select * from T_PRICETABLE where maxnum is null;
-- 查询不为空的记录
select * from T_PRICETABLE where maxnum is not null;

⑧ 去重复数据

需求：查询业主表中的地址ID不重复显示

-- 使用distinct关键字去掉重复数据
select distinct addressid from T_OWNERS;

⑨ 排序查询

需求：对T_ACCOUNT表按使用量进行升序排列

-- 使用order by 进行排序，默认是升序排序
select * from T_ACCOUNT order by usenum;
-- 使用order by 进行排序，进行降序排序 desc
select * from T_ACCOUNT order by usenum desc;

⑩ 基于伪列查询

在Oracle表的使用过程中，实际表中还有一些附加列，称为伪列。伪列就像表中的列一样，但是表中并不存储。伪列只能查询，不能进行增删改。

ROWID：表中的每一行在数据文件中都有一个物理地址，ROWID伪列返回的就是该行的物理地址。使用ROWID可以快速的定位表中的某一行。ROWID值可以唯一的标识表中的一行。由于ROWID返回的是该行的物理地址，因此使用ROWID可以显示行是如何存储的

select rowid,t.* from T_OWNERS t;

ROWNUM：在查询的结果集中，ROWNUM为结果集中每一行标识一个行号，第一行返回1，第二行返回2，以此类推。通过ROWNUM伪列可以限制查询结果集返回的行数

select rownum,t.* from T_OWNERS t;
select rownum,t.* from T_OWNERS t where id>3;

聚合统计

① 求和sum

需求：统计2012年所有用户的用水量总和

-- 求和函数sum
select sum(usenum) from T_ACCOUNT where year = '2012';

② 求平均avg

需求：统计2012年所有用水量的平均值

-- 求平均函数avg
select avg(usenum) from T_ACCOUNT where year = '2012';

③ 求最大值max

需求：统计2012年最高用水量

-- 最大值函数max
select max(usenum) from T_ACCOUNT where year = '2012';

④ 求最小值min

需求：统计2012年最低用水量

-- 最小值函数min
select min(usenum) from T_ACCOUNT where year = '2012';

⑤ 统计记录个数count

需求：统计业主类型ID为1的业主数量

-- 统计记录个数count
select count(*) from T_OWNERS where ownertypeid = 1;

⑥ 分组聚合group by

需求：按区域分组统计水费合计数

-- 分组聚合 group by
select areaid,sum(money) from T_ACCOUNT group by areaid;
-- select year, areaid,sum(money) from T_ACCOUNT group by areaid; 报错，year字段不是分组聚合条件，也不是聚合函数

注意：select后一定是分组聚合的条件或者是聚合函数

⑦ 分组后的条件查询

需求：查询水费合计大于169000区域及水费合计

-- 分组后通过having进行条件查询
select sum(money),areaid from T_ACCOUNT group by areaid having sum(money)>169000;

条件查询

内连接

内连接也称为等同连接，返回的结果集是两张表中所有相匹配的数据

① 需求：查询显示业主编号，业主名称，业主类型名称

字段来源于两张表，业主表，业主类型表，两张表可以通过业主类型id进行连接

select ow.id 业主编号, ow.name 业主名称, ot.name 业主类型
from T_OWNERS ow,T_OWNERTYPE ot
where ow.ownertypeid = ot.id;

② 需求：查询业主编号，业主名称，业主类型名称，地址

字段来源于三张表，业主表，业主类型表，地址表，业主表通过业主类型id和业主类型表连接，业主表通过地址id和地址表连接

select ow.id 业主编号, ow.name 业主名称, ot.name 业主类型, ad.name 地址
from T_OWNERS ow,T_OWNERTYPE ot,T_ADDRESS ad
where ow.ownertypeid = ot.id and ow.addressid = ad.id;

③ 需求：查询显示业主编号，业主名称，地址，所属区域，业主分类

字段来源于四张表，业主表，业主类型表，地址表，区域表，业主表通过类型id和业主类型连接，业主表通过地址id和地址表连接，地址表通过区域id连接区域表

select ow.id 业主编号, ow.name 业主名称, ot.name 业主类型, ad.name 地址, ar.name 区域
from T_OWNERS ow,T_OWNERTYPE ot,T_ADDRESS ad,T_AREA ar
where ow.ownertypeid = ot.id and ow.addressid = ad.id and ad.areaid = ar.id;

④ 需求：查询显示业主编号，业主名称，地址，所属区域，收费员，业主分类

字段来源于五张表，业主表，业主类型表，地址表，区域表，收费员表，业主表通过类型id和业主类型表连接，业主表通过地址id和地址表链接，地址表通过区域id连接区域表，地址表通过收费员id连接收费员表

select ow.id 业主编号, ow.name 业主名称, ot.name 业主类型, ad.name 地址, ar.name 区域, op.name 收费员
from T_OWNERS ow,T_OWNERTYPE ot,T_ADDRESS ad,T_AREA ar,T_OPERATOR op
where ow.ownertypeid = ot.id and ow.addressid = ad.id and ad.areaid = ar.id and ad.operatorid = op.id;

外连接

外连接分为左外连接和右外连接

左外连接是通过条件将两个表连接起来，以左表为基础，右表返回与左表匹配的记录，如果找不到与左表匹配的，返回空值

右外连接是通过条件将两个表连接起来，以右表为基础，左表返回与右表匹配的记录，如果找不到与右表匹配的，返回空值

① 需求：查询业主的账务记录，显示业主编号、名称、年、月、金额。如果此业主没有账务记录也要列出姓名，使用左外连接查询

由于业主表中的数据没有对应的账务记录也要进行展示，因此需要以业主表作为基础表

-- 左外连接 SQL1999通用标准写法
select ow.id 业主编号, ow.name 业主名称, ac.year 年, ac.month 月, ac.money 金额
from T_OWNERS ow left join T_ACCOUNT ac
on ow.id = ac.owneruuid;

-- 左外连接 Oracle特有写法
select ow.id 业主编号, ow.name 业主名称, ac.year 年, ac.month 月, ac.money 金额
from T_OWNERS ow , T_ACCOUNT ac
where ow.id = ac.owneruuid(+);

② 需求：查询业主的账务记录，显示业主编号、名称、年、月、金额。如果账务记录没有对应的业主信息，也要列出记录，使用右外连接

由于账务记录表中的数据有没有对应的业主都要进行展示，因此需要以账务表作为基础表

-- 右外连接 SQL1999标准通用写法
select ow.id 业主编号, ow.name 业主名称, ac.year 年, ac.month 月, ac.money 金额
from T_OWNERS ow right join T_ACCOUNT ac
on ow.id = ac.owneruuid;

-- 右外连接 Oracle特有写法
select ow.id 业主编号, ow.name 业主名称, ac.year 年, ac.month 月, ac.money 金额
from T_OWNERS ow , T_ACCOUNT ac
where ow.id(+) = ac.owneruuid;

子查询

where子句中的子查询

① 单行子查询

单行操作符

操作符	含义
=	等于
>	大于
>=	大于等于
<	小于
<=	小于等于
<>	不等于

需求：查询2012年1月用水量大于平均值的台账记录

-- 第一步：查询用水量的平均记录
select avg(usenum) from T_ACCOUNT where year = '2012' and month = '01';
-- 第二步：查询2012年1月的台账记录
select * from T_ACCOUNT where year = '2012' and month = '01';
-- 第三步：合成子查询
select * from T_ACCOUNT 
where year = '2012' 
and month = '01' 
and usenum>(select avg(usenum) from T_ACCOUNT where year = '2012' and month = '01');

② 多行子查询

多行操作符

操作符	含义
IN	等于列表中的任何一个
ANY	和子查询返回的任意一个值比较
ALL	和子查询返回的所有值比较

需求：查询地址编号为1、3、4的业主记录

select * from T_OWNERS where addressid in (1,3,4);

需求：查询地址含有花园的业主信息

select * from T_OWNERS 
where addressid in (select id from T_ADDRESS where name like '%花园%');

from子句中的子查询

需求：查询显示业主编号，业主名称，业主类型名称，条件为业主类型为居民

-- 第一步：查询业主编号，业主名称， 业主类型名称
select ow.id 业主编号, ow.name 业主名称, ot.name 业主类型 
from T_OWNERS ow,T_OWNERTYPE ot  where ow.ownertypeid = ot.id

-- 第二步：将第一步结果当作为一张数据表，添加条件合成子查询
select 业主编号,业主名称,业主类型
from ( select ow.id 业主编号, ow.name 业主名称, ot.name 业主类型 
from T_OWNERS ow,T_OWNERTYPE ot  where ow.ownertypeid = ot.id)
where 业主类型 = '居民';

select子句中的子查询

select子句的子查询必须为单行子查询

需求：列出业主信息，包括ID，名称，所属地址

-- 第一步：查询业主表的相关字段
select id,name,addressid from T_OWNERS ;

-- 第二步：将地址id更改为查询语句获取地址名称，合成子查询
select id,name,(select name from T_ADDRESS where id=addressid) addressName from T_OWNERS;

需求：列出业主信息，包括ID，名称，所属地址，所属区域

select ow.id,ow.name,(select name from T_ADDRESS where id=addressid) addressName,
(select (select name from T_AREA where id = areaid) from T_ADDRESS where id=addressid) areaName
from T_OWNERS ow;

分页查询

① 简单分页

在ORACLE进行分页查询，需要用到伪列ROWNUM

需求：查询账户记录前10条记录

select rownum, t.* from T_ACCOUNT t where rownum <=10;

如果查询下一页数据，写成这行SQL是查询不到值的

select rownum, t.* from T_ACCOUNT t where rownum <=20 and rownum>10;

注意：ROWNUM给数据赋值，只有当查询时扫描到数据表中的值才会其赋值，因此使用ROWNUM作为查询条件时只能用<或者<=

因此查询下一页数据，需要通过子查询，先对ROWNUM进行赋值，然后再作为条件进行查询

select * from
(select rownum r, t.* from T_ACCOUNT t where rownum<=20)
where r>10;

② 基于排序的分页

由于分页查询需要先对ROWNUM进行赋值，为了防止使用ORDER BY对数据进行排序导致ROWNUM未赋值成功，需要使用嵌套查询完成分页查询

需求：查询账户记录第二页的数据

-- 第一步将数据排序
select * from T_ACCOUNT order by usenum desc;

-- 第二步将排序后的数据行为ROWNUM赋值
select rownum r,t.* from 
(select * from T_ACCOUNT order by usenum desc) t;

-- 第三步添加查询条件
select * from (
       select rownum r,t.* from 
              (select * from T_ACCOUNT order by usenum desc) t 
              where rownum<=20
) where r>10;

函数

字符函数

函数	说明
ASCII	返回对应字符的十进制值
CHR	给出十进制返回字符
CONCAT	拼接两个字符串，与\|\|相同
INITCAT	将字符串的第一个字母变为大写
INSTR	找出某个字符串位置
INSTRB	找出某个字符串的位置和字节数
LENGTH	以字符给出字符串的长度
LENGTHB	以字节给出字符串的长度
LOWER	将字符串转换成小写
LPAD	使用指定的字符在字符的左边填充
LTRIM	在左边裁掉指定的字符
RPAD	使用指定的字符在字符右边填充
RTRIM	在右边裁掉指定的字符
REPLACE	执行字符串搜索和替换
SUBSTR	取字符串的子串
TRIM	裁掉前面或后面的字符串
UPPER	将字符串变为大写

常用字符函数

① 求字符串长度

-- 字符串长度
-- 由于ORACLE必须使用FROM，因此可以使用DUAL，DUAL是伪表
select length('abcd') from dual;

② 求字符串的子串

-- 字符串的子串
-- 下标从1开始，因此截取结果为bc
select substr('abcd',2,2) from dual;

③ 字符串拼接

-- 字符串拼接
select concat('abc','d') from dual;
-- concat只能拼接两个字符串，多个字符串拼接concat需要嵌套使用
select concat(concat('abc','d'),'ef') from dual;
-- 多个字符串拼接，建议使用 ||
select 'abc' || 'd' || 'ef' from dual;

数值函数

函数	说明
ABS(value)	绝对值
FLOOR(value)	小于或等于value的最大整数
MOD(value)	求模(取余数)
ROUND(value,precision)	按precision精度4舍5入
POWER(value,exponent)	value的exponent次幂

常用数值函数

① 四舍五入

-- 四舍五入 默认取整数
select round(100.19) from dual;
-- 按精度四舍五入
select round(100.156,2) from dual;

② 截取函数

-- 截取，默认截取掉整数后面的
select trunc(100.123) from dual;
-- 截取，按精度直接截取，不进行四舍五入
select trunc(100.568,2) from dual;

③ 取模

-- 求余数
select mod(10,3) from dual;

日期函数

① 加月函数

select add_months(sysdate,2) from dual;

② 求所在月的最后一天

select last_day(sysdate) from dual;

③ 截取日期

-- 截取日期，默认截掉时分秒  2023/10/30
select trunc(sysdate) from dual;
-- 截取日期，按年截取，得到今年的第一天  2023/1/1
select trunc(sysdate,'yyyy') from dual;
-- 按月截取，得到当月的第一天  2023/10/1
select trunc(sysdate,'mm') from dual;
-- 按日截取，与默认相同 2023/10/30
select trunc(sysdate,'dd') from dual;
-- 按小时截取  2023/10/30 20:00:00
select trunc(sysdate,'hh') from dual;
-- 按分钟截取 2023/10/30 20:16:00
select trunc(sysdate,'mi') from dual;

转换函数

① 数字转字符串

-- 数字转字符串
select to_char(111) || '分' from dual;

② 日期转字符串

-- 日期转字符串
select to_char(sysdate,'yyyy-mm-dd')  from dual;
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss')from dual;

③ 字符串转日期

-- 字符串转日期
select to_date('2023-10-30','yyyy-mm-dd') from dual;

④ 字符串转数字

-- 字符串转数字
select to_number('100') + 10 from dual;

其他函数

① 空值处理函数NVL

用法：NVL(检测的值，如果是NULL为其赋予的值)

select nvl(null,0) from dual;

需求：显示价格表中业主类型ID为1的价格记录，如果上限值为NULL，则显示999999

select nvl(maxnum,999999) maxnum from T_PRICETABLE  where ownertypeid=1;

② 空值处理函数NVL2

用法：NVL2(检测的值，如果不为null赋予的值，如果为null赋予的值)

需求：显示价格表中业主类型ID为1的价格记录，如果上限值为null，显示“不限”

select nvl2(maxnum,to_char(maxnum),'不限') from T_PRICETABLE where ownertypeid=1;

③ 条件取值decode

语法：decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

功能：根据条件返回相应值

需求：查询业主表显示业主类型名称，不关联业主类型表

select name,decode(ownertypeid,1,'居民',2,'行政事业单位',3,'商业','其他') from T_OWNERS;

当业主类型为1时，赋值居民；为2时，赋值行政事业单位；为3时赋值商业；都不符合时赋值缺省值其他。如果没有设置缺省值，赋值null

上面的语句也可以使用sql1999标准写法 case when then

select name,(case ownertypeid
                            when 1 then '居民'
                            when 2 then '行政事业单位'
                            when 3 then '商业'
                            else '其他'
                     end
)from T_OWNERS;

拓展：行列转换

需求：按季度统计2012年各个地区的水费

select (select name from T_AREA where id = areaid) 区域, 
sum( case when month>='01' and month<='03' then money else 0 end) 第一季度,
sum( case when month>='04' and month<='06' then money else 0 end) 第二季度,
sum( case when month>='07' and month<='09' then money else 0 end) 第三季度,
sum( case when month>='10' and month<='12' then money else 0 end) 第四季度
from T_ACCOUNT t where year = '2012' group by areaid;

分析函数

需求：按照业主用水量降序排列为其排名

① RANK函数相同的值序号相同，序号跳跃

select rank() over(order by usenum desc) 序号,t.* from T_ACCOUNT t;

② DENSE_RANK函数相同的值排序相同，排名连续

select dense_rank() over(order by usenum desc) 序号,t.* from T_ACCOUNT t;

③ ROW_NUMBER函数返回连续的排名，无论值是否相等

select row_number() over(order by usenum desc) 序号,t.* from T_ACCOUNT t;

集合运算

集合运算就是将两个或多个结果集组合成为一个结果集。集合运算包括：

UNION ALL(并集) ，返回各个查询的所有记录，包含重复记录
UNION(并集)，返回各个查询的所有记录，不包括重复记录
INTERSECT(交集)，返回两个查询共有的记录
MINUS(差集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录

① 并集运算

select * from T_OWNERS where id>5
union
select * from T_OWNERS where id<8;

② 并集运算(不去除重复记录)

select * from T_OWNERS where id>5
union all
select * from T_OWNERS where id<8;

③ 交集运算

select * from T_OWNERS where id>5
intersect
select * from T_OWNERS where id<8;

④ 差集运算

select * from T_OWNERS where id>5
minus
select * from T_OWNERS where id<8;

也可以通过差集运算完成分页操作

-- 查询账户记录表第二页的记录
select rownum, t.* from T_ACCOUNT  t where rownum<=20
minus
select rownum, t.* from T_ACCOUNT  t where rownum<=10;

视图

概述

视图是一种数据库对象，是从一个或者多个数据表或者视图中导出的虚表，视图所对应的数据并不真正地存储在视图中，而是存储在所引用的数据表中，视图的结构和数据是对数据表进行查询的结果

根据创建视图时给定的条件，视图可以是一个数据表的一部分，也可以是多个基表的联合，它存储了要执行检索的查询语句的定义，以便于引用该视图时使用

使用视图的优点：

简化数据库操作。视图可以简化用户处理数据的方式
着重于特定数据。不必要的数据或敏感数据可以不出现在视图中
视图提供了一个简单而有效的安全机制，可以定制不同用户对数据的访问权限
提供向后兼容性。视图使用户能够在表的架构更改时为表创建向后兼容接口

基础语法

创建或修改视图语法

CREATE [OR REPLACE] [FORCE] VIEW view_name
AS subquery
[WITH CHECK OPTION]
[WITH READ ONLY]

OR REPLACE：若所创建的视图已经存在，ORACLE自动创建该视图

FORCE：不管基表是否存在ORACLE都会自动创建该视图

subquery：一条完整的SELECT语句，可以在该语句中定义别名

WITH CHECK OPTION：插入或修改的数据行必须满足视图定义的约束

WITH READ ONLY：该视图上不能进行任何DML操作

删除视图语法

DROP VIEW view_name

案例

① 简单视图的创建和使用

如果视图中的语句只是单表查询，并且没有聚合函数，称之为简单视图

需求：创建视图，查询业主类型为1的业主信息

-- 创建视图，查询业主类型为1的业主信息
create view view_owners1
as select * from T_OWNERS where ownertypeid='1';

-- 查询视图中的数据
select * from view_owners1;

如果更新视图中的数据，数据表T_OWNERS里面的数据也会更改吗？

-- 更新视图中的数据
update view_owners1 set name = '范小兵' where id = '1';
commit;

-- 查询数据表T_OWNERS的数据
select * from T_OWNERS where id = '1';

注意：视图是一个虚拟表，它的数据来源于存储数据的真实表；如果更改了视图的数据，表的数据也会随之改变；同样更改数据表中的数据，视图也会随之改变。视图存储的并不是数据，而是一条SQL语句。

② 带检查约束的视图

需求：根据地址表创建视图，内容为区域id为2的记录

-- 创建带检查约束的视图
create view view_address2
as select * from T_ADDRESS where areaid = 2
with check option;

-- 查询视图中的数据
select * from view_address2;

带检查约束的视图，约束点在于对视图进行数据修改时，不能修改条件值

-- 更新语句无法成功执行，不能修改约束条件areaid
update view_address2 set areaid=3 where id = 4;

③ 只读视图

如果创建一个视图不希望用户对视图进行修改，创建视图时通过WITH READ ONLY选项可以将视图设置为只读

-- 创建只读视图
create or replace view view_owners1
as select * from T_OWNERS where ownertypeid = 1
with read only;

-- 更新只读数据，报错
update view_owners1 set name='范大冰' where id = 1;

④ 带错误的视图

创建一个视图，如果视图的SQL语句所涉及的表不存在，系统会给出错误提示。如果需要先对一个不存在的表创建一个视图，可以通过FORCE创建

-- 创建一个错误视图
create or replace force view view_temp
as select * from T_TEMP;

⑤ 多表关联的视图

需求：创建视图，查询显示业主编号，业主名称，业主类型

-- 创建多表关联视图
create or replace view view_owners
as 
select ow.id 业主编号,ow.name 业主姓名, ot.name 业主类型 
from T_OWNERS ow,T_OWNERTYPE ot 
where ow.ownertypeid = ot.id;

注意：多表关联的视图也可以对数据进行修改，但是只能修改键保留表中的字段值

创建视图时，多表关联查询语句中主键所在的表为键保留表，主键是业主编号来源于业主表，所以可以修改业主姓名，不能修改业主类型

⑥ 聚合统计的视图

需求：创建视图，按年月统计水费金额

-- 创建聚合统计视图
create or replace view view_accountsum
as
select year,month,sum(money) money from T_ACCOUNT
group by year,month
order by year,month;

注意：聚合统计的视图无法对数据进行修改

物化视图

概述

由于视图每次访问都会导致查询语句被执行一次。为了避免每次访问都执行查询，可以将查询，可以将查询结果集存储到一个物化视图(也叫实体化视图)

物化视图与普通视图相比的区别就是物理视图是建立的副本，类似于一张表，需要占用存储空间。而对一个物化视图查询的执行效率与查询一个表是一样的

创建物化视图语法

CREATE METERIALIZED VIEW view_name
[BUILD IMMEDIATE | BUILD DEFERRED]
REFRESH [FAST|COMPLETE|FORCE]
[
  ON [COMMIT | DEMAND] | START WITH (start_time) NEXT (next_time)
]
AS
subquery

BUILD IMMEDIATE：创建物化视图的时候就生成数据

BUILD DEFERRED：在创建时不生成数据，以后根据需要再生成数据，默认是BUILD IMMEDIATE

REFRESH：当基表发生了DML操作后，物化视图采用哪种方式和基表同步。FAST为增量刷新，COMPLETE为全量刷新，FORCE为自动选择，FORCE为默认选项

案例

① 手动刷新的物化视图

需求：查询地址ID，地址名称和所属区域名称

-- 创建物化视图，手动刷新
create materialized view mv_address
as 
select ad.id,ad.name,ar.name arName
from T_ADDRESS ad,T_AREA ar
where ad.areaid = ar.id;

-- 查询物化视图数据
select * from mv_address;

向基表中添加一条数据

-- 向基表中插入数据
insert into T_ADDRESS values(8,'西三旗',2,2);

-- 查询基表中的数据
select * from T_ADDRESS;

-- 查询物化视图数据
select * from mv_address;

注意：基本添加数据后，由于物化视图是手动刷新，因此需要执行SQL或者命令去更新物化视图

SQL更新物化视图

-- 手动更新物化视图数据
begin
  dbms_mview.refresh('mv_address','C');
end;

命令更新物化视图

exec dbms_mview.refresh('mv_address','C');

② 自动刷新的物化视图

创建自动刷新的物化视图

-- 创建自动刷新的物化视图，基表操作数据提交后，物化视图自动刷新
create materialized view mv_address2
refresh 
on commit
as
select ad.id,ad.name,ar.name arname
from T_ADDRESS ad,T_AREA ar
where ad.areaid = ar.id;

-- 向基表添加一条数据
insert into T_ADDRESS values(9,'宋家庄',2,2);
commit;

-- 查询物化视图数据
select * from mv_address2;

③ 创建时不生成数据的物化视图

-- 创建时不生成数据的物化视图
create materialized view mv_address3
build deferred
refresh
on commit
as
select ad.id,ad.name,ar.name arname
from T_ADDRESS ad,T_AREA ar;

-- 查询物化视图数据
select * from mv_address3;

注意：该物化视图更新数据如果是自动刷新，第一次必须通过执行SQL或者命令进行手动刷新

④ 增量刷新的物化视图

创建物化视图之前必须先创建物化视图日志，物化视图日志是记录基表发生了哪些变化，用这些记录去更新物化视图

-- 根据基表的字段创建物化视图日志
create materialized view log on t_address with rowid;
create materialized view log on t_area with rowid;

物化视图日志名称 MLOG$_数据表名

创建增量刷新的物化视图

-- 创建增量刷新的物化视图，查询语句中必须有物化视图日志中的字段
create materialized view mv_address4
refresh fast
as
select ad.rowid adrowid,ar.rowid arrowid,ad.id,ad.name,ar.name arname 
from t_address ad,t_area ar
where ad.areaid = ar.id;

向基表中操作数据

-- 操作基表中的数据
delete from T_ADDRESS where id = 9;
commit;

查询物化视图日志

-- 查询物化视图日志
select * from MLOG$_T_ADDRESS;

手动更新物化视图

-- 查询物化视图数据
select * from mv_address4;
-- 手动更新物化视图数据
begin
  dbms_mview.refresh('mv_address4','C'); 
end;

序列

概述

序列是Oracle提供的用于产生一系列唯一数字的数据库对象

创建序列的语法

create sequence 序列名称

通过序列的伪列来访问序列值

NEXTVAL：返回序列的下一个值

CURRVAL：返回序列的当前值

注意：如果序列是刚创建的话，无法获取序列的当前值，必须先执行一次获取下一个值，才可以获取到当前值

-- 创建简单序列
create sequence sequence_test;
-- 获取序列下一个值
select sequence_test.nextval from dual;
-- 获取序列当前值
select sequence_test.currval from dual;

语法

创建复杂序列语法

CREATE SEQUENCE sequenceName -- 创建序列名称
	[INCREMENT BY n] -- 递增的序列值是n，如果n是正数就递增，如果是负数就递减 默认是1
	[START WITH n] -- 开始的值，递增默认是minvalue，递减是maxvalue
	[{MAXVALUE n|NOMAXVALUE}] -- 最大值
	[{MINVALUE n|NOMINVALUE}] -- 最小值
	[{CYCLE|NOCYCLE}] -- 循环/不循环 默认是不循环
	[{CACHE n|NOCACHE}]; -- 分配并存入内存中 默认是20

案例

① 有最大值的非循环序列

-- 创建有最大值的非循环序列
create sequence seq_test1
increment by 2 -- 每次递增2
start with 10 -- 起始值为10
maxvalue 100 -- 最大值为100

select seq_test1.nextval from dual;

② 有最大值并且有缓存的循环序列

-- 创建有最大值的循环序列
create sequence seq_test2
increment by 3 -- 每次递增3
start with 5 -- 起始值为5
maxvalue 100 -- 最大值为100
cycle -- 循环
cache 20

select seq_test2.nextval from dual;

如果创建的序列是循环序列，一次循环的值不能小于一次缓存的数

一次缓存的数为 cache值*增长值

同义词

概述

同义词就是对象的一个别名，同义词有私有同义词和公有同义词，私有同义词只针对当前用户

创建同义词语法

create [public] SYNONYM synooym for object;

synonym表示要创建的同义词名称，object表示表，视图，序列等要创建同义词的对象名称

案例

① 为数据表创建同义词

-- 为业主表创建同义词
create synonym owners for T_OWNERS;
-- 使用同义词查询业主表记录
select * from owners;

同义词默认是私有的，只能当前用户使用；如果是超级管理员，需要以用户名.同义词访问

select * from wateruser.owners;

② 创建公有同义词

-- 创建公有同义词
create public synonym owners2 for T_OWNERS;
-- 使用同义词查询数据
select * from owners2;

公有同义词各个用户都可以使用

索引

概述

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低I/O次数，从而提高数据访问性能

索引是需要占据存储空间的，也可以理解为是一种特殊的数据。形式类似于树，树的节点存储的就是每条记录的物理地址，也就是提到的伪列(ROWID)

普通索引

创建普通索引语法

create index 索引名 on 表名(列名);

需求：为一个一百万条记录数的表添加索引，查看查询时的耗时时间

-- 创建一个数据表
create table t_indextest(id number,name varchar2(30));
-- 向数据表中插入一百万条记录
begin
  for x in 1..1000000
    loop
      insert into t_indextest values(x,'AA'||x);
    end loop;
    commit;
end;
-- 对id添加索引
create index index_id on t_indextest(id);

-- 查询数据查看耗时
select * from t_indextest where name = 'AA765432';
select * from t_indextest where id = 765432;

唯一索引和复合索引

如果需要在某个表某个列创建索引，而这列的值是不会重复的，这时可以创建唯一索引

唯一索引语法

create unique index 索引名称 on 表名(列名);

当表中经常需要对某几列进行查询，可以基于这些列建立一个复合索引

复合索引语法

create index 索引名称 on 表名(列名,列名......);

反向键索引

当某个字段的值为连续增长的值，如果构建标准索引，会形成歪脖子树。这样会增加查询的层数，性能会下降。建立反向键索引，可以使索引的值变得不规则，从而使索引树能均匀分布。

创建反向键索引语法

create index 索引名称 on 表名(列名,列名......);

位图索引

使用场景：位图索引适合创建在低基数列上(低基数为数量有限，个数少，如：性别、民族等)

位图索引不直接存储ROWID，而是存储字节位到ROWID的映射

优点：减少响应时间，节省空间占用

缺点：查询只能等值查询，不能进行范围查询，如：大于、小于等

创建位图索引语法

create bitmap index 索引名称 on 表名(列名);

PL/SQL

概述

PL/SQL是Oracle对SQL语言的过程化扩展，指在SQL命令语言中增加了过程处理语句(如分支、循环等)，使SQL语言具有过程处理能力。把SQL语言的数据操作能力与过程语言的数据处理能力结合起来，使得PLSQL面向过程比过程语言简单、高效、灵活和实用

基本语法结构

[declare
	-- 声明变量
]
 begin
 	-- 代码逻辑
 [exception
    -- 异常处理
 ]
 end;

变量声明与赋值

声明变量的语法

变量名 类型(长度);

变量赋值的方法

变量名:=变量值

需求：声明变量水费单价、水费字数、吨数、金额

对水费单价、水费字数进行赋值。吨数根据水费字数换算，规则为水费字数除以1000，并且四舍五入，保留两位小数。计算金额，金额=单价*吨数

输出单价、数量和金额

declare
  v_price number(10,2); -- 水费单价
  v_usenum number; -- 水费字数
  v_usenum2 number(10,2); -- 吨数
  v_money number(10,2); -- 金额
begin
  -- 变量赋值
  v_price:=2.45;
  v_usenum:=9213;
  v_usenum2:=round(v_usenum/1000,2);
  v_money:=v_price*v_usenum2;
  
  -- 输出结果
  DBMS_OUTPUT.put_line('金额:'||v_money);
end;

select into 赋值方式

-- select into 赋值方式
declare
  v_price number(10,2); -- 水费单价
  v_usenum number; -- 水费字数
  v_usenum2 number(10,2); -- 吨数
  v_money number(10,2); -- 金额
  v_num0 number; -- 上月水表数
  v_num1 number; -- 本月水表数
begin
  -- 变量赋值
  v_price:=2.45;
  -- select into 赋值
  select usenum,num0,num1 into v_usenum,v_num0,v_num1 from t_account
  where year='2012' and month = '01' and owneruuid =1;
  v_usenum2:=round(v_usenum/1000,2);
  v_money:=v_price*v_usenum2;
  
  -- 输出结果
  DBMS_OUTPUT.put_line('金额:'||v_money);
end;

属性类型

① %TYPE引用型

作用：引用某表某列的字段类型

-- 属性类型 %TYPE引用型 表名.列名%TYPE
declare
  v_price number(10,2); -- 水费单价
  -- 使用%TYPE引用
  v_usenum t_account.usenum%type; -- 水费字数
  v_usenum2 number(10,2); -- 吨数
  v_money number(10,2); -- 金额
  v_num0 t_account.num0%type; -- 上月水表数
  v_num1 t_account.num1%type; -- 本月水表数
begin
  -- 变量赋值
  v_price:=2.45;
  -- select into 赋值
  select usenum,num0,num1 into v_usenum,v_num0,v_num1 from t_account
  where year='2012' and month = '01' and owneruuid =1;
  v_usenum2:=round(v_usenum/1000,2);
  v_money:=v_price*v_usenum2;
  
  -- 输出结果
  DBMS_OUTPUT.put_line('使用吨数:'||v_usenum||' 金额:'||v_money);
end;

② %ROWTYPE

作用：引用某表某行的字段类型

-- 属性类型 %ROWTYPE引用型
declare
  v_price number(10,2); -- 水费单价
  v_usenum2 number(10,2); -- 吨数
  v_money number(10,2); -- 金额
  -- 使用引用型引用数据表中的一行数据类型
  v_account t_account%rowtype;
begin
  -- 变量赋值
  v_price:=2.45;
  -- select into 赋值给引用型
  select * into v_account from t_account
  where year='2012' and month = '01' and owneruuid =1;
  -- 使用引用型赋值
  v_usenum2:=round(v_account.usenum/1000,2);
  v_money:=v_price*v_usenum2;
  
  -- 输出结果
  DBMS_OUTPUT.put_line('使用吨数:'||v_account.usenum||' 金额:'||v_money);
end;

异常

在运行程序时出现的错误叫做异常，发生异常后，语句将停止执行，控制权转移到PL/SQL块的异常处理部分

异常有两种类型

预定义异常。当PL/SQL程序违反Oracle规则或超越系统限制时隐式引发
用户定义异常。用户可以在PL/SQL块的声明部分定义异常，自定义的异常通过RAISE语句显式引发

使用预定义异常

-- 使用预定义异常
declare
  v_price number(10,2); -- 水费单价
  v_usenum2 number(10,2); -- 吨数
  v_money number(10,2); -- 金额
  -- 使用引用型引用数据表中的一行数据类型
  v_account t_account%rowtype;
begin
  -- 变量赋值
  v_price:=2.45;
  -- select into 赋值给引用型
  select * into v_account from t_account
  where year='2012' and month = '01';
  -- 使用引用型赋值
  v_usenum2:=round(v_account.usenum/1000,2);
  v_money:=v_price*v_usenum2;
  -- 输出结果
  DBMS_OUTPUT.put_line('使用吨数:'||v_account.usenum||' 金额:'||v_money);
  -- 处理预定义异常
 exception
   when no_data_found then
     DBMS_OUTPUT.put_line('没有找到账务数据');
   when too_many_rows then
     DBMS_OUTPUT.put_line('返回多行账务数据');
end;

条件判断

基本语法1

if 条件 then
	业务逻辑
end if;

基本语法2

if 条件 then
	业务逻辑
else
	业务逻辑
end if;

基本语法3

if 条件 then
	业务逻辑
elsif 条件 then
	业务逻辑
else
	业务逻辑
end if;

需求：设置三个等级的水费，5吨以下2.45元/吨，5吨到10吨部分3.45元/吨，超过10吨部分4.45，根据使用水费的量来计算阶梯水费

-- 使用预定义异常
declare
  v_price1 number(10,2); -- 5吨以下单价
  v_price2 number(10,2); -- 5到10吨单价
  v_price3 number(10,2); -- 10吨以上
  v_usenum2 number(10,2); -- 吨数
  v_money number(10,2); -- 金额
  -- 使用引用型引用数据表中的一行数据类型
  v_account t_account%rowtype;
begin
  -- 变量赋值
  v_price1:=2.45;
  v_price2:=3.45;
  v_price3:=4.45;
  -- select into 赋值给引用型
  select * into v_account from t_account
  where year='2012' and month = '01' and owneruuid=1;
  -- 使用引用型赋值
  v_usenum2:=round(v_account.usenum/1000,2);
  -- 使用条件判断
  if v_usenum2<5 then 
    v_money:=v_price1*v_usenum2;
  elsif v_usenum2>=5 and v_usenum2<=10 then 
    v_money:=v_price1*5+v_price2*(v_usenum2-5);
  else
    v_money:=v_price1*5+v_price2*5+v_price3*(v_usenum2-10);
  end if;
  -- 输出结果
  DBMS_OUTPUT.put_line('使用吨数:'||v_account.usenum||' 金额:'||v_money);
  -- 处理预定义异常
 exception
   when no_data_found then
     DBMS_OUTPUT.put_line('没有找到账务数据');
   when too_many_rows then
     DBMS_OUTPUT.put_line('返回多行账务数据');
end;

循环

无条件循环语法

loop
  -- 循环语句
end loop;

有条件循环语法

while 条件
loop
  -- 循环语句
end loop;

for循环语法

for 变量 in 条件
loop
 -- 循环语句
end loop;

需求：输出1--100

-- 无条件循环
declare
  v_num number;
begin
  v_num:=1;
  loop 
    dbms_output.put_line(v_num);
    v_num:=v_num+1;
    exit when v_num>100;
  end loop;
end;

-- 带条件的循环
declare
 v_num number;
begin
  v_num:=1;
  while v_num<=100
  loop
    dbms_output.put_line(v_num);
    v_num:=v_num+1;
  end loop;
end;

-- for循环
begin
  for v_num in 1 .. 100
  loop
    dbms_output.put_line(v_num);
  end loop;
end;

游标

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。可以把游标理解为PL/SQL中的结果集，在声明区声明游标，语法如下

cursor 游标名称 is SQL语句;

使用游标语法

open 游标名称
loop
	fetch 游标名称 into 变量
	exit when 游标名称%notfound
end loop;
close 游标名称

需求：打印业主类型为1的价格表

-- 游标案例 打印业主类型为1的价格表
declare
  v_price T_PRICETABLE%rowtype; -- 引用价格表行类型
  cursor cur_price is select * from t_pricetable where ownertypeid=1; -- 声明游标
begin
  open cur_price; -- 开启游标
       loop
         fetch cur_price into v_price; -- 提取游标
         exit when cur_price%notfound; -- 退出游标循环
         dbms_output.put_line(v_price.price);
       end loop;
  close cur_price; -- 关闭游标
end;

当值为可变时，如业主类型值不确定时，可以使用带参数的游标

-- 带参数的游标
declare
  v_price T_PRICETABLE%rowtype; -- 引用价格表行类型
  cursor cur_price(v_type number) is select * from t_pricetable where ownertypeid=v_type; -- 声明游标，带参数
begin
  open cur_price(2); -- 开启游标,传参数值
       loop
         fetch cur_price into v_price; -- 提取游标
         exit when cur_price%notfound; -- 退出游标循环
         dbms_output.put_line(v_price.price);
       end loop;
  close cur_price; -- 关闭游标
end;

for循环游标

-- for循环 带参数的游标
declare
  cursor cur_price(v_type number) is select * from t_pricetable where ownertypeid=v_type; -- 声明游标
begin
  -- 使用for循环
  for v_price in cur_price(1)
     loop
       dbms_output.put_line(v_price.price);
     end loop;
end;

存储函数

存储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。在函数中可以使用PLSQL进行逻辑的处理

创建或修改存储函数的语法

CREATE [OR REPLACE] FUNCTION 函数名称
(参数名称 参数类型,参数名称 参数类型,...)
RETURN 结果变量数据类型
IS
 声明变量部分;
BEGIN 
 逻辑部分;
 RETURN 结果变量;
[EXCEPTION 异常处理部分]
END;

自定义存储函数案例

-- 存储函数
create or replace function fun_getAddress(v_id number)
return varchar2
is
  -- 声明变量
  v_name varchar2(30);
begin
  -- select into 赋值
  select name into v_name from t_address where id = v_id;
  -- 返回结果
  return v_name;
end;
-- 使用存储函数
select fun_getAddress(3) from dual;
select id,name,fun_getAddress(addressid) from t_owners;

存储过程

概述

存储过程是被命名的PL/SQL块，存储于数据库中，是数据库对象的一种。应用程序可以调用存储过程，执行相应的逻辑。

存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如下

存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过传出参数返回多个值
存储函数可以在select语句中直接使用，而存储过程不能。过程多数是被应用程序所调用
存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码

创建或修改存储过程语法

CREATE [OR REPLACE] PROCEDURE 存储过程名称
(参数名 类型,参数名 类型,...)
IS|AS
 变量声明部分;
BEGIN
 逻辑部分;
 [EXCEPTION 异常处理部分]
END;

参数只指定类型，不指定长度

过程参数的三种模式

IN 传入参数(默认)

OUT 传出参数，主要用于返回程序运行结果

IN OUT 传入传出参数

案例

① 不带传出参数的存储过程

需求：添加业主信息

创建存储过程

-- 创建不带参数的存储过程
-- 创建序列
create sequence seq_owners start with 11;
-- 创建存储过程
create or replace procedure pro_owners_add(
       v_name varchar2,
       v_addressid number,
       v_housenumber varchar2,
       v_watermeter varchar2,
       v_type number
)
is
begin
  insert into t_owners values(seq_owners.nextval,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_type);
  commit;
end;

调用存储过程

-- 调用存储过程
call pro_owners_add('马小跳',2,'22333','66777',1);
-- 查询数据
select * from t_owners;

② JDBC调用存储过程

// 调用存储过程
public static void addProcedure(Owners owner){
    Connection conn = null;
    CallableStatement call = null;
    try {
        conn = JdbcUtil.getConnection();
        call = conn.prepareCall("{call pro_owners_add(?,?,?,?,?)}");
        call.setString(1,owner.getName());
        call.setLong(2,owner.getAddressId());
        call.setString(3,owner.getHouseNumber());
        call.setString(4,owner.getWaterMeter());
        call.setLong(5,owner.getOwnerTypeId());
        call.execute();
        JdbcUtil.closeConnection(null,call,conn);
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
}

package com.spark.test;

import com.spark.dao.OwnersDao;
import com.spark.entity.Owners;

/**
 * Test2 class
 * description: JDBC调用不带参数的存储过程
 *
 * @author Administrator
 * @date 2023/11/18
 */
public class Test2 {
    public static void main(String[] args) {
        Owners owners = new Owners();
        owners.setName("张小跳");
        owners.setAddressId(2L);
        owners.setHouseNumber("1-2455");
        owners.setWaterMeter("774488");
        owners.setOwnerTypeId(1L);
        OwnersDao.addProcedure(owners);
        // 查询数据
        System.out.println(OwnersDao.getById(12L));
    }
}

③ 带传出参数的存储过程

-- 创建带传出参数的存储过程
create or replace procedure pro_owners_add(
       v_name varchar2,
       v_addressid number,
       v_housenumber varchar2,
       v_watermeter varchar2,
       v_type number,
       v_id out number -- 传出参数
)
is
begin
  -- 给传出参数赋值
  select seq_owners.nextval into v_id from dual;
  insert into t_owners values(v_id,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_type);
  commit;
end;

-- 调用带传出参数的存储过程
declare
   v_id number;
begin
  pro_owners_add('新之助',2,'5289','571369',1,v_id);
  dbms_output.put_line(v_id);
end;

④ JDBC调用带传出参数的存储过程

// 带传出参数的存储过程
public static Long addProOut(Owners owner){
    Connection conn = null;
    CallableStatement call = null;
    Long id = 0L;
    try {
        conn = JdbcUtil.getConnection();
        call = conn.prepareCall("{call pro_owners_add(?,?,?,?,?,?)}");
        call.setString(1,owner.getName());
        call.setLong(2,owner.getAddressId());
        call.setString(3,owner.getHouseNumber());
        call.setString(4,owner.getWaterMeter());
        call.setLong(5,owner.getOwnerTypeId());
        // 注册传出参数类型
        call.registerOutParameter(6, OracleTypes.NUMBER);
        call.execute();
        // 获取传出参数值
        id = call.getLong(6);
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }finally {
        JdbcUtil.closeConnection(null,call,conn);
    }
    return id;
}

package com.spark.test;

import com.spark.dao.OwnersDao;
import com.spark.entity.Owners;

/**
 * Test3 class
 * description: JDBC调用带传出参数的存储过程
 *
 * @author Administrator
 * @date 2023/11/18
 */
public class Test3 {
    public static void main(String[] args) {
        Owners owners = new Owners();
        owners.setName("向日葵");
        owners.setAddressId(2L);
        owners.setHouseNumber("1-7755");
        owners.setWaterMeter("754608");
        owners.setOwnerTypeId(1L);
        OwnersDao.addProOut(owners);
        // 查询数据
        System.out.println(OwnersDao.getById(14L));
    }
}

触发器

概述

数据库触发器是一个与表相关的、存储的PL/SQL程序。每当一个特定的数据操作语句(insert,update,delete)在指定的表上发出时，Oracle自动执行触发器中定义的语句序列

触发器可用于

数据确认
实施复杂的安全性检查
做审计，跟踪表上所做的数据操作等
数据的备份和同步

触发器分类

前置触发器(BEFORE)
后置触发器(AFTER)

语法

创建触发器语法

CREATE [OR REPLACE] TRIGGER 触发器名
	BEFORE | AFTER
	[DELETE] [[OR] INSERT] [[OR] UPDATE [OF 列名]]
	ON 表名
	[FOR EACH ROW] [WHEN(条件)]
declare
  ...
begin
  PLSQL
end;

FOR EACH ROW 作用是标注此触发器是行级触发器，默认是语句触发器；行记录有多少条，行级触发器就执行多少次，而语句级触发器只执行一次

触发语句	:old	:new
insert	所有字段都是空(null)	将要插入的数据
update	更新以前该行的值	更新后的值
delete	删除以前该行的值	所有字段都是空(null)

案例

① 前置触发器

需求：当用户输入本月累计表数后，自动计算出本月使用数

-- 前置触发器
create or replace trigger tri_account
       before
       update of num1
       on t_account
       for each row
declare
begin
  :new.usenum:=:new.num1-:new.num0;
end;

② 后置触发器

需求：当用户修改了业主信息表的数据时记录修改前和修改后的值

-- 后置触发器
-- 创建一个业主日志表
create table t_owners_log
(
       updatetime date,
       ownerid number,
       oldname varchar2(30),
       newname varchar2(30)
);
create or replace trigger tri_owners
       after
       update of name
       on t_owners
       for each row
declare
begin
  insert into t_owners_log values(sysdate,:new.id,:old.name,:new.name);
end;

嗨饼干

关注

20
点赞
踩
17

收藏

觉得还不错? 一键收藏
1
评论
01—Oracle

Oracle通常应用于大型系统的数据库产品。Oracle是目前世界上使用最为广泛的数据库管理系统。作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系型数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能Oracle数据库具备以下特点支持多用户、大事务量的事务处理数据安全性和完整性控制支持分布式数据处理可移植性Oracle的存储结构分为逻辑存储结构和物理存储结构逻辑存储结构数据块。
复制链接

扫一扫