自关联和视图

自关联

ｆｌｏａｔ(6,2)
实现结果形式　　0000.00
汉字/3
字符

• 建表

mysql> insert into area1(id,name,pid) values(3,'沈阳市',1),(4,'武汉市',2),(5,'新民县',3),(6,'襄阳县',4);
Query OK, 4 rows affected (0.12 sec)
Records: 4  Duplicates: 0  Warnings: 0

• 查询

mysql> select * from area1 as dis inner join area1 as city on city.pid=dis.id inner join area1 as city1 on city.id=city1.pid;
+----+-----------+------+----+-----------+------+----+-----------+------+
| id | name      | pid  | id | name      | pid  | id | name      | pid  |
+----+-----------+------+----+-----------+------+----+-----------+------+
|  1 | 辽宁省    | NULL |  3 | 沈阳市    |    1 |  5 | 新民县    |    3 |
|  2 | 湖北省    | NULL |  4 | 武汉市    |    2 |  6 | 襄阳县    |    4 |
+----+-----------+------+----+-----------+------+----+-----------+------+
2 rows in set (0.00 sec)

mysql> select city1.name,city.name,dis.name from area1 as dis inner join area1 as city on city.pid=dis.id inne
+-----------+-----------+-----------+
| name      | name      | name      |
+-----------+-----------+-----------+
| 新民县    | 沈阳市    | 辽宁省    |
| 襄阳县    | 武汉市    | 湖北省    |
+-----------+-----------+-----------+
2 rows in set (0.00 sec)

视图

１．从原始表导出的表
２．他是虚表

增加数据，不影响视图。

创建视图。

一对一　在视图下可改数据，并且实表中数据也改。
一对多　不能改　比如计数例子。
不能改的项目：主键和外键相关的。多列运算如ｓｕｍ（）不能改数据。

假如要在虚表中插入数据，要保证虚表里面的列必须含有实表里没有默认值的列（或者ＮＯＴ　ｎｕｌｌ）（有默认值的可以不用插，默认插入）

视图　ｓｅｌｅｃｔ。实表ｗｈｅｒｅ和视图ｗｈｅｒｅ一起组合返回。
临时表（实在的表，相当于实表），所以只返回实表的排序，一条条返回，先执行实体的，后执行临时白表外面的ｗｈｅｒｅ。结果没多大不同。

 create view v_area1_info as select city1.id as city1_id,city1.name as c1_name from area1 as dis inne                                                    rea1 as city1 on city.id=city1.pid;
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+-------------------+
| Tables_in_evenhom |
+-------------------+
| area              |
| area1             |
| areas             |
| customer          |
| customer1         |
| orde              |
| order1            |
| orderdetail       |
| orderdetail1      |
| product           |
| product1          |
| v_allcount        |
| v_area1_info      |
| v_pid_area1       |
+-------------------+
14 rows in set (0.00 sec)

mysql> select * from v_area1_info;
+----------+-----------+
| city1_id | c1_name   |
+----------+-----------+
|        5 | 新民县    |
|        6 | 襄阳县    |
+----------+-----------+
2 rows in set (0.00 sec)

mysql> update v_area1_info set c1_name='北京县' where city1_id=5;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from v_area1_info;
+----------+-----------+
| city1_id | c1_name   |
+----------+-----------+
|        5 | 北京县    |
|        6 | 襄阳县    |
+----------+-----------+
2 rows in set (0.00 sec)

例子

mysql> select * from studentinfo where age >23 and gender =1 order by age desc limit 1;
+------------+--------------+-----+--------+----------+
| student_id | student_name | age | gender | phonenum |
+------------+--------------+-----+--------+----------+
|     180413 | 金刚         |  99 |      1 | NULL     |
+------------+--------------+-----+--------+----------+
1 row in set (0.00 sec)

mysql> create view v_stu as select * from studentinfo where age >23 and gender =1 order by age desc limit 1;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from v_stu;
+------------+--------------+-----+--------+----------+
| student_id | student_name | age | gender | phonenum |
+------------+--------------+-----+--------+----------+
|     180413 | 金刚         |  99 |      1 | NULL     |
+------------+--------------+-----+--------+----------+
1 row in set (0.00 sec)