sql常用问题(一)

一、sql要掌握

      1、sum

          select  sum(score) from table

      2、group

            select name, sum(score) from table group by 

      3、avg()

           SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

 

一、查询是否存在

      1、查询数据是否存在

           SELECT COUNT(*) FROM Table_name WHERE ID=xxx;

     2、查找重复数据  

           select * from people where peopleId in (select peopleId from people group by peopleId having count (peopleId) > 1)

     3、查找表中多余的重复记录（多个字段）

         比如名字重复：

         select  name, count(name) as count from test_table group by name having count>1;

        例子：查下图重复的student_no, 输入：

          

            输入：select student_no, count(student_no) as count from score GROUP BY student_no having count>1;

            

    4、查询平均分大于80；字段（name、score1、score2）

       select name from test_table group by name having avg(score)>80

          例子，在以下两个表格找，平均分大于80的student name

                   

              运行：select student_name from student where student_no = ( 

                                      select student_no from score a GROUP BY student_no having AVG(score) > 80)

              

  5、查询三门课都大于80 的学生；

         SELECT S.name FROM Student S GROUP BY S.name Having MIN(S.score)>=80