Mysql中的增删改查操作

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;