一篇文章吃透Oracle：从SQL查询到PL/SQL编程

一篇文章吃透Oracle：从SQL查询到PL/SQL编程

目录

• 一、Oracle环境配制
• 二、Scott账号
• 三、数据库基础
• 四、结构化查询语言（SQL）
  • 2、过滤和排序数据
  • 3、单行函数
  • 4、多表查询
  • 5、自然连接【了解】
  • 6、USING子句【了解】
  • 7、子查询
  • 8、分组查询
  • 9、数据操作语言
  • 10、事务
  • 11、创建表
  • 12、结束
• 五、JDBC
  • 5.1Oracle数据库驱动：
  • 5.2 mysql数据库驱动
• 六、视图与索引
• 七、用户管理【了解】
• 八、PL/SQL
• 九、游标(cursor)
• 十、函数(function)/过程
• 十二、触发器[了解]
• 十三、备份与恢复
• 十四、mysql8.0补充

Oracle：甲骨文软件系统有限公司，从数据库起家的，是全球第2大软件公司；在数据库应用领域，Oracle决对全球第一。

oracle数据库是最复杂的关系型数据库，安装后会产生7个服务：如果使用Java访问Oracle,只需要关注2个服务：

OracleServiceOrcl:Oracle数据库服务(必须启动)

OracleOraDb11g_home1TNSListener:监听服务（通过网络访问Oracle数据库| 如果使用Sql Developer管理Oracle）

启动与关闭服务---通过命令打开与关闭服务(必须以管理员身份运行)net stop OracleServiceOrcl：关闭oracle数据库服务 netstartOracleServiceOrcl：开启oracle数据库服务 lsnrctl stop :关闭监听服务 lsnrctlstart:开启监听服务---- lsnrctl status 查看监听服务的状态

Oracle数据库产品比较重要的几个版： I版本(internateOracle 8i/9i)G版本(Grid Oracle 10g/11g) C版本(Cloud 12C/19C)

一、Oracle环境配制

安装Oracle数据库，如果忘记密码（管理员密码或才某一个需要用的账号），怎么处理

Oracle数据库提供2个客户端，一个命令行的客户端(Sqlplus)，一个是图形界面的客户端（Sql Developer）

sys或者system登录进入系统： sqlplus sys/orclassysdba;|sqlplus system/orclassysdba;sys/system以无密码进入系统：sqlplus sys/nologassysdba;安装Oracle数据库后，如果没有手动激活某一个内置账号，所有账号默认处理锁定状态，锁定状态，账号是不能用的(SCOTT账号默认也是锁定的)普通账号登录：sqlplus scott/tiger(sqlplus 账号/密码)，切换账号：conn sys/orclassysdba 解锁一个账号并设置账号的密码 解锁:alteruserscott accountunlock|加锁：alteruserscott accountlock;修改密码：alteruserscott identifiedby123;

scott账号是Oracle公司早期设计的一个示例账号，为了方便用户学习Oracle数据库而设计的;后来，又提供一个升级版的示例账号(hr),此账号下的资源相对scott要多一些

二、Scott账号

Oracle数据库对资源管理是基于账号的（与mysql与Sql Server数据库的设计是不一样的）,所有的资源是属于某一个账号,使用Oracle数据库，需要一个自己的账号

在自己账号下创建属于自己的资源,对学习，使用内置的账号即可 ----scott

在数据库中，数据是表为单位来 存贮的（表是数据库中真正存放数据的以象） ----表对象

查看SCOTT账号下有哪些表(table)select*fromtab;-- 查询SCOTT账号下的所有表对象TNAME TABTYPE CLUSTERID---------------------------------------------------BONUSTABLEDEPTTABLEEMPTABLESALGRADETABLEBONUS:工资表（此表为空表） DEPT：部门表 EMP:雇员表 SALGRADE:工资等级表 SQLPLUS配制setlinesize400设置每行显示的字符数;setpagesize :40设置每页显示的行数 每一次启动SQLPLUS都需要重新设置一下 查看表结构 ：descdept;名称 是否为空? 类型 备注---------------------------------------------DEPTNONOTNULLNUMBER(2)部门编号 DNAME VARCHAR2(14)部门名称 LOC VARCHAR2(13)部门地址 名称 是否为空? 类型 备注---------------------------------------------GRADE NUMBER 等级编号 LOSAL NUMBER 最低工资 HISAL NUMBER 最高工资 名称 是否为空? 类型 备注 EMPNONOTNULLNUMBER(4)雇员编号 ENAME VARCHAR2(10)雇员姓名 JOB VARCHAR2(9)雇员职务 MGR NUMBER(4)雇员上司 HIREDATEDATE雇佣日期 SAL NUMBER(7,2)薪水 COMM NUMBER(7,2)佣金 DEPTNO NUMBER(2)部门编号

三、数据库基础

数据(data)：计算机中所存贮的信息统称为数据（文本、数字、音频、视频、图片） ---- 以文件为单位存在磁盘中；

数据库(database)：数据的仓库称为数据库（本质是存在磁盘中的文件 ------- 管理系统）,数据库对应的文件有三个：.DBF|.LOG| *.CTL

数据库管理系统(DBMS) ：管理与维护数据库的软件 ----任何一数据库产品都需要提供管理系统（用户是通过管理系统来操作数据库）

数据库管理员（DBA）

数据库系统：一个完整的数据库系统包含：硬件 + 操作系统+数据库管理系统+数据库管理员+ 数据库

关系数据库：数据以表为单位来存的；

常见的关系型数据库：Oracle (1521),MySql(3306) ,SQL Server(1433) 、DB2

四、结构化查询语言（SQL）

学习数据库从SQL开始：Structured Query Language — 是所有关系型数据库都支持的语言（语法都完全一样）

DQL：Data Query Language 数据查询语言 ---- SELECT

DML：Data Manipulation Language 数据操作语言 ---- INSERT ,UPDATE,DELETE

DCL：Data Control Language — 数据控制语言 GRANT| REVOKE

TCL：Transaction Control Language 事务控制语言：COMMIT|ROLLBACK

DDL：Data Definition Language 数据定义语言 CREATE TABLE|VIEW|INDEX| PROCEDURE|… DROP …

1、查询与排序

SELECT*|{[DISTINCT]column|expression[alias],...}FROMtable;

示例：查询表所有列的数据SELECT*FROMemp;示例：查询雇员表中的姓名、职务、薪水、佣金信息SELECTename,job,sal,commFROMemp;SQL支持简单表达式运算 ： 算述运算符（+-*/） 示例：查询雇员的姓名及薪水SELECTename,sal+commFROMemp;通过观察结果得到：与一个空值做运算得到一个空值SELECTename,sal+NVL(comm,0)FROMemp;示例：查询雇员的姓名及薪水SELECTename 姓名,(sal+NVL(comm,0))薪水FROMemp;SELECTenameAS姓名,(sal+NVL(comm,0))AS薪水FROMemp;SQL支持查询结果的合并,需要使用连字符：||合并姓名与职务SELECTename||jobFROMemp;示例：查询雇员部门信息（去重复）SELECTDISTINCTdeptnoFROMemp

2、过滤和排序数据

SELECT*|{[DISTINCT]column|expression[alias],...}FROMtable[WHEREcondition(s)][ORDERBY{column,expr,alias}[ASC|DESC]];

示例：查询部门编号为20的所有雇员 :等值查询SELECT*FROMempWHEREdeptno=20备注：如果值类型的数字：可以不使用单引号括起来，如果是其它类型需要使用单引号 查询职务为办事员的所有雇员SELECT*FROMempWHEREjob='CLERK'比较运算符：=,>,<,>=,<=,<>(!=)查询工资大于2000的所有雇员SELECT*FROMempWHEREsal>2000如果条件涉及到多个，需要用到连接条件的运算符AND,OR查询工资大于2000并且部门编号为20的所有雇员SELECT*FROMempWHEREsal>2000ANDdeptno=20查询工资大于3000以及部门为10的雇员SELECT*FROMempWHEREsal>2000ORdeptno=10BETWEEN....AND:表示范围[a,b]查询工资在2000到3000之间的所有雇员SELECT*FROMempWHEREsal>=2000ANDsal<=3000;SELECT*FROMempWHEREsalBETWEEN2000AND3000;IN设置条件 查询雇员编号为：7369,7788,7902的员工SELECT*FROMempWHEREempno=7369ORempno=7788ORempno=7902SELECT*FROMempWHEREempnoIN(7369,7788,7902,1234)SELECT*FROMempWHEREempnoNOTIN(7369,7788,7902,1234)NULL空查值询 查询佣金为空的雇员SELECT*FROMempWHEREcommISNULL;SELECT*FROMempWHEREcommISNOTNULL;模糊查询-LIKE模糊查询用到的2个通配符：%--匹配0个或者多个字符 _ ：匹配任意单个字符查询姓名中包含字母‘A'所有雇员 SELECT * FROM emp WHERE ename LIKE '%A%' 查询姓名第3个字符为’A'所有雇员SELECT*FROMempWHEREenameLIKE'__A%'SELECT*FROMempWHEREenameNOTLIKE'__A%'按工资从低到高显示所有雇员;SELECT*FROMempORDERBYsalASC;SELECTename,hiredateFROMempORDERBYhiredateASC;

3、单行函数

函数（function)：一段被命名的代码块称为函数（Oracle中的函数主要用来做运算，封装的是SQL脚本)-- Oracle内置函数 | 自定义函数

• 字符函数
• 数值函数
• 日期函数
• 转换函数
• 通用函数

字符函数

测试函数可以基于某一张表或者使用DUAL虚拟表来测试

lower|upper|initcap|concat|substr|length|instr|lpad|rpad|trim|replaceSELECTlower('HELLO,WORLD'),upper('hello,world'),initcap('hello,world')FROMdualSELECTename,substr(ename,1,3)FROMemp substr(str,startindex,len);[startindex,len]SELECTename,substr(ename,1,3),length(ename),instr(ename,'A'),lpad(ename,10,'*'),rpad(ename,10,'*'),length(' abc '),length(trim(' abc ')),replace(ename,'A','tom')FROMemp

数值函数

数值运算，在使用日期函数时，往往需要数值函数的支持

round | trunc | mod SELECT round(12.456,2),trunc(123.567,1),mod(10,3) FROM dual;

日期函数

Oracle日期默认格式：日 - 月 - 年 ；通过SYSDATE函数可以查看当前日期,日期显示的格式可以通过转换函数时行格式化;

yyyy mm dd hh24 mi ssSELECTto_char(sysdate,'yyyy-mm-dd hh24:mi:ss')FROM