mysql表复制与子查询

mysql表复制与子查询

1、复制表

create table g2 like goods;

g2和goods表头都一样，但无内容

insert into g2 select *from goods order by cat_id asc,shop_price desc;

将goods表按cat_id升序、shop_price降序导入到g2表中（无效，还是goods表中原来的顺序）

truncate g2;

清空g2表

2、子查询
where型、from型、exists型
where型：把内层的查询结果作为外层查询的比较条件
例：查找每个栏目下最贵的商品

select goods_id, cat_id, goods_name, shop_price from goods where shop_price in (select max(shop_price) from goods group by cat_id);

from型：把内层的查询结果当成临时表(必须有别名，即加 as 临时表名)，共外层sql再次查询
例：查找每个栏目下最贵的商品

select * from (select goods_id, cat_id, goods_name, shop_price from goods order by cat_id, goods_id desc) as tmp group by cat_id;

exist型：把外层的查询结果拿到内层，看内层查询是否成立
例：查有商品的栏目（典型）

select select cat_id, cat_name, from category where exists (select * from goods where goods.cat_id=category.cat_id);

category.cat_id为外层查询结果，它带入内层select；