MySQL一对一的操作

一、一对一的操作

在实际的项目开发中,一对一的操作常见于表的扩展(用户表及用户扩展表),采用外键约束

• 1、创建一个用户表

  create table user(
    id int primary key auto_increment comment "用户id",
    name varchar(100) not null unique comment "用户名",
    mobile varchar(100) not null unique comment "手机号码",
    password varchar(100) not null comment "密码",
    create_at datetime default now() comment "创建时间"
  );
  

• 2、创建一个用户扩展表(外键也是唯一的)

  create table userExt(
    id int primary key auto_increment comment "用户id",
    gender varchar (100) comment "性别",
    city varchar (100) comment "城市",
    marriage enum("已婚", "未婚") default "未婚",
    create_at timestamp default current_timestamp comment "创建时间",
    user_id int unique comment "外键",
    foreign key(user_id) references user(id) on delete cascade
  );
  

• 3、插入数据

  insert into user (name, mobile, password) values ("张三", "110", "123456"), ("李四", "120", "123456"), ("王五", "119", "123456");
  insert into userExt(gender, city, marriage, user_id) values ("男", "广东", "已婚", 1), ("女", "广西", "未婚", 2), ("女", "湖南", "未婚", 3);
  

• 4、查询数据

  mysql> select * from user as a, userExt as b where a.id = b.user_id\G;
  *************************** 1. row ***************************
        id: 1
      name: 张三
    mobile: 110
  password: 123456
  create_at: 2018-06-29 17:34:41
        id: 1
    gender: 男
      city: 广东
  marriage: 已婚
  create_at: 2018-06-29 17:34:51
    user_id: 1
  *************************** 2. row ***************************
        id: 2
      name: 李四
    mobile: 120
  password: 123456
  create_at: 2018-06-29 17:34:41
        id: 2
    gender: 女
      city: 广西
  marriage: 未婚
  create_at: 2018-06-29 17:34:51
    user_id: 2
  *************************** 3. row ***************************
        id: 3
      name: 王五
    mobile: 119
  password: 123456
  create_at: 2018-06-29 17:34:41
        id: 3
    gender: 女
      city: 湖南
  marriage: 未婚
  create_at: 2018-06-29 17:34:51
    user_id: 3
  3 rows in set (0.00 sec)
  
  ERROR: 
  No query specified
  
  mysql>
  

• 5、修改数据跟单张表操作是一样的