SQL数据库基础（3）表的连接与查询

查询

1）查询表所有的数据

select *（*代表是所有的字段名） from 表

2）过滤重复的数据性别

select DISTINCT sex from student

3）NULL空值条件查询

例：SELECT * FROM student where StudentName is null;

   SELECT * FROM student where StudentName is NOT null;

4）空符串查询

例：SELECT * FROM student where address='';

表的连接

1） 内连接简写语法：

SELECT * from  表1 , 表2  WHERE 条件
  
例：-- 查询学员所在的班级名称，学员姓名;
 select GradeName,StudentName from student st,Grade gr
 where st.gradeid =gr.gradeid;

  -- 查询学员所在的班级名称，学员姓名,考试成绩;
 select GradeName,StudentName,StudentResult from student st,Grade gr,result r
 where st.gradeid =gr.gradeid and st.StudentNo=r.StudentNo;

 
  -- 查询学员所在的班级名称，学员姓名,考试科目，考试成绩;
 select GradeName,StudentName,SubjectName,StudentResult from student st,Grade gr,result r,`subject` su
 where st.gradeid =gr.gradeid and st.StudentNo=r.StudentNo  and su.SubjectNo =r.SubjectNo;

2） 外连接简写语法

1. 左外连接（LEFT JOIN）  （以左表为基准，如果没有匹配的，那么就用Null填充）

select * from  表1 LEFT JOIN 表2 on 条件

例：select * from result LEFT JOIN student on student.StudentNo = result.StudentNo;

 -- 没有参加考试的学员信息
  select student.StudentNo, StudentName from student LEFT JOIN result 
 on student.StudentNo = result.StudentNo where result.StudentResult is null;	


2. 右外连接（RIGHT JOIN）（以右表为基准，如果没有匹配的，那么就用Null填充）

select * from  表1 right JOIN 表2 on 条件

例：-- 查询学生所在的年级
 select * from  grade RIGHT JOIN student on  grade.GradeID =s	tudent.GradeId;

3） 自连接查询

 select c1.cName,c2.cName from category c1,category c2 where c1.cId=c2.pid;

例：查询不同课程成绩相同的学生的学号、课程号、学生成绩(自连接)   （源自home3)
select DISTINCT s1.sid,s1.cid,s2.score from sc s1, sc s2
where s1.cid<>s2.cid and s1.score=s2.score ORDER BY s2.score

其他

1）ORDER BY排序 （desc降序/ASC升序(默认可写可不写)）

 select * from student;
 select * from result;
 
 例： -- 查询学员姓名及成绩，成绩按降序排序
  select studentname,studentResult from student st,result r
  where st.StudentNo =r.StudentNo
  order by StudentResult desc;

 -- 查询学员学号,姓名及成绩，成绩按降序排序,学号升序
  select st.StudentNo, studentname,studentResult from student st,result r
  where st.StudentNo =r.StudentNo
  order by StudentResult desc,st.StudentNo asc;

2）LIMIT（限制SELECT返回结果的行数）

--    LIMIT [m,]n  或  LIMIT  n  OFFSET  m
-- m 制定第一个返回记录行的偏移量
-- n 制定返回记录行的最大数目

   -- 查询成绩表前8条  （第一页）
   select * from result LIMIT 0,8;
   -- （第二页）
   select * from result LIMIT 8,8;
   -- （第三页）
   select * from result LIMIT 16,8;
   -- （第四页）
   select * from result LIMIT 24,8;
   -- 0,8,16,24... (页数-1)*每页显示个数
   
例：-- 查询学生姓名，课程名，成绩，显示前5名的信息
select studentname 姓名, subjectname 课程名, studentResult 成绩
   from student st,`subject` s,result r
   where st.studentno=r.StudentNo and s.SubjectNo =r.SubjectNo
   order by StudentResult DESC
   LIMIT 5;

3）MySQL函数

1.最大函数           max()
2.最小函数           min()
3.平均函数			avg()
4.总和函数 			sum()
5.总个数函数 		count()
例： -- 查询学号是1000的最高分，最低分，平均分，总分，总个数；
     select count(1),sum(studentResult),avg(studentResult),min(studentResult),max(studentResult)
     from result where studentno=1000;

4）group by 分组

-- 按照不同的课程分组，分别算出其平均分、最高分和最低分，对于低于60分平均分的不予显示
     select SubjectNo,avg(studentResult) avgs,min(studentResult),max(studentResult)
     from result group by SubjectNo