Orcale数据库
介绍
- Oracle数据库：是一种网络上的数据库，他在网络上支持多用户，支持服务器/客户机等部署
- 服务器与客户d机是软件概念，他们与计算机硬件不存在一一对应的关系，即，同一台计算机既可以充当服务器，又可以充当客户机，或者，一台计算机只充当服务器或只充当客户机
- oracle的优点：移植性好、使用方便、功能强
- SQL语言（结构化查询语言）
- S——structured
- Q——query
- L——language
简单查询
语法：
select 要查询的内容 from 数据来源（表名）--在XX表中查找XXX内容
具体：
select * from 表名 --查找表内所有的内容
select name,age from user --查找user表中的name项和age项
别名
表别名 语法：表 表别名
列表别名 语法：列 列别名
列 as 列别名（标准写法）
注意事项：
① 数字和特殊符号需要用双引号包裹。
②多表查询需要注明所查询的列信息在哪一个表中（表名.列明）
③ as只能用在列别名中，不能用在表别名上
④ 别名构成 汉字 英文 数字 数字+英文
⑤ 别名中汉字或者包含特殊符号区分大小写时，需使用双引号包裹
代码
select ename 员工姓名,
ename abc,
ename "123",
ename "%#@",
ename as 员工姓名,
ename as abc,
ename as "123",
ename as "%#@"
from emp;
select e.*
from emp e;
注释
单行注释：两个短线 --注释内容
多行注释：/* 注释内容 */
select * --查询所有的列
from emp;--员工信息表
数据类型（重点）
数值类型
- number([数1[,数2]]) 会进行四舍五入
- 数1表示整数位长度，数2表示小数位数 不写默认为整数
- 数1最大为38
- 数2最长可为126
- 变量定义时数一表示整体长度，数2表示小数位位数，所以要数2<数1
- int 整型
- 只能存储的数据相当于 number(22)
字符类型
- char([数]) 定长字符
- 最多存储2000字节的字符 数不写 默认为1
- 位数不足时用空格补齐
- varchar/varchar2 (数) 不定长字符
- 最多存储2000字节的字符 数必须要写
- 位数不足时 直接存储
- 二者区别：
- char 读取速度快 可能会浪费空间
- varchar/varchar2 读取速度慢 不会浪费空间
日期类型
- date由一个九字节组成的定宽日期时间类型
- timestamp 时间戳
- 比date更加精确 存储了时区
select 123,'abc',date'2022-6-16'
from dual;
select 123*456
from dual;
select sysdate
from dual;
--隐式转换 纯数字字符串默认属于数字型
select '123'+'456',123+456,
'123+456'
from dual;
dual表被称为万能表
数字默认右对齐，字符默认左对齐
大对象
- clob 字符大对象 用char存储 比如：.xml文件
- blob 二进制大对象用二进制存储 保存图片
条件查询
语法：
select 查询内容
from 表名
where 条件;
案例：
--查询user表中name为小明的数据
select *
from user
where name = '小明';
比较运算符
| < | 小于 |
|---|---|
| > | 大于 |
| <= | 小于等于 |
| >= | 大于等于 |
| = | 等于 |
| != | 不等于 |
| <> |
--查询emp表中 name！=scott 的员工信息
select *
from emp
where ename!='SCOTT';
--查询emp表中 工资>2000的员工姓名和工资
select ename,sal
from emp
where sal>2000;
--查询emp表中 1981-1-1之前入职的员工信息
select *
from emp
where hiredate<date'1981-1-1';
--查询emp表中 不是30部门的员工信息
select *
from emp
where deptno!=30;
select *
from emp
where deptno<>30;
逻辑运算符
| and | 并且 | 两边条件都要满足 |
|---|---|---|
| or | 或者 | 两边条件只需满足一个 |
| not | 非 | 取反 |
注意：and和or是相反的
--工资>2000并且是30部门的员工信息
select *
from emp
where sal>2000 and deptno = 30;
--工资>2000或30部门的员工信息
select *
from emp
where sal>2000 or deptno = 30;
--查询既不是30部门也不是10部门的员工信息
select *
from emp
where deptno != 30 and deptno <>10;
select *
from emp
where not(deptno = 30 or deptno = 10);
%做输出展示，不能再过程中运算
在SQL中大括号和中括号都用小括号代替
范围判断
在两者之间取值
between 小值 and 大值 == 小值<= and <=大值
--查询工资1981年入职的员工信息
select *
from emp
where hiredate between date'1981-1-1' and date'1981-12-31';
--查询工资在2000~4000之间的员工信息
select *
from emp
where sal between 2000 and 4000;
--查询工资1981年上半年入职的员工信息
select *
from emp
where hiredate between date'1981-1-1' and date'1981-6-31';
--查询职位是'SALESMAN'并且提成在1000~2000之间的员工信息
select *
from emp
where job = 'SALESMAN'
AND COMM between 1000 and 2000;
查询匹配多个字段
列名 in(值1,值2,值3,...) == 列=值1 or 列=值2 or 列=值3 or...
--查询员工编号为7788,7654,7839的员工信息
select *
from emp
where empno in(7788,7654,7839);
select *
from emp
where empno = 7788 or empno = 7654or empno = 7839;
--查询职位不是MANAGER ANALYST SALESMAN 的员工信息
select *
from emp
where job not in('MANAGER','ANALYST','SALESMAN');
select *
from emp
where not job in('MANAGER','ANALYST','SALESMAN');
算术运算符
-
-
- / ()
-
--查询工资加上500后大于3000的员工信息
select *
from emp
where sal+500 > 3000;
select *
from emp
where sal > 3000 - 500;
判断空值和非空值
is null / is not null
select *
from emp
where mgr is null;
select *
from emp
where comm is not null;
--查询没有提成的员工信息（提成是0的也算没有提成）
select *
from emp
where comm = 0 or comm is null;
注意事项：
- in函数中可以有null，不影响结果
- not in 中有null 结果为空
- not in 有null 需要去空(nvl函数)
去空函数
nvl(参数1，参数2）当参数1为空时，返回参数2，否则为参数1
select nvl(comm,0),comm
from emp;
连接符（管道符）
把多个字段连接成一个新的字符串类型的字段
--查询20部门职位是职员的的员工信息
select *
from emp
where deptno = 20 and job = 'CLERK';
select *
from emp
where deptno || job = '20CLERK';
--查询10部门提成为空的员工信息
select *
from emp
where deptno || comm='10';
select *
from emp
where deptno =10 and comm is null;
--查询30部门提成是0的员工信息
select *
from emp
where deptno || comm ='300';
select *
from emp
where comm || deptno ='030';
优先级
*/ 优先于 + -
优先级同级时，从左向右计算
select 2+4 ||5,2||4+5 --运算符优先连接符
from dual;
优先级顺序：
- ()
- */
- +-||
- 比较符
- is null、not is null
- between and
- not
- and
- or
可以使用括号改变优先级，不清楚优先级的也可以括号
模糊查询
语法：
select 内容
from 表
where 列 like '匹配的内容';
当给到一个模糊的概念时，要和上级沟通，确认具体的需求
通配符
% 表示0或多个字符 _表示一个任意字符
--查询名字中包含S的员工信息
select *
from emp
where ename like '%S%';
--查询姓名中第二个字母是L的员工信息
select *
from emp
where ename like '_L%';
--查询以A开头的姓名的员工信息
select *
from emp
where ename like 'A%';
--查询长度是4个字符的员工信息
select *
from emp
where ename like '____';
--查询姓名以J开头的长度为5的员工信息
select *
from emp
where ename like 'J____';
--查询姓名以J开头S结尾的员工信息
select *
from emp
where ename like 'J%S';
--查询姓名既不包含S也不包含A的员工信息
select *
from emp
where ename not like '%S%'
and ename not like '%A%';
转义
1.通用
q'{转义的内容}'
2.单引号转义第二种写法
单引号双写
--显示一个单引号
select q'{‘}'
from dual;
select ''''
from dual;
--显示 'A SAID:"B SAID:'LET'S . GO'"' 这个内容
select q'{'A SAID:"B SAID:'LET'S . GO'"'}'
from dual;
select' ''A SAID:"B SAID:''LET''S . GO''"'' '
from dual;--先写一个单引号由于包裹要显示的内容，内容中每个单引号进行双写
3.模糊查询中的转义
一个转义标志只能转义一个符号
语法：列 like '内容' escape '转义标志'
--查询以%开头的信息
select *
from a
where name like 'a%%' escape'a';--表示a后面的第一个%要进行转义，a只是一个标志，可以随意设置
--查询a 表中有3个下划线的
select *
from a
where name like 'b_b_b_' escape'b';
--查询a 表中有3个下划线的
select *
from a
where name like '%b%' escape'b';
系统函数
单行函数
数值函数
- abs(值) 求绝对值
正数的绝对值是它本身
负数的绝对值是相反数
0的绝对值是0
select abs(1223),abs(-123),abs(0)
from dual;
- mod(值1，值2) 取余
值1是被除数，值2是除数
值2可以为0，结果为值1
结果的正负与值1一致，和值2无关
select mod(13,2),--13÷2=6...1
mod(-10,3),
mod(-13,-2),
mod(13,-2),
mod(13,0)
from dual;
--假设员工编号奇数是男生，偶数是女生 查询所有男员工的信息
select *
from emp
where mod(empno,2) = 1;
- ceil(值) 向上取整
select ceil(3.5),
ceil(3.01),
ceil(-3.5)
from dual;
- floor(值) 向下取整
select floor(3.5),
floor(3.01),
floor(-3.5)
from dual;
- round(值1[,值2]) 四舍五入
数2保留小数的位数，不写默认保留整数
小数位数不够 不会补0
数2负数时，保留到小数位数点前几位
select round(3.1415926,3),
round(1113.1415926,-2),
round(3.1415926),
round(3.1415926,-2),
round(3.14,3)
from dual;
--查询员工的姓名和入职日期，根据入职时间计算司龄
select ename,hiredate,round((sysdate-hiredate)/365)
from emp;--sysdate为当前系统时间
- trunc(值1[,值2]) 截取/截断（不进位）
select trunc(3.1415926,3),
trunc(1113.1415926,-2),
trunc(3.1415926),
trunc(3.1415926,-2),
trunc(3.14,3)
from dual;
- sign(值) 判断正负零
正数返回值为1
负数返回值为-1
0返回值为0
select sign(10),sign(-10),sign(0)
from dual;
--员工编号>经理编号为优秀，<为不优秀，查询优秀的员工信息
select *
from emp
where empno>mgr;
select *
from emp
where sign(empno-mgr)=1;
- power(值1,值2) 次方/开方
select power(2,4),
power(2,-2),
power(2,1/2),
power(2,0),
power(2,0.3)
from dual;
--查询工资的部门编号的1/10次方
select power(sal,deptno/10)
from emp;
扩展
order by 列名 顺序要求,列名 顺序要求, ...
asc 升序 desc 降序
order by name asc,age desc;--按姓名为升序，年龄为降序进行排序``
字符函数
- upper(str) 转为大写
- lower(str) 转为小写
- initcap(str) 首字母大写
- 多个单词（单词之间有非英文字符被看成两个单词)的首字母都会被转成首字母大写
select upper('apple'),
lower('APPLE'),
initcap('apple'),
initcap('APPLE'),
initcap('I我like喜欢eat吃apple苹果')
from dual;
--将emp表中的ename项转成小写和首字母大写
select lower(ename),initcap(ename)
from emp;
-
length(str) 返回字符串长度(字符)
-
lengthb(str) 返回字符串长度(字节)
注意：一个英文字母为一个字节，也为一个字符一个汉字为两个字节，为一个字符
一般查询都用lengthb(str)
select length('我,'),lengthb('我,'),
length('abc'),lengthb('abc'),
length('燚，'),lengthb('燚，')
from dual;
--查询员工姓名 员工姓名的长度
select ename,length(ename),length('ename')
from emp;
- trim(str) 去除字符串两端的空格
- ltrim(str1[,str2]) 去除字符串左端的指定字符，str2不写默认为空格
- rtrim(str1[str2]) 去除字符串右端的指定字符，str2不写默认为空格
select trim(' a b c '),
ltrim(' a b c '),
rtrim(' a b c ')
from dual;
select ltrim('aaabbbcccc','a'),--结果为bbbccc
rtrim('aaabbbcccc','cb')--cb系统默认为两个字符，所以结果为aaa
from dual;
--查询员工编号 员工编号左边去掉7 再去掉左边的8 的员工编号
select empno,ltrim(ltrim(empno,7),8)
from emp;
9.lpad(str1,数
- lpad(str,数,str2) 向左填充
- rpad(str,数,str2) 向右填充
str：表示原字符
数：填充后展示的字符长度 长度过长会被截取，先截取最右边
str2：要填充的字符串
select lpad('abc',6,'星'),rpad('abc',6,'星'),
lpad('abc',2,'*'),rpad('abc',2,'*')
from dual;
--abc 左右各填充 ** 结果为 **abc**
select lpad(rpad('abc',5,'*'),7,'*')
from dual;
--abc 左右各填充 星星 结果为 星星abc星星
select lpad(rpad('abc',7,'星'),11,'星')
from dual;
--员工姓名左右两边各填充两个** **SCOTT** **KING**
select ename,
rpad(lpad(ename,lengthb(ename)+2,'*'),
lengthb(ename)+4,'*') 填充后姓名
from emp;
- instr(str1,str2[,数1[,数2]]) 查找str2在str1中的位置
str1：原字符串
str2：要查找的字符串
数1：从第几位开始，默认第一位
负数从倒数第几位开始
数2：第几次出现的，默认第一次，始终从左向右
select instr('helloworld','o') a,--o第一位第一次出现 5
instr('helloworld','o',6) b,--o第六位第一次出现 7
instr('helloworld','o',1,2) c,--o第一位第二次出现 7
instr('helloworld','o',-1) d,--o倒数第一位开始找 7
instr('helloworld','o',-5) e,--o倒数第五位开始找 5
instr('helloworld','o',-1,2) f --o倒数第一位第二次 5
from dual;
--查询名字第二个字母是L的员工信息
select *
from emp
where instr(ename,'L')=2;
--查询'helloworld' 中l第一次和第二次查询的位置
select instr('helloworld','l') 第一次,
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;
- substr(str,数1[,数2]) 截取字符串
str：原字符串
数1：从第几位开始截取 负数为从倒数第几位开始截取
数2：截取几位，不写默认截取到最后
select 'helloworld',
substr('helloworld',2),--从第二位开始，截取到最后
substr('helloworld',2,3),--从第二位开始，截取三位
substr('helloworld',-2),--从倒数第二位开始，截取到最后
substr('helloworld',5,2),--从第五位开始，截取两位
substr('helloworld',-5,2)--从倒数第五位开始，截取两位
from dual;
--从'helloworld'中，截取owo、截取最后三位、截取3-5位、截取出hrld
select substr('helloworld',5,3),
substr('helloworld',-3,3),
substr('helloworld',3,3),
substr('helloworld',1,1)||substr('helloworld',-3,3)
from dual;
--截取员工姓名从A开始到最后
select substr(ename,instr(ename,'A'))
from emp
where instr(ename,'A')!=0;
- replace(str1,str2[,str3]) 整体替换
把str1中的str2整体替换为str3，str3不写默认替换为空
select replace('helloworld','l','*'),
replace('helloworld','owo','!')
from dual;
--把helloworld中的l替换为# and o 换位%
select replace(replace('helloworld','l','#'),'o','%')
from dual;
- translate(str1,str2,str3) 逐一替换
把str1中的str2逐一替换成str3
参数3比参数2多的情况下，只认第一次匹配的，多出来的无效
参数2比参数3多的情况下，参数2多出来的替换成空
参数2或参数3有一个为空时，结果为空
多次替换只有一次有效
select translate('helloworld','lo','@%') --l换@，o换%。结果：he@@%w%r@d
from dual;
select translate('helloworld','lo','*') --l换*，o换空。结果：he**wr*d
from dual;
select translate('helloworld','lll','234') --l换2。结果：he11owor1d
from dual;
select translate('helloworld','*'||'l','*') --l换为空。结果：heoword
from dual;
--把员工姓名中AMS替换成空格
select translate(ename,'AMS',' ')
from emp
where instr(ename,'A')!=0
or instr(ename,'M')!=0
or instr(ename,'S')!=0;
--查询ename和job有一个或一个以上相同的字母的员工信息
select *
from emp
where ename !=nvl(translate(ename,'*'||job,'*'),0);--将ename中有job中任意字母一样的换为空，如果ename发生改变证明符合条件
- concat(str1,str2) 连接字符串
只能连接两个字符串，如果要连接多个需要嵌套
select concat(concat(ename,job),sal)
from emp;
- to_single_byte(str) 转半宽半角 半型
- to_multi_byte(str) 转全宽全角 全型
select '^**%$23ab字',
to_single_byte('^**%$23ab字'),
to_multi_byte('^**%$23ab字')
from dual;
- 随机字符串函数
dbms_random.value(小数，大数) 生成小数和大数之间的随机数
dbms_random.string('格式',长度) 生成给定长度的随机字符串
格式：
U 大写 L 小写 A 字母 x 大写字母和数字 p 可打印字符
select dbms_random.value(2，2345),
dbms_random.string('L',8)
from dual;
日期函数
- sysdate 获取系统时间
select sysdate,
sysdate+1,
sysdate+1/24,
sysdate+1/24/60,
sysdate+1/24/60/60
from dual;
--查询今年2月份天数
select date'2022-3-1'-date'2022-2-1'
from dual;
--查询距离2023年元旦还有多少天
select floor(date'2023-1-1'-sysdate)
from dual;
--查询1000天之后的日期
select sysdate+1000
from dual;
- add_months(日期，数) 几个月后的日期
select add_months(sysdate,1),--一个月后
add_months(sysdate,-1),--一月前
add_months(sysdate,12),--一年后
add_months(sysdate,-12)--一年前
from dual;
--查询三个月前距离今天多少天
select sysdate-add_months(sysdate,-3)
from dual;
* 年 add_months(日期,数*12) 几年前或几年后
* 月 add_months(日期,数) 几月前或几月后
* 日 日期±数 几月前或几天后
* 小时 日期±数/24 几月小时前或几小时后
* 分钟 日期±数/24/60 几分钟前或几分钟后
* 秒 日期±数/24/60/60 几秒前或几秒后
- months_between(日期1，日期2) 两个日期相差的月数
- Oracle算法：把一天当作1/31月
--查询2008年8月8号距离今天多少个月
select months_between(date'2008-8-8',sysdate)
from dual;
--查询2021-12-8距离今年多少年 保留两位
select round(months_between(date'2021-12-18',sysdate)/12,2)
from dual;
--把入职日期做为出生年月日，计算每个人活了多少年-多少月-多少天
select floor(months_between(sysdate,hiredate)/12) 年,
floor(mod(months_between(sysdate,hiredate),12)) 月,
floor(sysdate-add_months(hiredate,floor(months_between(sysdate,hiredate)))) 天
from emp;
* **求两个日期相差的**
* 年数 months_between(sysdate,hiredate)/12
* 月数 months_between(sysdate,hiredate)
* 天数 日期相减
* 小时 日期相减*24
* 分钟 日期相减*12*60
* 秒 日期相减*12*60*60
- next_day(日期,'星期几'/对应的数字) 求下个星期几的日期
- 数字：由于国外每周第一天为周日，所以1为周日
select next_day(sysdate,'星期二'),
next_day(sysdate,3),
next_day(sysdate,1)
from dual;
--求2020年第一个星期五，2020年十月的第一个星期五
select next_day(date'2019-12-31',6),
next_day(date'2020-9-30',6)
from dual;
- last_day(日期) 日期所在月的最后一天
--求本月的最后一天
select last_day(sysdate)
from dual;
--三个月的最后一天距离今天多少天
select last_day(add_months(sysdate,3))-sysdate
from dual;
- trunc(日期，'格式') 截取日期之初
- 格式:
- yyyy 一年之初
- mm 本月之初（本月1号）
- dd 当天之初（零点零分零秒）
- q 当季度之初
- day/dy/d 当周之初（本周周日）
- hh/hh12 整小时（12小时制）
- hh24 整小时（24小时制）
- mi 整分
- ss 报错-精度错误
- 格式:
select trunc(sysdate,'yyyy') yyyy,
trunc(sysdate,'mm') mm,
trunc(sysdate,'dd') dd,
trunc(sysdate,'q') q,
trunc(sysdate,'day') day,
trunc(sysdate,'dy') dy,
trunc(sysdate,'d') d,
trunc(sysdate,'hh') hh,
trunc(sysdate,'hh12') hh12,
trunc(sysdate,'hh24') hh24,
trunc(sysdate,'mi') mi
from dual
转换函数
- ascii(str) 将字符串转换为ASCII码
- 多个字符只转换一次
select ascii('A'),
ascii('a'),
ascii(','),
ascii('asd'),
ascii('S')
from dual;
- chr(数) 把一个数转成对应的字符
select chr(55),
ascii(88)
from dual;
--查询员工姓名以S开头的员工信息 用4种方法
select *
from emp
where ename like 'S%';
select *
from emp
where ascii(ename)=ascii('S');
select *
from emp
where instr(ename,'S')=1;
select *
from emp
where substr(ename,1,1)='S';
- to_number(str) 把纯数字的字符转换成数字（类型转换)
select '123',
to_number('123')
from dual;
- to_date(str,'格式') 按照格式把字符转换成日期（类型转换）
- yyyy 年 mm 月 dd 日 hh/hh12 小时(12小时制) hh24(24小时制) mi 分 ss 秒 am 上午 pm 下午
select '2022-6-20',
to_date('2022-6-20 下午 2:10:45','yyyy-mm-dd pm hh12:mi:ss'),
to_date('2022-6-20 14:10:45','yyyy-mm-dd hh24:mi:ss'),
to_date('2022-6-20 2:10:45','yyyy-mm-dd hh12:mi:ss')
from dual;
--查询2019年12月5号中午12点
--2022-10-1 的下午两点
select to_date('2022-10-1 下午 12:00:00','yyyy-mm-dd pm hh12:mi:ss'),
to_date('2022-10-1 12:00:00','yyyy-mm-dd hh24:mi:ss'),
to_date('2022-10-1 上午 12:00:00','yyyy-mm-dd pm hh12:mi:ss'),--上午12点是00:00:00,省略不显示
--to_date('2019-12-5 中午 12:00:00','yyyy-mm-dd pm hh12:mi:ss') 报错，只有AM&PM
to_date('2022-10-1 下午 2:00:00','yyyy-mm-dd pm hh12:mi:ss'),
to_date('2022-10-1 14:00:00','yyyy-mm-dd hh24:mi:ss'),
to_date('10 14:00:00','mm hh24:mi:ss')--yyyy,dd 默认本年，1号
from dual;
- to_char函数
- to_char(日期,'格式') 提取日期中的元素
- 格式
- yyyy 年
- mm 月
- dd 天
- q 季度
- day/dy 星期 汉字
- d 星期 数字
- hh/hh12 时（12小时制）
- hh24 时（24小时制）
- mi 分
- ss 秒
- sssss 表示当前秒是今天的第几秒
- ddd 所给日期是所在年的第几天
- 注意：提取日式用汉字做分隔符 汉字要加双引号
- 格式
- to_char(日期,'格式') 提取日期中的元素
select to_char(sysdate,'yyyy"年" mm"月" dd"日" pm hh12:mi:ss dy')
from dual;
--查找闰年入职的员工信息
select *
from emp
where to_char(to_date(to_char(hiredate,'yyyy')||'-3-1','yyyy-mm-dd')-1,'dd')=29;
select *
from emp
where to_char(to_date(to_char(hiredate,'yyyy')||'-12-31','yyyy-mm-dd'),'ddd')=366;
/*闰年
1.2月有29天
2.这年一共366天
3.能被4整除并且不能被100整除 或能被400整除*/
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;
* **to_char(数,'格式') 格式化数字**
* **格式：**
* 自带四舍五入
* 9 占位符
* 0 占位符(用在各位)
* $ 美元
* L 本地货币
select to_char(3.1415,'9.999') a,
to_char(3.1415,'0.999') b,
to_char(3.14,'9.999') c,
to_char(0.1415,'9.999') d,
to_char(0.1415,'0.999') e,
to_char(3241415,'L999,999,999.999') f,
to_char(0.1415,'9999') g
from dual;
* **to_char(参数) 把参数转换为字符串**
select 123，--123
to_char(123),--'123'
to_char(sysdate)--'20-6月 -22'
from dual;
总结
- 日期的三种表达方式：
- date'年-月-日'
- to_date(str,'格式')
- '日-几月-年' 如:'1-6月-1981' 只能在where后使用，不能在select后使用（默认为字符串）
--查询1981年6月1号后入职的员工信息
select *
from emp
where hiredate>'1-6月-1981';
通用函数
通用函数：参数不限制类型的函数
- userenv('language') 查看客户端字符集
select userenv('language')
from dual;
- greatest(参1,参2...) 求多个参数中的最大值
- least(参1,参2...) 求多个参数中的最小值
注：参数类型要一致
如果参数中有null 结果为null
select greatest(1,2,3,4) a,
greatest('A','a','z','Z') b,
greatest(date'2011-1-1',date'2022-1-1',sysdate) c
from dual;
select least(1,2,3,4) a,
least('A','a','z','Z') b,
least(date'2011-1-1',date'2022-1-1',sysdate) c
from dual;
select greatest('我','爱','中','国')
from dual;
--查询员工编号，经理编号，部门编号，他们中大值和最小值
select empno,mgr,deptno,greatest(empno,nvl(mgr,0),deptno),
least(empno,nvl(mgr,0),deptno)
from emp;
- coalesce(参1,参2...) 查找多个参数中，第一个不为空的值（重点）
注：参数类型一致
select coalesce(null,1,2,3) a,
coalesce('a',null,'z','w') b,
coalesce(null,date'2022-1-1',sysdate) c
from dual;
--查询经理，提成，工资 第一个不为空的值
select comm,mgr,sal,coalesce(comm,mgr,sal)
from emp;
--查询每个人的姓名和月薪
select ename,sal,comm,
sal+nvl(comm,0)--nvl只适用于Oracle数据库
from emp;
select ename,sal,comm,
sal+coalesce(comm,0)--coalesce 适用于多个数据库
from emp;
- nvl(参1,参2) 参1为空返回参2，否则返回参1
注：两个参数类型一致
--查询每个人的姓名和月薪
select ename,sal,comm,
sal+nvl(comm,0)--nvl只适用于Oracle数据库
from emp;
--查询 员工姓名 经理编号 如果经理编号为空返回666
select ename,nvl(mgr,666)
from emp;
- nvl2(参1,参2,参3) 参1为空返回参3，否则返回参2
如果经理参数为空，返回老大，否则返回小弟
select mgr,nvl2(mgr,'小弟','老大')
from emp;
如果comm列有空或为0，返回无奖金，否则返回有奖金
select nvl2(replace(comm,0),'有奖金','无奖金')
from emp;
select replace(comm,0),translate(comm,0,'')
from emp;
- distinct 去重
注：多列去重只有完全一致的才会去重
select distinct deptno
from emp;
select distinct job
from emp;
select distinct deptno,job
from emp;
- case when
- 第一种用法
case 列 when 列中值1 then 值1
when 列中值2 then 值2
...
[else 值n]
end
注：空格不能少些，then后面的值需要一个类型
--如果部门编号是10，返回十部门 20，返回二十部门，30，返回三十部门
select deptno,
case deptno when 10 then '十部门'
when 20 then '二十部门'
else '三十部门'
end 中文部门
from emp;
--查询员工职位和中文职位
select job,
case job when 'CLERK' then '职员'
when 'SALESMAN' then '销售员'
when 'PRESIDENT' then '董事长'
when 'MANAGER' then '经理'
else '分析员'
end 中文职位
from emp;
* **第二种用法(重点）**
case when 条件 then 值1
when 条件 then 值2
...
[else 值n]
end
/*如果部门编号是10，返回十部门
20，返回二十部门，
30，返回三十部门*/
select deptno,
case when deptno=10 then '十部门'
when deptno=20 then '二十部门'
else '三十部门'
end "中文部门"
from emp;
--查询员工职位和中文职位
select job,
case when job='CLERK' then '职员'
when job='SALESMAN' then '销售员'
when job='PRESIDENT' then '董事长'
when job='MANAGER' then '经理'
else '分析员'
end "中文职位"
from emp;
/*如果工资>=3000 返回一级 >=2000 返回二级
>=1000 返回三级 否则返回四级*/
select sal,
case when sal>=3000 then '一级'
when sal>=2000 then '二级'
when sal>=1000 then '三级'
else '四级'
end "中文工资"
from emp;
/*如果入职日期在1981年前 返回他的部门
1981-1981 返回入职的年份
1982之后 返回入职月份*/
select hiredate,
case when hiredate < date'1981-1-1' then ''||deptno
when to_char(hiredate,'yyyy') = 1981 then to_char(hiredate,'yyyy')
when date'1982-12-31' < hiredate then to_char(hiredate,'mm')
end "筛选信息"
from emp;
select hiredate,
case when hiredate < date'1981-1-1' then to_char(deptno)
when to_char(hiredate,'yyyy') = 1981 then to_char(hiredate,'yyyy')
when date'1982-12-31' < hiredate then to_char(hiredate,'mm')
end "筛选信息"
from emp;
select hiredate,
case when hiredate < date'1981-1-1' then ''||deptno
when hiredate between date'1981-1-1' and date'1981-12-31' then to_char(hiredate,'yyyy')
when date'1982-12-31' < hiredate then to_char(hiredate,'mm')
end "筛选信息"
from emp;
- decode(列,列中值1,值1,列中值2,值2...值n)
注：和case when 的第一种用法可以互换
/*如果部门编号是10，返回十部门
20，返回二十部门，
30，返回三十部门*/
select deptno,
decode(deptno,10,'十部门',20,'二十部门','三十部门')
from emp;
- nullif(参1,参2) 判断两个参数是否相等 相等返回空，不等返回参数一
注：参数类型要一直
参数1不能为空
sql中如果有除法运算 为了防止除数为0报错，可以用nullif nullif(除数,0) 除数为零报错，除数为空结果为空
空参与任何运算结果都为空
--查询员工姓名 工资 提成 工资是提成的多少倍
select ename,sal,comm,sal/nullif(comm,0)
from emp;
聚合函数（重点）
聚合函数又称为分组函数
- min(列) 最小
- max(列) 最大
- sum(列) 求和 只能是数值
- avg(列) 平均 只能是数值
- count(列) 计数
select min(sal),max(sal),sum(sal),avg(sal),count(sal),
count(comm),count(*),count(1)
from emp;
--查询最小和最大的员工姓名，最小和最大的入职日期
--平均工资，有奖金的人数
select min(ename),max(ename),
min(hiredate),max(hiredate),
avg(sal),count(comm)
from emp;
- group by 分组
查询每个部门的人数和平均工资
select deptno,count(1),avg(sal)
from emp
group by deptno;
查询每种职位的人数 平均工资 最高工资 总工资
select job,count(1),avg(sal),max(sal),sum(sal)
from emp
group by job;
查询每个经理的下属人数 有奖金的人数 最低工资
select mgr,count(1),count(comm),min(sal)
from emp
group by mgr;
查询每个部门每种职位中最早和最晚入职的日期
select deptno,job,min(hiredate),max(hiredate)
from emp
group by deptno,job;
- having 分组后筛选
--查询部门和部门最高工资 只显示最高工资大于2900的部门
select deptno,max(sal)
from emp
group by deptno
having max(sal)>2900;
--查询最早入职日期在1981-6-1之前 的职位
select job,min(hiredate)
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;
--查询每个部门的平均工资 只显示平均工资>1000的，按平均工资排序
select deptno,avg(sal)
from emp
group by deptno
having avg(sal)>1000
order by 2;
--查询每个部门中名字包含A的员工人数 只显示 人数<3的
select deptno,count(1)
from emp
where ename like '%A%'
group by deptno
having count(1)<3;
--统计员工表中每个职位上半年入职的人数 结果只显示人数<3的
select job,count(1)
from emp
where to_char(hiredate,'mm')<=6
group by job
having count(1)<3;
- wm_concat(列) 分组连接字符串
结果默认分隔符是都好 结果不能排序
select deptno，wm_concat(ename)
from emp
group by deptno;
- listagg(列,分隔符) within group(order by 列) 分组连接字符串
可以自己指定分隔符 还能进行排序
select deptno，listagg(ename,'-') within group(order by ename)--ename排序
from emp
group by deptno;
select deptno，listagg(ename,'-') within group(order by sal)--ename不排序
from emp
group by deptno;
小结
-
null小结：
- 空不占内存 空不等于0 不等于空格 也不等价于空字符串''
- 对一行值进行计算式 单行函数/四则运算 空和任何值计算结果都为空
- 对一列值进行计算时 聚合函数/分析函数 空不参与计算
- 可以使用coalesce/nvl/nvl2 处理空值
-
count(1) count(*) count(列) 的区别：（面试题）
-
count(1)是对行进行计数(统计行数) ，数字随意不影响结果，习惯写1
count()是全表扫描，count(1)比count()速度快 -
count(1) count(*) 都返回行数
count(列)
-
-
where和having的区别
-
where 是在分组前 对分组前的数据进行筛选
having 是分组后 对分组后的数据进行筛选 -
where后不能加聚合函数
having后可以加聚合函数
-
-
执行顺序
select 查询的内容 5
from 表 1
where 查询条件 2
group by 分组 3
having 分组后筛选 4
order by 排序 6
- 你们平时用到的函数（面试题）
- 聚合函数和分析函数的区别：
- 聚合函数一个组只能看到一个结果
- 分析函数既可以看到聚合的结果，还能看到组内的明细
- 值累加和行累加
- 聚合，分析，表连接
分析函数（重点）
又称为 开窗函数
-
语法：
函数名 (列) over ([partition by 列 order by 列]) -
函数：min max sum avg count
--查询一下每个部门的最低工资
select deptno,min(sal)
from emp
group by deptno;
--查询一下每个部门的最低工资,姓名，员工编号
select deptno,min(sal),ename,empno
from emp
group by deptno;--聚合函数 报错
select deptno,min(sal)over(partition by deptno),ename,empno
from emp;--开窗函数
--查询每种职位的平均工资，全表有提成的人数
select job,avg(sal)over(partition by job) "职位平均工资",
count(comm) over()
from emp;
--查询姓名，部门编号，部门经理，部门最低工资，经理的下属人数，全表最高工资
select ename,deptno,mgr,min(sal)over(partition by deptno),count(1)over(partition by mgr),max(sal)over()
from emp;
--查询SMITH的姓名 部门编号 部门最高工资 职位工资总和 部门最早入职日期
select *
from (
select ename 姓名,
deptno 部门编号,
max(sal)over(partition by deptno) 部门最高工资,
sum(sal)over(partition by job) 职位工资总和,
min(hiredate)over(partition by deptno) 部门最早入职日期
from emp)
where 姓名 = 'SMITH';
- 按值累加、平均,在over后加上order by
select ename,sal,sum(sal)over(order by sal),avg(sal)over(order by sal)
from emp;
- 按行累加、平均
--语法
order by 列 rows between unbounded preceding and current row
--例题
select ename,
sal,
sum(sal)over(order by sal rows between unbounded preceding and current row),
avg(sal)over(order by sal rows between unbounded preceding and current row)
from emp;
- 排序(面试题)
- 不重复的排序
row_number()over([partition by 列 ]order by 列)--必须排序
* **并列 跳一级 排序**
rank()over([partition by 列 ]order by 列)--必须排序
* **并列 不跳级 排序**
dense_rank()over([partition by 列 ]order by 列)--必须排序
* **列题**
--查询员工姓名，工资，工资的排序 用这三种方法
select ename,
sal,
row_number()over(order by sal),
rank()over(order by sal),
dense_rank()over(order by sal)
from emp;
--查询每个部门工资排名第一的人
select *
from (
select deptno,ename,
rank()over(partition by deptno order by sal desc) "排名"
from emp )
where 排名=1;
select *
from (
select e.*,
rank()over(partition by deptno order by sal desc) "排名"
from emp e )
where 排名=1;
- 数据向上提
lead(列[,数[,值]])over([partition by 列] order by 列)
值要和原来列表中的值类型一致
值不写默认为空，数不写默认为1
- 数据向下拉
lag(列[,数[,值]])over([partition by 列] order by 列)
值要和原来列表中的值类型一致
值不写默认为空，数不写默认为1
--按照工资降序 查询每个人比上个人的工资多多少
select ename,sal,
lag(sal,1,0)over(order by sal desc) 上个人的工资,
sal-lag(sal,1,0)over(order by sal desc)
from emp;
- 分组连接字符串
--语法
wm_concat(列)over([partition by 列] order by 列)
listagg(列,分隔符) within group(order by 列)over([partition by 列] order by 列)
--查询每个人的姓名职位员工编号 每种职位对应的姓名 用 ， - 连接
select ename ,job ,empno,
wm_concat(ename)over(partition by job),
listagg(ename,'-') within group(order by ename)over(partition by job)
from emp;
自定义函数
--语法：
create [or replace] function 函数名 [参数1 [in]/out 类型,参数2 [in]/out 类型,....]
return 类型 is/as
[声明的变量]
begin
要执行的内容
return 值/变量;
[exception]
end;
--or replace:如果有重名进行替换
没有参数
--反余弦函数
select acos(-1) from dual;--Π
select acos(0) from dual;--1/2Π
select acos(1) from dual;--0
--制作一个Π函数
create or replace function fun1
return number is
v_a number(20,19);
begin
v_a:=acos(-1);
return v_a;
end;
--函数调用
select fun1 from dual;
有参数
--输入一个员工编号返回他的工资
create or replace function fun2(v_empno number)
return number is
v_s number(10);
begin
select sal into v_s from emp where empno=v_empno;
return v_s;
exception
when no_data_found then
return 0;
end;
--调用函数
select fun2(7788) from dual;
--输入一个部门编号和一个职位，返回这个部门和这个职位的人数 没有数据返回0
create or replace function fun3(v_deptno number,v_job varchar2)
return number is
v_n number(10);
begin
select count(1)over() into v_n
from emp
where deptno = v_deptno
and job = v_job;
return v_n;
exception
when no_data_found then
return 0;
end;
select fun3(40,'a') from dual;
--输入一个部门编号 返回这个部门所有的人名，用 ,隔开
create or replace function fun4(v_deptno number)
return varchar is
v_n varchar(100);
begin
select wm_concat(ename)
into v_n
from emp
where deptno=v_deptno
group by deptno;
return v_n;
exception
when no_data_found then
return '没有';
end;
select fun4(40) from dual;
--输入一个年份，返回时平年还是闰年
create or replace function fun5(v_year number)
return varchar is
v_y varchar(10);
begin
if (mod(v_year,4)=0 and mod(v_year,100)!=0) or mod(v_year,400)=0 then
v_y:='闰年';
else v_y:='平年';
end if;
return v_y;
end;
create or replace function fun6(v_year number)
return varchar is
v_y varchar(10);
begin
select case when
to_char(to_date(v_year||'-12-31','yyyy-mm-dd'),'ddd')=366
then '闰年'
else '平年'
end
into v_y
from dual;
return v_y;
end;
select fun6(2000) from dual;
--输入一个员工编号返回他的上级层级关系 用/隔开
create or replace function fun7(v_empno number)
return varchar2 is
v_s varchar2(100);
begin
select listagg(ename,'/') within group(order by level)
into v_s
from emp
start with empno=v_empno
connect by prior mgr = empno
order siblings by ename;
return v_s;
end;
select fun7(7788) from dual;
--输入一个日期返回这个日期所在的月有几个工作日
create or replace function fun8(v_date date)
return number is
v_n number(10);
begin
SELECT count(lv) into v_n
FROM (SELECT TRUNC(v_date,'mm')-1+LEVEL LV
FROM DUAL
CONNECT BY LEVEL <=last_day(v_date)-trunc(v_date,'mm')+1)
--CONNECT BY LEVEL <=to_char(last_day(v_date),'dd'))
WHERE TO_CHAR(LV,'D') not in(1,7);
return v_n;
end;
select fun8(date'2022-6-4') from dual;
子查询
相关子查询
exists 存在
- 可以引导非相关子查询
- 但是更多时候引导的是相关子查询
- 子查询没有结果 最终结果为空
--查询部门所在地在NEW YORK 的员工信息
select *
from emp e
where exists(select*
from dept d
where d.loc='NEW YORK'
and e.deptno = d.deptno )
select *
from emp e
where exists(select 1
from dept d
where d.loc='NEW YORK'
and e.deptno = d.deptno )
--效率上来说，1>anycol>*，因为不用查字典表
--查询经理是SCOTT/BLAKE的员工信息
select *
from emp e
where exists(select*
from emp m
where m.ename in('SCOTT','BLAKE')
and e.mgr = m.empno)
select *
from emp e
where mgr in(select empno
from emp m
where m.ename in('SCOTT','BLAKE')
)
非相关子查询（标准子查询） 重点
单行单列 一个值
--查询和SMITH同部门的信息
select *
from emp
where deptno = (select deptno from emp where ename = 'SMITH')
--查询和SCOTT工资相等的员工信息 结果不显示SCOTT
select *
from emp
where sal = (select sal from emp where ename = 'SCOTT')
and ename != 'SCOTT';
--查询和7788员工首字母相同的员工信息
select *
from emp
where substr(ename,1,1) = (select substr(ename,1,1) from emp where empno=7788);
select *
from emp
where ascii(ename) = (select ascii(ename) from emp where empno=7788);
--工资大于全表平均工资的员工信息
select *
from emp
where sal> (select avg(sal) from emp);
单列多行 一个列
单行比较符 < > = <= >= <> !=
--查询比20部门所有人的工资都要高的员工信息
select *
from emp
where sal > all(select sal
from emp
where deptno = 20);
--查询比JONES所在部门任意一个人的工资低的员工信息
select *
from emp
where sal < any(select sal
from emp
where deptno = (select deptno
from emp
where ename = 'JONES'));
--查询比7654员工所在部门所有人的工资低的员工信息
select *
from emp
where sal < all(select sal
from emp
where deptno=(select deptno
from emp
where empno = 7654));
--查询和king下属同工作的员工信息
select *
from emp
where job =any(select job
from emp
where mgr=(select empno
from emp
where ename='KING'))
多列多行 一个表
多行比较符 any 任意一个
all 所有的
in/exists 存在
>any
<any
=any
>all
<all
=all
多列子查询
--查询部门平均工资最高的部门的员工信息
select *
from emp
where deptno in(select deptno
from (select deptno,avg(sal) pj
from emp
group by deptno)
where pj=(select max(avg(sal))
from emp
group by deptno));
with as 临时表
语法：
with a as(子查询语句)[,b as(子查询语言)]... --a/b为表名
select 语句;
--查询部门平均工资最高的部门的员工信息
with a as(select deptno,avg(sal) pj
from emp
group by deptno)
select *
from emp
where edptno in(select deptno
from a
where pj = (select max(pj) from a));
in的用法
* 常规用法
* in(值1,值2...)
* in 单列多行子查询
* 特殊用法
* in 多列子查询
--查询SCOTT 同部门同职位的员工信息
select *
from emp
where (deptno,job) in (select deptno,job
from emp
where ename='SCOTT');
--查询每个部门工资最低的员工信息
select *
from emp
where (deptno,sal) in (select deptno,min(sal)over(partition by deptno)
from emp);
--查询员工姓名，部门编号 部门名称
select e.ename,e.deptno,d.dname
from emp e,dept d
where e.deptno=d.deptno;
select ename,deptno,(select dname from dept d
where e.deptno=d.deptno) dname
from emp e;
相关子查询和非相关子查询的区别：
1. 非相关子查询的子查询的结果 供 主查询使用 子查询先于主查询执行 子查询可以单独执行
2. 相关子查询的子查询和主查询互相依赖 主查询先于子查询执行 子查询不能单独执行
表连接
通过标语表的关联字段 一次查询多张表的数据
如果有n张表 至少要n-1个关联条件
- 分类
- 内连接 [inner] join
- 外连接
- 左外连接 left [outer] join
- 右外连接 right [outer] join
- 全外连接 full [outer] join
笛卡尔积
性能杀手
select * from emp,dept; 14*4=56
查询结果行数为 emp表行数*dept表行数
内连接
显示 多张表的共有数据
select e.*,d.*
from emp e
join dept d
on e.deptno=d.deptno;--关联条件
左外连接
显示 多张表的共有数据 和 左表中的独有数据
select e.*,d.*
from emp e --主表
left join dept d
on e.deptno=d.deptno;
右外连接
显示 多张表的共有数据 和 右表中的独有数据
select e.*,d.*
from emp e
right join dept d
on e.deptno=d.deptno;
全外连接
显示 多张表的共有数据 和 各表中的独有数据
select e.*,d.*
from emp e
full join dept d
on e.deptno=d.deptno;
不等值连接
--查询emp表中每个人的工资等级
select * from salgrade;
select e.*,s.grade
from emp e
left join salgrade s
on e.sal between s.losal and s.hisal;
表连接第二种语法（不推荐）
--内连接
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;
--全外连接没有第二种语法
自连接
--查询每个人的姓名和他的经理名字
select a.ename,a.mgr,b.ename,b.empno
from emp a
left join emp b
on a.mgr=b.empno;
select a.ename,a.mgr,b.ename,b.empno
from emp a
join emp b
on a.mgr=b.empno;
/*select a.ename,a.mgr,b.ename,b.empno
from emp a
right join emp b
on a.mgr=b.empno;*/
--错误代码
--查询经理SCOTT/BLAKE的员工信息
select a.*
from emp a
left join emp b
on a.mgr = b.empno
where b.ename in('SCOTT','BLAKE');
多表连接
--查询员工姓名 工资 工资等级 部门名称
select e.ename,
e.sal,
s.grade,
d.dname
from emp e
left join dept d
on e.deptno=d.deptno
left join salgrade s
on e.sal between s.losal and s.hisal;
表连接小结
- 在表连接中 where 和on 对结果的影响（面试题重点）
- where 是对关联之后的数据做限制
- on是关联前的数据做限制
- 知识点小结
在内连接中
on 后加不成立的条件 结果是空
在左外连接中
on 后加不成立的条件 返回左表的数据 右表用空补齐
在右外连接中
on 后加不成立的条件 返回右表的数据 左表用空补齐
全外连接中
on 后加不成立的条件 返回各自独有的数据 互相用空补齐
不管什么连接 where后加不成立的条件 结果为空
执行顺序
(8)SELECT (9)DISTINCT (11)Top <num> <select_list>
(1)FROM [left_table]
(3)<join_type> JOIN <right_table>
(2)ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(6)WITH <CUBE | RollUP>
(7)HAVING <having_condition>
(10)ORDER BY <order_by_list>
集合
- 交集 intersect
- 并集 union/union all
- 差集 minus
a={1,2,3,4,5,6}
b={1,2,5,6,9}
a union b ={1,2,3,4,5,6,9} --合并去重
a union all b ={1,2,3,4,5,6,1,2,5,6,9} --合并所有
a intersect b ={1，2，5，6}
a minus b ={3,4}
a minus b ={9}
查询两张表 独有的数据
1.全外连接 minus 内连接
select a.*,b.*
from a
full join b
on a.id=b.id
minus
select a.*,b.*
from a
join b
on a.id=b.id;
2.一个左表非空列 is null or 一个右表非空列 is null
select a.*,b.*
from a
full join b
on a.id=b.id
where a.id is null or b.id is null;
行列转换
工作中使用比较少，面试较多
行转列
语法：case when/decode + 聚合函数 +分组
--把kecheng表中的course列 行转列
select id,name,
sum(case course when '语文' then score end) 语文,
sum(case course when '数学' then score end) 数学,
sum(case course when '英语' then score end) 英语,
sum(case course when '历史' then score end) 历史,
sum(case course when '化学' then score end) 化学
from kecheng
group by id,name;
--把kecheng表中的name列 行转列
select course,
sum(case name when '张三' then score end) 张三,
sum(case name when '李四' then score end) 李四,
sum(case name when '王五' then score end) 王五
from kecheng
group by course;
--把demo表中的name列 id列 分别 行转列
select * from demo;
select id,
sum(case name when '苹果' then nums end) 苹果,
sum(case name when '橘子' then nums end) 橘子,
sum(case name when '葡萄' then nums end) 葡萄,
sum(case name when '芒果' then nums end) 芒果
from demo
group by id
order by id;
select name,
sum(case id when 1 then nums end) 第一季度,
sum(case id when 2 then nums end) 第二季度,
sum(case id when 3 then nums end) 第三季度,
sum(case id when 4 then nums end) 第四季度
from demo
group by name;
列转行
语法：union all
--把demo1列转成行
select *
from demo1;
select id ,'苹果'name,苹果 nums from demo1
union all
select id ,'橘子'name,橘子 nums from demo1
union all
select id ,'芒果'name,芒果 nums from demo1
union all
select id ,'葡萄'name,葡萄 nums from demo1
--把demo2列转成行
select* from demo2;
select 1 id ,name,一季度 nums from demo2
union all
select 2 id ,name,二季度 nums from demo2
union all
select 3 id ,name,三季度 nums from demo2
union all
select 4 id ,name,四季度 nums from demo2
order by name;
行列转换函数
行转列函数
语法：pivot(被聚合的列 for 行转列的列 in(列中值1，值2...))
demo表name 行转列
select *
from demo
pivot(sum(nums) for name in('苹果','芒果','葡萄','橘子'))
列转行函数
语法：unpivot(聚合列的新列名 for 列转行的新列名 in(字段名1，名2...))
demo1 列转行
select*
from demo1
unpivot(nums for name in(苹果,芒果,葡萄,橘子))
树形函数（层级函数）
- 只能在Oracle中使用
- 树形查询通常由根节点 父节点 子节点 叶子节点 组成
- 根节点：当前节点之上没有节点的节点
- 父节点：当前节点之下有节点的节点
- 子节点：当前节点之上有节点的节点
- 叶子节点：当前节点之下没有节点的节点
- level 代表节点的深度
--语法
select 列[level]...
from 表
[where 条件]
[start with 条件] --开始的条件 不写默认每一个都查
connect by prior 儿子列 = 父亲列 --查询上级
父亲列 = 儿子列 --查询下级
order siblings by 儿子的某个属性 --同一级别之间的排序
-----------------------案例-------------------------------
--查询emp 的层次结构 king开始查下级
select ename,empno,job,mgr,sal,level
from emp
start with ename='KING'
connect by prior empno=mgr
order siblings by sal desc;
--查询ADAMS的上级
select ename,empno,job,mgr,sal,level
from emp
start with ename='ADAMS'
connect by prior mgr = empno
order siblings by sal desc;
--查询king的下属，和下属的下属
/*select ename,empno,job,mgr,sal,level
from emp
where level between 2 and 3
start with ename='KING'
connect by prior empno=mgr
order siblings by sal desc;*/
--不符合逻辑，巧合
select *
from (select e.*,level ll
from emp e
start with ename='KING'
connect by prior empno=mgr
)
where ll in(2,3);
伪列（面试）
可以像其他的列一样被查询 但是不能修改和删除
level
- 必须从1开始，并且是连续的
- 必须和connect by 配合使用
- 主要用法：
- 树形查询，返回节点深度
- 生成多条记录
select level
from dual
connect by level<=100;
--查询今年所有的星期五 日期格式展示
select *
from (select (trunc(sysdate,'yyyy') - 1 + level) lv
from dual
connect by level<=365
)
where to_char(lv,'d')=6;
select to_char(sysdate,'yyyy- ')
from dual;
rownum
- select 中使用可以将数据进行不重复的排序
- 只能用在where 中查询
- 必须从1开始 并且是连续的
--查询emp表的前五行
select *
from emp
where rownum<=5;
--查询emp表的前六到时行
select *
from (select e.*，rownum ro
from emp e
)
where ro between 6 and 10;
--rownum 主要用分页查询
select *
from (select rownum rm,e.*
from emp e
where rownum<=3 * &页
)
where rm >= 3 * &页 -2;
-- & 宏代换
rowid
- 去除重复数据
- 给每行数据一个独有的编码
select rowid
from emp;
数据库语言
--不同数据库查询前十条数据
select * from emp limit 10；--pg、mpp、mysql、bd2 语言
select * from emp where rownum <=10; --Oracle语言
DQL
数据库的查询语言：简单查询、条件查询、模糊查询、树形查询、子查询...
DCL
数据库控制语言：
赋权：grant 权限 to 用户
回收权限：revoke 权限 from 用户
权限有：all,select,delete,update
grant select on emp to job; --将emp表的查询权限给到job
grant dba to scott; --将最高权限给到scott
DDL
数据库定义语言：
创建表 视图 序列 create table/view/sequence
删除表 视图 序列 drop table/view/sequence
修改表 视图 序列 alter table/view/sequence
表 table
创建
- 复制表
- 表名的命名规范：
驼峰式：每个单词的首字母大写
- 表名的命名规范：
下划线：每个单词之间用下划线隔开
- 只要求表结构时，where 后加一个不成立的条件
语法：create table 表名 as select 语句;
--创建emp2表数据同emp
create table emp2 as select * from emp;
select * from user_tables;--查询当前用户下所有的表
--创建emp10,emp20,emp30 数据同10部门20部门30部门
create table emp10 as select * from e mp where deptno = 10;
create table emp20 as select * from emp where deptno = 20;
create table emp30 as select * from emp where deptno = 30;
select * from emp10;
--创建一张空表格式同emp
create table emp_null as select * from emp where 1=2;
select * from emp_null;
- 手动创建
--语法
create table 表名(列1 类型长度 [约束],
列2 类型长度 [约束]
...)
--创建一张学生表
create table stu (
stu_name varchar2(8),
stu_id number,
stu_sex char(2),
stu_buth date,
stu_pic blob);
select * from stu;
修改列
- 修改非空 默认值 列的类型长度
- 注意事项
- 修改列的类型长度时 列必须是空的
- 列改为可为空 该列必须现在是非空
- varchar2的长度可以加长 如果要减短 则修改的值必须不小于列中最长值的长度
- number要修改长度 必须此列为空
alter table 表名 modify 列名 类型长度 [default 值][not null/null];
alter table stu1 modify stu_id number(10) default 5;
增加列
alter table 表名 add(列名1 类型长度,列名2 类型长度...);
alter table stu add(aihao varchar2(100),a number);
列的重命名
alter table 表名 rename column 旧名字 to 新名字;
alter table stu rename column a to b;
删除列
alter table 表名 drop (列1，列2...); --删除多个列
alter table 表名 drop column 列名; --删除一个列
alter table stu drop(aihao);
alter table stu drop(aihao,b);
表的重命名
rename 旧名字 to 新名字;
rename stu to student;
清空表的数据
truncate table 表名;
删除表
drop table 表名[purge];
加上purge 是物理删除
不加是逻辑删除 就是把表放进了回收站(recyclebin)
查看回收站
select * from user_recyclebin;
从回收站还原
从回收站返回（闪回）删除的表
flashback table 表名 to before drop [rename to 新表名];
flashback table c to before drop rename to cccccc;
select * from user_tables;--查看当前用户下所有的表
约束 constraint
- 作用
- 用来确保数据满足业务的需求 保障数据的合理性 合法性
- 分类
|主键约束|primary key|简写pk|唯一且非空|
|:----|:----|:----|:----|
|外键约束|foreign key|简写fk| |
|非空约束|not null|简写nn|放所有约束最后|
|唯一约束|unique|简写un|可以有空|
|检查约束|check|简写ck|给他一个条件，检查是哪一个|
|默认值|default| | |
非列模式 不带约束名
create table stu1 (
stu_name varchar2(8) not null,
stu_id number primary key,
stu_sex char(2) check(stu_sex in('男','女')),
stu_bith date default date '2020-4-4',
stu_card char(18) unique,
stu_pic blob);
select * from stu1;
stu_card char(18) unique not null --一个列可以有多个约束 非空约束写在最后
非列模式 带约束名
create table stu2 (
stu_name varchar2(8) constraint nn_name not null,
stu_id number constraint pk_id primary key,
stu_sex char(2) constraint ck_sex check(stu_sex in('男','女')),
stu_bith date default date '2020-4-4',
stu_card char(18)constraint un_cord unique
constraint nn_cord not null,
stu_pic blob);
select * from stu2;
列模式 不带约束名
create table stu3 (
stu_name varchar2(8),
stu_id number,
stu_sex char(2),
stu_bith date,
stu_card char(18),
stu_pic blob,
primary key(stu_id),
check (stu_name is not null),
check (stu_sex in('男','女')),
unique(stu_card));
注：默认值不能列模式 非空用列模式需要换成检查
外键约束和复合主键 只能由列模式创建
复合主键：列模式，
primary key(列1，列2...)
列模式 带约束名
create table stu4 (
stu_name varchar2(8),
stu_id number,
stu_sex char(2),
stu_bith date,
stu_card char(18),
stu_pic blob,
constraint pk_1 primary key(stu_id),
constraint ck_1 check (stu_name is not null),
constraint ck_2 check (stu_sex in('男','女')),
constraint un_2 unique(stu_card));
外键约束
外键约束需要两张表 创建外键约束的表是子表 参考表是父表
创建外键约束的列叫外键列 参考表的参考列必须是父表的主键列
作用：外键列中的值必须是父表主键列中存在的
--语法：
[constraint fy_name]foreign key(外键列) references 父表(主键列) [on delete cascade/on delete set null]
on delete cascade:级联删除
on delete set null：级联删除为空
不写 默认不指定级联删除 no action
fy_name 为约束名
--创建emp_1 列deptno 上创建外键约束 参考dept表中deptno
create table emp_1(
ename varchar2(10),
deptno number,
foreign key(deptno) references dept(deptno)
);
- 删除父表的数据时：
- 如果没有指定级联删除 必须要先删除子表的相关项 再删除父表的对应项
- 如果指定了级联删除 子表的相关项和父表的对应行一起被删除
- 如果制定了级联删除为空 父表的对应行被删除 子表的相关项还存在 外键列的值为空
- 插入/更新到子表中外键列的数据必须是父表的主键列中存在的
- 插入到父表的主键列中的数据没有限制 只要满足主键约束即可
增加约束
alter table 表名 add[constraint 约束名] 约束类型(列);
alter table stu add primary key(stu_id);
约束重命名
alter table 表名 rename constraint 旧名字 to 新名字;
alter table stu rename constraint SYS_C0011199 to pk_111;
删除约束
alter table 表名 drop constraint 约束名;
alter table stu drop constraint pk_111;
视图 view
- 视图是一张虚拟表 其内容有查询定义
- 视图的优缺点
- 优点：不占空间还安全
- 缺点：性能差，修改受限制
- 注意事项
- 视图名一般 v_ 开头
- 修改视图相当于修改表
- 工作中一般不允许通过视图来修改数据
- 视图和表的区别：
- 视图是编译好的SQL语句 表不是
- 视图是没有实际的物理记录 表有
- 视图是窗口 表是内容
- 表占用物理的存储空间 视图不占用物理的存储空间 只是逻辑概念存在 表可以及时进行修改 视图只能由创建语句来修改
- 表是内模式 视图是外模式
- 视图是查看数据表的方法 可以查询数据表中某些字段构成的数据 只是SQL语句的集合 从安全角度来讲 视图可以不给用户直接接触表就可以知道表数据的方法
- 表是全局模式中的表 是实表 视图是局部模式的表 是虚表
- 视图的创建和删除影响视图本身 不影响对应的基表
创建
create [or replace] view 视图名 as select 语句[with read only];
加上or replace 如果视图名已存在 会覆盖之前的视图
不加or replace 如果视图名已存在 会报错(名称已有现有对象占用)
不加[with read only]只是创建普通视图 视图可以被修改
加上[with read only]是创建只读视图 视图不能被修改
--创建v_emp_20 数据同20部门
create view v_emp_20 as select * from emp where deptno=20;
查看
select * from v_emp_20;
select info from v_emp_20;--查视图中的信息 ==select *
删除
drop view 视图名;
drop view v_emp_20;
序列 sequence
- 数据库中存放等差数列的表 主要是用来提供主键值
创建
--语法：
create sequence 序列名
[start with 数] --从几开始，默认是1
[maxvalue 数] --最大到几 默认99999...(28个9)
[minvalue 数] --最小到几 默认是1
[increment by 数] --间隔 等差 不写默认是1
[cache 数] --缓冲值 默认20 循环值不能大于本轮循环产生的数量 cache<maxvalue-minvalue/increment
[cycle] --是否循环 默认不循环
--创建一个序列s1 从6开始
create sequence s1
start with 6
maxvalue 50
minvalue 2
increment by 2
cache 10
cycle;
使用
序列名.nextval --先执行
序列名.currval --后执行
select s1.nextval,s1.currval from dual;
删除
drop sequence 序列名;
drop sequence s6;
DML
数据库操作语言
插入 insert 更新 update 删除 delete 合并 merge
插入数据
批量插入
--语法：
insert into 表名(列) select 语句;
--创建一张空表 emp_11 格式同emp
create table emp_11 as select * from emp where 1=2;
insert into emp_11 select * from emp where deptno = 10;
insert into emp_11(empno,deptno,sal)
select empno,deptno,sal from emp where deptno=20;
select *from emp_11;
手动插入
create table aa(id number ,name varchar2(20));
insert into aa values(1,'a');
insert into aa values(s1.nextval,'a');
select * from aa;
更新数据
update 表名 set JSC_JSRYJSC_JSRY 列1=值1,列2=值2...[where 条件]
不加where 条件会对全表进行修改
--创建一个emp_1 数据同emp
drop table emp_1;
create table emp_1 as select * from emp;
--把emp_1中的10部门的员工职位 改为小写，工资增加888
update emp_1 set job=lower(job),sal=sal+888 where deptno=10;
select * from emp_1;
drop table emp_1;
create table emp_1 as select * from emp;
update emp_1 set ename=lower(ename),sal=sal*4 where deptno=10;
update emp_1 set ename=initcap(ename),sal=sal/4 where deptno=20;
update emp_1 set ename=lpad(ename,lengthb(ename)+1,'*'),sal=power(sal/100,3) where deptno=30;
update emp_1 set comm=nvl(comm,0)+5000;
select * from emp_1;
--如果现在的工资（emp_1）低于原来的工资（emp）则把职位更新为淘汰
update emp_1 set job='淘汰' where sal<(select sal
from emp
where emp.empno=emp_1.empno);
删除数据
delete from 表 [where 条件];
不加where 表示对全表数据删除
删除emp_1 中1981年入职的员工信息
delete from emp_1 where to_char(hiredate,'yyyy')=1981;
delete from emp_1;
- delete 和 truncate 的区别：（重点、面试）
-
truncate 是一个DDL 不能回滚
delete 是DML 可以回滚 -
truncate 不能加条件
delete 后面可以加条件 -
truncate 可以降低高水位线 --一下子整体删除
delete 不可以可以降低高水位线 --一条一条的删 -
truncate 速度快 delete 速度慢
合并数据
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...) values(b.列1/一个值1,b.列2/一个值2...)
--创建一个a、b表
--a中插入emp的empno ename deptno
--b中插入emp的empno lower(ename) deptno 部门10改为40
drop table b;
create table a as select empno,ename,deptno from emp;
create table b as select empno,lower(ename) ename,decode(deptno,10,40,deptno) deptno from emp;
update b set deptno=40 where deptno=10;
merge into a
using b
on(a.empno=b.empno and a.deptno=b.deptno)
when matched then update set a.ename=b.ename
when not matched then insert values(b.empno,b.ename,b.deptno)
select * from a;
进阶
--合并数据 merge 进阶
merge into 表a --进行插入更新的表
using 表b --参考表
on 条件 --关联条件
when matched then --匹配成功
update set a.列1=b.列1/一个值1,a.列2=b.列2/一个值2...
[where 更新条件]
[delete where 删除条件]
when not matched then --没有匹配成功
insert(a.列1,a.列2...) values(b.列1/一个值1,b.列2/一个值2...)
[where 插入条件]
--创建aa和bb表
drop table bb;
create table aa as select empno,ename,deptno,sal,job from emp;
create table bb as select empno,lower(ename) ename,decode(deptno,10,40,deptno) deptno,sal,job from emp;
--参考bb对aa进行插入更新，插入和更新的条件是工资大于2000，把工作是ANALYST的删除
merge into aa
using bb
on(aa.empno=bb.empno and aa.deptno=bb.deptno)
when matched then
update set aa.ename=bb.ename
where aa.sal>2000
delete where aa.job='ANALYST'
when not matched then
insert values(bb.empno,bb.ename,bb.deptno,bb.sal,bb.job)
where bb.sal>2000;
select * from bb;
查找并删除表中重复数据
--如何查找表中的重复数据(重点 面试)
drop table emp_1;
create table emp_1 as select * from emp;
insert into emp_1 select * from emp where deptno=20;
--方法一 排序
select empno,ename,job,mgr,hiredate,sal,comm,deptno
from(select e.*,
row_number()over(partition by empno order by sal) ro
from emp_1 e
)
where ro=2;
--方法二 rowid
select *
from emp_1
where rowid not in(select min(rowid)
from emp_1
group by empno
);
--方法三 分组
select *
from emp_1
where empno in(select empno
from emp_1
group by empno
having count(1)>1);
--删除重复数据
1.创建一个新表 数据同emp_1去重后的数据
create table emp_1_b as select distinct * from emp_1;
2.清空emp_1
truncate table emp_1;
3.把备份的数据插回到emp_1中
insert into emp_1 select * from emp_1_b;
4.删除新表
drop table emp_1_b;
TPL/TCL
事务处理/控制语言
提交 commit 回滚 rollback
- 事务
- 狭义：从当前的TPL到下一个TPL之间的所有的DML（如果执行DDL DDL之前的DML会自动提交）
- 广义：任何一次操作 要么成功 要么失败
- 事务的四个特性：（重点 面试）
- 原子性：事务包含的操作要么全部成功，要么全部失败回滚 成功必须完全应用到数据库 失败不会对数据库造成影响
- 一致性：事务执行前和执行后状态必须是一致的
- 隔离性：当多个事务并发访问数据库时 数据库为每个用户开启的事务不受其他事务操作的影响 多个事务之间时相互隔离的
- 持久性：一个事务提交之后 对数据库的改变是永久的 即使故障也不会改变
SQL必知必会—管理事务处理_Cacra的博客-CSDN博客_sql事务处理
PL/SQL编程
- 在SQL语句基础上增加了一些过程化的语句
- 过程化的语句：类型定义、判断、循环、游标...
- 匿名块：变量、赋值、判断、循环....
- 有名块：
- 自定义函数 function
- 存储过程 procedure
- 包 package
- 触发器 trigger
- 定时器
匿名块
[declare] 声明的部分
声明的内容;
begin
要执行的语句;
[exception] --异常
要执行的语句;
end;
注意：每个部分的语句后面都要加 ; end后面也要加;
多写注释
赋值使用的符号 := 判断相等的符号 =
声明部分
--声明变量的语法：
变量名 类型长度;
v_a number(12);
注：变量名以v开头 不要和列名 关键字重名
一个变量一个时刻只能接受一个值
- 类型
- 数值型number int
- 字符型char varchar varchar2
- 日期型date timestamp
- 布尔型 Boolean 只返回对或者错 不能打印 用来做判断
- 表名.列%type 和某张表某个列的类型长度一致 例如：emp.deptno%type
- 表%rowtype 和某张表的表结构一致 例如：emp%rowtype
- %type 的特性（面试 重点）
- 所引用的数据库列的类型可以不知道
- 所引用的数据库列的类型可以实时改变
打印：
- dbms_output.put_line(值) --换行打印
- dbms_output.put(值) -- 不换行打印（必须和换行打印配合使用)
- 一个或多个打印语句时 最后一条必须为换行打印
变量的赋值
- 直接赋值
declare
v_1 number(10) := 123; --在生命的时候赋值
v_2 number(10);
begin
v_2:=999;
dbms_output.put(v_1);--不换行打印
dbms_output.put_line(v_2);--换行打印
dbms_output.put(v_1);
dbms_output.put(v_2);
dbms_output.put_line(v_1);
dbms_output.put_line(v_2);
dbms_output.put_line(v_1);
dbms_output.put(v_2);
end;
- 变量的值赋给变量
declare
v_1 number(10):=123;
v_2 number(10);
begin
v_2:=v_1;
dbms_output.put(v_1);--不换行打印
dbms_output.put_line(v_2);--换行打印
end;
- 变量可以多次赋值，新值会覆盖旧值
declare
v_1 date:=date'2022-6-28';
begin
dbms_output.put_line(v_1);
v_1:=sysdate;
dbms_output.put_line(v_1);
end;
- 变量的赋值可以计算
declare
v_1 number:=2;
v_2 number:=3;
v_3 number;
begin
v_3:=power(v_1,v_2);
dbms_output.put_line(v_3);
end;
- 变量的赋值可以使用函数
declare
v_1 varchar(20):='i like apple';
v_2 varchar(20):=upper(v_1);
v_3 varchar(20):=initcap(v_1);
begin
dbms_output.put_line(v_1);
dbms_output.put_line(v_2);
dbms_output.put_line(v_3);
end;
--v_y 是当前时间的年 v_m是当前时间的月 v_d 是当前时间的日 打印v_y 的v_m次方开v_d次方
declare
v_y number:=to_number(to_char(sysdate,'yyyy'));
v_m number:=to_number(to_char(sysdate,'mm'));
v_d number:=to_number(to_char(sysdate,'dd'));
v_1 number;
begin
v_1:=power(power(v_y,v_m),1/v_d);
dbms_output.put_line(v_1);
end;
- 键盘直接赋值
--键盘输入自己的生日，查询自己活了多少年，多少月，多少天
declare
v_date date:=date'&生日';
v_s date:=sysdate;
v_y varchar(50);
begin
dbms_output.put_line('输入你的生日：'||v_date);
select floor(months_between(v_s,v_date)/12)||'年'||
floor(mod(months_between(v_s,v_date),12))||'月'||
floor(v_s-add_months(v_date,floor(months_between(v_s,v_date))))||'天'
into v_y
from dual;
dbms_output.put_line('你的年龄是'||v_y);
end;
- select into赋值
select into 可以是多列 但是只能单行
--打印SMITH的工资和职位
DECLARE
v_s number;
v_j varchar2(20);
begin
select sal,job into v_s,v_j
from emp
where ename='SMITH';
DBMS_OUTPUT.PUT_LINE('SMITH的工资是:'||v_s||'职位是:'||v_j);
end;
--键盘输入一个年份 输出这个年份是平年还是闰年
declare
v_y number:=&年份;
v_n varchar2(20);
begin
select case when
to_char(to_date(v_y||'-12-31','yyyy-mm-dd'),'ddd')=366
then '闰年'
else '平年'
end
into v_n
from dual;
dbms_output.put_line(v_n);
end;
-----------------------------
declare
v_y number:=&年份;
v_n varchar2(20);
begin
select case when mod(v_y,4)=0 and mod(v_y,100)<>0
or mod(v_y,400)=0
then '闰年'
else '平年'
end
into v_n
from dual;
dbms_output.put_line(v_n);
end;
--打印emp表中KING的姓名 工资 职位
declare
v_e emp%rowtype;
begin
select * into v_e
from emp
where ename='KING';
dbms_output.put_line(v_e.ename||' '||v_e.sal||' '||v_e.job);
end;
--打印dept中10部门的部门名称和部门所在地
declare
v_d dept.dname%type;
v_l dept.loc%type;
begin
select dname,loc into v_d,v_l
from dept
where deptno=10;
dbms_output.put_line(v_d||' '||v_l);
end;
- returning into 把DML的值赋值给变量
只能单行可以多列
--创建emp_1表 数据同emp drop table emp_1;
create table emp_1 as select * from emp;
--删除SMITH的行 打印删除了的姓名和工资
--插入一条数据 打印插入了的姓名和工资
--更新KING的名字为小写 打印更新的姓名和工资
declare
v_m emp.ename%type;
v_s emp.sal%type;
begin
--------删除------------
delete from emp_1 where ename='SMITH'
RETURNING ENAME,SAL INTO V_M,V_S;
dbms_output.put_line('删除了'||v_m||' '||v_s);
-------插入----
insert into emp_1(ename,sal) values('abc',12000)--手动插入
returning ename,sal into v_m,v_s;
dbms_output.put_line('插入了'||v_m||' '||v_s);
--------更新----------
update emp_1 set ename=lower(ename) where ename='KING'
returning ename,sal into v_m,v_s;
dbms_output.put_line('更新了'||v_m||' '||v_s);
END;
判断
- case when
语法：
case when 条件1 then 要执行的语句;
when 条件2 then 要执行的语句;
...
[else 要执行的语句;]
end case;
/*键盘输入一个字符 如果是大写 打印大写字母
小写 打印小写字母
否则打印不认识*/
declare
v_1 varchar(20):='&字母';
begin
case when v_1 between 'A' and 'Z' then
dbms_output.put_line('大写字母');
when v_1 between 'a' and 'z' then
dbms_output.put_line('小写字母');
else dbms_output.put_line('不认识');
end case;
end;
sql中的case when和pl/sql中case when的区别：
1. sql中的case when可以加列 plsql中只能加条件
2. sql中的case when不加分号 plsql中then执行的语句都要加分号
3. sql中的case when以end结尾 plsql中的以end case结尾
4. sql中的case when不能加DML plsql中可以加dml
- if判断
if 条件1 then 执行的语句;
elsif 条件2 then 执行的语句;
...
[else 执行的语句;]
end if;
--键盘输入一个数字 大于0打印正数 小于0打印负数 0打印零
declare
v_s number:=&数字;
begin
if v_s<0 then
dbms_output.put_line('负数');
elsif v_s>0 then
dbms_output.put_line('正数');
else dbms_output.put_line('零');
end if;
end;
/*键盘输入一个员工编号 如果工资大于3000打印有钱人
大于2000打印一般般
大于1000打印还凑合
小于1000打印可怜人*/
declare
v_e number:=&员工编号;
v_s number;
begin
select sal into v_s
from emp
where empno=v_e;
if v_s>3000 then
dbms_output.put_line('有钱人');
elsif v_s>2000 then
dbms_output.put_line('一般般');
elsif v_s>1000 then
dbms_output.put_line('还凑合');
elsif v_s<1000 then
dbms_output.put_line('可怜人');
end if;
end;
循环
- 普通循环
语法：
loop
执行的语句;
exit when 退出的条件;
[执行的语句;]
end loop;
注：设置一个自增变量 防止出现死循环
--循环打印1 2 3 4 5 6 7 8 9 10
declare
v_a number:=1;
begin
loop
dbms_output.put(v_a||' ');
v_a:=v_a+1;
exit when v_a=11;
end loop;
dbms_output.put_line(' ');
end;
--循环打印1+2+3+4..10的和 55
declare
v_1 number:=1;
v_2 number:=0;
begin
loop
v_2:=v_2+v_1;
v_1:=v_1+1;
exit when v_1=11;
end loop;
dbms_output.put_line(v_2);
end;
--打印1+2+3+4...+10=55这个式子
declare
v_1 number:=1;
v_2 number:=0;
begin
loop
dbms_output.put(v_1||'+');
v_2:=v_2+v_1;
v_1:=v_1+1;
exit when v_1=10;
end loop;
dbms_output.put_line('10='||(v_2+10));
end;
- while循环
语法：
while 条件 --进入循环的条件
loop
执行的语句;
[exit when 中途退出的条件;]
end loop;
--循环打印1 2 3 4 5 6 7 8 9 10
declare
v_a number:=1;
begin
while v_a<=10
loop
dbms_output.put(v_a||' ');
v_a:=v_a+1;
end loop;
dbms_output.put_line(' ');
end;
- for循环
语法：
for 变量 in [reverse] 小值..大值
loop
要执行的语句;
[exit when 条件;]
end loop;
--循环打印1 2 3 4 5 6 7 8 9 10
begin
for i in 1..10
loop
dbms_output.put(i||' ');
end loop;
dbms_output.put_line('');
end;
--循环打印1+2+3+4..10的和 55
declare
v_1 number:=0;
begin
for i in 1..10
loop
v_1:=v_1+i;
end loop;
dbms_output.put_line(v_1);
end;
--打印1+2+3+4...+10=55这个式子
declare
v_1 number:=0;
begin
for i in 1..9
loop
dbms_output.put(i||'+');
v_1:=v_1+i;
end loop;
dbms_output.put_line('10=55');
end;
--打印直角三角形
*
* *
* * *
* * * *
* * * * *
begin
for i in 1..5 --打印的层数
loop
for a in 1..i--每层打印星星的数量
loop
dbms_output.put('* ');--每一层的星星 不换行
end loop;
dbms_output.put_line('');--换行
end loop;
end;
游标
- 游标是一种能从多条记录的结果集中每次提取一条记录的机制
- 可以充当指针 能遍历结果集中所有的行 但是每次只能指向一行
- 游标提供了在逐行的基础上操作表中数据的方法
- 游标的结果集是由查询语句定义
分类
- 静态游标：
- 显式游标：
- 普通的
- 带参数的
- 隐式游标
- 显式游标：
- 动态游标：
- 强类型 --了解
- 弱类型 --了解
- 动态游标 --重点
静态游标和动态游标的区别：
- 静态游标的结果集是固定的 中间不能打开
- 动态的结果集可以改变的 每次打开都可以更换结果集
显式游标和隐式游标的区别：
- 显式游标的结果集在DECLARE部分声明 中途不能改变
- 隐式游标的结果集不用声明 且游标无需打开 关闭 这些由系统自动完成
静态游标：显式游标：普通的
语法：
declare
cursor 游标名 is select 语句;
begin
open 游标名;
...
close 游标名;
end;
--打印dept表的数据
declare
cursor cur_1 is select * from dept;
v_1 dept%rowtype;
begin
open cur_1;
fetch cur_1 into v_1;--提取记录给了变量
dbms_output.put_line(v_1.dname||' '||v_1.deptno
||' '||v_1.loc);
fetch cur_1 into v_1;--提取记录给了变量
dbms_output.put_line(v_1.dname||' '||v_1.deptno
||' '||v_1.loc);
fetch cur_1 into v_1;--提取记录给了变量
dbms_output.put_line(v_1.dname||' '||v_1.deptno
||' '||v_1.loc);
fetch cur_1 into v_1;--提取记录给了变量
dbms_output.put_line(v_1.dname||' '||v_1.deptno
||' '||v_1.loc);
fetch cur_1 into v_1;--提取记录给了变量
dbms_output.put_line(v_1.dname||' '||v_1.deptno
||' '||v_1.loc);
close cur_1; --关闭游标
end;
游标的四个属性：
- cur_name%isopen 判断游标是否打开(对,错)--布尔型
- cur_name%found 判断游标是否有值(对,错)--布尔型
- cur_name%notfound 判断游标是否没值(对,错)--布尔型
- cur_name%rowcount 返回游标指针指过的行数 --数值型
游标的循环
普通循环
- 普通循环的步骤：
begin
open->loop->提取->cur_name%notfound->打印->end loop->close
----打印dept表的数据 普通循环
declare
cursor cur_1 is select * from dept;
v_1 dept%rowtype;
begin
open cur_1;
loop
fetch cur_1 into v_1;
exit when cur_1%notfound;--退出的条件
dbms_output.put_line(v_1.dname||' '||v_1.deptno
||' '||v_1.loc);
end loop;
close cur_1;
end;
while循环
while循环的步骤：
begin
open->提取->while+cur_name%found->loop->打印->提取->end loop
->close
declare
cursor cur_1 is select * from dept;
v_1 dept%rowtype;
begin
open cur_1;
fetch cur_1 into v_1;
while cur_1%found
loop
dbms_output.put_line(v_1.dname||' '||v_1.deptno
||' '||v_1.loc);
fetch cur_1 into v_1;
end loop;
close cur_1;
end;
静态游标 显式游标 带参数的
declare
cursor cur_2(v_1 number,v_2 varchar2)is
select * from emp where deptno=v_1 and job=v_2;
v_e emp%rowtype;
begin
open cur_2(10,'MANAGER');--打开游标的时候赋参
loop
fetch cur_2 into v_e;
exit when cur_2%notfound;
dbms_output.put_line(v_e.ename||' '||v_e.deptno||' '||v_e.job);
end loop;
close cur_2;
-----------------------
open cur_2(20,'MANAGER');
loop
fetch cur_2 into v_e;
exit when cur_2%notfound;
dbms_output.put_line(v_e.ename||' '||v_e.deptno||' '||v_e.job);
end loop;
close cur_2;
end;
--打印工作是MANAGER的姓名和工作
DECLARE
CURSOR CUR_1(V_1 VARCHAR2) IS
SELECT ENAME,JOB FROM EMP WHERE JOB=V_1;
V_A EMP.ENAME%TYPE;
V_J EMP.JOB%TYPE;
BEGIN
OPEN CUR_1('MANAGER');
LOOP
FETCH CUR_1 INTO V_A,V_J;
EXIT WHEN CUR_1%NOTFOUND;
dbms_output.put_line(v_A||' '||v_J);
END LOOP;
CLOSE CUR_1;
END;
--打印工作是CLERK的姓名和佣金
DECLARE
CURSOR CUR_1(V_1 VARCHAR2) IS
SELECT ENAME,COMM FROM EMP WHERE JOB=V_1;
V_A EMP.ENAME%TYPE;
V_J EMP.COMM%TYPE;
BEGIN
OPEN CUR_1('CLERK');
LOOP
FETCH CUR_1 INTO V_A,V_J;
EXIT WHEN CUR_1%NOTFOUND;
dbms_output.put_line(v_A||' '||v_J);
END LOOP;
CLOSE CUR_1;
END;
--打印工作是ANALYST的姓名和入职日期
DECLARE
CURSOR CUR_1(V_1 VARCHAR2) IS
SELECT ENAME,HIREDATE FROM EMP WHERE JOB=V_1;
V_A EMP.ENAME%TYPE;
V_J EMP.HIREDATE%TYPE;
BEGIN
OPEN CUR_1('ANALYST');
LOOP
FETCH CUR_1 INTO V_A,V_J;
EXIT WHEN CUR_1%NOTFOUND;
dbms_output.put_line(v_A||' '||v_J);
END LOOP;
CLOSE CUR_1;
END;
静态游标：隐式游标
- 不用声明 不用打开 不用赋值 不用关闭
- 常见的方式有 delete update insert select .. into 赋值
隐式游标的四个属性：
- sql%isopen 判断游标是否打开 永远返回错
- sql%found 判断指针是否有值 如果最近一次有值返回对 否则返回错
- sql%notfound 判断指针是否没值 最近一次没有 返回对
- sql%rowcount 返回指针指过的行数 返回最近一次的记录数
--创建emp1数据同emp
drop table emp1;
create table emp1 as select * from emp;
declare
v_e emp%rowtype;
begin
-------更新10部门的数据-------
update emp1 set ename=lower(ename) where deptno=10;
if sql%found then
dbms_output.put_line('真的更新了');
else dbms_output.put_line('没有更新了');
end if;
dbms_output.put_line('更新了'||sql%rowcount||'行');
------------把20部门的数据删除-----
delete from emp1 where deptno=20;
if sql%found then
dbms_output.put_line('真的删除了');
else dbms_output.put_line('没有删除了');
end if;
dbms_output.put_line('删除了'||sql%rowcount||'行');
-----------插入数据emp--------------------
insert into emp1 select * from emp;
if sql%found then
dbms_output.put_line('真的插入了');
else dbms_output.put_line('没有插入了');
end if;
dbms_output.put_line('插入了'||sql%rowcount||'行');
-----------select into 赋值------------
select * into v_e from emp where ename='KING';
if sql%found then
dbms_output.put_line('真的赋值了');
else dbms_output.put_line('没有赋值了');
end if;
dbms_output.put_line('赋值了'||sql%rowcount||'行');
------判断游标是否打开----------
if sql%isopen then
dbms_output.put_line('打开了');
else
dbms_output.put_line('没有打开');
end if;
end;
动态游标-强类型游标
- 可以更换结果集，不能更换结果集的表格式
CREATE TABLE emp_a AS SELECT * FROM emp;
CREATE TABLE emp_b AS SELECT * FROM emp;
CREATE TABLE emp_c AS SELECT * FROM emp;
DECLARE
TYPE cur_1_ref IS REF CURSOR RETURN emp%ROWTYPE;
--声明一个强类型
cur_1 cur_1_ref;--把强类型赋给游标
v_emp emp%ROWTYPE;
BEGIN
dbms_output.put_line('-----emp_a中10部门的员工姓名和部门编号-----');
OPEN cur_1 FOR SELECT * FROM emp_a WHERE deptno=10;
LOOP
FETCH cur_1 INTO v_emp;
EXIT WHEN cur_1%NOTFOUND;
dbms_output.put_line(v_emp.ename||' '||v_emp.deptno);
END LOOP;
CLOSE cur_1;
dbms_output.put_line('-----emp_b中工作是销售的员工姓名和工作-----');
OPEN cur_1 FOR SELECT * FROM emp_b WHERE job='SALESMAN';
LOOP
FETCH cur_1 INTO v_emp;
EXIT WHEN cur_1%NOTFOUND;
dbms_output.put_line(v_emp.ename||' '||v_emp.job);
END LOOP;
CLOSE cur_1;
dbms_output.put_line('-----emp_c中1981年入职的员工姓名和入职日期-----');
OPEN cur_1 FOR SELECT * FROM emp_c WHERE to_char(hiredate,'yyyy')=1981;
LOOP
FETCH cur_1 INTO v_emp;
EXIT WHEN cur_1%NOTFOUND;
dbms_output.put_line(v_emp.ename||' '||v_emp.hiredate);
END LOOP;
CLOSE cur_1;
END;
动态游标-弱类型游标
- 弱类型游标没有return,中途可以换结果集,可以换表格式
DECLARE
TYPE cur_1_ref IS REF CURSOR;--声明一个弱类型
cur_1 cur_1_ref;--把弱类型赋给游标
v_num NUMBER;
v_char VARCHAR2(20);
BEGIN
dbms_output.put_line('--每种职位的人数--');
OPEN cur_1 FOR SELECT job,COUNT(1) FROM emp GROUP BY job;
LOOP
FETCH cur_1 INTO v_char,v_num;
EXIT WHEN cur_1%NOTFOUND;
dbms_output.put_line(v_char||' '||v_num);
END LOOP;
CLOSE cur_1;
dbms_output.put_line('--每种每个人的工资等级--');
OPEN cur_1 FOR SELECT ename,grade FROM emp e JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal;
LOOP
FETCH cur_1 INTO v_char,v_num;
EXIT WHEN cur_1%NOTFOUND;
dbms_output.put_line(v_char||' '||v_num);
END LOOP;
CLOSE cur_1;
END;
--用弱类型游标,打印emp表名字包含A的员工姓名和人数,dept表部门名称包含A的部门名称和部门数
DECLARE
TYPE cur_1_ref IS REF CURSOR;
cur_1 cur_1_ref;
v_num NUMBER;
v_char VARCHAR2(20);
BEGIN
dbms_output.put_line('<<emp表名字包含A的员工姓名和人数>>');
OPEN cur_1 FOR SELECT ename,COUNT(1)OVER() FROM emp WHERE ename LIKE '%A%';
LOOP
FETCH cur_1 INTO v_char,v_num;
EXIT WHEN cur_1%NOTFOUND;
dbms_output.put_line(v_char||' '||v_num);
END LOOP;
CLOSE cur_1;
dbms_output.put_line('<<dept表部门名称包含A的部门名称和部门数>>');
OPEN cur_1 FOR SELECT dname,COUNT(1)OVER() FROM dept WHERE dname LIKE '%A%';
LOOP
FETCH cur_1 INTO v_char,v_num;
EXIT WHEN cur_1%NOTFOUND;
dbms_output.put_line(v_char||' '||v_num);
END LOOP;
CLOSE cur_1;
END;
强类型和弱类型的区别
- 强类型有return open时for后面的结果集必须和return后面的保持一致
- 弱类型没有return open时for后面的结果集比较自由
动态游标
declare
cur_1 sys_refcursor;
v_1 date;
v_2 varchar2(20);
v_3 varchar2(20);
begin
----今年所有的周末-------
open cur_1 for
select * from (
select trunc(sysdate,'yyyy')-1 + level lv
from dual
connect by level<=365)
where to_char(lv,'d') in (1,7);
loop
fetch cur_1 into v_1;
exit when cur_1%notfound;
dbms_output.put_line(v_1);
end loop;
close cur_1;
-------每个人的名字和他上级的名字---
open cur_1 for
select a.ename,b.ename
from emp a
join emp b
on a.mgr=b.empno;
loop
fetch cur_1 into v_2,v_3;
exit when cur_1%notfound;
dbms_output.put_line(v_2||'的经理是'||v_3);
end loop;
close cur_1;
end;
游标+for循环
带游标名的
声明一个显式游标 不用打开 不用赋值 不用关闭
declare
cursor cur_1 is select * from dept;
begin
for i in cur_1
loop
dbms_output.put_line(i.dname||' '||i.deptno||' '||i.loc);
end loop;
end;
不带游标名的
不用声明 不用打开 不用赋值 不用关闭
begin
for i in (select * from dept)
loop
dbms_output.put_line(i.dname||' '||i.deptno||' '||i.loc);
end loop;
end;
-------每个人的名字和他上级的名字---
begin
for i in(select a.ename ename,b.ename mname
from emp a
join emp b
on a.mgr=b.empno)
loop
dbms_output.put_line(i.ename||' '||i.mname);
end loop;
end;
--打印每个部门每种职位的人数
begin
for i in(select deptno,job,count(1) l
from emp
group by deptno,job)
loop
dbms_output.put_line(i.deptno||' '||i.job||' '||i.l);
end loop;
end;
异常 error
- 异常处理时既要插入又要弹窗 必须先插入提交事务后再弹窗
预定义异常
系统中已经有名字、错误代码、错误描述
| 违反了唯一性限制 | dup_val_on_index |
|---|---|
| 没有找到数据 | no_data_found |
| 返回多行 | too_many_rows |
| 试图用零做除数 | zero_divide |
| 其他异常 | others |
--创建一张表存放异常信息
create table t_err(code number,
errm varchar2(1000),
err_date date);
declare
v_e emp.ename%type;
v_c number;
v_r varchar2(1000);
begin
select ename into v_e from emp where empno=&empno;
dbms_output.put_line(v_e);
exception
when others then
dbms_output.put_line(sqlcode||sqlerrm);
v_c:=sqlcode;
v_r:=sqlerrm;
insert into t_err values(v_c,v_r,sysdate);
--在往表中插入数据时，需要借助变量，不能直接插入
--处理异常时，需要先打印再赋值再插入
end;
select * from t_err;
注：
- 在往表中插入数据时，需要借助变量，不能直接插入
- 处理异常时，需要先打印再赋值再插入
非预定义异常
系统中有 但没有名字 只有错误代码和描述
declare
err exception;
v_c number;
v_e varchar2(1000);
pragma exception_init(err,-2291);--把异常代码是2291的起了一个名字
begin
insert into emp(empno,deptno) values (1324,50);
exception
when err then
v_c:=sqlcode;
v_e:=sqlerrm;
dbms_output.put_line(sqlcode||sqlerrm);
insert into t_err values(v_c,v_e,sysdate);
end;
自定义异常
自己定义的异常
--往emp_1表中插入一条数据，要求：必须是7开头并且是四位
--不满足上面要求 报异常
select * from emp_1;
create table emp_1 as select * from emp;
declare
v_empno number:=&empno;
err exception;
begin
if v_empno like '7___' then
insert into emp_1(empno) values(v_empno);
else
raise err;--不满足条件，抛出异常
end if;
exception
when err then
dbms_output.put_line('编号不符合规范');
raise_application_error(-20001,'员工编号错误');
--带内容的弹窗报错
--错误代码的取值范围-20000~-20999之间
--raise; 不带内容的弹窗报错
end;
--创建emp2表格式同emp
--插入一条数据 员工编号7开头4位 工资大于3000 工作不能为空 如果不符合规范报错异常信息插入到t_err表中并弹窗报错
create table emp2 as select * from emp where 1=2;
declare
v_empno number:= &empno;
v_sal number:= &sal;
v_job varchar2(10):= '&job';
err exception;
begin
if v_empno like '7___'
and v_sal>3000
and v_job is not null then
insert into emp_1(empno,sal,job) values(v_empno,v_sal,v_job);
else
raise err;--不满足条件，抛出异常
end if;
exception
when err then
dbms_output.put_line('条件不满足');
insert into t_err values(-2000,'插入数据不符合要求',sysdate);
commit;--
raise_application_error(-20000,'插入数据不符合要求');
end;
select * from t_err;
存储过程 procedure
--语法：
create [or replace] procedure pro_name
[(参数1 [in]/out/in out 类型,参数2...)] is/as
[声明部分]
begin
执行语句;
[exception]
[执行语句;]
end;
--调用存过
call pro_name();
相较于SQL存过的好处：
- 存过只在创建的时候进行编译，以后每次执行不需要编译；SQL语句每次执行都要编译，所以存过可以提高执行速度
- 当数据库执行复杂操作的时候（多表连接查询）可以将复杂的事务处理结合一起使用这些操作；如果使用SQL 需要多次连接数据库 存过只需要连接一次，减少了数据库连接的次数
- 存过可以重复利用 减少了开发人员的工作量
无参数存过
truncate table emp_1;
create table emp_1 as select * from emp;
--创建一个存过 把emp中的30部门的员工信息到emp_1中,更新emp_1中职位是MANAGER的员工薪资为原来的一般,打印更新前后的工资
create or replace procedure por_1 is
v_sal number;
begin
insert into emp_1 select * from emp where deptno=30;
commit;
select sal into v_sal from emp_1 where job='MANAGER';
dbms_output.put_line('更新前的工资：'||v_sal);
update emp_1 set sal=sal/2 where job='MANAGER';
dbms_output.put_line('更新后的工资：'||v_sal/2);
end;
call por_1();
select * from emp_1;
/*创建一个存过 把emp中职位是CLERK的员工信息插入到emp_1中
更新emp_1中工资最高的人的工资变为原来的一半
打印更新前后的工资*/
create or replace procedure por_2 is
v_sal number;
begin
insert into emp_1 select * from emp where job='CLERK';
commit;
select max(sal) into v_sal from emp_1;
dbms_output.put_line('更新前的工资：'||v_sal);
update emp_1 set sal=sal/2 where sal=(select max(sal) from emp_1);
dbms_output.put_line('更新后的工资：'||v_sal/2);
end;
call por_2();
有in无out
--创建一个存过 输入一个部门编号 把部门的员工姓名和工资插入到emp_1 打印插入了几条数据
create or replace procedure por_3(v_deptno number) is
begin
insert into emp_1(ename,sal)
select ename,sal from emp
where deptno = v_deptno;
dbms_output.put_line('插入了'||sql%rowcount);
end;
call por_3(10);
/*创建emp_1 数据同emp
创建emp_2 格式同emp
创建存过 输入一个数字和部门编号 数字必须是0~9 如果不是抛出异常1
如果部门编号不存在抛出异常2 当部门人数小于该数字 将部门的员工信息插入到emp_2中打印插入了几条数据
当部门人数大于该数字 将部门的员工姓名删除 打印删了几条
等于 更新部门的员工工资为工资/100的3次方 打印更新了几条数据*/
select * from emp_2;
create table t_err(code number,
errm varchar2(1000),
err_date date);
create or replace procedure por_4(v_n number,v_deptno number)is
v_a number;
err exception;
err2 exception;
begin
select count(1) into v_a from emp where deptno = v_deptno;
if v_n not between 0 and 9 then
raise err;
elsif v_a=0 then
raise err2;
else
if v_a<v_n then
insert into emp_2 select * from emp_1 where deptno=v_deptno;
dbms_output.put_line('插入了'||sql%rowcount);
elsif v_a>v_n then
update emp_1 set ename=null where deptno=v_deptno;
dbms_output.put_line('删除了'||sql%rowcount);
else
alter table emp_1 modify sal number(15,2);--更改列的类型长度，否则会报精度异常
update emp_1 set sal=power(sal/100,3) where deptno=v_deptno;
dbms_output.put_line('更新了'||sql%rowcount);
end if;
end if;
commit;
exception
when err then
insert into t_err values(-20000,'数字不符合要求',sysdate);
commit;
raise_application_error(-20000,'数字不符合要求');
when err2 then
insert into t_err values(-20001,'部门编号不存在',sysdate);
commit;
raise_application_error(-20001,'部门编号不存在');
end;
call por_4(3,10);
有out的
--写一个存过 输入一个员工编号 输出部门平均工资
create or replace procedure por_5(v_empno number,
v_a_s out number) is
begin
select avg(sal)
into v_a_s
from emp
where deptno=(select deptno
from emp
where empno=v_empno);
end;
--有out的调用
declare
v_a_s number;
begin
por_5(7788,v_a_s);
dbms_output.put_line(v_a_s);
end;
--编写一个存过 输入员工姓名 打印该员工所在部门的所有人的姓名用，隔开
create or replace procedure por_6(v_name varchar2,
v_s_names out varchar2) is
begin
select wm_concat(ename)
into v_s_names
from emp
where deptno=(select deptno
from emp
where ename=v_name);
end;
declare
v_s_names varchar2(100);
begin
por_6('JONES',v_s_names);
dbms_output.put_line(v_s_names);
end;
有in out 的
--写一个存过 输入一个名字和要给他的工资 输出新工资
create or replace procedure por_7(v_name varchar,
v_sal in out number)is
begin
update emp_1 set sal=sal+v_sal
where ename =v_name
returning sal into v_sal;
dbms_output.put_line(v_sal);
end;
declare
v_sal number:=300;
begin
por_7('SMITH',v_sal);
end;
--写一个存过 输入两个人的姓名 更改他们两人的名字为各自首字母中间加上+ 输出更新后的名字
create or replace procedure por_8(v_n_1 in out varchar,
v_n_2 in out varchar)is
begin
update emp_1
set ename = substr(v_n_1,1,1)||'+'||substr(v_n_2,1,1)
where ename=v_n_1
returning ename into v_n_1;
update emp_1
set ename = substr(v_n_2,1,1)||'+'||substr(v_n_1,1,1)
where ename=v_n_2
returning ename into v_n_2;
end;
declare
v_n_1 varchar(10):='ALLEN';
v_n_2 varchar(10):='SCOTT';
begin
por_8(v_n_1,v_n_2);
dbms_output.put_line(v_n_1||'_'||v_n_2);
end;
select * from emp_1;
out动态游标
--创建一个存过 输出emp表的员工姓名和经理姓名
create or replace procedure por_9(cur_1 out sys_refcursor)is
v_1 varchar(20);
v_2 varchar(20);
begin
open cur_1 for
select a.ename,b.ename
from emp a
join emp b
on a.mgr=b.empno;
loop
fetch cur_1 into v_1,v_2;
exit when cur_1%notfound;
dbms_output.put_line(v_1||'的经理是'||v_2);
end loop;
close cur_1;
end;
declare
cur_1 sys_refcursor;
begin
por_9(cur_1);
end;
--写一个存过 输出每个人的名字和每个人名字中的字母
--SMITH S,M,I,T,H
create or replace procedure por_10(cur_1 out sys_refcursor)is
v_name varchar(20);
v_n_c varchar(20);
begin
open cur_1 for
select ename
from emp;
loop
fetch cur_1 into v_name;
v_n_c:=null;
for i in 1..lengthb(v_name)
loop
v_n_c:=v_n_c||','||substr(v_name,i,1);
end loop;
v_n_c:=ltrim(v_n_c,',');
exit when cur_1%notfound;
dbms_output.put_line(v_name||'拆分为'||v_n_c);
end loop;
close cur_1;
end;
declare
cur_1 sys_refcursor;
begin
por_10(cur_1);
end;
存过和自定义函数的区别：1,2,4
- 函数有return 存过没有
- 函数不能调用存过 但是存过可以调用函数
- 函数是用来实现某些功能 存过是用来实现某些操作
- DML一般用存过
- 有out的时，输出最好用存过
数据字典
静态
- user_* 存储了当前用户所有的对象信息
- all_* 存储了当前用户可以访问的所有对象信息
- dba_* 存储了整个数据库所有的对象信息
select * from user_tables;
select * from all_tables;
select * from dba_tables;
user_tables 所有的表
user_views 所有的视图
user_sequences 所有的序列
user_indexes 所有的索引
user_recyclebin 回收站
user_source 所有的有名块
user_constraints 所有的约束
user_tab_cols 所有的表所有的列的属性
user_tab_columns 所有的表所有的列的属性
user_tab_comments 表注释
user_col_comments 列注释
--查询建表语句
select dbms_metadata.get_ddl('TABLE','EMP')from dual;--ORACLE table和表名要大写
show_table(表名);--pg mpp 数据库
--表注释
comment on table 表名 is '注释';
--列注释
comment on column 表.列 is '注释';
--ddl 建表语句
create table 表名();
comment on column emp_1.ename is '员工姓名';
select * from user_col_comments;
动态
- v$
- v$session 当前有谁连接我的数据库 terminal是主机名
- 会话：一个用户从登录到退出
--查询谁连接了我的数据库
select distinct terminal from v$session;
动态SQL
DDL的批量增删改查
--语法：
execute immediate '执行的SQL语句' [using/into];
没有using/into
--删除emp3
begin
execute immediate 'drop table emp3';
end;
写一个存过，批量建表emp_1..100
create or replace procedure pro_a(v_name varchar,
v_1 number,
v_2 number)
authid current_user is--加上authid current_user表示调用者权限以调用者权限执行（看角色权限）
--不加 表示定义者权限 以定义者身份执行（不看角色权限 只看系统权限)
begin
for i in v_1..v_2
loop
execute immediate 'create table '||v_name||'_'||i||' as select * from emp';--注意table后和as前的空格
end loop;
end;
call pro_a('emp',40,50);
select * from user_tables where table_name like 'EMP_%';
--写一个存过，批量删除表
create or replace procedure pro_b(v_name varchar,
v_1 number,
v_2 number)
authid current_user is
begin
for i in v_1..v_2
loop
execute immediate'drop table '||v_name||'_'||i;
end loop;
end;
call pro_b('emp',40,50);
有using/into
--批量建表
call pro_a('emp',40,80);
--创建一个存过 emp——表的数据清空 插入一条数据 打印这条数据
create or replace procedure pro_c(v_ename varchar,
v_empno number)
authid current_user is
v_e emp%rowtype;
begin
for i in 40..80
loop
--批量清空表内数据
execute immediate'truncate table emp_'||i;
--批量插入数据
execute immediate'insert into emp_'||i||'(ename,empno) values(:1,:2)' using v_ename,v_empno;--传值
--批量找到数据
execute immediate'select * from emp_'||i||' where rownum=1' into v_e;--取值
dbms_output.put_line('emp_'||i||'第一条数据是'||v_e.ename||' '||v_e.empno);
end loop;
end;
call pro_c('小明',7894);
using和into同时使用
先into 后using
--把emp的数据插入到emp_40-80 输入一个数字 打印emp40——50中工资大于这个数的员工姓名
create or replace procedure pro_d(v_n number)
authid current_user is
v_ename varchar2(200);
begin
for i in 40..80
loop
execute immediate 'insert into emp_'||i||' select * from emp';
execute immediate 'select wm_concat(ename) from emp_'||i||' where sal>:1' into v_ename using v_n;
dbms_output.put_line('emp_'||i||'中工资大于'||v_n||'的人有'||v_ename);
end loop;
end;
call pro_d(2000);
包 package
- 在大型项目中 有很多的模块 每个模块有自己的存过 函数等，这些东西默认是会放在一起 时间久了 不容易维护
包定义：package
包主体：package body
--包定义的语法：
create or replace package 包名 is/as
function 名1 ...return ...--函数
function 名2 ...return ...
..
procedure 1...--存过
procedure 2...
...
type;--类型 表.%rowtype 表.列%type
cursor;--游标 显式游标
变量1;
变量2;
..
end 包名;
--包主题的语法：
create or replace package body 包名 is/as
function 1..return ..
[声明的内容]
begin
执行的内容;
return ;
end;
..
procedure 1 is/as
begin
执行的内容;
end;
end 包名;
包定义：声明了数据类型 变量 游标异常等
包主体：是包定义具体的实现 定义了包定义部分声明的游标和子程序 全局变量 本地变量
--创建一个包 包含fun1 pro1 pro2
--fun1 输入一个员工编号 返回一个部门名称
--pro1 输入一个员工编号 输出部门的平均工资和人数
--pro2 输入一个姓名 打印他的名字和他经理的名字
--包定义
create or replace package pck_1 is
function fun1(v_empno number) return varchar;
procedure pro1(v_empno number,v_a_s out number,v_c out number);
procedure pro2(v_name varchar);
end pck_1;
--包主题
create or replace package body pck_1 is
--fun1 输入一个员工编号 返回一个部门名称
function fun1(v_empno number) return varchar is
v_dname varchar(20);
begin
select d.dname
into v_dname
from emp e
join dept d
on e.deptno=d.deptno
where e.empno=v_empno;
return v_dname;
end;
--pro1 输入一个员工编号 输出部门的平均工资和人数
procedure pro1(v_empno number,
v_a_s out number,
v_c out number) is
begin
select avg(sal),count(1)
into v_a_s,v_c
from emp
where deptno = (select deptno
from emp
where empno=v_empno);
end;
--pro2 输入一个姓名 打印他的名字和他经理的名字
procedure pro2(v_name varchar) is
v_m_n varchar(20);
begin
select m.ename
into v_m_n
from emp e
join emp m
on e.mgr=m.empno
where e.ename=v_name;
dbms_output.put_line(v_name||' 的经理是 '||v_m_n);
end;
end pck_1;
--调用 遵循函数和存过的调用方式
select pck_1.fun1(7654) from dual;
declare
v_1 number;
v_2 number;
begin
pck_1.pro1(7788,v_1,v_2);
dbms_output.put_line(v_1||''||v_2);
end;
call pck_1.pro2('ALLEN');
/*创建一个包 包含3个函数 1个存过
fun 1 输入一个员工编号 返回他的工资
fun2 输入一个姓名 返回部门所在地
fun3 输入一个日期 返回这个日期之前的人数
pro1 把dept表10部门的部门所在地插入到emp1的job列*/
--包定义
create or replace package pck_2 is
function fun1(v_empno number) return number;
function fun2(v_name varchar) return varchar;
function fun3(v_date date) return number;
procedure pro2;
end pck_2;
--包主体
create or replace package body pck_2 is
--fun 1 输入一个员工编号 返回他的工资
function fun1(v_empno number) return number is
v_sal number;
begin
select sal
into v_sal
from emp
where empno=v_empno;
return v_sal;
end;
--fun2 输入一个姓名 返回部门所在地
function fun2(v_name varchar) return varchar is
v_loc varchar(100);
begin
select d.loc
into v_loc
from emp e
join dept d
on e.deptno=d.deptno
where e.ename=v_name;
return v_loc;
end;
--fun3 输入一个日期 返回这个日期之前的人数
function fun3(v_date date) return number is
v_n number;
begin
select count(1)
into v_n
from emp
where hiredate<v_date;
return v_n;
end;
--pro1 把dept表10部门的部门所在地插入到emp1的job列
procedure pro2 is
v_loc varchar(20);
begin
-------将10部门的人的工作进行更新------------------------------
select loc into v_loc from dept where deptno=10;
update emp1 set job=v_loc where deptno=10;
---------------------插入到新的一行----------------------------
--insert into emp(job) select loc from dept where deptno = 10;
end;
end pck_2;
create table emp1 as select * from emp;
select * from emp1;
--调用
select pck_2.fun1(7788) from dual;
select pck_2.fun2('JONES') from dual;
select pck_2.fun3(date'1981-1-1') from dual;
call pck_2.pro2();
包的重载
包的子程序名字相同 通过传入不同的参数(类型、顺序、数量) 得到不同的结果
回收站闪回表
- 注意
- 如果回收站有重名表 会闪回最近删除的
- 如果要闪回较早删除的表 可以用 object_name
--语法
flashback table 表名 to before drop rename to 新表名;
--闪回到修改前（dml）的状态
前提是必须开启行移动：
开启：alter table 表名 enable row movement;
关闭：alter table 表名 disable row movement;
--闪回到一个时间戳
select * from emp_1;
insert into emp_1 select * from emp;
--开启行移动
alter table emp_1 enable row movement;
--闪回到修改之前
flashback table emp_1 to timestamp
timestamp'2022-7-6 16:21:00.000';
或
flashback table emp_1 to timestamp
to_timestamp('2022-7-6 16:21','yyyy-mm-dd hh24:mi');
触发器
- 分类
- DML触发器
- DDL触发器
DML触发器
--DML触发器的语法
create or replace trigger 触发器名称
before/after 事前/事后
DML 操作 on 表
[for each row] 加上表示行级触发不加表示语句触发
[声明的内容]
begin
执行的语句;
end;
--行级触发：影响一行的数据 触发一次
--语句触发：一个语句只触发一次
--创建一个emp_1 和emp_2 数据同emp
drop table emp_1;
drop table emp_3;
create table emp_1 as select * from emp;
create table emp_3 as select * from emp;
--针对emp_2 创建一个触发器 删除emp_2时 向emp_1中插入一个empno1
create or replace trigger tri_1
before
delete or update or insert
on emp_2
for each row
begin
insert into emp_1(empno,hiredate) values(1,sysdate);
end;
delete from emp_2 where deptno=10;
select * from emp_1;
--创建一个触发器对emp_2 进行插入操作 同时向emp_1 ename列和diredate列插入"插入" 系统时间
create or replace trigger tri_2
before
insert
on emp_2
begin
insert into emp_1(ename,hiredate) values('插入',sysdate);
end;
insert into emp_2 select * from emp_2 where deptno=20;
触发器的启动和禁用
--开启某个触发器
alter trigger 触发器名 enable;
alter trigger tri_2 enable;
--禁用某个 触发器
alter trigger 触发器名 disable;
alter trigger tri_1 disable;
alter trigger tri_2 disable;
--启动全表
alter table 表名 enable all triggers;
alter table emp_2 enable all triggers;
--禁用全表
alter table 表名 disable all triggers;
alter table emp_1 disable all triggers;
触发器的作用：
-
维护数据库的安全性 一致性和完整性
-
可在写入数据表前 强制检验或转换数据
-
当触发器发生错误时 异常结果会被撤销
-
部门数据库管理系统可以针对DDL使用触发器 成为DDL触发器
还可以针对对视图定义替代触发器
优点： -
触发器可以通过数据库中的相关表实现级联删除
-
从约束的角度而言 触发器可以定义比check 更为复杂的约束
-
触发器也可以评估数修改前后的状态 并根据其差异采取对策
-
一个表中的多个不同类型触发器(insert/update/delete) 允许采取
多个不同对策 以响应同一个修改语句
缺点： -
滥用会造成数据库及应用程序的维护困难 在数据库操作中 可以通过关系触发器 存储过程 应用程序等来实现数据操作 同时 规则 约束 缺省值 也是保证数据完整性的重要保障 如果对触发器过分的依赖 那么势必会影响数据库的结构 同时增加了维护的复杂性
-
一个表的触发器越多 对于表的DML操作性能影响越大
-
如果触发频率高 占用内存 减低数据库访问速度
-
相对不灵活 一旦触发马上执行 不能排除特殊情况
-
一定程度上打乱代码结构 相关的代码都需要特别注释 否则造成阅读和维护上的困难
-
过度使用也会造成数据库的维护困难
触发器中的新值和旧值
:old.列 列中的旧值
:new.列 列中的新值
注：必须是行级触发
insert update delete
:old 没有 有 有
:new 有 有 没有
--把emp_2中的入职日期改为时间戳类型
alter table emp_2 modify hiredate timestamp;
/*创建emp_3 数据同emp
对emp_3 创建触发器 对表进行更新的时候 把更新前后的员工信息插入到emp_2 并在job列表明更新前还是更新后 在hiredate插入时间戳*/
create or replace trigger tri_3
before
update
on emp_3
for each row
begin
insert into emp_2(ename,job,hiredate) values(:old.ename,'前',systimestamp);
insert into emp_2(ename,job,hiredate) values(:new.ename,'后',systimestamp);
end;
update emp_3 set ename=lower(ename) where deptno=10;
select * from emp_2;
触发器和自定义异常的联动
create or replace trigger tri_4
before
insert
on emp_3
for each row
begin
if :new.empno not like '7___' then
raise_application_error(-20001,'编码错误');
end if;
end;
insert into emp_3(empno) values(2233);
定时器 job
- t+1(拿到的数据永远时昨天的数据）
- job是Oracle 的定时任务 也叫定时器 定时作业 作业
- 数据库定时地自动执行一些脚本 或做数据备份 或数据提炼 或数据库的性能优化，包括重建索引等等的工作 需要用到job
--创建job的包
dbms_job
--创建job的语法
declare
v_jobid number;
begin
dbms_job.submit(job=>v_jobid,
what=>'pro_name/dml语句',
next_date=>sysdate+1/20/60,--第一次执行的时间，一分钟后执行
interval=>'trunc(sysdate,''mi'')+1/24/60');--间隔的时间 整分执行
end;
---------------
declare
v_jobid number;
begin
dbms_job.submit(job=>v_jobid,
what=>'insert into emp_1
select * from emp where deptno=10;',
next_date=>sysdate+1/20/60,
interval=>'trunc(sysdate,''mi'')+1/24/60');
end;
--查看定时任务
select * from user_jobs;
select count(1) from emp_1;
--立即执行
call dbms_job.run(job id);
call dbms_job.run(23);
--停止任务
begin
dbms_job.broken(jobid,true);
commit;
end;
begin
dbms_job.broken(23,true);
commit;
end;
--删除job
call dbms_job.remove(jobid);
call dbms_job.remove(23);
当job执行失败后他会重试
- 每次重试时间是递增的，第一次2分钟，第二次4分钟，8分钟。。。
- 当超过1440分钟，也就是24小时的时候 固定时间的重试时间为1天
- 超过16次重试后 job 就会被标记为broken next_date为400-1-1 也就是不再进行job重试
面试：技术+语言表达（分析能力、逻辑能力、表达能力）
感谢大家的浏览。欢迎一起学习交流
