表操作练习题：
练习：用户表，用户组表，主机表，业务线表
关联：用户与用户组 主机与业务线 用户与主机
# 用户表
create table user(
id int not null unique auto_increment,
username varchar(20) not null,
password varchar(50) not null,
primary key(username,password)
);
insert into user(username,password) values
(‘root‘,‘123‘),
(‘egon‘,‘456‘),
(‘alex‘,‘alex3714‘)
;
mysql> desc user;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | UNI | NULL | auto_increment |
| username | varchar(20) | NO | PRI | NULL | |
| password | varchar(50) | NO | PRI | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | root | 123 |
| 2 | egon | 456 |
| 3 | alex | alex3714 |
+----+----------+----------+
3 rows in set (0.00 sec)
# 用户组表
create table usergroup(
id int primary key auto_increment,
groupname varchar(20) not null unique
);
insert into usergroup(groupname) values
(‘IT‘),
(‘Sale‘),
(‘Finance‘),
(‘Boss‘)
;
mysql> desc usergroup;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| groupname | varchar(20) | NO | UNI | NULL | |
+-----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> select * from usergroup;
+----+-----------+
| id | groupname |
+----+-----------+
| 4 | Boss |
| 3 | Finance |
| 1 | IT |
| 2 | Sale |
+----+-----------+
4 rows in set (0.01 sec)
# 主机表
create table host(
id int primary key auto_increment,
ip char(15) not null unique default ‘127.0.0.1‘
);
insert into host(ip) values
(‘172.16.45.2‘),
(‘172.16.31.10‘),
(‘172.16.45.3‘),
(‘172.16.31.11‘),
(‘172.10.45.3‘),
(‘172.10.45.4‘),
(‘172.10.45.5‘),
(‘192.168.1.20‘),
(‘192.168.1.21‘),
(‘192.168.1.22‘),
(‘192.168.2.23‘),
(‘192.168.2.223‘),
(‘192.168.2.24‘),
(‘192.168.3.22‘),
(‘192.168.3.23‘),
(‘192.168.3.24‘)
;
mysql> desc host;
+-------+----------+------+-----+-----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+-----------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| ip | char(15) | NO | UNI | 127.0.0.1 | |
+-------+----------+------+-----+-----------+----------------+
2 rows in set (0.00 sec)
mysql> select * from host;
+----+---------------+
| id | ip |
+----+---------------+
| 5 | 172.10.45.3 |
| 6 | 172.10.45.4 |
| 7 | 172.10.45.5 |
| 2 | 172.16.31.10 |
| 4 | 172.16.31.11 |
| 1 | 172.16.45.2 |
| 3 | 172.16.45.3 |
| 8 | 192.168.1.20 |
| 9 | 192.168.1.21 |
| 10 | 192.168.1.22 |
| 12 | 192.168.2.223 |
| 11 | 192.168.2.23 |
| 13 | 192.168.2.24 |
| 14 | 192.168.3.22 |
| 15 | 192.168.3.23 |
| 16 | 192.168.3.24 |
+----+---------------+
16 rows in set (0.01 sec)
# 业务线表
create table business(
id int primary key auto_increment,
business varchar(20) not null unique
);
insert into business(business) values
(‘轻松贷‘),
(‘随便花‘),
(‘大富翁‘),
(‘穷一生‘)
;
mysql> desc business;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| business | varchar(20) | NO | UNI | NULL | |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> select * from business;
+----+-----------+
| id | business |
+----+-----------+
| 3 | 大富翁 |
| 4 | 穷一生 |
| 1 | 轻松贷 |
| 2 | 随便花 |
+----+-----------+
4 rows in set (0.00 sec)
# 建关系 user usergroup
create table user2usergroup(
id int not null unique auto_increment ,
user_id int not null,
group_id int not null,
primary key(user_id,group_id),
foreign key(user_id) references user(id)
on delete cascade
on update cascade,
foreign key(group_id) references usergroup(id)
on delete cascade
on update cascade
);
insert into user2usergroup(user_id,group_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(2,3),
(2,4),
(3,4)
;
mysql> desc user2usergroup;
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | UNI | NULL | auto_increment |
| user_id | int(11) | NO | PRI | NULL | |
| group_id | int(11) | NO | PRI | NULL | |
+----------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> select * from user2usergroup;
+----+---------+----------+
| id | user_id | group_id |
+----+---------+----------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
| 5 | 2 | 3 |
| 6 | 2 | 4 |
| 7 | 3 | 4 |
+----+---------+----------+
7 rows in set (0.00 sec)
# 建关系 host business
create table host2business(
id int not null unique auto_increment,
host_id int not null,
business_id int not null,
primary key(host_id,business_id),
foreign key(host_id) references host(id)
on delete cascade
on update cascade,
foreign key(business_id) references business(id)
on delete cascade
on update cascade
);
insert into host2business(host_id,business_id) values
(1,1),
(1,2),
(1,3),
(2,2),
(2,3),
(3,4)
;
mysql> desc host2business;
+-------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | UNI | NULL | auto_increment |
| host_id | int(11) | NO | PRI | NULL | |
| business_id | int(11) | NO | PRI | NULL | |
+-------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> select * from host2business;
+----+---------+-------------+
| id | host_id | business_id |
+----+---------+-------------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 2 |
| 5 | 2 | 3 |
| 6 | 3 | 4 |
+----+---------+-------------+
6 rows in set (0.00 sec)
# 建关系 user host
create table user2host(
id int not null unique auto_increment,
user_id int not null,
host_id int not null,
primary key(user_id,host_id),
foreign key(user_id) references user(id)
on delete cascade
on update cascade,
foreign key(host_id) references host(id)
on delete cascade
on update cascade
);
insert into user2host(user_id,host_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(1,7),
(1,8),
(1,9),
(1,10),
(1,11),
(1,12),
(1,13),
(1,14),
(1,15),
(1,16),
(2,2),
(2,3),
(2,4),
(2,5),
(3,10),
(3,11),
(3,12)
;
mysql> desc user2host;
+---------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+----------------+
| id | int(11) | NO | UNI | NULL | auto_increment |
| user_id | int(11) | NO | PRI | NULL | |
| host_id | int(11) | NO | PRI | NULL | |
+---------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> select * from user2host;
+----+---------+---------+
| id | user_id | host_id |
+----+---------+---------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
| 5 | 1 | 5 |
| 6 | 1 | 6 |
| 7 | 1 | 7 |
| 8 | 1 | 8 |
| 9 | 1 | 9 |
| 10 | 1 | 10 |
| 11 | 1 | 11 |
| 12 | 1 | 12 |
| 13 | 1 | 13 |
| 14 | 1 | 14 |
| 15 | 1 | 15 |
| 16 | 1 | 16 |
| 17 | 2 | 2 |
| 18 | 2 | 3 |
| 19 | 2 | 4 |
| 20 | 2 | 5 |
| 21 | 3 | 10 |
| 22 | 3 | 11 |
| 23 | 3 | 12 |
+----+---------+---------+
23 rows in set (0.00 sec)
原文：https://www.cnblogs.com/alice-bj/p/8830569.html