测试常用sql语句

1、查询出某条件下的所有信息

select * from student where age = 18

2、求和

select sum(score) from student where name="lilei"

3、求平均数

select avg(score) from student where name="lelei"

4、对查询结果排序（倒叙：desc ）

select * from student where name = "lilei" order by score desc

5、求总数

select count(*) from student where class=“one”

6、求总数（去重）

select count(distinct id) from student where class="one"

7、根据一个或多个列表字段进行分组统计查询（where>分组>聚合函数）

select class,count(distinct age) from student where age>19 group by class

8、两个表连接，并去掉重复的列

select a.*,b.address from student a join info b on a.id=b.id

9、嵌套查询

select a.id,a.name,(select b.address from info b where a.id=b.id) address from student a

select a.id,a.name,b.address from student a,info b where a.id=b.id

 

二、插入、修改和删除

1、插入多条数据

insert into student values

(1,"stuname1",18,"Maths","class1"),

(2,"stuname2",88,"Maths","class2") 

2、修改

update student set score=92 where name="kim" and subject="Maths"

3、删除

delete from student where name="kim" and subject="English"