Oracle数据库

函数

单行函数：一条记录返回一条结果

自定义函数

1. -- trunc 取整

select ename,trunc(months_between(sysdate,hiredate)) from emp;

内置函数

日期对象与日期字符串之间相互转换

1. to_char：转换为字符

select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select sysdate,to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;

2. to_date：转换为数据

select sysdate,to_date('2022年02月23日','yyyy"年"mm"月"dd"日"') from dual;

多行|聚合|组函数：多条记录返回一个结果

1. count() ：求和（统计数目）

2. sum() ：求和（值求和）

3. max()：求最大值

4. min() ：求最小值

5. avg()：求平均值

注意：

1. 确定要计算的数据然后才能使用组函数,对结果集,分组

2. 如果select后面一旦出现组函数的使用,不能与非分组字段一起使用

分组

group by :根据指定的规则对数据进行分组

1. 语法: select 数据 from 数据源 where 行过滤条件 group by 分组 having 组过滤信息 order by 排序字段;

2. 执行流程 : from -> where -> group by -> having -> select -> order by

注意：

1. 先分组还是先过滤根据题来做出判断

2. where中不能使用组函数，因为执行顺序不允许

3. 可以在分组之后加having来进行过滤

行转列

--行转列
SELECT NAME,
       DECODE(COURSE, '语文', SCORE) 语文,
       DECODE(COURSE, '数学', SCORE) 数学,
       DECODE(COURSE, '英语', SCORE) 英语
  FROM TB_STUDENT;
  
  SELECT NAME,
       MAX(DECODE(COURSE, '语文', SCORE)) 语文,
       MAX(DECODE(COURSE, '数学', SCORE))数学,
       MAX(DECODE(COURSE, '英语', SCORE)) 英语
  FROM TB_STUDENT GROUP BY NAME;

表连接

定义：查询数据来自于多个数据源时运用表连接

分类

按条件分

1. 等值连接：两数据某字段相等连接，基于笛卡尔积基础上

   1. 92：需要连接条件

   2. 99：

      1. 自然连接 : 自动做等值连接(同名字段|主外键字段) natural join

         select ename,deptno,dname from emp natural join dept;

          

      2. 如果有多个字段可以做等值连接：join ..using(同名字段) 指定使用哪个同名字段做等值连接

         select ename,deptno,dname from emp join dept using(deptno);

          

2. 非等值连接：判断区间，> < != <>between and

99中：...join...on既可以做等值也可以做非等值连接

等值
select ename,e.deptno,dname from emp e join dept d on e.deptno = d.deptno;
非等值
select ename,sal,job,grade from emp e join salgrade s on sal between losal and hisal;

注意：在join..on中同名字段的必须使用限定词

按连接分

1. 内连接：满足条件查询，不满足则过滤

   1. 92：默认（inner）join

2. 外连接：某一数据不满足条件也显示

   1. 左外连接|左连接：from后面主表在左

      1. 99：left join

   2. 右外连接|右连接：from后面主表在右

      1. 99：right join

   注意：

   1. 主表：主表中的数据无论是否满足连接条件都能显示

   2. 如何区分主表：在连接条件位置,主表的对面添加(+)

      select * from emp e1,emp e2 where e1.mgr=e2.empno(+);

自连接：一张表作为两个数据源，自己与自己连接。

全连接：两张表全是主表，在99中：full join

rowid 与 rownum

1. rowid：实现重复记录的删除

   --1)查询到要保留的数据
   SELECT DISTINCT ID,NAME,COURSE,SCORE FROM TB_STUDENT; --显示去重
   SELECT MAX(ROWID) FROM TB_STUDENT GROUP BY ID,NAME,COURSE,SCORE; 
   --2)查到要删除的数据
   SELECT * FROM TB_STUDENT WHERE NOT ROWID IN (SELECT MAX(ROWID) FROM TB_STUDENT GROUP BY ID,NAME,COURSE,SCORE);
   --3)删除这些数据
   DELETE FROM TB_STUDENT WHERE NOT ROWID IN (SELECT MAX(ROWID) FROM TB_STUDENT GROUP BY ID,NAME,COURSE,SCORE);

    

2. rownum：结果集中行记录的序号

   1. 必须排序，如果存在排序之后,rownum序号被打乱,可以在select外部再次嵌套select语句,外部的select语句的rownum就是有规律的,可以作为判断使用的

      select empno,ename,sal,n,rownum num from (select empno,ename,sal,rownum n from emp order by sal desc);

       

   2. 不能直接取大于1的数

   3. 如果想要对rownum进行判断,需要针对一条已经确定的rownum进行判断,解决方案,外部嵌套select

   案例：在oracle数据库中可以使用rownum实现分页查询

   --分页 :  每页显示数据个数 n = 3   当前显示第几页 i    起始位置rownum : rownum> (i-1)*n;    结束位置rownum : rownum<=i*n
   ?
   select *
     from (select empno, ename, sal, rownum num
             from (select empno, ename, sal, rownum n from emp order by sal desc))
    where num <= 6
      and num > 3;

    

表设计

表名 ：明确表的作用

1. 明确表中的字段

   1. 字段名

   2. 字段类型

   3. 字段约束

      1. 主键约束(唯一+非空)

      2. 唯一约束

      3. 非空约束

      4. 默认约束

      5. 检查约束

      6. 外键约束

         1. 父表：

         2. 子表：子表中添加一个外键字段,关联主表的主键字段，外键字段的值只能为主表中主键字段已有的值

表的三范式：最终的目的避免数据重复冗余

1. 1NF-->列不可再分最小原子 （避免重复);

2. 2NF-->主键依赖(确定唯一)；

3. 3NF-->消除传递依赖(建立主外键关联 拆分表);**

表与表之间的关系

1. 一对一关系：主外键关联关系 用户表 身份证信息表

2. 一对多|多对一：主外键关联关系 : 在多的一方设置外键,关联一的一方的主键 班级表 学生表

3. 多对多: 中间表 : 定义两张表的关联关系 订单与商品 学生与选课