《MySQL数据库应用与管理项目化教程》课件-项目5-3跨表连接查询

项目五-3跨表连接查询任务目标任务一交叉连接查询任务二内连接查询任务三自然连接查询任务四外连接查询任务一交叉连接查询基本查询

主要内容:连接查询语法交叉连接规律交叉连接应用场合重点难点:交叉连接规律和应用场合基本查询查阅语法：官网文档：/doc/refman/8.0/en/join.html语法：SELECT[ALL|DISTINCT]select_expr[,select_expr...][FROMtable_references[WHEREwhere_condition][GROUPBY{col_name|expr|position},...[WITHROLLUP]][HAVINGwhere_condition][ORDERBY{col_name|expr|position}[ASC|DESC][LIMIT{[offset,]row_count|row_countOFFSEToffset}]]跨表数据查询ANSI连接语法:SELECT字段列表FROM表1[连接类型]JOIN表2ON连接条件WHERE条件表达式MySQL连接语法:（适合内连接、交叉连接,同SQLSERVER语法一致）SELECT字段列表FROM表名列表WHERE连接条件交叉连接交叉连接（CROSSJOIN）不带ON子句，即不带连接条件，返回的是两表的乘积，也叫笛卡尔积。即第一个表的每一行记录均与第二个表的每一行记录进行组合形成新的记录。语法一：SELECT字段列表FROM表1CROSSJOIN表2语法二格式：SELECT<字段列表>FROM<表1>,<表2>交叉连接观察交叉连接的结果例1：mysql>USEdb_shop;mysql>SELECT*FROMdepartment,staffer;例2：mysql>USEdb_shop;mysql>SELECT*FROMdepartmentCROSSJOINstaffer;考考你对职员表和部门表进行交叉连接任务二内连接查询基本查询

主要内容:内连接查询语法内连接规律内连接应用场合重点难点:内连接规律和应用场合内连接查询ANSI语法：SELECT字段列表FROM表1[INNER]JOIN表2ON连接条件WHERE条件表达式；作用：如果两个来源表的相关字段满足连接条件，则从这两个表中提取数据并组合成新的记录。MySQL连接语法:SELECT字段列表FROM表名列表WHERE连接条件；内连接查询例1：mysql>USEdb_shop;mysql>SELECTstaffer.*,department.*

FROMstafferINNERJOINdepartmentONdepartment.dept_id=staffer.dept_id；例2：mysql>USEdb_shop;mysql>SELECT*

FROMstaffer,department WHEREdepartment.dept_id=staffer.dept_id；内连接查询例3：mysql>USEdb_shop;mysql>SELECT*

FROMstaffer,department

WHEREdepartment.dept_id=staffer.dept_id ANDdept_name='销售部'；内连接查询可为来源表定义别名：表名[AS]别名例1：mysql>USEdb_shop;mysql>SELECTA.*,B.*

FROMstafferAINNERJOINdepartmentBONA.dept_id=B.dept_id；例2：mysql>USEdb_shop;mysql>SELECT*

FROMstafferA,departmentB WHEREA.dept_id=B.dept_id；内连接查询如果打算从两个以上的表中检索符合连接条件的记录，则需要使用嵌套形式的INNERJOIN运算。语法：SELECT字段列表FROM表1[INNER]JOIN表2ON连接条件[INNER]JOIN表3ON连接条件内连接查询例2.4.3查询订单中的商品名单、单价基本信息、库存信息及其供应商信息。方法一：mysql>USEdb_shop;

mysql>SELECT*FROMitem/*用ANSI语法*/

INNERJOINgoodsONitem.goods_id=goods.goods_id

INNERJOINsupplierONitem.supplier_id=supplier.supplier_id;方法一：mysql>USEdb_shop;

mysql>SELECT*FROMitem,goods,supplier

WHEREitem.goods_id=goods.goods_idAND

item.supplier_id=supplier.supplier_id;考考你对职员表和部门表进行内连接，显示职员的全部信息，显示部门名称；显示订单销售信息，直观显示出商品名称、供应商名称、职员名称；观察交叉连接与内连接的联系与区别；任务三自然连接查询基本查询

主要内容:自然连接查询语法自然连接规律自然连接应用场合重点难点:内连接规律和应用场合自连接ANSI语法：SELECT字段列表FROM表名1别名1INNERJOIN表名1别名2ON连接条件WHERE条件表达式作用：把某一个表中的行同该表中另外一些行连接起来，主要用在查询比较相同的信息方面自连接例2.4.5查询订单中至少提供了1和2号这两种商品的供应商。方法一：mysql>USEdb_shop;

mysql>SELECTA.item_id,A.goods_id,A.supplier_id,B.item_id,

B.supplier_id,B.goods_id

FROMitemAinnerJOINitemBONA.supplier_id=B.supplier_id

WHEREA.goods_id='1'ANDB.goods_id='2';方法二：mysql>USEdb_shop;

mysql>SELECTA.item_id,A.goods_id,A.supplier_id,B.item_id,

B.supplier_id,B.goods_id

FROMitemA,itemB

WHEREA.goods_id='1'ANDB.goods_id='2'ANDA.supplier_id=B.supplier_id;考考你显示所有订单的订单号、商品名称、供应商名称、数量；任务四外连接外连接

主要内容:左外连接右外连接完全外连接重点难点:外连接的应用场合左外连接ANSI语法：SELECT字段列表FROM表1LEFT[OUTER]JOIN表2ON连接条件WHERE条件表达式作用:查询结果将返回左表（表1）的全部满足WHERE条件的记录，若右表（表2）中有满足连接条件的记录，则右表返回对应的值，否则右表返回空值。当右表是左表的子集时使用左外连接。左外连接左连接例1:查看所有商品的订单状况；

mysql>USEdb_shopmysql>SELECT*FROMgoods

LEFTJOINitemONgoods.goods_id=item.goods_id；右外连接ANSI语法：SELECT字段列表FROM表1RIGHT[OUTER]JOIN表2ON连接条件WHERE条件表达式；作用:查询结果将返回右表（表2）的全部满足WHERE条件的记录，若左表（表1）中有满足连接条件的记录，则左表返回对应的值，否则左表返回空值。当左表是右表的子集时使用右外连接。右外连接例1:查看所有商品的订单状况；

mysql>USEdb_shop；mysql>SELECT*FROMitem

RIGHTJOINgoodsONgoods.goods_id=item.goods_id；考考你1、查看所有员工的部门情况。2、查看所有部门的员工情况。任务实施按下列操作完成db_shopping数据库表记录的跨表连接查询。1.选择db_shopping数据库，执行语句如下：mysql>USEdb_shopping;2.显示各部门职员，显示部门名称、员工名称、性别和电话，执行语句如下：mysql>SELECTdept_name,staff_name,sex,phoneFROMstafferINNERJOINdepartmentONdepartment.id=staffer.dept_id;3.显示各部门女职员，显示部门名称、员工名称、性别和电话，执行语句如下：mysql>SELECTdept_name,staff_name,sex,phoneFROMstafferINNERJOINdepartmentONdepartment.id=staffer.dept_idWHEREsex='F';4.显示所有部门的员工信息，显示部门名称、员工名称、员工性别、电话，无职员的部门也显示出来，执行语句如下：mysql>SELECTde