sql基本操作2

sql语句基本操作2
1.插入记录
insert into user(uid,uname,upassword)values(null,'zhangsan','123');
insert into user values(null,'lisi','321');
2.修改表记录
update user set upassword='666';
update user set upassword='666' where uid=3;
改多个值用,隔开
3.删除表记录
delete from user where uid=1;注意删除后uid不会重置
delete from user 全部删除了
面试题:关于删除操作
说说delete和truncate的区别?
delete 删除的时候是一条一条删除,配合事务可以找回删除的数据,自动增长还在
truncate 是摧毁表,再创建一张一模一样的表,不可找回删除的数据,自动增长不存在了
查询操作
select * from user;
select uid,uname,upassword from user;
select * from user as u;(表的别名)
select uname as name from user;(列的别名)
select distinct(price) from user;(去掉重复数据)
select pname,price+10 from product;(+10)
条件查询
select * from product where pname='zhangsan';
select * from product where price>60;
select * from product where pname like "%手机";
select * from product where pid in(3,6,9);
select * from product where pid=6 and pname like '%手机%';
select * from product where pid=6 or pid=7;
select * from product where pid not in(3,6,9);
排序
select * from product order by price asc;
select * from product order by price desc;
select * from product where pname like '%手机%' order by price desc;
聚合函数
select sum(price) from product;
select avg(price) from product;
select count(*) from product;
select max(price) from product;
select min(price) from product;
注意聚合函数不统计null值
分组
select cid ,count(*) from product group by cid;
select avg(price) from product group by cid having avg(price) >20000;
select cid, avg(price) from product group by cid having avg(price) >20000;
查询总结
select 一般在的后面的内容都是要查询的字段 from(要查询的表) where group by having (分组后还有条件的只能使用having)order by(顺序)