2021-06-01

第七章 链接查询题目

三表链接的语法：

select  Fieldlist  from  TAB1  
  join TAB2on  TAB1.COL1=TAB2.COL1
  join  TAB3  on  TAB2.COL2=TAB3.COL3  
[where<条件表达式>]

一、查询bus数据库

第一小问：

-- 第一题
SELECT
	V.plateNO AS 车牌号,
	V.model AS 型号,
	V.type AS 车类型,
	D.NAME AS 司机姓名,
	L.lineNo AS 线路号,
	L.from_station AS 起点站,
	L.end_station AS 终点站 
FROM
	vehicle AS V
	LEFT JOIN driver AS D ON V.driverID = D.driverID
	JOIN line AS L ON L.lineID = V.lineID 
WHERE
	V.type = '非空调车';--

结果如下：

第二小问：

-- 第二题
SELECT
	D.NAME AS 司机姓名,
	D.licenseNO AS 司机身份证,
	D.gender AS 司机性别,
	D.phone AS 司机电话,
	L.company AS 公交公司 
FROM
	vehicle AS V
	LEFT JOIN line AS L ON V.lineID = L.lineID
	RIGHT JOIN driver AS D ON D.driverID = V.driverID 
WHERE
	L.company = '公交二公司';--

结果如下

第三小问：

-- 第三题
SELECT 
    V.plateNo AS 车牌号,
    V.model AS 型号,
    L.lineNo AS 线路号,
    L.from_station AS 起点站,
    L.end_station AS 终点站
FROM 
    vehicle AS V LEFT JOIN line AS L ON V.lineID = L.lineID;

结果如下

第四小问：

-- 第四题
SELECT
	D.NAME AS 司机姓名,
	D.gender AS 司机性别,
	D.Phone AS 司机电话,
	V.plateNo AS 车牌号,
	V.model AS 型号,
	L.lineNo AS 线路号,
	L.from_station AS 起始站,
	L.end_station AS 终点站 
FROM
	vehicle AS V
	JOIN line AS L ON V.lineID = L.lineID
	RIGHT JOIN driver AS D ON D.driverID = V.driverID;

结果如下

二、查询easyshopping数据库

第一小问：

-- 第一题
SELECT
	O.ordersID,
	G.category,
	G.unitPrice,
	G.saleCount
FROM
	goods AS G
	JOIN ordersdetail AS O G.goodsID = O.goodsID 
WHERE
	O.ordersID = '4';--

第二小问：

-- 第二题
SELECT
	C.cName,
	O.customerID,
	O.ordersDate,
	G.category,
	G.unitPrice,
	G.saleCount 
FROM
	customer AS C
	JOIN orders AS O ON C.customerID = O.customerID
	JOIN ordersdetail AS O2 ON O2.ordersID = O.ordersID
	JOIN goods AS G ON G.goodsID = O2.goodsID 
WHERE
	C.cName = '王传华'; --

第三小问：

-- 第三题
SELECT
	C.cName,
	C.phone,
	O.customerID,
	O.ordersDate 
FROM
	orders AS O
	LEFT JOIN customer AS C ON O.customerID = C.customerID;--

第四小问：

-- 第四题
SELECT
	C.cName,
	C.phone,
	O.customerID,
	O.ordersDate 
FROM
	customer AS C
	RIGHT JOIN orders AS O ON C.customerID = O.customerID;