有上面两张表,编写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);