多表设计:利用外键约束实现,是为了保证数据的完整性。 外键实际上是一种约束,表示这个属性是另一个实体集中的主标识属性,即另一个表中的主键。 外键用于建立和加强两个表数据之间的链接的一列或多列。 定义外键约束: 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 );
来源: http://www.linuxidc.com/Linux/2017-04/142602.htm