title: ‘oracle’
date: 2019-07-22 15:32:20
tags: [oracle]
published: true
hideInList: false
feature: https://s2.ax1x.com/2019/07/22/eC3r26.jpg
常用sql
改表名 rename table 旧表名 to 新表名
表头 create table 表名(字段名 类型 约束,..)
增列 alter table 表名 add 列名 类型 约束；
改列 alter table 表名 change 旧列名 新列名 类型 约束
删列 alter table 表名 drop 列名
插行 insert into 表名 values(值1,值2......)
改行 update 表名 set age=22 where id=2
删行 delete from 表名 where 列名=值
查库 show databases;
查字符集 show create database db;
进库 use lb 查有多少张表:show tables;
查建表语句:show create table 表名
查看表结构 desc 表名; 但所有行:select*from from1;
查1行:SELECT*FROM star WHERE id=3 WHERE price>3000 HAVING TYPE=1
多行:select*from form1 where id in(1,3,5,7,15,20) HAVING price>3000模糊 select*from from1 where name like %波_;
区间 select*from from1 where price between 2000 and 6000; [bɪˈtwin]
降序 select * from from1 order by price asc升/desc降;
去重并年龄升序SELECT DISTINCT price FROM star ORDER BY 年龄 ASC; 逻辑和 select * from from1 where price>3000 and age>30;
select*from user where username='张三' and password='123456'
逻辑或 select * from from1 where price>5000 or name like "周%";
逻辑非 select * from from1 where not(name like "周%");查1列/多个列:SELECT 姓名 FROM star;
并筛选:SELECT id,年龄 FROM star WHERE 年龄>30; 聚合算条目数不能有where: select count(*) from from1
聚合算总和值 select sum(price) from from1
聚合最大值 select max(age) from star;
聚合最小值 select min(age) from from1;
分组聚合算平均值 SELECT TYPE, AVG(price)FROM star GROUP BY TYPE;分组:整个表分组并把年龄*2的结果 单独列出 SELECT*,年龄*2 FROM star
以年龄分组 并把年龄大于30单独统计 SELECT 姓名,年龄>30 FROM star
以分组形式把姓名列出:SELECT TYPE,GROUP_CONCAT(姓名)FROM star GROUP BY TYPE
各个分组身份的平均值 select type,avg(price) from star group by type;
各个分组身份的最大的 select type,min(price) from star group by type;
各个分组身份的条目数 select type,count(*) from star group by type;各个分组身份的条目数 并大于2的显示条目
select type,count(*)from star group by type having count(*)>2;
每组 条目里年龄大于30的显示条目;WHERE只能在分组前
SELECT TYPE,COUNT(*)FROM star WHERE 年龄>30 GROUP BY TYPE
每组 条目里年龄大于30的 并且条目大于2的 显示条目;HAVING可以分组后再过滤
SELECT TYPE,COUNT(*)FROM star WHERE 年龄>30 GROUP BY TYPE HAVING COUNT(*)>2
多表
MySQL多表 商品表的外键和分类表的主键
添加一个外键:alter table 表名 add foreign key(外键字段的字段名) references 主键表名 (主键字段名) ['ɔ:ltə] [ˈfɒrən][ˈrefrənsiz] [ˈkætəgɔri]
alter table star add foreign key(type)references category(cid)
删除一个外键：alter table 表名 drop foreign key 约束名
ALTER TABLE star DROP FOREIGN KEY star_ibfk_1- 一对多(重点)- 应用场景:学生和班级、商品和分类、用户和订单- 建表原则:在多的一方添加一个外键，指向一的一方的主键
- 多对多(重点)- 应用场景:商品和订单、学生和选课- 建表原则：创建一张第三方表，该表除了id字段之外，还至少得有两字段，分别作为外键指向两张表的主键
- 一对第一- 应用场景:人和身份证号- 建表原则:先当成一对多来处理，再给多的一方的外键字段添加一个唯一约束交叉查询得到的结果是一个笛卡尔积，这个结果中包含错误数据。
select*from star,category连接查询:为了筛选交叉查询中获取带的正确数据(重点)其实就是在交叉查询的基础上添加一个条件进行筛选正确数据满足条件则显示，不满足则不显示 两种结果一样
隐式内连接查询 查询结果是a的外键star.type=b的主键(cid)/两个int相同就显示 b的主键对应的数据(cname) 简写的结果一模一样只是精简了代码- select * from a表,b表where 条件(a表的外键=b表的主键)SELECT*FROM star,category WHERE star.type=category.cid
SELECT*FROM star s,category c WHERE s.type=c.cid 可以用别名简化
显式 内连接查询 显示里面的,on只能用主外键关联作为条件,如果还有其它条件,后面加where- select * from a表 inner join b表 on 条件SELECT*FROM star s JOIN category c ON s.type=c.cid
SELECT*FROM star s JOIN category c ON s.type=c.cid 简化外连接查询：显示主表 的所有数据，从表 的数据若满足条件则显示，不满足则以null显示
- 左外连接:以join左边的表为主表/star
select * from a表left outer join b表 on 条件SELECT*FROM star s LEFT OUTER JOIN category c ON s.type=c.cid
- 右外连接:以join右边的表为主表 /category- select * from a right outer join b on 条件
SELECT*FROM star s RIGHT OUTER JOIN category c ON s.type=c.cid子查询(重点)
定义：一个select语句中嵌套另外一个完整的select语句，也就是说一个select语句作为另外一个select语句的条件1--先查出最高身价明星 2再根据最高身价查出明星信息
SELECT MAX(price) FROM star
SELECT*FROM star WHERE price=5050
子查询就可以合并成一句查询 嵌套
SELECT*FROM star WHERE price=(SELECT MAX(price)FROM star)--1查宝宝的身价2.以宝宝的身份去查询身价大于这个数的明星信息--
SELECT price FROM star WHERE NAME="宝宝"
SELECT*FROM star WHERE price>5000
SELECT*FROM star WHERE price>(SELECT price FROM star WHERE NAME="宝宝")--1查出小白的类别2 根据这个类别查同类别明星信息--
SELECT TYPE FROM star WHERE NAME="小白"
SELECT*FROM star WHERE TYPE=2
SELECT*FROM star WHERE TYPE=(SELECT TYPE FROM star WHERE NAME="小白")
SELECT*FROM star WHERE TYPE=(SELECT TYPE FROM star WHERE NAME="小白")AND NAME!="罗永浩"//后面还可以加上不等于的语句SELECT*FROM star WHERE TYPE=(SELECT cid FROM category WHERE cname="宝剑队"); //查所有类别为宝剑队的明星联合查询(了解)：合并两个select语句查询到的结果集- union,去重
- union all，不去重分页查询(重点)- 关键字:limit a,b- a代表从第几个下标开始查询
SELECT*FROM star LIMIT 0,4; //第二次 4,4- b代表查询多少条数据，也就是每页显示的数据条数
- 将客户端传入的要查询的页数"page"转换成aa=(page-1)*b
对明星进行按价格从高到低排序，然后取第一个明星信息
SELECT * FROM star ORDER BY price DESC LIMIT 0,1SELECT ename,job FROM emp WHERE deptno=10 //10号部门员工和工作
SELECT deptno,AVG(sal)FROM emp GROUP BY deptno 以部门分组 查出平均工资
Oracle数据库 [ˈɒrəkl]
安装
安装全部下一步,检查先决条件时的网络配置需求,勾选用户已验证,最后一步更改两个用户名 把是否锁定帐户勾取消设置密码 密码 scott 密码tiger hr 密码hr
连接
http://pc5268-20140727:1158/em/ 用户名sys 密码root 连接身份sysdba 也可以用ip地址
instantclient_12_1目录下 dos执行 sqlplus scott/tiger@192.168.44.68:1521/orcl 第一次要输用户名和密码scott 密码tiger
用NavicatPremium要在工具->选项->环境->OCI环境,填instantclient_11_2\oci.dll 下载的 软件和下载的版本要一致32/64位
sql基础常识
在Oracle的学习之中，重点使用的是SQL语句，而所有的SQL语句都要在scott用户下完成，这个用户下一共有四张表，可以使用：
SELECT * FROM tab;
表结构详解
1、 部门表：dept [dɪˈpɑ:tmənt]department
| № | 名称 | 类型 | 描述 |
|---|---|---|---|
| 1 | DEPTNO | NUMBER(2) | 表示部门编号，由两位数字所组成 |
| 2 | DNAME | VARCHAR2(14) | 部门名称，最多由14个字符所组成 |
| 3 | LOC | VARCHAR2(13) | 部门所在的位置 |
2、 雇员表：emp [ɪmˈplɔɪi:] employee
| № | 名称 | 类型 | 描述 |
|---|---|---|---|
| 1 | EMPNO | NUMBER(4) | 雇员的编号，由四位数字所组成 |
| 2 | ENAME | VARCHAR2(10) | 雇员的姓名，由10位字符所组成 |
| 3 | JOB | VARCHAR2(9) | 雇员的职位 包括管理 管理的管理 |
| 4 | MGR | NUMBER(4) | 上级的字段 雇员对应的领导编号，领导也是雇员 |
| 5 | HIREDATE | DATE | 雇员的雇佣日期 |
| 6 | SAL | NUMBER(7,2) | 基本工资，其中有两位小数，五倍整数，一共是七位 |
| 7 | COMM | NUMBER(7,2) | 奖金，佣金 |
| 8 | DEPTNO | NUMBER(2) | 雇员所在的部门编号 |
3、 工资等级表：salgrade [ˈsæləri] salary grade [greɪd]
| № | 名称 | 类型 | 描述 |
|---|---|---|---|
| 1 | GRADE | NUMBER | 工资的等级 |
| 2 | LOSAL | NUMBER | 此等级的最低工资 |
| 3 | HISAL | NUMBER | 此等级的最高工资 |
4、 资金表：bonus [ˈbəʊnəs]
| № | 名称 | 类型 | 描述 |
|---|---|---|---|
| 1 | ENAME | VARCHAR2(10) | 雇员姓名 |
| 2 | JOB | VARCHAR2(9) | 雇员职位 |
| 3 | SAL | NUMBER | 雇员的工资 |
| 4 | COMM | NUMBER | 雇员的奖金 |
SCOTT用户 表空间下的表 SALGRADE工资等级表 BONUS奖金表 DEPT部门表 EMP员工表
HI用户下的表 REGIONS地区 COUNTRIES国家 LOCATIONS地址 DEPARTMENTS部门 EMPLOYEES员工 JOBS职位 JOB_HISTORY职位历史
sql结构化查询语言 分类语言:四类语言
DDL数据定义语言 操作数据库结构 create drop alter
DML数据操纵语言 操纵的是数据 insert增 update改 delete删
DCL数据控制语言 控制权限grant revoke
DQL数据查询语言 select
查询语法: select 查询列名 from 表名
查询列名处为要查询的最后过滤规则:查询全部列用* 单个/多个列 用列名 别名 用,隔开 全部列加单个/多个用 表名.*,列名,列名
sql编写顺序 :select/选择结果 要查询/显示的列 from/来自 表名 where 条件判断 group by 分组的条件 having 分组之后的条件过滤 order by 排序
编写顺序:select…from…shere…group by…having.order by…
执行顺序 from…where…group by…having…select…order by…
limit属于mysql独有的方言 select*sal, from emp
规则:
Oracle体系结构 数据库–实例orcl–表空间/用户操作表空间–数据文件
创建一个项目:先创建一个用户->再创建表空间/指定用户操作的表空间->创建表
和mysql最大区别 Oracle: 表是属于用户的 Oracle是属于多用户的
基本查询
select*from emp --查询emp表
或者 select emp.* from emp
select ename from emp 查 姓名单列
SELECT ENAME from emp WHERE ENAME=‘JONES’ 查单列并加条件
select ename as “姓&名”,job 工作 from emp --只查出两列 并以别名姓名和工作 显示表头
SELECT emp.*,job from emp查整个表 并在后面查多出一列job的列
SELECT emp.*, sal**12 from emp 年薪 from emp;–查询所有 多输出一列sal并计算 查询员工年薪以别名显示
select 2+2*5 from dual --运算查询
别名中间有特殊字符需要使用双引号包含整个别名 as可以省略
select distinct job from emp --去除重复数据查询 去除了重复的工作 多行是每一行对应都相同就去除重复的
–null值不能参与运算 nvl(null,5)专门判断第一参数如果为null就返回参数2否则返回参数1
select sal,comm, nvl(comm,0)+sal from emp --查询出工资 奖金 和总收入 总收入解决null不能计算用nvl
select concat(‘我爱’,‘java’)from dual;–通用两个字符串拼接 字符串用用单引
select ‘我爱’||‘Java’||'go’from dual;–两个字符串拼接 字符串用用单引
条件查询/where 符合条件的行
where后面的写法就是条件 查询出符合条件的行
关系运算符 > >= < <= = != <> 逻辑 or and not
其它in在集合内 in null专门判断是null is not null不为null between…and在区间内 like模糊 any任意 all所有 exists存在
select*from emp where comm is not null and sal>1500–查能获得到奖金并且工资大于1500的员工 资金不是null的条件并且…
select*from emp where comm is null and not(sal>1500)–没有奖金并且工资 不大于1500的
select*from emp where sal between 1500 and 3000 --工资1500-3000区间
select*from emp where HIREDATE between to_date(‘1981-1-1’,‘yyyy-mm-dd’) and to_date(‘1981-12-31’,‘yyyy-mm-dd’); --查1981年1月1号到12月31日入职的员工 转日期查询
select*from emp where ename='smith’or empno=7499 --名字是smith或者编号7499都行
select*from emp where empno in(7369,7499,7521) --员工确定编号的几个 确定列的范围 中的多个
select*from emp where ename like ‘_M%’ --模糊查询 员工名字第二个字符是M后面任意的
select*from emp where empno!=7369 order by comm desc nulls last,HIREDATE asc nulls last–排除编号7369 以奖金降序排 入职按升序排 遇到空值放后面
单行函数
单行函数/对单个值进行处理 数值函数 字符函数 日期 转换 通用nvl
多行函数/聚合函数 sum avg max min count
数值函数
select ceil(-13.9)from dual–13 向下取整
select floor(-12.9)from dual–13 向上取整
select round(3.155,2)from dual–3.16 四舍五入 留2位小数
select trunc(3.155,2)from dual–3.15 直接截断留2位小数
select mod(9,2)from dual–1 取模 整除后的余数
字符
select concat(‘我爱’,‘Java’)from dual–我爱Java 字符串拼接
select substr(‘abcdef’,2,3)from dual–bcd 字符串截取 从第二个开始截三位 开始位置0或1
select length(‘abcdef’)from dual–6 字符串长度
select replace(‘abcdef’,‘a’,‘b’)from dual–bbcdef 字符串替换
select trim(‘s’from’sscdess’)from dual–cde 去掉两边的s
日期
select sysdate from dual --2018-02-28 23:31:07 获取当前时间
select ename,round((sysdate-hiredate)/7)from emp–获得员工入职的当前时间减入职时间 /7的周数的四舍五入
select ename,round(sysdate-hiredate)from emp --员工入职天数
select ename,round(MONTHS_BETWEEN(sysdate,hiredate))from emp–获得员工入职的月数 专用函数
select ename,round(MONTHS_BETWEEN(sysdate,hiredate)/12)from emp–获得员工入职的年数
select add_months(sysdate,3)from emp–三个月以后的日期是多少
转换
select to_number(‘10’)+24 from dual;–34 字符串转数值
select ‘10’+24 from dual;–34 运算时会自动转数值 不是就报错
经数值转字符:
select sal,to_char(sal,‘9999.99’)from emp;–1600-1600.00 对工资 经数值转字符 处理后 对比 只能用9去匹配 格式化字符串 这里是前面最多可以四位数 后面加两个小数 1600-1600.00 16000报错
select sal,to_char(sal,'$9,999.99')from emp;--1600-$1,600.00 格式化为加美元符加千位符
日期转字符
select to_char(sysdate,‘yyyy-mm-dd’) from dual;–2018-02-28格式化只显示日期
select to_char(sysdate,‘yyyy’) from dual;–2018 只显示年
select to_char(sysdate,‘ddd’) from dual;–059 现在距今年过了几天 dd距这个月过了几天 d 距这个星期过了几天
select to_char(sysdate,‘day’) from dual;–webnesday 现在星期几的英语 dy星期几的缩写
字符转日期
select*from emp where HIREDATE between to_date(‘1981-1-1’,‘yyyy-mm-dd’) and to_date(‘1981-12-31’,‘yyyy-mm-dd’); --查1981年1月1号到12月31日入职的员工 转日期查询
通用
处理空值nvl
-null值不能参与运算 nvl(null,5)专门判断第一参数如果为null就返回参数2否则返回参数1
select sal,comm, nvl(comm,0)+sal from emp --查询出工资 奖金 和总收入 总收入解决null不能计算用nvl
nvl2 相当于判断第一个参数是不是Null 的三元运算符
select nvl2(null,5,6)from dual --6如果第一个参数是null返回第三个 否则返回第二个
select nvl2(2,5,6)from dual --6如果第一个参数是null返回第三个 否则返回第二个
条件表达式 case
select ename,case ename when’KING’ then ‘王者’ when ‘ALLEN’ then ‘艾伦’ else ‘路人甲’ end from emp
独有的 未匹配到的为null
select ename,decode(ename,‘KING’,‘王者’,‘SMITH’,‘史密斯’)from emp
多行函数/聚合函数
where是在分组之前执行的条件过滤 不能接聚合 可以接单行 having是分组之后 的条件过滤 可以接聚合
sql编写顺序 select…from…where…group by…having…order by…
sql执行顺序 from…where…group by…having…select…order by…
函数 计数count 最大max 最小min 平均avg 求和sum 会忽略空值
select count()from emp;–查询所有员工人数 14
select sum(comm)from emp;–查询奖金总金额 2200
select avg(comm)from emp–查询全公司平均奖金 550 会忽略空值 不行
select sum(comm)/count() from emp;–用总奖金除总人数就是对的 157.1
笛卡尔积/两张表的乘积
–笛卡尔积的概念就是同时查询两张表,列数相加,行数相乘
select*from emp,dept --员工14行和部门4行 查出了56行
–在笛卡尔积基础上筛选出有意义的数据 隐式内连接 可以加别名
select*from emp,dept where emp.deptno=dept.deptno;
内连接:内连接(自然连接): 只有两个表相匹配的行才能在结果集中出现内连接是只显示满足条件 外左连接显示左边全部的和右边与左边相同的 右连接显示右边全部的和左边与右边相同的
加别名内隐式连接 员工8列14行部门3列4行 查出了11列14行 员工表在前部门前在后
select*from emp e,dept d where e.deptno=d.deptno;
–显式内连接inner join …on 查询出两张表交叉的以后的内容 和隐式一样结果
select*from emp e inner join dept d on e.deptno=d.deptno;
以下都是查几列的 并加条件的内连接查询
–查询员工编号,姓名 经理编号 姓名
from来自于员工的两张 并取别名来查,再第一张的编号和第二张的经理编号就得出了是领导的员工编号最后select过滤出来的就是员工编号 姓名 经理编号 姓名的列
select e.empno,e.ename,e.mgr,m.ename from emp e,emp m where e.mgr=m.empno
–查询员工编号,姓名 部门 经理编号 姓名
select e1.empno,e1.ename,e1.mgr,m1.ename,d1.dname from emp e1,emp m1,dept d1 where e1.mgr=m1.empno and e1.deptno=d1.deptno;
–查询员工编号 姓名 部门 经理编号 姓名 部门
select e1.empno,e1.ename,d1.dname,e1.mgr,m1.ename,d2.dname from emp e1,emp m1,dept d1,dept d2 where e1.mgr=m1.empno and e1.deptno=d1.deptno and m1.deptno=d2.deptno
–查询出每个雇员的姓名，工资，部门名称，工资在公司的等级及其领导的姓名，领导的工资，以及领导所对应的等级
select DISTINCT e.ENAME,e.SAL,d.DNAME,g.GRADE,s.ENAME mgr_name,g2.grade m_grade from emp e,dept d,SALGRADE g,emp s,salgrade g2 where e.DEPTNO=d.DEPTNO and e.SAL BETWEEN g.LOSAL and g.HISAL and s.EMPNO=e.MGR and s.sal between g2.losal and g2.hisal
外连接: left/right join on
左外连接: 就是在等值连接的基础上加上左主表中的未匹配数据
右外连接:等值连接的基础上加上被右连接表的不匹配数据
全外连接 full join on
全外连接是在等值连接的基础上将左表和右表的未匹配数据都加上
select*from emp e left outer join dept d on e.deptno=d.deptno --左外连接
子查询/嵌套:
一个查询语句嵌套另一个查询内容 解决复杂查询单行子查询出来的结果只有单个值 > >= = > >= != 多行子查询出来的结果有多行 in any all exists
–查询最高工资的员工信息 先查信息 where条件为最大的
select*from emp where sal=(select max(sal)from emp)–7839
单行子查询:子查询出来的结果只有一行
–查询出比员工编号7654的工资高 同时和7788号从事相同工作的员工信息 先查员工信息再条件查工资比7654高同时…
select*from emp where sal>(select sal from emp where empno=7654) and job=(select job from emp where empno=7788)
查询 每个部门最低工资的员工信息和他所在部门信息 先查部门…
select deptno,min(sal) minsal from emp group by deptno–先查各部门最低工资的分组再查每个部门最低的员工 连接他所在的部门
select*from emp e1,(select deptno,min(sal)minsal from emp GROUP BY deptno)t1, dept d1 where e1.DEPTNO =t1.deptno and e1.sal=t1.minsal and e1.deptno=d1.deptno;
多行子查询 in , not in , any , all, exists,
in表示值是否存在子查询结果集中 not in反之 any任意 是表示子查询结果中任意一个，all表示子查询结果中的所有
– 查询出比10号部门任意员工薪资高的员工信息
select sal from emp where deptno = 10; --先查10号部门里任意员工工资 再以条件 查整个表
select * from emp where sal >any(select sal from emp where deptno = 10);
–查询所有是领导的员工信息 编号是 mgr里的 去重
selectfrom emp where empno in(select distinct mgr from emp)
selectfrom emp where empno =any(select distinct mgr from emp)
–查询所有不是领导的员工信息 有空值is not null
selectfrom emp where empno not in(select distinct mgr from emp where mgr is not null)
selectfrom emp where empno !=all(select distinct mgr from emp where mgr is not null)
exists(查询语句): 存在如果查询语句,有结果,则返回true,否则返回false
–如果有7339编号存在 就查询所有
select * from emp where exists(select * from emp where empno=7369);
– 查询有员工的部门信息
select * from dept d where exists(select * from emp e where e.deptno=d.deptno);
– 关联子查询和非关联子查询
关联子查询: 子查询依赖外层查询
先执行外层查询,然后再执行内层查询
非关联子查询: 子查询可以单独执行,不依赖外层查询条件
先执行子查询,再执行外层查询,内层子查询只执行一次
按照行划分: 单行子查询多行子查询
按照出现的位置划分:select from 当作一张表处理,需要取别名where having
select 后面接子查询:
select ename,deptno from emp;---- 查询员工姓名和部门名称 下面再接子查询
select ename,(select dname from dept d where d.deptno=e.deptno) from emp e;
– from后面接子查询 ( 注意: 子查询当作表处理 子查询一定要取一个别名)
薪资高于10号部门平均工资的所有员工信息having后面接子查询
select deptno,avg(sal) from emp group by deptno having avg(sal)>(select avg(sal) from emp where deptno=30);
练习一: 找到员工表中工资最高的前三名(只要前三条)
select * from emp order by sal desc;
rownum : 伪列 ,代表的是行号
rownum 默认起始值是 1 ,每查询出一条记录, rownum++
rownum :主要运用在分页查询
– 查询行号大于3的所有记录
select rownum,emp.* from emp where rownum > 3;
– 查询行号小于3的所有记录
select rownum,emp.* from emp where rownum < 3;
select rownum,emp.* from emp where rownum >=1;
– rownum没有顺序了
/*
from … where … group by … having … select …rownum… order by
/
select emp. from emp order by sal desc;
select rownum,tt.* from (select emp.* from emp order by sal desc) tt;
– 查询工资最高的前三名
select rownum,tt.* from (select emp.* from emp order by sal desc) tt where rownum <=3;
练习二:找到员工表中薪水大于本部门平均薪水的员工
– 1.分组统计每个部门的平均薪水
select deptno , avg(sal) avgsal from emp group by deptno;
– 2. 连接查询结果
select * from emp e,(select deptno , avg(sal) avgsal from emp group by deptno) tt
where e.deptno = tt.deptno and e.sal > tt.avgsal;
练习三:统计每年入职员工的个数
select hiredate from emp;
– 只显示年
select to_char(hiredate,‘yyyy’) from emp;
– 分组统计每年入职个数
select to_char(hiredate,‘yyyy’) yy,count(1) cc from emp group by to_char(hiredate,‘yyyy’);
– 1. 先将1987竖起来
select case yy when ‘1987’ then cc end
from
(select to_char(hiredate,‘yyyy’) yy,count(1) cc from emp group by to_char(hiredate,‘yyyy’)) tt;
– 2. 去除1987中空值
select sum(case yy when ‘1987’ then cc end) “1987”
from
(select to_char(hiredate,‘yyyy’) yy,count(1) cc from emp group by to_char(hiredate,‘yyyy’)) tt;
– 3. 计算Total
select sum(cc) “TOTAL”
from
(select to_char(hiredate,‘yyyy’) yy,count(1) cc from emp group by to_char(hiredate,‘yyyy’)) tt;
– 4. 合并1987和total
select sum(cc) “TOTAL”,
sum(case yy when ‘1987’ then cc end) “1987” from
(select to_char(hiredate,‘yyyy’) yy,count(1) cc from emp group by to_char(hiredate,‘yyyy’)) tt;
– 5. 最终结果
select sum(cc) “TOTAL”,
sum(case yy when ‘1987’ then cc end) “1987”,
sum(case yy when ‘1980’ then cc end) “1980”,
sum(case yy when ‘1981’ then cc end) “1981”,
sum(case yy when ‘1982’ then cc end) “1982”
from
(select to_char(hiredate,‘yyyy’) yy,count(1) cc from emp group by to_char(hiredate,‘yyyy’)) tt;
rownum 和rowid
– 查询第5-10条记录
– 1.查询前10条记录
select rownum line,emp.* from emp where rownum <=10;
– 2. 查询行号>=5的记录
select * from (select rownum line,emp.* from emp where rownum <=10) tt where line >=5;
rowid: 伪列,代表的每行记录在磁盘中存放的真实的物理地址rowid,用在索引查询
select rowid,emp.* from emp where deptno>20 order by sal;
集合运算: 并集,交集,差集
员工表 , 经理表
工资,婚否 工资,婚否
所有员工的婚否
工资大于1500 或者 20号部门下的员工-- 并集
select * from emp where sal > 1500
union
select * from emp where deptno=20;
– 工资大于1500 并且 20号部门下的员工–交集
select * from emp where sal > 1500
intersect
select * from emp where deptno=20;
– 工资大于1500 并且不是20号部门下的员工–差集
select * from emp where sal > 1500
minus
select * from emp where deptno=20;
– 集合运算注意事项
/*
1.列的数量要一致 如果不足,可以用null补齐
2.列的类型要保持一致
3.列的顺序要一致
*/select ename,sal from emp where sal > 1500
union
select ename,null from emp where deptno = 20;
表空间
数据库 —> 实例Orcl —> 表空间(用户) —> 数据文件
地球 —> China —> 省份(用户) —> 土地
创建表空间 2创建用户 root3 用户去建表
create tablespace 表空间的名称datafile '存放的未知'size 初始大小autoextend onnext 每次增长多少
1创建表空间
先需要登录管理员帐号 system 密码root
create tablespace shenzhen
datafile ‘c:\shenzhen.dbf’
size 100m
autoextend on
next 10m;
– 删除表空间drop tablespace shenzhen;
2创建用户
create user 用户名 identified by 密码 default tablespace 表空间名称
*/
create user zhangsan
identified by zs408
default tablespace shenzhen;
3用户授权
grant create session to zhangsan;
revoke create session from zhangsan;
– 授予DBA(数据库管理员)角色,拥有权限
grant dba to zhangsan;
select * from scott.emp;
update scott.emp set ename=‘ALLEN’ where ename = ‘ALL%EN’;
4创建表
格式:
create table 表名(字段 字段类型 [约束],字段 字段类型 [约束]
);
char(最大长度) : 固定长度字符串 char(10) hello , 占10个, 不满10 用空格补满
字段类型:
varchar2(最大长度) : 可变长度字符串varchar2(10) hello , 占5个
number(总长度,小数位数)
date : 年月日时分秒
timestamp : 时间戳 , 精确到毫秒
Long : 大字符串类型, 最大支持保存2G
CLOB : Character Large Object 字符串大对象 最大4G
BLOB : Binary Large Object 二进制大对象 4G
– 创建表
create table person(
name varchar2(20),
age number,
salary number(10,2)
);
还可以-- 使用子查询创建表, 相当于复制表,包含了表结构和表数据,但是不包含约束
create table emp as select * from scott.emp;
select * from emp;
使用子查询创建表,只要表的结构 后面故意条件是没有的条件create table emp1 as select * from scott.emp where empno=1234567;
select * from emp1;
修改表
– 修改表格式: 添加列add, 重定义列 modify, 修改列名 rename, 删除列 drop, 修改表名,rename
alter table person add ( phone varchar2(11),address varchar2(20) );
– 重定义列 modify
alter table person modify phone number;
– 修改列名 rename
alter table person rename column phone to mobile;
– 删除列名 drop
alter table person drop column mobile;
– 修改表名
rename person to person1;
删除表 drop table person1;
表的约束
表的五大约束:
主键约束: primary key , 非空唯一唯一约束: unique , 唯一可以为空非空约束: not null, 不能为空检查约束: check(条件),mysql中可以写,但是mysql直接忽略掉外键约束: foreign key , 约束从表中的记录必须参考主表中的记录
单表约束
create table person(pid number primary key,name varchar2(20) unique,age number not null,sex varchar2(20) check(sex in('男','女','妖'))
);
-- male,female,ladyboy
insert into person values(1,'zhangsan',18,'妖');
insert into person values(2,'lisi',18,'妖');
外键约束 外键约束: foreign key , 约束从表中的记录必须参考主表中的记录 主表是商品类型 从表是商品表 有主键是外键的是主表
create table category(cid number primary key,cname varchar2(20)
);create table product(pid number primary key,pname varchar2(20),cno number
);insert into category values(1,'手机数码');
insert into product values(100,'锤子',11);truncate table product;
-- 添加外键约束
alter table product add foreign key(cno) references category(cid);
insert into product values(100,'锤子',1);-- 删除主表: 先取消外键约束,再删除自己
drop table category cascade constraint;-- 级联删除,添加外键约束
--
alter table product add foreign key(cno) references category(cid) on delete cascade;-- 删除category主表中的记录
-- 级联删除: 先去从表中查看有没有关联的数据,如果有,则先删除从表中的记录,再删除主表中记录
delete from category where cid=1;select * from category;
select * from product;
增删改表操作
/*插入数据 insert into
*/
-- 使用子查询插入数据
select * from emp;
select * from emp1;-- 将10号部门的所有员工信息插入到emp1中
insert into emp1 select * from scott.emp where deptno=10;/*删除所有数据:delete 和 truncate逐条删除数据 先删除再创建表DML DDL支持事务 不支持事务效率高
*/
通过Navicat Premium 实现Oracle基本操作
system root，连接后。我们点击面板上的“其他”下的选项“表空间->新建表空间->弹出设置界面
表空间类型不要改 名 随便填.DBF 大小100选m 路径是服务器上的地址 自动扩展on 下一个大小20M 其它不要填 保存 服务器对应路径就有个文件了
新建用户:点击面板上的“用户”选项->新建用户->设置界面
用户名”处填写的用户名字母一定是大写,密码设置,默认表空间”处，我们选择理科前面我们新建的表空间 临时表空间选temp
“成员属于”中我们选择了“DBA”授予和默认就可以保存了。其实对于一般的用户，我们只需赋予connect、resource、服务器权限create view这几个权限即可。但是这里我们为了演示方便就直接选择“DBA”就可以保存了…
事务
/*事务: 一组操作,要么都成功,要么都失败事务特型:原子性一致性隔离性持久性不考虑隔离级别的问题： 脏读， 虚读／幻读，不可重复读mysql隔离级别:READ UNCOMMITTED 读取未提交 脏读， 虚读／幻读，不可重复读READ COMMITTED 读取已提交, 虚读／幻读，不可重复读REPEATABLE READ 可重复读, 虚读／幻读，SERIALIAZABLE 串行化 Oracle隔离级别:READ COMMITTED (默认)SERIALIAZABLEREAD ONLY 事务回滚点/保存点savepoint 保存点的名称rollback to 保存点 主要使用场景: 数据迁移的时候, 1000万 100万提交一次, 50万的时候,
*/
create table lou(f number primary key
);-- PLSQLB编程
declare-- 声明部分
begin-- 业务逻辑insert into lou values(1);insert into lou values(2);insert into lou values(3);savepoint aa;insert into lou values(3);insert into lou values(4);insert into lou values(5);commit;
exception -- 捕获异常when others then -- 处理异常rollback to aa;commit;
end;select * from lou;
视图:对查询结果的封装
语法: create [or replace] view 视图名称 as 查询语句
/*视图view: 实际是对查询结果的封装,视图本身不存储任何数据,所有的数据都存放在原来的表中作用:封装复杂查询语句屏蔽表中的细节语法:create [or replace] view 视图名称 as 查询语句 [with read only]
*/
-- 封装员工表
create or replace view view_test1 as select ename,job,mgr from emp;
select * from view_test1;-- 同义词
create synonym yuangong for view_test1;
select * from yuangong;-- 通过视图去修改数据
update view_test1 set ename='SMITHaaaa' where ename='SMITH';select * from emp;-- 创建只读视图
create or replace view view_test2 as select ename,job,mgr from emp with read only;
update view_test2 set ename='SMITHaaaa' where ename='SMITH';
序列 生成自增的编号
语法: create sequence 序列的名称
/*序列: 1,2,3,4,5,6,7模拟类似auto_increment作用作用: Oracle中主要是用来生成自增的编号 语法:create sequence 序列的名称start with 从几开始incrementby 每次增长多少minvalue | nominvaluemaxvalue | nomaxvaluecycle | nocyclecache n 缓存cache 51,2,3,4,56,7,8,9,10 序列的属性:currval : 当前值 , 必须是调用过一次nextval之后nextval : 下一个值 注意: 序列中的数,一旦取过了, 无论回滚还是异常,都是永不回头的向下递增
*/
-- 1,3,5,7,9,1,3,5,7,9.....
create sequence seq_test1
start with 1
increment by 2
minvalue 1
maxvalue 9
cycle
cache 3;-- 获取当前值
select seq_test1.currval from dual;-- 获取下一个值
select seq_test1.nextval from dual;-- 通常创建序列的写法
create sequence seq_test2;
select seq_test2.nextval from dual;truncate table lou;select * from lou;insert into lou values(seq_test2.nextval);索引 int_coll 索引后查询巨快
什么是索引: 一种已经排好序的数据结构 能够查询效率, 索引还可以提高排序效率
语法: create index 索引名称 on 表名(列名1,列名2…);
主键约束: 自带唯一索引 唯一约束: 自带唯一索引
-- 创建测试数据500万
-- 创建一张表
create table wbw(c1 number primary key, c2 varchar2(20),c3 varchar2(20)
); -- 插入500万条记录索引
create sequence seq_wbw;-- PLSQL中的循环
declarebeginfor i in 1..5000000 loopinsert into wbw values(seq_wbw.nextval,'c2:'||i,'c3:'||i);end loop; commit;
end;select count(*) from wbw;-- 在没有创建索引的情况下 6.489s
select * from wbw where c2='c2:4000000';-- 0.023
select * from wbw where c1=4000000;-- 创建索引
create index wbw_c2 on wbw(c2);
PLSQL编程
Procedure Language 实际上是Oracle对SQL语言的能力扩展,让SQL语言拥有了if条件判断,for循环处理
declare -- 声明部分相当于语法:java-- class--- main
变量名 变量类型 := 初始值
变量名 emp.sal%type; -- 引用类型的变量 知道具体的列
变量名 emp%rowtype; -- 记录型变量 记录一行
变量名 number;
begin
-- 业务逻辑
dbms_output.put_line('hello world !'); 相当于是java打印
end;
基本语法
declare
i number :=5000;--先定义变量i并赋值
begindbms_output.put_line('你好呀'||i);--打印结果是:你好呀5000
end;declarevsal emp.sal%type;--先定义 引用类型的变量vsal 为具体列
beginselect sal into vsal from emp where empno=7369;--给变量赋值 为查询结果编号7369的工资dbms_output.put_line(vsal);--打印vsal 是emp表里工资 条件是编号7369输出是:4600
end;declarevrow emp%rowtype;--先定义 记录型变量vsal
begin
select * into vrow from emp where empno=7369;----给变量赋值 为编号是7369的一行记录dbms_output.put_line(vrow.empno||':'|| vrow.ename);--打印 7369:SMITH/史密斯
end;
if条件判断
/*if条件判断if 条件1 thenelsif 条件2 thenelseend if;
*/
-- 根据不同年龄输出信息
declareage number := &请输入;--&会弹出输入框 请输入 为输入名称输入的 为值
beginif age<=18 thendbms_output.put_line('未成年人'); elsif age>18 and age<=24 thendbms_output.put_line('年轻人');elsif age>24 and age<48 thendbms_output.put_line('中年人');elsedbms_output.put_line('老年人');end if;
end;
三种循环
三种循环语法
for循环: for 变量名 in 起始值..结束值 loopend loop; while循环语法: while 条件 loopend loop;loopexit循环语法: exit when 退出的条件循环体end loop;-- 输出1-10
declarebeginfor i in 1..10 loopdbms_output.put_line(i);end loop;
end;-- 输出10-1
declarebeginfor i in reverse 1..10 loopdbms_output.put_line(i);end loop;
end;-- 输出1-10
declarei number :=1; --变量i赋值为1
beginwhile i<=10 loopdbms_output.put_line(i);i := i+1;--i的变化为+1end loop;
end;-- 输出1-10
declarei number :=1;
beginloopexit when i>10;dbms_output.put_line(i);i := i+1;end loop;
end;
游标 是对查询结果集的封装
游标: (光标/指针) 是对查询结果集的封装, 相当于是jdbc中的ResultSet语法:声明游标:
cursor 游标名 is 查询语句
cursor 游标名(参数名 参数类型) is 查询语句 where empno=参数名开发步骤:1.打开游标 open 游标名2.从游标中提取数据:fetch 游标名 into 变量游标名%notfound 没有数据游标名%found 找到数据3.关闭游标 close 游标名 -- 输出所有员工的信息
declarecursor vemps is select * from emp; -- 声明游标vrow emp%rowtype;-- 声明变量
beginopen vemps; --1. 打开游标loop --2. 提取数据fetch vemps into vrow;--赋值给vrowexit when vemps%notfound;-- 判断无数据就退出查询dbms_output.put_line('姓名:'||vrow.ename||' 工资:'||vrow.sal);-- 打印数据end loop;close vemps; -- 关闭游标
end;-- 输出指定部门的员工信息
declare-- 声明游标cursor vemps(vdeptno number) is select * from emp where deptno = vdeptno;vrow emp%rowtype; -- 声明记录型变量
beginopen vemps(20);-- 1. 打开游标loop-- 2.循环遍历游标fetch vemps into vrow;exit when vemps%notfound;--判断没有数据就退出-- 打印数据dbms_output.put_line('姓名:'||vrow.ename||' 工资:'||vrow.sal);end loop;close vemps;-- 3. 关闭游标
end;
游标练习
-- 按照职位涨工资,总裁涨1000 , 经理涨800 , 其他人涨400
/*游标: 所有员工遍历所有员工根据职位涨工资
*/
declare-- 声明游标cursor vemps is select * from emp; -- 声明记录型变量vrow emp%rowtype;
begin-- 1.打开游标open vemps;-- 2.游标 循环提取数据给变量loopfetch vemps into vrow;exit when vemps%notfound;--游标没有查询了才退出-- 根据职位涨工资 if vrow.job = 'PRESIDENT' thenupdate emp set sal=sal+1000 where empno=vrow.empno;elsif vrow.job = 'MANAGER' thenupdate emp set sal=sal+800 where empno=vrow.empno; elseupdate emp set sal=sal+400 where empno=vrow.empno;end if;end loop;-- 关闭游标close vemps;-- 提交数据commit;
end;/*excel产品 --- > xml,json php,python扩展: 使用for循环遍历游标*/
declare-- 声明游标cursor vemps is select * from emp;
beginfor vrow in vemps loopdbms_output.put_line(vrow.ename);end loop;
end;
例外 相当于是java异常
/*例外 (意外): 相当于是java异常语法:declare声明部分begin业务逻辑exception处理例外when 例外1 thenwhen 例外2 thenwhen others thenend;常见的系统的例外zero_divide : 除零例外value_error : 类型转换no_data_found : 没有找到数据例外too_many_rows : 查询出多行记录,但是赋值给了单行变量
*/
declarei number; vrow emp%rowtype;
begin-- i := 5/0;-- i := 'aaa';-- select * into vrow from emp where empno=1234567;select * into vrow from emp ;
exceptionwhen too_many_rows thendbms_output.put_line('查询出多行记录,但是赋值给了单行变量');when no_data_found thendbms_output.put_line('发生了没有找到数据例外');when value_error thendbms_output.put_line('发生了类型转换的例外');when zero_divide then dbms_output.put_line('发生除零例外');when others thendbms_output.put_line('发生了未知的例外');
end;
自定义例外
/*自定义例外声明:例外名称 exception;抛出自定义例外:raise 例外名称
*/
declare-- 声明例外no_emp_found exception;
begin-- 抛出例外raise no_emp_found;
exception-- 捕获例外when no_emp_found thendbms_output.put_line('自定义例外'); when others thendbms_output.put_line('未知例外');
end;- 查询指定编号的员工,若这个员工没有找到,则抛出自定义的例外
-- 正确做法:
declare-- 声明例外no_emp_found exception;-- 定义记录型变量vrow emp%rowtype;-- 声明游标cursor vemps is select * from emp where empno=1234567;
begin-- 打开游标open vemps;-- 向下移动游标fetch vemps into vrow;-- 判断游标是否找到数据if vemps%notfound thenraise no_emp_found;end if;-- 关闭游标close vemps;
exception when no_emp_found thendbms_output.put_line('捕获了自定义的例外');when others thendbms_output.put_line('未知的例外');
end;
存储过程 编译好的PLSQL代码片断,封装在数据库中 相当于方法调用的时候传参数
/*相当于是在java main函数里面写了一些业务逻辑
-- 将main函数中的业务逻辑抽取成单个的方法存储过程: 实际上是将一段已经编译好的PLSQL代码片断,封装在数据库中作用:1. 提高执行效率2. 提高代码复用性语法:create [or replace] procedure 过程名称(参数1 in|out 参数类型,参数2 in|out 参数类型)is | as-- 声明部分begin-- 业务逻辑end;
*/
-- in代表输入参数 out代表输出参数
-- 给指定员工涨薪,并打印涨薪前和涨薪后的工资
-- 员工编号 : 输入参数
-- 涨多少 : 输入参数
/*1. 查询当前工资2. 打印涨薪前工资3. 涨工资4. 打印涨薪后的工资5. 提交数据
*/
-- select * from emp where empno=1234567; ---> 发送给数据库 ---> 分析编译---> 执行-- 给指定员工涨工资, 并且打印涨工资前的工资和涨工资之后的工资
-- 参数: 员工编号 in
-- 参数: 涨薪数量 in
create or replace procedure proc_updatesal(vempno in number,vcount in number)
is-- 声明变量,接收当前工资vsal emp.sal%type;
begin-- 1.查询当前工资select sal into vsal from emp where empno=vempno;-- 2.打印当前工资dbms_output.put_line('涨薪前:'||vsal);-- 3.涨工资updateupdate emp set sal=vsal+vcount where empno=vempno; -- 4.打印涨薪后的工资dbms_output.put_line('涨薪后:'||(vsal+vcount));-- 5.提交事务commit;
end; -- 调用存储过程,方式1
call proc_updatesal(7369,100);-- 方式2
declarebeginproc_updatesal(7369,200);
end;-- 封装一个获取指定编号员工年薪的存储过程
-- 参数: 员工编号 in
-- 参数: 年薪 out
create or replace procedure proc_getyearsal(vempno in number,vyearsal out number)
is
beginselect sal*12+nvl(comm,0) into vyearsal from emp where empno=vempno;
end;-- 调用获取年薪
declareyearsal number;
beginproc_getyearsal(7369,yearsal);dbms_output.put_line('年薪:'||yearsal);
end;-- 获取指定编号员工的年薪
/*编号: in 输入年薪: out 输出
*/
create or replace procedure proc_getyearsal(vempno in number,vyearsal out number)
isbeginselect sal*12+nvl(comm,0) into vyearsal from emp where empno=vempno;
end;-- plsql代码片断中调用
declareyearsal number;
beginproc_getyearsal(7369,yearsal);dbms_output.put_line(yearsal);
end;-- 封装存储过程,输出的是游标类型, 所有员工
/*sys_refcursor : 系统引用游标
*/
create or replace procedure proc_getemps(vemps out sys_refcursor)
isbegin-- 打开游标, 谁调用谁关闭open vemps for select * from emp;
end;declarevemps sys_refcursor;vrow emp%rowtype;
begin-- 调用存储过程proc_getemps(vemps);loopfetch vemps into vrow; exit when vemps%notfound; dbms_output.put_line(vrow.ename);end loop;-- 关闭游标close vemps;
end;
存储函数
/*存储函数: 实际上是将一段已经编译好的PLSQL代码片断,封装在数据库中 作用:1. 提高执行效率2. 提高代码复用性语法:
create [or replace] function 函数名称(参数1 in|out 参数类型) return 返回类型isbeginend; 存储过程和存储函数:1. 函数有返回值,过程没有2. 函数可以直接在SQL语句中使用,过程不可以3. 函数能实现的功能,过程能实现4. 过程能实现的功能,函数也能实现5. 函数和过程本质上没有区别通常情况下,我们自己开发封装的是存储过程
*/
-- 存储函数: 获取指定编号员工的年薪
-- 参数 : 员工编号
-- 返回 : 年薪
create or replace function func_getyearsal(vempno number) return number
is -- 声明变量vyearsal number;
beginselect sal*12+nvl(comm,0) into vyearsal from emp where empno=vempno;return vyearsal;
end;-- 调用
declareyearsal number;
beginyearsal := func_getyearsal(7369);dbms_output.put_line('年薪:'||yearsal);
end;
-- 直接在SQL语句中使用
select emp.*,func_getyearsal(empno) from emp;/*使用java调用数据库中封装好了存储过程用户注册 ---> 数据校验javaEE后台什么校验都不写, 直接将所有校验都丢给数据库存储过程java调用存储过程的步骤:JDBC开发步骤:1. 注册驱动2. 获取连接3. 获取执行SQL的对象4. 执行SQL5. 获取结果6. 释放资源
*/
-- java 调用一个获取年薪的存储过程
-- 练习 : java调用输出类型为游标的存储过程
/*cursor 游标名 is 查询结果sys_refcursor : 系统引用游标, 游标类型声明
*/
create or replace procedure proc_getemps(vemps out sys_refcursor)
isbegin-- 输出sys_refcursor20号部门下的所有员工-- 打开游标的时候,指定结果集open vemps for select * from emp where deptno=20;-- 谁用谁关闭
end;
-- 调用
declareemps sys_refcursor;vrow emp%rowtype;
begin-- 已经打开并且指定了结果集proc_getemps(emps);--循环提取游标中的数据loopfetch emps into vrow; exit when emps%notfound;dbms_output.put_line('姓名:'||vrow.ename);end loop;-- 关闭游标close emps;
end;/*自定义例外存储过程存储函数java调用存储过程触发器:当用户对表执行了insert | update | delete 这些操作的时候,可以使用触发器去触发一段PLSQL代码逻辑的执行作用:监听表中数据变化对表中数据进行校验 语法:create or replace trigger 触发器名称before | afterinsert | update | deleteon 表名[for each row]declarebeginend; 触发器的分类:行级触发器: 一条SQL语句, 影响了多少行记录, 触发器就会执行多少次两个内置对象::new 新的记录 :old 旧的记录update emp set sal=sal+100;:new.sal ---> sal+100:old.sal ---> sal语句级触发器: 一条SQL语句, 无论影响了多少行记录, 都只触发一次
*/
-- 若用户向表中插入数据之后, 打印一句话
create or replace trigger tri_test1
after
insert
on emp
declarebegindbms_output.put_line('有人插入了....');
end;insert into emp(empno,ename) values(9527,'华安');
-- 执行一条更新工资的语句-- 周二老板不在,不能办理员工入职(不能向员工表中插入数据)
-- 触发器
-- before insert
-- 判断今天是否是周二
select trim(to_char(sysdate,'day')) from dual;create or replace trigger tri_checkday
before
insert
on emp
declarevday varchar2(20);
begin-- 查询当前周几select trim(to_char(sysdate,'day')) into vday from dual;-- 判断是否为周二,若为周二,则需要中断插入操作if vday = 'tuesday' then-- -20000 - -20999raise_application_error(-20001,'周二老板不在,不能插入');end if;
end;insert into emp(empno,ename) values(9527,'华安');select * from emp;-- 语句级触发器
create trigger tri_test3
before
update
on emp
declarebegindbms_output.put_line('语句级触发器');
end;-- 行级触发器
create or replace trigger tri_test4
before
update
on emp
for each row
declarebegindbms_output.put_line('行级触发器,旧的工资:'||:old.sal||' 新的工资:'||:new.sal);
end;update emp set sal=sal+100;-- 6个月 ---> 人事 加薪 ---> 加10块钱 ---> 老板签字
-- 校验员工薪资 调整后的工资一定要 大于 薪资调整前的工资
-- 触发器: before update on emp
-- 行级触发器
create or replace trigger tri_checksal
before
update
on emp
for each row
declarebegin-- 调整后的工资 <= 薪资调整前的工资 ,则中断更新操作-- :new.sal <= :old.salif :new.sal <= :old.sal thenraise_application_error(-20002,'坑爹的,降薪啦!');end if;
end;update emp set sal=sal-100;/*使用触发器模拟类似auto_increment功能当用户插入的时候,若为sid为null,则给sid赋值一个编号*/
create table stu(sid number primary key,name varchar2(20)
);-- 创建一个序列
create sequence seq_stu;-- 触发器: before insert on stu
-- 行级触发器
create or replace trigger tri_auto
before
insert
on stu
for each row
declarebegin-- 从序列中查询一个数字出来,赋值给sidselect seq_stu.nextval into :new.sid from dual;
end;-- 同样一张表,有时候自己指定id, 有时候需要数据库自动生成id
insert into stu values(null,'zs');
insert into stu values(4,'zs');
select * from stu;
