示例创建表

DROP TABLE IF EXISTS `customer`;

create table customer(

customer_id  int(10) auto_increment,

customer_name  VARCHAR(50) not null DEFAULT '',

customer_phone  VARCHAR(20) not null DEFAULT '',

customer_address  varchar(50) not null DEFAULT '',

customer_time datetime not null,

PRIMARY KEY(customer_id)

)ENGINE=INNODB auto_increment=1 DEFAULT CHARSET=utf8;

INSERT INTO customer(customer_name,customer_phone,customer_address,customer_time) VALUES('耐克','021-22798156','南京路54号',NOW());

INSERT INTO customer(customer_name,customer_phone,customer_address,customer_time) VALUES('阿迪达斯','021-22798156','南京路54号',NOW());

INSERT INTO customer(customer_name,customer_phone,customer_address,customer_time) VALUES('特步','021-22798156','南京路54号',NOW());

INSERT INTO customer(customer_name,customer_phone,customer_address,customer_time) VALUES('安踏','021-22798156','南京路54号',NOW());

INSERT INTO customer(customer_name,customer_phone,customer_address,customer_time) VALUES('耐克','021-22798156','南京路54号',NOW());

DROP TABLE IF EXISTS `customer_rate`;

create TABLE customer_rate(

customer_id int(10) not null,

rate float(5,2)not null

)ENGINE=INNODB auto_increment = 1 DEFAULT CHARSET=utf8;

ALTER TABLE customer_rate MODIFY customer_id int(10) auto_increment PRIMARY KEY;

INSERT INTO customer_rate(rate) VALUES(0.8);

INSERT INTO customer_rate(rate) VALUES(0.78);

select c.customer_name,20*(ifnull(r.rate,1)) final_price 

from customer c

left outer join customer_rate r

on c.customer_id = r.customer_id;

--左联，如果没有则不打折