《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语句的语法格式如下 SELECT DISTINCTselect listFROMtable source WHEREsearch condition GROUPBYgroup by expression HAVINGsearch condition ORDERBYorder expression ASC DESC 其中 DISTINCT 用于删除结果集中重复的行 select list指明要查询的选择列表 列表可以包括若干个列名或表达式 列名或表达式之间用逗号隔开 用来指示应该返回哪些数据 表达式可以是列名 函数或常数的列表 FROMtable source指定所查询的表或视图的名称 WHEREsearch condition指明查询所要满足的条件 GROUPBYgroup by expression根据指定列中的值对结果集进行分组 HAVINGsearch condition对用FROM WHERE或GROUPBY子句创建的中间结果集进行的筛选 它通常与GROUPBY子句一起使用 ORDERBYorder expression ASC 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 fromempwheresalbetween2500and3500andhiredatebetween 01 1月 81 and 31 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 fromempwhereenamelike A 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 fromempwheresal all selectsalfromempwheredeptno 20 显示高于部门编号为20的任意员工的工资的员工信息 select fromempwheresal any 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 selectdeptnofromdeptwheredeptno 30 selectdeptno enamefromempwherejob CLERK selectdept deptno emp deptno enamefromdept empwheredept deptno 30andemp job CLERK 事务控制 日常生活中我们需要一种能保证数据完整性的机制 比如学生父母给孩子打的生活费要么发送成功 要么发送失败 不应该出现一边发送成功 另一边接受不到的情况 这样才是安全的 可靠的 数据库管理中也需要类似的机制 那就是事务 事务是作为单个逻辑工作单元执行的一系列操作 这一系列操作或者都被执行或者都不被执行 在一个事务中可以包括一条或多条SQL语句 如果一个事务执行成功 则表明该事务中的所有语句均执行成功 只要事务中的一条语句出现错误 则会取消整个事务的执行 事务做为一个逻辑工作单元必须有四个属性 称为ACID 原子性 一致性 隔离性和持久性 属性 只有这样才能成为一个事务 事务具有以下4个属性 1 原子性 事务应作为一个逻辑工作单元看待 事务中的所有语句要么全部执行 要么全部取消 2 一致性 事务中的任何操作都不能破坏数据库的完整性 事务如果能够执行成功 则事务所修改的数据必须遵循数据库中各种约束 规则的要求 所有数据应该处于一致性状态 3 隔离性 当多个事务同时运行时 不同的事务互不干扰 独立运行 4 永久性 当事务执行成功后 事务对数据库所做的操作结果会永久保持 在Oracle中 可以使用commit命令提交完成的事务 使用rollback回滚事务 使用savepoint设置保留点 事务开始于第一条可执行语句 事务结束 遇到commit或rollback语句遇到DDL或DCL语句用户会话结束系统崩溃事务的提交和回滚显式的提交和回滚操作是为了更好地保证数据的一致性 下述情况下事务会被自动提交 执行一个DDL语句 执行一个DCL语句 正常结束会话会话异常终止 系统崩溃时事务会被自动回滚 SqlPlus的自动提交SqlPlus中执行SQL语句时可以设置是否自动提交 缺省设置为非自动提交 查看设置 showautocommit autocommitOFF autocommitIMMEDIATE改变设置 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 字符函数1 ASCII c 返回字符c对应的ASCII 十进制数 2 SUBSTR c m 返回C的子串 其中m是子串开始的位置 n是子串的长度 3 LENGTH c 返回字符串c的长度 4 LOWER c 返回字符串c的所有字符的小写 5 UPPER 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转换为相应的字符串 2 TO DATE c fomat model c表示字符串 format model表示一种特殊格式的字符串 返回format model格式的字符串c对应的日期 函数将字符串c转换成date数据类型 3 TO NUMBER c fomat model c表示字符串 format model表示一个特殊格式的字符串 函数返回format model格式的数字 函数将字符串c转换为相应的数字 例 selectto char sysdate YYYY MM DDHH24 MI SS fromdual selectename to char hiredate yyyy mm dd hiredatefromempwheredeptno 20 insertintoemp empno ename hiredate values 9005 WANG TO DATE 1999 12 12 YYYY MM DD 其他函数大家知道 NULL值表示一个未知数据或者一个空值 算术操作符的任何一个操作数为NULL值 结果均为NULL值 这个规则也适合很多函数 但是在数据库查询中如果查询的结果为NULL 就无法参与运算了 如 300 NULL 这是非法的 怎么办呢 函数NVL专门处理空值NULL 格式为 NVL x1 x2 其中x1和x2都是表达式 当x1不为null时返回x1 否则返回x2 参数x1和x2可以是任意数据类型 但必须匹配 例 selectename sal comm sal nvl comm 0 fromempwheredeptno 30orderbyename NVL2 x1 x2 x3 其中x1 x2和x3都是表达式 当x1不为null时返回x2 否则返回x3 参数x1可以是任意数据类型 但参数x2和x3不能是long数据类型 而且二者必须与x1匹配 例 selectename sal comm NVL2 comm sal comm sal fromempwheredeptno 20orderbyename 上机练习 打开SQL PLUS 进行下述练习 1请从表EMP中查找工种是职员CLERK或经理MANAGER的雇员姓名 工资 2请在EMP表中查找部门号在10 30之间的雇员的姓名 部门号 工资 工作 3请从表EMP中查找姓名以J开头所有雇员的姓名 工资 职位 4请从表EMP中查找工资低于2000的雇员的姓名 工作 工资 并按工资降序排列 5请从表中查询工作是CLERK的所有人的姓名 工资 部门号 部门名称以及部门地址的信息 6查询表EMP中所有的工资大于等于2000的雇员姓名和他的经理的名字 7在表EMP中查询所有工资高于JONES的所有雇员姓名 工作和工资 7列出没有对应部门表信息的所有雇员的姓名 工作以及部门号 8雇员中谁的工资最高 9查询所有81年7月1日以前来的员工姓名 工资 所属部门的名字 10查询各部门中81年1月1日以后来的员工数 11建立一个表 my emp 表结构与EMP相同 并将emp表中的记录插入到该表中 12将my emp中与员工SCOTT工资相同的员工的工资都增加300元 13练习课本上事务控制的例子