修改列定义
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="超级本");