MySQL数据查询 - 联合查询

联合查询

1. 内连接查询

1. 内连接查询
   在实际开发中，我们会联合多个表来实现查询，比如把班级表和学生表联合起来就同时可以看到班级、老师和学员的信息，一个班级表：
   

   这种只有2张表匹配的行才能显示的连接方式在Mysql 中称之为内连接： INNER JOIN
   在MySQL中内连接数据查询通过“INNER JOIN…ON”语句来实现，语法形式如下所示。

   SELECT field1,field2,…,fieldn FROM tablename1
       INNER JOIN tablename2 [INNER JOIN tablenamen] ON CONDITION; 
   

   其中，参数fieldn表示要查询的字段名，来源于所连接的表tablename1和tablename2，关键字INNER JOIN表示表进行内连接，参数CONDITION表示进行匹配的条件。

   mysql> CREATE TABLE class(id int PRIMARY KEY, name varchar(128), teacher varchar(64));  #创建班级表 class  
   mysql> insert into class values(101, '萌新一班', '张三'),(102, '萌新二班', '李四'),(103, '萌新三班', '王五');			#往班级表中插入多条记录  
   mysql> CREATE TABLE student(id int, name varchar(64), class_id int, sex enum('F','M'));	#创建学生表
   mysql> insert into student values(1,'小花',101,'M'),(2,'小红',102, 'F'),(3,'小军',102,'F'),(4,'小白',101,'F');  		#往学生表中插入多条记录  
   mysql> select * from class inner join student  on class.id = student.class_id;  #查询class 表和student 表中班级字段相同的记录并进行内连接
   +-----+----------+---------+------+------+----------+------+
   | id  | name     | teacher | id   | name | class_id | sex  |
   +-----+----------+---------+------+------+----------+------+
   | 101 | 萌新一班 | 张三    |    1 | 小花 |      101 | M    |
   | 102 | 萌新二班 | 李四    |    2 | 小红 |      102 | F    |
   | 102 | 萌新二班 | 李四    |    3 | 小军 |      102 | F    |
   | 101 | 萌新一班 | 张三    |    4 | 小白 |      101 | F    |
   +-----+----------+---------+------+------+----------+------+
   
   mysql> select * from class as a inner join student as b where a.id = b.class_id; #同上，使用别名查询
   

   当表名特别长时，直接使用表名很不方便，或者在实现自连接操作时，直接使用表名无法区别表。为了解决这一类问题，MySQL提供了一种机制来为表取别名，具体语法如下：

   SELECT field1, field2, ...,fieldn [AS] otherfieldn 
   	FROM tablename1 [AS] othertablename1, ...,
     	Tablenamen [AS] othertablenamen ... where othertablename1.fieldx = othertablenamen.fieldx ... ;
   
   mysql> select s.id, s.name, s.class_id, s.sex, c.id, c.name, c.teacher from class AS c inner join student AS s on c.id=s.class_id;
   
   其中，参数tablename*为表原来的名字，参数othertablename*为新表名，之所以要为表设置新的名字，是为了让SQL语句代码更加直观、更加人性化和实现更加复杂的功能。
   

2. 自连接
   内连接查询中存在一种特殊的等值连接——自连接。所谓自连接，就是指表与其自身进行连接。
   如：查询学生 ”小红 ”所在班级的其他学生,SQL语句如下：

   mysql>  select * from student s1 inner join student s2 on s1.class_id = s2.class_id and s2.name= '小红';  #查询学生 ”小红 ”所在班级的其他学生   
   +------+------+----------+------+------+------+----------+------+
   | id   | name | class_id | sex  | id   | name | class_id | sex  |
   +------+------+----------+------+------+------+----------+------+
   |    2 | 小红 |      102 | F    |    2 | 小红