6 高级2 自连接 视图

1.如何判断关系

2.自关联

• 问题：能不能将两个表合成一张表呢？
• 思考：观察两张表发现，citys表比provinces表多一个列proid，其它列的类型都是一样的
• 意义：存储的都是地区信息，而且每种信息的数据量有限，没必要增加一个新表，或者将来还要存储区、乡镇信息，都增加新表的开销太大

　　　　　　

• 答案：定义表areas，结构如下
  • id
  • atitle
  • pid
• 因为省没有所属的省份，所以可以填写为null
• 城市所属的省份pid，填写省所对应的编号id
• 这就是自关联，表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id
• 在这个表中，结构不变，可以添加区县、乡镇街道、村社区等信息
• 创建areas表的语句如下：

mysql> create table areas(
    -> id int primary key auto_increment not null,
    -> title varchar(20),
    -> pid int,
    -> foreign key(pid) references areas(id));

mysql> desc areas;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| title | varchar(20) | YES  |     | NULL    |                |
| pid   | int(11)     | YES  | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

　　　　

• 从sql文件中导入数据

source areas.sql;

mysql> select count(*) from areas;
+----------+
| count(*) |
+----------+
|     3518 |

 

• 查询省的名称为“山西省”的所有城市

mysql> select id from areas where title='山西省';
+--------+
| id     |
+--------+
| 140000 |
+--------+

mysql> select * from areas where pid=140000;
+--------+-----------+--------+
| id     | title     | pid    |
+--------+-----------+--------+
| 140100 | 太原市    | 140000 |
| 140200 | 大同市    | 140000 |
| 140300 | 阳泉市    | 140000 |
| 140400 | 长治市    | 140000 |
| 140500 | 晋城市    | 140000 |
| 140600 | 朔州市    | 140000 |
| 140700 | 晋中市    | 140000 |
| 140800 | 运城市    | 140000 |
| 140900 | 忻州市    | 140000 |
| 141000 | 临汾市    | 140000 |
| 141100 | 吕梁市    | 140000 |
+--------+-----------+--------+

mysql> select * from areas where pid=(select id from areas where title='山西省');

select * from areas as sheng
inner join areas as shi on sheng.id=areas.pid

select sheng.id as sid,sheng.title as stitle,
shi.id as shiid,shi.title as shititle
from areas as sheng
inner join areas as shi on sheng.id=shi.pid

select sheng.id as sid,sheng.title as stitle,
shi.id as shiid,shi.title as shititle
from areas as sheng
inner join areas as shi on sheng.id=shi.pid
limit 0,100;

select sheng.id as sid,sheng.title as stitle,
shi.id as shiid,shi.title as shititle
from areas as sheng
inner join areas as shi on sheng.id=shi.pid
where sheng.pid is not null
limit 0,20

mysql> select sheng.id as sid,sheng.title as stitle,
    -> shi.id as shiid,shi.title as shititle
    -> from areas as sheng
    -> inner join areas as shi on sheng.id=shi.pid
    -> where sheng.pid is  null and sheng.title='山西省'

• 查询市的名称为“广州市”的所有区县

select dis.*,dis2.* from areas as dis
inner join areas as city on city.id=dis.pid
left join areas as dis2 on dis.id=dis2.pid
where city.atitle='广州市';

3.视图  (一个复杂的select语句)（对select语句封装）

• 对于复杂的查询，在多次使用后，维护是一件非常麻烦的事情
• 解决：定义视图
• 视图本质就是对查询的一个封装
• 定义视图

mysql> select * from scores
    -> inner join students on scores.stuid=students.id
    -> inner join subjects on scores.subid=subjects.id;

+----+-------+-------+-------+----+-----------+--------+---------------------+----------+----+---------+----------+
| id | score | stuid | subid | id | name      | gender | birthday            | isDelete | id | title   | isDelete |
+----+-------+-------+-------+----+-----------+--------+---------------------+----------+----+---------+----------+
|  1 | 100.0 |     1 |     1 |  1 | 腾旭      |       | 1999-09-09 00:00:00 |          |  1 | python  |          |
|  3 | 100.0 |     3 |     2 |  3 | 网易      |       | NULL                |          |  2 | linux   |         |
|  4 | 100.0 |     4 |     5 |  4 | 小米      |       | NULL                |          |  5 | mysqlDB |         |
|  5 |  94.0 |     3 |     5 |  3 | 网易      |       | NULL                |          |  5 | mysqlDB |         |
|  6 |  94.0 |     7 |     5 |  7 | QQ        |       | NULL                |          |  5 | mysqlDB |         |
|  7 |  92.0 |     7 |     5 |  7 | QQ        |       | NULL                |          |  5 | mysqlDB |         |
|  8 |  92.0 |     8 |     5 |  8 | 腾讯云    |       | NULL                |          |  5 | mysqlDB |         |
|  9 |  72.0 |     8 |     5 |  8 | 腾讯云    |       | NULL                |          |  5 | mysqlDB |         |
+----+-------+-------+-------+----+-----------+--------+---------------------+----------+----+---------+----------+

• 构建视图

mysql> create view v_stu_sub_sco as 
    -> select * from scores 
    -> inner join students on scores.stuid=students.id
    -> inner join subjects on scores.subid=subjects.id;
ERROR 1060 (42S21): Duplicate column name 'id'

#  id  重名

mysql>  create view v_stu_sub_sco as  
    ->  select students.*,scores.score,subjects.title from scores  
    ->  inner join students on scores.stuid=students.id 
    ->  inner join subjects on scores.subid=subjects.id;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_py31 |
+----------------+
| areas          |
| scores         |
| stu            |
| students       |
| subjects       |
| v_stu_sub_sco  |
+----------------+

• 视图的用途就是查询

mysql> select * from v_stu_sub_sco;

mysql>  create view v_1 as  
    ->  select students.*,scores.score,subjects.title from scores  
    ->  inner join students on scores.stuid=students.id 
    ->  inner join subjects on scores.subid=subjects.id
    ->  where students.isDelete=0 and subjects.isDelete=0;