数据库呀,想了想,到数据库了,一开始学的第一个数据库就是oracle,学sql语句,虽然后面也接触了pl,但感觉还是建立在sql上面学的。
oracle安装emmm都到这了,应该开源软件的安装没什么问题的吧。
官方的下载地址: www.oracle.com/technetwork…
下载安装,网上随便就能找到个教程。
1、数据库简介
1.1、Oracle公司
甲骨文软件系统有限公司,目前是全球第2大软件公司(第1:微软),Oracle公司在数据库市场,毫无争议第1
1977 :软件开发实验室”(Software Development Labs);
1978 :关系式软件公司”(RSI)
1982 :甲骨文软件系统有限公司(Oracle Systems Corporation)
Oracle合伙人:Ed Oates,Bob Miner,Larry Ellison;**Bruce Scott **是Oracle雇佣的第1个员工
1.1、Oracle数据库客户端
启动OracleServiceOrcl —– sqlplus (1)
启动OracleOraDb11g_home1TNSListener: sqldeveloper(2)
启动OracleDBConsoleorcl:web管理客户端(3)
https://localhost:1158/em
小结:安装Oracle数据库,产生7个服务
OracleServiceOrcl :必须启动
OracleOraDb11g_home1TNSListener:可以不启动(使用远程连接访问Oracle数据库需要启动)
OracleDBConsoleorcl:可以不启动(web管理需要启动)
OracleJobSchedulerORCL :可以不启动(作业调度)
OracleMTSRecoveryService | OracleOraDb11g_home1ClrAgent :可以不启动 微软技术相关需要启动的服务
Oracle ORCL VSS Writer Service:可以不启动 (卷映射拷贝写入服务)
Oracle数据库端口:1521 | MySql数据库端口:3306 | SQL Server端口:1433
1.2、SqlPlus设置
sqlplus是Oracle命令行客户端程序
启动sqlplus 并登录:sqlplust scott/tiger
show user :显示当前登录用户
select * from tab;查看当前用户下有哪些表资源(默认看到4张表 --EMP|DEPT|SALGRADE|BONUS)
set pagesize 40 设置每页显示的条数为40(大小可以自己定)
set linesize 300 设置每行显示的字符数300(大小自己定)
quit:退出sqlplus
1.3、账号管理基础
如果忘记sys账号的密码,怎么登录
sqlplus
sys/nolog as sysdba :不需要密码直接进入系统;
alter user sys identified by orcl :修改密码;
conn scott/tiger 切换账号
alter user scott account lock; 给scott账号锁定
alter user scott account unlock:给scott解锁账号
1.4、数据库系统
一个完整的数据库系统由下面几个部分构成
硬件系统
操作系统
数据库管理系统(Oralce带管理系统)
数据库(数据仓库:存数据) — 文件(DBF — 数据文件,CTL(控制文件),LOG(日志文件));
数据库管理员(DBA)
1.5、数据库发展历史
内存:不能持久化
文件:存数据
层次|网状数据库:可以存贮 ,可以维护、统计、分析
**关系型数据库:**数据以表为单位保存
1.6、数据库产品
大型数据库:甲骨文的Oracle数据库 | Sysbase公司的Sysbase数据库|IBM公司的DB2数据库
中型数据库:MySQL数据库(开源) | 微软 SQL Server
小型数据库:Access数据库(MS) — ASP(.NET) | Sqlite(嵌入式数据库 –一个数据库文件,不需要安装任何服务)
Oracle数据库有很多对象(表、视图、索引、程序包、过程、函数……….),数据是存贮在表这个对象中;
表结构:行(数据)、列(表结构);表的每一行称为记录(实体)
Oracle数据库的几个关键版本:i(版本oracle8i,oracle9i)、g版本(oracle10g,oralce11g),c版本(oracle12c)
2、SCOTT账号下的表对象
select * from tab #查看 scott账号下所有的表对象(4个)
DEPT #部门表
EMP #员工表
SALGRADE #工资等级表
BONUS #奖金表(没数据)
DESC 表名 #显示表结构
DEPT #部门表
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
EMP #雇员员
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SALGRADE #工资等级表
GRADE NUMBER
LOSAL NUMBER
HISAL NUMBER
BONUS #奖金表
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
SAL NUMBER
COMM NUMBER
3、SQL
SQL:Structure Query Language(结构化查询语言) 是所有关系型数据库都遵守的语言,为了满足各数据库特定业务需要,每一个数据库厂商在尊守SQL的基础上,对SQL语法做了扩展(此部分每个数据库是不一样),Oracle数据库对SQL进行了扩展,扩展的语法称为:PL/SQL,SQLServer数据库扩展的语法称为:T-SQL
SQL分为以下种:DQL | DML | DDL|DCL|TCL
DQL :Data Query Language 数据查询语言; —– SELECT 【重点|难点】
DML : Data Manipulation Language 数据操作语言; —– INSERT | UPDATE | DELETE
DDL:Data Definition Language 数据定义语言 — CREATE | ALTER | DROP
DCL: Data Control Language 数据控制语言 — GRANT | INVOKE
TCL:Transaction Control Language 事务控制语言 — COMMIT | ROLLBACK | SAVEPOINT
面试题:写SQL语句题(以查询为主)
3.1 基本 SELECT 语句
基本 SELECT 语句
SELECT
*|{[DISTINCT] column|expression [alias],...}
FROM table;
#显示emp表所有属性数据
SELECT * FROM emp;(SELECT EMPNO,ENAME, JOB,MGR ,HIREDATE,SAL,COMM, DEPTNO FROM emp;)
#显示emp表的EMPNO,ENAME,JOB,HIREDATE,SAL
SELECT empno,ename,job,hiredate,sal FROM emp;
#显示emp表部门编号
SELECT DISTINCT deptno FROM emp;
#表达式:查询语句中可以包含表达式(主要是算述表达式)
#显示emp表员工的编号,姓名,职务、薪水,及加200后的薪水;
SELECT empno,ename,job,sal, sal+200 FROM emp;
#显示emp表员工的编号,姓名,职务、本月工资
SELECT empno,ename,job,sal+NVL(comm,0) FROM emp;
#显示emp表员工的编号,姓名,职务、本月工资 并取别名工资
SELECT empno,ename,job,sal+NVL(comm,0) monthsal FROM emp;
SELECT empno,ename,job,sal+NVL(comm,0) AS monthsal FROM emp;
3.2 条件过滤
SELECT
*|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)]
#查询员工编号为7369的员工 #查询部门编号为10的员工;
SELECT * FROM emp WHERE empno=‘7369’
要求:设置条件时,属性的值建议用单引号括起来(不管什么类型的数据)
#条件查询:比较运算符(=,> >= < <= <>|!=)
#查询薪水大于等于2000的所有员工
ELECT * FROM emp WHERE sal>=2000;
#BETWEEN ... AND
#查询工资在2000 到3000之间的所有员工
SELECT * FROM emp WHERE sal BETWEEN 2000 AND 3000;
SELECT * FROM emp WHERE sal >=2000 AND sal<=3000;
#IN (适合属性的值为固定的某些值) | NOT IN
#查询EMP表中职务为:CLERK,SALESMAN的所有员工
SELECT * FROM emp WHERE JOB IN('CLERK','SALESMAN');
SELECT * FROM emp WHERE JOB NOT IN('CLERK','SALESMAN','DEVELOPER');
#LIKE:实现模糊查询,配合模糊查询的两个通配符:%:0个或者任意多个字符 _:任意的单个字符
#查询EMP表中姓名中有A字符的所有员工;
SELECT * FROM emp WHERE ename LIKE '%A%';
#查询EMP表中job以A开始的所有员工;
SELECT * FROM emp WHERE job LIKE 'A%';
#查询EMP表中姓名第2个字符为A的员工?
SELECT * FROM emp WHERE ename like '_A%';
#IS NULL | IS NOT NULL
#查询佣金为空的所有员工
SELECT * FROM emp WHERE comm IS NULL;
#多条件:需要使用逻辑运算符:NOT AND OR
#查询部门编号为30并且工资大于2500的员工;
SELECT * FROM emp WHERE deptno=30 AND sal > 2500
#查询部门编号为30并且工资大于2500的员工以及部门编号为20工资小于1500;
SELECT * FROM emp WHERE (deptno=30 AND sal > 2500) OR (deptno=20 AND sal < 1500)
3.3 排序
SELECT
*|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)]
[ORDER BY {column, expr, alias} [ASC|DESC]];
#对EMP表,按工资升序排序;
SELECT * FROM emp ORDER BY sal ASC;
默认为升序(如果是升序,可以省略 ASC)
#查询部门为30的员工,按工资降序排序;
SELECT * FROM emp
WHERE deptno=30
ORDER BY sal DESC
#排序可以设置多个排序属性(列)
SELECT * FROM emp ORDER BY sal ASC,empno DESC;
SELECT
FROM
WHERE
ORDER BY
4、Oracle单行函数
函数是Oracle数据库中一个对象,函数分为内置函数(单行函数),自定义函数;函数的主要功能是用来执行运算的; Oracle中的单行函数:字符函数,数值函数,日期函数,转换函数,通用函数
dual:oracle数据库中的一张虚拟表,主要用来做函数与表达式测试的;
4.1 字符函数
#常见的字符函数:lower upper initcap
SELECT LOWER('HELLO'),UPPER('hello'),INITCAP('hello,world') FROM dual;
SELECT LOWER(ename),UPPER(ename),INITCAP(ename) FROM emp;
#常见的字符函数:CONCAT SUBSTR LENGTH LPAD | RPAD TRIM REPLACE INSTR
#CONCAT(字符串1,字符串2),
#SUBSTR(字符串,start,len)
#LPAD(字符串,len ,'补全的字符') | RPAD(字符串,len ,'补全的字符')
#TRIM:去左右空格(中间去不掉)
#REPLACE(替换的字符串,'oldchar','newchar')
#INSTR(字符串,'字符')
4.2 数值函数
数值运算的几个常用函数:round trunc ,mod
SELECT ROUND(12.1256,2),ROUND(12.1256,1) ,ROUND(12.1256)FROM dual;
SELECT TRUNC(12.123) ,TRUNC(12.125,2) FROM dual;#可以保留小数,但不会做四舍五入处理;
SELECT mod(10,3) FROM dual;
4.3 日期函数
sysdate可以获取当前的日期与时间,oracle数据库默认的日期格式为:日-月-年,如果需要按按中文的格式显示需要用到转换函数:to_char;
SELECT sysdate from dual;
yyyy || yy :年份
MM|mm :月分
dd |d :日
HH24|hh12 :时
MI|mi :分
SS|ss : 秒
oracle中的日期函数:months_between |add_months|next_day|last_day; 需要理解日期运算
日期 – 日期 = 天数
日期 + 数字(天数) = 日期
日期 – 数字(天数 )= 日期
#显示每一个雇员雇佣月分,天数
select
ename,
to_char(hiredate,'yyyy-MM-dd'),
trunc(months_between(sysdate,hiredate)) ,
trunc(sysdate-hiredate)
from emp;
#5个月后的今天
select
to_char(sysdate,'yyyy-MM-dd'),
to_char(add_months(sysdate,5),'yyyy-MM-dd')
from dual;
#next_day:求某一日期的下一个日期
select sysdate,next_day(sysdate,'星期四') ,next_day(sysdate,4) from dual;
next_day(curdate,'format')format的值可以为:[1-7]或者[星期一,星期日]
#5月的最后一天
select last_day(sysdate) from dual;
SELECT * FROM emp WHERE hiredate = last_day(hiredate) ;
算出每一个雇员 雇佣了多少年,多少月,多少天
select
ename,
to_char(hiredate,'yyyy-MM-dd') 雇佣日期,
trunc(months_between(sysdate,hiredate) /12) 年 ,
trunc(mod(months_between(sysdate,hiredate),12)) 月,
sysdate - add_months(hiredate, (months_between(sysdate,hiredate))) 天
from emp;
4.4 转换函数
#oracle中常用的转换函数:to_char,to_number,to_date
to_date(char,'format') :将一个字符串格式的日期转成一个日期类型
select to_date('2012-05-22 20:49:10','yyyy-mm-ss hh24:mi:ss') from dual;
4.5 空值处理函数
NVL(列|表达式,value) | 空值处理函数 |
---|---|
NVL2(列|表达式,value1,value2) | 表达式不为空,返加value1,否则返回value2 |
COALESCE(exp1,exp2,…value) | 表达式1为空,判断列|表达式2,…,value |
#空值处理函数:NVL | NVL2 |
#计算年终奖:佣金不为空,年终奖为基本工资的1.5倍,佣金为空,年终奖为基本工资1.2倍
select ename, nvl2(comm,sal*1.5,sal*1.2) from emp;
#判断佣金,如果佣金为空,显示此雇员上司,如果此雇员没有上司,则显示"老板"
SELECT COALESCE(TO_CHAR(comm),TO_CHAR(mgr),'老板') FROM emp;
4.6 条件表达式
条件表达式:CASE表达式、DECODE表达式,此2个表达式功能类似;
CASE expr
WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
select
empno,ename,job,sal,
case deptno
when 10 then 'ACCOUNTING'
when 20 then 'RESEARCH'
when 30 then 'SALES'
when 40 then 'OPERATIONS'
else '没有此部门'
end 部门名称
from emp;
select
empno,ename,job,sal,
decode(deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPERATIONS','没有此部门')
部门名称
from emp;
5、多表查询【这是个重点】
当需要同时取出多张表的数据时,就需要联合多表来查询,多表查询会产生笛卡尔乘积会产生的记录数:N1*N2,由此可知,多表查询效率是很底的(如果单表查询能解决,尽量不要用多表查询)
多表联合查询条件设置: 根据两张表的关联属性设条件(emp表中有一个属性deptno — 外键,dept表中有一个属性deptno — 主键) —往往是一个表的主键在另一张表中充当外键;
也可以根据两张表的间接属性设置条件(上下文语义匹配)
5.1 等值查询
#查询雇员表中员工编号,姓名,职务,工资,及雇员的部门编号,部门名称,部门地址
select
emp.empno ,emp.ename,emp.job,emp.sal,dept.deptno,dept.dname,dept.loc
from emp ,dept
where emp.deptno=dept.deptno;
#命名用别名
select
e.empno ,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno;
#查询雇员表中员工编号,姓名,职务,工资,及雇员的部门名称,部门地址及雇员的工资等级;
select
e.empno,e.ename,e.job,e.sal,d.dname,d.loc,s.grade
from emp e,dept d,salgrade s
where e.deptno=d.deptno and e.sal between s.losal and s.hisal
#查询雇员表中员工编号,姓名,职务,工资,及雇员的部门名称,部门地址及雇员的工资等级;
#grade为1:显示 一级, 2 :二级 3 :三级 4 四级 5 五级
select
e.empno,e.ename,e.job,e.sal,d.dname,d.loc,
case s.grade
when 1 then '实习工资'
when 2 then '初级工资'
when 3 then '中级工资'
when 4 then '高级工资'
when 5 then '架构师级工资'
end 等级
from emp e,dept d,salgrade s
where e.deptno=d.deptno and e.sal between s.losal and s.hisal;
#查询雇员表中员工编号,姓名,职务,工资,雇员的部门名称,部门地址,雇员的工资等级以及雇员的上司;
select
e1.empno,e1.ename,e2.ename 上司,e1.job,e1.sal,d.dname,d.loc,s.grade
from emp e1,dept d,salgrade s,emp e2
where
e1.deptno=d.deptno
and e1.sal between s.losal and s.hisal
and e1.mgr=e2.empno
5.2 连接查询
连接查询是多表查询实现方式之一,oralce 数据库中的连接查询分为:外连接(outer join)和内连接(inner join)
内连接:等效于等值查询(完全一样)
#查询雇员表中员工编号,姓名,职务,工资,及雇员的部门编号,部门名称,部门地址
select
e.empno ,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc
from emp e inner join dept d on e.deptno=d.deptno;
#查询雇员表中员工编号,姓名,职务,工资,及雇员的部门名称,部门地址及雇员的工资等级;
select
e.empno ,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc,s.grade
from emp e
inner join dept d on e.deptno=e.deptno
inner join salgrade s on e.sal between s.losal and s.hisal;
#查询雇员表中员工编号,姓名,职务,工资,雇员的部门名称,部门地址,雇员的工资等级以及雇员的上司;
select
e1.empno ,e1.ename,e2.ename 上司,e1.job,e1.sal,d.deptno,d.dname,d.loc,s.grade
from emp e1
inner join dept d on d.deptno=e1.deptno
inner join salgrade s on e1.sal between s.losal and s.hisal
inner join emp e2 on e1.mgr=e2.empno;
外连接: 分为左向外连接、右向外连接、完全外连接
左连接:显示左侧表所有数据及右则表满足条件的数据(去笛卡尔乘积)
右连接:显示右侧表所有数据及左则表满足条件的数据(去笛卡尔乘积)
左连接与右连接是可以转换的(只是表的位置不一样而以)
#显示所有的部门以及部门的所有员工(左接接实现的)
select
d.deptno,d.dname,d.loc,e.empno,e.ename,e.job,e.sal
from dept d left outer join emp e on d.deptno=e.deptno;
#显示所有的部门以及部门的所有员工(右接接实现的)
select
d.deptno,d.dname,d.loc,e.empno,e.ename,e.job,e.sal
from emp e right outer join dept d on e.deptno=d.deptno;
完全外连接:显示左侧、右侧表所有数据(去笛卡尔乘积)
#显示emp表所有数据及部门表所有数据(去笛卡尔乘积)
select
d.deptno,d.dname,d.loc,e.empno,e.ename,e.job,e.sal
from emp e full outer join dept d on e.deptno=d.deptno;
5.3 自然连接
等值查询或者连接查询语法的简化版,当关联的属性名称在2张表中相同时才可以使用;
查询雇员表中的姓名、职务、薪水以及雇员所属的部门名称及部门地址 ----(deptno)
SELECT
e.ename,e.job,e.sal,d.dname,d.loc
FROM emp e natural join dept d
5.4 using子句
using子句 :两张表有相同的关联属性(deptno),可以直接使用using简化条件设置
查询雇员表中的姓名、职务、薪水以及雇员所属的部门名称及部门地址 ----(deptno)
SELECT
e.ename,e.job,e.sal,d.dname,d.loc
FROM emp e join dept d using(deptno)
6、子查询
子查询:一个查询内部包含的查询称为子查询(外部的查询称为主查询),子查询的结果往往是外部查询(主查询)的条件,所以子查询要先运行; 子查询需要用括号括起来; —子查询的结果是单列的
单行子查询:子查询的结果就一个值
#查询比FORD工资高的所有员工
select * from emp where sal > select sal from emp where ename='FORD'
#查询与FORD同一部门的所有员工
select * from emp where deptno = (select deptno from emp where ename='FORD')
#小结:单行子查询 支持的比较运算符:>,>= <,<= = <>
多行子查询:子查询的结果有多个值; 过滤条件时需要结合谓词:ALL 、ANY,IN(SOME),不能再用:= >,>=,< <= <>
#查询比部门20所有员工 工资都高的所有员工
select * from emp where sal > all ( select sal from emp where deptno=20)
#查询工资高于部门20其中一个员工工资的所有员工
select * from emp where sal > any ( select sal from emp where deptno=20)
IN子查询询
查询部门编号为:10,20的员工
select * from emp where deptno in (10,20) #查询与部门编号为20的雇员工资相同的员工;
select * from emp where sal in (select sal from emp where deptno=20)
oracle数据库分页问题 — 子查询
Oracle数据库表的分页展示,要求表中有一个具有连续行号的一个属性,此属性Oracle直接提供,通过ROWNUM得到行号(ROWNUM生成的列称为伪例);Oracle数据库的分页需要定一个三层嵌套的SQL
第一层SQL:解决排序的
第二层SQL:解决生成伪例(产生一个连续的行号–分页需要)
第三层SQL:解决获取某一页数据
分页:数据库得到2个参数:pageindex=1,pagesize=4
select * from
(select e.*,rownum rn from
(select * from emp order by empno asc) e ) #得到一个结果集
where rn between startposition and endposition
startposition:(pageindex-1)* pagesize + 1
endposition : pagesize * pageindex
以上可以实现Oracle数据库表数据分页,但运行时,效率低一些; --上面SQL优化后
SELECT * FROM
(SELECT e1.*,rownum rn FROM
(SELECT * FROM emp order by empno asc) e1 WHERE rownum <=endposition)
WHERE RN >=startposition
startposition:(pageindex-1)* pagesize + 1
endposition : pagesize * pageindex
select * from emp limit startposition,endposition :mysql分页
分页显示数据,必须向数据库传递2个参数:pageindex(当前页), pagesize(每页大小)
表:总记录确定 / pagesize —> 总页数 —— pageindex —> 得到当前页数据
pageindex=1,pagesize =4
查询表中第31 到40 之间的数据(users)
select * from
(select e.*,rownum rn from
(select * from users) e)
where rn between 31 and 40
7、分组查询
分组查询:得到的是以组为单位的数据,分组查询是为统计服务的,分组首先需要知道基于组进行统计的组函数(聚合函数)
Oracle 数据库常用的几个分组用的组函数:sum ,avg,max,min,count
select
sum(sal+nvl(comm,0)) 每月分的总工资,
avg(sal+nvl(comm,0)) 平均工资,
max(sal + nvl(comm,0)) 最高工资 ,
min(sal+nvl(comm,0)) 最低工资,
count(*) 总人数
from emp;
count(*),count(1),count(列) 区别
分组总是和组函数结合在一起的(不分家),分组查询得到是以组为单位的统计结果;按什么进行分组,分组查询能显示的结果:分组的列,组函数
经验:按表的哪一个属性进行分组(分组的列往往是有重复数据的列:部门|职务|性别)
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
HAVING group_condition] -- 对分组后的数据进行过滤,用having
[ORDER BY column];
#查询emp表数据,按部门进行分组,统计每个部门每月发放的总工资,最高工资,最低工资,平均工资,总人数;
select
deptno, sum(sal),max(sal),min(sal),avg(sal),count(*)
from emp
group by deptno having min(sal) < 3000
总结:找准分组的列 | 显示的数据(组函数+分组的列)
ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2)
RANK() OVER(PARTITION BY col1 ORDER BY col2)
DENSE_RANK() OVER(PARTITION BY col1 ORDER BY col2)
8、DML
DML : Data Manipulation Language 数据操作语言; —– INSERT | UPDATE | DELETE
INSERT INTO table_name (column1,column2,...) values ( value1,value2, ...);
UPDATE table_name set column1=new value,column2=new value,... [WHERE <条件>]
DELETE FROM <table_name> WHERE <条件>
删除表全部数据
delete from emp -- 删除表所有数据 (可以恢复)
truncate table emp; --截断表所有数据 (不能恢复的)
区别:delete 删除表所有数据与truncate删除表数据区别
9.、事务(transaction)
会话(session): 每建立一个与Oracle数据库的连接,表示与Oracle 数据库建立一个会话;
在Oracle 数据库中,对表进行增、删、改,默认并不没有持久化写入到表数据中,而是放在session缓冲区,如果没有提交数据,是不会持久化的;
想要提交数据,执行:commit — 作为一个事务提交的;当数据在提交之前,是可以回滚的,但一旦提交完成,不能回滚; 回滚操作:rollback 即可;
事务(transaction):是一个工作单元,可以封装一系列操作,事务要求此系列操作要么都成功,要么都失败;事务执行完后,是不能回滚的;
事务特点|特性:
原子性 :系列操作是不能分割的(必须作为一个整体一起提交到数据库中运行)
转帐:(2步操作:一个账号要减,一个账号要加)
一致性:事务操作前后数据必须操作一致;
隔离性:事务的操作不受其它事务影响
持久性:事务一旦完成,将持久写入数据
Oracle数据库默认(增、删、改)操作并没有开启事务,所以需要手动提交,可以开启Oracle数据库自动事务
set autocommit on;
set autocommit off;
10、DDL(表相关)
DDL:Data Definition Language 数据定义语言 — CREATE | ALTER | DROP
Oracle数据库对象的创建默认是基于事务的;
10.1 Oracle数据类型
数据类型 | 描述 |
---|---|
VARCHAR2(size) | 可变长字符数据 |
CHAR(size) | 定长字符数据 |
NUMBER(p,s) | 可变长数值数据 |
DATE | 日期型数据 |
CLOB | 字符数据,最大可达到4G |
BLOB | 二进制数据,最大可达到4G |
10.2 创建表
CREATE TABLE <table_name>(
column1 DATATYPE [NOT NULL] [PRIMARY KEY],
column2 DATATYPE [NOT NULL],
...
[constraint <约束名> 约束类型 (要约束的字段)
... ] )
创建一张表(tb_students)
tb_students
---------------------------------------------------
属性名 类型 约束 备注
sid varchar2(50) 学生编号
sname varchar2(100) 学生姓名
sgender varchar2(10) 学生性别
semail varchar2(50) 学生邮箱
stel varchar2(20) 电话
sbirthday date 出生日期
......
关于表主键属性的赋值:主键约束要求属性值不能为空,属性值不能重复,主键属性的值一般采用以下2种方案:
1、标识列 : 是数字类型(整数),会自动生成,在mysql,sqlserver数据库中很方便,在oracle数据库中不太方便
2、uuid :全球唯一的一串字符串值;生成uuid可以java语言中生成,再传到数据库中,也可以直接使用oralce生成唯一的uuid 值;
产生UUID两种方式 调用oracle数据库中的:SYS_GUID() 函数生成 | 通过JAVA生成
String uuid =UUID.randomUUID().toString();
10.3 序列
CREATE SEQUENCE <sequencen_name>
INCREMENT BY n
START WITH n
[MAXVALUE n][MINVALUE n]
[CYCLE|NOCYCLE]
[CACHE n|NOCACHE];
1 CURRVAL :获取序列当前值;
2.NEXTVAL :下一个序列值
序列:生成一个连续的标识值
mysql数据库生成标识列:autoincrement ,sqlserver数据库中产生标识列:identity
10.4 删除表
drop table tablename;
10.5 修改表结构
添加列 | 删除列 | 修改列
ALTER TABLE <table_name> ADD (字段1 类型 [NOT NULL]
alter table tb_students add saddress varchar2(100)
ALTER TABLE <table_name> drop (字段1,字段2.... );
alter table tb_students drop (saddress);
ALTER TABLE <table_name> modify(字段1 类型,字段2 类型.... );
alter table tb_students modify (sname varchar2(50))
10.6 Oracle闪回机制
oracle数据库10g版本后,增加闪回机制,表对象删除默认放到回收站对象中存放;
查看oracle 回收站
show recyclebin;
从回收站中恢复被删除的表;
flashback table 表名 to before drop [rename to 新表名]
从回收站中再次册除表 ---彻底没了
purge table 表名
清空回收站
purge recyclebin
删除表对象,不放到回收站(永久删除)
drop table tablename purge;
10.7 修改表名
RENAME <table_name> to <new table_name>;
10.8 复制表
复制表结构与数据 | 复制表结构
create table deptbak as select * from dept;
create table deptbak as select * from dept where 1=2;
11、约束(constraint)
约束对象:表数据约束 — 有效性 + 完整性 === 正确性
约束添加的时机:可以在创建表时同时添加约束; 也可以表创建完后,再追加约束;
主键约束:一般建表是需要添加主键约束的(主键是确定表数据的唯一标识) 主键约束要求:非空 + 维一
非空约束:值不能为空;
检查约束:验证属性值的有效性
外键约束:表中的属性值关联外部表的某一个属性值
唯一约束:值不重复(可以为空值,但只能有一个空值)
create table tb_students
(
sid varchar2(50),
sname varchar2(100) constraint null_students_sname not null ,
sgender varchar2(10) ,
mid varchar2(50),
stel varchar2(20),
constraint pk_students_sid primary key (sid) ,
constraint ck_students_sgender check(sgender='男' or sgender='女'),
constraint fk_students_mid foreign key (mid) references tb_majors(mid),
constraint un_students_stel unique(stel)
)
追加约束
ALTER TABLE <table_name> ADD CONSTRAINT <constraint_name> 约束类型 (针对的字段名);
删除约束
ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>;
alter table tb_students add constraint pk_students_sid primary key(sid)
alter table tb_students add constraint ck_students_sgender check(sgender='男' or sgender='女')
alter table tb_students add constraint fk_students_mid foreign key (mid) references tb_majors(mid);
alter table tb_students add constraint un_students_stel unique(stel)
alter table tb_students modify sname varchar2(50) constraint null_students_sname not null
12、视图(View)
视图:基于源表查询得到的结果,视图是不存放数据的(数据存在源表中) — 虚拟表,
视图对象的作用:可以屏蔽源表中比较敏感的数据; 可以保存复杂的SQL
CREATE [OR REPLACE] VIEW <view_name>
AS
<SELECT 语句>;
#屏蔽 emp 表中的sal,comm信息,其它信息用户可查看;
create or replace view v_emp
as
select empno,ename,job,hiredate,deptno from emp;
create or replace view v_select
as
select e1.ename,e2.ename 上司,d.dname,e1.sal,s.grade from emp e1
join dept d on e1.deptno=d.deptno
join salgrade s on e1.sal between s.losal and s.hisal
join emp e2 on e1.mgr = e2.empn
select * from v_emp;
select * from v_select;
DROP VIEW <view_name> -- 删除视图
13、同义词
同义词:对象的别名称为同义词
CREATE SYNONYM <synonym_name> for <tablename/viewname>
DROP SYNONYM synonym_name
14、索引
理解索引:索引服务于查询,索引是数据库的一个对象,索引对象类似图书目录 ,当对创建索引后,在查询时,oracle数据库会自动使用索引; 当对表数据进行增删改,oracle会自动维护索引;
创建索引环境:表数据比较多,表的属性值不需要频繁更新数据的属性,经常设置条件过滤的属性
不适合创建索引:表数据比较小,频繁更新数据的属性
CREATE INDEX <index_name> ON <table_name>(字段 [ASC|DESC]);
create index index_ename on emp (ename,asc)
select * from emp where ename ='';
对表来说,如果存在主键约束、维一约束,数据库会自动创建基于主键字段的索引及维一约束属性上索引
15、权限控制
熟悉Oracle 数据库的权限设置
以管理员的身份进入系统:创建账号
create user 账号 identified by 密码;
conn stu/123 --- > ORA-01045: user STU lacks CREATE SESSION privilege; logon denied
标识当前账号没有会话的权限
授予会话权限
grant 权限 to 账号
grant create session to stu
grant create table to stu
grant resource to stu :此授权后,可以访问默认的表空间(USERS)
grant create view to stu;
grant create sequence to stu;
......
常见权限
Create session
Create table
Create view
Create sequence
Create procedure
Create trigger
Create function
撤销权限
revoke 权限 from 账号
授予某一账号访问本账号下资源的权权限
ORA-01950: 对表空间 'USERS' 无权限 :不能访问(读写)USERS表空间的资源,需要通过管理员授权
grant resource to stu :此授权后,可以访问默认的表空间(USERS)
grant 权限 on 表|视图…. To 账号
grant select|insert|update|delete on users to scott
当账号比较多时,需要基于角色管理权限
创建角色
create role 角色名称; | drop role 角色名称
给角色授权
grant
create session,
create table,
create view,
create sequence,
create trigger,
create function,
create procedure,
resource to developer
再将角色赋给某一账号
grant developer to 账号1,账号2......; | revoke developer from user02;
16、PL/SQL
SQL 是所有关系型数据库都遵守的语法规则,PL/SQL是SQL语法的增强版,只适用于Oralce数据库,增强的主要是编程需要一些基本语法(面向过程语言中的语法基础)
常量、变量 、运算符、流程控制语句、异常处理等;
PL/SQL基本程序结构
[DECLARE] --声明部分
声明语句
BEGIN --执行部分
执行语句
[EXCEPTION] --异常处理部分
执行语句
END;
# 在控制台输出hello,world
SET SERVEROUTPUT ON; --默认为OFF;
BEGIN
DBMS_OUTPUT.PUT_LINE('hello,wrold');
END;
# 使用PL/SQL语法结构 添加数据V1
BEGIN
INSERT INTO DEPT(DEPTNO,DNAME,LOC) VALUES (50,'研发中心','北京');
--COMMIT;
END;
# 使用PL/SQL语法结构 添加数据V2
DECLARE
vdeptno NUMBER := 70 ;
vdname VARCHAR2 := '人事1';
vloc VARCHAR2 := '天津1';
BEGIN
INSERT INTO DEPT(DEPTNO,DNAME,LOC) VALUES (vdeptno,vdname,vloc);
DBMS_OUTPUT.PUT_LINE('添加成功');
END;
#特殊类型 %type :取出属性的类型 %rowtype :行类型
DECLARE
vdeptno SCOTT.DEPT.DEPTNO%type := 70 ;
vdname SCOTT.DEPT.DNAME%type := '人事1';
vloc SCOTT.DEPT.LOC%type := '天津1';
BEGIN
INSERT INTO DEPT(DEPTNO,DNAME,LOC) VALUES (vdeptno,vdname,vloc);
DBMS_OUTPUT.PUT_LINE('添加成功');
END;
#取出deptno为10的部门并打印出当前记录的数据 ;V1
DECLARE
vno DEPT.DEPTNO%type :=10;
vdname DEPT.DNAME%type ;
vloc DEPT.LOC%type;
BEGIN
SELECT * INTO vno, vdname,vloc FROM DEPT WHERE DEPTNO=vno;
DBMS_OUTPUT.PUT_LINE('dno:' || vno || ' dname:' || vdname || ',loc' || vloc);
END;
#取出deptno为10的部门并打印出当前记录的数据 ;V2
DECLARE
vrow DEPT%rowtype;
BEGIN
SELECT * INTO vrow FROM DEPT WHERE DEPTNO='10';
DBMS_OUTPUT.PUT_LINE('DNO:' || vrow.deptno || ' DNAME:' || vrow.dname || ',LOC:' || vrow.loc);
END;
#用户交互:&
DECLARE
vrow DEPT%rowtype;
BEGIN
SELECT * INTO vrow FROM DEPT WHERE DEPTNO='&请输入部门编号';
DBMS_OUTPUT.PUT_LINE('DNO:' || vrow.deptno || ' DNAME:' || vrow.dname || ',LOC:' || vrow.loc);
END;
#测试异常
DECLARE
vrow DEPT%rowtype;
BEGIN
SELECT * INTO vrow FROM DEPT WHERE DEPTNO='&请输入部门编号';
DBMS_OUTPUT.PUT_LINE('DNO:' || vrow.deptno || ' DNAME:' || vrow.dname || ',LOC:' || vrow.loc);
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE('没有此部门');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('未知错误.....');
END;
PL/SQL流程控制
DECLARE
vscore number(3) := '&请输入分数';
BEGIN
IF vscore >= 60 THEN
DBMS_OUTPUT.PUT_LINE('通过了考试');
END IF;
END;
DECLARE
vscore number(3) := '&请输入分数';
BEGIN
IF vscore >= 60 THEN
DBMS_OUTPUT.PUT_LINE('通过了考试');
ELSE
DBMS_OUTPUT.PUT_LINE('考试未通过');
END IF;
END;
--根据薪水判断:>4000 富裕 >3000 :小康 > 2000 :温饱 贫困
DECLARE
vempno EMP.EMPNO%type :='&empno';
vsal EMP.SAL%type ;
BEGIN
select sal into vsal from emp where empno=vempno;
IF vsal >=4000 THEN
DBMS_OUTPUT.PUT_LINE('富裕级别');
ELSIF vsal >=3000 THEN
DBMS_OUTPUT.PUT_LINE('小康级别');
ELSIF vsal >=2000 THEN
DBMS_OUTPUT.PUT_LINE('温饱级别');
ELSE
DBMS_OUTPUT.PUT_LINE('贫困级别');
END IF;
END;
---------------------------------------------------------------------------------------
DECLARE
v_i number :=1;
v_sum number :=0;
BEGIN
LOOP
v_sum := v_sum + v_i;
v_i := v_i + 1;
EXIT WHEN v_i > 100 ;
END LOOP;
DBMS_OUTPUT.PUT_LINE('1+2+3+...+100 = ' || v_sum );
END;
DECLARE
v_i number :=1;
v_sum number :=0;
BEGIN
WHILE v_i <=100
LOOP
v_sum := v_sum + v_i;
v_i := v_i + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('1+2+3+...+100 = ' || v_sum );
END;
DECLARE
v_sum number :=0;
BEGIN
FOR v_i IN 1..100
LOOP
v_sum := v_sum + v_i;
END LOOP;
DBMS_OUTPUT.PUT_LINE('1+2+3+...+100 = ' || v_sum );
END;
17、游标(cursor)
游标(CURSOR ): 是一指向查询结果集的一个对象(取名叫游标|指针),通过游标对象可以对结果集中的数据逐行读取;
Oracle数据库中游对象的使用:声明游标 —打开游标 —-获取数据 –关闭游标
声明游标: CURSOR <游标名> IS <SELECT 语句>
打开游标: OPEN <游标名>
获取数据: FETCH <游标名> INTO 变量1,变量2,变量3,....变量n,;
或者
FETCH <游标名> INTO 行对象; --取出游标当前位
关闭游标 CLOSE <游标名>
游标对象属性:%ISOPEN | %NOTFOUND
DECLARE
vrow dept%rowtype;
-- 1声明游标
CURSOR curdept IS select * from dept order by deptno asc ; -- curemp游标类型的变量
BEGIN
-- 2打开游标
IF curdept%ISOPEN THEN
CLOSE curdept;
END IF;
OPEN curdept;
-- 3 获取数据 (FETCH <游标名> INTO 行对象| 变量; )
LOOP
FETCH curdept INTO vrow; --第1次:移动游标对象到第1行,再取数据;
DBMS_OUTPUT.PUT_LINE('DEPTNO: ' || vrow.deptno || ' ,DNAME: ' || vrow.dname || ',LOC : ' ||vrow.loc);
EXIT WHEN curdept%NOTFOUND;
END LOOP;
-- 4 关闭游标
CLOSE curdept;
END;
18、函数
函数:主要用来实现计算的,并且有返回值的;
定义:CREATE [OR REPLACE] FUNCTION <过程名>[(参数列表)] RETURN 数据类型 IS
[局部变量声明]
BEGIN
可执行语句
EXCEPTION
异常处理语句
END [<过程名>];
变量的类型:in 为默认类型,表示输入; out 表示只输出;in out 表示即输入又输出;
#声明一个函数,用来根据账号统计员工的年薪;
create or replace function fn_yearsal(eno in SCOTT.EMP.SAL%TYPE) RETURN NUMBER
IS
yearsal SCOTT.EMP.SAL%TYPE :=0;
BEGIN
select (sal + nvl(comm,0))*12 into yearsal from emp where empno=eno;
return yearsal;
END;
set serveroutput on;
declare
n emp.sal%type;
m emp.sal%type;
begin
n:=&请输入员工号;
m:=fn_yearsal(n);
dbms_output.put_line('m的值为 '||m);
end;
19、过程
过程:oracle数据库中用业封装 对表进行操作的或者数据库维护的SQL;
定义:CREATE [OR REPLACE] PROCEDURE <过程名>[(参数列表)] IS
[局部变量声明]
BEGIN
可执行语句
EXCEPTION
异常处理语句
END [<过程名>];
变量的类型:in 为默认类型,表示输入; out 表示只输出;in out 表示即输入又输出;
#示例:定义一个过程 ,向表添加一条数据;
CREATE OR REPLACE PROCEDURE proc_insert
IS
BEGIN
INSERT INTO dept(deptno,dname,loc) VALUES(50,'人事','北京');
COMMIT;
END;
BEGIN
proc_insert();
END;
#示例:定义一个过程 ,向表添加一条数据;
CREATE OR REPLACE PROCEDURE proc_insert2(dno IN DEPT.DEPTNO%TYPE,dn IN DEPT.DNAME%TYPE,loca IN DEPT.LOC%TYPE)
IS
BEGIN
INSERT INTO dept(deptno,dname,loc) VALUES(dno,dn,loca);
COMMIT;
END;
DECLARE
dno NUMBER := &dno;
dname VARCHAR2(14) := '&dn';
loc VARCHAR2(13) := '&loc';
BEGIN
PROC_INSERT2(dno,dname,loc);
END
#示例 :统计表中记录数
create or replace PROCEDURE PROC_COUNT (dno IN NUMBER, total OUT NUMBER)
IS
BEGIN
select count(*) into total from emp where deptno=dno;
END;
DECLARE
DNO NUMBER;
TOTAL NUMBER;
BEGIN
DNO := 10;
PROC_COUNT(
DNO => DNO,
TOTAL => TOTAL
);
:TOTAL := TOTAL;
END;
过程 | 函数 |
---|---|
没有返回值 | 有返回值 |
执行操作 | 执行数据运算(统计) |
PROCEDURE | FUNCTION |
DML |DQL 中不可以调用过程 | DML ,DQL 可以 |
过程可以作为PL/SQL语句独立运行 | 函数不可以作为PL/SQL语句独立运行(需要放到一个表达式中) |
20、[数据]包
包(package):oracle对象包装; oracle中的包分为两部分:包头,包体
CREATE [OR REPLACE] PACKAGE <数据包名> AS
--公共类型和对象声明
--子程序说明
END;
定义包的主体
CREATE [OR REPLACE] PACKAGE BODY <数据包名> AS
--公共类型和对象声明
--子程序主体
BEGIN
-初始化语句
END;
create or replace PACKAGE DEPTPACKAGE AS
-- 部门表操作创建的对象组织到包中;
FUNCTION getTotalRecoreds RETURN NUMBER;
-- 2,3,4
PROCEDURE PROC_INSERT;
END ;
CREATE OR REPLACE
PACKAGE BODY DEPTPACKAGE AS
FUNCTION getTotalRecoreds RETURN NUMBER AS
num number := 0;
BEGIN
select count(*) into num from dept;
RETURN num;
END ;
PROCEDURE PROC_INSERT AS
BEGIN
INSERT INTO DEPT VALUES (11,'aa','bb');
END;
END DEPTPACKAGE;
21、触发器
触发器:触发器是Oracle数据库中自动执行的存贮过程;当条件满足,自动执行;Oracle触发器分为DML触发器,系统触发器;对程序员来说,主要关注的业务是DML触发器;
要阻止添加|删除|修改操作,只需要触发器抛出一个错误即可;
RAISE_APPLICATION_ERROR(错误号(-20000到-20999),消息[,{true|false}]);
CREATE [OR REPLACE] TRIGGER <触发器名>
BEFORE|AFTER
INSERT|DELETE|UPDATE [OF <列名>] ON <表名>
[FOR EACH ROW]
BEGIN
END;
例1:对dept表执行删除操作(前),自动给出提示
CREATE OR REPLACE TRIGGER tri1
BEFORE DELETE
ON dept
BEGIN
DBMS_OUTPUT.PUT_LINE('正在删除数.....');
END;
例2:写一个更新提示的触发器(要求每更新一条,都打印出提示信息)
CREATE OR REPLACE TRIGGER tri2
AFTER UPDATE
ON dept
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('一条数据完成更新.....');
END;
FOR EACH ROW :添加此设置,表中的每一行发生改变,都有触发 触发器的自动运行;
例3:数据在休息日不允许修改|删除|添加;
create or replace TRIGGER tri3
BEFORE DELETE OR UPDATE OR INSERT
ON dept
BEGIN
IF DELETING THEN
BEGIN
IF TO_CHAR(SYSDATE,'day') = '星期六' OR TO_CHAR(SYSDATE,'day') = '星期日' THEN
RAISE_APPLICATION_ERROR(-20001,'星期六、星期天不允许对数据进行删除操作....',true);
END IF;
END;
END IF;
IF UPDATING THEN
BEGIN
IF TO_CHAR(SYSDATE,'day') = '星期六' OR TO_CHAR(SYSDATE,'day') = '星期日' THEN
RAISE_APPLICATION_ERROR(-20001,'星期六、星期天不允许对数据进行修改操作....',true);
END IF;
END;
END IF;
IF INSERTING THEN
BEGIN
IF TO_CHAR(SYSDATE,'day') = '星期六' OR TO_CHAR(SYSDATE,'day') = '星期日' THEN
RAISE_APPLICATION_ERROR(-20001,'星期六、星期天不允许对数据进行添加操作....',true);
END IF;
END;
END IF;
END;
例4:删除数据时自动备份
create or replace TRIGGER tri4
BEFORE
DELETE
ON dept
FOR EACH ROW
BEGIN
INSERT INTO deptbak VALUES(:old.deptno,:old.dname,:old.loc);
END;
例5:测试 :old 与 :new
create or replace TRIGGER tri5
BEFORE
UPDATE
ON dept
FOR EACH ROW
BEGIN
-- 更新前的数据| 更新后的数据;
DBMS_OUTPUT.PUT_LINE(:old.deptno);
DBMS_OUTPUT.PUT_LINE(:old.dname);
DBMS_OUTPUT.PUT_LINE(:old.loc);
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
DBMS_OUTPUT.PUT_LINE(:new.deptno);
DBMS_OUTPUT.PUT_LINE(:new.dname);
DBMS_OUTPUT.PUT_LINE(:new.loc);
END;
:old :原数据对象 ,:new 新数据对象
22、数据备份与恢复
1、导入(import)|导出(export)
表方式
备份:Exp 账号/密码@数据库 file =备份文件 log= 日志文件 tables=要备份的表;
exp scott/tiger@orcl file=f:/dbbak/databk.dmp log=f:/dbbak/databklog.log tables=emp,dept
恢复: imp 账号/密码@数据库 file =备份文件 log= 日志文件 tables=表名 fromuser="源账号" touser=目标账号 commit=y(事务提交) ignore=y(重复数据忽略)
imp scott/tiger@orcl file=f:/dbbak/databk.dmp log=f:/dbbak/databklog.log tables=emp fromuser=scott touser=scott commit=y ignore=y
用户方式 :
exp scott/tiger@orcl file=f:/dbbak/databk2.dmp log=f:/dbbak/databklog2.log
imp scott/tiger@orcl file=f:/dbbak/databk2.dmp log=f:/dbbak/databklog2.log tables=emp fromuser=scott touser=scott commit=y ignore=y
全库方式
exp system/orcl@orcl file=f:/dbbak/databk3.dmp log=f:/dbbak/databklog3.log
imp sys/orcl@orcl file=f:/dbbak/databk3.dmp log=f:/dbbak/databklog3.log full=y ignore=y destroy=y
2、冷备份
如果要进行冷备份,则需要备份出数据库中的几个核心内容:
● 控制文件:指的是控制整个oracle数据库的实例服务的核心文件,直接通过”v$contronlfile”找到;
● 重做日志文件:可以进行数据库的灾难恢复,直接通过”v$logfile”找到;
● 数据文件:表空间文件,通过”v
tablespace”找到;
● 核心操作的配置文件(pfile),通过”show paramter pfile”找到;
从实际的Oracle的部署来讲,所有的文件为了达到IO的平衡操作,要分别保存在不同的硬盘上。
今天的文章Java学习之路-oracle数据库分享到此就结束了,感谢您的阅读。
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
如需转载请保留出处:https://bianchenghao.cn/16585.html