mysql中创建表实例全析及查询基本操作

 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;