前言
--数据库语言
DQL 数据查询语言:简单查询,限定查询,模糊查询树形查询,子查询,表连接等等
DDL 数据定义语言┌CREATE 创建
├ALTER 修改
└DROP 删除
DML 数据操作语言┌INSERT 插入
├UPDATE 更新
├DELETE 删除
└MERGE 合并
DCL 数据控制语言 对数据库的权限的操作
TPL/TCP 事务处理/控制语言┌COMMIT 提交
└ROLLBACK 回滚
--DCL 数据控制语言
DCL需要在命令窗口执行
只有数据库管理员(DBA)可以对其它用户进行赋权或回收权限
赋权语法:
GRANT 权限 TO 用户; grant 权限列表 on 数据库名.表名 to '用户名'@'主机名' ;
回收权限语法:
REVOKE 权限 FROM 用户; revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名' ;
CMD窗口下连接数据库
sqlplus 用户名/密码 --登录普通用户
sqlplus / AS SYSDBA --登录DBA用户--可以在linux 环境下授权使用SQL操作
SELECT *
FROM HR.employees
SELECT *
FROM scott.emp
一、DQL 数据查询语言
1.简单查询
1.1基本工具
oracle 11g --当前使用的数据库版本
其他版本
8i 9i --Internet
10g 11g --grid 网格式处理
12c 18c 19c --cloud 云计算
SELECT 查询什么
FROM 表/视图/对象
SELECT * --*表示所有字段/列
FROM emp;
--多个列用逗号隔开,最后一个列后不写逗号
SELECT EMPNO, --员工编号
ENAME, --员工姓名
JOB, --职位
MGR, --经理编号
HIREDATE, --入职日期
SAL, --工资、薪资
COMM, --提成/奖金
DEPTNO --部门编号
FROM emp;
1.2注释
--注释
┌单行注释 --单行注释,从两个-开始,到改行末尾全部注释
└多行注释 从/*开始
多行
注释
到*/结束
SELECT ename
--,sal 此行被注释掉，不在发挥作用
FROM emp;
--查询员工表中的员工姓名,经理编号,入职日期
SELECT ename
,mgr
,hiredate
FROM emp;
1.2.1简单查询例题
--查询员工表中的员工编号,薪资,部门编号
SELECT empno
,sal
,deptno
FROM emp;
--查询部门表中的部门名称和部门所在地
SELECT dname
,loc
FROM dept;
--查询工资登记表中的所有列
SELECT *
FROM SALGRADE;
1.2.2数据类型
┌数值型 NUMBER INT 123 456 111 --纯数字直接写
├字符型 CHAR VARCHAR/VARCHAR2 '字符' --单引号内的任意字符,所见即所得
└日期型 DATE DATE'年-月-日' 比如 DATE'2023-4-19'
SELECT 123,'a',DATE'2023-4-19'
FROM dual; --万能表,相当于白纸
SELECT *
FROM emp;
1.2.3别名
┌列别名
└表别名
语法:
SELECT 列1 [AS] 别名1,列2 [AS] 别名2...
FROM 表 表别名;
列别名可以加as,也可以不加,表别名不能加as
SELECT ename AS 员工姓名
FROM emp;
SELECT e.EMPLOYEE_ID,
e.FIRST_NAME,
e.LAST_NAME,
e.EMAIL,
e.PHONE_NUMBER,
e.HIRE_DATE,
e.JOB_ID,
e.SALARY,
e.COMMISSION_PCT,
e.MANAGER_ID,
e.DEPARTMENT_ID
FROM employees e;
SELECT e.ENAME
,e.SAL
,e.DEPTNO
FROM emp e;
样例：
--查询emp表所有的列,并且每一列都用它得中文起列别名
SELECT e.EMPNO AS 员工编号,
e.ENAME AS 员工姓名,
e.JOB AS 职位,
e.MGR AS 经理编号,
e.HIREDATE AS 入职日期,
e.SAL AS 薪资,
e.COMM AS 提成,
e.DEPTNO AS 部门编号
FROM emp e;
表 TABLE
列 COLUMN
行 ROW
SELECT empno AS "1a1" --数字开头 双引号
,ename AS "$" --特殊符号
,sal AS "gz" --需要保留大小写
FROM EMP;
别名小结:
1.as可以用在列别名上,表别名不能加as
2.别名可以是数字,常见符号(比如下划线),字母和汉字的任意组合(数字不能作为开头)
3.别名为数字开头,包含特殊符号或需要区分大小写时,需要给别名加双引号
小结:
- 表格的列之间用英文逗号隔开；
- 单行或多行注释掉的语句不执行；
- 别名的AS可以不加，数字开头、区分大小写和特殊符号需要加双引号。
2.限定查询
语法:
SELECT 列
FROM 表
WHERE 条件; --条件中除了布尔型以外,都要满足 一个列/值 操作符 另一个列/值
2.1.1比较运算符
< > = <= >= != <>
样例:
查询emp表员工工资大于2000的人的员工信息
SELECT *
FROM emp
WHERE sal>2000;
--日期可以比较大小,日期越晚越大,越早越小
--查询在1981-1-1之前入职的员工姓名,工资和入职日期
SELECT ename
,sal
,hiredate
FROM emp
WHERE hiredate<DATE'1981-1-1';
--查询SMITH的员工姓名,工资和部门编号
SELECT ename
,sal
,deptno
FROM emp
WHERE ename='SMITH';
--查询不是30部门的员工的员工信息
SELECT *
FROM emp
WHERE deptno!=30;
--查询职位不是CLERK的员工的员工姓名,职位,薪资
SELECT ename
,job
,sal
FROM emp
WHERE job!='CLERK';
--查询1986年国庆节之后入职的员工的员工信息
SELECT *
FROM emp
WHERE hiredate>DATE'1986-10-1';
2.1.2逻辑运算符
AND 和 --两端的条件同时满足--优先级在OR之前，要想让OR先起作用给那段语句加（）
OR 或 --两端的条件至少满足其一
NOT 非 --取反,不能用于连接两个条件
--查询工资大于2000并且是30部门的员工的员工信息
SELECT *
FROM emp
WHERE sal>2000 AND deptno=30;
--查询工资大于2000或者是30部门的员工的员工信息
SELECT *
FROM emp
WHERE sal>2000 OR deptno=30;
--两端条件相同时,使用AND的结果是使用OR的结果的子集。
2.1.3 取反
< 取反 >=
> 取反 <=
= 取反 != <>
AND 取反 OR
OR 取反 AND
样例：
--查询工资大于2000或者是30部门的员工的员工信息
SELECT *
FROM emp
WHERE NOT (sal>2000 OR deptno=30);
--查询20部门职位是MANAGER的员工的信息
SELECT *
FROM emp
WHERE deptno=20 AND job='MANAGER';
--查询经理编号或员工编号是7788的员工的信息
SELECT *
FROM emp
WHERE mgr=7788 OR empno=7788;
--查询1981年入职的员工的信息
SELECT *
FROM emp
WHERE hiredate>DATE'1980-12-31' AND hiredate<DATE'1982-1-1';
3.列 BETWEEN 小值 AND 大值
相当于 列 >=小值 AND 列<=大值 --BETWEEN AND 的两端都带等于
--查询1981年入职的员工的信息
SELECT *
FROM emp
WHERE hiredate BETWEEN DATE'1981-1-1' AND DATE'1981-12-31';
相当于
SELECT *
FROM emp
WHERE hiredate>=DATE'1981-1-1' AND hiredate<=DATE'1981-12-31';
--查询工资在2450-3000之间的员工的员工姓名和工资
SELECT ename
,sal
FROM emp
WHERE sal BETWEEN 2450 AND 3000;
--查询1987年上半年入职的员工的员工信息
SELECT *
FROM emp
WHERE hiredate BETWEEN DATE'1987-1-1' AND DATE'1987-6-30';
--查询职位是SALESMAN的员工中提成在1000-2000的员工的信息
SELECT *
FROM emp
WHERE job='SALESMAN' AND comm BETWEEN 1000 AND 2000;
--查询工资在500-1500,或者在3000-5000的员工的信息
SELECT *
FROM emp
WHERE sal BETWEEN 500 AND 1500 OR sal BETWEEN 3000 AND 5000;
--查询工资不在500-1500,也不在3000-5000的员工的信息
SELECT *
FROM emp
WHERE sal NOT BETWEEN 500 AND 1500 AND sal NOT BETWEEN 3000 AND 5000;
或
SELECT *
FROM emp
WHERE NOT (sal BETWEEN 500 AND 1500 OR sal BETWEEN 3000 AND 5000);
4.列 IN (值1,值2,值3,值4....)
相当于 列=值1 OR 列=值2 OR 列=值3 ....
--in的括号内可以有空 –-OR把值连起来
--所以not in的括号内要注意去空–-AND把值连起来
该列内的行只要与IN括号内任意一个值相等,条件就成立
--查询10部门或20部门的员工的信息
SELECT *
FROM emp
WHERE deptno=10 OR deptno=20;
或
SELECT *
FROM emp
WHERE deptno IN (10,20);
--查询员工编号为7788/7654/7902的员工的信息
SELECT *
FROM emp
WHERE empno IN (7788,7654,7902);
--查询职位不是MANAGER,SALESMAN,CLERK的员工的信息
SELECT *
FROM emp
WHERE JOB NOT IN ('MANAGER','SALESMAN','CLERK');
--查询30部门职位是SALESMAN或MANAGER的员工的信息
SELECT *
FROM emp
WHERE deptno=30 AND job IN ('SALESMAN','MANAGER');
5.算数运算符 + - * / ()
--算数运算符不能作为条件中间的操作符使用,只能用于运算,大多数时候是在select后运算时使用
SELECT ename
,sal
,sal-1
FROM emp;
2.1.4空NULL
空 做条件时,不能用< > = != 等方式表达,只有两种表达方式:
列 IS NULL
列 IS NOT NULL
列 = NULL --×
--查询没有经理的员工的信息,经理编号为空的为没有经理
SELECT *
FROM emp
WHERE mgr IS NULL;
--查询有提成的员工的信息
SELECT *
FROM emp
WHERE comm IS NULL;
--查询没有提成的员工的信息
SELECT *
FROM emp
WHERE comm IS NULL;
--查询提成为300或500或null的员工的信息
SELECT *
FROM emp
WHERE comm IN (300,500,NULL); --×
↓
SELECT *
FROM emp
WHERE comm=300 OR comm=500 OR comm=NULL;
--in的括号内有null时,相当于没有null这个值
SELECT *
FROM emp
WHERE comm NOT IN (300,500,NULL);
↓
SELECT *
FROM emp
WHERE NOT (comm=300 OR comm=500 OR comm=NULL);
↓
SELECT *
FROM emp
WHERE comm!=300 AND comm!=500 AND comm!=NULL;
--not in的括号内不能有NULL,如果有NULL时,结果集必定为空结果集
2.1.5连接符 ||
相当于双面胶,可以把它两端的列/值粘贴到一块
--不论之前是什么类型,连接后的结果一定为字符型,日期型会被强行转为字符型之后再连接
SELECT ename,sal,ename||sal||job
FROM emp
2.1.6隐式转换
纯数字的字符型在需要当做数值型使用时,ORACLE会自动将其转为数值型,数值型在需要当做字符型使用时,ORACLE会自动将其转为字符型。
SELECT *
FROM emp
WHERE empno='7788';
SELECT 1+1
FROM dual; --√
SELECT 1+'1'
FROM dual; --√
SELECT '1'+'1'
FROM dual; --√
SELECT '1+1'
FROM dual; --结果所见即所得
SELECT 1'+1'
FROM dual; --×
--只使用1和2两个数字,标点符号不限,得出 1+2=3 这个式子
SELECT '1+2='||(1+2)
FROM dual;
2.1.7优先级
1. () --如果需要先运行某一个命令,可以用小括号将其括起来
2. * /
3. + - ||
4. 比较运算符
5. IS NULL / in
6. BETWEEN AND
7. NOT
8. AND
9. OR
样例：假设现有条件 a b c d
a AND b OR c AND d → (a AND b) OR (c AND d)
a OR b AND c OR d → a OR (b AND c) OR d
--如果where内有多个条件,并且想先筛选符合OR的,需要把OR和它两边的条件括起来。
小结：
- 不论之前是什么类型,连接后的结果一定为字符型,日期型会被强行转为字符型之后再连接。
- 隐式转换：纯数字的字符型在需要当做数值型使用时,ORACLE会自动将其转为数值型,数值型在需要当做字符型使用时,ORACLE会自动将其转为字符型。
3.模糊查询
3.1模糊查询
3.1.1语法
SELECT 列
FROM 表
WHERE 列 LIKE '匹配的内容'
通配符：
⑴ _ ：一个任意字符
⑵ % ：0或多个任意字符
⑶ ：空格可以直接表示
--查询员工姓名包含S的员工的信息
SELECT *
FROM emp
WHERE ename LIKE '%S%';
样例：
--查询员工姓名S开头的员工的信息
SELECT *
FROM emp
WHERE ename LIKE 'S%';
--查询员工姓名S结尾的员工的信息
SELECT *
FROM emp
WHERE ename LIKE '%S';
--查询员工姓名第二个字符是L的员工信息
SELECT *
FROM emp
WHERE ename LIKE '_L%';
--查询员工姓名倒数第二个字符是K的员工信息
SELECT *
FROM emp
WHERE ename LIKE '%K_';
--查询名字是四位的员工的信息
SELECT *
FROM emp
WHERE ename LIKE '____';
--查询名字总长度为5并且开头为J的员工的信息
SELECT *
FROM emp
WHERE ename LIKE 'J____';
--查询名字开头为J结尾为S的员工的信息
SELECT *
FROM emp
WHERE ename LIKE 'J%S';
--查询名字开头为J结尾为S,并且包含O的员工的信息
SELECT *
FROM emp
WHERE ename LIKE 'J%O%S';
3.1.2 转义
1.模糊查询中的转义
定义一个字符为转义符,一般为\,在前面匹配的内容中,每一个要转义的字符前都写一个\
语法:
SELECT 列
FROM 表
WHERE 列 LIKE '匹配的内容' ESCAPE '\'
样例：
--查询某表的某列中包含%的行
SELECT *
FROM 某表
WHERE 某列 LIKE '%\%%' ESCAPE '\'
--查询某表的某列中开头是%并且包含_的行
SELECT *
FROM 某表
WHERE 某列 LIKE '\%%\_%' ESCAPE '\'
2.单引号的转义
①双写转义,在要转义的单引号旁边再写一个单引号,两个单引号就会变为一个没有特殊含义的单引号
SELECT 'let''s go',''''
from dual;
②q转义,会把大括号内所有的单引号全部转义
SELECT q'{let's go}',q'{'}'
from dual;
3.& 宏代换
SELECT *
FROM emp
WHERE hiredate>DATE'&请输入一个年份-&请输入一个月份-&请输入几号';
SELECT *
FROM emp
WHERE ename='&请输入一个姓名';
--&后没有字符时,可以直接显示出来,&后有字符时才会触发变量弹框,这时候可以使用双写&转义
SELECT '&&a'
FROM dual;
小结：
- 模糊查询时，LIKE后’’内_代表单个任意字符，%代表多个任意字符，空格可以用空格直接表示。
- 转义，当_和%要体现在字符段里时，在前面加\，然后空格后缀ESCAPE '\' 。
4.函数
┌系统函数┌单行函数┌数值函数
│ │ ├字符函数
│ │ ├日期函数
│ │ ├转换函数
│ │ └通用函数
│ ├聚合函数
│ └分析函数
└自定义函数
函数的注意事项:
函数的用法,函数名的写法,参数个数,参数类型,返回值的类型
4.1数值函数
1. ABS(数) 求绝对值
正数和零的绝对值是它本身,负数的绝对值是它的相反数
SELECT -1,ABS(-1),ABS(-10*2-3),ABS(0),ABS(1000)
FROM dual;
SELECT ename,sal,ABS(sal-10000)
FROM emp;
2. MOD(数1,数2) 取余
10 ÷ 3 = 3 ... 1
数1 ÷ 数2 = 商 ... 余数
数2可以为0,结果为数1
结果的正负只与数1有关,与数2无关
SELECT MOD(12,3)
FROM dual;
判断奇偶数
假设员工编号是奇数为男员工,偶数的为女员工,查询所有男员工
SELECT *
FROM emp
WHERE MOD(empno,2)=1;
取小数部分
SELECT 10.5
,MOD(10.5,1)
FROM dual;
SELECT empno,MOD(empno,100)
FROM emp
3. CEIL(数) 向上取整,向正无穷方向取整
4. FLOOR(数) 向下取整,向负无穷方向取整
整数取整还是它本身,有小数的数值取整时,会向上/下自动进位到整数
SELECT CEIL(1.00000000000000005),CEIL(-1.99999),CEIL(0),
FLOOR(1.00000000000000005),FLOOR(-1.99999),FLOOR(0)
FROM dual;
5. ROUND(数1[,数2]) 四舍五入
将数1四舍五入到数2位,不写数2时,默认四舍五入到整数位
数2写负数时,四舍五入保留到小数点前数2的前一位
SELECT ROUND(1.5),ROUND(3.1415926,2),
ROUND(31415.26,-3)
FROM dual;
--查询员工姓名,工资,部门编号,工资是部门编号的多少倍(四舍五入保留两位小数)
SELECT ename
,sal
,deptno
,ROUND(sal/deptno,2)
FROM emp;
6. TRUNC(数1[,数2]) 截断 --只舍不入
将数1截断到数2位,不写数2时,默认截断到整数位
数2写负数时,截断保留到小数点前数2的前一位
SELECT TRUNC(1.5),TRUNC(999.999,2),TRUNC(999999.9999,-3)
FROM dual;
7. SIGN(数) 判断正负零
如果为正数,返回1,如果为负数,返回-1,如果为0,返回0
SELECT SIGN(999999999999)
,SIGN(-999999999999999)
,SIGN(0)
FROM dual;
--假设员工编号比经理编号大的为优秀员工,否则为普通员工,查询优秀员工的员工信息
SELECT *
FROM emp
WHERE empno>mgr;
SELECT *
FROM emp
WHERE SIGN(empno-mgr)=1
8. POWER(数1,数2) 幂运算,次方与开方
数2为正数时,数1的数2次方
数2为分数时,数1开数2的分母次方
数2为负数时,数1的数2次方分之1
SELECT POWER(2,3)
,POWER(16,1/3)
,POWER(2,-2)
FROM dual;
4.2字符函数
1. UPPER(str) 转大写
2. LOWER(str) 转小写
SELECT UPPER('aBc'),
LOWER('aBc')
FROM dual;
SELECT e.FIRST_NAME,LOWER(e.FIRST_NAME),UPPER(e.FIRST_NAME)
FROM employees e;
3. INITCAP(str) 转首字母大写
除了字母和数字以外的任何字符都会把字母分隔为多个独立的"单词",再去分别转首字母大写
SELECT 'ab cd',INITCAP('ab cd')
FROM dual;
SELECT ename,INITCAP(ename)
FROM emp;
4. LENGTH(str) 字符长度
5. LENGTHB(str) 字节长度
--汉字/中文标点符号/全宽字符,每一个占2-3个字节
16GBK 汉字/中文标点符号/全宽字符,每个占2个字节
UTF-8 汉字/中文标点符号/全宽字符,每个占3个字节
ORACLE数据库默认为16GBK字符集
SELECT LENGTH('abc一二三'),
LENGTHB('abc一二三')
FROM dual;
--查询员工姓名长度为5的员工的信息和名字长度
SELECT e.*,LENGTH(ename)
FROM emp e
WHERE LENGTH(ename)=5;
6. TRIM(str) 去两端空格
SELECT ' a qwea sd ',
TRIM(' a qwea sd ')
FROM dual;
7. LTRIM(str1[,str2]) 去除左端字符
8. RTRIM(str1[,str2]) 去除右端字符
--去除STR1中第一个不存在于STR2中的字符左/右侧的所有存在于STR2中的字符。
去除str1左端/右端的str2,str1左端/右端的字符中与str2内的任意一个字符相同就会被去除
str2不写,默认去除str1左端/右端空格
SELECT 'abaaab ababcab',
LTRIM('abaaab ababcab','ab '),
RTRIM('abaaab ababcab','ab ')
FROM dual;
--查询员工编号,去掉左端7再去掉左端8的员工编号,同时去掉左端7和8的员工编号
SELECT empno,
LTRIM(LTRIM(empno,7),8),
LTRIM(empno,78)
FROM emp;
9. LPAD(str1,数,str2) 左填充
10. RPAD(str1,数,str2) 右填充
str1 原字符串，数 填充后的字符段长度，str2 用于填充的字符
用str2把str1填充到数位,如果str1原本的长度就比数大,会从左向右数数位保留下来
SELECT LPAD('abc',5,'*'),
RPAD('abc',5,'*'),
LPAD('abc',2,'*'),
RPAD('abc',2,'*'),
LPAD('abc',6,'*+'),
RPAD('abc',6,'*+')
FROM dual;
--在abc左侧填充两个"星"
SELECT LPAD('abc',7,'星')
FROM dual;
--在abc左右两侧各填充两个*
SELECT RPAD(LPAD('abc',5,'*'),7,'*')
FROM dual;
--查询员工姓名和在员工姓名左侧填充两个*之后的员工姓名
SELECT ename,
'**'||ename,
LPAD(ename,LENGTH(ename)+2,'*')
FROM emp;
小结：
1.数值函数
ABS(数) 求绝对值
MOD(数1,数2) 取余
CEIL(数) 向上取整,向正无穷方向取整
FLOOR(数) 向下取整,向负无穷方向取整
ROUND(数1[,数2]) 四舍五入
TRUNC(数1[,数2]) 截断 --只舍不入,不写数2默认截到整数位
SIGN(数) 判断正负零
POWER(数1,数2) 幂运算,次方与开方
2.字符函数
UPPER(str) 转大写
LOWER(str) 转小写
INITCAP(str) 转首字母大写
LENGTH(str) 字符长度
TRIM(str) 去两端空格
LTRIM(str1[,str2]) 去除左端字符
RTRIM(str1[,str2]) 去除右端字符
LPAD(str1,数,str2) 左填充
RPAD(str1,数,str2) 右填充
4.2.1续字符函数
11. INSTR(str1,str2[,数1[,数2]]) 查找字符串
返回数值型结果，在str1内找str2,从数1位开始找,找第数2次出现的位置。
数1数2不写默认为1,可以写数1不写数2,不能只写数2不写数1；
数1可以写负数,负数表示从倒数第几位向前找,但是返回的位数仍然是从左向右数的；
SELECT INSTR('helloworld','l')
,INSTR('helloworld','l',4,2)
,INSTR('helloworld','l',-1,3)
,INSTR('helloworld','o',1,3) --找不到返回0
FROM dual;
--查找hellorld内o第一次和第二次出现的位置wo
SELECT INSTR('helloworld','o')
,INSTR('helloworld','o',1,2)
FROM dual;
--查找helloworld内l倒数第一次和倒数第二次出现的位置
SELECT INSTR('helloworld','l',-1)
,INSTR('helloworld','l',-1,2)
FROM dual;
--查找员工姓名和员工姓名中A第一次和第二次出现的位置,名字不包含A的不显示
SELECT ename
,INSTR(ename,'A')
,INSTR(ename,'A',1,2)
FROM emp
WHERE INSTR(ename,'A')!=0;
12. SUBSTR(str,数1[,数2]) 截取字符串
把str从数1位开始截取数2位,数2不写,默认截取到最后,数1可以是负数,表示从倒数第几位开始截取(从左向右截取)
SELECT SUBSTR('helloworld',1,3)
,SUBSTR('helloworld',3,3)
,SUBSTR('helloworld',-4,2)
,SUBSTR('helloworld',6)
,SUBSTR('helloworld',-5)
FROM dual;
--截取员工姓名,从A开始截取,截取到最后,只显示包含A的
SELECT ename
,SUBSTR(ename,INSTR(ename,'A'))
FROM emp
WHERE INSTR(ename,'A')!=0;
13. REPLACE(str1,str2[,str3]) 整体替换
在str1内找str2,找到后整体替换成str3,str3不写,默认替换成空,整体替换可以多位字符替换为1位,或把1位替换成多位。
--把helloworld内的o替换成#,l替换成*,ow整体替换成空
SELECT REPLACE('helloworld','o','#')
,REPLACE('helloworld','l','*')
,REPLACE('helloworld','ow')
FROM dual;
--把员工编号内的7替换成8,再把所有的8替换成9,最后去掉左端的9,显示原本的和替换后员工编号
SELECT empno
,LTRIM(REPLACE(REPLACE(empno,7,8),8,9),9) AS 替换后
FROM emp;
--把员工姓名内的A替换成空,然后把S替换成#,显示原本的和替换后员工姓名
SELECT ename
,REPLACE(REPLACE(ename,'A'),'S','#') AS 替换后
FROM emp;
14. TRANSLATE(str1,str2,str3) 逐一替换
⑴在str1内找str2内的每一个字符,找到后替换成str3内与str2内在位置上一一对应的字符；
⑵如果str2内的字符比str3的多,str2内多出的字符会被替换成空；
⑶如果str3内的字符比str2的多,str3内多出的字符无效；
⑷当str1/str2/str3任意一个为空或空字符串时,结果为空；
⑸多次替换,只有第一次替换有效；
SELECT TRANSLATE('helloworld','hel','qwe')
,TRANSLATE('helloworld','hel','q')
,TRANSLATE('helloworld','h','qas')
,TRANSLATE('helloworld','hel','')
,TRANSLATE('helloworld','','qwe')
,TRANSLATE('','hel','qwe')
,TRANSLATE('helloworld','llz','zxc')
FROM dual;
--把helloworld里的hel替换成空
SELECT TRANSLATE('helloworld','*hel','*') –-“加*大法”STR3内只有一个字符，STR2内的*在STR1内不存在二存在的剩余字符会被替换成空。
FROM dual;
--把员工姓名中的AMS三个字母替换成空,显示替换前和替换后的员工姓名
SELECT ename,TRANSLATE(ename,'*AMS','*')
FROM emp;
--把员工姓名中和职位中重复的字母替换成*
SELECT ename
,job
,TRANSLATE(ename,job,LPAD('*',LENGTH(job),'*')) AS 替换后
FROM emp;
--把员工姓名中和职位中重复的字母替换成空
SELECT ename
,job
,TRANSLATE(ename,'*'||job,'*') AS 替换后
FROM emp;
--查询所有员工姓名与职位中有重复字母的员工的信息
SELECT *
FROM emp
WHERE ename!=TRANSLATE(ename,job,LPAD('*',LENGTH(job),'*'));
SELECT *
FROM emp
WHERE ename!=TRANSLATE(ename,'*'||job,'*')
OR TRANSLATE(ename,'*'||job,'*') IS NULL;
15. CONCAT(str1,str2) 连接两个str
相当于一个连接符||
一次只能连接两个str,如果需要连接三个,嵌套使用
SELECT ename,sal,deptno,
CONCAT(CONCAT(ename,sal),deptno),ename||sal||deptno
FROM emp;
小结：
INSTR(str1,str2[,数1[,数2]]) 查找字符串位置
SUBSTR(str,数1[,数2]) 截取字符串
REPLACE(str1,str2[,str3]) 整体替换
TRANSLATE(str1,str2,str3) 逐一替换 –-加*处理换空
CONCAT(str1,str2) 连接两个str—直接连接，中间没有标点符号
4.3日期函数
SYSDATE 当前系统时间
SELECT SYSDATE
FROM dual;
日期的运算规则:
日期±数字=日期±天数 –-日期直接加减数字就是在加减天
日期-日期=两日期相差的天数 –-日期相互之间只能相减
日期+日期=错误
日期*日期=错误
日期÷日期=错误
SELECT SYSDATE-1,
DATE'2023-4-21'-DATE'2023-4-20',
SYSDATE-DATE'2023-4-20',
SYSDATE+1/24
FROM dual;
--查询一天后,两小时后,三分钟后,四秒钟后的时间
SELECT SYSDATE
,SYSDATE+1 --一天后
,SYSDATE+2/24 --两小时后
,SYSDATE+3/24/60 --三分钟后
,SYSDATE+4/24/60/60 --四秒后
FROM dual;
--查询2023年二月有几天
SELECT DATE'2023-3-1'-DATE'2023-2-1'
FROM dual;
--查询距离2024年元旦还有几天
SELECT DATE'2024-1-1'-SYSDATE
FROM dual;
--查询今天是2023年的第几天
SELECT SYSDATE-DATE'2023-1-1'
FROM dual;
--查询10000天前的日期
SELECT SYSDATE-10000
FROM dual;
4.4转换函数
1. ASCII(str) 把str的首位字符转为ASCII码 ]
2. CHR(数) 把ASCII码转为相应的字符 ] –-配合使用可以处理一些转码问题
SELECT ASCII('a') --97
,ASCII('A') --65
FROM dual;
SELECT CHR(4294967295)
,CHR(50131)
FROM dual;
--查询员工姓名S开头的员工信息
SELECT *
FROM emp
WHERE ename LIKE 'S%';
SELECT *
FROM emp
WHERE INSTR(ename,'S')=1;
SELECT *
FROM emp
WHERE SUBSTR(ename,1,1)='S';
SELECT *
FROM emp
WHERE ASCII(ename)=ASCII('S');
3. to_number(纯数字的str) 将纯数字的字符段转为数值型
SELECT '123',to_number('123')
FROM dual;
4. to_date(str,'格式') 将字符段按照格式要求转为日期型
yyyy 年
mm 月
dd 天
hh/hh12 12小时制的时
hh24 24小时制的时
mi 分
SS 秒
SELECT to_date('2021-1-1 23:34:50','yyyy mm dd hh24 mi ss')
FROM dual;
--to_date可以有一些缺省,会自动补上这部分日期/时间
缺省年 默认为本年
缺省月 默认为本月
缺省日 默认1号
缺省时 默认为0时
缺省分 默认为0分
缺省秒 默认为0秒
时分秒都缺省时,默认为0时0分0秒,0时0分0秒默认不显示
SELECT to_date('1','mi')
FROM dual;
--查询2028年9月18日下午3时27分59秒
SELECT to_date('2028 9 18 15 27 59','yyyy/mm-dd hh24 mi ss')
FROM dual;
SELECT to_date('2028年9月18日 15时27分59秒','yyyy"年"mm"月"dd"日" hh24"时"mi"分"ss"秒"')
FROM dual;
--如果原字符串分隔符为汉字时,格式内同样要以汉字为分隔符,汉字要加双引号
--查询今年5月1日凌晨3点整
SELECT to_date('5 3','mm hh24')
FROM dual;
--查询本月1号中午12点01秒
SELECT to_date('12 1','hh24 ss')
FROM dual;
--日期的三种表达方式
1. DATE'年-月-日' 只能表示年月日,不能表示具体的时间
2. to_date(str,'格式') 可以表示年月日时分秒,但是比较麻烦
3. '日-某月-年' 比如'31-1月-23' 只能在where条件中使用
5. to_char 三种用法
1) to_char(参数) 将该参数转为字符型
SELECT sal,hiredate,to_char(sal),to_char(hiredate)
FROM emp;
2)to_char(日期,'格式') 按照格式提取日期中的元素
yyyy 年
mm 月
dd 日
q 季度
dy/DAY 中文的星期,比如星期一 星期二等
d 美式的星期,比如1 2 3 4 5 6 7,而且中文的星期一是美式的2,美式的1是中文星期日
hh/hh12 12小时制的时
hh24 24小时制的时
mi 分
SS 秒
am/pm 上下午
ddd 今天是今年的第几天
sssss 现在是今天的第几秒
--提取当前系统时间的年 月 日 时 分 秒 季度 星期 上下午和今天是今年第几天,当前秒是今天第几秒
SELECT SYSDATE
,to_char(SYSDATE,'yyyy')
,to_char(SYSDATE,'mm')
,to_char(SYSDATE,'dd')
,to_char(SYSDATE,'hh')
,to_char(SYSDATE,'hh24')
,to_char(SYSDATE,'mi')
,to_char(SYSDATE,'ss')
,to_char(SYSDATE,'am')
,to_char(SYSDATE,'q')
,to_char(SYSDATE,'d') --提取出来的时纯数字；
,to_char(SYSDATE,'day')--提取出来的时星期_;
,to_char(SYSDATE,'ddd')
,to_char(SYSDATE,'sssss')
FROM dual;
--提取当前时间的时分秒,并显示为 xx点xx分xx秒 格式
SELECT SYSDATE
,to_char(SYSDATE,'hh24"点"mi"分"ss"秒"')
FROM dual;
--查询所有闰年入职的员工的信息
SELECT *
FROM emp
WHERE MOD(to_char(hiredate,'yyyy'),4)=0 AND MOD(to_char(hiredate,'yyyy'),100)!=0
OR MOD(to_char(hiredate,'yyyy'),400)=0;
SELECT *
FROM emp
WHERE to_date(to_char(hiredate,'yyyy')||'-3-1','yyyy mm dd')-to_date(to_char(hiredate,'yyyy')||'-2-1','yyyy mm dd')=29;
SELECT *
FROM emp
WHERE to_date(to_char(hiredate,'yyyy')||'-12-31','yyyy mm dd')-to_date(to_char(hiredate,'yyyy')||'-1-1','yyyy mm dd')=365;
--闰年的定义
非整百年可以被4整除的为普通闰年,整百年可以被400整除的为世纪闰年,其余为平年
--查询当前秒是今年的第几秒
SELECT (to_char(SYSDATE,'ddd')-1)*24*60*60+to_CHAR(SYSDATE,'sssss')
FROM dual;
SELECT (SYSDATE-DATE'2023-1-1')*24*60*60
FROM dual;
3)to_char(数,'格式') 统一数字格式--格式中小数点的前一位写0
9 占位符
0 占位符(放到个位)
$ 美元符号
L 当地货币符号
9和0两个占位符的区别是小数点前占位符如果是9,数据长度比格式短时,不会补零,如果是0就会补零,小数点后不论是哪个占位符,都会补零,to_char统一数字格式时,会自动四舍五入
SELECT ename
,sal
,TRIM(to_char(sal,'L999,999,999,999,990.99'))
FROM emp;
SELECT TRIM(to_char(0.5678,'$999,990.99'))
FROM dual;
--查询员工姓名,薪资,和薪资增长12.55%之后的薪资,带本地货币符号三位一逗号四舍五入保留两位小数显示
SELECT ename
,sal
,TRIM(to_char(sal*1.1255,'L999,999,999,990.99')) AS 薪资
FROM emp;
小结：
ASCII(str) 把str的首位字符转为ASCII码
CHR(数) 把ASCII码转为相应的字符
to_number(纯数字的str) 将纯数字的str转为数值型
to_date(str,'格式') 将str按照格式转为日期型
to_char(参数) 将该参数转为字符型to_char(日期,'格式') 按照格式提取日期中的元素to_char (数,'格式') 统一数字格式，注意格式中小数点前最后一位用9，否则纯小数时小数点左侧没有0
4.5 通用函数
--字符函数
to_multi_byte(str) 转全宽
to_single_byte(str) 转半宽
SELECT to_multi_byte('a'),to_single_byte('ａ')
FROM dual;
4.5.1 通用函数
1. USERENV('language') 查询当前客户端的字符集
SELECT USERENV('language')
FROM dual;
16GBK 一个汉字或全宽字符为2个字节
UTF-8 一个汉字或全宽字符为3个字节
2. GREATEST(参1,参2,参3,参4....) 返回多个参数中的最大值
多个参数必须是同一类型
如果有空,必定返回空
SELECT GREATEST(1,2,3,4,59,111,31)
,GREATEST('a','b','ab','p')
,GREATEST(DATE'2020-1-1',DATE'2021-1-1',DATE'9999-5-3')
,GREATEST('5','27','199999999')
FROM dual;
SELECT sal,comm,deptno,
GREATEST(sal,comm,deptno)
FROM emp
3. LEAST(参1,参2,参3,参4....) 返回多个参数中的最小值
多个参数必须是同一类型
如果有空,必定返回空
SELECT LEAST(1,2,3,4,59,111,31)
,LEAST('a','b','ab','p')
,LEAST(DATE'2020-1-1',DATE'2021-1-1',DATE'9999-5-3')
,LEAST('5','27','199999999')
FROM dual;
SELECT sal,comm,deptno,
LEAST(sal,comm,deptno)
FROM emp;
4. COALESCE(参1,参2,参3....) 返回第一个不为空的参数,多个参数必须是同一类型#去空
SELECT COALESCE(2,NULL,1,NULL)
FROM dual;
--查询提成,经理编号,员工编号,以及它们中第一个不为空的值
SELECT comm,mgr,empno,
COALESCE(comm,mgr,empno)
FROM emp;
5. NVL(参1,参2) 参1为空返回参2,参1不为空返回参1,参1和参2类型必须一致
--显示员工姓名,提成和提成+1000之后的值(提成为空的也发放1000提成)
SELECT ename,comm,
NVL(comm,0)+1000
FROM emp;
6. NVL2(参1,参2,参3) 参1为空返回参3,参1不为空返回参2,参2和参3类型必须一致,参1无所谓
--查询员工姓名,提成,显示是否有提成
SELECT ename,comm,
NVL2(comm,'有提成','无提成')
FROM emp;
7. DISTINCT 列1,列2,列3..... 对后面的列进行去重
如果是多个列,会对多个列进行共同去重,必须多个列都相同的行才会去重
distinct只能写在select和列的中间位置,不能在列后写distinct
--去重显示emp表的所有部门
SELECT DISTINCT deptno
FROM emp;
--去重显示emp表的所有职位
SELECT DISTINCT job
FROM emp;
--去重显示emp表的所有部门的所有职位
SELECT DISTINCT deptno,job
FROM emp;
8. CASE WHEN 两种用法，都是新增返回值一列
1)CASE 列 WHEN 值1 THEN 返回值1
WHEN 值2 THEN 返回值2
WHEN 值3 THEN 返回值3
...
[ELSE 返回值N]
END AS 新列名
--如果部门编号是10,返回十部门,20,返回二十部门,否则返回其他部门
SELECT deptno,
CASE deptno WHEN 10 THEN '十部门'
WHEN 20 THEN '二十部门'
ELSE '其他部门'
END
FROM emp;
--查询员工姓名,职位,以及职位的中文翻译
SELECT ename,
job,
CASE job WHEN 'CLERK' THEN '职员'
WHEN 'SALESMAN' THEN '销售'
WHEN 'MANAGER' THEN '经理'
WHEN 'PRESIDENT' THEN '董事长'
ELSE '分析'
END AS 中文职位
FROM emp;
2)CASE WHEN 条件1 THEN 返回值1
WHEN 条件2 THEN 返回值2
WHEN 条件3 THEN 返回值3
...
[ELSE 返回值N]
END AS 新列名
有条件的不用在case后边加列
--如果工资大于3000,返回一级,
--如果工资大于2000,返回二级,
--如果工资大于1000,返回三级,
--否则返回四级,显示员工姓名,工资和工资等级
SELECT ename,sal,
CASE WHEN sal>3000 THEN '一级'
WHEN sal>2000 THEN '二级'
WHEN sal>1000 THEN '三级'
ELSE '四级'
END AS 工资等级
FROM emp;
/*如果工资小于等于8000,返回1级,
如果工资小于等于4000,返回2级,
如果工资小于等于2000,返回3级,
如果工资小于等于1000,返回4级,
显示每个人的姓名,工资,工资等级*/
SELECT ename,sal,
CASE WHEN sal<=8000 THEN '1级'
WHEN sal<=4000 THEN '2级'
WHEN sal<=2000 THEN '3级'
WHEN sal<=1000 THEN '4级'
END AS 工资等级
FROM emp; --×
①补全范围
SELECT ename,sal,
CASE WHEN sal<=8000 AND sal>4000 THEN '1级'
WHEN sal<=4000 AND sal>2000 THEN '2级'
WHEN sal<=2000 AND sal>1000 THEN '3级'
WHEN sal<=1000 THEN '4级'
END AS 工资等级
FROM emp;
②先写小范围，第一行筛选出去的数据不会进入第二行的判定
SELECT ename,sal,
CASE WHEN sal<=1000 THEN '4级'
WHEN sal<=2000 THEN '3级'
WHEN sal<=3000 THEN '2级'
WHEN sal<=8000 THEN '1级'
END AS 工资等级
FROM emp;
/*如果员工是1981年之前入职的,返回他的部门编号,
如果员工是1981-1982年入职的,返回他的职位,
如果员工是1982年之后入职的,返回他的入职日期,
并且显示员工姓名,入职日期*/
SELECT ename,hiredate,
CASE WHEN to_char(hiredate,'yyyy')<1981 THEN to_char(deptno)
WHEN to_char(hiredate,'yyyy') BETWEEN 1981 AND 1982 THEN job
WHEN to_char(hiredate,'yyyy')>1982 THEN to_char(hiredate)
END AS a
FROM emp;
9. DECODE(列,值1,返回值1,值2,返回值2,值3,返回值3,...,[返回值N])
--相当于case when的第一种用法
--如果部门编号是10,返回十部门,20,返回二十部门,否则返回其他部门
SELECT deptno,
DECODE(deptno,10,'十部门',20,'二十部门','其他部门') AS 中文部门
FROM emp;
--查询员工姓名,职位,以及职位的中文翻译
SELECT ename,
job,
DECODE(job,'CLERK','职员','SALESMAN','销售','MANAGER','经理','PRESIDENT','董事长','分析') AS 中文职位
FROM emp;
10. NULLIF(参1,参2) 参1与参2相同返回空,不相同返回参1，参1和参2类型要一致
主要用于解决除数为0的情况
被除数÷NULLIF(除数,0)
--查询员工的姓名,工资,提成以及工资是提成的多少倍,提成为空的不显示
SELECT ename,sal,comm,
sal/NULLIF(comm,0)
FROM emp
WHERE comm IS NOT NULL;
4.5.2 聚合函数(分组函数)
--空值不参与计算
1. MIN(列) 最小值
2. MAX(列) 最大值
3. AVG(列) 平均值
4. SUM(列) 求和
5. COUNT(*/1/列) 计数
COUNT(列)在计数时,不会统计该列为空的行数,
COUNT(1)和COUNT(*)在计算时不涉及到某个列的具体数据,只计算行数所以统计时不论是否有空都会计数
--查询全表的最低工资,最高工资,平均提成,提成总和,有提成的人数,全表人数
SELECT MIN(sal),MAX(sal),AVG(comm),SUM(comm),COUNT(comm),COUNT(1),COUNT(*)
FROM emp;
--group by 分组 –-对分组列默认升序排序
--查询每个部门的人数,平均工资
SELECT deptno,COUNT(1),AVG(sal)
FROM emp
GROUP BY deptno;
--使用聚合函数时,select后只能写分组的列和聚合函数处理过的列
--查询每种职位的人数,最高工资,平均工资,总工资
SELECT job,COUNT(1),MAX(sal),AVG(sal),SUM(sal)
FROM emp
GROUP BY job;
--查询每个经理手下的人数,有奖金的人数,最低工资
SELECT mgr,COUNT(1),COUNT(comm),MIN(sal)
FROM emp
GROUP BY mgr;
--查询各部门各职位人数,最早和最晚的入职日期
SELECT deptno,job,COUNT(1),MIN(hiredate),MAX(hiredate)
FROM emp
GROUP BY deptno,job;
--having 分组后的筛选/条件，后边可以写聚合函数
--where内不能使用聚合函数作为条件,聚合函数作条件时,需要写在having后
--查询每种职位的人数,只显示人数大于3的
SELECT job,COUNT(1)
FROM emp
GROUP BY job
HAVING COUNT(1)>3;
--查询部门和每个部门的最高工资,只显示最高工资大于2900的部门
SELECT deptno,MAX(sal)
FROM emp
GROUP BY deptno
HAVING MAX(sal)>2900;
--查询最早的入职日期在1981-6-1之前的职位
SELECT job
FROM emp
GROUP BY job
HAVING MIN(hiredate)<DATE'1981-6-1';
--查询每个经理下属的平均工资,只显示平均工资在1000-2000的
SELECT mgr,AVG(sal)
FROM emp
GROUP BY mgr
HAVING AVG(sal) BETWEEN 1000 AND 2000;
--order by 排序
ASC 升序 --默认
DESC 降序 --写在需要降序排序的列的后面,如果多个列排序,每个需要降序排序的列后都要写
--order by后可以写多个列,逗号隔开,表示前面列的值相同时按照后面列排，order by后可以写数字,表示按select后边写的第几个列进行排序
SELECT *
FROM emp
ORDER BY sal DESC,hiredate DESC
SELECT ename,sal,hiredate
FROM emp
ORDER BY 2 DESC,3 DESC
--查询每个部门的平均工资,按照平均工资降序排序
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno
ORDER BY AVG(sal) DESC;
--查询每个职位的最高工资,只显示最高工资不低于2500的,按照最高工资升序排序
SELECT job,MAX(sal)
FROM emp
GROUP BY job
HAVING MAX(sal)>=2500
ORDER BY 2;
--查询每个部门名字中包含A的人数,只显示人数小于3的,按照人数降序排序
SELECT deptno,COUNT(1)
FROM emp
WHERE INSTR(ename,'A')!=0
GROUP BY deptno
HAVING COUNT(1)<3
ORDER BY 2 DESC;
WHERE 开启分组前的筛选条件 → GROUP BY 分组 → HAVING 开启分组后的筛选条件。
--统计员工表每个职位上半年入职的员工数量,只显示人数小于3的职位,按照职位排序
SELECT job,COUNT(1)
FROM emp
WHERE to_char(hiredate,'mm')<7
GROUP BY job
HAVING COUNT(1)<3
ORDER BY 1;
--统计HR表中FIRST_NAME长度为5的员工中,在一周的每一天入职的人数,显示入职人数小于5的,按人数降序排序
SELECT to_char(e.HIRE_DATE,'day'),COUNT(1)
FROM employees e
WHERE LENGTH(e.FIRST_NAME)=5
GROUP BY to_char(e.HIRE_DATE,'day')
HAVING COUNT(1)<5
ORDER BY 2 DESC;
--统计每年每个月入职的人数,只显示人数大于1的月份,按照人数降序排序
SELECT to_char(hiredate,'yyyy-mm'),COUNT(1)
FROM emp
GROUP BY to_char(hiredate,'yyyy-mm')
HAVING COUNT(1)>1
ORDER BY 2 DESC;
--统计emp表各字母开头的人数,筛选出人数大于1的,按照字母升序排序
SELECT SUBSTR(ename,1,1),COUNT(1)
FROM emp
GROUP BY SUBSTR(ename,1,1)
HAVING COUNT(1)>1
ORDER BY 1;
--统计工资小于3000的员工中每年入职的人数,平均工资,筛选出平均工资在1000-2000的,按照年份升序排序
SELECT to_char(hiredate,'yyyy'),COUNT(1),AVG(sal)
FROM emp
WHERE sal<3000
GROUP BY to_char(hiredate,'yyyy')
HAVING AVG(sal) BETWEEN 1000 AND 2000
ORDER BY 1;
6. wm_concat(列) 按照分组连接字符串
分隔为逗号,并且连接时只能按照默认的顺序去连接该列
--查询每个部门的员工姓名,用逗号隔开
SELECT deptno,wm_concat(ename)
FROM emp
GROUP BY deptno;
7. listagg(列,'分隔符')WITHIN GROUP(ORDER BY 排序列) 按照分组连接字符串,可以排序可以指定分隔符
--查询每个部门的员工姓名,用/隔开,并且按照姓名排序
SELECT deptno,
listagg(ename,'/')WITHIN GROUP(ORDER BY ename)
FROM emp
GROUP BY deptno;
--查询每个部门的员工姓名,用/隔开
SELECT deptno,REPLACE(wm_concat(ename),',','/')
FROM emp
GROUP BY deptno;
--排序时空值的位置：
默认空值最大,可以在排序列后边加上nulls first和nulls last改变空值的位置把空值放在列的开头或者结尾
写在降序之后,如果需要,每个排序列都要单独写
NULLS FIRST 排序时空值会排在最前
NULLS LAST 排序时空值会排在最后
--查询员工姓名,工资,提成,按照提成升序排序
SELECT ename,sal,comm
FROM emp
ORDER BY comm NULLS FIRST; --空值在前
SELECT ename,sal,comm
FROM emp
ORDER BY comm DESC NULLS LAST; --空值在后
NULL的小结：
1.空不占存储,空不等于0,也不等于空格,也不完全等于空字符''
2.对一行值进行计算时,比如单行函数/四则运算,任何数与空的结果都为空
3.对一列值进行计算时,比如聚合函数/分析函数,空不参与运算
4.可以使用 NVL/NVL2/COALESCE 对空进行处理
5.排序时空最大,和空比较大小时结果为空
where和having的区别：
1.where是分组前的筛选,写在group by之前
having是分组后的筛选,写在group by之后
2.where条件内不能写聚合函数
having条件内可以写聚合函数
--执行顺序
SELECT --5
FROM --1
WHERE --2
GROUP BY --3
HAVING --4
ORDER BY --6
SELECT ename,sal AS 工资
FROM emp
WHERE 工资>1000; --×
SELECT ename,sal AS 工资
FROM emp
ORDER BY 工资; --√
--因为where的执行顺序先于select,所以select中起的别名where无法识别
--order by的执行顺序晚于select,所以order by可以识别select中起的别名
4.6分析函数(开窗函数)
分析函数和聚合函数的区别:
聚合函数分组后一个组只能看到一条聚合的记录，分析函数除了可以看聚合的记录(不止一条),还可以看到明细信息。
使用时的注意事项：
在分析函数的使用中如果括号内保留分组则表示按照分组进行处理，分组列的排序默认是升序，只有在累计求和或者是按行累计求和时才加上排序；除此之外的其他函数运算在括号内再次对分组列或分组列外的其他列进行排序均不影响函数运算的结果。
1.分析函数的语法(开窗子句)：
函数名([列])OVER([PARTITION BY 分组列] [ORDER BY 排序列])
⑴ MIN(列)OVER([PARTITION BY 分组列]) 每一行后面都跟一个最大值
⑵ MAX(列)OVER([PARTITION BY 分组列])
⑶ AVG(列)OVER([PARTITION BY 分组列]) 每一行后面都跟一个平均值
⑷ SUM(列)OVER([PARTITION BY 分组列])
⑸ COUNT(*/1/列)OVER([PARTITION BY 分组列]) 每一行后面都跟一个计数值
当不需要分组时如何？？？？表里的所有行都对应显示函数处理后的数据即不受分组的限制例如按行累计求职从头一直累计到尾。
--查询每个部门的最低工资,每种职位的平均工资,全表有提成的人数,并且显示员工姓名,部门编号,职位
SELECT ename,deptno,job,
MIN(sal)OVER(PARTITION BY deptno),
AVG(sal)OVER(PARTITION BY job),
COUNT(comm)OVER()
FROM emp;
--查询每个人的姓名,部门编号,经理编号,部门总工资,经理下属人数,全表最高工资
SELECT ename,deptno,mgr,
SUM(sal)OVER(PARTITION BY deptno),
COUNT(1)OVER(PARTITION BY mgr),
MAX(sal)OVER()
FROM emp;
--累计求值,只需要在over的括号内加上 order by 排序列 即可
--查询员工姓名,工资,全表总工资(累计求和)
SELECT ename,sal,
SUM(sal)OVER(ORDER BY sal) --累计求和
FROM emp;
--累计求值时,如果排序列出现相同的值,那么累计求值的列的对应行会一块去累计,
--并且相同的行后面都显示多行一块累计之后的值
--如果想按行累计,而不是按照排序列的值累计,只需要在order by排序列之后加上
rows between unbounded preceding and current row 即可。
order by 累计求值与按行累计求值的区别：order by 时当排序列的值相同时，例如sal：1000，3000，3000将会出现1000，4000，4000。
--查询员工姓名,工资,全表总工资(累计求和)
SELECT ename,sal,
SUM(sal)OVER(ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS a --按行累计
FROM emp;
--查询工资,全表工资总和,全表平均工资,按工资升序排序(按行累计)
SELECT sal,
SUM(sal)OVER(ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS a,
AVG(sal)OVER(ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS b
FROM emp;
--按照部门分组,查询名字,部门,工资,累计总工资,累计平均工资
SELECT ename,deptno,sal,
SUM(sal)OVER(PARTITION BY deptno ORDER BY sal) AS a,
AVG(sal)OVER(PARTITION BY deptno ORDER BY sal) AS b
FROM emp;
6. row_number()OVER([PARTITION BY 分组] ORDER BY 排序) 计数 --必须排序
按照排序列排序时,如果排序列值相同,不存在并列,结果为 1 2 3 4 5
7. RANK()OVER([PARTITION BY 分组] ORDER BY 排序) 排名 --必须排序
按照排序列排序时,如果排序列值相同,并列跳级,结果为 1 2 2 4 5
8. denSe_rank()OVER([PARTITION BY 分组] ORDER BY 排序) 颁奖 --必须排序
按照排序列排序时,如果排序列值相同,并列不跳级,结果为 1 2 2 3 4
--查询员工姓名,工资,工资排名
SELECT ename,sal,
RANK()OVER(ORDER BY sal DESC) AS 工资排名
FROM emp;
--查询员工姓名,工资,部门编号,各部门工资排名
SELECT ename,sal,deptno,
RANK()OVER(PARTITION BY deptno ORDER BY sal DESC) AS 工资排名
FROM emp;
--查询各部门工资排名第一的员工的信息
SELECT *
FROM (SELECT ename,sal,deptno,
RANK()OVER(PARTITION BY deptno ORDER BY sal DESC) AS 工资排名
FROM emp)
WHERE 工资排名=1;
--查询SMITH的员工姓名,工资,部门编号,职位,部门最高工资,职位工资总和,部门最早的入职日期
SELECT *
FROM (SELECT ename,sal,deptno,job,
MAX(sal)OVER(PARTITION BY deptno) AS zg,
SUM(sal)OVER(PARTITION BY job) AS zh,
MIN(hiredate)OVER(PARTITION BY deptno) AS zz
FROM emp)
WHERE ename='SMITH';
9.LEAD(列[,数[,值]])OVER([PARTITION BY 分组列] ORDER BY 排序列) 向上提 --必须排序
10.LAG(列[,数[,值]])OVER([PARTITION BY 分组列] ORDER BY 排序列) 向下拉 --必须排序
将列向上提/向下拉数位,空出来的用值补上，数不写默认为1，值不写默认为空，列和值数据类型必须一致；列向上或向下提拉后空出的位置如不填充数据的话则是空，在此位置上一般的运算结果为空
--环比增长率=(本月-上月)/上月*100%
SELECT TIME,yye AS 本月,LAG(yye)OVER(ORDER BY TIME) AS 上月,
LTRIM(ROUND((yye-LAG(yye)OVER(ORDER BY TIME))/LAG(yye)OVER(ORDER BY TIME)*100,2)||'%','%') AS hbzzl
FROM hbzzl;
SELECT TIME,yye AS 本月,LAG(yye)OVER(ORDER BY TIME) AS a,LEAD(yye)OVER(ORDER BY TIME) AS b
FROM hbzzl
--按照工资降序排序,查询每个人工资比他下一个人的工资多多少
SELECT ename,sal,sal-LEAD(sal)OVER(ORDER BY sal DESC)
FROM emp;
--按照工资降序排序,查询每个人工资比他上一个人的工资少多少
SELECT ename,sal,LAG(sal)OVER(ORDER BY sal DESC)-sal
FROM emp;
11.first_value(列)OVER([PARTITION BY 分组列] [ORDER BY 排序列]) 取第一个值 --可以排序
12.last_value(列)OVER([PARTITION BY 分组列]) 取最后一个值 --不能排序
如果排序,取到值是每行自己的值,不会取最后一个值
SELECT deptno,sal
,first_value(sal)OVER(PARTITION BY deptno ORDER BY sal DESC)
,last_value(sal)OVER(PARTITION BY deptno)
FROM emp;
--查询emp表每个员工比最早入职的员工晚入职几天
SELECT ename,hiredate,
hiredate-MIN(hiredate)OVER(),
hiredate-first_value(hiredate)OVER(ORDER BY hiredate)
FROM emp;
--查询每个人和本部门最早入职的员工的工资相差多少
SELECT ename,deptno,sal,hiredate,sal-first_value(sal)OVER(PARTITION BY deptno ORDER BY hiredate) AS 差值
FROM emp;
13.wm_concat(列)OVER([PARTITION BY 分组列] [ORDER BY 排序列]) 分组连接字符串
14.listagg(列,'分隔符')WITHIN GROUP(ORDER BY 排序列)OVER([PARTITION BY 分组列] [ORDER BY 排序列]) 分组连接字符串
?????????????????????????????????????????
SELECT ename,deptno,
wm_concat(ename)OVER(PARTITION BY deptno)
,listagg(ename,'-')WITHIN GROUP(ORDER BY ename)OVER(PARTITION BY deptno)
FROM emp;
--查询每个人的姓名,职位,每个职位都有哪些员工(以逗号连接,按姓名字母排序)
SELECT ename,job,
listagg(ename,',')WITHIN GROUP(ORDER BY ename)OVER(PARTITION BY job) AS a
FROM emp;
--查询员工编号,员工姓名,部门编号,每个部门的 员工编号-员工姓名
SELECT empno,ename,deptno,
wm_concat(empno||'-'||ename)OVER(PARTITION BY deptno) AS a
FROM emp;
15.ntile(数)OVER([PARTITION BY 分组列] ORDER BY 排序列) --必须排序
按照排序平均把数据分为数组
--按照工资降序,将emp表分为2部分
SELECT e.*,NTILE(2)OVER(ORDER BY sal DESC) AS a
FROM emp e;
--分组之后可以再聚合
--按照工资降序将EMP表分为3部分,查询每部分的平均工资
SELECT a,AVG(sal)
FROM (SELECT e.*,DECODE(NTILE(3)OVER(ORDER BY sal DESC),1,'高',2,'中',3,'低') AS a
FROM emp e)
GROUP BY a
小结：
- 五个简短函数的累计计算（累计求值,只需要在over的括号内加上 order by 排序列 即可）
SUM(__)OVER(PARTITION BY __ ORDER BY __)
AVG(__)
MAX(__)
MIN(__)
COUNT(__)
2.row_number()OVER([PARTITION BY 分组] ORDER BY 排序) 计数 --必须排序 12345
3.RANK()OVER([PARTITION BY 分组] ORDER BY 排序) 排名 --必须排序12245
4.denSe_rank()OVER([PARTITION BY 分组] ORDER BY 排序) 颁奖 --必须排序12234
5.LEAD(列[,数[,值]])OVER([PARTITION BY 分组列] ORDER BY 排序列) 向上提
6.LAG(列[,数[,值]])OVER([PARTITION BY 分组列] ORDER BY 排序列) 向下拉 --必须排序
7.first_value(列)OVER([PARTITION BY 分组列] [ORDER BY 排序列]) 取第一个值 --可以排序
8.last_value(列)OVER([PARTITION BY 分组列]) 取最后一个值 --不能排序
9.wm_concat(列)OVER([PARTITION BY 分组列] [ORDER BY 排序列])
10.listagg(列,'分隔符')WITHIN GROUP(ORDER BY 排序列)OVER([PARTITION BY 分组列] [ORDER BY 排序列])
11.ntile(数)OVER([PARTITION BY 分组列] ORDER BY 排序列) 按照排序平均把数据分为数组 --必须排序 不写数默认分成
4.8行列转换/部分日期函数
4.8.1 日期函数
1. SYSDATE 当前系统时间
2. add_months(日期,数) 日期加减月
数默认截断到整数
如果日期是当月的最后一天,加减月份后会自动变为目标月的最后一天
如果日期的天数在目标月没有,比如1月31日加一月,而二月没有31日,会自动变为当月最后一天
SELECT add_months(SYSDATE,2)
FROM dual;
SELECT add_months(DATE'2023-2-28',1),
add_months(DATE'2023-1-31',1)
FROM dual;
--查询三个月前距今多少天
SELECT SYSDATE-add_months(SYSDATE,-3)
FROM dual;
--查询三个月后是几月
SELECT to_char(add_months(SYSDATE,3),'mm')
FROM dual;
--查询一年后今天是星期几
SELECT to_char(add_months(SYSDATE,12),'day')
FROM dual;
3.months_between(日期1,日期2) 两个日期相差的月数
在oracle的这个函数中,不足月的部分统一按照一月31天计算,即不足月的天数/31得出小数部分
SELECT months_between(SYSDATE,DATE'2020-1-1'),
months_between(DATE'2024-1-2',DATE'2023-1-1')
FROM dual;
--查询2008-8-8距今几个月,四舍五入保留两位小数
SELECT ROUND(months_between(SYSDATE,DATE'2008-8-8'),2)
FROM dual;
--查询2012-12-21距今多少年,四舍五入保留两位小数
SELECT ROUND(months_between(SYSDATE,DATE'2012-12-21')/12,2)
FROM dual;
1.求两个日期相差的
年数 months_between(日期1,日期2)/12
月数 months_between(日期1,日期2)
天数 日期1-日期2
小时数 (日期1-日期2)*24
分数 (日期1-日期2)*24*60
秒数 (日期1-日期2)*24*60*60
2.日期加减
年 add_months(日期,年数*12)
月 add_months(日期,月数)
天 日期±天数
小时 日期±小时数/24
分钟 日期±分钟数/24/60
秒 日期±秒数/24/60/60
--查询3年5个月零6天23小时59分59秒之后的日期
SELECT add_months(SYSDATE,41)+7-1/24/60/60
FROM dual;
--把hiredate当做员工的生日,查询每个员工现在几岁零几个月零几天
SELECT ename,
REPLACE(REPLACE(TRUNC(months_between(SYSDATE,hiredate)/12)||'岁零'||
TRUNC(MOD(months_between(SYSDATE,hiredate),12))||'个月零'||
TRUNC(MOD(months_between(SYSDATE,hiredate),1)*31)||'天','零0个月'),'零0天','整') AS 年龄
FROM emp;
4.next_day(日期,'星期几') 求今天之后最近的星期几(不包含今天)
SELECT next_day(SYSDATE,'星期三'),
next_day(SYSDATE,'星期二')
FROM dual;
--查询下周周三
SELECT next_day(next_day(SYSDATE-1,'星期日'),'星期三')
FROM dual;
--查询本周周三和下下周周三
SELECT next_day(next_day(SYSDATE-8,'星期日'),'星期三') AS 本周三,
next_day(next_day(SYSDATE+6,'星期日'),'星期三') AS 下下周三
FROM dual;
5.last_day(日期) 求该日期的月的最后一天
SELECT last_day(SYSDATE)
FROM dual;
--查询今天距离月末还有几天
SELECT last_day(SYSDATE)-SYSDATE
FROM dual;
6.round(日期,'格式') 把日期四舍五入到格式之初
yyyy 年,1-6月舍到今年初,7-12入到明年初
mm 月,1-15舍到本月初,16及以后入到下月初
dd 日,0-12时舍到今天0时,13-23入到明天0时
q 季度,第二个月的15日及之前舍到本季度初,16日及以后入到下季度初
d/dy/DAY 星期,1-4(上周日-本周三)舍到上周日,5-7(周四-周六)入到本周日
hh 小时,0-30分舍到本小时整,31-59分入到下小时整
mi 分,0-30秒舍到本分钟整,31-59入到下分钟整
不能四舍五入到秒
SELECT ROUND(SYSDATE,'yyyy')
,ROUND(SYSDATE,'mm')
,ROUND(SYSDATE,'dd')
,ROUND(SYSDATE,'q')
,ROUND(SYSDATE,'d')
,ROUND(SYSDATE,'dy')
,ROUND(SYSDATE,'hh')
,ROUND(SYSDATE,'mi')
FROM dual;
7.trunc(日期,'格式') 把日期截断到格式之初
yyyy 年,舍到今年初
mm 月,舍到本月初
dd 日,舍到今天0时
q 季度,舍到本季度初
d/dy/DAY 星期,舍到上周日
hh 小时,舍到本小时整
mi 分,舍到本分钟整
不能截断到秒
SELECT TRUNC(SYSDATE,'yyyy')
,TRUNC(SYSDATE,'mm')
,TRUNC(SYSDATE,'dd')
,TRUNC(SYSDATE,'q')
,TRUNC(SYSDATE,'dy')
,TRUNC(SYSDATE,'hh')
,TRUNC(SYSDATE,'mi')
FROM dual;
--查询今年有多少天
SELECT to_char(add_months(TRUNC(SYSDATE,'yyyy'),12)-1,'ddd'),
add_months(TRUNC(SYSDATE,'yyyy'),12)-TRUNC(SYSDATE,'yyyy')
FROM dual;
--查询本月有多少天
SELECT to_char(last_day(SYSDATE),'dd'),
to_char(add_months(TRUNC(SYSDATE,'mm'),2)-1,'dd')
FROM dual;
--求平闰年
1.定义法
SELECT *
FROM emp
WHERE MOD(to_char(hiredate,'yyyy'),4)=0
AND MOD(to_char(hiredate,'yyyy'),100)!=0
OR MOD(to_char(hiredate,'yyyy'),400)=0
2.求全年天数
SELECT *
FROM emp
WHERE to_char(add_months(TRUNC(SYSDATE,'yyyy'),12)-1,'ddd')=366;
3.求2月天数
SELECT *
FROM emp
WHERE to_char(add_months(TRUNC(SYSDATE,'yyyy'),2)-1,'dd')=29;
4.8.2 集合
--集合
a={1,2,3,4}
b={3,4,5,6}
a UNION b = {1,2,3,4,5,6} --union会去重
a UNION ALL b = {1,2,3,3,4,4,5,6} --union all不去重
a INTERSECT b = {3,4}
a MINUS b = {1,2} --谁在前显示谁独有的数据
b MINUS a = {5,6}
a:员工表名字包含A的员工的姓名,工资
b:员工表名字包含S的员工的姓名,工资
--求a和b的并集,交集和差集
SELECT ename,sal
FROM emp
WHERE ename LIKE '%A%' --7
UNION --10,因为会去重
SELECT ename,sal
FROM emp
WHERE ename LIKE '%S%'; --5
SELECT ename,sal
FROM emp
WHERE ename LIKE '%A%' --7
UNION ALL --12,没有去重—会保留前后都有的值
SELECT ename,sal
FROM emp
WHERE ename LIKE '%S%'; --5
SELECT ename,sal
FROM emp
WHERE ename LIKE '%A%' --7
INTERSECT --2
SELECT ename,sal
FROM emp
WHERE ename LIKE '%S%'; --5
SELECT ename,sal
FROM emp
WHERE ename LIKE '%A%' --7
MINUS --5
SELECT ename,sal
FROM emp
WHERE ename LIKE '%S%'; --5
SELECT ename,sal AS gz
FROM emp
WHERE ename LIKE '%S%' --5
MINUS --3
SELECT ename,sal
FROM emp
WHERE ename LIKE '%A%'; --7
--两个集合的列数,类型,顺序必须一致
--如果需要起别名,只需要在前面语句中起别名即可,结果以前面语句中的列名为准
4.8.3 行列转换#k&p
┌行转列 CASE WHEN/DECODE+聚合函数+GROUP BY
│ PIVOT(聚合函数(被聚合的列) FOR 行转列的列 IN (列中值1 别名1,列中值2 别名2...))
└列转行 UNION ALL
UNPIVOT [INCLUDE NULLS ](聚合的列的新列名 FOR 列名转行的新列名 IN (字段名1,字段名2...))
--加include nulls会保留空,不加自动去空
SELECT *
FROM kecheng;
--行转列
SELECT 保留的列,
聚合函数(CASE 行转列的列 WHEN 列中值1 THEN 聚合的列 END) AS 别名1,
聚合函数(DECODE(行转列的列,列中值1,聚合的列)) AS 别名2,
....
FROM 表
GROUP BY 保留的列;
--把kecheng表的course列进行行转列
SELECT NAME,
MAX(CASE course WHEN '语文' THEN score END) AS 语文,
MAX(DECODE(course,'数学',score)) AS 数学,
MAX(CASE course WHEN '英语' THEN score END) AS 英语,
MAX(CASE course WHEN '历史' THEN score END) AS 历史,
MAX(CASE course WHEN '化学' THEN score END) AS 化学
FROM kecheng
GROUP BY NAME;
SELECT *
FROM kecheng
PIVOT(MAX(score) FOR course IN ('语文' 语文,'数学' 数学,'英语' 英语,'历史' 历史,'化学' 化学));
--把kecheng表的name列进行行转列
SELECT course,
MIN(DECODE(NAME,'张三',score)) AS 张三,
SUM(DECODE(NAME,'李四',score)) AS 李四,
AVG(DECODE(NAME,'王五',score)) AS 王五
FROM kecheng
GROUP BY course;
SELECT *
FROM kecheng
PIVOT(SUM(score) FOR NAME IN ('张三' 张三,'李四' 李四,'王五' 王五));
SELECT *
FROM demo;
--把demo表的ID列行转列
SELECT NAME,
SUM(DECODE(ID,1,nums)) AS 一季度,
SUM(DECODE(ID,2,nums)) AS 二季度,
SUM(DECODE(ID,3,nums)) AS 三季度,
SUM(DECODE(ID,4,nums)) AS 四季度
FROM demo
GROUP BY NAME;
SELECT *
FROM demo
PIVOT(SUM(nums) FOR ID IN (1 一季度,2 二季度,3 三季度,4 四季度));
--行转列例题：
--把demo表的name列行转列
SELECT ID,
SUM(DECODE(NAME,'苹果',nums)) AS 苹果,
SUM(DECODE(NAME,'橘子',nums)) AS 橘子,
SUM(DECODE(NAME,'葡萄',nums)) AS 葡萄,
SUM(DECODE(NAME,'芒果',nums)) AS 芒果
FROM demo
GROUP BY ID;
SELECT *
FROM demo
PIVOT(SUM(nums) FOR NAME IN ('苹果' 苹果,'橘子' 橘子,'葡萄' 葡萄,'芒果' 芒果));
--列转行例题：
SELECT * FROM demo1;
SELECT * FROM demo2;
--把demo1列转行
SELECT *
FROM (SELECT ID,'苹果' AS NAME,苹果 AS nums
FROM demo1
UNION ALL
SELECT ID,'橘子',橘子
FROM demo1
UNION ALL
SELECT ID,'葡萄',葡萄
FROM demo1
UNION ALL
SELECT ID,'芒果',芒果
FROM demo1)
WHERE nums IS NOT NULL;
SELECT *
FROM demo1
UNPIVOT INCLUDE NULLS(销量 FOR 水果 IN (苹果,橘子,葡萄,芒果));
--把demo2列转行
SELECT *
FROM (SELECT 1 AS ID,NAME,一季度 AS nums
FROM demo2
UNION ALL
SELECT 2,NAME,二季度
FROM demo2
UNION ALL
SELECT 3,NAME,三季度
FROM demo2
UNION ALL
SELECT 4,NAME,四季度
FROM demo2)
WHERE nums IS NOT NULL;
SELECT *
FROM demo2
UNPIVOT(nums FOR 季度 IN (一季度,二季度,三季度,四季度));
小结：
1.SYSDATE 当前系统时间
2.add_months(日期,数) 日期加减月
3.months_between(日期1,日期2) 两个日期相差的月数
4.next_day(日期,'星期几') 求今天之后最近的星期几(不包含今天)
5.last_day(日期) 求该日期所在的月的最后一天
6.trunc(日期,'格式') 把日期截断到格式之初
7.ROUND(日期,'格式') 把日期四舍五入到格式之初
8.集合UNION/UNION ALL 并集（不加ALL会自动去重）;INTERSECT 交集;MINUS 差集
9. 行转列
SELECT 保留的列,
聚合函数(CASE 行转列的列 WHEN 行转列的列中值1 THEN 聚合的列 END)， AS 别名1,
聚合函数(DECODE(行转列的列,列中值1,聚合的列)) AS 别名2,
....
FROM 表
GROUP BY 保留的列;
PIVOT(聚合函数(被聚合的列) FOR 行转列的列 IN (列中值1 别名1,列中值2 别名2...))
10. 把demo1列转行
SELECT *
FROM (SELECT ID,'苹果' AS NAME,苹果 AS nums
FROM demo1
--UNION ALL--
SELECT ID,'橘子',橘子
FROM demo1
--UNION ALL--
SELECT ID,'葡萄',葡萄
FROM demo1
UNION ALL
SELECT ID,'芒果',芒果
FROM demo1)
WHERE nums IS NOT NULL;
SELECT *
FROM demo1
UNPIVOT INCLUDE NULLS(销量 FOR 水果 IN (苹果,橘子,葡萄,芒果));？？？？
4.9 子查询和表连接
4.9.1 子查询
┌相关子查询
│ ┌单列单行子查询
└非相关子查询├单列多行子查询
(标准子查询) └多列子查询
--相关子查询和非相关子查询的区别
1.相关子查询的子查询和主查询相互依赖,主查询先于子查询执行,子查询不能单独执行
例如：SELECT *
FROM A
WHERE A.b IN (SELECT b
FROM B
WHERE B.a=A.a);
2.非相关子查询的子查询的结果供主查询使用,子查询先于主查询执行,子查询可以单独执行
--单列单行子查询,返回一个值
--查询和ALLEN相同部门的员工的信息
SELECT *
FROM emp
WHERE deptno=(SELECT deptno
FROM emp
WHERE ename='ALLEN');
--查询和SCOTT工资相同的员工的信息
SELECT *
FROM emp
WHERE sal=(SELECT sal
FROM emp
WHERE ename='SCOTT');
--查询工资比SMITH工资高,比ALLEN工资低的员工的信息
SELECT *
FROM emp
WHERE sal>(SELECT sal
FROM emp
WHERE ename='SMITH')
AND sal<(SELECT sal
FROM emp
WHERE ename='ALLEN');
--查询和7788号员工的首字母相同的员工的信息
SELECT *
FROM emp
WHERE SUBSTR(ename,1,1)=(SELECT SUBSTR(ename,1,1)
FROM emp
WHERE empno=7788);
--查询工资大于全表平均工资的员工的信息
SELECT *
FROM emp
WHERE sal>(SELECT AVG(sal)
FROM emp);
SELECT *
FROM (SELECT e.*,AVG(sal)OVER() AS pj
FROM emp e)
WHERE sal>pj;
--单列多行子查询,返回一个列
单列单行子查询的操作符:< > = != 等等
单列多行子查询的操作符:ANY ALL IN EXISTS
ANY和ALL不能单独使用,需要配合单列单行子查询的操作符取使用
>ANY 大于任意一个(大于最小的)
<ANY 小于任意一个(小于最大的)
>ALL 大于所有的(大于最大的)
<ALL 小于所有的(小于最小的)
=ANY 相当于 IN
=ALL 等于所有的 --×
--查询比20部门所有人的工资都要高的员工的信息
SELECT *
FROM emp
WHERE sal >ALL (SELECT sal
FROM emp
WHERE deptno=20);
SELECT *
FROM emp
WHERE sal > (SELECT MAX(sal)
FROM emp
WHERE deptno=20)
--查询比10部门任意一人的工资高的员工的信息
SELECT *
FROM emp
WHERE sal >ANY (SELECT sal
FROM emp
WHERE deptno=10);
SELECT *
FROM emp
WHERE sal > (SELECT MIN(sal)
FROM emp
WHERE deptno=10);
--查询和任意一个名字包含S的员工部门相同的员工的信息
SELECT *
FROM emp
WHERE deptno =ANY (SELECT deptno
FROM emp
WHERE INSTR(ename,'S')!=0);
SELECT *
FROM emp
WHERE deptno IN (SELECT deptno
FROM emp
WHERE INSTR(ename,'S')!=0);
--EXISTS 存在,一般是用来引导相关子查询,看子查询是否有返回值
--IN和EXISTS谁快:
看子查询与主查询的数据量,如果子查询数据量少,IN快,子查询的数据量大,EXISTS快
--相关子查询的前提:子查询与主查询的表有关联条件
--查询部门所在地在NEW YORK的部门的员工信息
SELECT *
FROM emp
WHERE deptno IN (SELECT deptno
FROM dept
WHERE loc='NEW YORK');
SELECT *
FROM emp e
WHERE EXISTS (SELECT 1
FROM dept d
WHERE d.deptno=e.deptno
AND loc='NEW YORK');
--多列子查询,把子查询当表用
--查询部门平均工资最高的部门的员工的信息
SELECT *
FROM emp
WHERE deptno IN (SELECT deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal) = (SELECT MAX(AVG(sal))
FROM emp
GROUP BY deptno));
--WITH AS
WITH 临时表名1 AS (子查询1)[,临时表名2 AS (子查询2),...]
SELECT 列
FROM 表/临时表名
...
--with as 的好处
简化多次重复执行的SQL,将复杂的SQL命令作为"临时表"使用,
可以加快执行的速度(减少了重复多次执行该SQL),还可以减轻我们的工作量
WITH t1 AS (SELECT 列 FROM 表)
SELECT *
FROM t1
WHERE sal>(SELECT xxx
FROM t1
WHERE xxx IN (SELECT xxx
FROM t1));
--查询工资大于全表平均工资的员工的信息
WITH t1 AS (SELECT e.*,AVG(sal)OVER() AS pj FROM emp e)
SELECT *
FROM t1
WHERE sal>pj;
--查询工资大于自己部门平均工资的员工的信息,使用with as
WITH a AS (SELECT e.*,AVG(sal)OVER(PARTITION BY deptno) AS pj FROM emp e)
SELECT *
FROM a
WHERE sal>pj;
--查询工资比NEW YORK部门的任意员工薪资高的员工的信息
SELECT *
FROM emp
WHERE sal >ANY (SELECT sal
FROM emp
WHERE deptno IN (SELECT deptno
FROM dept
WHERE loc='NEW YORK'));
--查询比SALES部门的任意员工入职早的员工的信息
SELECT *
FROM emp
WHERE hiredate <ANY (SELECT hiredate
FROM emp
WHERE deptno IN (SELECT deptno
FROM dept
WHERE dname='SALES'));
--IN的特殊用法
(列1,列2,列3...) IN (SELECT 列1,列2,列3... FROM 表)
--前后的列的个数,顺序,类型必须一一对应
前面括号内的多个列与后面子查询内的多个列同时对应上时,条件才成立
--查询各部门工资最低的员工的信息
SELECT *
FROM EMP
WHERE (DEPTNO,SAL) IN (SELECT DEPTNO,MIN(SAL)
FROM EMP
GROUP BY DEPTNO);
--查询和SCOTT同部门同职位的员工的信息
SELECT *
FROM emp
WHERE job IN (SELECT job FROM emp WHERE ename='SCOTT')
AND deptno IN (SELECT deptno FROM emp WHERE ename='SCOTT');
SELECT *
FROM emp
WHERE (job,deptno) IN (SELECT job,deptno FROM emp WHERE ename='SCOTT')
--标量子查询(性能杀手)：写在select和from之间的子查询，子查询的结果是“单个值”(一行一列)，可以改为表连接
--查询员工姓名,部门编号,部门名称
SELECT ename,deptno,(SELECT dname FROM dept d WHERE e.deptno=d.deptno)
FROM emp e;
4.9.2 表连接
表连接的类型：
└外连接┌左外连接 LEFT [OUT] JOIN
├右外连接 RIGHT [OUT] JOIN
└全外连接 FULL [OUT] JOIN
┌等值连接
└不等值连接
通过两表之间的关联字段,一次查询多种表的数据,如果N张表相连接,那么至少需要N-1
⑴ON表连接
①语法:
SELECT 列
FROM 表1
JOIN 表2
ON 连接条件 --必须一个join一个on,否则将会报错
[WHERE 条件]
[GROUP BY 分组]
...
②例题：
--内连接,显示两张表共有的数据(符合连接条件的)
SELECT *
FROM emp e
JOIN dept d
ON e.deptno=d.deptno;
--查询员工姓名,部门编号,部门名称
SELECT ename,e.deptno,dname
FROM emp e
JOIN dept d
ON e.deptno=d.deptno;
--左外连接,显示共有的数据和左表独有的数据(写在前面的是左表),右表用空补齐
SELECT *
FROM dept d
LEFT JOIN emp e
ON e.deptno=d.deptno;
--右外连接,显示共有的数据和右表独有的数据(写在后面的是右表),左表用空补齐
SELECT *
FROM emp e
RIGHT JOIN dept d
ON e.deptno=d.deptno;
--全外连接,显示共有的数据和两表各自独有的数据,相互用空补齐
SELECT *
FROM employees e
FULL JOIN departments d
ON e.DEPARTMENT_ID=d.DEPARTMENT_ID;
--不等值连接
--查询每个人的姓名,工资,工资等级
SELECT ename,sal,grade
FROM emp e
JOIN salgrade s
ON e.SAL BETWEEN s.LOSAL AND s.HISAL
⑵表连接的第二套语法—WHERE表连接
语法：
查看 ‘CLARK','BLAKE'的下属信息：
SELECT A.*
FROM EMP A ,EMP B
WHERE A.MGR=B.EMPNO
AND B.ename IN ('CLARK','BLAKE');
①内连接
SELECT *
FROM emp e,dept d
WHERE e.DEPTNO=d.DEPTNO
--左外连接
SELECT *
FROM emp e,dept d
WHERE e.DEPTNO=d.DEPTNO(+)
--右外连接
SELECT *
FROM emp e,dept d
WHERE e.DEPTNO(+)=d.DEPTNO
左条件 = 右条件(+) 也称为左外连接；
左条件(+) = 右条件 也称为右外连接。
②WHERE表连接不支持全外连接
--where和on的区别
1.ON是对两表关联前的数据进行筛选,where是对关联后的数据进行筛选
左外连接中,ON后加一个不成立的条件,结果为左表全部数据,右表用空补齐
右外连接中,ON后加一个不成立的条件,结果为右表全部数据,左表用空补齐
全外连接中,ON后加一个不成立的条件,结果为两表各自全部数据,两表互相用空补齐
WHERE后加一个不成立的条件,结果为空
⑶交叉连接(笛卡尔积)
在JOIN表连接中的ON后写一个必定成立的条件或在WHERE表连接中没有写WHERE,就会发生笛卡尔积
返回的行数为两表行数的乘积
SELECT *
FROM emp e
JOIN dept d
ON 1=1
SELECT *
FROM emp e,dept d
--多表连接,一个JOIN一个ON
--查询员工姓名,工资,工资等级,部门名称
SELECT ename,sal,grade,dname
FROM emp e
JOIN dept d
ON e.DEPTNO=d.DEPTNO
JOIN salgrade s
ON e.SAL BETWEEN s.LOSAL AND s.HISAL;
--查询经理是CLARK或BLAKE的员工的信息(使用IN/EXISTS/JOIN表连接/WHERE表连接四种方法)
SELECT *
FROM emp
WHERE mgr IN (SELECT empno
FROM emp
WHERE ename IN ('CLARK','BLAKE'));
SELECT *
FROM emp A
WHERE EXISTS (SELECT 1
FROM EMP B
WHERE A.MGR=B.EMPNO
AND ename IN ('CLARK','BLAKE'));
SELECT A.*
FROM EMP A
JOIN EMP B
ON A.MGR=B.EMPNO
WHERE B.ename IN ('CLARK','BLAKE');
--执行顺序以及是否可以使用子查询
WITH AS --1,必须使用
SELECT --8,可以,标量子查询,改为表连接
FROM --2,可以,内联视图
JOIN --3,可以,内联视图
ON --4,不可以
WHERE --5,可以,经常写
GROUP BY --6,不可以
HAVING --7,可以
ORDER BY --9,不可以
小结：
1.单列多行子查询,返回一个列
单列单行子查询的操作符:< > = != 等等
单列多行子查询的操作符:ANY、ALL、IN、EXISTS
ANY和ALL不能单独使用,需要配合单列单行子查询的操作符取使用
>ANY 大于任意一个(大于最小的)
<ANY 小于任意一个(小于最大的)
>ALL 大于所有的(大于最大的)
<ALL 小于所有的(小于最小的)
=ANY 相当于 IN
=ALL 等于所有的 --×
2.EXISTS 存在,一般是用来引导相关子查询,看子查询是否有返回值
IN和EXISTS谁快:
看子查询与主查询的数据量,如果子查询数据量少,IN快,子查询的数据量大,EXISTS快
3.WITH AS
语法:
WITH 临时表名1 AS (子查询1)[,临时表名2 AS (子查询2),...]
SELECT 列
FROM 表/临时表名
...
3.IN的特殊用法
(列1,列2,列3...) IN (SELECT 列1,列2,列3... FROM 表)
--前后的列的个数,顺序,类型必须一一对应
前面括号内的多个列与后面子查询内的多个列同时对应上时,条件才成立
4. 表连接语法:
SELECT 列
FROM 表1
JOIN 表2
ON 连接条件 --必须一个join一个on,否则报错
[WHERE 条件]
[GROUP BY 分组]
...
5. 交叉连接(笛卡尔积)
在JOIN表连接中的ON后写一个必定成立的条件或在WHERE表连接中没有写WHERE,就会发生笛卡尔积，返回的行数为两表行数的乘积
4.10树形查询(层次查询)
4.10.1 树形查询
树形查询通常由根节点,父节点,子节点,叶子节点组成
根节点当前节点之上没有节点的节点 --根据条件来说,必须是它之上没有节点的
父节点当前节点之上还有节点的节点 --是相对来说的,任何一层都可以是父节点(只要它之下还有节点)
子节点当前节点之下还有节点的节点 --是相对来说的,任何一层都可以是子节点(只要它之上还有节点)
叶子节点当前节点之下没节点的节点 --根据条件来说,必须是它之下没有节点的
LEVEL 表示节点的深度
树形查询的函数
sys_connect_by_path(列,'分隔符') 合并层级
把一个根节点下的所有节点通过某个分隔符进行划分,一般我们使用/为分隔符
NAME sys_connect_by_path(NAME,'/')
张三 /张三
张小三 /张三/张小三
张小小三 /张三/张小三/张小小三
张小小四 /张三/张小三/张小小四
张小四 /张三/张小四
张小四2.0 /张三/张小四/张小四2.0
李四 /李四
李小四 /李四/李小四
李小小四 /李四/李小四/李小小四
李小五 /李四/李小五
李小小五 /李四/李小五/李小小五
connect_by_root 列 返回根节点某列的内容
NAME connect_by_root NAME
张三 张三
张小三 张三
张小小三 张三
张小小四 张三
张小四 张三
张小四2.0 张三
李四 李四
李小四 李四
李小小四 李四
李小五 李四
李小小五 李四
connect_by_isleaf 判断是否为叶子节点,是返回1,不是返回0
NAME connect_by_isleaf
张三 0
张小三 0
张小小三 1
张小小四 1
张小四 0
张小四2.0 1
李四 0
李小四 0
李小小四 1
李小五 0
李小小五 1
树形查询的语法:
SELECT [LEVEL,]列
FROM 表
[WHERE 条件]
[START WITH 条件] --满足条件的为根节点,每行数据都会做一次根节点，从谁开始第一行就是谁
CONNECT BY PRIOR 儿子列=父亲列 --查下级
父亲列=儿子列 --查上级
[ORDER SIBLINGS BY 排序列] --同级不同行之间的排序
SELECT * FROM MAP;
--查询map表的层次结构
SELECT LEVEL,LPAD(' ',LEVEL*5-5,' ')||NAME --控制缩进
FROM MAP
START WITH PARENT=0
CONNECT BY PRIOR ID=PARENT;
--查询emp表的层次结构
SELECT LEVEL,LPAD(' ',LEVEL*5-5,' ')||ENAME
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO=MGR
--查询SMITH的上级关系
SELECT LEVEL,LPAD(' ',LEVEL*5-5,' ')||ENAME
FROM EMP
START WITH ENAME='SMITH'
CONNECT BY PRIOR MGR=EMPNO;
--查询KING的下级的下级
SELECT LEVEL,ENAME
FROM EMP
WHERE LEVEL=3 --只有树形查询内可以直接写 LEVEL=数
START WITH ENAME='KING'
CONNECT BY PRIOR EMPNO=MGR;
--树形查询的函数
SELECT LEVEL,LPAD(' ',LEVEL*5-5,' ')||ename,
sys_connect_by_path(ename,'/') AS ①合并层级,
connect_by_root ename AS ②返回根节点,
connect_by_isleaf AS ③是否叶子节点
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno=mgr;
--查询SMITH的上级的上级的员工信息
SELECT *
FROM EMP
WHERE LEVEL=3
START WITH ENAME='SMITH'
CONNECT BY PRIOR MGR=EMPNO;
--查询所有没有下属的员工的信息
SELECT DISTINCT *
FROM EMP
WHERE CONNECT_BY_ISLEAF=1
--START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO=MGR;
SELECT *
FROM EMP
WHERE EMPNO NOT IN (SELECT NVL(MGR,0) FROM EMP);
--查询所有有下属的员工的姓名,每个人的下属人数(包括自己的直属下属和下属的下属等)
SELECT NAME,COUNT(1)-1 AS 下属人数
FROM (SELECT LEVEL,ENAME,CONNECT_BY_ROOT ENAME AS NAME
FROM EMP
CONNECT BY PRIOR EMPNO=MGR)
GROUP BY NAME
HAVING COUNT(1)>1;
4.10.2 伪列
伪列可以像正常列一样被查询使用,但是不能修改删除等
┌LEVEL -- 1
├ROWNUM --2
└ROWID
1．LEVEL
必须从1开始且连续
必须和CONNECT BY配合使
LEVEL的用法:
⑴树形查询中作为节点深度
⑵控制显示格式 --控制缩进
⑶生成多条记录
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL<=100 --LEVEL作为伪列使用时,只能 LEVEL<=数 或 LEVEL<数
--查询今年所有的日期
SELECT TRUNC(SYSDATE,'YYYY')+LEVEL-1
FROM DUAL
CONNECT BY LEVEL<=TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),12)-1,'DDD');
--查询今年所有的星期五
SELECT TRUNC(SYSDATE,'YYYY')+LEVEL-1
FROM DUAL
WHERE TO_CHAR(TRUNC(SYSDATE,'YYYY')+LEVEL-1,'DY')='星期五'
CONNECT BY LEVEL<=TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),12)-1,'DDD');
--查询2008-2020年所有的 年-月 月初 月末
SELECT ADD_MONTHS(DATE'2008-1-1',LEVEL-1) AS 月初,
LAST_DAY(ADD_MONTHS(DATE'2008-1-1',LEVEL-1)) AS 月末,
TO_CHAR(ADD_MONTHS(DATE'2008-1-1',LEVEL-1),'YYYY-MM')
FROM DUAL
CONNECT BY LEVEL<=(2020-2008+1)*12;
⑷一行转多行
'A,B,C,D,E'
SELECT SUBSTR('A,B,C,D,E,F',LEVEL*2-1,1)
FROM DUAL
CONNECT BY LEVEL<=LENGTH(REPLACE('A,B,C,D,E,F',','));
'AA,BB,CC,DD,EE'
SELECT SUBSTR('AA,BB,CC,DD,EE',LEVEL*3-2,2)
FROM DUAL
CONNECT BY LEVEL<=LENGTH(REPLACE('AA,BB,CC,DD,EE',','))/2;
如果每个部分有N位,分隔符为1位
SELECT SUBSTR(STR,LEVEL*(N+1)-N,N)
FROM DUAL
CONNECT BY LEVEL<=LENGTH(REPLACE(STR,'分隔符'))/N;
'AA,,BB,,CC,,DD,,EE'
SELECT SUBSTR('AA,,BB,,CC,,DD,,EE',LEVEL*4-3,2)
FROM DUAL
CONNECT BY LEVEL<=LENGTH(REPLACE('AA,,BB,,CC,,DD,,EE',','))/2;
如果每个部分有N位,并且分隔符的位数为M位
SELECT SUBSTR(STR,LEVEL*(N+M)-(N+M-1),N)
FROM DUAL
CONNECT BY LEVEL<=LENGTH(REPLACE(STR,'分隔符'))/N;
2.ROWNUM 行号
从1开始且连续
SELECT ROWNUM,e.*
FROM emp e
--查询EMP表的前5行
SELECT *
FROM emp
WHERE ROWNUM<=5
--查询EMP表的6-10行
SELECT *
FROM (SELECT E.*,ROWNUM AS RM
FROM emp E)
WHERE RM BETWEEN 6 AND 10;
--查询EMP表全表工资最高的5个人
方法一：
SELECT *
FROM (SELECT E.*,RANK()OVER(ORDER BY SAL DESC) AS RN
FROM EMP E)
WHERE RN<=5;
方法二：
SELECT E.*,ROWNUM
FROM (SELECT *
FROM EMP
ORDER BY SAL DESC) E
WHERE ROWNUM<=5
--查询EMP表全表工资最高的第6-10名
SELECT *
FROM (SELECT E.*,ROWNUM AS RM
FROM (SELECT *
FROM EMP
ORDER BY SAL DESC) E)
WHERE RM BETWEEN 6 AND 10;
ROWNUM主要用于分页查询：
语法:
SELECT *
FROM (SELECT ROWNUM AS RN,A.*
FROM 表 A
WHERE ROWNUM<=每页显示行数*&页数) –-确定左端
WHERE RN>每页显示行数*(&页数-1) –-对括号里的范围进行限制确定右端
例题：分页查询EMPLOYEES表,每页10行
SELECT *
FROM (SELECT ROWNUM AS RN,A.*
FROM EMPLOYEES A
WHERE ROWNUM<=10*&页数)
WHERE RN>10*(&页数-1);
3.ROWID 伪列、行地址
相当于每行数据的身份证号，每行数据的ROWID都不重复,是每行数据的存放的逻辑位置，从表头至表末越来越大
SELECT E.*,ROWID
FROM EMP E
4.10.3 增强型分组函数
┌CUBE 显示总计和所有的小计
├ROLLUP 显示总计和以前面列为基础展开的后面的小计
└GROUPING SETS 只显示每一个列的小计
(1). group by A,B,C with cube，--先整体分组，再两两分组，再逐列分组，最后再对全表进行分组
则首先会对(A、B、C)进行group by，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行group by操作。
(2). rollup 是根据维度在数据结果集中进行的聚合操作。--先整体分组，然后依次从右向左减少列进行分组，最后对全表进行分组
group by A,B,C with rollup，
首先会对(A、B、C)进行group by，然后对(A、B)进行group by，然后是(A)进行group by，最后对全表进行group by操作。
group by A,B,C with rollup等价于grouping sets((A,B,C),(A,B),(A),())
(3).使用grouping sets(A,B,C)--分别对括号内的每组进行分组
会分别对这3个维度进行group by，也可以grouping sets ((A,B),(A,C)))此时相当于group by (A,B) union group by (A,C),也就是说grouping sets 后面可以指定想要的各种维度组合。
--查询zqfz表内总工资,各公司总工资,各部门总工资,各公司各部门的总工资
SELECT NULL AS BASE,NULL AS DEPT,SUM(sal)
FROM zqfz
UNION
SELECT base,NULL,SUM(sal)
FROM zqfz
GROUP BY base
UNION
SELECT NULL,dept,SUM(sal)
FROM zqfz
GROUP BY dept
UNION
SELECT base,dept,SUM(sal)
FROM zqfz
GROUP BY base,dept;
SELECT base,dept,SUM(sal)
FROM zqfz
GROUP BY CUBE(base,dept); --交换列的顺序结果不受影响
SELECT base,dept,sex,SUM(sal)
FROM zqfz
GROUP BY ROLLUP(base,dept,sex); --交换列的顺序结果会改变
SELECT base,dept,sex,SUM(sal)
FROM zqfz
GROUP BY GROUPING SETS(base,dept,sex); --交换列的顺序结果不会改变
小结：
1.树形查询的自有函数：
sys_connect_by_path(列,'分隔符') 合并层级
connect_by_root 列 返回根节点某列的内容
connect_by_isleaf 判断是否为叶子节点,是返回1,不是返回0
2.树形查询的语法
SELECT [LEVEL,]列
FROM 表
[WHERE 条件]
[START WITH 条件] --满足条件的为根节点,每行数据都会做一次根节点
CONNECT BY PRIOR 儿子列=父亲列 --查下级
父亲列=儿子列 --查上级
[ORDER SIBLINGS BY 排序列] --同级之间的排序
3.伪列
伪列可以像正常类一样被查询使用,但是不能修改删除等
┌LEVEL
├ROWNUM
└ROWID
4.LEVEL
必须从1开始且连续；
必须和CONNECT BY配合使；
树形查询中作为节点深度；
控制显示格式 --控制缩进；
生成多条记
5. ROWNUM主要用于分页查询
语法:
SELECT *
FROM (SELECT ROWNUM AS RN,A.*
FROM 表 A
WHERE ROWNUM<=每页显示行数*&页数)
WHERE RN>每页显示行数*(&页数-1)
6.增强型分组函数—放在GROUP BY 的后边
CUBE 显示总计和所有的小计
ROLLUP 显示总计和以前面列为基础展开的后面的小计
GROUPING SETS 只显示每一个列的小计
二、DDL 数据定义语言
1.DDL-CREATE 创建表
┌复制表
└手动创建表
1.1复制表
语法:
CREATE TABLE 表名 AS SELECT 语句;
--创建emp1,数据同EMP表工资大于2000的员工的信息
CREATE TABLE EMP1 AS SELECT * FROM emp WHERE sal>2000;
SELECT * FROM EMP1;
--创建emp10,emp20,emp30三张表,数据分别为emp表10,20,30部门的员工的信息
CREATE TABLE EMP10 AS SELECT * FROM EMP WHERE DEPTNO=10;
CREATE TABLE EMP20 AS SELECT * FROM EMP WHERE DEPTNO=20;
CREATE TABLE EMP30 AS SELECT * FROM EMP WHERE DEPTNO=30;
--创建空表emp_null,结构同emp表
CREATE TABLE EMP_NULL AS SELECT * FROM EMP WHERE 1=2;
--命名规则
驼峰式 ZhongguoBeijing --适用于区分大小写的情况下
下划线式 zhongguo_beijing
1.2手动创建表
语法:
CREATE TABLE 表名(列1 类型长度[ 约束1],列2 类型长度[ 约束2],列3....);
数据类型
数值型
NUMBER[(数1[,数2])]
数1是长度,数2是小数位数,数1的长度包含数2的小数位数,
比如 NUMBER(7,2) 小数点前最多可以有5位,小数点后最多可以有2位
数2不写为整数(不能有小数),有小数时会自动四舍五入或报错
数1最大为38,即number的最大精度为38位
NUMBER后不写()和数1数2,默认为 NUMBER(126),但是精度仍然为38位
INT 整数型
相当于 NUMBER(22)
字符型
CHAR[(数)] 定长字符
数为字节长度,数不写默认为1,数最大为2000
假如数为5,插入的数据为'abc',CHAR类型的列会自动在数据后补空格补够5位,即'abc '
优点:读取速度相对于可变长字符类型较快
缺点:肯能会浪费存储空间
VARCHAR(数)/VARCHAR2(数) 可变长字- -名字一般用这个
VARCHAR的数最大为2000,VARCHAR2的数最大为4000
优点:位数不够时,不会补空格,直接存储,不会浪费存储空间
缺点:相对于定长字符,读取较慢
日期型
DATE
包含 世纪 年 月 日 时 分 秒 7个元素
TIMESTAMP 时间戳
比DATE更精细,存储了
世纪 年 月 日 时 分 秒 毫秒 时区 9个元素
大对象类型
BLOB 二进制大对象 存储视频/音频/图片等,用二进制存储 比如:jpg文件,mp3文件,位图等
CLOB 字符大对象 存储比较大的字符型,用CHAR来存储 比如:XML文件等
--创建一张学生表
CREATE TABLE STU(SNO NUMBER(4),
SNAME VARCHAR2(20),
SEX CHAR(2),
IDC CHAR(18),
BIRTHDAY DATE);
1.2.1约束 constraint
约束是确保数据准确性/合理性的最后一道防线
约束的分类:
主键约束 PRIMARY KEY 唯一且非空 --一张表只能有一个主键约束
唯一约束 UNIQUE 不重复
非空约束 NOT NULL 不能为空
检查约束 CHECK 给创建该约束的列写一个条件,只有满足条件才能插入
外键约束 FOREIGN KEY 参考别的表的主键列,让创建该约束的列只能有参考列内有的数据
默认值 DEFAULT 没有插入数据时,会自动补上默认的值
查看约束：
SLELCT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USERS_CONSTRAINTS;
--非列模式,不带约束名
CREATE TABLE STU2(SNO NUMBER(4) PRIMARY KEY,
SNAME VARCHAR2(20) NOT NULL,
SEX CHAR(2) CHECK(SEX IN ('男','女')),
IDC CHAR(18) UNIQUE NOT NULL,
BIRTHDAY DATE DEFAULT DATE'2000-1-1');
--非列模式,带约束名
CREATE TABLE STU3(SNO NUMBER(4) CONSTRAINT PK_STU3_SNO PRIMARY KEY,
SNAME VARCHAR2(20) CONSTRAINT NN_STU3_SNAME NOT NULL,
SEX CHAR(2) CONSTRAINT CK_STU3_SEX CHECK(SEX IN ('男','女')),
IDC CHAR(18) CONSTRAINT UN_STU3_IDC UNIQUE CONSTRAINT NN_STU3_IDC NOT NULL,
BIRTHDAY DATE DEFAULT DATE'2000-1-1'); --默认值不能起名
--列模式,不带约束名
CREATE TABLE STU4(SNO NUMBER(4),
SNAME VARCHAR2(20),
SEX CHAR(2),
IDC CHAR(18),
BIRTHDAY DATE,
PRIMARY KEY(SNO,IDC), --复合主键,一个表只能有一个主键，但是可以对多个列作用
CHECK(SNAME IS NOT NULL),
CHECK(SEX IN ('男','女'));
①列模式下,主键约束可以同时对多个列生效,多个列以逗号隔开,称为复合主键
②列模式下不能用默认值,非空约束要改写为检查约束
③外键约束和复合主键只能在列模式下使用
④列模式,带约束名
CREATE TABLE STU5(SNO NUMBER(4),
SNAME VARCHAR2(20),
SEX CHAR(2),
IDC CHAR(18),
BIRTHDAY DATE,
CONSTRAINT PK_STU5_SNO_IDC PRIMARY KEY(SNO,IDC),
CONSTRAINT CK_STU5_SNAME CHECK(SNAME IS NOT NULL),
CONSTRAINT CK_STU5_SEX CHECK(SEX IN ('男','女'));
1.2.2 外键约束
创建外键约束的列内的值必须是选择的参考表的参考列内有的值,参考列必须是参考表的主键列
--比如emp表的deptno列的外键约束就参考DEPT表的DEPTNO列
--比如向EMP的DEPTNO插入DEPT的DEPTNO列没有的数据
INSERT INTO emp(empno,deptno) VALUES(1111,50);
语法:
ALTER TABLE 表 ADD [CONSTRAINT 约束名] FOREIGN KEY(创建外键约束的列) REFERENCES 参考表(参考列) [ON DELETE CASCADE/ON DELETE SET NULL]
级联删除
NO ACTION 不指定级联删除 --默认
ON DELETE CASCADE 指定级联删除
ON DELETE SET NULL 指定级联删除为空
不指定级联删除:删除父表(参考表)内被子表(创建外键约束的表)的列使用的数据时会报错，未使用的不会报错
指定级联删除:删除子表或父表时，子表的相关项和父表的对应行会一块被删除
指定级联删除为空:删除父表的内容时，父表对应行会被删除,子表创建外键约束的列的相关数据会变为空
1.3 增、删、改、查列或整表（不涉及表中具体内容）
A.增加约束
ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型(列);
B.约束重命名
ALTER TABLE 表名 RENAME CONSTRAINT 旧约束名 TO 新约束名
C.约束的启用与禁用
ALTER TABLE 表名 ENABLE/DISABLE [VALIDATE/NOVALIDATE] CONSTRAINT 约束名;
ENABLE/DISABLE 启用和禁用
VALIDATE/NOVALIDATE 生效和不生效 –-针对已有的数据
启用且生效:约束正常运行 --已有和新增的都必须符合约束
启用但不生效:已有的数据可以不符合约束,新增的数据必须符合约束 --只有检查约束存在该状态
禁用但生效:锁表,只能查询,不能对表内数据进行,插入更新和删除
禁用且不生效:约束不运行 --已有和新增的都不用符合约束
D.修改非空,默认值,列的类型长度
ALTER TABLE 表名 MODIFY 列名 [类型长度] [DEFAULT 默认值] [NULL/NOT NULL];
修改时必须按照该顺序写,
包括建表时,默认值和非空必须写在最后,这俩同时存在时,非空在默认值后
修改哪个写哪个,不修改的可以不写
修改列的类型时,该列必须为空,即该列不能有数据
修改列的长度时:
字符型改长随便改,改短不能短于现有数据中最大的长度
数值型如果不涉及小数部分,规则与字符型一致,如果涉及小数部分,该列必须为空
E.删除约束
ALTER TABLE 表名 DROP CONSTRAINT 约束名;
F.增加列
ALTER TABLE 表名 ADD 列名1 类型长度 [DEFAULT 值][约束1],列名2 类型长度 [约束2]....;
G.列重命名
ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名;
H.删除列
ALTER TABLE 表名 DROP COLUMN 列名; --一次删除一个列
或
ALTER TABLE 表名 DROP (列1,列2....); --一次删除多个列
I.表重命名
RENAME 旧表名 TO 新表名;
Z.清空表数据
TRUNCATE TABLE 表名;
K.删除表
DROP TABLE 表名 [PURGE];
加purge为物理删除,即彻底删除(不可以闪回)
不加purge为逻辑删除,即进入回收站(还可以闪回)
L.查看回收站
SELECT * FROM USER_RECYCLEBIN
M.闪回回收站中的表
FLASHBACK TABLE 表名 TO BEFORE DROP [RENAME TO 新表名];
FLASHBACK TABLE stu3 TO BEFORE DROP;
SELECT * FROM stu3;
DROP TABLE stu3;
FLASHBACK TABLE stu3 TO BEFORE DROP RENAME TO STU33;
SELECT * FROM stu33;
DROP TABLE STU33 PURGE;
FLASHBACK TABLE stu33 TO BEFORE DROP;
小结：
1.手动创建表
语法:
CREATE TABLE 表名(列1 类型长度[ 约束1],列2 类型长度[ 约束2],列3....);
2.类型：
NUMBER[(数1[,数2])]
INT 整数型
CHAR[(数)] 定长字符
VARCHAR(数)/VARCHAR2(数) 可变长字- -名字一般用这个
Date
TIMESTAMP 时间戳
BLOB 二进制大对象
CLOB 字符大对象
3. 约束 constraint
约束是确保数据准确性/合理性的最后一道防线
约束的分类
主键约束 PRIMARY KEY 唯一且非空 --一张表只能有一个主键约束
唯一约束 UNIQUE 不重复
非空约束 NOT NULL 不能为空
检查约束 CHECK 给创建该约束的列写一个条件,只有满足条件才能插入
外键约束 FOREIGN KEY 参考别的表的主键列,让创建该约束的列只能有参考列内有的数据
默认值 DEFAULT 没有插入数据时,会自动补上默认的值
4. 列模式下,主键约束可以同时对多个列生效,多个列以逗号隔开,称为复合主键
--列模式下不能用默认值,非空约束要改写为检查约束
--外键约束和复合主键只能在列模式下使用
--列模式,带约束名
2. 视图序列
2.1 视图 VIEW
是一张虚拟表,内容由创建时的查询定义
语法:
CREATE [OR REPLACE] VIEW 视图名 AS SELECT 语句 [WITH READ ONLY];
加OR REPLACE在创建时如果该视图名已存在,会替换掉原有的
不加OR REPLACE在替换式如果该视图名已存在,会报错名称已由现有对象使用
视图名一般是以V_开头
加WITH READ ONLY为创建只读视图,只能查询,不能修改
不加WITH READ ONLY为创建普通视图,可以查询和修改
修改视图会通过视图修改原表,不允许通过视图去改表
--创建一张视图v_emp_30,数据同EMP表30部门的员工的姓名,工资和部门编号
CREATE OR REPLACE VIEW V_EMP_30 AS SELECT ENAME,SAL,DEPTNO FROM EMP WHERE DEPTNO=30;
SELECT *
FROM V_EMP_30;
视图的优缺点:
优点:不占空间,安全
缺点:修改限制,性能差
--删除视图
DROP VIEW 视图名;
2.2 序列 SEQUENCE
在数据库中创建一个特殊的存放等差数列的表,主要是用来提供主键值
创建语法:
CREATE SEQUENCE 序列名
[START WITH 数] --第一次使用时从几开始,默认为1
[MAXVALUE 数] --到几结束,默认9999999999999999999999999999
[MINVALUE 数] --结束之后从几开始重新循环,默认为1,不能大于START WITH
[INCREMENT BY 数] --等差/步长,默认为1
[CACHE] 数 --缓存值,默认为20,不能大于循环值{循环值=（maxvalue-minvalue/步长）}
[CYCLE] --是否循环,写上循环，默认不循环
--创建一个序列s_1
CREATE SEQUENCE S_1;
使用序列（新创建的序列第一次必须使用序列名.NEXTVAL）：
序列名.NEXTVAL --下一个值；
序列名.CURRVAL --当前值
SELECT S_1.CURRVAL,S_1.NEXTVAL
FROM dual;
--创建一个序列s_2,第一次执行从5开始,最大值为10,等差为2,开启循环,循环后从2开始
CREATE SEQUENCE S_2
START WITH 5
MAXVALUE 10
MINVALUE 2
INCREMENT BY 2
CACHE 2
CYCLE;
SELECT S_2.CURRVAL,S_2.NEXTVAL
FROM dual;
删除序列
DROP SEQUENCE 序列名;
DROP SEQUENCE S_1;
三、DML 数据操作语言
1.插入、删除和更新数据
更新和删除的条件是加给被处理表的，插入的条件是加给参考表的。
1.1批量插入
插入数据 INSERT
┌批量插入
└手动插入
语法:
INSERT INTO 表名[(列1,列2,列3....)] SELECT 语句;
--创建一张空表EMP_NULL,格式同EMP
CREATE TABLE EMP_NULL AS SELECT * FROM EMP WHERE 1=2;
SELECT * FROM EMP_NULL;
--向emp_null内插入emp表30部门的数据
INSERT INTO EMP_NULL SELECT * FROM EMP WHERE DEPTNO=30;
SELECT * FROM EMP_NULL;
--向EMP_NULL的ENAME,SAL,DEPTNO插入EMP表20部门的数据
INSERT INTO EMP_NULL(ENAME,SAL,DEPTNO) SELECT ENAME,SAL,DEPTNO FROM EMP WHERE DEPTNO=20;
SELECT * FROM EMP_NULL;
主键列必须插入数据,否则会报错(并无法将NULL插入到该列)
ALTER TABLE EMP_NULL ADD PRIMARY KEY (EMPNO);
1.1.1 INSERT FIRST和INSERT ALL
把一个结果集插入到多张表
语法:
INSERT FIRST/ ALL
WHEN 条件1 THEN INTO 表1[(列1,列2...)] VALUES(列1/值1,列2/值2...) --条件约束的是要插入的值
WHEN 条件2 THEN INTO 表2[(列1,列2...)] VALUES(列1/值1,列2/值2...)
WHEN 条件3 THEN INTO 表3[(列1,列2...)] VALUES(列1/值1,列2/值2...)
...
SELECT 语句;
--创建三种空表EMP_1000,EMP_2000,EMP_3000,格式同EMP
CREATE TABLE EMP_1000 AS SELECT * FROM EMP WHERE 1=2;
CREATE TABLE EMP_2000 AS SELECT * FROM EMP WHERE 1=2;
CREATE TABLE EMP_3000 AS SELECT * FROM EMP WHERE 1=2;
--把EMP表工资大于1000的员工姓名和工资插入到EMP_1000的姓名和工资列内
--工资大于2000的员工姓名和工资插入到EMP_2000的姓名和工资列内
--工资大于3000的员工姓名和工资插入到EMP_3000的姓名和工资列内
INSERT FIRST:
WHEN SAL>3000 THEN INTO EMP_3000(ENAME,SAL) VALUES(ENAME,SAL)
WHEN SAL>2000 THEN INTO EMP_2000(ENAME,SAL) VALUES(ENAME,SAL)
WHEN SAL>1000 THEN INTO EMP_1000(ENAME,SAL) VALUES(ENAME,SAL)
SELECT * FROM EMP; --满足前面条件的数据不会再和后面的条件做对比,只插入第一个满足条件的表内
INSERT ALL:
WHEN SAL>1000 THEN INTO EMP_1000(ENAME,SAL) VALUES(ENAME,SAL)
WHEN SAL>2000 THEN INTO EMP_2000(ENAME,SAL) VALUES(ENAME,SAL)
WHEN SAL>3000 THEN INTO EMP_3000(ENAME,SAL) VALUES(ENAME,SAL)
SELECT * FROM EMP; --满足前面条件的数据还会和后面的条件做对比,数据可以多次插入符合条件的表内
SELECT * FROM EMP_1000;
SELECT * FROM EMP_2000;
SELECT * FROM EMP_3000;
--创建三种空表,EMPA,EMPJ,EMPS,格式同EMP
CREATE TABLE EMPA AS SELECT * FROM EMP WHERE 1=2;
CREATE TABLE EMPJ AS SELECT * FROM EMP WHERE 1=2;
CREATE TABLE EMPS AS SELECT * FROM EMP WHERE 1=2;
--把EMP表内名字包含J的员工信息插入到EMPJ
--名字包含S的员工信息插入到EMPS
--名字包含A的员工信息插入到EMPA
--分别使用INSERT FIRST和INSERT ALL完成
INSERT FIRST
WHEN INSTR(ENAME,'A')!=0 THEN INTO EMPA VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
WHEN INSTR(ENAME,'J')!=0 THEN INTO EMPJ VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
WHEN INSTR(ENAME,'S')!=0 THEN INTO EMPS VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
SELECT * FROM EMP;
INSERT ALL
WHEN INSTR(ENAME,'A')!=0 THEN INTO EMPA VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
WHEN INSTR(ENAME,'J')!=0 THEN INTO EMPJ VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
WHEN INSTR(ENAME,'S')!=0 THEN INTO EMPS VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
SELECT * FROM EMP;
1.2手动插入
⑴语法:
INSERT INTO 表名[(列1,列2...)] VALUES(值1,值2...);
注意：手动插入时一个INSERT INTO只能插入一条/行数据
--清空EMP_NULL
TRUNCATE TABLE EMP_NULL;
--向EMP_NULL手动插入一条数据
INSERT INTO EMP_NULL VALUES(1234,'小明','小秘书',7788,DATE'2000-1-1',8888.88,666.66,40);
COMMIT; --提交
ROLLBACK; --回滚
SELECT * FROM EMP_NULL;
可以把序列值当做主键值使用：
INSERT INTO EMP_NULL(EMPNO,ENAME,SAL) VALUES(S_1.NEXTVAL,'小红',1);
INSERT INTO EMP_NULL(EMPNO,ENAME,SAL) VALUES(S_1.NEXTVAL,'小绿',2);
INSERT INTO EMP_NULL(EMPNO,ENAME,SAL) VALUES(S_1.NEXTVAL,'小白',3);
INSERT INTO EMP_NULL(EMPNO,ENAME,SAL) VALUES(S_1.NEXTVAL,'小黑',4);
INSERT INTO EMP_NULL(EMPNO,ENAME,SAL) VALUES(S_1.NEXTVAL,'小黄',5);
INSERT INTO EMP_NULL(EMPNO,ENAME,SAL) VALUES(S_1.NEXTVAL,'小紫',6);
INSERT INTO EMP_NULL(EMPNO,ENAME,SAL) VALUES(S_1.NEXTVAL,'小青',7);
INSERT INTO EMP_NULL(EMPNO,ENAME,SAL) VALUES(S_1.NEXTVAL,'小橙',8);
INSERT INTO EMP_NULL(EMPNO,ENAME,SAL) VALUES(S_1.NEXTVAL,'小粉',9);
INSERT INTO EMP_NULL(EMPNO,ENAME,SAL) VALUES(S_1.NEXTVAL,'小蓝',10);
INSERT INTO EMP_NULL(EMPNO,ENAME,SAL) VALUES(S_1.NEXTVAL,'小那',11);
INSERT INTO EMP_NULL(EMPNO,ENAME,SAL) VALUES(S_1.NEXTVAL,'小林',12);
INSERT INTO EMP_NULL(EMPNO,ENAME,SAL) VALUES(S_1.NEXTVAL,'小纪',13);
INSERT INTO EMP_NULL(EMPNO,ENAME,SAL) VALUES(S_1.NEXTVAL,'小郑',14);
INSERT INTO EMP_NULL(EMPNO,ENAME,SAL) VALUES(S_1.NEXTVAL,'小肖',15);
COMMIT;
SELECT * FROM EMP_NULL;
INSERT INTO EMP_NULL(EMPNO,ENAME,SAL) VALUES(S_1.NEXTVAL,'小刘',16);
COMMIT;
⑵用DEFAULT 默认值插入数据
①修改默认值
ALTER TABLE EMP_NULL MODIFY HIREDATE DEFAULT DATE'2000-1-1';
②不对称插入数据
INSERT INTO EMP_NULL(EMPNO,ENAME,SAL) VALUES(S_1.NEXTVAL,'小赵',17);
COMMIT;
INSERT INTO EMP_NULL(EMPNO,ENAME,SAL,HIREDATE) VALUES(S_1.NEXTVAL,'小孙',18,DEFAULT);
COMMIT;
如果有设置默认值的列在没有插入数据时(例如被VALUES的值中没有写HIREDATE),会自动补默认值,如果要显示空不用默认值,需要给它插入一个NULL
INSERT INTO EMP_NULL(EMPNO,ENAME,SAL,HIREDATE) VALUES(S_1.NEXTVAL,'小李',19,NULL);
COMMIT;
SELECT * FROM EMP_NULL;
1.3更新 UPDATE
语法:
UPDATE 表名 SET 列1=值1/处理过的列1[,列2=值2/处理过的列2....] [WHERE 条件];
注意：不加WHERE时是对全表进行更新
--创建EMP1,数据同EMP
CREATE TABLE EMP1 AS SELECT * FROM EMP;
--更新EMP1内所有人的职位为小写,工资增加500
UPDATE EMP1 SET JOB=LOWER(JOB),SAL=SAL+500;
SELECT * FROM EMP1;
--创建emp1,数据同EMP
CREATE TABLE EMP1 AS SELECT * FROM EMP;
--更新10部门的工资为之前的两倍,名字转小写
--更新20部门的工资为之前的一半,名字转首字母大写
--更新30部门的工资为原来的1/100,名字左侧加个*
UPDATE EMP1 SET SAL=SAL*2,ENAME=LOWER(ENAME) WHERE DEPTNO=10;
UPDATE EMP1 SET SAL=SAL/2,ENAME=INITCAP(ENAME) WHERE DEPTNO=20;
UPDATE EMP1 SET SAL=SAL/100,ENAME='*'||ENAME WHERE DEPTNO=30;
UPDATE EMP1 SET SAL=CASE WHEN DEPTNO=10 THEN SAL*2
WHEN DEPTNO=20 THEN SAL/2
WHEN DEPTNO=30 THEN SAL/100
ELSE SAL END,
ENAME=CASE DEPTNO WHEN 10 THEN LOWER(ENAME)
WHEN 20 THEN INITCAP(ENAME)
WHEN 30 THEN '*'||ENAME
ELSE ENAME END
WHERE DEPTNO IN (10,20,30);
--如果EMP1表员工更新后的工资比原本的工资低,将他的职位更新为淘汰
UPDATE EMP1 A SET JOB='淘汰' WHERE SAL<(SELECT SAL FROM EMP B WHERE B.EMPNO=A.EMPNO);
SELECT * FROM EMP1;
1.4 删除数据 DELETE
语法:
DELETE FROM 表名 [WHERE 条件];
注意：不加WHERE删除全表所有数据
--删除EMP1表内1981年入职的员工的信息
DELETE FROM EMP1 WHERE TO_CHAR(HIREDATE,'YYYY')=1981;
SELECT * FROM EMP1;
--删除EMP1表所有数据
DELETE FROM EMP1;
TRUNCATE TABLE EMP1;
--TRUNCATE和DELETE的区别
TRUNCATE是DDL,不能回滚
2.TRUNCATE不能加条件,只能清空全表数据,
DELETE可以加条件删除某些数据
3.TRUNCATE可以降低高水位线,
DELETE不行
4.TRUNCATE相对较快,
DELETE相对较慢
5.DELETE会产生大量日志,
TRUNCATE不会
-.DROP是直接删除整张表
1.5 去重
--创建EMP_CF,数据同EMP表,并且向其再一次插入30部门的员工信息(即30部门所有人的信息都重复)
CREATE TABLE EMP_CF AS SELECT * FROM EMP;
INSERT INTO EMP_CF SELECT * FROM EMP WHERE DEPTNO=30;
COMMIT;
SELECT * FROM EMP_CF;
1.5.1 情况一：包括主键列在内完全重复(所有列都重复)（可能还未设置主键但是计划设置主键的列目前即重复）
1.倒表
1)创建临时表EMP_CF_1,数据为DISTINCT后的EMP_CF
CREATE TABLE EMP_CF_1 AS SELECT DISTINCT * FROM EMP_CF;
--SELECT * FROM EMP_CF_1;
2)清空EMP_CF
TRUNCATE TABLE EMP_CF;
3)将临时表EMP_CF_1内的数据插入回原表EMP_CF
INSERT INTO EMP_CF SELECT * FROM EMP_CF_1;
COMMIT;
4)删掉临时表EMP_CF_1
DROP TABLE EMP_CF_1;
2.ROWID --越早的越小
--按照所有列分组,分到同一组的一定是重复数据,此时每一组对应好几个ROWID,在每一组的“行”中只保留一个最早的或者最晚的,其余的删除
3.ROW_NUMBER[不跳数排序]
DELETE FROM EMP_CF WHERE ROWID IN
(SELECT ROWID
FROM (SELECT ROW_NUMBER()OVER(PARTITION BY EMPNO,ENAME,SAL,DEPTNO,JOB ORDER BY ROWID) AS RN,ROWID
FROM EMP_CF)
WHERE RN>=2);
纯ROW_NUMBER;
DELETE FROM TABLE WHERE COLUMN1 IN
(SELECT COLUMN
FROM (SELECT COLUMN1,COLUMN2…
FROM (SELECT COLUMN1,COLUMN2…,ROW_NUMBER()OVER(PARTITION BY COLUMN1,COLUMN2…) AS RN
FROM TABLE)
WHERE RN>=2);
1.5.2 情况二：不完全重复(主键列等某些列不重复)
--假设EMP_CF表内EMPNO为主键列,并且该列不重复,其余列全部重复
1.倒表
1)创建临时表EMP_CF_1,数据为DISTINCT后的EMP_CF
CREATE TABLE EMP_CF_1 AS SELECT DISTINCT 除了不重复的列外的所有列 FROM EMP_CF;
SELECT * FROM EMP_CF_1;
2)清空EMP_CF
TRUNCATE TABLE EMP_CF;
3)将EMP_CF_1内的数据插入回EMP_CF
INSERT INTO EMP_CF SELECT 序列名.NEXTVAL,a.* FROM EMP_CF_1 a;
--原来的主键列不能用了所以需要引入一个序列作为新的主键列
COMMIT;
4)删掉EMP_CF_1
DROP TABLE EMP_CF_1;
2.ROWID
DELETE FROM EMP_CF WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM EMP_CF GROUP BY 除了不重复的列外的所有列);
--此时重复行的主键列也会一同删掉，不会出现某行除了主键列外数据为空的情况
3.ROW_NUMBER ？？？？？
DELETE FROM EMP_CF WHERE ROWID IN
(SELECT ROWID
FROM (SELECT ROW_NUMBER()OVER(PARTITION BY ENAME,SAL,DEPTNO,JOB ORDER BY EMPNO DESC) AS RN,ROWID
FROM EMP_CF)
WHERE RN>=2);
SELECT * FROM EMP_CF FOR UPDATE;
小结：
PART.4 第四部分
1. MERGE INTO 事务 正则函数
1.1 合并数据 MERGE
语法（参考B表来处理A表；可以一次性进行更新、插入和删除，可以像单独的增、删、改语句一样加条件）:
MERGE INTO 表A --进行插入更新的表
USING 表B --参考表
ON (条件) --两表的关联条件
WHEN MATCHED THEN --符合关联条件的
UPDATE SET A.列1=B.列1/值1[,A.列2=B.列2/值2...] --更新
WHEN NOT MATCHED THEN --不符合关联条件的
INSERT[(A.列1,A.列2...)] --插入到表A的哪些列,不写表示所有列
VALUES(B.列1,B.列2...) ; --用表B的哪些列插入,或者使用常量 注意末尾加分号
--创建T_A和T_B两张表,数据同EMP
CREATE TABLE T_A AS SELECT * FROM EMP;
CREATE TABLE T_B AS SELECT * FROM EMP;
--将T_B内的员工姓名改为小写,再插入两条与表内已有数据不同的数据
UPDATE T_B SET ENAME=LOWER(ENAME);
COMMIT;
INSERT INTO T_B VALUES(1,'小明','秘书',7839,DATE'1999-1-1',3500,NULL,40);
INSERT INTO T_B VALUES(2,'小红','司机',7839,DATE'2000-1-1',3200,NULL,40);
COMMIT;
SELECT *
FROM T_B;
--参考T_B合并更新T_A
MERGE INTO T_A A
USING T_B B
ON (A.EMPNO=B.EMPNO)
WHEN MATCHED THEN
UPDATE SET A.ENAME=B.ENAME
WHEN NOT MATCHED THEN
INSERT
VALUES(B.EMPNO,B.ENAME,B.JOB,B.MGR,B.HIREDATE,B.SAL,B.COMM,B.DEPTNO);
SELECT *
FROM T_A;
完整语法:
MERGE INTO 表A --进行插入更新的表
USING 表B --参考表
ON (条件) --两表的关联条件
WHEN MATCHED THEN --符合关联条件的
UPDATE SET A.列1=B.列1/值1[,A.列2=B.列2/值2...] --更新
[WHERE 条件] --A表内符合条件的才会被更新
[DELETE WHERE 条件] --删除条件,A表内符合条件的会被删除(必须同时符合 ON条件，WHERE条件和它本身的条件才会被删除)
WHEN NOT MATCHED THEN --不符合关联条件的
INSERT[(A.列1,A.列2...)] --插入到表A的哪些列,不写表示所有列
VALUES(B.列1,B.列2...) --用表B的哪些列插入,或者使用常量
[WHERE 条件] --B表内符合条件的才会插入到A表
--DELETE只能跟在UPDATE后面，而且只能有一个加WHERE条件，在UPDATE后面加条件后再在DELETE后面加条件条件失效。
--参考t_b合并更新T_A,更新和插入的条件为工资大于1500,删除工作是SALESMAN的员工
MERGE INTO T_A A
USING T_B B
ON (A.EMPNO=B.EMPNO)
WHEN MATCHED THEN
UPDATE SET A.ENAME=B.ENAME WHERE A.SAL>1500 --？？？
DELETE WHERE A.JOB='SALESMAN' --？？？
WHEN NOT MATCHED THEN
INSERT
VALUES(B.EMPNO,B.ENAME,B.JOB,B.MGR,B.HIREDATE,B.SAL,B.COMM,B.DEPTNO)
WHERE B.SAL>1500;
SELECT *
FROM T_A;
1.2 事务
广义的事务:任何一次要么成功要么失败的操作(DQL/DDL/DML等等)
狭义的事务:从一次提交/回滚到下一次提交/回滚之间所有的DML操作
会话：一个窗口就是一个会话
事务的四个特性(ACID)：
A原子性:事务包含的所有操作,要么成功,要么失败回滚,成功必须完全应用到数据库,失败不会对数据库产生影响；
C一致性:事务执行前和执行后保持一致的状态；
I隔离性:当多个用户并发访问数据库时,数据库为每个用户开启的事务不被其他用户所干扰,多个事务之间是相互隔离的；
D持久性:一个事务一旦提交,那么对数据库的改变是永久的,即便数据库遇到故障,也不会丢失事务的操作。
1.3 正则函数 REGEXP_....(…)
通配符
1. ^ 行首 ^A A开头的
2. [^] 非 [^A] 非A,除了A以外的都可以
3. $ 行尾 A$ A结尾的
4. . 任意单个字符
5. * 匹配0次或多次 A* 0或多个A .* 0或多个任意字符
6. + 匹配1次或多次 A+ 一个或多个A .+ 一个或多个任意字符
7. ? 匹配1次或0次 A? 0或1个A .? 0或1个任意字符
8. {数} 刚好匹配数次 A{3} AAA .{5} 5个任意字符
9. {数,} 至少匹配数次 A{3,} 至少三个A .{5,} 至少5个任意字符
10. {数1,数2} 匹配数1到数2次 A{3,5} 3-5个A .{5,10} 5-10个任意字符
11. str1|str2 str1或str2 a|b a或b a|b|c a或b或c
12. [str1str2str3....] str1或str2或str3 [ab] a或b
13. [[:SPACE:]] 空白字符,比如:换行,换页,回车等
14. [[:punct:]] 标点符号,比如: , . ? ! 等等
15. [[:cntrl:]] 空值字符
16. [[:print:]] 所有可打印字符
17. () 标签
18. \ 转义
19. [[:LOWER:]] [a-z] 所有小写字母
20. [[:UPPER:]] [A-Z] 所有大写字母
21. [[:alpha:]] [a-zA-Z] 所有字母
22. [[:digit:]] [0-9] 所有数字
23. [[:alphanum:]] [a-zA-Z0-9] 所有的字母和数字
SELECT * FROM PERSON;
A.regexp_like(str,'匹配模式'[,'匹配行为']) --返回的值为布尔型,只有成立/不成立
在str内查找匹配模式内的内容
匹配行为: c 大小写敏感 --默认
i 大小写不敏感
n 允许将 . 匹配换行等
m 允许将有换行的数据当做多行匹配
--查询PERSON表ZIP列字母结尾的行
SELECT *
FROM PERSON
WHERE REGEXP_LIKE(ZIP,'[A-Za-z]$');
--查询PERSON表ZIP列数字开头的行
SELECT *
FROM PERSON
WHERE REGEXP_LIKE(ZIP,'^[0-9]');
--查询FIRST_NAME以S开头N结尾的行
SELECT *
FROM PERSON
WHERE REGEXP_LIKE(FIRST_NAME,'^S.*N$','i')
或
SELECT *
FROM PERSON
WHERE REGEXP_LIKE(FIRST_NAME,'^[Ss].*[Nn]$')
或
SELECT *
FROM PERSON
WHERE REGEXP_LIKE(FIRST_NAME,'^S|s.*N|n$')
或
SELECT *
FROM PERSON
WHERE REGEXP_LIKE(UPPER(FIRST_NAME),'^S.*N$')
--查询ZIP列第四位是字母的行
SELECT *
FROM PERSON
WHERE REGEXP_LIKE(ZIP,'^.{3}[A-Za-z]')
--查询EMAIL列m和l之间有任意字符的行
SELECT *
FROM PERSON
WHERE REGEXP_LIKE(EMAIL,'m..l');
或
SELECT *
FROM PERSON
WHERE REGEXP_LIKE(EMAIL,'m.l','n');
--查询EMAIL列邮箱开头是l的行
SELECT *
FROM PERSON
WHERE REGEXP_LIKE(EMAIL,'^l','m');
B.REGEXP_REPLACE(STR1,'匹配模式'[,STR2[,数1[,数2[,'匹配行为']]]])
在STR1内查找匹配模式的内容,从数1位开始找,找第数2次出现的内容,替换为STR2
STR2不写替换为空
数1不写默认为1
数2不写默认找到的全部替换
--把PERSON表ZIP列所有的数字替换为*
SELECT ZIP,REGEXP_REPLACE(ZIP,'[0-9]','*')
FROM PERSON;
--把PERSON表ZIP列所有的非数字替换为*
SELECT ZIP,REGEXP_REPLACE(ZIP,'[^0-9]','*')
FROM PERSON;
--把PERSON表ZIP列所有的数字替换为*,字母替换为#
SELECT ZIP,REGEXP_REPLACE(REGEXP_REPLACE(ZIP,'[0-9]','*'),'[A-Za-z]','#')
FROM PERSON;
--查询EMP表的员工姓名,和把员工姓名的A-M替换为*,N-Z替换为#之后的员工姓名
SELECT ENAME,REGEXP_REPLACE(REGEXP_REPLACE(ENAME,'[A-M]','*'),'[N-Z]','#')
FROM EMP;
C. REGXP_INSTR(STR,'匹配模式'[,数1[,数2[,数3[,'匹配行为']]]])
在STR内数1位开始找匹配模式的内容,找数2次出现
数1数2不写默认为1
数3┌0 返回查找到内容的第一位的位置，不写默认是0
└非0 返回查找到的内容的整体的后一位的位置
--查找ZIP列内第一个非数字的字符的位置
SELECT ZIP,REGEXP_INSTR(ZIP,'[^0-9]') --返回查找到内容的位置
FROM PERSON;
SELECT ZIP,REGEXP_INSTR(ZIP,'[^0-9]',1,1,1) --返回查找到的内容的下一位的位置
FROM PERSON;
--查找ZIP列第二次出现 字母+数字 的组合的位置
SELECT ZIP,REGEXP_INSTR(ZIP,'[A-Za-z][0-9]',1,2)
FROM PERSON;
--查找EMAIL列内@第一次出现的位置,.第一次出现的位置,标点符号第一次出现的位置
SELECT EMAIL,
REGEXP_INSTR(EMAIL,'@'),
REGEXP_INSTR(EMAIL,'\.'),
REGEXP_INSTR(EMAIL,'[[:punct:]]')
FROM PERSON;
D.REGEXP_SUBSTR(STR,'匹配模式'[,数1[,数2[,'匹配行为']]]) ，在STR内从数1位开始找，截取第数2次出现匹配模式内的内容，数1数2不写默认为1
--截取ZIP列第二次出现 数字+字母 的组合
SELECT ZIP,REGEXP_SUBSTR(ZIP,'[0-9][a-zA-Z]',1,2)
FROM PERSON;
--截取ZIP列第一次出现两个数字的组合
SELECT ZIP,REGEXP_SUBSTR(ZIP,'[0-9]{2}')
FROM PERSON;
5.REGEXP_COUNT(STR,'匹配模式'[,数[,'匹配行为']]) --返回数值，从STR内第数位开始统计有几个符合匹配模式内容的，数位之前的不参与统计
--统计ZIP列每行有几个数字
SELECT ZIP,REGEXP_COUNT(ZIP,'[0-9]')
FROM PERSON;
--查找EMP表ENAME列内只包含一个A的员工的信息
SELECT *
FROM EMP
WHERE REGEXP_COUNT(ENAME,'A')=1;
或
SELECT *
FROM EMP
WHERE ENAME LIKE '%A%' AND ENAME NOT LIKE '%A%A%';
或
SELECT *
FROM EMP
WHERE INSTR(ENAME,'A')>0 AND INSTR(ENAME,'A',1,2)=0;
小结：
REGEXP_LIKE(STR, '匹配模式'[,'匹配行为'])
REGEXP_REPLACE(STR1,'匹配模式'[,STR2[,数1[,数2[,'匹配行为']]]])
REGXP_INSTR(STR,'匹配模式'[,数1[,数2[,数3[,'匹配行为']]]])
REGEXP_SUBSTR(STR,'匹配模式'[,数1[,数2[,'匹配行为']]])
REGEXP_COUNT(STR,'匹配模式'[,数[,'匹配行为']])
2. PL/SQL匿名块 变量赋值 判断
2.1 PLSQL
是在SQL的基础上增加了一些过程化的语句,比如:类型定义/判断/循环/游标/异常处理等等
┌匿名块
└有名块
--匿名块的语法
[DECLARE --声明部分
声明变量;]
BEGIN --执行部分
要执行的语句;
[EXCEPTION] --异常处理部分
异常处理;
END;
/ --命令窗口下最后结尾要单独再写一个 /
注意事项:
1.每一个部分内的完整语句后都要加分号,END后也要加分号
2.多加注释
声明部分 DECLARE
声明变量的语法:
①变量名 类型长度;
例: v_a VARCHAR2(20);
②变量名 表名.列名%TYPE; --声明一个变量,类型长度与该表的某列一致
例: v_a emp.ename%TYPE; 声明一个变量v_a 类型长度与EMP表的ENAME列一致
③变量名 表名%ROWTYPE; --声明一个变量,变量包含该表的所有列,使用时为 变量名.该表的某个列名
例: v_a emp%ROWTYPE; 使用时为 V_A.EMPNO V_A.ENAME V_A.JOB
变量的注意事项:
1.变量一般V开头,变量的名字不要与列名相同
2.变量在赋值前是空的,变量内没有值
3.变量同一时间只能存储一个值,再次赋值会覆盖之前的值
打印(输出)
DBMS_OUTPUT.PUT(参数); --不换行打印
DBMS_OUTPUT.PUT_LINE(参数); --换行打印
DBMS_OUTPUT.NEW_LINE; --不打印只换行
不换行打印必须配合换行打印或换行使用,如果使用不换行打印之后没有使用换行或换行打印,输出的结果为空
2.2 变量赋值
1.直接赋值
DECLARE
V_A VARCHAR2(20):='ABC'; --声明部分直接赋值,:=为赋值的操作符
BEGIN
DBMS_OUTPUT.PUT(V_A);
V_A:='DEF'; --执行部分直接赋值,如果在声明部分已对此变量赋过值那么新赋的值会追加在前值之后
DBMS_OUTPUT.PUT_LINE(V_A);
END;
2.变量的值赋给变量
DECLARE
V1 NUMBER:=123;
V2 NUMBER;
BEGIN
V2:=V1; --把V1的值赋给V2
DBMS_OUTPUT.PUT_LINE(V2);
END;
3.变量赋值可以运算
DECLARE
V1 DATE:=DATE'2000-1-1'+1;
BEGIN
DBMS_OUTPUT.PUT_LINE(V1);
END;
4.变量赋值可以使用函数（字符、日期、通用、分析等）
DECLARE
V1 VARCHAR2(20):=TO_CHAR(SYSDATE,'YYYY-MM-DD');
BEGIN
DBMS_OUTPUT.PUT_LINE(V1);
END;
或
DECLARE
V1 DATE:=DATE'2000-1-1';
BEGIN
DBMS_OUTPUT.PUT_LINE(TO_CHAR(V1,'YYYY-MM-DD'));
END;
5.键盘直接输入数据
DECLARE
V1 VARCHAR2(20):=&A；--宏代换弹窗请输入数据
BEGIN
DBMS_OUTPUT.PUT_LINE(V1);
END;
6.SELECT INTO赋值 --只能单行赋值(此时SELECT的结果只能为一行)
--打印员工编号为7788的员工的姓名和工资，打印员工编号为7788的员工的所有信息，用空格隔开
DECLARE
V1 EMP.ENAME%TYPE;
V2 EMP%ROWTYPE;--对这种变量SELECT赋值时,SELECT后面选中的列与“变量.列名”，的数量顺序要保持一致
V3 NUMBER;
BEGIN
SELECT ENAME,SAL INTO V1,V3 FROM EMP WHERE EMPNO=7788; --把SELECT的结果赋给变量
DBMS_OUTPUT.PUT_LINE(V1||' '||V3);
SELECT * INTO V2 FROM EMP WHERE EMPNO=7788; --把EMP表的某行全部列按行赋给变量V2
DBMS_OUTPUT.PUT_LINE(V2.EMPNO||' '||V2.ENAME||' '||V2.JOB||' '||V2.MGR||' '||V2.HIREDATE||' '||V2.SAL||' '||V2.COMM||' '||V2.DEPTNO);
END;
--打印SMITH的工资和职位
DECLARE
V1 NUMBER;
V2 VARCHAR2(20);
BEGIN
SELECT SAL,JOB INTO V1,V2 FROM EMP WHERE ENAME='SMITH';
DBMS_OUTPUT.PUT_LINE(V1||' '||V2);
END;
--打印DEPT表10部门的部门信息
DECLARE
V1 DEPT%ROWTYPE;
BEGIN
SELECT * INTO V1 FROM DEPT WHERE DEPTNO=10;
DBMS_OUTPUT.PUT_LINE(V1.DEPTNO||' '||V1.DNAME||' '||V1.LOC);
END;
7.自定义类型
TYPE 类型名 IS RECORD (小变量1 类型长度,小变量2 类型长度...);
变量名 类型名;
--打印SMITH的员工编号,工资,入职日期,职位
DECLARE
TYPE T_A IS RECORD (V1 NUMBER,V2 NUMBER,V3 DATE,V4 VARCHAR2(20));
V_A T_A;
BEGIN
SELECT EMPNO,SAL,HIREDATE,JOB INTO V_A FROM EMP WHERE ENAME='SMITH';--提取一行插入
DBMS_OUTPUT.PUT_LINE(V_A.V1||' '||V_A.V2||' '||V_A.V3||' '||V_A.V4);
END;
9.RETURNING INTO赋值 --把DML操作后的表中的值赋给变量,只能单行,可以多列(INSERT批量插入不能用?????)
--创建EMP1表,数据与EMP一致
CREATE TABLE EMP1 AS SELECT * FROM EMP;
--编写一个匿名块,删除SMITH的数据,并且打印删除的员工姓名和工资
--插入一条数据,并且打印插入的名字和工资
--更新KING的名字为小写,工资+10000,打印更新后的名字和工资
DECLARE
V1 VARCHAR2(20);
V2 NUMBER;
BEGIN
DELETE FROM EMP1 WHERE ENAME='SMITH' RETURNING ENAME,SAL INTO V1,V2;
DBMS_OUTPUT.PUT_LINE(V1||' '||V2);
INSERT INTO EMP1(EMPNO,ENAME,SAL) VALUES(1234,'小明',8000) RETURNING ENAME,SAL INTO V1,V2;
DBMS_OUTPUT.PUT_LINE(V1||' '||V2);
UPDATE EMP1 SET ENAME=LOWER(ENAME),SAL=SAL+10000 WHERE ENAME='KING' RETURNING ENAME,SAL INTO V1,V2;
DBMS_OUTPUT.PUT_LINE(V1||' '||V2);
END;
2.3判断
┌IF 判断
└CASE WHEN 判断
⑴IF判断语法(与case when 一样，满足前面的条件之后就不会再后面的条件中进行筛选，所以要将大的范围写在前面)：
IF 条件1 THEN 要执行的语句1;
ELSIF 条件2 THEN 要执行的语句2;
ELSIF 条件3 THEN 要执行的语句3;
...
[ELSE 要执行的语句N;]
END IF;
--从键盘输入一个数,如果大于0,打印正数,小于0,打印负数,等于0,打印零
DECLARE
V1 NUMBER:=&请输入一个数字;
BEGIN
IF V1<0 THEN
DBMS_OUTPUT.PUT_LINE('负数');
ELSIF V1>0 THEN
DBMS_OUTPUT.PUT_LINE('正数');
ELSE DBMS_OUTPUT.PUT_LINE('零');
END IF;
END;
--从键盘输入一个员工编号,如果他的工资
--大于2999,打印 XX是高薪人士
--大于1999,打印 XX是中薪人士
--大于999,打印 XX是低薪人士
--否则打印 我不认识XX
DECLARE
V1 VARCHAR2(20);
V2 NUMBER;
BEGIN
SELECT ENAME,SAL INTO V1,V2 FROM EMP WHERE EMPNO=&请输入一个员工编号;
IF V2>2999 THEN
DBMS_OUTPUT.PUT_LINE(V1||'是高薪人士');
ELSIF V2>1999 THEN
DBMS_OUTPUT.PUT_LINE(V1||'是中薪人士');
ELSIF V2>999 THEN
DBMS_OUTPUT.PUT_LINE(V1||'是低薪人士');
ELSE DBMS_OUTPUT.PUT_LINE('我不认识'||V1);
END IF;
END;
⑵CASE WHEN判断
语法：
CASE WHEN 条件1 THEN 要执行的语句1;
WHEN 条件2 THEN 要执行的语句2;
...
[ELSE 要执行的语句N;]
END CASE;
--从键盘输入一个字符,如果是大写字母,打印大写字母,如果是小写字母,打印小写字母,否则打印我不认识该字符
DECLARE
V1 CHAR:='&请输入一个字符';
BEGIN
CASE WHEN REGEXP_LIKE(V1,'[A-Z]') THEN
DBMS_OUTPUT.PUT_LINE('大写字母');
WHEN REGEXP_LIKE(V1,'[a-z]') THEN
DBMS_OUTPUT.PUT_LINE('小写字母');
ELSE DBMS_OUTPUT.PUT_LINE('我不认识该字符');
END CASE;
END;
/*从键盘上输入一个员工编号,
如果他的名字的首字母是A,B,C,D,E之中一个,打印1
如果他的名字的首字母是F,G,H,I,J之中一个,打印2
如果他的名字的首字母是K,L,M,N,O中的一个,打印3
如果他的名字的首字母是P,Q,R,S,T中的一个,打印4
否则打印5*/
DECLARE
V1 CHAR;
BEGIN
SELECT SUBSTR(ENAME,1,1) INTO V1 FROM EMP WHERE EMPNO=&请输入一个员工编号;
CASE WHEN REGEXP_LIKE(V1,'[A-E]') THEN
DBMS_OUTPUT.PUT_LINE(1);
WHEN REGEXP_LIKE(V1,'[F-J]') THEN
DBMS_OUTPUT.PUT_LINE(2);
WHEN REGEXP_LIKE(V1,'[K-O]') THEN
DBMS_OUTPUT.PUT_LINE(3);
WHEN REGEXP_LIKE(V1,'[P-T]') THEN
DBMS_OUTPUT.PUT_LINE(4);
ELSE DBMS_OUTPUT.PUT_LINE(5);
END CASE;
END;
或
DECLARE
V1 CHAR;
BEGIN
SELECT SUBSTR(ENAME,1,1) INTO V1 FROM EMP WHERE EMPNO=&请输入一个员工编号;
CASE WHEN V1 BETWEEN 'A' AND 'E' THEN --比较ASCII码
DBMS_OUTPUT.PUT_LINE(1);
WHEN V1 BETWEEN 'A' AND 'E' THEN
DBMS_OUTPUT.PUT_LINE(2);
WHEN V1 BETWEEN 'K' AND 'O' THEN
DBMS_OUTPUT.PUT_LINE(3);
WHEN V1 BETWEEN 'P' AND 'T' THEN
DBMS_OUTPUT.PUT_LINE(4);
ELSE DBMS_OUTPUT.PUT_LINE(5);
END CASE;
END;
小结：
3． 循环 游标
3.1 循环
┌LOOP 循环
├WHILE 循环
└FOR 循环
3.1.1 LOOP 循环(普通循环)
语法:
LOOP
要执行的语句;
EXIT WHEN 退出条件; --不写会死循环
END LOOP;
--循环打印1-10
DECLARE
V1 INT:=0;
BEGIN
LOOP
V1:=V1+1; --自增
DBMS_OUTPUT.PUT_LINE(V1);
EXIT WHEN V1=10;
END LOOP;
END;
如果要得到正确的循环结果,需要注意:
1.初始值
2.退出条件
3.语句的顺序
--循环打印1-10的和
DECLARE
V1 INT:=0;
V2 INT:=0;
BEGIN
LOOP
V1:=V1+1;
V2:=V1+V2;
DBMS_OUTPUT.PUT_LINE(V2);
EXIT WHEN V1=10;
END LOOP;
END;
--循环打印1+2+3+4+...+10的和
DECLARE
V1 INT:=0;
V2 INT:=0;
BEGIN
LOOP
V1:=V1+1;
V2:=V1+V2;
EXIT WHEN V1=10;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V2);
END;
--不换行打印1-10
DECLARE
V1 INT:=0;
BEGIN
LOOP
V1:=V1+1;
DBMS_OUTPUT.PUT(V1||' ');
EXIT WHEN V1=10;
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END;
--用循环的方式打印 1+2+3+4+5+6+7+8+9+10=55 这个式子
DECLARE
V1 INT:=0;
V2 INT:=0;
BEGIN
LOOP
V1:=V1+1;
V2:=V1+V2;
IF V1<10 THEN
DBMS_OUTPUT.PUT(V1||'+');
ELSE DBMS_OUTPUT.PUT(V1||'=');
END IF;
EXIT WHEN V1=10; --此时 10= 已经打印完成了
END LOOP; --关闭循环
DBMS_OUTPUT.PUT_LINE(V2); --打印最后的求和值
END;
或
DECLARE
V1 INT:=0;
V2 INT:=0;
BEGIN
LOOP
V1:=V1+1;
V2:=V1+V2;
EXIT WHEN V1=10;
DBMS_OUTPUT.PUT(V1||'+');
END LOOP;
DBMS_OUTPUT.PUT_LINE(V1||'='||V2);
END;
--循环不换行打印A-Z
DECLARE
V1 INT:=ASCII('A');
BEGIN
LOOP
DBMS_OUTPUT.PUT(CHR(V1)||' ');
EXIT WHEN V1=ASCII('Z');
V1:=V1+1;
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END;
--循环打印A-Z,5个一换行
DECLARE
V1 INT:=ASCII('A');
V2 INT:=0;
BEGIN
LOOP
DBMS_OUTPUT.PUT(CHR(V1)||' ');
V2:=V2+1; --计数
IF MOD(V2,5)=0 THEN
DBMS_OUTPUT.NEW_LINE;
END IF;
EXIT WHEN V1=ASCII('Z');
V1:=V1+1;
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END;
--循环打印1-100,10个一换行
DECLARE
V1 INT:=0;
BEGIN
LOOP
V1:=V1+1;
DBMS_OUTPUT.PUT(V1||' ');
IF MOD(V1,10)=0 THEN --加判断
DBMS_OUTPUT.NEW_LINE;
END IF;
EXIT WHEN V1=100;
END LOOP;
END;
--循环打印100-1,10个一换行
DECLARE
V1 INT:=100;
--V2 INT:=0;
BEGIN
LOOP
DBMS_OUTPUT.PUT(V1||' ');
V1:=V1-1;
--V2:=V2+1;
IF MOD(V1,10)=0 THEN
DBMS_OUTPUT.NEW_LINE;
END IF;
EXIT WHEN V1=0;
END LOOP;
END;
3.1.2 WHILE 循环(有条件的循环)--可以加条件
语法:
WHILE 继续循环的条件 LOOP
要执行的语句;
[EXIT WHEN 退出条件;]
END LOOP;
--循环打印1-10
DECLARE
V1 INT:=0;
BEGIN
LOOP
V1:=V1+1;
DBMS_OUTPUT.PUT_LINE(V1);
EXIT WHEN V1=10;
END LOOP;
END;
DECLARE
V1 INT:=1;
BEGIN
WHILE V1<=10 LOOP
DBMS_OUTPUT.PUT_LINE(V1);
V1:=V1+1;
END LOOP;
END;
3.1.3 FOR 循环
语法：
FOR 变量名 IN [REVERSE] 小值..大值 LOOP --加上REVERSE为从大值到小值
要执行的语句;
[EXIT WHEN 退出条件;]
END LOOP;
--循环打印1-10
BEGIN
FOR I IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(I);
END LOOP;
END;
--循环打印10-1
BEGIN
FOR I IN REVERSE 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(I);
END LOOP;
END;
--循环打印A-Z
BEGIN
FOR I IN ASCII('A')..ASCII('Z') LOOP
DBMS_OUTPUT.PUT_LINE(CHR(I));
END LOOP;
END;
--循环打印1-100,10个一换行
BEGIN
FOR I IN 1..100 LOOP
IF MOD(I,10)=0 THEN
DBMS_OUTPUT.PUT_LINE(I);
ELSE DBMS_OUTPUT.PUT(I||' ');
END IF;
END LOOP;
END;
--打印如下图形
* * * * *
* * * * *
* * * * *
* * * * *
* * * * *
BEGIN
FOR I IN 1..5 LOOP
FOR J IN 1..5 LOOP
DBMS_OUTPUT.PUT('* ');
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END LOOP;
END;
--打印如下图形
*
* *
* * *
* * * *
* * * * *
BEGIN
FOR I IN 1..5 LOOP
FOR J IN 1..I LOOP
DBMS_OUTPUT.PUT('* ');
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END LOOP;
END;
--打印如下图形
*
* *
* * *
* * * *
* * * * *
BEGIN
FOR I IN 1..5 LOOP
FOR K IN 1..5-I LOOP
DBMS_OUTPUT.PUT(' ');--打印出需要的空格
END LOOP;
FOR J IN 1..I LOOP
DBMS_OUTPUT.PUT('* ');--打印出需要的*
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END LOOP;
END;
--打印如下图形
* * * * *
* * * *
* * *
* *
*
BEGIN
FOR I IN REVERSE 1..5 LOOP
FOR K IN 1..5-I LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;
FOR J IN 1..I LOOP
DBMS_OUTPUT.PUT('* ');
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END LOOP;
END;
--打印如下图形
*
* *
* * *
* * * *
* * * * *
* * * *
* * *
* *
*
BEGIN
FOR I IN 1..5 LOOP
FOR J IN 1..5-I LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;
FOR J IN 1..I LOOP
DBMS_OUTPUT.PUT('* ');
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END LOOP;
FOR I IN REVERSE 1..4 LOOP
FOR J IN 1..5-I LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;
FOR J IN 1..I LOOP
DBMS_OUTPUT.PUT('* ');
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END LOOP;
END;
--打印如下图形
* * *
* * * *
* * * * *
BEGIN
FOR I IN 3..5 LOOP
FOR J IN 1..5-I LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;
FOR J IN 1..I LOOP
DBMS_OUTPUT.PUT('* ');
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END LOOP;
END;
BEGIN
FOR I IN 1..3 LOOP
FOR J IN 1..3-I LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;
FOR J IN 1..I+2 LOOP
DBMS_OUTPUT.PUT('* ');
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END LOOP;
END;
--打印如下图形
* * * * *
* * * * *
* * * * *
* * * * *
* * * * *
BEGIN
FOR I IN 1..5 LOOP
FOR J IN 1..5-I LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('* * * * * ');
END LOOP;
END;
--打印九九乘法表
BEGIN
FOR I IN 1..9 LOOP
FOR J IN 1..I LOOP
DBMS_OUTPUT.PUT(J||'×'||I||'='||J*I||' ');
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END LOOP;
END;
3.2 游标 CURSOR
游标是一种能从多条记录的结果集中每次提取一条记录的机制
游标可以充当指针,能遍历结果集中的所有行,但是一次只能指向一行
游标提供了在逐行的基础上操作表中数据的方法,游标的结果集由SELECT语句决定
--游标的分类
┌静态游标┌显式游标┌带参数的 ---静态游标在使用之前他们的定义已经完成不能再进行更改(#在BEGIN之前的声明部分就已经进行了定义 )
│ │ └普通的
│ └隐式游标
└动态游标┌强类型：在声明变量时使用return关键字定义游标的返回类型 ---动态游标游标在声明时没有设定，在打开时可以对其进行修改,每次打开都以无数据开始重新设置结果集，嵌套时上一层的游标对下一层的没有影响；
├弱类型：在声明变量时不使用return关键字定义游标的返回类型
└动态游标
静态游标和动态游标的区别:
静态游标的结果集是固定的,中途不能改变
动态游标的结果集是不固定的,每次打开都能重新定义结果集
显式游标和隐式游标的区别:
显式游标的结果集在DECLARE部分声明,中途不能改变
隐式游标不用声明不用打开不用关闭,这些都是ORACLE系统自动完成的
3.2.1 静态游标-显式游标-普通的显式游标
语法:
DECLARE
CURSOR 游标名 IS SELECT 语句; --声明一个游标,并定义它的结果集
...
BEGIN
OPEN 游标名; --打开游标
FETCH 游标名 INTO 变量; --把游标指针内的值赋给变量,每次FETCH游标的指针都会向下走一行
要执行的语句;
...
CLOSE 游标名;
END;
--打印DEPT表的数据
DECLARE
CURSOR CUR_1 IS SELECT * FROM DEPT;
V1 DEPT%ROWTYPE;
BEGIN
OPEN CUR_1;
FETCH CUR_1 INTO V1;
DBMS_OUTPUT.PUT_LINE(V1.DEPTNO||' '||V1.DNAME||' '||V1.LOC);
FETCH CUR_1 INTO V1;
DBMS_OUTPUT.PUT_LINE(V1.DEPTNO||' '||V1.DNAME||' '||V1.LOC);
FETCH CUR_1 INTO V1;
DBMS_OUTPUT.PUT_LINE(V1.DEPTNO||' '||V1.DNAME||' '||V1.LOC);
FETCH CUR_1 INTO V1;
DBMS_OUTPUT.PUT_LINE(V1.DEPTNO||' '||V1.DNAME||' '||V1.LOC);
FETCH CUR_1 INTO V1; --指针内以及没有值了,所以没有赋值,变量还是之前的值
DBMS_OUTPUT.PUT_LINE(V1.DEPTNO||' '||V1.DNAME||' '||V1.LOC);
CLOSE CUR_1;
END;
--游标的四个属性
游标名%ISOPEN 判断该游标是否已经打开,只返回是/否 --布尔型
游标名%FOUND 判断该游标的指针内是否还有值,只返回是/否 --布尔型
游标名%NOTFOUND 判断该游标的指针内是否没值,只返回是/否 --布尔型
游标名%ROWCOUNT 返回该游标的指针指过的行数,返回数值
3.2.2 静态游标-隐式游标
在PL/SQL块中执行SELECT、INSERT、DELETE和UPDATE语句时，ORACLE会在内存中为其分配上下文区（Context Area），即缓冲区。游标是指向该区的一个指针，或是命名一个工作区（Work Area），或是一种结构化数据类型。它供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方式。
因为不用打开/不用声明/不用赋值/不用关闭,所以使用时只能用隐式游标的四个属性
常用于 DELETE/INSERT/UPDATE
隐式游标的四个属性
SQL%ISOPEN 判断游标是否打开,永远返回否
SQL%FOUND 判断游标的指针内是否有值,如果最近的一次未提交的DML有结果,返回是,否则返回否
SQL%NOTFOUND 判断游标的指针内是否没值,如果最近的一次未提交的DML没结果,返回是,否则返回否
SQL%ROWCOUNT 返回指针指过的行数 --返回最近的一次未提交的DML影响的行数
BEGIN
UPDATE EMP1 SET ENAME=LOWER(ENAME) WHERE DEPTNO=11;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('更新成功,更新了'||SQL%ROWCOUNT||'行');
ELSIF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('更新失败');
END IF;
DELETE FROM emp1 WHERE deptno=20;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('删除成功,删除了'||SQL%ROWCOUNT||'行');
ELSIF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('删除失败');
END IF;
INSERT INTO EMP1 SELECT * FROM EMP WHERE DEPTNO=30;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('插入成功,插入了'||SQL%ROWCOUNT||'行');
ELSIF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('插入失败');
END IF;
END;
3.2.3 动态游标-动态游标
语法：
DECLARE
游标名 SYS_REFCURSOR; --声明一个动态游标；与静态游标的区别为不在最开始声明游标的部分给游标定义结果集
声明变量;
BEGIN
OPEN 游标名 FOR SELECT 语句; --打开游标的同时声明一个结果集
LOOP –-注意：动态游标由于其自身属性原因需要搭配LOOP循环使用
FETCH 游标名 INTO 变量;
EXIT WHEN 游标名%NOTFOUND;
要执行的语句;
END LOOP;
CLOSE 游标名;
OPEN 游标名 FOR 另一个 SELECT 语句; --再次打开游标时可以重新定义结果集
....
END;
--打印DEPT表所有数据,打印EMP表10部门的员工姓名和工资
DECLARE
CUR_1 SYS_REFCURSOR; --声明游标但不定义结果集
V_DEPT DEPT%ROWTYPE;
V_NAME VARCHAR2(20);
V_SAL NUMBER;
BEGIN
OPEN CUR_1 FOR SELECT * FROM DEPT; --打开游标
LOOP --开启循环
FETCH CUR_1 INTO V_DEPT; --指针提取数据
EXIT WHEN CUR_1%NOTFOUND; --确定退出条件
DBMS_OUTPUT.PUT_LINE(V_DEPT.DEPTNO||' '||V_DEPT.DNAME||' '||V_DEPT.LOC);
END LOOP; --关闭循环
CLOSE CUR_1; --关闭游标
DBMS_OUTPUT.PUT_LINE('---------------------------');
OPEN CUR_1 FOR SELECT ENAME,SAL FROM EMP WHERE DEPTNO=10;
LOOP
FETCH CUR_1 INTO V_NAME,V_SAL;
EXIT WHEN CUR_1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V_NAME||' '||V_SAL);
END LOOP;
CLOSE CUR_1;
END;
3.2.4 游标+循环
┌LOOP 循环+游标 --都用的静态游标-显示游标？？？
├WHILE 循环+游标
└FOR 循环+游标
A.LOOP 循环+游标
--打印表的数据
DECLARE
CURSOR CUR_1 IS SELECT * FROM DEPT;
V1 DEPT%ROWTYPE;
BEGIN
OPEN CUR_1;
LOOP
FETCH CUR_1 INTO V1;
EXIT WHEN CUR_1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V1.DEPTNO||' '||V1.DNAME||' '||V1.LOC);
END LOOP;
CLOSE CUR_1;
END;
LOOP循环+%NOTFOUND的步骤:
OPEN 游标 → LOOP → FETCH → EXIT WHEN 游标名%NOTFOUND → 要执行的语句 → END LOOP → CLOSE 游标
B.WHILE 循环+游标
--打印DEPT表的数据
DECLARE
CURSOR CUR_1 IS SELECT * FROM DEPT;
V1 DEPT%ROWTYPE;
BEGIN
OPEN CUR_1;
FETCH CUR_1 INTO V1;
WHILE CUR_1%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(V1.DEPTNO||' '||V1.DNAME||' '||V1.LOC);
FETCH CUR_1 INTO V1;
END LOOP;
CLOSE CUR_1;
END;
WHILE循环+%FOUND的步骤
OPEN 游标 → 先FETCH一次 → WHILE %FOUND LOOP → 要执行的语句 → FETCH → END LOOP → CLOSE 游标
WHILE与LOOP加游标的区别是写循环退出条件的位置不同。
C.FOR 循环+游标
不用打开不用赋值不用关闭
⑴带游标名的
语法
DECLARE
CURSOR 游标名 IS SELECT 语句;
BEGIN
FOR 变量名 IN 游标名 LOOP
要执行的语句;
END LOOP;
--打印DEPT表所有数据
DECLARE
CURSOR CUR_1 IS SELECT * FROM DEPT;
BEGIN
FOR I IN CUR_1 LOOP
DBMS_OUTPUT.PUT_LINE(I.DEPTNO||' '||I.DNAME||' '||I.LOC);
END LOOP;
END;
⑵不带游标名的
因为没有游标名,所以不能使用游标的四个属性,而且不用声明
语法:
FOR 变量名 IN (SELECT 语句) LOOP—省略了从游标中提取数据赋值给变量的部分
要执行的语句;
END LOOP;
--打印DEPT表所有数据
BEGIN
FOR I IN (SELECT * FROM DEPT) LOOP
DBMS_OUTPUT.PUT_LINE(I.DEPTNO||' '||I.DNAME||' '||I.LOC);
END LOOP;
END;
小结：
4. 异常 层层嵌套、子块并列 自定义函数
4.1 异常 EXCEPTION
┌预定义异常 --系统自带的,有名字的
├非预定义异常 --系统自带的,没有名字的
└自定义异常 --用户自定义的
问：为什么要处理异常？
由于PLSQL程序块一旦产生异常而没有指出如何处理该异常,程序会自动终止,而
异常处理就是用来处理这些正常执行中未预料到的情况的
问：什么是PLSQL程序块？
PL/SQL分为三个部分，声明部分、可执行部分和异常处理部分
DECLARE
声明部分;
BEGIN /* 块开始标记 */
执行部分;
EXCEPTION
异常处理部分;
END; /* 块结束标记 */
说明：
（1）声明部分：声明部分是可选的。由关键字DECLARE引出，用于定义常量、变量、游标、异常、复杂数据类型。在编写程序时，程序块中引用的数据对象和程序单元应遵循先定义后使用的原则。
（2）执行部分：执行部分是必需的。由关键字BEGIN开始，至END结束。PL/SQL程序块至少包含一条可执行语句，也可以嵌套其他PL/SQL程序块。
（3）异常处理部分：异常处理部分是可选的。由关键字EXCEPTION开始。当执行部分发生错误时，将会引起异常。这时，正常的执行将被停止且转移到异常程序处理。异常处理完成后，将结束对应PL/SQL块的执行。
4.1.1 预定义异常
违反唯一约束 DUP_VAL_ON_INDEX
没有找到数据 NO_DATA_FOUND
返回多行 TOO_MANY_ROWS
除数为0 ZERO_DIVEDE
其它异常 OTHERS --如果和别的异常一起使用,其他异常要写在最后
--从键盘输入一个员工姓名,打印员工编号,从键盘输入一个部门编号,打印部门所有人的员工编号
DECLARE
V1 INT;
BEGIN
SELECT EMPNO INTO V1 FROM EMP WHERE ENAME='&请输入一个员工姓名';
DBMS_OUTPUT.PUT_LINE(V1);
SELECT EMPNO INTO V1 FROM EMP WHERE DEPTNO=&请输入一个部门编号;
DBMS_OUTPUT.PUT_LINE(V1);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);
END;
错误代码 SQLCODE
错误描述 SQLERRM
4.1.2 非预定义异常
DECLARE
ERR EXCEPTION; --声明一个异常
PRAGMA EXCEPTION_INIT(ERR,-2291); --把-2291这个错误赋给ERR这个异常
BEGIN
INSERT INTO EMP(EMPNO,DEPTNO) VALUES(1234,11);
EXCEPTION
WHEN ERR THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);
END;
4.1.3 自定义异常
--向EMP1插入一条数据,要求员工编号必须是7开头的4位数字
DECLARE
V1 INT:=&请输入一个员工编号;
V2 VARCHAR2(20):='&请输入一个员工姓名';
ERR EXCEPTION; --声明一个异常
BEGIN
IF NOT REGEXP_LIKE(V1,'^7[0-9]{3}$') THEN
RAISE ERR; --抛出异常
END IF;
INSERT INTO EMP1(EMPNO,ENAME) VALUES(V1,V2);
EXCEPTION
WHEN ERR THEN
RAISE_APPLICATION_ERROR(-20000,'员工编号必须是7开头的4位数字'); --自定义弹窗报错
END;
自定义弹窗报错：
RAISE_APPLICATION_ERROR(自定义错误代码,'自定义错误描述')
自定义错误代码范围 -20000 到 -20999
SELECT * FROM EMP1;
--向EMP1表插入一条数据,要求员工编号必须是7开头的4位数字
--员工工资不能低于3000
--工作不能为空
--否则报错 XX不能XXX
DECLARE
ERR1 EXCEPTION;
ERR2 EXCEPTION;
ERR3 EXCEPTION;
V1 INT:=&请输入一个员工编号; --输入数字的宏代换不用加单引号
V2 NUMBER:=&请输入一个工资;
V3 VARCHAR2(20):='&请输入一个职位'; --输入字符串的宏代换必须要加单引号
BEGIN
IF NOT REGEXP_LIKE(V1,'^7[0-9]{3}$') THEN
RAISE ERR1;
END IF;
IF V2<=3000 THEN
RAISE ERR2;
END IF;
IF V3 IS NULL THEN
RAISE ERR3;
END IF;
INSERT INTO EMP1(EMPNO,SAL,JOB) VALUES(V1,V2,V3);
EXCEPTION
WHEN ERR1 THEN
RAISE_APPLICATION_ERROR(-20000,'员工编号必须是7开头的4位数字');
WHEN ERR2 THEN
RAISE_APPLICATION_ERROR(-20001,'员工工资不能低于3000');
WHEN ERR3 THEN
RAISE_APPLICATION_ERROR(-20002,'工作不能为空');
END;
--向EMP1表插入一条数据,要求员工编号必须是7开头的4位数字,如果不符合,改为7000再插入
--员工工资不能低于3000,如果不符合改为3000再插入
--工作不能为空,如果不符合改为小秘书再插入
DECLARE
V1 INT:=&请输入一个员工编号;
V2 NUMBER:=&请输入一个工资;
V3 VARCHAR2(20):='&请输入一个职位';
BEGIN
IF NOT REGEXP_LIKE(V1,'^7[0-9]{3}$') THEN
V1:=7000;
END IF;
IF V2<=3000 THEN
V2:=3000;
END IF;
IF V3 IS NULL THEN
V3:='小秘书';
END IF;
INSERT INTO EMP1(EMPNO,SAL,JOB) VALUES(V1,V2,V3);
END;
4.2 层层嵌套和子块并列
程序的运行分两种情况:
串行:程序的运行依赖于上一个程序的结果,如果上一个程序运行出错,那么下一个程序也会出错
并行:多个程序之间没有关联关系,可能只是为了运行方便而放在一块执行,其中一个出错不影响其余程序
4.2.1 层层嵌套
DECLARE
V1 VARCHAR2(20):='外';
BEGIN
DECLARE
V2 VARCHAR2(20):='中';
BEGIN
DECLARE
V3 VARCHAR2(20):='内';
BEGIN
DBMS_OUTPUT.PUT_LINE(V3||' '||V2||' '||V1);
SELECT ENAME INTO V3 FROM EMP WHERE DEPTNO=10;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('内层异常');
END;
DBMS_OUTPUT.PUT_LINE(V2||' '||V1);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('中层异常');
END;
DBMS_OUTPUT.PUT_LINE(V1);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('外层异常');
END;
--内层可以使用外层的变量/异常,外层不能使用内层的变量/异常
--处理异常时,由内向外抛出异常,本层的异常处理部分不能匹配该异常时,会去更外层寻找
--寻找到可以处理该异常的异常处理部分时,会从报错位置直接跳到该异常处理部分,中间所有内容不再执行
4.2.2 子块并列
DECLARE
V1 VARCHAR2(20):='外';
BEGIN
DECLARE
V2 VARCHAR2(20):='内1';
BEGIN
DBMS_OUTPUT.PUT_LINE(V2||' '||V1);
V1:='外1';
END;
DECLARE
V3 VARCHAR2(20):='内2';
BEGIN
DBMS_OUTPUT.PUT_LINE(V3||' '||V1);
END;
END;
内层可以用外层的变量/异常，子块之间不能使用对方的变量/异常
5. 自定义函数
语法:
CREATE [OR REPLACE] FUNCTION 函数名[(参1 [IN/OUT/IN OUT] 类型,参2 [IN/OUT/IN OUT] 类型....)] RETURN 类型 AS/IS -----参数类型的连接符默认是IN
声明部分;
BEGIN
要执行的语句;
RETURN 返回值;
[EXCEPTION
异常处理部分;]
END;
5.1 一般自定义函数
--创建一个自定义函数,效果同SIGN
CREATE OR REPLACE FUNCTION MY_SIGN(V1 NUMBER) RETURN NUMBER IS
BEGIN
IF V1>0 THEN
RETURN 1;
ELSIF V1<0 THEN
RETURN -1;
ELSE RETURN 0;
END IF;
END;
SELECT MY_SIGN(-789),MY_SIGN(890),MY_SIGN(0)
FROM DUAL;
--编译一个自定义函数FUN_1,输入一个数,返回它的相反数
CREATE FUNCTION FUN_1(V1 NUMBER) RETURN NUMBER IS
BEGIN
RETURN -V1;
END;
SELECT FUN_1(-5),FUN_1(9),FUN_1(0)
FROM DUAL;
--编译一个函数,表示π
CREATE FUNCTION π RETURN NUMBER IS
BEGIN
RETURN ACOS(-1);
END;
SELECT π
FROM DUAL;
--查询半径为2的圆的面积
SELECT π*2*2
FROM DUAL;
--查询半径是3的球的体积
SELECT 4/3*π*POWER(3,3)
FROM DUAL;
--编译自定义函数FUN_2,输入三个数,返回最大的数
CREATE FUNCTION FUN_2(V1 NUMBER,V2 NUMBER,V3 NUMBER) RETURN NUMBER IS
BEGIN
IF V1>=V2 AND V1>=V3 THEN
RETURN V1;
ELSIF V2>=V1 AND V2>=V3 THEN
RETURN V2;
ELSE RETURN V3;
END IF;
END;
CREATE FUNCTION FUN_2(V1 NUMBER,V2 NUMBER,V3 NUMBER) RETURN NUMBER IS
VA NUMBER;
BEGIN
IF V1>=V2 THEN
VA:=V1
ELSE VA:=V2;
END IF;
IF VA>=V3 THEN --前面的语句执行完结果进入本行条件，前面已将V1、V2中最大的数选出
RETURN VA;
ELSE RETURN V3;
END IF;
END;
--编译一个自定义函数FUN_3,输入一个员工姓名,返回他的工资
CREATE FUNCTION FUN_3(V_ENAME VARCHAR2) RETURN NUMBER IS
V_SAL NUMBER;
BEGIN
SELECT SAL INTO V_SAL FROM EMP WHERE ENAME=V_ENAME;
RETURN V_SAL;
END;
SELECT FUN_3('KING')
FROM DUAL;
--编译一个自定义函数FUN_4,输入一个员工姓名,返回比他工资高的人数
CREATE FUNCTION FUN_4(V1 VARCHAR2) RETURN INT IS
V2 INT;
BEGIN
SELECT COUNT(1) INTO V2 FROM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME=V1);
RETURN V2;
END;
--编译一个自定义函数FUN_5,输入一个部门一个职位,返回该部门该职位的人数
CREATE TUNCTION FUN_5(V1 INT,V2 VARCHAR2) RETURN INT IS
V3 INT;
BEGIN
SELECT COUNT(1) INTO V3 FROM EMP WHERE DEPTNO=V1 AND JOB=V2;
RETURN V3;
END;
--编译一个自定义函数FUN_6,输入一个部门和一个年份,返回这个部门这个年份入职的人的名字(逗号隔开)
CREATE FUNCTION FUN_7(V1 INT,V2 INT) RETURN VARCHAR2 IS
V3 VARCHAR2(2000);
BEGIN
SELECT WM_CONCAT(ENAME) INTO V3 FROM EMP WHERE DEPTNO=V1 AND TO_CHAR(HIREDATE,'YYYY')=V2;
RETURN V3;
END;
5.2 自定义函数返回动态游标
如果想要返回多行结果，需要用到游标，也就是，通过游标来实现查询，并且把游标作为返回参数，逐行输出，需要注意的是返回的结果并非是表的形式。
编译一个自定义函数FUN_7,输入一个部门编号,打印这个部门的人的名字
CREATE FUNCTION FUN_7(V1 INT) RETURN SYS_REFCURSOR IS
CUR_1 SYS_REFCURSOR;
V2 VARCHAR2(20);
BEGIN
OPEN CUR_1 FOR SELECT ENAME FROM EMP WHERE DEPTNO=V1;--给游标填充结果集
LOOP
FETCH CUR_1 INTO V2;--从游标里按行从上到下提取数据赋值给变量
EXIT WHEN CUR_1%NOTFOUND;--当游标中的本行没有数据时退出
DBMS_OUTPUT.PUT_LINE(V2);
END LOOP;
RETURN CUR_1;
CLOSE CUR_1;
END;
SELECT FUN_7(20)
FROM DUAL;
5.3 有OUT参数的自定义函数
--编译一个自定义函数FUN_8,输入一个员工编号,输出他的工资,返回他的部门编号
CREATE FUNCTION FUN_8(V1 INT,V2 OUT NUMBER) RETURN INT IS
V3 INT;
BEGIN
SELECT SAL,DEPTNO INTO V2,V3 FROM EMP WHERE EMPNO=V1;
RETURN V3;
END;
--PLSQL调用
DECLARE
VA NUMBER;
VB NUMBER;
BEGIN
VB:=FUN_8(7788,VA);
DBMS_OUTPUT.PUT_LINE(VB||' '||VA);
END;
函数小结
1.自定义函数有OUT参数时,一般会改写为存储过程的形式
2.自定义函数一般不包含DML
3.自定义函数不能包含DDL
小结：
⑴自定义函数在括号内写参数的类型时不需要写类型长度
⑵自定义函数可以没有输入值，但是必须要有返回值
6. 存过 PROCEDURE
--存储过程 PROCEDURE
语法:
CREATE [OR REPLACE] PROCEDURE 存过名[(参1 [IN/OUT/IN OUT] 类型,参2 ...)] IS/AS
[声明部分;]
BEGIN
要执行的语句;
[EXCEPTION
异常处理部分;]
END;
6.1 DML/输出查询
--编译一个存过,把EMP表的员工信息插入到EMP1,
--之后更新EMP1的员工姓名为小写,并且删除1981年入职的员工
CREATE PROCEDURE PRO_1 IS
BEGIN
INSERT INTO EMP1 SELECT * FROM EMP;
COMMIT;
UPDATE EMP1 SET ENAME=LOWER(ENAME);
COMMIT;
DELETE FROM EMP1 WHERE TO_CHAR(HIREDATE,'YYYY')=1981;
COMMIT;
END;
--调用
CALL 存过名(); --SQL窗口下调用
EXEC 存过名(); --命令窗口下调用
CALL PRO_1();
SELECT * FROM EMP1;
--相对于SQL,存储过程有什么好处
1.存过只在创建时进行编译,以后每次执行都不需要再次编译,
而一般的SQL语句,每次执行都需要再次编译,所以存过可以提高数据库的执行速度
2.当数据库进行复制的操作时(比如多表连接查询/修改等时),如果是SQL需要多次连接数据库,而存过只需要连接一次,可以减少数据库的连接次数
3.存过可以重复使用,减少开发人员的工作量
--有IN无OUT的
--编译一个存过PRO_2,输入一个员工姓名,打印他的工资
CREATE PROCEDURE PRO_2(V1 VARCHAR2) IS
V2 NUMBER;
BEGIN
SELECT SAL INTO V2 FROM EMP WHERE ENAME=V1;
DBMS_OUTPUT.PUT_LINE(V2);
END;
--调用
CALL PRO_2('KING');
--编译一个存过PRO_3,输入一个员工姓名,如果该员工工资比SCOTT高,打印高,低打印低,一样打印巧了
CREATE OR REPLACE PROCEDURE PRO_3(V1 VARCHAR2) IS
V2 NUMBER;
V3 NUMBER;
BEGIN
SELECT SAL INTO V2 FROM EMP WHERE ENAME=V1;
SELECT SAL INTO V3 FROM EMP WHERE ENAME='SCOTT';
IF V2>V3 THEN
DBMS_OUTPUT.PUT_LINE('高');
ELSIF V2<V3 THEN
DBMS_OUTPUT.PUT_LINE('低');
ELSE DBMS_OUTPUT.PUT_LINE('巧了');
END IF;
END;
--调用
CALL PRO_3('FORD');
6.2 打印图形
--编译一个存过PRO_4,输入一个数字,打印这个数字层数的等腰三角形
CREATE OR REPLACE PROCEDURE PRO_4(V1 INT) IS
BEGIN
FOR I IN 1..V1 LOOP
FOR J IN 1..V1-I LOOP
DBMS_OUTPUT.PUT(' ');--横向打印空格格
END LOOP;
FOR J IN 1..I LOOP
DBMS_OUTPUT.PUT('* ');--横向打印*
END LOOP;
DBMS_OUTPUT.NEW_LINE;--纵向打印上面的打印
END LOOP;
END;
--调用
CALL PRO_4(30);
--编译一个存过PRO_5,输入一个数字,打印这个数字/2层数的等腰梯形
CREATE OR REPLACE PROCEDURE PRO_5(V1 INT) IS
BEGIN
FOR I IN V1/2..V1 LOOP
FOR J IN 1..V1-I LOOP
DBMS_OUTPUT.PUT(' '); --当V1等于6时，第一次大循环中I=3，本行先横着打印3次空格
END LOOP;
FOR J IN 1..I LOOP
DBMS_OUTPUT.PUT('* ');
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END LOOP;
END;
--调用
CALL PRO_5(5);
--编译一个存过PRO_6,输入一个数字,打印这个数字层数的长方形
CREATE OR REPLACE PROCEDURE PRO_6(V1 INT) IS
BEGIN
FOR I IN 1..V1 LOOP
FOR J IN 1..V1/2 LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;
FOR J IN 1..V1/2 LOOP
DBMS_OUTPUT.PUT('* ');
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END LOOP;
END;
--调用
CALL PRO_6(5);
--组合圣诞树
--PLSQL调用
DECLARE
V1 INT:=&请输入一个数字;
BEGIN
PRO_4(V1); --PLSQL调用存过不需要写CALL或EXEC
PRO_5(V1);
PRO_5(V1);
PRO_6(V1);
END;
6.3 存过的异常处理
/*编译一个存过,输入一个数字和一个部门编号,
要求必须是0-9的整数,如果不是则抛出异常报错 '请输入0-9的整数',
输入的部门编号必须是EMP表内有的,如果没有抛出异常,报错 '部门错误',
当该部门的人数小于输入的数时,则将该部门的员工姓名插入到EMP_1,并打印插入了多少行,
当该部门的人数大于输入的数时,则将该部门的员工姓名,工资删除,打印删除了几行,
当该部门的人数等于输入的数时,则将该部门的员工的职位更新为小写,并打印更新了几行*/
CREATE OR REPLACE PROCEDURE PRO_7(V1 INT,V2 INT) IS
V3 INT;
ERR1 EXCEPTION;
ERR2 EXCEPTION;
BEGIN
IF NOT REGEXP_LIKE(V1,'^[0-9]$') THEN
RAISE ERR1;
END IF;
SELECT COUNT(1) INTO V3 FROM EMP WHERE DEPTNO=V2;
IF V3=0 THEN
RAISE ERR2;
END IF;
IF V3<V1 THEN
INSERT INTO EMP_1(ENAME) SELECT ENAME FROM EMP1 WHERE DEPTNO=V2;
DBMS_OUTPUT.PUT_LINE('插入了'||SQL%ROWCOUNT||'行');
COMMIT;
ELSIF V3>V1 THEN
UPDATE EMP1 SET ENAME=NULL,SAL=NULL WHERE DEPTNO=V2;
DBMS_OUTPUT.PUT_LINE('删除了'||SQL%ROWCOUNT||'行');
COMMIT;
ELSIF V3=V1 THEN
UPDATE EMP1 SET JOB=LOWER(JOB) WHERE DEPTNO=V2;
DBMS_OUTPUT.PUT_LINE('更新了'||SQL%ROWCOUNT||'行');
COMMIT;
END IF;
EXCEPTION
WHEN ERR1 THEN
RAISE_APPLICATION_ERROR(-20000,'请输入0-9的整数');
WHEN ERR2 THEN
RAISE_APPLICATION_ERROR(-20001,'部门错误');
END;
6.4 存过里的OUT参数
存过里有OUT参数时调用需要使用PL/SQL，有INOUT使用PL/SQL时，需要先在声明部分给变量IN值，然后在执行部分直接使用。
--有OUT的
--编写一存过PRO_8,输入一个员工编号,输出部门编号和工资
CREATE OR REPLACE PROCEDURE PRO_8(V1 INT,V2 OUT INT,V3 OUT NUMBER) IS
BEGIN
SELECT DEPTNO,SAL INTO V2,V3 FROM EMP WHERE EMPNO=V1;
END;
--纯OUTPLSQL调用
DECLARE
VA INT;
VB NUMBER;
BEGIN
PRO_8(7788,VA,VB);
DBMS_OUTPUT.PUT_LINE(VA||' '||VB);
END;
--编译一个存过PRO_9,输入一个员工姓名,输出他的员工编号,工资,部门名称
CREATE OR REPLACE PROCEDURE PRO_9(V1 VARCHAR2,V2 OUT INT,V3 OUT NUMBER,V4 OUT VARCHAR2) IS
BEGIN
SELECT EMPNO,SAL,DNAME INTO V2,V3,V4 FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO WHERE ENAME=V1;
END;
--调用
DECLARE
V1 INT;
V2 NUMBER;
V3 VARCHAR2(20);
BEGIN
PRO_9('KING',V1,V2,V3);
DBMS_OUTPUT.PUT_LINE(V1||' '||V2||' '||V3);
END;
--INOUT调用
--编译一个存过PRO_10,输入一个姓名,输出他的职位
CREATE OR REPLACE PROCEDURE PRO_10(V1 IN OUT VARCHAR2) IS
BEGIN
SELECT JOB INTO V1 FROM EMP WHERE ENAME=V1;
END;
--调用
DECLARE
V1 VARCHAR2(20):='KING';
BEGIN
PRO_10(V1);
DBMS_OUTPUT.PUT_LINE(V1);
END;
--编译一个存过PRO_11,输入一个员工编号,输出他的工资
CREATE OR REPLACE PROCEDURE PRO_11(V1 IN OUT NUMBER) IS
BEGIN
SELECT SAL INTO V1 FROM EMP WHERE EMPNO=V1;
DBMS_OUTPUT.PUT_LINE(V1);
END;
--调用
DECLARE
V1 NUMBER:=7788;
BEGIN
PRO_11(V1);
END;
存储过程和自定义函数的区别：
1.函数有RETURN,存过没有
2.存过内可以使用函数,函数内不能使用存过
3.存过一般是用来实现某种功能/操作/业务,函数一般是用来实现某些运算等
4.DML一般用存过
5.DDL只能用存过
6.有OUT的一般用存过
--编写一个存过PRO_12,打印100以内包含7的数或7的倍数
CREATE OR REPLACE PROCEDURE PRO_12 IS
BEGIN
FOR I IN 1..100 LOOP
IF MOD(I,7)=0 OR REGEXP_LIKE(I,'7') THEN
DBMS_OUTPUT.PUT_LINE(I);
END IF;
END LOOP;
END;
--调用
CALL PRO_12();
7. 数字字典 动态SQL操作 随机函数 闪回 触发器
7.1 数据字典(视图)
┌静态┌DBA_XXX 存储了数据库内所有的XXX --必须有DBA权限
│ ├ALL_XXX 存储了当前用户有权限操作的所有的XXX
│ └USER_XXX 存储了当前用户所有的XXX
└动态 V$XXX
USER_TABLES 当前用户所有的表
USER_VIEWS 当前用户所有的视图
USER_CONSTRAINTS 当前用户所有的约束
USER_SEQUENCES 当前用户所有的序列
USER_INDEXES 当前用户所有索引
USER_PROCUDRES 当前用户所有的存过和函数
USER_SOURCE 当前用户所有的有名块
USER_RECYCLEBIN 回收站
V$SESSION 查看当前的进程等信息
SELECT * FROM V$SESSION;
USER_TAB_COLS 当前用户所有的表和所有的列
USER_TAB_COLUMNS 当前用户所有的表和所有的列
USER_TAB_COMMENTS 表注释
USER_COL_COMMENTS 列注释
SELECT * FROM USER_TAB_COMMENTS;
SELECT * FROM USER_COL_COMMENTS;
--增加注释
COMMENT ON TABLE 表名 IS '注释'; --给表添加注释
COMMENT ON TABLE EMP1 IS '员工表1';
COMMENT ON COLUMN 表名.列名 IS '注释'; --给列添加注释
COMMENT ON COLUMN EMP1.EMPNO IS '员工编号1';
--通过数据字典批量生成语句(小批量)
SELECT 'DROP TABLE '||TABLE_NAME||';'
FROM USER_TABLES
WHERE REGEXP_LIKE(TABLE_NAME,'EMP.')
DROP TABLE 表名;
DROP TABLE EMP_1000;
DROP TABLE EMP_2000;
DROP TABLE EMP_3000;
DROP TABLE EMPA;
DROP TABLE EMPJ;
DROP TABLE EMPS;
DROP TABLE EMP_CF_1;
DROP TABLE EMP2;
DROP TABLE EMP3;
DROP TABLE EMP1;
DROP TABLE EMP_CF;
DROP TABLE EMP_NULL;
--给所有EMP开头的(不包括EMP表),增加一个列 REMARK VARCHAR2(20),并且设置默认值为A,并且非空
SELECT 'ALTER TABLE '||TABLE_NAME||' ADD REMARK VARCHAR2(20) DEFAULT ''A'' NOT NULL;'
FROM USER_TABLES
WHERE REGEXP_LIKE(TABLE_NAME,'EMP.')
ALTER TABLE EMP1 ADD REMARK VARCHAR2(20) DEFAULT 'A' NOT NULL;
SELECT * FROM EMP1;
7.2 动态SQL
┌AUTHID CURRENT_USER 识别用户权限 –动态SQL中有DDL时必须在IS前加上此句
└EXECUTE IMMEDIATE 立即执行
把SQL语句赋值给变量，通过执行变量来执行SQL语句，可以快捷处理批量DML操作。
7.2.1 批量DML
--向EMP1,EMP2,EMP3插入一条数据
CREATE OR REPLACE PROCEDURE PRO_1 IS
V1 VARCHAR2(4000);
BEGIN
FOR I IN 1..3 LOOP
V1:='INSERT INTO EMP'||I||'(EMPNO,ENAME) VALUES(1234,''小明'')';
EXECUTE IMMEDIATE V1;
COMMIT;
END LOOP;
END;
--调用
CALL PRO_1();
CREATE TABLE EMP1 AS SELECT * FROM EMP;
CREATE TABLE EMP2 AS SELECT * FROM EMP;
CREATE TABLE EMP3 AS SELECT * FROM EMP;
SELECT * FROM EMP1;
SELECT * FROM EMP2;
SELECT * FROM EMP3;
--批量DML
--向EMP开头的表(不包括EMP表)插入一条数据
CREATE OR REPLACE PROCEDURE PRO_1 IS
V1 VARCHAR2(4000);
BEGIN
FOR I IN (SELECT TABLE_NAME
FROM USER_TABLES
WHERE REGEXP_LIKE(TABLE_NAME,'EMP.')) LOOP --FOR循环+游标
V1:='INSERT INTO '||I.TABLE_NAME||'(EMPNO,ENAME) VALUES(1234,''小明'')';
EXECUTE IMMEDIATE V1;
COMMIT;
END LOOP;
END;
--调用
CALL PRO_1();
SELECT * FROM EMPJ;
7.2.2 DDL
--创建一个存过PRO_CREATE,
--创建EMP_1,数据同EMP
CREATE OR REPLACE PROCEDURE PRO_CREATE AUTHID CURRENT_USER IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE EMP_1 AS SELECT * FROM EMP';
END;
--调用
CALL PRO_CREATE();
SELECT * FROM EMP_1;
--创建一个存过PRO_CREATE,
--批量创建EMP1-EMP20
CREATE OR REPLACE PROCEDURE PRO_CREATE AUTHID CURRENT_USER IS
BEGIN
FOR I IN 1..20 LOOP
EXECUTE IMMEDIATE 'CREATE TABLE EMP'||I||' AS SELECT * FROM EMP'; --注意AS前面的空格
END LOOP;
END;
--调用
CALL PRO_CREATE();
SELECT * FROM EMP3;
--编辑一个存过PRO_DROP,删除所有EMP开头的表(不包括EMP)
CREATE OR REPLACE PROCEDURE PRO_DROP AUTHID CURRENT_USER IS
BEGIN
FOR I IN (SELECT TABLE_NAME
FROM USER_TABLES
WHERE REGEXP_LIKE(TABLE_NAME,'EMP.')) LOOP --FOR循环+游标
EXECUTE IMMEDIATE 'DROP TABLE '||I.TABLE_NAME;
END LOOP;
END;
--调用
CALL PRO_DROP();
7.2.3 动态SQL的子句
┌INTO 子句 --从动态SQL中提取值
└USING 子句 --向动态SQL中传入值
--输入一个员工编号和员工姓名,用USING子句向EMP1-EMP20插入这条数据
CREATE OR REPLACE PROCEDURE PRO_1(V1 INT,V2 VARCHAR2) AUTHID CURRENT_USER IS
V_SQL VARCHAR2(4000);
BEGIN
FOR I IN 1..20 LOOP
V_SQL:='INSERT INTO EMP'||I||'(EMPNO,ENAME) VALUES(:1,:2)';
EXECUTE IMMEDIATE V_SQL USING V1,V2;
COMMIT;
END LOOP;
END;
--调用
CALL PRO_1(321,'小绿');
SELECT * FROM EMP9;
--创建一个存过,将EMP1-EMP20表,双数表内的员工工资减半
CREATE OR REPLACE PROCEDURE PRO_1 IS
BEGIN
FOR I IN 1..20 LOOP
IF MOD(I,2)=0 THEN
EXECUTE IMMEDIATE 'UPDATE EMP'||I||' SET SAL=SAL/2';
END IF;
COMMIT;
END LOOP;
END;
--调用
CALL PRO_1();
--INTO子句和USING子句一起使用时,先写INTO再写USING
--创建存过,输入一个员工编号,打印EMP1-EMP20的该员工的工资
CREATE OR REPLACE PROCEDURE PRO_1(V1 NUMBER) IS
V2 NUMBER;
V_SQL VARCHAR2(4000);
BEGIN
FOR I IN 1..20 LOOP
V_SQL:='SELECT SAL FROM EMP'||I||' WHERE EMPNO=:1';
EXECUTE IMMEDIATE V_SQL INTO V2 USING V1; --实际上是分两段各自执行
DBMS_OUTPUT.PUT_LINE(V2);
END LOOP;
END;
--调用
CALL PRO_1(7788);
7.3 随机函数 RANDOM
DBMS_RANDOM.VALUE(数1,数2) 生成数1-数2之间的随机数字,默认包含小数
DBMS_RANDOM.STRING('格式',数) 按照格式生成数位长的随机字符
格式有:
U 大写字母
L 小写字母
A 字母
X 大写字母和数字
P 所有可打印字符
SELECT DBMS_RANDOM.VALUE(0,100),
DBMS_RANDOM.VALUE(0,100),
DBMS_RANDOM.VALUE(0,100),
DBMS_RANDOM.VALUE(0,100),
DBMS_RANDOM.VALUE(0,100)
FROM DUAL;
SELECT DBMS_RANDOM.STRING('U',18),
DBMS_RANDOM.STRING('L',18),
DBMS_RANDOM.STRING('A',18),
DBMS_RANDOM.STRING('X',18),
DBMS_RANDOM.STRING('P',18)
FROM DUAL;
--编写一个函数,生一个1-100的随机数,
--如果这个数大于85,返回一等奖
--如果这个数大于70,返回二等奖
--如果这个数大于50,返回三等奖
--否则返回谢谢惠顾
CREATE OR REPLACE FUNCTION FUN_1 RETURN VARCHAR2 IS
V1 NUMBER:=DBMS_RANDOM.VALUE(0,100);
BEGIN
IF V1>85 THEN
RETURN '一等奖';
ELSIF V1>70 THEN
RETURN '二等奖';
ELSIF V1>50 THEN
RETURN '三等奖';
ELSE RETURN '谢谢惠顾';
END IF;
END;
SELECT FUN_1,FUN_1,FUN_1,FUN_1,FUN_1,FUN_1,FUN_1,FUN_1,FUN_1,FUN_1,FUN_1
FROM DUAL;
CREATE OR REPLACE PROCEDURE PRO_1(VA INT) AUTHID CURRENT_USER IS
V1 NUMBER:=DBMS_RANDOM.VALUE(0,100);
V2 INT;
V3 INT;
V4 INT;
BEGIN
V2:=TRUNC(VA/10)+VA;
SELECT COUNT(1) INTO V4 FROM USER_SEQUENCES WHERE SEQUENCE_NAME='S_8';
IF V4=0 THEN
EXECUTE IMMEDIATE 'CREATE SEQUENCE S_8 MAXVALUE 200 CYCLE';
END IF;
FOR I IN 1..V2 LOOP
IF V1>98 THEN
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('一等奖 ');
DBMS_OUTPUT.NEW_LINE;
ELSIF V1>88 THEN
DBMS_OUTPUT.PUT('二等奖 ');
EXECUTE IMMEDIATE 'SELECT S_8.NEXTVAL FROM DUAL' INTO V3;
ELSIF V1>70 THEN
DBMS_OUTPUT.PUT('三等奖 ');
EXECUTE IMMEDIATE 'SELECT S_8.NEXTVAL FROM DUAL' INTO V3;
ELSE DBMS_OUTPUT.PUT('谢谢惠顾 ');
EXECUTE IMMEDIATE 'SELECT S_8.NEXTVAL FROM DUAL' INTO V3;
END IF;
V1:=DBMS_RANDOM.VALUE(0,100);
IF V3=200 THEN
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('触发保底 ');
DBMS_OUTPUT.NEW_LINE;
--EXECUTE IMMEDIATE 'DROP SEQUENCE S_8';
END IF;
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END;
--调用
CALL PRO_1(10);
7.4 闪回
┌闪回回收站中的表
├闪回表内数据到某个时间点的状态(中间不能更改表定义)
└闪回查询
⑴查看回收站：
SELECT * FROM USER_RECYCLEBIN;
⑵闪回回收站中的表：
FLASHBACK TABLE 表名 TO BEFORE DROP [RENAME TO 新表名];
⑶闪回表内数据到某个时间点的状态(中间不能更改表定义即不能改变表的结构)：
FLASHBACK TABLE 表名 TO TIMESTAMP 时间戳;
前提是该表开启了行迁移(默认是未开启的)：
ALTER TABLE 表名 ENABLE/DISABLE ROW MOVEMENT;
ALTER TABLE EMP1 ENABLE ROW MOVEMENT;
SELECT * FROM EMP1;
FLASHBACK TABLE EMP1 TO TIMESTAMP TIMESTAMP'2023-5-12 15:00:00.000';
DATE'年-月-日'
TIMESTAMP'年-月-日 时:分:秒.000'
TRUNCATE TABLE EMP1;
--闪回的时间戳到当前时间该表不能更改过表定义(即不能执行过DDL),否则会报错
--闪回查询(查询某个时间戳下该表的数据),中间不能更改表定义
SELECT 列
FROM 表名 AS OF TIMESTAMP 时间戳
DELETE FROM EMP2;
COMMIT;
SELECT *
FROM EMP2 AS OF TIMESTAMP TIMESTAMP'2023-5-12 15:10:00.000'
7.5 触发器 TRIGER
DML触发器
语法:
CREATE OR REPLACE TRIGGER 触发器名 BEFORE/AFTER --事前触发器/事后触发器
DML操作 ON 表
[FOR EACH ROW] --写上为行级触发器,不写为语句级触发器
[DECLARE --触发器要声明变量等必须要写DECLARE
声明部分;]
BEGIN
要执行的语句;
END;
行级触发器:DML操作影响该表多少行,就执行BEGIN部分的语句多少次
语句级触发器:DML操作不论影响该表多少行,只执行BEGIN的语句一次
7.5.1 一般结构触发器
--创建空表EMP1,格式同EMP
CREATE TABLE EMP1 AS SELECT * FROM EMP WHERE 1=2;
--对EMP1创建触发器,当它被插入数据时,向EMP2的EMPNO插入一个1
CREATE OR REPLACE TRIGGER TRI_1 BEFORE
INSERT ON EMP1
BEGIN
INSERT INTO EMP2(EMPNO) VALUES(1);
--COMMIT; 触发器内不能写COMMIT
END;
INSERT INTO EMP1 SELECT * FROM EMP WHERE DEPTNO=10;
SELECT * FROM EMP1;
SELECT * FROM EMP2;
--对EMP1创建一个触发器,当他被删除数据时,向EMP2的ENAME插入'DELETE',HIREDATE插入删除的时间
CREATE OR REPLACE TRIGGER TRI_1 BEFORE
DELETE ON EMP1
BEGIN
INSERT INTO EMP2(ENAME,HIREDATE) VALUES('DELETE',SYSDATE);
END;
DELETE FROM EMP1 WHERE ENAME='KING';
SELECT * FROM EMP1;
SELECT * FROM EMP2;
7.5.2 :NEW.列 :OLD.列
:NEW.列 --修改之后的数据
:OLD.列 --修改之前的数据
INSERT UPDATE DELETE
:OLD.列 NULL 有 有
:NEW.列 有 有 NULL
--对EMP1创建触发器,当EMP1的ENAME被更新时触发,向EMP2的ENAME列插入更新前和更新后的数据,
--向JOB插入'更新前/更新后',向HIREDATE插入更新的时间
CREATE OR REPLACE TRIGGER TRI_1 BEFORE
UPDATE OF ENAME ON EMP1
FOR EACH ROW
BEGIN
INSERT INTO EMP2(ENAME,JOB,HIREDATE) VALUES(:OLD.ENAME,'更新前',SYSDATE);
INSERT INTO EMP2(ENAME,JOB,HIREDATE) VALUES(:NEW.ENAME,'更新后',SYSDATE);
END;
UPDATE EMP1 SET ENAME=LOWER(ENAME);
SELECT * FROM EMP1;
SELECT * FROM EMP2;
7.5.3触发器和异常的联动
--对EMP1创建触发器,当它在每天8-22时被插入数据时触发,弹窗报错提示8-22时不能对该表插入数据
CREATE OR REPLACE TRIGGER TRI_1 BEFORE
INSERT ON EMP1
BEGIN
IF TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) BETWEEN 8 AND 22 THEN
RAISE_APPLICATION_ERROR(-20000,'8-22时不能对该表插入数据');
END IF;
END;
INSERT INTO EMP1 SELECT * FROM EMP WHERE DEPTNO=20;
7.5.4 同步DML（需要创建行级触发器）
⑴一表多插(同步插入数据)
--对EMP1创建触发器,当EMP1被插入数据时,同时将数据插入到EMP2
CREATE OR REPLACE TRIGGER TRI_1 BEFORE
INSERT ON EMP1
FOR EACH ROW
BEGIN
INSERT INTO EMP2 VALUES (:NEW.EMPNO,:NEW.ENAME,:NEW.JOB,:NEW.MGR,:NEW.HIREDATE,:NEW.SAL,:NEW.COMM,:NEW.DEPTNO);
END;
INSERT INTO EMP1 SELECT * FROM EMP WHERE DEPTNO=20;
SELECT * FROM EMP1;
SELECT * FROM EMP2;
⑵同步更新
--对EMP1创建触发器,当EMP1的ENAME列更新数据时,同时更新EMP2的ENAME的相应的数据
CREATE OR REPLACE TRIGGER TRI_1 BEFORE
UPDATE OF ENAME ON EMP1
FOR EACH ROW
BEGIN
UPDATE EMP2 SET ENAME=:NEW.ENAME WHERE EMPNO=:OLD.EMPNO;
END;
UPDATE EMP1 SET ENAME=LOWER(ENAME) WHERE ENAME='KING';
SELECT * FROM EMP1;
SELECT * FROM EMP2;
8 程序包 定时任务
8.1 包 PACKAGE
┌包定义 PACKAGE --包头,相当于目录
└包主体 PACKAGE BODY --包体,相当于内容
--包头语法
CREATE [OR REPLACE] PACKAGE 包头名 IS/AS
FUNCTION 函数1(参1 类型...) RETURN 类型;
FUNCTION 函数2(参1 类型...) RETURN 类型;
FUNCTION 函数3(参1 类型...) RETURN 类型;
...
PROCEDURE 存过1(参1 类型...);
PROCEDURE 存过2(参1 类型...);
PROCEDURE 存过3(参1 类型...);
...
TYPE ...----?????? 变量赋值？
CURSOR ...
..
END 包头名;
--包体语法
CREATE [OR REPLACE] PACKAGE BODY 包体名 IS/AS
全局声明部分;
FUNCTION 函数1(参1 类型...) RETURN 类型 IS/AS
本地声明部分;
BEGIN
要执行的语句;
RETURN 返回值;
[EXCEPTION
异常处理;]
END;
FUNCTION 函数2(参1 类型...) RETURN 类型 IS/AS
本地声明部分;
BEGIN
要执行的语句;
RETURN 返回值;
[EXCEPTION
异常处理;]
END;
...
PROCEDURE 存过1(参1 类型...) IS/AS
BEGIN
要执行的语句;
[EXCEPTION
异常处理;]
END;
PROCEDURE 存过2(参1 类型...) IS/AS
BEGIN
要执行的语句;
[EXCEPTION
异常处理;]
END;
...
END 包体名;
--包头和包体要分开执行,先执行创建包头的语句,再执行创建包体的语句
--包头和包体的名字一般是相同的
--包头内写的函数和存过等要和包体内有的函数和存过相同(特别是名字/输入值类型等)
--创建一个包,里面包含FUN1,FUN2,PRO1三个有名块
--FUN1 函数,输入一个员工姓名,返回他的部门名称
SELECT PKG_1.FUN1('KING')
FROM DUAL;
--FUN2 函数,输入一个数字,返回它的相反数
SELECT PKG_1.FUN2(-99)
FROM DUAL;
--PRO1 存过,输入一个日期,打印该日期之前入职的人数
CALL PKG_1.PRO1(DATE'1982-1-1');
--包头
CREATE OR REPLACE PACKAGE PKG_1 IS
FUNCTION FUN1(V1 VARCHAR2) RETURN VARCHAR2;
FUNCTION FUN2(V1 NUMBER) RETURN NUMBER;
PROCEDURE PRO1(V1 DATE);
END PKG_1;
--包体
CREATE OR REPLACE PACKAGE BODY PKG_1 IS
FUNCTION FUN1(V1 VARCHAR2) RETURN VARCHAR2 IS
V2 VARCHAR2(20);
BEGIN
SELECT DNAME INTO V2 FROM DEPT WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME=V1);
RETURN V2;
END;
FUNCTION FUN2(V1 NUMBER) RETURN NUMBER IS
BEGIN
RETURN -V1;
END;
PROCEDURE PRO1(V1 DATE) IS
V2 INT;
BEGIN
SELECT COUNT(1) INTO V2 FROM EMP WHERE HIREDATE<V1;
DBMS_OUTPUT.PUT_LINE(V2);
END;
END PKG_1;
--创建一个包,包含三个函数,一个存过
--F1 函数,输入一个员工编号,返回员工工资
SELECT PKG_1.F1(7788)
FROM DUAL;
--F1 函数,输入一个员工姓名,返回部门人数
SELECT PKG_1.F1('KING')
FROM DUAL;
--F1 函数,输入一个日期,返回这个日期之后入职的人数
SELECT PKG_1.F1(DATE'1982-1-1')
FROM DUAL;
--P1 存过,把DEPT表10部门的部门所在地插入到EMP1的JOB列
CALL P1();
--包头
CREATE OR REPLACE PACKAGE PKG_1 IS
FUNCTION F1(V1 INT) RETURN NUMBER;
FUNCTION F1(V1 VARCHAR2) RETURN INT;
FUNCTION F1(V1 DATE) RETURN INT;
PROCEDURE P1;
END PKG_1;
--包体
CREATE OR REPLACE PACKAGE BODY PKG_1 IS
FUNCTION F1(V1 INT) RETURN NUMBER IS
V2 NUMBER;
BEGIN
SELECT SAL INTO V2 FROM EMP WHERE EMPNO=V1;
RETURN V2;
END;
FUNCTION F1(V1 VARCHAR2) RETURN INT IS
V2 INT;
BEGIN
SELECT COUNT(1) INTO V2 FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME=V1);
RETURN V2;
END;
FUNCTION F1(V1 DATE) RETURN INT IS
V2 INT;
BEGIN
SELECT COUNT(1) INTO V2 FROM EMP WHERE HIREDATE>V1;
RETURN V2;
END;
PROCEDURE P1 IS
BEGIN
INSERT INTO EMP1(JOB) SELECT LOC FROM DEPT WHERE DEPTNO=10;
COMMIT;
END;
END PKG_1;
--包的重载
包内的函数和存过可以重名,使用时会根据输入/输出的参数的个数或数据类型自动区分是哪一个
8.2 定时任务 JOB
JOB是ORACLE的定时任务,也叫定时器/定时作业/作业,能够定时的执行一些脚本/DML语句
一般用于作数据备份/数据提炼/数据库的性能优化/重建索引等等的工作
创建JOB的语法(用PLSQL创建):
DECLARE
变量名 NUMBER;
BEGIN
DBMS_JOB.SUBMIT(JOB => 变量名, --JOB号
WHAT => '存过名;/DML操作', --存过不用跟括号
NEXT_DATE => SYSDATE+数, --第一次执行的时间,立刻执行写SYSDATE
INTERVAL => 'SYSDATE+数'); --间隔时间
COMMIT; --创建定时任务需要提交
END;
--创建一个JOB,每分钟执行一次PKG_1.P1
DECLARE
V_JOB NUMBER;
BEGIN
DBMS_JOB.SUBMIT(JOB => V_JOB,
WHAT => 'PKG_1.P1;',
NEXT_DATE => SYSDATE,
INTERVAL => 'SYSDATE+1/24/60');
END;
SELECT * FROM EMP1;
⑴查看JOB
SELECT * FROM USER_JOBS;
⑵停止JOB(PLSQL)
BEGIN
DBMS_JOB.BROKEN(JOB号,TRUE);
COMMIT;
END;
BEGIN
DBMS_JOB.BROKEN(23,TRUE);
END;
⑶立即执行JOB
CALL DBMS_JOB.RUN(JOB号);
CALL DBMS_JOB.RUN(23);
⑷删除JOB
CALL DBMS_JOB.REMOVE(JOB号);
COMMIT;
CALL DBMS_JOB.REMOVE(23);
当JOB执行失败后会自动重试
1.每次的重试时间都是递增的,第一次间隔1分钟,第二次2分钟,第三次4分钟,以此类推
2.当重试时间超过1440分钟时,固定重试时间为1440分钟,即24小时
3.超过16次重试后,该JOB不会再重试,而是将其标记为BROKEN,16次重试大概需要7天半不到8天
193
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
