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

/* 表关系：一对多：多端加外键，一端加主键 多对多：添加一个关系表，变为一对多的关系 一对一：外键--唯一键 E-R图：实体--表 属性--列 元组--行 主键列：唯一仅有的 外键列：用来约束主键列关联项的取 */ --创建 create table book( bid number(5), bname

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

  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