mysql 建立表之间关系 练习 1
2024-08-31 22:04:59
练习：账号信息表，用户组，主机表，主机组
#用户表
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',''),('alex',''),('mike','');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> 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: 0 mysql> 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 cascade
on update cascade,
foreign key(group_id) references usergroup(id)
on delete cascade
on 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: 0 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)
;
#建关系：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),
(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)
最新文章
- SQLite -- 分页查询
- [转]easyui tree 模仿ztree 使用扁平化加载json
- linux线程的实现【转】
- 启用WCF测试客户端的相关技巧
- js事件处理相关-实现一个div的拖拽
- URAL 1244. Gentlemen （DP）
- SQL Server网络地址备份
- C#.Net 如何动态加载与卸载程序集（.dll或者.exe）5-----Assembly.Unload
- SSH端口修改
- 不用图片，纯Css3实现超酷的类似iphone的玻璃气泡效果
- 谈谈 WLST Custom Commands
- Python的包管理
- jquery参考手册
- 一口一口吃掉Hibernate（四）——多对一单向关联映射
- Opecv + Anaconda 读取视频（windows）
- LeetCode（66）： 加一
- 谁说java里面有返回值的方法必须要有返回值，不然会报错？？？？
- Redis setnx命令 分布式缓存
- 【Python爬虫】PyQuery解析库
- 生成器的使用demo