这篇博客主要介绍了Oracle数据库的基础知识，包括其核心概念、数据类型、SQL语法以及实践操作，通过学习可以帮助初学者掌握Oracle数据库的基本操作和应用。
Oracle基础+练习
SELECT * FROM EMP ;
查询 所有 从 表名 。
--关键字 固定写法 oracle不区分大小写 所有的符号必须是英文的
--; ； ' ‘ ,， () （）
--表名 员工信息表
表头 列名 字段名
EMPNO 员工编号
ENAME 员工姓名
JOB 岗位
MGR 上级编号
HIREDATE 入职日期
SAL 工资
COMM 奖金
DEPTNO 部门编号
SELECT * FROM EMP;
--指定字段查询 显示员工编号 姓名 部门编号
--每个字段之间使用逗号隔开
SELECT EMPNO, ENAME ,SAL FROM EMP;
--查询哪张表 from后写对应表名即可
--DEPTNO 部门编号
--DNAME 部门名称
--LOC 工作地点
SELECT * FROM DEPT;
--起别名
--别名不能以数字/特殊符号开头
--如果别名需要包含特殊符号，给别名加""
--给字段起别名 1.字段名后直接加AS 2.字段名后加空格
--给表起别名 1.表名之后加空格
SELECT EMPNO "{员工编号}" , ENAME AS 员工姓名,
SAL AS 工资 FROM EMP;
--别名打点调用表中字段 先起别名再去调用
SELECT E.ENAME,E.JOB FROM EMP E;
--表名打点调用表中字段
SELECT EMP.ENAME,EMP.JOB FROM EMP;
SELECT * FROM EMP W;
--上一句sql没写分号，下一句可能会调用上一句sql的别名
SELECT W.DNAME FROM DEPT W;
--星号不能跟字段一起使用 但是可以别名打点使用
SELECT E.*,ENAME FROM EMP E;
--E.ALL代表显示该表所有的字段
SELECT E.EMPNO,
E.ENAME,
E.JOB,
E.MGR,
E.HIREDATE,
E.SAL,
E.COMM,
E.DEPTNO FROM EMP E;
--WHERE 条件筛选
--算数运算符 + - * /
--老板开心 给所有人加1000工资
SELECT SAL - 1000 AS NEW_SAL,SAL AS OLD_SAL FROM EMP;
--只有数字类型可以进行算数运算
SELECT HIREDATE-1000,HIREDATE FROM EMP;
--日期类型可以加减数字但是不可以乘除
--比较运算符 = < > <= >= 不等于 1.!= 2.<>
我想只显示SCOTT的员工信息
--WHERE写在表名之后
--字符串类型一定要加单引号
SELECT * FROM EMP WHERE ENAME = 'SCOTT' ;
--字符串大小写要和表中数据保持一致
我想显示除了SCOTT以外的员工信息
!=
SELECT * FROM EMP WHERE ENAME <> 'SCOTT' ;
显示工资在2000以上的员工信息
SELECT * FROM EMP WHERE ENAME >= 'C';
字符串之间比较大小是通过ASCII值来进行大小比较
--逻辑运算符 或与非 OR AND NOT
--OR 或者 两边条件满足其中一个就会返回查询结果
--NAD 并且 两边条件同时满足才会返回查询结果
显示工资在 1000-3000的员工信息
SELECT * FROM EMP WHERE SAL >1000 AND SAL < 3000;
显示SCOTT，SMITH，KING三个人的员工信息
SELECT * FROM EMP WHERE ENAME = 'SCOTT' OR
ENAME = 'SMITH' OR ENAME = 'KING';
--显示20部门中CLERK岗位和MANAGER岗位的员工信息
--AND的执行顺序高于OR
--NOT > AND >OR
SELECT * FROM EMP WHERE DEPTNO = 20 AND (JOB ='MANAGER' OR
JOB = 'CLERK');
--IN 在某些个之间
IN可以判断多个值也可以判断一个值
=只能等于一个值
显示SCOTT，SMITH，KING三个人的员工信息
SELECT * FROM EMP WHERE ENAME = 'SCOTT' OR
ENAME = 'SMITH' OR ENAME = 'KING';
--IN写法
SELECT * FROM EMP WHERE ENAME IN('SCOTT','SMITH','KING');
SELECT * FROM EMP WHERE ENAME IN('SCOTT');
查询10，20部门的员工信息
SELECT * FROM EMP WHERE DEPTNO = 10 OR DEPTNO = 20;
--IN 是等于20或者等于10 并不是在10-20之间 11 12 13...都不算
SELECT * FROM EMP WHERE DEPTNO IN(10,20);
--NOTIN 取反
查询不是10，20部门的员工信息
SELECT * FROM EMP WHERE DEPTNO NOT IN(10,20);
---------------------------------
显示除了SCOTT，SMITH，KING三个人的员工信息
SELECT * FROM EMP WHERE ENAME NOT IN('SCOTT','SMITH','KING');
--------------------------------0526------------------
--空值 NULL
--有空格也占一个长度
SELECT * FROM EMP;
--找没有奖金的员工信息
--IS NULL 为空
SELECT * FROM EMP WHERE COMM IS NULL OR COMM = 0;
--找有奖金的员工信息
--IS NOT NULL 不为空
SELECT * FROM EMP WHERE COMM IS NOT NULL AND COMM !=0;
--空值特称
--不参与排序，不参于比较 不参与算数运算 任何值加减乘除空值都得空
SELECT * FROM EMP A JOIN EMP B ON A.COMM = B.COMM;
--给所有员工加1000块钱奖金
SELECT COMM + 1000 AS 新奖金,COMM 旧奖金,EMPNO FROM EMP;
--把空值转化成0
--NVL 转换空值 函数
NVL(转换字段,要转换的值)
--要转的值必须和1原字段的数据类型保持一致
SELECT NVL(COMM,0)+1000 新,COMM 旧 FROM EMP;
-- 把姓名为空的转化成无名氏
SELECT * FROM EMP1 FOR UPDATE; --修改表中数据
--字符串类型装一切
SELECT NVL(ENAME,'无名氏') FROM EMP1;
--字符串长度是字节长度 一个汉字占两个字节
VARCHAR2
CHAR
VARCHAR
STRING
INT
DATETIME
DATE
--NULLIF 把字段里的值转为空值
--姓名为smith的员工转化为空值
SELECT NULLIF(ENAME,'SMITH'),ENAME FROM EMP;
--BETWEEN AND 区间
--查询工资在1000到3000之间的员工信息
--大于等于左边值 小于等于右边值
SELECT * FROM EMP WHERE SAL BETWEEN 1000 AND 3000;
--找出在1981年四月到1981年十月入职的员工信息
SELECT * FROM EMP WHERE HIREDATE
BETWEEN DATE'1981-04-01' AND DATE'1981-10-30'
;
--模糊查询 LIKE
--% 通配符 代表任意多个字符 可以是零个
--_ 占位符 代表一个字符
--ESCAPE'\' 转义字符 可以讲特殊符号转为普通字符
--找出姓名第一个字为S的员工信息
SELECT * FROM EMP WHERE ENAME LIKE'S%';
--找出姓名最后一个字为S的员工信息
SELECT * FROM EMP WHERE ENAME LIKE'%S';
--找出姓名有A的员工信息
SELECT * FROM EMP WHERE ENAME LIKE'%A%';
--找出姓名有LE的员工信息
SELECT * FROM EMP WHERE ENAME LIKE'%LE%';
--找出姓名有L和E的员工信息
SELECT * FROM EMP1 WHERE ENAME LIKE'%L%E%';
--找出姓名有L和E的员工信息,L和E不考虑前后顺序
--OR
SELECT * FROM EMP1 WHERE ENAME LIKE'%L%E%' OR
ENAME LIKE'%E%L%';
--AND
SELECT * FROM EMP1 WHERE ENAME LIKE'%L%' AND
ENAME LIKE'%E%';
--找出名字第二个字为A的员工信息
SELECT * FROM EMP1 WHERE ENAME LIKE'_A%';
--找出名字倒数第二个字为A的员工信息
SELECT * FROM EMP1 WHERE ENAME LIKE'%T_';
--找出名字名字里有_的员工信息
SELECT * FROM EMP1 WHERE ENAME LIKE'%\_%'
ESCAPE'\';
--找出名字名字里有%的员工信息
SELECT * FROM EMP1 WHERE ENAME LIKE'%=%%'
ESCAPE'=';
SELECT * FROM EMP1 FOR UPDATE
查询有奖金的员工的不同工种
--DISTINCT 去重
SELECT DISTINCT JOB FROM EMP WHERE COMM IS NOT NULL
AND COMM != 0;
查询没有奖金或奖金低于100的员工信息
SELECT * FROM EMP WHERE COMM IS NULL OR COMM <100;
查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息
SELECT * FROM EMP WHERE (DEPTNO = 10 AND JOB = 'MANAGER') OR
(DEPTNO = 20 AND JOB = 'CLERK');
查询所有工种不是MANAGER和CLERK，
且工资大于或等于2000的员工的详细信息
--AND
SELECT * FROM EMP WHERE JOB != 'MANAGER' AND JOB != 'CLERK'
AND SAL >=2000;
--NOT IN
SELECT * FROM EMP WHERE JOB NOT IN ('MANAGER','CLERK')
AND SAL >=2000;
查询员工名字中不包含字母“S”员工
SELECT * FROM EMP WHERE ENAME NOT LIKE'%S%';
--聚合函数 只写一个参数
SELECT SUM(SAL+NVL(COMM,0)) FROM EMP;
SUM 求和 MAX 最大 MIN 最小 COUNT 计数 AVG 求平均值
SELECT SUM(SAL) AS 总工资,--不统计空值
MAX(SAL) AS 最高工资,--不统计空值
MIN(SAL) AS 最低工资,
AVG(COMM) AS 平均工资,--不统计空值
COUNT(*) AS 总人数, --COUNT(*) 统计空值
COUNT(COMM) AS 总人数, --COUNT(字段)不统计空值
--COUNT(主键字段) 效率最高 主键 非空且唯一
COUNT(1) AS 总人数 --统计空值
FROM EMP;
--COUNT/SUM
1
0
1
1 SUM=3 COUNT=4
--聚合函数数据类型
SUM/AVG 只支持数字类型
COUNT/MAX/MIN 支持所有数据类型
SELECT COUNT(E.HIREDATE) FROM EMP E;
--注意事项
--分组函数不能和表中字段一起使用，除非是分组后的字段
SELECT AVG(SAL),JOB FROM EMP GROUP BY JOB;
SELECT SAL,DEPTNO FROM EMP;
--分组 GROUP BY 去重效果
--没有where就写在表名之后 有where就写在where后面
--只有分组后的字段可以写在SELECT之后
--那个字段在前现根据字段进行分组，然后再根据后面的字段进行分组
--只要看到各个 每个就分组
各部门各岗位下的总工资
SELECT SUM(SAL),DEPTNO,JOB FROM EMP GROUP BY DEPTNO,JOB;
求工资在2000以上的各部门总工资
SELECT SUM(SAL),DEPTNO FROM EMP WHERE SAL > 2000 GROUP BY DEPTNO
--HAVING 条件过滤
WHERE --过滤表中源数据 不能和聚合函数搭配使用
HAVING --过滤分组后的数据
显示人数在三人以上的部门编号
SELECT COUNT(EMPNO) 人数,DEPTNO FROM EMP
GROUP BY DEPTNO
HAVING COUNT(EMPNO) > 3;
小练习：
查出每个部门每个职位中工资大于1000的人中
最高工资，最低工资，平均工资，工资总和，职位人数。
SELECT MAX(SAL) AS 最高,MIN(SAL) AS 最低,
SUM(SAL) AS 总工资,COUNT(EMPNO) AS 人数,
AVG(SAL) AS 平均值,
DEPTNO,JOB FROM EMP WHERE SAL > 1000 GROUP BY DEPTNO,JOB ;
SELECT * FROM EMP WHERE DEPTNO = 30
小练习：
1.查出10和20部门的所有员工信息。
SELECT * FROM EMP WHERE DEPTNO IN (10,20);
SELECT * FROM EMP WHERE DEPTNO =10 OR DEPTNO = 20;
2.查出30部门全年工资与奖金总和。
SELECT SUM(SAL*12+NVL(COMM,0)*12) AS 工资总和 FROM EMP WHERE DEPTNO = 30;
3.查出每个部门每个职位的员工数量。
SELECT COUNT(EMPNO) 人数,DEPTNO,JOB FROM EMP GROUP BY DEPTNO,JOB
4.查出年薪大于5000且奖金大于1000的员工信息。
SELECT * FROM EMP WHERE SAL*12 >5000 AND COMM > 1000;
5.查出各部门各职位员工数量与部门职位年薪。
SELECT COUNT(*),SUM(SAL*12) AS 年薪,DEPTNO,JOB FROM EMP
GROUP BY DEPTNO,JOB;
6.查出20部门CLERK和MANAGER职位的员工数量。
SELECT COUNT(*) FROM EMP WHERE DEPTNO = 20
AND (JOB = 'CLERK' OR JOB = 'MANAGER');
SELECT * FROM EMP;
7.查出30部门年薪大于5000或奖金大于1000的员工信息。
SELECT * FROM EMP WHERE DEPTNO = 30 AND (SAL * 12 > 5000
OR COMM > 1000);
8.统计各个岗位的人数与平均工资
SELECT COUNT(*) , AVG(SAL) FROM EMP GROUP BY JOB;
找出每个部门的最高工资大于2850的部门及最高工资
SELECT MAX(SAL),DEPTNO FROM EMP GROUP BY DEPTNO
HAVING MAX(SAL) > 2850;
查询 部门10 之外的其余部门的 人数和最高工资
SELECT COUNT(1),MAX(SAL),DEPTNO FROM EMP WHERE DEPTNO !=10
GROUP BY DEPTNO;
查询每种工作的人数，以及每种工作的平均工资>1500的工作信息
SELECT COUNT(1) AS 人数,AVG(SAL) AS 平均工资 FROM EMP
GROUP BY JOB HAVING AVG(SAL) > 1500;
--排序关键字 ORDER BY
--永远写在查询语句最后
升序 123456 ASC 默认升序
降序 654321 DESC
SELECT * FROM EMP ORDER BY SAL ASC;
--根据多个字段进行排序
--谁在前现根据谁来排序，当前面字段出现值一样的情况再根据后面的字段
--进行排序
--只有orderby 后支持使用别名
SELECT * FROM EMP ORDER BY SAL ASC,EMPNO DESC;
--根据日期/字符串进行排序
SELECT * FROM EMP ORDER BY HIREDATE DESC;
--根据字符串进行排序
SELECT * FROM EMP ORDER BY ENAME DESC;
--SQL执行顺序
--写
SELECT 1 FROM 2 WHERE 3 GROUP BY 4 HAVING 5 ORDER BY 6
--执行
FROM 1 WHERE 2 GROUP BY 3 HAVING 4 SELECT 5 ORDER BY 6
SAL SAL SAL SAL SAL AS 工资 工资
SELECT SAL AS 工资 FROM EMP ORDER BY 工资;
--常用数据类型
字符串类型 VARCHAR2 必须要给长度 最大 4000
字符串可以进行拼接 必须要加'' 通过ASCII值进行比较
数字类型 NUMBER 可以给长度也可以不给 最大38 数字类型进行算数运算
NUMBER(A,B) A=总长度 B=小数点后长度
日期类型 DATE 不能给长度 不能相加，可以相减 相减结果为天数
日期可以加减数字 加10相当于加十天 谁的数字大谁就大
--CHAR和VARCHAR2的区别
--都是字符串类型
CHAR 不可变长度 占用空间大/效率高
长度10 ABCD 实际长度为4 CHAR会填充空格 最终长度10
VARCHAR2 可变长度 占用空间小/效率低
长度10 ABCD 实际长度为4 VARCHAR2不会填充空格 最终长度4
SELECT LENGTH(NAME1) AS 定长,LENGTH(NAME2) AS 可变长
FROM QQQQ;
SELECT * FROM QQQQ;
SELECT * FROM QQQQ FOR UPDATE;
CREATE TABLE QQQQ(
NAME1 CHAR(10),
NAME2 VARCHAR2(10)
);
小练习：
1.显示所有的姓名、工种、工资和奖金，按工种降序排列，
若工种相同则按工资升序排列
SELECT ENAME,JOB,SAL,COMM FROM EMP ORDER BY JOB DESC,
SAL ASC ;
2.查询员工的姓名和入职日期，并按入职日期从先到后进行排列
SELECT ENAME,HIREDATE FROM EMP ORDER BY HIREDATE;
3.查询EMP表中 各部门工资大于2000的 员工人数，
并且按人数从高到低排列
SELECT COUNT(1) AS 人数, DEPTNO
FROM EMP
WHERE SAL > 2000
GROUP BY DEPTNO
ORDER BY 人数 DESC;
4.查询出EMP表中所有HIREDATE 大于 1982年1月1日的员工信息。
SELECT * FROM EMP WHERE HIREDATE > DATE'1982-01-01';
-------------------------------------0527-----------------------
--字符函数
ASCII
--只求首位的ASCII值 ASCII比较大小逐位比较
SELECT ASCII('9') FROM DUAL;
SELECT MAX(QQ) AS 字符串,MAX(QQH) AS 数字 FROM AZKM;
QQ 9
QQH 12
CREATE TABLE AZKM(
QQ VARCHAR2(20),
QQH NUMBER
);
SELECT * FROM AZKM FOR UPDATE;
DUAL --万能表 测试表
SELECT * FROM DUAL;
--CONCAT 拼接 1.函数名 2.函数有几个参数 3.每个参数的作用
--两个参数 函数之间可以相互嵌套
SELECT CONCAT('张三','李四') FROM DUAL;
SELECT CONCAT(CONCAT('张三','李四'),'王五') FROM DUAL;
--姓名和岗位进行拼接
SELECT CONCAT(ENAME,JOB) FROM EMP;
--姓名和岗位进行拼接分隔符
SELECT CONCAT(CONCAT(ENAME,'---'),JOB) FROM EMP;
--|| 管道拼接符
SELECT ENAME ||' ' || JOB ||'<=岗位工资=>'|| SAL FROM EMP;
--WM_CONCAT 可以讲一列数据拼接一行里显示，并且用逗号隔开
SELECT WM_CONCAT(ENAME) FROM EMP;
--LENGTH 求字符长度 一个参数
SELECT LENGTH('张三李四') FROM DUAL;
--求每个员工的姓名长度
SELECT LENGTH(ENAME),ENAME FROM EMP;
--LENGTHB 求字节长度 一个汉字占两个字节 其余类型没有区别
SELECT LENGTHB('ABCD') FROM DUAL;
--求张三李四的字节长度
SELECT LENGTHB('张三李四') FROM DUAL;
SELECT LENGTHB(ENAME) FROM EMP;
LPAD ---左填充 三个参数 LPAD(字符串,10,填充值)
SELECT LPAD('ABCD',9,'-') FROM DUAL;
SELECT 'A' || LPAD(ENAME,10,'-') FROM EMP;
RPAD ---右填充
SELECT RPAD('ABCD',9,'-') FROM DUAL;
UPPER --转大写 一个参数
SELECT UPPER('ABCD')FROM DUAL;
SELECT UPPER(ENAME) FROM EMP;
LOWER --转小写 一个参数
SELECT LOWER('ABCD') FROM DUAL;
SELECT LOWER(ENAME) FROM EMP;
TRIM --默认去掉字符串两边空格
SELECT TRIM(' A B C D ') FROM DUAL; --字符串中间空格无法去除
SELECT LENGTH(TRIM(' ABCD ')) FROM DUAL; --去空格后长度
SELECT LENGTH(' ABCD ') FROM DUAL; --去空格前长度
--去掉字符串两边指定字符
--TRIM('指定字符' FROM 字符串)
SELECT TRIM('=' FROM '====AB==CD=====') FROM DUAL;
--去掉姓名两边S 指定字符只能有一个字符
SELECT TRIM('S' FROM ENAME),ENAME FROM EMP;
LTRIM --默认去字符串左边空格
SELECT LTRIM(' ABCD ') FROM DUAL;
--去掉字符串左边指定字符
LTRIM(字符串,指定字符)
SELECT LTRIM('====ABCD====','=') FROM DUAL;
--去掉姓名左边的'S'
SELECT LTRIM(ENAME,'S'),ENAME FROM EMP;
--可以去掉多个指定字符
SELECT LTRIM('ABBAAABB','AB') FROM DUAL;
RTRIM --默认去字符串右边空格
SELECT RTRIM(' ABCD ') FROM DUAL;
--去掉字符串右边指定字符
RTRIM(字符串,指定字符)
SELECT RTRIM('====ABCD====','=') FROM DUAL;
--去掉姓名右边的'S'
SELECT RTRIM(ENAME,'S'),ENAME FROM EMP;
--可以去掉多个指定字符
SELECT RTRIM('ABBAAABB','AB') FROM DUAL;
INSTR --返回字符在字符串中出现的位置
INSERT(字符串,匹配字符,起始位置,匹配成功的次数)
--不管怎么样查找，返回的位置都是第一个字从左向右数
SELECT INSTR('ABCDEFDA','D',1,3) FROM DUAL;
--如果找不到字符返回数字零
--后两个参数不写，默认是1,1
SELECT INSTR('ABCDEFDA','D') FROM DUAL;
--负数用法 从右向左找
SELECT INSTR('ABCDEFDA','D',-1,1) FROM DUAL;
REPLACE--替换
'张三和李四是好朋友' '王五' '张三和王五是好朋友'
REPLACE(字符串,旧的值,新的值)
SELECT REPLACE('张三和李四是好朋友','李四','王五')
FROM DUAL;
把姓名中所有的S替换成埃斯
SELECT REPLACE(ENAME,'S','艾斯') ,ENAME FROM EMP;
SUBSTR --截取函数
张三跟李四 张三
SUBSTR(字符串,起始位置,截取长度)
SELECT SUBSTR('张三跟李四',2,2) FROM DUAL;
--不设置截取长度，默认向后截取全部内容
SELECT SUBSTR('张三跟李四',2) FROM DUAL;
--负数用法
SELECT SUBSTR('张三跟李四',-2,2) FROM DUAL;
SELECT SUBSTR(ENAME,1,1),ENAME FROM EMP;
INITCAP --讲字符串 首字母大写 其余字母小写
--一个参数用法
SELECT INITCAP('ABCD EFG') FROM DUAL;
SELECT INITCAP(ENAME),ENAME FROM EMP;
查询员工姓名以'S'结尾的员工信息，不允许使用like，
写出两种查询方法。
--1.0 SUBSTR
SELECT SUBSTR(ENAME,-1,1) ,ENAME FROM EMP
WHERE SUBSTR(ENAME,-1,1) = 'S';
--2.0 INSTR
SELECT INSTR(ENAME,'S',-1,1) ,ENAME,LENGTH(ENAME) FROM EMP
WHERE INSTR(ENAME,'S',-1,1) = LENGTH(ENAME);
--3.0 RTRIM
SELECT RTRIM(ENAME,'S'),ENAME FROM EMP
WHERE RTRIM(ENAME,'S') != ENAME;
1、在emp表中查询出姓名的第二个字母为A的记录。
SELECT * FROM EMP WHERE SUBSTR(ENAME,2,1) = 'A';
SELECT * FROM EMP WHERE INSTR(ENAME,'A',1,1) = 2;
2、显示员工姓名正好为5个字符的员工。
SELECT * FROM EMP WHERE LENGTH(ENAME)=5;
3、显示所有员工姓名的前三个字符。
SELECT SUBSTR(ENAME,1,3),ENAME FROM EMP;
4、显示所有员工的姓名，用 a 替换A。 ALLEN aLLEN
SELECT REPLACE(ENAME,'A','a'),ENAME FROM EMP;
5.首字符大写其余字符小写 俩种写法 SMITH Smith
1.
SELECT INITCAP(ENAME) FROM EMP;
2.
SELECT UPPER(SUBSTR(ENAME,1,1)) ||
LOWER(SUBSTR(ENAME,2)) AS NEW_NAME ,ENAME FROM EMP;
6.在截取出市区
SELECT * FROM DQ ;
--市区
SELECT SUBSTR(DZ,INSTR(DZ,'-')+1,
INSTR(DZ,'-',1,2) - INSTR(DZ,'-')-1
) AS 市区 FROM DQ ;
--省份
SELECT SUBSTR(DZ,1,INSTR(DZ,'-')-1) AS 省份 FROM DQ;
--区
SELECT SUBSTR(DZ,INSTR(DZ,'-',1,2)+1) AS 区 FROM DQ;
--数字函数
ABS --绝对值
SELECT ABS(-100) FROM DUAL;
SELECT ABS(SAL - 10000) FROM EMP;
MOD --求余数 两个
--MOD(A,B) A除以B余几
SELECT MOD(10,3) FROM DUAL;
SELECT MOD(SAL,2) FROM EMP;
POWER --幂次方
--POWER(A,B) 求A的B次方
SELECT POWER(23,4)FROM DUAL;
SELECT POWER(SAL,2) FROM EMP;
CEIL --向上取整
--只要小数点后有值就向个位数进一
SELECT CEIL(1.999991) FROM DUAL;
FLOOR --向下取整
--只要小数点后有值就会舍去
SELECT FLOOR(1999.99999999) FROM DUAL;
ROUND --四舍五入
--一个参数 默认四舍五入到整数位
SELECT ROUND(1.366) FROM DUAL;
--两个参数 ROUND（A,B） 四舍五入到小数点后几位
SELECT ROUND(18.666,1) FROM DUAL;
--负数用法 四舍五入到小数点前几位
SELECT ROUND(777.777,-2) FROM DUAL;
TRUNC --对数字进行截断
--一个参数 默认截断到整数位
SELECT TRUNC(1.999) FROM DUAL;
--两个参数 TRUNC（A,B） 截断到小数点后几位
SELECT TRUNC(1.999,2) FROM DUAL;
--负数用法 四舍五入到小数点前几位
SELECT TRUNC(999.999,-2) FROM DUAL;
--日期函数
SYSDATE --返回当前系统时间
SELECT SYSDATE FROM DUAL;
MONTHS_BETWEEN --计算两个日期相差的月份
--俩个参数 A,B A-B=几个月
SELECT MONTHS_BETWEEN(TO_DATE(20200202,'YYYYMMDD'),
TO_DATE(20180202,'YYYYMMDD')) AS 相差月份 FROM DUAL;
--小-大 得出的结果是负数
SELECT MONTHS_BETWEEN(TO_DATE(20180202,'YYYYMMDD'),
TO_DATE(20200202,'YYYYMMDD')) AS 相差月份 FROM DUAL;
--天数不一致 自动换算小数
SELECT ROUND(MONTHS_BETWEEN(TO_DATE(20200209,'YYYYMMDD'),
TO_DATE(20180215,'YYYYMMDD')),2) AS 相差月份 FROM DUAL;
TO_DATE --可以将数字/字符串转化为日期类型的值
YYYY年MM月DD天 HH24小时MI分钟SS秒
YYYYMMDD HH24MISS
SELECT TO_DATE('20200202','YYYYMMDD') FROM DUAL;
--年月日时分秒
SELECT TO_DATE('20200909 121212','YYYYMMDD HH24MISS') FROM DUAL;
ADD_MONTHS --月份加减
--两个参数 （A,B） A+B(数字)
--添加月份
SELECT ADD_MONTHS(TO_DATE('20230401','YYYYMMDD'),2)FROM DUAL;
--减去月份
SELECT ADD_MONTHS(TO_DATE('20230401','YYYYMMDD'),-2)FROM DUAL;
--NEXT_DAY 下一个星期日的日期是多少
SELECT NEXT_DAY(TO_DATE('20230527','YYYYMMDD'),'星期三') FROM DUAL;
--LAST_DAY 返回函数内月份的最后一天
SELECT LAST_DAY(TO_DATE('20230427','YYYYMMDD')) FROM DUAL;
ROUND --对日期进行四舍五入
SELECT ROUND(TO_DATE('20230727','YYYYMMDD'),'YYYY')
FROM DUAL;
--七月份之前归本年，七月份之后包括七月份归下一年
SELECT ROUND(TO_DATE('20230215','YYYYMMDD'),'MM')
FROM DUAL;
--16号之前归本月，16号之后包括16号归下个月
SELECT ROUND(TO_DATE('20230215 111212','YYYYMMDD HH24MISS'),'DD')
FROM DUAL;
--中午十二点之前归今天，十二点之后归明天
TRUNC --对日期进行截断
SELECT TRUNC(TO_DATE('20230727','YYYYMMDD'),'YYYY')
FROM DUAL;--对年进行截断
SELECT TRUNC(TO_DATE('20230727','YYYYMMDD'),'MM')
FROM DUAL;--对月份进行截断
SELECT TRUNC(TO_DATE('20230727','YYYYMMDD'),'DD')
FROM DUAL;--对天进行截断
--默认值就是DD
SELECT TRUNC(TO_DATE('20230727','YYYYMMDD'))
FROM DUAL;
--Q 季度 返回当前季度的第一天
SELECT TRUNC(TO_DATE('20230627','YYYYMMDD'),'Q')
FROM DUAL;
--转化函数
TO_NUMBER --将字符转化为数字类型
SELECT * FROM AZKM ORDER BY TO_NUMBER(QQ);
--求最大值
SELECT MAX(TO_NUMBER(QQ)),MAX(QQH) FROM AZKM
TO_CHAR --将数据转化为字符串类型
SELECT TO_CHAR(EMPNO) FROM EMP1
ORDER BY TO_CHAR(EMPNO); --将员工编号转化为字符串类型
--将日期转化为字符串
SELECT TO_CHAR(TO_DATE('20230627','YYYYMMDD'),'YYYY') FROM DUAL;
--提取年份
SELECT TO_CHAR(TO_DATE('20230627','YYYYMMDD'),'MM') FROM DUAL;
--提取月份
SELECT TO_CHAR(TO_DATE('20230627','YYYYMMDD'),'DD') FROM DUAL;
--提取天数
SELECT TO_CHAR(SYSDATE,'D') FROM DUAL;--一周的第一天
SELECT TO_CHAR(TO_DATE('20230115','YYYYMMDD'),'DDD') FROM DUAL;
--一年的第几天
SELECT TO_CHAR(TO_DATE('20230504','YYYYMMDD'),'DAY') FROM DUAL;
--返回星期几
SELECT TO_CHAR(TO_DATE('20231004','YYYYMMDD'),'Q') FROM DUAL;
--返回日期所在季度
-----------------------0529-------------------------------
--流程控制函数
NVL2
--对空值进行转换 还可以对非空进行转换
--NVL2(判断字段,非空的返回值,为空的返回值)
--两个返回值的数据类型保持一致
SELECT NVL2(COMM,'我不是空值','我是空值'),COMM FROM EMP;
--CASE WHEN 判断
CASE WHEN 判断表达式 THEN 条件满足返回值 ELSE 其余情况 END
SELECT CASE WHEN 10>5 THEN '你好' ELSE '再见' END FROM DUAL;
--ELSE '字符串' 字段 不写默认返回空值
--两个返回值数据类型保持一致 以then前面的数据类型为准
SELECT CASE WHEN ENAME = 'SMITH' THEN '张三'
ELSE '李四' END,ENAME FROM EMP;
--多个判断条件
--SMITH=张三 SCOTT=李四 KING = 王五
SELECT CASE WHEN ENAME = 'SMITH' OR ENAME = 'SCOTT'
THEN '张三' END,ENAME FROM EMP;
--
SELECT CASE
WHEN ENAME = 'SMITH' THEN
'张三'
WHEN ENAME = 'SCOTT' THEN
'李四'
WHEN ENAME = 'KING' THEN
'王五'
ELSE
ENAME
END AS 新名字,
ENAME
FROM EMP;
--WHEN 使用函数
SELECT CASE WHEN SUBSTR(ENAME,1,1)='S' THEN '员工姓S'
ELSE '无名氏' END ,ENAME FROM EMP;
--工资评级 3000以上评为高级 2000 以上中级 1000以上低级
--其余需要努力
--满足上一个条件的数据不会参与到下一个条件
SELECT CASE WHEN SAL > 3000 THEN '高级'
WHEN SAL > 2000 THEN '中级'
WHEN SAL > 1000 THEN '低级'
ELSE '仍需努力' END 工资等级,
SAL FROM EMP;
--第二种写法 判断等于
SELECT CASE ENAME --要判断的字段
WHEN 'SMITH' --判断的值
THEN '赵四'
ELSE ENAME
END, ENAME FROM EMP;
--DECODE 流程空值 跟case when很像 oracle独有
--DECODE(判断字段,判断值,条件成立返回值,其余情况返回值) 等于
-- WHEN THEN ELSE
SELECT DECODE(JOB,'CLERK','店员','老板') ,JOB FROM EMP;--1.0
SELECT DECODE(JOB,'CLERK','店员',JOB) ,JOB FROM EMP;--2.0
SELECT DECODE(JOB,'CLERK','店员') ,JOB FROM EMP;--3.0
--SMITH=张三 SCOTT=李四 KING = 王五
--在上一个返回值之后接着写条件
SELECT DECODE(ENAME,'SCOTT','张三','SMITH','李四',
'KING','王五',ENAME),ENAME FROM EMP;
SELECT * FROM EMP;
1.假如工资在两千以上才算及格，那么求整个公司工资的及格率
SELECT ROUND(COUNT(CASE WHEN SAL > 2000 THEN '及格' END) /
COUNT(*)*100,2)||'%' AS 及格率 FROM EMP;
2.在员工表中查询出员工的工资，并计算应交税款：
如果工资小于1000,税率为0，
如果工资大于等于1000并小于2000，税率为10％，
如果工资大于等于2000并小于3000，税率为15％，
如果工资大于等于3000，税率为20％
SELECT CASE WHEN SAL >= 3000 THEN SAL * 0.2
WHEN SAL >= 2000 THEN SAL * 0.15
WHEN SAL >= 1000 THEN SAL * 0.1
ELSE 0 END 应交税款 ,SAL FROM EMP;
--行列转换
SELECT * FROM TEST_TABLE;
T=年 Q=季度 AMT=销售额
Y Q1 Q2 Q3 Q4
2015 100 110 130 100
2016 200 150 100 300
SELECT Y,AVG(CASE WHEN Q=1 THEN AMT END) AS Q1,
MAX(CASE WHEN Q=2 THEN AMT END) AS Q2,
MIN(CASE WHEN Q=3 THEN AMT END) AS Q3,
SUM(CASE WHEN Q=4 THEN AMT END) AS Q4
FROM TEST_TABLE GROUP BY Y;
--DECODE
SELECT Y, SUM(DECODE(Q,1,AMT)) AS Q1,
SUM(DECODE(Q,2,AMT)) AS Q2,
SUM(DECODE(Q,3,AMT)) AS Q3,
SUM(DECODE(Q,4,AMT)) AS Q4
FROM TEST_TABLE GROUP BY Y;
--PIVOT 列转行函数
SELECT * FROM TEST_TABLE PIVOT(
MAX(AMT) FOR Q IN(1 AS Q1,2 AS Q2,3 AS Q3,4 AS Q4)
); --IN(字段里存在的值)
/* 开始多行注释
SELECT * FROM TEST_TABLE PIVOT(
聚合函数 FOR 需要列转行的字段 IN(
值 AS 字段名 值2 AS 字段名2
)
)
结束多行注释
*/
--行转列 UNPIVOT
SELECT * FROM TEST_TABLE1 UNPIVOT(
AMT FOR Q IN (Q1 AS 1,Q2 AS 2,Q3 AS 3,Q4 AS 4)
);
/*
SELECT * FROM TEST_TABLE1 UNPIVOT(
存放值的字段 FOR 行专列后生成的新字段 IN
(Q1 AS 1,Q2 AS 2,Q3 AS 3,Q4 AS 4)
需要进行行转列的字段名
);
*/
CREATE TABLE TEST_TABLE (
Y NUMBER,
Q NUMBER,
AMT NUMBER
);
INSERT INTO TEST_TABLE VALUES(2015,1,100);
INSERT INTO TEST_TABLE VALUES(2015,2,110);
INSERT INTO TEST_TABLE VALUES(2015,3,130);
INSERT INTO TEST_TABLE VALUES(2015,4,100);
INSERT INTO TEST_TABLE VALUES(2016,1,200);
INSERT INTO TEST_TABLE VALUES(2016,2,150);
INSERT INTO TEST_TABLE VALUES(2016,3,100);
INSERT INTO TEST_TABLE VALUES(2016,4,300);
--TEST_TABLE1建表语句
CREATE TABLE TEST_TABLE1 AS
SELECT Y,AVG(CASE WHEN Q=1 THEN AMT END) AS Q1,
MAX(CASE WHEN Q=2 THEN AMT END) AS Q2,
MIN(CASE WHEN Q=3 THEN AMT END) AS Q3,
SUM(CASE WHEN Q=4 THEN AMT END) AS Q4
FROM TEST_TABLE GROUP BY Y;
--表关联
3 5 6 1
SELECT * FROM EMP,DEPT; --没有关联条件
SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO;--不常用
--关联方式 内关联 INNER JOIN
--右外关联 RIGHT JOIN 左外关联 LEFT JOIN 全外关联 FULL JOIN
3 5 6 0
SELECT * FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
--内关联 INNER 可以省略
SELECT * FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
--左关联 左边的表是主表
SELECT * FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
--右关联 匹配不上的数据填充为空值
SELECT * FROM EMP E FULL JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
--全外连接 显示两个表所有的数据
SELECT * FROM LJB1 A FULL JOIN LJB B ON A.B = B.A ;
SELECT * FROM LJB;
1 1
1 3
2 4
3 5
5 7
7 8
8 9
全外连接
1 1
1 1
3 3
2 空
空 4
5 5
7 7
8 8
空 9
左外连接
1 1
1 1
2 空
3 3
5 5
7 7
8 8
右外连接
1 1
1 1
3 3
空 4
5 5
7 7
8 8
空 9
内关联
1 1
1 1
3 3
5 5
7 7
8 8
--关联条件 没有数量限制 数据类型 多个关联条件 AND OR
SELECT * FROM EMP E JOIN DEPT D ON E.ENAME = D.DNAME OR
E.DEPTNO = D.DEPTNO;
--关联条件之后筛选 WHERE写在ON之后
SELECT * FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE E.DEPTNO = 30;
--WHERE 和ON 的区别 在内连接中没有区别
SELECT * FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO
AND E.DEPTNO = 30;
----WHERE 和ON 的区别
--在外关联中where会过滤掉主表数据 on不过滤主表数据
SELECT * FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE E.DEPTNO = 30;
--ON
SELECT * FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO = D.DEPTNO
AND D.DEPTNO = 30;
--非等值连接
SELECT * FROM EMP E JOIN DEPT D ON E.DEPTNO != D.DEPTNO;
--自连接 自己连自己
SELECT * FROM EMP A JOIN EMP B ON A.ENAME = B.ENAME;
--当显示字段名重复的字段时，需要声明使用的表
SELECT E.DEPTNO,D.* FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
SELECT * FROM SALGRADE;
--多表关联
SMITH 800 销售部门 北京 1
SELECT E.ENAME, E.SAL, S.GRADE 工资等级, D.DNAME, D.LOC
FROM EMP E
JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
JOIN SALGRADE S
ON E.SAL > S.LOSAL
AND E.SAL <= S.HISAL;
CREATE TABLE QD (
NAME VARCHAR2(20));
SELECT ASCII('甲') FROM QD; 1 2 3 4
甲 1乙2丙3丁4
甲乙丙丁
321
SELECT * FROM EMP ;
小练习 请用一句SQL求出所有球队的比赛组合
SELECT * FROM QD A JOIN QD B ON A.NAME > B.NAME;
查寻出工资比SCOTT高的员工信息
SELECT A.* FROM EMP A JOIN EMP B ON
B.ENAME = 'SCOTT' AND A.SAL > B.SAL;
查询员工编号，员工姓名，领导编号，领导姓名
SELECT A.EMPNO 领导编号,
A.ENAME 领导姓名,
B.EMPNO 员工编号,
B.ENAME 员工姓名
FROM EMP A
RIGHT JOIN EMP B
ON A.EMPNO = B.MGR;
查询入职日期早于其直接上级领导的所有员工信息
SELECT B.*
FROM EMP A
JOIN EMP B
ON A.EMPNO = B.MGR AND A.HIREDATE > B.HIREDATE;
查询所有部门及其员工信息，包括那些没有员工的部门40
SELECT * FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
查询所有工种为CLERK的员工的姓名及其部门名称
SELECT ENAME,DNAME FROM EMP E JOIN DEPT D ON
E.DEPTNO = D.DEPTNO
WHERE JOB = 'CLERK';
查询各个部门的详细信息以及部门人数、部门平均工资
SELECT D.*, COUNT(E.EMPNO) 人数, AVG(SAL) 平均工资
FROM EMP E
RIGHT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO GROUP BY D.DEPTNO,D.DNAME,D.LOC;
查询最低工资低于2000的部门名称及其员工名称
SELECT E.ENAME,D.DNAME FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE SAL < 2000;
查询所有员工工资都在900~3000之间的部门的信息
SELECT MAX(SAL), MIN(SAL), D.DEPTNO, D.DNAME, D.LOC
FROM EMP E
JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
GROUP BY D.DEPTNO, D.DNAME, D.LOC
HAVING MAX(SAL) <= 3000 AND MIN(SAL) >= 900;
如何用左连接实现内连接的效果
--对从表的主键列取非空 主键 非空且唯一
SELECT * FROM DEPT D JOIN EMP E ON
E.DEPTNO = D.DEPTNO; --内连接
--外连接
SELECT * FROM EMP E LEFT JOIN DEPT D ON
E.DEPTNO = D.DEPTNO WHERE E.EMPNO IS NOT NULL;
--表关联时 用小表关联大表 小表作为驱动表
--内连接 Oracle会自动用数据量少的表作为驱动表
--少用SELECT *
--GROUP BY 去重高于DISTINCT
--避免隐式转换 耗费性能 导致索引失效
--少用NOT IN /NOT EXISTS会导致索引失效
SELECT 100 + '300' FROM DUAL;
--------------------0530-----------------------------------
--子查询
--查询工资比SCOTT高的员工信息
SELECT * FROM EMP WHERE SAL >
(SELECT SAL FROM EMP WHERE ENAME = 'SCOTT');
--FROM后跟子查询
--把子查询当作一张表来使用
--当子查询中使用函数一定要起别名
SELECT * FROM (SELECT E.ENAME,LENGTH(ENAME) AS 姓名长度 FROM EMP E JOIN
DEPT D ON E.DEPTNO = D.DEPTNO
) A WHERE A.姓名长度 > 4
--WHERE 后跟子查询
跟SMITH从事相同工作的员工信息
SELECT * FROM EMP WHERE JOB =
(SELECT JOB FROM EMP WHERE ENAME = 'SMITH')
AND ENAME != 'SMITH'
--单行子查询 查询结果为单行的子查询
--多行子查询 查询结果为多行的子查询
跟SMITH,SCOTT从事相同工作的员工信息
SELECT * FROM EMP WHERE JOB IN
(SELECT JOB FROM EMP WHERE ENAME = 'SMITH' OR ENAME = 'SCOTT');
--ANY 等同于IN/OR 等于子查询返回结果中任意一个结果即可
SELECT * FROM EMP WHERE JOB = ANY
(SELECT JOB FROM EMP WHERE ENAME = 'SMITH' OR ENAME = 'SCOTT');
跟SMITH,SCOTT从事不相同工作的员工信息
SELECT * FROM EMP WHERE JOB NOT IN
(SELECT JOB FROM EMP WHERE ENAME = 'SMITH' OR ENAME = 'SCOTT');
--ALL
--等同于NOT IN 不等于子查询返回的所有结果
SELECT * FROM EMP WHERE JOB != ALL
(SELECT JOB FROM EMP WHERE ENAME = 'SMITH' OR ENAME = 'SCOTT');
--SELECT 后跟子查询 单行子查询
--半连接
SELECT ENAME,(SELECT SAL FROM EMP B WHERE A.EMPNO = B.EMPNO )
FROM EMP A ;
--显示员工编号，员工姓名，领导姓名，领导编号
SELECT A.EMPNO,A.ENAME,A.MGR,
(SELECT ENAME FROM EMP B WHERE B.EMPNO = A.MGR ) 领导姓名
FROM EMP A;
--EXISTS 存在
--NOT EXISTS 不存在
--EMP和DEPT中都存在部门编号
--IN 判断条件两遍字段数量要一致数据类型一致
SELECT * FROM DEPT WHERE DEPTNO IN (
SELECT DEPTNO FROM EMP
);
--EXISTS 半连接 10 20 30 40
SELECT * FROM DEPT D WHERE EXISTS (
SELECT 1 FROM EMP E WHERE E.DEPTNO = 30
);
--IN/EXISTS可以互转
--跟SMITH从事相同岗位的员工信息
SELECT * FROM EMP A WHERE EXISTS(
SELECT 1 FROM EMP B WHERE B.ENAME='SMITH'
AND A.JOB = B.JOB
)AND ENAME <> 'SMITH';
小练习：查询EMP表中每个部门的最低工资的员工信息
SELECT * FROM EMP1 A WHERE SAL IN
(SELECT MIN(SAL) FROM EMP1 B WHERE A.DEPTNO = B.DEPTNO GROUP BY DEPTNO);
SELECT * FROM EMP1 FOR UPDATE;
查询工资比SMITH员工工资高的所有员工信息
SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP
WHERE ENAME = 'SMITH')
查询工资比SCOTT或者SMITH高的员工信息
--1.0
SELECT * FROM EMP WHERE SAL > ANY(SELECT SAL FROM EMP
WHERE ENAME = 'SCOTT' OR ENAME = 'SMITH');
--2.0
SELECT * FROM EMP WHERE SAL > (SELECT MIN(SAL) FROM EMP
WHERE ENAME = 'SCOTT' OR ENAME = 'SMITH');
查询所有员工的姓名及其直接上级的姓名(子查询)
查询工资高于公司平均工资的所有员工信息
SELECT * FROM EMP WHERE SAL > (SELECT AVG(SAL)
FROM EMP);
列出工资等于30号部门中某个员工工资的所有员工的姓名和工资。
SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL IN (
SELECT SAL FROM EMP WHERE DEPTNO = 30
) AND DEPTNO != 30;
查询工资高于30号部门中工作的所有员工的工资的员工姓名和工资
SELECT * FROM EMP WHERE SAL > ALL(
SELECT SAL FROM EMP WHERE DEPTNO = 30);
SELECT * FROM EMP WHERE SAL > (
SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30);
查询工资高于30号部门中工作的任意员工的工资的员工姓名和工资
SELECT * FROM EMP WHERE SAL > (
SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 30)
AND DEPTNO != 30;
ANY
查询所有员工工资都大于1000的部门的信息
SELECT * FROM DEPT WHERE DEPTNO IN (
SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING MIN(SAL) > 1000);
查询所有员工工资都大于1000的部门的信息及其员工信息
SELECT *
FROM DEPT D
JOIN EMP E
ON D.DEPTNO = E.DEPTNO
WHERE D.DEPTNO IN
(SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING MIN(SAL) > 1000);
TRUE FALSE 永真 1=1
SELECT * FROM DEPT D WHERE EXISTS (
SELECT 1 FROM EMP E WHERE E.DEPTNO = D.DEPTNO
);
SELECT * FROM DEPT D WHERE DEPTNO IN (
SELECT DEPTNO FROM EMP E
);
IN 先走子查询 把子查询的储存在内存中在跟主查询进行比较
子查询数据量少的时候 IN的效率高
EXISTS 先走主查询再去跟子查询进行比较，条件满足返回主查询结果
子查询数据量多的时候 EXISTS的效率高
SELECT * FROM EMP;
SELECT * FROM DEPT;
小练习 查询从事同一种工作但不属于同一部门的员工信息
SELECT * FROM EMP A WHERE EXISTS (
SELECT * 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 B.ENAME = 'SMITH'
AND B.JOB = A.JOB
)AND A.ENAME != 'SMITH';
查询工资比工司平均工资水平高的员工信息
SELECT * FROM EMP A WHERE EXISTS
(SELECT 1 FROM EMP B HAVING A.SAL > AVG(B.SAL));
查询在NEW YORK上班的员工名字
SELECT ENAME FROM EMP A WHERE EXISTS
(SELECT 1 FROM DEPT B WHERE B.DEPTNO = A.DEPTNO
AND B.LOC='NEW YORK');
查询和10号部门中，岗位相同的其他员工的信息
SELECT * FROM EMP A WHERE EXISTS(
SELECT 1 FROM EMP B WHERE B.DEPTNO=10 AND A.JOB = B.JOB
)AND A.DEPTNO != 10;
查询和SMITH是相同岗位的但是部门是ACCOUNTING的员工信息
SELECT *
FROM EMP A
JOIN DEPT D
ON A.DEPTNO = D.DEPTNO
WHERE EXISTS (SELECT *
FROM EMP B
WHERE B.ENAME = 'SMITH'
AND A.JOB = B.JOB)
AND D.DNAME = 'ACCOUNTING';
SELECT * FROM (SELECT ENAME FROM EMP) A
JOIN
(SELECT * FROM DEPT) B
ON A.DEPTNO = B.DEPTNO;
--集合运算 并集 交集 补集
UNION ALL --并集 不去重
--上下两个结果集的字段数量数据类型要保持一致
SELECT ENAME,SAL FROM EMP
UNION ALL
SELECT JOB,SAL FROM EMP;
UNION --并集 去重
SELECT EMPNO,SAL FROM EMP
UNION
SELECT EMPNO,SAL FROM EMP
UNION
SELECT SAL ,MGR FROM EMP;
--交集 INTERSECT 显示两个结果集共有的记录
SELECT DEPTNO FROM EMP
INTERSECT
SELECT DEPTNO FROM DEPT;
--补集 MINUS 显示上一个结果集有但是下一个结果集没有的记录
SELECT DEPTNO FROM EMP
MINUS
SELECT DEPTNO FROM DEPT;
--开窗 分析函数 OVER()
SUM MAX AVG MIN COUNT
SELECT SUM(SAL) OVER() 总工资,
EMPNO,
MAX(SAL) OVER() 最高,
MIN(SAL) OVER() 最低,
AVG(SAL) OVER() 平均,
COUNT(*) OVER() 人数
FROM EMP;
--要求每个部门的最高薪资
SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO;
--MAX()OVER()
--PARTITION BY 分组 不去重
SELECT SUM(SAL)OVER(PARTITION BY DEPTNO),DEPTNO FROM EMP;
--ORDER BY 排序
SELECT MAX(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL),
SAL,DEPTNO FROM EMP;
--累加 SUM()OVER(ORDER BY ) *******
SELECT SUM(SAL)OVER(ORDER BY EMPNO),SAL,EMPNO FROM EMP;
--组内累加 SUM()OVER(PARTITION BY ORDER BY )
SELECT SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY EMPNO) AS 累加,
SAL,
EMPNO,
DEPTNO
FROM EMP;
--设置累加范围
SELECT SUM(SAL)OVER(ORDER BY EMPNO
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING --上一行到下一行
) AS 第一,
SUM(SAL)OVER(ORDER BY EMPNO
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS 第二, --上一行到当前行
SUM(SAL)OVER(ORDER BY EMPNO
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) 第三 ,
SAL,EMPNO FROM EMP;
--MAX(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL)
--取值范围 第一行到当前行
--MAX(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL)
--取值范围第一行到最后一行
SELECT * FROM EMP;
CURRENT ROW:当前行
2 PRECEDING:往前n行
2 FOLLOWING:往后n行
UNBOUNDED PRECEDING:表示从前面的起点
UNBOUNDED FOLLOWING:表示到后面的终点
--排序开窗函数 *******
ROW_NUMBER() 遇到相同值也会给不一样的序列号 123456
RANK() 可能会生成一组不连续的序列号 遇到相同值会给一样的序列号 123356
DENSE_RANK() 密集型排序，生成一组连续的序列号
遇到相同值会给一样的序列号 123345
--OVER(里必须写ORDER BY )
SELECT ROW_NUMBER()OVER(ORDER BY SAL DESC),SAL FROM EMP;
--组内排序
SELECT ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC),
SAL,DEPTNO
FROM EMP;
--RANK()
SELECT RANK()OVER(ORDER BY SAL DESC),SAL FROM EMP;
--DENSE_RANK()
SELECT DENSE_RANK()OVER(ORDER BY SAL DESC),SAL FROM EMP;
--求出公司工资最高的员工信息
--HWERE不能和开窗函数搭配使用也不能跟聚合函数
SELECT * FROM (
SELECT ROW_NUMBER()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) PM,B.* FROM EMP B) A
WHERE A.PM = 2
;
--ROW_NUMBER 去重
SELECT * FROM (
SELECT ROW_NUMBER()OVER(PARTITION BY SAL ORDER BY
SAL ) PM,SAL FROM EMP) A WHERE A.PM = 1;
--ROW_NUMBER
有登陆表LOGINTEST, 表中字段：USER_ID 用户名，LOG_DATE 登录日期，查找连续登录三天的用户信息
SELECT COUNT(A.JG) 次数,A.USER_ID FROM (
SELECT L.*,
ROW_NUMBER() OVER(PARTITION BY L.USER_ID ORDER BY L.LOG_DATE) PM,
L.LOG_DATE - ROW_NUMBER() OVER(PARTITION BY L.USER_ID ORDER BY L.LOG_DATE) JG
FROM LOGINTEST L ) A
GROUP BY A.USER_ID,A.JG HAVING COUNT(A.JG) >=3;
--LEAG
--1.0 不是很智能
SELECT * FROM (
SELECT L.*,
LEAD(L.LOG_DATE, 1) OVER(PARTITION BY L.USER_ID ORDER BY L.LOG_DATE) A,
LEAD(L.LOG_DATE, 2) OVER(PARTITION BY L.USER_ID ORDER BY L.LOG_DATE) B
FROM LOGINTEST L ) Q
WHERE Q.A - Q.LOG_DATE = 1 AND Q.B - Q.A = 1
;
---2.0 推荐
SELECT * FROM (
SELECT L.*,
LEAD(L.LOG_DATE, 2) OVER(PARTITION BY L.USER_ID ORDER BY L.LOG_DATE) B
FROM LOGINTEST L ) Q
WHERE Q.B - Q.LOG_DATE = 2
;
求出每个部门工资第二高的员工信息
求出每个部门工资第二高的员工信息（不能使用开窗函数）
----------------------------------0531--------------------------
偏移函数 LAG 向下 LEAD 向上
LAG(偏移字段,偏移长度,缺省值/默认值)OVER(PARTITION BY ORDER BY )
LEAD(偏移字段,偏移长度,缺省值/默认值)OVER(PARTITION BY ORDER BY )
SELECT LAG(ENAME,2)OVER(ORDER BY ENAME),ENAME FROM EMP;
SELECT LEAD(ENAME,2)OVER(ORDER BY ENAME),ENAME FROM EMP;
--组内偏移
SELECT LEAD(ENAME,1)OVER( PARTITION BY DEPTNO
ORDER BY ENAME),ENAME,DEPTNO FROM EMP;
DATE - DATE +1
华为 20230303 20230306
华为 20230323
小米 20230605 20230624
小米 20230624
大米 20230708 20230712
大米 20230712
--求占比
SELECT ratio_to_report(SAL)OVER(),SAL FROM EMP;
--OVER()内不写内容时求整张表的占比
--窗口说明内不允许使用ORDER BY
--每个员工占部门总工资的占比
SELECT ratio_to_report(SAL)OVER(PARTITION BY DEPTNO),
SAL,DEPTNO FROM EMP;
SELECT SUM(SAL) FROM EMP;
--求第一行值
FIRST_VALUE(展示姓名就写姓名字段)OVER(PARTITION BY ORDER BY )
--求出整个公司工资最高的员工姓名
SELECT FIRST_VALUE(ENAME)OVER(ORDER BY SAL DESC) FROM EMP;
--求出整个公司工资最高的员工姓名
SELECT FIRST_VALUE(ENAME)OVER(PARTITION BY DEPTNO ORDER BY SAL DESC),
DEPTNO,SAL FROM EMP;
--求最后一行的值
LAST_VALUE
--必须搭配ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
--默认取值范围是第一行到当前行
--求出整个公司工资最低员工姓名
SELECT LAST_VALUE(ENAME)OVER(ORDER BY SAL DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
),SAL,ENAME FROM EMP;
--求出每个部门工资最低员工姓名
SELECT LAST_VALUE(ENAME)OVER(PARTITION BY DEPTNO ORDER BY SAL DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
),SAL,ENAME FROM EMP;
SELECT * FROM EMP;
--切片
NTILE
数据量/分组数+1
--将工资划分为四个等级
SELECT NTILE(3)OVER(ORDER BY SAL) AS 工资等级,SAL FROM EMP;
--将每个部门工资划分为2个等级
SELECT NTILE(2)OVER(PARTITION BY DEPTNO ORDER BY SAL) AS 工资等级,SAL FROM EMP;
1.显示各部门员工的工资，并附带显示该部门的最高工资。
2.按照deptno分组，然后计算每组工资的总和
3.对各部门进行分组，并附带显示第一行至当前行的工资汇总，根据员工编号累加
当前行至最后一行的汇总
当前行的上一行(rownum-1)到当前行的汇总
当前行的上一行(rownum-1)到当前行的下两行(rownum+2)的汇总
SELECT SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY EMPNO) AAA,
SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY EMPNO ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS BBB,
SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY EMPNO ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS CCC,
SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY EMPNO ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS DDD,
SAL,
EMPNO,
DEPTNO
FROM EMP;
-- 1、查询姓“猴”的学生名单
SELECT * FROM STUDENT WHERE SNAME LIKE'猴%';
-- 1.1查询姓名中最后一个字是聪的学生名单
SELECT * FROM STUDENT WHERE SNAME LIKE'%聪';
SELECT * FROM STUDENT WHERE SUBSTR(SNAME,-1,1) ='聪';
SELECT * FROM STUDENT
WHERE INSTR(SNAME,'聪',1,1)=LENGTH(SNAME);
RTRIM
-- 1.2查询姓名中带猴的学生名单
SELECT * FROM STUDENT WHERE SNAME LIKE'%猴%';
SELECT INSTR(SNAME,'猴'),SNAME FROM STUDENT WHERE
INSTR(SNAME,'猴') != 0;
-- 1.3、查询姓“孟”老师的个数
SELECT COUNT(TNAME) 人数 FROM TEACHER WHERE TNAME LIKE'孟%';
--2、查询课程编号为“0002”的总成绩
SELECT SUM(S.SSCORE) AS 总成绩 FROM SCORE S WHERE S.SCOURE = 2 ;
-- 2.1、查询选了课程的学生人数
SELECT COUNT(DISTINCT SID) 人数 FROM SCORE S
WHERE S.SSCORE IS NOT NULL;
-- 3、查询各科创建最高和最低分数 最终显示科目名称 最高 最低
SELECT C.CNAME,MAX(S.SSCORE),MIN(S.SSCORE) FROM SCORE S JOIN
COURSE C ON S.SCOURE = C.SCOURE
GROUP BY C.CNAME;
SELECT * FROM SCORE;
SELECT * FROM COURSE
-- 3.1、查询每门课程被选修学生数
SELECT COUNT(SID) 人数,SCOURE FROM SCORE GROUP BY SCOURE;
-- 3.2、查询男生，女生人数
SELECT COUNT(1),S.SGENDER FROM STUDENT S GROUP BY S.SGENDER ;
男 女
3 1
SELECT SUM(CASE WHEN S.SGENDER = '男' THEN 1 END) AS 男,
SUM(CASE WHEN S.SGENDER = '女' THEN 1 END) AS 女
FROM STUDENT S
-- 4、查询平均成绩大于70分学生的学号和平均成绩
SELECT AVG(SSCORE) 平均成绩,SID FROM SCORE GROUP BY SID
HAVING AVG(SSCORE) > 70;
-- 4.1、查询至少选修两门课程的学生学好
SELECT COUNT(SCOURE) 选课数,SID FROM SCORE GROUP BY SID
HAVING COUNT(SCOURE) >1;
-- 4.2、查询同名同性学生名单并统计同名人数
--同名同姓
SELECT SNAME,COUNT(1) FROM STUDENT GROUP BY SNAME
HAVING COUNT(1) > 1;
--同名同性
SELECT SNAME,COUNT(1) FROM STUDENT S GROUP BY SNAME,S.SGENDER
HAVING COUNT(1) > 1;
SELECT * FROM STUDENT FOR UPDATE;
-- 4.3、查询不及格的课程并按课程号从大到小排列
SELECT * FROM SCORE S WHERE S.SSCORE < 90 ORDER BY S.SCOURE DESC;
SELECT * FROM EMP WHERE 1=2;
-- 4.4、查询每门课程的平均成绩，结果按平均成绩升序排序，
--平均成绩相同时，按课程号降序排列
SELECT ROUND(AVG(S.SSCORE), 2) 平均成绩, S.SCOURE
FROM SCORE S
GROUP BY S.SCOURE
ORDER BY 平均成绩,S.SCOURE DESC;
-- 4.5、检索课程编号为“0003”且分数小于90的学生学号，
--结果按按分数降序排列
SELECT * FROM SCORE WHERE SCOURE = 3 AND SSCORE < 90
ORDER BY SSCORE DESC;
--4.6、统计每门课程的学生选修人数(超过2人的课程才统计)
--要求输出课程号和选修人数，查询结果按人数降序排序，
--若人数相同，按课程号升序排序
SELECT COUNT(S.SID) 人数,S.SCOURE FROM SCORE S GROUP BY S.SCOURE
HAVING COUNT(S.SID) > 2 ORDER BY 人数 DESC ,S.SCOURE;
-- 4.7、查询两门以上不及格课程的同学的学号及其平均成绩
--求出该学生不及格课程的平均成绩
--该学生所有课程的平均成绩
1.0
SELECT AVG(SSCORE),S.SID FROM SCORE S WHERE S.SID IN (
SELECT S.SID
FROM SCORE S
WHERE S.SSCORE < 90
GROUP BY S.SID
HAVING COUNT(S.SCOURE) > 2) GROUP BY S.SID;
2.0
SELECT COUNT(S.SCOURE), S.SID, AVG(S.SSCORE)
FROM SCORE S
WHERE S.SSCORE < 90
GROUP BY S.SID
HAVING COUNT(S.SCOURE) > 2;
-- 5、查询学生的总成绩并进行排序
SELECT SUM(S.SSCORE) 总成绩 FROM SCORE S GROUP BY S.SID
ORDER BY 总成绩;
-- 5.1、查询平均成绩大于60分的学生学号和平均成绩
SELECT AVG(S.SSCORE),S.SID FROM SCORE S GROUP BY S.SID
HAVING AVG(S.SSCORE) > 60;
--DQL语言 数据查询语言 SELECT GROUP BY ORDER BY
--DDL语言 数据定义语言 元数据 管理数据的数据 不能提交不能回滚
--ALTER TABLE修改表 CREATE TABLE 创建表 DROP TABLE 删除表
--TRUNCATE TABLE 清空表(数据)
--CREATE TABLE 创建表
/*
CREATE TABLE 表名(
字段名 数据类型,
字段名2 数据类型,
字段名2 数据类型
........
);*/
CREATE TABLE EMP_0531(
EMPNO NUMBER(38),
ENAME VARCHAR2(4000),
SR DATE,
AGE NUMBER
);
--创建用户
CREATE USER SCOTT1 IDENTIFIED BY 123456;
SELECT * FROM USER_USERS; --查看所有用户信息
--删除用户
DROP USER SCOTT1;
--修改用户
ALTER USER SCOTT1 IDENTIFIED BY 654321;
--根据查询结果创建 复制表
--CREATE TABLE 表名 AS 查询语句
CREATE TABLE EMP_XXXX AS
SELECT ENAME,EMPNO,SAL FROM EMP WHERE SAL > 2000;
--创建比原表多字段的情况
--复制EMP表数据，多一个字段公司名称 自动对焦公司
CREATE TABLE EMP_AAAA AS
SELECT E.*,'自动对焦公司' AS 公司名称,SYSDATE AS SR FROM EMP E;
--只复制表结构，不要表中数据
CREATE TABLE EMP_AAAA AS SELECT E.*,D.DNAME,D.LOC FROM EMP E JOIN
DEPT D ON E.DEPTNO = D.DEPTNO WHERE 1=2;
--DROP TABLE 表名; 不仅删除表中数据 删除表结构
DROP TABLE EMP_AAAA;
--TRUNCATE TABLE 清空表 仅删除表中数据
TRUNCATE TABLE EMP_AAAA;
--ALTER TABLE 修改表
--给表新加字段
SELECT * FROM EMP_0531;
--给EMP1新家年龄字段
ALTER TABLE EMP1 ADD AGE NUMBER(3);
--ALTER TABLE 表名 ADD 字段名 数据类型
--删除表中字段 字段删除后该字段的数据也会随之删除
--给EMP1删除年龄字段
COLUMN --列 字段
ALTER TABLE EMP1 DROP COLUMN EMPNO;
--ALTER TABLE 表名 DROP COLUMN 字段名;
--修改表名
--把EMP1改为EMP_0531
ALTER TABLE EMP1 RENAME TO EMP_0531;
--ALTER TABLE 表名 RENAME TO 新表名;
--修改字段名
SELECT * FROM EMP_0531;
把EMP_0531表中的AGE改为年龄
ALTER TABLE EMP_0531 RENAME COLUMN 年龄 TO AGE;
--ALTER TABLE 表名 RENAME COLUMN 旧字段名 TO 新字段名;
--修改字段的数据类型(长度)
--把AGE字段的数据类型改为字符串类型
ALTER TABLE EMP_0531 MODIFY AGE VARCHAR2(200);
--ALTER TABLE 表名 MODIFY 字段名 数据类型;
--DML 数据操作语言 增删改
--插入语句 INSERT INTO
--向EMP——0531表插入一条新数据
INSERT INTO EMP_0531 VALUES(9999,'张三','保安',7369,
TO_DATE(20230531,'YYYYMMDD'),9999,9999,90);
--INSERT INTO 表名 VALUES(值1,值2......);
--字段数量数据类型保持一致 一一对应 注意数据的长度
INSERT INTO EMP_0531 VALUES(9999,'张三','保安',7369,
TO_DATE(20230531,'YYYYMMDD'),9999,9999);
--字段数量不一致
INSERT INTO EMP_0531 VALUES(9999,'张三','保安',7369,
TO_DATE(20230531,'YYYYMMDD'),9999,'没有奖金',90);
--数据类型不一致
INSERT INTO EMP_0531 VALUES(9999,'张三','保安',7369,
TO_DATE(20230531,'YYYYMMDD'),99999999999,3000,90);
--数据长度过长
INSERT INTO EMP_0531 VALUES(9999,'保安','张三',7369,
TO_DATE(20230531,'YYYYMMDD'),9999,3000,90);
--值与字段之间的映射关系错误
--INSERT INTO 2.0
--指定字段插入数据 姓名 工资
INSERT INTO EMP_0531(ENAME,SAL,JOB) VALUES('王五',9999,'保洁');
--INSERT INTO 表名(字段一,字段二) VALUES(值1,值2);
--INSERT INTO 3.0
--INSERT INTO 表名 查询语句
--根据查询结果插入数据
INSERT INTO EMP_0531 SELECT E.EMPNO,E.ENAME,D.LOC,
D.DEPTNO,E.HIREDATE,E.SAL,E.COMM,D.DEPTNO FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;
--DELETE FROM 表名 FROM 可以省略不写
--TRUNCATE效率优于DELETE 但是TRUNCATE不能回滚 慎用
DELETE FROM EMP_0531; --清空表
--删除指定数据
--删除姓名为SMITH的员工信息
DELETE FROM EMP_0531 WHERE ENAME = 'SMITH' OR ENAME = 'SCOTT';
DELETE FROM EMP_0531 WHERE SAL > 2000;
--删除在纽约上班的员工信息
DELETE FROM EMP_0531 WHERE DEPTNO IN (
SELECT DEPTNO FROM DEPT D WHERE D.LOC = 'NEW YORK');
--UPDATE 表名 SET 要修改的值
--把史密斯的工资改为2000
UPDATE EMP_0531 SET SAL = 2000; --直接修改全表
UPDATE EMP_0531 SET SAL = 2000 WHERE ENAME = 'SMITH';
--我向把SCOTT的工资改成300，岗位改成保安
UPDATE EMP_0531 SET SAL = 300 WHERE ENAME = 'SCOTT';
UPDATE EMP_0531 SET JOB = '保安' WHERE ENAME = 'SCOTT';
--升级后2.0
--同时修改多个字段的值
UPDATE EMP_0531 SET SAL=90000,JOB = '总裁',MGR=NULL
WHERE ENAME = 'SCOTT';
--我要把BLAKE的工资加1000块钱
UPDATE EMP_0531 SET SAL = SAL+1000 WHERE ENAME = 'BLAKE';
--事务 是数据库中可以执行的一个逻辑程序单元
--COMMIT ROLLBACK
原子性 要么都成功要么都不成功
一致性 事务产生前后 数据应该保持逻辑一致
隔离性 持久性
200 -20 180
300 +20 320
INSERT INTO EMP_0531(EMPNO,SAL) VALUES(7777,9999);
INSERT INTO EMP_0531(EMPNO,SAL) VALUES(7777,9999);
INSERT INTO EMP_0531(EMPNO,SAL) VALUES(7777,9999);
INSERT INTO EMP_0531(EMPNO,SAL) VALUES(7777,9999);
SELECT * FROM EMP_0531 FOR UPDATE;
求每个部门工资第二高的员工信息
SELECT *
FROM (SELECT MAX(SAL) 最高, E.DEPTNO
FROM EMP E
LEFT JOIN (SELECT MAX(SAL) MAX, DEPTNO FROM EMP GROUP BY DEPTNO) A
ON E.SAL = A.MAX
AND E.DEPTNO = A.DEPTNO
WHERE A.DEPTNO IS NULL
GROUP BY E.DEPTNO) W
LEFT JOIN (SELECT * FROM EMP) B
ON W.最高 = B.SAL
AND W.DEPTNO = B.DEPTNO
;
--2.0补集
SELECT MAX(SAL),DEPTNO FROM
(SELECT SAL,DEPTNO FROM EMP
MINUS
SELECT MAX(SAL),DEPTNO FROM EMP GROUP BY DEPTNO)
GROUP BY DEPTNO;
---------------------0601------------------------------------------
--伪列
ROWNUM --在表中生成一列序号本身没有排序效果
SELECT A.*,ROWNUM FROM (
SELECT E.* FROM EMP E ORDER BY SAL) A;
--分页查询 在本层查询只能小于不能大于/等于 (数字一除外)
SELECT E.*,ROWNUM AS XH FROM EMP E WHERE ROWNUM = 1;
--显示EMP表中第五条数据到第十条
SELECT * FROM (
SELECT E.*,ROWNUM XH FROM EMP E) A
WHERE A.XH > 4 AND A.XH <11;
--MYSQL LIMIT 4,6
ROWID --每条数据生成一个物理地址 索引
--不会重复 两条数据完全一样ROWID也不会重复
SELECT E.*,ROWID FROM EMP E;
--可以通过ROWID查询数据
SELECT * FROM EMP WHERE ROWID = 'AAAR3sAAEAAAACXAAA';
--通过ROWID去重 两条数一模一样
CREATE TABLE TEST7 AS
SELECT '张三' AS NAME , '28' AS AGE , '男' AS SEX FROM DUAL
UNION ALL
SELECT '李四' AS NAME , '22' AS AGE , '女' AS SEX FROM DUAL
UNION ALL
SELECT '张三' AS NAME , '28' AS AGE , '男' AS SEX FROM DUAL;
DELETE FROM TEST7 WHERE ROWID NOT IN (
SELECT MAX(ROWID) FROM TEST7 GROUP BY NAME,AGE,SEX);
SELECT * FROM TEST7;
--正则表达式
^--匹配一个字符串的开始
$--匹配字符串的结尾
[]--用于指定一个匹配列表可能会出现的值一个
+ --匹配一个或多个出现
.--任意单个字符
{M} 匹配m次。
{M，} 至少匹配m次。
{M，N} 至少匹配m次，但不多于n次。
REGEXP_LIKE
判断字段 --第一个参数
正则表达式 --第二个参数
是否区分大小写 --第三个参数 i 不区分 c 区分大小 默认
找出纯数字并且是 4位的
纯字母 4位
找出纯字母或者纯数字
找出有连续三个3的值或者连续三个数字的
SELECT *
FROM (
SELECT 'BcDD' AS CN FROM DUAL
UNION ALL
SELECT '122q34'AS CN FROM DUAL
UNION ALL
SELECT '4333'AS CN FROM DUAL
) T
WHERE REGEXP_LIKE(CN,'\d{3}333')
--WHERE REGEXP_LIKE(CN,'(^[A-Z]+$)|(^\d+$)','i')
-- | 相当于OR
--WHERE REGEXP_LIKE(CN,'^[A-Z]+$','i') OR REGEXP_LIKE(CN,'^\d+$','i')
--WHERE REGEXP_LIKE(CN,'^[A-Z]{4}$','i')
--WHERE REGEXP_LIKE(CN,'^[A-Za-z]{4}$')
--WHERE REGEXP_LIKE(CN,'^\d{4}$','i');
1. 查询 value 中以 1 开头 60 结尾的记录并且长度是 7 位
SELECT * FROM MOHU WHERE REGEXP_LIKE(VALUE,'^1....60$');
2. 查询 value 中以 1 开头 60 结尾的记录并且长度是 7 位
且全部是数字的记录
SELECT * FROM MOHU WHERE REGEXP_LIKE(VALUE,'^1\d{4}60$') ;
3. 查询 value 中不包含任何数字的记录
SELECT * FROM MOHU WHERE REGEXP_LIKE(VALUE,'^\D+$');
4. 查询 value 中不是纯数字的记录
SELECT * FROM MOHU WHERE REGEXP_LIKE(VALUE,'\D');
SELECT * FROM MOHU WHERE NOT REGEXP_LIKE(VALUE,'^\d+$')
5. 查询以 12 或 1b 开头的记录，区分大小写
SELECT * FROM MOHU WHERE REGEXP_LIKE(VALUE,'^1[2b]');
6. 查询以 12 或 1b 开头的记录，不区分大小写
SELECT * FROM MOHU WHERE REGEXP_LIKE(VALUE,'^1[2b]','i');
7. 查询所有均为 小写字母 或 数字的记录
SELECT * FROM MOHU WHERE
REGEXP_LIKE(VALUE, '(^[a-z]+$)|(^\d+$)');
--REGEXP_INSTR
第一个参数 判断字段/字符串
第二个 正则表达式
第三个 起始位置 1
第四个参数 匹配成功的次数 1
第五个参数 0返回字符串第一个值的位置 0以外的任意数字最后一个值的位置+1
第六个参数 'i'不区分大小写'c' 区分
'NDSAD312NVNDSK'
--找数字第一次出现的位置
SELECT REGEXP_INSTR('ND312NVN34DSK','\d+',1,1,1,'i') FROM DUAL;
'123ad456AD'找到AD出现的位置 区分大小写
SELECT REGEXP_INSTR('123ad456AD','AD') FROM DUAL;
'123ad456AD'找到AD出现的位置 不区分大小写
SELECT REGEXP_INSTR('123ad456AD','AD',1,1,0,'i') FROM DUAL;
REGEXP_SUBSTR
第一个参数 字段/字符串
第二个 正则表达式
第三个参数 起始位置
第四个参数 匹配成功次数
第五个参数 'i'不区分大小写'c' 区分
'李一,李二,李三,李四,李五'
--找出第三个人的姓名
SELECT REGEXP_SUBSTR('李一,李二,李三,李四,李五','[^,]+',
1,3) FROM DUAL;
SELECT REGEXP_SUBSTR(DZ,'[^\-]+',1,3) FROM DQ;
小练习
SELECT * FROM JIEQU ;
--拆分出邮箱
SELECT REGEXP_SUBSTR(YX,'\w+'),
REGEXP_SUBSTR(YX,'\@\w+\.\w+')
FROM JIEQU;
--筛选出固定电话和手机号码
固定电话格式：0开头 + 2到3位任意数字 + '-' + 6到7位任意数字
手机号码格式：1开头 + 10位任意数字（11位）
SELECT * FROM DIANHUA;
SELECT REGEXP_SUBSTR(NU,'^0\d{2,3}-\d{6,7}$') 固定电话,
REGEXP_SUBSTR(NU,'^1\d{10}$')
FROM DIANHUA;
SELECT *
FROM DIANHUA WHERE REGEXP_LIKE(NU,'^0\d{2,3}-\d{6,7}$');
REGEXP_REPLACE
第一个参数 字段/字符串
第二个 正则表达式
第三个参数 新的值
第四个参数 起始位置(从第几个字开始替换)
第五个参数 匹配成功的次数
第六个参数 'i'不区分大小写'c' 区分
--将员工编号里的所有数字替换成A
SELECT REGEXP_REPLACE(EMPNO,'\d','A',2,2),EMPNO FROM EMP;
--------------------0602----------------------------------------
小练习
用'#'替换字符串中的数字0,9
'SDF45SDF0045D999'
SELECT REGEXP_REPLACE('SDF45SDF0045D999','[09]','#') FROM DUAL;
有一个字符串 'JDSAHDJSA143214K32JK252^*%' 提取里面的数字
SELECT REGEXP_REPLACE('JDSAHDJSA143214K32JK252^*%','\D',NULL)
FROM DUAL;
有一个字符串 'JDSAHDJSA143214K32JK252^*%' 提取里面的英文字母
SELECT REGEXP_REPLACE('JDSAHDJSA143214K32JK252^*%','[^A-Za-z]',NULL)
FROM DUAL;
格式化手机号，将+86 13811112222转换为(+86) 138-1111-2222,’
SELECT REGEXP_REPLACE('+86 13811112222',
'(\+\d{2})( )(\d{3})(\d{4})(\d{4})',
'(\1) \2\3-\4-\5') FROM DUAL;
'AAAA1234' => 'AAAA=1234'
'BBBB5678' => 'BBBB=5678'
SELECT REGEXP_REPLACE('AAAA1234','([A-Z]+)(\d{4})','(\1)=\2') FROM DUAL;
--sql面试题：行列如何互换？（****）
SELECT * FROM SCORE;
1=语文 2= 数学 3 = 英语
SID 语文 数学 英语
1 80 90 99
2 null 60 80
3 80 80 80
--CASE WHEN 230
SELECT SID,SUM(CASE WHEN SCOURE = 1 THEN SSCORE END) 语文,
SUM(CASE WHEN SCOURE = 2 THEN SSCORE END) 数学,
SUM(CASE WHEN SCOURE = 3 THEN SSCORE END) 英语
FROM SCORE GROUP BY SID;
--DECODE
SELECT SID,AVG(DECODE(SCOURE,1,SSCORE)) 语文,
AVG(DECODE(SCOURE,2,SSCORE)) 数学,
AVG(DECODE(SCOURE,3,SSCORE)) 英语
FROM SCORE GROUP BY SID;
--PIVOT
CREATE TABLE SCORE1 AS SELECT * FROM SCORE S PIVOT(
SUM(SSCORE) FOR SCOURE IN (1 AS 语文, 2 AS 数学, 3 AS 英语)
);
SELECT * FROM SCORE1;
SELECT * FROM SCORE;
UNPIVOT
SELECT * FROM SCORE1 UNPIVOT(
SSCORE FOR SCOURE IN (语文 AS 1,数学 AS 2,英语 AS 3)
);
--------------------PLSQL---------------------------------
DECLARE
--声明部分 不需要声明时可以不写
--定义变量 常量 游标
BEGIN
--执行部分
EXCEPTION WHEN OTHERS THEN
--异常处理部分
--异常处理 不需要异常时可以省略不写
END;
--ORACLE里的语法函数PLSQL都通用 SELECT * => SELECT INTO
--每写完一句独立的代码都需要加;
--变量 V_ 默认值为空 不能以数字/特殊字符开头
--可以在声明部分赋值 执行部分赋值 外部赋值 SELECT查询语句赋值
--:=代表赋值 =代表判断
--变量可以被重新赋值 执行顺序从上向下 从左向右
--&位置没有限制 &后提示符内容不能一样
DECLARE
--V_NAME VARCHAR2(20):='张三';
--V_AGE NUMBER(10,2) := 20.89; 如果值为小数，需要预留小数的长度
V_NAME VARCHAR2(20) := '&输入姓名';
V_AGE NUMBER(10) := &输入年龄;
V_NAME1 VARCHAR2(20) := '坤坤';
V_SR DATE :=TO_DATE('&请输入生日','YYYYMMDD HH24MISS');
--日期类型怎么进行外部赋值 1.直接在输入框里写TO_DATE
BEGIN
DBMS_OUTPUT.put_line('你的名字是'||V_NAME
||'年龄是'||V_AGE || '生日是' || TO_CHAR(V_SR,'YYYYMMDD HH24MISS'));
V_NAME := '李四';--对变量重新赋值
V_NAME := V_NAME1;
--DBMS_OUTPUT.put_line('HELLO WORLD');
DBMS_OUTPUT.put_line(V_NAME);
END;
--查询语句赋值
--传入一个员工编号输出他的姓名 ,岗位,工资
DECLARE
V_ENAME VARCHAR2(20);
V_EMPNO NUMBER:=&请输入员工编号;
V_JOB VARCHAR2(20);
V_SAL NUMBER;
BEGIN
--当出现异常时，剩下的代码不会再执行而是立刻跳转到异常部分
DBMS_OUTPUT.put_line('HELLO');
SELECT ENAME,JOB,SAL INTO V_ENAME, V_JOB,V_SAL
--INTO前后字段数量，数据类型保持一致,一一对应
FROM EMP WHERE EMPNO = V_EMPNO;
DBMS_OUTPUT.put_line('该员工姓名为'||V_ENAME );
DBMS_OUTPUT.put_line('该员工工作为'||V_JOB);
DBMS_OUTPUT.put_line('该员工工资为'||V_SAL);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line('本公司没有该员工');
END;
--传入一个部门编号输出他的姓名 ,岗位,工资 看能不能执行
--变量只允许接受一行值
--当需要输出多行值时需要使用游标
DECLARE
V_ENAME VARCHAR2(20);
V_DEPTNO NUMBER:=&请输入部门编号;
V_JOB VARCHAR2(20);
V_SAL NUMBER;
BEGIN
SELECT ENAME,JOB,SAL INTO V_ENAME, V_JOB,V_SAL
--INTO前后字段数量，数据类型保持一致,一一对应
FROM EMP WHERE DEPTNO = V_DEPTNO;
DBMS_OUTPUT.put_line('该员工姓名为'||V_ENAME );
DBMS_OUTPUT.put_line('该员工工作为'||V_JOB);
DBMS_OUTPUT.put_line('该员工工资为'||V_SAL);
END;
--异常处理 不需要异常时可以省略不写
--输入8888输出请输入本公司没有该员工
DECLARE
V_ENAME VARCHAR2(20);
V_EMPNO NUMBER:=&请输入员工编号;
V_JOB VARCHAR2(20);
V_SAL NUMBER;
BEGIN
--当出现异常时，剩下的代码不会再执行而是立刻跳转到异常部分
DBMS_OUTPUT.put_line('HELLO');
SELECT ENAME,JOB,SAL INTO V_ENAME, V_JOB,V_SAL
--INTO前后字段数量，数据类型保持一致,一一对应
FROM EMP WHERE EMPNO = V_EMPNO;
DBMS_OUTPUT.put_line('该员工姓名为'||V_ENAME );
DBMS_OUTPUT.put_line('该员工工作为'||V_JOB);
DBMS_OUTPUT.put_line('该员工工资为'||V_SAL);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line('本公司没有该员工');
END;
-------------------------------------------
DECLARE
V_A NUMBER;
BEGIN
V_A := '张三';
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line('请输入一个数字');
DBMS_OUTPUT.put_line(SQLERRM);
--SQLERRM sql的1报错信息
END;
--常量 CONSTANT 不能数字/特殊符号开头
--变量可以不给初始值 常量必须给初始值
--变量可以在执行部分重新赋值，常量不可以
DECLARE
V_A VARCHAR2(20):='王五';
C_B CONSTANT VARCHAR2(20):='张四';
BEGIN
V_A := '赵六';
DBMS_OUTPUT.put_line(V_A || C_B);
END;
--%TYPE 引用某张表中某个字段的数据类型
DECLARE
V_A EMP.SAL%TYPE;
BEGIN
V_A := 8*8+8;
DBMS_OUTPUT.put_line(V_A);
END;
--%ROWTYPE 引用某张表中一整行的数据类型
DECLARE
V_A EMP%ROWTYPE;
--相当于定义了八个变量
--只可以在执行部分赋值
BEGIN
V_A.SAL := 9999;
V_A.ENAME := '张安';
DBMS_OUTPUT.put_line(V_A.SAL || V_A.ENAME);
END;
--传入一个员工编号输出他的员工信息
DECLARE
V_A EMP%ROWTYPE;
BEGIN
V_A.EMPNO := &请输入一个员工编号;
SELECT * INTO V_A FROM EMP WHERE EMPNO = V_A.EMPNO;
DBMS_OUTPUT.put_line(V_A.EMPNO);
DBMS_OUTPUT.put_line(V_A.ENAME);
DBMS_OUTPUT.put_line(V_A.SAL);
DBMS_OUTPUT.put_line(V_A.COMM);
DBMS_OUTPUT.put_line(V_A.MGR);
DBMS_OUTPUT.put_line(V_A.HIREDATE);
DBMS_OUTPUT.put_line(V_A.DEPTNO);
DBMS_OUTPUT.put_line(V_A.JOB); --变量名后的字段名要和表中一致
END;
--自定义数据类型
DECLARE
TYPE ROW_TYPE IS RECORD (
A NUMBER,
B VARCHAR2(20),
C DATE
);
--TYPE 数据类型名 IS RECORD( 数据类型1 ,数据类型2 ....)
V_A ROW_TYPE;
BEGIN
V_A.A := 787;
V_A.B :='张三';
DBMS_OUTPUT.put_line(V_A.A || V_A.B);
END;
传入一个半径 求出⚪的面积
DECLARE
V_BJ NUMBER := &请输入一个半径;
C_Π CONSTANT NUMBER := 3.14;
V_MJ NUMBER;
BEGIN
V_MJ := POWER(V_BJ,2) * C_Π;
DBMS_OUTPUT.put_line('圆的面积为' || V_MJ);
END;
传入一个员工编号返回该员工所在部门的平均工资
DECLARE
V_EMPNO NUMBER:=&DFD;
V_AVG_SAL NUMBER;
V_DEPTNO NUMBER;
BEGIN
SELECT DEPTNO INTO V_DEPTNO FROM EMP WHERE EMPNO = V_EMPNO;
SELECT AVG(SAL) INTO V_AVG_SAL FROM EMP WHERE DEPTNO = V_DEPTNO;
DBMS_OUTPUT.put_line('该员工所在部门为'|| V_DEPTNO||
'平均薪资为'|| V_AVG_SAL);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line('该员工不存在');
END;
--子查询
DECLARE
V_EMPNO NUMBER:=&DFD;
V_AVG NUMBER;
V_DEPTNO NUMBER;
BEGIN
SELECT AVG(SAL) INTO V_AVG FROM EMP WHERE DEPTNO =(
SELECT DEPTNO FROM EMP WHERE EMPNO = V_EMPNO
);
DBMS_OUTPUT.put_line('平均薪资为'|| V_AVG);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line('SDS');
END;
传入一个员工编号，返回他所在的部门名称
DECLARE
V_EMPNO NUMBER := &请输入一个员工编号;
V_DNAME VARCHAR2(20);
V_DEP NUMBER;
BEGIN
SELECT DEPTNO INTO V_DEP FROM EMP WHERE EMPNO = V_EMPNO;
SELECT DNAME INTO V_DNAME FROM DEPT WHERE DEPTNO =V_DEP;
DBMS_OUTPUT.put_line(V_DNAME);
END;
--表关联
DECLARE
V_EMPNO NUMBER := &请输入一个员工编号;
V_DNAME VARCHAR2(20);
V_DEP NUMBER;
BEGIN
SELECT DNAME INTO V_DNAME FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE EMPNO = V_EMPNO;
DBMS_OUTPUT.put_line(V_DNAME);
END;
--子查询
DECLARE
V_EMPNO NUMBER:=&DFD;
V_DNAME VARCHAR2(20);
BEGIN
SELECT DNAME INTO V_DNAME FROM DEPT WHERE DEPTNO =(
SELECT DEPTNO FROM EMP WHERE EMPNO = V_EMPNO
);
DBMS_OUTPUT.put_line('部门名称为'|| V_DNAME);
--EXCEPTION WHEN OTHERS THEN
-- DBMS_OUTPUT.put_line('SDS');
END;
传入一个员工编号，返回他的上级姓名
DECLARE
V_EMPNO NUMBER := &请输入一个员工编号;
V_NAME VARCHAR2(20);
BEGIN
SELECT B.ENAME INTO V_NAME FROM EMP E JOIN EMP B ON E.MGR = B.EMPNO
WHERE E.EMPNO = V_EMPNO;
DBMS_OUTPUT.put_line('该员工的上级姓名为' || V_NAME);
END;
--流程控制 IF
BEGIN
IF 条件表达式 THEN --开始判断
--条件满足后的逻辑
ELSIF 条件表达式 THEN
ELSE 其余情况
END IF; --结束判断
END;
DECLARE
V_A NUMBER := &请输入一个数字;
V_B NUMBER := &请输入一个数字1; --V_A = 4 V_B > 10
BEGIN
--当上一个条件不满足才执行下一个条件
--上一个条件满足就不会在执行下面的条件
IF ABS(V_A) =1 THEN --条件1
DBMS_OUTPUT.put_line('加班'); --执行1
ELSIF V_A = 2 THEN --条件2
DBMS_OUTPUT.put_line('下班'); --执行2
ELSIF V_A = 3 THEN --条件3
DBMS_OUTPUT.put_line('发工资'); --执行3
ELSIF V_A = 4 AND V_B > 10 THEN
DBMS_OUTPUT.put_line('下课');
ELSE --不用写判断条件 --条件4
DBMS_OUTPUT.put_line('上班');
END IF;
END;
--IF嵌套
20 30 输出老员工 40 输出一个更老员工
10 20 输出新员工 40 输出老员工
其余部门输出不是该公司员工
DECLARE
V_BM NUMBER :=&部门;
V_AGE NUMBER:=&年零;
BEGIN
--当外层判断满足时才会进入嵌套的if判断
IF V_BM = 20 THEN --条件1
IF V_AGE > 30 AND V_AGE < 40 THEN
DBMS_OUTPUT.put_line('20老员工');
ELSIF V_AGE >= 40 THEN
DBMS_OUTPUT.put_line('20更老员工');
END IF;
ELSIF V_BM = 10 THEN --条件2
IF V_AGE > 20 THEN
DBMS_OUTPUT.put_line('10新员工');
ELSIF V_AGE > 40 THEN
DBMS_OUTPUT.put_line('10老员工');
END IF;
ELSE
DBMS_OUTPUT.put_line('不是该公司员工');
END IF;
END;
CREATE TABLE EMP_0603 AS SELECT * FROM EMP;
小练习：不要改EMP
传入一个员工编号
如果这个员工工资小于1000，就给他加300奖金
如果这个员工工资1000到2000，就给他加200奖金
如果这个员工工资大于2000，就给他加100奖金
输出员工编号，员工名字，员工工资，员工奖金(为修改之后的奖金)
DECLARE
V_EMPNO NUMBER := &请输入员工编号;
V_A EMP%ROWTYPE;
BEGIN
SELECT * INTO V_A FROM EMP_0603 WHERE EMPNO = V_EMPNO;
IF V_A.SAL < 1000 THEN
UPDATE EMP_0603 SET COMM = NVL(COMM,0) + 300 WHERE EMPNO = V_EMPNO;
ELSIF V_A.SAL BETWEEN 1000 AND 2000 THEN
UPDATE EMP_0603 SET COMM = NVL(COMM,0) + 200 WHERE EMPNO = V_EMPNO;
ELSE
UPDATE EMP_0603 SET COMM = NVL(COMM,0) + 100 WHERE EMPNO = V_EMPNO;
END IF;
SELECT COMM INTO V_A.COMM FROM EMP_0603 WHERE EMPNO = V_EMPNO;
DBMS_OUTPUT.put_line(V_EMPNO||V_A.ENAME||V_A.SAL||V_A.COMM);
END;
SELECT * FROM EMP_0603;
传入一个员工编号
操作EMP_0602表
如果这个员工的岗位是ANALYST或者CLERK或者SALESMAN，
就给该员工加500元奖金
如果这个员工的岗位是MANAGER，就给该员工加800块奖金
如果这个员工的岗位是PRESIDENT，不做处理
DECLARE
V_EMPNO NUMBER := &请输入员工编号;
V_JOB VARCHAR2(20);
BEGIN
SELECT JOB INTO V_JOB FROM EMP_0603 WHERE EMPNO = V_EMPNO;
IF V_JOB IN ('ANALYST','CLERK','SALESMAN') THEN
UPDATE EMP_0603 SET COMM = NVL(COMM,0) + 500 WHERE EMPNO = V_EMPNO;
ELSIF V_JOB = 'MANAGER' THEN
UPDATE EMP_0603 SET COMM = NVL(COMM,0) + 800 WHERE EMPNO = V_EMPNO;
END IF;
END;
查询7369的员工工资，查询7788的员工工资，
将工资高的这个员工的名字打印出来。
DECLARE
V_A EMP%ROWTYPE;
BEGIN
V_A.EMPNO :=7369;
V_A.SAL := 7788;
SELECT SAL INTO V_A.COMM FROM EMP WHERE EMPNO = V_A.EMPNO; --7369
SELECT SAL INTO V_A.MGR FROM EMP WHERE EMPNO = V_A.SAL;
IF V_A.COMM > V_A.MGR THEN
SELECT ENAME INTO V_A.ENAME FROM EMP WHERE EMPNO = V_A.EMPNO;
DBMS_OUTPUT.put_line(V_A.ENAME);
ELSIF V_A.MGR > V_A.COMM THEN
SELECT ENAME INTO V_A.ENAME FROM EMP WHERE EMPNO = V_A.SAL;
DBMS_OUTPUT.put_line(V_A.ENAME);
ELSE
DBMS_OUTPUT.put_line('俩一样大');
END IF;
END;
传入一个员工编号，求出员工的工资，打印出这个工资对应的等级，
2000以下C，2000-3000是B，3000以上是A
DECLARE
V_EMPNO NUMBER := 7369;
V_SAL NUMBER;
BEGIN
SELECT SAL INTO V_SAL FROM EMP WHERE EMPNO = V_EMPNO;
IF V_SAL < 2000 THEN
DBMS_OUTPUT.put_line('C');
ELSIF V_SAL BETWEEN 2000 AND 3000 THEN
DBMS_OUTPUT.put_line('B');
ELSE
DBMS_OUTPUT.put_line('A');
END IF;
END;
输入一个员工编号(=&员工编号)，如果编号在表中是不存在的，
则打印没有这个人，如果用户编号存在，
则打印这个员工的名字。
DECLARE
V_EMPNO NUMBER :=&SDS;
V_ENAME VARCHAR2(20);
BEGIN
SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = V_EMPNO;
DBMS_OUTPUT.put_line(V_ENAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line('没有这个人');
END;
2.0
DECLARE
V_EMPNO NUMBER :=&SDS;
V_ENAME VARCHAR2(20);
V_COUNT NUMBER;
BEGIN
SELECT COUNT(1) INTO V_COUNT FROM EMP WHERE EMPNO = V_EMPNO;
IF V_COUNT = 1 THEN
SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = V_EMPNO;
DBMS_OUTPUT.put_line(V_ENAME);
ELSE
DBMS_OUTPUT.put_line('没有这个人');
END IF;
END;
6.传入一个员工编号，如果该员工是'ANALYST','CLERK','SALESMAN'
岗位就加500工资，并且该员工工资在3000以上则再加500，
如果是MANAGER岗位则加800工资
DECLARE
V_EMPNO NUMBER :=&SDS;
V_JOB VARCHAR2(20);
V_SAL NUMBER;
BEGIN
SELECT JOB,SAL INTO V_JOB,V_SAL FROM EMP_0603 WHERE
EMPNO = V_EMPNO;
IF V_JOB IN ('ANALYST','CLERK','SALESMAN') THEN
UPDATE EMP_0603 SET COMM = NVL(COMM,0) + 500 WHERE EMPNO = V_EMPNO;
IF V_SAL >= 3000 THEN
UPDATE EMP_0603 SET COMM = NVL(COMM,0) + 500 WHERE EMPNO = V_EMPNO;
END IF;
ELSIF V_JOB = 'MANAGER' THEN
UPDATE EMP_0603 SET COMM = NVL(COMM,0) + 800 WHERE EMPNO = V_EMPNO;
END IF;
END;
SELECT * FROM EMP_0603;
7.判断用户7788的名字是否是S开头并且是20号部门
DECLARE
V_ENAME VARCHAR2(20);
V_DEPTNO NUMBER;
BEGIN
SELECT ENAME,DEPTNO INTO V_ENAME,V_DEPTNO FROM EMP
WHERE EMPNO = 7788;
IF SUBSTR(V_ENAME,1,1) = 'S' THEN
IF V_DEPTNO = 20 THEN
DBMS_OUTPUT.put_line('是');
END IF;
ELSE
DBMS_OUTPUT.put_line('不是');
END IF;
END;
--------------------0603----------------------------------
--CASE WHEN
DECLARE
V_A NUMBER :=&DFSF;
V_B NUMBER :=ⅅ
BEGIN
CASE WHEN V_A = 1 THEN
DBMS_OUTPUT.put_line('你好');
WHEN V_A = 2 THEN
CASE WHEN V_B = 2 THEN
DBMS_OUTPUT.put_line('再见');
ELSE
DBMS_OUTPUT.put_line('不再见');
END CASE;
WHEN V_A = 3 THEN
DBMS_OUTPUT.put_line('拜拜');
ELSE
DBMS_OUTPUT.put_line('HELLO');
END CASE;
END;
--循环
LOOP WHILE FOR
--LOOP
DECLARE
V_A NUMBER:=0;--设置记录循环次数的变量
BEGIN
LOOP --开启循环
V_A := V_A +1;
DBMS_OUTPUT.put_line('我循环了'|| V_A ||'次');
--EXIT WHEN V_A =10; --退出循环关键字
IF V_A = 10 THEN
EXIT;
END IF;
END LOOP; --结束循环
END;
--WHILE 进入循环的条件 LOOP
DECLARE
V_A NUMBER := 0;
BEGIN
WHILE V_A < 10 LOOP
DBMS_OUTPUT.put_line('我循环了'|| V_A ||'次');
V_A := V_A +1;
--EXIT WHEN V_A = 5 ;
END LOOP;
END;
--FOR 最简单最好使
BEGIN
FOR Q IN 1.. 10 LOOP
--EXIT WHEN Q = 5 ;
IF Q = 5 THEN
CONTINUE;
END IF;
DBMS_OUTPUT.put_line('我循环了'|| Q ||'次');
END LOOP;
END;
--退出循环 EXIT
--跳出本次循环 CONTINUE
--求出10以内的整数和
--LOOP
DECLARE
V_A NUMBER := 0;
V_SUM NUMBER := 0;
BEGIN
LOOP
EXIT WHEN V_A = 10;
V_A := V_A +1; --步长
V_SUM := V_SUM + V_A;
DBMS_OUTPUT.put_line('十以内整数和为'|| V_SUM);
END LOOP;
END;
--WHILE
DECLARE
V_A NUMBER := 0;
V_SUM NUMBER := 0;
BEGIN
WHILE V_A < 10 LOOP
V_A := V_A + 1;
V_SUM := V_SUM + V_A;
END LOOP;
DBMS_OUTPUT.put_line('十以内整数和为' || V_SUM);
END;
--FOR
DECLARE
V_SUM NUMBER:=0;
BEGIN
FOR I IN 1..10 LOOP
V_SUM := V_SUM + I;
END LOOP;
DBMS_OUTPUT.put_line('十以内整数和为' || V_SUM);
END;
--求出10以内的偶数和
--LOOP
DECLARE
V_A NUMBER := 0;
V_SUM NUMBER := 0;
BEGIN
LOOP
EXIT WHEN V_A = 10;
V_A := V_A +1;
IF MOD(V_A ,2)=0 THEN
V_SUM := V_SUM + V_A;
END IF;
DBMS_OUTPUT.put_line('十以内偶数和为'|| V_SUM);
END LOOP;
END;
--WHILE
DECLARE
V_A NUMBER := 0;
V_SUM NUMBER := 0;
BEGIN
WHILE V_A < 10 LOOP
V_A := V_A + 1;
IF MOD(V_A ,2) != 0 THEN
CONTINUE;
END IF;
V_SUM := V_SUM + V_A;
END LOOP;
DBMS_OUTPUT.put_line('十以内整数和为' || V_SUM);
END;
--FOR
DECLARE
V_SUM NUMBER:=0;
BEGIN
FOR I IN 1..10 LOOP
IF MOD(I,2)=0 THEN
V_SUM := V_SUM + I;
END IF;
END LOOP;
DBMS_OUTPUT.put_line('十以内整数和为' || V_SUM);
END;
--求两数之间的偶数和
10 30
FOR I IN 10..30 LOOP
30 10
FOR I IN 10..30 LOOP
DECLARE
V_A NUMBER :=&第一个数字;
V_B NUMBER :=&第二个数字;
V_SUM NUMBER := 0;
BEGIN
IF V_A > V_B THEN
FOR I IN V_B..V_A LOOP
IF MOD(I,2)=0 THEN
V_SUM := V_SUM + I;
END IF;
END LOOP;
ELSE
FOR I IN V_A..V_B LOOP
IF MOD(I,2)=0 THEN
V_SUM := V_SUM + I;
END IF;
END LOOP;
END IF;
DBMS_OUTPUT.put_line('两数之间偶数和为' || V_SUM);
END;
--WHILE
DECLARE
V_A NUMBER :=&第一个数字;
V_B NUMBER :=&第二个数字;
V_SUM NUMBER := 0;
BEGIN
WHILE V_A <= V_B LOOP
IF MOD(V_A,2) = 0 THEN
V_SUM := V_SUM + V_A;
END IF;
V_A := V_A + 1;
END LOOP;
DBMS_OUTPUT.put_line('两数之间偶数和为' || V_SUM);
END;
1.使用一个for循环，打印出下面的图案
*
**
***
****
*****
******
--输入一个行数 就生成一个直角三角性
DECLARE
V_A VARCHAR2(200):='*';
V_B NUMBER :=&输入一个行数;
BEGIN
FOR I IN 1..V_B LOOP
DBMS_OUTPUT.put_line(V_A);
V_A := V_A || '*';
END LOOP;
END;
SELECT LPAD('张三',10,'*') FROM DUAL;
DECLARE
V_A VARCHAR2(200):='*';
V_B NUMBER :=&输入一个行数;
BEGIN
FOR I IN 1..V_B LOOP
DBMS_OUTPUT.put_line(LPAD(V_A,I,'*'));
END LOOP;
END;
2.假设现在有鸡和兔子，一共有35只，它们的脚一共有94个，
使用一个for循环计算出鸡和兔子分别有多少只。
DECLARE
--I=鸡 35-i=兔
BEGIN
FOR I IN 0..35 LOOP
IF I + (35-I) = 35 AND I * 2 + (35-I)*4 = 94 THEN
DBMS_OUTPUT.put_line('鸡有'|| I || '只');
DBMS_OUTPUT.put_line('兔有'|| (35 - I)|| '只');
END IF;
END LOOP;
END;
3.纸厚度1mm，珠穆朗玛峰高度8848m，问纸要对折多少次，
高度才会超过山
DECLARE
V_ZHI NUMBER := 1;
V_COUNT NUMBER := 0; --记录次数
BEGIN
WHILE V_ZHI <= 8848000 LOOP
V_ZHI := V_ZHI *2;
V_COUNT := V_COUNT + 1;
DBMS_OUTPUT.put_line(V_COUNT);
DBMS_OUTPUT.put_line(V_ZHI);
END LOOP;
END;
4.从今天开始存钱，今天存1分，明天2分，后天4分，
每天翻倍，问要几天才能存够100W元。
DECLARE
V_DAY NUMBER := 0;
V_SUM_QIAN NUMBER := 0;
V_DAY_QIAN NUMBER := 1;
BEGIN
WHILE V_SUM_QIAN <100000000 LOOP
V_SUM_QIAN := V_SUM_QIAN + V_DAY_QIAN;
V_DAY_QIAN := V_DAY_QIAN*2;
V_DAY := V_DAY + 1;
DBMS_OUTPUT.put_line('天数'||V_DAY);
DBMS_OUTPUT.put_line('每天存钱数'||V_DAY_QIAN);
DBMS_OUTPUT.put_line('总金额'||V_SUM_QIAN);
END LOOP;
END;
5.使用一个while循环，打印出下面的图案
***********
*********
*******
*****
***
*
DECLARE
V_A VARCHAR2(30):='*********';
V_B VARCHAR2(20):='';
BEGIN
FOR I IN 1..5 LOOP
DBMS_OUTPUT.put_line(V_B||V_A);
V_A := SUBSTR(V_A,3);
V_B := V_B ||' ';
END LOOP;
END;
--输入一个行数 生成一个倒三角
***********
********* 5*2-1=9
******* 4*2-1=7
***** 3*2-1=5
*** 2*2-1=3
* 1*2-1=1
DECLARE
V_A NUMBER := &请输入一个数字 ;--循环总行数
V_B NUMBER :=0; --记录*
V_C NUMBER :=0; --记录空格
V_D NUMBER :=0; --记录循环次数
BEGIN
WHILE V_D < V_A LOOP
WHILE V_C < V_D LOOP
DBMS_OUTPUT.put(' ');
V_C := V_C + 1;
END LOOP;
WHILE V_B < (V_A -V_D )*2-1 LOOP --控制星号的循环
DBMS_OUTPUT.put('*');
V_B := V_B + 1;
END LOOP;
DBMS_OUTPUT.new_line;
V_B := 0;
V_C:=0;
V_D := V_D + 1;
END LOOP;
END;
--正三角
*
***
*****
*******
*********
DECLARE
V_A VARCHAR2(50) :='*';
V_B VARCHAR2(30) :=' ';
BEGIN
FOR I IN 1..5 LOOP
DBMS_OUTPUT.put_line(V_B||V_A);
V_B:=SUBSTR(V_B,2);
V_A:=V_A||'**';
END LOOP;
END;
--PUT
*
***
*****
*******
*********
DECLARE
V_A NUMBER := 9;--总行数
V_B NUMBER :=1;--记录循环次数 1*2-1 2*2-1 3*2-1 4 *2-1 5*2-1
V_C NUMBER := 0;--控制空格
V_D NUMBER :=0;--控制星号
BEGIN
WHILE V_B <= V_A LOOP --控制行数
WHILE V_C < V_A-V_B LOOP
DBMS_OUTPUT.put(' '); --输出空格
V_C := V_C + 1;
END LOOP;
WHILE V_D < 2*V_B-1 LOOP
DBMS_OUTPUT.put('*'); --输出星号
V_D := V_D + 1;
END LOOP;
DBMS_OUTPUT.put_line('');
V_B := V_B + 1;
V_C :=0;
V_D :=0;
END LOOP;
END;
BEGIN
DBMS_OUTPUT.put_line('张三');
DBMS_OUTPUT.put_line('李四');
END;
BEGIN
DBMS_OUTPUT.put('张三');--不会自动换行
DBMS_OUTPUT.put('李四');
--把数据放在缓冲区，直到读取到换行符时才展示数据
-- DBMS_OUTPUT.put_line('');--利用换行符进行输出
DBMS_OUTPUT.new_line;
END;
6.请编写一个PL/SQL代码块，使用循环结构将一个字符型变量 str 反转，
并输出反转后的字符串。
ABCD => DCBA
DECLARE
V_A VARCHAR2(20):='&字符串';
V_B VARCHAR2(20);
V_C VARCHAR2(20);
BEGIN
FOR I IN 1..LENGTH(V_A) LOOP
V_B :=SUBSTR(V_A,-I,1);
V_C := V_C || V_B ;
END LOOP;
DBMS_OUTPUT.put_line(V_C);
END;
--REVERSE
DECLARE
V_A VARCHAR2(20):='&字符串';
V_B VARCHAR2(20);
V_C VARCHAR2(20);
BEGIN
FOR I IN REVERSE 1..LENGTH(V_A) LOOP
V_B :=SUBSTR(V_A,I,1);
V_C := V_C || V_B ;
DBMS_OUTPUT.put_line(I);
END LOOP;
DBMS_OUTPUT.put_line(V_C);
END;
ABCDE
SUBSTR(V_A,5,1); E
SUBSTR(V_A,4,1); D
SUBSTR(V_A,3,1); C
SUBSTR(V_A,2,1); B
SUBSTR(V_A,1,1); A
7.请编写一个PL/SQL代码块，使用循环结构打印出以下图案。
*****
****
***
**
*
DECLARE
V_A VARCHAR2(20) := '*****';
BEGIN
FOR I IN 1..5 LOOP
DBMS_OUTPUT.put_line(V_A);
V_A := SUBSTR(V_A,2);
END LOOP;
END;
DECLARE
V_A NUMBER := &行数;
V_B NUMBER :=0;
V_C NUMBER := 0;--记录星号
BEGIN
WHILE V_B < V_A 5 LOOP
WHILE V_C < V_A-V_B LOOP
DBMS_OUTPUT.put('*');
V_C := V_C + 1;
END LOOP;
V_B := V_B + 1;
V_C := 0;
DBMS_OUTPUT.put_line('');
END LOOP;
END;
-------------------------------
WHILE V_A < 5 LOOP 1 2 3 4 5
WHILE V_B < 9 LOOP 9 9 9 9 9
END LOOP;
END LOOP;
8.--6.请编写一个PL/SQL代码块，使用循环结构打印出以下图案。
*
**
***
****
*****
--1.0
DECLARE
V_A VARCHAR2(20) :='*';
BEGIN
FOR I IN 1..5 LOOP
DBMS_OUTPUT.put_line(LPAD(V_A,5,' '));
V_A := '*'||V_A;
END LOOP;
END;
----2.0
DECLARE
V_A VARCHAR2(20):='*';
V_B VARCHAR2(20):=' ';
BEGIN
FOR I IN 1..5 LOOP
DBMS_OUTPUT.put_line(V_B || V_A);
V_A := V_A || '*';
V_B := SUBSTR(V_B , 2);
END LOOP;
END;
--3.0PUT写法
百元买鸡
小明他妈给他100块钱让他买100只鸡
已知公鸡5元一只，母鸡3元一只，小鸡一元三只
问有多少种买法 4种
BEGIN
FOR A IN 0 .. 20 LOOP
FOR B IN 0 .. 33 LOOP
FOR C IN 0 .. 300 LOOP
IF A + B + C = 100 AND A * 5 + B * 3 + C / 3 = 100 THEN
DBMS_OUTPUT.put_line('工鸡买' || A || '只 '||'母鸡买' || B || '只 '
||'小鸡买' || C || '只 ');
END IF;
END LOOP;
END LOOP;
END LOOP;
END;
--LOOP循环写法
DECLARE
V_GJ NUMBER := 0 ;
V_MJ NUMBER :=0;
V_XJ NUMBER :=0;
BEGIN
LOOP
LOOP
LOOP
IF V_GJ+V_MJ+V_XJ=100 AND V_GJ*5 + V_MJ *3 + V_XJ/3 = 100 THEN
DBMS_OUTPUT.put_line('工鸡买' || V_GJ || '只 '||'母鸡买' || V_MJ || '只 '
||'小鸡买' || V_XJ || '只 ');
END IF;
EXIT WHEN V_XJ = 300;
V_XJ := V_XJ+1;
END LOOP;
EXIT WHEN V_MJ = 33;
V_MJ := V_MJ + 1;
V_XJ := 0;
END LOOP;
EXIT WHEN V_GJ = 20;
V_GJ := V_GJ + 1;
V_MJ:=0;
V_XJ:=0;
END LOOP;
END;
利用循环写出一个乘法口诀表
DECLARE
V_A VARCHAR2(1000);
V_B VARCHAR2(1000);
BEGIN
FOR A IN 1..9 LOOP --控制行
V_B := '';
FOR B IN 1..A LOOP --控制列
IF A*B < 10 THEN
V_A := B || '*' || A || '=' || (A*B) || ' ';
ELSE
V_A := B || '*' || A || '=' || (A*B) || ' ';
END IF;
V_B := V_B || V_A;
END LOOP;
DBMS_OUTPUT.put_line(V_B);
END LOOP;
END;
1*2=
--PUT
BEGIN
FOR A IN 1..9 LOOP
FOR B IN 1..A LOOP
DBMS_OUTPUT.put(B||'*'||A||'='||(A*B)||' ');
END LOOP;
DBMS_OUTPUT.put_line('');
END LOOP;
END;
游标是指向查询结果集的一个指针，通过游标可以将查询结果集中的记录逐一取出，
并在PL/SQL程序块中进行处理。
游标的类型有两种：隐式游标和显式游标。隐式游标是由系统自动创建并管理的游标。
PL/SQL会为所有的sql数据操作声明一个隐式的游标，包括只返回一条记录的查询操作。
对于返回多条记录的查询，必须自己创建显式游标。
--显式游标 CURSOR
--搭配LOOP循环
DECLARE
CURSOR C_A IS SELECT ENAME FROM EMP WHERE SAL > 2000 ;
V_ENAME VARCHAR2(20);
V_A NUMBER :=0;
BEGIN
OPEN C_A ;--打开游标
LOOP
FETCH C_A INTO V_ENAME;--向变量传值
--数据类型字段数量都需要保持一至
DBMS_OUTPUT.put_line(V_ENAME);
--EXIT WHEN V_ENAME = 'SCOTT';
EXIT WHEN V_A = 14;
V_A := V_A + 1;
END LOOP;
CLOSE C_A; --关闭游标
END;
--搭配WHILE使用
DECLARE
CURSOR C_A IS SELECT EMPNO,ENAME FROM EMP;
CURSOR C_B IS SELECT ENAME FROM EMP;
V_EMPNO NUMBER;
V_ENAME VARCHAR2(20);
V_A NUMBER := 0;
BEGIN
OPEN C_A;
WHILE V_A <20 LOOP
FETCH C_A INTO V_EMPNO,V_ENAME ;
V_A := V_A + 1;
DBMS_OUTPUT.put_line(V_ENAME||V_EMPNO);
END LOOP;
CLOSE C_A;
END;
--游标的四个属性 游标名%属性类型
%FOUND 布尔类型 TRUE FALSE 在游标中找到数据返回TRUE,没找到数据返回FALSE
%NOTFOUND 布尔类型 未找到数据返回TRUE，找到数据返回FALSE
%ISOPEN 布尔类型 游标成功打开返回TRUE，反之返回FLASE
%ROWCOUNT 数字类型 返回游标中存在多少条数据
DECLARE
CURSOR C_A IS SELECT ENAME FROM EMP ;
V_ENAME VARCHAR2(20);
BEGIN
OPEN C_A ;--打开游标
LOOP
FETCH C_A INTO V_ENAME;--向变量传值
--数据类型字段数量都需要保持一至
--EXIT WHEN V_ENAME = 'SCOTT';
EXIT WHEN C_A%NOTFOUND; --搭配NOTFOUND使用
DBMS_OUTPUT.put_line(V_ENAME);
END LOOP;
CLOSE C_A; --关闭游标
END;
--搭配WHILE使用
DECLARE
CURSOR C_A IS SELECT EMPNO,ENAME FROM EMP;
V_EMPNO NUMBER;
V_ENAME VARCHAR2(20);
BEGIN
OPEN C_A;
FETCH C_A INTO V_EMPNO,V_ENAME ; --进入WHILE循环的条件
WHILE C_A%FOUND LOOP--搭配FOUND使用
DBMS_OUTPUT.put_line(V_ENAME||V_EMPNO);
FETCH C_A INTO V_EMPNO,V_ENAME ; --更新游标里的数据
END LOOP;
DBMS_OUTPUT.put_line('游标里有'||C_A%ROWCOUNT||'条数据');
IF C_A%ISOPEN THEN
DBMS_OUTPUT.put_line('打开了');
END IF;
CLOSE C_A;
END;
--游标搭配FOR循环使用 for循环会自动打开关闭游标
DECLARE
CURSOR C_A IS SELECT * FROM EMP;
BEGIN
FOR I IN C_A LOOP
DBMS_OUTPUT.put_line(I.ENAME||I.SAL);
END LOOP;
END;
--带参游标
参数 定义了一定要用 P_ IN入参 OUT出参 IN OUT 可以入参也可以出参
参数不可以设置长度
变量 定义了可以不用
--传入一个岗位 输出岗位下的员工姓名
DECLARE
CURSOR C_A(P_JOB VARCHAR2,P_DEP NUMBER) IS SELECT ENAME FROM EMP
WHERE JOB = P_JOB AND DEPTNO = P_DEP;
V_ENAME VARCHAR2(20);
V_JOB VARCHAR2(20) := '&请输入一个岗位';
V_DEP NUMBER := &部门;
BEGIN
--OPEN C_A('&请输入一个岗位');--开启游标同时进行传参
OPEN C_A(V_JOB,V_DEP);
LOOP
FETCH C_A INTO V_ENAME;
EXIT WHEN C_A%NOTFOUND;
DBMS_OUTPUT.put_line(V_ENAME);
END LOOP;
CLOSE C_A;
END;
--FOR循环
--传入一个岗位 输出岗位下的员工姓名
DECLARE
CURSOR C_A(P_JOB VARCHAR2) IS SELECT * FROM EMP
WHERE JOB = P_JOB;
BEGIN
FOR I IN C_A('SALESMAN') LOOP
DBMS_OUTPUT.put_line(I.ENAME);
END LOOP;
END;
--FOR循环支持使用子查询代替游标
--传入一个岗位 输出岗位下的员工姓名
BEGIN
FOR I IN (SELECT ENAME FROM EMP) LOOP
DBMS_OUTPUT.put_line(I.ENAME);
END LOOP;
END;
1.打印出EMP前五条员工信息
DECLARE
CURSOR C_A IS SELECT * FROM EMP WHERE ROWNUM <6;
BEGIN
FOR I IN C_A LOOP
DBMS_OUTPUT.put_line(I.ENAME||I.EMPNO||I.SAL);
END LOOP;
END;
2.传入一个部门编号，输出该部门下所有的员工编号和工资
DECLARE
CURSOR C_A(P_DEPTNO NUMBER) IS SELECT EMPNO,SAL
FROM EMP WHERE DEPTNO = P_DEPTNO;
V_EMPNO NUMBER;
V_SAL NUMBER;
BEGIN
OPEN C_A(&sdfs);
LOOP
FETCH C_A INTO V_EMPNO,V_SAL;
EXIT WHEN C_A%NOTFOUND;
DBMS_OUTPUT.put_line(V_EMPNO||V_SAL);
END LOOP;
CLOSE C_A;
END;
3. 对于EMP_0605表中SALESMAN和MANAGER，
如果这个员工工资小于1000，就给他加300奖金
如果这个员工工资1000到2000，就给他加200奖金
如果这个员工工资大于2000，就给他加100奖金
输出员工编号，员工名字，员工工资，员工奖金
DECLARE
CURSOR C_A IS SELECT * FROM
EMP_0605 WHERE JOB IN ('SALESMAN','MANAGER') ;
V_A EMP%ROWTYPE;
BEGIN
OPEN C_A;
LOOP
FETCH C_A INTO V_A;
IF V_A.SAL < 1000 THEN
UPDATE EMP_0605 SET COMM = NVL(COMM,0) + 300 WHERE EMPNO = V_A.EMPNO;
ELSIF V_A.SAL BETWEEN 1000 AND 2000 THEN
UPDATE EMP_0605 SET COMM = NVL(COMM,0) + 200 WHERE EMPNO = V_A.EMPNO;
ELSE
UPDATE EMP_0605 SET COMM = NVL(COMM,0) + 100 WHERE EMPNO = V_A.EMPNO;
END IF;
SELECT COMM INTO V_A.COMM FROM EMP_0605 WHERE EMPNO = V_A.EMPNO;
DBMS_OUTPUT.put_line(V_A.EMPNO||V_A.ENAME||V_A.SAL||' '||V_A.COMM);
EXIT WHEN C_A%NOTFOUND;
END LOOP;
CLOSE C_A;
END;
SELECT * FROM EMP_0605;
4.用游标FOR循环操作EMP_1010表
如果这个员工的岗位是ANALYST或者CLERK或者SALESMAN，就给该员工加500元奖金
如果这个员工的岗位是MANAGER，就给该员工加800块奖金
如果这个员工的岗位是PRESIDENT，不做处理
DECLARE
CURSOR C_A IS SELECT * FROM EMP_0605;
BEGIN
FOR I IN C_A LOOP
IF I.JOB IN ('ANALYST','CLERK','SALESMAN') THEN
UPDATE EMP_0605 SET COMM = NVL(COMM,0) + 500 WHERE EMPNO = I.EMPNO;
ELSIF I.JOB = 'MANAGER' THEN
UPDATE EMP_0605 SET COMM = NVL(COMM,0) + 800 WHERE EMPNO = I.EMPNO;
END IF;
END LOOP;
COMMIT;
END;
5.题目：
有一个表格：使用循环，往表格里面添加1-200的所有的数字
做如下题目：
查询刚才这个test2表，找出这个表格中两两相加等于288的数字，
将它们的组合都打印出来
CREATE TABLE TEST3 (SZ NUMBER);
SELECT * FROM TEST3;
BEGIN
FOR I IN 1..200 LOOP
INSERT INTO TEST3 VALUES(I);
END LOOP;
COMMIT;
END;
DECLARE
CURSOR C_A IS SELECT * FROM TEST3;
CURSOR C_B IS SELECT * FROM TEST3;
BEGIN
FOR A IN C_A LOOP
FOR B IN C_B LOOP
IF A.SZ + B.SZ = 288 AND A.SZ >=B.SZ THEN
DBMS_OUTPUT.put_line(A.SZ||' '||B.SZ);
END IF;
END LOOP;
END LOOP;
END;
--隐式游标 执行增删改语句时会创建隐式游标
--隐式游标名SQL 查询结果为一行的查询语句也会创建隐式游标
%FOUND 布尔类型 TRUE FALSE 在游标中找到数据返回TRUE,没找到数据返回FALSE
%NOTFOUND 布尔类型 未找到数据返回TRUE，找到数据返回FALSE
%ISOPEN 布尔类型 游标成功打开返回TRUE，反之返回FLASE 返回值永远为FALSE
%ROWCOUNT 数字类型 返回游标中存在多少条数据
DECLARE
V_A VARCHAR2(20);
BEGIN
SELECT ENAME INTO V_A FROM EMP WHERE EMPNO = 7788;
--INSERT INTO EMP_0605 SELECT * FROM EMP WHERE SAL > 2000;
--UPDATE EMP_0605 SET ENAME='张三';
-- DELETE FROM EMP_0605 WHERE DEPTNO = 10;
IF SQL%FOUND THEN
DBMS_OUTPUT.put_line('该游标内存在数据 ');
END IF;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.put_line('该游标内不存在数据 ');
END IF;
DBMS_OUTPUT.put_line('游标内存在'||SQL%ROWCOUNT||'条数据');
IF SQL%ISOPEN THEN
DBMS_OUTPUT.put_line('打开了');
END IF;
END;
--1.请编写一个PL/SQL代码块，
使用循环结构将表 scores 中的每一行数据都删除。
DECLARE
CURSOR C_A IS SELECT SID FROM SCORE GROUP BY SID;
BEGIN
FOR I IN C_A LOOP
DELETE FROM SCORE WHERE SID = I.SID;
END LOOP;
END;
DECLARE
CURSOR C_A IS SELECT * FROM SCORE;
BEGIN
FOR A IN C_A LOOP
DELETE FROM SCORE WHERE ROWNUM ='1';
END LOOP;
END;
--2.请编写一个PL/SQL代码块，
使用循环结构将表 scores 中每个学生的分数加10分
DECLARE
CURSOR C_A IS SELECT SID FROM SCORE GROUP BY SID;
BEGIN
FOR I IN C_A LOOP
UPDATE SCORE SET SSCORE = SSCORE + 10 WHERE SID = I.SID;
END LOOP;
END;
--3.请编写一个PL/SQL代码块，
使用循环结构将表 scores 中每个学生的分数降低到不高于80分。
SELECT * FROM SCORE
DECLARE
CURSOR C_A IS SELECT * FROM SCORE;
BEGIN
FOR I IN C_A LOOP
IF I.SSCORE >80 THEN
UPDATE SCORE SET SSCORE = 80 WHERE SID = I.SID;
END IF;
END LOOP;
END;
--4.请编写一个PL/SQL代码块，
循环打印出数字1~100中所有是7的倍数的数字。
BEGIN
FOR I IN 1..100 LOOP
IF MOD(I,7)=0 THEN
DBMS_OUTPUT.put_line(I);
END IF;
END LOOP;
END;
9.4 动态SQL
在PL/SQL程序开发中，可以使用DML语句，但是很多语句(如DDL)
不能直接在PL/SQL中执行，这些语句可以使用动态SQL来实现。
使用动态方式，可以执行任何类型的sql语句。
语法格式：
EXECUTE IMMEDIATE 动态语句字符串
[INTO 变量列表]
[USING 参数列表] --参数前必须要有:
BEGIN
--EXECUTE IMMEDIATE 'CREATE TABLE EMP_0606 AS SELECT * FROM EMP';
EXECUTE IMMEDIATE'DROP TABLE EMP_0605';
--TRUNCATE TABLE EMP_0605;
END;
--执行增删改
--利用动态SQL传入一个员工编号删除该员工信息
DECLARE
V_EMPNO NUMBER:=&SFDF;
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM EMP_0606 WHERE EMPNO ='||V_EMPNO;
--DBMS_OUTPUT.put_line
--('DELETE FROM EMP_0606 WHERE EMPNO ='||V_EMPNO);
END;
--利用动态SQL传入一个员工姓名删除该员工信息
DECLARE
V_ENAME VARCHAR2(20):='SCOTT';
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM EMP_0606 WHERE ENAME ='
||CHR(39)||V_ENAME || CHR(39); --CHR(39)=单引号 CHR(13) = 换行
--DBMS_OUTPUT.put_line
--('DELETE FROM EMP_0606 WHERE ENAME ='|| CHR(39) ||V_ENAME||CHR(39));
END;
--USING 传参
--利用动态SQL传入一个员工姓名删除该员工信息
DECLARE
V_ENAME VARCHAR2(20):='SCOTT';
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM EMP_0606 WHERE ENAME = :P_NAME'
USING V_ENAME; --变量的数量和数据类型一一对应
END;
--把SMITH的姓名改成张三
DECLARE
V_ENAME VARCHAR2(20):='&输入一个员工姓名';
V_A VARCHAR2(20) := '&输入该员工新名字';
BEGIN
EXECUTE IMMEDIATE 'UPDATE EMP_0606 SET ENAME = :P_A
WHERE ENAME = :P_B'
USING V_A,V_ENAME; --变量的数量和数据类型一一对应
END;
--INTO
--传入一个编号返回他的姓名
DECLARE
V_ENAME VARCHAR2(20);
V_EMPNO NUMBER := 7788;
V_SQL VARCHAR2(1000);
V_DEPTNO NUMBER:= 20;
V_SAL NUMBER;
BEGIN
V_SQL := 'SELECT ENAME,SAL FROM EMP WHERE EMPNO = :A
AND DEPTNO = :B';
EXECUTE IMMEDIATE V_SQL
INTO V_ENAME,V_SAL;
USING V_EMPNO,V_DEPTNO;--7788;
DBMS_OUTPUT.put_line(V_ENAME||V_SAL);
END;
-- -- 怎么将scott用户里面，
--EMP1和DEPT1表中所有不够200长度的varchar2数据类型，
--改成VARCHAR2(200) 动态SQL去写
表字段信息表：user_tab_columns
DEPT1 EMP1
DECLARE
CURSOR C_A IS
SELECT TABLE_NAME, L.COLUMN_NAME, L.DATA_LENGTH, L.DATA_TYPE
FROM user_tab_columns L
WHERE (TABLE_NAME = 'EMP1' OR TABLE_NAME = 'DEPT1')
AND DATA_TYPE = 'VARCHAR2';
BEGIN
FOR I IN C_A LOOP
IF I.DATA_LENGTH < 200 THEN
--ALTER TABLE EMP MODIFY DNAME VARCHAR2(200)
EXECUTE IMMEDIATE 'ALTER TABLE ' || I.TABLE_NAME || ' MODIFY ' ||
I.COLUMN_NAME || ' VARCHAR2(200)';
--DBMS_OUTPUT.put_line('ALTER TABLE '||I.TABLE_NAME||' MODIFY '||
-- I.COLUMN_NAME || ' VARCHAR2(200)');
END IF;
END LOOP;
END;
表信息表：user_tables
-- 将当前scott用户前五个表格，全部都备份一份出来
--比如，如果有一个emp表，那么将这个表复制一个新的表，
--名字为emp_20230418; --(系统当前的年月日)
DECLARE
CURSOR C_A IS
SELECT TABLE_NAME FROM user_tables WHERE ROWNUM < 6;
BEGIN
FOR I IN C_A LOOP
--CREATE TABLE 表名 AS SELECT * FROM 表名
EXECUTE IMMEDIATE 'CREATE TABLE ' || I.TABLE_NAME || '_' ||
TO_CHAR(SYSDATE, 'YYYYMMDD') || ' AS SELECT * FROM ' ||
I.TABLE_NAME;
-- DBMS_OUTPUT.put_line('CREATE TABLE '|| I.TABLE_NAME||'_'||
--TO_CHAR(SYSDATE,'YYYYMMDD') ||' AS SELECT * FROM '|| I.TABLE_NAME);
END LOOP;
END;
-----------------------------------------------------------
-- 7.3、查询出每门课程的及格人数和不及格人数（***）
SELECT S.SCOURE,COUNT(CASE WHEN SSCORE <= 60 THEN '不及格' END) AS 不及格 ,
COUNT(CASE WHEN SSCORE > 60 THEN '及格' END) AS 及格
FROM SCORE S GROUP BY S.SCOURE ;
-- 7.4、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，
--分别统计：各分数段人数，课程号和课程名称
SELECT C.SCOURE,C.CNAME,COUNT(CASE WHEN SSCORE BETWEEN 85 AND 100 THEN 1 END) "[100-85]",
COUNT(CASE WHEN SSCORE BETWEEN 70 AND 84 THEN 1 END) "[85-70]",
COUNT(CASE WHEN SSCORE BETWEEN 60 AND 69 THEN 1 END) "[70-60]",
COUNT(CASE WHEN SSCORE <60 THEN 1 END ) "[<60]"
FROM SCORE S JOIN COURSE C ON S.SCOURE = C.SCOURE
GROUP BY C.SCOURE,C.CNAME;
-- 7.5、查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名
SELECT W.SID,W.SNAME FROM SCORE S JOIN STUDENT W ON S.SID = W.SID
WHERE S.SCOURE = 3 AND S.SSCORE > 80;
-- 8.sql面试题：行列如何互换？（****）
--9.多表连接
--9.1、检索0001课程分数小于90，按照分数降序排列的学生信息
-- 9.2、查询不同老师所教授不同课程平均分从高到低显示
SELECT * FROM TEACHER;
SELECT * FROM SCORE;
SELECT * FROM COURSE;
-- 12.6、查询课程编号为“0001”的课程比“0002”
--的课程成绩等于或者低的所有学生的学号，成绩，和课程号
SELECT * FROM SCORE A JOIN SCORE B ON A.SCOURE = 1 AND
B.SCOURE = 2 AND A.SSCORE <=B.SSCORE;
--12.7、查询学过“孟扎扎”老师所教的所有课的同学的学号、姓名
SELECT * FROM TEACHER
-- 12.8、查询没学过"孟扎扎"老师讲授的任一门课程的学生姓名
--先求出学过孟老师教授课程的学生id
SELECT * FROM TEACHER;
SELECT * FROM COURSE;
SELECT * FROM SCORE;
SELECT *
FROM STUDENT
WHERE SID NOT IN (SELECT SID
FROM TEACHER T
JOIN COURSE C
ON T.CTEACHER = C.CTEACHER
JOIN SCORE S
ON S.SCOURE = C.SCOURE
WHERE T.TNAME = '孟扎扎')
-----------------存储过程----------------------------
存储过程是一个命名程序块，包括过程的名称，过程使用的参数以及过程执行的操作。
语法格式：创建存储过程
CREATE [OR REPLACE] PROCEDURE
过程名(参数1 [IN/OUT/IN OUT] 数据类型,参数2 [IN/OUT/IN OUT] 数据类型...)
IS/AS
PL/SQL过程体;
(1)OR REPLACE:表示如果存储过程已经存在，则替换已有的存储过程。
(2)IN 表示传入参数，不可以被赋值;OUT表示传出参数，可以被赋值;
IN OUT表示传入传出参数，可以传入值，可以被赋值，可以返回值。
如果这部分省略，默认表示传入参数，创建存储过程可以带参也可以不带参。
(3)IS/AS:在IS/AS后声明变量不要加DECLARE语句。
(4)参数的数据类型也是不能带长度。
命名规范：
存储过程命名规范:SP_目标表名
存储过程传入参数命名规范:P_参数名(P_START_DATE)
存储过程变量命名规范:V_变量名(V_END_DATE)
--过程创建成功，想要生效必须要调用才会生效
--语法结构 PROCEDURE 不能以数字/特殊符号开头
CREATE OR REPLACE PROCEDURE SP_A
--创建 或者 替换 OR REPLACE 可以省略不写
IS --/AS
--在存储过程中不能写DECLARE
--在IS/BEGIN之间定义变量常量游标
BEGIN
DBMS_OUTPUT.put_line('HELLO WORLD');
END;
--调用
1.0
CALL SP_B();--存储过程名后要加()
2.0
BEGIN
SP_A; --都可以
END;
--IN 默认 入参 OUT 出参 IN OUT 入参/出参
--传入一个员工编号 返回他的姓名
CREATE OR REPLACE PROCEDURE SP_A(P_EMPNO NUMBER)
AS
V_ENAME VARCHAR2(20);
BEGIN
SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = P_EMPNO;
DBMS_OUTPUT.put_line(V_ENAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SQLERRM);
END;
--调用
BEGIN
--SP_A(7654);--传参
SP_A(P_EMPNO=>7788); --可以同时调用多个存储过程
SP_B;
END;
--传入一个员工编号 返回他的姓名 出参
CREATE OR REPLACE PROCEDURE SP_A(P_EMPNO IN NUMBER,
P_ENAME OUT VARCHAR2)
IS
--出参可以作为赋值对象 入参不可以作为赋值目标
BEGIN
SELECT JOB INTO P_ENAME FROM EMP WHERE EMPNO = P_EMPNO;
END;
--调用
DECLARE
V_A VARCHAR2(20);
BEGIN
SP_A(7654,V_A);
DBMS_OUTPUT.put_line(V_A);
END;
--INOUT
--传入一个员工编号 返回他的工资，岗位，姓名
CREATE OR REPLACE PROCEDURE SP_A(P_A IN OUT EMP%ROWTYPE )
IS
BEGIN
SELECT SAL,JOB,ENAME INTO P_A.SAL,P_A.JOB,P_A.ENAME
FROM EMP WHERE EMPNO = P_A.EMPNO;
-- 出参 入参
END;
--调用
DECLARE
V_B EMP%ROWTYPE ; --先入参 7788作为入参使用
BEGIN
V_B.EMPNO := 7788;
SP_A(V_B);
DBMS_OUTPUT.put_line('该员工工资为'||V_B.SAL);
DBMS_OUTPUT.put_line('该员工岗位为'||V_B.JOB);
DBMS_OUTPUT.put_line('该员工姓名为'||V_B.ENAME);
END;
1.传入一个员工编号，输出该员工所在部门名称和平均工资，
使用出参输出
CREATE OR REPLACE PROCEDURE SP_A(P_EMPNO NUMBER,P_DNAME OUT VARCHAR2,
AVG_SAL OUT NUMBER)
IS
V_DEPTNO NUMBER;
BEGIN
SELECT E.DEPTNO,D.DNAME INTO V_DEPTNO,P_DNAME FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE EMPNO = 7369;
SELECT AVG(SAL) INTO AVG_SAL FROM EMP WHERE DEPTNO = V_DEPTNO;
END;
--调用
DECLARE
V_A VARCHAR2(20);
V_B NUMBER;
BEGIN
SP_A(7369,V_A,V_B);
DBMS_OUTPUT.put_line('部门名称 '|| V_A ||'平均工资 ' || V_B);
END;
2.传入一个岗位，修改该岗位下工资最高的姓名改为张三
CREATE OR REPLACE PROCEDURE SP_A(P_JOB VARCHAR2)
IS
BEGIN
-- UPDATE EMP1 SET ENAME = '张三' WHERE SAL = (
--SELECT MAX(SAL) FROM EMP1 WHERE JOB = P_JOB ) AND JOB = P_JOB;
UPDATE EMP1 SET ENAME = '张三' WHERE EMPNO =(
SELECT EMPNO FROM (
SELECT EMPNO FROM EMP1 WHERE JOB = 'CLERK' ORDER BY SAL DESC
) WHERE ROWNUM = 1); --如果工资重复使用RANK DENSE_RANK
--SELECT RANK()OVER(ORDER BY SAL DESC),SAL FROM EMP;
END;
--
BEGIN
SP_A('CLERK');
END;
SELECT * FROM EMP1 FOR UPDATE;
3.传入一个员工编号，判断员工是否存在，存在就删除该员工信息，
不存在就输出请输入正确的员工编号，删除成功输出'删除成功',
反之则输出删除失败。
CREATE OR REPLACE PROCEDURE SP_A(P_EMPNO NUMBER)
IS
V_S NUMBER;
BEGIN
SELECT COUNT(1) INTO V_S FROM EMP WHERE EMPNO = P_EMPNO;
IF V_S = 0 THEN
DBMS_OUTPUT.put_line('输入正确的员工编号');
ELSIF V_S = 1 THEN
DELETE FROM EMP1 WHERE EMPNO = P_EMPNO;
IF SQL%FOUND THEN
DBMS_OUTPUT.put_line('删除成功');
ELSE
DBMS_OUTPUT.put_line('删除失败');
END IF;
END IF;
END;
-- 调用
BEGIN
SP_A(7788);
END;
小练习：
创建一个存储过程，输入参数为员工编号，
输出参数为输入员工所在部门名称和该部门平均工资，
要求在处理过程中使用EMP_0606表作为员工表：
1.若输入员工在ACCOUNTING部门，该员工职位为MANAGER，
则该部门全员涨薪1000，该员工为其他职位，则该部门全员涨薪800；
2.若输入员工在SALES部门，该员工职位为MANAGER，则该部门全员涨薪800，
该员工为其他职位，则该部门全员涨薪600；
3.若输入员工在其他部门，不管该员工什么职位，所在部门全员涨薪500；
4.将上述涨薪操作更新EMP_0606表；
5.平均工资为涨薪后平均工资。
CREATE OR REPLACE PROCEDURE SP_A(P_EMPNO NUMBER,
P_DNAME OUT VARCHAR2,
AVG_SAL OUT NUMBER) IS
V_JOB VARCHAR2(20);
V_DEP NUMBER;
BEGIN
SELECT JOB, DEPTNO INTO V_JOB, V_DEP FROM EMP WHERE EMPNO = P_EMPNO;
SELECT DNAME INTO P_DNAME FROM DEPT WHERE DEPTNO = V_DEP;
IF P_DNAME = 'ACCOUNTING' THEN
--只判断部门
IF V_JOB = 'MANAGER' THEN
--嵌套判断岗位
UPDATE EMP1 SET SAL = SAL + 1000 WHERE DEPTNO = V_DEP;
ELSIF V_JOB != 'MANAGER' THEN
UPDATE EMP1 SET SAL = SAL + 800 WHERE DEPTNO = V_DEP;
END IF;
ELSIF P_DNAME = 'SALES' THEN
IF V_JOB = 'MANAGER' THEN
UPDATE EMP1 SET SAL = SAL + 800 WHERE DEPTNO = V_DEP;
ELSIF V_JOB != 'MANAGER' THEN
UPDATE EMP1 SET SAL = SAL + 600 WHERE DEPTNO = V_DEP;
END IF;
ELSE
UPDATE EMP1 SET SAL = SAL + 500 WHERE DEPTNO = V_DEP;
END IF;
SELECT ROUND(AVG(SAL)) INTO AVG_SAL FROM EMP1 WHERE DEPTNO = V_DEP;
END;
--调用
DECLARE
V_A VARCHAR2(20);
V_B NUMBER;
BEGIN
SP_A(7782,P_DNAME =>V_A ,AVG_SAL => V_B);
DBMS_OUTPUT.put_line(V_A);
DBMS_OUTPUT.put_line(V_B);
END;
2.0
CREATE OR REPLACE PROCEDURE SP_A(P_EMPNO NUMBER,
P_DNAME OUT VARCHAR2,
AVG_SAL OUT NUMBER) IS
V_JOB VARCHAR2(20);
V_DEP NUMBER;
BEGIN
SELECT JOB, DEPTNO INTO V_JOB, V_DEP FROM EMP WHERE EMPNO = P_EMPNO;
SELECT DNAME INTO P_DNAME FROM DEPT WHERE DEPTNO = V_DEP;
IF P_DNAME = 'ACCOUNTING' AND V_JOB = 'MANAGER' THEN
UPDATE EMP1 SET SAL = SAL + 1000 WHERE DEPTNO = V_DEP;
ELSIF P_DNAME = 'ACCOUNTING' AND V_JOB != 'MANAGER' THEN
UPDATE EMP1 SET SAL = SAL + 800 WHERE DEPTNO = V_DEP;
ELSIF P_DNAME = 'SALES' AND V_JOB = 'MANAGER' THEN
UPDATE EMP1 SET SAL = SAL + 800 WHERE DEPTNO = V_DEP;
ELSIF P_DNAME = 'SALES' AND V_JOB != 'MANAGER' THEN
UPDATE EMP1 SET SAL = SAL + 600 WHERE DEPTNO = V_DEP;
ELSE
UPDATE EMP1 SET SAL = SAL + 500 WHERE DEPTNO = V_DEP;
END IF;
SELECT ROUND(AVG(SAL)) INTO AVG_SAL FROM EMP1 WHERE DEPTNO = V_DEP;
END;
SELECT * FROM EMP1;
--全量更新
A 张三 李四 王五
B 张三 李四 王五
--创建目标表
CREATE TABLE EMP_MB AS SELECT * FROM EMP WHERE SAL > 2000;
--创建数据来源表
CREATE TABLE EMP_YB AS SELECT * FROM EMP;
--修改数据来源表的一条数据
SELECT * FROM EMP_YB FOR UPDATE;
--创建全量更新的存储过程
CREATE OR REPLACE PROCEDURE SP_A IS
BEGIN
--1.先清空目标表
--DELETE FROM EMP_MB;
EXECUTE IMMEDIATE ' TRUNCATE TABLE EMP_MB';
--2.把源数据表导入到目标表
INSERT INTO EMP_MB
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
SELECT E.EMPNO,
E.ENAME,
E.JOB,
E.MGR,
E.HIREDATE,
E.SAL,
E.COMM,
E.DEPTNO
FROM EMP_YB E;
--不可以写*要把所有的字段名都写出来
COMMIT;
END;
--调用
BEGIN
SP_A;
END;
SELECT * FROM EMP_MB;
SELECT * FROM EMP_YB;
--增量更新
A 1张三 2李五 3赵六
B 1张三 2李五 3赵六
--存在就更新 不存在就插入
--MERGE INTO Oracle
MERGE INTO 目标表
USING (源数据表)
ON (比较条件)
WHEN MATCHED THEN --存在就更新
UPDATE SET --不要写表名
WHEN NOT MATCHED THEN -- 不存在就插入
INSERT() VALUES(); --INTO 表名都不需要写
--创建目标表
DROP TABLE EMP_YB;
CREATE TABLE EMP_MB AS SELECT E.*, SYSDATE-1 AS UP_DATE FROM EMP E;
--创建源数据表
CREATE TABLE EMP_YB AS SELECT E.*, SYSDATE-1 AS UP_DATE FROM EMP E;
SELECT * FROM EMP_YB;
SELECT * FROM EMP_MB;
--修改源数据表的数据
UPDATE EMP_YB SET ENAME = '张三',UP_DATE = SYSDATE
WHERE EMPNO = 7369;
UPDATE EMP_YB SET JOB = '保安',UP_DATE = SYSDATE
WHERE EMPNO = 7788;
INSERT INTO EMP_YB VALUES(8888,'IKUN','练习生',9999,TRUNC(SYSDATE),
9999,9999,90,SYSDATE);
--创建增量更新存储过程
CREATE OR REPLACE PROCEDURE SP_A(P_KS VARCHAR2,P_JS VARCHAR2)
IS
V_KS DATE :=TO_DATE(P_KS,'YYYYMMDD HH24MISS');
V_JS DATE :=TO_DATE(P_JS,'YYYYMMDD HH24MISS');
BEGIN
MERGE INTO EMP_MB M
USING (SELECT Y.EMPNO,
Y.ENAME,
Y.JOB,
Y.MGR,
Y.HIREDATE,
Y.SAL,
Y.COMM,
Y.DEPTNO,
Y.UP_DATE FROM EMP_YB Y WHERE Y.UP_DATE >= V_KS AND
Y.UP_DATE <= V_JS) Y
ON (M.EMPNO = Y.EMPNO) --通过主键来比较
WHEN MATCHED THEN --存在就修改
UPDATE SET
-- M.EMPNO = Y.EMPNO, 比较字段不可以修改
M.ENAME = Y.ENAME,
M.JOB = Y.JOB,
M.MGR = Y.MGR,
M.HIREDATE = Y.HIREDATE,
M.SAL = Y.SAL,
M.COMM = Y.COMM,
M.DEPTNO = Y.DEPTNO,
M.UP_DATE = Y.UP_DATE
WHEN NOT MATCHED THEN
INSERT (M.EMPNO,
M.ENAME,
M.JOB,
M.MGR,
M.HIREDATE,
M.SAL,
M.COMM,
M.DEPTNO,
M.UP_DATE)
VALUES (Y.EMPNO,
Y.ENAME,
Y.JOB,
Y.MGR,
Y.HIREDATE,
Y.SAL,
Y.COMM,
Y.DEPTNO,
Y.UP_DATE);
--COMMIT;
END;
--调用
BEGIN
SP_A('20230607 000000',
'20230608 000000');
END;
--查看结果
SELECT * FROM EMP_MB;
SELECT Y.EMPNO,
Y.ENAME,
Y.JOB,
Y.MGR,
Y.HIREDATE,
Y.SAL,
Y.COMM,
Y.DEPTNO,
Y.UP_DATE FROM EMP_YB Y ;
--根据部门编号进行增量更新
CREATE OR REPLACE PROCEDURE SP_A(P_DEPTNO NUMBER)
IS
BEGIN
MERGE INTO EMP_MB M
USING (SELECT Y.EMPNO,
Y.ENAME,
Y.JOB,
Y.MGR,
Y.HIREDATE,
Y.SAL,
Y.COMM,
Y.DEPTNO,
Y.UP_DATE FROM EMP_YB Y WHERE Y.DEPTNO = P_DEPTNO) Y
ON (Y.EMPNO = M.EMPNO)
WHEN MATCHED THEN
UPDATE SET
M.ENAME = Y.ENAME,
M.JOB = Y.JOB,
M.MGR = Y.MGR,
M.HIREDATE = Y.HIREDATE,
M.SAL = Y.SAL,
M.COMM = Y.COMM,
M.DEPTNO = Y.DEPTNO,
M.UP_DATE = Y.UP_DATE
WHEN NOT MATCHED THEN
INSERT(M.EMPNO,
M.ENAME,
M.JOB,
M.MGR,
M.HIREDATE,
M.SAL,
M.COMM,
M.DEPTNO,
M.UP_DATE)
VALUES(Y.EMPNO,
Y.ENAME,
Y.JOB,
Y.MGR,
Y.HIREDATE,
Y.SAL,
Y.COMM,
Y.DEPTNO,
Y.UP_DATE);
END;
--调用
BEGIN
SP_A(20);
END;
--游标 通用
SELECT * FROM EMP_MB;
SELECT * FROM EMP_YB FOR UPDATE;
CREATE OR REPLACE PROCEDURE SP_A(P_KS VARCHAR2, P_JS VARCHAR2) IS
V_KS DATE := TO_DATE(P_KS, 'YYYYMMDD HH24MISS');
V_JS DATE := TO_DATE(P_JS, 'YYYYMMDD HH24MISS');
CURSOR C_MB IS
SELECT Y.EMPNO,
Y.ENAME,
Y.JOB,
Y.MGR,
Y.HIREDATE,
Y.SAL,
Y.COMM,
Y.DEPTNO,
Y.UP_DATE
FROM EMP_YB Y
WHERE Y.UP_DATE >= V_KS
AND Y.UP_DATE <= V_JS;
V_S NUMBER;
BEGIN
FOR I IN C_MB LOOP
SELECT COUNT(1) INTO V_S FROM EMP_MB WHERE M.EMPNO = I.EMPNO;
IF V_S = 1 THEN
UPDATE EMP_MB M
SET M.ENAME = I.ENAME,
M.DEPTNO = I.DEPTNO,
M.COMM = I.COMM,
M.HIREDATE = I.HIREDATE,
M.SAL = I.SAL,
M.MGR = I.MGR,
M.JOB = I.JOB,
M.UP_DATE = I.UP_DATE WHERE EMPNO = I.EMPNO;
ELSIF V_S = 0 THEN
INSERT INTO EMP_MB
VALUES
(I.EMPNO,
I.ENAME,
I.JOB,
I.MGR,
I.HIREDATE,
I.SAL,
I.COMM,
I.DEPTNO,
I.UP_DATE);
END IF;
END LOOP;
--COMMIT;
END;
--调用
BEGIN
SP_A('20230607 000000','20230608 000000');
END;
-- 删除存储过程
DROP PROCEDURE SP_B;
--自定义函数 计算指标
函数有返回值 存储过程没有
函数可以使用查询语句调用 存储过程不可以
函数没有出参
--语法 FUNCTION 返回当前系统日期
CREATE OR REPLACE FUNCTION NOW
RETURN DATE --声明返回值的数据类型
AS --/IS
--声明变量
V_A DATE;
BEGIN
V_A:=SYSDATE;
RETURN V_A;
END;
--定义一个函数，可以计算两数之间的偶数和
CREATE OR REPLACE FUNCTION FUN_A(P_A NUMBER,P_B NUMBER)
RETURN NUMBER
IS
V_SUM NUMBER:=0;
BEGIN
FOR I IN P_A..P_B LOOP
IF MOD(I,2) = 0 THEN
V_SUM := V_SUM + I;
END IF;
END LOOP;
RETURN V_SUM;
END;
--1.0在SQL语句中调用 2.0 在begin end 之间调用
1.0
SELECT FUN_A(1,3) FROM DUAL;
2.0
DECLARE
V_SUM NUMBER;
BEGIN
V_SUM := FUN_A(0,10);
DBMS_OUTPUT.put_line(V_SUM);
END;
传入三个数字求出之间的最大值
CREATE OR REPLACE FUNCTION FUN_A(P_A NUMBER,P_B NUMBER,P_C NUMBER)
RETURN NUMBER
IS
V_MAX NUMBER;
BEGIN
V_MAX := P_A;
IF P_B >=P_A THEN
V_MAX := P_B;
END IF;
IF P_C >=P_A AND P_C >=P_B THEN
V_MAX := P_C;
END IF;
RETURN V_MAX;
END;
--调用
SELECT FUN_A(30,30,90) FROM DUAL;
返回用户明年预期的工资是多少，
输入一个员工的编号，10号部门涨工资10%，20号涨20%，30号涨30%，
返回这个员工的预期工资。
CREATE OR REPLACE FUNCTION FUN_A(P_EMPNO NUMBER)
RETURN NUMBER
IS
V_DEPTNO NUMBER;
V_SAL NUMBER;
BEGIN
SELECT DEPTNO,SAL INTO V_DEPTNO,V_SAL FROM EMP WHERE EMPNO
= P_EMPNO;
IF V_DEPTNO = 10 THEN
RETURN V_SAL * 1.1;
ELSIF V_DEPTNO=20 THEN
RETURN V_SAL * 1.2;
ELSIF V_DEPTNO = 30 THEN
RETURN V_SAL * 1.3;
END IF;
END;
--调用
SELECT FUN_A(7788) FROM DUAL;
创建一个函数，输入一个字符串提取出字符串中的数字(两种写法)
CREATE OR REPLACE FUNCTION FUN_A(P_A VARCHAR2)
RETURN NUMBER
IS
V_A NUMBER;
BEGIN
SELECT REGEXP_REPLACE(P_A,'[^0-9]','') INTO V_A FROM DUAL;
RETURN V_A;
END;
--调用
SELECT FUN_A('ZSDS123FSDF456') FROM DUAL;
CREATE OR REPLACE FUNCTION FUN_A(P_A VARCHAR2)
RETURN NUMBER
IS
V_A NUMBER;
BEGIN
FOR I IN 1..LENGTH(P_A) LOOP
IF ASCII(SUBSTR(P_A,I,1)) BETWEEN 48 AND 57 THEN
V_A := V_A || SUBSTR(P_A,I,1);
END IF;
END LOOP;
RETURN V_A;
END;
SELECT ASCII('9') FROM DUAL;
创建一个函数，输入员工编号返回员工姓名
CREATE OR REPLACE FUNCTION FUN_A(P_EMPNO NUMBER)
RETURN VARCHAR2
IS
V_ENAME VARCHAR2(20);
BEGIN
SELECT ENAME INTO V_ENAME FROM EMP WHERE
EMPNO = P_EMPNO;
RETURN V_ENAME;
END;
SELECT FUN_A(7788) FROM DUAL;
---异常
系统的预定义异常：数据库给某个错误赋予了错误的编号和错误的名字（21种）
no_data_found 查询没有数据
too_many_rows 查询数据太多
value_error 赋值的错误
zero_divide 除数为0
......
DECLARE
V_A NUMBER;
BEGIN
--SELECT ENAME INTO V_A FROM EMP WHERE EMPNO = 7788;
V_A := 10/0;
EXCEPTION WHEN NO_DATA_FOUND THEN--只针对查询结果为空的情况
DBMS_OUTPUT.put_line('你好啊');
WHEN too_many_rows THEN --只针对查询结果为多行的情况
DBMS_OUTPUT.put_line('再见');
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.put_line('早上好');
WHEN zero_divide THEN
DBMS_OUTPUT.put_line('数学重考');
END;
--自定义异常
--员工不配查询老板信息 7839
DECLARE
V_A NUMBER:=&FDDG;
ZDDYC EXCEPTION;--自定义异常名
V_ENAME VARCHAR2(20);
BEGIN
SELECT ENAME INTO V_ENAME FROM EMP
WHERE EMPNO = V_A;
IF V_A = 7839 THEN
RAISE ZDDYC;
END IF;
DBMS_OUTPUT.put_line(V_ENAME);
EXCEPTION WHEN ZDDYC THEN
DBMS_OUTPUT.put_line('您不配');
-- WHEN OTHERS THEN
-- DBMS_OUTPUT.put_line('他也不配');
END;
--引发程序报错
DECLARE
V_A NUMBER:=&DFD;
V_ENAME VARCHAR2(20);
BEGIN
SELECT ENAME INTO V_ENAME FROM EMP
WHERE EMPNO = V_A;
IF V_A = 7839 THEN
-- -20000 - -20999
RAISE_APPLICATION_ERROR(-20000,'员工不配查询老板');
END IF;
DBMS_OUTPUT.put_line(V_ENAME);
--EXCEPTION WHEN OTHERS THEN
-- DBMS_OUTPUT.put_line('您不配');
END;
--序列 SEQUENCE 递增
SEQUENCE
id name (id) PRIMARY KEY 非空唯一 自动递增
1 张三
2 李四
3
4
5
6
7
8
--创建序列
--属性
--CURRVAL查看序列当前值 使用过序列后才能查看当前值
--NEXTVAL 查看序列下一个值
--删除序列
DROP SEQUENCE TEST1;
--创建序列
CREATE SEQUENCE TEST1
MINVALUE 1 --最小值
MAXVALUE 50--最大值
CYCLE --开启循环 默认不开启 NOCYCLE 不循环
CACHE 20 --缓存20条序列信息
INCREMENT BY 1 --设置步长
START WITH 1 --起始位置
;
--查看序列
SELECT TEST1.NEXTVAL FROM DUAL;
INSERT INTO EMP(EMPNO)VALUES(TEST1.NEXTVAL,'占山');
--日志表 记录程序的运行状态
DROP TABLE LOG_TABLE;
--创建日志表 LOG
CREATE TABLE LOG_TABLE(
LOG_ID NUMBER(20), --日志id
PROCEDURE_NAME VARCHAR2(100), --程序名称
KS_DATE DATE, --开始时间
JS_DATE DATE, --结束时间
ZT VARCHAR2(20), --状态
SQL_ERROR VARCHAR2(1000) --报错信息
);
SELECT * FROM LOG_TABLE;
--创建向日志表插入数据的存储过程
CREATE OR REPLACE PROCEDURE SP_LOG(P_ID NUMBER,
P_NAME VARCHAR2,P_KS DATE,P_JS DATE,P_ZT VARCHAR2,
P_ERROR VARCHAR2)
IS
BEGIN
INSERT INTO LOG_TABLE VALUES(P_ID,P_NAME,P_KS,P_JS,P_ZT,P_ERROR);
COMMIT;
END;
存储过程 =》 日志的存储过程
--在全量更新存储过程中调用日志存储过程
CREATE OR REPLACE PROCEDURE SP_A IS
V_NAME VARCHAR2(300);
V_KS DATE;
V_JS DATE;
BEGIN
--SELECT INTO
V_KS := SYSDATE;
V_NAME := 'SP_A';
EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP_MB';
INSERT INTO EMP_MB
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, UP_DATE)
SELECT Y.EMPNO,
Y.ENAME,
Y.JOB, --↓
Y.MGR, --↑
Y.HIREDATE,
Y.SAL,
Y.COMM,
Y.DEPTNO,
Y.UP_DATE
FROM EMP_YB Y;
V_JS := SYSDATE;
-- SP_LOG(LOG_XL.NEXTVAL, V_NAME, V_KS, V_JS, '成功', NULL);
-- INSERT INTO LOG_TABLE VALUES(LOG_XL.NEXTVAL, V_NAME, V_KS, V_JS, '成功', NULL);
EXCEPTION
WHEN OTHERS THEN
V_JS := SYSDATE; --从新获取一下结束时间
SP_LOG(LOG_XL.NEXTVAL, V_NAME, V_KS, V_JS, '失败', SQLERRM);
END;
--创建一个序列
CREATE SEQUENCE LOG_XL
MINVALUE 1
MAXVALUE 999999
CACHE 50
;
--调用存储过程
BEGIN
SP_A();
END;
9.7包 PACKAGE
包就是把相关的存储过程、函数、变量、常量和游标等PL/SQL程序组合在一起，
并赋予一定的管理功能的程序块。
一个程序包由两部分组成：包定义和包体。其中包定义部分声明包内
数据类型、变量、常量、游标、子程序和函数等元素，这些元素为包的共有元素，
包体则定义了包定义部分的具体实现。
-------------创建包头------------------------------
CREATE OR REPLACE PACKAGE BAO
--不要写BEGIN
IS
TYPE ABCD IS RECORD (A NUMBER ,B DATE , C VARCHAR2(90));
--声明共有变量常量游标，声明包内函数和存储过程
V_A EMP%ROWTYPE; --定义共有变量
--定义俩函数 一个两数乘积 一个求偶数和 一个存储过程 输出HELLOWOLD
FUNCTION FUN_A(P_A NUMBER,P_B NUMBER)
RETURN NUMBER;--只需要写函数名，参数名，数据类型，返回值数据类型
FUNCTION FUN_B(P_A NUMBER,P_B NUMBER)
RETURN NUMBER;
PROCEDURE SP_A;--声明一个存储过程 写存储过程名，参数
END;
------------创建包体---------------------------------
CREATE OR REPLACE PACKAGE BODY BAO IS
--函数存储过程具体代码
--包头包体保持完全一致
FUNCTION FUN_A(P_A NUMBER, P_B NUMBER) RETURN NUMBER IS
--V_A NUMBER;
BEGIN
BAO.V_A := P_A * P_B;
RETURN BAO.V_A; --P_A * P_B
END FUN_A; --END 程序名
--第二个函数
FUNCTION FUN_B(P_A NUMBER, P_B NUMBER) RETURN NUMBER IS
V_SUM NUMBER:=0;
BEGIN
FOR I IN P_A .. P_B LOOP
IF MOD(I, 2) = 0 THEN
V_SUM := V_SUM + I;
END IF;
END LOOP;
RETURN V_SUM;
END FUN_B;
--存储过程
PROCEDURE SP_A IS
BEGIN
DBMS_OUTPUT.put_line('HELLO WORLD');
END SP_A;
END;
--调用
--包名打点调用
SELECT BAO.FUN_A(33,99) FROM DUAL;
DECLARE
--V_A NUMBER;
BEGIN
BAO.V_A:=BAO.FUN_A(10,20);
DBMS_OUTPUT.put_line(BAO.V_A);
--V_A:=BAO.FUN_B(0,10);
--DBMS_OUTPUT.put_line(V_A);
BAO.SP_A;
END;
然后我要把它们放在同一个包里面，进行统一的管理
存储过程：
新增一个部门，输入一个部门的编号、名称、地点，
如果这个编号和名字没有重复，就插入数据，否则就弹框提示该部门已存在
函数：传入一个年份，返回他的属相，已知1900年生肖是鼠
鼠、牛、虎、兔、龙、蛇、马、羊、猴、鸡、狗、猪
1901 牛
1918 🐎
(1918 - 1900) /12
存储过程，向dept表传入部门编号 ，在部门表里删除该部门信息，
删除成功输成功，失败返回失败
---------------------------0609---------------------------------
--创建包头
CREATE OR REPLACE PACKAGE BAO
IS
PROCEDURE SP_INSERT(P_DEPTNO NUMBER,P_DNAME VARCHAR2,P_LOC VARCHAR2);
PROCEDURE SP_DELETE(P_DEPTNO NUMBER);
FUNCTION FUN_A(P_Y NUMBER)
RETURN VARCHAR2;
V_A EMP%ROWTYPE;
END;
--创建包体
CREATE OR REPLACE PACKAGE BODY BAO
IS
--第一个存储过程
PROCEDURE SP_INSERT(P_DEPTNO NUMBER,P_DNAME VARCHAR2,
P_LOC VARCHAR2)
IS
BEGIN
SELECT COUNT(1) INTO BAO.V_A.EMPNO FROM DEPT WHERE DEPTNO = P_DEPTNO
OR DNAME = P_DNAME;
IF BAO.V_A.EMPNO IN(1,2) THEN
RAISE_APPLICATION_ERROR(-20001,'该部门已存在');
ELSIF BAO.V_A.EMPNO = 0 THEN
INSERT INTO DEPT VALUES(P_DEPTNO,P_DNAME,P_LOC);
END IF;
END SP_INSERT;
--第二个存储过程
PROCEDURE SP_DELETE(P_DEPTNO NUMBER)
IS
BEGIN
DELETE FROM DEPT WHERE DEPTNO = P_DEPTNO;
IF SQL%FOUND THEN
DBMS_OUTPUT.put_line('删除成功');
ELSE
DBMS_OUTPUT.put_line('删除失败');
END IF;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line('删除失败' || SQLERRM);
END SP_DELETE;
--第三个函数
FUNCTION FUN_A(P_Y NUMBER)
RETURN VARCHAR2
IS
BEGIN
BAO.V_A.SAL:=MOD(P_Y-1900,12);
IF P_Y - 1900 >=0 THEN
BAO.V_A.ENAME:=SUBSTR('鼠牛虎兔龙蛇马羊猴鸡狗猪',BAO.V_A.SAL+1,1);
ELSIF P_Y-1900 < 0 THEN
BAO.V_A.ENAME:=SUBSTR('鼠牛虎兔龙蛇马羊猴鸡狗猪',BAO.V_A.SAL,1);
END IF;
RETURN BAO.V_A.ENAME;
END FUN_A;
END;
--调用
SELECT BAO.FUN_A(100) FROM DUAL;
BEGIN
--调用第一个存储过程
--BAO.SP_INSERT(80,'安保','上海');
--调用第二个存储过程
BAO.SP_DELETE(80);
END;
-----------约束---------------------
CONSTRAINT
1.PRIMARY KEY: 主键约束 非空唯一
2.FOREIGN KEY: 外键约束
3.CHECK: 检查约束
4.UNIQUE: 唯一约束
5.NOT NULL: 非空约束
--给emp1表里的empno字段添加一个主键约束
--一张表里只能有一个主键约束，但是可以同时约束多个字段
ALTER TABLE EMP1 ADD CONSTRAINT ZJYS PRIMARY KEY(EMPNO,ENAME);
--ALTER TABLE 表名 ADD CONTRAINT 约束名 约束类型(约束字段);
--测试约束
INSERT INTO EMP1(EMPNO)VALUES(7788);--违反唯一效果
INSERT INTO EMP1(SAL) VALUES(3000); --违反非空效果
SELECT * FROM EMP1;
--检查约束 性别 = 男 OR 性别 = 女
--字段下的值本身不能为反约束条件
ALTER TABLE EMP1 ADD CONSTRAINT JCYS CHECK(SAL < 7000 AND SAL > 700);
--CHECK(SEX='男' OR SEX = '女')
--测试
INSERT INTO EMP1(SAL) VALUES(3000);--违反检查约束 SAL>700
--删除约束
ALTER TABLE EMP1 DROP CONSTRAINT ZJYS;
--唯一约束
--当给字段添加唯一约束时会自动为该字段创建唯一索引
ALTER TABLE EMP1 ADD CONSTRAINT WYYS UNIQUE(ENAME);
--约束测试
INSERT INTO EMP1(ENAME)VALUES('SCOTT'); --违反唯一条件姓名重复
INSERT INTO EMP1(JOB) VALUES('保安');--唯一约束可以为空
--非空约束 非空约束可以重复
ALTER TABLE EMP1 MODIFY JOB NOT NULL;
--测试效果
INSERT INTO EMP1(EMPNO) VALUES(1111);--违反非空约束
--删除非空约束
ALTER TABLE EMP1 MODIFY JOB NULL;--JOB字段可以为空
--外键约束 一个表的外键必须是另一个表的主键
--给emp1表deptno字段添加外键约束，前提给dept1里deptno加主键约束
SELECT * FROM EMP1;
SELECT * FROM DEPT1;
--给DEPT1表创建主键字段
ALTER TABLE DEPT1 ADD CONSTRAINT DEPT_ZJ PRIMARY KEY(DEPTNO);
--再给EMP1里添加外键约束
ALTER TABLE EMP1 ADD CONSTRAINT WJYS FOREIGN KEY(DEPTNO)
REFERENCES DEPT1(DEPTNO);
--主键表不能删除已在从表外键存在的数据
DELETE FROM DEPT1 WHERE DEPTNO = 40;
--从表外键不能添加未在主键表存在的数据
INSERT INTO EMP1(DEPTNO) VALUES(50);
--在建表时添加约束
--1.0
CREATE TABLE YS_TABLE (
EMPNO NUMBER PRIMARY KEY, --添加主键约束
ENAME VARCHAR2(20) UNIQUE , --添加唯一约束
SEX VARCHAR2(20) CHECK(SEX='男' OR SEX = '女'),--检查约束
SAL NUMBER NOT NULL, --非空约束
DEPTNO NUMBER CONSTRAINT WJYS1 REFERENCES DEPT1(DEPTNO) --外键约束
--不需要写外键约束的关键字 FOREIGN KEY
);
--测试效果
INSERT INTO YS_TABLE VALUES(1,'SCOTT','男转女',2000,20);
--违反检查约束
INSERT INTO YS_TABLE VALUES(1,'SCOTT','女',2000,90);
--违反外键约束
INSERT INTO YS_TABLE VALUES(2,'张三','女',2000,90);
--违反非空约束
INSERT INTO YS_TABLE VALUES(2,'张三','女',2000,20);
--2.0
CREATE TABLE YS_TABLE (
EMPNO NUMBER PRIMARY KEY, --添加主键约束
ENAME VARCHAR2(20) UNIQUE , --添加唯一约束
DEPTNO NUMBER, --外键约束
SEX VARCHAR2(20) CHECK(SEX='男' OR SEX = '女'),--检查约束
SAL NUMBER NOT NULL ,--非空约束
--在建表语句最后添加外键约束
CONSTRAINT WJYS1 FOREIGN KEY(DEPTNO) REFERENCES DEPT1(DEPTNO)
);
-------------拉链表---------------------------
--记录历史数据 (缓慢变化维)
--拉链表和普通表的区别 多了开始时间 和结束时间
--创建源数据表
CREATE TABLE YB(
ID NUMBER,
NAME VARCHAR2(20),
SAL NUMBER
);
SELECT * FROM YB;
--向原表插入数据
INSERT INTO YB VALUES(1,'坤坤',2000);
INSERT INTO YB VALUES(2,'张三',3000);
INSERT INTO YB VALUES(3,'李四',4000);
下单 20230607 20230608
发货 20230608 20230609
派送 20230609 20230610
签收 20230610 20230610
--创建一个拉链表
DROP TABLE LLB;
CREATE TABLE LLB AS SELECT Y.*,SYSDATE AS KS_DATE,SYSDATE AS
JS_DATE FROM YB Y WHERE 1=2;
--把原表数据同步到拉链表
INSERT INTO LLB SELECT Y.ID,
Y.NAME,
Y.SAL,
TRUNC(SYSDATE)-1000,
TO_DATE(99991231,'YYYYMMDD')
FROM YB Y;
--修改原表数据
UPDATE YB SET NAME = '张三' WHERE ID = 2;
UPDATE YB SET SAL = 9999 WHERE ID = 1;
--新增原表数据
INSERT INTO YB VALUES(4,'菜徐坤',5000);
--把产生变化的数据同步到拉链表
INSERT INTO LLB SELECT Y.*,TRUNC(SYSDATE),
TO_DATE(99991231,'YYYYMMDD') FROM YB Y
WHERE NOT EXISTS (SELECT 1 FROM LLB L
WHERE L.ID= Y.ID AND L.NAME = Y.NAME AND
L.SAL = Y.SAL AND L.JS_DATE = TO_DATE(99991231,'YYYYMMDD')) ;
拉链表 1 张三 2000 失效 20230609
1 张四 2000 生效 99991231
原表 1 张三 2000 生效
--修改拉链表里重复数据的失效时间
UPDATE LLB L SET JS_DATE = TRUNC(SYSDATE)
--SELECT * FROM LLB L
WHERE EXISTS (SELECT 1 FROM YB Y WHERE
L.ID = Y.ID AND (L.NAME != Y.NAME OR L.SAL != Y.SAL)
)AND JS_DATE = TO_DATE(99991231,'YYYYMMDD');
SELECT * FROM LLB;--有生效/失效
SELECT * FROM YB;--只有生效
第一次 张三
第二次 张四
第三次 张三
--EMP当作源数据表 ，根据源数据表创建EMP_LLB
--把原表新增数据同步到拉链表，并且修改失效时间
SELECT * FROM EMP A JOIN EMP B ON NVL(A.COMM,0) = B.COMM;
----------------------------周末礼包----------------------------------------
10.2视图
视图(View)实际上是一张或者多张表上的预定义查询，这些表称为基表，
从视图中查询信息与从表中查询信息的方法完全相同。只需要SELECT FROM即可。
视图具有以下优点：
1.可以限制用户只能通过视图检索数据，这样就可以对最终用户屏蔽建表时
底层的基表，具有安全性。
2.可以将复杂的查询保存为视图，屏蔽复杂性。
创建视图：
语法格式：
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW VIEW_NAME
AS
SELECT语句
[WITH READ ONLY]
给SCOTT用户创建视图的权限：
1. win+r 输入cmd进入命令窗
2. 在命令窗中输入SQLPLUS进入ORACLE
3. 用户名:SYSTEM 密码:123456
4. grant create view to scott;给SCOTT用户创建视图的权限
5. EXIT;退出oracle
创建视图实例：
CREATE VIEW EMP_10
AS
SELECT * FROM EMP WHERE DEPTNO=10;
1.OR REPLACE:如果视图已经存在，则替换旧视图；
2.FORCE:即使基表不存在，也可以创建视图，但该视图不能正常使用，
当基表创建成功后，视图才能正常使用。
3.NOFORCE:如果基表不存在，无法创建视图，该项是默认选项。
4.WITH READ ONLY:默认可以通过视图对基表执行增删改操作，但是通常不是用对基表进行DML操作。
WITH READ ONLY说明视图是只读视图，不能通过该视图进行增删改操作，
现实开发中，基本上不通过视图对表中的数据进行增删改操作。
--视图名和表名不能冲突
--视图本身是没有任何数据 ,视图的数据会随着基表的变化而变化
CREATE OR REPLACE VIEW AA
AS SELECT * FROM EMP1 WHERE DEPTNO IN (10,20); --基表
--删除基表数据
DELETE FROM EMP1 WHERE DEPTNO = 30;
SELECT * FROM EMP1;
--基表数据减少，视图数据也会减少
SELECT * FROM AA;
--不会去删除基表数据,一般通过WHERE过滤来显示数据
--创建只读视图
CREATE OR REPLACE VIEW AA
AS
SELECT * FROM EMP1
WITH READ ONLY; --只读
--不能进行增删改
DELETE FROM AA WHERE ENAME = '张三';
--强制创建视图 默认NOFORCE
CREATE OR REPLACE FORCE VIEW AA
AS
SELECT * FROM EMP11 --即使基表不存在可以强制创建视图
WITH READ ONLY;
CREATE TABLE EMP11 AS SELECT * FROM EMP;
SELECT * FROM AA;
10.3索引
数据库中索引(index)的概念与目录的概念非常类似。如果某列出现在查询的条件中，
而该列的数据是无序的，查询时只能从第一行开始一行一行的匹配。创建索引就是对某些特定列中
的数据排序，生成独立的索引表。在某列上创建索引后，如果该列出现在查询条件中，oracle
会比较全表扫描与索引扫描的代价，如果索引扫描代价小，那oracle会自动引用该索引，先从索引表中查询
出符合条件记录的ROWID,由于ROWID是记录的物理地址，因此可以根据ROWID快速定位到具体的记录，
表中的数据非常多时，引用索引带来的查询效率非常可观。
什么时候应该用索引：
1.如果表中的某些字段经常被查询，并且作为查询条件出现，可以考虑为该列创建索引。
2.经常作为关联条件的字段，也可以考虑为这些字段创建索引
基本原则：当任何单个查询(sql)要查询的行数少于或等于整个表行数的10%时，索引非常有用。
创建索引：
oracle数据库会为表的主键和包含唯一约束的列，自动创建唯一索引；索引可以提升查询效率，
但是在数据发生增删改的时候需要更新索引。
索引类型：
唯一索引：索引列的值必须是唯一的(emp.empno)
普通索引：索引列的值没有唯一限制(emp.deptno)
组合索引：多个字段创建索引(deptno+ename)
SELECT * FROM EMP;
语法格式：
CREATE [UNIQUE] INDEX 索引名 on 表名(列名[,列名])
1.UNIQUE:指定索引列上的值必须是唯一的，称为唯一索引
2.列名：为表中某个列创建索引，当我们对多个列创建索引的时候，这种索引就是组合索引。
1.创建普通索引
CREATE INDEX INDEX_DEPTNO ON EMP_1010(DEPTNO);
2.创建唯一索引
CREATE UNIQUE INDEX UNI_DEPTNO ON EMP_1010(EMPNO);
3.创建组合索引
CREATE INDEX EMP_JOB ON EMP_1010(JOB,DEPTNO);
删除索引：
DROP INDEX INDEX_DEPTNO;
--索引不一定比全表扫描快条件可以过滤掉90%以上数据时索引快
--最终展示数据占全部数据一小部分时索引扫描块
--经常会用作条件筛选的字段,经常用作表关联的字段
--主键约束/唯一约束会自动给字段添加唯一索引
SELECT * FROM EMP11;
--添加普通索引 索引名不能重复
CREATE INDEX PTSY ON EMP11(SAL);
--普通索引可以重复可以为空
INSERT INTO EMP11(ENAME) VALUES('3000');
--F5查看执行计划 PLSQL里独有的快捷键
SELECT * FROM EMP11 WHERE SAL > 2000;
--没有索引的表
SELECT * FROM EMP WHERE SAL > 2000;
--EXPLAIN PLAN FOR 查看执行计划
EXPLAIN PLAN FOR
SELECT * FROM EMP11 WHERE SAL > 2000;
SELECT * FROM TABLE(DBMS_XPLAN.display);
--创建唯一索引 建立索引字段下不可以有重复值
CREATE UNIQUE INDEX WYSY ON EMP11(EMPNO);
--多个条件之间用OR关联会导致索引失效
SELECT * FROM EMP11 WHERE EMPNO = 9999 AND SAL > 2000;
--组合索引 最左原则
CREATE INDEX ZHSY ON EMP11(DEPTNO,EMPNO,SAL);
--没有使用最左字段 索引失效
SELECT * FROM EMP11 WHERE SAL = 7788;
--使用了最左边的字段，索引生效
SELECT * FROM EMP11 WHERE SAL = 7788 AND DEPTNO = 10;
--删除索引
DROP INDEX PTSY;
索引失效:
1.隐式转换会导致索引失效(开发中常犯的错误)
--将EMP1表中SAL字段数据类型改为VARCHAR2
ALTER TABLE EMP1 MODIFY SAL VARCHAR2(20);
--给sal字段建立索引
CREATE INDEX SY1 ON EMP11(SAL);
--字段发生隐式转换 索引失效
SELECT * FROM EMP1 WHERE SAL > 2000;
--字段没有发生隐式转换 索引生效
SELECT * FROM EMP1 WHERE SAL > '2000';
--SAL是数字类型 SAL有普通索引 会走索引 因为字段没有发生隐式转换
SELECT * FROM EMP11 WHERE SAL = '2000';
2.对索引列进行运算会导致索引失效(+ - * / != > <)
--算数运算导致索引失效
SELECT * FROM EMP WHERE EMPNO +1000 > 2000;
SELECT * FROM EMP WHERE EMPNO > 2000-1000;
3.NOT IN,NOT EXISTS会导致索引失效
-- IN EXISTS 不会导致索引失效
SELECT * FROM EMP WHERE EMPNO IN (7369,7788);
-- NOT IN NOT EXISTS 导致索引失效
SELECT * FROM EMP WHERE EMPNO NOT IN (7369,7788);
SELECT * FROM EMP A WHERE EXISTS (
SELECT * FROM EMP1 B WHERE A.EMPNO = 7788
);
4.组合索引的时候，单独引用索引中非第一位置的索引，会导致索引失效。
SELECT * FROM EMP_1010 WHERE JOB='CLERK' AND DEPTNO=20;--索引生效，索引扫描
SELECT * FROM EMP_1010 WHERE DEPTNO=20 AND JOB='CLERK';--索引生效，索引扫描
SELECT * FROM EMP_1010 WHERE JOB='CLERK';--索引生效，索引扫描
SELECT * FROM EMP_1010 WHERE DEPTNO=20; --索引失效，全表扫描
5.使用orcale函数也会导致索引失效，此时应创建基于函数的索引。
错误示范：SELECT * FROM EMP WHERE ROUND(EMPNO) = 7788; --索引失效，全表扫描
正确示范：
CREATE INDEX AAA ON EMP1(ROUND(EMPNO,2));
SELECT * FROM EMP1 WHERE ROUND(EMPNO) = 7788
函数索引的局限：
SELECT * FROM EMP1 WHERE ROUND(EMPNO,2)>10; --索引失效，全表扫描
SELECT * FROM EMP_1010 WHERE TRUNC(EMPNO,2)>10; --索引失效，全表扫描
--分区 PARTITION
--已经存在的表无法转化为分区表
10.4 表分区
表分区的具体作用：
分区功能可以将表、索引或者索引组织表进一步细分为段，这些数据对象的段叫作分区，
每个分区都有自己的名称，一个分区后的对象(表)具体有多个段，这些段既可以集体管理，也可以单独管理。
什么时候需要用分区表：
(1)表的数据量特别大
(2)表中包含历史数据，新的数据被增加到新的分区中。
表分区的优缺点：
优点：
1.改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
2.增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用。
3.维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可。
4.均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统的性能。
缺点：
1.已经存在的表没有方法可以直接转化为分区表。
2.需要维护。
表分区的几种类型：
1.范围分区：RANGE 针对数值、金额、年龄、时间等进行数据内容的划分，对相同范围的数据进行统一的管理
2.列表分区：LIST 有大量的重复信息的列上面，例如省份、商品分类、性别、部门、岗位...
3.散列分区 如果表格的字段没有任何的规则，那么就创建散列分区，数据库内部通过数据的hash哈希算法，将数据计算能不同的哈希值，然后根据计算的结果进行数据的分类。
4.组合分区：范围分区+列表分区 列表分区+范围分区
范围分区 RANGE
列表分区 LIST
哈希分区 HASH
--创建范围分区
/*
CREATE TABLE 表名
PARTITION BY 分区类型（分区字段）(
PARTITION 分区名 VALUES LESS THAN(值),--小于不包含1000
PARTITION 分区名 VALUES LESS THAN(值)
)
AS SELECT * FROM EMP;
*/ DROP TABLE EMP_FQ;
CREATE TABLE EMP_FQ
PARTITION BY RANGE(SAL)(
PARTITION FQ VALUES LESS THAN(1000),--小于不包含1000
PARTITION FQ1 VALUES LESS THAN(2000),
PARTITION FQ2 VALUES LESS THAN(MAXVALUE) --设置一个没有上限的值
)
AS SELECT * FROM EMP;
--查询分区表
SELECT * FROM EMP_FQ PARTITION(FQ2)
--WHERE ENAME = 'SCOTT';
--分区的最大值到5001 假如有工资8000新员工
INSERT INTO EMP_FQ(ENAME,SAL)VALUES('张三',90001);
--根据日期进行分区
CREATE TABLE EMP_FQ
PARTITION BY RANGE(HIREDATE)(
PARTITION FQ VALUES LESS THAN(TO_DATE('19810501','YYYYMMDD')),--小于不包含1000
PARTITION FQ1 VALUES LESS THAN(TO_DATE('19820123','YYYYMMDD')),
PARTITION FQ2 VALUES LESS THAN(MAXVALUE) --设置一个没有上限的值
)
AS SELECT * FROM EMP;
--查询
SELECT * FROM EMP_FQ PARTITION (FQ2);
--创建范围分区 2.0
CREATE TABLE EMP_FQ(
EMPNO NUMBER,
ENAME VARCHAR2(20),
SAL NUMBER,
DEPTNO NUMBER
)PARTITION BY RANGE(SAL)(
PARTITION FQ VALUES LESS THAN(1000),--小于不包含1000
PARTITION FQ1 VALUES LESS THAN(2000),
PARTITION FQ2 VALUES LESS THAN(MAXVALUE)
);
--向分区表插入数据
INSERT INTO EMP_FQ SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP;
--查询
SELECT * FROM EMP_FQ PARTITION (FQ2);
--列表分区
CREATE TABLE EMP_FQ1
PARTITION BY LIST(DEPTNO)(
PARTITION FQ1 VALUES(10),
PARTITION FQ2 VALUES(20),
PARTITION FQ3 VALUES(30)
)
AS SELECT * FROM EMP;
--查询
SELECT * FROM EMP_FQ1 PARTITION (FQ3);
--列表分区 2.0 DROP TABLE EMP_FQ
CREATE TABLE EMP_FQ1(
EMPNO NUMBER,
ENAME VARCHAR2(20),
DEPTNO NUMBER,
JOB VARCHAR2(20)
)PARTITION BY LIST(JOB)(
PARTITION FQ1 VALUES('SALESMAN'),
PARTITION FQ2 VALUES('MANAGER','CLERK'),
PARTITION FQ3 VALUES('ANALYST','PRESIDENT')
);
--插入数据
INSERT INTO EMP_FQ1 SELECT EMPNO,ENAME,DEPTNO,JOB FROM EMP;
--查询数据
SELECT * FROM EMP_FQ1 PARTITION(FQ3);
--散列分区 HASH
CREATE TABLE EMP_FQ
PARTITION BY HASH(ENAME) PARTITIONS 3 --系统自动添加的分区名
AS SELECT * FROM EMP;
--查询
SELECT * FROM EMP_FQ PARTITION (SYS_P105);
--散列分区 HASH 指定分区名版本
CREATE TABLE EMP_FQ
PARTITION BY HASH(ENAME)(
PARTITION FQ1,
PARTITION FQ2,
PARTITION FQ3,
PARTITION FQ4
)
AS SELECT * FROM EMP;
--查询
SELECT * FROM EMP_FQ PARTITION (FQ1);
--2.0
CREATE TABLE EMP_FQ(
EMPNO NUMBER,
ENAME VARCHAR2(20),
SAL NUMBER
) PARTITION BY HASH(ENAME)
--PARTITIONS 3;
(
PARTITION FQ1,
PARTITION FQ2,
PARTITION FQ3,
PARTITION FQ4
);
--插入数据
INSERT INTO EMP_FQ SELECT EMPNO,ENAME,SAL FROM EMP;
--查询数据
SELECT * FROM EMP_FQ PARTITION(FQ1);
--创建组合分区
PARTITION --主分区关键字
SUBPARTITION --子分区关键字
--范围分区 主 + 列表分区 子 / 列表分区 主 + 范围分区 子
DROP TABLE EMP_FQ;
CREATE TABLE EMP_FQ
PARTITION BY RANGE(SAL) --声明主分区
SUBPARTITION BY LIST(DEPTNO) --声明子分区
(
PARTITION FQ1 VALUES LESS THAN(1000)(
SUBPARTITION ZFQ1 VALUES(10),
SUBPARTITION ZFQ2 VALUES(20),
SUBPARTITION ZFQ3 VALUES(30)
),
PARTITION FQ2 VALUES LESS THAN(2000)(
SUBPARTITION ZFQ4 VALUES(10),
SUBPARTITION ZFQ5 VALUES(20),
SUBPARTITION ZFQ6 VALUES(30)
),
PARTITION FQ3 VALUES LESS THAN(MAXVALUE)(
SUBPARTITION ZFQ7 VALUES(10),
SUBPARTITION ZFQ8 VALUES(20),
SUBPARTITION ZFQ9 VALUES(30)
)
)
AS SELECT * FROM EMP;
--查询
SELECT * FROM EMP_FQ SUBPARTITION(ZFQ6);
--列表分区 主 + 散列分区 子
CREATE TABLE EMP_FQ
PARTITION BY LIST(JOB)
SUBPARTITION BY HASH(ENAME)(
PARTITION FQ1 VALUES('CLERK')(
SUBPARTITION ZFQ1,
SUBPARTITION ZFQ2
),
PARTITION FQ2 VALUES('ANALYST','MANAGER')
(
SUBPARTITION ZFQ3,
SUBPARTITION ZFQ4
),
PARTITION FQ3 VALUES('SALESMAN','PRESIDENT')(
SUBPARTITION ZFQ5,
SUBPARTITION ZFQ6
)
)
AS SELECT * FROM EMP;
--查询
SELECT * FROM EMP_FQ SUBPARTITION(ZFQ4)
DROP TABLE EMP_FQ;
--按天自动创建分区
CREATE TABLE EMP_FQ
PARTITION BY RANGE(HIREDATE) INTERVAL(NUMTODSINTERVAL(1,'DAY'))
(PARTITION MRFQ VALUES LESS THAN(TO_DATE('19810220','YYYYMMDD'))
)
AS SELECT * FROM EMP;
--插入数据测试分区
INSERT INTO EMP_FQ(HIREDATE)VALUES(TRUNC(SYSDATE+3));
--查询结果
SELECT * FROM EMP_FQ PARTITION(SYS_P122);
--按周自动创建分区
CREATE TABLE EMP_FQ
PARTITION BY RANGE(HIREDATE) INTERVAL(NUMTODSINTERVAL(7,'DAY'))
(PARTITION MRFQ VALUES LESS THAN(TO_DATE('19810220','YYYYMMDD'))
)
AS SELECT * FROM EMP;
--按月自动创建分区
CREATE TABLE EMP_FQ
PARTITION BY RANGE(HIREDATE) INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION MRFQ VALUES LESS THAN(TO_DATE('19810220','YYYYMMDD'))
)
AS SELECT * FROM EMP;
--插入数据测试分区
INSERT INTO EMP_FQ(HIREDATE)VALUES(TO_DATE(20230712,'YYYYMMDD'));
--查询结果
SELECT * FROM EMP_FQ PARTITION(SYS_P134);
--按年自动创建分区
CREATE TABLE EMP_FQ
PARTITION BY RANGE(HIREDATE) INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))
(PARTITION MRFQ VALUES LESS THAN(TO_DATE('19810220','YYYYMMDD'))
)
AS SELECT * FROM EMP;
删除分区
ALTER TABLE 表名 DROP PARTITION 分区名;
ALTER TABLE EMP_RANGE3 DROP PARTITION RANGE_MAX;
SELECT * FROM EMP_RANGE3 PARTITION(RANGE_MAX);
ALTER TABLE EMP_RANGE_LIST3 DROP SUBPARTITION P1B;
SELECT * FROM EMP_RANGE_LIST3 SUBPARTITION(P1B);
添加分区
只能添加已存分区规则外的规则的分区
ALTER TABLE 表名 ADD PARTITION 分区名 VALUES LESS THAN(上限值);
ALTER TABLE EMP_RANGE3 ADD PARTITION RANGE_1984 VALUES LESS THAN(TO_DATE('19850101','YYYYMMDD'));
ALTER TABLE EMP_LIST2 ADD PARTITION LIST_30 VALUES (30);
截断分区
ALTER TABLE 表名 TRUNCATE PARTITION 分区名;
ALTER TABLE 表名 TRUNCATE SUBPARTITION 子分区名;
ALTER TABLE EMP_RANGE3 TRUNCATE PARTITION RANGE_1981;
ALTER TABLE EMP_RANGE_LIST3 TRU NCATE SUBPARTITION P1A;
合并分区
ALTER TABLE 表名 MERGE PARTITIONS 分区名1,分区名2 INTO PARTITION 新分区名;
ALTER TABLE EMP_RANGE4 MERGE PARTITIONS RANGE_1981,RANGE_1982 INTO PARTITION RANGE_NEW;
ALTER TABLE EMP_LIST4 MERGE PARTITIONS LIST_10,LIST_20 INTO PARTITION LIST_1020;
拆分分区
ALTER TABLE 表名 SPLIT PARTITION 分区名 AT (分割值) INTO (PARTITION 新分区名1,PARTITION 新分区名2);
ALTER TABLE EMP_RANGE4 SPLIT PARTITION RANGE_NEW
AT (TO_DATE('19810101','YYYYMMDD')) INTO (PARTITION RANGE_1981,PARTITION RANGE_1982);
重命名分区
ALTER TABLE 表名 RENAME PARTITION 原分区名 TO 新分区名;
ALTER TABLE EMP_RANGE4 RENAME PARTITION RANGE_1982 TO RANGE_1982_2;
2168
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
