多表连接查询

表one

表two

表course

一、两表连接查询

1、左连接SQL语句

左外连接包含left join左表所有行，如果左表中某行在右表没有匹配，则结果中对应行右表的部分全部为空(NULL)

SELECT * FROM one LEFT JOIN two ON one.id =two.id

2、右连接SQL语句

右外连接包含right join右表所有行，如果左表中某行在右表没有匹配，则结果中对应左表的部分全部为空(NULL)。

SELECT * FROM one RIGHT JOIN two ON one.id =two.id

3、内连接SQL语句

inner join 是比较运算符，只返回符合条件的行。

SELECT * FROM one INNER JOIN two ON one.id=two.id 

相当于

SELECT * FROM one,two WHERE one.id=two.id

4、交叉连接

SELECT * FROM one CROSS JOIN two WHERE one.id=two.id

5、USING (column_list)

其作用是为了方便书写连接的多对应关系，大部分情况下USING语句可以用ON语句来代替。

SELECT * FROM one  JOIN two USING(id)

相当于

SELECT * FROM one LEFT JOIN two ON one.id =two.id

6、示例

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Table01 {
   public static void main(String[] args) throws Exception{
      Class.forName("com.mysql.jdbc.Driver");
      Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","username","");
      Statement stmt = con.createStatement();
     String query ="SELECT * FROM one right JOIN two ON one.id =two.id";
      ResultSet rs = stmt.executeQuery(query);
      System.out.println("name sex id");
      while (rs.next()) {    
         String name = rs.getString("name");       
         String sex = rs.getString("sex");
         String id = rs.getString("id");
         System.out.println(name+"  "+sex+"  "+id);
      }
      System.out.println();   
   }
}

运行结果

二、三表连接查询

1、SQL语句

查询one表中的name、two表中的sex以及course表中的cname

SELECT name,sex,cname 
FROM one,two,course 
WHERE one.id=two.id AND one.id=course.cno

2、SQL语句

查询one表中的id、two表中的id以及course表中的cno

SELECT one.id,two.id,course.cno 
FROM (ONE,two)
LEFT JOIN course ON (course.cno=one.id )
WHERE one.id=two.id;

3、示例

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Table {
   public static void main(String[] args) throws Exception{
      Class.forName("com.mysql.jdbc.Driver");
      Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","username","");
      Statement stmt = con.createStatement();
     String query ="SELECT name,sex,cname FROM one,two,course WHERE one.id=two.id AND one.id=course.cno";
      ResultSet rs = stmt.executeQuery(query);
      System.out.println("name sex cname");
      while (rs.next()) {    
         String name = rs.getString("name");       
         String sex = rs.getString("sex");
         String cname = rs.getString("cname");
         System.out.println(name+"  "+sex+"  "+cname);
      }
      System.out.println();
   }
}

运行结果