Oracle数据库

Oracle体系结构：

1. 数据库：按照数据结构组织，存储和管理数据的文件系统。
2. 数据库实例：通常情况下，Oracle只有一个数据库是orcl。
3. 表空间：逻辑单位
4. 数据文件：物理单位

mysql和Oracle区别：

1. 都遵循SQL标准。
2. 是不同厂商的，不同数据库产品，有自己的方言。
3. 一个项目：
   mysql中创建一个数据库，然后创建表。
   Oracle是创建一个表空间，然后创建用户，创建表。
4. mysql是多数据库的，Oracle是多用户的。
5. Oracle的安全性能好，mysql开源免费。

SQL语句分类

DDL，DML，DCL，DQL。

虚表与实表

select 1+1;
mysql可以得到结果为2，Oracle直接报没有找到from的错误

dual：是Oracle系统提供的一张虚表，主要用来补齐语法结构的，如写成select 1+1 from dual；在Oracle中可以得到结果2。

别名查询：as

使用as 关键字，可以省略
别名中不能有特殊字符或者关键字，如果有要使用双引号隔开。

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

去除重复:distinct

单列去除重复的数据：

select distinct job from emp;

多列去除重复数据：每一列都一样才算重复

select distinct ename,job from emp;

四则运算：+ - * /

年薪：
select sal*12 from emp;

null：是不确定的，不可预知的内容，不能做四则运算
函数：nvl(参数1，参数2)，如果参数1为null，该函数就将参数2返回。`

年收入：
select sal*12+comm from emp;
如果comm为null时，返回空。应该写成：
select sal+nvl(comm,0) from emp;

字符串拼接

||是Oracle特有的字符串拼接符；
concat(字符串1，字符串2)：在mysql和Oracle都适用

要求得到格式为：姓名：ename的列
写法1：select '姓名:'||ename from emp;
写法2：select concat('姓名：',ename) from emp;

单引号与双引号的区别：
单引号是使用的值，是字符，双引号用于取别名

条件查询where后的语句

关系运算符：> >= < <= = <> !=
逻辑运算符：and or not
其他运算符：like：_ % 模糊查询
between....and：在某个区间内
is not null：判断不为空
is null：判断为空
in (set)：范围查询

查询有奖金的员工信息：
select * from emp where comm is not null;
此处使用is not null（结果中会有等于0的员工信息）
如果使用>0的条件，就不会有包含0的结果了

模糊查询：

转义字符:关键字escape
查询名字包含%的员工：
select * from emp where ename like '%\%%' escape '\';
告诉Oracle \是一个转义字符。其中\也可以是其他任意字符
如：select * from emp where enam like '%#%%' escape '#';

排序：order by

asc：升序 ascend
desc:降序 descend
排序中的空值问题：空值在前在后是由asc和desc关键字决定的，如果还想控制其位置可以在order by后使用nulls来控制，nulls无默认值，写了就必须指定其值，可以取first（空值在前），last（空值在后）

按奖金排序（空值在后）：
select * from emp order by comm;
按奖金排序（升序但空值在前）：
select * from emp order by comm nulls first;

多条件排序：每个条件之间使用逗号隔开，条件的执行有先后顺序：先执行第一个条件，然后在第一个条件执行完成的基础上按照顺序依次执行其他条件。

select * from emp order by deptno desc,sal asc;

函数

多行函数：对某一列的多行进行处理。
有：count，avg，max，min，sum
其中count的参数可以为int类型数值，表示第几列。

单行函数：对某一行的某个值进行处理。
数值函数，字符函数，日期函数，转换函数，通用函数，

多行函数演示

多行函数会自动忽略空值。

求员工的奖金总和：
select sum(comm) from emp;
求员工的平均奖金：
select avg(comm) from emp;
这里求的结果是有奖金的员工的平均值，奖金为null的员工是不算的。
正确的写法：
select sum(comm)/count(*) from emp;
为了得到整数可以写成：
select floor(sum(comm)/count(*)) from emp;

数值函数

select ceil(45.926) from dual;   --45
select floor(45.926) from dual;   --46

四舍五入：
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,-3) 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,-3) from dual;  --0

求余：
select mod(9,3) from dual;   --0
select mod(9,2) from dual;   --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
如果起始索引为0或1都是从第一个字符开始截取的；
如果是2就从第2个字符截取，其他依次类推。
select substr('abcdefg',-1,3) from dual;  --g
select substr('abcdefg',-2,3) from dual;  --fg
如果起始索引为负数，则从后面开始数。

去除前后空格：
select trim('  abcd ') from dual;  --abcd
还有特殊用法参见函数手册。

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

替换字符串：
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 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()：

select 100+'10' from dual;默认就会转换
select 100+to_number('10') from dual;
结果一样

数值转字符to_char():
select to_char(1234.5,'$9999.99') from dual;--$1234.5
第二个参数的9为数字格式代表，也不可为其他数字；
如果用0代替9，表示如果没有有效位，就使用0填补。
逗号表示千分位。
第二个参数的$为货币符号，其中$代表美元，
L代表本地货币符号，与系统匹配。
select to_char(1234.5,'$9999.90') from dual;--$1234.50
select to_char(1234.5,'$9,999.9') from dual;--$123,4.5

日期转字符to_char()：
格式化显示当前时间
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
24小时制
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
只显示年：
select to_char(sysdate,'yyyy') from dual;
星期中的第几天，返回序号1-7
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;
星期的缩写：
select to_char(sysdate,'dy') from dual;

字符转日期to_date：
select to_date('1921-12-3','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，返回参数1；如果参数1为null返回参数2。
nvl2（参数1，参数2，参数3）：如果参数1为null，返回参数3，不为null返回参数2。
nullif（参数1，参数2）：如果参数1等于参数2，返回null，不等于返回参数1。
coalesce（可变参数）：返回第一个不为null的参数。

条件表达式

case 字段
    when 值1 then 值
    when 值2 then 值
    else 
     默认值
     end
case....when：这是通用写法MySQL和Oracle都可以。
例子： 给名字取别名
    select 
       case ename
          when 'SMITH' then '史密斯'
          when 'ALLEN' then '艾伦'
          when 'WARD'  then '沃德'
          else '路人甲'
          end "中文名" from emp;    

Oracle特有写法：

decode(字段,if1,值1,if2,值2，else)
给员工取别名：
select decode(ename,'SMITH','史密斯','ALLEN','艾伦','WARD','沃德','路人甲') from emp;

分组group by

格式：select 分组条件，分组后的操作 from 表名 group by 分组条件 having 条件过滤；
例子：分组统计所有部门的平均工资，找出平均工资大于2000的部门；
分组统计所有部门的平均工资：
select deptno,avg(sal) from emp group by dept;
找出平均工资大于2000的部门:
select deptno,avg(sal) from emp group by dept having avg(sal)>2000;
下面列出几种错误写法：
别名：
select deptno,avg(sal) deptnosal from emp group by dept having deptnosal>2000;
having 后的子句报错，此处不能使用别名。但可以写成：
select deptno,avg(sal) deptnosal from emp group by dept having avg（sal）>2000;

与分组查询无关字段：
select deptno,enamel，avg(sal) from emp group by dept having avg(sal)>2000;
enamel报错。

where和having的区别：where后可以接单行函数，但不能接聚合函数（多行函数）。having都可以。