2021-06-01

第七章
内连接的语法如下：
SELECT fieldlidt FROM rable1 【INNER】 JOIN table2 ON rable1.column=table.column2【where condition】

三表连接查询的语法：
SELECT fieldlidt
FROM rable1
JOIN table2 ON table1.coulumn1=table2.column2_1
JOIN table3 ON table2.coulumn2_2=table3.column3
【where condition】

两表连接查询语法格式：
SELECT fieldlist FROM rable1,table2
WHERE rable1.column1=table2.column2
【and 其他条件】

一、查询bus数据库，实现如下需求：
1.获取所有非空调车的车牌号、型号和司机姓名、所属线路的线路号、起点站和终点站信息；

SELECT
	V.platNO 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 = '非空调车';

 运行结果：

 这个题目容易出现只打出来少部分的非空调车的情况，要小心。

2.获取公交二公司所有司机信息。要求输出司机姓名、身份证、性别和电话；

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 = '公交二公司';

运行结果：

3.查询所有非空调车的车牌号、型号、线路号、起点站和终点站；

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;

运行结果：

4.显示所有线路及其所配车辆基本信息，并查询车辆所配司机相关信息，要求输出司机姓名、性别、电话、车牌号、型号、线路号、起点站和终点站；

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数据库，实现如下需求：
1.获取订单 ID 为 4 的订购明细信息，要求输出商品名、单价和件数。
得出代码：

SELECT
	O.ordersID AS 订单 ID,
	G.category AS 商品名,
	G.unitPrice AS 商品单价,
	G.saleCount AS 商品件数 
FROM
	goods AS G
	JOIN ordersdetail AS O G.goodsID = O.goodsID 
WHERE
	O.ordersID = '4';

2.获取客户“王传华”所下订单详细信息，要求显示客户姓名、订单 ID、下单日期、商品名、单价和件数。
得出代码：

SELECT
	C.cName AS 客户姓名,
	O.customerID AS 订单 ID,
	O.ordersDate AS 下单日期 G.category AS 商品名,
	G.unitPrice AS 商品单价,
	G.saleCount AS 商品件数 
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 = '王传华';

3.使用左接获取所有客户的基本信息以及订购信息，要求输出客户姓名、电话、订单 ID 和下单时间。
得出代码：
SELECT
C.cName AS 客户姓名,
C.phone AS 客户电话,
O.customerID AS 订单 ID,
O.ordersDate AS 下单时间
FROM
orders AS O
LEFT JOIN customer AS C ON O.customerID = C.customerID;
4.使用右接获取所有客户的基本信息以及订购信息，要求输出客户姓名、电话、订单 ID 和下单时间。
得出代码：

SELECT
	C.cName AS 客户姓名,
	C.phone AS 客户电话,
	O.customerID AS 订单 ID,
	O.ordersDate AS 下单时间 
FROM
	customer AS C
	RIGHT JOIN orders AS O ON C.customerID = O.customerID;

注意:打代码的时候要明确表名不然结果会很离谱。