当前位置: 代码迷 >> SQL >> SQL查询跟SQL函数
  详细解决方案

SQL查询跟SQL函数

热度:103   发布时间:2016-05-05 12:59:29.0
SQL查询和SQL函数
 二、SQL查询和SQL函数 SQL支持如下类别的命令:  数据定义语言: create(创建)、alter(更改)、drop(删除)和truncate(截断)命令。  数据操纵语言: insert(插入)、select(选择)、delete(删除)和update(更新)命令。  事务控制语言: commit(提交)、savepoint(保存点)和rollback(回滚)命令。  数据控制语言: grant(授予)和revoke(回收)命令。 数据类型:  char:           长度在1到2000个字节,声明多少字节在内存中就占用多少字节,输入的值小于指定的长度时用空格填充。  varchar2:       长度在1到4000个字节,输入的值是多少字节,就占用多少字节。  long:           长度在2GB,设置为此类型的列时,要注意:一个表中只有一列可以为long类型,long类型列不能定义为唯一约束或主键约束,          不能建立索引,过程或存储过程不能接受long类型的参数。  number(p,s):    其中p为精度,表示数字的总位数,在1至38之间。s为范围,表示小数点右边数字的位数,在-84至127之间。  date:           日期类型,sysdate为当前系统时间。格式为08-9月 -07。  timestamp:    用于存储日期的年、月、日以及时间的时、分和秒。其中秒精确到小数点后6位,                    systimestamp返回当前日期、时间。格式为08-9月 -07 04.08.30.000000 下午。  raw:            此数据类型用于存储基于字节的数据,如二进制数据或字节串,该类型最多能存储2000个字节,可以建立索引。  long raw:   此数据类型用于可变长度的二进制数据,最多能存储2GB。long数据类型的所有限制对long raw数据类型也同样有效。  lob又称为"大对象"数据类型,最多能存储4GB的非结构化信息。包括:  clob:           clob代表Character LOB(字符LOB),它能存储大量字符数据。如XML文档。    blob:      blob代表Binary LOB(二进制LOB),它能存储较大的二进制对象,如图形、视频剪辑和声音剪辑。    bfile:     bfile代表Binary File(二进制文件),它能够将二进制文件存储在数据库外部的操作系统文件中。 伪列:  rowid:  select rowid,ename, from scott.emp where empno='7900';    rownum:   select * from scott.emp where rownum<11;             //限制查询返回的行数  语法知识:  desc 表名;             //查看表结构  alter table 表名 modify (列名 varchar2(25));    //修改列  alter table 表名 add (列名 varchar2(12),列名 number(12));  //添加列  alter table 表名 drop column 列名;  //删除列  truncate table 表名;  //中删除记录而不删除结构,不使用事务处理,因此无法回滚  drop table 表名;  //删除表及其全部数据  create table 新表名 as select * from 表名 where 1=2;  //用现有的表创建一个新表  select deptno*2 "New No",dname,loc from dept;  //指定一个含有特殊字符(如空格)的列标题    commit;      //提交事务  savepoint 标记名;   //标记事务点  rollback;    //回滚整个事务处理  rollback to [savepoint] 标记名;   //回滚到事务中某个特定的保存点  集合操作符:    union(联合):           此操作符返回两个查询选定的所有不重复的行。    语法:                 select orderno from order_master UNION select orderno from order_detail;    union all(联合所有):   此操作符合并两个查询选定的所有行,包括重复的行。    语法:                 select orderno,ename from order_master UNION ALL select orderno,proname from order_detail order by 2;    注意:在两个select语句中指定的列名不必相同,但数据类型必须匹配。也可以对联合查询的结果进行排序,使用Order By子句时,它必须放在最后          一个select语句之后,而且必须指定列索引来排序,而不是指定列名,列索引是从1开始的整数。上述语法便是以proname的索引排序    intersect(交集):       此操作符只返回两个查询都有的行。    语法:                 select orderno from order_master INTERSECT select orderno from order_detail;    minus(减集):           此操作符中返回由第一个查询选定但是第二个查询中没有选定的行,也就是在第一个查询结果中排除第二个查询结果中出现的行。    语法:                 select orderno from order_master MINUS select orderno from order_detail;    查询尚未交付的订单  连接(||)操作符:    语法:   select ('供应商'||venname||'的地址是'||venadd1||' '||venadd2||' '||venadd3) 地址 from vendor_master where vencode='V002';             将多个字符串合并为一个字符串 SQL函数:  1、日期函数:    add_months:       此函数返回给指定的日期加上指定的月数后的日期值。语法为add_months(d,n),其中d是日期,n表示月数。    示例:select add_months(sysdate,2) from dual;    将当前时间加上2个月后的日期值。    months_between:   此函数返回两个日期之间的月数。语法为months_between(d1,d2),其中d1和d2是日期,如果d1大于d2,则结果为正数;否则为负数。    last_day:         此函数返回指定日期当月的最后一天的日期值,语法为last_day(d),其中d表示日期。    示例:select last_day(sysdate) from dual;        返回当前日期的月的最后一天,如果是9月就返回30-09月-07    round:            此函数返回日期值,将日期四舍五入为格式模型指定的单位。语法为round(d,[fmt])。其中d是日期,fmt是格式模型。fmt是一个可                      选项,日期默认舍入为最靠近的那一天。如果指定格式为年"Year",则舍入到年的开始,即1月1日;如果格式为月"Month",则舍入到                      月的第一日;如果格式为周"Day",则舍入到最靠近的星期日。    示例:select round(sysdate,'month') from dual;   返回最接近的一个月。    next_day:         此函数返回指定的下一个星期几的日期。语法为next_day(d,day)。其中d表示日期,而day指周内任何一天。    示例:select next_day(sysdate,'星期日') from dual; 返回下一个星期日的日期,也可以用1表示,以此类推,星期一以2表示。    trunc:            此函数将指定日期截断为由格式模型指定的单位日期,与Round函数不同的是它只舍不入,语法为trunc(d,[fmt]),与round格式相同。    示例:select trunc(sysdate,'year') from dual;    返回当前年的第一天,也就是1月1日。    示例:select trunc(sysdate,'day') from dual;     返回紧靠前面的星期日。如果为"2005年1月27日"就会返回"2005年1月23日"。    extract:          此函数提取日期时间类型中的特定部分。语法为extract(fmt from d),其中d是日期时间表达式,fmt是要提取的部分的格式。格式                      的取值可以是year,month,day,hour,minute,second,注意此处的格式不使用单引号。    示例:select extract(year from sysdate) from dual;  返回当前的年份。    2、字符函数:    initcap(char):    首字母大写,示例:select initcap('hello') from dual; 输出结果:Hello。    lower(char):      转换为小写,示例:select lower('FUN') from dual; 输出结果:fun。    upper(char):      转换为大写,示例:select upper('sun') from dual; 输出结果:SUN。    ltrim(char,set):  左剪裁,示例:select ltrim('xyzadams','xyz') from dual; 输出结果:adams。    rtrim(char,set):  右剪裁,示例:select rtrim('xyzadams','ams') from dual; 输出结果:xyzad。    translate(char,from,to):  按字符翻译,示例:select translate('jack','abcd','1234') from dual; 输出结果:j13k。    replace(char,search_str,replace_str):  字符串替换,示例:select replace('jack and jue','j','bl') from dual; 输出结果:black and blue。    instr(char,substr[,pos1,pos2]):  查找子字串位置。    示例:select instr('vorldwide','d') from dual; 输出结果:5。pos1为可选,表示从第几个位置查找。pos2为可选,表示从第几次出现的位置找。    substr(char,pos,len):  取子字符串,示例:select substr('abcdefg',3,2) from dual; 输出结果:cd。    concat(char1,char2):  连接字符串,示例:select concat('Hello','world') from dual; 输出结果:Helloworld。    chr:     此函数根据Ascii码返回对应的字符,示例:select chr(45788),chr(53671),chr(50167),chr(65) from dual; 输出结果:曹学明 A。    ascii:   此函数返回GBK编码值,示例:select ascii('曹') cao ,ascii('学') xue,ascii('明') Ming from dual; 输出结果:45788 53671 50167。    lpad和rpad:  示例:select lpad('function',15,'=') from dual;  输出结果:=======function。而rpad则相反,字符串填充在右边。    trim:    此函数从字符串的开头或结尾(或开头和结尾)剪裁特定的字符,默认剪裁空格。如果加上leading选项时与ltrim函数相似。指定trailing时和             rtrim函数相似。             示例: select trim(9 from 999992598899) from dual; 输出结果:25988。             示例: select trim(leading 9 from 999992598899) from dual; 输出结果:2598899。             示例: select trim(trailing 9 from 999992598899) from dual; 输出结果:9999925988。    length:  此函数返回字符串的长度,示例:select length('frances') from dual; 输出结果:7。    decode:  示例:select deptno,dname,decode(loc,'NEW YORK','纽约','BOSTON','波士顿') from scott.dept;             此示例将替换显示loc列的结果,结果为"NEW YORK"的替换为"纽约","BOSTON"的替换为"波士顿"。    GREATEST/least:  返回一组表达式中的最大值/最小值,即比较字符的编码大小.    示例:select greatest('AA','AB','AC') from dual;  输出结果:AC。          select least('AA','AB','AC') from dual;  输出结果:AA。          select greatest('啊','安','天') from dual;  输出结果:天。          select least('啊','安','天') from dual;  输出结果:啊。    3、数字函数:    abs(n):     取绝对值,示例:select abs(-15) from dual;  输出结果:15。    ceil(n):    向上取整,示例:select ceil(44.778) from dual;  输出结果:45。    sign(n):    取符号,示例:select sign(-2) from dual;  输出结果:-1。    floor(n):   向下取整,示例:select floor(200.88) from dual;  输出结果:200。    power(m,n): m的n次幂,示例:select power(5,3) from dual;  输出结果:125。    mod(m,n):   取余数,示例:select mod(10,3) from dual;  输出结果:1。    round(m,n): 四舍五入,示例:select round(100.256,2) from dual;  输出结果:100.26。    trunc(m,n): 截断,示例:select trunc(100.256,2) from dual;  输出结果:100.25。    sqrt(n):    平方根,示例:select sqrt(4) from dual;  输出结果:2。    4、转换函数:    to_char(d|n[,fmt]):   其中d是日期,n是数字,fmt指定日期或数字的格式。    示例:select to_char(sysdate,'yyyy"年"fmmm"月"fmdd"日" hh24:mi:ss') from dual;  输出结果:2007年9月09日 20:44:27。          select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;          select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;          select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24"时"mi"分"ss"秒"') from dual;    示例:select to_char(sal,'$99999') from emp;  输出结果:$1600。    to_date(char[,fmt]):  此函数将char或varchar2数据类型转换为日期数据类型。    示例:select to_date('2005-12-06','yyyy-mm-dd') from dual;  输出结果:06-12月-05。          select to_date('2008/08/10 20:08:08','yyyy/mm/dd hh24:mi:ss') from dual;          select to_date('2008-08-10 20:08:08','yyyy/mm/dd hh24:mi:ss') from dual;          select to_date('2008年08月10日 20时08分08秒','yyyy"年"mm"月"dd"日" hh24"时"mi"分"ss"秒"') from dual;    to_number(char):  此函数将包含数字的字符串转换为number数据类型,通常不用这么做,因为Oracle可以对数字字符串进行隐式转换。    示例:select sqrt(to_number('100')) from dual;  输出结果:10。    5、其它函数:    nvl(expression1,expression2):   如果expression1为NULL,则nvl返回expression2。    nvl2(expression1,expression2,expression3):   如果expression1不是NULL,则nvl2返回expression2,如果expression1是NULL,则返回expression3。    nullif(expr1,expr2):  此函数比较两个表达式,如果它们相等,则返回空值,否则返回expr1。    nullif函数等价于以下的case表达式:    case when expr1=expr2 then null else expr1 end    6、分组函数:    avg:   此函数返回指定列值的平均值,示例:select avg(sal) from emp;  输出结果:2073.21429。    min:   此函数返回指定列值的最小值,示例:select min(sal) from emp;  输出结果:800。    max:   此函数返回指定列值的最大值,示例:select max(sal) from emp;  输出结果:5000。    sum:   此函数返回指定列值的总和,示例:select sum(sal) from emp;  输出结果:29025。    count: 此函数是为了计算行数,它可以接受3种不同的参数    示例:select count(*) from emp;  输出结果:14。    示例:select count(列名) from 表名;     示例:select count(distinct 列名) from 表名;    group by:  此子句用于将信息表划分为组,按组进行聚合运算。select后面跟的列名只能是分组函数、group by子句中出现的列或表达式。    示例:select deptno,max(sal) from emp group by(deptno); 查出每个部门的最高工资。    示例:select deptno,count(*),sum(sal) from scott.emp group by deptno;  //group by主要用来对一组数进行统计    having:    此子句用来指定group by子句的检索条件。    示例:select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5;  //having对分组统计再加限制条件    7、分析函数:只能出现在select列表或order by子句中。    row_number:  下面对所有员工的工资进行排名,即使工资相同,其排名也不能相同。    示例:select ename,job,deptno,sal,row_number() over(order by sal desc) as 排名 from scott.emp;    下面对所有员工的工资按部门进行排名,即使工资相同,排名也不同。    示例:select ename,job,deptno,sal,row_number() over(partition by deptno order by sal desc) as 排名 from scott.emp;    rank:  此函数计算一个值在一组值中的排位,排位是以1开头的连续整数,如果两行的序数为1,则没有序数2,下行的序数为3。    下面根据员工的工资和佣金对员工在每个部门中进行排位。相同的工资排位相同,并且排位不连续。    示例:select ename,sal,comm,deptno,rank() over(partition by deptno order by sal desc,comm) 排名 from scott.emp;    dense_rank:  此函数计算一个行在一组有序行中的排位,排位是以1开头的连续整数,具有相同值的排位相同,并且排位是连续的。    下面首先选择所有在accounting或research部门中工作的员工的部门名称、员工姓名和工资,    然后分别计算每个员工的工资在部门中的排位,相等的工次排位相同    示例:select d.dname,e.ename,e.sal,dense_rank() over(partition by e.deptno order by e.sal desc) 排名          from emp e,dept d where e.deptno=d.deptno;
  相关解决方案