数据库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