mysql可以关联视图_Mysql 五： 数据库自关联、视图

怎么判断两张表中的关系是 一对多 还是 多对一 还是 一对一？

表A 中的 一条数据 对应 表B 中的 一条数据， 则为 一对一 。

表A 中的 一条数据 对应 表B 中的 多条数据， 则为 一对多 。

表A 中的 多条数据 对应 表B 中的 一条数据， 则为 多对一 。

这个 对应关系 是以 一条数据 为单位 看对应关系 的，

而不是按 表名的逻辑关系 判断的 。

购物车的属性 ： id ， 消费者id ， 商品id ， 数量 ，价格

商品的属性 ： id ，名称 ，价格 ， 单位 ， 日期 ， ...

同一个商品(一条记录) 可以被多个用户加入购物车 (多条记录) 。

所以 商品 与 购物车 的关系 是 1 对 n

切记 【不要理解为】 一个购物车可以存放 多个商品，商品 对购物车为 n 对 1！！！！

使用关系的步骤 ：

1.确定实体间是否存在关系

2.确定是几对几的关系

3.确定在那个实体中建立字段

如果太过抽象，可以举出具体的例子进行分析

一、关联查询

查询男生的姓名、总分

男生 ： students.gender=1

姓名 ： students.name

总分 ： sum(scores.score)

建立连接 ： students.id=scores.stuid

select students.name,sum(scores.score)

from students

right join scores on scores.stuid=students.id

where gender=1

group by students.id;

+--------+-------------------+

| name | sum(scores.score) |

+--------+-------------------+

| 郭靖 | 198.00 |

+--------+-------------------+

1 row in set (0.00 sec)

左连接查询 ：

select students.name,scores.score from scores right join students on scores.stuid=students.id;

+-----------+-------+

| name | score |

+-----------+-------+

| 郭靖 | 99.00 |

| 郭靖 | 99.00 |

| 黄蓉 | NULL |

| 杨过 | NULL |

| 小龙女 | NULL |

| 雕 | NULL |

+-----------+-------+

6 rows in set (0.00 sec)

mysql> select students.name,sum(scores.score) from students right join scores on scores.stuid=students.id;

+--------+-------------------+

| name | sum(scores.score) |

+--------+-------------------+

| 郭靖 | 198.00 |

+--------+-------------------+

1 row in set (0.00 sec)

如上 ： 如果成绩为 null ， 则 使用了 sum 后 该记录不会出现。

二、自关联 【可用于常见的级联菜单】

当我们需要存储 省/市/区 这样的级联信息时，使用三张表，市表里存对应的省的 id ，

区表里存对应的市的 id ，这样使用外键进行关系的存储，也可以，但是由于省、市

表里面数据太少，可能会导致表性能的 浪费。

为此，我可以创建一张表来即存储关系 又存储数据，如下

id name pid

数据自己的 id 数据的名称 数据的父级id

这个就称为 自关联 ，这样的好处在于不用增加新表的开销。

create table areas(aid int primary key,

atitle varchar(20),

pid int,

foreign key(pid) references areas(id)

);

注意这个外键是指向他自己的主键的！！！！！

插入数据

插入 省

insert into areas value(1,'广东',null);

插入 市

insert into areas value(1001,'深圳',1);

查询数据

查询所有的省 【这里不能写 pid=null，要用 is ！！】

select * from areas where pid is null;

查询aid为1的省里面的市

select * from areas where pid=1;

查询广东省里面的市

select atitle from areas where pid=(select aid from areas where atitle='广东');

自关联查询：

查询省的名称为 广东 的所有城市

select city.* from areas as city

inner join areas as province on city.pid=province.aid

where province.atitle='广东';

| aid | atitle | pid |

+-----+--------+------+

| 101 | 深圳 | 1 |

+-----+--------+------+

1 row in set (0.00 sec)

【注意】 ： 自关联的表进行关联查询时要使用别名，意思是要先把areas这种表当做

省表来用，再把areas这张表当做市表来用，逻辑上就有了两张表(虽然物理上

还是同一张 表)，相当于两张表在做关联查询。否则就会报错，如下 ：

select areas.* from areas

inner join areas on areas.pid=areas.aid

where areas.atitle='广东';

报错信息 ：

ERROR 1066 (42000): Not unique table/alias: 'areas'

三、视图

对于复杂的查询，在多次使用后，维护是一件非常麻烦的事情

解决办法 ： 定义视图

视图的本质就是对查询的 一个封装

定义视图 ：

语法 ：

create view 视图名称 as 需要封装的sql语句;

为了辨别这个是封装的 视图 ，而不是表，可以在这个名字前面加个 v_

例如 ：

create view v_stuscore as

select students.*,scores.score from scores

inner join students on scores.stuid=students.id;

视图的用途就是用于查询 ：

select * from v_stuscore ;

结果 ：

+----+--------+--------+---------------------+----------+----------+-------+

| id | name | gender | brithday | isDelete | hometwon | score |

+----+--------+--------+---------------------+----------+----------+-------+

| 7 | 郭靖 | | 0000-00-00 00:00:00 | | 蒙古 | 99.00 |

| 7 | 郭靖 | | 0000-00-00 00:00:00 | | 蒙古 | 99.00 |

+----+--------+--------+---------------------+----------+----------+-------+

2 rows in set (0.00 sec)

如上 ： 把

select students.*,scores.score from scores

inner join students on scores.stuid=students.id;

这个 select 语句 封装起来放到一个视图 v_stuscore ，

在 以后需要 执行

select students.*,scores.score from scores

inner join students on scores.stuid=students.id;

这个 select 查询的时候，直接 使用 v_stuscore 这个视图代替这个select语句，

同样可以获得这个select查询的结果集。

这样的好处是 ：

1.减少 重复的代码

2.便于维护 ，以后如果这个语句需要修改，那只要修改视图里这一个地方就行了，

不使用视图则所有用到这个 select 的地方都要修改

修改视图 ：

语法 ：

alert view 要修改的视图名称 as 需要封装的新的sql语句;

例如 ：

alter view stuscore as

select students.*,scores.score from scores

inner join students on scores.stuid=students.id

where students.isDelete=1;