oralce+基础知识

一、选择行1.简单的SELECT语句SELECT字段名1[AS]'字段名1解释'FROMtable;2.处理NULLNVL函数可把NULL转换成其它类型的符号编程技巧:NVL函数在多条件模糊查询的时候比较有用NVL函数可返回多种数据类型:返回日期NVL(start_date,'2002-02-01')返回字符串NVL(title,'notitle')返回数字NVL(salary,1000)3.使用SQL*PLUS（1）SQLdesctable;显示表结构SQLselect*fromtab;查看用户下所有的表SQLsetpauseon;可以使大量结果集在用户按“Enter”(回车)后翻页SQLsetpagesize100;设定SQL语句返回结果集一页的行数100,默认值是14SQLsetlinesize100;设定SQL语句返回结果集一行的宽度100,默认值是804.SQL*PLUS里规定字段的显示格式规定数字的显示格式SQLcolumn字段名format99999999999;SQLcolumn字段名format999,999,999,999;SQLcolumn字段名formata数字[word_wrapped];规定long字符的显示宽度SQLsetlong200;规定字段名的显示内容SQLcolumn字段名heading'字段名显示内容';SQLsetheadingoff;查询时不显示字段名称规定字段的对齐方向SQLcolumn字段名justify[left|right|center];清除字段的格式SQLcolumn字段名clear;5.SQL*PLUS里规定字段的显示格式例子SQLcolumnlast_nameheading'Employee|Name'formata15;SQLcolumnsalaryjustifyrightformat$99,999.99;SQLcolumnstart_dateformata10null'NotHired';说明：如果start_date为null,显示字符串'NotHired'6.判断题(T/F)(1).SQLcommandarealwaysheldinsqlbuffer.[T](2).SQL*PLUScommandassitwithquerydata.[T]5J2EE@zxwSQL*PLUS命令只控制SELECT结果集的显示格式及控制文件.只有SQL命令能访问数据库.二、限制选择行1.按指定的规则排序SELECTexprFROMtable[ORDERBY{column,expr}[ASC|DESC]];默认的排序是ASC升序(由小到大)还可以ORDERBY字段名的位置[1]|[2]ASC|DESC;2.用WHERE限制选择行(1)比较操作符===!=^=与NULL比较不能用上面的比较操作符ANYSOMEALLSQL操作符BETWEEN…AND…INLIKEISNULLNOTBETWEEN…AND…NOTINNOTLIKEISNOTNULL逻辑操作符ANDORNOT3.用WHERE限制选择行(2)比较顺序(可以用括号改变它们的顺序)(1).===inlikeisnullbetween(2).and(3).Or4.LIKE操作%零到任意多个字符_一个字符例如:字段名like'M%'字段名like'%m%'字段名like'job_'如果要找含下划线的字符,要加反斜线例如:字段名like'%X/_Y%'escape'/'5.日期字段的比较举例:日期字段betweento_date('2001-12-12','YYYY-MM-DD')andto_date('2002-02-01','YYYY-MM-DD')日期字段to_date('2001-12-12','YYYY-MM-DD')and日期字段=to_date('2002-02-01','YYYY-MM-DD');6.不能用到索引的比较操作符ISNULLISNOTNULLLIKE'%m%'三、单行函数1.数字函数ABS取绝对值POWER乘方LN10为底数取0SQRT平方根EXPe的n次乘方LOG(m,n)m为底数n取0数学运算函数:ACOSATANATAN2COSCOSHSIGNSINSINHTANTANHCEIL大于或等于取整数FLOOR小于或等于取整数MOD取余数ROUND(n,m)按m的位数取四舍五入值如果round(日期):中午12以后将是明天的日期.round(sysdate,'Y')是年的第一天TRUNC(n,m)按m的位数取小数点后的数值如果trunc(日期),确省的是去掉时间6J2EE@zxw2.字符函数CHR按数据库的字符集由数字返回字符CONCAT(c1,c2)把两个字符c1,c2组合成一个字符,和||相同REPLACE(c,s,r)把字符c里出现s的字符替换成r,返回新字符SUBSTR(c,m,n)m大于0,字符c从前面m处开始取n位字符,m等于0和1一样,m小与0,字符c从后面m处开始取n位字符TRANSLATE(c,f1,t1)字符c按f1到t1的规则转换成新的字符串INITCAP字符首字母大写,其它字符小写LOWER字符全部小写UPPER字符全部大写LTRIM(c1,c2)去掉字符c1左边出现的字符c2RTRIM(c1,c2)TRIM(c1,c2)去掉字符c1左右两边的字符c2LPAD(c1,n,c2)字符c1按制定的位数n显示不足的位数用c2字符串替换左边的空位RPAD(c1,n,c2)3.日期函数ADD_MONTHS(d,n)日期值加n月LAST_DAY返回当月的最后一天的日期MONTHS_BETWEEN(d1,d2)两个日期值间的月份,d1d2返回负数NEXT_DAY返回日期值下一天的日期SYSDATE当前的系统时间DUAL是SYS用户下一个空表，它只有一个字段dummy4.转换函数(1)TO_CHAR(date,'日期显示格式')TO_CHAR(number)用于显示或报表的格式对齐TO_DATE(char,'日期显示格式')TO_LOB把long字段转换成lob字段TO_NUMBER(char)用于计算或者比较大小4.转换函数(2)to_date里日期显示格式YYYY年YEARYYYYYYQ季度MM月MONTHMONW星期(weekofmonth)WW,IW(weekofyear)(说明：周计是按ISO标准,从1月1日的星期数到后面七天为一周,不一定是从周一到周日)DD日DAYDYHH24小时HH12HHMI分钟SS秒如果想固定日期的显示格式可以在数据库的参数文件initorasid.ora里新写一行参数NLS_DATE_FORMAT=yyyy-mm-ddhh24:mi:ss可以在UNIX环境变量或者NT的注册表里的设置NLS_DATE_FORMAT=yyyy-mm-dd7J2EE@zx:ss4.转换函数(3)如果想固定日期的显示格式可以用altersession命令改变SQLaltersessionsetnls_date_format='yyyy-mm-ddhh24:mi:ss';它的作用顺序如下:initializationparameterEnvironmentvariableALTERSESSIONcommand4.转换函数(4)to_char(number)里数字显示格式9数字位0数字前面补0to_char(-1200,'00000.00').小数点的位置,标记位置的逗号用在数字显示格式的左边L根据数据库字符集加货币符号to_char(-1200,'L9999.99')B把数字0显示为空格,用在数字显示格式的右边MI右边的负数标记to_char(-1200,'9999.99MI')PR括起来的负数to_char(-1200,'9999.99PR')EEEE用指数方式表示to_char(-1200,'9999.99EEEE')5.输入字符,返回数字的函数instr(c1,c2)字符c2出现在c1的位置,不出现,返回0,常用于模糊查询length按数据库的字符集,算出字符c的长度,跟数据库的字符集有关,一个汉字长度为16.有逻辑比较的函数NVL(EXPR1,EXPR2)函数解释:IFEXPR1=NULLRETURNEXPR2ELSERETURNEXPR1DECODE(AA0V10R10V20R2....)函数解释:IFAA=V1THENRETURNR1IFAA=V2THENRETURNR2..…ELSERETURNNULL举例:decode(id,1,'deptsale',2,'depttech')四、从多个表里选取数据记录1.数据表间的连接简单的连接语法:SELECT字段名1,字段名2,……FROM表名1,[表名2,……]WHERE表名1.字段名=表名2.字段名[AND……];SELECT字段名1,字段名2,……FROM表名1,[表名2,……]WHERE表名1.字段名=表名2.字段名(+)[AND……];有(+)号的字段位置自动补空值连接的分类:等于的连接=不等于的连接!=BETWEEN…AND…IN注意IN和OR不能一起用8J2EE@zxw外连接有一个字段名(+),没有满足的条件补空值自连接同一个表自己跟自己连接例如找重复记录2.数据表间的连接例子删除table_name表里字段名email重复的记录:SQLdeletefromtable_namet1wheret1.rowid(selectmin(rowid)fromtable_namet2wheret1.email=t2.emailgroupbyemailhavingcount(email)1);找到手机用户的服务区域:SQLselecta.handphoneno,nvl(c.name,'null'),a.totalscorefromtopscorea,chargeoperatorcc,chargeoperatorinfocwheresubstr(a.handphoneno,1,7)=cc.hpnohead(+)andcc.chargetype=c.chargetype(+)orderbya.totalscoredesc;3.数据表间的连接技巧连接N个表,需要N-1个连接操作被连接的表最好建一个单字符的别名,字段名前加上这个单字符的别名BETWEEN..AND..比用=AND=要好连接操作的字段名上最好要有索引连接操作的字段最好用整数数字类型有外连接时,不能用OR或IN的比较操作4.如何分析和执行SQL语句写多表连接SQL语句时要知道它的分析执行计划的情况.Sys用户下运行@/ORACLE_HOME/sqlplus/admin/plustrce.sql产生plustrace角色Sys用户下把此角色赋予一般用户SQLgrantplustraceto&username;一般用户下运行@/ORACLE_HOME/rdbms/admin/utlxplan.sql产生plan_tableSQLsettimeon;说明：打开时间显示SQLsetautotraceon;说明：打开自动分析统计，并显示SQL语句的运行结果SQLsetautotracetraceonly;说明：打开自动分析统计，不显示SQL语句的运行结果接下来你就运行测试SQL语句，看到其分析统计结果了。一般来讲，我们的SQL语句应该避免大表的全表扫描。SQLsetautotraceoff;说明：关闭自动分析统计五、集合函数经常和groupby一起使用1.集合函数列表AVG(DISTINCT|ALL|N)取平均值COUNT(DISTINCT|ALL|N|expr|*)统计数量MAX(DISTINCT|ALL|N)取最大值MIN(DISTINCT|ALL|N)取最小值SUM(DISTINCT|ALL|N)取合计值9