《Oracle数据库应用开发及实践》课件04-第4章 SQL 应用基础

-1-本章目标-1了解SQL语句的特点以及编写规则掌握列查询、条件查询、运算符查询、数据排序和联合查询掌握内连接和外连接的区别及其用法

掌握子查询的语法及其应用理解函数的分类和作用以及单行函数和分组函数的定义掌握数值型函数、字符函数、日期函数、转换函数以及常用的分组函数的用法-2-本章目标-2掌握使用INSERTINTO语句插入数据，以及使用用子查询复制数据掌握使用UPDATE语句更新数据，以及根据子查询更新数据掌握特殊数据在进行数据插入、更新时的处理掌握使用DELETE语句删除表格中的数据掌握在DML操作中COMMIT、ROLLBACK、SAVEPOINT的用法

-3-SQL（StructuredQueryLanguage）是“结构化查询语言”，语言结构简洁、功能强大、简单易学。特点：SQL语言是统一的语言SQL语言语法简单易学SQL语言是所有关系数据库的公共语言SQL语言概述-4-数据查询语言(DQL-DataQueryLanguage)包括SELECT；数据操纵语言(DML-DataManipulationLanguage)包括INSERT、UPDATE、DELETE；数据定义语言(DDL-DataDefinitionLanguage)包括CREATE、ALTER、DROP等；数据控制语言(DCL-DataControlLanguage)包括COMMIT、ROLLBACK等。SQL分类-5-SQL关键字在执行时并不区分大小写，为了统一标准，通常指定SQL关键字需要大写。对象名和列名不区分大小写，为了统一标准，通常SQL的对象名或列名指定为小写。字符值和日期值区分大小写。适当的增加空格和缩进，使程序更易读。使用注释增强程序可读性。单行注释：--多行注释：/*和*/

SQL语句编写规则-6-语句格式：*指示查询数据表的所有列；DISTINCT指示消除结果集中的重复记录；column指定需要查询的表的列名，多列之间用逗号“,”分隔；alias指定列（表达式）的别名；table_name指定需要查询的表的名字。列查询

SELECT*|[DISTINCTcolumn|expression[alias],...]FROMtable_name-7-语法格式：WHERE常用来构成一个限制检索表中行数据的条件表达式；<conditionexpression>进行数据筛选的条件表达式，在条件表达式中可以使用常用的比较运算、逻辑运算符等。条件查询SELECT*|[DISTINCTcolumn|expression[alias],...]FROMtable[WHERE<conditionexpression>];-8-语法格式column_order应该是查询结果中的一个字段，且可以进行大小比较(如数值、时间日期等)；ASC代表升序，默认值可以省略；DESC代表降序。排序SELECT*|[DISTINCTcolumn|expression[alias],...]FROMtable[,<table2>[,…]][WHERE<conditionexpression>][ORDERBY<column_order>[ASC|DESC]];-9-显示10号部门的员工名称。显示工资在1000到3000之间的员工信息。显示首字母为J的员工。按照工资的升序显示员工信息。查询实例selectdeptno,enamefromempwheredeptno=10;;selectename,salfromempwheresalbetween1000and3000;selectename,deptnofromempwhereenamelike'J%';selectename,salfromemporderbysalasc;-10-语句格式：满足条件：两个查询具有相同的列数；两个查询采用相同的列顺序；两个查询对应列的数据类型兼容联合查询SELECT*|[DISTINCTcolumn|expression[alias],...]FROMtable[,<table2>[,…]][WHERE<conditionexpression>]UNIONSELECT*|[DISTINCTcolumn|expression[alias],...]FROMtable[,<table2>[,…]][WHERE<conditionexpression>]-11-内连接显式内连接：INNERJOIN用于连接两个表，ON用于指定连接条件隐式内连接：相等连接外连接左外连接：LEFTJOIN-ON右外连接：RIGHTJOIN-ON完整外连接：FULLJOIN-ON交叉连接外连接-12-单行子查询：单行比较符（>、<、=、>=、<=，<>）多行子查询：ALL、ANY、IN多列子查询：operator是指多行运算符(IN、ANY、ALL)column1、column2是指成对比较的列。相关子查询子查询SELECT*|[DISTINCTcolumn|expression[alias],...]FROMtable[,<table2>[,…]][WHEREcolumn1,column2[,…]operator(SELECTcolumn1,column2[,…]FROMtable);SELECT*|[DISTINCTcolumn|expression[alias],...]FROMtable1t1WHEREcolumn1operator(SELECTcolumn1,column2FROMtable2t2WHEREexpr1=t1.expr2);-13-等值连接，显示员工名称和部门名称自连接，显示员工的姓名和上级的姓名左外连接，显示所有部门及部门员工的信息。联合查询实例selectw.ename"员工姓名",m.ename"上级"fromempw,empmwherew.mgr=m.empno(+);selectdname,enamefromdeptleftjoinempon(dept.deptno=emp.deptno);selectename,dnamefromemp,deptwhereemp.deptno=dept.deptno;-14-定义：函数是用于执行数据处理和复杂计算的，通过对一组数据进行一系列运算，得到最终需要的输出结果。函数分类：单行函数：每行作为一个输入参数，经过函数计算得到每行的计算结果多行函数：对多行输入值进行计算，得到多行对应的单个结果。另外，单行函数可以进行嵌套，即函数可以作为另一个函数的输入参数。系统函数-15-数值型函数：数值型函数对数字类型数据进行计算。

字符函数：字符函数用于对字符类型数据进行处理和计算。日期函数：日期类型函数针对日期类型数据运算。转换函数：转换函数可以对数值、字符、日期三种类型的数据进行类型转换。其他函数：用来对特殊数据进行处理或者完成一些高级的计算功能等。单行函数-16-ABS函数：返回一数值的绝对值CEIL函数：输入参数可以是非整数值，但返回结果则是大于等于输入参数的最小整数。FLOOR函数：返回一个小于或等于给定十进制数的最大整数MOD函数：返回一个数除以另一数的余数ROUND函数：根据给定的精度舍入数值TRUNC函数：不对指定小数前或后的部分做相应舍入选择处理，而全部截去数值函数ABS(number)CEIL(number)MOD(number,divisor)TRUNC(number,precision)ROUND(number,precision)FLOOR(number)-17-CONCAT(strl,str2)函数：将两个输入字符串，组成一个INSTR函数：确定一个字符串在另一个字符串中的位置格式：LOWER函数：返回指定字符串的小写形式UPPER函数：返回指定字符串的大写形式LPAD函数：从左面填充指定的一组字符格式：RPAD函数：从右面填充指定的一组字符字符函数-1INSTR(string,substring[,start[,occurrence]])LPAD(STRING,LENGTH[,PADDING])-18-LTRIM函数：用于删除字符串左边的前缀字符格式：RTRIM函数：用于删除字符串右边的尾随字符TRIM函数：用于删除字符串的前缀（或尾随）字符格式：函数中trimchar参数只能是一个字符SUBSTR函数：用于取得字符串的子串格式：REPLACE函数：用于替换字符串中的子串内容格式：字符函数-2LTRIM(STRING[,TRIMCHARS])TRIM([LEADING|TRAILING|BOTH][TRIMCHARFROM]STRING)SUBSTR(STR,START[,LENGTH]))REPLACE(STR,SUB[,REPLACEMENT])-19-ADD_MONTHS函数：将一个日期上加上指定的月份数格式：CURRENT_DATE函数：返回当前session所在时区的默认时间EXTRACT函数：用于从日期时间值中摘取特定数据格式：日期时间函数-1ADD_MONTHS(DATE,MONTHS)EXTRACT(DATEPARTFROMDATETIME)-20-MONTHS_BETWEEN函数：返回两个日期之间的月份数格式：NEXT_DAY函数：返回从实参日期开始，紧随其后的指定星期对应的日期格式：ROUND函数：将一日期处理（舍入）成第二个参数指定的形式格式：日期时间函数-2MONTHS_BETWEEN(DATE1,DATE2)NEXT_DAY(DATE,WEEKDAY)ROUND(DATE[,FMT])-21-TO_CHAR函数：将NUMBER转换为字符串TO_DATE函数：以定制格式将字符串转换为日期型数据格式：TO_NUMBER函数：用于将字符串转换成数字格式转换函数TO_DATE(STRING[,FMT[,’NLS_DATE_LANGUAGE=LANGUAGE’]])-22-DECODE函数：相当于条件语句(IF)，将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。格式：NVL函数：用于将NULL转变为实际值格式：参数expr1和expr2可以是任意数据类型，但两者数据类型必须匹配其他函数DECODE(INPUT,VALUE,RESULT[,VALUE,RESULT…][,DEFAULT_RESULT]);NVL(expr1,expr2);-23-COUNT函数：用于取得行数的总计。AVG、SUM函数：分别用于取得并返回指定列或表达式的平均值和总和。MAX、MIN函数：分别用于取得并返回指定列或表达式的最大值和最小值。分组函数-24-GROUPBY语句：按照指定的列进行数据分组SELECT语句中出现的列必须出现在GROUPBY语句中HAVING语句：用于过滤分组后的数据，必须与GROUPBY一起使用数据分组的限制：引入GROUPBY语句的查询操作中，在SELECT语句中出现的列，必须出现在GROUPBY子句中使用分组函数时，忽略NULL行分组函数只能出现在SELECT、ORDERBY、HAVING语句中ORDERBY用于对查询结果进行排序，必须放在分组语句之后

分组语句-25-查出工资最多的员工。查询每个部门的最大工资是谁。查询工资总和高于10号部门工资总和的部门。子查询、分组函数实例selectenamefromempwheresal=(selectmax(sal)fromemp);selectdeptno,ename,salfromempwhere(deptno,sal)in(selectdeptno,max(sal)fromempgroupbydeptno);selectdeptno,sum(sal)fromempgroupbydeptnohavingsum(sal)>(selectsum(sal)fromempwheredeptno=10);-26-插入单行数据格式：

按指定列插入数据插入所有列数据缺省数据列插入日期数据表的数据复制格式：插入数据INSERTINTOtable_name[(column[,column...])]VALUES(value[,value...]);INSERTINTOtable_name[column(,column...)]subquery;-27-插入单行数据

表的数据复制插入数据实例INSERTINTOdept(50,’technology’‘qingdao’);INSERTINTOdept2(deptno,dname,loc)selectdeptno,dname,locfromdept;-28-使用表达式更新数据更新指定列数据更新日期数据列更新缺省数据列使用子查询更新数据更新当前表数据更新基于另一个表的数据注：在UPDATE语句中使用子查询时，必须确保该子查询只为每个要更新的记录返回一个值，否则UPDATE会失败更新数据-29-删除满足条件的数据当使用DELETE语句删除表的所有数据时，不会释放表格所占用的空间，如要删除表格中的所有数据，可以使用TRUNCATETABLE语句，速度更快使用子查询删除数据删除数据DELETE[FROM]table_nameWHEREconditioninsubquery；-30-使用子查询删除数据删除数据DELETEdept2WHEREdeptnoin(selectdeptnofromdept)；-31-commit：事务的提交rollback：事务的回滚savepoint：可以使用savepoint设置事务的保存点，以控制事务进行部分回滚。DML事务操作-32-SQL是一种介于关系代数和关系演算之间的结构化查询语言，是一个通用的、功能极强的关系数据库语言SQL语言集数据查询、数据操纵、数据定义和数据控制等功能于一体使用连接查询可以实现多表查询SQL函数