oracle学习壁击

忿忿的泥巴坨  金牌会员 | 2024-8-21 13:39:01 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 845|帖子 845|积分 2535

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)
不即是<> !=
  1. SELECT empno,ename,sal,deptno FROM emp WHERE sal>2000 ORDER BY sal DESC;
复制代码
  1. SELECT empno,ename,sal,deptno,job FROM emp WHERE job='MANAGER';
复制代码
工资大于1500 后哦这部分是10号的人
  1. SELECT empno,ename,sal,deptno FROM emp WHERE sal>1500 or deptno=10;
复制代码
查询10号部分的manager
  1. SELECT empno,ename,job,deptno FROM emp WHERE job='MANAGER' AND deptno=10;
复制代码
查询工资大于1000的clerk 的 工号姓名工作工资部分 空格/as 后加别名
  1. SELECT empno 工号,ename 姓名,job 工作,sal,deptno FROM emp WHERE sal>1000 AND job='CLERK' ORDER BY sal DESC;
复制代码
虚拟表 dual不存咋真实数据的假表，她的存在为了让某些盘算符合语法 SELECT * FROM 表;
  1. SELECT (10*5)+7 FROM dual;
  3. SELECT SYSDATE FROM dual;
复制代码
day2

多个字段起落序按先后次序而定
闭区间 开区间
>=300 <=900
>300 <=900
>=300 <900
闭区间的表示方式
between and
查看1000到5000工资的人员信息
  1. select * from emp where sal between 1000 and 5000；
复制代码
in 再元素中查找 not in 取反
空值特性：单元格里什么都没有
1.排序老大 2.不参与盘算 select e.*,comm+200 from emp e;
3.表示方式
找出绩效为空的人员
  1. select * from emp where comm is null;
  3. select * from emp where comm is not null;
复制代码
4.转空值 当值为空值时，把它转为另一个值
nvl（）
给每个人绩效加两百
伪列 数据表中不存在的列，是查询寻出结果后赋予行数的基表行号
动态分配
ROWNUM从1开始数
不能写>和 >=
like 含糊查询
同查和通配符结合利用
% 匹配0-n字符
_匹配一个字符
escape ‘/’
查询名字以S开头，第三位是O，末了一位是T的人员
  1. SELECT * FROM emp WHERE ename LIKE 'S_O%T';
复制代码
ESCAPE '/'把紧跟着/后面的一个通配符转义为普通字符
查询名字中含有％的人
  1. SELECT * FROM emp WHERE ename LIKE '%/%' ESCAPE '/';
复制代码
any 恣意一个
  1. SELECT * FROM emp WHERE sal =ANY(1000,2000,3000)
复制代码
all 所有
  1. 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实验次序

  • 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() 字符拼接
  1. SELECT CONCAT('1','2') FROM dual;
  3. SELECT '1'||'2'||'3'FROM dual;
复制代码
LENGTH() 盘算字符串长度
  1. SELECT LENGTH('十九点'||'sfs') FROM dual;
复制代码
字符更换
单个更换
REPLACE()
aabbcc a更换成1
  1. SELECT replace('aabbccaadd','aa',1) FROM dual;
复制代码
逐个更换 translate
aabbccdd
  1. SELECT translate('abcdc','ab',12) FROM dual;
复制代码
被更换字符多则对不上的不会生效，少则丢失
对字母的操纵
upper（）转大写
lower（）转小写
initcap（）首字母大写
  1. SELECT UPPER('aabBDc'),LOWER('aaBdDc'),INITCAP('aabb') FROM dual
复制代码
对空格的操纵
trim()去双方的空格
LTRIM()去左边的空格
RTRIM()去右边的空格
  1. SELECT rTRIM(' aa ') FROM dual;
复制代码
盘算函数
MOD()取余
round()四舍五入
trunc（）
power（）
sqrt（）
sum（）
min（）
count（）
abs（）
ceil（）向上取整
floor（）向下取整


  • day5
instr（str,’ ',1, ）末了没有表示查到末了
case when 条件表达式
另有一种只能写即是的情况
  1. SELECT e.*,
  3.        CASE job WHEN 'MANAGER' THEN '经理'
  5.         ELSE 'a' end
  7. FROM emp e
复制代码
-----------另有一种只能表示即是的情况
oracle 特有的
  1. DECODE 表示 CASE 字段 WHEN
  3. SELECT e.*,
  5.     DECODE(job,'MANAGER','经理','杂务工')
  7. FROM emp e;
  9. ##
复制代码
day4

RPAD(ename,10,‘-’)右添补 添补的字段(可剪切，决定从原字段第几位开始添补)，添补的总长度，
lpad(ename,20,’ ')左添补 括号内（添补字段，添补后的字符长度，添补的字符）
  1. SELECT RPAD(ename,10,'-')右填充,lpad(ename,20,' ')左填充 FROM emp;
复制代码
日期函数
to_date(‘2024-03-28’,‘yyyy-mm-dd’)
  1. SELECT * FROM emp WHERE hiredate >=to_date('1982-12-01','yyyy-mm-dd') *--不写时分秒默认为00：00：00*
复制代码
  1. SELECT SYSDATE FROM dual; *--获取系统时间*
复制代码
  1. SELECT DATE'2000-4-14' FROM dual; *--不支持时分秒*
复制代码
  1. SELECT to_date('1919-12-12 13:12:12','yyyy-mm-dd hh24:mi:ss') FROM dual; *--to_date支持时分秒*
复制代码
  1. SELECT * FROM emp WHERE hiredate >=to_date('1982-12-03','yyyy-mm-dd')
复制代码
日期±
  1. SELECT sysdate+300 FROM dual;
复制代码
  1. SELECT SYSDATE-300 FROM dual;
复制代码
  1. SELECT to_date('2024-01-01','yyyy-mm-dd'),DATE'2024-01-01' FROM dual;
复制代码
  1. SELECT SYSDATE -DATE'2023-04-09' FROM dual;
复制代码
月份加减
  1. SELECT add_months(SYSDATE,18) FROM dual
复制代码
查询 今天隔断2020-05-28有多少个月
MONTHS_between
  1. 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;
  1. SELECT ROUND(DATE'2023-06-30','yyyy')FROM dual
复制代码
  1. SELECT SYSDATE 系统日期  FROM DUAL;
复制代码
  1. SELECT SYSDATE + 1 日期加减，DATE '2020-1-1' - 2 日期加减天数 FROM DUAL;
复制代码
  1. SELECT ADD_MONTHS(SYSDATE, -3) 加减月份  FROM DUAL;
复制代码
  1. SELECT last_day(date'2001-2-1')当月最后一天  FROM DUAL;
复制代码
  1. SELECT next_Day(SYSDATE,1)  FROM DUAL; *--下一周第几天,周日为1*
复制代码
  1. SELECT TRUNC(sysdate,'yyyy')按年取整  FROM DUAL;*--返回的就是今年的第一天 换位mm或dd则是按月和天取整*
  3.       ROUND 同理
复制代码
盘算上个月的第一天，下个月的末了一天
  1. SELECT TRUNC((add_months(SYSDATE, -1)),'mm'), last_day(add_months(SYSDATE,+1)) FROM dual
复制代码
盘算emp表1号入职的人
  1. SELECT * FROM emp WHERE hiredate=TRUNC(hiredate,'mm')
复制代码
  1. SELECT EXTRACT(DAY FROM Sysdate) AS hire_month
  3. FROM dual
复制代码
（1）四年一闰百年不闰：即假如year可以大概被4整除，但是不能被100整除，则year是闰年。
（2）每四百年再一闰：假如year可以大概被400整除，则year是闰年。
盘算emp表，入职日期是闰年的
WHERE MOD(YEAR(hiredate),4)=0
先盘算
  1. SELECT  *
  3. FROM
  5. (SELECT e.*,last_day( ADD_months(TRUNC(hiredate,'yyyy'),+1)) a FROM emp e) e WHERE to_number(to_char(a,'dd'))=29
  7. SELECT  *
  9. FROM
  11. (SELECT e.*,last_day( ADD_months(TRUNC(hiredate,'yyyy'),+1)) a FROM emp e) WHERE to_char(a,'dd')=29
  13. 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()字符个数
  1.          substr要保留的数据     张三2个字符       2个字符  
  3. SELECT RPAD(substr(ename,1,3),LENGTH(ename),'*') FROM emp
  5.           rpad       4个字母长度     2个字母的长度
复制代码
​ 5
  1. SELECT substr(ename,1,3),LENGTH(ename) FROM emp
复制代码
  1. SELECT * FROM emp FOR UPDATE
复制代码
2.查询1981年一月到三月入职的员工
  1. SELECT * FROM emp WHERE hiredate >=to_date('1981-1','yyyy-mm') AND hiredate <=to_date('1981-3','yyyy-mm')
复制代码
3.转正是三个月的话，查询员工转正的日期
  1. SELECT e.*,add_months(hiredate,+3) FROM emp e
复制代码
4查询北京奥运会距今多少年 08-8-8
  1. SELECT (months_between(SYSDATE,DATE'2008-08-08'))/12 FROM dual;
复制代码
  1. 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 把时间转换为各种想要的结果
  1. SELECT emp.* FROM emp WHERE TO_CHAR(hiredate,'yyyy-mm-dd')>'1981-01-01'
复制代码
  1. SELECT to_char(SYSDATE,'d') FROM dual;dd ddd优先级最高
复制代码
day5

  1. CASE WHEN THE END
  3.      WHEN 情况1 THEN 做事情1
  5.      WHEN 情况2 THEN 做事情2
  7.       WHEN 情况n THEN 做事情n
  9.        ELSE 否则做什么 END；
复制代码
例子
当job是manager时 显示为经理
当他时clerk 显示为职员
当他时saleman时显示为销售员
其他情况显示为杂务工
  1. SELECT e.*,CASE WHEN job='MANAGER' THEN '经理'
  3.         WHEN job='CLERK' THEN '职员'
  5.          WHEN job='SALEMAN' THEN '销售'
  7.           ELSE '杂务工'
  9. END FROM emp e
复制代码
另有一种只能写即是的情况
  1. SELECT e.*,
  3.        CASE job WHEN 'MANAGER' THEN '经理'
  5.         ELSE 'a' end
  7. FROM emp e
复制代码
-----------另有一种只能表示即是的情况
oracle 特有的
  1. DECODE 表示 CASE 字段 WHEN
  3. SELECT e.*,
  5.      DECODE(job,'MANAGER','经理','杂务工')
  7. FROM emp e;
复制代码
当部分是10号时 显示出工资＋200
20时 显示工资+300
30时 显示工资-300
40时 工资+99
  1. SELECT e.*,CASE deptno WHEN 10 THEN sal+200
  3.          WHEN 20 THEN sal+300
  5.           WHEN 30 THEN sal-300
  7.            WHEN 40 THEN sal+99
  9.             END
  11.             FROM emp e
复制代码
  1. SELECT e.*,
  3.         DECODE(deptno,10,sal+200,20,sal+300,30,sal-300,40,sal+9999)
  5. FROM emp e
复制代码
当工资大于5000时输出你很屌哦，在四千到五千时输出菜坤，三千到四千输出牛比，其他输出小菜坤
  1. SELECT sal,CASE WHEN sal>=5000 AND sal<6000 THEN '坤哥'
  3.         WHEN sal BETWEEN 4000 AND 4999 THEN '坤坤'
  5.          WHEN sal BETWEEN 3000 AND 3999 THEN '蔡坤'
  7.           ELSE '小蔡坤' END 蔡坤们
  9. FROM emp e
复制代码
要求按列显示
要求按列显示名字
10 20 30
zs sa ad
  1. SELECT n,COUNT(1) FROM (
  3. SELECT DECODE(deptno,10,10,20,10,30,10,40,10) n,
  5.          CASE deptno WHEN 10 THEN ename END 十 ,
  7.           CASE deptno WHEN 20 THEN ename END 二,
  9.            CASE deptno WHEN 30 THEN ename END 三,
  11.             CASE deptno WHEN 40 THEN ename END 四   
  13. FROM emp e ORDER BY n) a GROUP BY  n
复制代码
  1. SELECT
  3. DECODE(货主地区,'华北','地区','华中','地区','华东','地区') n,
  5. max(CASE 货主地区 WHEN '华北' THEN 订单量 END) 华北,
  7. max(CASE 货主地区 WHEN '华中' THEN 订单量 END) 华中,
  9. MAX( CASE 货主地区 WHEN '华东' THEN 订单量 END) 华东 FROM
  11. (SELECT 货主地区,COUNT(1) 订单量 FROM ORDER_1 WHERE 货主地区 IN ( '华东','华北','华中') GROUP BY 货主地区) a GROUP BY n
复制代码
  1. SELECT
  3. sum(CASE 货主地区 WHEN '华北' THEN 订单量 END) 华北,
  5. sum(CASE 货主地区 WHEN '华中' THEN 订单量 END) 华中,
  7. sum(CASE 货主地区 WHEN '华东' THEN 订单量 END) 华东 FROM
  9. (SELECT 货主地区,COUNT(1) 订单量 FROM ORDER_1 WHERE 货主地区 IN ( '华东','华北','华中') GROUP BY 货主地区)
复制代码
  1. SELECT n, 一,二,三,四 FROM(
  3. SELECT DECODE(deptno,10,10,20,20,30,30,40,40) n,
  5.         to_char( CASE deptno WHEN 10 THEN ename END ) 一 ,
  7.           to_char(CASE deptno WHEN 20 THEN ename END) 二,
  9.            to_char(CASE deptno WHEN 30 THEN ename END) 三,
  11.             to_char(CASE deptno WHEN 40 THEN ename END) 四   
  13. FROM emp e )GROUP BY n
复制代码
  1. SELECT
  3.   deptno,
  5.   NVL(MAX(CASE WHEN deptno = '10' THEN ename END), '') AS "DepartmentA",
  7.   NVL(MAX(CASE WHEN deptno = '20' THEN ename END), '') AS "DepartmentB",
  9.   NVL(MAX(CASE WHEN deptno = '30' THEN ename END), '') AS "DepartmentC",
  11.   NVL(MAX(CASE WHEN deptno = '40' THEN ename END), '') AS "DepartmentD"
  13.   *-- 其他部门...*
  15. FROM
  17.   emp
  19. GROUP BY
  21.   deptno;
复制代码
累加结果
sum()OVER(ORDER BY)
AVG()OVER(ORDER BY)
  1. SELECT e.*,AVG(sal)OVER(ORDER BY hiredate),
  3.           MAX(sal)OVER(ORDER BY hiredate)
  5.           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
按工资排名
  1. SELECT e.*,
  3. row_number()OVER(ORDER BY sal DESC),
  5. RANK()OVER(ORDER BY sal DESC),
  7. dense_rank()OVER(ORDER BY sal DESC)
  9. FROM emp e
复制代码
查询显示每个岗位的工资排名情况
  1. SELECT e.*,row_number()OVER(PARTITION BY job ORDER BY sal DESC) FROM emp e
复制代码
查询每个部分的工资前三名
  1. SELECT a.* FROM(
  3. SELECT e.*,row_number()OVER(PARTITION BY deptno ORDER BY sal DESC ) 排名 FROM emp e) a WHERE 排名<=3
复制代码
查询每个部分第二个入职的人员日期
  1. SELECT a.* FROM
  3. (SELECT e.*,row_number()OVER(PARTITION BY deptno ORDER BY hiredate ) 排名 FROM emp e) a WHERE 排名=2
复制代码
----------------------------------------------------------------
偏移
向上偏移 lead()
向下偏移 LAG()
​ 给偏移后的空值设置默认值
LEAD(偏移字段，偏移几行，默认值)
按入职的前后次序，查询每一位员工比他上一位入职的多多少钱
  1. SELECT LAG(sal,1)OVER(ORDER BY hiredate),
  3.         sal-LAG(sal,1)OVER(ORDER BY hiredate)
  5. FROM emp
复制代码
---------------------------------------------------------------------
同一个部分按入职时间次序查询出员工比他下一位入职的多夺少钱？
  1. SELECT E.*,
  3.     LEAD(SAL, 1) OVER(PARTITION BY DEPTNO ORDER BY HIREDATE),
  5.     SAL - LEAD(SAL, 1) OVER(PARTITION BY DEPTNO ORDER BY HIREDATE)
  7.   FROM EMP E;
复制代码
查询工号相邻的员工，工资相差夺少钱？
  1. SELECT E.*,
  3.     SAL - LAG(SAL, 1) OVER(ORDER BY EMPNO) 与上一位相比,
  5.     SAL - LEAD(SAL, 1) OVER(ORDER BY EMPNO) 与下一位相比
  7.   FROM EMP E
复制代码
------------------------------------------------------------------------------------
同环比
同比 这一期比客岁同一期
环比 这一期比上一期
南宁房价24-3 月房价 同比增长 和23-3月比
​ 环比 和24-2月比
2024-3 10000
2023-3 9000
2024-2 11000
同比增长
  1. SELECT (10000-9000)/9000 FROM dual;
复制代码
环比增长
  1. SELECT (10000-11000)/11000 FROM dual;
复制代码
（新-旧）/旧
---------------------------------------------------------
子查询
嵌套在另一个查询中的查询
1.放在select 后面
显示人员工号姓名工资部分编号部分名称
  1. SELECT empno,ename,sal,deptno,(SELECT dname FROM dept d WHERE ) FROM emp
复制代码
2.from 后面，结果集当成一个表再次查询
同样 WITH AS
铲鲟比部分匀称工资高的人员
  1. WITH aa AS
  3. (SELECT e.*,AVG(sal)OVER(PARTITION BY deptno) 部门平均工资 FROM emp e)
  5. SELECT * FROM aa WHERE sal >部门平均工资
复制代码
3.放在where后面
查询和30号部分有同名的人员
  1. SELECT * FROM emp WHERE ename IN (SELECT ename FROM emp WHERE deptno=30) AND deptno<>30
复制代码
放在where后有一种特殊情况 单行多列
查询和6011这位员工同一天入职同一岗位的人员
  1. SELECT * FROM emp WHERE (hiredate,job)=
  3. (SELECT hiredate,job FROM emp WHERE empno=6011)
复制代码
4.放在having 后面
查询比30 号部分人员多的部分
  1. SELECT deptno,Count(1) FROM emp GROUP BY deptno HAVING COUNT(1)>(SELECT COUNT(1) FROM emp WHERE deptno =30)
复制代码
---------------------------------------------------------------------------------------
多表查询
找到表和表之间的联系
笛卡儿积
a表的每一行数据关联b的每一行数据
球队的编号 让你显示亮亮对战的可能性
  1. CREATE TABLE test_a (ID VARCHAR(2));
  3. SELECT * FROM test_a a,test_a b WHERE a.id<b.id;
  5. SELECT * FROM test_a a CROSS JOIN test_a b WHERE a.id<b.id ;
复制代码
内毗连
两个表能相互关联得上的数据
  1. SELECT e.*,d.* FROM emp e ,dept d;
  3. SELECT * FROM emp e INNER JOIN dept d ON d.deptno=e.deptno
复制代码
------------------------------------------------
查询部分地址是new York 的人员信息
  1. SELECT * FROM emp e INNER JOIN dept d ON d.deptno=e.deptno WHERE loc ='NEW YORK'
复制代码
查询华南地域客户的所有订单
  1. SELECT o1.* FROM order_1 o1 INNER JOIN USER_1 u1 ON o1.客户id=u1.客户id WHERE 地区='华南';
复制代码
查询订单编号10407的订单销售额
  1. SELECT sum(单价*数量)销售额 FROM
  3. （SELECT * FROM ORDER_INFO WHERE 订单id=10407）GROUP BY 订单id
  5. 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
得到结果
以左边的表为主表，右边的表为从表，显示完主表的所有数据，从表只显示能和主表关联的上的信息
  1. SELECT e.*,'--',d.* FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno
  3. WHERE d.deptno IS NULL
复制代码
用左毗连得出没有其部分信息的人员
外毗连注意字段
--------------------------------------
查询1997年有购买过东西，但是1998年没买过东西的客户
  1. SELECT * FROM
  3. (SELECT DISTINCT o.客户id,to_char(o.订购日期,'yyyy') FROM order_1 o WHERE to_char(订购日期,'yyyy')=1997) a
  5. LEFT JOIN
  7. (SELECT distinct o2.客户id,to_char(o2.订购日期,'yyyy') FROM order_1 o2 WHERE to_char(订购日期,'yyyy')=1998) b ON a.客户id = b.客户id
  9. WHERE b.客户id IS null
复制代码
查询1997年有购买过东西，但是1998年没买过东西的客户
  1. SELECT * FROM
  3. (SELECT DISTINCT o.客户id,to_char(o.订购日期,'yyyy') FROM order_1 o WHERE to_char(订购日期,'yyyy')=1998) a
  5. RIGHT JOIN
  7. (SELECT DISTINCT o2.客户id,to_char(o2.订购日期,'yyyy') FROM order_1 o2 WHERE to_char(订购日期,'yyyy')=1997) b
  9. ON a.客户id=b.客户id JOIN User_1 u ON u.客户id=b.客户id
  11. WHERE a.客户id IS NULL
复制代码
全毗连
FULL JOIN
不区分主从，显示完能相互关联的上的数据，在分别显示关联不上的数据，关联不上数据对应补null
  1. SELECT e.*,'--',d.* FROM emp e FULL JOIN dept d ON e.deptno=d.deptno
复制代码
  1. SELECT * FROM num_a ORDER BY ID;
复制代码
  1. SELECT * FROM num_b ORDER BY ID;
复制代码
  1. 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
复制代码
  1. SELECT COALESCE(a.id,b.id) AS ID,
  3.        COALESCE(a.name, b.name) AS NAME,a.age,b.nums
  5. FROM num_a a
  7. FULL JOIN num_b b
  9. ON a.id=b.id ORDER BY ID
复制代码
不等值关联 关联一个区间
  1. SELECT * FROM salgrade;
  3. SELECT e.*,s.* FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
复制代码
结果集
交集
Intersect查询两个结果集中重复的行
  1. SELECT * FROM emp WHERE deptno IN (10,20)
  3. intersect
  5. SELECT * FROM emp WHERE deptno IN (30,20)
复制代码
并集
Union查询两个结果集并去掉重复的的行
  1. SELECT * FROM emp WHERE deptno IN (10,20)
  3. UNION all/union
  5. SELECT * FROM emp WHERE deptno IN (30,20)
复制代码
补集
SELECT * FROM emp WHERE deptno IN (30,20)
MINUS
Minus查询返回第一个结果集中存在而第二个结果集中不存在的行
  1. SELECT empno,deptno FROM emp WHERE deptno IN (10,20)
  3. minus
  5. 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范例盘算

  • 第三种方法 pivot
语法: SELECT * FROM 表 PIVOT ( max(数据列 ) FOR 要转的列名 IN （新的列1,新的列2，…) ) ;–for是循环 也是遍历
for 可以看成循环，for前是行转列后显示的值(这里必须利用聚合函数)，for后是循环的字段(一样平常是有限的固定值，如字典等) in (里是对for后的循环字段处理，比如起别名，做运算等)
  1. SELECT * FROM 学生成绩表 PIVOT(MAX(分数) FOR 科目 IN ('语文' 语文 ,'数学' 数学,'英语' 英语 ) );
复制代码
快速建表
drop table 门生成绩_列
create table 表名 as 查询出来的结果
uppivot 列转行
  1. SELECT * FROM 表名 UNPIVOT （给要转的数据其新的列名 FOR 把列名 转行后的字段名 IN 把那些列转成行）
复制代码
  1. SELECT * FROM 学生成绩_列 unpivot( 分数 FOR 成绩 IN （'语文','数学','英语'）)
复制代码
  1. select 姓名,科目,成绩
  3. from 学生成绩_列 unpivot ( 成绩 for 科目 in ( 语文, 数学, 英语 ) );
复制代码
SELECT 你必要的列名 FROM 表 unpivot( 数据 FOR 新的列名 IN （原来老的列名）)
开窗函数
其实就是在明细后面加一列聚合的结果
比如
我想看一下每个人的具体信息和其对应部分的匀称工资
  1. SELECT emp.*,AVG(sal)OVER(PARTITION BY deptno) FROM emp
复制代码
聚合函数(字段)over(partition by 分组规则)
day7 累加排名同环比

累加结果
sum()OVER(ORDER BY)
AVG()OVER(ORDER BY)
  1. SELECT e.*,AVG(sal)OVER(ORDER BY hiredate),
  3. ​          MAX(sal)OVER(ORDER BY hiredate)
  5. ​          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
按工资排名
  1. SELECT e.*,
  3. row_number()OVER(ORDER BY sal DESC),
  5. RANK()OVER(ORDER BY sal DESC),
  7. dense_rank()OVER(ORDER BY sal DESC)
  9. FROM emp e
复制代码
偏移
向上偏移 lead()
向下偏移 LAG()
​ 给偏移后的空值设置默认值
LEAD(偏移字段，偏移几行，默认值)
按入职的前后次序，查询每一位员工比他上一位入职的多多少钱
  1. SELECT LAG(sal,1)OVER(ORDER BY hiredate),
  3. ​        sal-LAG(sal,1)OVER(ORDER BY hiredate)
  5. FROM emp
复制代码
同一个部分按入职时间次序查询出员工比他下一位入职的多夺少钱？
  1. SELECT E.*,
  3. ​    LEAD(SAL, 1) OVER(PARTITION BY DEPTNO ORDER BY HIREDATE),
  5. ​    SAL - LEAD(SAL, 1) OVER(PARTITION BY DEPTNO ORDER BY HIREDATE)
  7.   FROM EMP E;
复制代码
查询工号相邻的员工，工资相差夺少钱？
  1. SELECT E.*,
  3. ​    SAL - LAG(SAL, 1) OVER(ORDER BY EMPNO) 与上一位相比,
  5. ​    SAL - LEAD(SAL, 1) OVER(ORDER BY EMPNO) 与下一位相比
  7.   FROM EMP E
复制代码
------------------------------------------------------------------------------------
同环比
同比 这一期比客岁同一期
环比 这一期比上一期
南宁房价24-3 月房价 同比增长 和23-3月比
​ 环比 和24-2月比
2024-3 10000
2023-3 9000
2024-2 11000
同比增长
  1. SELECT (10000-9000)/9000 FROM dual;
复制代码
环比增长
  1. SELECT (10000-11000)/11000 FROM dual;
复制代码
（新-旧）/旧
---------------------------------------------------------
子查询
嵌套在另一个查询中的查询
1.放在select 后面
显示人员工号姓名工资部分编号部分名称
  1. SELECT empno,ename,sal,deptno,(SELECT dname FROM dept d WHERE ) FROM emp
复制代码
2.from 后面，结果集当成一个表再次查询
同样 WITH AS
铲鲟比部分匀称工资高的人员
  1. WITH aa AS
  3. (SELECT e.*,AVG(sal)OVER(PARTITION BY deptno) 部门平均工资 FROM emp e)
  5. SELECT * FROM aa WHERE sal >部门平均工资
复制代码
3.放在where后面
查询和30号部分有同名的人员
  1. SELECT * FROM emp WHERE ename IN (SELECT ename FROM emp WHERE deptno=30) AND deptno<>30
复制代码
放在where后有一种特殊情况 单行多列
查询和6011这位员工同一天入职同一岗位的人员
  1. SELECT * FROM emp WHERE (hiredate,job)=
  3. (SELECT hiredate,job FROM emp WHERE empno=6011)
复制代码
4.放在having 后面
查询比30 号部分人员多的部分
  1. SELECT deptno,Count(1) FROM emp GROUP BY deptno HAVING COUNT(1)>(SELECT COUNT(1) FROM emp WHERE deptno =30)
复制代码
---------------------------------------------------------------------------------------
多表查询
找到表和表之间的联系
笛卡儿积
a表的每一行数据关联b的每一行数据
球队的编号 让你显示亮亮对战的可能性
  1. CREATE TABLE test_a (ID VARCHAR(2));
  3. SELECT * FROM test_a a,test_a b WHERE a.id<b.id;
  5. SELECT * FROM test_a a CROSS JOIN test_a b WHERE a.id<b.id ;
复制代码
内毗连
两个表能相互关联得上的数据
  1. SELECT e.*,d.* FROM emp e ,dept d
  3. SELECT * FROM emp e INNER JOIN dept d ON d.deptno=e.deptno
复制代码
------------------------------------------------
查询部分地址是new York 的人员信息
  1. SELECT * FROM emp e INNER JOIN dept d ON d.deptno=e.deptno WHERE loc ='NEW YORK'
复制代码
查询华南地域客户的所有订单
  1. SELECT o1.* FROM order_1 o1 INNER JOIN USER_1 u1 ON o1.客户id=u1.客户id WHERE 地区='华南';
复制代码
查询订单编号10407的订单销售额
  1. SELECT sum(单价*数量)销售额 FROM
  3. （SELECT * FROM ORDER_INFO WHERE 订单id=10407）GROUP BY 订单id
复制代码
  1. 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
  1. CREATE TABLE 学生表
  3. (
  5. sno VARCHAR(20),
  7. NAME Varchar(50),
  9. sex Varchar(5),
  11. birthday DATE,
  13. CLASS VARCHAR2(10),
  15. id_card Varchar(18),
  17. tel VARCHAR(11),
  19. photo Blob
  21. );
复制代码
SELECT * FROM 门生表
增
  1. INSERT INTO 学生表 VALUES ('s001','李世民','男',DATE'2000-10-01','高三一班','450121','1333','0');
  3. INSERT INTO 学生表(sno,NAME,id_card) VALUES ('s002','苏轼','313144'),('s006','苏轼','313144');
  5. INSERT INTO 学生表 VALUES ('s003','王安石','女',DATE'2000-09-01','高三一班','450124','1333','0');
  7. INSERT INTO 学生表 VALUES ('s004','王维','女',DATE'2000-08-01','高三一班','450125','1333','0');
  9. INSERT INTO 学生表 VALUES ('s005','李白','女',DATE'2000-07-01','高三一班','450125','1333','0');
复制代码
COMMIT;
更
  1. UPDATE 学生表 SET sex='男' WHERE sno='s002';
复制代码
删
  1. DELETE FROM 学生表 WHERE sno='s005'
复制代码
–TRUNCATE TABLE 只能删全表 不能回滚
------------------------------------------------------------
对表布局的增删改
  1. SELECT * FROM 学生表;
复制代码
增
  1. ALTER TABLE 学生表 ADD 照片 BLOB;
复制代码
删
  1. ALTER TABLE 学生表 DROP COLUMN 照片;
复制代码
改
修改数据范例和精度
  1. ALTER TABLE 学生表 MODIFY tel VARCHAR2(14)
复制代码
修改字段名
  1. ALTER TABLE 学生表 RENAME COLUMN tel TO telphone;
复制代码
增
  1. ALTER TABLE 学生表 ADD 身高 number(5,2);
复制代码
删
  1. ALTER TABLE 学生表 DROP COLUMN 身高;
复制代码
改
  1. ALTER TABLE 学生表 MODIFY 身高 number(6,2);
  3. ALTER TABLE 学生表 RENAME COLUMN 身高 TO tall;
复制代码
--------------------------------------------------------------------
创建表的时候有什么规范？
数据库设计要满足的几个范式通常被称为关系数据库的范式化。主要的范式包括：
第一范式（1NF）：要求表中的每一列都是不可再分的原子值，也就是每个字段都是单一值的，而不能是集合、数组或者其他复杂范例。此外，每个表必须有一个唯一的主键来唯一标识每行数据。
第二范式（2NF）：在满足第一范式的基础上，要求表中的非主键列完全依靠于主键，而不是部分依靠。假如有部分依靠的情况，必要将其分离成独立的表。
第三范式（3NF）：在满足第二范式的基础上，要求表中的每个非主键列之间不存在传递依靠。也就是说，任何非主键列都不应该依靠于其他非主键列。由主键传递信息
-----------------------------------------------------------------------
约束
限制插入的数据符合我们要求的规范
主键约束 PRIMARY KEY 非空且唯一
唯一约束 UNIQUE 唯一约束（可空）
非空约束 NOT NULL 非空
检查约束 CHECK 检查字段信息是否符合定义规范
门生表
NAME 不能为空
sno 主键
id_card 唯一
tel 11位
  1. ALTER TABLE 学生表 MODIFY NAME NOT NULL
  3. ALTER TABLE 学生表 ADD CONSTRAINT pk_学生表 PRIMARY KEY(sno)
  5. ALTER TABLE 学生表 ADD CONSTRAINT uk_学生表 UNIQUE (id_card)
  7. ALTER TABLE 学生表 ADD CONSTRAINT ck_学生表 CHECK(LENGTH(tel)=11)
  9. SELECT * FROM 学生表
  11. UPDATE 学生表 SET id_card = '';
复制代码
学号 主键
  1. ALTER TABLE 学生表 ADD CONSTRAINT pk_学生表 PRIMARY KEY(sno);
复制代码
名字不能为空
  1. ALTER TABLE 学生表 MODIFY NAME NOT NULL;
复制代码
身份证要唯一
  1. ALTER TABLE 学生表 ADD CONSTRAINT uk_学生表 UNIQUE(id_card);
复制代码
身份证18位
  1. ALTER TABLE 学生表 ADD CONSTRAINT ck_学生表 CHECK(Length(id_card)=18);
复制代码
身份证非空
  1. ALTER TABLE 学生表 MODIFY id_card NOT NULL
复制代码
电话非空
  1. ALTER TABLE 学生表 MODIFY tel NOT NULL
复制代码
电话要11位
  1. ALTER TABLE 学生表 ADD CONSTRAINT ck_学生表_tel CHECK(Length(tel)=11);
复制代码
性别 只能 男女
  1. ALTER TABLE 学生表 ADD CONSTRAINT ck_学生表_sex check(sex IN ('男','女'))
复制代码
---------------------------------------------------
删除约束
  1. ALTER TABLE 学生表 DROP CONSTRAINT 约束名
复制代码
day10外键、数据抽取、索引

外键约束
可以分为三种3种
无级联外键
父表
  1. ALTER TABLE emp ADD CONSTRAINT fk FOREIGN KEY(deptno) REFERENCES dept(deptno);
复制代码
问题
我要删除dept的50号部分
  1. DELETE FROM dept WHERE deptno=55;
复制代码
UPDATE emp SET deptno=NULL WHERE deptno =55;
级联删除的外键
  1. ALTER TABLE emp ADD CONSTRAINT fk FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE CASCADE
复制代码
级联置空
  1. ALTER TABLE emp ADD CONSTRAINT fk FOREIGN KEY(deptno) REFERENCES dept(deptno) ON DELETE SET NULL;
复制代码
deptno约束为空时，级联置空
无法删除父表列名
快速建表
  1. CREATE TABLE 表明 AS 结果集；
复制代码
快速插入
把一个结果集的结果 插入到一个表里
SELECT * FROM employee
把这些员工插入到emp去
  1. INSERT INTO emp
  3. SELECT e.雇员id,e.姓氏||e.名字,e.职务 FROM employee e
复制代码
-----------------------------------------------------
db LINK =databaselink 数据库毗连
用来毗连另外一台电脑的oracle数据库
我想通过oracle毗连服务器的数据库
  1. SELECT * FROM z_memcard@to_nanning_orcl
复制代码
怎么建立dblink
SELECT * FROM z_memcard@fuwuqi
-----------------------------------
从服务器上把数据抽取到我自己的电脑的数据库上
抽取z_memcard d 的2010年的数据到自己的电脑上
1.快速建表
数据分层
ods贴源层
  1. CREATE TABLE ods_z_memcard as
  3. SELECT * FROM z_memcard@fuwuqi WHERE to_char(createtime,'yyyy')='2010'
复制代码
快速插入
  1. INSERT INTO ods_z_memcard
  3. SELECT * FROM z_memcard@fuwuqi WHERE to_char(createtime,'yyyy')='2011'
  5. SELECT COUNT(1) FROM ods_z_memcard
复制代码
---------------------------------------------------------
MERGE INTO
MERGE 是 SQL 中用于将数据插入（INSERT）、更新（UPDATE）或删除（DELETE）到目的表的操纵。
它通常用于同时检查源表和目的表，根据某些条件实验差异的操纵。
  1. MERGE INTO ods_z_memcard a
  3. USING (SELECT * FROM z_memcard@fuwuqi) b
  5. ON (a.id= b.id)
  7. WHEN MATCHED THEN UPDATE
  9.   SET a.cardlevel=b.cardlevel
  11. WHEN NOT MATCHED THEN INSERT
  13. VALUES (b.id,
  15. ​    b.memcardno,
  17. ​    b.busno,
  19. ​    b.cardtype,
  21. ​    b.cardlevel,
  23. ​    b.cardpass,
  25. ​    b.cardstatus,
  27. ​    b.saleamount,
  29. ​    b.realamount,
  31. ​    b.puramount,
  33. ​    b.integral,
  35. ​    b.integrala,
  37. ​    b.integralflag,
  39. ​    b.cardholder,
  41. ​    b.cardaddress,
  43. ​    b.sex,
  45. ​    b.tel,
  47. ​    b.handset,
  49. ​    b.fax,
  51. ​    b.createuser,
  53. ​    b.createtime,
  55. ​    b.tstatus,
  57. ​    b.notes,
  59. ​    b.stamp,
  61. ​    b.idcard,
  63. ​    b.birthday,
  65. ​    b.allowintegral,
  67. ​    b.apptype,
  69. ​    b.applytime,
  71. ​    b.invalidate,
  73. ​    b.lastdate,
  75. ​    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+树索引
用在去重之后，仍有很多值
  1. SELECT * FROM ods_z_memcard WHERE cardholder='张坤' ;
复制代码
  1. CREATE INDEX ind_z_memcard_name ON ods_z_memcard(cardholder);
复制代码
位图索引
用在去重，有较少的值，比如 性别，婚姻
  1. SELECT * FROM ods_z_memcard WHERE cardlevel='5'; *--0.12*
复制代码
  1. CREATE bitmap INDEX ods_memcard_bitmap ON ods_z_memcard(cardlevel);
复制代码
基于函数的索引
经常用某种函数去查询某个字段的时候
查看身份证是否够18
  1. SELECT * FROM ods_z_memcard WHERE LENGTH(idcard)=18;
  3. *--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’

  • 课后作业：
程序块完成，打印输出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

  • 有1 2 3 4 四个数字，可以组成多少个不重复的数字，显示出来，比如，不能有112，223，一共有多少个？
  • 假如你要预备结婚的彩礼是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

  • 在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;

  • 在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:= ld.联系人姓名;
v_new_contacts:= :new.联系人姓名;
v_old_user:= ld.公司名称;
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 都是用于处理重复数据的关键字，但它们之间有几个关键的区别：

  • 用途：

    • DISTINCT 用于从结果集中消除重复行，返回唯一的行。
    • GROUP BY 用于对数据进行分组，并对每个组应用聚合函数，以便对每个组返回一个汇总值。

  • 作用范围：

    • DISTINCT 实用于整个查询结果集，消除所有列的重复行。
    • GROUP BY 实用于对特定列或列组进行分组，而且通常与聚合函数一起利用，以便对每个组盘算一个值。

  • 结果集：

    • 利用 DISTINCT 可以得到整个结果集中的唯一行，而不进行任何聚合盘算。
    • 利用 GROUP BY 可以对结果集中的数据进行分组，而且通常会对每个组应用聚合函数，以便得到每个组的汇总值。

  • 性能：

    • 在一些情况下，DISTINCT 可能会比 GROUP BY 更高效，尤其是当仅必要简单地消除重复行时。
    • 但是，当必要对数据进行分组并盘算聚合值时，GROUP BY 更适合，因为它可以在数据库引擎层面上对数据进行优化。

  • 利用场景：

    • DISTINCT 实用于必要得到唯一值的场景，但不必要进行汇总盘算。
    • GROUP BY 实用于必要对数据进行分组并盘算每个组的聚合值的场景，例如统计每个部分的总销售额或匀称工资等。


免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

忿忿的泥巴坨

金牌会员
这个人很懒什么都没写!

楼主热帖

标签云

挺好的 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表