mysql 的主从关系_mysql 店铺–商品的关键主从关系查询实例

CREATE TABLE `tp_goods` (

`goods_id` int(11) NOT NULL AUTO_INCREMENT,

`store_id` int(11) DEFAULT NULL,

`goods_name` varchar(45) DEFAULT NULL,

`keywords` varchar(200) DEFAULT NULL,

PRIMARY KEY (`goods_id`)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

CREATE TABLE `tp_store` (

`store_id` int(11) NOT NULL AUTO_INCREMENT,

`store_name` varchar(45) DEFAULT NULL,

`longitude` decimal(10,7) DEFAULT NULL,

`latitude` decimal(10,7) DEFAULT NULL,

`score` decimal(2,1) DEFAULT NULL,

PRIMARY KEY (`store_id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

insert into tp_store(store_name,latitude,longitude,score) values(‘程四果汤店eee2’,118.56189,16.48525,4.6);

insert into tp_goods(goods_name,store_id) values(‘ass33e3四果汤ssss’,2);

select s.*,g.* from tp_goods g join tp_store s on s.store_id = g.store_id

where s.store_name like ‘%四果汤%’ or g.goods_name like ‘%四果汤%’

order by s.store_id;

select s.*,g.* from tp_goods g join tp_store s on s.store_id = g.store_id

where s.store_name like ‘%四果汤%’ or g.goods_name like ‘%四果汤%’

order by s.score ;

select s.*,g.* from tp_goods g join tp_store s on s.store_id = g.store_id

where s.store_name like ‘%四果汤%’ or g.goods_name like ‘%四果汤%’

order by (longitude-16 + latitude -118) desc ;

select s.* from tp_store s

where s.store_name like ‘%四果汤%’ or exists ( select g.store_id from tp_goods g where g.goods_name like ‘%四果汤%’)

order by (longitude-16 + latitude -118) desc ;