sql-多表查询基础操作

所用表以及数据：

CREATE TABLE `stu` (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(20) DEFAULT NULL,
  age int(11) DEFAULT NULL,
  score double(4,2) DEFAULT NULL,
  address_id int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  CONSTRAINT `FK_stu_address` FOREIGN KEY (address_id) REFERENCES address (`id`) ON UPDATE CASCADE
) ;


create table address(
        id int primary key auto_increment, # id自增
        description varchar(20)
);
插入的数据：
insert into `address` (`id`, `description`) values('1','France');
insert into `address` (`id`, `description`) values('2','England');
insert into `address` (`id`, `description`) values('3','China');
insert into `address` (`id`, `description`) values('4','');
insert into `stu` (`id`, `name`, `age`, `score`, `address_id`) values('1','li','19','90.80',NULL);
insert into `stu` (`id`, `name`, `age`, `score`, `address_id`) values('2','wang','19','89.40','1');
insert into `stu` (`id`, `name`, `age`, `score`, `address_id`) values('3','gu','18','94.00','1');
insert into `stu` (`id`, `name`, `age`, `score`, `address_id`) values('4','luo','19','95.00','1');
insert into `stu` (`id`, `name`, `age`, `score`, `address_id`) values('5','cui','20','96.00','1');
insert into `stu` (`id`, `name`, `age`, `score`, `address_id`) values('6','zhang','21','96.00','2');
insert into `stu` (`id`, `name`, `age`, `score`, `address_id`) values('7','chen','22','97.00','2');
insert into `stu` (`id`, `name`, `age`, `score`, `address_id`) values('8','yao','23','98.00','2');

内连接查询

• 隐式内连接查询
  创建：select 表1.列1，表1.列2，表2.列2 from 表1，表2 where 表1.列1=表2.列1；
  例子：select stu.name,stu.age,address.description from stu,address where stu.address_id=address.id;

mysql> select stu.name,stu.age,address.description from stu,address where stu.address_id=address.id;
+-------+------+-------------+
| name  | age  | description |
+-------+------+-------------+
| wang  |   19 | France      |
| gu    |   18 | France      |
| luo   |   19 | France      |
| cui   |   20 | France      |
| zhang |   21 | England     |
| chen  |   22 | England     |
| yao   |   23 | England     |
+-------+------+-------------+
7 rows in set (0.00 sec)

• 显式内连接查询
  创建：select 表1.列1，表1.列2，表2.列2 from 表1 (inner) join 表2 on 表1.列1=表2.列1;
  例子：select stu.name,stu.age,address.description from stu join address on stu.address_id=address.id;

mysql> select  stu.name,stu.age,address.description from stu join address on stu.address_id=address.id;
+-------+------+-------------+
| name  | age  | description |
+-------+------+-------------+
| wang  |   19 | France      |
| gu    |   18 | France      |
| luo   |   19 | France      |
| cui   |   20 | France      |
| zhang |   21 | England     |
| chen  |   22 | England     |
| yao   |   23 | England     |
+-------+------+-------------+
7 rows in set (0.00 sec)

外连接查询

• 左连接查询
  创建：select * from 表1 left join 表2 on 表1.列1=表2.列1;其中表1是左表，表2是右表，显示表1中所有记录及与表2的交集部分。
  例子：select * from stu left join address on stu.address_id=address.id;

mysql> select * from stu left join address on stu.address_id=address.id;
+----+-------+------+-------+------------+------+-------------+
| id | name  | age  | score | address_id | id   | description |
+----+-------+------+-------+------------+------+-------------+
|  1 | li    |   19 | 90.80 |       NULL | NULL | NULL        |
|  2 | wang  |   19 | 89.40 |          1 |    1 | France      |
|  3 | gu    |   18 | 94.00 |          1 |    1 | France      |
|  4 | luo   |   19 | 95.00 |          1 |    1 | France      |
|  5 | cui   |   20 | 96.00 |          1 |    1 | France      |
|  6 | zhang |   21 | 96.00 |          2 |    2 | England     |
|  7 | chen  |   22 | 97.00 |          2 |    2 | England     |
|  8 | yao   |   23 | 98.00 |          2 |    2 | England     |
+----+-------+------+-------+------------+------+-------------+
8 rows in set (0.00 sec)

• 右连接查询
  创建：select * from 表1 right join 表2 on 表1.列1=表2.列1;其中表1是左表，表2是右表，显示表2中所有记录及与表1的交集部分。
  例子：select * from stu right join address on stu.address_id=address.id;

mysql> select * from stu right join address on stu.address_id=address.id;
+------+-------+------+-------+------------+----+-------------+
| id   | name  | age  | score | address_id | id | description |
+------+-------+------+-------+------------+----+-------------+
|    2 | wang  |   19 | 89.40 |          1 |  1 | France      |
|    3 | gu    |   18 | 94.00 |          1 |  1 | France      |
|    4 | luo   |   19 | 95.00 |          1 |  1 | France      |
|    5 | cui   |   20 | 96.00 |          1 |  1 | France      |
|    6 | zhang |   21 | 96.00 |          2 |  2 | England     |
|    7 | chen  |   22 | 97.00 |          2 |  2 | England     |
|    8 | yao   |   23 | 98.00 |          2 |  2 | England     |
| NULL | NULL  | NULL |  NULL |       NULL |  3 | China       |
| NULL | NULL  | NULL |  NULL |       NULL |  4 |             |
+------+-------+------+-------+------------+----+-------------+
9 rows in set (0.00 sec)

内连接查询与外连接查询区别：
内连接查询：显示交集部分数据，为空则不显示；
外连接查询：显示左（右）表所有数据，及与右（左）表的交集部分数据。