MYSQL DML增删改 命令

插入一条记录

 insert into users(uid,uname,upassword) values(null,'me','123');

查询一条记录

select * from users

更新全部字段

UPDATE users SET upassword='666';

带条件更新

UPDATE users SET upassword='999' WHERE uid=1;

带条件删除

DELETE FROM users WHERE uid=1 

全部删除

DELETE FROM users;

查询所有表

select * from product; 表名

查询 pname price字段

select pname,price from product;

查询字段 去除重复

 select distinct(price) from product;

查询商品为qwe的字段

select * from product where pname='qwe';

查询price大于50的字段

select * from product where price > 50;

大于等于

select * from product where price >= 998;

查询含有q的字段

select * from product where pname like '%q%';

查询id 4 5 6 字段

select * from product where pid in(4,5,6);

查询 2 5 id

select * from product where pid=2 or pid=5;

查询排序 按照价格从小到大

 select * from product order by price asc;

按照时间从大到小

select * from product order by pdate desc;

聚合函数

获得所有商品的价格的总和

select sum(price) from product;

平均价格

 select  avg(price) from  product;

获取所有商品个数

 select count(*) from product;

4.5 分组操作

1.添加分类id

alter table product add cid varchar(32);

2.初始化数据

update product set cid='1';
update product set cid='2' where  pid in (5,6,7);

根据cid字段分组，分组后统计商品的个数

 select cid ,count(*) from product group by cid;

根据cid查询每组的大于0的平均价格

select cid,avg(price) from product group by cid having avg(price) > 0;

select  一般在的后面的内容都是要查询的字段

from  要查询到表

where

group by

having  分组后带有条件只能使用having

order by 它必须放到最后面

转载于:https://my.oschina.net/u/2999760/blog/1486438