oracle常用操作1

select * from emp;

select * from dept;

create table course_inf
(
 course_id number primary key,
 course_name varchar2(50),
 course_time varchar2(50),
 prefix_id number,
 foreign key(prefix_id) references course_inf(course_id)
);

create sequence course_id_seq
start with 1
increment by 1
nominvalue
nomaxvalue
cache 10;

select * from course_inf;

insert into course_inf
values(course_id_seq.nextval , 'javase' , '1.5月' , null);

insert into course_inf
values(course_id_seq.nextval , 'javaweb' , '0.5月' , 1);

insert into course_inf
values(course_id_seq.nextval , 'struts' , '5天' , 2);

insert into course_inf
values(course_id_seq.nextval , 'android' , '20天' , 1);

select c.*, p.course_name
from course_inf c
left join course_inf p
on c.prefix_id = p.course_id;

select * from emp;

select ename , sal + comm "realSal" from emp;

select concat('aaa' , null) from dual;
select 5 + null from dual;

4. and,  or等逻辑运算
  false → null → true
  and往false方向取值。
  or往true方向取值。
     
select  from dual;

select ename , nvl(sal , 0) + nvl(comm , 0) "realSal" from emp;

select nvl2(comm, comm , 0) from emp;
select comm,  nvl2(comm, '800' , 0) from emp;

select nullif('aa' , 'aa') from dual;

select nullif('aa' , 'aaaa') from dual;

select * from emp;

select ename , nvl(sal , 0) + nvl(comm , 0) from emp;

# 要求表达式的值与被比较值相等。
select ename , case nvl(sal , 0) + nvl(comm , 0)
               when 2000 then '有点低，暂不考虑'
               when 3000 then '不错'
               when 4000 then '挺合适的'
               when 5000 then '这个好'
               else '其他' end
               from emp;

# 要求表达式的值与被比较值相等。
select ename , case 
               when nvl(sal , 0) + nvl(comm , 0) < 1000 then '绝不考虑'
               when nvl(sal , 0) + nvl(comm , 0) < 2000 then '有点低，暂不考虑'
               when nvl(sal , 0) + nvl(comm , 0) < 3000 then '不错'
               when nvl(sal , 0) + nvl(comm , 0) < 4000 then '挺合适的'
               else '很好' end
               from emp;

select ename , decode(nvl(sal , 0) + nvl(comm , 0)
               , 2000, '有点低，暂不考虑'
               , 3000 , '不错'
               , 4000 , '挺合适的'
               , 5000 , '这个好'
               , '其他')
               from emp;

select * from emp;
select * from dept;

select e.* , d.dname from emp e, dept d
where e.deptno = d.deptno;

create table user_inf
(
 user_id number primary key,
 user_name varchar2(50),
 user_pass varchar2(50)
);

create table horse_inf
(
 horse_id number primary key,
 horse_color varchar2(50),
 horse_weight number,
 owner_id number,
 foreign key(owner_id) references user_inf(user_id)
);

select * from user_inf;
insert into user_inf
values(1, '孙悟空' , '222');
insert into user_inf
values(2, '猪八戒' , '333');
insert into user_inf
values(3, '白骨精' , '555');

insert into horse_inf
values(1, '白色' , 334 , 1);
insert into horse_inf
values(2, '红色' , 334 , 1);
insert into horse_inf
values(3, '绿色' , 334 , 2);
insert into horse_inf
values(4, '蓝色' , 334 , 2);
insert into horse_inf
values(5, '黄色' , 344 , null);

select h.* , o.user_name
from horse_inf h , user_inf o
where h.owner_id <= o.user_id;

select * from horse_inf;

select h.* , o.user_name
from horse_inf h , user_inf o
where h.owner_id = o.user_id;

# 外联接运算符的作用：把对方表里没有选出的记录全部列出来。
select h.* , o.user_name
from horse_inf h , user_inf o
where h.owner_id(+) = o.user_id;

select h.* , o.user_name
from horse_inf h , user_inf o
where h.owner_id = o.user_id(+);

commit;

select *
from emp e
natural join dept d;

select * from emp;
select * from dept;

select h.* , o.user_name
from horse_inf h
cross join user_inf o;

select h.* , o.user_name
from horse_inf h
join user_inf o
on h.owner_id = o.user_id;

select h.* , o.user_name
from horse_inf h
join user_inf o
on h.owner_id < o.user_id;

select h.* , o.user_name
from horse_inf h
left outer join user_inf o
on h.owner_id = o.user_id;

select h.* , o.user_name
from horse_inf h
right outer join user_inf o
on h.owner_id = o.user_id;

select h.* , o.user_name
from horse_inf h
full outer join user_inf o
on h.owner_id = o.user_id;

begin
   -- 执行输出
   dbms_output.put_line('Hello World!');
end;

declare
   -- 声明变量时，先写变量名。
   v_name varchar2(50);
   
   -- 定义常量
   c_max_age constant number := 120;
   
   v_color varchar2(50) not null default '红色';
begin
   v_name := '孙悟空';
   dbms_output.put_line(v_name);
   
   v_color := '绿色';
   
   dbms_output.put_line(v_color);

end;