Day57-Oracle01 - oracle体系结构、sql语句分类和结构、基本查询、函数入门

Oracle介绍

Orachle公司（甲骨文）提供的以分布式数据库为核心的一组软件产品；安全级别高。是目前最流行的客户/服务器（client/server）或B/S体系结构的数据库之一；
scott账户 – 提供给数据库学习者的，密码一般设置成为tiger
SYS账户拥有最高权限，密码可以设置为root
SYSTEM账户一般是开发人员用的，权限略少于SYS；

1）oracle数据库的体系结构：

a)数据库：database
         Oracle数据库就是数据的物理存储；Oracle数据库的概念和其他数据库的概念不一样，这里的数据库是一个操作系统就只有一个库，可以看做是Oracle就只有一个大数据库

b)实例：Oracle Instance
     一个oracle实例由一系列的后台进程和内存结构组成，一个数据库可以有n个实例，但是一般情况下就只有一个实例（ORCL）；

c)表空间：  -- 逻辑上的
     表空间是Oracle对物理数据库上相关数据文件的一个逻辑映射；一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构；每个数据库至少有一个表空间，称之为system表空间

d)数据文件：dbf  -- 物理单位
     数据文件是数据库的物理存储单位；数据库的数据是存储在表空间的，一旦数据文件被加入到某个表空间之后，就不能删除这个文件，如果要删除这个数据文件，就只能顾删除其所属的表空间才行；

e)用户：用户是在实例下面创建的；不同实例中可以建立相同名字的用户；
     表不是由表空间去查询的，而是由用户去查询的，因为不同用户可以在同一表空间建立同一名字的表，这里区分的就是用户了。

数据库 –> 实例 –> 表空间 –> 数据文件
可以把数据库看成是一个操作系统，把实例看成是之前我们理解的数据库，

2)Scott用户和HR用户
Oracle为了让学习者更好的学习，在安装成功之后，也就创建了初始的用户，其中SCOTT和HR就是初始的普通用户，这些用户鞋面默认都存在了表结构的。
SCOTT下面的表：DEPT部门表 、 EMP员工表 、 BONUS奖金表 、 SALGRADE工资级别表

hr下面的表：

---

sql语句的分类和语句结构

1）Sql语句的分类：

* DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库database、表table,列column等。关键字：创建create, 修改alter, 删除drop，truncate等（结构）
* DML（Data Manipulation Language）：数据操作语言，用来对数据库中表的记录进行更新。关键字：插入insert, 删除delete, 更新update等（数据）
* DQL（Data Query Language）：数据查询语言，用来查询数据库中表的记录。关键字：select, from ,where等。
* DCL（Data Control Language）：数据控制语言，用来定义数据库的访问权限和安全级别及创建用户;关键字：grant，revoke等

2）sql查询语句的结构：

select [列名,列名] [*] [聚合函数][distinct 字段] from 表名 [WHERE --> group by -->having--> order by]
注意是没有limit分页的，limit是属于mysql的方言

---

Oracle基本查询

oracle是区分大小写的

1）别名查询和虚表dual
oracle语言要求严格，dual是虚表、伪表，主要作用就是用来补齐语法结构的。

 select 1+1 from dual;

 别名需要用双引号引起来，（当然一般情况不引起来也是可以的,不加引号的时候别名中间不能够有特殊符号和空格，但是不能够是单引号，因为单引号的含义是字符串）

select 1+1 as "编%号" from dual;
select count(1) from emp；   -- 效率比count（*）要高

2）去除重复数据: distinct关键字,多列去重

select distinct deptno from emp;
select distinct deptno,job from emp ;

3）四则运算 – 引入nvl（p1,p2）
nvl( p1 , p2) 判断第一个参数是不是空，如果是，就返回第二参数

select nvl(null,2) as 结果 from dual;  -- 2
select ename,sal*12,sal*12+nvl(comm,0) as 所有 from emp;

4）字符串拼接
– concat（str1 ， str2） 这个函数只支持两个参数的字符串拼接，当然还可以函数嵌套函数
– || 拼接多个参数

select '英文名'||ename||'测试' from emp;
select concat('英文名',ename) from emp;  -- ename表示一列

5）条件查询

   条件查询: where 后面的写法
         逻辑运算: and or not
         关系运算: > >= = < <= != <>
               <> : 标准SQL
               != : SQLSERVER
         其它运算:
               is null
               is not null
               between .. and ..
               in(集合内)
               not in(集合)
               like
               exists 存在
               escape 定义转义字符
               order by  （asc/desc） nulls last

-- 查询每月能得到奖金的员工信息
select * from emp where comm <> 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,TURNER,FORD  -- oracle是区分大小写的，对字符串
select * from emp where ename in ('JONES','TURNER','FORD');
select * from emp where ename in ('JONES','TURNER','ford'); -- 这样是查不到FORD的

-- 查询名称中第三个字母是R的员工信息 --  _表示一个字母，%表示多个
select * from emp where ename like '__R%';

-- 查询名称中包含%的员工信息
select * from emp where ename like '%\%%'  -- 默认就会认为\是一个转义字符
select * from emp where ename like '%\%%' escape '\'  -- 完整写法是这样的
select * from emp where ename like '%A%%' escape 'A' -- 也可以把其他字符定义成为转义字符，只有'\'的时候可以不用写escape

-- 先按照部门升序排序,然后再按照工资进行降序排序
select * from emp order by deptno asc,sal desc
-- 按照奖金进行降序排序  -- 默认情况是nulls first，默认如果存在null，这一行放在前面
select * from emp order by comm desc nulls last

---

Oracle函数入门 – 函数都有一个返回值的

1）字符串函数substr , replace, length, trim
substr（参数1，参数2，参数3）
—- 参数1,：字符串，参数
– 参数2,：从第几个开始；从0和从1开始，都是一样的结果。默认从1开始
– 参数3,：截取多少个

select substr('fdafdafdafdafwfsaf',2,5) from dual;  

replace（字符串，旧字符串，新字符串）

select replace('fdafdafdafdafwfsaf','f','!') from dual;  --!da!da!da!da!w!sa!

length(字符串)

select length('fdafdafdafdafwfsaf') from dual

trim(字符串)

select trim('    ss   ') from dual;  --ss

2)数值函数：round,trunc,mod,ceil,floor
round（数字，保留位数） – 四舍五入

select round(44.44,0) from dual;  -- 44
select round(44.44,1) from dual;  -- 44.4
select round(44.44,3) from dual;  -- 44.44
select round(44.44,-1) from dual;  -- 40

floor(数字) – 向下取整

select floor(-13.1) from dual;  --14
select floor(12.4) from dual;   --12

ceil(数字) –向上取整

select ceil(-13.1) from dual;  -- -13
select ceil(12.4) from dual;   -- 13

trunc(数字，截断位数) – 截断

select trunc(13.1111,1) from dual;  -- 13.1
select trunc(13.1111,2) from dual;  -- 13.11

mod（被除数，除数） – 取模

select mod(9,2) from dual;  -- 1
select trunc(13,10) from dual;  -- 13

3）日期函数 – 日期是可以做加减运算的

查询系统时间
select sysdate from dual;

--计算员工入职天数
select ceil(sysdate - hiredate) as 入职天数 from emp;
--计算员工入职周数
select ceil((sysdate - hiredate)/7) as 入职周数 from emp;
--计算员工入职月数
select round(months_between(sysdate,hiredate)) as 入职月数 from emp;
--计算员工入职年数
select round(months_between(sysdate,hiredate)/12) as 入职年数 from emp;
--几个月后的今天
select sysdate+interval '6' month from dual;
select to_char(sysdate+interval '6' month,'yyyy-mm-dd') from dual;

4）转换函数

字符 - >数值：to_number (鸡肋，默认进行转换)
to_char(p1,'格式化字符串')
数值 -> 字符：to_char    -- 格式化数值    to_char(数字，数字格式)
日期 -> 字符 ：to_char（）     to_char（日期，日期格式）
字符 - > 日期 : to_date

to_number

select 100+'23' from dual  -- 123
select 100 + to_number('23') from dual  -- 123

格式化数字格式： – 一般只有to_char才能够写格式

select to_char(22222222,'$999,999,999.00') from dual;  --  $22,222,222.00
select to_char(22222222,'L999,999,999.00') from dual;  -- L表示当前系统语言的币种

格式化日期格式 – 一般只有to_char才能够写格式

select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy-mm-dd,hh:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;  --24小时制

其他转换：

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,'ww') from dual;  -- 每年的第几周
select to_char(sysdate,'mm') from dual;  -- 每年的第几月
select to_char(sysdate,'yy') from dual;  -- 本世纪的第几年

to_date

select to_date('2016-7-15','yyyy-mm-dd') from dual;

5)通用函数 nvl nvl2

nvl（字段，返回值）   -- 如果字段不为空，返回字段，如果为空，返回后面的返回值
nvl2(字段，返回值1，返回值2)   -- 如果字段不为空，返回返回值1，如果为空，返回返回值2

select empno,nvl(comm,0) from emp;
select nvl2(null,1,2) from dual;  -- 2
select nvl2(1,1,2) from dual;   -- 1

6）条件表达式 –！！！！重要

a:通用方式：

select case 列
       when 值1 THEN 新值
       when 值2 THEN 新值
       else
             其他值
       end
       from 表名;

select case ename
       when 'SMITH' then '史密斯'
       when 'ALLEN' then '艾伦'
       when 'WARD'  then '伍德'
       else
          '路人甲'
       end
       from emp;

b:oracle特有方式

decode(字段,'if1','then1','if2','then2','else')

select decode(ename,'SIMTH','史密斯','ALLEN','艾伦','WARD','伍德','路人甲') from emp;

7)多行函数 – sum() avg() count() max() min()

-- 查询平均工资大于2000的部门
select deptno,avg(SAL) from emp group by deptno having avg(sal)>2000;
-- 分组统计每个部门的工资总和
select deptno,sum(sal) from emp group by deptno;