mysql基础（二）

修改列定义

alter table users2 modify id int not null first;

修改列名称

alter table users2 CHANGE id id_1 int not null;

修改数据表名

ALTER TABLE users2 RENAME user3;
RENAME TABLE user3 TO user4;

查看表结构

show COLUMNS FROM tdb_goods;

查看表中的所有数据

SELECT * from tdb_goods;
子查询是指出现在其他sql语句内的select语句
子查询指嵌套在查询内部，且必须始终出现在圆括号内。
子查询可以包含多个关键字或条件。子查询的外层查询可以是：select，insert，update，set或do等

查询所有商品的价格平均值

SELECT AVG(goods_price) FROM tdb_goods;
价格平均值保留两位小数
SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;
SELECT * FROM tdb_goods WHERE goods_price>5845.10;

查询价格大于平均值的商品

SELECT * FROM tdb_goods WHERE goods_price>(SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);

查询所有超级本的价格

SELECT goods_price from tdb_goods WHERE goods_cate="超级本";
SELECT * FROM tdb_goods WHERE goods_price> ANY(SELECT goods_price from tdb_goods WHERE goods_cate="超级本");
SELECT * FROM tdb_goods WHERE goods_price NOT in (SELECT goods_price from tdb_goods WHERE goods_cate="超级本");