mysql 建立表之间关系 一对一 练习2

 

 

创建db5数据库

create database db5 charset=utf8;

 

use db5;

 

例二：一个管理员唯一对应一个用户
    用户表：
    id user  password
     1  egon    xxxx
     2  alex    yyyy

创建用户表

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

 

插入用户信息

mysql> insert into user(user,password) values('alex','123'),('mike','1234'),('jack','123');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----+------+----------+
| id | user | password |
+----+------+----------+
|  1 | alex | 123      |
|  2 | mike | 1234     |
|  3 | jack | 123      |
+----+------+----------+
3 rows in set (0.00 sec)

 

    管理员表：
       fk+unique
    id user_id password
     1     1      xxxxx
     2     2      yyyyy


创建管理员表

mysql> create table Administrator(
id int primary key auto_increment,
user_id int unique,
password varchar(50) not null,
foreign key(user_id) references user(id)
on delete cascade 
on update cascade);
Query OK, 0 rows affected (0.20 sec)

 

插入记录

mysql> insert into Administrator(user_id,password) values(1,'123'),(2,'1234'),(3,'123');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

 

 

mysql> select * from Administrator;
+----+---------+----------+
| id | user_id | password |
+----+---------+----------+
|  1 |       1 | 123      |
|  2 |       2 | 1234     |
|  3 |       3 | 123      |
+----+---------+----------+
3 rows in set (0.00 sec)