mySQL第七章

第七章：

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

这道题刚开始打的时候第一个表与第二个表之间的链接我并没有使用外连接所以有些NULL空值并没有查到，对于我来说是一个易错点

1.1没有使用外连接的结果

1.1使用外连接之后的结果

1.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 = '公交二公司';
-- 有上一题的教训，这一题并没有什么错误，使用了左外连接与右外连接  

1.2代码运行结果

1.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;   
-- 与上题同理，这题也没有遇到什么问题

1.3代码运行结果

1.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  
-- 这题也没什么问题

1.4代码运行结果

2.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.1代码运行结果

2.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 = '王传华';
-- 商品件数打成了订单件数，没有其他问题

2.2代码运行结果

2.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;
-- 商品件数打成了订单件数，没有其他问题

2.3代码运行结果

2.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;
-- 商品件数打成了订单件数，没有其他问题

2.4代码运行结果，与2.3没有差距，但左外连接换成了右外连接

今日的mySQL学习大概可以用这两道大题概括,需要熟练的是多表连接查询和外连接的使用，需要注意的大概就是NULL空值的查询需要用到外连接,

多表连接语法如下

select freldlist 
from 
    table1 join table2 on table1.colunm1=table2.colunm2_1 
    join table3 on table2.colunm2_2=table3.colunm3
[where condition]

外连接语法如下

select 字段名
from 表名1
left|right|full [outer]
 join 表名2
 on 表名1.字段名1=表名2.字段名2

然后就是我觉得老师的随机选人还是有暗箱操作的