掌握Oracle数据库

关系型数据库Oracle

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

新建项目

MySQL: 创建一个数据库，创建相应的表
Oracle：创建一个表空间 创建用户，用户去创建表

Oracle与Mysql的差别

1.遵循SQL标准
2.不同厂商(MySQL已被甲骨文收购)，不同的数据库产品，有不同的差异
3.使用自己的语言，也能够完成相同的功能
4.Oracle安全级别高 Mysql开源免费
5.Oracle是多用户的，Mysql是多数据库的

基本查询

SQL：结构化查询语言
SQL的分类以及每类常见的操作符有哪些
四类：
	DDL：数据定义语言： create alter drop
	DML：数据操作语言： insert updata delete
	DCL：数据控制语言： 安全 授权 grant revoke
	DQL：数据查询语言： select from 子句 where 子句

查询语句的结构：
	select [列名] [*] form 表名 [where 条件] [group by 分组条件] [having 过滤] [order by 排

一些差异：
select 1+1; --报错，在Mysql中输出结果是2
dual: oracle 中的虚表，伪表，主要是用来补齐语法结构

select 1+1 from dual;

select * from dual;

select 1 from emp;

直接写一个常量比写*要高效

select count(1) from emp;
select count(*) from emp;

别名查询：使用as 关键字 可以省略
别名中不能有特殊字符 或者关键字 如果有要加双引号

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

去除重复数据 distinct
多列去除重复： 每一列都一样才能够算做是重复

SELECT job from emp;

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

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

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

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

--查询员工年薪+奖金
select sal*12 + comm from emp;

--nvl 函数： 如果参数1为null 就返回参数2
select sal*12 + nvl(comm,0) from emp;

注意： null值 代表不确定 不可预知的内容 不可用做四则运算

字符串拼接：

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 判断不为空

-- 查询每月能得到奖金的员工信息
select * from emp where comm is not null and comm > 0;

-- 查询工资在1500--3000之间的员工细信息
select * from emp where SAL >= 1500 and SAL <= 3000;
select * from emp where SAL between 1500 and 3000;

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

模糊查询 like

% 匹配多个字符
_ 匹配单个字符
如果有特殊字符，需要使用escape转义

-- 查询员工姓名第三个字符是o的员工信息
select * from emp where ename like '__O%';

-- 查询员工姓名中包含 % 的员工信息  使用\可以转义
select * from emp where ename like '%#%%' escape '#';

排序：order 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;

函数：

必须要有返回值
单行函数：对某一行中的某个值进行处理
数值函数
字符函数
日期函数
转换函数
通用函数
多行函数： 对某一列的所有行进行处理
max() min() count() sum() avg()
1.直接忽略空值

-- 统计员工工资总和
select sum(sal) from emp;

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

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

-- 统计员工的平均奖金
select avg(comm) from emp;  错误
select sum(comm)/count(1) from emp;
select ceil(sum(comm)/count(1)) 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 floor(-12.5) from dual; =-13
	select ceil(-12.5) from dual; =-12
	select floor(12.5) from dual; =12
	

-- 截断
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 substr('abcdefg',-1,3) from dual; =g

-- 获取字符串长度 24 28
select length('abcdefg') from dual;

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

Oracle常用函数(字符串、日期)

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


-- 日期函数 (时间来自服务器时间)
-- 查询今天的日期
select sysdate from dual;

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

-- 查询三天后的日期
select sysdate + 3 from dual;

-- 查询员工日志的天数
select ename,sysdate - hiredate from emp;
select ename,ceil(sysdate - hiredate) from emp;

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

-- 查询员工入职的月数  months_between函数查看间隔的月份
select ename,months_between(sysdate,hiredate) from emp;

-- 查询员工入职的年份
select ename,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; =$1,600.00

-- 日期转字符 to_char()
select sysdate from dual;
select to_char(sysdate, 'YYYY-mm-dd hh:mm:ss') from dual;
select to_char(sysdate, 'YYYY-mm-dd hh24:mm:ss') from dual; --使用24小时制
select to_char(sysdate, 'YYYY') from dual;

select to_char(sysdate, 'd') from dual;  --一周中的第几天 周日开始
select to_char(sysdate, 'dd') from dual;  --一个月中的第几天
select to_char(sysdate, 'ddd') from dual;   --一年中的第几天

select to_char(sysdate, 'day') from dual;  --thursday 
select to_char(sysdate, 'dy') from dual;  --thu 星期的简写


-- 字符转日期
select to_date('2019-09-13','yyyy-mm-dd') from dual;

--查询1981年~1985年入职的员工信息
select * from emp where hiredate between to_date('1981','yyyy') and to_date('1985','yyyy');

通用函数 NVL

nvl(参数1，参数2) 如果参数1 = null 返回参数2
nvl2(参数1，参数2, 参数3) 如果参数1 = null 返回参数3 否则返回参数2

nullif(参数1，参数2) 如果参数1 = 参数2 那么就返回null 否则返回参数1

coalesce(参数1，参数2，参数3…) 返回第一个不为null的值

select nvl2(null,1,2) from dual; =2
select nvl2(6,1,2) from dual;  =1

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

select coalesce(null,null,3,5,6) from dual;

条件表达式

case 字段：
		when 值1 then 值
		when 值2 then 值
		else
			默认值
		end

case…when 通用的写法 mysql与Oracle都可使用

Oracle特有的写法：decode(字段,if1,then1,if2,then2,else1)

-- 给表中姓名取一个中文名
select 
	case ename
	when 'SMITH' then '柯南'
	when 'ALLEN' then '小兰'
	else
			'阿笠'
	end 
from emp;

-- 给表中姓名取一个中文名2
select decode(ename,'SMITH','柯南','ALLEN','小兰','SCOTT','黑衣人','路人') from emp;

分组表达式 group by

select 分组的条件，分组之后的条件 form 表名 group by 分组的条件 having 条件过滤

-- 分组统计所有部门的平均工资，找出平均工资大于2000的部门
select deptno,avg(sal) from emp group by deptno;
-- 过滤出大于2000的
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

	SQL的编写顺序:
			select  from  where group by having order by;
	SQL的执行顺序:
			from  where group by having select order by
	
	where 与 having 区别:
			where 后面不能接聚合函数，可以接单行函数
			having 是在group by之后执行，可以接聚合函数

?中秋快乐！