Oracle（一）

一、Oracle数据库的体系结构

数据库  ---> 数据库实例  --->  表空间(逻辑单位)(用户)  ---> 数据文件(物理单位)

地球    ---> 一个国家    --->  省份(逻辑单位)(公民)    ---> 山川河流(物理单位)

• 数据库：database

Oracle数据库是数据的物理存储。这就包括（数据文件ORA或者DBF、控制文件、联机日志、参数文件）。其实Oracle数据库的概念和其它数据库不一样，这里的数据库是一个操作系统，只有一个库。可以看作是Oracle就只有一个大数据库。

• 数据文件（dbf）：

数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。

• 表空间：

表空间是Oracle对物理数据库上相关数据文件（ORA或者DBF文件）的逻辑映射。一个数据库在逻辑上被划分成若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间（称之为system表空间）。

每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件（datafile）。一个数据文件只能属于一个表空间。

• 用户：

用户是在时立下建立的。不同实例中可以建相同名字的用户。

表的数据是由用户放入某一个表空间的，而这个表空间会随机将这些表数据放到一个或者多个数据文件中。

由于oracle的数据库不是普通的概念，oracle是由用户和表空间对数据进行管理和存放的。但是表不是由表空间去查询的。而是由用户去查询的。因为不同用户可以在同一个表空间建立同一个名字的表。这里区分的就是用户了。

 

通常情况下，Oracle数据库只会有一个实例ORCL，

新建一个项目：

       MYSQL：创建一个数据库，创建相应的表

       Oracle：创建一个表空间，创建用户，用户去创建表

Oracle和MYSQL的区别：

1. 遵循SQL标准
2. 不同厂商，不同的数据库产品，但是有自己的方言
3. 使用自己的方言，也能够完成相同的功能
4. Oracle安全级别要高，MYSQL开源免费

二、基本查询

SQL：结构化查询语言

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 排序]

select * from emp;

select 1+1;--在Oracle等于报错，在MYSQL中输出结果2

/*
     dual : oracle中的虚表 ,伪表, 主要是用来补齐语法结构  
*/
select 1+1 from dual;
select * from dual;

---

select 1 from emp;

select count(1) from emp; --0.009
--1、直接写一个常量要比写*高效
select count(*) from emp;--0.026

---

/*
       2、别名查询：使用as关键字，可以省略
       别名中不能有特殊字符或者关键字，如果有就加双引号
*/
select ename 姓名，sal 工资 from emp;

select ename "姓        名"，sal 工资 from emp;

---

/*
       3、去除重复数据distinct
       多列去除重复：每一列都一样才能够算作是重复
*/

--单列去除重复
select distinct job from emp;

--多列去除重复的
select distinct job,deptno from emp;

---

--4、查询中四则运算
select 1+1 from dual;

--查询员工年薪
select sal*12 from emp;

--查询员工年薪+奖金
select sal*12+comm from emp;
--nvl函数：如果参数1为null，就返回参数2
select sal*12+nvl(comm,0) from emp;
/*
       注意：null值，代表不确定，不可预知的内容，不可以做四则运算
*/

---

/*
       5、字符串拼接：
       java：+号拼接
       Oracle特有的连接符：||拼接
       在Oracle中，双引号主要是别名的时候使用，单引号使用的是值，是字符
       concat（str1，str2）函数，在mysql和Oracle中都有
*/
--查询员工姓名：姓名：SCOTT
select ename from emp;
--使用拼接符
select '姓名:'||ename from emp;
--使用函数拼接
select concat('姓名：',ename) from emp;

---

/*
       6、条件查询：[where后面的写法]
       关系运算符：> >= < <= = != <>
       逻辑运算符：and or not
       其他运算符：
                 like 模糊查询
                 in(set) 在某个集合内
                 between...and... 在某个区间内
                 is null 判断为空
                 is not null 判断不为空
               
*/
--查询每月能得到奖金的员工信息
select * from emp where comm is not null;

--查询工资在1500-3000之间的员工信息
select * from emp where sal between 1500 and 3000;

select * from emp where sal>=1500 and sal <=3000;

--查询名字在某个范围的员工信息('JONES','SCOTT','FORD')
select * from emp where ename in('JONES','SCOTT','FORD');

---

/*
       7、模糊查询：like
       _ 匹配单个字符
       % 匹配多个字符
       如果有特殊字符，需要使用escape转义
*/
--查询员工姓名第三个字符是o的员工信息
select * from emp where ename like '__O%';

--查询员工姓名中，包含%的员工信息
update emp set ename='SCOOT' where ename='SC%OOT';
select * from emp where ename like '%\%%' escape '\';
select * from emp where ename like '%#%%' escape '#';

---

/*
       8、排序：oeder by
       升序：asc ascend
       降序：desc descend
       
       排序注意null问题：nulls first | last
       
       同时排序多列，用逗号隔开
*/
--查询员工信息，按照奖金由高到低排序
select * from emp order by comm desc nulls last;

--查询部门编号和工资，按照部门升序排序，工资降序排序
select deptno,sal from emp order by deptno asc,sal desc;

---

/*
       9、函数：必须要有返回值
       单行函数：对某一行中的某个值进行处理
                  数值函数
                  字符函数
                  日期函数
                  转换函数
                  通用函数
       
       多行函数：对某一列的所有行进行处理，直接忽略空值
                  max() min count sum avg
*/
--统计员工工资总和
select sum(sal) from emp;

--统计员工奖金总和 2200
select sum(comm) from emp;

--统计员工人数 14
select count(1) from emp;

--统计员工的平均奖金 550 错误2200/14=
select avg(comm) from emp;
--统计员工的平均奖金 157.142857142857
select sum(comm)/count(1) from emp;
select ceil(sum(comm)/count(1)) from emp;--158

select * from emp;
--数值函数
select ceil(45.926) from dual;--46
select floor(45.926) from dual;--45

--四舍五入
select round(45.926,2) from dual;--45.93
select round(45.926,1) from dual;--45.9
select round(45.926,0) from dual;--46
select round(45.926,-1) from dual;--50
select round(45.926,-2) from dual;--0
select round(65.926,-2) from dual;--100
--截断
select trunc(45.926,2) from dual;--45.92
select trunc(45.926,1) from dual;--45.9
select trunc(45.926,0) from dual;--45
select trunc(45.926,-1) from dual;--40
select trunc(45.926,-2) from dual;--0
select trunc(65.926,-2) from dual;--0
--求余
select mod(9,3) from dual;--0
select mod(9,4) from dual;--1

--字符函数substr(str1,起始索引，长度)
--注意：起始索引不管写0还是1，都是从第一个字符开始截取
select substr('abcdefg',0,3) from dual;--abc
select substr('abcdefg',1,3) from dual;--abc
select substr('abcdefg',2,3) from dual;--bcd

--获取字符串长度
select length('abcdef') from dual;--6

--去除字符左右两边的空格
select trim('    hello    ') from dual;

--替换字符串
select replace('hello','l','a') from dual;

--日期函数
--查询今天的日期
select sysdate from dual;
--查询3个月后的今天的日期
select add_months(sysdate,3) from dual;
--查询3天后的日期
select sysdate+3 from dual;

--查询员工入职的天数
select sysdate-hiredate from emp;

select ceil(sysdate-hiredate) from emp;
--查询员工入职的周数
select (sysdate-hiredate)/7 from emp;
--查询员工入职的月数
select months_between(sysdate,hiredate) from emp;
--查询员工入职的年数
select months_between(sysdate,hiredate)/12 from emp;

--转换函数  数值转字符  字符转数值  日期
--字符转数值  to_number(str)
select 100+'10' from dual;--110  默认已经帮我们转换
select 100+to_number('10') from dual;--110
--数值转字符
select to_char(sal,'$9,999.99') from emp;

select to_char(sal,'L9,999.99') from emp;
/*
to_char(1210.73, '9999.9') 返回 '1210.7' 
to_char(1210.73, '9,999.99') 返回 '1,210.73' 
to_char(1210.73, '$9,999.00') 返回 '$1,210.73' 
to_char(21, '000099') 返回 '000021' 
to_char(852,'xxxx') 返回' 354'
*/
--日期转字符 to_char()
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
--只要年
select to_char(sysdate,'yyyy') from dual;--2019

--只要日
select to_char(sysdate,'dd') from dual;--09代表一个月中第几天
select to_char(sysdate,'d') from dual;--4代表一个星期中第几天
select to_char(sysdate,'ddd') from dual;--009代表一年中第几天

select to_char(sysdate,'day') from dual;--星期三
select to_char(sysdate,'dy') from dual;--星期三
--字符转日期
select to_date('2019-1-9','yyyy-mm-dd') from dual;
--查询1981年-1985年入职的员工信息
select * from emp where hiredate between to_date('1981','yyyy') and to_date('1985','yyyy');

/*
       通用函数：
       nvl(参数1，参数2) 如果参数1=null，就返回参数2
       nvl2(参数1，参数2，参数3) 如果参数1=null，就返回参数3，否则返回参数2
       nullif(参数1，参数2) 如果参数1=参数2，返回null，否则返回参数1
       coalesce：返回第一个不为null的值
*/
select nvl2(null,5,6) from dual;--6
select nvl2(1,5,6) from dual;--5

select nullif(5,6) from dual;--5
select nullif(5,5) from dual;--null

select coalesce(null,null,4,6) from dual;--4

select ceil(-12.5) from dual;-- -12
select floor(12.5) from dual;-- 12