数据库SQL之表关系&连接查询

/*
  表关系：一对多：多端加外键，一端加主键
  多对多：添加一个关系表，变为一对多的关系
  一对一：外键-->唯一键

  E-R图：实体-->表
  属性-->列
  元组-->行
  主键列：唯一仅有的
  外键列：用来约束主键列关联项的取值
 

• 创建

create table book(
  bid number(5),
  bname varchar2(20),
  slid number(5)
)

• 建立主键

alter table book
add constraints PK_BOOKlei
primary key(bid)

• 建立外键

alter table book
add constraints FK_BOOK_BOOKlei
foreign key(slid)
references booklei(blid)

• 创建

create table booklei(
  blid number(5),
  blname varchar2(20)
)

• 多对多 

create table student(
  sid number(5) primary key,
  sname varchar2(20)
)
create table tea_stu(
  tid number(5) references student(sid),
  tid number(5) references teacher(tid)
)
create table teacher(
  tid number(5) primary key,
  tname varchar2(20)
)

• 一对一

create table person(
  pid number(5) primary key,
  pname varchar2(20),
  cid number(5) unique references idcard(cid)
)
create table idcard(
  cid number(5) primary key,
  cnumber varchar2(20)
)

• 连接查询（笛卡尔积）
• 等值连接（不一定是=号，两张表也不一定是有主外键关系）

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

• 查询部门为'ACCOUNTING'的员工(***连接查询和嵌套子查询的扫描次数不同***)

select * from emp e,dept d where e.deptno = d.deptno and d.dname = 'ACCOUNTING'
select * from emp where deptno = (select deptno from dept where dname = 'ACCOUNTING')

• 查询员工的薪金等级

select * from SALGRADE s,emp e where e.sal between s.losal and s.hisal

• 查询各个工资等级的人数

select s.grade,count(*) from emp e,salgrade s
where e.sal between s.losal and s.hisal
group by s.grade

• 查询每个部门每种工资等级的人数

select s.grade,d.dname,count(*) from emp e, dept d,salgrade s 
where e.deptno = d.deptno and e.sal between s.losal and s.hisal
group by s.grade,d.dname
order by s.grade

• 外连接：左外连接 (以左边的表为标准) / 右外连接(以右边的表为标准)

select * from emp e left join dept d on d.deptno = e.deptno
select * from dept d left join emp e on d.deptno = e.deptno
where empno is null

• 自连接

select e1.ename,count(*) from emp e,emp e1 where e.mgr = e1.empno
group by e1.ename