The implementation method of MySQL federated query view

MySQL Tutorial federated query View implementation method

Mysql>
Mysql> CREATE TABLE Employee (
-> ID int,
-> first_name varchar (15),
-> last_name varchar (15),
-> start_date Date,
-> end_date Date,
-> Salary Float (8,2),
-> City varchar (10),
-> description varchar (15)
->);
Query OK, 0 rows affected (0.02 sec)

Mysql>
Mysql> CREATE TABLE Job (
-> ID int,
-> title varchar (20)
->);
Query OK, 0 rows affected (0.06 sec)

Mysql>
Mysql> INSERT into employee (Id,first_name, last_name, start_date, end_date, salary, city, description)
-> values (1, ' Jason ', ' Martin ', ' 19960725 ', ' 20060725 ', 1234.56, ' Toronto ', ' Programmer ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> INSERT into employee (Id,first_name, last_name, start_date, end_date, salary, city, description)
-> values (2, ' Alison ', ' Mathews ', ' 19760321 ', ' 19860221 ', 6661.78, ' Vancouver ', ' tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> INSERT into employee (Id,first_name, last_name, start_date, end_date, salary, city, description)
-> values (3, ' James ', ' Smith ', ' 19781212 ', ' 19900315 ', 6544.78, ' Vancouver ', ' tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> INSERT into employee (Id,first_name, last_name, start_date, end_date, salary, city, description)
-> VALUES (4, ' Celia ', ' rice ', ' 19821024 ', ' 19990421 ', 2344.78, ' Vancouver ', ' manager ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> INSERT into employee (Id,first_name, last_name, start_date, end_date, salary, city, description)
-> values (5, ' Robert ', ' Black ', ' 19840115 ', ' 19980808 ', 2334.78, ' Vancouver ', ' tester ');
Query OK, 1 row affected (0.01 sec)

Mysql>
Mysql> INSERT into employee (Id,first_name, last_name, start_date, end_date, salary, city, description)
-> VALUES (6, ' Linda ', ' green ', ' 19870730 ', ' 19960104 ', 4322.78, ' New York ', ' tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> INSERT into employee (Id,first_name, last_name, start_date, end_date, salary, city, description)
-> VALUES (7, ' David ', ' Larry ', ' 19901231 ', ' 19980212 ', 7897.78, ' New York ', ' manager ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> INSERT into employee (Id,first_name, last_name, start_date, end_date, salary, city, description)
-> VALUES (8, ' James ', ' Cat ', ' 19960917 ', ' 20020415 ', 1232.78, ' Vancouver ', ' tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
mysql> INSERT into Job (ID, title) VALUES (1, ' tester ');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT into Job (ID, title) VALUES (2, ' accountant ');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT into Job (ID, title) VALUES (3, ' Developer ');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT into Job (ID, title) VALUES (4, ' coder ');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT into Job (ID, title) VALUES (5, ' director ');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT into Job (ID, title) VALUES (6, ' mediator ');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT into Job (ID, title) VALUES (7, ' proffessor ');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT into Job (ID, title) VALUES (8, ' Programmer ');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT into Job (ID, title) VALUES (9, ' Developer ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> select * from Job;
+------+------------+
| ID | Title |
+------+------------+
| 1 | Tester |
| 2 | Accountant |
| 3 | Developer |
| 4 | Coder |
| 5 | Director |
| 6 | Mediator |
| 7 | Proffessor |
| 8 | Programmer |
| 9 | Developer |
+------+------------+
9 Rows in Set (0.00 sec)

Mysql> SELECT * from employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| ID | first_name | last_name | start_date | end_date | Salary | City | Description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in Set (0.00 sec)

Mysql>
Mysql>
mysql> Create algorithm = temptable View MyView
-> (ID, first_name, title) as
-> Select O.id, O.first_name, C.title
-> from job C, employee O
-> where o.id = c.id;
Query OK, 0 rows Affected (0.00 sec)

Mysql>
Mysql> select * from MyView;
+------+------------+------------+
| id   | first_ name | title      |
+------+------------+------------+
|    1 | jason      | tester      |
|    2 | alison     | accountant |
|    3 | james      | developer  |
|    4 | celia      | coder      |
|    5 | robert     | director   |
|    6 | linda      | mediator   |
|    7 | david      | proffessor |
|    8 | james      | programmer |
+------+------------+------------+
8 rows in Set (0.00 sec)

Mysql>
mysql> drop View MyView;
Query OK, 0 rows Affected (0.00 sec)

Mysql>
Mysql>
Mysql>
Mysql>
mysql> drop table job;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table employee;
Query OK, 0 rows Affected (0.00 sec)

Mysql>