一个简单的货运站汽车管理数据库

以下在ubuntu14.04 mysql 5.5.62 验证正常。

 

1.数据库，卡车表，货运站表，汽车型号表创建，定义必要的实例完整性和参照完整性。

CREATE DATABASE IF NOT EXISTS 车管理;
USE 车管理;

CREATE TABLE IF NOT EXISTS 货运站表(
货运站编号 VARCHAR(128) PRIMARY KEY,
地址  VARCHAR(128),
联系电话   VARCHAR(128),
负责人 VARCHAR(128)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO 货运站表 (货运站编号,地址,联系电话,负责人) VALUES ('A01','北京市东城区花市大街111号','010-67301234','张华');

INSERT INTO 货运站表 (货运站编号,地址,联系电话,负责人) VALUES ('A02','北京市海淀花园路101号','010-64248892','李玉明');

INSERT INTO 货运站表 (货运站编号,地址,联系电话,负责人) VALUES ('A03','北京市东城区花市大街111号','010-45363542','周强永');

INSERT INTO 货运站表 (货运站编号,地址,联系电话,负责人) VALUES ('A04','北京市东城区花市大街111号','010-54634645','王桂平');

CREATE TABLE IF NOT EXISTS 汽车型号表(
类型 VARCHAR(128) PRIMARY KEY,
汽车型号 VARCHAR(10)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO 汽车型号表 (类型,汽车型号) VALUES ('11','DJS130');

INSERT INTO 汽车型号表 (类型,汽车型号) VALUES ('22','DLS121');

CREATE TABLE IF NOT EXISTS 卡车表(
车号 VARCHAR(128) PRIMARY KEY,
货运站编号 VARCHAR(128),
类型  VARCHAR(128),
总行程   FLOAT(2),
购入日期 VARCHAR(128)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE 卡车表 ADD CONSTRAINT FK_FSN FOREIGN KEY(货运站编号) REFERENCES 货运站表(货运站编号);

ALTER TABLE 卡车表 ADD CONSTRAINT FK_TYPE FOREIGN KEY(类型) REFERENCES 汽车型号表(类型);

INSERT INTO 卡车表 (车号,货运站编号,类型,总行程,购入日期)VALUES('1001','A01','11',59002.7,'10/06/01');

INSERT INTO 卡车表 (车号,货运站编号,类型,总行程,购入日期)VALUES('1002','A02','22',54523.8,'17/08/03');

INSERT INTO 卡车表 (车号,货运站编号,类型,总行程,购入日期)VALUES('1003','A03','22',32116.6,'16/12/05');

INSERT INTO 卡车表 (车号,货运站编号,类型,总行程,购入日期)VALUES('1004','A02','22',3256.9,'12/04/25');

2.联合查找 '李玉明' 管理卡车号 及 总行程。

SELECT 车号,总行程,负责人 FROM 卡车表 JOIN 货运站表 ON 卡车表.货运站编号=货运站表.货运站编号 WHERE 负责人='李玉明';

3.

SELECT 货运站表.货运站编号,COUNT(车号) AS 车数 ,ROUND(AVG(总行程),1) AS 平均总行程 FROM 卡车表  RIGHT JOIN 货运站表 ON 卡车表.货运站编号=货运站表.货运站编号 GROUP BY 货运站表.货运站编号 ORDER BY 平均总行程 DESC;
4.
UPDATE 卡车表 K  LEFT JOIN 汽车型号表 Q ON K.类型=Q.类型 SET K.货运站编号='A04' WHERE 汽车型号='DLS121' AND 总行程<5000;
5.
SELECT 车号,购入日期,联系电话,汽车型号 FROM 卡车表 K LEFT JOIN  货运站表 H  ON K.货运站编号=H.货运站编号 LEFT JOIN 汽车型号表 Q ON K.类型=Q.类型 WHERE 汽车型号='DJS130';