oracle从入门到精通第三篇(条件取值语句|约束|子查询|自查询|连表查询)

条件取值语句

条件取值语句（decode()）：

decode(c1,c2,c3,c4,c5…Cx,Cx+1)
c1：拿来比较的值
从第二个参数开始，每两个参数看作是一组，拿每一组的第一个参数和c1进行比较，如果相同则返回该组的第二个参数
第一次判断：c2 == c1 ? c3 :
第二次判断：c4 == c1 ? c5 :
如果参数个数是奇数个，并且最终判断没有相同的值，则返回空
如果参数个数是偶数个，并且最终判断没有相同的值，则返回最后一个参数的值

例：
如果部门为10，那么给员工涨薪200
如果部门为20，那么给员工减薪200
– 其他部门涨薪500

select ename,deptno,sal,decode(deptno,10,sal+200,20,sal-200) a from emp order by deptno;
select ename,deptno,sal,decode(deptno,10,sal+200,20,sal-200,sal+500) a from emp order by deptno;

练习：
根据emp表中job字段，如果工种为CLERK则涨薪200
如果工种为SALESMAN则减薪200，其他工种涨薪500

条件取值语句（case when）：

(case – 条件
when – 如果条件成立
then – 则返回
else – 默认值
end) – 结束

SELECT ename, deptno, sal,( CASE deptno WHEN 10 THEN sal + 200 WHEN 20 THEN sal - 200 END ) a 
FROM
	emp 
ORDER BY
	deptno;

SELECT ename, deptno, sal,( CASE deptno WHEN 10 THEN sal + 200 WHEN 20 THEN sal - 200 ELSE sal + 500 END ) a 
FROM
	emp 
ORDER BY
	deptno;

约束：限制

主键约束：primary key

主键列：在一个表中能够唯一定位一条数据的列，非空且唯一 一张表只能有一个主键
*：constraint 约束名 可以不写

CREATE TABLE test (
id number ( 5 ) CONSTRAINT zj PRIMARY KEY,
NAME varchar2 ( 20 ));

外键约束：foreign key *：references

外键：在子表中如果有一个列引用了母表中的主键列，那么这个列在子 表中就被称为外键列

分析oracle自带的表中的主外键

   表                      主键                        外键
	emp                   empno            			deptno  salno          		 子表
	dept              	  deptno                                            		 母表
	dept              	  deptno                    	salno                 		 子表       
	salgrade              salno                                               	 母表

非空约束：not null

唯一约束：unique

检查约束：check

小练习

teacher表

create table teacher(
id number(5) primary key,
name varchar2(20),
salary number(6) check(salary <= 20000));

insert into teacher values(1,'zhoulaoshi',12222);
insert into teacher values(2,'zhanglaoshi',13333);
insert into teacher values(3,'lulaoshi',14444);
insert into teacher values(4,'laoda',666);

班级表

create table class(
id number(5) primary key,
name varchar2(20),
tid number(5) references teacher(id));

insert into class values(1,'ET1912',2);
insert into class values(2,'ET1911',1);
insert into class values(3,'ET1910',3);
insert into class values(4,'ET1909',4);

学校表

create table school(
id number(5) primary key,
name varchar2(20) not null unique,
phone varchar2(20) check(length(phone) = 11));

insert into school values(1,'山大','13356785678');
insert into school values(2,'山科','13378907890');
insert into school values(3,'临大','15567896789');
insert into school values(4,'北大','15523456789');

学生表

create table student(
id number(5) primary key,
name varchar2(20) unique,
salary number(5) check(salary between 5000 and 10000),
email varchar2(50) unique,
birthday date,
sid number(5) references school(id),
cid number(5) references class(id));

insert into student values(1,'葫芦娃',5000,'hlw@163.com',sysdate,2,3);
insert into student values(2,'蜘蛛侠',6000,'zzx@126.com',to_date('19951213121212','yyyy-mm-dd hh24:mi:ss'),1,2);
insert into student values(3,'哪咤',7000,'nz@ali.com',to_date('19961213121212','yyyy-mm-dd hh24:mi:ss'),2,1);
insert into student values(4,'白龙',8000,'bl@yahoo.com',to_date('19981213121212','yyyy-mm-dd hh24:mi:ss'),4,4);

查看当前用户下有哪些约束：

select constraint_name,constraint_type from user_constraints;

建表以后，如何添加约束

alter table test add constraint pk_zj primary key (id);

查看当前用户下有哪些约束：

select constraint_name,constraint_type from user_constraints;

如何删除约束：

alter table test drop constraint pk_zj;

数据库中约束越多，表越健壮，效率越低 通常用到的约束：主键约束，外键约束

子查询 = 嵌套查询 = 某些条件是通过查询得到的

select 子查询 from 子查询 where 子查询;

谁和葫芦娃一个学校的？
1）葫芦娃是哪学校的？

select sid from student where name = '葫芦娃';

2）谁还是葫芦娃这个学校的？

select name,sid from student where sid = 2;

SELECT NAME
	,
	sid 
FROM
	student 
WHERE
	sid = ( SELECT sid FROM student WHERE NAME = '葫芦娃' ) 
	AND NAME != '葫芦娃';

学生表的学生都上哪些学校？

SELECT NAME,( SELECT NAME FROM school WHERE school.id = student.sid ) schoolname 
FROM
	student;

练习
查询所有工种为CLERK的员工的工号、员工名和部门名。

SELECT empno, ename,( SELECT dname FROM dept WHERE dept.deptno = emp.deptno ) dname 
FROM
	emp 
WHERE
	job = 'CLERK';

自查询

SELECT
	empno,
	ename,
	dname 
FROM
	emp,
	dept,
	salno 
WHERE
	emp.deptno = dept.deptno 
	AND job = 'CLERK';

连表查询

内连接 inner join on = join on

通过关联关系能够得到的部分数据

select student.name,school.name from student
join school on student.sid = school.id;

练习
-查询所有工种为CLERK的员工的工号、员工名和部门名。

外连接 outer join on

不仅包含有关联关系的数据，还包含没有关联关系的数据

左外连接 left join on

from 左表 left join 右表 on 关联关系 以左表为主，左表的所有数据都展示，关联不上的右表的数据以空展示

*：左表关联上右表的数据 + 关联不上的左表的数据

SELECT
	school.NAME,
	student.NAME 
FROM
	school
	LEFT JOIN student ON student.sid = school.id;

练习
-查询所有部门及其员工信息，包括那些没有员工的部门

右外连接 right join on

语法

from 左表 right join 右表 on 关联关系 以右表为主，右表的所有数据都展示，关联不上的左表的数据以空展示

*：左表关联上右表的数据 + 关联不上的右表的数据

SELECT
	student.NAME,
	school.NAME 
FROM
	student
	RIGHT JOIN school ON student.sid = school.id;

SELECT
	student.NAME,
	school.NAME,
	class.NAME 
FROM
	student
	RIGHT JOIN school ON student.sid = school.id
	LEFT JOIN class ON class.id = student.cid;

练习：
展示学校名，学生名，老师名，班级名
表名 主键 外键

school        id        
student       id         sid(school.id)   cid(class.id)
teacher       id
class         id         tid(teacher.id)

SELECT
	school.NAME,
	student.NAME,
	teacher.NAME,
	class.NAME 
FROM
	student
	LEFT JOIN class ON student.cid = class.id
	LEFT JOIN school ON student.sid = school.id
	LEFT JOIN teacher ON class.tid = teacher.id;

全外连接 full join on

交叉连接 cross join