OracleSQL实用基础教程.ppt

王忠海2020 2 24 OracleSQL实用基础教程 SQL概述 历史SQL StruceuredQueryLanguage1974年 由Boyce和Chamber提出1975 1979年 在SystemR上实现 由IBM的SanJose研究室研制 称为Sequel SQL概述 标准化有关组织ANSI AmericanNaturalStandardInstitute ISO InternationalOrganizationforStandardization 有关标准SQL 86 数据库语言SQL SQL 89 具有完整性增强的数据库语言SQL 增加了对完整性约束的支持SQL 92 数据库语言SQL 是SQL 89的超集 增加了许多新特性 如新的数据类型 更丰富的数据操作 更强的完整性 安全性支持等 SQL 99正在讨论中的新的标准 将增加对面向对象模型的支持 SQL概述 特点一体化集DDL DML DCL于一体单一的结构 关系 带来了数据操作符的统一面向集合的操作方式一次一集合高度非过程化用户只需提出 做什么 无须告诉 怎么做 不必了解存取路径两种使用方式 统一的语法结构SQL既是自含式语言 用户使用 又是嵌入式语言 程序员使用 语言简洁 易学易用 SQL概述 1SQL命令基础 准备工作 用SQLPLUS来学习SQL连接到SQLPLUS 创建学习用的用户和数据SCOTT用户在ORACLE805中默认已经创建 在8i和9i中需要手动运行 rdbms admin scott sql 是ORACLE安装的主目录 在SQLPLUS中可以用 来代替 例如 SQL connect assysdbaConnected SQL rdbms admin scott sqlSQL connectscott tigerConnected 基本的SELECT命令 SELECT命令用于从数据库中获得想要的信息 语法 SELECT column alias FROMtable 一个最简单的查询语句至少要包括SELECT子句和FROM子句 SELECT后面指定要选择的列FROM后面指定从哪些表或视图中获取数据 SQL语句基本规则 SQL命令是大小写不敏感SQL命令可写成一行或多行一个关键字不能跨多行或缩写子句通常位于独立行 以便编辑 并易读空格和缩进使程序易读关键字大写 其他小写 使用SELECT 可显示所有的列 SQL SELECT FROMdept DEPTNODNAMELOC 10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON 选择指定的列 我们可以在SELECT后面指定要选择的列 SQL SELECTdeptno locFROMdept DEPTNOLOC 10NEWYORK20DALLAS30CHICAGO40BOSTON 在SQL PLUS中查看表具有哪些列 在SQLPLUS中 用DESCTABLENAME命令可以查看表具有的列以及类型等 SQL descdept名称是否为空 类型 DEPTNONUMBER 2 DNAMEVARCHAR2 14 LOCVARCHAR2 13 从数据字典中获取表的列信息 SQL SELECTTABLE NAME COLUMN NAME DATA TYPE DATA LENGTH NULLABLEFROMUSER TAB COLUMNSWHERETABLE NAME DEPT TABLE NAMECOLUMN NAMEDATA TYPEDATA LENGTHNULLABLE DEPTDEPTNONUMBER22YDEPTDNAMEVARCHAR214YDEPTLOCVARCHAR213Y 使用算术运算 可以对数字类型的字段进行算术运算 运算的先后顺序是先乘除 后加减 括号优先 上面例子中 计算的是一年的报酬 月工资乘以12个月 再加上100 SQL SELECTename sal 12 sal 100FROMemp ENAMESAL12 SAL 100 KING500060100BLAKE285034300CLERK245029500JONES297535800 14rowsselected 关于空值 NULL 空值是指不可用 不知道 不适用的值空值不等于零也不等于空格对空值进行的任何运算仍然为空值 SQL SELECTenameNAME 12 sal commFROMempWHEREename KING NAME12 SAL COMM KING 定义列的别名 当显示查询结果时 SQL PLUS通常使列名作为列头 在很多情况下 列名并非是此列的清晰描述 因此我们就可使用列的别名作为列头 缺省情况下 列的别名是大写的 如果区别大小写 可加双引号 如有特殊字符如 也必须使用双引号将其括起来 上面的例子中的AS可以省略 SQL SELECTenameASname salsalaryFROMemp NAMESALARY SQL SELECTename Name sal 12 AnnualSalary FROMemp NAMEAnnualSalary 列连接操作 使用双竖条 操作符 可将列和运算表达式常量连起来显示 形成一个输出显示 SQL SELECTENAME ssalaryis sal Employee sSalary fromemp Employee sSalary SMITH ssalaryis800ALLEN ssalaryis1600WARD ssalaryis1250JONES ssalaryis2975 已选择14行 去除重复记录 默认情况下 显示所有行 包括重复记录 如果想去掉重复记录 可以在DISTINCT关键字 如果在DISTINCT后面指定了多个列 则DISTINCT将对所有被选择的列有效 其结果是不同的列的组合 SQL SELECTdeptnoFROMemp DEPTNO 103010 14rowsselected SQL SELECTDISTINCTdeptnoFROMemp DEPTNO 102030 SQL PLUS程序介绍 SQL PLUS是是Oracle自带的与Oracle交互的一个工具 你可在SQL PLUS中做以下操作 执行SQL命令来修改 查询 增加 删除数据库中的数据格式化 计算 存储 数据于一定格式的报告中产生用于存储SQL命令的脚本 以便以后执行SQL PLUS专用命令可被分为如下类别 环境 影响通常的SQL命令格式化 格式化查询结果文件处理 存储 调用 运行脚本文件编辑 修改SQL缓存中的SQL命令显示列的定义 登陆到SQL PLUS 在命令行提示符下输入SQLPLUS NOLOG然后在SQL 提示符下输入CONNECTUSERNAME PASSWORD DATABASE C DocumentsandSettings Administrator SQLPLUS NOLOGSQL Plus Release9 2 0 4 0 Productionon星期四5月2409 51 422007Copyright c 1982 2002 OracleCorporation Allrightsreserved SQL connectsystem manager tlgaxz已连接 SQL PLUS的编辑命令 SQL PLUS的命令每次只能键入一行 且不能存于SQL缓存中A PPEND text将text加到当前行的后面C HANGE old new将当前行的old改为newC HANGE text 从当前行中删除textC LEAR BUFF ER 从缓存中删除所有的行DEL删除当前行L IST 列出SQL缓存中的所有行L IST n 列出一行 由n指出列出的行 R UN 显示并运行SQL缓存中的SQL语句 SQL PLUS的编辑命令 续 GETfilename ext 把文件的内容写到SQL缓存 filename ext 运行文件ED IT 调用编辑器编辑当前缓存中的内容ED IT filename ext 调用编辑器编辑存的文件SPOOL filename ext 将查询结果存于文件中SPOOLOFF结束结果内容输出EXIT 退出SQL PLUS 2限定和排序数据 目的 限制某一查询所取记录排序查询结果 使用选择限定记录 在上面的例子中 假定你想要显示部门10的所有员工 这种方式是基于WHERE子句的SQL命令 使用where子句限定返回的记录 WHERE子句在FROM子句后面Condition 由列名表达式 常量和比较操作符组成 SELECT DISTINCT column alias FROMtable WHEREcondition s 使用WHERE语句 上面例子中返回job CLERK 的所有员工的name job和deptno注意 字符的大小写是敏感的 SQL SELECTename job deptnoFROMempWHEREjob CLERK ENAMEJOBDEPTNO KINGCLERK30BLAKECLERK20TURNERCLERK10 14rowsselected 字符串和日期 在WHERE子句中的字符串和日期必须用单引号括起来 所有的字符是大小写敏感的 Oracle存储日期是以内定的格式存放 它们代表世纪 年 月 日 小时 分钟和秒 缺省显示的日期格式是DD MON YY 也可能是其他格式 为了避免日期字段查询条件不同格式下可能造成的错误 通常用TO DATE函数来进行转换 日期类型查询条件举例 上面第二个例子使用了TO DATE函数 就不再受日期格式的影响了 否则同样的查询 同样的表数据 不同的系统日期格式会导致结果不正确 SQL SELECTENAME HIREDATEFROMEMPWHEREHIREDATE 03 DEC 81 ENAMEHIREDATE JAMES03 DEC 81FORD03 DEC 81SQL SELECTENAME HIREDATEFROMEMPWHEREHIREDATE TO DATE 19811203 YYYYMMDD ENAMEHIREDATE JAMES03 DEC 81FORD03 DEC 81 常用比较运算符 使用BETWEEN SQL SELECTename salFROMempWHEREsalBETWEEN1000AND1500 ENAMESAL KING1250BLAKE1500CLERK1250JONES1300 注意 BETWEEN后面要先写低值 后写高值 使用IN运算符 SQL SELECTempno ename sal mgrFROMempWHEREmgrIN 7902 7566 7788 EMPNOENAMESALMGR 7902KING125075667369BLAKE150079027788CLARK125075667876JONES13007788 使用LIKE运算符 使用LIKE运算符执行通配查询查询条件可包含文字字符或数字 可表示零或多个字符 可表示一个字符 SQL SELECTenameFROMempWHEREenameLIKE S ENAME SMITHSCOTT 用LIKE和ESCAPE来查找包含特殊字符的数据 例如如果想查找表EMP中ENAME包含下划线 的数据 就需要用到ESCAPE选项 否则查询结果不准确 ESCAPE后面单引号内只能有一个字符 表示前面的LIKE条件中这个字符后面的第一个字符当作普通字符处理 SQL SELECTEMPNO ENAMEFROMEMPWHEREENAMELIKE ESCAPE EMPNOENAME 9999FOR TEST 使用ISNULL 查询包含空值的记录 SQL SELECTename mgrFROMempWHEREmgrISNULL ENAMEMGR KING 逻辑运算符 优先级次序 1所有的比较运算2NOT3AND4OR括号将跨越所有优先级 使用AND运算符 AND需要条件都满足 SQL SELECTempno ename job salFROMempWHEREsal 1100ANDjob CLERK EMPNOENAMEJOBSAL 7369BLAKECLERK13007788CLARKCLERK1250 使用OR运算符 OR需要满足条件之一即可 SQL SELECTempno ename job salFROMempWHEREsal 1100ORjob CLERK EMPNOENAMEJOBSAL 7369BLAKECLERK13007788CLARKCLERK12507839KINGPERSIDENT50007645MARTINMANAGER1050 使用NOT运算符 SQL SELECTename jobFROMempWHEREjobNOTIN CLERK MANAGER ANALYST ENAMEJOB KINGPERSIDENTMARTINSALESMANWARDSALESMAN ORDERBY语句 在缺省情况下 查询返回的结果是没被排序的 使用ORDERBY子可将记录排序 ORDERBY子句放在最后 ASC表示升序排序 DESC表示降序排序 缺省为ASC SELECTexprFROMtable WHEREcondition ORDERBY column expr ASC DESC 降序排列 SQL selectename hiredatefromemporderbyhiredatedesc ENAMEHIREDATE FOR TESTADAMS12 JAN 83SCOTT09 DEC 82MILLER23 JAN 82JAMES03 DEC 81SMITH17 DEC 80 15rowsselected 使用列别名排序 SQL selectempno ename sal 12annualfromemporderbyannual EMPNOENAMEANNUAL 7369SMITH96007900JAMES114007876ADAMS132007521WARD150007654MARTIN150007934MILLER156007844TURNER18000 15rowsselected 按照多个列排序 SQL SELECTename sal deptnoFROMEMPORDERBYdeptno salDESC ENAMESALDEPTNO KING500010CLARK245010MILLER130010SCOTT300020FORD300020JONES297520ADAMS110020 15rowsselected 3单行函数 SQL函数 SQL函数有两种不同的SQL函数 单行函数 多行函数单行函数这些函数仅作用于单行记录 并对每行记录返回一个值 有许多不同类型的单行函数 常用的类型有 字符函数 数字函数 日期函数 转换函数多行函数这些函数作用于记录组 每组记录返回一个结果 单行函数 单行函数单行函数操作数据项 它们接收一个或多个参数 并对查询出的每一条记录返回一个值 参数可以是 用户提供的常量 一个列名 一个表达式单行函数的特性 它们作用于查询的每一条记录 每条记录返回一个结果 它们可返回一个不同于它所参照的数据类型 它们可嵌入到SELECT WHERE和ORDERBY子句 字符串函数 字符函数被分为 大小写转换函数 字符处理函数LOWER column expression 将字符转换为小写UPPER column expression 将字符转换不大写INITCAP column expression 将每一个单词的第一个字母大写其它小写CONCAT column expression 返回第一个串接上第二个串 它的作用和 运算是相同的SUBSTR column expression 返回从字母m开始 有n个字符长的字符串 LENGTH column expression 返回字符串长度INSTR column1expression m n 返回字符串中字符的位置LPAD column1cxpression n string 在字符串前填补字符 使其长度达到n RPAD column1cxpression n string 在字符串后填补字符 使其长度达到n 字符串函数举例 上面的例子将ename列的首字母大写 其余字母小写 SQL SELECTINITCAP ename FROMemp INITCAP EN SmithAllenWard 字符串处理函数举例 SQL SELECTENAME SUBSTR ENAME 1 3 SUBSTR RPAD ENAME 10 RPAD LENGTH ENAME LENGTH FROMEMP ENAMESUBSTRRPADLENGTH SMITHSMISMITH 5ALLENALLALLEN 5WARDWARWARD 4JONESJONJONES 5MARTINMARMARTIN 6 数字函数 数字函数接收数字输入返回数字值ROUND column expression n 返回舍入到小数点右边n位的值TRUNC column expression n 返回截断到n位的值MOD m n 返回m和n相除后的余数 使用ROUND函数 上面的例子分别显示45 923到小数点后两位 个位 十位 SQL SELECTROUND 45 923 2 ROUND 45 923 0 ROUND 45 923 1 FROMDUAL ROUND 45 923 2 ROUND 45 923 0 ROUND 45 923 1 45 924650 使用TRUNC函数 显示45 923到小数点后两位 个位 十位 SQL SELECTTRUNC 45 923 2 TRUNC 45 923 0 TRUNC 45 923 1 FROMDUAL TRUNC 45 923 2 TRUNC 45 923 0 TRUNC 45 923 1 45 924540 使用MOD函数 这个例子计算工资除以奖金后的余数 SQL SELECTename sal comm MOD sal comm FROMempWHEREjob SALESMAN ENAMESALCOMMMOD SAL COMM ALLEN1600300100WARD1250500250MARTIN125014001250TURNER150001500 使用日期函数 Oracle的日期函数Oracle使用内部的数字化格式存储日期 它们代表世纪 年 月 日 小时 分钟和秒 缺省显示的日期格式为DD MON YY 有效的日期在公元前4712年1月1日到公元后9999年12月31日SYSDATESYSDATE是一个返回当前日期和时间的日期函数DUALDUAL是一个SYS用户所拥有的表 所有的用户都可以访问 它包括一个列DUMMY和一条记录值为X 例子 显示当前的日期SQL SELECTSYSDATEFROMSYS DUAL SYSDATE 24 MAY 07 日期运算 日期 数字 日期加天数日期 数字 日期减天数日期 日期 数字两日期间的天数日期 number 24 日期加小时注意 两个日期类型字段不能相加 日期运算举例 SQL SELECTENAME SYSDATE HIREDATEFROMEMP ENAMESYSDATE HIREDATE SMITH9654 54954ALLEN9589 54954WARD9587 54954JONES9548 54954MARTIN9369 54954 常见的日期函数 日期函数使用举例 SQL SELECTSYSDATE ADD MONTHS SYSDATE 12 LAST DAY SYSDATE NEXT DAY SYSDATE FRIDAY FROMDUAL SYSDATEADD MONTHSLAST DAY SNEXT DAY S 2007 05 242008 05 242007 05 312007 05 25 转换函数 转换函数用于数据类型之间的转换 SQL尽可能地自动进行转换 它会隐含地调用转换函数 但是你无法对隐含调用中使用的格式指定符进行控制 并且这会使得你的代码很难理解 因此使用显式转换函数而不依赖于隐式转换是一个很好的程序设计风格 转换函数 Oracle提供了3个转换函数 TO CHAR number date fmt 将数字或日期按格式转换成字符 TO NUMBER char 将字符串转换成数字 此字符串必须是数字 TO DATE CHAR fmt 将字符串按指定的格式转换成日期 使用TO CHAR操作日期函数举例 上面的例子中 YYYY代表4位的年份 MM代表月份 HH24代表24小时 MI代表分钟 SS代表秒 SQL SELECTSYSDATE TO CHAR SYSDATE YYYY MM DDHH24 MI SS DETAILDATEFROMDUAL SYSDATEDETAILDATE 24 MAY 072007 05 2413 47 32 使用TO DATE函数举例 上面的例子 如果不用TO DATE进行转换 会怎么样呢 SQL SELECTENAME HIREDATEFROMEMPWHEREHIREDATE TO DATE 19820101 YYYYMMDD ENAMEHIREDATE SCOTT09 DEC 82ADAMS12 JAN 83MILLER23 JAN 82 SQL SELECTENAME HIREDATEFROMEMPWHEREHIREDATE 19820101 SELECTENAME HIREDATEFROMEMPWHEREHIREDATE 19820101 ERRORatline1 ORA 01861 literaldoesnotmatchformatstring 因为19820101不是采用默认日期格式写的 Oracle无法进行隐含转换 导致报错 因此可以看出 对于可能发生转换的语句 应该显式指定转换 NVL函数 将空值转换为实际的值数据格式可以是日期 字符 数字数据类型必须匹配 NVL comm 0 如果comm为空 则转换为0NVL hiredate 01 JAN 97 如果hiredate为空 则转换为 01 JAN 97 NVL job NoJobYet 如果job为空 则转换为 NoJobYet 使用NVL SQL SELECTename comm sal sal 300 sal 12 NVL comm 0 FROMemp ENAMECOMMSALSAL 300 SAL 12 NVL COMM 0 SMITH80011009600ALLEN3001600190019500WARD5001250155015500JONES2975327535700MARTIN14001250155016400BLAKE2850315034200CLARK2450275029400SCOTT3000330036000 4从多个表中选择数据 从多个表中获取数据 有时候你需要从多个表中获得数据 在上面的例子中 报告显示的数据取自两个表 EMPNO存在于EMP表中 DEPTNO在EMP和DEPT表中都有 LOC存在于DEPT表中为了生成上面的报告 你需要将表EMP和DEPT连起来 从两个中获取数据 定义连接 当从两个以上的表中获取数据时 就要使用连接条件 一个表中的记录可以根据两个表的相同列和另一个表的记录相连接 两表中的相同列一般是主键和外键列 为了能显示两个或多个表中的数据 在WHERE子句中需要设简单的连接条件 语法如下 table column指定取数据的表和它的列table column1 table2 column2将表连接起来的条件当写一个有连接的SELECT命令时 为了避免同样的不同表具有同样的列名 应该在列前加表的名字或者表的别名 当在表中有相同的列名时 并且这个列作为显示内容或者查询条件 必须在列名前加表名或表的别名作为前缀 如果要将n个表连起来 你必须指定n 1个连接条件 因此连接4个表需要有3个连接条件 如果你的表有组合主键 此规则可能不适用 此时多一条记录需要多个列唯一标识 笛卡尔结果 笛卡尔结果笛卡尔结果形成于 连接条件被省略 连接条件无效 第一个表的所有记录连接到第二个表的所有记录一个笛卡尔结果趋于产生一个巨大的记录数 通常没有意义 为了避免笛卡尔结果我们要在WHERE子句中使用有效连接 笛卡尔结果举例 SELECT FROMEMP DEPT 连接类型 有两种主要的连接条件等值连接非等值连接其它的连接方式包括多连接自连接定置运算符 什么是等值连接 就是两个表连接的WHERE条件是一个表的列等于另外一个表的列 通常情况下 这种连接是主键和外键的连接 使用等值连接获取记录举例 因为DEPTNO列在EMP和DEPT中都存在 因此需要在这个列前面加上表名 否则Oracle认为有歧义 语句无法执行 SQL SELECTENAME EMP DEPTNO DNAMEFROMEMP DEPTWHEREEMP DEPTNO DEPT DEPTNO ENAMEDEPTNODNAME MILLER10ACCOUNTINGKING10ACCOUNTINGCLARK10ACCOUNTINGFORD20RESEARCHADAMS20RESEARCHSCOTT20RESEARCH 额外的条件 除了连接条件 可能还有额外的查询条件 例如 显示员工King的员工号 各字 部门号和部门位置 这时在WHERE子句中需要设定一个额外的条件 SQL SELECTempno ename emp deptno LocFROMemp deptWHEREemp deptno dept deptnoANDINITCAP ename King EMPNOENAMEDEPTNOLOC 7839KING10NEWYORK 使用表的别名 表的别名使用表名限定列名可能会很浪费时间 尤其是当表名特别长 这时你可以使用表的别名 使用表的别名会减少程序代码 因此占用较少的内存 注意 表的别名是在FROM子句中指定的 表别名规则 表的别名最长为30个字符 但通常以短字符为佳表的别名最好有一定的含义表的别名只在当前的SELECT语句有效如果在FROM子句定义了表的别名 在SELECT子句中必须用它来替代表名 SQL SELECTempno ename e deptno LocFROMempe deptdWHEREe deptno d deptnoANDINITCAP ename King EMPNOENAMEDEPTNOLOC 7839KING10NEWYORK 非等值连接 在EMP表和SALGRADE表中 没有直接的对应列 它们之间的关系是EMP的SAL列的值在SALGRADE表的LOSAL和HISAL列之间 它们是不等值连接 非等值连接举例 SQL SELECTe sal e ename s gradeFROMempe salgradesWHEREe salBETWEENs losalANDs hisal SALENAMEGRADE 5000KING53000SCOTT43000FORD42975JONES42850BLAKE42450CLARK41600ALLEN31500TURNER31300MILLER2 外连接 使用外部连接 返回连接两边有一边为NULL的记录外连接运算符是加号 外连接运算符 可以加在左边 也可以加在右边 但不能两边同时加外连接有 的一边表示这边的值要么等于另外一边 要么为NULL从9i开始 SQL支持ANSISQL 也就是支持LEFTOUTERJOIN RIGHTOUTERJOIN和FULLOUTERJOIN SQL SELECTtable column table columnFROMtable1 table2WHEREtable1 column table2 column SQL SELECTtable column table columnFROMtable1 table2WHEREtable1 column table2 column 外连接举例1 这个例子 在e deptno这边 意味着e deptno可以是NULL SQL SELECTename e deptno E DEPTNO d deptno D DEPTNO d dnameFROMEMPE DEPTDWHEREE DEPTNO D DEPTNO ENAMEE DEPTNOD DEPTNODNAME SMITH2020RESEARCHALLEN3030SALES FORD2020RESEARCHMILLER1010ACCOUNTING40OPERATIONS15rowsselected 外连接举例2 这个例子 在d deptno这边 意味着d deptno可以是NULL SQL SELECTename e deptno E DEPTNO d deptno D DEPTNO d dnameFROMEMPE DEPTDWHEREE DEPTNO D DEPTNO ENAMEE DEPTNOD DEPTNODNAME MILLER1010ACCOUNTINGKING1010ACCOUNTING ALLEN3030SALESwzh90FOR TEST9016rowsselected 外连接举例3 从9i开始 可以用ANSISQL语法来写外连接 这样也提供了一个以前的 不能实现的功能 全外连接 SQL SELECTE ENAME E DEPTNO E DEPTNO D DEPTNO D DEPTNO D DNAMEFROMEMPEFULLOUTERJOINDEPTDON E DEPTNO D DEPTNO ENAMEE DEPTNOD DEPTNODNAME MILLER1010ACCOUNTINGKING1010ACCOUNTING WARD3030SALESALLEN3030SALESwzh90FOR TEST9040OPERATIONS17rowsselected 表的自连接 有的时候 需要对表进行自连接 例如上图所示 EMP表中的MGR列的代表员工的经理的员工编号 所以要想显示出每个员工的经理就要对EMP进行自连接 用MGR EMPNO 实际上 自连接只是等连接 等外连接 的一个特例 同一张表用不同的别名 区别成了不同的表 表自连接举例 上面这个例子相当一个等外连接 如果员工有经理 则显示出员工为谁工作 打工者 否则显示员工为自己干活 老板 SQL SELECTE ENAME worksfor NVL M ENAME himself RELATIONSFROMEMPE EMPMWHEREE MGR M EMPNO RELATIONS FORDworksforJONES JONESworksforKINGSMITHworksforFORDKINGworksforhimself14rowsselected 5使用分组函数 什么是分组函数 和单行函数不同 分组函数作用于一组记录 每一组返回一个结果 这些组可能是整个表 也可能是由GROUPBY子句将表分成的多个组 主要的分组函数 COUNT DISTINCE ALL expr 返回记录数 这里expr赋非空值 表示所有被选择的记录 包括重复记录和空值 MAX DISTNCT ALL expr 表达式的最大值 忽略空值MIN DISTNCT ALL expr 表达式的最小值 忽略空值AVG DISTNCT ALL expr 平均值 忽略空值STDDEV DISTINCT ALL X 返回标准差 忽略空值SUM DIXNTICT ALL n 求和 忽略空值VARIANCE DISTINCT ALL X 返回统计方差这些分组函数中 COUNT是不计算NULL值的 其它函数忽略NULL值 使用分组函数举例1 SQL SELECTSUM SAL MAX SAL MIN SAL AVG SAL FROMEMP SUM SAL MAX SAL MIN SAL AVG SAL 2902550008002073 21429 使用分组函数举例2 SQL SELECTCOUNT FROMEMP COUNT 16SQL SELECTCOUNT EMPNO FROMEMP COUNT EMPNO 16SQL SELECTCOUNT MGR FROMEMP COUNT MGR 13 产生数据组 GROUPBY子句 GROUPBY子句使用GROUPBY子句将一个表分成许多小组 并对每一个小组返回一个计算值 Group by expression 指定按什么列分组规则 在SELECT子句中 如果使用分组函数 不能对GROUPBY子句中指定的列使用分组函数 使用WHERE子句 可预先排除某些记录在GROUPBY子句中必须有表中的列在GROUPBY子句中不能使用列的别名缺省情况下在GROUPBY子句中的列以升序排 你可以使用orderby子句改变它 SELECTcolumn group functionFROMtable WHEREcondition GROUPBYgroup by expression ORDERBYcolumn 使用GROUPBY举例 注意 不是分组的列不能出现在SELECT后面 否则会提示 ORA 00979 notaGROUPBYexpression 组函数也不能出现在WHERE子句中 否则会提示ORA 00934 groupfunctionisnotallowedhere SQL SELECTDEPTNO SUM SAL MAX SAL MIN SAL AVG SAL FROMEMPGROUPBYDEPTNO DEPTNOSUM SAL MAX SAL MIN SAL AVG SAL 108750500013002916 6666720108753000800217530940028509501566 66667 多列分组举例 有时你可能需要在组中再分组 上面例子中显示每个部门中不同头衔的工资和与平均工资 此时EMP表首先以部门分组 然后按头衔分组 SQL SELECTDEPTNO JOB SUM SAL AVG SAL FROMEMPGROUPBYDEPTNO JOB DEPTNOJOBSUM SAL AVG SAL 10CLERK1300130010MANAGER2450245010PRESIDENT5000500020CLERK190095020ANALYST6000300020MANAGER2975297530CLERK95095030MANAGER2850285030SALESMAN56001400 使用HAVING子句限定分组函数结果值 前面我们讲过 分组函数不能写在WHERE子句中 如果要对分组函数结果值进行限定 可以用HAVING子句 SQL SELECTDEPTNO JOB SUM SAL AVG SAL FROMEMPGROUPBYDEPTNO JOBHAVINGAVG SAL 2000 DEPTNOJOBSUM SAL AVG SAL 10MANAGER2450245010PRESIDENT5000500020ANALYST6000300020MANAGER2975297530MANAGER28502850 练习 练习分组函数的使用练习GROUPBY子句练习HAVING子句掌握分组函数中易犯的错误 6子查询 使用子查询解决问题 假设想知道谁的工资高于Jones 为了解决这个问题 必须执行两个查询 第一个查询查到了Jones的工资 第二个查询查找高于这个工资的人 可以将这两个查询组合起来 将一个查询放在另一个中来解决此问题 一个内部的查询或子查询返回一个值 此值被外部查询或主查询使用 使用子查询等价于执行两个顺序查询 第一个查询的结果作为第二个查询检索的值 子查询语法 子查询在主查询前执行一次主查询使用子查询的结果 SELECTselect listFROMtablenameWHEREexpr operator SELECTselect listFROMtable 子查询举例 SQL selectename salfromempwheresal selectsalfromempwhereename JONES ENAMESAL SCOTT3000KING5000FORD3000 子查询类别 单行子查询 内部SELECT命令返回一条记录多行子查询 内部SELECT命令返回多条记录多列子查询 内部SELECT命令返回多个数据列 子查询使用规则 子查询要用括号括起来将子查询放在比较运算符的右边子查询中不要加ORDERBY子句对单行子查询使用单行运算符 如 也可以使用多行运算符对多行子查询使用多行运算符 IN ANY ALL属于多行运算符 子查询容易犯的错误1 用单行操作符 但是子查询返回多行 SQL selectename salfromempwheresal selectsalfromemp selectename salfromempwheresal selectsalfromemp ERRORatline1 ORA 01427 single rowsubqueryreturnsmorethanonerow 子查询容易犯的错误2 另一个常见的错误是内层查询没有返回记录 SQL selectename salfromempwheresal selectsalfromempwhereename NOTHING norowsselected 多行子查询举例 查找个部门工资最高的员工 SQL SELECTENAME DEPTNO SALFROMEMPWHERESALIN SELECTMAX SAL FROMEMPGROUPBYDEPTNO ENAMEDEPTNOSAL KING105000FORD203000SCOTT203000BLAKE302850 在多行子查询中使用ANY ANY运算子查询返回每个值 上面例子中返回工资低于任何CLERK的员工 并且他不是CLERK CLERK工资最高值为 1300 上面的例子返回工资低于 1300 且非CLERK的员工 ANY 意味着大于最小值 ANY 等于IN SQL SELECTename deptno job sal2FROMempWHEREsal CLERK ENAMEDEPTNOJOBSAL WARD30SALESMAN1250MARTIN30SALESMAN1250 7操作数据 课程目标 在这一节课里 你将学会如何往表中插入记录 如何修改和删除表中的记录 另外 你还将学会如何用COMMIT和ROLLBACK语句控制事务 数据操作语言 DML 当你想在数据库中增加 修改或删除数据时 你就要执行DML语句 由一组DML语句组成的逻辑工作单元叫做一个事务 DML语句可以SQL PLUS或SQL DBA中直接执行 也可以在象Developer 2000 TOAD这样的工具中执行 还可以在SQL预编译程序中执行 数据操作语言主要有INSERT UPDATE和DELETE INSERT语句 table 表名column 列名value 列的值通过INSERT语句在表中增加一条新记录 如果省略表后面的列 那么要在VALUES后面按照表的顺序指定所有的列的值 INSERTINTOtable column column VALUES value value INSERT举例 如果表中的列没有在VALUES中列出来 则系统分配给这些列默认值 没有默认值则为空 如果该列不能为空 则插入语句不成功 如果违反约束条件 插入语句同样不能成功 SQL INSERTINTOdept deptno dname loc VALUES 50 DEVELOPMENT BEIJING 1rowcreated 从其它表中拷贝数据 SQL INSERTINTOMANAGERS empno ename sal hiredate SELECTempno ename sal hiredateFROMempWHEREjob MANAGER 3rowscreated UPDATE语句 用UPDATE语句可以一次修改一条或者多条记录 如果没有WHERE条件 则所有的SET后面的列的值都会被更新 UPDATEtableSETcolumn value column value WHEREcondition UPDATE举例 给EMP表中部门编号是30的员工工资增加20 SQL UPDATEEMPSETSAL SAL 1 2WHEREDEPTNO 30 6rowsupdated DELETE语句 用DELETE语句从表中删除数据 如果不加WHERE条件 那么所有的记录都将被删除 DELETE FROM table WHEREcondition 从表中删除指定的记录 SQL DELETEFROMDEPTWHEREDNAME DEVELOPMENT 1rowdeleted 提交所作的修改 用INSERT UPDATE和DELETE语句对数据进行改动后 数据并没有真正的保存 这些改动的数据别的会话中看不到 为了真的保存 需要执行COMMIT命令 一些隐式的COMMIT 如果在SQL PLUS中执行了DML语句 然后虽然没有执行COMMIT 但是如果存在如下情形之一 Oralce会执行一个隐式的COMMIT 一定要注意 1 正常退出SQL PLUS2 执行了一条DDL语句 例如创建了一个表 取消所做的修改 可以用ROLLBACK命令取消所做的修改 隐式的ROLLBACK 如果修改数据后 没有COMMIT 也没有提交 那么下列情况下Oracle会取消所做的修改 1 系统崩溃2 程序异常退出 DML语句应该注意的问题 一定要加合适的WHERE条件 避免错误修改数据执行DML后因该尽快的显式执行COMMIT或者ROLLBACK 因为被改动的数据别的用户是不能修改的 长时间不结束事务会增加死锁的可能性 COMMIT举例 SQL UPDATEempSETdeptno 10WHEREdeptno 7782 1rowupdated SQL COMMIT Commitcomplete ROLLBACK举例 SQL SELECTCOUNT FROMEMP COUNT 16SQL DELETEFROMEMP 已删除16行 SQL SELECTCOUNT FROMEMP COUNT 0SQL ROLLBACK 回退已完成 SQL SELECTCOUNT FROMEMP COUNT 16SQL 8创建并管理表 常用的数据库对象 表 Table 存储数据视图 View 一个或几个表数据的子集序列 sequence 生成主要健值索引 index 加快某些查询速度同义词 synonym 给出对象的另一个可选用名字 命名规则 数据库表和列的命名是根据Oracle数据库对象的标准命名规则进行的 表名和列必须由字母开头 最多有30个字符长 名字只能包含这些字符 大小字母A Z 小写字母a z 数字0 9 下划线 和 在同一个数据库用户中表名不能重复 名字不能是Oracle的保留字 创建表语法 tablename 要创建的表名colname 列名datatype 数据类型 CREATETABLEtablename colnamedatatype colname1datatype 创建表举例 这个例子中 我们创建一个notebook表 包括了ID NAME和PHONE三个字段 其中ID是主键 也就是说 ID不能重复命令一旦执行就会被自动提交 SQL CREATETABLEnotebook idnumber 4 primarykey namevarchar2 30 phonevarchar2 30 Tablecreated 查询自己拥有的对象 通过查询USER OBJECTS数据字典 可以查看到本用户拥有哪些数据对象 提示 如果想看其它用户用有的数据对象 可以查看ALL OBJECTS SQL selectobject name object typefromuser objects OBJECT NAMEOBJECT TYPE BIG TABLETABLEBONUSTABLEDEPTTABLEDUMMYTABLEEMPTABLE 查看自己拥有的表 SQL SELECTtable nameFROMuser tables TABLE NAME MANAGERSNOTEBOOKRUN STATSBIG TABLEEMPDEPTBONUS 常用的数据类型 使用子查询创建表 用CREATETABLE ASSELECT 可以将一个表的结构复制给另外一个表 同时包含满足条件的数据 SQL CREATETABLEEMP2ASSELECT FROMEMP Tablecreated SQL SELECTCOUNT FROMEMP2 COUNT 16 用ALTERTABLE增加列 SQL ALTERTABLENOTEBOOKADDADDRESSVARCHAR2 80 Tablealtered SQL DESCNOTEBOOKNameNull Type IDNOTNULLNUMBER 4 NAMEVARCHAR2 30 PHONEVARCHAR2 30 ADDRESSVARCHAR2 80 用ALTERTABLE修改列 如果发现表的某个列的长度太小 则可以用ALTERTABLE MODIFY 来修改 本例子把ADDRESS改成VARCHAR2 120 SQL ALTERTABLEnotebookMODIFYaddressVARCHAR2 120 Tablealtered SQL DESCNOTEBOOK NameNull Type IDNOTNULLNUMBER 4 NAMEVARCHAR2 30 PHONEVARCHAR2 30 ADDRESSVARCHAR2 120 删除表DROPTABLE 表中所有的数据将被删除事务被提交所有索引被删除不能回退 SQL DROPTABLENOTEBOOK Tabledropped SQL DESCNOTEBOOK ERROR ORA 04043 objectNOTEBOOKdoesnotexist 给表重命名 如果表和其他表之间存在约束关系则不能重命名 SQL RENAMEemp2TOemp10 Tablerenamed SQL descemp10NameNull Type EMPNONOTNULLNUMBER 4 ENAMEVARCHAR2 10 JOBVARCHAR2 9 清空表的内容TRUNCATETABLE TRUNCATETABLE命令将表的全部数据都清空 释放表的存储空间 不需要WHERE语句 而且不可以回退 因此运用此命令一定要慎重 SQL TRUNCATETABLEEMP10 Tabletruncated SQL SELECT FROMEMP10 norowsselected 9索引和其它对象简介 什么是索引 索引是一种模式对象 是为了加快某些查询的访问速度 它的目的是使用索引路径快速定位数据 减少磁场盘I O 索引在Oracle中是自动引用和维护的 当删除一个表时 对应的索引也将被删除 关于索引要考虑到的 一个设计优良的系统应该充分考虑索引的设计 索引不是越多越好 不合适的索引同样会导致系统性能下降 是否需要创建 修改一个索引不是使用者需要考虑的问题 是系统架构师应该考虑的问题 查看某个表具有的索引 SQL SELECTTABLE NAME COLUMN NAME INDEX NAMEFROMUSER IND COLUMNSWHERETABLE NAME CASE M AJZLZ TABLE NAMECOLUMN NAMEINDEX NAME CASE M AJZLZCASEIDPK AJZLZCASE M AJZLZINCEPT