一些sql总结

找出某张表的某个字段为空的数据

select * from table_name where column_name is null;

 

更新某张表，把某个字段为空的，设置为0

update istester column_name = 0 where column_name is null;

 

找出某张表，某个字段小于60的人

select * from table_name where column < 60;

 

更新某张表，把某个字段小于60的人，改为59

update table_name set column_name = 59 where column < 60;

 

查询某张表有多少条数据

select count(1) from table_name; 

 

对某个字段分组并去重

select column_name,count(distinct column_name) from table group by column_name;

 

查找某张表某个字段的累加分数

select sum(column_name) from table_name;

 

查找某张表某个字段的平均分数

select avg(grade) from table_name;

 

 查找某张表成绩在80-100之间的人

select name,grade from table_name where column_name between 80 and 100;

 

查找某张表的id为2,6,8的数据

select * from table_name where id in (2,6,8);

 

删除某张表id大于12的数据

 delete from table_name where id >10;

 

把某张表1的所有数据，插入到另一张表2里

insert into table_name2(id,stu_id,name,z_test,grade)select id,id,name,60,60 from table_name1;

 

修改某张表的字段名

alter table table_name change column1 column2 varchar(160);

 

修改表1为表2

alter table table_name1 rename to table_name2;

 

查询某张表排名3-6名的人和分数

SELECT name, grade FROM table_name ORDER BY grade DESC LIMIT 2, 4;

 

排名6名以后的所有人和分数

select name,grade from table_name group by grade order by grade asc limit 6,3;

 

总结：

is null表示字段为空

更新表的字段在某个条件下为某值

update table_name set column_name = value where column is null

left join左连接

right join右连接

inner join内连接