MySQL笔记

增删改查

1.group by

//语法结构
group by {col_name | position} {ASC | DESC}

select sex from users group by sex

//设定条件having
//having后面，1.聚合函数  2.字段必须出现在select语句当中

select sex from users group by 1 having count(id)>3 ;

对查询结果进行排序

order by {col_name | expr | position}[ASC|DESC],...

//限制查询结果返回的数量

limit [offset] row_conut  //offset偏移值，结果集从0开始计数

insert  select   //两个命令的嵌套使用

子查询

子查询可以返回标量，一行，一列或子查询

比较运算符
SELECT AVG（goods_price）FROM tdb_goods
SELECT ROUND(AVG(goods_price),2) FROM tdb_goods

子查询语句：

查询数据表里商品价格大于平均价格的商品

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= (SELECT AVG(goods_price) FROM tdb_goods );