我要学做网站,页面设计的作用,网站悬浮图标怎么做,开发公司名字大全时光在不经意间#xff0c;总是过得出奇的快。小暑已过#xff0c;进入中暑#xff0c;太阳更加热烈的绽放着ta的光芒#xff0c;...在外面被太阳照顾的人们啊#xff0c;你们都是勤劳与可爱的人啊。在房子里已各种姿势看我这篇这章的你#xff0c;既然点了进来#xff…时光在不经意间总是过得出奇的快。小暑已过进入中暑太阳更加热烈的绽放着ta的光芒...在外面被太阳照顾的人们啊你们都是勤劳与可爱的人啊。在房子里已各种姿势看我这篇这章的你既然点了进来那就由我继续带你回顾MySql的知识吧回顾练习资料girls库以及两张表的脚本链接: https://pan.baidu.com/s/1bgFrP7dBBwk3Ao755pU4Qg 提取码: ihg7引题:笛卡尔现象先来观看一下两张表。SELECT * FROM boys;SELECT * FROM beauty;SELECT NAME,boyname FROM boys,beauty;最终结果:12*448行#进阶6:连接查询含义:又称多表查询,当查询的字段来自于多个表时就会用到连接查询笛卡儿积现象: 表1 有m行,表2有n行,结果m*n行产生原因:没有有效的连接条件解决方法:添加有效的连接条件连接分类:按年代分类:SQL1992标准(192标准):仅支持内连接SQL1999标准(199标准) [推荐]:支持内连接外联结(左外与右外)交叉连接按功能分类:内连接:等值连接非等值连接自联结外连接:左外连接右外连接全外连接交叉连接:左外连接右外连接全外连接交叉连接:SELECT NAME,boyname FROM boys,beautyWHERE beauty.boyfriend_id boys.id;#一.SQL192标准#1.等值连接①多表等值连接的结果为多表的交集部分②n表连接,至少需要n-1个连接条件③多表的顺序没有要求④一般需要为表起别名⑤可以搭配前面介绍的所有子句使用,比如,排序,分组,筛选。#多表查询先匹配在筛选#案例1.查询员工名和对应的部门名。SELECT first_name AS 名,department_name AS 部门名FROM employees,departmentsWHERE employees.department_id departments.department_id;#案例2.查询员工名,工种号工种名。对于两张表共有的字段需要加表名作限定不然会报错。错误示例:SELECT first_name AS 名,employees.job_id AS 工种号,job_title AS 工种名FROM employees,jobsWHERE employees.job_id jobs.job_id;#2.为表起别名①提高语句的简洁度②区分多个重命名的字段注意:如果为表起了别名,则查询的字段就不能使用原来的表名去限定SELECT first_name AS 名,e.job_id AS 工种号,job_title AS 工种名FROM employees AS e,jobs AS jWHERE e.job_id j.job_id;给表起了别名再用表完整名子做限定会报错不允许。根据执行顺序走先走FROM走完FROM后就用别名的相当于生成了一个虚拟的视图不再认原来的表名。#3.两个表名的顺序是否可以调换是可以调换的。SELECT first_name AS 名,e.job_id AS 工种号,job_title AS 工种名FROM jobs AS j,employees AS eWHERE e.job_id j.job_id;#4.可以加筛选#案例3.查询有奖金的员工名,部门名。SELECT first_name AS 名,department_name AS 部门名,commission_pct AS 奖金FROM employees AS e,departments AS dWHERE e.department_idd.department_idAND commission_pct IS NOT NULL;#AND e.commission_pct IS NOT NULL;#案例4.查询城市名中第二个字符为o对应的城市名与部门名。SELECT city AS 城市,department_name AS 部门名FROM locations AS l,departments AS dWHERE l.location_id d.location_idAND city LIKE _o%;#5.可以加分组#案例1.查询每个城市的部门个数。SELECT city AS 城市,COUNT(department_id) AS 个数FROM locations AS l,departments AS dWHERE l.location_id d.location_idGROUP BY l.city;#案例2.查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资。#查询的时候不确定把两个列都加上。SELECT commission_pct AS 奖金,department_name AS 部门名,d.manager_id AS 领导编号,MIN(salary) AS 最低工资FROM employees AS e,departments AS dWHERE e.department_id d.department_idAND commission_pct IS NOT NULLGROUP BY department_name,d.manager_id;#6.可以加排序#案例1:查询每个工种的工种名和员工的个数,并且按员工个数降序。SELECT j.job_title AS 工种名,COUNT(employee_id) AS 个数FROM employees AS e,jobs AS jWHERE e.job_id j.job_idGROUP BY job_titleORDER BY 个数 DESC;#7.三表连接#案例1.查询员工名,部门名与所在的城市SELECT first_name AS 名,d.manager_id AS 部门名,city AS 城市FROM employees AS e,departments AS d,locations AS lWHERE e.department_id d.department_idAND d.location_id l.location_id;#案例2.查询员工名,部门名与所在的城市,城市以s开头。SELECT first_name AS 名,d.manager_id AS 部门名,city AS 城市FROM employees AS e,departments AS d,locations AS lWHERE e.department_id d.department_idAND d.location_id l.location_idAND city LIKE s%;#案例3.查询员工名,部门名与所在的城市,城市以s开头,按姓名降序排列。SELECT first_name AS 名,d.manager_id AS 部门名,city AS 城市FROM employees AS e,departments AS d,locations AS lWHERE e.department_id d.department_idAND d.location_id l.location_idAND city LIKE s%ORDER BY department_name DESC;新增一张工资等级表。CREATE TABLE job_grades(grade_level VARCHAR(3),lowest_sal int,highest_sal int);INSERT INTO job_gradesVALUES (A, 1000, 2999);INSERT INTO job_gradesVALUES (B, 3000, 5999);INSERT INTO job_gradesVALUES(C, 6000, 9999);INSERT INTO job_gradesVALUES(D, 10000, 14999);INSERT INTO job_gradesVALUES(E, 15000, 24999);INSERT INTO job_gradesVALUES(F, 25000, 40000);#2.非等值连接,(范围判断)#案例1.查询员工的工资和工资级别。SELECT salary AS 工资,grade_level AS 等级FROM employees AS e,job_grades AS gWHERE salary BETWEEN g.lowest_sal AND g.highest_sal;#案例2.查询员工的工资和工资级别,展示出A级别的员工。SELECT salary AS 工资,grade_level AS 等级FROM employees AS e,job_grades AS gWHERE salary BETWEEN g.lowest_sal AND g.highest_salAND g.grade_level A;#3.自联接[自己连接自己]#案例1.查询员工名和上级的名称.SELECT e.employee_id,e.last_name AS 员工,m.employee_id,m.last_name AS 领导FROM employees e,employees mWHERE e.manager_id m.employee_id;经过了以上的示例相信你对多表查询已经有了一个了解赶快动动你的小手手来练习一下吧o(^▽^)o到此这篇关于MySQL多表查询详解上的文章就介绍到这了,更多相关MySQL多表查询内容请搜索易采站长站以前的文章或继续浏览下面的相关文章希望大家以后多多支持易采站长站
