用户表设计-多账户绑定

-- 创建用户表
CREATE TABLE user (
  user_id INT PRIMARY KEY AUTO_INCREMENT,
  user_name VARCHAR(50) NOT NULL,
  user_password VARCHAR(50) NOT NULL
);

-- 创建账号表
CREATE TABLE account (
  account_id INT PRIMARY KEY AUTO_INCREMENT,
  account_type VARCHAR(50) NOT NULL,
  account_value VARCHAR(50) NOT NULL,
  user_id INT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES user (user_id)
);

-- 插入一些测试数据
INSERT INTO user (user_name, user_password) VALUES ('张三', '123456');
INSERT INTO user (user_name, user_password) VALUES ('李四', '654321');

INSERT INTO account (account_type, account_value, user_id) VALUES ('微信', 'wx123', 1);
INSERT INTO account (account_type, account_value, user_id) VALUES ('支付宝', 'zfb456', 1);
INSERT INTO account (account_type, account_value, user_id) VALUES ('手机', '13800000000', 1);
INSERT INTO account (account_type, account_value, user_id) VALUES ('邮箱', 'zhangsan@163.com', 1);

INSERT INTO account (account_type, account_value, user_id) VALUES ('微信', 'wx789', 2);
INSERT INTO account (account_type, account_value, user_id) VALUES ('支付宝', 'zfb101112', 2);
INSERT INTO account (account_type, account_value, user_id) VALUES ('手机', '13911111111', 2);
INSERT INTO account (account_type, account_value, user_id) VALUES ('邮箱', 'lisi@gmail.com', 2);

-- 查询用户和账号信息
SELECT u.*, a.account_type, a.account_value
FROM user u
JOIN account a ON u.user_id = a.user_id;