iis5.1 建立网站,庞各庄网站建设,有没有免费的资源可以在线观看,wordpress数据录入平台多表查询#xff0c;也称为关联查询#xff0c;是指两个表或多个表一起完成查询操作
前提条件#xff0c;这些一起查询的表之间是有关系的#xff08;一对一、一对多#xff09;#xff0c;它们之间一定是有关联字段的。这个关联字段可能建立了外键#xff0c;也可能没…多表查询也称为关联查询是指两个表或多个表一起完成查询操作
前提条件这些一起查询的表之间是有关系的一对一、一对多它们之间一定是有关联字段的。这个关联字段可能建立了外键也可能没有建立外键。比如员工表和部门表这两个表依靠“部门编号”进行关联
一、一个案例引发的多表连接
1案例说明 2笛卡尔积或交叉连接的理解
笛卡尔积是一个数学运算。假设我有两个集合X和Y那么X和Y的笛卡尔积就是X和Y的所有可能组合也就是说第一个对象来自于X的的所有可能第二个对象来自于Y的所有可能。组合的个数即为两个集合中元素个数的乘积SQL92中笛卡尔积也称为交叉连接英文是CROSS JOIN。在SQL99中也是使用CROSS JOIN表示交叉连接。它的作用就是把任意表进行连接即使这两张表不相关。在MySQL如下情况会出现笛卡尔积
3案例分析与问题解决
多表查询的正确方式需要有连接条件就是先把表格交叉连接然后筛选出符合条件的笛卡尔积的错误会在下面条件下产生 省略多个表的连接条件或关联条件连接条件或关联条件无效所有表中的所有行互相连接因为employees表和departments表中都有字段department_id所以这样会导致错误如果查询语句中出现了多个表中都存在的字段则必须指明该字段所在的表。建议从SQL优化的角度建议多表查询时每个字段前都指明其所在的表可以在FROM中给表起别名在SELECT和WHERE中使用表的别名如果给表起了别名一旦在SELECT或WHERE中使用表名的话则必须使用表的别名而不能再使用表的原名练习查询员工的employee_idlast_namedepartment_namecity总结如果有n个表实现多表查询则至少需要n-1个连接条件
二、多表查询分类讲解
1等值连接 VS 非等值连接
非等值连接的例子
2自连接 VS 非自连接
非自连接不同的表进行的连接操作自连接的例子连接的表是同一张表给同一张表起不同的名字
3内连接 VS 外连接
内连接当合并具有同一列的两个以上的表的行结果集中不包含一个表与另一个表不匹配的行比如有的员工没有被分配部门他的部门id是null所以没有出现在结果中外连接当合并具有同一列的两个以上的表的行结果集中除了包含一个表与另一个表匹配的行之外还查询到了左表或右表中不匹配的行外连接的分类1左外连接2右外连接3满外连接左外连接两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行这种连接称为左外连接。如果是左外连接则连接条件中左边的表也称为主表右边的表称为从表右外连接两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行这种连接称为右外连接。如果是右外连接则连接条件中右边的表也称为主表左边的表称为从表SQL92语法实现内连接的方式见上看一中的2略SQL92语法实现外连接的方式使用但是MySQL不支持SQL92中外连接的写法。因为左边的数据更多所以写在右边用以补齐
三、SQL99语法实现多表查询
SQL99语法实现内连接JOIN前面可以省略INNERSQL99语法实现外连接必须标明左外连接还是右外连接上面这个例子中如果是左外连接那么有的员工没有被分配部门他的部门id就为null因为是左外连接所以他被保留。如果是右外连接那么有的部门没有员工即它的部门id没有在员工表中出现过因为是右外连接所以它被保留满外连接MySQL不支持FULL OUTER JOIN
四、UNION的使用
合并查询结果利用UNION关键字可以给出多条SELECT语句并将它们的结果组合成单个结果集。合并时两个表对应的列数和数据类型必须相同并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔UNION操作符返回两个查询结果集的并集去除重复记录UNION ALL操作符返回两个查询结果集的并集。对于两个结果集的重复部分不去重注意执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据或者不需要去除重复数据则尽量使用UNION ALL语句以提高查询数据的效率
五、7种SQL JOINS的实现
内连接的实现SELECT e.employee_id,d.department_name
FROM employees e JOIN departments d
ON e.department_id d.department_id; 左外连接的实现SELECT e.employee_id,d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id d.department_id; 右外连接的实现SELECT e.employee_id,d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id d.department_id; 图一的实现SELECT e.employee_id,d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id d.department_id
WHERE e.department_id IS NULL; 图二的实现SELECT e.employee_id,d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id d.department_id
WHERE e.employee_id IS NULL; 图三的实现SELECT employee_id,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id d.department_id
WHERE e.department_id IS NULLUNION ALLSELECT employee_id,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id d.department_id
WHERE e.employee_id IS NULL; 满外连接的实现SELECT employee_id,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id d.department_idUNION ALLSELECT employee_id,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id d.department_id
WHERE e.employee_id IS NULL;
六、SQL99语法新特性
1自然连接
SQL99在SQL92的基础上提供了一些特殊语法比如NATURAL JOIN用来表示自然连接。我们可以把自然连接理解为SQL92中的等值连接。它会帮你自动查询两张连接表中所有相同的字段然后进行等值连接在SQL92中这两张表有两个相同字段 SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id d.department_id
AND e.manager_id d.manager_id; 在SQL99中 SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;
2USING连接
当我们进行连接的时候SQL99还支持使用USING指定数据表里的同名字段进行等值连接。但是USING只能配合JOIN一起使用举例
七、课后练习
显示所有员工的姓名、部门号和部门名称 #显示所有员工的姓名、部门号和部门名称
SELECT e.last_name,e.department_id,d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id d.department_id; 查询90号部门员工的job_id和90号部门员工的location_id #查询90号部门员工的job_id和90号部门员工的location_id
SELECT e.job_id,d.location_id
FROM employees e JOIN departments d
ON e.department_id d.department_id
WHERE e.department_id 90; 选择所有有奖金的员工的 last_name , department_name , location_id , city #选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT e.last_name,d.department_name,l.location_id,l.city
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id d.department_id
LEFT OUTER JOIN locations l
ON d.location_id l.location_id
WHERE e.commission_pct IS NOT NULL; 选择city在Toronto的员工的last_name , job_id , department_id , department_name #选择city在Toronto的员工的last_name , job_id , department_id , department_name
SELECT e.last_name,e.job_id,d.department_id,d.department_id
FROM employees e JOIN departments d
ON e.department_id d.department_id
JOIN locations l
ON d.location_id l.location_id
WHERE l.city Toronto; 查询员工所在的部门名称、部门地址、姓名、工作、工资其中员工所在部门的部门名称为’Executive’ #查询员工所在的部门名称、部门地址、姓名、工作、工资其中员工所在部门的部门名称为’Executive’
SELECT e.last_name,e.job_id,e.salary,d.department_name,l.street_address
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id d.department_id
LEFT OUTER JOIN locations l
ON d.location_id l.location_id
WHERE d.department_name Executive; 选择指定员工的姓名、员工号以及他的管理者的姓名、员工号 #选择指定员工的姓名、员工号以及他的管理者的姓名、员工号
SELECT emp.last_name employees,emp.employee_id Emp#,mgr.last_name manager,mgr.employee_id Mgr#
FROM employees emp LEFT OUTER JOIN employees mgr
ON emp.manager_id mgr.employee_id; 查询哪些部门没有员工 #查询哪些部门没有员工
SELECT d.department_id,d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id d.department_id
WHERE e.employee_id IS NULL; 查询哪个城市没有部门 #查询哪个城市没有部门
SELECT l.city
FROM departments d RIGHT OUTER JOIN locations l
ON d.location_id l.location_id
#总结where相当于是在on的基础上再筛选
WHERE d.location_id IS NULL; 查询部门名为 Sales 或 IT 的员工信息 #查询部门名为 Sales 或 IT 的员工信息
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e JOIN departments d
ON e.department_id d.department_id
WHERE d.department_name IN(Sales,IT);
该笔记根据尚硅谷的MySQL课程整理
