mysql 数据操作 多表查询 多表连接查询 笛卡尔积
1 交叉连接：不适用任何匹配条件。生成笛卡尔积
所有员工都和四个部门 做了对应关系
mysql> select * from employee,department; +----+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+------------+--------+------+--------+------+--------------+ | 1 | mike | male | 18 | 200 | 200 | 技术 | | 1 | mike | male | 18 | 200 | 201 | 人力资源 | | 1 | mike | male | 18 | 200 | 202 | 销售 | | 1 | mike | 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 | jack | male | 38 | 201 | 200 | 技术 | | 3 | jack | male | 38 | 201 | 201 | 人力资源 | | 3 | jack | male | 38 | 201 | 202 | 销售 | | 3 | jack | 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.00 sec)
有两张表 一张左表 一张右表 左表所有记录 都会跟右表对应一遍
优点： 简单粗暴 两张表对上
缺点：有些记录没有意义 没用
在笛卡尔积基础上筛选有关系的 加上限制关系
员工表的employee.dep_id == 部门表department.id 对上 才是有关联的记录
加上where 过滤掉没有意义的记录 取出那些有关联关系的记录
库名.表名.字段名
mysql> select * from employee,department where employee.dep_id = department.id; +----+-----------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+-----------+--------+------+--------+------+--------------+ | 1 | mike | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | jack | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | +----+-----------+--------+------+--------+------+--------------+ 5 rows in set (0.00 sec)
笛卡尔积 了解下就可以了 ，不要用where做连表操作，where只是单纯来过滤 ，mysql有专门提供连表查询语法
