多表查询
1.说明
当存储时，相同数据出现多次决不是一件好事，这个因素是关系数据库设计的基础。关系表的设计就是要保证把信息分解成多个表，一类数据一个表,各表通过默写常用的值互相关联。
分解数据为多个表能更有效地存储，更方便地处理，并且具有更大的可伸缩性。但这样就不能使用单条SELECT语句检索出数据。
SQL最强大的功能之一就是能在数据检索查询的执行中连接（join）表。
常见术语：
外键（foreign key）外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
可伸缩性（scale）能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好（sacle well）。
数据源
#建表
create table department(
id int,
name varchar(20)
);
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;
#查看表结构和数据
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
表department与employee
自https://www.cnblogs.com/Eva-J/articles/9688383.html
2.建立连接
2.1. 交叉连接：生成笛卡儿积
笛卡儿积（cartesian product）由没有连接条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
范例：
mysql> select * from employee,department; +----+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 1 | egon | male | 18 | 200 | 201 | 人力资源 | | 1 | egon | male | 18 | 200 | 202 | 销售 | | 1 | egon | male | 18 | 200 | 203 | 运营 | | 2 | alex | female | 48 | 201 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 2 | alex | female | 48 | 201 | 202 | 销售 | | 2 | alex | female | 48 | 201 | 203 | 运营 | | 3 | wupeiqi | male | 38 | 201 | 200 | 技术 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 202 | 销售 | | 3 | wupeiqi | male | 38 | 201 | 203 | 运营 | | 4 | yuanhao | female | 28 | 202 | 200 | 技术 | | 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 4 | yuanhao | female | 28 | 202 | 203 | 运营 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | 5 | liwenzhou | male | 18 | 200 | 201 | 人力资源 | | 5 | liwenzhou | male | 18 | 200 | 202 | 销售 | | 5 | liwenzhou | male | 18 | 200 | 203 | 运营 | | 6 | jingliyang | female | 18 | 204 | 200 | 技术 | | 6 | jingliyang | female | 18 | 204 | 201 | 人力资源 | | 6 | jingliyang | female | 18 | 204 | 202 | 销售 | | 6 | jingliyang | female | 18 | 204 | 203 | 运营 | +----+------------+--------+------+--------+------+--------------+ 24 rows in set (0.01 sec)
2.2. 内部连接：只连接匹配的行
建立的连接基于两个表之间的相等测试，称之为等值连接（equijoin），也称之为内部连接。
语法
SELECT 表名1.字段名1, 表名1.字段名2, 表名1.字段名3, 表名2.字段名1 FROM 表名1 INNER JOIN 表名2 ON 表名1.字段=表名2.字段;
这里两个表之间的关系是FROM子句的组成部分，以INNER JOIN指定。在使用这种语法时，连接条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同。
范例
# 找出两张表共有的字段，利用条件从笛卡儿积结果中筛选除了正确的结果。
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name dep_name from employee inner join department on employee.dep_id=department.id; +----+-----------+------+--------+--------------+ | id | name | age | sex | dep_name | +----+-----------+------+--------+--------------+ | 1 | egon | 18 | male | 技术 | | 2 | alex | 48 | female | 人力资源 | | 3 | wupeiqi | 38 | male | 人力资源 | | 4 | yuanhao | 28 | female | 销售 | | 5 | liwenzhou | 18 | male | 技术 | +----+-----------+------+--------+--------------+ 5 rows in set (0.00 sec) mysql> select employee.id,employee.name,employee.age,employee.sex,department.name dep_name from employee, department where employee.dep_id=department.id; +----+-----------+------+--------+--------------+ | id | name | age | sex | dep_name | +----+-----------+------+--------+--------------+ | 1 | egon | 18 | male | 技术 | | 2 | alex | 48 | female | 人力资源 | | 3 | wupeiqi | 38 | male | 人力资源 | | 4 | yuanhao | 28 | female | 销售 | | 5 | liwenzhou | 18 | male | 技术 | +----+-----------+------+--------+--------------+ 5 rows in set (0.00 sec)
2.3 外部连接
连接中包含了在相关表中没有关联行的行
外部连接的类型
存在两种基本的外部连接形式：左外部连接和右外部连接。他们之间的唯一差别时所关联的表的顺序不同。换句话说，左外部连接可通过颠倒FROM或WHERE子句中表的顺序转换为右外部连接。因此，两种类型的外部连接可互换使用，而究竟使用哪一种纯粹是根据方便而定。
2.3.1. 外部连接之左连接
以左表为准，优先显示左表全部记录
语法
SELECT 表名1.字段名1, 表名1.字段名2, 表名1.字段名3, 表名2.字段名1 FROM 表名1 LEFT JOIN 表名2 ON 表名1.字段=表名2.字段;
范例
mysql> select * from employee; +----+------------+--------+------+--------+ | id | name | sex | age | dep_id | +----+------------+--------+------+--------+ | 1 | egon | male | 18 | 200 | | 2 | alex | female | 48 | 201 | | 3 | wupeiqi | male | 38 | 201 | | 4 | yuanhao | female | 28 | 202 | | 5 | liwenzhou | male | 18 | 200 | | 6 | jingliyang | female | 18 | 204 | +----+------------+--------+------+--------+ 6 rows in set (0.00 sec) mysql> select * from department; +------+--------------+ | id | name | +------+--------------+ | 200 | 技术 | | 201 | 人力资源 | | 202 | 销售 | | 203 | 运营 | +------+--------------+ 4 rows in set (0.00 sec) mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id; +----+------------+--------------+ | id | name | depart_name | +----+------------+--------------+ | 1 | egon | 技术 | | 5 | liwenzhou | 技术 | | 2 | alex | 人力资源 | | 3 | wupeiqi | 人力资源 | | 4 | yuanhao | 销售 | | 6 | jingliyang | NULL | +----+------------+--------------+ 6 rows in set (0.00 sec)
2.3.2. 外部连接之右连接
以右表为基准，优先显示右表全部记录
语法
SELECT 表名1.字段名1, 表名1.字段名2, 表名1.字段名3, 表名2.字段名1 FROM 表名1 RIGHT JOIN 表名2 ON 表名1.字段=表名2.字段;
范例
mysql> select * from employee; +----+------------+--------+------+--------+ | id | name | sex | age | dep_id | +----+------------+--------+------+--------+ | 1 | egon | male | 18 | 200 | | 2 | alex | female | 48 | 201 | | 3 | wupeiqi | male | 38 | 201 | | 4 | yuanhao | female | 28 | 202 | | 5 | liwenzhou | male | 18 | 200 | | 6 | jingliyang | female | 18 | 204 | +----+------------+--------+------+--------+ 6 rows in set (0.00 sec) mysql> select * from department; +------+--------------+ | id | name | +------+--------------+ | 200 | 技术 | | 201 | 人力资源 | | 202 | 销售 | | 203 | 运营 | +------+--------------+ 4 rows in set (0.00 sec) mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id; +------+-----------+--------------+ | id | name | depart_name | +------+-----------+--------------+ | 1 | egon | 技术 | | 2 | alex | 人力资源 | | 3 | wupeiqi | 人力资源 | | 4 | yuanhao | 销售 | | 5 | liwenzhou | 技术 | | NULL | NULL | 运营 | +------+-----------+--------------+ 6 rows in set (0.00 sec)
2.3.3. 外部连接
显示左右两个表的全部记录，及左连接和右连接的合集
语法
SELECT * FROM 表名1 LEFT JOIN 表名2 ON 表名1.字段1=表名2.字段2 UNION SELECT * FROM 表名1 RIGHT JOIN 表名2 ON 表名1.字段1=表名2.字段2 ;
范例
mysql> select * from employee left join department on employee.dep_id=department.id union select * from employee right join department on employee.dep_id=department.id; +------+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 6 | jingliyang | female | 18 | 204 | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | +------+------------+--------+------+--------+------+--------------+ 7 rows in set (0.01 sec)
2.4. 连接查询
如果过滤条件的字段在两个表中都存在，需要加上表名
语法
SELECT 表名1.字段名1, 表名1.字段名2, 表名1.字段名3, 表名2.字段名1 FROM 表名1 INNER JOIN 表名2 ON 表名1.字段=表名2.字段 WHERE 过滤条件...;
SELECT 表名1.字段名1, 表名1.字段名2, 表名1.字段名3, 表名2.字段名1 FROM 表名1 LEFT JOIN 表名2 ON 表名1.字段=表名2.字段 WHERE 过滤条件...;
SELECT 表名1.字段名1, 表名1.字段名2, 表名1.字段名3, 表名2.字段名1 FROM 表名1 RIGHT JOIN 表名2 ON 表名1.字段=表名2.字段 WHERE 过滤条件...;
SELECT * FROM 表名1 LEFT JOIN 表名2 ON 表名1.字段1=表名2.字段2 UNION SELECT * FROM 表名1 RIGHT JOIN 表名2 ON 表名1.字段1=表名2.字段2 WHERE 过滤条件...;
范例
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'wher' at line 1 mysql> select employee.id,employee.name,department.name as depart_name from employee inner join department on employee.dep_id=department.id where name regexp 'li'; ERROR 1052 (23000): Column 'name' in where clause is ambiguous mysql> select employee.id,employee.name,department.name as depart_name from employee inner join department on employee.dep_id=department.id where employee.name regexp 'li'; +----+-----------+-------------+ | id | name | depart_name | +----+-----------+-------------+ | 5 | liwenzhou | 技术 | +----+-----------+-------------+ 1 row in set (0.00 sec)
3.子查询
查询（query）任何SQL语句都是查询。但此术语一般指SELECT语句。
子查询（subquery）即嵌套在其他查询中的查询
特点
1- 子查询是将一个查询语句嵌套在另一个查询语句中。
2- 内层查询语句的查询结果，可以为外层查询语句提供查询条件。
3- 子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
4- 还可以包含比较运算符：= 、 !=、> 、<等
3.1 带关键字IN的子句
语法
SELECT 字段 FROM 表名1 WHERE 字段1 IN (SELECT 字段2 FROM 表名2);
范例
mysql> select * from employee; +----+------------+--------+------+--------+ | id | name | sex | age | dep_id | +----+------------+--------+------+--------+ | 1 | egon | male | 18 | 200 | | 2 | alex | female | 48 | 201 | | 3 | wupeiqi | male | 38 | 201 | | 4 | yuanhao | female | 28 | 202 | | 5 | liwenzhou | male | 18 | 200 | | 6 | jingliyang | female | 18 | 204 | +----+------------+--------+------+--------+ 6 rows in set (0.00 sec) mysql> select * from department; +------+--------------+ | id | name | +------+--------------+ | 200 | 技术 | | 201 | 人力资源 | | 202 | 销售 | | 203 | 运营 | +------+--------------+ 4 rows in set (0.00 sec) mysql> select id from department; +------+ | id | +------+ | 200 | | 201 | | 202 | | 203 | +------+ 4 rows in set (0.00 sec) mysql> select * from employee where dep_id in (select id from department); +----+-----------+--------+------+--------+ | id | name | sex | age | dep_id | +----+-----------+--------+------+--------+ | 1 | egon | male | 18 | 200 | | 2 | alex | female | 48 | 201 | | 3 | wupeiqi | male | 38 | 201 | | 4 | yuanhao | female | 28 | 202 | | 5 | liwenzhou | male | 18 | 200 | +----+-----------+--------+------+--------+ 5 rows in set (0.00 sec)
3.2 带运算符的子查询
语法
SELECT 字段 FROM 表名1 WHERE 字段1 比较运算符 (SELECT 字段2 FROM 表名2);
范例
mysql> select name,age from employee where age > (select avg(age) from employee); +---------+------+ | name | age | +---------+------+ | alex | 48 | | wupeiqi | 38 | +---------+------+ 2 rows in set (0.00 sec)
3.3 带EXISTS关键字的子查询
当子查询语句为真的时候，执行外层查询语句。反之，则不进行外部查询语句。
语法
SELECT 字段 FROM 表名1 EXISTS (子查询语句);
范例
# 子查询语句为假，不执行外层查询语句
mysql> select * from employee where exists (select id from department where id=205); Empty set (0.00 sec)
# 子查询语句为真，执行外层查询语句 mysql> select * from employee where exists (select id from department where id=201); +----+------------+--------+------+--------+ | id | name | sex | age | dep_id | +----+------------+--------+------+--------+ | 1 | egon | male | 18 | 200 | | 2 | alex | female | 48 | 201 | | 3 | wupeiqi | male | 38 | 201 | | 4 | yuanhao | female | 28 | 202 | | 5 | liwenzhou | male | 18 | 200 | | 6 | jingliyang | female | 18 | 204 | +----+------------+--------+------+--------+ 6 rows in set (0.00 sec)