Oracle基础1

数据准备

create table my_user
(
    id    number primary key,
    name1 varchar2(30),
    name2 varchar2(30)
);
alter table my_user
    add
        (name3 varchar2(30));
-- 修改字段
alter table my_user
    modify
        (name1 varchar2(10),
         name2 varchar2(20));
-- 修改字段名
alter table my_user rename column name3 to name33;
select *
from my_user;

-- 删除字段
alter table my_user
    drop column name33;

insert into my_user
values (1, 'xx1', 'xx2');
commit;
-- **分析性：维度建模
-- **交易性：


--建立价格区间表
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
);
drop table t_address;


--账务表--
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);

-- todo 1 基本结构创建
-- 查看当前用户
select user
from dual;
select sysdate
from dual;

--创建表空间
--datefile '地址'(告诉用户表存在哪个文件里）
--size
--autoextend on
--next

--创建用户 default tablespace 默认表空间

--给新用户授权 grant dba to c##wateruser;

--查看用户

-- todo 2 数据类型
-- 字符串类型
-- mysql选择
-- 数据不多且追求速度
-- name char(10) name插入数据就一定会占用10字节空间
-- 数据量比较大
-- name varchar(10) name插入数据'123' 就一定会占用3字节空间
-- long 2G

-- oracle
-- name varchar2(20) 在一定条件下把汉字,普通字符按照2个字节处理数字1字节

-- hive 字符串
-- string 2G 可变

-- 数字类型
-- number(5) 数字有5位,小数点后没有
-- number(5,2) 总共5位小数点后2位 188.88
-- number 整数 100 -10

-- 时间类型
-- todo mysql中可以有 date datetime year等时间类型
-- 并且字符串可以和这些时间类型比较插入数据
-- todo oracle 中 date 2000-10-10 10:10:10
-- 不能和字符串进行比较插入

create table xxx
(
dt date
);
-- date 2000-10-10 10:10:10
-- oracle时间基本使用 date类型
select SYSDATE
from dual;
insert into xxx
values (SYSDATE);
-- 在oracle中把时间类型转化为字符串
select *
from xxx
where to_char(dt, 'yyyy-mm-dd hh24:mi:ss') = '2024-04-17 12:07:11';
-- timestamp ==> current_timestamp
-- 区分 ==> 时区
select current_timestamp
from dual;
-- 把unix类型的时间戳转化为data时间类型
SELECT date '1970-01-01' + NUMTODSINTERVAL(1713327444, 'SECOND')
from dual;

-- todo 3 表操作
-- 扩展(了解)
-- 如果想要主键自增长需要设置自增序列
-- create sequence seq_userinfo
-- increment by 1
-- start with 1
-- nomaxvalue
-- nominvalue
-- cache 20;
-- 查看表结构
-- 在命令窗口 desc 表名
-- 在pl/sql中 SELECT DBMS_METADATA.GET_DDL('TABLE','XXX') FROM DUAL;
-- 注意: 表名必须大写

-- 1. 创建表(主键没有自增长)
create table my_user
(
id number primary key,
name1 varchar2(30),
name2 varchar2(30)
);

-- 修改表结构
-- 2. 增加一个字段
-- ALTER TABLE 表名称 ADD(列名 1 类型 [DEFAULT 默认值]，列名 1 类型 [DEFAULT 默认值]...)
alter table my_user
add (name3 varchar2(30));
-- 增加多个字段
alter table my_user
add (name4 varchar2(30),name5 varchar2(30));

-- 3. 修改字段
-- 修改数据类型不能修改字段名
-- ALTER TABLE 表名称 MODIFY(列名 1 类型 [DEFAULT 默认值]，列名 1 类型 [DEFAULT 默认值]...)
alter table my_user
modify (
name1 varchar2(20),
name2 varchar2(10)
);

-- 4. 修改字段名
-- ALTER TABLE 表名称 RENAME COLUMN 原列名 TO 新列名
alter table my_user rename column name5 to name55;

-- 5. 删除一个字段
-- ALTER TABLE 表名称 DROP COLUMN 列名
alter table my_user
drop column name55;

-- 6. 删除多个字段
-- ALTER TABLE 表名称 DROP (列名 1,列名 2...)
alter table my_user
drop (name4, name3);

-- 7. 删除表
drop table 表名;

-- todo 4 数据增删改
-- 默认开启事务的只要使用了增删改(都需要commit)
-- 创建表
-- 插入一条数据
insert into my_user
values (1, 'xxx1', 'xxx2');
select *
from my_user;
commit;

-- 插入多条数据(不能同时插入多条数据)
insert into my_user
values (2, 'xxx3', 'xxx4');
insert into my_user
values (3, 'xxx5', 'xxx6');
rollback;
select *
from my_user;
insert into my_user
values (4, 'xxx7', 'xxx8');
commit;

-- 数据修改和mysql一样
-- update 表名 set 字段名='xxx' where ;
-- 数据删除和mysql一样
-- delete from xxx where ;

-- delete可以回滚
-- truncate table 不可以回滚
-- 把数据表直接删除然后从新创建新的

-- todo 5 条件查询语句
-- 模糊查询 NAME like
-- 逻辑运算符优先级问题
-- 查询业主名称包含“刘”的或者门牌号包含 5 的业主记录，并且地址编号为 3 的记录。
select *
from T_OWNERS
where (name like '%刘%' or HOUSENUMBER like '%5%')
and ADDRESSID = 3;

-- 三种去重方式(查询 t_account 中去重的month)
select MONTH
from T_ACCOUNT;

-- 方法1
select distinct month
from T_ACCOUNT;
-- 方法2
select MONTH
from T_ACCOUNT
group by MONTH;
-- 方法3
-- from > where > group by > having > select > order by > limit
with t1 as (
select month, row_number() over (partition by MONTH order by MONTH) rn
from T_ACCOUNT)
select *
from t1
where rn = 1;

-- 基于伪列查询
-- 伪列并不真实存在于表中他是根据orcale在读取插入数据的时候根据物理地址信息编码形成的信息
-- 查询t_owners
select *
from T_OWNERS;
-- rownum 基于查询的自动产生的一个编号并且一定从1开始
-- rownum 行号
select rownum, T_OWNERS.*
from T_OWNERS;
-- rownum<5的所有数据
select rownum, T_OWNERS.*
from T_OWNERS
where ROWNUM < 5;
-- rownum>5的所有数据使用rownum的时候不要使用> 不支持
select rownum, T_OWNERS.*
from T_OWNERS
where ROWNUM > 5;
-- 基于查询结果获取的编号并且必定从1开始
with t1 as (
select rownum rn, T_OWNERS.*
from T_OWNERS)
select *
from t1
where rn > 5;
-- 查询rownum大于3的所有数据
with t1 as (
select rownum rn, T_OWNERS.*
from T_OWNERS)
select *
from t1
where rn > 3;
-- rowid 行id (T_OWNERS)
select ROWID, T_OWNERS.*
from T_OWNERS;
-- 通过 rowid 可以指定查数据
select *
from T_OWNERS
where ROWID = 'AAASuiAANAAAAC9AAA';

-- todo 6 rowid 和 rownum的使用
-- todo 6.1 rowid 去重(t_account)
-- 1. 查看rowid
select rowid
from my_user;

-- 2. 通过rowid去重(效率较低不建议使用)
-- 按照月份去重(按照月份分组并获取每组中的最小值rowid)
delete
from my_user
where rowid not in (select min(rowid)) from my_user
group by id, name);

-- todo 6.2 删除完全一样的重复数据
-- 准备数据用作修改删除
-- 表中有完全相同的数据(准备表)
create table my_test
(
id number,
name varchar2(50)
);
truncate table my_test;
insert into my_test
values (1, '老王');
insert into my_test
values (1, '老王');
insert into my_test
values (2, '老李');
insert into my_test
values (2, '老李');
insert into my_test
values (3, '老张');
insert into my_test
values (3, '老张');
commit;
select *
from my_test;

-- 4. 修改一条指定数据update
-- 不用rowid进行数据修改(不可行)

-- 按照rowid就行数据修改

-- 5. 删除重复数据
-- 只删除重复数据(注意:如果有数据本身就不重复会不会受影响?)

--todo 6.3 rownum 分页限制数据数量显示(oracle中没有limit)
--rownum只有在rownum<xx的时候才会显示
--每一个结果集都会有一个rownum
--1. 查看rownum t_owners表

--2.分页显示[ <=页数 * 每页个数, > (页数-1) * 每页个数 ]
--每页显示2条数据显示第3页数据

--3.排序分页
with t1 as (
select *
from T_OWNERS
order by name
),
t2 as (
select ROWNUM rm, t1.*
from t1
)
select *
from t2
where rm <= 3 * 2
and rm > (3 - 1) * 2;

-- todo 7 聚合函数练习
-- 1. 按区域统计水费之和
-- 最终显示区域: t_area.name
-- 总钱数: sum(t_account.money)
select ta.NAME, sum(tt.MONEY)
from t_account tt
join t_area ta
on tt.AREAID = ta.ID
group by ta.NAME;

-- 2. 查询水费合计大于 169000 的区域及水费合计
-- having 方式1
select ta.NAME, sum(tt.MONEY) sy
from t_account tt
join t_area ta
on tt.AREAID = ta.ID
group by ta.NAME
having sum(tt.MONEY) > 169000;

-- with 方法2
with t1 as (
select ta.NAME te, sum(tt.MONEY) sy
from t_account tt
join t_area ta
on tt.AREAID = ta.ID
group by ta.NAME
)
select t1.te, t1.sy
from t1
where sy > 169000;

-- hive中，不能直接select cout(*) from 表,应该分组求再的乘

-- todo 8 连接查询
-- mysql方式
-- 内连接: a inner join b ==> a join b
-- 外连接: a left/right join b ==> a left/right outer join b
-- 笛卡尔积: a,b
-- oracle方式
--左连接 : select xxx from 表1,表2 where 表1.字段=表2.字段(+);
--右连接 : select xxx from 表1,表2 where 表1.字段(+)=表2.字段;
--内连接 : select xxx from 表1,表2 where 表1.字段=表2.字段;

-- 1. 查询业主(t_owners)的账务记录(t_account)
-- 显示业主编号、名称、年、月、金额。如果此业主
-- mysql方式(inner join)

-- mysql方式(join)

-- oracle方式

-- 2. 查询业主(t_owners)的账务记录(t_account)
-- 显示业主编号、名称、年、月、金额。如果此业主没有账务记录也要列出姓名
-- mysql方式(left join)

-- mysql方式(left outer join)

--oracle方式

-- 3. 笛卡尔积
-- 创建笛卡尔积
-- mysql方式(inner join 没有on)

-- cross join(交叉连接)

-- oracle方式(不加where)

-- 4. 笛卡尔积的使用
-- 年份月份
-- 2020 01
-- 2020 02
-- 2020 03
-- 2020 04
-- 2020 05
-- 2020 06
with t1 as (
select '01' as 月份
from dual
union all
select '02' as 月份
from dual
union all
select '03' as 月份
from dual
union all
select '04' as 月份
from dual
union all
select '05' as 月份
from dual
union all
select '06' as 月份
from dual
),
t2 as (
select '2020' as 年份
from dual
)
select *
from t1,
t2;

-- todo 9 子查询
-- 1. 单行子查询 : 返回的是一行一列
-- 查询 2012 年 1 月用水量大于 1月整月平均值的台账记录(month需要为'01',T_ACCOUNT表)
select *
from T_ACCOUNT
where YEAR = '2012'
and MONTH = '01'
and USENUM > (select avg(USENUM)
from T_ACCOUNT
where YEAR = '2012'
and MONTH = '01');

-- 2. 多行子查询返回多条数据
-- 查询 2012 年 1月 2月用水量大于 2012整年平均值的台账记录

-- 3. from 子句中的子查询
-- 查询显示业主编号，业主名称，业主类型名称，条件为业主类型为”居民” T_OWNERS, T_OWNERTYPE，
-- 1. from子句中写法

-- 2. 内连接写法

-- 4. select 子句中的子查询
-- 4.1 列出业主信息，包括 ID ，名称，所属地址 T_OWNERS T_ADDRESS
-- 1. 内连接的方法
select tr.ID, tr.NAME, te.NAME
from T_OWNERS tr
join T_ADDRESS te
on tr.ADDRESSID = te.ID

-- 2. select子句中写法
select tr.ID,
tr.NAME,
(select name
from T_ADDRESS te
where tr.ADDRESSID = te.ID)
from T_OWNERS tr;

-- 4.2 列出业主信息，包括 ID ，名称，所属地址, 所属区域 T_OWNERS T_ADDRESS T_AREA
-- 1. 内连接的方法
select T_OWNERS.ID, T_OWNERS.NAME, T_ADDRESS.NAME, T_AREA.NAME
from T_OWNERS
join T_ADDRESS
on T_OWNERS.ADDRESSID = T_ADDRESS.ID
join T_AREA
on T_ADDRESS.AREAID = T_AREA.ID;

-- 2. select子句中写法
select T_OWNERS.ID,
T_OWNERS.NAME,
(select T_ADDRESS.NAME from T_ADDRESS where T_OWNERS.ADDRESSID = T_ADDRESS.ID),
(select T_AREA.NAME
from T_AREA,
T_ADDRESS
where T_ADDRESS.AREAID = T_AREA.ID
and T_OWNERS.ADDRESSID = T_ADDRESS.ID)
from T_OWNERS;

select substr('6666@qq.com', instr('6666@qq.com', '@') + 1, length('6666@qq.com') - instr('6666@qq.com', '@') + 1)
from dual;

select sysdate from dual;
select add_months(sysdate,2) from dual;

select sysdate + interval '1' year from dual;
select sysdate + interval '1' month from dual;
select sysdate + interval '1' day from dual;
select sysdate + interval '1' hour from dual;
select sysdate + interval '1' minute from dual;
select sysdate + interval '1' second from dual;

select 1+1 from dual;