oracle学习壁击

day1sql简单查询

oracle system权限最大；使用scott用户进行学习；

sql语句与英译汉差不多；

select 查询显示；* 所有字段； from 来自于；

number(7,2) 数字型 七位数字 两位小数

次方

power(2,3)

开方

sqrt（9）

取余数

mod(10,3)

函数count()积数

取小数位数四舍五入 round() 只舍不入 Trunc()

ROUND(3.14159,3) 取整写0

TRUNC(3.14159,4)

不等于<> !=

SELECT empno,ename,sal,deptno FROM emp WHERE sal>2000 ORDER BY sal DESC;

SELECT empno,ename,sal,deptno,job FROM emp WHERE job='MANAGER';

工资大于1500 后哦这部门是10号的人

SELECT empno,ename,sal,deptno FROM emp WHERE sal>1500 or deptno=10;

查询10号部门的manager

SELECT empno,ename,job,deptno FROM emp WHERE job='MANAGER' AND deptno=10;

查询工资大于1000的clerk 的 工号姓名工作工资部门 空格/as 后加别名

SELECT empno 工号,ename 姓名,job 工作,sal,deptno FROM emp WHERE sal>1000 AND job='CLERK' ORDER BY sal DESC;

虚拟表 dual不存咋真实数据的假表，她的存在为了让某些计算符合语法 SELECT * FROM 表;

SELECT (10*5)+7 FROM dual;

SELECT SYSDATE FROM dual;

day2

多个字段升降序按先后顺序而定

闭区间 开区间

>=300 <=900

>300 <=900

>=300 <900

闭区间的表示方式

between and

查看1000到5000工资的人员信息

select * from emp where sal between 1000 and 5000；

in 再元素中查找 not in 取反

空值特性：单元格里什么都没有

1.排序老大 2.不参与计算 select e.*,comm+200 from emp e;

3.表示方式

找出绩效为空的人员

select * from emp where comm is null;

select * from emp where comm is not null;

4.转空值 当值为空值时，把它转为另一个值

nvl（）

给每个人绩效加两百

伪列 数据表中不存在的列，是查询寻出结果后赋予行数的基表行号

动态分配

ROWNUM从1开始数

不能写>和 >=

like 模糊查询

同查和通配符结合使用

% 匹配0-n字符

_匹配一个字符

escape ‘/’

查询名字以S开头，第三位是O，最后一位是T的人员

SELECT * FROM emp WHERE ename LIKE 'S_O%T';

ESCAPE '/'把紧跟着/后面的一个通配符转义为普通字符

查询名字中含有％的人

SELECT * FROM emp WHERE ename LIKE '%/%' ESCAPE '/';

any 任意一个

SELECT * FROM emp WHERE sal =ANY(1000,2000,3000)

all 所有

SELECT * FROM emp WHERE sal >ALL(1000,2000,3000)

COUNT(*)

COUNT(1)

共同点：只要有一行数据就计1

不同点：查询效率上，如果一个表只有一个列（一个字段），用count(*)快

​ 多列 count(1)

COUNT(字段) 如果这个字段是逐渐，这是三个count中最快的

​ 如果不是主键会不计算null

多少人

SELECT COUNT(1),COUNT(*),COUNT(empno),COUNT(comm) FROM emp;

查询10号和20号部门的人员最高工资是夺少

SELECT deptno,MAX(sal) 最高 FROM emp WHERE deptno in(10,20) GROUP BY deptno

导入

imp 用户名/密码@实例 file=‘备份文件的路径\文件名.dmp’ full =y

day3

分组 group by

分组过滤 having 聚合后再次筛选

oracle执行顺序

1. FROM 2. WHERE 3. GROUP BY 4. HAVING 5.SELECT 6.ORDER BY

SELECT 查询看到的字段或计算 FROM 表明 WHERE 筛选条件 GROUP BY 分组条件 HAVING 对聚合的再次筛选 ORDER BY 排序规则；、

where和having

where 筛选的行数据

having 筛选聚合数据

CONCAT() 字符拼接

SELECT CONCAT('1','2') FROM dual;

SELECT '1'||'2'||'3'FROM dual;

LENGTH() 计算字符串长度

SELECT LENGTH('十九点'||'sfs') FROM dual;

字符替换

单个替换

REPLACE()

aabbcc a替换成1

SELECT replace('aabbccaadd','aa',1) FROM dual;

逐个替换 translate

aabbccdd

SELECT translate('abcdc','ab',12) FROM dual;

被替换字符多则对不上的不会生效，少则丢失

对字母的操作

upper（）转大写

lower（）转小写

initcap（）首字母大写

SELECT UPPER('aabBDc'),LOWER('aaBdDc'),INITCAP('aabb') FROM dual

对空格的操作

trim()去两边的空格

LTRIM()去左边的空格

RTRIM()去右边的空格

SELECT rTRIM(' aa ') FROM dual;

计算函数

MOD()取余

round()四舍五入

trunc（）

power（）

sqrt（）

sum（）

min（）

count（）

abs（）

ceil（）向上取整

floor（）向下取整

• day5

instr（str,’ ',1, ）最后没有表示查到最后

case when 条件表达式

还有一种只能写等于的情况

SELECT e.*,

       CASE job WHEN 'MANAGER' THEN '经理'

        ELSE 'a' end

 FROM emp e

-----------还有一种只能表示等于的情况

oracle 特有的

 DECODE 表示 CASE 字段 WHEN

 SELECT e.*,

    DECODE(job,'MANAGER','经理','杂务工')

 FROM emp e;

## 

day4

RPAD(ename,10,‘-’)右填充 填充的字段(可剪切，决定从原字段第几位开始填充)，填充的总长度，

lpad(ename,20,’ ')左填充 括号内（填充字段，填充后的字符长度，填充的字符）

SELECT RPAD(ename,10,'-')右填充,lpad(ename,20,' ')左填充 FROM emp;

日期函数

to_date(‘2024-03-28’,‘yyyy-mm-dd’)

SELECT * FROM emp WHERE hiredate >=to_date('1982-12-01','yyyy-mm-dd') *--不写时分秒默认为00：00：00*

SELECT SYSDATE FROM dual; *--获取系统时间*

SELECT DATE'2000-4-14' FROM dual; *--不支持时分秒*

SELECT to_date('1919-12-12 13:12:12','yyyy-mm-dd hh24:mi:ss') FROM dual; *--to_date支持时分秒*

SELECT * FROM emp WHERE hiredate >=to_date('1982-12-03','yyyy-mm-dd')

日期±

SELECT sysdate+300 FROM dual;

SELECT SYSDATE-300 FROM dual;

SELECT to_date('2024-01-01','yyyy-mm-dd'),DATE'2024-01-01' FROM dual;

SELECT SYSDATE -DATE'2023-04-09' FROM dual;

月份加减

SELECT add_months(SYSDATE,18) FROM dual

查询 今天距离2020-05-28有多少个月

MONTHS_between

SELECT months_between(SYSDATE,DATE'2020-05-28')FROM dual

SELECT SYSDATE 系统日期,

​ SYSDATE + 1 日期加减，DATE ‘2020-1-1’ - 2 日期加减天数,

​ ADD_MONTHS(SYSDATE, -3) 加减月份,

​ last_day(date’2001-2-1’)当月最后一天,

​ next_Day(SYSDATE,1) 下一周第几天周日为1,

​ TRUNC(sysdate,‘yyyy’)按年取整,

​ trunc(SYSDATE,‘mm’)月取整，

​ TRUNC(SYSDATE,‘dd’) 天取整,

​ ROUND(sysdate,‘yyyy’)按年四舍五入,–返回的就是明年的第一天 区分上下半年

​ ROUND(sysdate,‘mm’) 按月四舍五入,

​ ROUND(sysdate,‘dd’)按天四舍五入

FROM DUAL;

 SELECT ROUND(DATE'2023-06-30','yyyy')FROM dual

SELECT SYSDATE 系统日期  FROM DUAL;

SELECT SYSDATE + 1 日期加减，DATE '2020-1-1' - 2 日期加减天数 FROM DUAL;

SELECT ADD_MONTHS(SYSDATE, -3) 加减月份  FROM DUAL;

SELECT last_day(date'2001-2-1')当月最后一天  FROM DUAL;

SELECT next_Day(SYSDATE,1)  FROM DUAL; *--下一周第几天,周日为1*

SELECT TRUNC(sysdate,'yyyy')按年取整  FROM DUAL;*--返回的就是今年的第一天 换位mm或dd则是按月和天取整*

      ROUND 同理

计算上个月的第一天，下个月的最后一天

SELECT TRUNC((add_months(SYSDATE, -1)),'mm'), last_day(add_months(SYSDATE,+1)) FROM dual

计算emp表1号入职的人

SELECT * FROM emp WHERE hiredate=TRUNC(hiredate,'mm')

SELECT EXTRACT(DAY FROM Sysdate) AS hire_month

FROM dual

（1）四年一闰百年不闰：即如果year能够被4整除，但是不能被100整除，则year是闰年。

（2）每四百年再一闰：如果year能够被400整除，则year是闰年。

计算emp表，入职日期是闰年的

WHERE MOD(YEAR(hiredate),4)=0

先计算

SELECT  *

 FROM

(SELECT e.*,last_day( ADD_months(TRUNC(hiredate,'yyyy'),+1)) a FROM emp e) e WHERE to_number(to_char(a,'dd'))=29

SELECT  *

 FROM

(SELECT e.*,last_day( ADD_months(TRUNC(hiredate,'yyyy'),+1)) a FROM emp e) WHERE to_char(a,'dd')=29

SELECT * FROM emp WHERE MOD(to_number(to_char(hiredate,'yyyy')),4)=0 AND MOD(to_number(to_char(hiredate,'yyyy')),100)!=0 OR MOD(to_number(to_char(hiredate,'yyyy')),400)=0

1.显示人员的名字为前三个字+后面有多少个字就有多少个星

LENGTH()字符个数

         substr要保留的数据     张三2个字符       2个字符  

SELECT RPAD(substr(ename,1,3),LENGTH(ename),'*') FROM emp

          rpad       4个字母长度     2个字母的长度

​ 5

SELECT substr(ename,1,3),LENGTH(ename) FROM emp

SELECT * FROM emp FOR UPDATE

2.查询1981年一月到三月入职的员工

SELECT * FROM emp WHERE hiredate >=to_date('1981-1','yyyy-mm') AND hiredate <=to_date('1981-3','yyyy-mm')

3.转正是三个月的话，查询员工转正的日期

SELECT e.*,add_months(hiredate,+3) FROM emp e

4查询北京奥运会距今多少年 08-8-8

SELECT (months_between(SYSDATE,DATE'2008-08-08'))/12 FROM dual;

SELECT (SYSDATE -DATE'2008-08-08')/365 FROM dual;

--------------------------------------------------------------------------------------

to_date() 把字符转换为时间

to_ NUMBER() 转为时间

to_char 数字转换为字符

nvl（） 空值转换

NVL2()

SELECT emp.*,NVL2(comm,11111,22222) FROM emp;

TO_char 把时间转换为各种想要的结果

SELECT emp.* FROM emp WHERE TO_CHAR(hiredate,'yyyy-mm-dd')>'1981-01-01'

SELECT to_char(SYSDATE,'d') FROM dual;dd ddd优先级最高

day5

CASE WHEN THE END

     WHEN 情况1 THEN 做事情1

     WHEN 情况2 THEN 做事情2

      WHEN 情况n THEN 做事情n

       ELSE 否则做什么 END；

例子

当job是manager时 显示为经理

当他时clerk 显示为职员

当他时saleman时显示为销售员

其他情况显示为杂务工

SELECT e.*,CASE WHEN job='MANAGER' THEN '经理'

        WHEN job='CLERK' THEN '职员'

         WHEN job='SALEMAN' THEN '销售'

          ELSE '杂务工'

END FROM emp e

还有一种只能写等于的情况

SELECT e.*,

       CASE job WHEN 'MANAGER' THEN '经理'

        ELSE 'a' end

 FROM emp e

-----------还有一种只能表示等于的情况

oracle 特有的

DECODE 表示 CASE 字段 WHEN

 SELECT e.*,

     DECODE(job,'MANAGER','经理','杂务工')

 FROM emp e;

当部门是10号时 显示出工资＋200

20时 显示工资+300

30时 显示工资-300

40时 工资+99

SELECT e.*,CASE deptno WHEN 10 THEN sal+200

         WHEN 20 THEN sal+300

          WHEN 30 THEN sal-300

           WHEN 40 THEN sal+99

            END

            FROM emp e

SELECT e.*,

        DECODE(deptno,10,sal+200,20,sal+300,30,sal-300,40,sal+9999)

 FROM emp e

当工资大于5000时输出你很屌哦，在四千到五千时输出菜坤，三千到四千输出牛比，其他输出小菜坤

SELECT sal,CASE WHEN sal>=5000 AND sal<6000 THEN '坤哥'

        WHEN sal BETWEEN 4000 AND 4999 THEN '坤坤'

         WHEN sal BETWEEN 3000 AND 3999 THEN '蔡坤'

          ELSE '小蔡坤' END 蔡坤们

 FROM emp e

要求按列显示

要求按列显示名字

10 20 30

zs sa ad

SELECT n,COUNT(1) FROM (

SELECT DECODE(deptno,10,10,20,10,30,10,40,10) n,

         CASE deptno WHEN 10 THEN ename END 十 ,

          CASE deptno WHEN 20 THEN ename END 二,

           CASE deptno WHEN 30 THEN ename END 三,

            CASE deptno WHEN 40 THEN ename END 四   

 FROM emp e ORDER BY n) a GROUP BY  n

 SELECT

 DECODE(货主地区,'华北','地区','华中','地区','华东','地区') n,

 max(CASE 货主地区 WHEN '华北' THEN 订单量 END) 华北,

 max(CASE 货主地区 WHEN '华中' THEN 订单量 END) 华中,

 MAX( CASE 货主地区 WHEN '华东' THEN 订单量 END) 华东 FROM

 (SELECT 货主地区,COUNT(1) 订单量 FROM ORDER_1 WHERE 货主地区 IN ( '华东','华北','华中') GROUP BY 货主地区) a GROUP BY n

 SELECT

sum(CASE 货主地区 WHEN '华北' THEN 订单量 END) 华北,

sum(CASE 货主地区 WHEN '华中' THEN 订单量 END) 华中,

sum(CASE 货主地区 WHEN '华东' THEN 订单量 END) 华东 FROM

 (SELECT 货主地区,COUNT(1) 订单量 FROM ORDER_1 WHERE 货主地区 IN ( '华东','华北','华中') GROUP BY 货主地区)

 

 SELECT n, 一,二,三,四 FROM(

SELECT DECODE(deptno,10,10,20,20,30,30,40,40) n,

        to_char( CASE deptno WHEN 10 THEN ename END ) 一 ,

          to_char(CASE deptno WHEN 20 THEN ename END) 二,

           to_char(CASE deptno WHEN 30 THEN ename END) 三,

            to_char(CASE deptno WHEN 40 THEN ename END) 四   

 FROM emp e )GROUP BY n

SELECT 

  deptno, 

  NVL(MAX(CASE WHEN deptno = '10' THEN ename END), '') AS "DepartmentA", 

  NVL(MAX(CASE WHEN deptno = '20' THEN ename END), '') AS "DepartmentB",

  NVL(MAX(CASE WHEN deptno = '30' THEN ename END), '') AS "DepartmentC",

  NVL(MAX(CASE WHEN deptno = '40' THEN ename END), '') AS "DepartmentD"

  *-- 其他部门...* 

FROM 

  emp 

GROUP BY 

  deptno;

累加效果

sum()OVER(ORDER BY)

AVG()OVER(ORDER BY)

SELECT e.*,AVG(sal)OVER(ORDER BY hiredate),

          MAX(sal)OVER(ORDER BY hiredate)

          FROM emp e

​

排名row_number RANK dense_rank

连续排名 条约排名 连续不跳越排名

90 1 1 1

80 2 2 2

70 3 3 3

70 4 3 3

60 5 5 4

按工资排名

SELECT e.*,

row_number()OVER(ORDER BY sal DESC),

RANK()OVER(ORDER BY sal DESC),

dense_rank()OVER(ORDER BY sal DESC)

 FROM emp e

查询显示每个岗位的工资排名情况

 SELECT e.*,row_number()OVER(PARTITION BY job ORDER BY sal DESC) FROM emp e

查询每个部门的工资前三名

 SELECT a.* FROM(

 SELECT e.*,row_number()OVER(PARTITION BY deptno ORDER BY sal DESC ) 排名 FROM emp e) a WHERE 排名<=3

查询每个部门第二个入职的人员日期

 SELECT a.* FROM

 (SELECT e.*,row_number()OVER(PARTITION BY deptno ORDER BY hiredate ) 排名 FROM emp e) a WHERE 排名=2

----------------------------------------------------------------

偏移

向上偏移 lead()

向下偏移 LAG()

​ 给偏移后的空值设置默认值

LEAD(偏移字段，偏移几行，默认值)

按入职的前后顺序，查询每一位员工比他上一位入职的多多少钱

SELECT LAG(sal,1)OVER(ORDER BY hiredate),

        sal-LAG(sal,1)OVER(ORDER BY hiredate)

 FROM emp

---------------------------------------------------------------------

同一个部门按入职时间顺序查询出员工比他下一位入职的多夺少钱？

 SELECT E.*,

    LEAD(SAL, 1) OVER(PARTITION BY DEPTNO ORDER BY HIREDATE),

    SAL - LEAD(SAL, 1) OVER(PARTITION BY DEPTNO ORDER BY HIREDATE)

  FROM EMP E;

 

查询工号相邻的员工，工资相差夺少钱？

 SELECT E.*,

    SAL - LAG(SAL, 1) OVER(ORDER BY EMPNO) 与上一位相比,

    SAL - LEAD(SAL, 1) OVER(ORDER BY EMPNO) 与下一位相比

  FROM EMP E

------------------------------------------------------------------------------------

同环比

同比 这一期比去年同一期

环比 这一期比上一期

南宁房价24-3 月房价 同比增长 和23-3月比

​ 环比 和24-2月比

2024-3 10000

2023-3 9000

2024-2 11000

同比增长

SELECT (10000-9000)/9000 FROM dual;

环比增长

SELECT (10000-11000)/11000 FROM dual;

（新-旧）/旧

---------------------------------------------------------

子查询

嵌套在另一个查询中的查询

1.放在select 后面

显示人员工号姓名工资部门编号部门名称

SELECT empno,ename,sal,deptno,(SELECT dname FROM dept d WHERE ) FROM emp

2.from 后面，结果集当成一个表再次查询

同样 WITH AS

铲鲟比部门平均工资高的人员

WITH aa AS

(SELECT e.*,AVG(sal)OVER(PARTITION BY deptno) 部门平均工资 FROM emp e)

SELECT * FROM aa WHERE sal >部门平均工资

3.放在where后面

查询和30号部门有同名的人员

SELECT * FROM emp WHERE ename IN (SELECT ename FROM emp WHERE deptno=30) AND deptno<>30

放在where后有一种特殊情况 单行多列

查询和6011这位员工同一天入职同一岗位的人员

SELECT * FROM emp WHERE (hiredate,job)=

(SELECT hiredate,job FROM emp WHERE empno=6011)

4.放在having 后面

查询比30 号部门人员多的部门

SELECT deptno,Count(1) FROM emp GROUP BY deptno HAVING COUNT(1)>(SELECT COUNT(1) FROM emp WHERE deptno =30)

---------------------------------------------------------------------------------------

多表查询

找到表和表之间的联系

笛卡儿积

a表的每一行数据关联b的每一行数据

球队的编号 让你显示亮亮对战的可能性

CREATE TABLE test_a (ID VARCHAR(2));

SELECT * FROM test_a a,test_a b WHERE a.id<b.id;

SELECT * FROM test_a a CROSS JOIN test_a b WHERE a.id<b.id ;

内连接

两个表能互相关联得上的数据

SELECT e.*,d.* FROM emp e ,dept d;

SELECT * FROM emp e INNER JOIN dept d ON d.deptno=e.deptno

------------------------------------------------

查询部门地址是new York 的人员信息

SELECT * FROM emp e INNER JOIN dept d ON d.deptno=e.deptno WHERE loc ='NEW YORK'

查询华南地区客户的所有订单

SELECT o1.* FROM order_1 o1 INNER JOIN USER_1 u1 ON o1.客户id=u1.客户id WHERE 地区='华南';

查询订单编号10407的订单销售额

SELECT sum(单价*数量)销售额 FROM

（SELECT * FROM ORDER_INFO WHERE 订单id=10407）GROUP BY 订单id

SELECT * FROM order_1 o INNER JOIN ORDER_INFO o1 ON o.订单id =o1.订单id WHERE 订单id=10407;

SELECT * FROM order_1 WHERE 订单id=10407;

SELECT * FROM order_info WHERE 订单id=10407

查看所有人员的信息何其对应部门的部门信息，没有部门的留空

左连接

LEFT JOIN

得到结果

以左边的表为主表，右边的表为从表，显示完主表的所有数据，从表只显示能和主表关联的上的信息

SELECT e.*,'--',d.* FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno

WHERE d.deptno IS NULL

用左连接得出没有其部门信息的人员

外连接注意字段

--------------------------------------

查询1997年有购买过东西，但是1998年没买过东西的客户

SELECT * FROM

(SELECT DISTINCT o.客户id,to_char(o.订购日期,'yyyy') FROM order_1 o WHERE to_char(订购日期,'yyyy')=1997) a

LEFT JOIN

(SELECT distinct o2.客户id,to_char(o2.订购日期,'yyyy') FROM order_1 o2 WHERE to_char(订购日期,'yyyy')=1998) b ON a.客户id = b.客户id

WHERE b.客户id IS null

查询1997年有购买过东西，但是1998年没买过东西的客户

SELECT * FROM

(SELECT DISTINCT o.客户id,to_char(o.订购日期,'yyyy') FROM order_1 o WHERE to_char(订购日期,'yyyy')=1998) a

RIGHT JOIN

(SELECT DISTINCT o2.客户id,to_char(o2.订购日期,'yyyy') FROM order_1 o2 WHERE to_char(订购日期,'yyyy')=1997) b

ON a.客户id=b.客户id JOIN User_1 u ON u.客户id=b.客户id

WHERE a.客户id IS NULL

全连接

FULL JOIN

不区分主从，显示完能互相关联的上的数据，在分别显示关联不上的数据，关联不上数据对应补null

SELECT e.*,'--',d.* FROM emp e FULL JOIN dept d ON e.deptno=d.deptno

SELECT * FROM num_a ORDER BY ID;

SELECT * FROM num_b ORDER BY ID;

SELECT nvl(a.ID,b.id) ID,nvl(a.NAME,b.name),a.age,nvl(b.nums,0) FROM num_a a FULL JOIN num_b b ON a.id=b.id ORDER BY ID

SELECT COALESCE(a.id,b.id) AS ID,

       COALESCE(a.name, b.name) AS NAME,a.age,b.nums

FROM num_a a

FULL JOIN num_b b

 ON a.id=b.id ORDER BY ID

不等值关联 关联一个区间

 SELECT * FROM salgrade;

 SELECT e.*,s.* FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal

结果集

交集

Intersect查询两个结果集中重复的行

 SELECT * FROM emp WHERE deptno IN (10,20)

 intersect

 SELECT * FROM emp WHERE deptno IN (30,20)

并集

Union查询两个结果集并去掉重复的的行

 SELECT * FROM emp WHERE deptno IN (10,20)

UNION all/union

 SELECT * FROM emp WHERE deptno IN (30,20)

补集

SELECT * FROM emp WHERE deptno IN (30,20)

MINUS

Minus查询返回第一个结果集中存在而第二个结果集中不存在的行

 SELECT empno,deptno FROM emp WHERE deptno IN (10,20)

minus

 SELECT empno,deptno FROM emp WHERE deptno IN (30,20)

同列同数据类型

day6

order_1显示发货日期，订购日期，到货日期的格式为：xxxx年xx月xx日 （此处表内日期为时间戳格式）

SELECT to_char(发货日期,'yyyy"年"mm"月"dd"日 " ') 发货日期,

to_char(订购日期,'yyyy"年"mm"月"dd"日 " ')订购日期,

to_char(到货日期,'yyyy"年"mm"月"dd"日 " ')到货日期 FROM order_1

日期计算转换尽量不要用char类型计算

1. 第三种方法 pivot

语法: SELECT * FROM 表 PIVOT ( max(数据列 ) FOR 要转的列名 IN （新的列1,新的列2，…) ) ;–for是循环 也是遍历

for 可以看成循环，for前是行转列后显示的值(这里必须使用聚合函数)，for后是循环的字段(一般是有限的固定值，如字典等) in (里是对for后的循环字段处理，比如起别名，做运算等)

SELECT * FROM 学生成绩表 PIVOT(MAX(分数) FOR 科目 IN ('语文' 语文 ,'数学' 数学,'英语' 英语 ) );

快速建表

drop table 学生成绩_列

create table 表名 as 查询出来的结果

uppivot 列转行

SELECT * FROM 表名 UNPIVOT （给要转的数据其新的列名 FOR 把列名 转行后的字段名 IN 把那些列转成行）

SELECT * FROM 学生成绩_列 unpivot( 分数 FOR 成绩 IN （'语文','数学','英语'）)

select 姓名,科目,成绩

from 学生成绩_列 unpivot ( 成绩 for 科目 in ( 语文, 数学, 英语 ) );

SELECT 你需要的列名 FROM 表 unpivot( 数据 FOR 新的列名 IN （原来老的列名）)

开窗函数

其实就是在明细后面加一列聚合的结果

比如

我想看一下每个人的详细信息和其对应部门的平均工资

SELECT emp.*,AVG(sal)OVER(PARTITION BY deptno) FROM emp

聚合函数(字段)over(partition by 分组规则)

day7 累加排名同环比

累加效果

sum()OVER(ORDER BY)

AVG()OVER(ORDER BY)

SELECT e.*,AVG(sal)OVER(ORDER BY hiredate),

​          MAX(sal)OVER(ORDER BY hiredate)

​          FROM emp e

​

排名row_number RANK dense_rank

连续排名 跳越排名 连续不跳越排名

90 1 1 1

80 2 2 2

70 3 3 3

70 4 3 3

60 5 5 4

按工资排名

SELECT e.*,

row_number()OVER(ORDER BY sal DESC),

RANK()OVER(ORDER BY sal DESC),

dense_rank()OVER(ORDER BY sal DESC)

 FROM emp e

偏移

向上偏移 lead()

向下偏移 LAG()

​ 给偏移后的空值设置默认值

LEAD(偏移字段，偏移几行，默认值)

按入职的前后顺序，查询每一位员工比他上一位入职的多多少钱

SELECT LAG(sal,1)OVER(ORDER BY hiredate),

​        sal-LAG(sal,1)OVER(ORDER BY hiredate)

 FROM emp

同一个部门按入职时间顺序查询出员工比他下一位入职的多夺少钱？

 SELECT E.*,

​    LEAD(SAL, 1) OVER(PARTITION BY DEPTNO ORDER BY HIREDATE),

​    SAL - LEAD(SAL, 1) OVER(PARTITION BY DEPTNO ORDER BY HIREDATE)

  FROM EMP E;

查询工号相邻的员工，工资相差夺少钱？

 SELECT E.*,

​    SAL - LAG(SAL, 1) OVER(ORDER BY EMPNO) 与上一位相比,

​    SAL - LEAD(SAL, 1) OVER(ORDER BY EMPNO) 与下一位相比

  FROM EMP E

------------------------------------------------------------------------------------

同环比

同比 这一期比去年同一期

环比 这一期比上一期

南宁房价24-3 月房价 同比增长 和23-3月比

​ 环比 和24-2月比

2024-3 10000

2023-3 9000

2024-2 11000

同比增长

SELECT (10000-9000)/9000 FROM dual;

环比增长

SELECT (10000-11000)/11000 FROM dual;

（新-旧）/旧

---------------------------------------------------------

子查询

嵌套在另一个查询中的查询

1.放在select 后面

显示人员工号姓名工资部门编号部门名称

SELECT empno,ename,sal,deptno,(SELECT dname FROM dept d WHERE ) FROM emp

2.from 后面，结果集当成一个表再次查询

同样 WITH AS

铲鲟比部门平均工资高的人员

WITH aa AS

(SELECT e.*,AVG(sal)OVER(PARTITION BY deptno) 部门平均工资 FROM emp e)

SELECT * FROM aa WHERE sal >部门平均工资

3.放在where后面

查询和30号部门有同名的人员

SELECT * FROM emp WHERE ename IN (SELECT ename FROM emp WHERE deptno=30) AND deptno<>30

放在where后有一种特殊情况 单行多列

查询和6011这位员工同一天入职同一岗位的人员

SELECT * FROM emp WHERE (hiredate,job)=

(SELECT hiredate,job FROM emp WHERE empno=6011)

4.放在having 后面

查询比30 号部门人员多的部门

SELECT deptno,Count(1) FROM emp GROUP BY deptno HAVING COUNT(1)>(SELECT COUNT(1) FROM emp WHERE deptno =30)

---------------------------------------------------------------------------------------

多表查询

找到表和表之间的联系

笛卡儿积

a表的每一行数据关联b的每一行数据

球队的编号 让你显示亮亮对战的可能性

CREATE TABLE test_a (ID VARCHAR(2));

SELECT * FROM test_a a,test_a b WHERE a.id<b.id;

SELECT * FROM test_a a CROSS JOIN test_a b WHERE a.id<b.id ;

内连接

两个表能互相关联得上的数据

SELECT e.*,d.* FROM emp e ,dept d

SELECT * FROM emp e INNER JOIN dept d ON d.deptno=e.deptno

------------------------------------------------

查询部门地址是new York 的人员信息

SELECT * FROM emp e INNER JOIN dept d ON d.deptno=e.deptno WHERE loc ='NEW YORK'

查询华南地区客户的所有订单

SELECT o1.* FROM order_1 o1 INNER JOIN USER_1 u1 ON o1.客户id=u1.客户id WHERE 地区='华南';

查询订单编号10407的订单销售额

SELECT sum(单价*数量)销售额 FROM

（SELECT * FROM ORDER_INFO WHERE 订单id=10407）GROUP BY 订单id

SELECT * FROM order_1 o INNER JOIN ORDER_INFO o1 ON o.订单id =o1.订单id WHERE 订单id=10407;

多表join

SELECT * FROM 表1

JOIN 表二 ON 关联字段

JOIN 表三 ON 关联字段

day9增删改

数据库对象：数据库里的对象 表 函数 数据类型 用户 表空间。。。

表

创建表，修改表结构，插入表数据

常用数据类型

number(7,2) 7位数字，2位小数

字符型

把中文看成两个字节

英文1个 两个字节

25个字为1k

GB MB KB BYTE bit

VARCHAR VARCHAR2 可变长型字符型 放多长存多大 节省空间 慢

CHAR 定长型字符型 定多大就多大 占空间 快

DATE 时间 TIMESTAMP 时间戳

大数据型

CLOB 存的也是字符 4G

BLOB 存的是二进制 4G

CREATE TABLE 学生表

(

sno VARCHAR(20),

NAME Varchar(50),

sex Varchar(5),

birthday DATE,

CLASS VARCHAR2(10),

id_card Varchar(18),

tel VARCHAR(11),

photo Blob

);

SELECT * FROM 学生表

增

INSERT INTO 学生表 VALUES ('s001','李世民','男',DATE'2000-10-01','高三一班','450121','1333','0');

INSERT INTO 学生表(sno,NAME,id_card) VALUES ('s002','苏轼','313144'),('s006','苏轼','313144');

INSERT INTO 学生表 VALUES ('s003','王安石','女',DATE'2000-09-01','高三一班','450124','1333','0');

INSERT INTO 学生表 VALUES ('s004','王维','女',DATE'2000-08-01','高三一班','450125','1333','0');

INSERT INTO 学生表 VALUES ('s005','李白','女',DATE'2000-07-01','高三一班','450125','1333','0');

COMMIT;

更

UPDATE 学生表 SET sex='男' WHERE sno='s002';

删

DELETE FROM 学生表 WHERE sno='s005'

–TRUNCATE TABLE 只能删全表 不能回滚

------------------------------------------------------------

对表结构的增删改

SELECT * FROM 学生表;

增

ALTER TABLE 学生表 ADD 照片 BLOB;

删

ALTER TABLE 学生表 DROP COLUMN 照片;

改

修改数据类型和精度

ALTER TABLE 学生表 MODIFY tel VARCHAR2(14)

修改字段名

ALTER TABLE 学生表 RENAME COLUMN tel TO telphone;

增

ALTER TABLE 学生表 ADD 身高 number(5,2);

删

ALTER TABLE 学生表 DROP COLUMN 身高;

改

ALTER TABLE 学生表 MODIFY 身高 number(6,2);

ALTER TABLE 学生表 RENAME COLUMN 身高 TO tall;

--------------------------------------------------------------------

创建表的时候有什么规范？

数据库设计要满足的几个范式通常被称为关系数据库的范式化。主要的范式包括：

第一范式（1NF）：要求表中的每一列都是不可再分的原子值，也就是每个字段都是单一值的，而不能是集合、数组或者其他复杂类型。此外，每个表必须有一个唯一的主键来唯一标识每行数据。

第二范式（2NF）：在满足第一范式的基础上，要求表中的非主键列完全依赖于主键，而不是部分依赖。如果有部分依赖的情况，需要将其分离成独立的表。

第三范式（3NF）：在满足第二范式的基础上，要求表中的每个非主键列之间不存在传递依赖。也就是说，任何非主键列都不应该依赖于其他非主键列。由主键传递信息

-----------------------------------------------------------------------

约束

限制插入的数据符合我们要求的规范

主键约束 PRIMARY KEY 非空且唯一

唯一约束 UNIQUE 唯一约束（可空）

非空约束 NOT NULL 非空

检查约束 CHECK 检查字段信息是否符合定义规范

学生表

NAME 不能为空

sno 主键

id_card 唯一

tel 11位

ALTER TABLE 学生表 MODIFY NAME NOT NULL

ALTER TABLE 学生表 ADD CONSTRAINT pk_学生表 PRIMARY KEY(sno)

ALTER TABLE 学生表 ADD CONSTRAINT uk_学生表 UNIQUE (id_card)

ALTER TABLE 学生表 ADD CONSTRAINT ck_学生表 CHECK(LENGTH(tel)=11)

SELECT * FROM 学生表

UPDATE 学生表 SET id_card = '';

学号 主键

ALTER TABLE 学生表 ADD CONSTRAINT pk_学生表 PRIMARY KEY(sno);

名字不能为空

ALTER TABLE 学生表 MODIFY NAME NOT NULL;

身份证要唯一

ALTER TABLE 学生表 ADD CONSTRAINT uk_学生表 UNIQUE(id_card);

身份证18位

ALTER TABLE 学生表 ADD CONSTRAINT ck_学生表 CHECK(Length(id_card)=18);

身份证非空

ALTER TABLE 学生表 MODIFY id_card NOT NULL

电话非空

ALTER TABLE 学生表 MODIFY tel NOT NULL

电话要11位

ALTER TABLE 学生表 ADD CONSTRAINT ck_学生表_tel CHECK(Length(tel)=11);

性别 只能 男女

ALTER TABLE 学生表 ADD CONSTRAINT ck_学生表_sex check(sex IN ('男','女'))

---------------------------------------------------

删除约束

ALTER TABLE 学生表 DROP CONSTRAINT 约束名

day10外键、数据抽取、索引

外键约束

可以分为三种3种

无级联外键

父表

ALTER TABLE emp ADD CONSTRAINT fk FOREIGN KEY(deptno) REFERENCES dept(deptno);

问题

我要删除dept的50号部门

DELETE FROM dept WHERE deptno=55;

UPDATE emp SET deptno=NULL WHERE deptno =55;

级联删除的外键

ALTER TABLE emp ADD CONSTRAINT fk FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE CASCADE

级联置空

ALTER TABLE emp ADD CONSTRAINT fk FOREIGN KEY(deptno) REFERENCES dept(deptno) ON DELETE SET NULL;

deptno约束为空时，级联置空

无法删除父表列名

快速建表

CREATE TABLE 表明 AS 结果集；

快速插入

把一个结果集的结果 插入到一个表里

SELECT * FROM employee

把这些员工插入到emp去

INSERT INTO emp

SELECT e.雇员id,e.姓氏||e.名字,e.职务 FROM employee e

-----------------------------------------------------

db LINK =databaselink 数据库连接

用来连接另外一台电脑的oracle数据库

我想通过oracle连接服务器的数据库

SELECT * FROM z_memcard@to_nanning_orcl

怎么建立dblink

SELECT * FROM z_memcard@fuwuqi

-----------------------------------

从服务器上把数据抽取到我自己的电脑的数据库上

抽取z_memcard d 的2010年的数据到自己的电脑上

1.快速建表

数据分层

ods贴源层

CREATE TABLE ods_z_memcard as

SELECT * FROM z_memcard@fuwuqi WHERE to_char(createtime,'yyyy')='2010'

快速插入

INSERT INTO ods_z_memcard

SELECT * FROM z_memcard@fuwuqi WHERE to_char(createtime,'yyyy')='2011'

SELECT COUNT(1) FROM ods_z_memcard

---------------------------------------------------------

MERGE INTO

MERGE 是 SQL 中用于将数据插入（INSERT）、更新（UPDATE）或删除（DELETE）到目标表的操作。

它通常用于同时检查源表和目标表，根据某些条件执行不同的操作。

MERGE INTO ods_z_memcard a

USING (SELECT * FROM z_memcard@fuwuqi) b

ON (a.id= b.id)

WHEN MATCHED THEN UPDATE

  SET a.cardlevel=b.cardlevel

WHEN NOT MATCHED THEN INSERT

 VALUES (b.id,

​    b.memcardno,

​    b.busno,

​    b.cardtype,

​    b.cardlevel,

​    b.cardpass,

​    b.cardstatus,

​    b.saleamount,

​    b.realamount,

​    b.puramount,

​    b.integral,

​    b.integrala,

​    b.integralflag,

​    b.cardholder,

​    b.cardaddress,

​    b.sex,

​    b.tel,

​    b.handset,

​    b.fax,

​    b.createuser,

​    b.createtime,

​    b.tstatus,

​    b.notes,

​    b.stamp,

​    b.idcard,

​    b.birthday,

​    b.allowintegral,

​    b.apptype,

​    b.applytime,

​    b.invalidate,

​    b.lastdate,

​    b.bak1)

SELECT * FROM ods_z_memcard

SELECT b.cardlevel,COUNT(1) FROM ods_z_memcard b GROUP BY b.cardlevel

建立主键的时候建立了索引

索引是什么

索引就是能加快我们查询数据的一种数据结构

就像一本书的目录

什么时候建立索引

1建立主键和唯一约束的时候，表会自己创建索引

2什么时候需要手动建立索引

经常用某个字段进行查询的时候，就在这个字段上手动建立索引

手动建立索引

1.普通索引

b+树索引

用在去重之后，仍有很多值

SELECT * FROM ods_z_memcard WHERE cardholder='张坤' ;

CREATE INDEX ind_z_memcard_name ON ods_z_memcard(cardholder);

位图索引

用在去重，有较少的值，比如 性别，婚姻

SELECT * FROM ods_z_memcard WHERE cardlevel='5'; *--0.12*

CREATE bitmap INDEX ods_memcard_bitmap ON ods_z_memcard(cardlevel);

基于函数的索引

经常用某种函数去查询某个字段的时候

查看身份证是否够18

SELECT * FROM ods_z_memcard WHERE LENGTH(idcard)=18;

*--CREATE INDEX ods_memcard_lengthid ON ods_z_memcard(length(idcard));*

索引

失效的情况

1.不写where 查全表

2.select * FROM ods_z_memcard o WHERE cardholder IS NULLIF

3.where +函数

SELECT * FROM ods_z_memcard o WHERE LENGTH(o.cardholder)

4.like进行模糊查询

SELECT * FROM ods_z_memcard o WHERE o.cardholder LIKE ‘%红’ —系统觉得全表查询块

5.where 子句种使用不等于操作

SELECT * FROM ods_z_memcard o WHERE o.cardlevel <>4;

6.等于和范围索引的时候

7.比较不匹配的数据类型

SELECT * FROM ods_z_memcard o WHERE o.cardlevel =‘4’;

表空间

就是数据库区分不同的存储空间的逻辑结构

.ctl控制文件

.log日志文件

.bdf表空间文件

表空间存储数据库各种对象的

自己创建表空间

CREATE TABLESPACE lu_240408 DATAFILE=‘D:\app\luzhaohuan\oradata\orcl\lu_240408.dbf’

SIZE 5m

AUTOEXTEND ON NEXT 5m

MAXSIZE UNLIMITED;

永久表空间

--------------------------------------

临时表空间

用来做缓存，比如计算order BY GROUP BY

CREATE TEMPORARY TABLESPACE lu_240408_temp

TEMPFILE DATAFILE=‘D:\app\luzhaohuan\oradata\orcl\lu_240408_temp.dbf’

SIZE 5m

AUTOEXTEND ON NEXT 1m

MAXSIZE 10m;

CREATE USER 用户名 IDENTIFIED BY 123456 DEFAULT TABLESPACE 用户名 TEMPORARY TABLESPACE 用户名_temp;

角色权限;

CONNECT 仅具有会话权限的角色

RESOURCE 开发者角色 创建表，索引等对象，不能创建数据结构（表空间）

DBA 管理员

GRANT CONNECT,RESOURCE TO 用户名

GRANT SELECT,UPDATE scott.emp

–public 公开的

day11视图、数据字典、导出导入

视图

视图是数据库中的一种对象

是查询结果的映射

查询每个客户每个月的运货费，用来给银行做对账

SELECT o.订单id,to_char(o.订购日期,‘yyyy-mm’),SUM(o.运货费) FROM Order_1 o GROUP BY o.订单id,to_char(o.订购日期,‘yyyy-mm’)

将结果集存入数据库，结果集会根据数据的增多或减少而自动变化

把结果集做成一个视图

和表是一样的

区别是视图的数据来源各个表

CREATE OR REPLACE VIEW view_user_month_money AS

创建一个视图

每年每月每个客户的销售额

CREATE OR REPLACE VIEW view_user_month_sales AS

SELECT o.客户id, to_char(o.订购日期,‘yyyy-mm’) 年月,sum(oi.单价oi.数量(1-oi.折扣)) 销售额 FROM Order_1 o

JOIN ORDER_INFO oi ON o.订单id=oi.订单id GROUP BY to_char(o.订购日期,‘yyyy-mm’),o.客户id

WITH READ ONLY

SELECT * FROM view_user_month_sales

可以使用GRANT语句来授予其他用户对视图的SELECT权限。以下是一个示例：

假设您有一个名为my_view的视图，并且您想授权另一个用户（例如other_user）查看该视图：

GRANT SELECT ON my_view TO other_user;

序列

相当于等差数列 一系列数字 有相同的差

1 3 5 7 9 公差2

CREATE SEQUENCE swq_test

START WITH 1

INCREMENT BY 2

MINVALUE 2 –nomaxvalue

MAXVALUE 20

CACHE 3 –缓存

CYCLE –循环 到最大值后从最小值开始循环

SELECT seq_test.nextval FROM dual;

SELECT seq_test.currval FROM dual

-------------------------------------------------

根据时间+自增

20240409||自增列

----------------------------------------------------

------------------------------------------------------

数据字典

数据库对象的字段

查询一下用户下有哪些表

SELECT * FROM User_Tables WHERE table_name=‘EMP’

查询数据库中都有什么表

SELECT * FROM dba_tables

SELECT * FROM Dba_Views –序列dba_sequence all_sequence

查用户

SELECT * FROM user_views

SELECT * FROM user_col_comments

SELECT * FROM user_indexes

SELECT * FROM User_Ind_Columns WHERE

----------------------------------------------------------------------

事务就是一件事

一件事包含很多步骤

但是这些步骤要么都成功

要么都失败

不允许一半成功一半失败

完成就commit；

不完成就rollback；

-----------------------------------------------------------------------

导入

imp 用户名/密码@实例 file=‘路径’

导出用户下的所有对象

EXP 用户名/密码@实例 TABLES=‘emp’ FILE=‘路径文件名.dmp’

导出数据库中所有对象

EXP 用户名/密码@实例 FILE=‘路径文件名.dmp’ full=y

day12qlsql for、while

PLsql

过程化语言

数据库变成 学习编程逻辑和思想

程序=‘过程和顺序’

plsql的基本组成=程序块

有三部分组成

声明部分

逻辑部分

异常部分

DECLARE

​ --声明定义变量或常量

BEGIN

​ --逻辑部分

​ --异常部分

END ;

-----------------------------------

变量 = 可以改变它的值的一个盒子

常量 = 不能改变它的值的盒子

-------------------------------------

DECLARE

v_name VARCHAR2(30);

v_ji CONSTANT VARCHAR2(30) :=‘篮球’; --不可变的变量

BEGIN

v_name:=‘鸡哥’；

dbms_output.put_line(v_name); --打印换行

END;

DECLARE

v_name VARCHAR2(30) :=‘&姓名’;

BEGIN

​ dbms_outln(v_name);

END;

---------------------------------------------

手动输入你们的名字和技能

打印出18随的时候会干什么

打印出20随的时候会干什么

DECLARE

​ v_name CONSTANT VARCHAR2(30) := ‘&姓名’;

​ v_skill VARCHAR2(40):=‘&18岁技能’;

​ v_skill VARCHAR2(40):=‘&20岁技能’;

BEGIN

​ dbms_output.put_line(v_name||‘18岁的时候’||v_skill);

​ dbms_output.put_line(v_name||‘20岁的时候’||v_skill);

END;

DECLARE

​ v_name emp.ename%TYPE; --使用原来的数据类型

BEGIN

​ SELECT ename INTO v_name FROM emp WHERE empno=7935 ;

​ dbms_output.put_line(v_name);

END;

-------------------------------------------------------

SELECT *FROM dept

输入一个部门编号，打印出这个部门的部门名称和地址

DECLARE

​ v_deptno NUMBER(4) :=&部门编号;

​ v_add VARCHAR2(30);

​ v_dname VARCHAR2(30);

BEGIN

​ SELECT dname,loc INTO v_dname,v_add FROM dept WHERE deptno=v_deptno;

​ dbms_output.put_line(v_deptno||‘的部门名称是’||v_dname||‘部门地址是’||v_add);

END;

SELECT * FROM order_1;

SELECT * FROM order_info;

SELECT * FROM User_1;

输入一个年份和客户id ，打印出客户id在这个年份的销售额

DECLARE

​ v_userid VARCHAR(20) := ‘&客户id’;

​ v_year VARCHAR(20) := ‘&年份’;

​ v_yearSales NUMBER(8,2);

BEGIN

​ SELECT 总销售额 INTO v_yearSales FROM

​ (SELECT u1.客户id ,to_char(o1.订购日期,‘yyyy’) 年份,SUM(oi.单价oi.数量(1-oi.折扣)) 总销售额 FROM order_1 o1

​ JOIN Order_Info oi ON o1.订单id=oi.订单id

​ JOIN USER_1 u1 ON o1.客户id=u1.客户id GROUP BY u1.客户id,to_char(o1.订购日期,‘yyyy’) )a

​ WHERE a.客户id=v_userid AND a.年份=v_year ;

​ dbms_output.put_line(v_userid||‘在’||v_year||‘年’||‘的销售额是’||v_yearSales);

END;

-------------------------------------------------------------------------

输入一个部门编号，打印出这个部门的部门名称和地址

找不到这部门

打印木有这个部门

用到了判断

if分支判断

IF 分支判断 THEN ganshenm

ELSIF 另一种条件成立 THEN 干什么

ELSE 除了上面的情况 干什么

END IF;

DECLARE

​ v_deptno NUMBER(4) :=&部门编号;

​ v_add VARCHAR2(30);

​ v_dname VARCHAR2(30);

BEGIN

​

​ SELECT dname,loc INTO v_dname,v_add FROM dept WHERE deptno=v_deptno;

​ dbms_output.put_line(v_deptno||‘的部门名称是’||v_dname||‘部门地址是’||v_add);

END;

day13游标

FOR 循环 WHILE循环 游标

FOR 循环 循环做一件事

打印100个名字

DECLARE

BEGIN

FOR i IN 1…100 LOOP

dbms_output.put_line(‘坤哥’) ;

END LOOP;

END;

---------------------------------------

打印一百个自己的名字，并写上第几个

DECLARE

BEGIN

FOR j IN 1…100 LOOP

dbms_output.put_line(‘第’||j||‘个’||‘自己的名字’) ;

END LOOP;

END;

计算1+2+3…+100

DECLARE

s NUMBER:=0;

BEGIN

FOR i IN 1…100 LOOP

s:=s+i;

IF i=100 THEN

​ dbms_output.put_line(‘1加到100的结果是’||s);

END IF;

END LOOP;

END;

DECLARE

s NUMBER(8):=0;

BEGIN

FOR i IN 101…1049 LOOP

IF MOD(i,2)=1 THEN

s:=s+i;

END IF;

END LOOP;

dbms_output.put_line(s);

END;

计算1+3+5+…+1049

DECLARE

i NUMBER(8):=101;

s NUMBER(8):=0;

BEGIN

WHILE i <= 1049

LOOP

s:=s+i;

i:=i+2;

dbms_output.put_line(s||‘,’||i);

END LOOP;

END;

DECLARE

s NUMBER(8):=0;

BEGIN

FOR i IN 1…100 LOOP

IF MOD(i,2)=1 THEN s:=s+i;

ELSE s:=s-i;

END IF;

dbms_output.put_line(s||‘,’||i);

END LOOP;

END;

-------------------------------------------

1 3 5

2 4 6

3 6 9

DECLARE

BEGIN

FOR i IN 1…5 LOOP

IF MOD(i,2)=1 THEN

FOR j IN 2…6 LOOP

​ IF MOD(j,2)=0 THEN

​ FOR k IN 3…9 LOOP

​ IF MOD(k,3)=0 THEN

​ dbms_output.put_line(i||j||k);

​ END IF;

​ END LOOP;

​ END IF;

​ END LOOP;

END IF;

END LOOP;

END;

DECLARE

v_empno NUMBER := 0;

BEGIN

FOR i IN (SELECT e.empno FROM emp e) LOOP

v_empno := i.empno;

dbms_output.put_line(v_empno);

END LOOP;

END;

计算emp表所有员工的工资总和

DECLARE

sum_sal NUMBER:=0;

v_sal NUMBER;

BEGIN

FOR i IN (SELECT e.empno FROM emp e) LOOP

SELECT sal INTO v_sal FROM emp e WHERE empno=i.empno;

sum_sal:=sum_sal+v_sal;

dbms_output.put_line(sum_sal);

END LOOP;

END a_block;

SELECT* FROM emp

DECLARE

v_money NUMBER:=10000;

v_year NUMBER:=0;

BEGIN

WHILE v_money<20000 LOOP

​ v_money:=v_money*0.025+v_money;

​ v_year :=v_year+1;

END LOOP;

dbms_output.put_line(v_year);

END;

---------------------------------------------

有一个水池需要5000方水

一个水管一个小时进2.5方

一个水管出1.3方

每过10小时停放一个小时

问多久能进够5000方水

DECLARE

v_cube NUMBER:=0;

v_hour NUMBER:=0;

v_in NUMBER;

v_out NUMBER;

BEGIN

WHILE v_cube<5000 LOOP

v_hour:=v_hour+1;

v_in:=2.5;

v_out:=1.3;

IF MOD(v_hour,10)=0 AND v_hour>0 THEN

v_in:=0;

v_out:=0;

END IF;

v_cube:=v_cube+v_in-v_out;

END LOOP;

dbms_output.put_line(v_hour);

END;

DECLARE

v_cube NUMBER:=0;

v_hour NUMBER:=0;

v_in NUMBER;

v_out NUMBER;

BEGIN

WHILE v_cube<5000 LOOP

v_hour:=v_hour+1;

v_in:=2.5;

v_out:=1.3;

v_cube:=v_cube+v_in-v_out;

END LOOP;

dbms_output.put_line(v_hour);

END;

SELECT 4167/10 FROM dual

DECLARE

v_cube NUMBER:=0;

v_hour NUMBER:=0;

v_in NUMBER;

v_out NUMBER;

BEGIN

WHILE v_cube<24 LOOP

v_in:=2.5;

v_out:=1.3;

IF MOD(v_hour,10)=0 AND v_hour>0 THEN

v_hour:=v_hour+1;

END IF;

v_cube:=v_cube+v_in-v_out;

v_hour:=v_hour+1;

END LOOP;

dbms_output.put_line(v_hour);

END;

123456789 10 11

23456789 20 21

23456789 30 31

-----------------------------------------------------------------------

游标

能存储一个结果集

CURSOR

打印10号部门人员的工号、名字和工资

DECLARE

CURSOR c_cur IS SELECT * FROM emp WHERE deptno=10;

BEGIN

FOR i IN c_cur LOOP

dbms_output.put_line(i.empno);

END LOOP;

END;

----------------------------------------------------------

打印出工作是manager的工号，名字，工资

DECLARE

CURSOR c_cur IS SELECT * FROM emp WHERE job=‘MANAGER’;

BEGIN

FOR i IN c_cur LOOP

dbms_output.put_line(i.empno||i.ename||‘的工资是’||i.sal);

END LOOP;

END;

用游标查询所有经理的工资，当经理的工资大于5000时就-200工资 当小于4000时就+400工资

打印出每个经理的工号和加薪前后的工资

DECLARE

v_sal emp.sal%TYPE;

CURSOR c_cur IS SELECT * FROM emp WHERE job=‘MANAGER’;

BEGIN

FOR i IN c_cur LOOP

SELECT sal INTO v_sal FROM emp WHERE empno=i.empno;

dbms_output.put_line(i.empno||‘经理薪资更新前的工资是’||v_sal);

IF i.sal>5000 THEN

UPDATE emp SET sal=sal-200 WHERE empno=i.empno;

–COMMIT;

ELSIF i.sal<4000 THEN

UPDATE emp SET sal=sal+400 WHERE empno=i.empno;

–COMMIT;

END IF;

SELECT sal INTO v_sal FROM emp WHERE empno=i.empno;

dbms_output.put_line(i.empno||‘经理薪资更新后的工资是’||v_sal);

END LOOP;

END;

1 8000 aCUU 威的d MANAGER 2024/2/1 21313.00 40

2 7566 JONES MANAGER 7839 1981/4/2 6575.00 20

3 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

4 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

SELECT * FROM emp WHERE job=‘MANAGER’;

1写一个程序块，把指定的某个字母开头的emp表的员工工资显示出来。

DECLARE

v_a VARCHAR(6):=‘&首字母’;

CURSOR c_cur IS SELECT * FROM emp WHERE substr(ename,1,1)=v_a;

BEGIN

FOR i IN c_cur LOOP

dbms_output.put_line(i.empno||‘员工的工资是’||i.sal);

END LOOP;

END;

SELECT * FROM emp WHERE substr(ename,1,1)=‘a’

1. 课后作业：

程序块完成，打印输出100至200之间的全部素数，输出素数并统计共有多少个素数。从

注：质数(prime number)又称素数，有无限个。一个大于1的自然数，除了1和它本身外，不能被其他自然数整除，

换句话说就是该数除了1和它本身以外不再有其他的因数; 否则称为合数。

DECLARE

BEGIN

FOR i IN 100…200 LOOP

FOR j IN 2…4 LOOP

IF MOD(i,j)<>0 THEN

​ dbms_output.put_line(i);

END IF;

END LOOP;

END LOOP;

END;

SELECT MOD(100,2) FROM dual

2. 有1 2 3 4 四个数字，可以组成多少个不重复的数字，显示出来，比如，不能有112，223，一共有多少个？
3. 假如你要准备结婚的彩礼是8万，然后你每个月能存2000块，然后女方家说，从现在开始算起，每年涨1万块。假如你的另一半还要买个房子，按照南宁的房价1万（房价不变），买个100平，首付20%，再算一下你多久能娶到你老婆。也就是说准备的钱是 彩礼+首付

–游标实现

1.按照salgrade等级表，按照每个员工的工资等级，分别进行加薪，5级%1,4级2%，3级%3,2级4% ，1级5%；输出加薪前后的员工姓名及工资信息；

2.给员工加薪，如果这个员工是在DALLAS工作，而且是MANAGER就加15%工资，如果是在NEW YORK并且是做CLERK就降薪10%；其他员工不变；

3.加薪，如果员工的老大是BLAKE这个家伙，而且员工是81年上半年入职 就加20%，如果是下半年入职的就加10%；

SELECT * FROM salgrade

day14 显式游标

从1到它本身 只有两个因数

DECLARE

v_su NUMBER:=0;

v_count NUMBER:=0;

BEGIN

FOR i IN 100…200 LOOP

​ v_su:=0;

​ FOR j IN 1…i LOOP

​ IF MOD(i,j)=0 THEN

​ v_su:=v_su+1; —计算能整除的因数的个数

​ END IF;

​ END LOOP;

​ IF v_su =2 THEN --因数个数为二的是质数，即为1和他本身

​ dbms_output.put_line(i);

​ v_count:=v_count+1;

​ END IF;

END LOOP;

dbms_output.put_line(v_count);

END;

Declare

Cursor c_ee is select * from dept;

V_dept dept%rowtype; --复用表中一行的参数类型

Begin

Open c_ee; --打开游标

Loop

Fetch c_ee into v_dept; --提取一行到变量

Dbms_output.put_line(v_dept.loc);

Exit when c_ee%notfound;–提取一行少一行，当没有时退出

End loop;

Close c_ee;–关闭游标

End ;

1 10 ACCOUNTING NEW YORK

2 20 RESEARCH DALLAS

3 30 SALES CHICAGO

4 40 OPERATIONS BOSTON

Declare

Cursor c_ee(v_dept number )is select * from dept;

Begin

For i in c_ee（10） loop—实参

Dbms_output.put_line(c_ee.ename);

End loop;

End ;

隐式图标（隐式的调用，不用手动打开，关闭，提取）

显示游标（要手动打开关闭调用）

打印10号部门的人员名字和工资、

DECLARE

CURSOR c1 IS SELECT * FROM emp WHERE deptno=10;

v_str emp%ROWTYPE;

BEGIN

OPEN c1;

​ LOOP

​ FETCH c1 INTO v_str;

​ EXIT WHEN c1%NOTFOUND;

​ dbms_output.put_line(v_str.empno||‘–’||v_str.ename);

​ END LOOP;

CLOSE c1;

END;

2.给员工加薪，如果这个员工是在DALLAS工作，而且是MANAGER就加15%工资，如果是在NEW YORK并且是做CLERK就降薪10%；其他员工不变；

DECLARE

CURSOR c1 IS SELECT e.empno,e.ename,e.job,e.sal,d.loc FROM emp e JOIN dept d ON e.deptno=d.deptno;

v_str c1%ROWTYPE;

v_sal emp.sal%TYPE;

BEGIN

OPEN c1;

LOOP

​ FETCH c1 INTO v_str;

​ EXIT WHEN c1%NOTFOUND;

​ IF v_str.job=‘MANAGER’ AND v_str.loc=‘DALLAS’ THEN

​ UPDATE emp SET sal = sal + sal*0.15 WHERE empno = v_str.empno;

​ ELSIF v_str.job=‘CLERK’ AND v_str.loc=‘NEW YORK’ THEN

​ UPDATE emp SET sal = sal - sal*0.1 WHERE empno = v_str.empno;

​ END IF;

​ SELECT sal INTO v_sal FROM emp WHERE empno=v_str.empno;

​ dbms_output.put_line(v_str.ename||‘的职位是’||v_str.job||‘工作地是’||v_str.loc||‘原薪资’||v_str.sal||‘更新后薪资是’||v_sal);

​ dbms_output.put_line(’ ');

END LOOP;

CLOSE c1;

END;

DECLARE

CURSOR c1 IS SELECT e.empno,e.ename,e.job,e.sal,d.loc FROM emp e JOIN dept d ON e.deptno=d.deptno;

v_str c1%ROWTYPE;

v_sal1 emp.sal%TYPE;

v_sal emp.sal%TYPE;

v_empno emp.empno%TYPE;

v_ename emp.ename%TYPE;

v_job emp.job%TYPE;

v_loc dept.loc%TYPE;

BEGIN

OPEN c1;

LOOP

​ FETCH c1 INTO v_empno,v_ename,v_job,v_sal,v_loc;

​ v_sal1:=v_sal;

​ EXIT WHEN c1%NOTFOUND;

​ IF v_job=‘MANAGER’ AND v_loc=‘DALLAS’ THEN

​ UPDATE emp SET sal = sal + sal*0.15 WHERE empno = v_empno;

​ ELSIF v_job=‘CLERK’ AND v_loc=‘NEW YORK’ THEN

​ UPDATE emp SET sal = sal - sal*0.1 WHERE empno = v_empno;

​ END IF;

​ SELECT sal INTO v_sal FROM emp WHERE empno=v_empno;

​ dbms_output.put_line(v_ename||‘的职位是’||v_job||‘工作地是’||v_loc||‘原薪资’||v_sal1||‘更新后薪资是’||v_sal);

​ dbms_output.put_line(’ ');

END LOOP;

CLOSE c1;

END;

------------------------------------------------------------------------------

%FOUND：变量最后从游标中获取记录的时候，在结果集中找到了记录。

%NOTFOUND：变量最后从游标中获取记录的时候，在结果集中没有找到记录。

%ROWCOUNT：当前时刻已经从游标中获取的记录数量。

%ISOPEN：是否打开。

----------------------------------------------------------------------------------

上面写的游标都是再声明定义的时候，赋予了结果集——静态游标

还有一种游标，是根据不同情况赋予不同的结果集——动态游标

声明的时候没有赋予结果集

写一个程序块，再输入D的时候打印部门信息

​ E的时候打印人员信息

DECLARE

c1 Sys_Refcursor;

v_str VARCHAR2(2):=‘&你想看D还是E’;

BEGIN

IF UPPER(v_str)=‘D’ THEN

​ c1 IS SELECT * FROM dept;

END IF;

OPEN c1 FOR v_sql;

​ LOOP

​ FETCH c1 INTO v_dept_row;

​ EXIT WHEN c1%NOTFOUND;

​ dbms_output.put_line();

​ END LOOP;

CLOSE OPEN;

END;

写一个程序块，在输入d的时候，打印 部门信息

​ 输入E的时候 打印 人员信息

​

DECLARE

c1 SYS_REFCURSOR;

v_in VARCHAR2(4):=‘&请输入想查看的信息（D部门信息，E人员信息）’;

v_sql Varchar2(300);

v1 Varchar2(100);

v2 Varchar2(100);

v3 Varchar2(100);

v4 Varchar2(100);

BEGIN

IF UPPER(v_in) = ‘D’ THEN

​ v_sql:=‘select deptno,dname,loc from dept’;

ELSIF UPPER(v_in)=‘E’ THEN

​ v_sql:=‘select empno,ename,job from emp’;

END IF;

dbms_output.put_line(v_sql);

IF length(v_sql)!=0 THEN

​ OPEN c1 FOR v_sql;

​ LOOP

​ FETCH c1 INTO v1,v2,v3 ;

​ EXIT WHEN c1%NOTFOUND;

​ dbms_output.put_line(v1||‘‘||v2||’’||v3||‘====’||v4);

​ END LOOP;

​ CLOSE c1;

ELSE dbms_output.put_line(‘请输入正确内容’);

END IF;

END;

创建函数

计算年岁

CREATE OR REPLACE FUNCTION f_age( p DATE) RETURN NUMBER IS

v_age NUMBER(4);

BEGIN

v_age:=TRUNC(months_between(Sysdate,p)/12);

RETURN v_age;

END;

–

自定义一个函数

计算两数的和*乘两数差 +两数的积

CREATE OR REPLACE FUNCTION f_jisuan(p1 NUMBER,p2 NUMBER ) RETURN NUMBER IS

v_tol NUMBER(7,2);

BEGIN

v_tol:=(p1+p2)(p1-p2)+p1p2;

RETURN v_tol;

END;

SELECT f_jisuan(2,5) FROM dual

DECLARE

n1 NUMBER:=&第一个数;

n2 NUMBER:=&第二个数;

v_1 NUMBER(7,2);

BEGIN

v_1 :=f_jisuan(n1,n2);

dbms_output.put_line(v_1);

END;

DROP FUNCTION f_jisuan

day15 存储过程

存储过程：做一系列复杂步骤的事情

传入一个工号和家的哦工资，给该员工加薪

写一个存储过程

CREATE OR REPLACE PROCEDURE sp_add_sal(p_empno IN NUMBER,p_add_sal IN NUMBER) IS

v_count NUMBER(2);

BEGIN

SELECT COUNT(1) INTO v_count FROM emp WHERE empno=p_empno;

IF v_count = 1 THEN

​ UPDATE emp SET sal =sal+p_add_sal WHERE empno=p_empno;

​ COMMIT;

ELSE

​ dbms_output.put_line(‘no this gay’);

END IF;

END;

通过程序块调用

DECLARE

BEGIN

sp_add_sal(7935,100);

END;

----------------------------------------------------------------------------------

create or replace procedure sp_get_data(P_date in varchar2 ,p_data_count out number,p_clean_count out number)

is

v_count number(1) ;

begin

—1 判断本地是否有 ods_z_memcard 的表，有的话清空表数据，没有的话创建–

select count(1) into v_count from user_tables where table_name =upper(‘ods_z_memcard’);

if v_count = 1 then

execute immediate ‘truncate table ods_z_memcard’;

else

execute immediate ‘create table ods_z_memcard as select * from z_memcard@to_fuwuqi where 1=2’;

end if;

—2 按月抽取数据，xx年xx月–

insert into ods_z_memcard

select * from z_memcard@to_fuwuqi where to_char(createtime,‘yyyy-mm’)=P_date;

p_data_count :=sql%rowcount;—获得上一条dml 影响的行数 =3 计算 这个月共有 多少条数据

commit;

—4 清洗数据，生日对不上身份证号，把身份证号的生日更新到生日字段上–

update ods_z_memcard set birthday = to_date(substr(idcard,7,8),‘yyyy-mm-dd’)

​ where birthday <> to_date(substr(idcard,7,8),‘yyyy-mm-dd’);

p_clean_count := sql%rowcount;

commit;

end ;

-------------------------

declare

v_date varchar2(20):=‘&抽数的年月’;

v_get_count number;

v_clean_count number;

begin

sp_get_data(v_date,v_get_count,v_clean_count);

dbms_output.put_line(‘共抽取’||v_get_count||‘条数据’);

dbms_output.put_line(‘共清洗’||v_clean_count||‘条数据’);

end ;

day16 异常控制

异常控制

把错误做好相应的措施，在程序中体现出来，而不至于遇到错误，让程序中止而不知道错误情况

DECLARE

v_name VARCHAR(20):=‘&姓名’;

v_empno NUMBER;

v_job NUMBER;

BEGIN

SELECT empno,job INTO v_empno,v_job FROM emp WHERE ename=v_name;

dbms_output.put_line(v_name||‘工号是’||v_empno||‘工作是’||v_job);

EXCEPTION

​ WHEN

​ dbms_output.put_line(‘程序错误’);

END;

异常

预定义异常：系统定义好的

非预定义异常：其他的标准的oracle错误，手动对它进行定义，再由oracle引发

用户自定义错误：用户主观认为的错误

​ 比如：员工工资大于8000，就是错的

----------------------------

给员工加薪

不是manager，工资不能大于8000

DECLARE

v_empno NUMBER(4):=&工号;

v_add_sal NUMBER(4):=&加钱;

v_ job VARCHAR(20);

v_sal NUMBER(4,2);

sal_hight EXCEPTION;

BEGIN

SELECT job,sal INTO v_job,v_sal FROM emp WHERE empno =v_empno;

IF v_job!=‘MANAGER’ AND v_sal>=8000 THEN

​ RAISE sal_hight;

ELSE

​ UPDATE emp SET sal=sal+v_add_sal WHERE empno=v_empno;

​ dbms_output.put_line(‘good’);

END IF;

EXCEPTION

​ WHEN no_date_found THEN

​ dbms_output.put_line(‘没这个人’);

​ WHEN to_many_rows THEN

​ dbms_output.put_line(‘该工号存在多条数据’);

​ WHEN sal_hight THEN

​ dbms_output.put_line(‘不是经理，不能超过8000’);

​ WHEN OTHERS THEN

​ dbms_output.put_line(‘其他错误’);

END;

-----------------------------------------------------------

创建一个过程

传入一个工号，当这个工号是10号部门的manager，给其加薪10%

20号部门的manager加百分之20

其他的人员加薪，百分之五

限制：员工的工资都不能超过8000

CREATE OR REPLACE PROCEDURE sp_add_sal(p_empno IN NUMBER) IS

v_job emp.job%TYPE;

v_sal emp.sal%TYPE;

v_deptno emp.deptno%TYPE;

v_add_sal_10manager NUMBER:=1.1;

v_add_sal_20manager NUMBER:=1.2;

v_add_sal_others NUMBER:=1.05;

sal_hight EXCEPTION;

BEGIN

SELECT sal,job,deptno INTO v_sal,v_job,v_deptno FROM emp WHERE empno=p_empno;

​ IF v_job=‘MANAGER’ AND v_sal*v_add_sal_10manager<=8000 AND v_deptno=10 THEN

​ v_sal:=v_sal*v_add_sal_10manager;

​ ELSIF v_job=‘MANAGER’ AND v_sal*v_add_sal_20manager<=8000 AND v_deptno=20 THEN

​ v_sal:=v_sal*v_add_sal_20manager;

​ ELSIF v_sal*v_add_sal_others<=8000 THEN

​ v_sal:=v_sal*v_add_sal_others;

​ ELSE

​ RAISE sal_hight ;

​ END IF;

​ UPDATE emp SET sal=v_sal WHERE empno=p_empno;

​ dbms_output.put_line(p_empno||‘员工已加薪’);

​ --COMMIT;

​

​ EXCEPTION

​ WHEN no_data_found THEN

​ dbms_output.put_line(‘没这个人’);

​ WHEN too_many_rows THEN

​ dbms_output.put_line(‘该工号存在多条数据’);

​ WHEN sal_hight THEN

​ dbms_output.put_line(‘薪资不能超过8000’);

​ WHEN OTHERS THEN

​ dbms_output.put_line(‘其他错误’);

END;

DECLARE

v_empno emp.empno%TYPE:=&工号;

BEGIN

sp_add_sal(v_empno);

END;

SELECT * FROM emp

13 7782 CLARK MANAGER 7839 1981/6/9 2799.96 10

10 7566 JONES MANAGER 7839 1981/4/2 7790.36 20

15 7839 KING PRESIDENT 1981/11/17 5151.51 10

---------------------------------------------------------------

包

对过程和函数的归类，打包放在一起

比如

对emp有以下操作

查询一个工号的姓名，工作、工资、部门

查询一个部门的人员工号姓名工作工资

给员工加薪

修改员工的岗位

修改员工的部门

增加一位员工

删除一位员工

把这些操作的过程分类写在一个包里。（面向对象）

包头 包体

---------------包头

CREATE OR REPLACE PACKAGE pack_emp

IS

END pack_emp;

—包体

CREATE OR REPLACE PACKAGE BODY pack_emp

IS

​ --增加一位员工

​ PROCEDURE sp_add_emp(p_name IN VARCHAR2,p_job IN VARCHAR2,sal IN NUMBER,p_dept IN NUMBER)IS

​

​ BEGIN

​ IF p_job<>‘MANAGER’ AND p_sal>8000 THEN

​ dbms_output.put_line(‘工资不能超过8000’);

​ ELSE

​ INSERT INTO emp VALUES(swq_test)

​ END IF ;

​ END sp_add_emp;

END pack_emp;

创建一个包：

实现以下需求

查询一个工号的 姓名，工作，工资，部门

查询一个部门的人员的 工号、姓名，工作，工资

给员工加薪

修改员工的岗位

修改员工的部门

增加一位员工

删除一位员工

限制：不是manager ,工资不能大于8000

完成对 ods_z_memcard 药店会员卡信息表的一个包编写

包含三个功能

1.根据createtime，指定时间节点范围，抽取数据

2.所有的数据，根据身份证把对应的 birthday的时间更正

3.输入会员卡号，显示会员的名字和 年龄（计算月份） month_between()

SYS_CONTEXT() 是一个 Oracle 数据库函数，用于检索当前会话的上下文信息。它返回与指定名称关联的上下文值。这个函数通常用于访问会话级别的信息，比如用户的登录信息、应用程序名称、数据库会话 ID 等。

SYS_CONTEXT() 函数的一般语法如下：

```sql

SYS_CONTEXT(namespace, parameter)

```

其中，namespace 是上下文的命名空间，parameter 是要检索的参数名。命名空间定义了参数的范围，可以是 USERENV、SESSION、GLOBAL 等。

例如，要检索当前会话的用户名，可以使用以下查询：

```sql

SELECT SYS_CONTEXT(‘USERENV’, ‘SESSION_USER’) FROM DUAL;

```

这将返回当前会话的用户名。

在实际应用中，SYS_CONTEXT() 函数可以用于执行基于会话信息的条件查询、动态筛选或跟踪用户活动等。

day17 触发器

emp 周五不能删除数据

CREATE OR REPLACE TRIGGER tri_fri_nodelete
BEFORE
DELETE OR UPDATE
ON emp

----插入的时间不能大于当前时间
BEGIN
IF deleting THEN
IF to_char(SYSDATE,‘day’)=‘星期五’ THEN
raise_application_error(-20001,‘周五不能删除数据’);
END IF;
ELSIF updating THEN
IF to_char(SYSDATE,‘day’)=‘星期六’ THEN
raise_application_error(-20001,‘周六不能删除数据’);
END IF;
END IF;
END tri_fri_nodelete;

表级触发器？ 行级触发器
:NEW - :OLD

CREATE op REPLACE TRIGGER trigger_test
BEFORE
DELETE
ON emp
FOR EACH ROW

BEGIN
dbms_output.put_line(‘删了一行数据’);
END;

表级
删除了一行
对表进行了一次操作

行级
对行进行了十四次操作

:NEW :OLD
UPDATE 新旧
INSERT 新
DELETE 旧

---

1.emp创建，当插入的人员的部门在部门表找不出时提示出来
限制：当工作不是manager时，工资不能大于8000，并提示出来
不能删除数据
CREATE OR REPLACE TRIGGER tri_emp_insert
BEFORE
INSERT OR DELETE
ON emp
FOR EACH ROW

DECLARE
v_count NUMBER(2);
BEGIN
IF inserting THEN
SELECT COUNT(1) INTO v_count FROM dept WHERE deptno= :new.deptno;
IF v_count!=1 THEN
raise_application_error(-20001,‘找不到这个部门’);
END IF;
IF :new.job !=‘MANAGER’ AND :new.sal >8000 THEN
raise_application_error(-20002,‘不是manager，不能超过8000’);
END IF;
END IF;
IF deleting THEN
raise_application_error(-20003,‘不能删除数据’);
END IF;
END;

INSERT INTO emp(empno,ename,job,sal,deptno) VALUES (swq_test.nextval,‘张4’,‘保安’,8000,50);

DELETE FROM emp WHERE empno=7935

SELECT * FROM emp

2. 在user_1表创建一个触发器，
   更改联系人，和公司名称时，把新的和旧的联系人，新的公司名称和旧的公司名称 记录到日志表中（自己建日志表）。
   新增客户时，要记录新增的客户id。
   删除时，不能删除。

SELECT * FROM user_1

CREATE TABLE record_user1_log(
ID NUMBER PRIMARY KEY,
tyep VARCHAR2(10),
username Varchar2(30),
ipadrr VARCHAR2(20),
old_contacts VARCHAR2(20),
new_contacts VARCHAR2(20),
old_user VARCHAR2(50),
new_user VARCHAR2(50),
sdate DATE
)

SELECT * FROM record_user1_log

CREATE SEQUENCE sq_u1_log
START WITH 1
INCREMENT BY 1
NOMAXVALUE
CACHE 10;

2. 在user_1表创建一个触发器，
   更改联系人，和公司名称时，把新的和旧的联系人，新的公司名称和旧的公司名称 记录到日志表中（自己建日志表）。
   新增客户时，要记录新增的客户id。
   删除时，不能删除。

CREATE OR REPLACE TRIGGER tri_user1
AFTER
UPDATE OR INSERT OR DELETE
ON USER_1
FOR EACH ROW

DECLARE
v_type VARCHAR2(20);
v_username VARCHAR2(30);
v_ipadrr VARCHAR(20);
v_old_contacts VARCHAR(20);
v_new_contacts VARCHAR(20);
v_old_user VARCHAR(50);
v_new_user VARCHAR(50);
v_sdate DATE;
v_new_userid VARCHAR2(6);
BEGIN
SELECT USER,SYS_CONTEXT(‘userenv’, ‘ip_address’),SYSDATE INTO v_username,v_ipadrr,v_sdate FROM dual;
IF updating THEN
v_type:=‘更新’;
v_old_contacts:= :old.联系人姓名;
v_new_contacts:= :new.联系人姓名;
v_old_user:= :old.公司名称;
v_new_user:= :new.公司名称;
v_new_userid:= :new.客户id;
INSERT INTO record_user1_log(ID,tyep,username,ipadrr,old_contacts,new_contacts,old_user,new_user,sdate,user_id)
VALUES(sq_u1_log.nextval,v_type,v_username,v_ipadrr,v_old_contacts,v_new_contacts,v_old_user,v_new_user,v_sdate,v_new_userid);
ELSIF inserting THEN
v_type:=‘新增’;
v_new_contacts:= :new.联系人姓名;
v_new_user:= :new.公司名称;
v_new_userid:= :new.客户id;
INSERT INTO record_user1_log(ID,tyep,username,ipadrr,new_contacts,new_user,sdate,user_id)
VALUES(sq_u1_log.nextval,v_type,v_username,v_ipadrr,v_new_contacts,v_new_user,v_sdate,v_new_userid);
ELSIF deleting THEN
raise_application_error(-20003,‘不能删除数据’);
END IF;
END tri_user1;

SELECT * FROM record_user1_log
SELECT * FROM user_1 WHERE 客户id=‘aavvv’;

UPDATE USER_1 SET 公司名称=‘赐芳股份有限公’ WHERE 客户id=‘SPECD’;

INSERT INTO User_1(客户id,公司名称,联系人姓名,联系人职务) VALUES (‘aavv6’,‘坤哥股份’,‘陆先生’,‘销售员’);

DELETE FROM User_1 WHERE 客户id=‘aavvv’;

有一个商品价格的字段，要求把价格的数字提取出来，例如？217.80元/瓶=217.80

DISTINCT 和 GROUP BY 都是用于处理重复数据的关键字，但它们之间有几个关键的区别：

1. 用途：
   • DISTINCT 用于从结果集中消除重复行，返回唯一的行。
   • GROUP BY 用于对数据进行分组，并对每个组应用聚合函数，以便对每个组返回一个汇总值。
2. 作用范围：
   • DISTINCT 适用于整个查询结果集，消除所有列的重复行。
   • GROUP BY 适用于对特定列或列组进行分组，并且通常与聚合函数一起使用，以便对每个组计算一个值。
3. 结果集：
   • 使用 DISTINCT 可以获得整个结果集中的唯一行，而不进行任何聚合计算。
   • 使用 GROUP BY 可以对结果集中的数据进行分组，并且通常会对每个组应用聚合函数，以便得到每个组的汇总值。
4. 性能：
   • 在一些情况下，DISTINCT 可能会比 GROUP BY 更高效，尤其是当仅需要简单地消除重复行时。
   • 但是，当需要对数据进行分组并计算聚合值时，GROUP BY 更适合，因为它可以在数据库引擎层面上对数据进行优化。
5. 使用场景：
   • DISTINCT 适用于需要获得唯一值的场景，但不需要进行汇总计算。
   • GROUP BY 适用于需要对数据进行分组并计算每个组的聚合值的场景，例如统计每个部门的总销售额或平均工资等。