mysql语句编写(只更新product 表的字段mail_address为空的列)
有上面两张表,编写sql
1,要将表product 表的字段mail_address为空的列的mail_address值更新为表addressbook表中字段为的值,关联字段为表 addressbook的字段tel_no和表product 的字段tel_no 为一对多的关系。 (只更新product 表的字段mail_address为空的列)
附建表sql:
-- Table structure for addressbook
DROP TABLE IF EXISTS addressbook
;
CREATE TABLE addressbook
(regist_no
int(11) NOT NULL COMMENT '注册编号',name
varchar(128) NOT NULL COMMENT '姓名',address
varchar(256) NOT NULL COMMENT '住址',tel_no
char(10) DEFAULT NULL COMMENT '电话号码',mail_address
char(20) DEFAULT NULL COMMENT '邮箱地址',postal_code
char(8) NOT NULL,
PRIMARY KEY (regist_no
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Records of addressbook
INSERT INTO addressbook
VALUES ('1', 'sewewe', 'werwrewr', '2', '陕西', '123');
INSERT INTO addressbook
VALUES ('2', 'etret', '334353', '3', '北京', '546456');
INSERT INTO addressbook
VALUES ('3', '564654', '456456', '5', '南京', '43534');
DROP TABLE IF EXISTS product
;
CREATE TABLE product
(product_id
char(4) NOT NULL,product_name
varchar(100) DEFAULT NULL,product_type
varchar(32) DEFAULT NULL,sale_price
int(11) DEFAULT NULL,purchase_price
int(11) DEFAULT NULL,regist_date
date DEFAULT NULL,tel_no
char(10) DEFAULT NULL,mail_address
char(20) DEFAULT NULL,
PRIMARY KEY (product_id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Records of product
INSERT INTO product
VALUES ('1', '你爸爸', '1', '11', '1', '2021-07-14', '2', '陕西');
INSERT INTO product
VALUES ('345', '你奶奶', '', null, null, null, '3', null);
INSERT INTO product
VALUES ('3456', 'fgdf', '1', '2', '3', '2021-09-24', '2', null);
INSERT INTO product
VALUES ('3459', 'ninhh', '', null, null, null, '5', null);
INSERT INTO product
VALUES ('4', '4345', '3', '4', '4', '2021-09-24', '5', null);
INSERT INTO product
VALUES ('6', '45', '5', '4', '3', '2021-09-24', '5', null);
update product set mail_address=(select b.mail_address from addressbook b where b.tel_no=tel_no limit 1) where mail_address is null
你建表有问题呀,关联字段为表 addressbook的字段tel_no和表product 的字段tel_no 为一对多的关系,既然这两个表关联了你还要在商品表里面写地址干什么?难道通过电话字段进行表连接就不能查出来地址吗?我建议你应该这样写,不然的话你的表关系真的太紧了,你应该进行解耦合
用户表 : 用户id,姓名,年龄
地址表:地址id,收货人,收货人地址,电话,用户id(外键)
商品表:商品id,商品名称,单价,库存
订单表:订单id,时间,状态,地址id,商品id,总价格
订单详情:订单id,用户id