1.增加数据
插入一条数据:
insert into student values(1,'朱常乐',26,66.66);
插入多条数据:
insert into student values(2,'双飞',21,77.77),(3,'海全',38,88.88),(4,'顺杰',25,99.99);
2.修改数据
修改一条数据中的一个字段:
update student set name='小张' where id=1;
修改一条数据中的多个字段:
update student set name='小飞',age=3 where id=2;
3.删除数据
删除一条数据:
delete from student where id=1;
删除表中全部数据:
delete from student ;
4.查询数据
普通查询:条件查询
select * from student;
查询一条信息:
select * from student where name='张吉';
条件查询:查出id为1和id为3的数据
select * from student where id in(1,3);
条件查询:查询成绩大于80且小于100的数据
select * from student where score between 80 and 100;
模糊查询:查询名字张开头的
select * from student where name like '张%';
排序:成绩从低到高
select * from student order by score;
排序(倒序):成绩从高到低
select * from student order by score desc;
分页:显示成绩靠前的前两名
select * from student order by score desc limit 0,2;
分组:按照性别分组
select * from student group by sex;
聚合数据:查询班里有多少学生!!count()
select count(*) from student;
聚合数据:查询班里成绩最高的!!max()
select max(score) from student;
聚合数据:查询班里成绩最低的!!min()
select min(score) from student;
聚合数据:查询班里总成绩!!sum()
select sum(score) from student;