Oracle 数据库

Oracle 数据库

一、三范式规范：

1NF： 数据库中每一列数据都是不可分割的基本数据项

2NF：第二范式在第一范式的基础上，符合非主属性非部分依赖于主键

3NF：第三范式建立在前俩个范式之上，满足属性不依赖于其他非主属性

二、常见的查询语句

查询语句	效果
SELECT * FROM 表名	查询该表中所有内容
SELECT 列名1，列名2 FROM 表名	查询该表中表示的列所有内容
SELECT DISTINCT 列名 FROM 表名	distinct 达到去重的效果
SELECT 列名 as 别名 FROM 表名 as 别名	as 可以被列名和表明取别名 as可省略
nvl（值1，值2）	当值1为null时，默认值2
not	取反
escape（‘ ’）	指定转义字符
Union | UnionAll | Minus	俩个查询结果的：并集去重|并集不去重|交集
brder by	排序 默认asc升序、desc 降序
nulls first|last	null值排序显示在最前|最后

三、函数

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

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

一、单行函数

1.日期函数：

​ 1、sysdate|current_date 以date类型返回当前的日期

select current_date from dual where 1=1; --dual 是虚表
select sysdate from dual where 1=1; 
select sysdate+2 from dual; --修改当前日期 加减都阔以

​ 2、add_months(d,x) 返回加上x月后的日期d的值

--当前5个月后的时间
select add_months(sysdate,5) from dual;

​ 3、LAST_DAY(d); 返回所在月份的最后一天

--返回雇佣日期当月最后一天的时间 
select ename, last_day(hiredate) d from dual;

​ 4、months_between(date1,date2) 返回date1和date2之间月的数目

--返回指定日期到现在时间的差
select months_between(sysdate , 指定日期) from dual; 

​ 5、next_day(sysdate,‘星期一’) 下周星期一

--返回下一个星期二的日期
select next_day(sysdate, '星期二') from dual; 

2.转换函数*

​ 一、to_date(c,m) 字符串以指定格式转换成为日期

select to_date('2017-3-21 18:12:12','yyyy-mm-dd hh24:mi:ss') time from dual; 
select to_char(sysdate,'yyyy-mm-dd')  from dual; 
select to_char(sysdate,'yyyy\mm\dd')  from dual;

​ 二、to_char(d,m) 日期以指定格式转换为字符串

to_char(sysdate,'yyyy"年"mm"月"dd"日"')  from dual; --注意中文的问题

3.组函数，多行函数

一、cont() 统计记录数

二、max() min() 最大值 最小值

三、sum() 求和

四、avg() 平均值

注意：1. 组函数仅在选择列表和Having句子中有效（不能再where中使用）

​ 2. 出现组函数，select 只能有组函数或分段字段

​

4.分组函数

分组：group by，将符合条件的记录进行分组

过滤组：having ，过滤组信息，表达式同where一致

select distinct * | 字段 | 表达式 | 函数 as 别名 
 from 表名 
 where 过滤行记录条件 
 group by 分组字段列表 
 having 过滤组

注意：

1. select 出现分组函数，就不能使用非分组信息，可以使用 group by 字段

2. group by字段可以不出现select中，select 除了组函数外的， 其他字段必须出现在group by 中

5.行转列的实现

decode(字段名1，字段值1，结果1) 函数

select name, 
       min(decode(course, '语文', score)) 语文, --score 对于科目的分数
       min(decode(course, '数学', score)) 数学, 
       min(decode(course, '英语', score)) 英语 
  from tb_student 

四、rowid/rowmun

1、rowid代表每一跳数据的一个id。即使数据一模一样，每行记录的rowid值也是不同的

作用：实现重复记录的删除

-- 每条记录的唯一标识 
select s.* , rowid from tb_student s; 
--找出 保留的rowid 
select min(rowid) from tb_student group by name,course; 
--删除 不要的记录
delete from tb_student where rowid not in (select min(rowid) from 
tb_student  group by name,course); 

2、rowmun每次查询会自动排序从1开始

作用：对表进行指定行数实现划分

--根据sal工资降序，然后取前五名
select e.*, rownum 
  from (select * from emp order by sal desc) e 
 where rownum <= 5; 

五、表连接

笛卡尔积：表连接会出现笛卡尔积现实，俩个表数据之间一一进行了匹配，要避免这个情况

select ename, dname, e.deptno from emp e, dept d;

1. 等值连接（在笛卡尔积 的基础上取相同条件的值）

   --找出30部门的员工名称及部门名称:先关联后过滤
   select ename, dname, e.deptno from emp e, dept d where 
   e.deptno=d.deptno and e.deptno=30; 
   

2. 非等值连接 < > != <> bentween 值1 and 值2

   --900  属于哪个等级
   select grade from salgrade where 900 >losal and 900<hisal; 
   select grade from salgrade where 900 between losal and 
   hisal; 
   

3. 自连接（来自于同一张表）

    --找出 存在上级的员工姓名 及上级名称
    -- 数据来源： emp e, emp m
    -- 字段： e.ename, m.ename
    -- 条件： e.mgr=m.empno
   select e.ename, m.ename from emp e, emp m where e.mgr=m.empno;
   

4. 外连接

   --找出 所有的员工姓名 及上级名称 
   --找出 所有部门的员工数 及部门名称 
   --看+号,主表在,的左边就叫左外连接 主表在,的右边叫右连接 
   select dname, nu from dept d, (select count(1) nu, deptno  from emp 
   group by deptno) e where d.deptno(+)=e.deptno; 
   

六、99连接

1. 交叉连接 corss join 会发生笛卡尔积现象

select * from emp cross join dept; 

2. 自然连接 natural join 等值连接

   select * from emp natural join dept; 
   --在指定列过程中同名列归共同所有(*除外) 
   select deptno,e.ename,d.dname from emp e natural join dept d; 
   

3. join using连接 等值连接

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

4. [inner] join on 连接 ***

   --on 
   select ename, dname 
     from emp 
     join dept 
       on emp.deptno = dept.deptno  
   

5. left|rigth join on 外连接

   --左外 left
   select dname, n 
     from dept d left outer join (select deptno, count(1) n from emp 
     group by deptno) i 
     on d.deptno = i.deptno; 
   --右外     
   select dname, n 
     from  (select deptno, count(1) n from emp group by deptno) i 
     right outer join  dept d 
       on d.deptno = i.deptno; 
   

6. full join on 全连接 满足直接匹配 不满足互相补充，确保所有表的记录都至少出现一次

select * from (select 1 no, 'a' "name" 
          from dual union 
        select 2 no, 'b' "name" from dual) a 
  full join (select 1 no, 'c' "name" 
               from dual 
             union 
             select 3 no, 'd' "name" from dual) b 
 using(no); 

七、视图

视图：建立在表|结果|视图上的虚拟表

作用：

1. 简化select 查询语句

   2. 重用封装select语句

   3. 隐藏内部细节

   4. 区分相同数据不同查询

      创建视图

      create or replace view 视图名 as select 语句[with read only]
      --所有列必须存在名称、对视图的删除不影响原有表的数据
      --删除视图
      drop view 视图名
      

八、索引

1. 索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检
   索方式，从而提高检索效率

2. oracle 创建主键时会自动在该列上创建索引

添加索引可以提高查询速度

1、唯一性较好字段适合创建索引

2、大量数据才有效果

3、主键|唯一：唯一索引

创建

create index 索引名 on 表名（字段列表）--创建索引
drop index 索引名 					--删除索引
create index idx_emp on emp(sal,ename); 
drop index idx_emp;