Oracle学习基础（一）总结

迈不过友情╰ 2022-03-01 10:22 339阅读 0赞

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

/*本节使用的Oracle自带的表：emp(员工表),dept(工资表)
      您可以使用SQL语句查看Oracle所有自带表：select * from tab;*/
      
      select * from emp;
      select * from dept;

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

**Oracle数据库结构：  
数据库---数据库实例（ORCL） ----表空间（逻辑单位，用户）---数据文件（物理单位）  
通常情况下Oracle 只会有一个数据库实例ORCL**

**新建一个项目：**  
***Mysql：创建数据库----数据库表  
Oracle：创建一个表空间---创建用户---由用户创建表***

***差别：Oracle是多用户的，Mysql是多数据库的。***

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

###    基本查询：         SQL：结构化查询语言   四大组成：         DDL：数据库定义语言。 create,alter,drop,truncate         DML:数据库操纵语言，insert,update,delete         DCL:数据库控制语言，安全 授权，grant, revoke         DQL:数据库查询语言 ， select    from 子句  where子句   查询语言的结构：         select 列名 from 表名 where 条件 group by 分组条件 having 过滤 order by 排序 limit 分页          ###

***知识介绍：***

/*
    查询语句的四则运算：
           通常 from dual(deal表是虚表，伪表，只是用来补充语法的。)
    */
    select * from emp;
    select 1+1;--在oracle里面等于报错，Mysql正常执行。
    select 1+1 from dual;--2 正常
    --查询员工一个月的工资
    select sal*12 from emp;

--通常写一个数字要比写* 更高效，查询计划没有变化，差距在于对表做过分析之后查询的快慢
    
    select * from dual;
    
    select 1 from emp;

/*
    两者都不忽略值为NULL的行，相反count(列名)的统计是忽略值为null
    */
    select count(1) from emp;--12
    select count(*) from emp;--12
    select count(comm) from emp;--4

/*
    别名查询：
           使用as关键字，可以忽略。
    别名中不能有特殊字符（包括空格）或者关键字，如果有就加双引号备注
    */
    select ename 姓名 from emp;--正常
    slect ename 姓 名 from emp;--报错，别名有可空格且没有双引号备注
    select ename "姓  名" from emp;--正常

/*
    去除重复数据：distinct
    多列去除重复：每一列一样才算是重复数据
    */
    --单列去除重复数据
    select job from emp;--14条数据
    select distinct job from emp; --5条数据
    
    --多列去除重复数据
    select job,deptno from emp;--14条
    select distinct job,deptno from emp;--9条数据

/*
    字符串拼接：
           java:+
           Oracle: \\ (Oracle中特有的拼接符)
    在Oracle中，双引号主要用于别名的备注，单引号主要用于字符值的备注
    拼接函数：concat(str1,str2) 函数，在Mysql中和Oracle中都有。
    */
    --查询员工姓名:姓名：SCOTT
    select ename from emp;
    --使用拼接符号：
    select '姓名:'||ename from emp;
    
    --使用拼接函数拼接
    select concat('姓名:',ename) from emp;

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

**条件查询：\[where\] 后面写条件  
    关系运算符：= > < >=  <=  != <>  
    逻辑运算符：and or not  
    其他运算符：  
              like 模糊查询  
              in(set集合) 在某个集合里  
              between ... and ..在某个区间内  
              is null 判断为空  
              is not null 判断不为空**

**模糊查询like:  
              \_匹配单个字符  
              %匹配多个字符  
              如果有特殊字符，需要使用escape转义  
              **

--查询每个月能得到奖金的员工
    select ename from emp where comm is not null;
    --查询工资在1500到3000之间的员工
    select * from emp where sal between 1500 and 3000;
    --查询名字在某个范围的员工信息
    select * from emp where ename in('JONES','SCOTT','FORD')
    --查询员工中包含%的员工信息
    select * from emp where ename like '%\%%'escape '\';

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

**排序：  
       order by   
升序：asc ascend  
降序：desc descend**

**排序注意null问题， nulls last/first  
同时排列多列，用,隔开**

--查询员工信息，将奖金由高到低
    select * from emp order by comm desc nulls last

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

###    函数：          函数必须有返回值   单行函数：          数值函数，字符函数，日期函数，转换函数，通用函数   多行函数：          对一列的所有进行集中处理：max(),min(),sum(),count(),avg()          多行函数忽略空值     ###

--统计员工工资总和
    select sum(sal) from emp;
    --统计员工员工数量
    select count(*) from emp;
    --统计员工平均奖金
    select avg(comm) from emp;

**数值函数  
ceil()--向上取整数  
floor()--向下取整  
round(数值，保留小数位数)--指定保留小数位数后四舍五入  
trunc(数值，指定截断小数位数)--指定截断小数位数后不进行四舍五入  
mod(被除数,除数)--求余**

select ceil(48.3678) from dual;--49
    select floor(48.3678) from dual--48
    select round(48.5678,2) from dual;--48.57
    select trunc(48.5678,2) from dual;--48.56
    select mod(9,3) from dual;--0
    select mod(9,4) from dual;--1

**字符函数；  
     substr(str1,起始位置索引，长度)  
 注意：起始位置索引不管是写0或者1都是从第一个字符开始截取。  
    length(str1)获取字符长度  
    trim(str1) 去除字符两边的空格  
    replace(str1,需要替换的字符,替换的字符)；**

select substr('abcdefg',0,3) from dual;--abc
    select substr('abcdefg',1,3) from dual;--abc
    --获取字符长度
    select length('strabcd') from dual;--7
    --去取字符左右两边的空格
    select trim('  hello  ')from dual;--'hello'
    --替换字符
    select replace('hello','e','w') from dual;--'hwllo'

**日期函数  
       今天的日期：sysdate  
       查询几个月后的日期：add\_months(此时的日期,此后的几个月)  
       查询两个入职时间相差月份：months\_between(日期1，日期2);**

--查询今天的日期：
    select sysdate from dual;
    --查询3个月后今天的日期
    select add_months(sysdate,3) from dual;
    --查询3天后的日期
    select sysdate+3 from dual;
    --查询员工入职的月数(不满一个月算一个月)
    select ceil(months_between(sysdate,hiredate)) from emp;

**转换函数：  
       数值转字符，字符转数值，日期  
 \* to\_number(str) 字符转数值，可以不写，默认会转换  
 \* to\_char(数值,自定义转换的格式) 数值转字符   
 \* to\_char(日期,转换格式) 日期转字符**

**\*to\_date(日期字符,日期格式) 字符转化为date类型的日期，也可以直接 date 字符。**

--查询时间在1998-01-01到1998-12-12日期入职的员工姓名
    
    select ename,hiredate from emp where hiredate between date '1998-01-01' and date '1998-12-12';
    
    select ename,hiredate from emp where hiredate between to_date('1998-01-01','yyyy-mm-dd') and to_date('1998-12-12','yyyy-mm-dd');
    
    
    --字符转数值
    select 100+'10' from dual;--110  默认转换
    select 100+to_number('10') from dual;--110 to_number转换
    
    --emp表工资数值转指定格式字符
    select to_char(sal,'$9,999.9') from emp; --按'$9,999.0'的格式转换输出
    
    --日期转字符
    select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;--输出2019-03-22 11:19:45
    --获取当前日期的年份
    select to_char(sysdate,'yyyy') from dual;--2019
    --获取当前日期的月份
    select to_char(sysdate,'mm') from dual;--03
    --获取当年日期的各种天数
    
    --*一个星期里的第几天（从星期天开始）
    select to_char(sysdate,'d') from dual;--6 (今天为星期五)
    --*一个月的第几天
    select to_char(sysdate,'dd') from dual;--22 (今日是22号)
    --*一年中的第几天
    select to_char(sysdate,'ddd') from dual;--081(代表一年中的第81天)
    --*一个星期的星期几
    select to_char(sysdate,'day') from dual;--星期五
    --*一个星期的星期几
    select to_char(sysdate,'dy') from dual;--星期五

**通用函数：  
       nvl(参数1，参数2) 如果参数1=null是返回参数2,如果不是null返回参数1  
       nvl2(参数1,参数2,参数3)如果参数1=null,就返回参数3，否则返回参数2  
       nullif(参数1,参数2) 如果参数1=参数2 则返回null,否则返回参数1  
       coalesce:返回第一个不为null的值**

--nvl
    select nvl(null,999) from dual;--999
    select nvl(100,99) from dual;--100
    --nvl2
    select nvl2(null,6,99) from dual;--99;
    select nvl2(100,6,99) from dual;--6
    
    --nullif
    select nullif(99,99) from dual;--null
    select nullif(99,9) from dual;--返回参数1  即99
    
    --coalesce
    select coalesce(null,null,66,99) from dual;--66

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

**条件表达式：**

**   1.case 列名 when 值1 then 值  when 值2 then 值 else 默认值  end**

**case ...when...表达方式在mysql和Oracle里都可以用，Oracle特有的写法：decode(字段,if1,then1,if2,then2,else1)**

**2.分组表达式：group by 条件**

**select 分组的条件，分组之后的操作 from 表名 group by 分组的条件  having 条件过滤**

**3.SQL的编写顺序：select .. from .. where .. group by .. having.. order by.. limit**

**        SQL的执行顺序：from .. where .. group by ....having... order by ... limit... select ...**

**由此可知：select .. 中取的别名，后面是不能直接拿来使用的。**

select deptno,avg(sal) bb from emp group by deptno having bb>2000;--会报错，原因是bb这个别名没有定义。

**where 和 having的区别：**

**     where 后面不能接聚合函数,可以执行单行函数。 having 接在group by 后面 可以接聚合函数。**