oracle SQL查询和SQL函数.doc

SQL查询和SQL函数一、Oracle中的伪列1、ROWID例 1：select rowid rid,empno,ename from emp;select rowid,ename from emp where empno=7369;2、ROWNUM例 2：select empno,ename,rownum rn from emp;select * from emp where rownum11;二、CREATE TABLE 命令 (创建表)例 3：创建表create table vendor_master(vencode varchar2(5),venname varchar2(20),venadd1 varchar2(20),venadd2 varchar2(20),venadd3 varchar2(20);三、ALTER TABLE 命令 (更改表)语法1：用于更改现有列：alter table modify (column definition .);语法2：用于向表中添加新列：alter table add (column definition .);语法3：用于删除表中现有列：alter table drop COLUMN column;例 4：更改venname列的字符长度 alter table vendor_master modify(venname varchar2(25);例 5：向现有的表添加新列 alter table vendor_master add(tel_no varchar2(12),tngst_no number(12);例 6：从表中删除列 alter table vendor_master drop column tngst_no;四、TRUNCATE TABLE 命令 (只删除表中的记录而不删除表结构) 语法1：truncate table ; 语法2：查看表的结构：desc ;例 7：显示vendor_master 表的结构 desc vendor_master;五、DROP TABLE 命令 (删除表及其全部数据)语法：drop table ;例 8：删除表 drop table vendor_master;六、SELECT 命令 (查询表)例 9：从vendor_master 表中选择所有的列 select * from vendor_master;例 10：查询表中的特定列 select vencode,venname,tel_no from vendor_master;例 11：选择无重复的行 select distinct vencode from vendor_master;例 12：带条件和排序的SELECT命令 select * from vendor_master where vencode=V002 order by venname;例 13：使用 order by 排序 select * from emp order by sal asc;例 14：利用现有的表创建新表 create table newTable as select * from vendor_master;例 15：用选定的列创建新表 create table newBiao as select vencode,venname,tel_no from vendor_master;例 16：仅复制表的结构，而不复制记录create table newJieGou as select * from emp where 1=2;例 17：使用列别名select empno as 编号,ename as 姓名,sal*2 as 销售额 from emp;例 18：在 Oracle 中指定含有特殊字符(如空格)的列标题select empno,ename,sal*2 销售额 from emp;七、INSERT命令 (插入数据)例 19：向vendor_master表中添加数据insert into vendor_master values(V001,John Smith,11,22,33,123456) 例 20：向表中插入某些列的值insert into vendor_master(vencode,venname,tel_no) values(V101,zhaoqi 例 21：跳过一个或多个字段的方式是为该列的值输入Nullinsert into vendor_master values(V201,wangwu,null,null,null 例 22：创建一个具有日期类型列的表create table order_master(orderno varchar2(5),odate date,vencode varchar2(5),ostatus char(1),del_date date);例 23：插入日期值 (格式为：”DD-MON-RR” 或 “06-12月-05”)insert into order_master values(o001,12-5月-05,v002,c,25-5月-05)例 24：插入来自其他表的记录 (两表的结构相同)insert into newJieGou select * from emp例 25：将选定的列复制到新表insert into newCopy(empno,ename) select empno,ename from emp八、UPDATE命令 (修改数据)例 26：根据编号修改姓名update vendor_master set venname=张三 where vencode=V002;例 27：不带 where 子句的更新语句update vendor_master set venadd1=1001;九、 DELETE命令 (删除数据)例 28： 从表中删除一行delete from vendor_master where vencode=V003十、COMMIT命令 (提交并结束事务处理)语法：commit work;或 commit;十一、SAVEPOINT命令 (保存点)语法：savepoint savepoint_id;十二、ROLLBACK命令 (回滚事务)语法：rollback work;或 rollback;如果要求回滚到事务中某个特定的阶段，即保存点，需执行以下命令 Rollback to savepoint save_pt;例 29：update vendor_master set venadd1=1001where vencode=102;savepoint mark1;delete from vendor_master where vencode=102;savepoint mark2;rollback to savepoint mark1;commit;十三、GRANT命令 (授予权限)例 30：将表的select 和 update 权限授予用户帐号为martin的用户grant select,update on vendor_master to martin;例 31：授予用户对表的venname列和venpwd列的更新权限 grant update (venname,venpwd) on vendor_master to martin;例 32：with grant option 的用法 grant select on vendor_master to martin with grant option;十四、REVOKE命令 (撤消已授予用户的权限)例 33：收回select 和update 两项权限revoke select,update on vendor_master from martin;十五、算术操作符例 34：减法(-)操作符 select ename,sal-deptno from emp;例 35：操作符的优先级 select ename,deptno*(mgr-sal) from emp;十六、比较操作符 例 36：小于()操作符 select ename,mgr,sal from emp where sal10-2月-06 and enddate30-2月-06;十八、集合操作符1、UNION (union操作符返回vendor_master和newTable表中所有不重复的列值 )例 41：使用union操作符select vencode from vendor_master union select vencode from newTable;2、UNION ALL(union all 操作符合并两个查询选定的所有行，包括重复的行)例 42：使用union all操作符select vencode from vendor_masterunion all select vencode from newTable;例 43：使用union操作符并进行排序select vencode from vendor_masterunion select vencode from newTable order by 2;3、INTERSECT (交集操作符)例 44：列出两个表中都有的vencode值select vencode from vendor_master intersect select vencode from newTable;4、MINUS (减集操作符) (返回有第一个查询选定但是没有被第二个查询选定的行) 例 45： 如何使用减集操作符select vencode from vendor_master minus select vencode from newTable;十九、连接( | )操作符例 46：使用|操作符 select (供应商 | venname | 的地址是 | venadd1 | 、| venadd2 | 、 | venadd3) address from vendor_master where vencode=1002;结果为: 供应商 John Smith 的地址是福建、厦门、湖里二十、日期函数1、ADD_MONTHS (返回给指定的日期加上指定的月数后的日期值)格式为：add_months(d,n) ,其中d是日期，n表示月数例 47：将月数加上2个月select hiredate,add_months(hiredate,2) from emp;2、MONTHS_BETWEEN (返回两个日期之间的月数)格式为：months_between(d1,d2) ,其中d1,d2是日期3、LAST_DAY (返回指定日期当月的最后一天的日期值) 格式为：last_day(d) 例 48：查询当月的最后一天 select sysdate,last_day(sysdate) from dual;4、ROUND (返回四舍五入的日期值) 格式为：round(d,fmt) ,其中d是日期，fmt是格式模型。 例 49：将日期四舍五入select hiredate,round(hiredate,YEAR) from emp; 例 50：日期被舍入为最近的月份select round(date 2006-6-10,MONTH),round(date 2006-6-23,MONTH) from dual;5、NEXT_DAY (返回指定的下一个星期几的日期) 格式为：next_day(d,day) ,其中d表示日期，day指周内任何一天。 例 51：显示下星期五的日期是什么?select next_day(sysdate,星期五) from dual;6、TRUNC (指定日期截断为由格式模型指定的单位的日期，它只舍不入) 格式为：trunc(d,fmt), 如果不指定fmt，日期则被截断为天 例 52：当前日期将被截断为1月1日select trunc(sysdate,year) from dual;例 53：当前日期将被截断为紧靠前面的星期日select trunc(sysdate,day) from dual; 例 54：该语句不包含fmt,因此日期的时间部分将被截掉，即当日0点0分select trunc(sysdate) from dual;7、EXTRACT (提取日期时间类型中的特定部分) 格式为：extract(fmt from d) ,其中d是日期时间表达式，fmt是要提取的部分的格式,格式的取值可以是year, month, day, hour, minute, second 。例 55：提取日期或时间的一部分select extract(year from sysdate) from dual;select extract(month from sysdate) from dual;select extract(day from sysdate) from dual;二十一、字符函数1、CHR (根据ASCII码返回对应的字符) 例 56：将数字转化为字符select chr(67) from dual;结果为：C2、LPAD (左边填充) 和 RPAD (右边填充) 例 57：在左边填充=符号 select lpad(function,15,=) from dual; 结果为：=function3、TRIM (从字符串的开头或结尾剪裁特定的字符，默认剪裁空格)例 58：字符串开头的所有9都被去掉，一旦遇到9以外的数字，将停止操作。select trim(leading 9 from 99998769789999) from dual; 结果为：8769789999例 59：字符串结尾的所有9都被去掉，一旦遇到9以外的数字，将停止操作。select trim(trailing 9 from 99998769789999) from dual; 结果为：9999876978例 60： 两者都不指定时，将剪裁与trim_char相等的首部字符和尾部字符。 select trim(9 from 99998769789999) from dual;结果为：876978 4、LENGTH (返回字符串的长度)例 61：select length(frances) from dual; 结果为：75、DECODE (进行逐个值的替换) 例 62： decode类似3元运算符和Switch语句 select ename,deptno,decode(deptno,20,销售部,30,开发部) from emp;6、一些字符函数 函数说明输入输出结果Initcap(char)首字母大写select initcap(hello) from dual;HelloLower(char)转换为小写select lower(FUN) from dual;funUpper(char)转换为大写select upper(sun) from dual;SUNLtrim(char,set)左剪裁select ltrim(xyzadams,xyz) from dual;adamsRtrim(char,set)右剪裁select rtrim(xyzadams,adams) from dual;xyzTranslate(char,from,to)按字符翻译select translate(jack,abcd,1234)from dual;j13kReplace(char,search_str,replace_str)字符串替换select replace(jack and jue,j,bl)from dual;black and blueInstr(char,substr,pos)查找字符串位置select instr(worldwide,d) from dual;5Substr(char,pos,len)取子字符串select substr(abcdefg,3,2) from dual;cdConcat(char1,char2)连接字符串select concat(Hello,world) from dual;Helloworld二十二、转换函数1、TO_CHAR (将日期以fmt指定的格式转换为varchar2数据类型的值)格式为：to_char(d |n ,fmt) ,其中d是日期，n是数字,fmt是指定日期或数字的格式例 63：显示日期和时间select to_char(sysdate,YYYY 年 fmMM 月 fmDD 日 HH24:MI:SS) from dual;结果为：2006 年 12 月 22 日 20:36:51例 64：将数值转换为字符串select to_char(itemrate,C99999) from itemfile;2、TO_DATE (将char 或 varchar 数据类型转换为日期数据类型) 例 65：select to_date(2006-12-22,yyyy-mm-dd) from dual;结果为：22-12月-063、TO_NUMBER (将包含数字的字符串转换为number数据类型) 例 66：显示100的平方根select sqrt(to_number(100) from dual;结果为：10二十三、数字函数函数说明输入输出结果Abs(n)取绝对值select abs(-15) from dual;15Ceil(n)向上取整select ceil(44.778) from dual;45Sin(n)正弦select sin(1.571) from dual;0.999999979258613Cos(n)余弦select cos(0) from dual;1Sign(n)取符号select sign(-32) from dual;-1Floor(n)向下取整select floor(100.2) from dual;100Power(m,n)M的n次幂select power(4,2) from dual;16Mod(m,n)取余数select mod(10,3) from dual;1Round(m,n)四舍五入select round(100.256,2) from dual;100.26trunc(m,n)截断select trunc(100.256,2) from dual;100.25Sqrt(n)平方根select sqrt(4) from dual;2二十四、其他函数 1、NVL (将空值替换为指定的值) 格式为：nvl(expression1, expression2) , 例 67：将comm列中为空值的用0替换 select ename comm,nvl(comm,0) from emp;2、NVL2 格式为：nvl2(expression1, expression2, expression3) , 例 68：将comm列中为空值的用0替换 select ename comm,nvl2(comm,comm,0) from emp;3、NULLIF (比较两个表达式，如果他们相等，则返回空值，否则返回expr1)Nullif函数等价于case表达式：case when expr1=expr2 then null else expr1 end例 69：nullif函数的用法 select ename,comm,nullif(comm,500) from emp;二十五、分组函数 (又称为聚合函数)1、AVG (平均值)例 70： select avg(sal) from emp;2、MIN (最小值)例 71：select min(sal) from emp;3、MAX (最大值)例 72：select max(sal) from emp;4、SUM (总和)例 73：select sum(sal) from emp;5、COUNT (行数)(1)、count() (计算行数)例 74：select count(sal) from emp;(2)、count(*) (计算所有的行数，包括重复值和空值的行) 例 75： select count(*) from emp;(3)、count(col_name) (统计指定列中非空值的个数) 例 76：select count(comm) from emp;(4)、count(distinct col_name) (统计时除去重复的值)例 77：select count(distinct deptno) from emp;7、GROUP BY 子句例 78：select ename,max(sal) from emp group by ename;8、HAVING 子句例 79：select ename,max(sal) from emp group by enamehaving ename not in(SCOTT);二十六、分析函数1、ROW_NUMBER (为有序组中的每一行返回一个唯一的排序值，序号由order by 子句指定，从1开始。)例 80：对所有员工的工资进行排名, 即使工资相同，其排名也不能相同。select ename,job,deptno,sal,row_number() over(order by sal desc) as sal_rank from emp;例 81：对员工的工资按部门进行排名,即使工资相