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）机器故障或系统崩溃（回滚）；
