[新版]select语句进修

[新版]select语句进修 select语句学习 oracle select语句学习 来了新任务,关于数据查询分析系统的,所以重温一下相关东西,顺便记一下。 刚刚看了下racle单行函数，主要是字符，数字，日期，转换，通用五个方面。 1>字符处理 upper(str)---将字符串str全部转换成大写 lower(str)---将字符串str全部转换成小写 initcap(str)---将字符串中每个单词的首字母大写 concat(str1,str2)---将字符串str1与str2连接起来(也可以通过'||'号直接相连) substr(str,a,b)---取字符串str中的指定字符，从位置a开始取长度为b的字符串，假如a为正则从左边开始，否则从右边开始 instr(str,'z')---取得str字符串中从左边开始每一次出现z字符的下标位置(下标从1开始) lpad(str,12,'*')---左填充，即将字符串str长度填充到12，假如其不足12位则在左边以*号填充 rpad(str,12,'*')---右填充，同上 length(str)---计算字符串str的长度 2>数字函数 round(45.926,2)---将前一数保留指定的小数位，并四舍五入(45.93)，假如指定位是负数则意为在小数点左边保留指定位,如round(45.923,-1)=50,rount(45.923,0)=46,round(45.93,-2)=0,ro und(55.93,-2)=100 trunc(45.926,2)---同上，得不四舍五入(45.92) mod(1600,300)---求余(100) 3>日期型函数 oracle中默认的格式是:DD-MON-RR。 oracle中有个到当前系统时间--sysdate，如: select sysdate from dual 可对日期进行自述运算: select (sysdate-mybirthday)/7 from person months_between('01-sep-95','11-jan-94')---取得二个日期之间的间隔月数(19.6774194) add_months('11-jan-94',6)---给指定日期加上指定的月份后得到一个新的日期(11-jul-94) next_day('01-sep-85','friday')---取得当前日期中下个周五的日期(01-jul-95) last_day('01-feb-95')---取得当前日期中月份的最后一天(28-feb-95) round进行四舍五入，trunc则否,以下是我的操作结果: sysdate为: SYSDATE ---------- 28-7月 -06 select round(sysdate,'month') RM, round(sysdate,'year') RY, trunc(sysdate,'month') TM, trunc(sysdate,'year') TY from dual; RM RY TM TY ---------- ---------- ---------- ---------- 01-8月 -06 01-1月 -07 01-7月 -06 01-1月 -06 4>转换函数 隐式转换:系统自动转换,如: varchar2 or char to number varchar2 or char to date number to varchar2 date to varchar2 显式转换:人为以函数加以转换 日期，字符，数据三者之间可以相互转换:日期<-->字符<-->数据 日期格式:YYYY 日期-->字符 select to_char(sysdate,'yyyy-mm-dd') ch from dual CH ---------- 2006-07-28 数字-->字符 to_char(number,'format_model'),oramat_model有如下: 9---用对应数字表示 0---强制用,表示 $---加一$符号 L---前加本地货币单位表示 .---十进制点 ,---千进制点 select to_char(0917,'l9999.99') local from dual LOCAL ------------------ RMB917.00 字符-->日期 tselect to_date('19830917','yyyy-mm-dd') bir from dual BIR ---------- 17-9月 -83 字符-->数字 select to_number('19821217','999999999') mybr from dual MYBR ---------- 19821217 注:所有函数均可以嵌套使用 5>通用函数 nvl(expr1,expr2)---expr1为空则显示expr2,否则显示expr1 nvl2(expr1,expr2,expr3)---expr1为空则显示expr2,否则显示expr3 nullif(expr1,expr2)---二个相等则显示空符,否则显示expr1 coalesce(expr1,expr2,...,exprn)---从expr1开始依次找到不为空的expr，找到就显示，直到最后，否则显 示exprn case表达式，如下图: 6>多表查询 对普通的多表查询，也就是不加where条件的时候实际上查询结果是各表的笛卡尔集 外连接:oracle实现外连接时在=号的二边加+号就OK，当+在左边时称为右连接，反之为左连接,它常常用来当要求未受限制对象的表数据也要求显示时，如下: select * from student ID NAME ADDRESS -------------------- -------------- 1 zhangshan zhejiang 3 lishi hangzhou 7 lily guangzhou select * from person ID NAME ADDRESS - -------------------- ----------- 1 zhangshan zhejiang 3 lishi hangzhou 7 lily guangzhou select p.id,p.sex,s.id,s.name,s.address from person p ,student s where p.id(+) = s.id ID SEX ID NAME ADDRESS -- ---- ---------- -------------------- ------------ 1 boy 1 zhangshan zhejiang 3 girl 3 lishi hangzhou 7 lily guangzhou select p.id,p.sex,s.id,s.name,s.address from person p ,student s where p.id = s.id(+) ID SEX ID NAME ADDRESS -- ---- ---------- -------------------- -------------- 1 boy 1 zhangshan zhejiang 2 girl 3 girl 3 lishi hangzhou 4 boy 5 girl self-join，就是同一张表连接，用自连接的时候要注意排除重复的记录(自身，循环重复等)，比如说找出student表中所有住在同一个地方的人。 select * from student ID NAME ADDRESS --- -------------------- --------- 1 zhangshan zhejiang 3 lishi hangzhou 7 lily guangzhou 2 name2 guangzhou 4 name4 guangzhou 5 name5 hangzhou 6 name6 shanghai 8 name8 shanghai 没有排除重复记录时的结果: select t1.name,t2.name from student t1, student t2 where t1.address = t2.address NAME NAME -------------------- -------------- lily lily name2 lily name4 lily lily name2 name2 name2 name4 name2 lily name4 name2 name4 name4 name4 lishi lishi name5 lishi NAME NAME -------------------- -------------- lishi name5 name5 name5 name6 name6 name8 name6 name6 name8 name8 name8 zhangshan zhangshan 已选择18行 排除重复与循环记录之后: select t1.name,t2.name,t1.address from student t1, student t2 where t1.address = t2.address and t1.id > t2.id NAME NAME ADDRESS -------------------- -------------------- ------------------- lily name2 guangzhou name4 name2 guangzhou lily name4 guangzhou name5 lishi hangzhou name8 name6 shanghai cross join,无条件连接，实际上跟不带where时一样得到的是笛卡 尔集 natural join(也可以直接记作join)，根据表中同名栏位来连接记 录,若表中可能出现多个同名栏位，则用using(col_name)来指定所 栏位。colname不能指定表的别名～限制条件用on来指定 select id,p.sex,id,s.name from person p join student s using(id); ID SEX ID NAME ---- ---- ---------- -------------------- 1 boy 1 zhangshan 2 girl 2 name2 3 girl 3 lishi 4 boy 4 name4 5 girl 5 name5 在sql标准语法中,左(右)连接用left(right) out join,限制条件 用on就可以了。 full out join完全外连接,顾名思义就是左外连接与右外连接都有 select id,p.sex,id,s.name from person p full join student s using(id); ID SEX ID NAME ---- ---- ---------- ------------------ 1 boy 1 zhangshan 3 girl 3 lishi 2 girl 2 name2 4 boy 4 name4 5 girl 5 name5 8 8 name8 6 6 name6 7 7 lily