6、多表查询（连接查询），比较常用 #ambiguous
e.g. selectcats.name,products.name from cats,products;
selectc.name cname,c.desn cdesn,p.name pname,p.price,p.desn pdesn,p.numfrom carts c,products as p;#将A表中的记录与B表中的记录依次匹配，得到A*B种结果【笛卡尔乘积】，该结果是没有意义的。
selectc.name cname,c.desn cdesn,p.name pname,p.price,p.desn pdesn,p.numfrom carts c,products as p where c.id=p.cid;
selectc.name cname,c.desn cdesn,p.name pname,p.price,p.desn pdesn,p.numfrom carts c,products as p where c.id=p.cid and c.id=3;
selecta.id aid,a.name aname,b.id bid,b.name bname from cats a,catsb; #将单表分为多表，进行查询
selecta.id aid,a.name aname,b.id bid,b.name bname from cats a,cats b wherea.pid = b.id;
7、嵌套查询子查询
e.g. select* from products where cid in(select id from carts where name regexp’^j’) ;
select* from products where cid in(select id from carts where name like’j%’); #作用相同
8、orderby 字段 [asc正序]desc倒序
e.g. select* from order by name;
select* from order by price; #按价格非递减排序
select* from order by price desc; #非递增排序
select* from where cid > 5 order by price desc; #与where结合使用
9、limitcount【限制显示个数】
e.g. select* from limit 7;
select* from order by id desc limit 7;
select* from where id < 10 order by id desc limit 7;
select* from where id > 14 order by id asc limit 0,1; #limit0,1表示从第0个开始取，取1个
10、groupby 字段【分组】
常用函数：
count() #一个字段的总数
sum()
avg()#平均值
max()
min()
e.g. selectcount(*),sum(price),avg(price),max(price),min(price) from products;
selectcid,count(price),sum(price),avg(price),max(price),min (price) fromproducts group by cid;
selectcid,count(price),sum(price),avg(price),max(price),min (price) fromproducts group by cid having avg(price) > 50; #加having条件，与where类似
#having必须与gropby结合才能使用