多表查询、左右链接等

   前阵子由于工作需要简单复习了下SQL,现在把之前写的总结发到这里。很基础的东西,出现错误欢迎指正。
一, 表结构:
create table USER_ID
(
 ID number(10),
 name varchar2(255),
 age number(3),
 telephone number(11),
 birthday varchar2(8)
);

create table school
(
  school_id number(10),
  name varchar2(255),
  address varchar2(255),
  level1 number(1),
  is_985 varchar2(1),
  createdate date
);

create table student
(
  ID number(10),
  student_id number(10),
  school_id number(10),
  enter_date date
);

create table course
(
 school_id number(10),
 course_id number(10),
 course_name varchar2(255),
 teacher varchar2(255)
);

create table grade_core
(
 school_id number(10),
 course_id number(10),
 student_id number(10),
 grade_core number(10),
 term varchar2(10)
)

二, 题目及sql:
1、 要求输出:学生号,学生名,年龄,课程名称,分数,学期;
Select student.student_id,User_id.Name,user_id.age,course.course_name,grade_core.grade_core,grade_core.term
from User_Id,student,grade_core,course
where User_Id.id=student.id
    and student.student_id=grade_core.student_id
    and grade_core.course_id=course.course_id
and course.school_id=student.school_id

其中:grade_core.student_id不是唯一的,所以加上了最后一个and

左链接:
select us.ID,g.STUDENT_ID,us.NAME,us.age,g.COURSE_NAME,g.GRADE_CORE,g.TERM
from grade_core g

left join COURSE c
on g.COURSE_ID=c.COURSE_ID

left join student st
on st.STUDENT_ID=g.STUDENT_ID
and st.SCHOOL_ID=g.SCHOOL_ID

left join user_id us
on st.ID=us.ID


2求liming第一学期的平均成绩,
   select avg(grade_core.grade_core) 
               from user_id,student,grade_core
               where user_id.id=student.id
               and student.school_id=grade_core.school_id
               and grade_core.term=1
               and user_id.name='liming'

    left:select avg(g.grade_core)
       from grade_core g 
       left join student stu
       on stu.student_id=g.student_id
       left join user_id us
       on us.id=stu.id
       where g.term=1
       and us.name='liming'
       

3求清华所有的学生数
       select count(student.id)
       from student,school
       where school.name='清华'
       and student.school_id=school.school_id

4、求每个人每个学期的平均成绩
select avg(g.grade_core)
        from grade_core g,student s,user_id u
        group by g.student_id,g.term


左连接,右链接的理解。
select * from grade_core g
left join student st
on g.SCHOOL_ID = st.school_id
and g.Student_id=st.student_id
and g.STUDENT_ID=22201—左链接的这一条,如果有的话,一定就会出现这个结果?
where id='102';
 

select * from grade_core g
left join student st
on g.SCHOOL_ID = st.school_id
and g.Student_id=st.student_id
where g.STUDENT_ID=22201—求此条件与上面左链接的交集?
and id='102'
 

感受一下这两个有什么区别


select * from grade_core g
left join student st
on g.SCHOOL_ID = st.school_id
and g.Student_id=st.student_id
and g.STUDENT_ID=22201;
上句更常写成:
select * from grade_core g
left join student st
on g.SCHOOL_ID = st.school_id
and g.Student_id=st.student_id
 and st.STUDENT_ID=22201


 

select * from grade_core g
left join student st
on g.SCHOOL_ID = st.school_id
and g.Student_id=st.student_id
where g.STUDENT_ID=22201
 

可以先拆分成这样看看结果有什么不一样
首先,select * 会把左链接的两表所有字段都查出来,链接、形成一个中间表。
Left join on 后边的and 通过限制表的链接过程,只对中间表的右表起限制,但不影响左表;而where后边的and则是对链接后形成的中间表起限制作用。


以后写关联的时候,尽量使用left join比较直观,方便梳理逻辑,一点点的查看关联是否正确。where X=X,作关联,是inner join的形式,取的是交集,不能满足所有的业务统计需求。


今天有时间的话,看一下,left join,right join,inner join,full join,cross join五种关联关系
Full join :返回左右链接的的并集。
Cross join: 返回笛卡尔积,返回两个表中所有可能的行组合。
create table grade_core_lsj
(
 course_id number(10) ,
 student_id number(10), primary key (course_id,student_id),
 school_id number(10) not null,
 grade_core number(10)not null,
 term varchar2(10)not null,
 student_name varchar(20)
)
posted @ 2017-04-17 10:01  名白  阅读(161)  评论(0编辑  收藏  举报