请用sql语言帮我完成一个机动车辆信息管理系统,实现基本要求:1、包括五个或五个以上基本表,其中至少有一个是基于实体之间联系的基本表;2、实现驾驶员信息、机动车信息、员工等信息的增加、删除、修改、查询(包括五种或五种以上单条件查询和多条件复合查询)、统计功能;3、设置用户权限管理,不同权限的用户对不同信息操作的权限要区分;
时间: 2023-08-11 19:05:04 浏览: 54
好的,以下是一个简单的机动车辆信息管理系统的SQL语言实现:
1. 基本表的创建
CREATE TABLE Driver (
DriverID INT PRIMARY KEY,
DriverName VARCHAR(50),
DriverAge INT,
DriverGender VARCHAR(10),
DriverPhone VARCHAR(20),
DriverAddress VARCHAR(100),
DriverLicenseType VARCHAR(50),
DriverLicenseNum VARCHAR(50)
);
CREATE TABLE Vehicle (
VehicleID INT PRIMARY KEY,
VehicleType VARCHAR(50),
VehicleBrand VARCHAR(50),
VehicleColor VARCHAR(50),
VehicleModel VARCHAR(50),
VehicleYear INT,
VehicleLicenseNum VARCHAR(50),
VehicleOwnerID INT,
FOREIGN KEY (VehicleOwnerID) REFERENCES Driver (DriverID)
);
CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50),
EmployeeGender VARCHAR(10),
EmployeePhone VARCHAR(20),
EmployeeAddress VARCHAR(100),
EmployeeJobTitle VARCHAR(50),
EmployeeSalary FLOAT
);
CREATE TABLE User (
UserID INT PRIMARY KEY,
UserName VARCHAR(50),
UserPassword VARCHAR(50),
UserPermission INT
);
2. 驾驶员信息、机动车信息、员工等信息的增加、删除、修改、查询、统计功能的实现
-- 添加驾驶员信息
INSERT INTO Driver (DriverID, DriverName, DriverAge, DriverGender, DriverPhone, DriverAddress, DriverLicenseType, DriverLicenseNum)
VALUES (1, '张三', 30, '男', '13800000001', '北京市海淀区', 'A1', '110101198001010001');
-- 删除驾驶员信息
DELETE FROM Driver WHERE DriverID = 1;
-- 修改驾驶员信息
UPDATE Driver SET DriverPhone = '13800000002' WHERE DriverID = 1;
-- 查询驾驶员信息(单条件查询)
SELECT * FROM Driver WHERE DriverName = '张三';
-- 查询驾驶员信息(多条件复合查询)
SELECT * FROM Driver WHERE DriverGender = '男' AND DriverLicenseType = 'A1';
-- 统计驾驶员数量
SELECT COUNT(*) FROM Driver;
-- 添加机动车信息
INSERT INTO Vehicle (VehicleID, VehicleType, VehicleBrand, VehicleColor, VehicleModel, VehicleYear, VehicleLicenseNum, VehicleOwnerID)
VALUES (1, '小轿车', '宝马', '黑色', 'X5', 2018, '京A00001', 1);
-- 删除机动车信息
DELETE FROM Vehicle WHERE VehicleID = 1;
-- 修改机动车信息
UPDATE Vehicle SET VehicleLicenseNum = '京A00002' WHERE VehicleID = 1;
-- 查询机动车信息(单条件查询)
SELECT * FROM Vehicle WHERE VehicleType = '小轿车';
-- 查询机动车信息(多条件复合查询)
SELECT * FROM Vehicle WHERE VehicleBrand = '宝马' AND VehicleYear = 2018;
-- 统计机动车数量
SELECT COUNT(*) FROM Vehicle;
-- 添加员工信息
INSERT INTO Employee (EmployeeID, EmployeeName, EmployeeGender, EmployeePhone, EmployeeAddress, EmployeeJobTitle, EmployeeSalary)
VALUES (1, '李四', '男', '13800000003', '北京市朝阳区', '程序员', 10000);
-- 删除员工信息
DELETE FROM Employee WHERE EmployeeID = 1;
-- 修改员工信息
UPDATE Employee SET EmployeeSalary = 12000 WHERE EmployeeID = 1;
-- 查询员工信息(单条件查询)
SELECT * FROM Employee WHERE EmployeeName = '李四';
-- 查询员工信息(多条件复合查询)
SELECT * FROM Employee WHERE EmployeeGender = '男' AND EmployeeJobTitle = '程序员';
-- 统计员工数量
SELECT COUNT(*) FROM Employee;
3. 用户权限管理的实现
-- 添加用户信息
INSERT INTO User (UserID, UserName, UserPassword, UserPermission)
VALUES (1, 'admin', 'admin', 1);
-- 修改用户信息
UPDATE User SET UserPassword = '123456' WHERE UserID = 1;
-- 查询用户信息
SELECT * FROM User WHERE UserName = 'admin' AND UserPassword = '123456';
-- 设置用户权限(1为管理员权限,2为普通用户权限)
UPDATE User SET UserPermission = 2 WHERE UserID = 1;
-- 判断用户权限
SELECT UserPermission FROM User WHERE UserID = 1;
以上是一个简单的机动车辆信息管理系统的SQL语言实现,可以根据实际需求进行修改和完善。