Oracle基础2

最新推荐文章于 2024-07-23 17:37:44 发布

Ashenlex

最新推荐文章于 2024-07-23 17:37:44 发布

阅读量567

点赞数 27

文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/Ashenlex/article/details/138231349

版权

数据准备

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 单行函数
-- 使用函数后返回的结果为单行单列

-- 1. 字符函数
-- 1.1 求字符串长度 length()
select length('123')
from dual;
select length(123)
from dual;
-- 1.2 求字符串的子串 substr(字符串,起始位置,数据数量)
select substr('123@456', 5, 3)
from dual;
-- 1.3 求子串在字符串中的位置 instr(字符串,子串) => 位置
select instr('123@456', '@')
from dual;
-- 1.4 substr 和 instr 结合使用获取指定字符串中指定字符后的数据
-- 已知字符串 6666@qq.com 获取邮箱类型 ==>qq.com
select substr('6666@qq.com', instr('6666@qq.com', '@') + 1, 10)
from dual;
-- 1.5 字符串拼接 concat(字符串1,字符串2)

-- 1.6 多个字符串拼接 ||

-- 2. 数值函数
-- 2.1 四舍五入函数 round
select round(3.1416)
from dual;
-- 2.2 指定四舍五入小数位数
select round(3.1416, 2)
from dual;
-- 2.3 截取函数 trunc(数值,小数位数)
select trunc(3.1416)
from dual;
-- 2.4 取模 mod(数值,被除数)

-- 2.5 创建表my_test02 创建语句如下
create table my_test02
(
num number
);

insert into my_test02
values (1);
insert into my_test02
values (2);
insert into my_test02
values (3);
insert into my_test02
values (4);
insert into my_test02
values (5);
insert into my_test02
values (6);
insert into my_test02
values (7);
-- 获取表my_test02中所有的偶数
with t1 as (
select num, mod(num, 2) md
from my_test02
)
select num
from t1
where md = 0;

-- 3. 时间函数
-- 3.1 获取系统时间 sysdate

-- 3.2 加月函数 add_months(时间类型,3)

-- 3.3 加减时间通用方法 select sysdate + interval '1' minute from dual;

-- 3.4 时间相关函数汇总
-- Orcale中时间类型date是必须为年月日时分秒的样式
-- 和mysql中不一样没有单独的year时间类型

-- 3.4.1 to_date是把字符串转化为时间类型:
SELECT TO_DATE('2006-05-01 19:25:34', 'YYYY-MM-DD HH24:MI:SS')
FROM DUAL;

SELECT TO_DATE('2006-05-01 19:25', 'YYYY-MM-DD HH24:MI')
FROM DUAL;

SELECT TO_DATE('2006-05-01 19', 'YYYY-MM-DD HH24')
FROM DUAL;

SELECT TO_DATE('2006-05-01', 'YYYY-MM-DD')
FROM DUAL;

SELECT TO_DATE('2006-05', 'YYYY-MM')
FROM DUAL;

SELECT TO_DATE('2006', 'YYYY')
FROM DUAL;

-- 3.4.2 trunc函数截取后的结果依然为时间类型
select sysdate S1,

trunc(sysdate) S2,

trunc(sysdate, 'year') YEAR,

trunc(sysdate, 'month') MONTH,

trunc(sysdate, 'day') DAY

from dual;
-- 获取当前日期和日期2024-04-10 的差值
select trunc(sysdate, 'dd')
from dual;

-- 3.4.3 to_char函数的结果为字符串类型
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') as nowTime
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;

-- 3.5 字符串转数字 TO_NUMBER

-- 5. 其他函数
-- 空值处理函数 NVL
-- NVL (检测的值，如果为 null 的值)

-- NVL2 (检测的值，如果不为 null 的值，如果为 null 的值)

-- todo 2 行转列
-- 1. 按月份统计 2012 年各个地区的水费 T_AREA t_account
-- 区域一月二月三月
-- 海淀 xxx xxx xxx
-- 西城 xxx xxx xxx

-- 2. 按季度统计 2012 年各个地区的水费 t_account t_area
-- 区域第一季度第一季度第一季度
-- 海淀 xxx xxx xxx
-- 西城 xxx xxx xxx
select T_AREA.name as 区域,
sum(case when T_ACCOUNT.MONTH in ('01', '02', '03') then MONEY end) 第一季度,
sum(case when T_ACCOUNT.MONTH in ('04', '05', '06') then MONEY end) 第二季度,
sum(case when T_ACCOUNT.MONTH in ('07', '08', '09') then MONEY end) 第三季度
from T_ACCOUNT
join T_AREA
on T_ACCOUNT.AREAID = T_AREA.ID
group by T_AREA.name;

-- todo 3 集合运算
-- 集合运算，集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括：
-- union all(并集) ，返回各个查询的所有记录，包括重复记录。
-- union(并集) ，返回各个查询的所有记录，不包括重复记录。
-- intersect(交集) ，返回两个查询共有的记录。
-- minus(差集) ，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录

-- 并集运算
-- 1. UNION ALL 不去掉重复记录 t_owners
select * from T_OWNERS
union all
select * from T_OWNERS;
-- 2. UNION 去掉重复记录 t_owners
select * from T_OWNERS
union
select * from T_OWNERS;

-- 交集运算
-- 3. intersect 返回属于两个集合的部分
-- t_owners 中id <= 7
-- t_owners 中id >= 4
select * from T_OWNERS where ID<=7
intersect
select * from T_OWNERS where ID>=4;
-- inner join方式
-- select T_OWNERS.id,T_OWNERS.name from T_OWNERS
-- inner join
-- (select id,name from T_OWNERS where ID>=4) t2 on T_OWNERS.id=t2.ID;

-- t1,t2 where方式

-- 差集运算
-- 4. minus 返回属于第一个集合但不属于第二个集合的部分
-- t_owners id<=7
-- t_owners id>=4
select * from T_OWNERS where ID<=7
minus
select * from T_OWNERS where ID>=4;
-- 5. minus 运算符来实现分页
-- t_owners id<=2
-- t_owners id>=0
select * from T_OWNERS where ID<=4
minus
select * from T_OWNERS where ID<=2;

-- todo 4 <窗口函数>
-- over(
-- partition by 分组字段
-- order by 排序字段
-- rows between current row and 1 following
-- range（连续的） between 1 preceding and 1 following
-- )
-- current row 当前行
-- 1 following 后1行
-- 1 preceding 前一行

-- t_owners表
-- 按照区域统计id的和显示id,name,addressid

Ashenlex

关注

27
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
Oracle基础2

sum(case when T_ACCOUNT.MONTH in ('01', '02', '03') then MONEY end) 第一季度,sum(case when T_ACCOUNT.MONTH in ('04', '05', '06') then MONEY end) 第二季度,sum(case when T_ACCOUNT.MONTH in ('07', '08', '09') then MONEY end) 第三季度。-- union(并集) ，返回各个查询的所有记录，不包括重复记录。
复制链接

扫一扫