练习：账号信息表，用户组，主机表，主机组

#用户表

mysql> create table user(id int not null unique auto_increment,username varchar(50) not null,password varchar(50) not null,primary key(username,password));Query OK, 0 rows affected (0.12 sec)

插入用户信息

mysql> insert into user(username,password) values(‘root‘,‘123‘),(‘alex‘,‘1234‘),(‘mike‘,‘1234‘);Query OK, 3 rows affected (0.01 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> select * from user;+----+----------+----------+| id | username | password |+----+----------+----------+| 1 | root | 123 || 2 | alex | 1234 || 3 | mike | 1234 |+----+----------+----------+3 rows in set (0.09 sec)

#用户组表

mysql> create table usergroup(id int primary key auto_increment,groupname varchar(20) not null unique);Query OK, 0 rows affected (0.21 sec)mysql> insert into usergroup(groupname) values(‘IT‘),(‘sale‘),(‘Finance‘),(‘boss‘);Query OK, 4 rows affected (0.10 sec)Records: 4 Duplicates: 0 Warnings: 0mysql> select * from usergroup;+----+-----------+| id | groupname |+----+-----------+| 4 | boss || 3 | Finance || 1 | IT || 2 | sale |+----+-----------+4 rows in set (0.00 sec)

#主机表

mysql> create table host(id int primary key auto_increment,ip char(16) not null unique default ‘127.0.0.1‘);Query OK, 0 rows affected (0.13 sec

插入ip记录

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> create table business(id int primary key auto_increment,business varchar(20) not null unique);Query OK, 0 rows affected (0.20 sec)mysql> insert into business(business) values -> (‘轻松贷‘), -> (‘随便花‘), -> (‘大富翁‘), -> (‘穷一生‘) -> ;Query OK, 4 rows affected (0.04 sec)Records: 4 Duplicates: 0 Warnings: 0

‘

多对多关系练习

’

#建关系：user与usergroup


创建一张user2usergroup表

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 cascadeon update cascade,foreign key(group_id) references usergroup(id)on delete cascadeon update cascade);

插入记录

mysql> insert into user2usergroup(user_id,group_id) values(1,1),(1,2),(1,3),(1,4),(2,3),(2,4),(3,4);Query OK, 7 rows affected (0.09 sec)Records: 7 Duplicates: 0 Warnings: 0mysql> 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 cascadeon update cascade,foreign key(business_id) references business(id)on delete cascadeon update cascade);

insert into host2business(host_id,business_id) values(1,1),(1,2),(1,3),(2,2),(2,3),(3,4);

#建关系：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 cascadeon update cascade,foreign key(host_id) references host(id)on delete cascadeon update cascade);

insert into user2host(user_id,host_id) values(1,1),(1,2),(1,3),(1,4),(2,2),(2,3),(2,4),(2,5),(3,10),(3,11),(3,12);

ysql> select * from user2host;+----+---------+---------+| id | user_id | host_id |+----+---------+---------+| 1 | 1 | 1 || 2 | 1 | 2 || 3 | 1 | 3 || 4 | 1 | 4 || 5 | 2 | 2 || 6 | 2 | 3 || 7 | 2 | 4 || 8 | 2 | 5 || 9 | 3 | 10 || 10 | 3 | 11 || 11 | 3 | 12 |+----+---------+---------+11 rows in set (0.00 sec)