MySQL中的多表设计

多表设计:利用外键约束实现,是为了保证数据的完整性。
外键实际上是一种约束,表示这个属性是另一个实体集中的主标识属性,即另一个表中的主键。
外键用于建立和加强两个表数据之间的链接的一列或多列。
定义外键约束:
1)可以直接在create语句中定义外键:foreign key 当前表名 (字段名) references 目标表名 (目标表的主键)
2)创建完语句后,可以直接使用修改语句定义:alter table 表名 add foreign key 当前表名 (字段名) references 目标表名 (目标表的主键);
多表设计中有三种实体关系:
(1)一对一(1:1):不必引入关系表,很少使用,因为完全可以作为一张表出现,但是也可能会因为一些业务的原因,而需要设计1对1的表结构。例如:一个公司对应一个地址,一个地址也只能对应一个公司。
建表原则:
第一种是主键对应:将两个表的主键进行关联;
第二种是唯一外键对应:在任何一方加一个外键,但是需要设置成唯一(unique),指向另一方的主键。
(2)一对多(1:N)::可以不引入关系表,例如一个班级可以有多个学生,但是一个学生只能属于一个班级,
建表原则(在多方表中进行维护):在多方表中添加一个字段,把该字段作为外键,并且指向一方表中的主键;将关系的属性及非多方的主标识加入到多方表;多方表的外键是非多方实体的主标识。
(3)多对多(N:N): :需要引入关系表,如:生活中一个学生可以选择多门课程,每一门课程也可以被多个学生所选择。
建表原则:
需要创建第三张表,该表至少有两个字段,作为外键分别指向多对多的双方的主键;
联系的属性及两个实体的主标识形成关系表;
关系表的主键为两个实体主标识的组合。
下面为多表设计的一个例子:
CREATE TABLE teacher (
id int(11) NOT NULL primary key auto_increment,
name varchar(20) not null unique
);
CREATE TABLE student (
id int(11) NOT NULL primary key auto_increment,
name varchar(20) NOT NULL unique,
score double default ‘0’,
teacher_id int(11) NOT NULL,
FOREIGN KEY (teacher_id) REFERENCES teacher (id)
) ;

多表查询—笛卡尔积
多表查询时,如果不指定表之间的连接条件,则连接变成笛卡尔乘积操作,进行笛卡尔积后,查询结果中存在大量无意义的数据,我们通过加上WHERE过滤条件得到想要的数据,这种横跨多表的查询操作一般用连接完成。

1,多表查询—内链接
内连接分成两种,两个表之间是有联系的,通过一个外键关联。
1)普通内连接
通过使用 select * from 表1 inner join 表2 on 条件,eg:select * from a inner join b on a.id=b.id;
2)隐式内连接
通过使用select * from 表1,表2 where 条件,eg:select * from a,b where a.id = b.id;
2,多表查询—外链接
1)左外连接(把左边表所有的数据都查出来)
语法:select * from 表1 left outer join 表2 … on
2)右外连接 (把右边表所有的数据都查出来)
语法:select * from 表1 right outer join 表2 … on

小总结:内连接与外链接的区别:
内连接:也称为自然连接,只有两个表相匹配的行才能在结果集中出现。返回的结果集选取了两个表中所有相匹配的数据,舍弃了不匹配的数据。
外连接:不仅包含符合连接的条件行,而且还包括左表(左外连接)、右表(右外连接)或两个边接表(全外连接)中的所有数据行。
3,多表查询—子查询
在sql语言中,select…from…语为一个查询块,将一个查询块嵌套在另一个查询块中作为条件称为嵌套查询,也称为子查询.外层的查询块称为父查询,内层的查询块称为子查询。
语法:select * from table where 条件 > (select * from table where 条件)

子查询-运算符
常用的运算符
< 小于 > 大于
<= 小于等于 >=大于等于
= 等于 <>或!= 不等于 in 范围
常用的运算符
any 或者 all

any:大于子查询中的最小值。
all: 大于子查询中的最大值。
语法:select * from table where 条件 >any(select * from table where 条件)

下面举一些小例子:
查看XX所属的部门名称和员工名称:
select d.did,d.dname,e.ename from dept d,emp e where d.did = e.dno
统计每个部门的人数(按照部门名称统计):
select d.did,d.dname,count(*) from dept d,emp e where d.did = e.dno group by d.did order by d.did asc;
统计部门的平均工资(按部门名称统计):
select d.dname,avg(salary) from dept d,emp e where d.did = e.dno group by d.dname;
统计部门的平均工资大于公司平均工资的部门:
select d.dname,avg(salary) as sa from dept d,emp e where d.did = e.dno group by d.dname having sa > (select avg(salary) from emp );

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/52e279e797b4970f6cbcd0d82c8a6eef.html