MySQL多对多关系表之堡垒机制作

1. 创建数据库

mysql> create database ssh;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class              |
| mysql              |
| performance_schema |
| school             |
| ssh                |
| student            |
| sys                |
| tianyun            |
+--------------------+
9 rows in set (0.00 sec)

2. 创建表格

mysql> use ssh;
Database changed
mysql> create table user(
    -> id int auto_increment primary key,
    -> name varchar(10),
    -> password int
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> create table server(
    -> id int auto_increment primary key,
    -> name varchar(10),
    -> ip varchar(15)
    -> 
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table server2user( id int auto_increment primary key, user_id int, server_id int );
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+---------------+
| Tables_in_ssh |
+---------------+
| server        |
| server2user   |
| user          |
+---------------+
3 rows in set (0.00 sec)

3. 给表添加相应信息

mysql> insert into user (name,password) values("wsl1","123"),("wsl2","123"),("wsl3","123"),("wsl4","123");
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----+------+----------+
| id | name | password |
+----+------+----------+
|  1 | wsl1 |      123 |
|  2 | wsl2 |      123 |
|  3 | wsl3 |      123 |
|  4 | wsl4 |      123 |
+----+------+----------+
4 rows in set (0.00 sec)

mysql> insert into server(name,ip) values("server1","192.168.123.121"),("server2","192.168.157.123"),("server3","192.123.23.43"),("server4","183.34.34.23");
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from server;
+----+---------+-----------------+
| id | name    | ip              |
+----+---------+-----------------+
|  1 | server1 | 192.168.123.121 |
|  2 | server2 | 192.168.157.123 |
|  3 | server3 | 192.123.23.43   |
|  4 | server4 | 183.34.34.23    |
+----+---------+-----------------+
4 rows in set (0.00 sec)

mysql> insert into server2user (user_id,server_id) values(1,1),(1,2),(1,3),(1,4),(2,1),(2,2),(2,3),(2,4),(3,1),(3,2),(3,3),(3,4),(4,1),(4,2),(4,3),(4,4);
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> select * from server2user;
+----+---------+-----------+
| id | user_id | server_id |
+----+---------+-----------+
|  1 |       1 |         1 |
|  2 |       1 |         2 |
|  3 |       1 |         3 |
|  4 |       1 |         4 |
|  5 |       2 |         1 |
|  6 |       2 |         2 |
|  7 |       2 |         3 |
|  8 |       2 |         4 |
|  9 |       3 |         1 |
| 10 |       3 |         2 |
| 11 |       3 |         3 |
| 12 |       3 |         4 |
| 13 |       4 |         1 |
| 14 |       4 |         2 |
| 15 |       4 |         3 |
| 16 |       4 |         4 |
+----+---------+-----------+
16 rows in set (0.00 sec)

4. 添加外键约束属性

mysql> alter table server2user add constraint baolei foreign key server2user(user_id) references user(id);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table server2user add constraint baolei2 foreign key server2user(server_id) references server(id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

5. 查询对应关系

mysql> mysql> select * from user a,server b,server2user c where c.user_id=a.id and c.server_id=b.id;
+----+------+----------+---------+----+---------+-----------------+----+---------+-----------+
| id | name | password | user_id | id | name    | ip              | id | user_id | server_id |
+----+------+----------+---------+----+---------+-----------------+----+---------+-----------+
|  1 | wsl1 |      123 |    NULL |  1 | server1 | 192.168.123.121 |  1 |       1 |         1 |
|  2 | wsl2 |      123 |    NULL |  1 | server1 | 192.168.123.121 |  5 |       2 |         1 |
|  3 | wsl3 |      123 |    NULL |  1 | server1 | 192.168.123.121 |  9 |       3 |         1 |
|  4 | wsl4 |      123 |    NULL |  1 | server1 | 192.168.123.121 | 13 |       4 |         1 |
|  1 | wsl1 |      123 |    NULL |  2 | server2 | 192.168.157.123 |  2 |       1 |         2 |
|  2 | wsl2 |      123 |    NULL |  2 | server2 | 192.168.157.123 |  6 |       2 |         2 |
|  3 | wsl3 |      123 |    NULL |  2 | server2 | 192.168.157.123 | 10 |       3 |         2 |
|  4 | wsl4 |      123 |    NULL |  2 | server2 | 192.168.157.123 | 14 |       4 |         2 |
|  1 | wsl1 |      123 |    NULL |  3 | server3 | 192.123.23.43   |  3 |       1 |         3 |
|  2 | wsl2 |      123 |    NULL |  3 | server3 | 192.123.23.43   |  7 |       2 |         3 |
|  3 | wsl3 |      123 |    NULL |  3 | server3 | 192.123.23.43   | 11 |       3 |         3 |
|  4 | wsl4 |      123 |    NULL |  3 | server3 | 192.123.23.43   | 15 |       4 |         3 |
|  1 | wsl1 |      123 |    NULL |  4 | server4 | 183.34.34.23    |  4 |       1 |         4 |
|  2 | wsl2 |      123 |    NULL |  4 | server4 | 183.34.34.23    |  8 |       2 |         4 |
|  3 | wsl3 |      123 |    NULL |  4 | server4 | 183.34.34.23    | 12 |       3 |         4 |
|  4 | wsl4 |      123 |    NULL |  4 | server4 | 183.34.34.23    | 16 |       4 |         4 |
+----+------+----------+---------+----+---------+-----------------+----+---------+-----------+
16 rows in set (0.00 sec)