SQL语句 第6章 连接查询

6.1  内连接查询

6.1.1  简单内连接

  1．相等连接
例6.1_1

SELECT teacher_id, name, department_name
  FROM Teachers, Departments
    WHERE Teachers.department_id = Departments.department_id;

6.1.2  复杂内连接
1．使用筛选条件

例6.1_8 

SELECT s.student_id, s.name, count(*) AS 所修课程门数
  FROM Students s, Students_grade sg
    WHERE s.student_id = sg.student_id
      GROUP BY s.student_id, s.name
        HAVING count(*)>1
          ORDER BY s.student_id;

6.2  外连接查询

6.2.1  左外连接

例6.2_2

SELECT teacher_id, name, department_name
  FROM Teachers t LEFT OUTER 
    JOIN Departments d ON t.department_id = d.department_id;

 

6.2.2  右外连接

例6.2_4

SELECT teacher_id, name, department_name
  FROM Teachers t RIGHT OUTER 
    JOIN Departments d ON t.department_id = d.department_id;

 

6.2.3  全外连接

例6.2_6

SELECT teacher_id, name, department_name
  FROM Teachers t FULL OUTER
    JOIN Departments d ON t.department_id = d.department_id;

 

6.3  其他特殊连接

6.3.1  交叉连接
例6.3_1

SELECT teacher_id, name, department_name
  FROM Teachers, Departments;

 

6.3.2  自连接

例6.3_3

SELECT s1.student_id, s1.name AS 学生名, s1.monitor_id, s2.name AS 班长名
  FROM Students s1 LEFT OUTER
    JOIN Students s2 ON s1.monitor_id = s2.student_id;