Oracle 数据库管理系统 = oracle数据库（数据文件集合） + oracle实例（Oracle服务） + 管理系统
Oracle12c（多租用户的环境） = CDB(数据库容器) PDB（可插拔数据库）
Oracle
Oracle的安装使用
安装虚拟机、vmtools
-
安装虚拟机（VMware Workstation Pro）
-
在虚拟机中安装win10
-
安装vmtools
将文件解压到：
点击解压好的文件中的setup64，点击下一步，直到安装完成，完成之后，需要重启虚拟机
-
vmtools的网络配置
- 桥接模式：直接链接物理网络 直接连接到你的路由器，获取和你的计算机同网段的一个IP地址
- NAT：用于共享主机的IP地址。将你的主机作为一个路由器，虚拟机从主机上获取一个IP ，和主机共享同一个外网IP
- 仅主机模式：仅有主机可以上网
我们使用桥接模式（优先），或NAT模式
设置好网络连接方式后，在虚拟机中测试虚拟机的网络是否畅通
- 下载Oracle数据库
目前主流的版本是 11g R2 和12CR2
目前Oralce最新的发布版：19C
当前安装的数据库的版本为Oracle 12C R2
下载地址：https://www.oracle.com/index.html
- 安装Oracle
- 将Oracle安装包拷贝到虚拟机中，再将其解压（解压时间较长）
打开解压好的文件，右键点击setup，选择以管理员身份运行
会出现这样一个窗口，稍等一会
启动页面
开始安装前的配置：
-
配置安全更新
-
安装选项
-
数据库选项
-
数据安装选项
-
安装类型
-
数据库版本
-
指定oracle主目录用户
-
安装位置 保持默认
-
配置类型
-
数据库标识符
-
配置选项
-
数据库存储
-
管理选项
-
恢复选项
-
方案口令
-
先决条件的检查（耗时较多）
-
概要
-
安装
-
完成安装
测试Oracle数据库
Oracle自带的命令行工具 SQL*PLUS
此处oracle在安装的时候 默认开放了两个账户 sys （超级管理员）和system（普通管理员）
使用system登陆（密码为root）：
使用sys登陆（密码为root）：
Oracle 的服务
在虚拟机中打开任务管理器，依次点击服务-> 打开服务
在安装过程中，oracle注册到windows系统中的服务,其中由两个服务是最为重要的：
- OracleServiceORCL：oracle的实例服务,表示一个oracle数据库对象
- OralceOraDB12Home1TNSListener：监听服务,当需要链接oracle的时候,必须打开此服务
虚拟机相当于一个Oracle的数据库服务器
客户端的配置
将文件解压之后放在一个文件夹中（要记住此路径，后面还要用到）
在其路径中新建文件夹 NETWORK ,在其中再新建文件夹 ADMIN
将虚拟机中oracle安装目录（C:\app\Adminstrator(此处为虚拟机名称)\virtual\product\12.2.0\dbhome_1\network\admin）中的文件拷贝出来（到桌面）
将其用记事本或其他文档软件打开，将部分参数进行修改（修改为虚拟机的IP地址）：
强烈推荐将参数修改为虚拟机中的计算机名（虚拟机中的命令提示符输入hostname获取计算机名），因为IP地址可能会改变，避免后期还需修改。
-
listener.ora：
-
tnsnames.ora：
修改完配置文件后，将配置文件分别复制到虚拟机中的原目录（替换）和本地主机的新建目录（如下）：
将虚拟机中的oracle的实例服务和监听服务分别重启（这一步很重要）：
重新启动之后，在虚拟机中通过sqlplus 重新登陆oracle，如果登陆成功，则说明配置文件的修改是正确。
Oralce的客户端工具 PLSQL Developer
安装完成后，再将中文配置文件安装到PLSQL的安装路径中
打开PLSQL,进行配置：
还需要将虚拟机中的防火墙关闭，才能正常使用
一切设置完成后，数据库即可正常使用。
Oracle 概述
Oracle 是一个公司名称也是一个产品名称
Oracle 数据库管理系统 = oracle 数据库（数据文件集合） + oracle 实例（Oracle 服务） + 管理系统
- 数据库
- Oracle 数据库是数据的物理存储。
- 这就包括（数据文件 ORA 或者 DBF、控制文件、联机日志、参数文件）。
- 其实 Oracle 数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是 Oracle 就只有一个大数据库
- 实例
一个 Oracle 实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。 一个数据库可以有 n 个实例。 - 用户
用户是在实例下建立的。不同实例可以建相同名字的用户。 - 表空间
- 表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件） 的逻辑映射。
- 一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。
- 每个数据库至少有一个表空间(称之为 system 表空间)。每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)（dbf、 ora）。一个数据文件只能属于一个表空间。
- 数据文件（dbf、 ora）
- 数据文件是数据库的物理存储单位。
- 数据库的数据是存储在表空间中的， 真正是在某一个或者多个数据文件中。 而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。
- 一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。
注： 表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。
由于 oracle 的数据库不是普通的概念， oracle 是有用户和表空间对数据进行管理和存放的。但是表不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了！
Oracle与Oracle实例
- 平常所说的 Oracle 或 Oracle 数据库指的是 Oracle 数据库管理系统.。
- Oracle 数据库管理系统是管理数据库访问的计算机软件(Oracle database manager system)，它由 Oracle 数据库和 Oracle 实例(instance)构成。
- Oracle 数据库: 一个相关的操作系统文件(即存储在计算机硬盘上的文件)集合,这些文件组织在一起, 成为一个逻辑整体, 即为 Oracle 数据库。
- Oracle 用它来存储和管理相关的信息.Oracle数据库必须要与内存里实例合作，才能对外提供数据管理服务。
- Oracle 实例: 位于物理内存里的数据结构，它由操作系统的多个后台进程和一个共享的内存池所组成,共享的内存池可以被所有进程访问.
- Oracle 用它们来管理数据库访问，用户如果要存取数据库(也就是硬盘上的文件) 里的数据, 必须通过Oracle实例才能实现, 不能直接读取硬盘上的文件。
- 实际上, Oracle 实例就是平常所说的数据库服务(service)。
- 区别：实例可以操作数据库；在任何时刻一个实例只能与一个数据库关联，访问一个数据库；而同一个数据库可由多个实例访问（RAC）
Oracle12c之前的版本中都有一个默认的普通用户 scott/tiger ,在之前的版本中，该用户携带一个学习使用的示例数据库。但是到了12C的版本的时候，这个默认用户不存在，同时他所拥有的示例数据库也不存在了 。
Oracle12c（多租用户的环境） = CDB(数据库容器) PDB（可插拔数据库）
- Oracle 12C引入了CDB与PDB的新特性，在ORACLE 12C数据库引入的多租用户环境（Multitenant Environment）中，允许一个数据库容器（CDB）承载多个可插拔数据库（PDB）。
- CDB全称为Container Database，中文翻译为数据库容器，PDB全称为Pluggable Database，即可插拔数据库。
- 在ORACLE 12C之前，实例与数据库是一对一或多对一关系（RAC）：即一个实例只能与一个数据库相关联，数据库可以被多个实例所加载。而实例与数据库不可能是一对多的关系。当进入ORACLE 12C后，实例与数据库可以是一对多的关系。
- 下面是官方文档关于CDB与PDB的关系图。
再12C中创建scott/tiger用户，并创建实例数据库，以sys用户登陆：
在SQL PLUS中连接：
PLSQL使用
创建用户
CREATE USER scott identified by tiger;
为用户授权
grant connect,resource,unlimited tablespace to scott;
设置用户使用的 表空间
Alter user scott default tablespace users;
设置临时表空间
alter user scott temporary tablespace temp;
切换用户 使用scott进行登陆
Conn scott/tiger;
创建scott用户所需的表：
-- 创建数据表
CREATE TABLE dept (
deptno NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
dname VARCHAR2(14) ,
loc VARCHAR2(13)
) ;
CREATE TABLE emp (
empno NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);
CREATE TABLE bonus (
enamE VARCHAR2(10) ,
job VARCHAR2(9) ,
sal NUMBER,
comm NUMBER
) ;
CREATE TABLE salgrade (
grade NUMBER,
losal NUMBER,
hisal NUMBER
);
-- 插入测试数据 —— dept
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
-- 插入测试数据 —— emp
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-07-87','dd-mm-yyyy')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-07-87','dd-mm-yyyy')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
-- 插入测试数据 —— salgrade
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
-- 事务提交
COMMIT;
Scott用户的表主要用户课堂学习。
表注解：
解锁hr用户 hr用户下也拥有一些示例数据 是一个人事管理系统的表结构。
解锁hr用户必须由sys用户了，其他用户没有解锁的权限
alter user hr account unlock;#解锁hr用户
alter user hr identified by hr ;#为hr用户设置
密码为hr
登陆hr：
hr 中的表：
中文乱码问题：
select * from V$NLS_PARAMETERS
select userenv('language') from dual;
依次运行以上两句指令，在计算机中新建环境变量（系统变量方便一点）
scott /tiger 普通用户
表的结构分析：
| EMPNO | ENAME | JOB | SALAYRY | HIREDATE | COMM |
|---|---|---|---|---|---|
| 7589 | Smith | Manager | 5000 | 5-06 | 100 |
数据表中的每一列称为字段，对应Java中的成员属性。
数据表中的每一行称为记录，对应Java中的对象。
EMP（雇员表）：
DEPT（部门表）：
SALGRADE（薪资等级表）：
HR用户表结构分析
Employees：
Department：
SQL 语言
SQL语言是结构化查询语言，适用于所有的关系型数据库。但是在遵循SQL标准的同时，各种数据库在某些方面也有一些细微的差别，将每种数据库在SQL中的细微差别，称之为方言。
SQL语言可以执行对数据库的增删改查（CRUD）
SQL 分类
-
数据操纵语言，主要用于完成数据的CRUD操作（DML）
常用的语言：
- Select 查询
- Insert 插入
- Update 更新
- Delete 删除
-
数据的定义语言，用来定义数据的库以及表的结构，及创建、删除、修改等操作（DDL）
常用语句：
- Create table 创建表
- Alter table 修改表
- Drop table 删除表
- Create view 创建视图
- Create Index 创建索引
-
数据控制语言，控制对数据库的访问，可以对用户进行权限的赋予和回收、事务处理（DCL）
常用语句：
- Grant 授权
- Revoke 回收权限
- COMMIT 提交事务
- Rollback 回滚事务
SQL 语句的注意事项
- 注意大小写的使用：
- 小写：列名（字段名），参数，变量名
- 大写：关键字，对象名
- 要合理使用空格，提高sql语句的可读性
- 要注意代码的格式，要有合理的缩进
- 合理使用注释
- Oracle 的注释：
- 单行注释：–
- 多行注释：/* */
- Mysql 的注释：#
- Oracle 的注释：
DML 相关 SQL 语言
查询：select
格式：SELECT * |column FROM teblename
- ＊为通配符，表示查询表中的所有字段
- column：字段，根据需要列出要查询的字段，多个字段之间用逗号隔开
SQL语言的特点：
- 大小写不敏感
- SQL语句可以写一行或多行，用分号来进行标记
- SQL语句需要有良好的格式，提高可读性
- 需要添加必要的注释，提高可维护性
- 查询所有的列
-- 查询EMP表中所有的雇员信息
SELECT * FROM emp;
-- 查询所有的部门信息
SELECT * FROM dept;
运行结果：
- 查询特定的列
-- 查询雇员的姓名，职位，薪资
SELECT ename,job,sal FROM emp;
算数运算符
数字和日期使用的算术运算符
| 操作符 | 描述 |
|---|---|
| + | 加 |
| - | 减 |
| * | 乘 |
| / | 除 |
算数运算的优先级：先乘除后加减，可以通过括号来改变运算的顺序
-- 查询雇员的工号、姓名、职位、薪资、奖金，并为每一个员工的薪资增加1000元
SELECT empno,ename,job,comm,sal+1000 FROM emp;
-- 查询雇员的工号、姓名、职位、薪资、奖金，并计算每一个员工的年薪
SELECT empno,ename,job,sal,comm,sal*12 + comm from emp;
运行结果：
空值在算数运算中的使用：
- 空值在算数运算中的使用：在算数运算中，如果某一个字段中包含空值，则运算之后的结果为空
- 区分空值和0：空值是null表示什么都没有，不是0也不是空格。
列的别名
列的别名指的是：给某个列重新进行命名，便于使用和区别，使得语义更加明确。
别名使用格式：列明 AS 别名
-- 列名的使用
SELECT empno as no,ename name from emp;
可以看到AS可以省略。
连接符（||）
-- 查询雇员的姓名、职位、薪资。入职时间作为个人基本信息输出到一列
SELECT ename||job||sal||hiredate AS "personalInfo" from emp;
-- 查询雇员的姓名、职位、薪资。入职时间作为个人基本信息输出到一列,每个信息之间同--分割
SELECT ename||'--'||job||'--'||sal||'--'||hiredate AS "personalInfo" from emp;
运行结果：
字符串
在Oracle中，字符串可以作为select语句中的一个字符，数字，日期等出现
日期和字符只能在单引号中出现，Oracle中的字符是使用单引号表示的
去除重复记录（distinct）
-- 查询雇员所在的部门编号
SELECT deptno FROM emp;
-- 去除查询中重复的记录,只看有哪些部门
SELECT DISTINCT deptno FROM emp;
运行结果：
去重前：
去重后：
SQL 和 SQL*PLUS（虚拟机里的）
-
SQL
结构化查询语言，是用来查询数据和操作数据库 -
SQLPLUS
工具（环境）主要用来编写SQL语句和相关命令
命令不能改变数据库中数据的值 -
SQLPLUS可以执行的命令：
- 查看表的结构
- 编辑SQL语句
- 执行SQL语句
实例：
- 查询语句：
select *|distinct column|表达式 as 别名 from tablename
过滤查询
where + 过滤条件
select *|distinct column|表达式 as 别名 from tablename where 条件；
实例：
--查询姓名为SMITH雇员的基本信息,注意对于字符串是严格区分大小写的
SELECT * FROM emp WHERE ename='SMITH';
--查询入职日期为17-12月-80的雇员信息,日期作为字符串出现,需要加单引号，同时月份要使用英文缩写
SELECT * FROM emp WHERE hiredate='17-dec-80';
比较运算
| 符号 | 意义 |
|---|---|
| = | 等于 （赋值运算符 :=） |
| > | 大于 |
| < | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| <>(或!=) | 不等于 |
| between… and… | 取介于两个值之间的 |
| IN(值1，值2….) | 等于其中的任意一个 |
| LIKE | 模糊查询 匹配查询 |
| IS NULL | 空值 |
实例：
-- 查询除了10部门的所有员工信息
SELECT * FROM emp WHERE deptno!=10;
SELECT * FROM emp WHERE deptno<>10;
--查询薪资介于1500--3000的员工,包含边界值
SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000;
--查询部门编号为10,20,30部门的员工
SELECT * FROM emp WHERE deptno IN(10,20,30);
--查询姓名以S开头的员工
/*在LIKE使用中，一般会用到两个通配符
_ 代表一个字符
% 代表零个或多个任意字符
*/
SELECT * FROM emp WHERE ename LIKE 'S%';
--查询姓名中第二个字母为A的员工
SELECT * FROM emp WHERE ename LIKE '_A%';
--查询姓名中包含字母A的员工信息
SELECT * FROM emp WHERE ename LIKE '%A%';
NULL
实例：
-- 查询没有奖金的雇员信息
SELECT * FROM emp WHERE comm IS NULL;
-- 查询有奖金的雇员信息
SELECT * FROM emp WHERE comm IS NOT NULL;
逻辑运算
| 符号 | 意义 |
|---|---|
| AND | 逻辑与 |
| OR | 逻辑或 |
| NOT | 取反 |
实例：
-- 查询工资大于1000,并且姓名开头字母为A的雇员信息
SELECT * FROM emp WHERE sal>1000 AND ename LIKE 'A%';
--查询工资大于1000,或者职位为CLERK的雇员信息
SELECT * FROM emp WHERE sal>1000 OR job='CLERK';
-- 查询职位不是CLERK的雇员信息
SELECT * FROM emp WHERE job!='CLERK';
SELECT * FROM emp WHERE NOT job='CLERK';
运行符的优先级
可以通过添加括号来改变运算符的优先级
| 优先级 | 运算符号 |
|---|---|
| 1 | 算数运算符 |
| 2 | 连接符 |
| 3 | 比较符 |
| 4 | IS(NOT) NULL,LIKE .(NOT) IN |
| 5 | (NOT) BETWEEN |
| 6 | NOT |
| 7 | AND |
| 8 | OR |
查询排序
格式：ORDER BY 子句
ASE ：升序（默认）
DESC ：降序
格式：SELECT 查询字段 FROM 表名 （WHERE 条件） ORDER BY 排序字段 DESC/ASC;
实例：
-- 查询排序
-- 默认排序为升序，ASC 升序,DESC降序
-- 按照薪资排序
SELECT * FROM emp ORDER BY sal desc;
SELECT * FROM emp ORDER BY sal ASC;
-- 查询所有雇员信息，并按照雇佣日期进行排序(日期型的数据排序)
SELECT * FROM emp ORDER BY hiredate desc;
SELECT * FROM emp ORDER BY hiredate ASC;
-- 计算所有雇员的年薪并排序（根据别名yearSal排序）
SELECT empno,ename,sal,sal * 12 yearSal FROM emp ORDER BY yearSal;
-- 计算所有雇员的年薪并排序，若年薪相同，则按照年龄进行排序
SELECT empno,ename,sal,sal * 12 yearSal FROM emp ORDER BY yearSal,ename;
-- 对20部门的员工按照薪资进行排序（先过滤后排序）
SELECT * FROM emp WHERE deptno=20 ORDER BY sal desc;
SELECT 查询字段 FROM 表名 WHERE 条件 ORDER BY 排序字段 DESC/ASC
课后小练
-- 1、选择部门30中的所有员工。本操作只需要一个判断条件，针对于deptno字段判断。
SELECT * FROM emp WHERE deptno IN(30);
-- 2、列出所有办事员（CLERK）的姓名，编号和部门编号。
SELECT ename,empno,deptno,job FROM emp WHERE job = 'CLERK' ;
-- 3、找出佣金高于薪金的60%的员工。 SELECT * FROM emp WHERE comm > sal * 0.6;
SELECT * FROM emp WHERE comm > sal * 0.6;
-- 4、找出部门10中所有经理（MANAGER）和部门20中所有办事员（CLERK）的详细资料。
/*
·条件一：部门10中所有经理，deptno=10 AND job='MANAGER'；
·条件二：部门20中所有办事员，deptno=20 AND job='CLERK'。
·两个条件的连接使用OR连接。
*/
SELECT * FROM emp WHERE job = 'MANAGER' AND deptno = 10 OR job = 'CLERK' AND deptno = 20;
-- 5、找出部门10中所有经理（MANAGER），部门20中所有办事员（CLERK），既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料。
/*
·条件一：部门10中所有经理，deptno=10 AND job='MANAGER'；
·条件二：部门20中所有办事员，deptno=20 AND job='CLERK'。
·条件三：既不是经理又不是办事员但其薪金大于或等于2000，job NOT IN('MANAGER','CLERK') AND sal>=2000
*/
SELECT * FROM emp WHERE job = 'MANAGER' AND deptno = 10 OR job = 'CLERK' AND deptno = 20 OR job NOT IN('MANAGER' , 'CLERK') AND sal >= 2000;
-- 6、找出收取佣金的员工的不同工作。
/*佣金上存在有null数据，所以现在需要使用IS NOT NULL来处理领取佣金。而且工作一定会存在有重复，要使用DISTINCT消除重复行。*/
SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL;
-- 7、找出不收取佣金或收取的佣金低于100的员工。
SELECT * FROM emp WHERE comm IS NULL OR comm < 100;
-- 8、显示不带有“R”的员工的姓名。
SELECT ename FROM emp WHERE ename NOT LIKE '%R%';
-- 9、显示姓名字段的任何位置包含“A”的所有员工的姓名，显示的结果按照基本工资由高到低排序，
-- 如果基本工资相同，则按照雇佣年限由早到晚排序，如果雇佣日期相同，则按照职位排序
SELECT ename,sal,hiredate FROM emp WHERE ename LIKE '%A%' ORDER BY sal desc,hiredate,job;
单行函数
分类
- 特征
- 操作数据对象
- 接收参数返回一个结果
- 指对一行进行变换
- 每行返回一个结果
- 可以转换数据类型
- 可以嵌套
- 参数可以是一列或一个值
- 分类
- 字符
- 数值
- 日期
- 转换
- 通用
字符函数
- 字符函数
- 大小写控制函数
- LOWER
- UPPER
- INITCAP
- 字符控制函数
- CONCAT
- SUBSTB
- LENGTH
- INSTR
- LPAD | RPAD
- RTIN
- REPLACE
- 大小写控制函数
1. 大小写控制函数：
- Lower：将所有字母转换为小写
- Upper：将所有字母转换为大写
- INITCAP：首字母大写
实例：
-- 将小写字母转换为大写，dual是Oracle的假表
SELECT UPPER('abcde') FROM dual;
-- 查询emp表中的smith initcap:首字母大写
SELECT * FROM emp WHERE ename = 'SMITH';
select * FROM emp WHERE lower(ename) = 'smith';
select lower(ename) from emp;
select initcap(ename) from emp;
2. 字符控制函数
- concat：字符串拼接
- substr：字符串截取（从1开始）
- length：长度
- instr：字符第一次出现的索引
- lpad：长度补全，不足左边补指定符号
- rpad：长度补全，不足右边补指定符号
- trim：去除字符串左右的空格（中间的不能）
- replace：替换
实例：(dual为Orlect中的一个伪表)
select concat('Hello ','World') from dual; -- 字符串拼接
select substr('Hellojava',2,4) from dual; -- 字符串截取
select length('Hellojava') from dual; -- 字符串长度
select instr('Hellojava','a') from dual; -- 字符第一次出现的索引
select lpad('sal',10,'-') from emp; -- 字符串补齐，补在左边
select rpad('sal',10,'+') from emp; -- 字符串补齐，补在右边
select trim(' he ll o world ') from dual; -- 去除字符串左右的空格
select replace('Hellojava',1,'h'); -- 替换
数字函数
- ROUND：四舍五入
- TRUNC：截断
- MOD：取余
select round(11.11) from dual; -- 四舍五入，不保留小数 11
select round(11.11,2) from dual; -- 四舍五入，保留两位 11.11
select trunc(11.59,1) from dual; -- 只保留需要的位数，不四舍五入 11.5
select mod(10,3) from dual; -- 取余 1
日期函数
首先设置时间格式：
Oracle提供了很多的日期时间相关函数，可以对日期进行加减操作
操作规律：
- 日期 – 数字 = 日期
- 日期 + 数字 = 日期
- 日期 – 日期 = 数字
select sysdate from dual;--查询系统的当前时间
select ename,round((sysdate - hiredate)/7) week from emp; -- 计算员工工作的周数
运行结果：
其他方法：
select ename,round(months_between(sysdate,hiredate)) months from emp; -- 查看员工入职月数
select add_months(sysdate,2) from dual; -- 指定日期加上数月 06-4月-21 22:42:45
select last_day(sysdate) from dual; -- 本月最后一天 28-2月-21 22:43:09
转换函数
- 隐性转换：自动完成
显式转换：
- 日期到字符（TO_CHAR 函数）
格式（TO_CHAR(date, ‘format_model’) ）：
• 必须包含在单引号中，而且大小写敏感。
• 可以包含任意的有效的日期格式。
• 日期之间用逗号隔开。
1.1 SELECT TO_CHAR(sysdate,‘yyyy-mm-dd hh:mi:ss’) FROM dual;
时间格式：
使用双引号向日期中添加字符：
-- 获取系统日期，并按照指定格式显示
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
select to_char(sysdate,'yyyy--mm--dd hh24:mi:ss') from dual; -- hh24 表示小时使用24小时格式
运行结果：
-- 查询所有雇员的信息，并将日期格式按照指定格式输出
select ename,job,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;
-- 查询1980年之后入职的员工
select ename,hiredate from emp where to_char(hiredate,'yyyy-mm-dd hh24:mi:ss')>'1980-1-1 00:00:00';
运行结果：
- 字符串到日期（to_date）
-- 将字符串类型转换为日期
SELECT to_date('2021-02-01','yyyy-mm-dd') from dual;
- 数字到字符（TO_CHAR）
TO_CHAR(number,'fermat_model)
TO_CHAR 函数中常用的格式：
-- 数值到字符的转换
SELECT ename,to_char(sal,'$999,999,999.00') FROM emp;
运行结果：
- 字符串转换为数字
-- 字符串转换为数字
SELECT to_number('$1131.34','$999999999.00') from dual;
通用函数
（这些函数适用于任何数据类型，同时也适用于空值）
- NVL (expr1, expr2) 将空值转换为一个数值
- NVL2 (expr1, expr2, expr3)
- NULLIF (expr1, expr2)
- COALESCE (expr1, expr2, …, exprn)
-
NVL 将空值（null）转换为一个数值，转换的对象可以是数值，字符，日期
NVL (expr1, expr2)
-- NVL 函数 计算员工的年薪，并加comm,没有comm的，加100元
SELECT empno,ename,sal*12 + NVL(comm,100) yearSal FROM emp;
-- 对于没有MGR的员工 统一由7701管理
SELECT empno,ename,NVL(mgr,7701) FROM emp;
-
NVL2
使用 NVL2 函数：
NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。
NVL2 (expr1, expr2, expr3)
-- 查询员工的姓名、薪资、奖金，如果奖金不为空，则此时显示年薪（包含奖金），如果计算得到的年薪为空(若奖金为null,则计算的年薪为null),则此时直接奖励10000
SELECT ename,sal,nvl2(comm,(sal *12 +comm),10000) from emp;
-
NULLIF
NULLIF (expr1, expr2)
NULLIF (expr1, expr2) : 相等返回NULL，不相等返回expr1
-- 查询员工的姓名和职位的名称的长度，如果长度相同，则结果返回null，否则返回员工姓名的长度
SELECT ename,LENGTH(ename)"expr1",
job, LENGTH(job) "expr2",
NULLIF(LENGTH(ename), LENGTH(job)) result
FROM emp;
- 使用 COALESCE 函数
- COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。
- 如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE
- 作用和用法类似于NVL2
-- 若comm为空，则返回(sal*12+comm)，若其也为空，则返回10000
SELECT ename,sal,coalesce(comm,(sal*12+comm),10000) FROM emp;
条件表达式
在 SQL 语句中使用IF-THEN-ELSE 逻辑
使用两种方法：
- CASE 表达式
- DECODE 函数
CASE 表达式
练习：查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数
-- 查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数
SELECT ename,job,deptno,sal,
case deptno when 10 then sal*1.1
when 20 then sal*1.2
when 30 then sal*1.3
ELSE sal
end "salary" -- 表示新字段的名称，必须用双引号
FROM emp;
DECODE 函数
decode() 函数的语法：
- select decode（columnname，值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值） From talbename Where
--Decode实现
SELECT ename,job,deptno,sal,
DECODE(deptno,10,sal * 1.1,20,sal * 1.2,30,sal * 1.3,sal) salary FROM emp;
-- 使用Decode 将员工的job显示为中文
SELECT ename,job,
DECODE(job,'CLERK','店员','SALESMAN','销售员','MANAGER','经理',job) job1
from emp;
函数的嵌套使用
函数里可以是另一个函数处理的结果。
查询雇员中没有领导的雇员信息，如果该雇员没有经理，则将manager_id 修改为’No Manager’
-- 查询雇员中没有领导的雇员信息，如果该雇员没有经理，则将manager_id 修改为'No Manager'
SELECT ename,job,mgr,NVL(to_char(mgr),'No Manager') mgr_1 from emp;
多表查询
-- 查询员工信息及其所在的部门信息
SELECT * FROM emp,dept; -- 56条记录 14*4=56 笛卡尔积
SELECT * FROM emp; -- 14条记录
SELECT * FROM dept; -- 4条记录
由于两张表在关联查询的时候，没有关联条件，则此时的结果为主表中的每一条记录与从表中的每一条记录进行关联。
结果为：主表的记录数 * 从表的记录数 （结果为笛卡尔积）
产生笛卡尔积的原因：
- 没有表的关联条件
- 关联条件无效
- 所有表中的所有记录都相互关联
解决方法：查询中引入有效的关联条件
Oracle 连接
多表关联查询
要求主表与从表有关联字段
-- 等值连接
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;
表中的关联字段：
在表设计时，主键要求唯一且不为空
-- 在多表查询的时候，可以给表起一个别名
SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno;
-- 查询特定的字段 当查询的字段在主表和从表中都存在时，
-- 此时需要明确的指定字段的来源 通过表的别名来指定
SELECT ename,job,sal,d.deptno,dname,loc FROM emp e,dept d WHERE e.deptno=d.deptno;
非等值链接
-- 查询员工的等级 emp表与salgrade表
SELECT * FROM salgrade;
-- 员工工资等级
SELECT * FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;
同表的关联查询（自连接）
一个表中的列存在某种关联，将表起两个别名，当成两个表分别使用
-- 查询每个雇员的信息及其领导的信息 e1 为雇员表 e2 为领导信息表
-- mgr 员工直属领导编号 empno 员工编号
-- 输出依次为：员工编号，员工姓名，员工直属领导编号，对应的领导编号，对应的领导姓名
SELECT e1.empno,e1.ename,e1.mgr,e2.empno,e2.ename FROM emp e1,emp e2 WHERE e1.mgr=e2.empno;
外连接（左外连接与右外连接）
- 左外连接：
-- 左外连接
-- 查询所有的部门及其部门下的雇员,此时我们要求显示所有部门,但是由于40部门下没有员工
-- 因此40部门的信息是看不到的 此时的结果就和要求不匹配
SELECT * FROM dept d,emp e WHERE d.deptno=e.deptno;
-- 要实现上述需求，即 即使部门没有雇员，部门仍需显示
SELECT * FROM dept d,emp e WHERE e.deptno(+)=d.deptno;
- 右外连接：
-- 右外连接(emp为主表，因此部门无法显示全)
SELECT * FROM dept d,emp e WHERE e.deptno=d.deptno(+);
- 使用（+）表示外连接，（+）出现在关联条件的等号的左边时，称为左外连接；出现在关联条件的等号的右边时，称为右外连接。
- 左外连接以右表为主表，左表为从表
- 右外连接以左边为主表，右表为从表
- 主表信息全部显示，用从表去关联主表，如果没有关联记录，则用空记录进行补全
- 使用（+）表示外连接的语法，属于Oracle的特有语法
练习：
- 要求：查询出每个员工的编号、姓名、部门名称、工资等级，并且查询出他的上级领导的姓名与工资等级，同时工资等级要求显示为“一级”、“二级”、“三级”、“四级”
- 分析：
- 数据来源：员工相关信息 emp表 dept 表 salgrade表
- 上级领导：emp表 salgrade表
-- 查询出每个员工的编号 姓名 部门名称 工资等级 并且查询出他的上级领导的姓名和工资等级
SELECT
e.empno,e.ename,d.dname,
DECODE(s.grade,
1,'一级',
2,'二级',
3,'三级',
4,'四级',
5,'五级') grade,
e1.ename,
DECODE(s1.grade,
1,'一级',
2,'二级',
3,'三级',
4,'四级',
5,'五级') leadergrade
FROM
emp e,dept d,emp e1,salgrade s,salgrade s1
WHERE
e.deptno=d.deptno
AND
e.sal BETWEEN s.losal AND s.hisal
AND
e.mgr = e1.empno
AND
e1.sal BETWEEN s1.losal AND s1.hisal
SQL：1999连接语法
- 叉集
--叉集(笛卡儿积)
SELECT * FROM emp CROSS JOIN dept;
共56条记录
-
自然连接（等值连接）
NATURAL JOIN 使用两张表中的公共字段作为条件,实现等值连接
-- 自然连接（等值连接）
SELECT * FROM emp NATURAL JOIN dept;
共14条记录
- 使用using子句
作用和自然连接相同，只是自然链接的关联字段是自动判断， USING 是手动指定关联字段
SELECT * FROM emp e JOIN dept d USING(deptno);
14条记录
- ON 子句
-- ON 子句
SELECT * FROM emp e JOIN dept d ON(e.deptno=d.deptno);
14条记录
- 外连接
- 左外连接： LEFT OUTER JOIN ON
-- 左外连接(14条记录)
SELECT * FROM emp e LEFT OUTER JOIN dept d ON(e.deptno=d.deptno);
14条记录
- 右外连接： RIGHT OUTER JOIN ON
-- 右外连接(15条记录)
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON(e.deptno=d.deptno);
15条记录
- 满外连接
-- 满外连接(15条记录)
SELECT * FROM emp e FULL OUTER JOIN dept d ON(e.deptno=d.deptno);
15条记录
分组函数
分组函数作用于一组数据，并对一组数据返回一个值
多行函数（聚合函数）
忽略空值
-
统计表中的记录数（count）
适用于任意数据类型
-- 查询EMP表中总共由多少条记录 count (* 为通配符)
SELECT count(*) FROM emp;
--count函数中 不推荐使用* 使用* 会影响查询的性能,使用列名
SELECT count(empno) FROM emp;
DISTINCT关键字：COUNT(DISTINCT expr) 返回expr 非空且不重复的记录数据
-
最值（最大值max与最小值min）
对任意数据类型的数据使用
-- 查询员工最低工资
SELECT min(sal) FROM emp;
-- 查询员工最高工资
SELECT max(sal) maxSal FROM emp;
-- 查询入职时间最早的员工（入职时间最小）
SELECT min(hiredate) FROM emp;
-
平均值（avg）
对数值型数据使用
-- 查询员工的平均工资
SELECT avg(sal) FROM emp;
-- 查询30部门的平均工资
SELECT avg(sal) FROM emp WHERE deptno=30;
- 求和（sum）
-- 查询所有员工的工资总和
SELECT sum(sal) FROM emp;
-- 查询30部门的工资总和
SELECT sum(sal) FROM emp WHERE deptno=30;
分组查询
GROUP BY 分组查询子句
WHERE 一定要放在 FROM 的后面
包含在GROUP BY 子句中的列不必包含在 SELSECT 列表中
SELECT *|列名 FROM 表名 （WHERE 查询条件） (GROUP BY 分组字段) ORDER BY 排序字段 ASC|DESC
-- 统计每个部门的人数
SELECT deptno,count(empno) FROM emp GROUP BY deptno;
-- 统计每个部门的平均工资
SELECT deptno,avg(sal) FROM emp GROUP BY deptno;
-
在排序中，如果select后面出现了分组字段（聚合函数），如果还包括其他的字段，那么该字段要么为分组字段，要么是其他聚合函数的参数，否则不能单独出现
-
如果使用了分组函数，GROUP BY 不是只可以查询分组函数的值
-
使用多个列分组
-- 按部门分组，查询出部门名称和部门员工的数量
SELECT d.deptno,dname,count(empno) FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.deptno,dname;
HAVING子句
对分组之后的数据进行过滤（分组之前用WHERE）
-- having子句
-- 查询出部门数量大于等于5的部门
-- dept emp count() 此时的结果我们需要先分组然后在过滤
SELECT d.deptno,dname,count(empno) FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.deptno,dname
HAVING count(empno)>=5;
-- 查询出平均工资大于2000的部门 分组字段可以出现在select之后 也可以不出现
select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000;
- 注：不能在WHERE子句中使用组函数，可以在HAVING子句中使用组函数
分析函数
与集合函数区别：
- 聚合函数每组只返回一行
- 分析函数每组返回多行
子查询
概述
- 子查询是在主查询之前执行的查询
- 子查询的结果是为主查询服务的
格式：SELECT * FROM 表名 WHERE 过滤字段 关系运算符 （SELECT 列 FROM 表名）
子查询包含在括号内
子查询往往作为主查询比较的条件
单行操作符对应单行子查询，多行操作符对应多行子查询
单行子查询
子查询的结果为一条记录
-- 查询出所有工资比WARD薪资高的所有人
SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='WARD');
-- 查询出job与7566相同,且sal比7499大的员工的姓名、职位、薪资
SELECT ename,job,sal FROM emp
WHERE job=(SELECT job FROM emp WHERE empno=7566)
AND sal>(SELECT sal FROM emp WHERE empno=7499);
- 在子查询中使用分组函数
-- 查询出所有员工中工资最低的员工的姓名、职位、薪资
SELECT ename,job,sal
FROM emp
WHERE sal=(SELECT min(sal) FROM emp);
- 在子查询中进行分组过滤
-- 查询最低工资大于20号部门最低工资 的部门id和最低工资
SELECT deptno,min(sal)
FROM emp
GROUP BY deptno
HAVING min(sal)>(SELECT min(sal) FROM emp WHERE deptno=20);
在使用子查询的时候，子查询的结果必须是单行记录，然后再根据要求来进行结果的匹配
如果返回多条记录，程序运行会出错
当子查询到的记录为空时，运行不会出错，不返回任何行
-- 查询出所有员工中工资最低的员工的姓名、职位、薪资
SELECT ename,job,sal
FROM emp
WHERE sal=(SELECT sal FROM emp WHERE empno=11111);
多行子查询
多行子查询的结果为多行，需要使用多行操作符。
| 操作符 | 说明 |
|---|---|
| IN | 等于其中的任意一个 |
| ANY | 和子查询返回的结果中的任意一个比较 |
| ALL | 和子查询中的所有的结果进行比较 |
ANY的使用：
-- 查询其他职位中比job为"SALESMAN"的 任意一个员工的工资低的员工的姓名、职位、薪资
SELECT ename,job,sal FROM emp
WHERE job<>'SALESMAN'
AND sal<ANY(SELECT min(sal) FROM emp WHERE job='SALESMAN');
ALL的使用：
-- 查询其他职位中比job为“SALESMAN”的所有员工工资低的员工的姓名 职位 薪资
SELECT ename,job,sal FROM emp
WHERE job!='SALESMAN'
AND sal<ALL(SELECT min(sal) FROM emp WHERE job='SALESMAN')
分页查询
数据伪列：列不存在，但是可以直接使用的列，称为伪列。
Oracle 中的伪列：sysdate rownum rowid
- rownum 的使用
-- Rownum的使用
SELECT rownum,empno,ename,job FROM emp WHERE job='SALESMAN';
-- 查询emp表中的第一条记录
SELECT rownum,empno,ename,job FROM emp WHERE rownum=1;
-- 查询emp表中的前五条记录
SELECT rownum,empno,ename,job FROM emp WHERE rownum<=5;
将15条记录，每页显示5条，可以分为三页
第一页：1~5
-- 显示第一页数据：1~5
SELECT * FROM
(SELECT empno,ename,job,hiredate,sal,rownum rn FROM emp WHERE rownum<=5) temp
WHERE temp.rn>0;
第二页：6~10
-- 显示第二页数据：6~10
SELECT * FROM
（SELECT empno,ename,job,hiredate,sal,rownum rn FROM emp WHERE rownum<=10） temp
WHERE temp.rn>5;
第三页：11~15
-- 显示第三页数据：11~15
SELECT * FROM
(SELECT empno,ename,job,hiredate,sal,rownum rn FROM emp WHERE rownum<=15) temp
WHERE temp.rn>10;
其他写法：
-
当前页数：currentPage
-
每页显示的条数：pages
-
起始条数：（CurrentPage-1）*pages
-
结束的条数：（CurrentPage-1）*pages+pages
-- 其他写法
SELECT * FROM (SELECT rownum r,emp.* FROM emp) b
WHERE b.r>5 AND b.r<11;
1295
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
