1、oracle 字段连接用‘||’;
2、EXTRACT()函数的使用说明:
EXTRACT(YEAR FROM SYSDATE)中FROM 的下一个参数是日期类型的时候可以抽取年月日.就是说这个参数是日期类型的时候就可以抽取年/月/日,而抽取不了时/分部分.抽取时分部分得在FROM 后面加带TIMESTAMP 且后面加时间字符.
这里要注意,在(MINUTE FROM TIMESTAMP '2005-10-10 10:10:10')中,如果把'2005-10-10 10:10:10'用日期类型的字段来代替并转换成字符类型后,也是错误的,估计是这方法的问题.
比如(MINUTE FROM TIMESTAMP to_date(to_char(日期类型,'YYYY-MM-DD hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss')),这样执行不了的.
3、decode()函数:
使用说明:decode(stu.name,'jack','张三','tom','李四','王五');
解释:if(stu.name.equals("jack"))
return "张三";
else if(stu.name.equals("tom"))
return "李四";
eles
retutn "王五";
4:将查询出的多条数据插入一张新表中:
declare
begin
for g(g为别名,任意取) in(
select stu.name,stu.age,stu.six,count(stu.id) stu_count from student stu
) loop
insert into student_new (name_new,age_new,six_new,cout_new)
values(g.name,g.age,g.six,g.stu_count);
commit;
end loop;
end;
/
5、抽取重复记录
select count(*),stu.name from student stu group by stu.name having count(*)>1;
6、一条记录同时插入多个表(9i以上版本实现方法)
insert all
into student (name,age) values(name_old,age_old)
into student_new (name_new,age_new) values(name_old,age_old)
select name_old,age_old from student_old where id_old=1;
commit;
7、行转列
drop table student;
create table student
(
id number(19),
name varchar2(50),
subject varchar2(50),
mark number(19,2)
);
select * from student for update;
alter table student add(enddate date);
delete student s where to_char(s.enddate,'yyyy') = 2009
select s.name as 姓名,
sum(decode(s.subject, '语文',s.mark)) as 语文,(如果查询的科目是语文,则返回语文的分数)
sum(decode(s.subject, '数学',s.mark)) as 数学,
sum(decode(s.subject, '英语',s.mark)) as 英语
from student s group by s.name
8、interval函数
--²éѯϵͳµ±Ç°Ê±¼ä¼õ7Ì죨1¸öÐÇÆÚÇ°µÄÈÕÆÚ£©
select to_date(sysdate) - interval '7' day from dual
--²éѯϵͳµ±Ç°Ê±¼ä¼õ2¸öÔ£¨2¸öÔÂÇ°µÄÈÕÆÚ£©
select to_date(sysdate) - interval '2' month from dual
--²éѯϵͳµ±Ç°Ê±¼ä¼õ2Ä꣨2ÄêÇ°µÄÈÕÆÚ£©
select to_date(sysdate) - interval '2' year from dual
Oracle 学习
最新推荐文章于 2023-01-23 23:39:45 发布