Oracle9i第5章SQL基础.ppt

第四章SQL基础 SELECT语句 SELECT的简单语法 SELECT DISTINCT ALL column1 column2 FROM table 1 subquery alias table 2 subquery alias WHEREcondition CONNECTBYcondition STARTWITHcondition GROUPBYexpn HAVINGexpn UNION ALL INTERSECT MINUS SELECT ORDERBY expn ASC DESC FORUPDATE OF user table view column NOWAIT 简单SELECT查询 selectdeptno dname locfromdept selectdeptno dname locfromdeptorderbydname select fromdeptorderbydeptnodesc dname select fromdeptorderbydname deptnoasc select fromdeptwherednamelike SA orderbydeptnodesc dname 注意 在SQL语句中 引用的字符串必须在单引号内 而不是双引号 如果需要在字符串内放 号 应该写两次 比如语句 select fromdeptwheredname SA ES 中的 SA ES 字符就是如此 要注意空值的表示方法是ISNULL或ISNOTNULL 在Oracle中使用 来匹配任何字符串使用 来匹配任何字符 汇总数据 selectcount fromdept selectdeptno count fromempgroupbydeptno 将返回工资总额小于10000的部门和实际工资总额selectdeptno sum sal fromempgroupbydeptnohavingsum sal 10000 selectcount distinctdeptno fromemp 内连接 selecta empno a ename a job b dnamefromempa deptbwherea deptno b deptno 外连接 外部关联关联了两个表并使得即使第二个表没有与第一个表符合的记录也能返回结果 selectdept deptno emp ename bonus sal mfromemp dept bonuswhereemp deptno dept deptnoandbonus ename emp ename 注意 表可以外部连接到至多一个其它的表Oracle是使用的是 号作为外连接标识 交叉连接 交叉连接不带WHERE子句 它返回被连接的两个表所有数据行的笛卡尔积 返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数 在表中有数据的情况下 selectycit ycit1 fromycit ycit1 selectdeptno enamefromempwheredeptnoin selectdeptnofromdept selectdeptno enamefromempawhereexists selectlocfromdeptbwherea deptno b deptno 连接查询 比如有两张表emp和log emp 其中emp中保存的是现行编制员工的编号和姓名 log emp中保存着已经退休员工的编号和姓名 如果要查询所有员工信息 可以执行下面的操作 selectempno enamefromempunionselectempno enamefromlog emp 使用空值 空值是三值逻辑 以下列语句为例 select fromempwheredeptno 10anddeptno10等同于select fromempwheredeptnoisnotnull 插入新数据 向表中插入行的一般用法 insertintotablename column1 column2 column3 values value1 value2 value3 注意 只要值的列表和表的列名精确匹配 就可以省略列全部列表 如 如果向dept表中增加一个新的部门 可以通过下面语句 insertintodept deptno dname loc values 50 System Nanjing 插入一个查询结果 insertintotablename column1 column2 column3 subquery insertintobonusselectename job sal commfromemp 在INSERT语句中没有给出列的列表 意味着在BONUS表中的全部列都必须和SELECT查询中的对应列的排列次序相同 更新数据 UPDATEtablenameSETcolumn1 value1 Column2 values2 WHEREcondition updatedeptsetloc Shanghai wheredeptno 50 使用基于子查询的更新 UPDATEtablenameSET column1 column2 SELECTcolumn3 column4 FROMtablename2WHEREconditions1 WHEREconditions2 updatebonusset job sal comm selectjob sal commfromempwhereempno 7788 whereename SCOTT 注意 无论什么时候在更新语句的SELECT子句中使用子查询时 都必须确认子查询确实返回了一行 如果子查询没有返回行 那么所有被更新的列都将被置为空 如果子查询返回的不止一行 Oracle将返回一个错误信息 删除数据 使用DELETE命令从表中删除行 命令的基本形式如下所示 DELETEFROMtablenameWHEREcondition deletefromlog empwheredeptno 10 TRUNCATEtablename SQL中的单记录函数 Oracle的SQL语句中用到的函数分为单值函数和多值函数 单值函数又分为字符函数和数字函数 单记录字符函数 ASCII 是字符串 返回与指定的字符对应的十进制数 selectascii A A ascii a a ascii 0 zero ascii spacefromdual selectascii 赵 zhao length 赵 lengfromdual CHR NCHAR 给出整数 返回对应字符 selectchr 54740 zhao chr 65 chr65fromdual CONCAT selectconcat 010 88018159 转23 赵元杰电话fromdual INITCAP 返回字符串c1并第一个字母变为大写 selectinitcap simth uppfromdual INSTR 在一个字符串中搜索指定的字符 返回发现指定的字符的位置 C1 被搜索的字符串C2 希望搜索的字符串I 搜索的开始位置 缺省是1J 出现的位置 缺省是1 SELECTINSTR OracleTraining ra 1 2 Instring FROMDUAL INSTRB 除了返回的字节外 与INSTR相同 LENGTH 返回字符串c的长度 selectname length name addr length addr sal length to char sal fromnchar tstNAMELENGTH NAME ADDRLENGTH ADDR SALLENGTH TO CHAR SAL 赵元杰3北京市海淀区699999 998 LENGTHB 以字节返回字符串的字节数 selectname lengthb name length name fromnchar tst NAMELENGTHB NAME LENGTH NAME 赵元杰63 LOWER 返回字符串并将所有字符变为小写 selectlower AaBbCcDd AaBbCcDdfromdual UPPER 将给出字符串变为大写 selectupper AaBbCcDd AaBbCcDdfromdual RPAD string Length set LPAD string Length set RPAD在列的右边粘贴字符 LPAD在列的左边粘贴字符 selectRPAD City 35 temperaturefromweather RPAD City 35 temperature CLEVELAND 85LOSANGELES 81 即不够35个字符用 填满 LTRIM string set LeftTRIM 左截断 删去左边出现的任何set字符 RTRIM string set RightTRIM 右截断 删去右边出现的任何set字符 SELECTRTRIM MotherTheresa The The ExampleofRightTrimming FROMDUAL SUBSTR string start Count 取子字符串中函数对字串 或字段 从start字符开始 连续取count个字符并返回结果 如果没有指count则一直取到尾 selectphone substr phone 1 3 0 substr phone 4 fromtelecommunicationwheremaster 中国电信 SUBSTRB string start Count 对字串 或字段 从start字节开始 连续取count个字节并返回结果 如果没有指count则一直取到尾 REPLACE string string in string out String 希望被替换的字符串或变量 String in 被替换字符串 String out 要替换字符串 selectreplace Informaix中国公司 Informaix IBMInformix IBM数据库fromdual SELECTREPLACE Oracle Or Mir Example FROMDUAL TRIM FROM TRIM可以使你对给定的字符串进行裁剪 前面 后面或前后 如果指定LEADING Oracle从trim char中裁剪掉前面的字符 如果指定TRAILING Oracle从trim char中裁剪掉尾面的字符 如果指定两个都指定或一个都没有给出 Oracle从trim char中裁剪掉前面及后面的字符 如果不指定trim character 缺省为空格符 将下面字符串中的前面和后面的 0 字符都去掉 SELECTTRIM 0FROM0009872348900 TRIMExample FROMDUAL 单记录数字函数 ABS 返回指定值的绝对值 selectabs 100 abs 100 fromdual ACOS 给出反余弦的值 selectacos 1 acosfromdual ASIN 给出反正弦的值 selectasin 1 arcsine fromdual ATAN 返回一个数字的反正切值 selectatan 1 arctangent fromdual CEIL 返回大于或等于给出数字的最小整数 selectceil 3 14159 fromdual COS 返回一个数字余弦值 selectcos 3 1415926 fromdual COSH 返回一个数字双曲余弦值 selectcosh 20 coshfromdual EXP 返回一个数字e的n次方的值 selectexp 2 exp 1 fromdual FLOOR 对给定的数字取整数 selectfloor 123 45 floor 45 56 fromdual LN 返回一个数字的对数值 n是大于0的数字 selectln 1 ln 2 ln 3 fromdual LOG 返回一个以n1为底的n2的对数 n1不是0或1的正数 selectlog 2 1 log 2 2 fromdual MOD selectmod 10 3 mod 10 2 mod 10 4 fromdual POWER 返回n1的n2次方值 selectpower 2 10 power 3 3 fromdual ROUND value precision 按照指定的精度进行舍入 selectround 55 5 round 55 5 trunc 55 5 trunc 55 5 fromdual SIGN 取数字n的符号 大于0返回1 小于0返回 1 等于0返回0 selectsign 123 sign 100 sign 0 fromdual SIN 返回一个数字的正弦值 selectsin 1 57079 fromdual SINH 返回双曲余弦的值 selectsin 20 sinh 20 fromdual SQRT 返回数字n的根 selectsqrt 64 sqrt 10 fromdual TAN 返回数字n的正切值 selecttan 20 tan 10 fromdual TANH 返回数字n的双曲正切值 selecttanh 20 tan 20 fromdual TRUNC value precision 按照指定的截取一个数 SELECTTRUNC 124 16666 2 trunc1 trunc 124 16666 2 fromdual 单记录日期函数 ADD MONTHS 增加月份和减去月份 selectto char add months to date 199712 yyyymm 1 yyyymm add monthfromdual selectto char add months to date 199712 yyyymm 1 yyyymm add mofromdual LAST DAY date 返回日期date所在月的最后一天 selectto char sysdate yyyy mm dd to char sysdate 1 yyyy mm dd fromdual selectto char last day sysdate yyyy mm dd fromdual selectlast day sysdate fromdual MONTHS BETWEEN date2 date1 给出date2 date1的月数 可以是小数 selectmonths between 19 12月 1999 19 3月 2000 mon betwfromdual selectmonths between to date 2000 05 20 yyyy mm dd to date 2005 05 20 yyyy mm dd mon betfromdual NEXT DAY date day 给出日期date和星期x之后计算下一星期x的日期 这里的day为星期 如 MONDAY Tuesday等 但在中文环境下 要写成 星期x 这样的格式 如今天是5月18日星期五 计算下一个星期五是几号 selectnext day 18 5月 2001 星期五 nxt dayfromdual SYSDATE用来得到系统的当前日期 selectto char sysdate dd mon yyyyday fromdual 单记录转换函数 TO CHAR date format 根据format重新格式日期date的格式 selectto char sysdate yyyy mm ddhh24 mi ss fromdual selectto char sysdate yy mon dd fromdual TO DATE string format 将字符串转换为ORACLE的日期 Insertintodemo demo key date col Values 1 to date 04 Oct 1999 DD Mon yyyy Selectto date 10 8月 06 yy mon dd Fromdual TO NUMBER 将给出的字符转换为数字 SELECTTO NUMBER 1947 FISCAL YEAR FROMDUAL GREATEST 返回一组表达式中的最大值 即比较字符的编码大小 selectgreatest AA AB AC fromdual selectgreatest 啊 安 天 fromdual LEAST 返回一组表达式中的最小值 即比较字符的编码大小 selectleast 啊 安 天 fromdual UID函数返回标识当前用户的唯一整数 showuserselectusername user idfromdba userswhereuser id UIDUSER函数返回当前用户的名字 selectuserfromdual USERENV 返回当前用户环境的信息 opt选项可以是 ENTRYID返回当前用户会话的入口IDSESSIONID返回当前用户会话的IDTERMINAL返回当前系统会话的操作系统标识ISDBA如果当前用户有DBA权限 则返回TRUELABLE返回当前用户会话的标号LANGUAGE返回当前用户的语言和区域CLIENT INFO为当前用户会话返回client info域的值 这个值由dbms application info set client info过程来设置 LANG以ISO的三个字符表示当前用户会话所使用的语言 VSIZE返回表达式的字节大小 ISDBA函数查看当前用户是否是DBA 当SYSDBA角色有效是才返回TRUE selectuserenv ISDBA fromdual SESSIONID函数返回审计会话标识 selectuserenv SESSIONID aud idfromdual ENTRYID函数返回审计会话入口标识 当initsid ora文件中的audit trail TRUE时可以用 selectuserenv ENTRYID fromdual INSTANCE函数返回当前INSTANCE的标识 selectuserenv INSTANCE fromdual LANGUAGE函数返回当前环境的语言 selectuserenv LANGUAGE fromdual LANG函数返回当前环境的语言的缩写 selectuserenv LANG fromdualTERMINAL函数返回用户的终端或机器的标识 selectuserenv TERMINAL fromdual 用BREAK命令来对结果进行排列 一般可以用BREAKONcolumnSKIPxx来对查询结果进行排列 BREAKON命令的参数如下 clearbreaks清除所有的break定义breakoncolumn在该列上中断breakonrow在每一行上中断breakonPage在每一页上中断breakonreport在每一报告上中断skipn跳过n行skippage跳过未用完的页 SQL breakondeptnoSQL setpagesize100SQL selectdeptno ename salfromemporderbydeptno SQL breakondeptnoskip2SQL selectdeptno ename salfromemporderbydeptno SQL breakondeptnoskippageSQL selectdeptno ename salfromemporderbydeptno 日期的格式化输出 Oracle系统提供了一个NLS DATE FORMAT的环境变量来设置日期的显示格式 用它可以完成按照不同格式要求的显示 1 系统日期sysdate的显示用sysdate可以显示ORACLERDBMS所在机器的日期及时间 altersessionsetnls date format 公元 yyyy 年 mm 月 dd 日 selectsysdatefromdual SYSDATE 公元2001年05月30日 小结 这章首先讲了SQL操作的一些查询的基本技能 包括关联查询 查询汇总 子查询的相关方法 SQL的SELECT是查询的机制 SELECT命令有5个组成部分 SELECT FROM WHERE GROUPBY和ORDERBY子句 本章还介绍了Oracle数据的操作语句增加 INSERT 修改 UPDATE 和删除 DELETE 方法 在插入数据时 可以一次插入一行 也可以用子查询来一次插入几行 UPDATE命令有两种基本变化 一种用文字或表达式来对列设置新值 另一种是使用一个子查询来执行这个功能 在删除数据时 使用WHERE子句告诉Oracle哪些数据是需要删除的 如果省略WHERE子句 将删除表中的所有数据