多表查询
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 1mysql> 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 ambiguousmysql> 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)