数据库查询

1、连接查询

1. 内连接查询
   1. 与单表查询相似,都是使⽤select语句
   2. 可以把多张表放到select后边,并⽤逗号隔开
   3. 还可以结合as取别名,⽅便引⽤
   4. 如果要查询多个表中的字段,⽆重名的情况下,可以省略表的指定
2. ⾮等值连接 迪卡尔积

3. -- 内连接中的非等值链接。
   select * from grade,subject;
   
   -- 内连接中的等值连接
   select * 
   from grade g,subject s
   where g.GradeId= s.GradeId;
   
   -- 内连接
   select * 
   from grade g
   inner join subject s on g.GradeId=s.GradeId;
   
   -- 练习1 查询所有课程的名称和年级名称
   select s.SubjectName,g.GradeName
   from subject s
   inner join grade g on s.GradeId=s.GradeId;
   
   -- 练习2 查询学生姓名，课程名称，考试分数
   select s.StudentName,sub.SubjectName,r.StudentResult
   from student s
   inner join result r on s.StudentNo=r.StudentNo
   inner join subject sub on r.SubjectNo=sub.SubjectNo;

    

4. 外连接
   1. 左连接
      1. 从左表中返回所有数据,即便在右表中没有匹配的⾏

      2. -- 左连接
         -- 查询所有年级学的课程
         select g.GradeName,s.SubjectName
         from grade g left join subject s
         on g.GradeId =s.GradeId;

          

   2. 右连接
      1. 从右表中返回所有的记录,即使在左表中没有匹配的⾏

      2. -- 右连接
         -- 查询所有年级学的课程
         select g.GradeName,s.SubjectName
         from grade g right join subject s
         on g.GradeId =s.GradeId;

          

5. join对比
   1. inner join 表中有⾄少有⼀个匹配的,则返回行。
   2. left join 不论右表是否有匹配,都会返回左表的所有的行。
   3. right join 不论左表是否有匹配,都会返回右表的所有的行。

2、排序 order by

1. 对select语句查询到的结果,按某些字段进行排序。
2. 升序asc 和 降序desc 默认是升序asc。

3. -- 排序
   -- 查询数据库结构-1的所有考试结果，并按成绩由高到低排序
   -- 显示姓名，课程名称，分数
   select s.StudentName,sub.SubjectName,r.StudentResult
   from student s
   inner join result r on s.StudentNo= r.StudentNo
   inner join subject sub on sub.SubjectNo=r.SubjectNo
   where sub.SubjectName="数据库结构-1"
   order by r.StudentResult desc;

    

3、limit 常⽤于分页显示

1. limit [m,] n
   1. 限制select返回结果的⾏数。
   2. m 制定第⼀个返回记录的偏移量。
   3. n 制定返回记录⾏的最⼤数⽬。

2. -- limit
   -- 返回成绩表中前5条记录
   select * from result limit 5;
   select * from result limit 0,5;
   
   -- 查询数据库结构-2的所有考试结果,并按成绩由高到低排序
   -- 显示学号,学生姓名,考试分数查询前5名
   select s.StudentNo,s.StudentName,r.StudentResult
   from student s
   inner join result r on s.StudentNo= r.StudentNo
   inner join subject sub on sub.SubjectNo=r.SubjectNo
   where sub.SubjectName="数据库结构-2"
   order by r.StudentResult desc
   limit 0,5;

    

4、子查询

1. 在查询语句中的where 条件中,⼜嵌套了另外⼀个查询语句
2. ⼦查询的结果⼀般都集合,建议使⽤in

3. -- 子查询
   -- 查询课程为高等数学-2 且分数不小于80的学生编号和姓名
   -- 方式一
   select s.StudentNo,s.StudentName
   from student s
   inner join result r on s.StudentNo= r.StudentNo
   inner join subject sub on sub.SubjectNo=r.SubjectNo
   where sub.SubjectName="高等数学-2" and r.StudentResult >= 80;

    

4. -- 方式二（子查询方式）
   -- 第一步先查询出高等数学的课程编号
   select sub.SubjectNo
   from student sub 
   where sub.StudentName="高等数学-2";
   -- 第二步查询课程编号为2的且分数大于等于80的学生编号
   select r.StudentNo
   from result r 
   where r.SubjectNo=2 and r.StudentResult>=80;
   -- 第三步根据学生编号查询学生姓名
   select s.StudentNo,s.StudentName
   from student s 
   where s.StudentNo in (select r.StudentNo
   from result r 
   where r.SubjectNo=2 and r.StudentResult>=80);
   -- 最终的
   select s.StudentNo,s.StudentName
   from student s 
   where s.StudentNo in (
   select r.StudentNo
   from result r 
   where r.SubjectNo=(
   select sub.SubjectNo
   from student sub 
   where sub.StudentName="高等数学-2")
   and r.StudentResult>=80);

    

5、统计函数

1. count() 返回满⾜select 条件的记录的总和数。
2. sum() 返回列的总和。
3. avg() 返回列平均值。
4. max() 返回列的最⼤值。
5. min() 返回列的最⼩值。

6. -- 统计函数
   -- 1、计数count
   -- 查询result表中有多少条记录
   select count(*) from result;
   -- 2、求和sum
   -- 求成绩大于98分的所有成绩的和
   select sum(StudentResult) from result where StudentResult>98;
   -- 3、求平均成绩 avg 
   -- 求成绩表中的平均成绩
   select avg(StudentResult) from result;
   -- 4、求最大值 max
   select max(StudentResult) from result;
   -- 5、求最小值 min
   select min(StudentResult) from result;

    

6.分组 group by 

1. 可以对所有 数据进⾏⼀个分组统计
2. 分组的依据字段是可以有多个的,并依次分组
3. 与having 结合使⽤ 进⾏分组后的数据筛选

4. -- 分组group by
   -- 按不同课程分组，分别计算出其平均分，最高分，最低分
   -- 对于低于80平均分的不显示
   select sub.SubjectName,avg(r.StudentResult),max(r.StudentResult),min(r.StudentResult)
   from subject sub inner join result r
   on sub.SubjectNo=r.SubjectNo
   group by sub.SubjectName
   having avg(r.StudentResult)>=80;