oracle中的常用sql语句(12)

常用的sql语句,如建表、建索引、建约束条件;以及增删改查和高级查询操作

帖子标题,t.topic_date as 发布时间
from bbs_user u inner join bbs_topic t on (er_id = t.topic_user_id);

oracle:
select _id as 编号, _user_id as 版主编号 , er_name as 版主名, s.section_name as 板块名
from bbs_user_section us, bbs_user u,bbs_section s
where _user_id = er_id and _section_id = s.section_id;
sql92:
select _id as 编号, _user_id as 版主编号 , er_name as 版主名, s.section_name as 板块名
from bbs_user_section us inner join bbs_user u on(_user_id = er_id)
inner join bbs_section s on (_section_id = s.section_id);

--5.2.3 非等值连接
> < >= <= <>
select er_name as 登录名,t.topic_title as 帖子标题,t.topic_date as 发布时间
from bbs_user u,bbs_topic t
where er_id <> t.topic_user_id;

--5.2.4 外连接
oracle:
select t.topic_id as 主贴编号,t.topic_title as 主贴标题,t.topic_date as 发贴时间,
r.reply_content as 回帖内容,r.reply_date as 回帖时间
from bbs_topic t,bbs_reply r
where t.topic_id = r.reply_topic_id(+);
sql92:
select t.topic_id as 主贴编号,t.topic_title as 主贴标题,t.topic_date as 发贴时间,
r.reply_content as 回帖内容,r.reply_date as 回帖时间
from bbs_topic t left outer join bbs_reply r on(t.topic_id = r.reply_topic_id);


select t.topic_id as 主贴编号,t.topic_title as 主贴标题,t.topic_date as 发贴时间,
nvl(r.reply_content,'<木有回帖>') as 回帖内容,r.reply_date as 回帖时间
from bbs_topic t,bbs_reply r
where t.topic_id = r.reply_topic_id(+);

--5.2.5 自连接

select
t1.empno as 雇员编号, t1.ename as 雇员姓名 , t1.job as 职务,
t2.empno as 上司编号, nvl(t2.ename,'BOSS') as 上司姓名
from emp t1, emp t2
where t1.mgr = t2.empno(+);


--5.3 子查询
--5.3.1 子查询类型
单行子查询
多行子查询
多列子查询
1、子查询会先于父查询执行
2、子查询允许嵌套，最里面的子查询最先执行
3、子查询一般出现在比较操作符的右边

--5.3.2 单行子查询

select * from bbs_user
where user_birthday < (select user_birthday from bbs_user where user_name = 'kitty_cat');

select * from bbs_topic
where topic_user_id = (select user_id from bbs_user where user_name = 'barrywey');

select * from emp
where sal >
(select sal from emp where ename = upper('allen'))
and deptno = (select deptno from emp where ename = upper('allen'));

--5.3.3 多行子查询

an

搜索“diyifanwen.net”或“第一范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，第一范文网，提供最新资格考试认证oracle中的常用sql语句(12)全文阅读和word下载服务。