create table cats(
id int not null auto_increment,
pid int not null default '0',
name varchar(60) not null default '',
desn text not null default '',
primary key(id),
index name(name, pid)
)engine=InnoDB default character set=utf8;
create table products(
id int not null auto_increment,
cid int not null default '0',
name varchar(60) not null default '',
price double not null default '0.00',
num int not null default '0',
desn text,
ptime int not null default '0',
primary key(id),
key pname(name, price)
)engine=InnoDB default character set=utf8;
mysql> select a.id aid, b.id bid, a.name aname, b.name bname from products a, products b;
mysql> select a.id aid, b.id bid, a.name aname, b.name bname from products a, products b where a.id=b.cid;
mysql> select a.id aid, b.id bid, a.name aname, b.name bname from products a, products b where a.id=b.cid order by aid;
mysql> select a.id aid, b.id bid, a.name aname, b.name bname from products a, products b where a.id=b.cid order by aid desc;
mysql> select a.id aid, b.id bid, a.name aname, b.name bname from products a, products b where a.id=b.cid order by aid asc
mysql> select a.id aid, b.id bid, a.name aname, b.name bname from products a, products b where a.id=b.cid order by aid asc limit 5;
mysql> select * from products group by cid having cid between 2 and 9;
mysql> select * from products group by cid having cid between 2 and 9 order by name limit 4;