【第30天】Oracle的约束、关联查询

• 1 约束

  • 1.1 主键（primary key）约束

  • 1.2 外键（foreign key）约束

  • 1.3 非空（not null）约束

  • 1.4 唯一（unique）约束

  • 1.5 检查（check）约束

• 2 关联查询

  • 2.1 嵌套子查询

  • 2.2 连表（级联）查询

    • 2.2.1 内连接

    • 2.2.1 外连接

1 约束

       针对每个字段所加的限制成为约束。

1.1 主键（primary key）约束

       主键约束在一个表中能够唯一定位一条数据的列。主键约束的字段的特点：非空且唯一。也就是说一张表只有一个主键，数据库会在具有唯一性的列上自动添加唯一性索引。

1.2 外键（foreign key）约束

       外键约束（foreign key，reference），在子表中，如果有一个列引用了母表主键列上的值，那么这个列在子表中就是外键列。一张表可以有多个外键。注意，使用外键会降低效率，当数据库需注重效率时，考虑使用其他办法替代外键。

emp 主键empno 外键deptno （子表）
dept 主键deptno 外键salno （母表）

dept 主键deptno 外键salno （子表）
salgrade 主键salno （母表）

1.3 非空（not null）约束

       当字段被加上非空约束时，该字段不允许null值存在。是检查（check）约束的一种形式。

1.4 唯一（unique）约束

       当字段被加上非空约束时，该字段不允许null值存在。是检查约束的一种形式。

1.5 检查（check）约束

       加上检查约束的字段，当有数据加进来时，先对这个数据做判断是否符合规范。

示例：

	create table teacher(
		---主键约束
		id number(3) primary key,
		---非空、唯一约束
		name varchar2(20) not null unique,
		---检查约束
		salary number(8,2) check(salary<=20000)
	);
	
	
	create table class(
		id number(3) primary key,
		name char(6) not null unique,
		qq varchar2(20) check(length(qq)>6),
		---外键约束
		tid number(3) references teacher(id)
	);
	
	
	create table school(
		id number(3) primary key,
		name varchar2(20) not null unique,
		phone varchar2(20) check(length(phone)=11)
	);
	
	
	create table student(
		id number(3) primary key,
		name varchar2(20) not null,
		salary number(7,2) check(salary between 5000 and 20000),
		email varchar2(50) unique,
		sid number(3) references school(id),
		cid number(3) references class(id)
	);

• 查看已经存在的约束：
  select constraint_name,table_name,constraint_type from user_constraints where table_name = ‘表名（大写）’;

• 如何删除约束：
  alter table 表名 drop constraints 约束名;
  约束名一般省略，自动生成。要删除时先查询这个表有哪些约束，删除查到的名字。

数据中约束越多，表越健壮，效率越低。通常一张表常用主键约束，外键约束。

2 关联查询

当关联查询涉及到多表（结果集）时要注意加别名以区分字段，在最终显示时若遇到重复的字段，一定要加别名来区分含义。

2.1 嵌套子查询

       也叫嵌套查询，某些条件是通过查询得出来的，括号中子查询展示的是select语句的结果集。

select (select name from school where id = sid),name from student;
select name from student where sid = (select sid from student where name = ‘张三’);

2.2 连表（级联）查询

2.2.1 内连接

inner join on（inner可以省略）

通过关联关系级联出来部分数据，但不显示空数据，只显示关联后两表都具有的数据
select student .name,school .name from student join school on school .id = student.sid;
= select student .name,school .name from student, school where school .id = student.sid;

2.2.2 外连接

• outer join on
  不仅包括有关联关系的数据，还包含没有关联系的数据

• left join on
  左外连接，以左表为主，左表的所有数据都展示，关联不上的数据右表的所有信息都以空展示
  from 左表 left join 右表 on 关联条件
  得到的结果集为：左表关联上右表的数据+关联不上的左表的数据

• right join on
  右外连接，以右表为主，右表的所有数据都展示，关联不上的数据左表的所有信息都以空展示
  from 左表 right join 右表 on 关联条件
  得到的结果为：左表关联上右表的数据+关联不上的右表的数据

例：
将学生表，学校表，班级表，老师表中的学生名，学校名，班级名，老师名统计出来：

	select student.name,school.name,class.name,teacher.name from student
	left join class on class.id = student.cid
	left join teacher on teacher.id = class.tid
	right join school on school.id = student.sid;	

• full join on
  全外连接，两张表关联后不管是否为空，数据都展示。
  from 左表 full join 右表 on 关联条件
  左表关联上右表的数据+关联不上的左表的数据+关联不上的右表的数据

• cross join
  交叉连接，两张表的数据一一对应，交叉形成最后结果，不带任何条件过滤时，返回的是笛卡尔积。

连表（级联）查询和子查询的应用场景：
1.如果需要查询的数据在多个表中，使用连表（级联）查询
2.不需要展示表A中的列，但是需要表A中的列作为条件，可以使用子查询
3.子查询中如果使用in some any all这几个关键字，建议转换成多表连接