《oracle基础学习》PPT课件.ppt

第三章SQL语言基础,SQL概述,SQL(StructuredQueryLanguage)结构化查询语言，是一种在关系数据库中定义和操纵数据库的标准语言，是用户与数据库之间进行交流的接口。SQL语言已经被大多数关系数据库管理系统采用。Oracle数据库提供的许多有用而强大的功能都需要通过SQL语言来体现。因此要使用Oracle数据库，一定要掌握SQL语言。,SQL特点,综合统一高度非过程化面向集合的操作方式同一种语法结构提供两种使用方式语言简洁、易学易用,分类,Select查询语句数据定义语言（DataDefinitionLanguage,DDL）：create,alter,drop,truncate数据操纵语言（DataManipulationLanguage,DML):insert,update,delete,merge数据控制语言（DataControlLanguage,DCL):grant,revoke事务控制（TransactionControl）：commit，rollback,savepoint,SELECT语句,数据检索是数据库中最频繁执行的活动，所以SELECT是使用频率最高的语句，它具有强大的查询功能，有的用户甚至只需要熟练掌握SELECT语句的一部分，就可以轻松地利用数据库来完成自己的工作。可以说SELECT是SQL语言的灵魂。SELECT语句的作用是让数据库服务器根据客户端的要求搜寻出用户所需要的信息资料，并按用户规定的格式进行整理后返回给客户端。,查询数据,SELECT语句的语法格式如下：SELECTDISTINCTselect_listFROMtable_sourceWHEREsearch_conditionGROUPBYgroup_by_expressionHAVINGsearch_conditionORDERBYorder_expressionASC|DESC其中：DISTINCT：用于删除结果集中重复的行。select_list指明要查询的选择列表。列表可以包括若干个列名或表达式，列名或表达式之间用逗号隔开，用来指示应该返回哪些数据。表达式可以是列名、函数或常数的列表。,FROMtable_source指定所查询的表或视图的名称。WHEREsearch_condition指明查询所要满足的条件。GROUPBYgroup_by_expression根据指定列中的值对结果集进行分组。HAVINGsearch_condition对用FROM、WHERE或GROUPBY子句创建的中间结果集进行的筛选。它通常与GROUPBY子句一起使用。ORDERBYorder_expressionASC|DESC对查询结果集中的行重新排序。ASC和DESC关键字分别用于指定按升序或降序排序。如果省略ASC或DESC，则系统默认为升序。,使用字段别名重命名查询结果中的字段，以增强可读性，可以使用AS为字段定义别名。用法举例：Selectdeptnoas部门编号,dnameas部门名称fromdept;,selectename姓名,sal工资fromempwheredeptno=10;,取消重复行使用distinct关键字可以取消查询结果中出现的重复行。用法举例：Selectdeptno,jobfromemp;,Selectdistinctdeptno,jobfromemp;,使用算术表达式在Select语句中，对NUMBER型数据可以使用算术运算符来创建表达式。算术运算符+-*/用法举例：selectempno,ename,sal,sal*12fromemp;,连接运算符连接运算符|可以把列与字符、或其它表达式连接在一起，得到一个新的字符串，实现合成列的功能。用法举例：selectename|isa|jobfromempwheredeptno=20;,selectempno,ename|sannualsalaryis|sal*12fromempwheredeptno=20;,使用WHERE子句,WHERE子句指定数据检索的条件,以限制返回的数据行.WHERE子句中的查询条件比较运算符:=（等于）、（大于）、=（大于等于）、（不等于）范围说明:betweenAandB,notbetweenAandB可选值列表：IN，NOTIN模式匹配：LIKE，NOTLIKE是否空值：ISNULL，ISNOTNULL上述条件的逻辑组合：NOT（非）、AND（与）、OR（或）,1使用比较运算符select*fromempwheredeptno20andjob=CLERK;,2使用BETWEEN关键字BETWEEN关键字总是与AND一起使用，用来检索在一个指定范围内的信息，NOTBETWEEN检索不在某一范围内的信息。,select*fromempwheresalbetween2500and3500andhiredatebetween01-1月-81and31-12月-81;,3使用IN关键字IN关键字允许用户选择与列表中的值相匹配的行，指定项必须用括号括起来，并用逗号隔开，表示“或”的关系。NOTIN表示含义正好相反。用法举例：查询部门编号为20或30的员工信息。select*fromempwheredeptnoin(20,30);等价于select*fromempwheredeptno=20ordeptno=30;,4使用LIKE关键字LIKE关键字用于查询与指定的某些字符串表达式模糊匹配的数据行。LIKE后的表达式被定义为字符串，必须用单引号（）括起来.通配符%：可匹配任意类型和长度的字符串。_（下划线）：可匹配任何单个字符。例如：LIKE刘%匹配以“刘”开始的字符串；LIKE%技术%匹配的是前后字符为任意，中间含有“技术”两个字的字符串；LIKE_秀%匹配的是第2个字为“秀”的任意字符串。,用法举例：查询姓名的首字符为A或第二个字符为Ade员工信息。select*fromempwhereenamelikeA%orenamelike_A%;查询姓名中出现字符A的员工信息。select*fromempwhereenamelike%A%;,5使用ISNULL关键字ISNULL关键字可以检索数据列中没有赋值的行。用法举例：显示津贴为空值的员工信息。select*fromempwherecommisnull;,6使用GROUPBY子句,本子句写在WHERE子句之后，用于对查询的结果集进行分组。用法举例：显示每个部门的平均工资和最高工资。selectdeptno,avg(sal),max(sal)fromempgroupbydeptno;显示每个部门、每种岗位的平均工资和最高工资。selectdeptno,job,avg(sal),max(sal)fromempgroupbydeptno,job;,7使用HAVING子句,HAVING用于限定组或聚合函数的查询条件，通常用在GROUPBY子句之后。通常，其作用与WHERE子句很相似。区别在于其作用的对象不同。WHERE子句作用于表和视图，HAVING子句作用于组。用法举例：selectdeptno,avg(sal),max(sal)fromempgroupbydeptno;显示平均工资高于2000元的部门编号、平均工资和最高工资。selectdeptno,avg(sal),max(sal)fromempgroupbydeptnohavingavg(sal)2000;,8使用ORDERBY子句,ORDERBY子句对查询结果集中的行进行重新排序。ASC和DESC关键字分别用于指定按升序或降序排序。如果省略ASC或DESC，则系统默认为升序。可以在ORDERBY子句中指定多个排序列，即嵌套排序，检索结果首先按第1列进行排序，对第1列值相同的那些数据行，再按照第2列排序依此类推。用法举例：以部门编号降序及姓名升序来查询工资在2000-3000元的员工信息。select*fromempwheresalbetween2000and3000orderbydeptnodesc,ename;,组处理函数,把查询出的数据组的值进行汇总运算，返回单值的函数。常用的组处理函数：平均值：AVG总和：SUM最小值：MIN最大值：MAX计数：COUNTCOUNT（*）、COUNT（DISTINCT）,用法举例：,select*fromempwheredeptno=20;,selectavg(sal)asarg1,avg(distinctsal)asarg2,max(sal)asmax,min(sal)asmin,sum(sal)assum,count(*)ascnt1,count(sal)ascnt2,count(distinctsal)ascint3,count(comm)ascnt4fromempwheredeptno=20;,子查询,子查询是在查询中包含另一个查询的查询。它本身是一个SELECT查询，可以代替表达式出现在WHERE子句中。它返回单个值且嵌套在SELECT、INSERT、UPDATE、DELETE语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。子查询的SELECT查询总是使用圆括号括起来。子查询中一般不使用orderby子句，orderby子句只能对最终查询结果进行排序。,单行子查询单行子查询只返回一行数据。在where子句中使用单行子查询时，可以使用单行运算符（=,=,)。用法举例：显示工资最高的员工信息。select*fromempwheresal=(selectmax(sal)fromemp);,多行子查询多行子查询可以返回多行数据。在where子句中使用多行子查询时，必须使用多行运算符（IN,NOTIN,EXISTS,NOTEXISTS,ALL,ANY).如果不能确认子查询会返回多少行，则在子查询中使用多行运算符比使用单行运算符更安全，否则容易失败。用法举例：显示与部门编号为20的岗位相同的员工信息。select*fromempwherejobin(selectdistinctjobfromempwheredeptno=20);,显示高于部门编号为20的所有员工工资的员工信息。,select*fromempwheresalall(selectsalfromempwheredeptno=20);,显示高于部门编号为20的任意员工的工资的员工信息。select*fromempwheresalany(selectsalfromempwheredeptno=20);,在DDL中使用子查询,1在CREATETABLE语句中使用子查询通过在CREATETABLE语句中使用子查询，可以在创建新表的同时插入表中的数据。createtabledept1(deptno,dname,loc)asselectdeptno,dname,locfromdept;,createtableemp1asselect*fromemp;,2在createview中使用子查询createorreplaceviewdept_20asselect*fromemp1wheredeptno=20orderbyempno;,在DML中使用子查询,1在UPDATE语句中使用子查询将与WARD岗位相同的员工的工资、补贴更新为与WARD完全相同。然后确认更新的结果。updateemp1set(sal,comm)=(selectsal,commfromemp1whereename=WARD)wherejob=(selectjobfromemp1whereename=WARD);selectename,job,sal,commfromemp1wherejob=(selectjobfromemp1whereename=WARD);,2在DELETE子句中使用子查询删除ACCOUNTIBG部门的员工信息。然后确认删除的结果。deletefromemp1wheredeptno=(selectdeptnofromdept1wheredname=ACCOUNTING);selectename,job,deptnofromemp1wheredeptno=(selectdeptnofromdept1wheredname=ACCOUNTING);,3在INSERT子句中使用子查询。将emp表中ACCOUNTING部门的雇员信息插入到emp表中，然后确认插入的结果。insertintoemp1select*fromempwheredeptno=(selectdeptnofromdeptwheredname=ACCOUNTING);select*fromemp1wheredeptno=(selectdeptnofromdept1wheredname=ACCOUNTING);,连接查询,用户在前面所作的查询大多是对单个表进行的查询，而在数据库的应用中，经常需要从多个相关的表中查询数据，这就需要使用连接查询。由于连接涉及多个表及其之间的引用，所以列的引用均必须明确，对于重复的列名必须用表名限定。,在Oracle系统中，为了从多个表中检索数据，可以使用下面的语法格式：selecttable1.column,table2.columnfromtable1,table2wheretable1.column1=table2.column2,笛卡尔乘积,连接查询的理论基础是笛卡尔乘积。也就是说，连接运算建立在笛卡尔乘积的基础上，所有连接运算的结果都是笛卡尔乘积的子集。笛卡尔乘积是指两个或两个以上的表进行相乘运算，结果是这些表中所有行的任意组合。例如，emp表中有14行数据，dept表中有4行数据，其结果中有56行数据。,连接类型,在连接运算中，根据指定的连接条件，可以把连接查询划分成3种类型，即内连接外连接交叉连接,内连接内连接是组合两个表的常用方法，它将两个表中的列进行比较，将两个表中满足连接条件的行组合起来，作为结果。内连接主要有两种形式，相等连接和不等连接。相等连接就是在连接列相等的条件下执行的连接操作。不等连接是指在连接列不相等的连接条件下执行连接操作。,相等连接,用法举例：selectempno,ename,emp.deptno,dname,locfromemp,deptwhereemp.deptno=dept.deptnoandemp.deptno=20;,可以使用表的别名来简化查询语句。selectempno,ename,a.deptno,dname,locfromempa,deptbwherea.deptno=b.deptnoanda.deptno=20;,不等连接,用法举例：selecta.ename,a.sal,b.gradefromempa,salgradebwherea.salbetweenb.losalandb.hisalanda.deptno=20;,外连接,在外连接中，某些不满足条件的列也会显示出来，也就是说，只限制其中一个表的行，而不限制另外一个表中的行。这种连接形式在许多情况下是非常有用的。外连接分为左外连接、右外连接和全外连接。对于外连接，oracle中可以使用“(+)”来表示，语法格式如下：selecttable1.column,table2.columnfromtable1,table2wheretable1.column1(+)=table2.column2,1、左外连接左外连接对连接条件中左边的表不加限制。返回结果集中将包括左表中所有的记录，而不仅仅是连接字段所匹配的记录。如果左表中的某条记录在右表中没有匹配的记录，则结果集相应记录的有关右表的所有字段将为空值。例：selectename,a.deptno,sal,dnamefromempa,deptbwherea.deptno=b.deptno(+)andsalbetween2500and3000;,等价于selectename,a.deptno,sal,dnamefromempaleftouterjoindeptbona.deptno=b.deptnowheresalbetween2500and3000;,2右外连接右外联接对右边的表不加限制。返回结果集中将包括右表中所有的记录，而不仅仅是连接字段所匹配的记录。如果右表中的某条记录在左表中没有匹配的记录，则结果集相应记录的有关左表的所有字段将为空值。例：selectename,a.deptno,sal,b.deptno,dnamefromempa,deptbwherea.deptno(+)=b.deptno;,等价于：selectename,a.deptno,sal,b.deptno,dnamefromemparightouterjoindeptbona.deptno=b.deptno;,3、全外连接全外连接对两个表都不加限制，所有两个表中的行都会包括在结果集中。结果集将包含两个数据表中的所有记录，当某条记录在另一个数据表中没有匹配记录时，则将另一个数据表的选择列表字段指定为空值。例：selectename,a.deptno,sal,b.deptno,dnamefromempafullouterjoindeptbona.deptno=b.deptno;,自连接（SELFJOIN）,自连接就是一个表与它自身的不同行进行连接。因为表名要在FROM子句中出现两次，所以需要对表指定两个别名，使之在逻辑上成为两张表。在SELECT子句中引用的列名也要使用表的别名进行限定。,selectempno,ename,mgrfromempwheredeptno=30;,selecta.ename雇员,b.ename经理fromempa,empbwherea.mgr=b.empnoanda.deptno=30;,交叉联接（CROSSJOIN）交叉连接也叫非限制连接，它将两个表不加任何约束地组合起来。在数学上，就是两个表的笛卡尔积。交叉连接后得到的结果集的行数是两个被连接表的行数的乘积。语法如下：SELECT列FROM表1CROSSJOIN表2或SELECT列FROM表1，表2,selectdeptnofromdeptwheredeptno30;selectdeptno,enamefromempwherejob=CLERK;selectdept.deptno,emp.deptno,enamefromdept,empwheredept.deptno30andemp.job=CLERK;,事务控制,日常生活中我们需要一种能保证数据完整性的机制，比如学生父母给孩子打的生活费要么发送成功，要么发送失败（不应该出现一边发送成功，另一边接受不到的情况），这样才是安全的、可靠的。数据库管理中也需要类似的机制，那就是事务。事务是作为单个逻辑工作单元执行的一系列操作。这一系列操作或者都被执行或者都不被执行。在一个事务中可以包括一条或多条SQL语句，如果一个事务执行成功，则表明该事务中的所有语句均执行成功，只要事务中的一条语句出现错误，则会取消整个事务的执行。,事务做为一个逻辑工作单元必须有四个属性，称为ACID（原子性、一致性、隔离性和持久性）属性，只有这样才能成为一个事务。事务具有以下4个属性：（1）原子性：事务应作为一个逻辑工作单元看待，事务中的所有语句要么全部执行，要么全部取消。（2）一致性：事务中的任何操作都不能破坏数据库的完整性。事务如果能够执行成功，则事务所修改的数据必须遵循数据库中各种约束、规则的要求，所有数据应该处于一致性状态。（3）隔离性：当多个事务同时运行时，不同的事务互不干扰，独立运行。（4）永久性：当事务执行成功后，事务对数据库所做的操作结果会永久保持。,在Oracle中，可以使用commit命令提交完成的事务，使用rollback回滚事务，使用savepoint设置保留点。事务开始于第一条可执行语句；事务结束：遇到commit或rollback语句遇到DDL或DCL语句用户会话结束系统崩溃事务的提交和回滚显式的提交和回滚操作是为了更好地保证数据的一致性。下述情况下事务会被自动提交执行一个DDL语句执行一个DCL语句正常结束会话会话异常终止/系统崩溃时事务会被自动回滚,SqlPlus的自动提交SqlPlus中执行SQL语句时可以设置是否自动提交，缺省设置为非自动提交。查看设置：showautocommit;autocommitOFFautocommitIMMEDIATE改变设置：setautocommiton;setautocommitoff;,一个使用rollback的例子：,一个使用savepoint的例子：,SQL函数,函数是指有0个或多个参数并且返回一个值的程序段。Oracle的函数有5种类型：单行函数；组处理函数；分析函数；对象参考函数；自定义函数单行函数是指输入一行，输出也是一行，以及直接对单个数据进行操作的函数。一般分为：数值函数、字符函数、日期函数、转换函数、其他函数等。,数学函数1.abs(x)函数此函数用来返回一个数的绝对值。2.sign(x)函数此函数用来返回一个数的正负值，若为一个正数则返回1，若为一个负数则返回-1，若为0则仍返回0。3.ceil(x)函数和floor(x)函数这两个函数中一个是用来返回大于或等于x的最小整数，另一个是用来返回小于或等于x的最大整数。4.三角函数sin(x)函数用来计算x的正弦值。5.mod(x,y)函数此函数返回x除以y的余数。6.round(x,y)函数round(x,y)函数用来四舍五入。7.trunc(x,y)执行截取数字。,例：selectROUND(45.926,2),ROUND(45.926),ROUND(45.926,-1)fromdual;,selectTRUNC(45.926,2),TRUNC(45.926),TRUNC(45.926,-1)fromdual;,selectmod(1600,300)fromdual;,字符函数1ASCII(c)返回字符c对应的ASCII（十进制数）2SUBSTR(c,m)返回C的子串，其中m是子串开始的位置，n是子串的长度。3LENGTH(c)返回字符串c的长度。4LOWER(c)返回字符串c的所有字符的小写。5UPPER(c)返回字符串c的所有字符的大写6.CONCAT(c1,c2)连接两个字符串c1和c27LPAD（c1,n,c2)在c1的左边填c2,直到字符串的总长度到达n.,例：selectlength(oracle)fromdual;selectlower(ename)fromempwheredeptno=10;selectSUBSTR(ename,1,3)fromempwheredeptno=10;,selectCONCAT(ename,job)fromempwheredeptno=10;,selectLPAD(ename,10,*)fromempwheredeptno=10;,日期时间函数1.sysdate函数返回当前数据库的日期时间，没有参数。2.add_months(x,y)函数此函数返回在日期x基础上再加上y个月后新的日期。3.last_day(x)函数此函数返回日期x的本月最后一天的日期。4.months_between(x,y)函数此函数返回日期x和日期y之间的月数。5.next_day(d,dow)返回日期d后由dow给出的条件的第一天，dow使用当前会话中给出的语言指定了一周中的某一天，返回的时间分量与d的时间分量相同。,Oracle默认日期格式：DD-MON-YYselectsysdatefromdual;,selectMONTHS_BETWEEN(sysdate,hiredate)/12fromempwheredeptno=10;,转换函数1.to_char(x,format-model)按照format-model的格式把x转换为相应的字符串。2TO_DATE(c,fomat-model)c表示字符串，format-model表示一种特殊格式的字符串。返回format-model格式的字符串c对应的日期。函数将字符串c转换成date数据类型。3TO_NUMBER(c,fomat-model)c表示字符串，format-model表示一个特殊格式的字符串，函数返回format-model格式的数字。函数将字符串c转换为相应的数字。,例：selectto_char(sysda