时间：2021-07-01 10:21:17 帮助过：8人阅读
第1章...............................................................................................................................
概述...................................................................................................................................
1. BI介绍..................................................................................................................
2. Oracle介绍与安装................................................................................................
2.1 Oracle安装..................................................................................................
2.2 Oracle 默认用户..........................................................................................
2.3 服务器连接...................................................................................................
3. PL/SQL Developer工具.........................................................................................
4. Oracle用户和权限................................................................................................
4.1用户和权限....................................................................................................
4.2角色..............................................................................................................
第2章...............................................................................................................................
SQL数据操作和查询...........................................................................................................
1. SQL简介................................................................................................................
2. 查询......................................................................................................................
2.1查询结构.......................................................................................................
2.2查询顺序........................................................ 3
2.3聚合函数........................................................ 3
3. 创建表和约束........................................................................................................
3.1 Oracle常用数据类型....................................................................................
3.2创建表...........................................................................................................
3.3表的约束.......................................................................................................
4. 数据操作语言（DML）............................................................................................
4.1数据插入.......................................................................................................
4.2更新数据.......................................................................................................
4.3删除数据.......................................................................................................
4.4 TRUNCATE（DDL命令）..................................................................................
5. 操作符...................................................................................................................
5.1算术运算.......................................................................................................
5.2关系运算和逻辑运算......................................................................................
5.3字符串连接操作符（||）...............................................................................
6. 高级查询...............................................................................................................
6.1 DISTINCT操作...............................................................................................
6.2 NULL操作......................................................................................................
6.3 IN 操作........................................................................................................
6.4 BETWEEN…AND…............................................................................................
6.5 LIKE模糊查询...............................................................................................
6.6 集合运算.......................................................................................................
6.7 连接查询.......................................................................................................
第3章...............................................................................................................................
子查询和常用函数.............................................................................................................
1. 子查询...................................................................................................................
2. Oracle中的伪列....................................................................................................
2.1 ROWID............................................................................................................
2.2 ROWNUM..........................................................................................................
3. Oracle单行函数....................................................................................................
3.1字符函数.......................................................................................................
3.2数字函数.......................................................................................................
3.3日期函数.......................................................................................................
3.4转换函数.......................................................................................................
3.5其他常用函数.................................................................................................
4. Oracle分析函数....................................................................................................
4.1 分析函数介绍................................................................................................
4.2 分析函数种类和用法.....................................................................................
4.3 行列转换.......................................................................................................
第4章...............................................................................................................................
表空间、数据库对象..........................................................................................................
1.同义词.....................................................................................................................
2.序列........................................................................................................................
3.视图........................................................................................................................
4.索引........................................................................................................................
4.1 索引介绍.......................................................................................................
4.2 索引种类.......................................................................................................
4.3 索引优缺点....................................................................................................
4.4 索引失效.......................................................................................................
5.表空间.....................................................................................................................
6.表分区.....................................................................................................................
6.1表空间及分区表的概念...................................................................................
6.2表分区的具体作用..........................................................................................
6.3.表分区的优缺点..............................................................................................
6.4表分区的几种类型及操作方法.........................................................................
6.5有关表分区的一些维护性操作.........................................................................
第5章...............................................................................................................................
PL/SQL程序设计................................................................................................................
1. PL/SQL简介...........................................................................................................
2. PL/SQL基础...........................................................................................................
2.1声明..............................................................................................................
2.2条件控制.......................................................................................................
2.3循环控制.......................................................................................................
2.4游标..............................................................................................................
2.5动态SQL........................................................................................................
3.创建存储过程..........................................................................................................
4.创建自定义函数.......................................................................................................
5.异常处理.................................................................................................................
6.同步数据.................................................................................................................
7.创建包.....................................................................................................................
8.创建日志.................................................................................................................
第6章...............................................................................................................................
BI理论基础.......................................................................................................................
1.数据仓库..................................................................................................................
2.维表与事实表...........................................................................................................
3.三范式.....................................................................................................................
4.星型模型与雪花模型................................................................................................
5.代理主键/业务主键...................................................................................................
6.保存历史数据的方法................................................................................................
7. 关联机制................................................................................................................
8.HINTS.......................................................................................................................
9.Shell介绍.................................................................................................................
10.项目经验与流程......................................................................................................
11.工具介绍................................................................................................................
12.面试题....................................................................................................................
1. BI介绍
商业智能（BI，Business Intelligence）。商业智能的概念最早在1996年提出。当时将商业智能定义为一类由数据仓库（或数据集市）、查询报表、数据分析、数据挖掘、数据备份和恢复等部分组成的、以帮助企业决策为目的技术及其应用。
目前，商业智能通常被理解为将企业中现有的数据转化为知识，帮助企业做出明智的业务经营
客户和供应商信息等，以及企业所处行业和竞争对手的数据、其他外部环境数据。而这些数据可能来自企业的CRM、SCM等业务系统。
商业智能的关键是从许多来自不同的企业运作系统的数据中提取出有用的数据并进行清理，以保证数据的正确性，然后经过抽取（Extraction）、转换（Transformation）和装载（Load），即ETL过程，合并到一个企业级的数据仓库里，从而得到企业数据的一个全局视图，在此基础上利用合适的查询和分析工具、数据挖掘工具、OLAP工具等对其进行分析和处理（这时信息变为辅助决策的知识），最后将知识呈现给管理者，为管理者的决策过程提供支持。
»源系统也是数据库，可以做简单统计，若要做复杂统计则需要与其他源系统关联多表，为了避免源系统之间的频繁交互，因此需要建立数据仓库。
»中间层：根据公共逻辑，对需要被重复关联的表提前做一些关联
»为什么会有数据集市？因为每个部门关心不一样。用于支撑报表。
»报表用于帮助企业决策
2. Oracle介绍与安装
Oracle公司是全球最大的信息管理软件及服务供应商，成立于1997年，主要的业务是推动电子商务平台的搭建，Oracle公司有自己的服务器、数据库、开发工具、编程语言，在行业软件上还有企业资源计划（ERP）软件、客户关系管理（CRM）软件、人力资源管理（HCM）软件等大型管理系统。Oracle是一家综合性的国际大公司，也是最有实力与微软公司在技术上一较高低的公司之一。
数据库创建完毕后，需要设置数据库的默认用户。Oracle中为管理员预置了两个用户分别是SYS和SYSTEM。同时Oracle为程序测试提供了一个普通用户scott，初始密码为TIGER，口令管理中，可以对数据库用户设置密码，设置是否锁定。 Oracle客户端使用用户名和密码登录Oracle系统后才能对数据库操作。
默认的用户中，SYS和SYSTEM用户是没有锁定的，安装成功后可以直接使用，SCOTT用户默认为锁定状态，因此不能直接使用，需要把SCOTT用户设定为非锁定状态才能正常使用。
SCOTT用户自带的四张表：EMP(员工)、DEPT(部门)、SALGRADE(工资等级)、BONUS(奖金)
客户端工具可以根据“服务器连接字符串”对服务器进行连接，有了连接字符串后客户端就可以像操作本机一样操作远程数据库，因此“服务器连接字符串”的配置也叫本地网络服务配置，该配置文件在Oracle安装目录下的 D:\app\xlovely\product\11.2.0\dbhome_1network/ADMIN/ tnsnames.ora。该文件是一个文本文件，用记事本打开后如下所示：
3. PL/SQL Developer工具
在实际Oracle开发中，经常使用一个功能强大的第三方工具：“PL/SQL Developer”工具。PL/SQL Developer基本上可以实现Oracle开发中的任何操作。它运行在客户端时必须先安装Oracle客户端，并且通过网络配置向导配置网络服务名后才能正常与服务器连接。
图20 PL/SQL Developer
4. Oracle用户和权限
Oracle中，一般不会轻易在一个服务器上创建多个数据库，在一个数据库中，不同的项目由不同的用户访问，每一个用户拥有自身创建的数据库对象，因此用户的概念在Oracle中非常重要。Oracle的用户可以用CREATE USER命令来创建。其语法是：
语法结构：创建用户
|
CREATE USER 用户名 IDENTIFIED BY 口令 [ACCOUNT LOCK|UNLOCK] |
语法解析：
LOCK|UNLOCK创建用户时是否锁定，默认为锁定状态。锁定的用户无法正常的登录进行数据库操作。
代码演示：创建用户
|
CREATE USER jerry IDENTIFIED BY tom [ACCOUNT UNLOCK] |
尽管用户成功创建，但是还不能正常的登录Oracle数据库系统，因为该用户还没有任何权限。如果用户能够正常登录，至少需要CREATE SESSION系统权限。
Oracle用户对数据库管理或对象操作的权利，分为系统权限和数据库对象权限。系统权限比如：CREATE SESSION，CREATE TABLE等，拥有系统权限的用户，允许拥有相应的系统操作。数据库对象权限，比如对表中的数据进行增删改操作等，拥有数据库对象权限的用户可以对所拥有的对象进行对应的操作。
还有一个概念就是数据库角色（role），数据库角色就是若干个系统权限的集合。下面介绍几个常用角色：
一般情况下，一个普通的用户（如SCOTT），拥有CONNECT和RESOURCE两个角色即可进行常规的数据库开发工作。
可以把某个权限授予某个角色，可以把权限、角色授予某个用户。系统权限只能由DBA用户授权，对象权限由拥有该对象的用户授权，授权语法是：
语法结构：授权
|
GRANT 角色|权限 TO 用户（角色） |
代码演示：授权
|
GRANT CONNECT TO jerry; GRANT CREATE TABLE TO CONNECT; |
语法结构：其他操作
|
//回收权限 REVOKE 角色|权限 FROM 用户（角色） //修改用户的密码 ALTER USER 用户名 IDENTIFIED BY 新密码 //修改用户处于锁定（非锁定）状态 ALTER USER 用户名 ACCOUNT LOCK|UNLOCK |
|
Oracle数据库中，默认情况下，所有系统的数据，SQL关键字等都是大写的，在操作过程中，Oracle会自动把这些内容转换为大写，因此用户操作时不需考虑大小写问题，一般情况下，为了良好的程序风格，程序中建议关键字用大写，非关键字可以使用小写。 |
表是数据库的一个对象,是数据库的重要组成部分,主要用来存储数据并定义数据之间的关系.在关系数据库中，数据库表是一系列二维数组的集合。它由纵向的列和横向的行组成,在表结构中列又称为字段.
表结构就是定义一个表的名称,字段、类型、主键、外键、索引，这些基本的属性组成了数据库的表结构,其中表的名称,字段,字段数据类型是必须组件,其它是可选组件
如上图,查询员工表返回的数据,其中纵轴每行数据表示一位员工,横轴每列(字段)依次表示 员工编号, 员工姓名,工作,所属领导员工编号,入职日期,工资,绩效,部门编号,最近更新日期
注意:大部分时候,可以认为列是对每行数据的描述,所以有些项目会将列称为 属性.
员工表表结构基本信息:
统计粒度：数据库中数据的细化和综合程度。细化程度越高，粒度越小。
粒度用来唯一定义区分表中的一行数据,比如上面的EMP表每行数据代表一位员工,那么该表的粒度就是员工;同理dept表的粒度是部门;
|
类型 |
含义 |
|
CHAR(length) |
。。固定长度的字符串。参数length必须是数字,指定了长度，如果存储的字符串长度小于length，用空格填充。默认长度是1，最长不超过2000字节。 |
|
VARCHAR2(length) |
存储可变长度的字符串。length必须是数字,指定了该字符串的最大长度。默认长度是1，最长不超过4000字符。 |
|
NUMBER(p，s) |
既可以存储浮点数(这里可以理解为小数)，也可以存储整数，p表示数字的最大位数（如果是小数包括整数部分和小数部分，p默认是38位），s是指小数位数。注意p,s必须是数字 |
|
DATE |
存储日期和时间，存储纪元、4位年、月、日、时、分、秒，存储时间从公元前4712年1月1日到公元后4712年12月31日。 |
表1 Oracle的部分数据类型
各种数据类型的表达形式：
字符型用单引号加字符表示，例如，’ABC’
数字型直接用阿拉伯数字表示，例如，123
日期型不能直接表示，必须使用函数转换，
例如，DATE’2016-12-31’，TO_DATE(‘20161231201237’,’YYYYMMDD HH24:MI:SS’)
各种数据类型的不同特点：
字符型可以进行拼接，
SELECT e.ename || ‘ 的工作是: ‘ || e.job FROM SCOTT.EMP e;
数字型可以进行算术运算
日期型也能进行算术运算，但是只能日期减日期，或者日期加减数字
各种数据类型比较大小的方式：
数字型比较数值的大小
日期型也是直接比较大小，越早的时间越小
字符型比较第一个字符的ASCII码的大小
数据库中的数据是以表的形式存储的，每一个表都被一个模式（或用户）所拥有，因此表是一种最基本的数据库模式对象。创建表时，Oracle在一个指定的表空间中为表分配存储空间。
Oracle创建表同SQL Server一样，使用CREATE TABLE命令来完成。可以直接创建表，也可以根据结果集创建表。
语法结构：直接创建表
|
CREATE TABLE 表名 (列名 数据类型 [, 列名 数据类型]... ) |
语法结构：根据结果集创建表
|
CREATE TABLE 表名 AS SELECT语句 |
使用上面命令创建的新表中，不存在任何约束，并且把查询的数据一起插入到新表中。如果只复制表结构，只需使查询的条件不成立（比如where 1=2），就不会查询从出任何数据，从而复制一个表结构。
语法结构：复制表结构
|
CREATE TABLE 表名1 AS SELECT * FROM 表名2 WHERE 1=2; |
语法结构：删除表
|
DROP TABLE 表名 |
语法结构：添加列
|
ALTER TABLE 表名 ADD 列名 数据类型 |
语法结构：修改列类型
|
ALTER TABLE 表名 MODIFY 列名 数据类型 |
语法结构：修改列名
|
ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名 |
语法结构：删除列
|
ALTER TABLE 表名 DROP COLUMN 列名 |
语法结构：修改表名
|
ALTER TABLE 表名 RENAME TO 新表名 |
【例1】创建一个学生表(student_1),表中包括学号(sno)、姓名(sname)、性别(ssex)、年龄(sage)、出生日期(sbirthday)
|
CREATE TABLE student_1( sno VARCHAR2(10), sname VARCHAR2(30), ssex VARCHAR2(2), sage NUMBER(3), sbirthday DATE ) |
表的约束是Oracle数据库中应用在表数据上的一系列强制性规则。当向已创建的表中插入数据或修改表中的数据时，必须满足表的完整性约束所规定的条件。例如，学生的性别必须是“男”或“女”，各个学生的学号不得相同等。在设计表的结构时，应该充分考虑在表上需要施加的完整性约束。表的完整性约束既可以在创建表时制定，也可以在表创建之后再指定。可以对一个或多个字段进行约束。
null: 什么都没有
按照约束用途分类：
1.PRIMARY KEY：主键约束
2.FOREIGN KEY：外键约束
3.CHECK：检查约束
4.UNIQUE：唯一约束
5.NOT NULL：非空约束
创建约束则使用如下命令：
语法格式：ALTER TABLE命令
|
ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束内容。 |
语法格式：添加主键约束
|
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY(列名1[,列名2...]) |
语法格式：添加外键约束
|
ALTER TABLE 主表名 ADD CONSTRAINT 约束名 FOREIGN KEY(列名1[,列名2...]) REFERENCES 从表名(列名1[,列名2...]) |
语法格式：添加CHECK约束
|
ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK(条件) |
语法格式：添加唯一约束
|
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名) |
语法格式：添加非空约束
|
ALTER TABLE 表名 MODIFY 列名 NOT NULL |
语法格式：删除约束
|
ALTER TABLE 表名 DROP CONSTRAINT 约束名 |
【例1】为学生表(student_1)学号添加主键约束，姓名添加非空约束，性别添加检查约束
|
ALTER TABLE student_1 ADD CONSTRAINT PK_SNO PRIMARY KEY(sno); ALTER TABLE student_1 MODIFY sname NOT NULL; ALTER TABLE student_1 ADD CONSTRAINT CK_SNO CHECK(ssex=‘男‘ OR ssex=‘女‘); |
约束也可以在创建表的时候添加
|
CREATE TABLE student_1( sno VARCHAR2(10) PRIMARY KEY, sname VARCHAR2(30) NOT NULL, ssex VARCHAR2(2) CHECK(ssex=‘男‘ OR ssex=‘女‘)， sage NUMBER(3), sbirthday DATE ) |
1.一张表只能有一个主键，主键非空且唯一
2.外键只能依赖于另一张表的主键（例如EMP表中的DEPTNO依赖于DEPT表中的DEPTNO）
3.约束不能修改，只能删除重建
4.创建表时,没有强制要求创建约束, 约束一般在业务系统使用的比较多,在数据量比较大的环境中(数据仓库)一般很少添加约束,在此加入学习用于加深大家对关系型数据库的理解
5.以华为 数据仓库为例,一般会对表创建唯一索引(数据唯一,可以为空), 不会添加其他约束
创建ORACLE临时表，可以有两种类型的临时表：
会话级临时表
事务级临时表
1）会话级临时表：因为这这个临时表中的数据和你的当前会话有关系，当你当前SESSION不退出的情况下，临时表中的数据就还存在，而当你退出当前SESSION的时候，临时表中的数据就全部没有了，当然这个时候你如果以另外一个SESSION登陆的时候是看不到另外一个SESSION中插入到临时表中的数据的。即两个不同的SESSION所插入的数据是互不相干的。当某一个SESSION退出之后临时表中的数据就被截断（TRUNCATE TABLE，即数据清空）了。
会话级的临时表创建方法：
|
CREATE GLOBAL TEMPORARY TABLE TABLE_NAME (COL1 TYPE1,COL2 TYPE2...) ON COMMIT PRESERVE ROWS; |
举例：
|
CREATE GLOBAL TEMPORARY TABLE STUDENT (STU_ID NUMBER(5), CLASS_ID NUMBER(5), STU_NAME VARCHAR2(8), STU_MEMO VARCHAR2(200)) ON COMMIT PRESERVE ROWS ; |
2）事务级临时表：是指该临时表与事务相关，当进行事务提交或者事务回滚的时候，临时表中的数据将自行被截断，其他的内容和会话级的临时表的一致（包括退出SESSION的时候，事务级的临时表也会被自动截断）。
事务级临时表的创建方法：
|
CREATE GLOBAL TEMPORARY TABLE TABLE_NAME (COL1 TYPE1,COL2 TYPE2...) ON COMMIT DELETE ROWS; |
举例：
|
CREATE GLOBAL TEMPORARY TABLE CLASSES (CLASS_ID NUMBER(5), CLASS_NAME VARCHAR2(8), CLASS_MEMO VARCHAR2(200)) ON COMMIT DELETE ROWS ; |
3）两种临时表的异同
相同点：两种表都不能永久的保存记录。他们都是用临时表空间。
不同点：会话级别只有当会话结束临时表中的数据才会被截断，而且事务级临时表则不管是COMMIT、ROLLBACK或者是会话结束，临时表中的数据都将被截断。
4）什么时候使用临时表
把复杂的逻辑拆分开来，用临时表储存中间结果，以方便后面的逻辑处理。程序执行过程中可能需要存放一些临时的数据，这些数据在整个程序的会话过程中都需要用的等等。
SQL：结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和 程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。
在Oracle开发中，客户端把SQL语句发送给服务器，服务器对SQL语句进行编译、执行，把执行的结果返回给客户端。Oracle SQL语句由如下命令组成：
数据库模式定义语言DDL(Data Definition Language)是用于描述数据库中要存储的实体的语言,即描述对象的语言
数据操纵语言DML（Data Manipulation Language），用户通过它可以实现对数据库的基本操作。例如，对表中数据的查询、插入、删除和修改。
数据控制语言（Data Control Language）是用来设置或者更改数据库用户或角色权限的语句，这些语句包括GRANT、DENY、REVOKE等语句，在默认状态下，只有管理员,所有者等角色的成员才有权利执行数据控制语言。
事务控制语言TCL(Transaction Control Language)
数据查询语言DQL(Data QueryLanguage) 数据查询语言
注意:
用户访问数据库时，数据库中的数据是放在缓冲区中的，当前用户可以通过查询操作，浏览对数据操作的结果。如果没有提交事务，其他用户是看不到事务的修改结果的(此时修改还没有写入数据库)。
提交事务时，对数据库所做的修改便永久写入数据库。
回滚事务时，对数据库所做的修改全部撤销，数据库恢复到操作前的状态。
目前主流的数据库产品（比如：SQL Server、Oracle）都支持标准的SQL语句。数据定义语言，表的增删改操作，数据的简单查询，事务的提交和回滚，权限的授权和撤销等，Oracle与SQL Server在操作上基本一致。
3.查询
数据查询是用SELECT命令从数据库的表中提取信息。是用户通过设置某些查询条件,从表或者其他查询选取全部或者部分数据,最终以表的形式显示数据供用户浏览.是一系列操作的集合;
|
SELECT * | 列名 | 表达式 -- 决定我们要展示的列(可以是表里面的,也可以生成常量) FROM 表名 [别名] WHERE 条件 ---对表里面的每一条记录进行判断 GROUP BY 列名 HAVING 条件ORDER BY 列名 [ ASC | DESC ] |
1.FROM :组装来自不同数据源的数据
2.WHERE :基于指定的条件对记录行进行筛选
3.GROUP BY :将数据按照指定的列(可以单列或者多列)划分为多个分组(相同的列值作为一个组)
4.HAVING :将数据划分为多个分组
5.SELECT :返回目标数据--以表格形式
6.ORDER BY :对结果集进行排序
语法解析：
例如: select ename,sal from emp order by sal;
-- 返回员工表每位员工的姓名和工资;
5. GROUP BY --按列分组，与聚合函数联用。GroupBy语句从英文的字面意义上理解 就是“根据(by)一定的规则进行分组(Group)”。它的作用是通过一定的规则将一 个数据集划分成若干个小的区域(组)，然后针对若干个小区域进行数据处理。
6. HAVING----分组结果的筛选条件。
7. ORDER BY 要求在查询的结果中排序，默认是升序ASC，降序为DESC。
注意: 如果语法中包含了分组子句 group by ,那么select展示只能返回group by 分组表达式和聚合函数表达式
全表扫描.
1.在数据库中，对无索引的表进行查询一般称为全表扫描。全表扫描是数据库服务器用来搜寻表的每一条记录的过程，直到所有符合给定条件的记录返回为止。
2.oracle数据库是行存储数据库,每行的数据可以看成一个存储单元,有独立的物理地址,查询表中的数据时,数据库首先扫描每一行所有的列后才会在此基础上返回我们要展示的列
3.在对表进行扫描的时候,是逐行进行扫描,每次都会扫描整行数据并对该行的字段信息进行判断,最终返回我们想要展示的信息, 例如,如果我们想要返回emp表的员工姓名,那么在读取数据时,会读取一整行数据,而不是只读取 姓名 这一列
DUAL介绍：
dual是一张虚拟表，只有一行一列，用来构成select的语法规则。Oracle的查询中，必须使用“select 列… from 表”的完整语法，当查询单行函数的时候，from后面使用DUAL表，dual表在系统中只有一行一列，该表在输出单行函数时为了select…from的语法完整性而使用。
函数可以理解为一种规则,接受一个或者多个输入参数，并返回一个输出结果。包括单行函数、聚合函数、分析函数等。下面介绍聚合函数，单行函数和分析函数在以后的章节细述。
聚合函数：聚合函数同时可以对同一组中的多行数据进行操作，并返回一个结果。比如SUM(x)返回结果集中x列的总合。
² 聚合函数-----与group by 使用。通过group by 可以对一列或者多列进行分组(相同的值作为一组),然后将同一组的多行数据聚合成一行,聚合函数可以对其中的某一列的数据按组进行统计或者计算,如果没有group by 则默认将整张表看成一组进行统计
聚合函数同时对一组数据进行操作，返回一行结果，比如计算一组数据的总和，平均值等。
|
名称 |
作用 |
语法 |
|
AVG |
返回指定组中的平均值，空值被忽略 |
AVG(表达式), 表达式必须是数字列 |
|
SUM |
求和 |
SUM(表达式), 表达式必须是数字列 |
|
MIN、MAX |
返回指定数据的最小(大)值,如果表达式的数据类型是字符,则以首字符对应的ascii码进行排序,首字符相同则判断第二个字符,以此类推 |
MIN(表达式)、MAX(表达式) |
|
COUNT |
返回指定组中统计对象的数量,如果表达式是常量,例如 0 , 1 那么指定组有多少行,则返回对应的数值 |
COUNT(表达式) |
表 5 聚合函数
注意：1.不使用分组则把整个表作为一组
2.聚合函数永远忽略空值(null)
3.如果某条记录中有缺少的数据值(什么值都没有)，就是空值（NULL值）。空值不 是0或者空格，空值是指未赋值、未知或不可用的值。
4.上面表格中的”表达式”可以是列名,常量或者函数,大部分情况下是对某列 进行统计计算
###
count(distinct mgr):计算mgr字段中所有非空的不同值的个数;
count( mgr):计算mgr字段中所有非空的值的个数;
count(常量):默认新增一列,该列的值全部赋予默认的常量,并计算该常量的个数,相当于统计同组数据返回的行数;
count(*)与count(1)返回的结果一样,但是解析sql的时候对资源消耗count(*)大于count(1)，特别是实际的生产环境，如果一个表有500个字段，上亿条记录，count(1)和count(*)还是会有很大差别的
###
练习:
1.查询EMP表中各个部门的最高工资,最低工资,总工资,平均工资;
2.查询EMP表中各个部门的人数,count(1) , count(comm), count(sal),count(empno)
3.查询EMP表中各个工作的最高工资,最低工资,总工资,平均工资;
4.查询EMP表中各个工作的人数
5.查询EMP表中各个部门每种工作的最高工资,最低工资,总工资,平均工资;
6.查询EMP表中各个部门每种工作的从事人数;
7.求emp员工表的平均工资;
8.描述下面两行代码分别代表的业务含义,并解释返回的结果
select count( distinct mgr) from emp;
select count(1) from (select distinct mgr from emp);
【例1】查询EMP表中各部门工资大于2000的员工人数，并且按人数从高到低排列
|
SELECT E.DEPTNO, COUNT(1) CT1 FROM EMP E WHERE E.SAL > 2000 GROUP BY E.DEPTNO ORDER BY CT1 DESC |
【例2】查询EMP表中员工人数大于3的所在部门的部门编号，及对应的人数
|
SELECT E.DEPTNO, COUNT(E.DEPTNO) FROM EMP E GROUP BY E.DEPTNO HAVING COUNT(E.DEPTNO) > 3 |
2.4 单表查询步骤
数据操纵语言（DML）用于对数据库的表中数据进行添加、修改、删除和SELECT…For UPDATE(后面专门学习该查询)操作。
4.数据操作语言（DML）
数据操纵语言（DML）用于对数据库的表中数据进行添加、修改、删除和SELECT…For UPDATE(后面专门学习该查询)操作。
用INSERT命令完成对数据的插入。
语法结构：数据插入
|
INSERT INTO 表名(列名1，列名2……) VALUES (值1，值2……) |
语法解析：
列名可以省略。当省略列名时，默认是表中的所有列名，列名顺序为表定义中列的先后顺序。值的数量和顺序要与列名的数量和顺序一致。值的类型与列名的类型一致。
【例】往学生表(student_1)里插一条记录,学号(s001),姓名(SASA),性别(女),出生日期(1995.08.20)
|
INSERT INTO student_1 VALUES（‘s001‘,‘SASA‘,‘女‘,23,TO_DATE(19950820,‘YYYYMMDD‘)) |
在Oracle中，一个INSERT命令可以把一个结果集一次性插入到一张表中。使用的语句是：INSERT INTO 表 SELECT子句，如下示例：
语法结构：INSERT向表中插入一个结果集
|
INSERT INTO 表名1(列名1，列名2……) 查询结果集; |
在这种语法下，要求结果集中每一列的数据类型必须与表中的每一列的数据类型一致，结果集中的列的数量与表中的列的数量一致。
插入新的数据会对老的数据有影响吗?
Oracle在表中更新数据的语法是：
语法结构：UPDATE操作1
|
UPDATE 表名 SET 列名1=值，列名2=值…… WHERE 条件 |
--注意：WHERE 条件没下的话则是全表更新
【例】在学生表(student_1)里更改一条记录,将学号(s001)的学生性别改为男
|
UPDATE student_1 SET ssex=‘男‘ WHERE sno=‘s001‘ |
Oracle在表中删除数据的语法是：
语法结构：DELETE操作
|
DELETE FROM表名 WHERE 条件 |
【例】删除学生表(student_1)里学号为s001的记录
|
DELETE FROM student_1 WHERE sno=‘s001‘ |
在数据库操作中，TRUNCATE可以把表中的所有数据一次性全部删除，语法是：
语法结构：TRUNCATE
|
TRUNCATE TABLE 表名 |
TRUNCATE和DELETE都能把表中的数据全部删除，他们的区别是：
由于TRUNCATE命令比较危险，因此在实际开发中，TRUNCATE命令慎用。
|
Oracle默认安装中，已经创建了一个SCOTT用户，该用户下有四张表分别是：雇员表（EMP），部门表（DEPT），工资登记表和奖金表，请参考本章后面的附表。接下来很多操作都是在该用户下完成的。 |
5. 操作符
Oracle开发中，依然存在算术运算，关系运算，和逻辑运算。
Oracle中的算术运算符，只有+、-、*、/四个，其中除号(/)的结果是浮点数。求余运算只能借助函数：MOD(x,y)：返回x除以y的余数。
【例】每名员工年终奖是2000元，请显示基本工资在2000元以上的员工的月工资，年总工资。
|
SELECT E.ENAME, E.SAL, (E.SAL * 12 + 2000) FROM EMP E WHERE E.SAL > 2000; |
Oracle中Where子句经中经常见到关系运算和逻辑运算。
常见的关系运算有：
|
运算符 |
说明 |
运算符 |
说明 |
|
= |
等于 |
> |
大于 |
|
<>或者!= |
不等于 |
<= |
小于或者等于 |
|
< |
小于 |
>= |
大于或者等于 |
表3 Oracle的关系运算符
三个逻辑运算符优先级：NOT>AND>OR （括号可以改变优先级）,逻辑运算符接逻辑值(真/假)
Select * from emp where sal < 1500 or sal >= 2000 and job = ‘ANALYST’;
等价于
Select * from emp where sal < 1500 or (sal >= 2000 and job = ‘ANALYST’);
在Oracle中，字符串的连接用双竖线（||）表示。
【例】在EMP表中，查询工资在2000元以上的姓名以及工作。
|
SELECT (ENAME || ‘is a ‘ || JOB) AS "Employee Details" FROM EMP WHERE SAL > 2000; |
代码解析：
Oracle中字符串可以用单引号，存在特殊字符的时候，必须用双引号。
在Oracle查询中结果中，可能出现若干行相同的情况，那么可以使用DISTINCT消除重复行。
【例】DISTINCT消除重复行（GROUP BY性能较好）
|
SELECT DISTINCT DEPTNO FROM EMP; |
如果某条记录中有缺少的数据值，就是空值（NULL值）。空值不是0或者空格，空值是指未赋值、未知或不可用的值。任何数据类型的列都可以包括NULL值，除非该列被定义为非空或者主键。
在查询条件中NULL值用IS NULL作条件，非NULL值用 IS NOT NULL做条件。
空值的一些特性：
1、 空值跟任何值进行算术运算，得到的结果都为空值
2、 空值跟任何值进行关系运算，得到的结果都为不成立
3、 空值不参与任何聚合运算，（空值转换！！）
4、 排序的时候，空值永远是最大的
【例】查询EMP表中没有发奖金的员工。
|
SELECT * FROM EMP E WHERE COMM IS NULL; |
在Where子句中可以使用IN操作符来查询其列值在指定的列表中的行,
in 表示在一个集合内进行查询
【例】查询出工作职责是SALESMAN、PRESIDENT或者ANALYST的员工。条件有两种表示方法：
代码演示：IN操作