第05章-SQL语言基础

Oracle应用开发第05章SQL语言基础本章内容ODP-C05-01关系型数据库概述

ODP-C05-02SQL语言概述

ODP-C05-03检索数据

ODP-C05-04多表关联查询

ODP-C05-05Oracle常用系统函数

ODP-C05-06子查询

ODP-C05-07操作数据库

ODP-C05-08事务处理

本章目标1、掌握数据库操作的基本语法。2、掌握数据库的多种查询方法。3、掌握数据库的关联查询方法。4、掌握数据库的条件查询方法。5、掌握数据库查询过程中可使用的函数6、掌握数据库的子查询方法。7、掌握数据库的插入、更新和删除方法。8、掌握数据库的事务概念。9、掌握数据库的事务操作方法。第01节

关系型数据库概述知识点预览#节知识点难点重点应用1ODP-C05-01关系型数据库概述1、XML与数据库比较

2、关系型数据库与数据库管理系统

3、关系型数据库的E-R模型√√4、1-1关联关系√√5、1-N关联关系√√6、N-N关联关系√√7、关系型数据库的设计范式√1、XML与数据库比较XML语言优势：是自描述性的（所用的标记描述了数据的结构和类型，尽管缺乏语义）可交换的，能够以树型或图形结构描述的数据XML语言缺陷：较繁琐，要对它进行解析和文本转换，所以数据访问速度较慢XML语言的数据库特性：XML文件是以文档为中心的数据的集合存储特性——XML文档模式——DTD,XMLschema等查询语言特性——XQuery,XPath,XQL等编程接口特性——SAX,DOM,JDOM等XML缺乏的数据库特性：高效的存储、索引、安全机制、事务和数据一致性、多用户访问、触发器等结论：在数据量小、用户少和性能要求不太高的环境下，可以将XML文档用作数据库，但是却不适用于用户量大、数据集成度高以及性能要求高的作业环境。2、关系型数据库和数据库管理系统-1数据(Data)：描述事物的符号记录称为数据数据的种类有数字、文字、图形、图像、声音、文本等数据库（DataBase,简称DB）：存储数据的仓库数据之间存在一定的关联关系，并非完全独立，数据库是储存在计算机内的、有组织的、可共享的数据集合数据库中的数据按一定的数据模型储存和管理，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种用户共享数据库使得我们对数据的操作更加高效数据库模型层次结构模型：该模型的实质是将数据以一棵定向有序树的结构存储网状结构模型：是对层次结构模型的提升，能更灵活地描述关系关系结构模型：该模型把一些复杂的数据结构归结为简单的二元关系(即二维表格形式)；由关系结构模型组成的数据库系统被称为关系型数据库2、关系型数据库和数据库管理系统-2关系模型E.F.Codd博士在1970年提出了数据库系统的关系模型关系模型是将复杂的数据结构归结为简单的二元关系，用二维表格的形式表示实体和实体间联系的数据模型关系模型的组成由大量对象或关系组成的集合对关系可进行一系列操作的运算符可保证数据的完整性和一致性要求关系型数据库使用关系模型建立的数据库包含一组被组织为表结构（table）的相关信息的集合，表结构包括：列（Column）：又称为字段，包含类型和约束信息行（row）：又称为记录，每条记录描述一个实例表在数据库中被存储在称为模式（schema）的结构中2、关系型数据库和数据库管理系统-3OracleServer

ID LAST_NAME FIRST_NAME 10 Havel Marta 11 Magee Colin 12 Giljum Henry 14 Nguyen Mai

SALES_ ID NAME PHONE REP_ID 201 Unisports 55-2066101 12 202 SimmsAtheletics 81-20101 14 203 DelhiSports 91-10351 14 204 Womansport 1-206-104-0103 11TableName:S_CUSTOMERTableName:S_EMPRow(行)Column(列)2、关系型数据库和数据库管理系统-4数据库系统(DataBase

Sytem，简称DBS)：数据库系统是指在计算机系统中引入数据库后的系统构成通常数据库系统由数据库、数据库管理系统(及其开发工具)、应用系统、数据库管理员等构成数据库管理系统(DataBase

Management

Sytem，简称DBMS)数据库管理系统是位于用户与操作系统之间的一层数据管理软件，用于科学地组织和存储数据、高效地获取和维护数据DBMS的主要功能包括数据定义功能、数据操纵功能、数据库的运行管理功能、数据库的建立和维护功能常见的DBMS工具Oracle

DB2SQLServerMySQLDBMS主要功能数据库定义功能数据库操纵功能数据库运行管理数据库的建立和维护功能2、关系型数据库和数据库管理系统-5核心OS操作系统3、关系型数据库的ER模型-1关系型数据库设计的三个阶段

概要设计将用户需求转换为概念模式设计数据库E-R模型图

逻辑设计将概念模型转换为关系型数据模型E-R模型转换为数据库表运用数据库设计三大范式进行审核

物理设计选择具体数据库进行物理实现建立表结构，并编写代码实现前端应用用户需求概念模式（E-R模型）数据模式（关系模型）DB数据库概要设计逻辑设计物理设计3、关系型数据库的ER模型-2E-R模型（E-R图）使用E-R图描述概念模型，反映实体集和实体集间的关系E-R模型基本组成实体（Entity）：表示客观存在且相互区别的事物，使用矩形框表示属性（Attribute）：表示实体所具有的特性，使用椭圆形表示联系（Relationship）：表示事物（实体）间的联系，使用菱形框表示一对一联系：反映在E-R图中为1:1一对多联系：反映在E-R图中为1:n多对多联系：反映在E-R图中为m:n学生隶属学号年龄名称年级班级n1姓名4、1-1关联关系1-1关联关系（1:1）如果实体集E1中的每个实体只能和实体集E2中的一个实体有联系，反之亦然，则称实体集E1和E2是一对一的联系，表示为1：1

关系模型设计一对一的关系模型，设计时可分为两张表或合并为一张表需要设计两张表时，可在其中一张表中建立新的字段，使其关联另一张表的主键字段首都隶属名称人口名称位置国家11IDCOUNTRYLOCATION1中国亚洲2美国北美洲IDNAMEPEOPLESCOUNTRYID1北京1000万12纽约1500万25、1-N关联关系1-N关联关系（1:n）如果实体集E1中的每个实体能和实体集E2中的多个实体有联系，而反之E2中的每个实体只能关联E1中的一个实体，则称实体集E1(父/一)和E2(子/多)是一对多的联系，表示为1：n

关系模型设计一对多的关系模型，设计时需将父表中的主关键字放入子表中IDNAMEGRADES1软件一班03H2硬件二班04IDNAMEAGECLASSID1001Angel21S11002Sandy20S1学生隶属学号年龄名称年级班级n1姓名6、N-N关联关系N-N关联关系（n:n）实体集E1中的每个实体能和实体集E2中的多个实体有联系，反之E2中的每个实体也能关联E1中的多个实体，则称实体集E1和E2是多对多的联系，表示为n：n

关系模型设计多对多的关系模型，设计时需新增一个表，称为关联表，在关联表中分别添加两个表的主键和其他关联字段学生选修学号年龄名称学时课程nnIDNAMETIMEJ001Java语言48C002C++语言64STUIDNAMEAGES1Angel21S2Sandy20姓名编号STUIDCIDSCORES1J00185S2J00164S1C00278成绩7、关系型数据库的设计范式-1关系型数据库的设计范式

关系型数据库的设计标准就是三个范式：第一范式（1NF–FirstNormalFromate）：

要求数据库表中的字段都是不可再分的最小数据单元例如：地址Address字段，可进一步拆分为多个字段7、关系型数据库的设计范式-2第二范式（2NF–SecondNormalFromate）：

要求数据库表中不存在非主键字段对任一候选关键字段的部分函数依赖；即对于组合主键（AB），要求里面的A或B与其他字段不能存在组合重复。解决方式：不采用组合主键的方式，尽量使用单一主键，确保表中的其他字段都只依赖于该单一主键7、关系型数据库的设计范式-3第三范式（3NF–ThirdNormalFromate）：

要求数据库表中不存在非主键字段对任一候选关键字段的传递函数依赖；

即如果存在A决定B，B决定C的关系，则C传递依赖于A

解决方法：将不直接依赖于主键的列拆分成独立的表，确保表中的每个列只与主键直接相关而非间接相关第02节SQL语言概述知识点预览#节知识点难点重点应用2ODP-C05-02SQL语言概述1、SQL语言的特点

2、DDL数据定义语言

√3、DML数据操作语言√4、DCL数据控制语言√5、数据库事务处理√1、SQL语言的特点什么是SQL语言？SQL（StructuredQueryLanguage）语言，全称叫结构化查询语言，是一种数据库访问的通用语言现行的数据库几乎都支持SQL语言，如Sybase、Oracle、SQLServer等SQL语言的特点高度非过程化的语言统一的语法结构，可以独立完成数据库生命周期中的全部活动语法结构简洁，易学易用所有关系型数据库的公共语言SQL的功能与分类定义数据库中存储的信息，包括表、视图、索引等（DDL）对数据库中的表进行增删改操作（DML）对数据库中的表进行查询检索（DQL）对数据库中的对象进行权限管理（DCL）2、DDL数据定义语言数据定义语言(DataDefinitionLanguage，简称DDL)用于定义数据库的逻辑结构DDL语言包括对数据库对象的定义，包括数据库、数据表、视图或索引的创建、修改、删除等操作语句说明CREATE创建数据库对象ALTER修改数据库对象DROP删除数据库对象RENAME更改表名TRUNCATE删除表的全部内容3、DML数据操作语言数据操作语言(DataManipulationLanguage,简称DML)用于对数据库表中的数据进行操作包括对表中的数据进行增加、修改、删除和查询等操作语句说明SELECT从表中查询数据INSERT向表中添加记录DELETE删除记录UPDATE修改记录的内容MERGE将一个表中的记录合并到另外一个表中4、DCL数据控制语言数据控制语言(DataControlLanguage，简称DCL)用于对数据库中的对象进行权限设置只有数据库管理员才有权限进行该操作提高数据库的安全性语句说明GRANT授予其他用户对数据库结构的访问权限REVOKE阻止其他用户访问数据库结构5、数据库事物处理事务控制语言(TransactionControl,简称TCL)用于控制对表中的记录所做的一系列修改永久性地存储到表中，或者取消这些修改操作语句说明COMMIT永久性地保存对数据所做的修改ROLLBACK取消对数据所做的修改SAVEPOINT设置一个“保存点”，可以将对行所做的修改回滚到此处第03节

检索数据知识点预览#节知识点难点重点应用3ODP-C05-03检索数据1、简单查询

√√2、筛选查询

√√3、排序查询√√√4、分组查询√√√1、简单查询-1基本的SELECT语句SELECT语句用于从数据库中检索信息，通常称为读数据基本语法星号符（*）：表示查询表中所有的列Col_name：指定要查询的列名，多个列之间用逗号分隔Table_name：要查询的列所在的表名称示例SELECT*|{col_name1,col_name2,…}FROMtable_name;SELECT*FROMemp;SELECTempno,ename,job,salFROMemp;1、简单查询-2算术表达式Oracle允许在SQL语句中使用算术表达式来进行算术运算算数表达式可以修改数据的显示方式、执行计算等常用的算术运算符：+：加法-：减法*：乘法/：除法示例查看某个员工的工号，姓名和年薪，其中员工表中的sal字段只代表月薪，年薪需要进行运算才能获得Selectempno,ename,sal*12fromemp;1、简单查询-3使用列别名Oracle允许在查询语句中重命名列名别名紧跟在列名后（列名和别名之间也可加关键字AS）基本语法示例查看某个员工的工号，姓名和年薪如果别名包含空格或特殊字符，或者区分大小写则需要使用双引号Selectempno,ename,sal*12asannual_salaryfromemp;SELECTcol_name[AS]new_col_nameFROMtable_name;Selectempno,ename,sal*12“AnnualSalary”fromemp;1、简单查询-4连接操作符“||”Oracle允许在SQL中使用”||”连接操作符实现字符串连接使用||可以使多列的值或者特殊的字符串合并到一个列进行显示基本语法示例查看某个员工的工号和姓名，要求显示全名SELECTempno,first_name||

''||last_nameAS“EmployeeName"FROMs_emp;SELECTcol_name1||

‘special_char'||

col_name2FROMtable_name;1、简单查询-5使用DISTINCT关键字在SQL中使用DISTINCT可以确保重复的记录只显示一条基本语法注意：distinct关键词必须紧跟select关键词后面如果distinct关键词后面如果出现多列，表示多列联合去重，即多列的值都相同的时候才会认为是重复的记录示例查看所有员工的职位名称和部门id，要求同职位同部门的只显示一次SELECTDISTINCTjob,deptnoFROMemp;SELECTDISTINCTcol_name1,col_name2,…FROM

table_name;2、筛选查询-1限制查询在Oracle中使用WHERE子句可以指定条件进行查询基本语法注意：where子句的优先级别最高condition条件语句可以包含多个条件表达式条件表达式由列名、表达式、常数和比较操作符组成，常见的比较操作符包括：比较运算符SQL操作符逻辑操作符SELECTcol_name1,col_name2,…FROM

table_nameWHERE

condition(s);2、筛选查询-2比较运算符运算符说明=等于<>或!=不等于<小于>大于<=小于或等于>=大于或等于ANY与一个列表中的任何值进行比较ALL与一个列表中的所有值进行比较2、筛选查询-3使用比较运算符查看薪资小于1000的员工查看部门编号大于20或30中任意一个的员工记录查看部门编号比20和30都大的员工记录Selectempno,ename,salfromempwheresal<1000;Selectempno,ename,deptnofromempwheredeptno>ANY(20,30);Selectempno,ename,deptnofromempwheredeptno>ALL(20,30);2、筛选查询-4SQL操作符LIKE操作符使用LIKE能实现模糊查询功能，当取值不是精确值时使用搜索条件可包含通配符（代替任何内容的符号）：

%：

通配零个或多个字符_：仅通配一个字符示例：查找名字第三个字母为’O’的员工运算符说明LIKE模糊查询，当取值不是精确的值时使用IN与值列表中的任一值匹配BETWEENAND介于两个值之间（包含这两个值）SELECT*FROMempWHEREenameLIKE‘__O%’;2、筛选查询-5IN操作符使用IN关键字可以检索其列值在某个列表中的记录示例查看工号为7788,7844,7566的员工信息BETWEENAND操作符使用BETWEENAND操作符可以检索值在指定区间内的记录示例查看工资在700到1500之间的员工信息SELECTempno,ename,salFROMempWHEREsalBETWEEN700AND1500;SELECTempno,ename,salFROMempWHEREempnoIN(7788,7844,7566);2、筛选查询-6逻辑操作符当条件存在多个的时候可使用该操作符示例查看员工部门为10或者30号部门且工资大于1000的员工运算符说明XANDY当x和y都为ture时，返回trueXORY当x和y中有一个为true时，就返回trueNOTX如果x为false,则返回true；如果x为true，则返回falseSELECTempno,ename,deptno,jobFROM

empWHEREsal>1000AND

(deptno=10ORdeptno=30);3、排序查询-1排序排序就是查询时将指定字段的值按照升序或者降序排列ASC：升序排列（默认）DESC：降序排列注意：使用orderby子句只对查询记录的显示进行调整，并不改变查询结果，所以执行权最低，即最后才执行如果有多个列排序，后面的列排序的前提是前面的列已经排好序以后有重复（相同）的值SELECTcol_name,...FROMtable_nameORDERBYcol_name[ASC|DESC],...3、排序查询-2示例查看s_emp中的员工信息，并按员工姓名降序排列查看s_emp中的员工信息，并按first_name升序排列，当first_name存在重复值时，将按last_name进行降序排列SELECTid,last_name,salaryFROMs_empORDERBYlast_nameDESC;

SELECTid,first_name,last_nameFROMs_empORDERBYfirst_nameASC, last_nameDESC;

4、分组查询-1使用GROUPBY语句SQL查询语句中可以使用GROUPBY子句实现分组查询，即将某些相同值的列进行分组，并实现对数据的归纳统计GROUPBY子句通常需要结合分组函数实现统计功能GROUPBYcol_name：将数据按照col_name列进行分组常见的组函数：AVG（）：求平均值COUNT（）：求总数MAX（）：求最大值MIN（）：求最小值SUM（）：求和SELECTcol_name,...FROMtable_nameWHEREcondition…GROUPBYcol_name,...[ORDERBYcol_name…]4、分组查询-2GROUPBY示例查看所有部门的部门员工工资总和，并按照部门工资降序排序注意：没有出现在groupby子句中或组函数中的列，不能出现在select子句中当groupby子句中出现多列的时候，会按照从左至右的顺序进行分组，即先按照第一列分组，然后在第一列分好的组里面，再按照第二列进行分组，以此类推。。。SELECTdept_id,SUM(salary)dept_salaryFROMs_empGROUPBYdept_idORDERBYdept_salaryDESC;---------------错误案例---------------SELECTdept_id,last_name,MAX(salary)FROMs_empGROUPBYdept_id;4、分组查询-3使用HAVING语句HAVING语句通常与GROUPBY一起使用，限制搜索条件，实现分组过滤与WHERE语句不同，HAVING子句只与分组相关，不与单个列值有关HAVING子句只作用于GROUPBY中创建的组，通常结合组函数应用如果限制条件中出现组函数，必须使用HAVING，不能使用WHERESELECTcolumn,group_function(column)FROMtable[WHERE condtion][GROUPBYgroup_by_expression][HAVING group_condtion][ORDERBYcolumn];SELECTproduct_type_id,AVG(price)FROMproductsWHEREprice<15GROUPBYproduct_type_idHAVINGAVG(pirce)>13ORDERBYproduct_type_id;第04节

多表关联查询知识点预览#节知识点难点重点应用4ODP-C05-04多表关联查询1、内连接√√√2、左外部连接√√√3、右外部连接4、自连接1、内连接-1多表查询又称多表联合查询，即查询语句中涉及到多张表，通过特定的方式将多个表进行连接，显示查询结果基本语法在WHERE子句中编写连接条件如果在多个表中出现了相同的列名，则在列名的前面加上表名作为前缀连接的类型内连接：当连接中的列满足连接条件时才会返回，只能查出匹配的记录等值连接：在连接中使用（=）操作符不等值连接：在连接中使用除等号之外的操作符外连接：即使连接条件中的一列包含空值也会返回一行自连接：返回连接到同一个表中的行SELECTtable1.column1,table2.column2FROM

table1,table2WHEREtable1.column3=table2.column3…;1、内连接-2customeremps外键主键等值连接等值连接查询后结果列表中不存在207客户信息1、内连接-3内连接也称简单连接，只能查询出匹配的记录，不匹配的记录无法查出内连接是最常用的查询方式可以使用表别名简化查询等值连接使用等号（=）操作符连接条件示例：查看每个员工的工号，姓名以及部门名称和所属区域名称不等值连接使用”>”、”<“、”<>”、”BETWEENAND”等除等号外的操作符连接多个表SELECTe.id,e.last_name,d.id,,FROMs_empe,s_deptd,s_regionrWHEREe.detp_id=d.idANDd.region_id=r.id;SELECTtest1.idFROMtest1,test2WHEREtest1.idBETWEENtest2.min_valueANDtest2.max_value;2、左外连接-1外连接使用内连接时，如果某个行不满足连接条件，那么该行将不会出现在查询结果中当一个表中的记录在另外一个表中不存在的时候，使用外连接可以显示这些不存在的结果外连接使用的操作符：(+)外连接的类型左外连接不仅仅返回符合连接条件的行记录，还保留左边表中的全部记录右外连接不仅仅返回符合连接条件的行记录，还保留右边表中的全部记录全外连接不仅仅返回符合连接条件的行记录，还保留两个表中的未匹配记录2、左外连接-2左外连接使用左外连接，表示当左边表（=号左边的表）中的记录在右边表中不存在的时候，依旧显示左边表的所有记录不仅仅返回符合连接条件的记录，还保留左边表中的全部记录未匹配的记录，其右表对应的列补空值示例：查看所有客户的工号、名字和对应的销售人员姓名，即使无对应销售人员也显示SELECTtb_name,col_name,...FROMtb_name1,tb_name2WHEREtb_name1.col_name=tb_name2.col_name(+);SELECTc.id,,e.last_nameFROMs_customerc,s_empeWHEREc.sales_rep_id=e.id(+)ORDERBYc.id;3、右外连接右外连接使用右外连接，表示当右边表（=号右边的表）中的记录在左边表中不存在的时候，依旧显示右边表的所有记录不仅仅返回符合连接条件的记录，还保留右边表中的全部记录未匹配的记录，其左表对应的列补空值示例：查看所有员工的姓名，及其负责的客户号和客户名称，即使无对应客户的也显示SELECTtb_name,col_name,...FROMtb_name1,tb_name2WHEREtb_name1.col_name(+)=tb_name2.col_name;SELECTc.id,,e.last_nameFROMs_customerc,s_empeWHEREc.sales_rep_id(+)=e.idORDERBYc.id;4、自连接自连接对同一个表进行的连接必须使用不同的表别名来标识在查询中每次对表的引用示例：查询员工姓名及其经理名字SELECT……FROMtb_namet1,tb_namet2WHEREt1.col_name=t2.another_col_name;SELECTworker.last_name,manager.last_nameFROMemployeesworker,employeesmanagerWHEREworker.manager_id=manager.employee_id;第05节Oracle常用系统函数知识点预览#节知识点难点重点应用5ODP-C05-05Oracle常用系统函数1、字符类函数√√2、数字类函数√√3、日期和时间类函数√√4、转换类函数√√√5、聚合类函数√√√1、字符类函数-1SQL函数功能进行数据计算修改单个数据项处理成组的行的输出指定日期和数字的显示格式转换列的数据类型SQL函数类型单行函数：查询表或视图时，作用于单行记录上，且只返回一个结果字符型函数数值型函数日期时间型函数转换函数聚合函数：聚合函数作用于多行记录上，返回一个结果，主要用于分组查询中1、字符类函数-2常用的字符型函数大小写处理函数函数说明LOWER(x)将x中的字母转换成小写UPPER(x)将x中的字母转换成大写INITCAP(x)将x中每个单词的首字母都转换成大写，并返回得到的字符串1、字符类函数-3字符处理函数函数说明CONCAT（x,y）将y添加到x上，并将得到的字符串作为结果返回SUBSTR(x,start[,length])返回x中的一个子字符串，这个字符串从start处开始，还可以为这个子字符串指定一个可选的length参数LENGTH(x)返回x中字符的个数INSTR(x,find_string[,start[,occurrence]])在x中查找字符串find_string，然后返回find_string所在的位置。可以提供可选的start位置来指定该函数从这个位置开始查找。同样，也指定一个可选的occurrence参数，来说明该返回find_string第几次出现的位置LPAD(x,width[,pad_string])在x的左边补齐空格，得到总长为width个字符的字符串。该函数可以提供一个可选的pad_string,这个参数用于指定重复使用哪个字符串来补齐x左边的空位。返回x被补齐之后的结果字符串RPAD(x,width[,pad_string])与LPAD（）类似，不过要对x的右边补齐1、字符类函数-4示例使用LENGTH函数显示name列中字符串的长度将description列中的每个单词的首字母转换成大写查看员工名字为chang的员工的id和薪资，chang不区分大小写，即（CHANGChangchang）都符合SELECTname,LENGTH(name)FROMproducts;SELECTproduct_id,INITCAP(description)FROMproducts;SELECTid,last_name,salaryFROMs_empWHERELOWER(last_name)='chang';2、数字类函数-1常见数值型函数MOD函数函数说明MOD（x,y）返回x除以y的余数ROUND(x[,y])返回对x取整的结果，会进行四舍五入；TRUNC(x[,y])返回对x直接截断的结果，不进行四舍五入；SELECTMOD(8,3),MOD(8,4)FROMdual;2、数字类函数-2ROUND(x,[y])函数用于计算对x取整的结果，会进行四舍五入其中y为可选参数，说明对第几位小数取整如果没有指定y，则x在0位小数处取整数，取整时四舍五入如果y是负数，则对x在小数点的左边的第|y|位处取整数，取整时四舍五入示例使用ROUND函数分别对5.75在0位、1位和-1位取整SELECT

ROUND(5.75),ROUND(5.75,1),ROUND(5.75,-1)FROMdual;2、数字类函数-3TRUNC(x,[y])函数用于计算对x数据直接截断的结果，不会进行四舍五入y为可选参数，说明对第几位小数截断如果没有y，则对在0位小数截断如果y为负数，则对x在小数点左边的第|y|位处截断示例对5.75在0位、1位、-1位小数处截断SELECT

TRUNC(5.75),TRUNC(5.75,1),TRUNC(5.75,-1)FROMdual;3、日期和时间类函数-1常用日期和时间函数函数说明SYSDATE返回系统的当前日期SYSTIMESTAMP返回系统当前时间，包含时区信息，精确到毫秒ADD_MONTHS(date,integer)返回在指定月份上加指定月数后的日期NEXT_DAY(date,char)返回当前日期向后一周的char所指定的对应日期LAST_DAY(date)返回参数指定的日期对应月份的最后一天MONTHS_BETWEEN(date1,date2)返回date1和date2之间的月份数…………3、日期和时间类函数-2系统表空间获取系统当前日期返回当前日期一周后的星期一所对应的日期返回当前日期过两个月后的日期返回当前日期和指定日期的间隔月数SELECTSYSDATEFROMDUAL;SELECTNEXT_DAY(SYSDATE,‘星期一’)FROMDUAL;SELECTADD_MONTHS(SYSDATE,2)FROMDUAL;SELECTMONTHS_BETWEEN(SYSDATE,TO_DATE('2013-04-03','yyyy-mm-dd'))FROMDUAL;4、转换类函数-1常用转换类函数函数说明TO_CHAR(x)将一个日期或数值类型数据转换为一个字符串TO_NUMBER（x[,format])将一个字符串类型数据转换为一个NUMBER类型TO_DATE(x[,format])将字符串类型数据转换为一个DATE类型CAST(xAStype)将x转换为type所指定的兼容数据库类型TO_BLOB(x)将x转换为一个二进制大对象类型…………4、转换类函数-2使用TO_CHAR函数处理数字其中format参数参数格式说明9999返回指定位置处的数字0099999900999：返回一个数字，前面补09990：返回一个数字，后面补0.999.99在指定位置处返回一个小数点,9,999在指定位置处返回一个逗号$$999在数字开头返回一个美元符号SELECT

TO_CHAR(12345.67,'99,999.99')FROMdual;4、转换类函数-3使用TO_CHAR函数处理日期其中format参数参数说明YYYY用数字表示完整年份YEAR拼写出的年份（用英文标识）MM月份的两位数值MONTH月份的完整名称MON月份的三个字母缩写DY一周中某日的三个字母缩写DAY一周中某日的完整名称DD用数字表示的月份中某日4、转换类函数-4格式样式具有以下特点必须放在单引号内区分大小写可以包含任何有效的日期格式元素输出的日和月的名称会自动用空白进行填充与日期之间用逗号分隔示例SELECTcustomer_id,TO_CHAR(reg_date,'MONTHDD,YYYY')FROMcustomers;4、转换类函数-5TO_NUMBER函数用于将字符串转换为一个数字指定一个可选参数format字符来说明char的格式示例使用TO_NUMBER函数将字符串970.13转换为一个数字，然后加25.5SELECTto_number('970.13')+25.5fromdual;TO_NUMBER(char[,’format’])5、聚合类函数-1聚合函数聚合函数主要用于分组查询操作中，因此又称组函数参数说明AVG(x)返回x的平均值COUNT(x)统计指定列的查询返回的行数MAX(x)返回x的最大值MEDIAN(x)返回x的中间值MIN(x)返回x的最小值STDDEV(x)返回x的标准差SUM(x)返回x的和VARIANCE(x)返回x的方差5、聚合类函数-2示例统计产品价格的平均值统计产品的数量统计产品价格的最大值和最小值统计所有产品价格之和SELECTAVG(price)FROMproducts;SELECTCOUNT(product_id)FROMproducts;SELECTMAX(price),MIN(price)FROMproducts;SELECTSUM(price)FROMproducts;第06节

子查询知识点预览#节知识点难点重点应用6ODP-C05-06子查询1、子查询概述√√2、单行子查询√√3、多行子查询√√4、关联子查询√1、子查询概述-1为什么需要子查询？通常当WHERE子句后面的条件不是一个确切的值或表达式，要依赖于另一个查询语句的查询结果时，需要使用子查询什么是子查询？子查询也称嵌套查询，是嵌套在另一个SQL语句中的SELECT查询语句子查询可以嵌套在SELECT、UPDATE和DELETE语句中子查询本质是WHERE子句的一个条件表达式SELECTcol_name,…FROMtable_nameWHEREcol_name比较操作符( SELECTcol_name1,.. FROMtb_name [WHEREcondition…])1、子查询概述-2使用子查询的准则将子查询扩在括号内将子查询放置在比较条件的右侧比较条件双方数据类型和数量要匹配单行运算符用于单行子查询，而多行运算符用于多行子查询子查询的类型单行子查询从内部SELECT语句中仅返回一行的查询使用单行比较操作符：=、>、>=、<、<=、<>多行子查询从内部SELECT语句中返回多行的查询使用量化操作符：IN、ANY、ALL、SOME2、单行子查询-1单行子查询从内部SELECT语句中仅返回一行的查询使用单行比较操作符：=、>、>=、<、<=、<>示例查看职位名称和名字为Chang的员工一样的所有员工工号和名字SELECTid,last_name,titleFROMs_empWHEREtitle=(

SELECTtitle FROMs_emp WHERElast_name='Chang’);2、单行子查询-2示例查询所有价格高于商品平均价格的产品号、产品名称和产品价格SELECTproduct_id,name,priceFROMproductsWHERE

price>( SELECTAVG(price)

FROMproducts);2、单行子查询-3在HAVING子句中使用子查询Oracle允许在分组查询中的HAVING子句中使用子查询示例查询平均价格低于同产品组平均价格最大值的产品的product_id和平均价格注意：ORDERBY排序子句不能出现在子查询中，只能用于外部查询SELECTproduct_type_id,AVG(price)FROMproductsGROUPBYproduct_type_idHAVINGAVG(price)<( SELECTMAX(AVG(price)) FROMproducts GROUPBYproduct_type_id)ORDERBYproduct_type_id;2、单行子查询-4在FROM中使用子查询在外部查询的FROM子句中使用子查询，子查询为FROM子句提供内联数据，也称为内联视图示例：在外部查询中从products表中检索product_id和price列，在子查询中检索一种产品已经被购买的次数SELECTduct_id,price,purchases_duct_countFROMproductsprds,( SELECTproduct_id,COUNT(product_id)product_count FROMpurchases GROUPBYproduct_id)purchases_dataWHEREduct_id=purchases_duct_id;3、多行子查询-1多行子查询返回多个行的子查询称为多行子查询使用的是量化运算符：函数说明IN匹配子查询返回的结果列表中的任意一个即可ANY将值与子查询返回的任意一个值进行比较ALL将值与子查询返回的每个值进行比较SOME与ANY含义类似，ANY多用于非”=”的情况3、多行子查询-2IN操作符用来检查在一个值中是否包含指定的值这个值列表可以是来自一个子查询返回的结果示例查找在子查询中所返回的product_id值列表中是否包含指定的product_id，并显示其产品号和产品名称SELECTproduct_id,nameFROMproductsWHEREproduct_idIN( SELECTproduct_id FROMproducts WHEREnameLIKE'%e%‘);3、多行子查询-3ANY操作符用来将一个值与一个列表中的任何值进行比较在ANY操作符前，必须使用一个=、<>、<、>、<=或>=操作符示例检查是否有员工的工资低于salary_grades表中任何一级最低工资SELECTemployee_id,last_nameFROMemployeesWHEREsalary<ANY

( SELECTlow_salary FROMsalary_grades);3、多行子查询-4ALL操作符用来将一个值与一个列表中的所有值进行比较在ALL操作符前，必须使用一个=、<>、<、>、<=或>=操作符示例检查是否有员工的工资高于salary_grades表中所有级别的最高工资SELECTemployee_id,last_nameFROMemployeesWHEREsalary>ALL

( SELECThigh_salary FROMsalary_grades);4、关联子查询-1集合操作符使用集合操作符可以将两个或多个查询返回的记录组合起来集合运算符的准则SELECT列表中的表达式在数量上必须匹配第二个查询中每一列的数据类型必须与第一个查询中对应列的数据类型相匹配ORDERBY子句只能出现在语句的末尾函数说明UNIONALL合并各个检索出的所有行，包括重复行UNION合并各个检索出的所有行，不包括重复行MINUS返回从第一个查询检索出的行减去第二个查询检索出的行之后剩余的记录（差集）INTERSECT返回两个查询检索出的公有记录（交集）4、关联子查询-2示例表使用UNIONALL操作符SELECTproduct_id,product_type_id,nameFROMproductsUNIONALLSELECTprd_id,prd_type_id,nameFROMmore_products;4、关联子查询-3使用UNION操作符使用INTERSECT操作符SELECTproduct_id,product_type_id,nameFROMproductsUNIONSELECTprd_id,prd_type_id,nameFROMmore_products;SELECTproduct_id,product_type_id,nameFROMproductsINTERSECTSELECTprd_id,prd_type_id,nameFROMmore_products;4、关联子查询-4使用MINUS操作符SELECTproduct_id,product_type_id,nameFROMproductsMINUSSELECTprd_id,prd_type_id,nameFROMmore_products;第07节

操作数据库知识点预览#节知识点难点重点应用7ODP-C05-07操作数据库1、插入数据√√2、更新数据√√3、删除数据√√4、TRUNCATE语句√1、插入数据-1INSERT语句使用INSERT语句可以向数据库表中插入记录INSERT语句一次只能插入一条记录基本语法注意插入一行新记录时，该记录的每一列都含有值，没有显式指定值的列会使用NULL空值INSERT语句中允许只指定表名不指定列名，要求插入时各个值的顺序是按照表中列的默认顺序排列的，且数据类型相互匹配要求字符型和日期型数据用单引号括起来INSERTINTOtable_name[(column1[,column2…])]VALUES(value1[,value2…]);1、插入数据-2示例向S_CUSTOMERS表中插入一条新记录显式插入空值，要求对应的列没有非空约束隐式插入空值INSERTINTO

s_customers(customer_id,first_name,last_name,dob,phone)VALUES(6,'fred','brown','01-1月-1970','800-555-1215');INSERTINTO

s_customersVALUES(6,'fred','brown',NULL,NULL);INSERTINTO

s_customers(customer_id,first_name,last_name)VALUES(6,'fred','brown');1、插入数据-3常见错误示例错误1：主键值为NULL空值错误2：插入的主键值与表中其他记录的主键值重复错误3：对存在NOTNULL非空约束的列插入NULL值INSERTINTO

s_customers(customer_id,first_name,last_name)VALUES(NULL,'fred','brown');INSERTINTO

s_customers(customer_id,first_name)VALUES(6,NULL);INSERTINTO

s_customers(customer_id,first_name,last_name)VALUES(1,'fred','brown');1、插入数据-4INSERT中使用子查询INSERT语句中，可以不使用列值，而是使用查询从一个表向另外一个表复制行要求源表和目标表的列数和列的类型匹配不要使用VALUES子句示例使用SELECT语句检索顾客编号为1的first_name和last_name值，并将其作为顾客号10的顾客的姓名插入数据库表中INSERTINTOs_customers(customer_id,first_name,last_name)SELECT10,first_name,last_nameFROMs_customersWHEREcustomer_id=1;2、更新数据-1UPDATE语句使用UPDATE语句可以更新数据库表中的记录UPDATE语句一次可以更新多条记录基本语法注意可以指定WHERE条件语句，指定要更新的记录如果不指定WHERE条件语句，将更新表中的所有记录可以在UPDATE语句中使用子查询UPDATEtable_nameSETcolumn=value[,column=value,…][WHEREconditions…];2、更新数据-2UPDATE示例修改满足WHERE条件的记录没有指定WHERE条件时，会修改表中的所有记录UPDATEs_customersSETlast_name=‘Oracle’WHEREcustomer_id=1;UPDATEs_customersSETlast_name=‘Oracle’;2、更新数据-3UPDATE中使用子查询可以在UPDATE语句的SET子句中使用子查询更新列值示例更新工号为4的员工的职位与薪资，使之与工号为2的员工的职务薪资相同UPDATE s_empsSETtitle=(SELECTtitle FROMs_emps WHEREemp_id=2),

salary=(SELECTsalary FROMs_emps WHEREemp_id=2)WHERE emp_id=4;3、删除数据-1DELETE语句使用DELETE语句可以删除数据库表中的记录DELETE语句一次可以删除多条记录基本语法注意可以使用WHERE条件语句删除指定的记录如果不使用WEHER语句，将删除表中的所有记录在DELETE语句中也可以使用子查询DELETE[FROM]table_name[WHEREconditions…];3、删除数据-2DELETE示例删除满足WHERE条件的记录没有指定WHERE条件时，会删除表中的所有记录使用子查询，从products表中删除产品类型名称为Book的产品DELETEFROMs_customersWHEREcustomer_id=1;DELETEFROMs_customers;DELETEFROMproductsWHEREproduct_id= (SELECTpro