Oracle历程第1天-基础中的基础

该笔记适合有Mysql使用基础的童鞋观摩，有高见请在下方评论

一 Mysql和Oracle适用范围

• MYSQL
  开源免费的数据库，多用于中小型企业
• ORACLE
  收费的数据库，多见于在国企，金融，银行，证券，保险 行业

二 安装Oracle

参考博文：https://blog.youkuaiyun.com/pancheng1984/article/details/79823467
Oracle11g文件下载:（提取码：lbwn）
https://pan.baidu.com/s/1RtyoTOZOJnr-zSBG-TGYyA

略

三 练习常用SQL语句

使用PLSQL可视化工具，请自行安装
练习时登陆SCOTT用户，密码一般为tiger

1. 认识EMP表

EMP表： employee 员工表
no代表英语中的number
EMPNO字段：employee number 员工编号
ENAME字段： employee name 员工姓名
JOB字段： 员工职位
MGR字段：manager 所属上级的编号
HIREDATE字段： hiredate 入职时间
SAL字段： salary 工资
COMM字段：奖金
DEPTNO字段： department number 部门编号

select * from EMP;

2. dual虚表

dual虚表，伪表。用于补齐语法结构

select 1+1 from DUAL; --四则运算
select sysdate from DUAL; --查看当前系统时间

3. 起别名

双引号用于起别名
单引号用于使用字符串的值

select sysdate "当前时间" from dual;
select '字符串的值' "别名" from dual;

4. distinct 去重

-- 单列去重
select distinct JOB "职业名称" from EMP;
-- 多列去重
select distinct JOB "职业名称",DEPTNO "部门编号" from EMP;

5. 字符串拼接

字符串拼接,使用||作为连接符

select ENAME||'先生' "拼接内容" from EMP;
-- 通用写法，oracle,mysql均可用
select concat(ENAME,'先生') "拼接内容" from EMP; 

6. 常用数值函数

-- 取整数
select ceil(45.26) from dual; --向上取整
select floor(45.26) from dual; --向下取整

-- 四舍五入
select round(45.26,1) from dual; -- 保留1位小数  45.3
select round(45.26,0) from dual; -- 保留0位小数  45
select round(45.26,-1) from dual; -- 保留个位    50
select round(45.26,-2) from dual; -- 保留十位    0

-- 截断  truncation
select trunc(45.26,1) from dual;  --  45.2
select trunc(45.26,0) from dual;  --  45
select trunc(45.26,-1) from dual; --  40
select trunc(45.26,-2) from dual; --  0

-- 取余  取 模运算
select mod(10,4) from dual;

7. 常用字符函数

-- 起始索引0和1都是从首个字符开始截取
select substr('oracle',0,3) from dual; -- ora
select substr('oracle',1,3) from dual; -- ora
select substr('oracle',2,3) from dual; -- rac

-- 字符串长度
select length('oracle') from dual;

-- 去除左右两边的空格
select length('oracle 11g') from dual;
select length('  oracle 11g  ') from dual;
select trim('  oracle 11g  ') from dual;
select length(trim('  oracle 11g  ')) from dual;

-- 替换字符串
select replace('oracle 11g','11g','10g')from dual;

8. 常用日期函数

-- 查询今天的日期
select sysdate from dual;
-- 查询3天后的日期
select sysdate+3 from dual;
-- 查询3个月后的今天的日期
select add_months(sysdate,3) from dual;
-- 查询员工入职天数
select sysdate-HIREDATE from EMP;
-- 查询员工入职周数
select (sysdate-HIREDATE)/7 from EMP;
-- 查询员工入职月数  月的天数是不固定的，months_between
select months_between(sysdate,HIREDATE) from EMP;
-- 查询员工入职年份  年的天数是不固定
select months_between(sysdate,HIREDATE)/12 from EMP;

9. 常用转换函数

字符与数值互转，字符与日期互转

-- 1.字符 ---> 数值
select 100+'10' from dual; -- 自动转换
select 100+to_number('10') from dual; --手动转换

-- 2.数值 ---> 字符
select to_char(SAL,'$9,999.99') from EMP; 
select to_char(SAL,'L9,999.99') from EMP; -- L为当地货币，根据系统来判定

-- 3.日期 ---> 字符
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,'yyyy') from dual; -- -- 获取年
select to_char(sysdate,'d') from dual; -- 一个星期中的第几天，从周日开始，起始为1
select to_char(sysdate,'dd') from dual;-- 一个月中的第几天
select to_char(sysdate,'ddd') from dual;-- 一年中的第几天
select to_char(sysdate,'day') from dual;  -- 显示星期

-- 4.字符 ---> 日期
select to_date('2018-08-08','yyyy-mm-dd') from dual;
select * from EMP
where HIREDATE between to_date('1981-01-01','yyyy-mm-dd') and to_date('1985-12-31','yyyy:mm:dd');  -- 查询1981年到1985年入职的员工信息

11. 通用函数

-- nvl(a,b)     :若a为null,则返回b,否则返回a
select nvl(null,'b') from dual;  -- b
select nvl('a','b') from dual;   -- a

-- nvl2(a,b,c)  :若a为null，则返回c，否则返回b
select nvl2(null,'b','c') from dual;  -- c
select nvl2(1,'b','c') from dual;     -- b

-- nullif(a,b)  : 若a=b,则返回null，否则返回a
select nullif(999,999) from dual; -- null
select nullif(999,998) from dual; -- 999

-- coalesce(a,b,c,...):    返回参数中第一个不为null的值
select coalesce(null,null,3,5,6) from dual;  -- 3

12. 条件表达式

/*
  通用写法：
       case 字段：
         when 值1 then 值
         when 值2 then 值
         else 默认值
       end
       
   ORACLE特有写法
   decode(字段,值1,值,值2,值,默认值)
*/
-- 给表中的英文名取个中文名
select 
   case ENAME
     when 'SMITH' then '刘备'
     when 'ALLEN' then '关羽'
     when 'WARD' then '张飞'
     else '小卒'
    end "中文名"
from EMP;


select decode(ENAME,'SMITH','刘备','ALLEN','关羽','WARD','张飞','小卒') "中文名" from EMP;

13. 实战SQL语句

1. 查询员工年薪

null值： 不确定，不可预知的内容，不能做四则运算
在有null值的数据中，若要做四则运算，一般使用nvl函数
nvl(a,b) 函数： 若a为null,则返回b值，否则返回a值

-- EMP表中只有月薪和奖金，故年薪=月薪*12+奖金
-- nvl(comm,0):若奖金comm为null，则返回0，否则返回奖金comm
select SAL*12+nvl(COMM,0) "年薪" from EMP;

2. 查询每月能拿到奖金的员工信息

select * from EMP where COMM is not null;

3. 查询工资在[1500,3000] 的员工信息

-- 方法1
select * from EMP where SAL between 1500 and 3000;
-- 方法2
select * from EMP where SAL >= 1500 and SAL <= 3000;

4. 查询员工 SCOTT, FORD, ALLEN 的信息

-- 这里in关键字后接集合
select * from EMP where ENAME in('SCOTT','FORD','ALLEN');

5. 查询员工姓名以TT结尾的员工信息

select * from EMP where ENAME like '%TT';

6. 查询员工姓名第二个字符为L的员工信息

select * from EMP where ENAME like '_L%'

7.查询员工姓名含有%的员工

select * from EMP where ENAME like '%#%%' escape '#'; -- escape指定转义符

8. 员工信息按照奖金从高到低排序

select * from EMP order by COMM desc nulls last;

9. 查询部门编号和工资，部门编号升序处理，工资降序处理

select DEPTNO "部门编号",SAL "工资" from EMP
order by DEPTNO asc,SAL desc;

10. 统计员工工资总和

select sum(SAL) "工资总和" from EMP;

11. 统计员工奖金总和 计算结果：2200

select sum(COMM) "奖金总和" from EMP;

12. 统计员工人数 计算结果：14

select count(1) "总人数" from EMP;

13. 统计员工的平均奖金

select avg(COMM) from EMP; --错误写法，由于空值导致的问题
select sum(COMM)/count(1) "平均奖金" from EMP; -- 正确写法

14. 分组统计所有部门的平均工资，找出平均工资大于2000的部门

/*
分组表达式 group by

  select 分组条件,分组后的操作 from 表名 group by 分组条件 having 条件过滤
  
  
  where 和 having 的区别：
        where后不能接聚合函数，可以接单行函数
        having 是在group by后执行的，可以接聚合函数
*/
select DEPTNO,avg(SAL)from EMP group by DEPTNO having avg(SAL)>2000