oracle 相关

Oracle安装配置

设置四个账户及对应的密码

No.	用户名	口令
1	sys	change_on_install
2	system	manager
3	scott	tiget
4	sh	sh

上面的口令(密码)是Oracle官方最早的初始密码，为了以后学习中不会忘记密码，我们进行统一的设置。

SqlPlus命令

在oracle中我们使用sqlplus命令来操作数据库。

首先在windows下打开cmd命令窗口。

示例：sqlplus命令使用

sqlplus回车

输入用户和口令

示例：切换到scott用户

conn scott/tiger

在scott用户下默认提供有一张emp雇员表，我们使用select语句查询一下。

select * from emp;

为了格式化显示我们使用一下命令：

set linesize 300 –表示设置行的尺寸

set pagesize 30 –表示设置每页显示多少记录

服务的配置

在win的服务中，把Oracle相关的服务自动设置为手动。

oracle服务的开启

监听服务OracleOraDb11g_home1TNSListener

实例服务OracleServiceORAC

开启监听和实例服务之后我们就可以正常使用sqlplus来操作oracle了。

基本的SQL语句示例（增删改查）

select * from emp;

select * from dept;

示例：查询所有雇员的雇员编号、雇员姓名、薪资、职位。

select empno,ename,sal,job

from emp;

示例：查询所有雇员的雇员编号、雇员姓名、年薪。

select empno,ename,sal*12

from emp;

示例：列出emp表中所有的部门编号（dis）。

select distinct deptno

from emp;

示例：列出emp表中所有的职位及对应的部门编号。

select distinct job,deptno

from emp;

切换用户、虚拟表dual

切换用户时，sys用户必须使用dba身份登录

conn sys/change_on_install as sysdba

conn system/manager

conn scott/tiger

conn sh/sh

虚拟表dual

now() –MySQL日期（函数）

sysdate –Oracle日期（伪列）

select sysdate from emp;（不用虚拟表，不方便）

select sysdate from dual;

接下来的内容要求有MySql基础。

练习题目：

题目1：查询每个部门的平均薪水，之后显示部门平均薪水>2000的部门编号和其平均薪水。

select avg(sal) from emp group by deptno;

select deptno,avg(sal) 

from emp 

group by deptno having avg(sal)>2000;

题目2：描述SQL语句：select deptno,avg(sal) from emp where sal>2000 group by deptno;

用文字描述，注意：薪资大于2000的雇员筛选出，对筛选出的数据再分组。

题目2扩展：查询在平均薪水大于2000的部门中，薪水大于三千的员工所在部门的部门编号，部门平均薪水。

select deptno,avg(sal）

from emp

group by deptno having avg(sal)>2000;

select deptno,avg(sal) 

from emp

where deptno in(select deptno

from emp

group by deptno having avg(sal)>2000) 

and sal >3000

group by deptno;

其实与以下语句是等价的

select deptno,avg(sal) 

from emp 

where sal>3000 

group by deptno having avg(sal)>2000;

题目3：查询每个部门的薪水和，再计算出所有部门的薪水的平均值。

select sum(sal)

from emp

group by deptno;

select deptno,sum(sal),avg(sal)

from emp

group by deptno;

单行函数

整个SQL的精髓：select语句+单行函数（背）。

函数形式：返回值 函数名(参数列表)

字符串函数

常用的处理字符串的函数有如下：

No.	函数名	含义
1	字符串 upper(列 | 字符串)	将传入的字符串全部转为大写
2	字符串 lower(列 | 字符串)	将传入的字符串全部转为小写
3	字符串 initcap(列 | 字符串)	将传入的字符串的首字母转为大写，其他字母全部转为小写
4	数字 length(列 | 字符串)	返回字符串的长度
5	字符串 substr(列 | 字符串,开始索引,[长度])	进行字符串截取，如果没有指定长度，则从开始索引截取的结尾
6	字符串 replace(列 | 字符串,旧内容,新内容)	对字符串的数据，以新的的子符内容替换旧的的子串内容

题目1：将字符串“WelcomeToZiBo”转换为大写。

select upper('welcomeToZiBo') from dual;

题目2：将emp表的雇员姓名全部转换为小写。

select lower(ename) from emp;

注意：Oracle数据库是区分大小写的。

SqlPlus命令接收用户输入语法：

select &input from dual;

注意：数字直接写，字符串单引号。

一般用户输入时不会写单引号，也不会考虑大小写问题。

select '&input' from dual;

select upper('&input') from dual;

题目3：由用户输入雇员姓名，然后显示雇员所有信息。

select * from emp where ename=upper('&in');

题目4：查询emp表所有的雇员姓名，返回的姓名首字母大写。

select initcap(ename) from emp;

题目5：查询雇员姓名长度为5的所有雇员的雇员编号、雇员姓名、职位。

select empno,ename,job from emp where length(ename)=5;

题目6：取得字符串'WelcomeToZiBo'的子串'ZiBo'。

select substr('welcomeToZiBo',10,4) from dual;

题目7：取得字符串'WelcomeToZiBo'的子串'come'。

select substr('welcomeToZiBo',4,4) from dual;

注意：Oracle的索引是从1开始的，及时设置了0也会从1开始。

题目8：取得emp表每位雇员的雇员姓名、雇员姓名的前两位。

select ename,substr(ename,1,2) from emp;

题目9：取得emp表每位雇员的雇员姓名、雇员姓名的后两位。

select ename,substr(ename,length(ename)-1,2) from emp;

题目10：取得emp表每位雇员的雇员姓名、雇员姓名的前两位+后两位作为密码。

select ename,substr(ename,1,2) || substr(ename,length(ename)-1,2) as password from emp;

注意：Oracle的函数有几十万个，不可能全部记住，但是此次的函数都是极度通用和高度归纳的，要求必须记下。

数值函数

常用的处理数值的函数有如下：

No.	函数名	含义
1	数字 round(列 | 数字[.小数位])	返回数据四舍五入后的值
2	数字 trunc(列 | 数字[.小数位])	数据截取，即不会进位
3	数字 mod(列 | 数字, 列 | 数字)	求余数

日期函数

之前用过一个伪列sysdate，还有一个时间戳systimestamp。

日期有三个操作公式：

日期 + 数字 = 日期（n天之后的日期）

日期 – 数字 = 日期（n天之前的日期）

日期 – 日期 = 数字（日期之间的天数）

示例1：显示三周前（21天）的日期。

select sysdate-21 from dual;

示例2：显示两周后（17天）的日期。

select sysdate+14 from dual;

示例3：查询每位雇员已经被雇佣的天数。

select sysdate-hiredate from emp;

select trunc(sysdate-hiredate) from emp;

示例4：查询每位雇员已经被雇佣的月份和年份。

select trunc(sysdate-hiredate)/30,trunc(sysdate-hiredate)/365 from emp;

使用天使实现年或月的计算一定是不准确的。Oracle提供有以下日期函数：

No.	函数名	含义
1	日期 add_months(列 | 日期,月数)	返回增加若干月之后的日期
2	数字 months_between(列 | 日期, 列 | 日期)	返回两个日期之间的月数
3	日期 last_day(列 | 日期)	返回日期所在月的最后一天
4	日期 next_day(列 | 日期,星期X)	返回日期在下周，星期X的日期

题目1：实现4个月之后是哪一天。

select add_months(sysdate,4) from dual;

题目2：查询emp所有雇员到现在为止的雇佣月数。

select empno,ename,trunc(months_between(sysdate,hiredate)) from emp;

题目3：查询当前所在月的最后一天。

select last_day(sysdate) from dual;

题目4：查询所有雇员到现在为止的雇佣年份。

方式1：（日期 - 日期 = 天数）/365    --（无法去除闰年）

方式2：months_between(sysdate,hiredate)/12

select empno,ename,trunc(trunc(months_between(sysdate,hiredate))/12) from emp;

题目5：查询出所有雇员到现在为止的雇佣了多少年、多少月、多少天（较难）。

转换函数

字符串、数值和日期三类数据之间是可以实现转换的。

No.	函数名	含义
1	字符串 to_char(列 | 日期,格式)	将日期或数字按格式转为字符串
2	日期 to_date(列 | 字符串,格式)	将字符串按格式转为日期
3	数字 to_number(列 | 字符串)	将字符串转为数字

to_char()

一、日期变为字符串，必须指定转换的格式。

日期：年yyyy月mm日dd

时间：时hh hh24分mi秒ss

数字：任意数字9，货币L

示例1：将日期显示格式化。

select to_char(sysdate,'yyyy-mm-dd') from dual;

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

示例2：查询所有在4月份雇佣的雇员。

select * from emp where to_char(hiredate,'mm')='04';

select * from emp where to_char(hiredate,'mm')=4; --自动转型

示例3：将数字格式化显示，使用货币格式化显示。

select to_char('4758475847583','9,9999,9999,9999') from dual;

select to_char('4758475847583','L9,999,999,999,999') from dual;

二、将字符串变为日期

示例4：将指定字符串按照格式转化为日期

select to_date('2017-11-21','yyyy-mm-dd') from dual;

三、将字符串转化为数字。

示例5：to_number()演示。

select to_number('12')+to_number('1') from dual;

select '12'+'1' from dual;

题目1：date char number之间经常进行转换，请描述下列SQL语句。

select to_date('2017-07-14','yyyy-MM-dd') from dual;

select to_date('2017-07-14','yyyy-MM-dd') as one_day from dual;

select to_char(sysdate,'yyyy-MM-dd') as today from dual;

select to_timestamp('2017-07-14 20:42:32.11','yyyy-MM-dd hh24:mi:ss:ff') as one_day from dual;

select to_char(systimestamp,'yyyy-MM-dd hh24:mi:ss:ff') as today from dual;

to_char(sysdate,'yyyy-MM-dd')

select to_number(replace(to_char(sysdate,'yyyy-MM-dd'),'-')) from dual;

select to_number(replace(to_char(sysdate,'yyyy-MM-dd'),'-')) as one_number from dual;

其他函数

有以下函数不便进行分类，开发之中会经常使用：

No.	函数名	含义
1	数字 nvl (列 | null, 默认值)	空值置换。如果传入的数据时null，则使用默认值，如果不是null，则使用原始数据。
2	数据类型 decode(列 | 字符串 | 数值,比较内容1,显示内容1, 比较内容2,显示内容2,…[,默认显示内容])	条件取值。设置的内容会与每一个比较内容进行比较，如果内容相同，则使用显示内容进行输出，如果都不行同，使用默认内容输出。

示例1：查询emp表所有雇员的雇员编号，姓名，薪资，佣金，年薪。

select empno,ename,sal,comm,(sal+nvl(comm,0))*12 from emp;

null与所有数据计算后还是null

示例2：将emp表中的职位（办事员、经理）置换为中文。

select empno,ename,job,decode(job,'CLERK','办事员','MANAGER','经理') from emp;

多表查询、分组统计查询、子查询、数据伪列、表及约束、数据库对象、触发器、游标、存储过程PL/SQL

这些知识的学习是开发和DBA都需要重点掌握的。

面试经常会被问到的问题：

在业界，大体可分为三类软件公司

1、互联网行业（电商、P2P、O2O、互联网金融）

2、传统领域软件行业（交通、电信、银行、电网）

3、产品软件行业（医疗、军工、OA、企业级管理系统、第三方软件需求）

数据库语言

数据操作语言DML(data manipulation language)->（select insert update delete merge）

数据定义语言DDL(data definition language)(create alter drop truncate)

事务控制语言TCL(transation control language)->（commit rollback savepoint）

数据控制语言DCL(data control language)->（grant revoke）

decode函数 VS case表达式

select empno,decode(deptno,10,'财务部',20,'研发部',30,'其他部门') from emp;

select empno,case when deptno=10 then '财务部' when deptno=20 then '研发部' else '其他部门' end from emp;

in和exists

“exists（xxx）”就表示括号里的语句能不能查出记录，它要查的记录是否存在。

in是全表扫描

exists是做是否存在，非全表扫描

示例1：查询属于领导（大小领导都包括）的员工。

select * from emp where empno in(select mgr from emp);

示例2：查询不存在员工的部门信息

select * from dept t1 where not exists(select * from emp t2 where t1.deptno=t2.deptno);

事务的开始和结束

事务特性：事务必须具备ACID四个属性。

原子性（Atomicity）事务是一个完整的操作，事务的各步操作是不可分的（原子的）；要么一起成功，要么一起失败。

一致性（Consistency）一个查询的结果必须与数据库在查询开始的状态一致（读不等待写，写不等待读）。

隔离性（Isolaton）对于其他会话来说，未完成的（未提交）事务必须不可见。

持久性（Durability）事务一旦提交完成，数据库就不可以丢失这个事务的结果，数据库通过日志能够保持事务的持久性。

事务采用隐式的方式，起始于session的第一条DML语句

查看事务

select * from v$transaction;

事务结束于

（1）commit提交或rollback回滚；

（2）DDL语句被执行（提交）；

（3）DCL语句被执行（提交）；

（4）用户退出SqlPlus（正常退出是提交，非正常退出时回滚）；

（5）机器故障或系统崩溃（回滚）；