第五章 数据高级查询

1、聚合函数

    1）sum   求和汇总

            select    sum(列)    from    表名

--查询所有成绩的总分
select sum(score) as sum_score from score

    

    2）avg    平均值

--查询班级的平均分
select round(avg(score)) as avg_score from score

    3）max    最大值

--查询最高分
select max(score) as max_score from score

    4）min    最小值

--查询最低分
select min(score) as min_score from score

    5）count    行数

            count函数的参数如果为列名则计算该有值的行数

--查询考试表的总行数
select count(*) as score_count from score

例子：

--查询编号为2的课程的最高分
select max(score) from score where courseId=2
--查询学号为1001的同学的考试的次数
select count(*) from score where stuId=1001
--查询学号为1000的同学所考的编号为2的课程的总分和平均分
select sum(score),round(avg(score),1) from score where stuId=1000 and courseId=2

2、分组查询

        select    列名    from    表名    group    by    列

        

        聚合函数在分组查询的过程将执行多次，取决于分组的组数。

        如果使用分组查询或聚合查询时，只能查询被分组的列或者是聚合列

        如下是错误的：

select stuId,sum(score) from score where stuId=1000

        正确的例子：        

select courseId,avg(score) from score group by courseId

        先执行where条件，当满足条件的数据筛选后再进行分组操作

select courseId,avg(score) from score where stuId=1000 group by courseId

        

        允许对多个列进行分组

                将多个列作为整体进行分组。

select stuId,,courseId from score group by courseId,stuId

--查询所有及格的每一个同学在每门课程中的最低分
select stuId,courseId,min(score)  from score where score>=60 group by stuId,courseId

    

        where 条件不能使用聚合函数，可以使用having。

        having条件筛选（having只能和group by 结合使用）

            select    列名    from    表名    group    by    列    having    条件

        

--查询单科考试4次以上的平均分
select courseId,avg(score) from score group by courseId having count(*)>4
--查询参加过补考的学生学号
select stuId from score group by stuId,courseId having count(*)>1 

         having 和 where条件的相同点和区别：

                1）都表示条件的过滤

                2）having只能写在group 后，where可以出现任何情况

                3）先执行where再执行group    by最后执行having

                4）having中可以使用聚合函数，where不能使用

3、多表连接

        表连接查询

            1）inner join

                    内连接查询，获取两表中共同部分的数据

                    select    列    from    表A    inner    join    表B    on    表A.列=表B.列    where    条件

                    下面方式等同内连接：

                    select    列 from    表A，表B    where    表A.列=表B.列    and  条件   

--查询学生的姓名和对应的成绩
select stuName,score from student stu inner join score s on stu.stuId=s.stuId
--作用等同于上述方式
select stuName,score from student,score where student.stuid=score.stuid

例：

--查询编号为102的员工姓名和所在部门的名称
select first_name||last_name as empName,department_name 
       from emp inner join dep on emp.department_id=dep.department_id where employee_id=102
       
select first_name||last_name as empName,department_name 
       from emp,dep where emp.department_id=dep.department_id and employee_id=102
       
--查询学生的姓名，课程名称，成绩
select stuName,courseName,score from student stu 
       inner join score s on stu.stuId=s.stuId 
             inner join course c on s.courseId=c.courseId
             
--查询每一个员工的姓名，岗位名称，薪水和部门名称（要求使用inner join实现）
select first_name||last_name as empName,job_title,department_name,salary from
       emp inner join dep on emp.department_id=dep.department_id
           inner join job on emp.job_id=job.job_id
--查询部门编号为100的员工姓名，部门名称，岗位名称（要求是用from a,b,c实现）
select first_name||last_name as empName,job_title,department_name,salary from
       emp,dep,job where emp.department_id=dep.department_id and emp.job_id=job.job_id and emp.department_id=100
     
--查询所有发帖用户的姓名，发帖的标题，内容，回帖人的姓名，回帖内容
select u1.uname as publish_name,ttopic,tcontents,u2.uname as reply_name,rcontents
       from bbsusers u1 inner join bbstopic t on u1.userid=t.tuid 
            inner join bbsreply r on t.tid=r.rtid
                  inner join bbsusers u2 on u2.userid=ruid

            2）left   join

                    左连接 获取左表中的所有数据和右表中匹配的数据

                    左连接查询左表的所有数据以及右表中和左表关联条件相同的共性数据

insert into card
       select cardId.nextval,a.accountId,1 from bank_account a left join card c on a.accountId=c.accountId where c.accountId is null

            3）right  join

                    右连接 获取右表中的所有数据和左表中匹配的数据

4、子查询

        子查询即查询语句使用嵌套的查询，在SQL语句中可以将查询的结果作为呈现数据或者将结果作为条件再次进行条件筛选

        select    列名    from    表名    where    列名=（select    列名    from    表名）

        子查询的应用场景：

               1） 将查询的结果作为值继续进行查询

               2）将查询的结果作为表格继续进行查询。

        如果将结果集作为值来查询的，同时使用in来关联该结果时，则结果集可以是一行或者是多行的单列数据

        如果将结果集作为值来查询的，使用in以外的运算符来关联该结果时，则结果集必须是单行单列的数据

select * from users where age>(select age from users where lower(userName)='tom');
--查询Java课程的平均分
select avg(score) from score where courseId=(select courseId from course where courseName='Java')

子查询的注意点：

            子查询必须写在括号中

            子查询的结果集可以作为列值或新表继续查询

            子查询不能作为order by的表达式

例子：

--查询成绩比jack的Oracle课程平均分高的学生的编号和课程编号以及成绩
select stuId,courseId,score from score where score>
   (select avg(score) from score where stuId=(select stuId from student where stuName='jack') 
       and courseId=(select courseId from course where courseName='Oracle'))
--查询成绩比jack的Oracle课程平均分高的学生的姓名和课程名称以及成绩
select stuName,courseName,score from student stu,course c,
    (select stuId,courseId,score from score where score>
        (select avg(score) from score where stuId=(select stuId from student where stuName='jack') 
                 and courseId=(select courseId from course where courseName='Oracle'))) new_table
                       where stu.stuId=new_table.stuId and c.courseId=new_table.courseId

嵌套子查询

标量子查询

    标量子查询，将子查询的结果作为常量列呈现

--查询所有员工最高的薪资和最低薪资
select (select max(salary) from emp),(select min(salary) from emp) from dual
--查询出员工最最高的薪资以及部门编号最高的部门名称
select (select max(salary) from emp),(select department_name from dep where department_id=(select max(department_id) from dep)) from dual
--distinct用来筛选掉重复的数据
select distinct(department_id) from emp;

相关子查询

    子查询语句依赖于外部的主查询语句，因此相关子查询是先执行主查询语句再执行子查询语句

--查询学生的姓名和他的成绩
select score,(select stuName from student where student.stuId=score.stuId) from score
--查询emp表中员工的姓名，岗位名称和部门名称
select first_name||last_name as empName,
       (select department_name from dep where dep.department_id=emp.department_id) as depName,
       (select job_title from job where job.job_id=emp.job_id) as jobName from emp