SQL 语言： DDL , DML , DQL , TCL
DDL语言: 数据定义语言，用于操作元数据。 CREATE(创建) DROP(删除) ALTER(修改) TRUNCATE(清空)
元数据：记录对象结构信息的数据
源数据：业务系统数据
1. 创建数据库（模式）
CREATE [DATaBASE|SCHEMA] 数据库名;
CREATE DATABASE MY1218; # 起名通用方法：英文字母，数字，下划线_ 的组合，以英文字母开头
USE my1218; -- 使用数据库
-- 连接数据库五要素
IP 端口 数据库名 账户 密码
-- 在mysql数据里
一个用户可以控制多个数据库（模式），数据库和模式可以互换
-- oracle数据库
一个数据库里可以创建多个用户，每个用户在创建时都会创建一个同名的模式
2.0 float 2.00000001 DOUBLE 2.0000000000000001
2.0 2.1 100
'2024-01-01'
'abcd'
char(10) 插入 '#)/1ac' 实际存储 '#)/1ac ' -- 定长
varchar(10) 插入 '#)/1ac' 实际存储 '#)/1ac' -- 不定长
ALTER TABLE TABLE_NAME MODIFY COLUMN column_name new_datatype;
DROP TABLE table_name;
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);
CREATE USER ZHANGSAN@'127.0.0.1' IDENTIFIED BY '123456';
DML语言：数据操作语言，针对表里真实的数据 INSERT(插入) UPDATE(修改) DELETE(删除)
-- 插入数据
INSERT INTO TABLE_NAME VALUES (值1，值2...);
INSERT INTO TABLE_NAME SELECT 语句; -- 数据开发
ODS DWD
INSERT INTO DWD_TABLE_NAME SELECT * FROM ODS_TABLE_NAME;
-- 修改数据
UPDATE TABLE_NAME SET COLUMN_NAME=值 [WHERE 条件];
-- 删除数据
DELETE FROM TABLE_NAME [WHERE 条件];
-- ------------------------------------------------------------------------
DQL语言：数据查询语言，查询表里的数据。 SELECT(查询)
SELECT column1, column2, ...
[FROM table_name] # 从表里获取数据
[WHERE condition] # 对表里原始数据进行条件筛选
[GROUP BY column_name] # 按column_name分组，一般配合聚合函数使用，相同的数据分为一组，返回一条数据
[HAVING condition] # 对聚合后数据进行条件筛选
[ORDER BY column_name [ASC | DESC]] # 排序
[LIMIT number]; # 分页查询
SELECT 1+1;
SELECT 'ABCD';
FROM -- 从
SELECT * FROM t_order;
-- 查询 所有列 从 表
-- 字段
SELECT o_id,o_consignee,o_consignee_tel FrOM t_order;
SELECT o_consignee,o_consignee_tel,o_id FROM t_order;
-- SQL语句书写的注意事项：
1. 所有的关键字和表名，字段名都时不区分大小写。表里真实的数据最好区分大小写 'T000001'。
2. 每一个sql语句写完，最好以 ; 结尾。
3. sql语句里面所有符号都得是英文状态下的。
4. 空格的数量不影响sql的运行
-- 起别名 在sql语句里对字段名或者表名重命名（内存）
# 起名通用方法：英文字母，数字，下划线_ 的组合，以英文字母开头
# 起别名可以用空格或者 AS
select o_consignee 收货人,o_consignee_tel as 联系电话,o_id 订单编号 FROM t_order as t; -- 别名只针对当前sql有效
select o_consignee 1收货人1,o_consignee_tel as `*联系电话`,o_id 订单编号 FROM t_order AS T; -- 别名只针对当前sql有效
# 代码编写通用原则： 怎么表示下级： 上级名.下级名
select t.o_id,t.o_consignee,t.o_consignee_tel,t.o_delivery_address 收获地址 ,T.o_order_status from t_order t;
SELECT A.od_id,A.od_order_price,A.od_sku_num,A.sku_name FROM t_order_detail A;
SELECT t_order.o_create_time,t_order.o_consignee_tel FROM t_order; -- 不推荐
SELECT A. ,B. ,C. ,D. FROM A B C D ; --
-- T0 T1 T2 T3
WHERE -- 对表里原始的数据（对原始数据进行算术运算或者行函数处理之后的数据）
select * FROM t_order t WHERE t.o_total_amount = 0 ;
select * FROM t_order t WHERE t.o_total_amount + 5 = 5 ;
-- 1. 算术运算符 + - * / 3+4*5 (3+4)*5
-- 2. 比较运算符 > 大于 ，>= 大于等于， < 小于， <= 小于等于 ， = 等于， != <> 不等于
-- 查询t_order_detail表里购买单价大于 500 的数据
SELECT * from t_order_detail a where a.od_order_price > 500 ;
-- 3. 逻辑运算符 NOT(非) AND(与) OR(或)
AND(与) -- 连接两个条件，当两个条件同时成立时，整个条件才成立
OR(或) -- 连接两个条件，当两个条件至少成立一个，整个条件就成立
NOT(非) -- 对后一个条件取反
-- 逻辑运算符 优先级： NOT 优于 AND 优于 OR
-- 查询t_order表里，付款方式为2并且运费大于10
SELECT * FROM t_order T WHERE T.o_payment_way=2 AND T.o_total_amount>10 ;
-- 查询t_order表里，付款方式为2或者运费大于10
SELECT * FROM t_order T WHERE T.o_payment_way=2 OR T.o_total_amount>10 ;
-- 查询t_order表里，付款方式不为2
SELECT * FROM t_order T WHERE NOT T.o_payment_way=2 ;
-- 查询t_order表里，付款方式不为1 但是运费小于10
SELECT * FROM t_order T WHERE NOT T.o_payment_way=1 AND T.o_total_amount<10 ;
SELECT * FROM t_order T WHERE T.o_payment_way!=1 AND T.o_total_amount<10 ;
SELECT * FROM t_order T WHERE NOT (T.o_payment_way=1 AND T.o_total_amount<10 ) ;
SELECT * FROM t_order T WHERE T.o_payment_way!=1 OR T.o_total_amount>=10 ;
IN (值1,值2...) -- 在 .. 里面
-- 收件人为 ‘张文’和‘王云’和‘丁冬琴’的订单信息
SELECT * FROM t_order t WHERE t.o_consignee='张文' or t.o_consignee='王云' or t.o_consignee='丁冬琴' ;
SELECT * FROM t_order t WHERE t.o_consignee in ('张文','王云','丁冬琴');
between x and y -- 在 x 和 y 之间（闭区间） x <= y >=x and <= y
-- 查询t_order_detail表里购买总价在300-500之间的明细信息
select * from t_order_detail t where t.od_order_price*t.od_sku_num >=300 and t.od_order_price*t.od_sku_num <=500 ;
select * from t_order_detail t where t.od_order_price*t.od_sku_num between 300 and 500 ;
like -- 模糊查询
% -- 通配符 表示任意个任意字符
_ -- 占位符 表示一个任意字符
-- 查询姓王的收件人的订单信息
SELECT * FROM t_order T WHERE T.o_consignee LIKE '王%';
-- 查询收件人名字中带‘王’的订单信息
SELECT * FROM t_order T WHERE T.o_consignee LIKE '%王%';
NULL -- 空值
空值的特性：
1. 不参与算术运算，任何值与空值进行算术运算得到的结果都是 空值
2. 不参与比较运算，任何值与空值进行比较，结果都是不成立
3. 不参与聚合函数运算
SELECT * FROM t_order T WHERE null != null;
空值的判断：IS NULL / IS NOT NULL
SELECT * FROM t_order T WHERE T.o_consignee IS null;
SELECT * FROM t_order T WHERE T.o_consignee IS NOT null;
空值的处理：
if(条件,条件成立的返回值,条件不成立的返回值) -- 流程控制
IFNULL(字段,为空的返回值)
COALESCE(字段,...)
-- 如果收件人为空返回‘未知的’，如果不为空返回本身
select if(o_consignee is null,'未知的', o_consignee) new_consignee,o_consignee from t_order;
select IFNULL(o_consignee ,'未知的') new_consignee,o_consignee from t_order;
SELECT column1, column2, ...
[FROM table_name] # 从表里获取数据
[WHERE condition] # 对表里原始数据进行条件筛选
[GROUP BY column_name] # 按column_name分组，一般配合聚合函数使用，相同的数据分为一组，返回一条数据
[HAVING condition] # 对聚合后数据进行条件筛选
[ORDER BY column_name [ASC | DESC]] # 排序
[LIMIT number]; # 分页预览
-- 查询 t_order_detail 表中购买单价大于500的交易记录
SELECT * FROM t_order_detail T WHERE T.od_order_price >500;
-- 查询 t_order_detail 表中总成交价小于等于300的交易记录(总成交价=购买价格*购买数量)
SELECT * FROM t_order_detail T WHERE T.od_order_price*T.od_sku_num <=300 ;
-- 查询 t_order_detail 表中订单编号为'T000006' 并且总成交价格小于200的交易记录
SELECT * FROM t_order_detail T WHERE T.od_order_price*T.od_sku_num < 200 AND T.o_id = 'T000006';
-- 查询 t_order_detail 表中订单编号为'T000006'的交易记录 或者 商品名称为'西瑞 非转基因食用油5L'并且总成交价格大于120的交易记录
SELECT * FROM t_order_detail T WHERE T.o_id = 'T000006' OR T.sku_name = '西瑞 非转基因食用油5L' AND T.od_order_price*T.od_sku_num > 120;
-- 查询 t_order_detail 表中总成交价在200到500之间的交易记录
SELECT * FROM t_order_detail T WHERE T.od_order_price*T.od_sku_num BETWEEN 200 AND 500 ;
-- 查询 t_order_detail 表中订单编号为'T000004'或者'T000005'或者'T000006'的交易记录
SELECT * FROM t_order_detail T WHERE T.o_id IN ('T000004','T000005','T000006');GROUP BY -- 分组 根据分组字段分组，将字段里相同的数据分为一组，每一组返回一条数据。 配合聚合函数使用
-- 去重
SELECT O_ID FROM t_order_detail GROUP BY O_ID;
-- 聚合函数
sum() 求和 avg() 求平均值 max() 求最大值 min() 求最小值 count() 统计行数
-- 求每个订单的单价的平均值
SELECT O_ID,AVG(od_order_price),SUM(od_order_price),MAX(od_order_price),MIN(od_order_price),COUNT(od_order_price)
FROM t_order_detail GROUP BY O_ID;
sum() 求和 avg() 求平均值 -- 只能用于数据类型是数值的字段
max() 求最大值 min() 求最小值 count() 统计行数 -- 无要求
-- 算术运算
数值数据类型 在任何情况下都可以进行算术运算
字符串数据类型不可以进行算术运算，除非字符串本身的值是数字。 'A'+2 × '200'*2
日期数据类型：日期之间可以相减，返回天数差。日期之间不可以 +*/
日期可以加减数字，返回多少天之后/之前 的日期。日期不可以乘除数字。
-- 比较运算
数值数据类型：正常比较
字符串数据类型： 从左往右逐位比较字符的ascii值。谁的ascii值大谁就大。有字符比没有字符大
'0' 'a' 'A' '%' '&' '@' '我'
'0'~'9' 的ascii值 依次增大
'a'~'z' 的ascii值 依次增大
'A'~'Z' 的ascii值 依次增大
'AB' < 'ABCD' 'B' > 'ABCD'
日期数据类型：依次比较年月日时分秒，谁的数值大谁就大。
'2023-12-31' = '2023-12-31' '2023-12-31' < '2024-01-01'
count(*) count(1) count(字段名) 的区别：
count(*) 和 count(1) 在运行结果上没有区别，统计表里所有数据的行数（分组的话就统计组内所有数据的行数）
count(字段名) 统计字段里非空数据的行数
SELECT COUNT(*),COUNT(1),COUNT(T.o_consignee) FROM t_order T;
-- 在表里查询具体的值 会以值作为字段名新生成一个字段，用值填充字段下的每一行
SELECT T.o_consignee , 2 数字,'abcd' 字符串 FROM t_order T;
-- 聚合函数，分组字段，普通字段 的关系：
写 group by 时，SELECT 后面只能写 分组字段和聚合函数
SELECT O_ID,AVG(od_order_price),SUM(od_order_price),MAX(od_order_price),MIN(od_order_price),COUNT(od_order_price)
FROM t_order_detail GROUP BY O_ID;
不写 group by 时，SELECT 后面一旦出现聚合函数，select 后面只能全部写聚合函数
SELECT AVG(od_order_price),SUM(od_order_price),MAX(od_order_price),MIN(od_order_price),COUNT(od_order_price)
FROM t_order_detail ;
select t.o_id,t.od_id,t.od_order_price,t.od_sku_num,t.sku_name
FROM t_order_detail t ;
-- 多字段分组 按多个字段的组合值来分组
SELECT O_ID,sku_name FROM t_order_detail GROUP BY O_ID,sku_name;
HAVING -- 对分组聚合的结果集(分组字段和聚合函数)进行条件筛选
-- 查询每个订单不同商品购买总价的最大值小于500 的订单编号的购买价格总价，购买的商品总件数
SELECT O_ID,sum(t.od_order_price*t.od_sku_num) 购买总价, sum(t.od_sku_num) 商品总件数
FROM t_order_detail t
GROUP BY O_ID
HAVING MAX(t.od_order_price*t.od_sku_num) < 500;
SELECT O_ID,sum(t.od_order_price*t.od_sku_num) 购买总价, sum(t.od_sku_num) 商品总件数
FROM t_order_detail t -- 19
GROUP BY O_ID -- 19
HAVING O_ID IN ('T000002','T000003','T000004'); -- 不推荐 -- 8 3
SELECT O_ID,sum(t.od_order_price*t.od_sku_num) 购买总价, sum(t.od_sku_num) 商品总件数
FROM t_order_detail t -- 19
WHERE O_ID IN ('T000002','T000003','T000004') -- 5
GROUP BY O_ID ; -- 5
-- 查询语句里 表关联和分组聚合是最耗费资源。
-- 要求在 表关联或者分组聚合 之前先筛选数据。
ORDER BY column_name [ASC | DESC] -- 对查询的结果进行排序再展示
ASC -- 升序 从小到大 排序方式不写默认为升序
DESC -- 降序 从大到小
SELECT O_ID,sum(t.od_order_price*t.od_sku_num) 购买总价, sum(t.od_sku_num) 商品总件数
FROM t_order_detail t -- 19
WHERE O_ID IN ('T000002','T000003','T000004','T000005') -- 5
GROUP BY O_ID
order by 购买总价 desc;
-- 多字段排序
ORDER BY COL1 [ASC | DESC],COL2 [ASC | DESC] ....
多字段排序逻辑：
先按col1的排序方式进行排序，如果col1里有重复的值，再对重复的数据按col2的排序方式进行排序...
SELECT * FROM t_order_detail T
ORDER BY T.o_id DESC,T.od_order_price ;
LIMIT n,m -- 分页查询 指定查询某些行的数据
-- n,m 两个数字 n 表示行数，从0开始，0表示第一行 。m 表示要展示的数据条数
SELECT * FROM t_order_detail T
ORDER BY T.o_id DESC,T.od_order_price LIMIT 5; -- LIMIT 0,5
select 语句编写顺序：
SELECT FROM WHERE GROUP BY HAVING ORDER BY LIMIT
FROM WHERE GROUP BY HAVING SELECT ORDER BY LIMIT
-- sql 复杂查询 子查询和表连接
-- 子查询 (select 语句)
1. 字段里查询（写在 select 后面） 要求子查询返回的结果必须是单行单列
SELECT T.* ,
(SELECT A.o_consignee FROM t_order A WHERE A.o_id=T.O_ID) 收件人
FROM t_order_detail T ;
2. FROM 后面的子查询 将select语句返回的结果集当作表来使用(要求子查询能单独运行)
-- 子查询需要起别名，子查询如果有对字段进行算术运算和函数处理后生成的字段，这些字段最好也要起别名
select a.o_id,a.o_consignee,a.o_consignee_tel,a.o_total_amount from
(select t.o_id,t.o_consignee,t.o_consignee_tel,
t.o_total_amount+20 o_total_amount,t.o_delivery_address from t_order t) a ;
3. 条件里的子查询
3.1 用比较运算符连接的条件，要求子查询返回的结果必须是单行单列
-- 查询t_order_detail表里商品单价比‘真无线蓝牙5.0耳机’ 的单价高的商品信息
SELECT * FROM t_order_detail t1 where t1.od_order_price >
(select od_order_price from t_order_detail t2 where t2.sku_name = '真无线蓝牙5.0耳机') ; -- 199
3.2 in 后面的查询，子查询返回的结果可以是多行
-- 查询其他订单里和 ‘T000001’订单里商品价格一样的信息
SELECT * from t_order_detail WHERE od_order_price IN
(SELECT od_order_price FROM t_order_detail WHERE O_ID = 'T000001')
AND O_ID != 'T000001';
-- 表关联
笛卡尔积： 两张表进行关联时，没有设置合适的关联条件，最终的结果的数据条数 = 两张表的数据条数相乘
-- 尽量避免笛卡尔积
select * from t_order,t_order_detail; -- 152
select * from t_order cross join t_order_detail; -- 152 交叉连接
select * from t_order join t_order_detail ON 1=1; -- 152 -- 关联条件不合适
数据发散：两张表进行关联时，设置合适关联条件。关联字段里数据的对应关系： 1对1 1对多 多对多
最终的结果的数据条数 = 对应关系相乘
select a.*,b.od_order_price,b.od_sku_num,b.sku_name from
(SELECT * FROM t_order WHERE O_ID IN ('T000001','T000003','T000005')) a
join t_order_detail b ON a.o_id=b.o_id;
表关联： 内关联和外关联
[INNER] JOIN -- 内关联 返回两张表都满足关联条件的数据
LEFT [OUTER] JOIN -- 左外关联 左侧的表为主表，右侧的表为从表
返回主表全部数据，从表展示满足关联条件的数据，不满足条件的数据全部填 NULL
RIGHT [OUTER] JOIN -- 右外关联 右侧的表为主表，左侧的表为从表
返回主表全部数据，从表展示满足关联条件的数据，不满足条件的数据全部填 NULL
create table a (aid int, aname varchar(10));
create table b (bid int, bname varchar(10));
select * from a;
select * from b;
-- 内关联
select * from a join b on a.aid=b.bid ;
-- 左外关联
select * from a left join b on a.aid=b.bid ;
-- 右外关联
select * from a right join b on a.aid=b.bid ;
-- 怎么使用左外关联实现内关联的效果
select * from a left join b on a.aid=b.bid where b.bid is not null;
select * from a left join b on a.aid=b.bid where a.aid=b.bid;
-- 多表关联 一般是用左外关联
select *
from a
left join b on a.col=b.col
left join c on a.col1=c.col1 -- 关联条件可以选择 a / b
left join d on c.col2=d.col2 -- 关联条件可以选择 a / b / c
WHERE
GROUP BY
-- 集合运算 结果集 并集 交集 补集
UNION [ALL] -- 并集 将两个结果集的数据纵向堆加在一起
UNION -- 会排序，然后去重，效率低
UNION ALL -- 不去重，效率高
SELECT * FROM t_order
UNION ALL
SELECT * FROM t_order; -- 16
SELECT * FROM t_order
UNION
SELECT * FROM t_order; -- 8
INTERSECT -- 交集 返回两个结果集都有的数据
SELECT * FROM t_order
INTERSECT
SELECT * FROM t_order where o_id in ('T000001','T000003','T000005'); -- 3
EXCEPT -- 差集 返回上一个结果集有，但是下一个结果集没有的数据
SELECT * FROM t_order where o_id in ('T000001','T000002','T000003')
EXCEPT
SELECT * FROM t_order where o_id in ('T000001','T000003','T000005'); -- 1
-- 注意事项
1. 所有的结果集要求字段的数量和数据类型要一一对应
2. 最终结果的字段名，数据类型和数量以第一个结果集为准
数据库： IP 端口：1521 数据库名： orcl 账户：scott 密码：123456
oracle数据库：
数据库名（实例）：orcl
模式：scott
用户：scott
Oracle：一个数据库（实例）里可以包含多个用户，创建用户是会自动创建和用户名同名的模式(schema)
mysql ：一个用户可以控制多个数据库名，数据库名和模式是一个意思。
-----------------------------------------------------------------------
dql语言： select -- 查询
SELECT * FROM EMP;
-- 查询 所有列 从 表名
表名：emp 员工信息表
EMPNO 员工编号
ENAME 员工姓名
JOB 岗位/职位/工种
MGR 上级编号
HIREDATE 入职日期
SAL 工资（月）
COMM 奖金（年）
DEPTNO 部门编号
SELECT * FROM DEPT;
表名： DEPT 部门信息表
DEPTNO 部门编号
DNAME 部门名称
LOC 部门地址
-- sql书写注意事项：
1. 关键字，表名，字段名 不区分大小写
2. 每一个sql语句写完最好 ; 结尾
3. sql里所有的符号都得是英文状态下
4. 表里真实的数据是区分大小写的
SELECT EMPNO,ENAME,SAL FROM EMP;
-- 查询所有员工的信息以及员工工资增加1000之后的工资
SELECT *,sal+1000 from emp; -- 报错 * 不能和字段一起写
SELECT E.*,SAL+1000 NEW_SAL1 FROM EMP E;
-- 起别名 用 英文字母，数字和下划线_ 组合
1.给字段起别名可以用 空格或者 as ，给表起别名只能用 空格
2.不能以数字开头，不能包含特殊字符
3.如果要以数字开头，或者要包含特殊字符，需要使用 "" 将别名括起来
单引号：强引用，所有被''括起来的都表示字符串 '1!Q4$%*/-+)(&hhg我'
双引号：弱引用，被""括起来的特殊字符会失去特殊含义，表示其本身。一般用于起别名
SELECT EMPNO "1 2*3" FROM EMP ;
'"' -- 表示字符 "
'''' -- 表示字符 '
"'" -- 用于起别名
所有从属关系怎么表示下级： 上级名.下级名
SELECT EMP.EMPNO 员工编号
,EMP.ENAME 员工姓名
,EMP.JOB
,EMP.MGR
,EMP.HIREDATE 入职日期
,EMP.SAL
,EMP.COMM
,EMP.DEPTNO
FROM (
SELECT EMP.EMPNO 员工编号
,EMP.ENAME 员工姓名
,EMP.JOB
,EMP.MGR
,EMP.HIREDATE 入职日期
,EMP.SAL
,EMP.COMM
,EMP.DEPTNO
FROM EMP
WHERE
GROUP BY
HAVING
ORDER BY)
WHERE
GROUP BY
HAVING
ORDER BY ;
------------------------------------------------
ORACLE数据库常用的数据类型：
NUMBER(P,S) -- 数值类型 定义时可以给长度，也可以不给长度。
-- P表示总长度，最大为38。S表示小数点后的位数，不写表示整数
-- NUMBER(4) -9999 ~ 9999 之间的任何整数
-- NUMBER(6,2) -9999.99 ~ 9999.99 之间的任何数
-- NUMBER 38位以内任意数字
VARCHAR2(n) -- 字符串类型 定义时一定要给长度，可变长。最大长度为4000(字节)
CHAR(n) -- 定长 ，最大长度为2000(字节)
-- 在ORACLE数据库一个中文汉字占2个字节
-- 怎么表示： 用''括起来
DATE -- 日期类型 ，定义时一定不能给长度
-- 怎么表示： 不能直接表示，需要使用函数或者关键字将数字或者字符串转成日期
DATE'2024-01-01' -- 表示2024年1月1号
-- 算术运算
数值类型：任何情况下都可以进行算术运算
字符串类型：不可以算术运算
日期类型：日期之间可以相减，得到两个日期的天数差。日期之间不能加乘除
日期可以加减数字，得到多少天之后/前的日期。日期不能乘除数字
-- 比较运算
数值类型：直接比较大小
字符串类型：从左往右逐位比较字符的ascii值，谁的ascii值大，谁就大
日期类型：依次比较年月日时分秒，谁的数值大谁就大
WHERE -- 对表里的原始数据(字段，字段算术运算，行函数处理的字段)进行筛选
-- 查询工资大于2000的员工信息
SELECT * FROM EMP WHERE SAL+500>2500;
-- 算术运算符 +-*/ 优先级： 先 */ 后 +-
-- 比较运算符 大于> 大于等于>= 小于< 小于等于<= 等于= 不等于!= <>
-- 逻辑运算符 NOT AND OR 优先级：NOT => AND => OR
AND -- 用于连接两个条件，当两边条件同时成立，and连接的大的条件才成立
OR -- 用于连接两个条件，当两边条件至少成立一个，or连接的大的条件就成立
NOT -- 对后的一个条件取反
IN -- 在..里面
-- 查询岗位为‘MANAGER’，‘ANALYST’的员工信息
SELECT * FROM EMP WHERE JOB IN ('MANAGER','ANALYST');
SELECT * FROM EMP WHERE JOB = 'MANAGER' OR JOB = 'ANALYST';
NOT IN -- 不在..里面
SELECT * FROM EMP WHERE JOB NOT IN ('MANAGER','ANALYST');
SELECT * FROM EMP WHERE NOT JOB IN ('MANAGER','ANALYST');
BETWEEN X AND Y -- 在 X 和 Y 之间（闭区间）
NOT BETWEEN X AND Y -- 不在 X 和 Y 之间（闭区间）
-- 在1981年入职的员工
SELECT * FROM EMP WHERE HIREDATE BETWEEN DATE'1981-01-01' AND DATE'1981-12-31';
-- 查询员工姓名的首字母在'A'到'F'之间
SELECT * FROM EMP WHERE SUBSTR(ENAME,1,1) BETWEEN 'A' AND 'F';
SELECT ENAME,SUBSTR(ENAME,1,1) FROM EMP;
LIKE -- 模糊查询
% -- 通配符，表示任意个任意字符
_ -- 占位符，表示一个任意字符
ESCAPE '字符' -- 转义 ，让特殊字符失去特殊含义，表示其本身
-- 查询姓名里包含'S'的员工信息
SELECT * FROM EMP WHERE ENAME LIKE '%S%';
SELECT * FROM EMP WHERE ENAME NOT LIKE '%S%'; -- 不包含'S'
-- 查询姓名里包含'%'的员工信息
SELECT * FROM TEST WHERE NAME LIKE '%\%%' ESCAPE '\';
NULL -- 空值
-- 空值的特性
1.与任何值进行算术运算得到的结果都是空值
2.与任何值进行比较，结果都是不成立
3.不参与聚合函数运算
-- 空值判断 IS NULL / IS NOT NULL
-- 空值的处理
NVL(字段,为空的返回值) -- 要求返回值的数据类型和字段的数据类型一致
SELECT COMM,NVL(COMM,-100) FROM EMP;
--查询工资大于2000,并且小于3000的员工信息
SELECT * FROM EMP WHERE SAL > 2000 AND SAL < 3000;
--查询工资小于等于2000,或者大于等于3000的员工信息
SELECT * FROM EMP WHERE SAL <= 2000 OR SAL >= 3000;
SELECT * FROM EMP WHERE NOT (SAL > 2000 AND SAL < 3000);
--查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息
SELECT * FROM EMP WHERE DEPTNO=10 AND JOB='MANAGER' OR DEPTNO=20 AND JOB='CLERK';
--查询所有工种不是MANAGER和CLERK，且工资大于或等于2000的员工的详细信息
SELECT * FROM EMP WHERE JOB NOT IN ('MANAGER','CLERK') AND SAL>=2000 ;
SELECT * FROM EMP WHERE JOB !='MANAGER' AND JOB !='CLERK' AND SAL>=2000 ;
-- 查询姓名是'SMITH'和'ALLEN'的信息
SELECT * FROM EMP WHERE ENAME IN ('SMITH','ALLEN');
-- 查询岗位是'SALESMAN'和工资大于1500的员工信息
SELECT * FROM EMP WHERE JOB='SALESMAN' AND SAL>1500;
-- 查询姓名中带 'M' 的员工信息
SELECT * FROM EMP WHERE ENAME LIKE '%M%';
--查询10和20部门的员工信息
SELECT * FROM EMP WHERE DEPTNO IN (10,20);
--查询倒数第二个字母为'E'的员工信息
SELECT * FROM EMP WHERE ENAME LIKE '%E_';
--查询姓名包含L和A的员工信息 L在前A在后
SELECT * FROM EMP WHERE ENAME LIKE '%L%A%';
--查询姓名包含L和A的员工信息 不知道L和A的顺序
SELECT * FROM EMP WHERE ENAME LIKE '%L%A%' OR ENAME LIKE '%A%L%';
SELECT * FROM EMP WHERE ENAME LIKE '%L%' AND ENAME LIKE '%A%';
--查询所有人的奖金加500之后的奖金信息
SELECT EMP.*,NVL(NVL(COMM,0)+500,0) NEW_COMM FROM EMP;
SELECT EMP.*,NVL(COMM+500,500) NEW_COMM FROM EMP;
--查询所有人的奖金+500之后大于600的员工信息
SELECT * FROM EMP WHERE COMM+500>600;
---------------------------------------------------------------------
聚合函数：多行变一行 SUM() AVG() MAX() MIN() COUNT()
wm_concat() -- 将字段的数据在一行展示，并用 , 分隔
SELECT DEPTNO,wm_concat(ENAME) FROM EMP GROUP BY DEPTNO;
-- 可以写在 SELECT ， HAVING 和 ORDER BY 的后面
普通函数(行函数)：字段原先有多少行，返回结果的行数不变
-- 全部关键字后面都可以写
分析函数(开窗函数)：字段原先有多少行，返回结果的行数不变
-- 只能写在 SELECT 后面
-- 函数的嵌套 从最里面往外层运行
普通函数之间可以一直嵌套
普通函数和聚合函数进行嵌套，聚合函数只能嵌套一层，普通函数可以一直嵌套 -- NVL(AVG(NVL(COMM,0)),0)
普通函数和分析函数进行嵌套，分析函数只能嵌套一层，普通函数可以一直嵌套
-----------------------------------------------------------
GROUP BY -- 分组 ，将字段里相同的数据分为一组，返回一行数据 ，一般配合聚合函数使用
-- 去重
-- 对部门编号和岗位一起进行去重
SELECT DEPTNO,JOB FROM EMP GROUP BY DEPTNO,JOB; -- 使用 GROUP BY 去重，运行效率高
SELECT DISTINCT DEPTNO,JOB FROM EMP; -- 使用 DISTINCT 去重，运行效率低
SUM() AVG() -- 只能用于数值数据类型
MAX() MIN() COUNT() -- 针对所有的数据类型
COUNT(*),COUNT(1) -- 返回所有的数据行数
COUNT(字段) -- 返回字段里非空数据的行数
-- 直接查询值，会以值为名生成字段，并且用值填充字段下的每一行
SELECT ENAME,1 数字 FROM EMP;
-- 去重计数 先去重再统计行数
-- 统计emp表里有多少个不同的岗位
SELECT COUNT(JOB) FROM EMP; -- 14
SELECT COUNT(DISTINCT JOB) FROM EMP; -- 5
-- 查询每个部门的工资总和，平均工资，最大工资，最小工资，人数
SELECT DEPTNO,SUM(SAL),AVG(SAL),MAX(SAL),MIN(SAL),COUNT(*) FROM EMP GROUP BY DEPTNO;
1. 写 group by ，SELECT 后只能写分组字段和聚合函数。
2. 不写 group by ，SELECT 后要么全部写普通字段(算术运算和行函数和开窗函数)，要么全部写聚合函数
HAVING -- 对聚合结果集(分组字段和聚合函数)进行条件筛选
-- 查询平均工资大于2000的部门的工资总和和人数
SELECT DEPTNO,SUM(SAL),COUNT(*) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)>2000;
SELECT DEPTNO,SUM(E.SAL),COUNT(*) FROM EMP E GROUP BY E.DEPTNO HAVING E.DEPTNO IN (10,20);
SELECT DEPTNO,SUM(E.SAL),COUNT(*) FROM EMP E WHERE E.DEPTNO IN (10,20) GROUP BY E.DEPTNO ; -- 效率高
-- 从运行效率上来说，先筛选数据，再进行分组聚合计算效率更高
ORDER BY -- 排序
-- 排序方式：
ASC 默认升序，由小到大。
DESC 降序，由大到小。
-- 多字段排序
ORDER BY COL1 排序方式1,COL2 排序方式2 ...
-- 排序逻辑
先按COL1的排序方式1进行排序，如果COL1有重复数据，再对重读的数据按COL2的排序方式2进行排序，....
-- 对员工信息按部门编号升序排序，再按工资降序排序
SELECT * FROM EMP ORDER BY DEPTNO,SAL DESC;
-- select语句编写顺序
SELECT FROM WHERE GROUP BY HAVING ORDER BY
-- select语句执行顺序
FROM WHERE GROUP BY HAVING SELECT ORDER BY
-- 对表起别名，在整个select语句里都能用。然后原表名不能再使用
-- 对字段起别名，只能在 ORDER BY 后面用。原字段名和别名都能用
SELECT DEPTNO,SUM(E.SAL) 总工资,COUNT(*) 人数 FROM EMP E WHERE E.DEPTNO IN (10,20)
GROUP BY E.DEPTNO ORDER BY SUM(E.SAL), 人数;
--函数 函数名 函数效果 参数
DUAL -- 万能表/测试表 查询什么就展示什么 做函数效果测试
SELECT * FROM DUAL;
SELECT 1+1 FROM DUAL;
SELECT 'ABCD' FROM DUAL;
-- 数值类 作用于 数值数据类型
ABS(x) -- 求 x 的绝对值
SELECT ABS(-100),ABS(50) FROM DUAL;
POWER(x,y) -- 求 x 的 y 次方
SELECT POWER(2,3),POWER(4,1/3) FROM DUAL;
MOD(x,y) -- 求 x 除以 y 的余数
SELECT MOD(10,3),MOD(-9,-3) FROM DUAL;
ORA_HASH(字段) -- ORACLE数据库求hash值
SELECT ORA_HASH(1) FROM DUAL; -- 2342552567
SELECT ORA_HASH('ABCD') FROM DUAL; -- 2823789359
SELECT ORA_HASH(DATE'2024-12-25') FROM DUAL; -- 768860178
SELECT EMP.*,MOD(ORA_HASH(EMPNO),4) FROM EMP;
ROUND(x,y) -- 对 x 四舍五入保留到小数点后 y 位
-- y 可以不写，默认保留到个位。
ROUND(6666.666) -- 6667
ROUND(6666.666,2) -- 6666.67
ROUND(6666.666,-2) -- 6700
ROUND(6636.666,-2) -- 6600
TRUNC(x,y) -- 对 x 截断保留到小数点后 y 位
-- y 可以不写，默认保留到个位。
TRUNC(6666.666) -- 6666
TRUNC(6666.666,2) -- 6666.66
TRUNC(6666.666,-2) -- 6600
TRUNC(6636.666,-2) -- 6600
CEIL(x) -- 向上取整
FLOOR(x) -- 向下取整
SELECT CEIL(97.98),FLOOR(97.98) FROM DUAL; -- 98 / 97
SELECT CEIL(-97.98),FLOOR(-97.98) FROM DUAL; -- -97 / -98
-- 隐式转换 字符串和其他数据类型的相互转换 （字符串和数字）
1. 当字符串和数字进行算术运算和比较运算时，尝试将字符串转成数字
SELECT '100'*2 FROM DUAL ;
2. 当对数字进行字符串类函数处理时，会将数字转成字符串
-------------------------------------------------------------
-- 字符串类的函数
ASCII() -- 返回字符/字符串首个字母的ascii值
SELECT ASCII('A'),ASCII('BCD') FROM DUAL;
SELECT ASCII('0'),ASCII(1) FROM DUAL;
SELECT ASCII('a'),ASCII('b') FROM DUAL;
SELECT ASCII('我'),ASCII('你') FROM DUAL;
CHR(x) -- 返回 x 对应的字符(x是数字)
SELECT ASCII('A'),CHR(65) FROM DUAL;
SELECT CHR(39) FROM DUAL; -- '
UPPER(字段) -- 将英文字母转大写
SELECT UPPER('abcd') FROM DUAL; -- 'ABCD'
LOWER(字段) -- 将英文字母转小写
SELECT LOWER('ABCD') FROM DUAL; -- 'abcd'
SELECT ENAME,LOWER(ENAME) FROM EMP;
LPAD(字段,填充后的长度(字节),用于填充的字符串) -- 左填充
SELECT LPAD('ABC',10,'=') FROM DUAL; -- '=======ABC'
SELECT LPAD('ABC',10,'QW') FROM DUAL; -- 'QWQWQWQABC'
SELECT LPAD('ABC',10,'我') FROM DUAL; -- ' 我我我ABC'
RPAD(字段,填充后的长度(字节),用于填充的字符串) -- 右填充
SELECT RPAD('ABC',10,'=') FROM DUAL; -- 'ABC======='
SELECT RPAD('ABC',10,'QW') FROM DUAL; -- 'ABCQWQWQWQ'
SELECT RPAD('ABC',10,'我') FROM DUAL; -- 'ABC我我我 '
TRIM(字段) -- 去除字段/字符串左右两边的空格
SELECT TRIM(' ABC BCD ') FROM DUAL; -- 'ABC BCD'
TRIM(指定字符 FROM 字段) -- 从字段/字符串去除指定的字符
SELECT TRIM('=' FROM '===ABC==BCD==') FROM DUAL; -- 'ABC==BCD'
LTRIM(字段,字符串) -- 去除左边的字符
-- 字符串 可以不写，默认去空格
SELECT LTRIM(' ABC BCD ') FROM DUAL; --'ABC BCD '
SELECT LTRIM('ABCBCD','A') FROM DUAL; --'BCBCD'
SELECT LTRIM('ABCBCD','ABC') FROM DUAL; --'D'
RTRIM(字段,字符串) -- 去除右边的字符
-- 字符串 可以不写，默认去空格
SELECT RTRIM(' ABC BCD ') FROM DUAL; --' ABC BCD'
SELECT RTRIM('ABCBCD','D') FROM DUAL; --'ABCBC'
SELECT RTRIM('ABCBCD','DBC') FROM DUAL; --'A'
-- 常用
SUBSTR(字段,起始位置,截取长度(字符)) -- 截取
-- 起始位置 不写 默认为1 。正数从左往右数，负数从右往左数
-- 截取长度，是字符长度。不写，默认结尾截取到末尾 截取方向始终都是从左往右
SELECT ENAME,SUBSTR(ENAME,1,1),SUBSTR(ENAME,3) FROM EMP;
SELECT ENAME,SUBSTR(ENAME,-3) FROM EMP;
SUBSTRB(字段,起始位置,截取长度(字节)) -- 截取
-- 起始位置 不写 默认为1 。正数从左往右数，负数从右往左数
-- 截取长度，是字节长度。不写，默认结尾截取到末尾 截取方向始终都是从左往右
SELECT SUBSTRB('我是中国人',1,5) FROM DUAL; -- '我是 '
CONCAT(字符串1,字符串2) -- 将字符串1和字符串2拼接成一个字符串
SELECT CONCAT('我是','中国人') FROM DUAL; -- '我是中国人'
SELECT CONCAT(ENAME,EMPNO) FROM EMP;
SELECT CONCAT(CONCAT('张三','李四'),'王五') from dual; -- '张三李四王五'
|| -- 管道拼接符 将两个字符串拼接成一个字符串
SELECT '张三'||'李四'||'王五' from dual; -- '张三李四王五'
LENGTH(字段) -- 求字段/字符串的字符长度
SELECT ENAME,LENGTH(ENAME) FROM EMP;
LENGTHB(字段) -- 求字段/字符串的字节长度
SELECT LENGTH('我是中国人'),LENGTHB('我是中国人') FROM DUAL;
INSTR(字段,字符/串,起始位置,第几次出现) -- 返回字符在字段/字符串中的位置(数字)
-- 字符/串 如果是字符串则返回字符串中第一个字符所在的位置
-- 起始位置 不写 默认为1 。正数从左往右数，负数从右往左数
-- 第几次出现 不写 默认为1 。起始位置正数从左往右查找，负数从右往左查找
'BAHGADGAHGK' 'A'
2 5 8
SELECT INSTR('BAHGADGAHGK','A',1,3) FROM DUAL; -- 8
SELECT INSTR('BAHGADGAHGK','A',-1,2) FROM DUAL; -- 5
SELECT INSTR('BAHGADGAHGK','A',3,2) FROM DUAL; -- 8
SELECT INSTR('BAHGADGAHGK','A',3,3) FROM DUAL; -- 0 -- 找不到返回0
SELECT INSTR('BAHGADGAHGK','HG',1,2) FROM DUAL; -- 9
REPLACE(字段,旧值,新值) -- 用 新值 替换 字段/字符串 里 旧值
SELECT ENAME,REPLACE(ENAME,'AL','al') from emp;
1.查询员工姓名以'S'结尾的员工信息，不允许使用like，
-- SUBSTR
SELECT * FROM EMP WHERE SUBSTR(ENAME,-1)='S';
-- INSTR
SELECT * FROM EMP WHERE INSTR(ENAME,'S',-1,1)=LENGTH(ENAME);
-- RTRIM
SELECT * FROM EMP WHERE RTRIM(ENAME,'S')!=ENAME;
2.在emp表中查询出姓名的第二个字母为A的记录。
-- LIKE
SELECT * FROM EMP WHERE ENAME LIKE '_A%';
-- SUBSTR
SELECT * FROM EMP WHERE SUBSTR(ENAME,2,1)='A';
-- INSTR
SELECT * FROM EMP WHERE INSTR(ENAME,'A',2,1)=2;
3.显示员工姓名正好为5个字符的员工。
SELECT * FROM EMP WHERE 5=LENGTH(ENAME);
4.显示所有员工姓名的前三个字符。
SELECT SUBSTR(ENAME,1,3) FROM EMP;
5.显示所有员工的姓名，用 a 替换A。
SELECT ENAME,REPLACE(ENAME,'A','a') from emp;
6.将ENAME字段首字符大写其余字符小写
INITCAP(字段) -- 将字段里数据按空格去分段，每段都首字母大写其余字母小写
SELECT ENAME,INITCAP(ENAME) FROM EMP;
SELECT INITCAP('ABC DEF GHJ AET') FROM DUAL; -- 'Abc Def Ghj Aet'
SELECT ENAME,
UPPER(SUBSTR(ENAME,1,1))||LOWER(SUBSTR(ENAME,2)) FROM EMP;
7.从DQ表里分别截取出 省、市、区 --面试题
CREATE TABLE DQ(DZ VARCHAR2(50));
INSERT INTO DQ VALUES ('广东省-广州市-白云区');
INSERT INTO DQ VALUES ('黑龙江省-哈尔滨市-松北区');
INSERT INTO DQ VALUES ('内蒙古自治区-呼和浩特市-新城区');
COMMIT;
SELECT * FROM DQ ;
省 市 区
广东省 广州市 白云区
黑龙江省 哈尔滨市 松北区
内蒙古自治区 呼和浩特市 新城区
SELECT
SUBSTR(DZ,1,INSTR(DZ,'-',1,1)-1) 省,
SUBSTR(DZ,INSTR(DZ,'-',1,1)+1,INSTR(DZ,'-',1,2)-INSTR(DZ,'-',1,1)-1) 市,
SUBSTR(DZ,INSTR(DZ,'-',1,2)+1) 区
FROM DQ;
-----------------------------------------------------------------------------------
-- 转换函数
TO_NUMBER(字段) -- 将字段的数据类型转成数值类型
SELECT * FROM SHUZI ORDER BY A DESC;
SELECT * FROM SHUZI ORDER BY TO_NUMBER(A) DESC;
TO_DATE(数字/字符串,日期格式) -- 将 数字/字符串 转成日期
-- 日期格式： 'YYYY-MM-DD HH24MISS' 'YYYYMMDD' 'YYYY-MM-DD' 'YYYY/MM/DD'
SELECT TO_DATE(20241225,'YYYYMMDD') FROM DUAL;
SELECT TO_DATE(20241225,'YYYYMMDD HH24MISS') FROM DUAL;
SELECT TO_DATE(20241225000000,'YYYYMMDD') FROM DUAL; -- 报错
SELECT TO_DATE('20241225 160100','YYYYMMDD HH24MISS') FROM DUAL;
SELECT TO_DATE('160100','HH24MISS') FROM DUAL; -- 2024/12/1 16:01:00
SYSDATE -- 当前系统日期
SELECT SYSDATE FROM DUAL;
TO_CHAR(日期,格式) -- 将日期转成字符串
SELECT TO_CHAR(SYSDATE) FROM DUAL; -- '25-12月-24'
SELECT TO_CHAR(SYSDATE,'YYYYMMDD') FROM DUAL; -- '20241225'
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL; -- '2024-12-25'
SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日"') FROM DUAL; -- '2024年12月25日'
SELECT TO_CHAR(SYSDATE,'MM-DD HH24:MI:SS') FROM DUAL; -- '12-25 16:19:56'
SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL; -- 返回第几年
SELECT TO_CHAR(SYSDATE,'MM') FROM DUAL; -- 返回年的第几月
SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL; -- 返回月的第几天
SELECT TO_CHAR(SYSDATE,'D') FROM DUAL; -- 返回周的第几天 国外周日作为周的第一天
SELECT TO_CHAR(SYSDATE,'DDD') FROM DUAL; -- 返回年的第几天
SELECT TO_CHAR(SYSDATE,'DAY') FROM DUAL; -- 返回 '星期几'
SELECT TO_CHAR(SYSDATE,'Q') FROM DUAL; -- 返回年的第几个季度
-- 日期类函数
MONTHS_BETWEEN(日期1,日期2) -- 返回两个日期相差的月份（数字）
SELECT MONTHS_BETWEEN(DATE'2024-12-25',DATE'2024-01-25') FROM DUAL; -- 11
SELECT MONTHS_BETWEEN(DATE'2024-12-24',DATE'2024-01-25') FROM DUAL; -- 10.9677419354839
SELECT MONTHS_BETWEEN(DATE'2024-12-26',DATE'2024-01-25') FROM DUAL; -- 11.0322580645161
-- 相差的天数/31
ADD_MONTHS(日期,数字) -- 返回日期加减多少个月之后的日期
SELECT ADD_MONTHS(SYSDATE,2) FROM DUAL; -- 2025/2/25 16:57:44
SELECT ADD_MONTHS(SYSDATE,-2) FROM DUAL; -- 2024/10/25 16:58:22
LAST_DAY(日期) -- 返回日期所有月份的最后一天（日期）
SELECT LAST_DAY(SYSDATE) FROM DUAL; -- 2024/12/31 16:59:49
NEXT_DAY(日期,数字) -- 返回日期往后最近的一个星期几对应的日期
-- 数字：1-7 。 1 星期日 ， 2 星期一 ，3 星期二 ， 4 星期三 ， 5 星期四 ， 6 星期五 ， 7 星期六
SELECT NEXT_DAY(SYSDATE,5) FROM DUAL; -- 2024/12/26 17:04:40
SELECT NEXT_DAY(SYSDATE,4) FROM DUAL; -- 2025/1/1 17:05:25
ROUND(日期,格式) -- 对日期进行 ”四舍五入“
-- 格式 不写，默认到天。 和 'DD' 是一样的
SELECT ROUND(SYSDATE,'YYYY') FROM DUAL; -- 返回某年的1月1日 -- 2025/1/1
-- 1-6月 返回当年的1月1日，7-12月返回下一年的1月1日
SELECT ROUND(SYSDATE,'MM') FROM DUAL; -- 返回某月1号 -- 2025/1/1
-- 1-15号 返回当月1号，16号以后返回下一月1号
SELECT ROUND(SYSDATE,'DD') FROM DUAL; -- 返回某日 -- 2024/12/26
-- 0-11点返回当天，12点之后返回第二天
SELECT ROUND(SYSDATE,'DAY') FROM DUAL; -- 返回某个周日 -- 2024/12/29
-- 周三11点之前返回当周日(上周)，周三12点之后返回下周日(本周)
SELECT ROUND(SYSDATE,'Q') FROM DUAL; -- 返回某个季度的第一天 -- 2025/1/1
-- 季中月份15号之前返回当前季度，季中月份16号之后返回下一个季度
TRUNC(日期,格式) -- 对日期进行截断（全舍）
SELECT TRUNC(SYSDATE,'YYYY') FROM DUAL; -- 返回当年的1月1日 -- 2024/1/1
SELECT TRUNC(SYSDATE,'MM') FROM DUAL; -- 返回当月1号 -- 2024/12/1
SELECT TRUNC(SYSDATE,'DD') FROM DUAL; -- 返回当日 -- 2024/12/25
SELECT TRUNC(SYSDATE,'DAY') FROM DUAL; -- 返回当周日 -- 2024/12/22
SELECT TRUNC(SYSDATE,'Q') FROM DUAL; -- 返回当季度的第一天 -- 2024/10/1
-- 练习
1.找出2021年4月15号的下一个月的最后一天
2.找出2021年4月15号的所在季度的第一天
3.找出2021年4月15号的所在是星期几
4.找出2021年4月15号三个月之前是哪一天
5.根据2021年4月15号 找到 7月1号
6.根据 日期 20201020 得到 2021年1月1号
7.根据 日期 20211020 得到 2021年11月1号
8.查询各月倒数第3天入职的员工信息
9.查询员工工龄大于或等于10年的员工信息(截止到目前)
10.查询每个部门中的员工数量、平均工资和平均工作年限(截止到目前)
11.查询10，20部门中截止到2000年1月1号，工作了多少个周，工作了多少个月
12.查询2000年1月1日到现在有多少个月多少个周（四舍五入）
13.显示所有员工的姓名、入职的年份和月份，按入职日期所在的月份排序，若月份相同则按入职的年份排序
14.查询在2月份入职的所有员工信息
-- 练习
1.找出2021年4月15号的下一个月的最后一天
SELECT LAST_DAY(ADD_MONTHS(DATE'2021-04-15',1)) FROM DUAL;
2.找出2021年4月15号的所在季度的第一天
SELECT TRUNC(DATE'2021-04-15','Q') FROM DUAL;
3.找出2021年4月15号的所在是星期几
SELECT TO_CHAR(DATE'2021-04-15','DAY') FROM DUAL;
4.找出2021年4月15号三个月之前是哪一天
SELECT ADD_MONTHS(DATE'2021-04-15',-3) FROM DUAL;
5.根据2021年4月15号 找到 7月1号
SELECT TRUNC(ADD_MONTHS(DATE'2021-04-15',3),'MM') FROM DUAL;
6.根据 日期 20201020 得到 2021年1月1号
SELECT ROUND(DATE'2020-10-20','YYYY') FROM DUAL;
7.根据 日期 20211020 得到 2021年11月1号
SELECT ROUND(DATE'2020-10-20','mm') FROM DUAL;
8.查询各月倒数第3天入职的员工信息
SELECT * FROM EMP WHERE LAST_DAY(HIREDATE)-2=HIREDATE;
9.查询员工工龄大于或等于10年的员工信息(截止到目前)
SELECT * FROM EMP WHERE MONTHS_BETWEEN(SYSDATE,HIREDATE)/12>=10;
10.查询每个部门中的员工数量、平均工资和平均工作年限(截止到目前)
SELECT DEPTNO,COUNT(*) 员工数量,ROUND(AVG(SAL),2) 平均工资,
ROUND(AVG(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12),2) 平均工作年限
FROM EMP
GROUP BY DEPTNO;
11.查询10，20部门中截止到2000年1月1号，工作了多少个周，工作了多少个月
SELECT DEPTNO,ROUND((DATE'2000-01-01'-HIREDATE)/7) 周,
ROUND(MONTHS_BETWEEN(DATE'2000-01-01',HIREDATE)) 月
FROM EMP
WHERE DEPTNO IN (10,20);
12.查询2000年1月1日到现在有多少个月多少个周（四舍五入）
SELECT ROUND((SYSDATE-DATE'2000-01-01')/7) 周,
ROUND(MONTHS_BETWEEN(SYSDATE,DATE'2000-01-01')) 月
FROM DUAL;
13.显示所有员工的姓名、入职的年份和月份，按入职日期所在的月份排序，若月份相同则按入职的年份排序
SELECT ENAME,TO_CHAR(HIREDATE,'YYYY') 年份, TO_CHAR(HIREDATE,'MM') 月份
FROM EMP ORDER BY 月份,TO_CHAR(HIREDATE,'YYYY');
14.查询在2月份入职的所有员工信息
SELECT * FROM EMP
WHERE TO_CHAR(HIREDATE,'MM')=2;
--WHERE TO_CHAR(HIREDATE,'MM')='02';
--WHERE TO_CHAR(HIREDATE,'MM')='2'; -- 没有结果
-----------------------------------------------------------
--流程控制
-- 第一种写法
CASE WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
WHEN 条件3 THEN 结果3
........
ELSE 其余情况的结果 -- 可以不写。不写else其余情况默认返回空值
END
-- 第二种写法 条件必须是等值判定
CASE 字段 WHEN 值1 THEN 结果1
WHEN 值2 THEN 结果2
WHEN 值3 THEN 结果3
........
ELSE 其余情况的结果 -- 可以不写。不写else其余情况默认返回空值
END
-- CASE WHEN 会新生成一个字段，将结果填充到字段下不同的行
-- 满足上面条件的数据不会参与下面条件的判定
-- 对员工姓名进行处理，将'SCOTT'替换成‘斯考特’，将'SMITH'替换成‘史密斯’，将'KING'替换成‘老板’，其余人不变
SELECT EMP.*,
CASE WHEN ENAME = 'SCOTT' THEN '斯考特'
WHEN ENAME = 'SMITH' THEN '史密斯'
WHEN ENAME = 'KING' THEN '老板'
ELSE ENAME
END NEW_NAME
FROM EMP; -- 第一种写法
SELECT EMP.*,
CASE ENAME WHEN 'SCOTT' THEN '斯考特'
WHEN 'SMITH' THEN '史密斯'
WHEN 'KING' THEN '老板'
ELSE ENAME
END NEW_NAME
FROM EMP; -- 第二种写法
-- 画像 贴标签
-- 工资小于1500，返回‘低工资’，工资小于3000，返回‘中等工资’，其余返回‘高工资’
SELECT EMP.*,
CASE WHEN SAL<1500 THEN '低工资'
WHEN SAL<3000 THEN '中等工资'
ELSE '高工资'
END 工资等级
FROM EMP;
/*员工工资大于等于3000的需要缴20% 的税，员工工资1500-3000（不含）的需要缴15% 的税，小于等于1500的需要缴10% 的税
求每个员工的信息以及需要缴的税*/
SELECT EMP.*,
CASE WHEN SAL>=3000 THEN SAL*0.2
WHEN SAL>1500 THEN SAL*0.15
WHEN SAL<=1500 THEN SAL*0.1
END 税
FROM EMP;
/*假如工资在两千以上才算及格，那么求整个公司工资的及格率
42.86% 及格人数/总人数 */
SELECT ROUND(COUNT(CASE WHEN SAL>2000 THEN 'JIGE' END)/COUNT(*)*100,2)||'%' 及格率 FROM EMP;
-- 满足条件聚合
-- 行列转换
-- 行转列 CASE WHEN
SELECT * FROM CJH;
SELECT * FROM CJL;
-- 使用select语句将cjh表的数据变成cjl表中的样子
SELECT NAME,
SUM(CASE WHEN SCOURE = '语文' THEN SSCORE END) 语文,
AVG(CASE WHEN SCOURE = '数学' THEN SSCORE END) 数学,
MAX(CASE WHEN SCOURE = '英语' THEN SSCORE END) 英语
FROM CJH GROUP BY NAME;
-- 集合运算 并集 交集 补集（minus）
UNION [ALL] -- 并集 将两个结果集纵向叠加
UNION -- 排序去重 效率低
UNION ALL -- 不去重 效率高
INTERSECT -- 交集 返回上下两个结果集都有的数据
MINUS -- 补集 返回上一个结果集有但是下一个结果集没有的数据
-- 注意事项：
1. 集合运算里所有结果集的字段的数量和数据类型要一一对应
2. 返回的结果的字段名称，数量和数据类型以第一个结果集为准
3. 集合运算的执行顺序是由上往下执行。要改变执行顺序，可以使用()
SELECT * FROM EMP WHERE DEPTNO IN (10,20)
UNION ALL
SELECT * FROM EMP WHERE DEPTNO IN (20,30)
INTERSECT
SELECT * FROM EMP WHERE DEPTNO=10 ; -- 10
SELECT * FROM EMP WHERE DEPTNO IN (10,20)
UNION ALL
(SELECT * FROM EMP WHERE DEPTNO IN (20,30)
INTERSECT
SELECT * FROM EMP WHERE DEPTNO=10) ; -- 10 20
-- 列转行 UNION ALL
SELECT * FROM CJH;
SELECT * FROM CJL;
-- 使用select语句将cjL表的数据变成cjH表中的样子
SELECT NAME ,'语文' 课程,语文 成绩 FROM CJL
UNION ALL
SELECT NAME ,'数学' 课程,数学 成绩 FROM CJL
UNION ALL
SELECT NAME ,'英语' 课程,英语 成绩 FROM CJL ;
-----------------------------------------------------------------------------
-- ORACLE数据库内置函数 内置函数运行效率会高一点
DECODE(字段,值1,结果1,值2,结果2,值3,结果3,....其余情况的结果) -- 其余情况的结果不写则返回空值
-- 作用等同于 CASE WHEN 第二种写法。只支持等值判定
SELECT EMP.*,
CASE ENAME WHEN 'SCOTT' THEN '斯考特'
WHEN 'SMITH' THEN '史密斯'
WHEN 'KING' THEN '老板'
ELSE ENAME
END NEW_NAME
FROM EMP; -- 第二种写法
SELECT EMP.*,
DECODE(ENAME,'SCOTT','斯考特','SMITH','史密斯','KING','老板',ENAME) NEW_NAME
FROM EMP;
SELECT NAME,
SUM(DECODE(SCOURE,'语文',SSCORE)) 语文,
AVG(CASE WHEN SCOURE = '数学' THEN SSCORE END) 数学,
MAX(CASE WHEN SCOURE = '英语' THEN SSCORE END) 英语
FROM CJH GROUP BY NAME;
PIVOT -- 行转列
-- CASE WHEN
SELECT NAME,
SUM(CASE WHEN SCOURE = '语文' THEN SSCORE END) 语文,
AVG(CASE WHEN SCOURE = '数学' THEN SSCORE END) 数学,
MAX(CASE WHEN SCOURE = '英语' THEN SSCORE END) 英语
FROM CJH GROUP BY NAME;
-- PIVOT
SELECT A.NAME 姓名,语文,数学,英语 FROM CJH PIVOT (
SUM(SSCORE) -- 聚合函数
FOR SCOURE IN ('语文' 语文,'数学' 数学,'英语' 英语)
) A;
UNPIVOT -- 列转行
-- UNION ALL
SELECT NAME ,'语文' 课程,语文 成绩 FROM CJL
UNION ALL
SELECT NAME ,'数学' 课程,数学 成绩 FROM CJL
UNION ALL
SELECT NAME ,'英语' 课程,英语 成绩 FROM CJL ;
-- UNPIVOT
SELECT * FROM CJL UNPIVOT(
成绩 -- 数据来源表字段起的别名
FOR 课程 /* 新增字段的别名 */ IN (语文,数学,英语)
);
-- DROP TABLE RK;
CREATE TABLE RK(
COUNTRY VARCHAR2(10),
SEX NUMBER,
RK NUMBER
);
--插入数据
INSERT INTO RK VALUES('中国',1,340);
INSERT INTO RK VALUES('中国',2,260);
INSERT INTO RK VALUES('美国',1,45);
INSERT INTO RK VALUES('美国',2,55);
INSERT INTO RK VALUES('加拿大',1,51);
INSERT INTO RK VALUES('加拿大',2,49);
INSERT INTO RK VALUES('英国',1,40);
INSERT INTO RK VALUES('英国',2,60);
commit;
--查询数据 1代表男 2代表女
SELECT * FROM RK;
--目标结构
国家 男 女 -- 字段名
中国 340 260
美国 45 55
加拿大 51 49
英国 40 60
-- 数据汇总层
insert into TABLE_NAME1
SELECT ID,TRUNC(RIQI) RIQI,
SUM(CASE WHEN TYPE='CUNRU' THEN AMT END) CUNRU,
SUM(CASE WHEN TYPE='QUXIAN' THEN AMT END) QUXIAN
FROM TABLE_NAME GROUP BY ID,TRUNC(RIQI); -- 每个客户每天的存款和取现的汇总
-- 指标层
INSERT INTO TABLE_NAME2
SELECT ID,TRUNC(RIQI,'MM') RIQI,
SUM(CUNRU),SUM(QUXIAN) FROM TABLE_NAME1 GROUP BY ID,TRUNC(RIQI,'MM') ; -- 每个客户每月的存款和取现的汇总
-- 子查询 (select 语句)
1. SELECT 后的子查询,字段里的子查询 -- 要求返回的结果是单行单列
-- 查询emp表数据，将deptno转换具体的部门名称
SELECT EMP.EMPNO,
EMP.ENAME,
EMP.JOB,
EMP.MGR,
EMP.HIREDATE,
EMP.SAL,
EMP.COMM,
EMP.DEPTNO,
(SELECT DNAME FROM DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO) DNAME
FROM EMP;
2. FROM 后的子查询。将子查询结果集当表使用，要求select语句能正常运行
SELECT A.EMPNO,ENAME,SAL FROM (SELECT * FROM EMP WHERE DEPTNO=30) A;
3. 条件里的子查询
3.1 用比较运算符连接。要求子查询返回的结果是单行单列
-- 查询工资比scott低的员工信息
SELECT * FROM EMP WHERE SAL < -- 3000
(SELECT SAL FROM EMP WHERE ENAME='SCOTT');
-- 工资比公司平均工资低的员工
SELECT * FROM EMP WHERE SAL <
(SELECT AVG(SAL) FROM EMP)
3.2 IN(在..里面) EXISTS(存在) --看条件成不成立 （看有没有数据）
SELECT * FROM EMP WHERE EMPNO=7788;
SELECT * FROM EMP WHERE EMPNO=8888;
-- 所有能用in写的自查询，全部都可以用exists写
-- 查询其他部门里和10部门岗位相同的员工信息
SELECT * FROM EMP A WHERE A.JOB IN -- IN
(SELECT B.JOB FROM EMP B WHERE B.DEPTNO = 10 )
AND A.DEPTNO!=10;
SELECT * FROM EMP A WHERE A.DEPTNO!=10 AND EXISTS
(SELECT 1 FROM EMP B WHERE A.JOB =B.JOB AND B.DEPTNO = 10 );
-- EXISTS 只看条件成不成立，成立就是存在，不成立就是不存在。不管select后面查询什么
--查询入职日期早于其直接上级领导的所有员工信息
SELECT * FROM EMP A WHERE EXISTS
(SELECT 1 FROM EMP B WHERE A.HIREDATE<B.HIREDATE AND A.MGR=B.EMPNO);
SELECT * FROM EMP A WHERE A.MGR IN
(SELECT B.EMPNO FROM EMP B WHERE A.HIREDATE<B.HIREDATE );
--查询工资比公司平均工资高的员工信息
SELECT * FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP);
SELECT * FROM EMP A WHERE EXISTS (SELECT 1 FROM EMP B HAVING A.SAL>AVG(B.SAL));
--查询工资比部门平均工资高的员工信息
SELECT * FROM EMP A WHERE EXISTS
(SELECT 1 FROM EMP B WHERE A.DEPTNO=B.DEPTNO GROUP BY DEPTNO HAVING A.SAL>AVG(B.SAL));
SELECT AVG(SAL),DEPTNO FROM EMP GROUP BY DEPTNO;
--查询从事同一种工作但不属于同一部门的员工信息
SELECT * FROM EMP A WHERE EXISTS
(SELECT 1 FROM EMP B WHERE A.JOB=B.JOB AND A.DEPTNO!=B.DEPTNO);
--查询与SMITH员工从事相同工作的所有员工信息
SELECT * FROM EMP A WHERE EXISTS
(SELECT 1 FROM EMP B WHERE A.JOB=B.JOB AND B.ENAME='SMITH');
--查询在NEW YORK上班的员工名字
SELECT ENAME FROM EMP A WHERE EXISTS
(SELECT 1 FROM DEPT B WHERE B.LOC='NEW YORK' AND A.DEPTNO=B.DEPTNO );
--查询和10号部门中岗位相同的其他员工的信息
SELECT * FROM EMP A WHERE A.DEPTNO!=10 AND EXISTS
(SELECT 1 FROM EMP B WHERE A.JOB =B.JOB AND B.DEPTNO = 10 );
--查询和SMITH是相同岗位的员工,但是部门是ACCOUNTING的员工信息
SELECT * FROM EMP A WHERE EXISTS
(SELECT 1 FROM EMP B WHERE A.JOB=B.JOB AND B.ENAME='SMITH')
AND EXISTS (SELECT 1 FROM DEPT C WHERE C.DNAME='ACCOUNTING' AND A.DEPTNO=C.DEPTNO);
SELECT * FROM EMP A WHERE EXISTS
(SELECT 1 FROM EMP B WHERE A.JOB=B.JOB AND B.ENAME='SMITH'
AND EXISTS (SELECT 1 FROM DEPT C WHERE C.DNAME='ACCOUNTING' AND A.DEPTNO=C.DEPTNO));
-- IN 和 EXISTS 的区别
IN -- 主要用于枚举值列表或者返回数据条数较少的子查询
-- 子查询结果集为驱动表，去匹配主查询的表
-- DEPTNO IN (10,20)
SELECT * FROM EMP WHERE DEPTNO IN (10,10,10,20);
SELECT * FROM EMP A WHERE A.JOB IN (SELECT B.JOB FROM EMP B WHERE B.DEPTNO=30);
EXISTS -- 主要用于子查询表数据量较大的情况
-- 主查询表作为驱动表，去匹配子查询的表。只要找到满足条件的数据就终止当前数据查找，然后下一行数据的扫描
SELECT * FROM EMP A WHERE EXISTS (SELECT 1 FROM EMP B WHERE B.DEPTNO=30 AND A.JOB=B.JOB);
-- 小表驱动大表运行效率高
--------------------------------------------------------------------------------------------------------
-- 表关联
笛卡尔积：两张表关联时没有设置合适的关联条件，那么关联的结果数据条数=两张表数据条数相乘
SELECT * FROM EMP,DEPT; -- 56=14*4
SELECT * FROM EMP CROSS JOIN DEPT;
SELECT * FROM EMP JOIN DEPT ON 1=1;
数据发散：表关联时设置关联条件后，关联字段里数据的对应关系：1对1，1对多，多对多
关联后的结果的数据条数=数据对应关系的乘积
-- 实际开发工作需要注意数据发散对指标聚合计算造成的影响
如果有影响，要先将对应关系变成1对1：
1. 先聚合再关联
2. 先筛选再关联
-- 表关联
[INNER] JOIN -- 内关联 展示两张表满足关联条件的数据
LEFT [OUTER] JOIN -- 左外关联 左侧表为主表，右侧为从表
-- 展示主表的全部数据，从表满足关联条件的数据对应展示，不满足的填空值
RIGHT [OUTER] JOIN -- 右外关联 右侧表为主表，左侧为从表
-- 展示主表的全部数据，从表满足关联条件的数据对应展示，不满足的填空值
FULL [OUTER] JOIN -- 全外关联
-- 展示两张表的全部数据，满足关联条件的数据对应展示，
-- 不满足关联条件的数据，左表有的，右边填空值。右表有的，左边填空值
SELECT * FROM A JOIN B ON A.ID=B.ID;
SELECT * FROM A LEFT JOIN B ON A.ID=B.ID;
SELECT * FROM A RIGHT JOIN B ON A.ID=B.ID;
SELECT * FROM A FULL JOIN B ON A.ID=B.ID;
-- 假设有A,B两张表，A表里有10000条数据，B表里有1000条数据。
-- 关联字段都是id，id里的数据都是唯一的。四种关联方式各会产生多少条数据。
内关联： 0 ~ 1000
左外关联：10000
右外关联：1000
全外关联：10000 ~ 11000
-- 写在 ON 后面是关联条件 ，写在 WHERE 后面的是刷选条件
-- 条件写在 ON 后面和写在 WHERE 后面在结果的区别
内关联：在结果上没有区别
SELECT * FROM EMP A JOIN DEPT B ON A.DEPTNO=B.DEPTNO AND A.DEPTNO = 20; -- 5
SELECT * FROM EMP A JOIN DEPT B ON A.DEPTNO=B.DEPTNO WHERE A.DEPTNO = 20; -- 5
左外关联：
SELECT * FROM EMP A LEFT JOIN DEPT B ON A.DEPTNO=B.DEPTNO AND A.DEPTNO = 20; -- 14
SELECT * FROM EMP A LEFT JOIN DEPT B ON A.DEPTNO=B.DEPTNO WHERE A.DEPTNO = 20; -- 5
右外关联：
SELECT * FROM EMP A RIGHT JOIN DEPT B ON A.DEPTNO=B.DEPTNO AND A.DEPTNO = 20; -- 8
SELECT * FROM EMP A RIGHT JOIN DEPT B ON A.DEPTNO=B.DEPTNO WHERE A.DEPTNO = 20; -- 5
全外关联：
SELECT * FROM EMP A FULL JOIN DEPT B ON A.DEPTNO=B.DEPTNO AND A.DEPTNO = 20; -- 17
SELECT * FROM EMP A FULL JOIN DEPT B ON A.DEPTNO=B.DEPTNO WHERE A.DEPTNO = 20; -- 5
-- 对内关联，条件写在ON的后面和WHERE的后面在结果上没有区别
-- 对外关联，写在ON的后面的条件只会过滤从表的数据，但是写在WHERE后面的条件会过滤主表的数据
-- 谓词下推 优化
-- 标准的谓词下推：
在左外/右外关联中，对主表数据的条件筛选写在WHERE后面，对从表数据的条件筛选写在ON后面。
-- 不谓词下推时sql
SELECT A.*,B.* FROM
(SELECT * FROM EMP WHERE JOB='CLERK') A
LEFT JOIN
(SELECT * FROM DEPT WHERE DEPTNO=20) B
ON A.DEPTNO=B.DEPTNO ;
-- 标准的谓词下推：
SELECT A.*,B.* FROM
EMP A
LEFT JOIN
DEPT B
ON A.DEPTNO=B.DEPTNO AND B.DEPTNO=20
WHERE A.JOB='CLERK';
1. 获取表数据,数据筛选
2. 表关联
-- 执行计划：sql语句在执行之前会生成执行计划，通过执行计划查看sql语句的运行。 按 F5
-- Oracle数据库优化器
SELECT A.*,B.* FROM
EMP A
LEFT JOIN
DEPT B
ON A.DEPTNO=B.DEPTNO
WHERE B.DEPTNO=20;
数据库底层表关联方式：
哈希连接： HASH JOIN
嵌套循环： NESTED LOOPS
排序合并： SORT JOIN MERGE JOIN
------------------------------------------------------------------------------------------
-- 自关联 表起别名
-- 查询员工编号，员工姓名，上级编号，上级姓名
SELECT B.EMPNO 员工编号,B.ENAME 员工姓名,A.EMPNO 上级编号,A.ENAME 上级姓名
FROM EMP A RIGHT JOIN EMP B ON A.EMPNO=B.MGR ;
-- 非等值连接
SELECT * FROM EMP A JOIN DEPT B ON A.DEPTNO < B.DEPTNO; -- 25=3*3+5*2+6*1
-- EMP.DEPTNO 10*3 20*5 30*6
-- DEPT.DEPTNO 10 20 30 40
-- 多表连接
常用左外关联。
SELECT * FROM
A LEFT JOIN B ON A.COL=B.COL
LEFT JOIN C ON A/B.COL=C.COL
....
CREATE TABLE DUIWU (NAME VARCHAR2(5));
INSERT INTO DUIWU VALUES ('A');
INSERT INTO DUIWU VALUES ('B');
INSERT INTO DUIWU VALUES ('C');
INSERT INTO DUIWU VALUES ('D');
COMMIT;
SELECT * FROM DUIWU;
--写SQL求出对战名单，A对B和B对A 是一场比赛展示一条数据。 6 条数据
--写SQL求出对战名单，A对B和B对A 是两场比赛展示两条数据。 12 条数据
--查询所有部门及其员工信息，包括那些没有员工的部门
--查询所有工种为CLERK的员工的姓名及其部门名称
--查询各个部门的编号,名称 以及部门人数、部门平均工资
--查询最低工资低于2000的部门名称
--查询所有员工工资都在900~3000之
SELECT * FROM DUIWU;
--写SQL求出对战名单，A对B和B对A 是一场比赛展示一条数据。 6 条数据
SELECT * FROM DUIWU A JOIN DUIWU B ON A.NAME < B.NAME;
--写SQL求出对战名单，A对B和B对A 是两场比赛展示两条数据。 12 条数据
SELECT * FROM DUIWU A JOIN DUIWU B ON A.NAME != B.NAME;
--查询所有部门及其员工信息，包括那些没有员工的部门
SELECT * FROM DEPT A LEFT JOIN EMP B ON A.DEPTNO=B.DEPTNO;
--查询所有工种为CLERK的员工的姓名及其部门名称
SELECT B.ENAME,A.DNAME FROM DEPT A LEFT JOIN EMP B ON A.DEPTNO=B.DEPTNO
WHERE B.JOB='CLERK';
--查询各个部门的编号,名称 以及部门人数、部门平均工资
SELECT A.DEPTNO,A.DNAME,COUNT(B.EMPNO) 人数,AVG(B.SAL) 平均工资
FROM DEPT A LEFT JOIN EMP B ON A.DEPTNO=B.DEPTNO
GROUP BY A.DEPTNO,A.DNAME;
--查询最低工资低于2000的部门名称
SELECT A.DNAME FROM DEPT A LEFT JOIN EMP B ON A.DEPTNO=B.DEPTNO
GROUP BY A.DNAME HAVING MIN(B.SAL)<2000;
--查询所有员工工资都在900~3000之间的部门的信息
SELECT A.DEPTNO,A.LOC,A.DNAME FROM DEPT A LEFT JOIN EMP B ON A.DEPTNO=B.DEPTNO
GROUP BY A.DEPTNO,A.LOC,A.DNAME HAVING MIN(B.SAL)>900 AND MAX(B.SAL)<3000;
------------------------------------------------------------------------------------------------------
--开窗函数/分析函数 14个 类似行函数，原先有多少行就返回多少行，只能写在 SELECT 后面
-- 聚合类开窗函数 5个 可以实现行控制
SUM()OVER() AVG()OVER() MIN()OVER() MAX()OVER() COUNT()OVER()
-- OVER() 能写 PARTITION BY 和 ORDER BY
PARTITION BY COL -- 分区 将col里相同的数据分为一个区，不同区之间相互独立
ORDER BY COL [ASC|DESC] -- 排序
SUM()OVER() AVG()OVER() -- SUM和AVG里字段只能用于数值类型
MIN()OVER() MAX()OVER() COUNT()OVER() -- 没有要求
-- 以SUM()OVER()举例：
1. OVER() 不写 PARTITION BY 和 ORDER BY，
将整个表的数据分为一个区，对区内的数据进行聚合，将结果填充到区里的每一行
SELECT EMP.*,SUM(SAL)OVER() NEW_SAL FROM EMP;
2. OVER() 写 PARTITION BY，不写 ORDER BY，
根据分区字段进行分区，对区内的数据进行聚合，将结果填充到区里的每一行
SELECT EMP.*,SUM(SAL)OVER(PARTITION BY DEPTNO) NEW_SAL FROM EMP;
3. OVER() 不写 PARTITION BY，写 ORDER BY。写 ORDER BY 会有累计效果
将整个表的数据分为一个区，对区内的数据进行累计聚合，将结果填充到对应的行
(默认从第一行累计到当前行，如果排序字段有重复值，会一直累积到重复值结束，将结果填充重复值的每一行)
SELECT EMP.*,SUM(SAL)OVER(ORDER BY SAL) NEW_SAL FROM EMP;
4. OVER() 写 PARTITION BY，写 ORDER BY。写 ORDER BY 会有累计效果
根据分区字段进行分区，对区内的数据进行累计聚合，将结果填充到对应的行
(默认从第一行累计到当前行，如果排序字段有重复值，会一直累积到重复值结束，将结果填充重复值的每一行)
SELECT EMP.*,SUM(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL) NEW_SAL FROM EMP;
-- 行控制 聚合类的开窗函数可以实现行控制 ORDER BY 后面 ROWS BETWEEN 行 AND 行
CURRENT ROW:当前行
n PRECEDING:当前行往前n行
n FOLLOWING:当前行往后n行
UNBOUNDED PRECEDING:表示从前面的起点 第一行
UNBOUNDED FOLLOWING:表示到后面的终点 最后一行
SELECT EMP.*,SUM(SAL)OVER(ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) NEW_SAL FROM EMP;
-- 从第一行累计到当前行
SELECT EMP.*,SUM(SAL)OVER(ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) NEW_SAL FROM EMP;
-- 从当前往前1行累计到当前行往后2行
SELECT EMP.*,
SUM(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) NEW_SAL FROM EMP;
-- 从当前往前1行累计到当前行往后2行
/*
按部门进行分区，根据员工编号排序累加 ，对工资汇总
第一行至当前行的汇总，
当前行至最后一行的汇总，
当前行的上一行到当前行的汇总，
当前行的上一行到当前行的下两行的汇总。
*/
SELECT EMP.*,
SUM(SAL)OVER(PARTITION BY DEPTNO ORDER BY EMPNO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) SAL1,
SUM(SAL)OVER(PARTITION BY DEPTNO ORDER BY EMPNO ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) SAL2,
SUM(SAL)OVER(PARTITION BY DEPTNO ORDER BY EMPNO ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) SAL3,
SUM(SAL)OVER(PARTITION BY DEPTNO ORDER BY EMPNO ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) SAL4
FROM EMP;
-- 聚合函数对NULL或者无数据进行聚合
SELECT SUM(COMM),COUNT(COMM) FROM EMP WHERE DEPTNO=10;
SELECT SUM(COMM),COUNT(COMM) FROM EMP WHERE EMPNO=8888;
-- 偏移类开窗函数 2个 一定要写 ORDER BY
LEAD(字段,行数,空值的返回值)OVER( ORDER BY ) -- 向上偏移
LAG(字段,行数,空值的返回值)OVER( ORDER BY ) -- 向下偏移
-- 行数 ，不写默认为 1
-- 空值的返回值 ，不写默认为 空值
SELECT EMP.*,LEAD(ENAME,1,'ZHANGSAN')OVER(ORDER BY EMPNO) NEW_NAME FROM EMP;
SELECT EMP.*,LAG(ENAME)OVER(PARTITION BY DEPTNO ORDER BY EMPNO) NEW_NAME FROM EMP;
-- 排名类开窗函数 3个 一定要写 ORDER BY
ROW_NUMBER()OVER( ORDER BY ) -- 生成连续不重复的序号 123456
RANK()OVER( ORDER BY ) -- 生成重复不连续的序号 123356
DENSE_RANK()OVER( ORDER BY ) -- 生成重复连续的序号 123345
SELECT EMP.*,
ROW_NUMBER()OVER(ORDER BY SAL) RN,
RANK()OVER(ORDER BY SAL) R,
DENSE_RANK()OVER(ORDER BY SAL) DR
FROM EMP;
-- 了解 4个
FIRST_VALUE()OVER() -- 获取第一条数据 可以实现行控制
-- PARTITION BY 分区
-- ORDER BY 排序，会有累计效果
SELECT EMP.*,
FIRST_VALUE(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) NEW_SAL FROM EMP;
LAST_VALUE()OVER() -- 获取最后一条的数据 可以实现行控制
-- PARTITION BY 分区
-- ORDER BY 排序，会有累计效果
SELECT EMP.*,
LAST_VALUE(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL) NEW_SAL FROM EMP;
RATIO_TO_REPORT()OVER() -- 求占比 一定不能写 ORDER BY
-- 求每个员工的工资占所在部门总工资的占比
SELECT EMP.*,RATIO_TO_REPORT(SAL)OVER(PARTITION BY DEPTNO) ZB ,
SAL/SUM(SAL)OVER(PARTITION BY DEPTNO) ZB1
FROM EMP;
NTILE(数字)OVER( ORDER BY ) -- 分片 一定要写 ORDER BY
每个序号的数据条数 = CEIL(剩余的数据条数/剩余片数)
SELECT EMP.*,NTILE(3)OVER(ORDER BY SAL) FP FROM EMP;
-- 1的数量 = CEIL(14/3) = 5
-- 2的数量 = CEIL(9/2) = 5
-- 3的数量 = CEIL(4/1) = 4
-- 求工资升序前20%的员工信息
SELECT * FROM
(SELECT EMP.*,NTILE(5)OVER(ORDER BY SAL) FP FROM EMP) A
WHERE FP=1;
七个用orderby有累积效果的开窗函数（5个聚合加上两个first_value,last_value）
--------------------------------------------------------------------------------------------
-- WITH 语句 建内存里临时表
WITH T1 AS (SELECT 语句)
, T2 AS (SELECT 语句) -- 子查询SELECT 语句可以从T1表里获取数据
, ....
SELECT 语句; -- 可以从所有的子查询里获取数据
WITH A AS (SELECT EMP.*,DENSE_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) PM FROM EMP)
SELECT * FROM A WHERE A.PM=2;
-- 建表
CREATE TABLE EMP1228 AS SELECT * FROM EMP; -- 物理表，数据存在磁盘里的
SELECT * FROM EMP1228;
-- 求每个员工的工资于所属部门的平均工资之间差值
-- 表关联
SELECT EMPNO,A.SAL-B.AVG_SAL FROM EMP A JOIN
(SELECT DEPTNO,AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO) B
ON A.DEPTNO=B.DEPTNO
-- 开窗函数
SELECT EMPNO,SAL,SAL-AVG(SAL)OVER(PARTITION BY DEPTNO) CHAZHI
FROM EMP;
-- 求每个部门工资第二高的员工信息（考虑并列第一，排除并列第一之后的工资最高的员工）
-- 开窗函数
SELECT * FROM
(SELECT EMP.*,DENSE_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) PM FROM EMP) A
WHERE A.PM=2;
-- 不用开窗函数
WITH T1 AS
(SELECT * FROM EMP WHERE SAL NOT IN
(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO))
SELECT * FROM T1 WHERE SAL IN
(SELECT MAX(SAL) FROM T1 GROUP BY DEPTNO)
-- 求每个部门工资第二高的员工信息（不考虑并列）
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 -- 其中一个
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
-- 开窗函数
SELECT * FROM
(SELECT EMP.*,ROW_NUMBER()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) PM FROM EMP) A
WHERE A.PM=2;
-- 不用开窗函数
WITH T1 AS
(SELECT * FROM EMP WHERE SAL IN
(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO))
,T2 AS (SELECT * FROM EMP WHERE EMPNO NOT IN
(SELECT MAX(EMPNO) FROM T1 GROUP BY SAL))
SELECT * FROM T2 WHERE SAL IN (
SELECT MAX(SAL) FROM T2 GROUP BY DEPTNO) ;
--连续题 用偏移函数或者排名函数
SELECT * FROM DL_LOG;
-- 找出连续3天及以上登录的用户id 结果为： 'S001'
-- 偏移
WITH T1 AS (SELECT DISTINCT USER_ID,TRUNC(LOG_DATE) LOG_DATE FROM DL_LOG)
,T2 AS (
SELECT T1.USER_ID,T1.LOG_DATE,
LEAD(LOG_DATE,2)OVER(PARTITION BY USER_ID ORDER BY LOG_DATE) DATE1 FROM T1)
SELECT DISTINCT USER_ID FROM T2 WHERE T2.DATE1-T2.LOG_DATE=2;
-- 排名
WITH T1 AS (SELECT DISTINCT USER_ID,TRUNC(LOG_DATE) LOG_DATE FROM DL_LOG) -- 去重
,T2 AS (
SELECT T1.USER_ID,T1.LOG_DATE,
T1.LOG_DATE-ROW_NUMBER()OVER(PARTITION BY USER_ID ORDER BY LOG_DATE) DATE1 FROM T1)
SELECT DISTINCT USER_ID FROM T2 GROUP BY USER_ID,DATE1 HAVING COUNT(*)>=3;
-- 每个用户连续登录的最大天数
WITH T1 AS (SELECT DISTINCT USER_ID,TRUNC(LOG_DATE) LOG_DATE FROM DL_LOG) -- 去重
,T2 AS (
SELECT T1.USER_ID,T1.LOG_DATE,
T1.LOG_DATE-ROW_NUMBER()OVER(PARTITION BY USER_ID ORDER BY LOG_DATE) DATE1 FROM T1)
SELECT USER_ID,MAX(LXTS) FROM
(SELECT USER_ID,DATE1,COUNT(*) LXTS FROM T2 GROUP BY USER_ID,DATE1)
GROUP BY USER_ID;
SELECT * FROM LX_SHUZI;
--找出至少连续3次出现的数字 结果为：1
-- 偏移
SELECT DISTINCT NUM FROM
(SELECT NUM,ID,LEAD(ID,2)OVER(PARTITION BY NUM ORDER BY ID) ID1 FROM LX_SHUZI) A
WHERE A.ID1-A.ID=2 ;
-- 排名
SELECT DISTINCT NUM FROM
(SELECT NUM,ID,ROW_NUMBER()OVER(PARTITION BY NUM ORDER BY ID) PM FROM LX_SHUZI) B
GROUP BY NUM,ID-PM HAVING COUNT(*)>=3;
-- 找出连续两年有员工入职的部门编号 10 20
SELECT * FROM EMP;
SELECT DEPTNO,TO_CHAR(HIREDATE,'YYYY') YEAR FROM EMP GROUP BY DEPTNO,TO_CHAR(HIREDATE,'YYYY')
---------------------------------------------------------------------------------------------
-- 同环比 一般配合日期粒度 粒度：维度的大小
月同比：当月的值与去年同月的值的比较 公式：(当月的值-去年同月的值)/去年同月的值 当月的值/去年同月的值-1
月环比：当月的值与同年上月的值的比较 公式：(当月的值-同年上月的值)/同年上月的值 当月的值/同年上月的值-1
SELECT * FROM THB ;
-- 计算月同比和月环比 偏移 表关联
-- 偏移 要求必须每月都有一行数据
SELECT NY,AMT,
ROUND(AMT/LAG(AMT,12)OVER(ORDER BY NY)-1,4) TB,
ROUND(AMT/LAG(AMT)OVER(PARTITION BY SUBSTR(NY,1,4) ORDER BY NY)-1,4) HB
FROM THB;
-- 表关联
SELECT A.*,A.AMT/B.AMT-1 TB, A.AMT/C.AMT-1 HB
FROM THB A
LEFT JOIN THB B ON A.NY=B.NY+100 -- 主表年份=从表的年份+1，月份=月份
-- ON SUBSTR(A.NY,1,4)=SUBSTR(B.NY,1,4)+1 AND SUBSTR(A.NY,5,2)=SUBSTR(B.NY,5,2)
-- ON A.NY=ADD_MONTHS(B.NY,12)
LEFT JOIN THB C ON A.NY=C.NY+1 ;
-----------------------------------------------------------------------------------------
--求emp表员工工资的中位数。
中位数： 如果员工人数为奇数，中位数=中间员工的工资
如果员工人数为偶数，中位数=中间两个员工的平均工资
SELECT AVG(SAL) FROM
(SELECT EMP.*,
ROW_NUMBER()OVER(ORDER BY SAL) PM,
COUNT(*)OVER() RS
FROM EMP) T1
WHERE PM IN (CEIL(RS/2),FLOOR(RS/2+1));
14 PM IN (7,8) CEIL(14/2) FLOOR(14/2+1)
15 PM IN (8,8) CEIL(15/2) FLOOR(15/2+1)
SELECT AVG(SAL) FROM
(SELECT EMP.*,
ROW_NUMBER()OVER(ORDER BY SAL) PM,
ROW_NUMBER()OVER(ORDER BY SAL DESC) PM1
FROM EMP) T1
WHERE PM-PM1 IN (-1,0,1);
奇数：PM - PM1 = 偶数 同一行两个序号是同奇同偶
1 奇数
2 偶数
3 奇数
偶数：PM - PM1 = 奇数 同一行两个序号是一奇一偶
-- DDL语言和DML语言
-- 事务 一个完整的事件
事务：ACID 四个特性
原子性 Atomicity 事务的所有操作要么全部成功执行，要么全部回滚到初始状态。
一致性 Consistency 事务执行前后数据库的状态必须保持一致。
隔离性 Isolation 多个事务并发执行时，每个事务的操作都不能被其他事务干扰。
持久性 Durability 事务一旦提交，其对数据库的改变就是永久性的。
A 给 B 转100块钱
A 余额 -100
B 余额 +100
C 给 B 转200块钱
C 余额 -200
B 余额 +200
-- DDL语言 ： 数据定义语言，操作的是元数据
CREATE 创建 DROP 删除 TRUNCATE 清空 ALTER 修改
CREATE -- 创建的语法
CREATE 对象的关键字 对象名 [元数据信息]
-- 创建用户
CREATE USER USER1 IDENTIFIED BY "123456"; -- 创建用户需要权限
-- 创建表
1. 通过建表语句
CREATE TABLE TABLE_NAME (
COL1 TYPE1,
COL2 TYPE2,
....
);
2. 通过select语句建表(复制select语句的结果集：字段名，数据类型，真实数据)
CREATE TABLE TABLE_NAME AS SELECT 语句;
CREATE TABLE EMP1231 AS SELECT * FROM EMP WHERE DEPTNO IN (10,20);
SELECT * FROM EMP1231; -- 复制表结构和数据
CREATE TABLE EMP12311 AS SELECT * FROM EMP WHERE 1=2;
SELECT * FROM EMP12311; -- 复制表结构
DROP -- 删除
DROP 对象的关键字 对象名;
DROP TABLE EMP12311; -- 表结构和表数据全部删除
DROP FUNCTION fun_del;
TRUNCATE -- 清空 主要用来清空表
TRUNCATE TABLE TABLE_NAME; -- 只删表数据，不删表结构 -- 先 DROP ，然后 CREATE
SELECT * FROM EMP1231;
TRUNCATE TABLE EMP1231;
ALTER -- 修改
ALTER 对象的关键字 对象名 [具体的操作];
-- 修改 表
-- 修改表名
ALTER TABLE 表名 RENAME TO 新表名;
ALTER TABLE EMP1129 RENAME TO EMP1231;
-- 修改字段名
ALTER TABLE 表名 RENAME COLUMN 原字段名 TO 新字段名;
ALTER TABLE EMP1231 RENAME COLUMN ENAME TO AGE;
-- 添加字段
ALTER TABLE 表名 ADD 字段名 数据类型;
ALTER TABLE EMP1231 ADD AGE NUMBER(2);
-- 删除字段
ALTER TABLE 表名 DROP COLUMN 字段名 ;
ALTER TABLE EMP1231 DROP COLUMN AGE ;
-- 修改字段数据类型
ALTER TABLE 表名 MODIFY 原字段名 新数据类型;
ALTER TABLE EMP1231 MODIFY ENAME NUMBER(5); -- 要求字段下没有数据
ALTER TABLE EMP1231 MODIFY JOB VARCHAR2(10); -- 如果字段下有数据，要求修改后的长度不能小于数据最大长度
------------------------------------------------------------------------------------------------
--DDL,DML,事务的关系
DDL 语言：不能提交和回滚(会自动提交事务)。
DML 语言：会触发事务。可以提交（commit）或者回滚（rollback）。运行DML语句，操作是被记录到回滚段（缓存区），当运行提交才会将操作写到磁盘，并且清空回滚段。当运行回滚时，撤销DML操作，并清空回滚段。
事务：可以提交（commit）或者回滚（rollback）。事务是分等级的。
锁 脏读 幻读 不可重复读
------------------------------------------------------------------------------------------------
-- DML语句 : 数据操作语言，主要针对表里真实的数据
INSERT UPDATE DELETE MERGE
INSERT -- 插入
1. 通过值插入,一次只能插入一行数据
INSERT INTO TABLE_NAME [(COL1,COL2,COL3...)] VALUES (值1，值2，值3....);
--(COL1,COL2,COL3...) 不写默认往表里所有字段插入数据，写往指定字段插入数据，没指定的字段插入空值
2. 通过 select语句的结果集插入数据(把结果集的数据全部插入)
INSERT INTO TABLE_NAME [(COL1,COL2,COL3...)] SELECT 语句;
--(COL1,COL2,COL3...) 不写默认往表里所有字段插入数据，写往指定字段插入数据，没指定的字段插入空值
-- 要求：值或者select语句查询的字段的数量和数据类型要与TABLE_NAME表里字段的数量和数据类型一一对应
UPDATE -- 修改
UPDATE TABLE_NAME SET COL1=值1[,COL2=值2...] [WHERE 条件]
-- [,COL2=值2...] 同时修改多个字段里的数据
-- [WHERE 条件] 不写，默认修改所有行对应字段的数据。写WHERE 条件，修改满足条件行对应字段的数据
DROP TABLE EMP1231;
CREATE TABLE EMP1231 AS SELECT * FROM EMP;
UPDATE EMP1231 A SET A.SAL=SAL+1000,A.COMM=NVL(COMM,0)+500 WHERE DEPTNO=10;
SELECT * FROM EMP1231;
DELETE -- 删除 一行一行删除
DELETE [FROM] TABLE_NAME [WHERE 条件];
-- [WHERE 条件] 不写，默认删除全表的数据。写[WHERE 条件]删除满足条件的行
DELETE EMP1231 WHERE DEPTNO=10;
MERGE -- 合并两张表 数据更新
-- 历史表(目标表) -- 对照表(数据发生变更(新增或者变更)的表)
-- 逻辑
1. 会有一个匹配条件(字段),字段里的数据要唯一，一般为主键
2. 当匹配成功时，说明目标表数据已经存在，只是发生变更。UPDATE
3. 当匹配不成功，说明目标表数据不存在，是新增的数据。 INSERT
-- MERGE的语法
MERGE INTO 目标表名 M
USING 对照表名(可以是子查询) D
ON (M.COL1=D.COL1) -- 匹配条件
WHEN MATCHED THEN -- 匹配成功
UPDATE -- 不能写表名
SET M.COL2=D.COL2, -- 不能写匹配字段
M.COL3=D.COL3,
M.COL4=D.COL4,
........
WHEN NOT MATCHED THEN -- 匹配不成功
INSERT -- 不能写 INTO 表名
[(M.COL1,M.COL2,M.COL3.....)]
VALUES
(D.COL1,D.COL2,D.COL3.....)
;
CREATE TABLE DEPT_M AS SELECT * FROM DEPT;
SELECT * FROM DEPT_M;
SELECT * FROM DEPT_D;
-- MERGE INTO语句
MERGE INTO DEPT_M M
USING DEPT_D D
ON (M.DEPTNO=D.DEPTNO) -- 匹配条件
WHEN MATCHED THEN -- 匹配成功
UPDATE -- 不能写表名
SET M.DNAME=D.DNAME, -- 不能写匹配字段
M.LOC=D.LOC
WHEN NOT MATCHED THEN -- 匹配不成功
INSERT -- 不能写 INTO 表名
(M.DEPTNO,M.DNAME,M.LOC)
VALUES
(D.DEPTNO,D.DNAME,D.LOC)
;
-- 三个删除的区别： DROP TRUNCATE DELETE
1. DROP 和 TRUNCATE 都属于DDL语句，执行之后不可以提交或者回滚。 DELETE 属于DML语句，执行之后可以提交或者回滚。
2. DROP 删除表结构和表数据。TRUNCATE 只删数据不删表结构。DELETE 只删数据不删表结构。
3. TRUNCATE 直接删除表再新建表结构，效率高。DELETE 一行一行删除数据，效率低。
-- 分批次提交
INSERT INTO TABLE_NAME SELECT 语句; -- 插入数据
-- 问题1：当结果集数据非常大的时候，占用内存越来越多，可能会导致运行变慢，甚至可能会死机
-- 解决问题1：分批次提交。插入一定量的数据后提交，清空回滚段
-- 问题2：如果脚本在运行的过程中，出现一些意外，比如服务器重启。需要重新运行脚本。
-- 解决问题2：再插入数据之前先清空表
最终语句：
TRUNCATE TABLE TABLE_NAME;
INSERT INTO TABLE_NAME SELECT 语句; -- 插入数据
-- 写脚本都要求能反复运行，结果不变。
-----------------------------------------------------------------------------------------------------
-- 伪列
ROWID -- 物理地址 非空且唯一 可以快速检索数据
1. 配合索引快速检索数据。
2. 快速删除重复数据。
SELECT * FROM EMP WHERE SAL>2000;
-- 存储格式：
1. 行式存储：以一行数据作为最小存储单元。 -- 方便筛选数据
2. 列式存储：以一列数据作为最小存储单元。 -- 方便处理数据(指标计算)
3. 行列存储：以n行m列的数据作为最小存储单元。
SELECT A.*,ROWID FROM EMP1231 A;
INSERT INTO EMP1231 SELECT * FROM EMP WHERE DEPTNO=20;
DELETE FROM EMP1231 WHERE ROWID NOT IN
(SELECT MIN(ROWID) FROM EMP1231 GROUP BY EMPNO);
SELECT * FROM EMP1231 WHERE ROWID='AAATVWAAEAAAAZTAAA';
-----------------------------------------------------------------------------------
ROWNUM -- 序号（不排序自然数序列）,在运行SELECT时才确定
SELECT EMP.*,ROWNUM FROM EMP WHERE DEPTNO IN (10,20) ORDER BY SAL;
-- 主要用于分页查询
1. 在 WHERE 直接使用 ROWNUM 作为筛选条件，展示的结果必须包含第一条数据，否则就没数据。
2. 如果查询的结果不包含第1条数据，必须要套子查询，必须要给ROWNUM起别名，用别名作为筛选条件
SELECT EMP.* FROM EMP WHERE ROWNUM BETWEEN 1 AND 5; -- 查询 第1-5条数据
SELECT EMP.* FROM EMP WHERE ROWNUM BETWEEN 6 AND 10; -- 查询 第6-10条数据 -- 没数据
SELECT A.* FROM
(SELECT EMP.*，ROWNUM RN FROM EMP) A
WHERE A.RN BETWEEN 6 AND 10;
LEVEL -- 层级
-- oracle里递归查询
SELECT EMP.*,LEVEL FROM EMP
START WITH MGR IS NULL -- 从上级编号为空的数据开始查
CONNECT BY MGR = PRIOR EMPNO ; -- 用于递归查询的条件
SELECT EMP.*,LEVEL ,
SYS_CONNECT_BY_PATH(ENAME,'→') 层级 -- 显示层级。用于展示的字段，分隔符
FROM EMP
START WITH MGR IS NULL -- 从上级编号为空的数据开始查
CONNECT BY MGR = PRIOR EMPNO ;
--START WITH empno =7678
-- CONNECT BY PRIOR MGR = EMPNO ; 从下往上查
SELECT * FROM
(SELECT EMP.*,LEVEL L FROM EMP
START WITH MGR IS NULL -- 从上级编号为空的数据开始查
CONNECT BY MGR = PRIOR EMPNO) A
WHERE A.L=4;
-- 表关联
-- 从子级查询上级
WITH T1 AS
(SELECT A.ENAME,
LTRIM(D.ENAME||'>'||C.ENAME||'>'||B.ENAME||'>'||A.ENAME,'>') PATH
FROM EMP A
LEFT JOIN EMP B ON B.EMPNO=A.MGR
LEFT JOIN EMP C ON C.EMPNO=B.MGR
LEFT JOIN EMP D ON D.EMPNO=C.MGR )
SELECT T1.ENAME,PATH,
LENGTH(PATH) - LENGTH(REPLACE(PATH,'>','')) +1 "LEVEL"
FROM T1;
正则表达式: 主要用于数据库操作中进行模式匹配和文本处理,灵活地搜索 验证 提取 和操作字符串数据
正则表达式入门网站: https://codejiaonang.com/#/course/regex_chapter1/0/0
正则表达式常用的匹配模式有以下几种：
一、字符匹配
. ：匹配任意单个字符。
例如，'a.b' 可以匹配 axb、ayb abb aab等
[]：匹配方括号中列出的任意一个字符。
例如，'[abc]' 匹配 a、b 或 c。
'[SAJ]' 匹配 S A J 不需要考虑顺序
'[a-z]' 匹配任意小写字母。
'[A-Z]' 匹配任意大写字母。
'[a-zA-Z]' 匹配任意字母。
'[0-9]' 匹配任意数字。
[^]：匹配不在方括号中列出的任意字符。 ^ 英文输入法 shfit+6
例如，'[^abc]' 匹配除了 a、b、c 之外的任意字符。
二、重复匹配
*：表示前面的元素出现零次或多次。
例如，'a*' 可以匹配 ''（空字符串）、'a'、'aa'、'aaa' 等。
+：表示前面的元素出现一次或多次。
例如，'a+' 可以匹配 'a'、'aa'、'aaa' 等，但不能匹配空字符串。
?：表示前面的元素出现零次或一次。
例如，'a?' 可以匹配 ''（空字符串）或 'a'。
{n}：表示前面的元素恰好出现 n 次。
例如，'a{3}' 匹配 'aaa'。
{n,}：表示前面的元素出现至少 n 次。
例如，'a{2,}' 匹配 'aa'、'aaa'、'aaaa' 等。
{n,m}：表示前面的元素出现至少 n 次，最多 m 次。
例如，'a{2,4}' 匹配 'aa'、'aaa'、'aaaa'。
三、位置匹配
^：表示匹配字符串的开头。
例如，'^a' 匹配以 a 开头的字符串。'^[0-9]' 匹配以数字开头的字符串 '^.'
$：表示匹配字符串的结尾。
例如，'a$' 匹配以 a 结尾的字符串。'[0-9]$' 匹配以数字结尾的字符串 '.$'
四、分组和引用(感兴趣可以研究一下)
()：用于分组，可以对一组字符或模式进行重复或其他操作。
例如，'(ab)+' 匹配 'ab'、'abab'、'ababab' 等。
\n（其中 n 是数字）：用于引用分组。
例如，'(\w)\1' 匹配一个单词字符，后面跟着同样的字符，即重复的字符。
五、特殊字符转义
如果要匹配正则表达式中的特殊字符本身，需要进行转义，使用 \。
例如，要匹配点 .，可以使用 '\.'。
要匹配加号 +，可以使用 '\+'。
--扩展
| 或者
例如 x|y 匹配“或” x|y可以匹配x或者y
[: :] 指定一个字符类，可以匹配该类中的任何字符
[[:alnum:]] 可以匹配字符0-9、A-Z、a-z 任意数字和字母类似于[0-9a-zA-Z]
[[:alpha:]] 可以匹配字符A-Z、a-z 任意字母，类似于 [a-zA-Z]
可以匹配中文 前提是 oracle的字符集能识别中文(支持中文)
字符集
ZHS16GBK 一个中文占2个字节
utf-8 一个中文占3个字节
--查看字符集
SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
[[:blank:]] 可以匹配空格或tab键
[[:digit:]] 可以匹配数字0-9 任意数字，类似于 [0-9]
[[:graph:]] 可以匹配非空字符
[[:lower:]] 可以匹配小写字母a-z
[[:print:]] 所有的可打印字符 与[[:graph:]]类似，不同之处在于[:print:]包括空格字符
[[:punct:]] 可以匹配标点符号.,""等等
[[:space:]] 可以匹配所有的空字符（不会打印出来）
[[:upper:]] 可以匹配大写字母A-Z
[[:xdigit:]] 可以匹配十六进制数字0-9、A-F、a-f
--扩展
以下是一些其他常用的正则表达式匹配模式：
1. \d ：匹配一个数字。
2. \D ：匹配一个非数字字符。
3. \w ：匹配字母、数字或下划线。
4. \W ：匹配非字母、数字或下划线的字符。
5. \s ：匹配空白字符，包括空格、制表符(tab)、换行符等。
6. \S ：匹配非空白字符。
正则函数
┌regexp_like 模糊匹配
├regexp_replace 用于替换串中的某个值。
├regexp_instr 使用正则表达式返回搜索模式的起点和终点（整数）
├regexp_substr 使用正则表达式来指定返回串的起点和终点
regexp_count(感兴趣 可以去搜索)
匹配参数/选项
c 大小写敏感 --默认 常用
i 大小写不敏感 --常用
n 允许可以匹配任意字符的操作符
m 多行模式,允许将原字符串作为多个字符串对待
x 扩展模式,忽略正则表达式中的空白字符
1.regexp_like 使用正则表达式进行模式匹配
语法: REGEXP_LIKE( source_string, pattern [, match_parameter ] )
source_string：要进行匹配的源字符串。
pattern：正则表达式模式，用于指定要匹配的模式。
match_parameter（可选）：可以用来指定匹配选项，例如 'i' 表示不区分大小写的匹配。
--查询emp表中姓名S开头或J开头的员工信息
select
*
from emp
where ename like 'S%' or ename like 'J%'
;
select
*
from emp
where REGEXP_LIKE(ename,'^[SJ]')
;
select
*
from emp
where REGEXP_LIKE(ename,'^[sj]','i')
;
--查询emp表中不是S开头或不是J开头的员工信息
select
*
from emp
where REGEXP_LIKE(ename,'^[^sj]','i')
;
--查询名字不以S或K结尾的员工信息
select
*
from emp
where REGEXP_LIKE(ename,'[^SK]$','c')
;
--查询名字是四位的员工姓名
select
*
from emp
where LENGTH(ENAME)=4
;
select
*
from emp
where REGEXP_LIKE(ename,'^.{4}$')
;
--查询person表 zip列数字开头的人
select
*
from person
where REGEXP_LIKE(ZIP,'^[0-9]')
;
--查询person表 zip列非数字结尾的人
select
*
from person
where REGEXP_LIKE(ZIP,'[0-9]$')
--查询person表 zip列 1到3或 7到9或a-c开头的人
select
*
from person
where REGEXP_LIKE(ZIP,'^[1-37-9a-c]')
--查询person表phone以13/15/18开头的11位数字的手机号码
select
*
from person
where REGEXP_LIKE(phone,'^1[358]\d{9}$')
2.regexp_replace 用于使用正则表达式进行字符串替换
语法：
REGEXP_REPLACE( source_string, pattern, replacement_string [, position [, occurrence [, match_parameter ]]] )
source_string：要进行替换操作的源字符串。
pattern：正则表达式模式，用于指定要匹配的部分。
replacement_string：用于替换匹配部分的字符串。--也是可选 不写的时候 把匹配的字符 去掉
position（可选）：开始搜索的位置，默认为 1，表示从源字符串的开头开始。
occurrence（可选）：指定要替换的模式出现的次数，默认为0 全部替换。
match_parameter（可选）：可以用来指定匹配选项，例如 'i' 表示不区分大小写的匹配。
--以 'helloworld' 做示例
select
'helloworld' a,
REGEXP_REPLACE('helloworld','[ol]') b,
REGEXP_REPLACE('helloworld','[ol]','a') c,
REGEXP_REPLACE('helloworld','[ol]','a',4) d,
REGEXP_REPLACE('helloworld','[ol]','a',4,1) e,
REGEXP_REPLACE('helloworld','[ol]','a',4,2) f --从第四位开始匹配 ,把匹配到的第2次出现的字符 替换
--REGEXP_REPLACE('helloworld','[ol]','a',-1) e 提示超出-1范围
from dual ;
--查询person表 把zip列所有的数字替换成*
select
zip,
REGEXP_REPLACE(zip,'\d','*') newzip
from person
--查询person表 把zip列 第一个数字替换成? =>把zip列 从第1位开始找 第1次出现的数字 替换成?
select
zip,
REGEXP_REPLACE(zip,'\d','?',1,1) newzip
from person
--查询person表 把zip列所有的字母部分截取出来 => 把zip列 所有非字母部分去掉 保留字母部分
select
zip,
REGEXP_REPLACE(zip,'[^[:alpha:]]','') newzip,
REGEXP_REPLACE(zip,'[^A-Z]','',1,0,'i') newzip2,
REGEXP_REPLACE(zip,'[^a-zA-Z]') newzip3
from person
--查询person表 把zip列所有的数字部分截取出来 => 把zip列 所有非数字部分去掉 保留数字部分
select
zip,
REGEXP_REPLACE(zip,'[^[:digit:]]','') newzip
from person
--从字符串 “Hello, world! This is a test.” 中删除所有标点符号。再删除空格
select
'Hello, world! This is a test.' a,
REGEXP_REPLACE('Hello, world! This is a test.','[[:punct:] ]','') b
from dual;
--从字符串 “Today is a sunny day! 12345” 中删除所有数字和感叹号。
select
'Today is a sunny day! 12345' a,
REGEXP_REPLACE('Today is a sunny day! 12345','[[:digit:]!]','') b
from dual;
--从字符串 “1a2BBbb3ccc4d5e” 中删除所有连续3个及以上字母串(不区分大小写)。
select
'1a2BBbb3ccc4d5e' a,
REGEXP_REPLACE('1a2BBbb3ccc4d5e','[a-zA-Z]{3,}') b
from dual;
删除连续出现3次以上的同一个字母？
select
'1a2BBbb3ccc4d5e' a,
REGEXP_REPLACE('1a2BBbb3ccc4CCCd5e','[a-z]{3,}|[A-Z]{3,}') b
from dual;
3.REGEXP_INSTR 在给定的字符串中搜索正则表达式模式，并返回该模式的起始位置 --返回数值
语法:
REGEXP_INSTR(source_string, pattern [,start_position [, occurrence [, return_option [, match_parameter ]]]] )
source_string：要搜索的源字符串。
pattern：正则表达式模式。
start_position（可选）：指定开始搜索的位置，默认为 1，表示从源字符串的开头开始搜索。
occurrence（可选）：指定要查找的模式出现的次数，默认为 1，表示查找第一次出现。
return_option(可选)：指定返回的结果类型。默认为 0。
0 表示返回模式出现的起始位置； n
1 表示返回模式出现后紧跟在模式后面的下一个字符的位置。 n+1
match_parameter（可选）：可以用来指定匹配选项，例如 'i' 表示不区分大小写的匹配。
--以 'helloworld' 做示例
select
'helloworld' a,
REGEXP_INSTR('helloworld','l') b,
REGEXP_INSTR('helloworld','l',3) c,
REGEXP_INSTR('helloworld','l',3,2) d,
REGEXP_INSTR('helloworld','l',3,2,1) e
from dual;
--查找EMP表 ENAME 中 从第二位开始 第三次出现 英文字母的 位置
select
ENAME,
REGEXP_INSTR(ENAME,'[a-zA-Z]',2,3) ins
from emp;
-- 查找字符串'DSAJ234KNKB4K2B4BK2' 第三次出现数值的数字位置 => 连着的数字 看成出现1次
select
'DSAJ234KNKB4K2B4BK2' a,
REGEXP_INSTR('DSAJ234KNKB4K2B4BK2','[0-9]+',1,3) b
from dual;
--查找person表zip列第一个非数字的字符的位置
select
zip,
REGEXP_INSTR(zip,'[^0-9]') newzip
from person
;
--查找person表zip列第二次出现"字母和数字的组合"(字母在前数字在后的组合)的位置(匹配字符串的第一个字符的位置)
select
zip,
REGEXP_INSTR(zip,'[a-zA-Z][0-9]',1,2) newzip
from person
;
--查找person表email列'@'第一次出现的位置, '.' 第一次出现的位置, 标点符号第一次出现的位置
select
email,
REGEXP_INSTR(email,'@') newe1,
REGEXP_INSTR(email,'[.]') newe2,
REGEXP_INSTR(email,'\.') newe22,
REGEXP_INSTR(email,'[[:punct:]]') newe3
from person
;
--截取'Hello WORLD',使用'i'参数进行大小写不敏感搜索 返回 “WORLD” 中的 “world” 部分（不区分大小写）
SELECT 'Hello WORLD',REGEXP_SUBSTR('Hello WORLD','world',1,1,'i') FROM DUAL;
--提取'abc123def456ghi789'第二次数字子串 => 返回 '456'
SELECT REGEXP_SUBSTR('abc123def45556ghi789', '[0-9]+', 1, 2) FROM dual;
--截取zip列第一个"数字+字母"的组合
SELECT
ZIP,
REGEXP_SUBSTR(ZIP,'[0-9][a-zA-Z]+',1,1) a,
REGEXP_SUBSTR(ZIP,'[0-9][a-z]+',1,1,'i') b
FROM PERSON
;
--截取zip列第一个连续的字母串 =>连续的字母串 指 连续2个或以上字母
SELECT
ZIP,
REGEXP_SUBSTR(ZIP,'[a-zA-Z]{2,}',1,1) a,
REGEXP_SUBSTR(ZIP,'[a-z]{2,}',1,1,'i') b
FROM PERSON
;
--截取'i love http://www.52oracle.com'第一次出现o开头e结尾中间任意四个字符的字符串
SELECT
'i love http://www.52oracle.com',
REGEXP_SUBSTR('i love http://www.52oracle.com','o....e',1,1)
FROM DUAL
;
select
'i love http://www.52oracle.com' a,
REGEXP_SUBSTR('i love http://www.52oracle.com','o.{4}e') b,
REGEXP_SUBSTR('i love http://www.52oracle.com','o[[:print:]]{4}e') c
from dual;
题目一: 访问量统计
我们有如下的用户访问数据
userId visitDate visitCount
u01 2017/1/21 5
u02 2017/1/23 6
u03 2017/1/22 8
u04 2017/1/20 3
u01 2017/1/23 6
u01 2017/2/21 8
U02 2017/2/23 6
U01 2017/2/22 4
--数据准备
--如果存在表则删除
drop table test1;
--创建表格
CREATE TABLE test1 (
userId VARCHAR2(50), --用户id
visitDate VARCHAR2(50), --访问日期
visitCount NUMBER --访问次数
);
--插入数据
INSERT INTO test1 (userId, visitDate, visitCount) VALUES ('u01', '2017/01/21', 5);
INSERT INTO test1 (userId, visitDate, visitCount) VALUES ('u02', '2017/01/23', 6);
INSERT INTO test1 (userId, visitDate, visitCount) VALUES ('u03', '2017/01/22', 8);
INSERT INTO test1 (userId, visitDate, visitCount) VALUES ('u04', '2017/01/20', 3);
INSERT INTO test1 (userId, visitDate, visitCount) VALUES ('u01', '2017/01/23', 6);
INSERT INTO test1 (userId, visitDate, visitCount) VALUES ('u01', '2017/02/21', 8);
INSERT INTO test1 (userId, visitDate, visitCount) VALUES ('u02', '2017/02/23', 6);
INSERT INTO test1 (userId, visitDate, visitCount) VALUES ('u01', '2017/02/22', 4);
commit;
--查询数据
select * from test1;
要求使用SQL统计出每个用户每个月的访问次数以及每个月累计访问次数
userId visitDate visitCount LJ_visitCount
--------------------------------------------------------------------------------
SELECT A.*,SUM(visitCount)OVER(PARTITION BY USERID ORDER BY visitDate) LJ_visitCount FROM
(SELECT USERID,SUBSTR(visitDate,1,7) visitDate ,SUM(visitCount) visitCount
FROM test1 GROUP BY USERID,SUBSTR(visitDate,1,7) ) A;
--------------------------------------------------------------------------------
题目二:电商场景TopK统计
有50W个京东店铺，
每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，
访问日志存储的表名为Visit，
访客的用户id为user_id，
被访问的店铺名称为shop，数据如下：
user_id shop
u1 a
u2 b
u1 b
u1 a
u3 c
u4 b
u1 a
u2 c
u5 b
u4 b
u6 c
u2 c
u1 b
u2 a
u2 a
u3 a
u5 a
u5 a
u5 a
--数据准备
--如果存在表则删除
drop table test2;
--创建表格
CREATE TABLE test2 (
user_id VARCHAR2(10),
shop VARCHAR2(10)
);
--插入数据
INSERT INTO test2 VALUES ('u1', 'a');
INSERT INTO test2 VALUES ('u2', 'b');
INSERT INTO test2 VALUES ('u1', 'b');
INSERT INTO test2 VALUES ('u1', 'a');
INSERT INTO test2 VALUES ('u3', 'c');
INSERT INTO test2 VALUES ('u4', 'b');
INSERT INTO test2 VALUES ('u1', 'a');
INSERT INTO test2 VALUES ('u2', 'c');
INSERT INTO test2 VALUES ('u5', 'b');
INSERT INTO test2 VALUES ('u4', 'b');
INSERT INTO test2 VALUES ('u6', 'c');
INSERT INTO test2 VALUES ('u2', 'c');
INSERT INTO test2 VALUES ('u1', 'b');
INSERT INTO test2 VALUES ('u2', 'a');
INSERT INTO test2 VALUES ('u2', 'a');
INSERT INTO test2 VALUES ('u3', 'a');
INSERT INTO test2 VALUES ('u5', 'a');
INSERT INTO test2 VALUES ('u5', 'a');
INSERT INTO test2 VALUES ('u5', 'a');
commit;
--查询数据
select * from test2;
请统计：
（1）每个店铺的访客数
--------------------------------------------------------------------------------
SELECT SHOP,COUNT(DISTINCT user_id) FROM test2 GROUP BY SHOP;
--------------------------------------------------------------------------------
(2)每个店铺访问次数排名前3的访客信息。输出店铺名称、访客id、访问次数
--------------------------------------------------------------------------------
SELECT B.SHOP,B.USER_ID,B.CS FROM
(SELECT A.*,ROW_NUMBER()OVER(PARTITION BY SHOP ORDER BY CS DESC) PM FROM
(SELECT SHOP,user_id,COUNT(*) CS FROM test2 GROUP BY SHOP,user_id) A) B
WHERE B.PM<=3;
--------------------------------------------------------------------------------
题目三:数据口径不一致问题
DROP TABLE t;
CREATE TABLE t(
dt date,
u_id int,
order_count int,
order_m float
);
INSERT INTO t VALUES (to_date('2020-07-01','yyyy-mm-dd'),1,1,100);
INSERT INTO t VALUES (to_date('2020-07-01','yyyy-mm-dd'),2,2,100);
INSERT INTO t VALUES (to_date('2020-07-02','yyyy-mm-dd'),3,1,100);
INSERT INTO t VALUES (to_date('2020-07-02','yyyy-mm-dd'),1,2,200);
INSERT INTO t VALUES (to_date('2020-07-03','yyyy-mm-dd'),4,1,300);
INSERT INTO t VALUES (to_date('2020-07-03','yyyy-mm-dd'),2,2,400);
INSERT INTO t VALUES (to_date('2020-07-05','yyyy-mm-dd'),5,1,500);
INSERT INTO t VALUES (to_date('2020-07-05','yyyy-mm-dd'),2,2,600);
COMMIT;
SELECT * FROM t;
--期望求出平台统计结果，如下表所示(假设当天是2020-07-05)
累计订单次数 累计下单总额 累计订单人数 最近3天累计下单次数 最近3天累计订单总额
XXX XXX XXX XXX XXX
--------------------------------------------------------------------------------
--快照 快速拍照片
SELECT * FROM
(SELECT SUM(order_count) 累计订单次数,SUM(order_m) 累计下单总额,
COUNT(DISTINCT u_id) 累计订单人数 FROM t) A,
(SELECT SUM(order_count) 最近3天累计订单次数,SUM(order_m) 最近3天累计下单总额 FROM t
WHERE DATE'2020-07-05'-DT <= 2 ) B;
-- CASE WHEN 满足条件进行聚合
SELECT SUM(order_count) 累计订单次数,SUM(order_m) 累计下单总额,
COUNT(DISTINCT u_id) 累计订单人数,
SUM(CASE WHEN DATE'2020-07-05'-DT <= 2 THEN order_count END) 最近3天累计订单次数,
SUM(CASE WHEN DATE'2020-07-05'-DT <= 2 THEN order_m END) 最近3天累计下单总额
FROM t;
-------------------------------------------------------------------------------
已知表以及创建语句
表一： 客户信息表 cust_info
CUST_ID CUST_NAME
00100010 张三
00100110 李四
00100210 王五
00100211 lee
00100201 sql
-- 创建客户信息表
CREATE TABLE cust_info (
CUST_ID VARCHAR2(20) PRIMARY KEY,
CUST_NAME VARCHAR2(50)
);
-- 插入数据
INSERT INTO cust_info VALUES ('00100010', '张三');
INSERT INTO cust_info VALUES ('00100110', '李四');
INSERT INTO cust_info VALUES ('00100210', '王五');
INSERT INTO cust_info VALUES ('00100211', 'lee');
INSERT INTO cust_info VALUES ('00100201', 'sql');
commit;
表二：存款余额表 bal_info
ACCT_ID CUST_ID BAL
100010001001 00100010 100
100100100101 00100010 200
100011021001 00100110 100
102102100101 00100210 150
123014001001 00100211 200
150160108101 00100211 300
150160108102 00100201 0
drop table bal_info;
-- 创建存款余额表
CREATE TABLE bal_info (
ACCT_ID VARCHAR2(20) PRIMARY KEY,
CUST_ID VARCHAR2(20),
BAL NUMBER(10, 2)
);
-- 插入数据
INSERT INTO bal_info VALUES ('100010001001', '00100010', 100);
INSERT INTO bal_info VALUES ('100100100101', '00100010', 200);
INSERT INTO bal_info VALUES ('100011021001', '00100110', 100);
INSERT INTO bal_info VALUES ('102102100101', '00100210', 150);
INSERT INTO bal_info VALUES ('123014001001', '00100211', 200);
INSERT INTO bal_info VALUES ('150160108101', '00100211', 300);
INSERT INTO bal_info VALUES ('150160108102', '00100201', 0);
commit;
表一 存款客户信息表 cust_info
表二 客户账户余额信息表 bal_info
提示：一个客户有多个账户，一个账户有多笔交易
select * from cust_info;
select * from bal_info;
题目四:请用一个SQL写出 总客户数及存款总金额
结果如下
cust_no sum_bal
XXX XXX
------------------------------------------------------------------------
SELECT COUNT(DISTINCT CUST_ID) cust_no,SUM(BAL) sum_bal FROM bal_info;
------------------------------------------------------------------------
题目五:请用一个SQL 写出当前 有存款余额 的 客户总数
------------------------------------------------------------------------
SELECT COUNT(DISTINCT CUST_ID) cust_no FROM bal_info WHERE BAL>0;
------------------------------------------------------------------------
题目六:请用一个SQL 写出当前客户 所有帐户存款余额都大于150的 客户总数
------------------------------------------------------------------------
SELECT COUNT(DISTINCT CUST_ID) cust_no FROM bal_info GROUP BY CUST_ID HAVING MIN(BAL)>150 ;
------------------------------------------------------------------------
题目七--表G0328如下,ID为自增长列，找出累计金额刚好超过100的那一条数据（6,1）
DROP TABLE G0328;
CREATE TABLE G0328 (
ID INT,
金额 INT
);
INSERT INTO G0328 VALUES (1,30);
INSERT INTO G0328 VALUES (2,30);
INSERT INTO G0328 VALUES (3,30);
INSERT INTO G0328 VALUES (4,9);
INSERT INTO G0328 VALUES (5,1);
INSERT INTO G0328 VALUES (6,1);
INSERT INTO G0328 VALUES (7,15);
INSERT INTO G0328 VALUES (8,33);
INSERT INTO G0328 VALUES (9,5);
INSERT INTO G0328 VALUES (10,8);
INSERT INTO G0328 VALUES (11,14);
INSERT INTO G0328 VALUES (12,3);
COMMIT;
SELECT * FROM G0328;
------------------------------------------------------------------------
SELECT B.ID,B.金额 FROM
(SELECT A.*,SUM(金额)OVER(ORDER BY ID) LJ FROM G0328 A) B
WHERE LJ>100 AND ROWNUM=1;
------------------------------------------------------------------------
题目八
DROP TABLE acct;
CREATE TABLE acct(
dt date ,
rate number(10,1)
);
INSERT INTO acct VALUES (to_date('2010-01-01','yyyy-mm-dd'),5.1); --
INSERT INTO acct VALUES (to_date('2010-10-01','yyyy-mm-dd'),5.1);
INSERT INTO acct VALUES (to_date('2011-01-01','yyyy-mm-dd'),6.0); --
INSERT INTO acct VALUES (to_date('2012-10-31','yyyy-mm-dd'),6.0);
INSERT INTO acct VALUES (to_date('2012-11-10','yyyy-mm-dd'),6.0);
INSERT INTO acct VALUES (to_date('2012-12-31','yyyy-mm-dd'),6.0);
INSERT INTO acct VALUES (to_date('2013-03-31','yyyy-mm-dd'),5.9); --
INSERT INTO acct VALUES (to_date('2013-09-01','yyyy-mm-dd'),5.5); --
INSERT INTO acct VALUES (to_date('2014-05-01','yyyy-mm-dd'),5.5);
INSERT INTO acct VALUES (to_date('2015-01-01','yyyy-mm-dd'),5.1); --
INSERT INTO acct VALUES (to_date('2016-06-01','yyyy-mm-dd'),5.1);
INSERT INTO acct VALUES (to_date('2017-09-01','yyyy-mm-dd'),5.1);
COMMIT;
SELECT * FROM acct;
-- 将上面的数据，显示成如下的结果：
-- 拉链表 记录历史变化数据
起始日期 截止日期 贷款利率
2010/1/1 2010/12/31 5.1%
2011/1/1 2013/3/30 6%
2013/3/31 2013/8/31 5.9%
2013/9/1 2014/12/31 5.5%
2015/1/1 9999/12/31 5.1%
----------------------------------------------------------------------------------------
SELECT B.DT 起始日期 ,
LEAD(B.DT-1,1,DATE'9999-12-31')OVER(ORDER BY B.DT) 截止日期,
B.RATE||'%' 贷款利率
FROM
(SELECT A.*,LAG(rate,1,0)OVER(ORDER BY dt)P_rate FROM acct A) B
WHERE B.P_RATE!=B.RATE ;
----------------------------------------------------------------------------------------
DROP TABLE cust_tal; --表五 存款客户信息表
DROP TABLE bal_info1; --表六 客户账户余额信息表
DROP TABLE AMT_INF ; --表七 账户交易流水信息表
-- 表五 存款客户信息表
CREATE TABLE cust_tal
(
CUST_ID NUMBER(3),
CUST_NAME nvarchar2(20)
);
INSERT INTO cust_tal VALUES('111','张三');
INSERT INTO cust_tal VALUES('222','李四');
INSERT INTO cust_tal VALUES('333','王五');
INSERT INTO cust_tal VALUES('444','lee');
INSERT INTO cust_tal VALUES('555','sql');
COMMIT;
SELECT * FROM cust_tal;
--表六 客户账户余额信息表
CREATE TABLE bal_info1
(
ACCT_ID NUMBER(6),
CUST_ID NUMBER(3),
BAL NUMBER(3)
);
INSERT INTO bal_info1 VALUES('111111','111','100');
INSERT INTO bal_info1 VALUES('222222','111','200');
INSERT INTO bal_info1 VALUES('333333','222','100');
INSERT INTO bal_info1 VALUES('444444','333','150');
INSERT INTO bal_info1 VALUES('555555','444','200');
INSERT INTO bal_info1 VALUES('666666','444','300');
COMMIT;
SELECT * FROM bal_info1;
--表七 账户交易流水信息表
CREATE TABLE AMT_INF
(
SEQ_ID NUMBER(1),
ACCT_ID NUMBER(6),
TRADE_DATE DATE,
TRADE_TYPE VARCHAR(10) ,
AMT NUMBER(3)
);
INSERT INTO AMT_INF VALUES('1','111111',TO_DATE('20191231','YYYYMMDD'),'存入','100');
INSERT INTO AMT_INF VALUES('2','111111',TO_DATE('20200130','YYYYMMDD'),'存入','50');
INSERT INTO AMT_INF VALUES('3','111111',TO_DATE('20200304','YYYYMMDD'),'取现','200');
INSERT INTO AMT_INF VALUES('4','111111',TO_DATE('20200601','YYYYMMDD'),'取现','10');
INSERT INTO AMT_INF VALUES('5','111111',TO_DATE('20200703','YYYYMMDD'),'取现','20');
INSERT INTO AMT_INF VALUES('6','333333',TO_DATE('20200306','YYYYMMDD'),'存入','200');
INSERT INTO AMT_INF VALUES('7','444444',TO_DATE('20200603','YYYYMMDD'),'取现','10');
INSERT INTO AMT_INF VALUES('8','444444',TO_DATE('20200706','YYYYMMDD'),'取现','20');
COMMIT;
SELECT * FROM AMT_INF;
题目九 请用一个SQL写出每个客户（所有客户）本年（取2020年）存入和取现总金额及当前存款余额。
CUST_ID CUST_NAME BAL 存入总金额 取现总金额
----------------------------------------------------------------------------------------
SELECT A.CUST_ID,A.CUST_NAME,SUM(BAL) BAL ,SUM(存入) 存入总金额,SUM(取现) 取现总金额 FROM
cust_info A
LEFT JOIN bal_info B ON A.CUST_ID=B.CUST_ID
LEFT JOIN
(SELECT ACCT_ID,
SUM(CASE WHEN TRADE_TYPE='存入' THEN AMT END) 存入,
SUM(CASE WHEN TRADE_TYPE='取现' THEN AMT END) 取现
FROM AMT_INF WHERE TO_CHAR(TRADE_DATE,'YYYY')='2020'
GROUP BY ACCT_ID) C ON B.ACCT_ID=C.ACCT_ID
GROUP BY A.CUST_ID,A.CUST_NAME ;
---------------------------------------------------------------------------------------
--附加题
已知某家基金公司在全国拥有多家代销机构代销其基金产品，每家代销机构下辖多个代销网点，每个代销网点下有多个销售员，此外不同销售机构的销售网点代码可能相同，同一销售机构的销售网点不可相同。
下表为代销机构的代销网点销量统计表：
销量统计表 T_SALE_STATS
AGENCYNO NETNO CITY SALEAMOUNT
--销售机构代码 销售机构网点号 网点所在城市 网点销量
001 001A 北京 10000
001 001B 上海 20000
001 001C 深圳 15000
001 001D 深圳 15000
002 002A 广州 9000
002 002B 广州 8000
003 003A 南京 5000
004 004A 成都 7000
下表为销售员与代销机构、网点关系表：
销售员网点关系表 T_SALERS
AGENCYNO NETNO SALERNO
--销售机构代码 销售机构网点号 销售员编码
001 001A S0001
001 001B S0002
001 001B S0003
001 001B S0004
001 001B S0005
002 002A S0006
002 002A S0007
002 002A S0008
002 002A S0009
002 002A S0010
002 002B S0011
002 002B S0012
002 002B S0013
004 004A S0014
004 004A S0015
因某些原因，部分销售机构网点未配置销售员，因此总部决定，以销售机构划分，将该销售机构下未配置销售员的网点销量分摊到该销售机构下有销售员的网点上，重新计算销量统计表，用于销量考核。
--建表语句
--创建销量统计表
DROP TABLE T_SALE_STATS;
CREATE TABLE T_SALE_STATS (
AGENCYNO VARCHAR2(20), --销售机构代码
NETNO VARCHAR2(20), --销售机构网点号
CITY VARCHAR2(20), --网点所在城市
SALEAMOUNT NUMBER --网点销量
);
INSERT INTO T_SALE_STATS VALUES ('001','001A','北京',10000); --16000 --第一种分摊的结果
INSERT INTO T_SALE_STATS VALUES ('001','001B','上海',20000); --44000
INSERT INTO T_SALE_STATS VALUES ('001','001C','深圳',15000); --没有
INSERT INTO T_SALE_STATS VALUES ('001','001D','深圳',15000); --没有
INSERT INTO T_SALE_STATS VALUES ('002','002A','广州',9000); --9000
INSERT INTO T_SALE_STATS VALUES ('002','002B','广州',8000); --8000
INSERT INTO T_SALE_STATS VALUES ('003','003A','北京',5000); --5000
INSERT INTO T_SALE_STATS VALUES ('004','004A','北京',7000); --7000
COMMIT;
--创建销售员网点关系表
DROP TABLE T_SALERS;
CREATE TABLE T_SALERS (
AGENCYNO VARCHAR2(20), --销售机构代码
NETNO VARCHAR2(20), --销售机构网点号
SALERNO VARCHAR2(20) --销售员编码
);
INSERT INTO T_SALERS VALUES ('001','001A','S0001');
INSERT INTO T_SALERS VALUES ('001','001B','S0002');
INSERT INTO T_SALERS VALUES ('001','001B','S0003');
INSERT INTO T_SALERS VALUES ('001','001B','S0004');
INSERT INTO T_SALERS VALUES ('001','001B','S0005');
INSERT INTO T_SALERS VALUES ('002','002A','S0006');
INSERT INTO T_SALERS VALUES ('002','002A','S0007');
INSERT INTO T_SALERS VALUES ('002','002A','S0008');
INSERT INTO T_SALERS VALUES ('002','002A','S0009');
INSERT INTO T_SALERS VALUES ('002','002A','S0010');
INSERT INTO T_SALERS VALUES ('002','002B','S0011');
INSERT INTO T_SALERS VALUES ('002','002B','S0012');
INSERT INTO T_SALERS VALUES ('002','002B','S0013');
INSERT INTO T_SALERS VALUES ('004','004A','S0014');
INSERT INTO T_SALERS VALUES ('004','004A','S0015');
COMMIT;
--查询表数据
SELECT * FROM T_SALE_STATS;
SELECT * FROM T_SALERS;
（1）第一种分摊方式：按照销售网点销售员数量分摊，生成新的销量统计表T_SALE_STATS_NEW_1,请写出对应重新统计销量统计表的语句。
（例如：上图数据中，001机构的001C，001D网点未配置销售员，两个网点的总销量30000，按照其他网点的人员比例分摊，001A网点一名销售员，001B网点4名销售员，则分摊后001A网点分摊销量为30000×（1/（1+4）），001B网点分摊销量为30000×（4/(1+4)），考核销量则为原实际销量+分摊销量）
-- 如果机构下没有人，各网点保留各自的销量，不分配
WITH T1 AS
(SELECT NETNO,COUNT(*) RS FROM T_SALERS GROUP BY NETNO) -- 每个网点的人数
,T2 AS (SELECT A.*,T1.RS FROM T_SALE_STATS A LEFT JOIN T1 ON A.NETNO=T1.NETNO) -- 将销量表和网点人数进行关联
-- SELECT AGENCYNO,SUM(SALEAMOUNT) FROM T2 WHERE RS IS NULL GROUP BY AGENCYNO
,T3 AS (SELECT T2.*,
SUM(RS)OVER(PARTITION BY AGENCYNO) ZRS,
SUM(CASE WHEN RS IS NULL THEN SALEAMOUNT END)OVER(PARTITION BY AGENCYNO) ZFPXL
FROM T2)
SELECT T3.AGENCYNO,T3.NETNO,T3.CITY,
T3.SALEAMOUNT + NVL(T3.ZFPXL*RS/ZRS,0) SALEAMOUNT
FROM T3
WHERE RS IS NOT NULL OR ZRS IS NULL;
-- WHERE NOT (RS IS NULL AND ZRS IS NOT NULL);
（2）第二种分摊方式：按照有销售员的网点销量占该销售机构总销量(不含未配置销售员网点销量)的占比分摊，生成新的销量统计表T_SALE_STATS_NEW_2,请写出对应重新统计销量统计表的语句。
（例如：上图数据中，001机构的001C网点未配置销售员，该网点的销量30000，按照其他网点的销量比例分摊，001A网点销量10000，001B网点销量20000，则分摊后001A网点分摊销量为30000×（10000/（10000+20000）），001B网点分摊销量为30000×（20000/（10000+20000）），考核销量则为原实际销量+分摊销量
-- 如果机构下没有人，各网点保留各自的销量，不分配
WITH T1 AS
(SELECT NETNO,COUNT(*) RS FROM T_SALERS GROUP BY NETNO) -- 每个网点的人数
,T2 AS (SELECT A.*,T1.RS FROM T_SALE_STATS A LEFT JOIN T1 ON A.NETNO=T1.NETNO) -- 将销量表和网点人数进行关联
-- SELECT AGENCYNO,SUM(SALEAMOUNT) FROM T2 WHERE RS IS NULL GROUP BY AGENCYNO
,T3 AS (SELECT T2.*,
SUM(RS)OVER(PARTITION BY AGENCYNO) ZRS,
SUM(CASE WHEN RS IS NULL THEN SALEAMOUNT END)OVER(PARTITION BY AGENCYNO) ZFPXL,
SUM(CASE WHEN RS IS NOT NULL THEN SALEAMOUNT END)OVER(PARTITION BY AGENCYNO) ZXL
FROM T2)
SELECT T3.AGENCYNO,T3.NETNO,T3.CITY,
T3.SALEAMOUNT + NVL(T3.ZFPXL*SALEAMOUNT/ZXL,0) SALEAMOUNT
FROM T3
WHERE RS IS NOT NULL OR ZRS IS NULL;
----------------------------------------------------------------------------------------
XXX银行活期利率(年化)
利息=存款*1.9%*存款天数/360 10000元 100天 利息=10000*0.019*100/360
存款天数=结束日期-开始日期
-- 利率变化表
ST_DATE END_DATE RATE
2023-01-01 2023-04-30 1.9%
2023-05-01 2023-09-30 2.0%
2023-10-01 2023-12-31 2.2%
2024-01-01 9999-12-31 1.8%
-- 存款记录表
NAME ST_D END_D AMT
张三 2023-04-15 2023-08-13 50000
张三 2023-09-10 2024-08-01 80000
LISI 2023-02-10 2023-07-08 30000
LISI 2023-09-20 2023-12-25 60000
LISI 2024-03-22 2024-10-08 70000
-- 50000*0.019*15/360+50000*0.02*104/360
--求每个客户的总利息
-- DROP TABLE RATE_T;
CREATE TABLE RATE_T(ST_DATE DATE,END_DATE DATE,RATE NUMBER);
INSERT INTO RATE_T VALUES (DATE'2023-01-01',DATE'2023-04-30',0.019);
INSERT INTO RATE_T VALUES (DATE'2023-05-01',DATE'2023-09-30',0.02);
INSERT INTO RATE_T VALUES (DATE'2023-10-01',DATE'2023-12-31',0.022);
INSERT INTO RATE_T VALUES (DATE'2024-01-01',DATE'9999-12-31',0.018);
--
CREATE TABLE CSR_INFO (ID VARCHAR2(10),NAME VARCHAR2(20),ST_D DATE,END_D DATE,AMT NUMBER);
INSERT INTO CSR_INFO VALUES ('S001','张三',DATE'2023-04-15',DATE'2023-08-13',50000);
INSERT INTO CSR_INFO VALUES ('S001','张三',DATE'2023-09-10',DATE'2024-08-01',80000);
INSERT INTO CSR_INFO VALUES ('S002','LISI',DATE'2023-02-10',DATE'2023-07-08',30000);
INSERT INTO CSR_INFO VALUES ('S002','LISI',DATE'2023-09-20',DATE'2023-12-25',60000);
INSERT INTO CSR_INFO VALUES ('S002','LISI',DATE'2024-03-22',DATE'2024-10-08',70000);
COMMIT;
SELECT * FROM RATE_T;
SELECT * FROM CSR_INFO;
SELECT C.NAME,ROUND(SUM(C.AMT*C.RATE*C.TS/360),2) LIXI FROM
(SELECT B.NAME,B.AMT,A.RATE,
CASE WHEN A.ST_DATE<B.ST_D AND B.END_D<A.END_DATE THEN B.END_D-B.ST_D -- 1
WHEN A.ST_DATE>B.ST_D AND B.END_D>A.END_DATE THEN A.END_DATE-A.ST_DATE -- 4
WHEN A.ST_DATE BETWEEN B.ST_D AND B.END_D THEN B.END_D-A.ST_DATE -- 2
WHEN B.ST_D BETWEEN A.ST_DATE AND A.END_DATE THEN A.END_DATE-B.ST_D -- 3
END TS
FROM RATE_T A,CSR_INFO B) C
GROUP BY C.NAME;
1.求1-100的所有数字之和 5050
DECLARE
V_SUM NUMBER:=0;
BEGIN
FOR S IN 1..100 LOOP
V_SUM:=V_SUM+S;
END LOOP;
DBMS_OUTPUT.put_line(V_SUM);
END;
2.假设现在有鸡和兔子，一共有35只，它们的脚一共有94个，
使用一个循环计算出鸡和兔子分别有多少只。 12 23
BEGIN
FOR J IN 0..35 LOOP
IF 2*J+4*(35-J)=94 THEN
DBMS_OUTPUT.put_line('鸡有'||J||',兔子有'||(35-J)||'只');
END IF;
END LOOP;
END;
3.百元买鸡 妈妈给小明100元 买100只鸡，已知
公鸡 5元一只 母鸡 3元一只 小鸡 一元3只
问有几种买法 （可以把每一种购买方式都打印出来）
--钱要花完，鸡也要买到
DECLARE
V_A NUMBER:=0; -- 统计买法
BEGIN
FOR G IN 0..20 LOOP
FOR M IN 0..33 LOOP
IF 5*G+3*M+(100-G-M)/3=100 THEN
DBMS_OUTPUT.put_line('公鸡有'||G||'只,母鸡有'||M||'只,小鸡有'||(100-G-M)||'只');
V_A:=V_A+1;
END IF;
END LOOP;
END LOOP;
DBMS_OUTPUT.put_line('有'||V_A||'种买法');
END;
4.纸厚度1mm，珠穆朗玛峰高度8848m，问纸要对折多少次，
高度才会超过山 24
DECLARE
V_A NUMBER:=0; -- 对折的次数
V_B NUMBER:=1; -- 纸的厚度
BEGIN
WHILE V_B <= 8848000 LOOP
V_A:=V_A+1;
V_B:=V_B*2; -- V_B:=1*POWER(2,V_A);
END LOOP;
DBMS_OUTPUT.put_line('对折'||V_A||'次');
END;
5.求任意两个数之间的偶数和(包含输入的数字)
DECLARE
V_A NUMBER:=&NO1;
V_B NUMBER:=&NO2;
V_SUM NUMBER:=0;
BEGIN
IF V_A>=V_B THEN
FOR S IN V_B..V_A LOOP
IF MOD(S,2)=0 THEN
V_SUM:=V_SUM+S;
END IF ;
END LOOP;
DBMS_OUTPUT.put_line(V_B||'和'||V_A||'之间的偶数和为'||V_SUM);
ELSE
FOR S IN V_A..V_B LOOP
IF MOD(S,2)=0 THEN
V_SUM:=V_SUM+S;
END IF ;
END LOOP;
DBMS_OUTPUT.put_line(V_A||'和'||V_B||'之间的偶数和为'||V_SUM);
END IF;
END;
6.请编写一个PL/SQL代码块，使用循环结构将一个字符型变量反转，
并输出反转后的字符串。 REVERSE
DECLARE
V_A VARCHAR2(100):='&CHAR';
V_B VARCHAR2(100);
BEGIN
FOR S IN 1..LENGTH(V_A) LOOP
V_B:=V_B||SUBSTR(V_A,-S,1);
END LOOP;
DBMS_OUTPUT.put_line(V_B);
END;
A H G A D G
1 2 3 4 5 6
-6 -5 -4 -3 -2 -1
7.斐波那契数列 求前30个
1 1 2 3 5 8 13 21
前两个数为1，从第3个数开始，每一个数等于前面两个数之和
DECLARE
V_A NUMBER:=1; -- 第一个数
V_B NUMBER:=1; -- 第二个数
V_C NUMBER:=0; -- 要求的第三个数
BEGIN
DBMS_OUTPUT.put_line('第1个数是:'||V_A);
DBMS_OUTPUT.put_line('第2个数是:'||V_B);
FOR S IN 3..50 LOOP
V_C:=V_A+V_B;
DBMS_OUTPUT.put_line('第'||S||'个数是:'||V_C);
V_A:=V_B;
V_B:=V_C;
END LOOP;
END;
8.使用循环，打印出下面的图案
*
**
***
****
*****
DECLARE
V_A NUMBER :=8;
BEGIN
FOR H IN 1..V_A LOOP -- 行的循环
FOR L IN 1..H LOOP -- 列的循环
DBMS_OUTPUT.put('*');
END LOOP;
DBMS_OUTPUT.new_line;
END LOOP;
END;
9.使用循环，打印出下面的图案
*********
*******
*****
***
*
DECLARE
V_A NUMBER :=20; -- 控制行数
BEGIN
FOR H IN 1..V_A LOOP -- 行的循环
FOR L IN 1..H-1 LOOP -- 列的循环 空格
DBMS_OUTPUT.put(' ');
END LOOP;
FOR L IN 1..2*(V_A-H)+1 LOOP -- 列的循环 *
DBMS_OUTPUT.put('*');
END LOOP;
DBMS_OUTPUT.new_line;
END LOOP;
END;
10.九九乘法表
BEGIN
FOR H IN 1..9 LOOP -- 行的循环
FOR L IN 1..H LOOP -- 列的循环
DBMS_OUTPUT.put(L||'*'||H||'='||L*H||' ');
IF L=2 AND L*H<10 THEN
DBMS_OUTPUT.put(' ');
END IF;
END LOOP;
DBMS_OUTPUT.new_line;
END LOOP;
END;
--------------------------------------------------------------------------------------------------
--游标 CURSOR 配合循环使用
游标是指向查询结果集的一个指针，通过游标可以将查询结果集中的记录逐一取出，并在PL/SQL程序块中进行处理。
游标的类型有两种：隐式游标和显式游标。隐式游标是由系统自动创建并管理的游标。
PL/SQL会为所有的sql数据操作声明一个隐式的游标，包括只返回一条记录的查询操作。
对于返回多条记录的查询，必须自己创建显式游标。
显式游标
语法结构：
DECLARE
游标的声明：
CURSOR 游标名[(参数1 数据类型,参数2 数据类型...)] C_NAME -- 约定的命名
IS SELECT 语句; -- 只能用 IS
BEGIN
游标的执行：
OPEN 游标名[(实际参数1,实际参数2...)]; --打开游标 游标才有数据
FETCH 游标名 INTO 变量1[,变量2,变量3...]; --查找数据并且赋值给变量 执行第几次就会读取第几行的数据
或者
FETCH 游标名 INTO 记录变量; --提取数据
CLOSE 游标名; --关闭游标
-- 输入一个部门编号，输出该部门里员工的姓名和工资
DECLARE
V_A NUMBER:=0; -- 记录读取的是第几行数据
V_B NUMBER; -- 获取游标里一共有多少行数据
CURSOR C_EMP IS SELECT * FROM EMP WHERE DEPTNO=&NO;
V_C EMP%ROWTYPE;
BEGIN
SELECT COUNT(*) INTO V_B FROM EMP WHERE DEPTNO=&NO;
OPEN C_EMP;
LOOP
FETCH C_EMP INTO V_C;
DBMS_OUTPUT.put_line('姓名是'||V_C.ENAME||',工资是'||V_C.SAL);
V_A:=V_A+1;
EXIT WHEN V_A=V_B;
END LOOP;
CLOSE C_EMP;
END;
-- 显示游标的属性
属性 用法 返回值类型 备注
%FOUND 游标名%FOUND 布尔类型 当在游标里找到数据返回true，找不到数据返回false
%NOTFOUND 游标名%NOTFOUND 布尔类型 当在游标里找不到数据返回true，找到数据返回false
%ISOPEN 游标名%ISOPEN 布尔类型 当游标打开时返回true，游标关闭了返回false
%ROWCOUNT 游标名%ROWCOUNT 数值 读取到第几行数据就返回数字几
-- 输入一个部门编号，输出该部门里员工的姓名和工资
-- LOOP
DECLARE
CURSOR C_EMP IS SELECT * FROM EMP WHERE DEPTNO=&NO;
V_C EMP%ROWTYPE;
BEGIN
OPEN C_EMP; -- 打开游标
LOOP
FETCH C_EMP INTO V_C; -- 查找数据
EXIT WHEN C_EMP%NOTFOUND; -- 当找不到数据时退出循环
DBMS_OUTPUT.put_line('姓名是'||V_C.ENAME||',工资是'||V_C.SAL);
DBMS_OUTPUT.put_line(C_EMP%ROWCOUNT);
END LOOP;
CLOSE C_EMP; -- 关闭游标
END;
-- WHILE
DECLARE
CURSOR C_EMP IS SELECT * FROM EMP WHERE DEPTNO=&NO;
V_C EMP%ROWTYPE;
BEGIN
OPEN C_EMP; -- 打开游标
FETCH C_EMP INTO V_C; -- 查找数据 1
WHILE C_EMP%FOUND LOOP
--FETCH C_EMP INTO V_C;
DBMS_OUTPUT.put_line('姓名是'||V_C.ENAME||',工资是'||V_C.SAL);
DBMS_OUTPUT.put_line(C_EMP%ROWCOUNT);
FETCH C_EMP INTO V_C;
END LOOP;
CLOSE C_EMP; -- 关闭游标
END;
-- FOR 自带遍历效果 在循环游标时，自带打开，赋值和关闭
DECLARE
CURSOR C_EMP IS SELECT * FROM EMP WHERE DEPTNO=&NO;
BEGIN
FOR S IN C_EMP LOOP
-- FOR X IN C_EMP LOOP -- 同一个游标不可以同时打开两次
DBMS_OUTPUT.put_line('姓名是'||S.ENAME||',工资是'||S.SAL);
END LOOP;
END;
BEGIN
FOR S IN (SELECT * FROM EMP WHERE DEPTNO=&NO) LOOP -- FOR循环自带遍历效果，可以直接接子查询
DBMS_OUTPUT.put_line('姓名是'||S.ENAME||',工资是'||S.SAL);
END LOOP;
END;
-- 注意事项：
1. 游标是在打开时获取结果集数据。
2. 游标打开之后不能再次打开。要先关闭游标，再打开游标。
3. 游标关闭之后重新打开会重新刷新游标。重新获取数据，重新从第一行读取数据。
-- 带参游标
-- 参数
1. 参数的定义： 参数名(P_NAME) 参数类型 数据类型
2. 参数类型:
IN -- 传入参数(入参) 一般用来当条件使用，不能被赋值。参数类型不写时 默认入参
OUT -- 传出参数(出参) 脚本需要向外传递值。可以被赋值
IN OUT -- 传入传出参数 既可以当作传入参数使用，也可以当作传出参数
3. 数据类型：数值，字符串，日期。参数定义时全部不能给长度
4. 参数定义了可以不使用，但是传入或者接受时需要给定值或者变量
（实际参数和定义参数的数量和数据类型要一一对应）
-- 输入一个部门编号，输出该部门里员工的姓名和工资
DECLARE
CURSOR C_EMP(P_NO IN NUMBER,P_JOB IN VARCHAR2) IS SELECT * FROM EMP WHERE DEPTNO=P_NO;
BEGIN
FOR S IN C_EMP(20,'A') LOOP -- 在打开游标时传入参数
DBMS_OUTPUT.put_line('姓名是'||S.ENAME||',工资是'||S.SAL);
END LOOP;
END;
对于EMP0106表中SALESMAN和MANAGER，
如果这个员工工资小于1000，就给他加300奖金
如果这个员工工资1000到2000，就给他加200奖金
如果这个员工工资大于2000，就给他加100奖金
输出员工编号，员工名字，员工工资，修改前的员工奖金，修改后的员工奖金
CREATE TABLE EMP0106 AS SELECT * FROM EMP;
DECLARE
CURSOR C_A IS SELECT * FROM EMP0106 WHERE JOB IN ('SALESMAN','MANAGER');
V_A EMP0106%ROWTYPE;
BEGIN
FOR S IN C_A LOOP
IF S.SAL<1000 THEN
V_A.COMM:=NVL(S.COMM,0)+300;
DBMS_OUTPUT.put_line(S.EMPNO||'/'||S.ENAME||'/'||S.SAL||'/'||S.COMM||'/'||V_A.COMM);
ELSIF S.SAL<=2000 THEN
V_A.COMM:=NVL(S.COMM,0)+200;
DBMS_OUTPUT.put_line(S.EMPNO||'/'||S.ENAME||'/'||S.SAL||'/'||S.COMM||'/'||V_A.COMM);
ELSE
V_A.COMM:=NVL(S.COMM,0)+100;
DBMS_OUTPUT.put_line(S.EMPNO||'/'||S.ENAME||'/'||S.SAL||'/'||S.COMM||'/'||V_A.COMM);
END IF;
END LOOP;
END;
------------------------------------------------------------------------------------------
隐式游标是由系统自动创建并管理的游标。
当执行DML语句或者返回结果为单行的查询语句就自动创建隐式游标，执行完就关闭。
只能获取最新一条能产生隐式游标的sql语句的隐式游标属性。
-- 隐式游标的属性
属性 用法 返回值类型 备注
%FOUND SQL%FOUND 布尔类型 当在游标里找到数据返回true，找不到数据返回false
%NOTFOUND SQL%NOTFOUND 布尔类型 当在游标里找不到数据返回true，找到数据返回false
%ISOPEN SQL%ISOPEN 布尔类型 当游标打开时返回true，游标关闭了返回false。永远为false。
%ROWCOUNT SQL%ROWCOUNT 数值 DML操作了多少条数据就返回数字几，查询语句返回1。
DECLARE
V_A NUMBER;
A VARCHAR2(10);
BEGIN
INSERT INTO EMP0106 SELECT * FROM EMP WHERE DEPTNO=10;
-- DELETE FROM EMP0106 WHERE DEPTNO=50;
UPDATE EMP0106 SET SAL=SAL+1000 WHERE DEPTNO=30;
-- SELECT SAL INTO V_A FROM EMP0106 WHERE EMPNO=7788;
IF SQL%FOUND THEN
DBMS_OUTPUT.put_line('找到了'||SQL%ROWCOUNT||'行数据');
ELSIF SQL%NOTFOUND THEN
DBMS_OUTPUT.put_line('没找到了数据'||SQL%ROWCOUNT);
END IF;
IF SQL%ISOPEN THEN
A:=1;
DBMS_OUTPUT.put_line('游标打开了');
ELSE
DBMS_OUTPUT.put_line('游标关闭了');
END IF;
END;
-----------------------------------------------------------------------------------------
-- 动态SQL语法：
EXECUTE IMMEDIATE 'SQL语句'
[INTO 变量/出参] -- 将结果赋值给变量/出参 一般用于SELECT语句
[USING 值/变量/入参] ; -- 把 值/变量/入参 传给sql当参数
-- INTO 和 USING 在使用是要用参数占位。 :P_NAME
1. DML,DDL,SELECT 语句在pl/sql里都可以使用动态sql执行。
2. DDL 语句不可以在pl/sql里直接执行，必须使用动态sql执行。
3. 动态SQL是将sql语句封装在字符串里执行，也可以将字符串赋值给变量，去执行变量。
4. 如果新建对象，用动态SQL创建，在同一段pl/sql代码里，所有与对象名相关的sql都得用动态SQL执行。
DDL 语句：CREATE DROP TRUNCATE ALTER
TRUNCATE TABLE EMP0106; -- 支持反复运行
CREATE TABLE EMP0106 AS SELECT * FROM EMP; -- 不支持反复运行
ALTER TABLE EMP0107 RENAME TO EMP0108; -- 不支持反复运行
DROP TABLE EMP0108 ; -- 不支持反复运行
-- 批量删除表
DECLARE
CURSOR C_DROP IS SELECT TABLE_NAME TN FROM USER_TABLES WHERE TABLE_NAME LIKE 'EMP_%';
V_A VARCHAR2(100);
BEGIN
FOR S IN C_DROP LOOP
/*V_A := 'DROP TABLE '||S.TN;
DBMS_OUTPUT.put_line(V_A);*/
EXECUTE IMMEDIATE 'DROP TABLE '||S.TN; -- 只能用拼接，不可以用 using
END LOOP;
END;
-- 批量建表 EMP_20250101 ~ EMP_20250131
DECLARE
V_A VARCHAR2(20); -- 获取后缀
BEGIN
FOR S IN 0..30 LOOP
V_A := TO_CHAR(DATE'2024-01-01'+S,'YYYYMMDD');
EXECUTE IMMEDIATE 'CREATE TABLE EMP_'||V_A||' AS SELECT * FROM EMP';
END LOOP;
END;
-- 批量改表名
DECLARE
CURSOR C_DROP IS SELECT TABLE_NAME TN FROM USER_TABLES WHERE TABLE_NAME LIKE 'EMP_%';
V_A VARCHAR2(100);
BEGIN
FOR S IN C_DROP LOOP
EXECUTE IMMEDIATE 'ALTER TABLE '||S.TN||' RENAME TO '||REPLACE(S.TN,'2024','2025');
END LOOP;
END;
-- SELECT 语句
1. 筛选条件字段的数据类型是数字
DECLARE
V_SAL1 NUMBER;
V_SAL2 NUMBER;
V_NAME VARCHAR2(20);
BEGIN
--EXECUTE IMMEDIATE 'SELECT SAL FROM EMP WHERE EMPNO=7788' INTO V_SAL;
EXECUTE IMMEDIATE 'SELECT SAL INTO :P_A FROM EMP WHERE EMPNO=7788' INTO V_SAL1;
DBMS_OUTPUT.put_line(V_SAL1);
--EXECUTE IMMEDIATE 'SELECT SAL FROM EMP WHERE ENAME='||''''||'SMITH'||'''' INTO V_SAL2;
--EXECUTE IMMEDIATE 'SELECT SAL FROM EMP WHERE ENAME='||CHR(39)||'SMITH'||CHR(39) INTO V_SAL2;
EXECUTE IMMEDIATE 'SELECT SAL,ENAME FROM EMP WHERE ENAME= :P_B AND JOB=:P_C'
INTO V_SAL2,V_NAME USING 'SMITH','CLERK';
DBMS_OUTPUT.put_line(V_SAL2||V_NAME);
END;
--创建一个表 一个字段 数据类型是number -- 建表语句写在pl/sql外面
--用循环往表里插入1-200
--从表里找出两个不同数字，相加之和288 ，打印出所有的组合 (144/143+145 or 145+143)
--找出是7的倍数的所有数字,打印出所有的数字
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE AAAAAAAA (A NUMBER)';
EXECUTE IMMEDIATE 'INSERT INTO AAAAAAAA VALUES(1)';
END;
--创建一个表 一个字段 数据类型是Aber -- 建表语句写在pl/sql外面
--用循环往表里插入1-200
--从表里找出两个不同数字，相加之和288 ，打印出所有的组合 (144/143+145 or 145+143)
--找出是7的倍数的所有数字,打印出所有的数字
creaTE TABLE NUM_A (A ABER);
SELECT * FROM NUM_A;
DECLARE
CURSOR C_A IS SELECT * FROM NUM_A;
CURSOR C_B IS SELECT * FROM NUM_A;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE A_A';
FOR S IN 1..200 LOOP
INSERT INTO A_A VALUES(S);
END LOOP;
COMMIT;
FOR X IN C_A LOOP
FOR Y IN C_B LOOP
IF X.A+Y.A=288 AND X.A<Y.A THEN
DBMS_OUTPUT.put_line(X.A||'+'||Y.A);
END IF ;
END LOOP;
END LOOP;
FOR Z IN C_A LOOP
IF MOD(Z.A,7)=0 THEN
DBMS_OUTPUT.put_line('7的倍数为'||Z.A);
END IF;
END LOOP;
END;
DECLARE
cursor c_i is select * from NUM_A;
v_A1 NUMber;
v_A2 NUMber;
BEGIN
DBMS_OUTPUT.PUT('7的倍数: ');
FOR i IN c_i LOOP
IF MOD(i.A, 7) = 0 THEN
DBMS_OUTPUT.PUT( i.A||' ');
END IF;
END LOOP;
DBMS_OUTPUT.new_line;
-- 查找两个数字之和为 288 的组合
DBMS_OUTPUT.PUT_LINE('可能的组合: ');
FOR i IN c_i LOOP
v_A1:= i.A;
FOR j IN (select * from NUM_A where A>i.A) LOOP
-- 避免重复组合（i.A < j.A）
IF i.A + j.A = 288 THEN
DBMS_OUTPUT.PUT( i.A || '+' || j.A ||' ');
END IF;
END LOOP;
END LOOP;
DBMS_OUTPUT.new_line;
END;
-------------------------------------------------------------------------------------------------
-- 存储过程 （存过）
-- 存储过程是一段命名的pl/sql代码，包括名称和参数 一般是以 SP_ 开头
-- 创建存储过程
CREATE [OR REPLACE] PROCEDURE SP_NAME [(定义参数)] IS -- AS
-- 声明部分 定义变量，常量，游标，自定义异常等等
BEGIN
-- 逻辑执行部分
EXCEPTION
-- 异常处理
END;
-- 调用存储过程
1.
CALL SP_NAME(); -- ()不能省略。call只支持没有参数或者只有入参的存储过程。
2. 在pl/sql代码块里调用，所有的存储过程都可以用
DECLARE
-- 声明部分
BEGIN
SP_NAME; -- 没有参数
SP_NAME(实际参数); -- 有参数
END;
-- 注意事项：
1. 第一次创建存储过程时，不要加 OR REPLACE ，等存储过程创建成功之后再加上。
2. 加 OR REPLACE 是替换，创建或替换存储过程是不会报错的
3. 创建或者替换存储过程时，逻辑部分代码没有执行，只是命名。
4. 当对象上有红色的×，说明存储过程无效。检查语法或者逻辑上的错误，不能检查数据上的错误。
5. 怎么修改存储过程，选中要修改的存储过程，右键，选择编辑。
6. 实际参数与定义参数的数量和数据类型要一一对应
-- 输入部门编号，输出部门里员工的姓名和工资
CREATE OR REPLACE PROCEDURE SP_DEPT(P_DEPT NUMBER,P_NAME OUT VARCHAR2,P_SAL OUT NUMBER) IS
CURSOR C_EMP IS SELECT ENAME,SAL FROM EMP WHERE DEPTNO=P_DEPT;
V_A NUMNER;
BEGIN
FOR S IN C_EMP LOOP
P_NAME:=S.ENAME;
P_SAL:=S.SAL;
SELECT SAL INTO V_A FROM EMP WHERE EMPNO=7788;
-- FETCH C_EMP INTO P_NAME,V_A;
V_A:=1;
V_A:=V_A+1;
DBMS_OUTPUT.put_line('姓名'||SUBSTR(P_NAME,1,3)||',工资'||(P_SAL+1000));
END LOOP;
END;
-- 在 PL/SQL 代码调用
DECLARE
V_NAME VARCHAR2(20);
V_SAL NUMBER;
BEGIN
--SP_DEPT(P_DEPT => 20,P_NAME => V_NAME,P_SAL => V_SAL);
SP_DEPT(30,V_NAME,V_SAL);
-- V_NAME := SUBSTR(V_NAME,1,2);
END;
-- 参数引用数据类型 %TYPE %ROWTYPE
-- 输入部门编号，输出部门里员工的姓名和工资
CREATE OR REPLACE PROCEDURE SP_DEPT(P_B IN EMP%ROWTYPE,P_A OUT EMP%ROWTYPE) IS
CURSOR C_EMP IS SELECT ENAME,SAL FROM EMP WHERE DEPTNO=P_B.DEPTNO;
V_A NUMBER;
BEGIN
FOR S IN C_EMP LOOP
P_A.ENAME:=S.ENAME;
P_A.SAL:=S.SAL;
DBMS_OUTPUT.put_line('姓名'||P_A.ENAME||',工资'||P_A.SAL);
END LOOP;
END;
-- 在 PL/SQL 代码调用
DECLARE
V_A EMP%ROWTYPE;
V_B EMP%ROWTYPE;
BEGIN
V_B.DEPTNO:=30;
SP_DEPT(V_B,V_A);
END;
-- 输入部门编号，输出部门里员工的姓名和工资
CREATE OR REPLACE PROCEDURE SP_DEPT1(P_A IN OUT EMP%ROWTYPE) IS
CURSOR C_EMP IS SELECT ENAME,SAL FROM EMP WHERE DEPTNO=P_A.DEPTNO;
BEGIN
FOR S IN C_EMP LOOP
P_A.ENAME:=S.ENAME;
P_A.SAL:=S.SAL;
DBMS_OUTPUT.put_line('姓名'||P_A.ENAME||',工资'||P_A.SAL);
END LOOP;
END;
-- 在 PL/SQL 代码调用
DECLARE
V_A EMP%ROWTYPE;
BEGIN
V_A.DEPTNO:=30;
SP_DEPT1(V_A);
END;
CREATE TABLE EMP0107 AS SELECT * FROM EMP;
/*
小练习：
创建一个存储过程，输入参数为员工编号，
输出参数为输入员工所在部门名称和该部门平均工资，
要求在处理过程中使用EMP0107员工表：
1.若输入员工在ACCOUNTING部门，该员工职位为MANAGER，
则该部门全员涨薪1000，该员工为其他职位，则该部门全员涨薪800；
2.若输入员工在SALES部门，该员工职位为MANAGER，则该部门全员涨薪800，
该员工为其他职位，则该部门全员涨薪600；
3.若输入员工在其他部门，不管该员工什么职位，所在部门全员涨薪500；
4.将上述涨薪操作更新EMP0107表；
5.平均工资为涨薪后平均工资。
*/
CREATE OR REPLACE PROCEDURE SP_A (P_EMPNO NUMBER,P_DNAME OUT VARCHAR2,P_AS OUT NUMBER) AS
V_DEPTNO NUMBER;
V_JOB VARCHAR2(20);
BEGIN
SELECT DEPTNO,JOB INTO V_DEPTNO,V_JOB FROM EMP0107 WHERE EMPNO=P_EMPNO;
SELECT DNAME INTO P_DNAME FROM DEPT WHERE DEPTNO=V_DEPTNO;
IF P_DNAME='ACCOUNTING' THEN
IF V_JOB='MANAGER' THEN
UPDATE EMP0107 SET SAL=SAL+1000 WHERE DEPTNO=V_DEPTNO;
ELSE
UPDATE EMP0107 SET SAL=SAL+800 WHERE DEPTNO=V_DEPTNO;
END IF;
ELSIF P_DNAME='SALES' THEN
IF V_JOB='MANAGER' THEN
UPDATE EMP0107 SET SAL=SAL+800 WHERE DEPTNO=V_DEPTNO;
ELSE
UPDATE EMP0107 SET SAL=SAL+600 WHERE DEPTNO=V_DEPTNO;
END IF;
ELSE
UPDATE EMP0107 SET SAL=SAL+500 WHERE DEPTNO=V_DEPTNO;
END IF;
-- COMMIT;
SELECT ROUND(AVG(SAL),2) INTO P_AS FROM EMP0107 WHERE DEPTNO=V_DEPTNO;
DBMS_OUTPUT.put_line('部门名称是'||P_DNAME||',平均工资是'||P_AS);
end;
DECLARE
V_DNAME VARCHAR2(10);
V_AS NUMBER;
BEGIN
SP_A(7788,V_DNAME,V_AS);
END;
---------------------------------------------------------------------------------------
-- 分批次提交
CREATE TABLE COM_1 (ID NUMBER,STR VARCHAR2(20));
SELECT * FROM COM_1;
DECLARE
V_A NUMBER;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE COM_1';
FOR S IN 1..300000 LOOP
INSERT INTO COM_1 VALUES (S,TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));
IF MOD(S,10000)=0 THEN
COMMIT; -- 每一万条提交一次
END IF;
END LOOP;
COMMIT; -- 最后一次多余提交
END;
-- 分批次提交 -- 简单
CREATE TABLE DWD_COM AS SELECT * FROM COM_1 WHERE 1=2;
SELECT * FROM DWD_COM ;
SELECT * FROM DWD_COM1 ;
-- 写存储过程实现分批次提交
CREATE OR REPLACE PROCEDURE SP_COM(P_A NUMBER) IS -- 控制每次提交的数据条数
V_A NUMBER:=0; -- 已经插入多少行数据
V_B NUMBER:=0;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE DWD_COM';
LOOP
V_B:=V_B+1;
INSERT INTO DWD_COM
SELECT B.ID,B.STR FROM
(SELECT A.ID,A.STR,ROWNUM RN FROM COM_1 A) B
WHERE RN>V_A AND RN<=V_A+P_A; -- >0 <=20000 >20000 <=40000
-- EXIT WHEN SQL%NOTFOUND; -- 一定会空插一次
EXIT WHEN SQL%ROWCOUNT<P_A; -- 当数据总条数能整除每次插入的数据条数时，会空插一次
COMMIT; -- 提交会导致隐式游标的属性消失
V_A:=V_A+P_A;
END LOOP;
COMMIT;
DBMS_OUTPUT.put_line(V_B);
END;
(X,Y,Z)
-- 分批次提交 -- 高阶用法
-- 用于DML语句的循环 游标 + BULK COLLECT FORALL
DROP TABLE DWD_COM1;
CREATE TABLE DWD_COM1 AS SELECT * FROM COM_1 WHERE 1=2;
SELECT * FROM DWD_COM1 ;
CREATE OR REPLACE PROCEDURE SP_COM1(P_A NUMBER) IS -- 控制每次提交的数据条数
TYPE T_DATA1 IS TABLE OF COM_1.ID%TYPE; -- 自定义数据类型 (嵌套表) (1,2,3...)
TYPE T_DATA2 IS TABLE OF COM_1.STR%TYPE; -- 自定义数据类型 (嵌套表) ('A','B','C'...)
V_T_DATA1 T_DATA1; -- 定义变量
V_T_DATA2 T_DATA2; -- 定义变量
CURSOR C_COM IS SELECT * FROM COM_1;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE DWD_COM';
OPEN C_COM; -- 打开游标
LOOP
FETCH C_COM BULK COLLECT INTO V_T_DATA1,V_T_DATA2 LIMIT P_A; -- 查找数据并赋值
-- BULK COLLECT 将列的数据变成嵌套表格式
EXIT WHEN C_COM%NOTFOUND;
FORALL S IN 1..V_T_DATA1.COUNT
INSERT INTO DWD_COM1 VALUES (V_T_DATA1(S),V_T_DATA2(S));
COMMIT;
END LOOP;
CLOSE C_COM;
END;
CALL SP_COM(1000); -- 13.653秒
CALL SP_COM1(1000); -- 0.288 秒
ID STR
504 2025-01-07 14:47:12
505 2025-01-07 14:47:12
506 2025-01-07 14:47:12
507 2025-01-07 14:47:12
508 2025-01-07 14:47:12
509 2025-01-07 14:47:12
510 2025-01-07 14:47:12
511 2025-01-07 14:47:12
512 2025-01-07 14:47:12
513 2025-01-07 14:47:12
514 2025-01-07 14:47:12
515 2025-01-07 14:47:12
516 2025-01-07 14:47:12
517 2025-01-07 14:47:12
518 2025-01-07 14:47:12
519 2025-01-07 14:47:12
520 2025-01-07 14:47:12
521 2025-01-07 14:47:12
522 2025-01-07 14:47:12
(504,505,506,....,521,522)(2)=505
('2025-01-07 14:47:12','2025-01-07 14:47:12',.....,'2025-01-07 14:47:12')
substr('agahfaeh',5,1)='f'
(504,505,506,....,521,522) -- 嵌套表 数组 元组 [0]
[1,2,3] -- 列表 [0]
{KEY:VALUE,KEY:VALUE} -- 字典 [KEY] .get(KEY)
------------------------------------------------------------------------------------------
-- 日志表 -- 监控pl/sql代码块的运行状态
1. 创建日志表
CREATE TABLE MY_LOG(
XH NUMBER, -- 自增序号
NAME VARCHAR2(100), -- 监控的程序的名称
ST_DATE DATE, -- 程序开始运行时间
END_DATE DATE, -- 程序结束运行时间
STATUS VARCHAR2(10), -- '成功'或者'失败'
ERRM VARCHAR2(500)
);
2. 创建序列 SEQUENCE
-- DROP SEQUENCE XL; -- 删除序列
CREATE SEQUENCE XL;
[START WITH n] -- 以n开始，默认为 1
[INCREMENT BY n] -- 步长，默认为 1。n为正数就是自增，n为负数就是自减
[MAXVALUE n | NOMAXVALUE] -- 最大值 | 没有最大值 。 默认最大值为 999
[MINVALUE n | NOMINVALUE] -- 最小值 | 没有最小值 。 默认最小值为 1
[CYCLE | NOCYCLE] -- 循环 | 不循环 。 默认不循环
[CACHE n | NOCACHE] -- 缓存 | 不缓存 。 默认缓存 20
-- 怎么使用序列 序列的属性
SELECT XL.NEXTVAL FROM DUAL; -- 获取下一个序号
SELECT XL.CURRVAL FROM DUAL; -- 获取当前序号
3. 创建日志表插入数据的存储过程
CREATE OR REPLACE PROCEDURE SP_LOG(P_1 NUMBER,P_2 VARCHAR2,P_3 DATE,P_4 DATE,P_5 VARCHAR2,P_6 VARCHAR2)
IS
BEGIN
INSERT INTO MY_LOG VALUES (P_1,P_2,P_3,P_4,P_5,P_6);
COMMIT;
END;
4. 创建存储过程模板
CREATE OR REPLACE PROCEDURE SP_DEMO(参数) IS
/*
*/
-- 声明日志表的变量
V_NAME VARCHAR2(100):= 'SP_DEMO'; -- 要改
V_ST_DATE DATE;
V_END_DATE DATE;
V_ERRM VARCHAR2(500);
-- 声明存储过程本身的变量，游标等等
BEGIN
V_ST_DATE:=SYSDATE;
---------------------------------------------------------------------
-- 逻辑执行代码
---------------------------------------------------------------------
V_END_DATE:=SYSDATE;
SP_LOG(XL.NEXTVAL,V_NAME,V_ST_DATE,V_END_DATE,'成功',V_ERRM);
EXCEPTION WHEN OTHERS THEN -- OTHERS 只要没有正常运行都叫OTHERS
V_END_DATE:=SYSDATE;
V_ERRM:=SUBSTRB(SQLERRM,1,500);
SP_LOG(XL.NEXTVAL,V_NAME,V_ST_DATE,V_END_DATE,'失败',V_ERRM);
END;
0108
CREATE OR REPLACE PROCEDURE SP_COM2(P_A NUMBER) IS
/*
程序名称：SP_COM2
程序功能：往DWD_COM表里插入数据，实现分批次提交
输入参数：P_A NUMBER ： 控制分批次提交的条数
输出参数：
返 回 值：
目 标 表：DWD_COM
来 源 表：COM_1
创 建 人：张三
创建时间：2025-01-08 09：35
修 改 人：
修改时间：
修改原因：
*/
-- 声明日志表的变量
V_NAME VARCHAR2(100):= 'SP_COM2'; -- 要改
V_ST_DATE DATE;
V_END_DATE DATE;
V_ERRM VARCHAR2(500);
-- 声明存储过程本身的变量，游标等等
TYPE T_DATA1 IS TABLE OF COM_1.ID%TYPE; -- 自定义数据类型 (嵌套表) (1,2,3...)
TYPE T_DATA2 IS TABLE OF COM_1.STR%TYPE; -- 自定义数据类型 (嵌套表) ('A','B','C'...)
V_T_DATA1 T_DATA1; -- 定义变量
V_T_DATA2 T_DATA2; -- 定义变量
CURSOR C_COM IS SELECT * FROM COM_1;
BEGIN
V_ST_DATE:=SYSDATE;
---------------------------------------------------------------------
EXECUTE IMMEDIATE 'TRUNCATE TABLE DWD_COM';
OPEN C_COM; -- 打开游标
LOOP
FETCH C_COM BULK COLLECT INTO V_T_DATA1,V_T_DATA2 LIMIT P_A; -- 查找数据并赋值
-- BULK COLLECT 将列的数据变成嵌套表格式
EXIT WHEN C_COM%NOTFOUND;
FORALL S IN 1..V_T_DATA1.COUNT
INSERT INTO DWD_COM1 VALUES (V_T_DATA1(S),V_T_DATA2(S));
COMMIT;
END LOOP;
CLOSE C_COM;
---------------------------------------------------------------------
V_END_DATE:=SYSDATE;
SP_LOG(XL.NEXTVAL,V_NAME,V_ST_DATE,V_END_DATE,'成功',V_ERRM);
EXCEPTION WHEN OTHERS THEN -- OTHERS 只要没有正常运行都叫OTHERS
V_END_DATE:=SYSDATE;
V_ERRM:=SUBSTRB(SQLERRM,1,500);
SP_LOG(XL.NEXTVAL,V_NAME,V_ST_DATE,V_END_DATE,'失败',V_ERRM);
END SP_COM2;
--------------------------------------------------------------------------------------
-- 异常和自定义异常
异常是有异常名，并且携带报错信息。可能用异常名触发异常处理，也可以用 OTHERS 去触发异常处理。
报错只有报错信息。只能用 OTHERS 去触发异常处理。
自定义异常：满足一定的条件触发异常，一旦异常被触发一定要做异常处理。
自定义报错：如果不做处理会报错。
DECLARE
V_A NUMBER;
BEGIN
-- SELECT SAL INTO V_A FROM EMP WHERE EMPNO=9999; -- NO_DATA_FOUND
SELECT SAL INTO V_A FROM EMP WHERE DEPTNO=10; -- TOO_MANY_ROWS
DBMS_OUTPUT.put_line(V_A);
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line(SQLERRM);
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.put_line(SQLERRM);
END;
为什么要做异常处理：
整个程序对外界的状态是运行成功的，只是里面逻辑执行部分没有运行。
存储过程的执行都是使用调度任务调度的。调度任务之间会有依赖关系。
CREATE TABLE EMP0108 AS SELECT * FROM EMP;
-- 输入员工编号，删除该员工信息，但是不能删除'KING'
-- 自定义异常
DECLARE
V_EMPNO NUMBER:=&NO;
V_NAME VARCHAR2(10);
ABC EXCEPTION; -- 自定义异常
BEGIN
SELECT ENAME INTO V_NAME FROM EMP0108 WHERE EMPNO=V_EMPNO;
IF V_NAME = 'KING' THEN
RAISE ABC; -- 满足条件触发异常
END IF;
DELETE FROM EMP0108 WHERE EMPNO=V_EMPNO;
EXCEPTION WHEN ABC THEN
DBMS_OUTPUT.put_line('不能删除老板');
END;
-- 自定义报错
ORA -01422 : 实际返回的行数超出请求的行数
ORACLE的缩写 报错编码 冒号 报错内容
自定义报错需要定义报错编码和报错内容：
报错编码：-20000 ~ -20999 数字
报错内容：自己写，字符串
DECLARE
V_EMPNO NUMBER:=&NO;
V_NAME VARCHAR2(10);
BEGIN
SELECT ENAME INTO V_NAME FROM EMP0108 WHERE EMPNO=V_EMPNO;
IF V_NAME = 'KING' THEN
RAISE_APPLICATION_ERROR(-20888,'不能删除老板'); -- 满足条件触发自定义报错
END IF;
DELETE FROM EMP0108 WHERE EMPNO=V_EMPNO;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SQLERRM);
END;
-----------------------------------------------------------------------------------------------
-- 自定义函数 FUNCTION
-- 自定义函数是一段命名的pl/sql代码，包括名称和参数 一般是以 FUN_ 开头，自定义函数要有返回值。
-- 创建自定义函数
CREATE [OR REPLACE] FUNCTION FUN_NAME (定义参数) RETURN 数据类型(不给长度) IS -- AS
-- 声明部分 定义变量，常量，游标，自定义异常等等
BEGIN
-- 逻辑执行部分
RETURN 值/算术运算式/函数处理的结果/变量
EXCEPTION
-- 异常处理
END;
-- 输入两个数字，返回乘积
-- 创建函数
CREATE OR REPLACE FUNCTION FUN_CJ1(P_A NUMBER,P_B NUMBER) RETURN NUMBER IS
V_A NUMBER;
BEGIN
--RETURN P_A*P_B;
V_A := P_A*P_B;
DBMS_OUTPUT.put_line('A');
RETURN ROUND(V_A,2);
DBMS_OUTPUT.put_line('B');
END;
-- 自定义函数的注意事项：
RETURN ：PL/SQL 代码块里，运行 RETURN 之后代码直接结束运行
EXIT ：退出某个循环，只能退一层
CONTINUE ：退出某个循环的某一次循环
-- 调用
1. SQL 语句里调用 --(如果函数里包含DML操作就不能在select语句里调用)
SELECT FUN_CJ(10.154544,20.45) FROM DUAL;
SELECT FUN_CJ(EMPNO,SAL) FROM EMP;
2. pl/sql 代码块
DECLARE
V_A NUMBER;
BEGIN
V_A := FUN_CJ(10,20);
SELECT FUN_CJ(EMPNO,SAL) INTO V_A FROM EMP WHERE EMPNO=7788;
END;
-- 补充函数
GREATEST(X,Y,Z....) -- 求所有参数的最大值 横向作比较行数据，行函数
LEAST(X,Y,Z....) -- 求所有参数的最小值 横向作比较行数据，行函数
-- 比较大小的方式以第一个参数的数据类型为准，结果也是按第一个参数的数据类型返回
MAX() -- 求最大值 纵向比较列数据，聚合函数
MIN() -- 求最小值 纵向比较列数据，聚合函数
SELECT GREATEST(EMPNO,SAL,DEPTNO) FROM EMP;
SELECT GREATEST(200,50,80,300) FROM DUAL; -- 300
SELECT GREATEST(200,50,'80',300) FROM DUAL; -- 300
SELECT GREATEST('200',50,80,300) FROM DUAL; -- '80'
NVL(字段,为空的返回值) -- 对字段里的数据进行判断，为空的数据给返回值，不为空的数据返回本身
NVL2(字段,不为空的返回值1,为空的返回值2) -- 对字段里的数据进行判断，不为空的数据给返回值1，为空的数据给返回值2
-- 返回值的数据类型要和字段的数据类型一致
SELECT COMM,NVL(COMM,20),NVL2(COMM,COMM,20) FROM EMP;
--写 拼接三个字符串的自定义函数
create or replace function FUN_CON(P_A VARCHAR2,P_B VARCHAR2,P_C VARCHAR2) return varchar2 is
begin
return P_A||P_B||P_C;
end FUN_CON;
SELECT FUN_CON('A','B','C') FROM DUAL;
--写 返回三个数字最大值的函数
create or replace function FUN_MAX(P_A NUMBER,P_B NUMBER,P_C NUMBER) return NUMBER is
begin
IF P_A>=P_B AND P_A>=P_C THEN
RETURN P_A;
ELSIF P_B>=P_A AND P_B>=P_C THEN
RETURN P_B;
ELSE
RETURN P_C;
END IF;
end;
SELECT FUN_MAX(25,25,20) FROM DUAL;
--输入一个员工编号，如果是属于10部门，该员工明年工资涨10%，如果是属于20部门，该员工明年工资涨15%，
--如果是属于30部门，该员工明年工资涨20%
--写一个函数，输入一个员工编号，返回涨之后的工资
create or replace function FUN_SAL(P_A NUMBER) return NUMBER is
V_DEPT NUMBER;
V_SAL NUMBER;
begin
SELECT DEPTNO,SAL INTO V_DEPT,V_SAL FROM EMP WHERE EMPNO=P_A;
IF V_DEPT=10 THEN
RETURN V_SAL*1.1;
ELSIF V_DEPT=20 THEN
RETURN V_SAL*1.15;
ELSE
RETURN V_SAL*1.2;
END IF;
end;
SELECT FUN_SAL(7788) FROM DUAL;
SELECT EMP.*,FUN_SAL(EMPNO) NEW_SAL FROM EMP;
--写个函数，输入一个字符串，返回字符串中的数字
create or replace function FUN_NUM(P_A VARCHAR2) return NUMBER is
V_A VARCHAR2(100);
begin
-- V_A := REGEXP_REPLACE(P_A,'\D+');
FOR S IN 1..LENGTH(P_A) LOOP
IF ASCII(SUBSTR(P_A,S,1)) BETWEEN ASCII(0) AND ASCII('9') THEN
V_A:=V_A||SUBSTR(P_A,S,1);
END IF;
END LOOP;
RETURN V_A;
end;
SELECT FUN_NUM('0AYU4URQTJET899WER8TEUG') FROM DUAL;
--写个函数，输入一个员工编号，删除 emp0108 表该员工信息，删除成功返回1，删除失败返回0
create or replace function FUN_DEL(P_A NUMBER) return NUMBER is
begin
DELETE FROM emp0108 WHERE EMPNO=P_A;
IF SQL%NOTFOUND THEN
RETURN 0;
ELSIF SQL%FOUND THEN
RETURN 1;
END IF;
-- COMMIT;
end;
SELECT FUN_DEL(7788) FROM DUAL;
DELETE FROM emp0107 WHERE EMPNO=FUN_DEL(7788);
-- DML语句里可以运行包含DML语句的函数，两个DML语句操作的表不能是同一张表。
DECLARE
V_A NUMBER;
BEGIN
V_A:=FUN_DEL(8888);
DBMS_OUTPUT.put_line(V_A);
END;
-- 包
包就是把相关的存储过程、函数、变量、常量和游标等PL/SQL程序组合在一起，并赋予一定的管理功能的程序块。
一个程序包由两部分组成：包定义和包体。其中包定义部分声明包内
数据类型、变量、常量、游标、存储过程和函数等元素，这些元素为包的共有元素，
包体则定义了包定义部分的具体实现。
创建包定义：
CREATE [OR REPLACE] PACKAGE 包名
IS/AS
[公有数据类型定义]
[公有游标声明]
[公有变量、常量声明]
[公有子程序声明]
END;
创建包体:
CREATE [OR REPLACE] PACKAGE BODY 包名
IS/AS
PL/SQL语句;
[私有数据类型定义]
[私有游标声明]
[私有变量、常量声明]
[私有子程序声明]
END;
-- 包定义和包体里定义的名称不能重复
-- EMP 表里有8个变量，不想使用8个变量，只想ename，sal，deptno
DECLARE
TYPE BBB IS TABLE OF NUMBER; -- 构造一个嵌套表数据类型
TYPE AAA IS RECORD(EN EMP.ENAME%TYPE,SAL NUMBER(7,2),DEPTNO NUMBER(2)); -- 构造一个记录变量数据类型
V_B EMP%ROWTYPE; -- 记录变量 8个变量
V_A AAA; -- 记录变量 3个变量
BEGIN
V_A.EN:='ABADG';
DBMS_OUTPUT.put_line(V_A.EN);
END;
-- 创建一个包，里面包含一个存储过程和两个函数
1. 存储过程：输入部门编号，输出部门里员工的姓名和工资
2. 函数：输入部门编号，输出每个员工增长后的工资
3. 函数：模拟substr函数的作用
-- 创建包定义
CREATE OR REPLACE PACKAGE BAO1 IS -- AS
CURSOR C_EMP(P_DEPTNO NUMBER,P_R NUMBER) IS SELECT ENAME,SAL*P_R AS SAL FROM EMP WHERE DEPTNO=P_DEPTNO;
-- 共有游标
TYPE ENAME_1 IS TABLE OF VARCHAR2(10);
TYPE SAL_1 IS TABLE OF NUMBER;
V_3 VARCHAR2(1000);
PROCEDURE SP_SS(P_A NUMBER) ;
FUNCTION FUN_SAL(P_B NUMBER,P_C NUMBER) RETURN SAL_1;
FUNCTION FUN_SUB(P_1 VARCHAR2,P_2 NUMBER DEFAULT 1 ,P_3 NUMBER DEFAULT LENGTH(V_3)) RETURN VARCHAR2;
END;
-- 创建包体
CREATE OR REPLACE PACKAGE BODY BAO1 IS -- AS
PROCEDURE SP_SS(P_A NUMBER) IS
-- 私有声明部分
BEGIN
FOR X IN C_EMP(P_A,1) LOOP
DBMS_OUTPUT.put_line(RPAD(X.ENAME,10,' ')||X.SAL);
END LOOP;
END SP_SS;
FUNCTION FUN_SAL(P_B NUMBER,P_C NUMBER) RETURN SAL_1 IS
-- 私有声明部分
V_A SAL_1; -- 私有变量
V_B ENAME_1;
BEGIN
OPEN C_EMP(P_B,P_C);
LOOP
FETCH C_EMP BULK COLLECT INTO V_B,V_A ;
EXIT WHEN C_EMP%NOTFOUND;
END LOOP;
CLOSE C_EMP;
RETURN V_A;
END FUN_SAL;
FUNCTION FUN_SUB(P_1 VARCHAR2,P_2 NUMBER DEFAULT 1 ,P_3 NUMBER DEFAULT LENGTH(V_3)) RETURN VARCHAR2 IS
-- 私有声明部分
V_2 VARCHAR2(1000);
BEGIN
V_3:=P_1;
FOR X IN P_2..P_2+P_3-1 LOOP -- 2..4
V_2:=V_2||SUBSTR(P_1,X,1);
END LOOP;
RETURN V_2;
END FUN_SUB;
END;
-- 调用：
1. 存储过程调用：
CALL BAO1.SP_SS(30);
2. 函数调用：
SELECT BAO1.FUN_SUB(ENAME,2) FROM EMP;
-- 调用第二个函数
declare
V_A BAO1.SAL_1; -- 引用包里的数据类型
BEGIN
V_A := BAO1.FUN_SAL(20,1.15); -- 赋值
FOR I IN 1..V_A.COUNT LOOP
DBMS_OUTPUT.put_line(V_A(I)); -- 循环读取每一个值，并打印
END LOOP;
END;
/*
创建一个包，里面一个存储过程一个函数。
存储过程：在dept0109表新增一个部门的编号、名称、地点，
如果这个编号和名字都没有重复，就插入数据，否则就提示该部门已存在
函数：传入一个年份，返回他的属相，已知1900年生肖是鼠
鼠,牛,虎,兔,龙,蛇,马,羊,猴,鸡,狗,猪
*/
CREATE TABLE dept0109 AS SELECT * FROM DEPT;
-- 包定义
CREATE OR REPLACE PACKAGE B1 IS
PROCEDURE SP_INT(P1 NUMBER,P2 VARCHAR2,P3 VARCHAR2) ;
FUNCTION FUN_YEAR (PA NUMBER) RETURN VARCHAR2;
END;
-- 包体
CREATE OR REPLACE PACKAGE BODY B1 IS
PROCEDURE SP_INT(P1 NUMBER,P2 VARCHAR2,P3 VARCHAR2) IS
V_A NUMBER;
BEGIN
SELECT COUNT(*) INTO V_A FROM dept0109 WHERE DEPTNO=P1 OR DNAME=P2;
IF V_A = 0 THEN
INSERT INTO DEPT0109 VALUES (P1,P2,P3);
--COMMIT;
ELSE
RAISE_APPLICATION_ERROR(-20666,'该部门已存在');
END IF;
END SP_INT;
FUNCTION FUN_YEAR (PA NUMBER) RETURN VARCHAR2 IS
V_B NUMBER;
BEGIN
-- V_B := MOD(MOD(PA-1900,12)+12,12);
V_B := MOD(PA-1900,12);
IF V_B<0 THEN
V_B:=V_B+12;
END IF;
RETURN SUBSTR('鼠牛虎兔龙蛇马羊猴鸡狗猪',V_B+1,1);
END FUN_YEAR;
END;
-- 调用
CALL B1.SP_INT(50,'ADGAG','AHADFH');
SELECT EMP.*,B1.FUN_YEAR(TO_CHAR(HIREDATE,'YYYY')) SX FROM EMP;
-------------------------------------------------------------------------------------------------------
--拉链表 记录历史变化数据的表
-- 在数仓开发中，拉链表一般是做成维度表，缓慢变化维。
-- 缓慢变化：每次发生变化的数据量占总数据量的比例很小
-- 常用于做成拉链表：客户信息表/员工信息表。地区表
-- 客户信息表(业务系统)
ID NAME TEL JOB ADDR -- 2024-04-01
001 张三 12222222222 大数据 凤和城
002 李四 13333333333 网安 凤和城
-------------------------------------------------------------
ID NAME TEL JOB ADDR -- 2024-06-01
001 张三 18888888888 大数据 凤和城
002 李四 13333333333 网安 凤和城
003 王五 15555555555 销售 人和
--------------------------------------------------------------------------
ID NAME TEL JOB ADDR -- 2024-10-01
002 李四 13333333333 网安 凤和城
003 王五 15555555555 销售 人和
-- 客户信息表(ODS数据贴源层) 假设抽数时间2024-04-02(抽取2024-04-01的数据)
-- 客户信息表(ODS数据贴源层) 假设抽数时间2024-06-02(抽取2024-06-01的数据)
-- 客户信息表(DIM维度层)拉链表
ID NAME TEL JOB ADDR ST_DATE END_DATE
001 张三 12222222222 大数据 凤和城 2024-04-01 2024-05-31 -- 2024-04-02运行脚本
002 李四 13333333333 网安 凤和城 2024-04-01 9999-12-31 -- 2024-04-02运行脚本
001 张三 18888888888 大数据 凤和城 2024-06-01 9999-12-31 -- 2024-06-02运行脚本
003 王五 15555555555 销售 人和 2024-06-01 9999-12-31 -- 2024-06-02运行脚本
-- 拉链表怎么做？
1. 将增量数据（新增或者变更）插入到拉链表
2. 将拉链表里发生变更的历史数据的结束时间进行修改
-- 准备数据
CREATE TABLE YW_DEPT AS SELECT * FROM DEPT;
SELECT * FROM YW_DEPT; -- 10 20 30 40
-- 创建拉链表表结构
CREATE TABLE DIM_DEPT AS SELECT A.*,DATE'2020-01-01' ST_DATE,DATE'2020-01-01' END_DATE FROM DEPT A WHERE 1=2;
SELECT * FROM DIM_DEPT; -- 空表
-- 调用存储过程
-- 2024-04-01 对比插入增量数据
INSERT INTO DIM_DEPT
SELECT YW.*,
DATE'2024-04-01' ST_DATE, --TRUNC(SYSDATE-1)
DATE'9999-12-31' END_DATE
FROM YW_DEPT YW ;
-- WHERE
COMMIT;
SELECT * FROM DIM_DEPT; -- 10 20 30 40
-- 直到2024-07-01业务系统发生变化
INSERT INTO YW_DEPT VALUES (50,'DASHUJU','GUANGZHOU');
UPDATE YW_DEPT SET DNAME='会计' WHERE DEPTNO=10;
UPDATE YW_DEPT SET LOC='BEIJING' WHERE DEPTNO=30;
COMMIT;
SELECT * FROM YW_DEPT; -- 10 20 30 40 50
SELECT * FROM DIM_DEPT; -- 10 20 30 40
-- 调用存储过程2024-07-02
INSERT INTO DIM_DEPT
SELECT YW.*,
DATE'2024-07-01' ST_DATE, --TRUNC(SYSDATE-1)
DATE'9999-12-31' END_DATE
FROM YW_DEPT YW
WHERE NOT EXISTS
(SELECT 2 FROM DIM_DEPT D WHERE D.DEPTNO=YW.DEPTNO AND D.DNAME=YW.DNAME AND D.LOC=YW.LOC
AND D.END_DATE=DATE'9999-12-31');
UPDATE DIM_DEPT D SET D.END_DATE= DATE'2024-06-30' -- TRUNC(SYSDATE-2)
WHERE D.END_DATE=DATE'9999-12-31' AND ( EXISTS
(SELECT 1 FROM YW_DEPT YW WHERE D.DEPTNO=YW.DEPTNO
AND (D.DNAME!=YW.DNAME OR D.LOC!=YW.LOC))
OR D.DEPTNO NOT IN (SELECT DEPTNO FROM YW_DEPT));
COMMIT;
-- 直到2024-10-01业务系统发生变化
UPDATE YW_DEPT SET DNAME='ACCOUNTING' WHERE DEPTNO=10;
DELETE FROM YW_DEPT WHERE DEPTNO=20;
COMMIT;
SELECT * FROM YW_DEPT; -- 10 30 40 50
SELECT * FROM DIM_DEPT; -- 10 20 30 40 10 30 50
-- 运行脚本
INSERT INTO DIM_DEPT
SELECT YW.*,
DATE'2024-10-01' ST_DATE, --TRUNC(SYSDATE-1)
DATE'9999-12-31' END_DATE
FROM YW_DEPT YW
WHERE NOT EXISTS
(SELECT 2 FROM DIM_DEPT D WHERE D.DEPTNO=YW.DEPTNO AND D.DNAME=YW.DNAME AND D.LOC=YW.LOC
AND D.END_DATE=DATE'9999-12-31');
UPDATE DIM_DEPT D SET D.END_DATE= DATE'2024-09-30' -- TRUNC(SYSDATE-2)
WHERE D.END_DATE=DATE'9999-12-31' AND ( EXISTS
(SELECT 1 FROM YW_DEPT YW WHERE D.DEPTNO=YW.DEPTNO
AND (D.DNAME!=YW.DNAME OR D.LOC!=YW.LOC))
OR D.DEPTNO NOT IN (SELECT DEPTNO FROM YW_DEPT));
COMMIT;
SELECT * FROM YW_DEPT; -- 10 30 40 50
SELECT * FROM DIM_DEPT; -- 10 20 30 40 10 30 50 10
-- 把调试好代码封装到存储过程
CREATE OR REPLACE PROCEDURE SP_DIM_DEPT IS
/*
程序名称：SP_DIM_DEPT
程序功能：DEPT 拉链表
输入参数：
输出参数：
返 回 值：
目 标 表：DIM_DEPT
来 源 表：YW_DEPT
创 建 人：
创建时间：
修 改 人：
修改时间：
修改原因：
*/
-- 声明日志表的变量
V_NAME VARCHAR2(100):= 'SP_DIM_DEPT'; -- 要改
V_ST_DATE DATE;
V_END_DATE DATE;
V_ERRM VARCHAR2(500);
-- 声明存储过程本身的变量，游标等等
BEGIN
V_ST_DATE:=SYSDATE;
---------------------------------------------------------------------
INSERT INTO DIM_DEPT
SELECT YW.*,
TRUNC(SYSDATE-1) ST_DATE,
DATE'9999-12-31' END_DATE
FROM YW_DEPT YW
WHERE NOT EXISTS
(SELECT 2 FROM DIM_DEPT D WHERE D.DEPTNO=YW.DEPTNO AND D.DNAME=YW.DNAME AND D.LOC=YW.LOC
AND D.END_DATE=DATE'9999-12-31');
UPDATE DIM_DEPT D SET D.END_DATE= TRUNC(SYSDATE-2)
WHERE D.END_DATE=DATE'9999-12-31' AND ( EXISTS
(SELECT 1 FROM YW_DEPT YW WHERE D.DEPTNO=YW.DEPTNO
AND (D.DNAME!=YW.DNAME OR D.LOC!=YW.LOC))
OR D.DEPTNO NOT IN (SELECT DEPTNO FROM YW_DEPT));
COMMIT;
---------------------------------------------------------------------
V_END_DATE:=SYSDATE;
SP_LOG(XL.NEXTVAL,V_NAME,V_ST_DATE,V_END_DATE,'成功',V_ERRM);
EXCEPTION WHEN OTHERS THEN -- OTHERS 只要没有正常运行都叫OTHERS
V_END_DATE:=SYSDATE;
V_ERRM:=SUBSTRB(SQLERRM,1,500);
SP_LOG(XL.NEXTVAL,V_NAME,V_ST_DATE,V_END_DATE,'失败',V_ERRM);
END;
--1.学生表 STUDENT(SId,Sname,Sage,Ssex)
--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表 Course(CId,Cname,TId) --CId --课程编号,Cname 课程名称,TId 教师编号
--3.教师表 Teacher(TId,Tname) --TId 教师编号,Tname 教师姓名
--4.成绩表 SCR(SId,CId,SCR) --SId 学生编号,CId 课程编号,SCR 分数
--学生表 Student
--DROP TABLE STUDENT;
create table Student (SId varchar(10),Sname varchar(10),Sage date,Ssex varchar(10));
insert into STUDENT values('01' , '赵雷' ,DATE '1990-01-01' , '男');
insert into STUDENT values('02' , '钱电' ,DATE '1990-12-21' , '男');
insert into STUDENT values('03' , '孙风' ,DATE '1990-05-20' , '男');
insert into STUDENT values('04' , '李云' ,DATE '1990-08-06' , '男');
insert into STUDENT values('05' , '周梅' ,DATE '1991-12-01' , '女');
insert into STUDENT values('06' , '吴兰' ,DATE '1992-03-01' , '女');
insert into STUDENT values('07' , '郑竹' ,DATE '1989-07-01' , '女');
insert into STUDENT values('09' , '张三' ,DATE '2017-12-20' , '女');
insert into STUDENT values('10' , '李四' ,DATE '2017-12-25' , '女');
insert into STUDENT values('11' , '李四' ,DATE '2017-12-30' , '女');
insert into STUDENT values('12' , '赵六' ,DATE '2017-01-01' , '女');
insert into STUDENT values('13' , '孙七' ,DATE '2018-01-01' , '女');
SELECT * FROM STUDENT;
--科目表 Course
--DROP TABLE Course;
create table Course (CId varchar(10),Cname VARchar2(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
--教师表 Teacher
--DROP TABLE Teacher;
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
--成绩表 SCR
--DROP TABLE SCR;
create table SCR(SId varchar(10),CId varchar(10),SCR decimal(18,1));
insert into SCR values('01' , '01' , 80);
insert into SCR values('01' , '02' , 90);
insert into SCR values('01' , '03' , 99);
insert into SCR values('02' , '01' , 70);
insert into SCR values('02' , '02' , 60);
insert into SCR values('02' , '03' , 80);
insert into SCR values('03' , '01' , 80);
insert into SCR values('03' , '02' , 80);
insert into SCR values('03' , '03' , 80);
insert into SCR values('04' , '01' , 50);
insert into SCR values('04' , '02' , 30);
insert into SCR values('04' , '03' , 20);
insert into SCR values('05' , '01' , 76);
insert into SCR values('05' , '02' , 87);
insert into SCR values('06' , '01' , 31);
insert into SCR values('06' , '03' , 34);
insert into SCR values('07' , '02' , 89);
insert into SCR values('07' , '03' , 98);
--insert into SCR values('08' , '01' , 99);
--insert into SCR values('09' , '02' , 98);
--insert into SCR values('10' , '01' , 99);
--insert into SCR values('10' , '02' , 98);
COMMIT;
-------------------------------------------------------------
--查询数据
SELECT * FROM STUDENT; --学生表
SELECT * FROM Course; --课程表
SELECT * FROM Teacher; --教师表
SELECT * FROM SCR; --成绩表
1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及'01'课程分数
SELECT * FROM SCR A
WHERE EXISTS
(SELECT 1 FROM SCR B WHERE A.CID='01' AND B.CID='02' AND A.SID=B.SID AND A.SCR>B.SCR)
OR EXISTS
(SELECT 1 FROM SCR B WHERE A.CID='02' AND B.CID='01' AND A.SID=B.SID AND A.SCR<B.SCR);
2.查询同时学习 " 01 "课程和" 02 "课程的学生id以及 " 01 "课程和" 02 "课程的成绩
SELECT * FROM SCR A
WHERE EXISTS
(SELECT 1 FROM SCR B WHERE A.SID=B.SID AND B.CID IN ('01','02') GROUP BY B.SID HAVING COUNT(*)=2)
AND A.CID IN ('01','02');
3.查询学习" 01 "课程但可能没学习" 02 "课程的成绩信息
(没学习时显示为 null )
-- 正常
SELECT * FROM
(SELECT * FROM SCR A WHERE A.CID='01') T1
LEFT JOIN
(SELECT * FROM SCR B WHERE B.CID='02') T2
ON T1.SID=T2.SID ;
-- 谓词下推：在外关联里，对主表数据的条件筛选写在where后面，对从表数据的条件筛选写在on的后面
SELECT * FROM
SCR A
LEFT JOIN
SCR B
ON A.SID=B.SID AND B.CID='02'
WHERE A.CID='01';
4.查询没学习" 01 "课程但学习" 02 "课程的学生信息
SELECT * FROM STUDENT C WHERE C.SID IN
(SELECT A.SID FROM SCR A
LEFT JOIN
SCR B ON A.SID=B.SID AND B.CID='01'
WHERE A.CID='02' AND B.SID IS NULL);
SELECT * FROM SCR A
WHERE NOT EXISTS
(SELECT 1 FROM SCR B WHERE A.SID=B.SID AND B.CID='01')
AND A.CID='02';
5.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT A.SID,A.SNAME ,AVG(SCR) 平均成绩 FROM STUDENT A LEFT JOIN SCR B ON A.SID=B.SID
GROUP BY A.SID,A.SNAME HAVING AVG(SCR)>=60;
6.查询在 SCR 表 有成绩的学生信息
SELECT * FROM STUDENT A WHERE
SID IN (SELECT SID FROM SCR) ;
7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT A.SID,A.SNAME ,COUNT(B.CID),SUM(SCR) 总成绩 FROM STUDENT A LEFT JOIN SCR B ON A.SID=B.SID
GROUP BY A.SID,A.SNAME ;
8.查询「李」姓老师的数量
SELECT COUNT(*) FROM TEACHER A WHERE A.TNAME LIKE '李%';
9.查询学过「张三」老师授课的同学的信息
SELECT a.* from student a left join scr b on a.sid=b.sid
left join Course c on b.cid=c.cid
left join teacher d on c.tid=d.tid
where d.tname='张三';
10.查询没有学全所有课程的同学的信息
SELECT a.* from student a
where a.sid not in
(select sid from scr group by sid having count(*)=
(select count(*) from Course));
11.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select * from student a
where exists
(select 1 from scr b where a.sid=b.sid and exists
(select 1 from scr c where b.cid=c.cid and c.sid='01'));
12.查询和" 05 "号的同学学习的课程 完全相同的其他同学的信息
select * from student where sid in
(select t1.sid from scr t1 where t1.sid not in -- 求出只学了01或者02或者同时学习 01/02 课程的学生信息
(select a.sid from scr a where a.cid not in -- 求出除了01/02之外还学了其他课程的同学的学号
(select b.cid from scr b where b.sid='05')) -- 求出05同学学的课程 01 02
and t1.sid!='05'
group by t1.sid having count(*) = (select count(*) from scr c where c.sid='05'))
-- 学习课程的数量和05同学学习课程的数量一样
13.查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT a.* from student a where a.sid not in
(select b.sid from scr b
left join Course c on b.cid=c.cid
left join teacher d on c.tid=d.tid
where d.tname='张三');
14.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select sid,avg(scr) 平均成绩 from scr b where sid in
(select sid from scr a where a.scr<60 group by sid having count(*)>=2)
group by sid;
15.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select a.* from student a left join scr b on a.sid=b.sid
where b.cid='01' and b.scr<60 order by b.scr desc;
16.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.*,
avg(scr)over(partition by sid) 平均成绩
from scr a order by 平均成绩 desc;
17.查询各科成绩最高分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select a.cid,b.cname,max(scr) 最高分,min(scr) 最低分,avg(scr) 平均分,count(*) 选修人数,
count(case when scr>=60 then 1 end)/count(*) 及格率,
count(case when scr>=70 and scr<80 then 1 end)/count(*) 中等率,
count(case when scr>=80 and scr<90 then 1 end)/count(*) 优良率,
count(case when scr>=90 then 1 end)/count(*) 优秀率
from scr a left join Course b on a.cid=b.cid
group by a.cid,b.cname
order by 选修人数 desc,a.cid;
18.按各科成绩进行排序，并显示排名， SCR 重复时保留名次空缺
select a.*,rank()over(partition by cid order by scr) pm
from scr a;
19.按各科成绩进行排序，并显示排名， SCR 重复时合并名次
select a.*,dense_rank()over(partition by cid order by scr) pm
from scr a;
20.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select a.*,rank()over(order by scr desc) pm from
(select sid,sum(scr) scr from scr group by sid) a;
21. 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
select a.*,dense_rank()over(order by scr desc) pm from
(select sid,sum(scr) scr from scr group by sid) a;
22.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select a.cid,b.cname,count(*),
count(case when scr>85 then 1 end)/count(*) "[100-85]",
count(case when scr>70 and scr<=85 then 1 end)/count(*) "[85-70]",
count(case when scr>60 and scr<=70 then 1 end)/count(*) "[70-60]",
count(case when scr<=60 then 1 end)/count(*) "[60-0]"
from scr a left join Course b on a.cid=b.cid
group by a.cid,b.cname;
23.查询各科成绩前三名的记录
select b.* from
(select a.*,row_number()over(partition by cid order by scr desc) pm from scr a) b
where b.pm<=3;
24.查询每门课程被选修的学生数
select cid ,count(*) from scr group by cid;
25.查询出只选修两门课程的学生学号和姓名
select b.sid,b.sname from scr a join student b on a.sid=b.sid
group by b.sid,b.sname
having count(*)=2;
26.查询男生、女生人数
select a.ssex,count(*) from student a group by ssex;
27.查询名字中含有「风」字的学生信息
select * from student a where a.sname like '%风%';
28.查询同名同性学生名单，并统计同名人数
select substr(sname,2),count(*) from student
group by substr(sname,2),ssex having count(*)>=2;
29.查询 1990 年出生的学生名单
select * from student a where to_char(a.sage,'yyyy')=1990;
30.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select cid,avg(scr) from scr group by cid
order by avg(scr) desc,cid;
31.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select b.sid,b.sname,avg(scr) from scr a join student b on a.sid=b.sid
group by b.sid,b.sname having avg(scr)>=85;
32.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
SELECT a.sname,b.scr from student a left join scr b on a.sid=b.sid
left join Course c on b.cid=c.cid
where c.cname='数学' and b.scr<60;
33.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
SELECT * from student a left join scr b on a.sid=b.sid
left join Course c on b.cid=c.cid ;
34.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT a.sname,c.cname,b.scr from student a left join scr b on a.sid=b.sid
left join Course c on b.cid=c.cid
where a.sid in
(SELECT b.sid from scr b
group by b.sid
having min(b.scr)>70);
35.查询分数不及格的课程
SELECT distinct cid
FROM scr where scr<60;
36.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select a.sid,a.sname from student a left join scr b on a.sid=b.sid
where b.cid='01' and b.scr>=80 ;
37.求每门课程的学生人数
select cid,count(*) from scr group by cid;
38.查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩，不考虑成绩重复的情况
select * from
(SELECT a.*,b.scr,row_number()over(order by b.scr desc) pm
from student a left join scr b on a.sid=b.sid
left join Course c on b.cid=c.cid
left join teacher d on c.tid=d.tid
where d.tname='张三' order by b.scr desc ) t1
where pm=1;
39.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select * from
(SELECT a.*,b.scr,rank()over(order by b.scr desc) pm
from student a left join scr b on a.sid=b.sid
left join Course c on b.cid=c.cid
left join teacher d on c.tid=d.tid
where d.tname='张三' order by b.scr desc ) t1
where pm=1;
40.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select a.*,b.* from scr a join scr b on a.cid!=b.cid and a.scr=b.scr ;
41.查询每门功课成绩最好的前两名
select * from
(select a.*,
row_number()over(partition by cid order by scr desc ) pm
from scr a) b
where b.pm<=2;
42.统计每门课程的学生选修人数（超过 5 人的课程才统计）。
select cid,count(*) from scr group by cid having count(*)>5;
43.检索至少选修两门课程的学生学号
select sid from scr group by sid having count(*)>=2;
44.查询选修了全部课程的学生信息
select sid from scr group by sid having count(*)=
(select count(*) from course);
45.查询各学生的年龄，只按年份来算
select a.*,to_char(sysdate,'yyyy')-to_char(a.sage,'yyyy') age
from student a;
46.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select a.*,floor(months_between(sysdate,a.sage)/12) age
from student a;
47.查询本周过生日的学生
select * from student a
where to_char(a.sage,'mmdd')
between to_char(trunc(sysdate,'day')+1,'mmdd') and to_char(trunc(sysdate,'day')+7,'mmdd');
48.查询上周过生日的学生
select * from student a
where to_char(a.sage,'mmdd')
between to_char(trunc(sysdate,'day')-6,'mmdd') and to_char(trunc(sysdate,'day'),'mmdd');
49.查询本月过生日的学生
select * from student a
where to_char(a.sage,'mm') = to_char(sysdate,'mm') ;
50.查询下月过生日的学生
select * from student a
where to_char(a.sage,'mm') = to_char(add_months(sysdate,1),'mm') ;
---------------------------------------------------------------------------------------------
CREATE TABLE MARKETING(
BRAND VARCHAR2(20), --品牌
STARTDATE DATE, --营销活动开始时间
ENDDATE DATE ); --营销活动结束时间
INSERT INTO MARKETING VALUES ('华为',DATE'2018-08-04',DATE'2018-08-05');
INSERT INTO MARKETING VALUES ('华为',DATE'2018-08-04',DATE'2020-12-25');
INSERT INTO MARKETING VALUES ('小米',DATE'2018-08-15',DATE'2018-08-20');
INSERT INTO MARKETING VALUES ('小米',DATE'2020-01-01',DATE'2020-01-05');
INSERT INTO MARKETING VALUES ('苹果',DATE'2018-09-01',DATE'2018-09-05');
INSERT INTO MARKETING VALUES ('苹果',DATE'2018-09-03',DATE'2018-09-06');
INSERT INTO MARKETING VALUES ('苹果',DATE'2018-09-09',DATE'2018-09-15');
COMMIT;
SELECT * FROM MARKETING ;
3.MARKETING表记录了每个品牌的营销活动开始日期以及结束日期，现需要统计出每个品牌的总营销天数。
--注意
--1：苹果第一行数据的营销结束日期比第二行数据的营销开始日期要晚， 这部分有重叠的日期的要去重计算。
--2：苹果第二行数据的营销结束日期和第三行的开始日期不连续，2019-09-07以及2019-09-08 不统计到营销天数中。
--3：如果活动从4号开始，5号结束，活动日期为2天。
select c.brand,sum(ts-cdts) hdts from
(select b.brand,b.startdate,b.enddate,b.ts,b.p_date,
case when p_date>=startdate then p_date-startdate+1 else 0 end cdts
from
(select a.*,ENDDATE-STARTDATE+1 ts,
lag(ENDDATE)over(partition by BRAND order by STARTDATE) p_date
from MARKETING a) b) c
group by c.brand;
-- drop table MARKET;
CREATE TABLE MARKET(
BRAND VARCHAR2(20), --品牌
STARTDATE DATE, --营销活动开始时间
ENDDATE DATE ); --营销活动结束时间
INSERT INTO MARKET VALUES ('苹果',DATE'2018-08-25',DATE'2018-09-06');
INSERT INTO MARKET VALUES ('苹果',DATE'2018-08-20',DATE'2018-09-04');
INSERT INTO MARKET VALUES ('苹果',DATE'2018-09-03',DATE'2018-09-05');
INSERT INTO MARKET VALUES ('苹果',DATE'2018-09-08',DATE'2018-09-15');
INSERT INTO MARKET VALUES ('苹果',DATE'2018-09-10',DATE'2018-09-12');
INSERT INTO MARKET VALUES ('苹果',DATE'2018-09-20',DATE'2018-09-30');
COMMIT;
SELECT * FROM MARKET ;
select sum(ts-cdts) from
(select b.*,enddate-startdate+1 ts,
case when p_date>=startdate and p_date<= enddate then p_date-startdate+1
when p_date>=startdate and p_date> enddate then enddate-startdate+1
else 0
end cdts
from
(SELECT a.* ,
lag(ENDDATE)over(order by STARTDATE) p_date
FROM MARKET a) b) c ;
01 20 20 0
03 05 20 3 3
04 10 05 7 2
12 22 10 11 0 26 22
with t1 as (select a.*,ENDDATE-STARTDATE+1 ts ,lag(ENDDATE)over(partition by BRAND order by STARTDATE) p_date,
max(ENDDATE)over(partition by BRAND order by startdate rows between UNBOUNDED PRECEDING and CURRENT ROW) ma
from MARKETING a),
t2 as (select t1.*,lag(ma)over(partition by BRAND order by STARTDATE) q from t1)
,t3 as (select t2.*,
case when q>= enddate then -1
when q>=startdate and q< enddate then q-startdate+1
else 0
end r from t2 )
select brand ,sum(ts)-sum(r) l from t3 where r!=-1 group by brand;
-- 扩展内容
-- 约束 constraint ： 数据库针对表里字段下真实数据的一系列强制性规则。遵循先来后到。
-- 约束类型
primary key -- 主键约束 非空且唯一：不能为空值，不能重复。一张表只能有一个主键约束
foreign key -- 外键约束 一张表的外键必须参照另一张表的主键字段。外键字段下的数据不能突破主键字段。
CHECK -- 检查约束 添加约束时会添加条件，字段下的数据必须满足条件。
unique -- 唯一约束 唯一：不能重复
not null -- 非空约束 非空：不能为空值
-- 给字段添加主键约束或者唯一约束时，会自动为该字段创建唯一索引
create table emp0111 as select * from emp;
create table dept0111 as select * from dept;
-- 添加约束的语法：
alter table 表名 add constraint 约束名 约束类型 (字段/条件) [references 表名(主键字段)]
-- 添加约束的字段可以是多个
-- 删除约束
alter table 表名 drop constraint 约束名;
-- 添加主键约束
alter table emp0111 add constraint zjys0112 primary key (empno);
alter table emp0111 add constraint zjys0111 primary key (empno,ename); -- 联合主键
insert into emp0111 (empno,ename) values (7788,'SCOTT'); -- 联合主键要两个字段里的数据一起看是非空且唯一
alter table emp0111 add constraint zjys0113 primary key (sal);
alter table dept0111 add constraint zjys_d primary key (deptno);
select * from emp0111 ;
-- 删除约束
alter table emp0111 drop constraint zjys0111;
-- 添加外键约束
alter table emp0111 add constraint wj01 foreign key (deptno) references dept0111(deptno);
--规则：子表里外键字段下的数据不能突破父表主键字段的数据。
-- 有主键字段字段的表(参照表)为父表，有外键字段的表为子表
dept0111.deptno 10 20 30 40
emp0111.deptno 10 20 30
insert into emp0111 (empno,sal,deptno) values (8888,2000,50); -- 报错 未找到父项关键字
delete from dept0111 where deptno=10; -- 报错 已找到子记录
-- 添加检查约束
alter table emp0111 add constraint jcys01 check (SAL+500<6000);
insert into emp0111 (empno,sal,deptno) values (8888,5499.99,40);
alter table emp0111 drop constraint jcys01;
-- 添加唯一约束 空值不算重复
alter table emp0111 add constraint wyys01 unique (comm);
-- 添加非空约束
alter table 表名 modify 字段名 not null ;
alter table emp0111 modify sal not null ;
-- 删除非空约束
alter table 表名 modify 字段名 null ;
alter table emp0111 modify sal null ;
-- 通过建表语句创建表时添加约束
-- 1.
CREATE TABLE EMP111 (
EMPNO number(4) primary key , -- 添加主键约束
ename varchar2(10) unique , -- 添加唯一约束
sal number(7,2) check(sal<8000), -- 添加检查约束
job varchar2(10) not null, -- 添加非空约束
dept1 number(2) constraint wjys01 references dept0111(deptno) -- 添加外键约束
);
-- 2.
CREATE TABLE EMP222 (
EMPNO number(4),
ename varchar2(10),
sal number(7,2),
job varchar2(10) not null, -- 添加非空约束
dept1 number(2),
-- constraint 约束名 约束类型 (字段/条件) [references 表名(主键字段)]
constraint zjys02 primary key (empno), -- 添加主键约束
constraint wyys02 unique (ename), -- 添加唯一约束
constraint jcys02 check (sal<8000), -- 添加检查约束
constraint wjys002 foreign key (dept1) references dept0111(deptno) -- 添加外键约束
);
------------------------------------------------------------------------------------------------------
-- 触发器 trigger
-- 监控对表的DML操作。当对表执行DML操作时，会自动运行pl/sql代码块
create table emp01111 as select * from emp;
create table emp_tri as select 1 xh,'1385185615' dml_type,sysdate dml_date,emp.* from emp where 1=2;
create sequence emp_xl;
-- 创建触发器的语法
create [or replace] trigger 触发器名
[before|after]
[delete|insert|update] -- 可以只写一个，也可以都写。都写时用 or 分隔
on 表名 -- 监控的表
[for each row] -- 写for each row就行级触发器。不写for each row语句级触发器
-- 行级触发器 每一行数据都会触发一次pl/sql代码块
-- 语句级触发器 每个语句触发一次
DECLARE
-- 声明部分
begin
-- 逻辑执行部分 -- 不能写 commit和rollback
exception
-- 异常处理
end;
-- 触发器怎么获取数据
:old -- 获取表里的原始数据 :old.字段 针对的dml操作： delete update
:new -- 获取表外数据 :new.字段 针对的dml操作： insert update
drop trigger emp_del;
-- 不能删老板
create or replace trigger emp_del
after delete
on emp01111
for each row
DECLARE
begin
if :old.ename='KING' then
raise_application_error(-20555,'不能删老板');
end if;
end;
delete from emp01111 where empno=7839;
-- 在触发器里怎么判断dml操作的类型：
inserting -- 返回布尔类型 。当执行 insert 语句时返回true。
updating -- 返回布尔类型 。当执行 update 语句时返回true。
deleting -- 返回布尔类型 。当执行 delete 语句时返回true。
-- 创建一个监控所有DML语句的触发器，
-- 当删除时，将删除的数据插入emp_tri表中，
-- 当插入时，将插入的数据插入emp_tri表中，
-- 当修改时，将原始数据插入emp_tri表中
create or replace trigger emp_dml
after delete or insert or update
on emp01111
for each row
DECLARE
begin
if inserting then
insert into emp_tri values (emp_xl.nextval,'insert',sysdate,:new.empno,
:new.ename,:new.job,:new.mgr,:new.hiredate,:new.sal,:new.comm,:new.deptno);
elsif deleting then
insert into emp_tri values (emp_xl.nextval,'delete',sysdate,:old.empno,
:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
elsif updating then
insert into emp_tri values (emp_xl.nextval,'update',sysdate,:old.empno,
:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
end if;
end;
delete from emp01111 where empno=7839;
select * from emp_tri;
----------------------------------------------------------------------------------------
-- 视图 对单表或者多表的预定义查询，数据来源表称为基表。 有名字的select语句
--视图的优点：
1.可以限制用户只能通过视图检索数据，这样就可以对最终用户屏蔽建表时底层的基表，具有安全性。
2.可以将复杂的查询保存为视图，屏蔽复杂性。
--视图和表的区别
1.查询数据，视图和表没有区别
2.表有真实数据，占磁盘空间。视图里没有数据，不占磁盘空间
3. with 语句创建的临时表，在内存里创建临时表，sql运行结束，表消失。
-- 创建视图需要权限
1. 登录 system 账户，密码：123456
2. 运行 grant create view to scott ; -- 授创建视图的权限给 scott
3. 运行 grant create materialized view to scott ; -- 授创建物化视图的权限给 scott
4. 切换回scott账户。
-- 创建视图的语法：
create [or replace] [FORCE] view 视图名
as select 语句
[with read only] ;
create view 视图名 as select 语句;
create table 表名 as select 语句;
-- or replace 替换
-- FORCE 强制创建
-- with read only 只读，只能查询
-- 视图名，表名，物化视图名 共用一个命名体系，相互之间都不能重名
create or replace view asdf as select a.*,b.dname ,b.loc
from emp a join dept b on a.deptno=b.deptno;
select * from asdf;
select * from (select a.*,b.dname ,b.loc
from emp a join dept b on a.deptno=b.deptno);
-- 注意事项：
1. 多表关联创建视图时，不能有重复的列名
2. 通过视图查询数据，其实是在将封装的select语句当作子查询在运行。
drop table emp0112;
create table emp0112 as select * from emp;
create table dept0112 as select * from dept;
-- 对视图进行dml操作
-- 1.对单基表视图，可以通过对视图进行DML操作来完成对基表DML操作 -- 不推荐
create or replace view emp2222 as select * from emp0112;
delete from emp2222 where deptno=10;
select * from emp2222;
delete from emp0112 where deptno=30;
-- 2.对多基表的视图，不可以通过对视图进行DML操作来完成对基表DML操作
-- 除非关联字段设置了主键，但是操作的是未将关联字段设置成主键的表
create or replace view asdf as select a.*,b.dname ,b.loc
from emp0112 a join dept0112 b on a.deptno=b.deptno;
delete from asdf where deptno=10; -- 报错
alter table dept0112 add constraint adgadg primary key (deptno); -- 给dept0112表的 deptno 添加主键约束
delete from asdf where deptno=10; -- 删除的是 emp0112
select a.* from emp0112 a;
select B.* from dept0112 B;
-- 强制创建视图：当基表不存在时，创建视图。创建的视图不能用，当基表创建之后就可以用了。
create or replace view abcdde as select * from sdfgjfdh; -- 报错
create or replace force view abcdde as select * from sdfgjfdh; -- 强制创建
select * from abcdde; -- 查询不了
create table sdfgjfdh as select * from emp where deptno=20; -- 创建基表
select * from abcdde; -- 可以查询
---------------------------------------------------------------------------------------------
-- 物化视图
-- 物化视图和视图的区别
1. 视图没有真实数据，不占磁盘空间。物化视图有真实数据，占磁盘空间。
2. 视图里的数据是实时更新的。物化视图可以实时更新也可以手动更新。实时更新，当数据提交时更新。
3. 视图的查询受基表逻辑的影响，但是物化视图不受。
-- 物化视图和表的区别
1. 通过select语句建表，表里数据在建表完成之后就和select语句的来源表里的数据没有关系了。
2. 通过select语句创建物化视图，物化视图里的数据还是会随着基表的数据变更而变更。
-- 创建物化视图的语法：
CREATE MATERIALIZED VIEW 视图名
refresh [complete|fast] -- 刷新方式
-- complete 全量刷新
-- fast 快速刷新/增量刷新
on [commit|demand] -- 刷新时机
-- commit 提交刷新，当基表的数据发生变更并且提交时，物化视图里的数据更新。只能用于单基表的物化视图
-- demand 手动刷新
with [primary key|rowid] -- 刷新依据，一定要是非空且唯一的
-- primary key 主键约束 ，要求物化视图里携带主键约束的字段。基表里要有主键约束
-- rowid 物理地址
as select 语句;
-- 单基表的物化视图
create MATERIALIZED view emp_mv
refresh complete on commit with rowid
as select * from emp0112;
select * from emp_mv; -- 物化视图
select * from emp2222; -- 视图
delete from emp0112 where deptno=30;
-- 未提交时，视图数据变更，物化视图没变更
commit;
-- 提交后，视图数据变更，物化视图变更
-- 创建多基表的物化视图
CREATE MATERIALIZED VIEW emp_dept_mv
refresh complete
on demand -- 只能用手动
with rowid
as select a.*,b.dname ,b.loc
from emp0112 a join dept0112 b on a.deptno=b.deptno;
delete from emp0112 where deptno=30;
commit;
select * from emp_dept_mv;
-- 怎么手动更新物化视图，需要调用存储过程
CALL DBMS_MVIEW.refresh('emp_dept_mv','c') ; -- 物化视图名(字符串) ，刷新方式的首字母
-- 定时刷新的物化视图
-- 每天早上10点刷新物化视图
CREATE MATERIALIZED VIEW emp_dept_mv10
refresh complete
on demand -- 只能用手动
with rowid
-- 从当前系统时间开始， next后是下一次刷新的时间 startwith后不能跟注释
start with sysdate next to_date(to_char(sysdate+1,'yyyymmdd')||'100000','yyyymmddhh24miss')
as select a.*,b.dname ,b.loc
from emp0112 a join dept0112 b on a.deptno=b.deptno;
-- 索引 index
数据库中索引(index)的概念与目录的概念非常类似。如果某列出现在查询的条件中，
而该列的数据是无序的，查询时只能从第一行开始一行一行的匹配。创建索引就是对某些特定列中
的数据排序，生成独立的索引表。在某列上创建索引后，如果该列出现在查询条件中，oracle
会比较全表扫描与索引扫描的代价，如果索引扫描代价小，那oracle会自动引用该索引，先从索引表中查询
出符合条件记录的ROWID,由于ROWID是记录的物理地址，因此可以根据ROWID快速定位到具体的记录，
表中的数据非常多时，引用索引带来的查询效率非常高。
SELECT E.*,rowid from emp E where sal>2000; -- 全表扫描
-- 全表扫描与索引扫描
1. 当满足条件的数据量占总数据量的比例在10%以内，索引扫描快。
2. 当满足条件的数据量占总数据量的比例在90%以上，全表扫描快。
-- 什么时候创建索引
1. 当字段经常被当作筛选条件时（经常写在where后面）
2. 当字段经常被用作关联条件时（经常写在on后面）
-- 索引的优缺点
-- 优点：快速筛选过滤数据
-- 缺点： 1. 需要经常维护
-- 2. 会一定程度影响增改删效率
b-tree b树 平衡树
叶节点 参数t 数据存储上限=2*t-1 下限=t-1 当t=3 上限5 下限2
800.00
950.00
1100.00
1250.00
1250.00
1300.00
1500.00
1600.00
2450.00
2850.00
2975.00
3000.00
3000.00
5000.00
-- 索引的分类
unique -- 唯一索引 ，字段里的数据是唯一的。给字段添加主键约束或者唯一约束时会自动创建唯一索引
-- 普通索引 ，给单个字段创建索引。对字段的数据没有要求
-- 组合索引 ，给多个字段创建索引。使用索引时需要满足最左原则。
-- 函数索引 ，给以函数名新生成的字段创建索引。只能对单一函数有用。
bitmap -- 位图索引 ，用于枚举值少，重复值多的字段
-- 创建索引
create [索引分类] index 索引名 on 表名(字段/函数 [asc|desc]);
create table emp0113 as select * from emp;
-- 创建唯一索引
create unique index ywsy01 on emp0113 (empno); -- 组合唯一索引
create index ptsy01 on emp0113 (sal); -- 普通索引
create index ptsy03 on emp0113 (comm); -- 普通索引
create index ptsy04 on emp0113 (ENAME);
create index ptsy02 on emp0113 (deptno,job,hiredate); -- 组合索引
create index hssy02 on emp0113 (round(sal)); -- 函数索引
create bitmap index wtsy01 on emp0113 (mgr); -- 位图索引
-- 删除索引
drop index 索引名;
drop index ywsy01;
-- 重建索引
ALTER INDEX 索引名 rebuild;
-- 索引失效 看执行计划(F5) 按缩进看，缩进越多的先执行
1. 对索引字段进行算术运算会导致索引失效
select * from emp0113 where sal>2000 ; -- 索引范围扫描
select * from emp0113 where sal+500>2500; -- 全表扫描
2. 对索引字段进行函数处理会导致索引失效
select * from emp0113 where sal>2000; -- 索引范围扫描
select * from emp0113 where trunc(sal)>2000; -- 全表扫描
select * from emp0113 where round(sal)>2000; -- 创建函数索引 索引范围扫描
select * from emp0113 where round(sal,2)>2000; -- 全表扫描
3. 组合索引没有遵循最左原则会导致索引失效
-- 最左原则：作为条件筛选时，组合索引里的第一个字段没有起到作用，索引会失效
SELECT * FROM emp0113 where job='MANAGER' and hiredate>date'1981-01-01'; -- 全表扫描
SELECT * FROM emp0113 where job='MANAGER' and hiredate>date'1981-01-01' or deptno=10; -- 全表扫描
SELECT * FROM emp0113 where deptno=10; -- 索引范围扫描
4. 当索引字段的数据类型发生隐式转换时，索引会失效
-- 1. 当数值类型和字符串类型进行算术运算或者比较运算时，会将字符串转成数值。
-- 2. 数值类型作为字符串类函数的参数时，会将数值转成字符串
substr(123456,2,3) -- '234'
'100'*2 -- 200
create index ywsy02 on student (sid); -- 给student表里的sid字段创建索引
select * from student where sid>'06'; -- 索引范围扫描
select * from student where sid>6; -- 全表扫描 将sid(索引字段)的数据类型转成数值
select * from emp0113 where sal>2000 ; -- 索引范围扫描
select * from emp0113 where sal>'2000' ; -- 索引范围扫描 将'2000'转成2000
5. not in 或者 not exists 会导致索引失效
-- not in 会导致主查询的索引失效
-- not exists 会导致子查询的索引失效
select * from emp0113 where deptno in (10,20); -- 索引范围扫描
select * from emp0113 where deptno not in (10,20); -- 全表扫描
select * from emp0113 a where exists -- 全表扫描
(select 1 from emp b where a.empno=b.empno and b.job='SALESMAN'); -- 索引全扫描
select * from emp0113 a where not exists -- 全表扫描
(select 1 from emp b where a.empno=b.empno and b.job='SALESMAN'); -- 全表扫描
6. or 会导致索引失效
SELECT * FROM emp0113 where deptno=10 and hiredate>date'1981-01-01'; -- 索引范围扫描
SELECT * FROM emp0113 where deptno=10 or hiredate>date'1981-01-01'; -- 全表扫描
7. is null 会导致索引失效
select * from emp0113 where comm is not null ; -- 索引全扫描
select * from emp0113 where comm is null ; -- 全表扫描
8. like 模糊查询'%'或者'_'写在左边会导致索引失效？
SELECT * FROM emp0113 where ENAME LIKE 'C%'; -- 索引范围扫描
SELECT * FROM emp0113 where ENAME LIKE '%C%'; -- 索引全扫描
Oracle SQL 优化：
-- SQL调优的核心：空间和时间平衡
1.常规语句优化：执行步骤的优化
2.索引的优化：空间换时间的优化
3.表结构的优化(分区)
4.优化器：oracle在执行SQL之前会生成执行计划，优化器会去选择一条系统最优的路线
(主要是从内存和CPU消耗作为判断)
RBO：基于规则优化
CBO：基于代价优化
常规的sql优化：
1. 执行步骤的优化：谓词下推(外关联，主表数据条件筛选写在where后面，从表数据的条件筛选写在on后面)
2. 在表关联之前先筛选数据（谓词下推）。在分组聚合计算前先筛选数据。
3. 有索引时尽量走索引，避免索引失效。 -- 要知道索引什么时候会失效
-- 正常情况下，优化器会比较全表扫描和索引扫描的代价来决定用那中方式获取数据。
-- 强制走索引，不管代价，直接走索引。前提条件是索引生效
select /* + index 表名(索引名) */ * from emp0113 where sal>2000 ; -- 表名/别名
select /* + index emp0113(ptsy01) */ * from emp0113 where sal>2000 ;
select /* + index T1(ptsy01) */ * from emp0113 T1 where sal>2000 ;
4. 不要写select * 写select 字段。
5. 清空表，用 truncate 代替 delete 。
6. 分批次提交
7. 用 exists 代替 in
8. 求并集，用 union all 代替 union 。
9. 当表数据量非常大时，会创建分区表。
10. sql 解耦。当多张数据量很大的表进行表关联时，运行时内存读取和计算的数据量越来越大，会导致查询效率变低，甚至出现内存溢出问题。
select * from a left join b on a.col1=b.col1
left join c on b.col2=c.col2
left join d on c.col3=d.col3
left join e on d.col4=e.col4; -- 当 a b c d e 都是数据量非常大的表时
with t1 as (select * from a left join b on a.col1=b.col1)
,t2 as (select * from c left join d on c.col3=d.col3 left join e on d.col4=e.col4)
select * from t1 left join t2 on t1.col2=t2.col2;
create table tmp_t1 as select * from a left join b on a.col1=b.col1;
create table tmp_t2 as select * from c left join d on c.col3=d.col3 left join e on d.col4=e.col4;
select * from tmp_t1 left join tmp_t2 on tmp_t1.col2=tmp_t2.col2;
----------------------------------------------------------------------------------------------------
-- 分区表
表分区的具体作用：
分区功能可以将表、索引或者索引组织表进一步细分为段，这些数据对象的段叫作分区，
每个分区都有自己的名称，一个分区后的对象(表)具体有多个段，这些段既可以集体管理，也可以单独管理。
什么时候需要用分区表：
(1)表的数据量特别大
(2)表中包含历史数据，新的数据被增加到新的分区中。
表分区的优缺点：
优点：
1.改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
2.增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用。
3.维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可。
4.均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统的性能。
缺点：
1.已经存在的表没有方法可以直接转化为分区表。
2.需要维护。
表分区的几种类型：
1.范围分区：RANGE 针对数值、金额、年龄、时间等进行数据内容的划分，对相同范围的数据进行统一的管理
2.列表分区：LIST 有大量的重复信息的列上面，例如省份、商品分类、性别、部门、岗位...
3.散列分区 HASH 如果表格的字段没有任何的规则，那么就创建散列分区，数据库内部通过数据的hash哈希算法，
将数据计算能不同的哈希值，然后根据计算的结果进行数据的分类。
4.组合分区：范围分区+列表分区 列表分区+范围分区 主分区+子分区
--分区的关键字
PARTITION --主分区
SUBPARTITION --子分区
-- 1. 通过select语句建分区表
create table table_name
partition by [RANGE|LIST|HASH] (字段名) -- 定义分区类型和字段
( partition 分区名1 values [less than] (值1),
partition 分区名2 values [less than] (值2),
partition 分区名3 values [less than] (值3)
......
) -- 定义分区规则
as select 语句 ;
-- 创建范围分区表
create table EMP11
partition by RANGE (SAL) -- 定义分区类型和字段
( partition fq1 values less than (1500), -- < 1500
partition fq2 values less than (3000), -- < 3000 >=1500
partition fq3 values less than (6000) -- < 6000 >=3000
--partition fq4 values less than (maxvalue) -- >= 6000
) -- 定义分区规则
as select * from emp ;
-- 查询数据
select * from EMP11 ; -- 查询全部数据
select * from emp11 partition (fq1); -- 读取分区的数据
-- 查询所有工资大于2000的员工信息
select * from emp11 partition (fq3)
union all
select * from emp11 partition (fq2) where sal>2000; -- 8
select * from emp11 where sal>2000; -- 14
insert into emp11 values (8888,'zhangsan','',null,null,6000,null,30); -- 报错
-- 创建列表分区表
create table emp22
partition by LIST (job) -- 定义分区类型和字段
( partition fq1 values ('ANALYST','MANAGER','PRESIDENT'),
partition fq2 values ('CLERK'),
partition fq3 values ('SALESMAN')
--partition fq4 values (default) -- 不满足上面岗位的员工全不放在fq4里
) -- 定义分区规则
as select * from emp ;
-- 查询数据
select * from EMP22 ; -- 查询全部数据
select * from emp22 partition (fq2); -- 读取分区的数据
-- 创建散列分区表
-- 1. 指定分区名
create table EMP33
partition by hash (ename) -- 定义分区类型和字段
( partition fq1 ,
partition fq2 ,
partition fq3
) -- 定义分区名
as select * from emp ;
-- 查询数据
select * from EMP33 ; -- 查询全部数据
select * from emp33 partition (fq3); -- 读取分区的数据
-- 2. 指定分区数量
create table EMP44
partition by hash (ename) -- 定义分区类型和字段
partitions 3 -- 定义分区数量
as select * from emp ;
-- 查询数据
select * from EMP44 ; -- 查询全部数据
select * from emp44 partition (SYS_P49); -- 读取分区的数据
select emp.*,mod(ora_hash(ename),3) from emp;
-- 范围分区+列表分区 以sal作为范围，以deptno列表
create table emp55
partition by RANGE (sal) subpartition by list (deptno) -- 定义主分区类型和字段以及子分区类型和字段
( partition fq1 values less than (1500)
(
subpartition zfq1 values (10),
subpartition zfq2 values (20),
subpartition zfq3 values (30)
) ,
partition fq2 values less than (3000)
(
subpartition zfq4 values (10),
subpartition zfq5 values (20),
subpartition zfq6 values (30)
) ,
partition fq3 values less than (6000)
(
subpartition zfq7 values (10),
subpartition zfq8 values (20),
subpartition zfq9 values (30)
)
) -- 定义主分区规则 (子分区规则)
as select * from emp ;
-- 查询数据
select * from EMP55 ; -- 查询全部数据
select * from emp55 partition (fq1); -- 读取主分区的数据
select * from emp55 subpartition (zfq3); -- 读取子分区的数据
-- 2. 通过建表语句创建分区表
create table table_name(
col1 type1,
col2 type2,
col3 type3,
.......
)
partition by [RANGE|LIST|HASH] (字段名) -- 定义分区类型和字段
( partition 分区名1 values [less than] (值1),
partition 分区名2 values [less than] (值2),
partition 分区名3 values [less than] (值3)
...... -- 定义分区规则
) ;
create table emp66(
empno number,
ename varchar2(10),
sal number,
deptno number)
partition by RANGE (sal) subpartition by list (deptno) -- 定义主分区类型和字段以及子分区类型和字段
( partition fq1 values less than (1500)
(
subpartition zfq1 values (10),
subpartition zfq2 values (20),
subpartition zfq3 values (30)
) ,
partition fq2 values less than (3000)
(
subpartition zfq4 values (10),
subpartition zfq5 values (20),
subpartition zfq6 values (30)
) ,
partition fq3 values less than (6000)
(
subpartition zfq7 values (10),
subpartition zfq8 values (20),
subpartition zfq9 values (30)
)
) -- 定义主分区规则 (子分区规则)
;
扫一扫
1121
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
