连接查询（内连接和外连接）

内连接语法:

SELECT fieldlist FROM  TABLE1 [INNER] JOIN TABLE2 ON TABLE1.colunmn1=TABLE2.column2 [WHERE CONDITION]

自然连接语法:

SELECT fieldlist FROM  TABLE1 NATURAL JOIN TABLE2
     [WHERE CONDITION]

多表查询语法:

 SELECT fieldlist FROM  TABLE1 JOIN TABLE2 
     ON TABLE1.colunmn1=TABLE2.column2_1 JOIN TABLE3 
     ON TABLE2.colunmn2_2=TABLE3.column3

外连接语法:

  SELECT 字段名称 FROM 表名1 LEFT|RIGHT|FULL[OUTER] 
     JOIN 表名2 ON 表名1.字段名1=表名2.字段名2  

1. 使用自然连接获取车辆型号含有“DD”字样的车辆信息和司机信息，要求车辆信息为全部列，司机信息中只需包含姓名和身份证

SELECT
	v.*,
	NAME licenseno 
FROM
	vehicle v
	NATURAL JOIN driver d 
WHERE
	model LIKE '%DD%'

2. 获取所有非空调车的车牌号、型号和司机姓名、所属线路的线路号、起点站和终点站信息

SELECT NAME
	,
	plateNo,
	model,
	lineNo,
	from_station,
	end_station 
FROM
	vehicle v
	JOIN driver d ON v.driverID = d.driverID
	JOIN line L ON v.lineID = L.lineID 
WHERE
	type = '非空调车'

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

SELECT
	plateNo,
	model,
	lineNo,
	from_station,
	end_station 
FROM
	vehicle v
	JOIN driver d ON v.driverID = d.driverID
	JOIN line L ON V.lineID = L.lineID 
WHERE
	type = '非空调车

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

```sql
SELECT NAME
	,
	licenseno,
	gender,
	phone 
FROM
	vehicle v
	JOIN driver d ON v.driverID = d.driverID
	JOIN line L ON v.lineID = L.lineID 
WHERE
	company = '公交二公司' 
GROUP BY
	NAME 
	--简单连接 
	SELECT NAME
	,
	plateNo,
	model,
	lineNo,
	from_station,
	end_station 
FROM
	vehicle v,
	driver d,
	line l 
WHERE
	v.driverID = d.driverID 
	AND v.lineID = l.lineID 
	AND company = '公交二公司'

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

SELECT NAME
	plateNo,
	phone,
	model,
	lineNo,
	gender,
	from_station,
	end_station 
FROM
	driver d
	LEFT JOIN vehicle v ON d.driverID = v.driverID
	LEFT JOIN line L ON v.lineID = L.lineID

6. 统计每一家公交公司所属线路的站点总数
SQL语句如下

SELECT
	company,
	count(*) 
FROM
	line_station LS,
	line L 
WHERE
	LS.lineNo = L.lineNo 
GROUP BY
	company 
ORDER BY
	count(*)

1. 获取订单 ID 为 4 的订购明细信息，要求输出商品名、单价和件数。

SELECT
	goodsName,
	quantity,
	unitPrice 
FROM
	orders o
	JOIN ordersdetail od ON o.ordersID = od.ordersID
	JOIN goods g ON od.goodsID = g.goodsID 
WHERE
	o.ordersID = 4;
--简单连接 
SELECT
goodsName,
unitPrice,
quantity 
FROM
	orders o,
	ordersdetail od,
	goods g 
WHERE
	o.ordersID = od.ordersID 
	AND od.goodsID = g.goodsID 
	AND o.ordersID = 4;

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

SELECT
	o.ordersID,
	ordersDate,
	goodsName,
	unitPrice,
	quantity 
FROM
	customer c
	JOIN orders o ON c.customerID = o.customerID
	JOIN ordersdetail od ON o.ordersID = od.ordersID
	JOIN goods g ON od.goodsID = g.goodsID 
WHERE
	cName = '王传华'

3. 使用左连接获取所有客户的基本信息以及订购信息，要求输出客户姓名、电话、订单 ID 和下单时间。

SELECT
	cName,
	ordersID,
	ordersDate,
	phone 
FROM
	customer c
	LEFT JOIN orders o ON c.customerID = o.customerID

4. 使用右连接获取所有客户的基本信息以及订购信息，要求输出客户姓名、电话、订单 ID 和下单时间。

SELECT
	cName,
	ordersID,
	ordersDate,
	phone 
FROM
	orders o
	RIGHT JOIN customer c ON c.customerID = o.customerID