目录
- 一、商品分类表 ProductClass
- 二、客户表 Customer记录
- 三、商品表 Product
- 四、员工表Employee
- 五、订单主表 OrderMaster
- 六、订单明细表 OrderDetail
一、商品分类表 ProductClass
1. 创建 ProductClass表
CREATE TABLE `ProductClass` ( `classNo` char(3) NOT NULL, `className` varchar(40) NOT NULL, PRIMARY KEY (`classNo`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;2. 插入数据
INSERT INTO `ProductClass` VALUES ('001', '手机');INSERT INTO `ProductClass` VALUES ('002', '电脑');INSERT INTO `ProductClass` VALUES ('003', '耳机');INSERT INTO `ProductClass` VALUES ('004', '小米手环');INSERT INTO `ProductClass` VALUES ('005', '摄像头');3. 展示
二、客户表 Customer记录
1. 创建 Customer表
CREATE TABLE `Customer` ( `customerNo` char(9) NOT NULL, `customer` char(40) NOT NULL, `address` varchar(40) NOT NULL, `telephone` varchar(20) NOT NULL, `zip` char(6) NOT NULL, PRIMARY KEY (`customerNo`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;2. 插入数据
INSERT INTO `Customer` VALUES ('C2023001', 'cool-wangtongzhou有限公司', '广州市', '011-1111', '1001');INSERT INTO `Customer` VALUES ('C2023002', '统一股份有限公司', '天津市', '022-2222', '1002');INSERT INTO `Customer` VALUES ('C2023003', '上海生物研究室', '上海市', '033-3333', '1003');INSERT INTO `Customer` VALUES ('C2023004', '腾讯有限公司', '深圳市', '044-4444', '1004');INSERT INTO `Customer` VALUES ('C2023005', '华为有限公司', '深圳市', '055-5555', '1005');INSERT INTO `Customer` VALUES ('C2023006', '字节跳动有限公司', '北京市', '066-6666', '1006');INSERT INTO `Customer` VALUES ('C2023007', '阿里巴巴有限公司', '杭州市', '077-7777', '1007');INSERT INTO `Customer` VALUES ('C2023008', '七牛云有限公司', '上海市', '088-8888', '1008');mysql> insert into Customer values ('C2023002', '统一股份有限公司','022-3566021',’天津市‘, 1002);ERROR 1054 (42S22): Unknown column '’天津市‘' in 'field list'如果遇到这种问题，就检查一下天津市对应的单引号是不是英文格式的，这个很容易弄混。
3. 展示
三、商品表 Product
1. 创建 Product表
CREATE TABLE `Product` ( `product` char(9) NOT NULL, `productName` varchar(40) NOT NULL, `classNo` char(3) NOT NULL, `productPrice` decimal(7,2) NOT NULL, `productStock` decimal(7,2) NOT NULL, `productMinstock` decimal(7,2) NOT NULL, PRIMARY KEY (`product`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;2. 插入数据
INSERT INTO `Product` VALUES ('P2023001', '手机一号', '1', 2798.00, 100.00, 10.00);INSERT INTO `Product` VALUES ('P2023002', '手机二号', '1', 3999.00, 100.00, 10.00);INSERT INTO `Product` VALUES ('P2023003', '手机三号', '1', 3999.00, 50.00, 5.00);INSERT INTO `Product` VALUES ('P2023004', '电脑一号', '2', 3999.00, 10.00, 6.00);INSERT INTO `Product` VALUES ('P2023005', '电脑二号', '2', 3999.00, 15.00, 5.00);INSERT INTO `Product` VALUES ('P2023006', '电脑三号', '2', 6999.00, 35.00, 5.00);INSERT INTO `Product` VALUES ('P2023007', '耳机一号', '3', 2399.00, 500.00, 50.00);INSERT INTO `Product` VALUES ('P2023008', '耳机二号', '3', 1499.00, 500.00, 50.00);INSERT INTO `Product` VALUES ('P2023009', '手环一号', '4', 399.00, 210.00, 30.00);INSERT INTO `Product` VALUES ('P2023010', '手环二号', '4', 199.00, 32.00, 23.00);INSERT INTO `Product` VALUES ('P2023011', '摄像头一号', '5', 233.00, 23.00, 21.00);3. 展示
四、员工表Employee
1. 创建 Employee表
CREATE TABLE `Employee` ( `employeeNo` char(8) NOT NULL, `employeeName` varchar(10) NOT NULL, `sex` varchar(1) NOT NULL, `birthday` datetime NOT NULL, `address` varchar(50) NOT NULL, `telephone` varchar(20) NOT NULL, `hireDate` datetime DEFAULT NULL, `department` varchar(30) NOT NULL, `headShip` varchar(30) NOT NULL, `salary` decimal(8,2) NOT NULL, PRIMARY KEY (`employeeNo`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;2. 插入数据
INSERT INTO `Employee` VALUES ('E2023001', '员工一', 'M', '1985-01-01 00:00:00', '南京市', '1111', '2023-01-01 00:00:00', '财务科', '科长', 11000.00);INSERT INTO `Employee` VALUES ('E2023002', '员工二', 'F', '1986-02-02 00:00:00', '上海市', '2222', '2023-02-02 00:00:00', '业务科', '职员', 3000.00);INSERT INTO `Employee` VALUES ('E2023003', '员工三', 'F', '1987-03-03 00:00:00', '南昌市', '3333', '2023-03-03 00:00:00', '业务科', '职员', 3000.00);INSERT INTO `Employee` VALUES ('E2023004', '员工四', 'M', '1988-04-04 00:00:00', '南昌市', '4444', '2023-04-04 00:00:00', '业务科', '职员', 5000.00);INSERT INTO `Employee` VALUES ('E2023005', '员工五', 'F', '1989-05-05 00:00:00', '上海市', '5555', '2023-05-05 00:00:00', '办公室', '主任', 9000.00);INSERT INTO `Employee` VALUES ('E2023006', '员工六', 'M', '1990-06-06 00:00:00', '广州市', '6666', '2023-06-06 00:00:00', '财务科', '会计', 10000.00);3. 展示
五、订单主表 OrderMaster
1. 创建 OrderMaster表
CREATE TABLE `ordermaster` ( `orderNo` char(12) NOT NULL, `customerNo` char(9) NOT NULL, `salerNo` char(8) NOT NULL, `orderDate` datetime NOT NULL, `orderSum` decimal(9,2) NOT NULL, `invoiceNo` char(10) NOT NULL, PRIMARY KEY (`orderNo`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;2. 插入数据
INSERT INTO `ordermaster` VALUES ('2023001', 'C2023001', 'E2023001', '2023-01-01 00:00:00', 1111.00, 'I001');INSERT INTO `ordermaster` VALUES ('2023002', 'C2023002', 'E2023002', '2023-02-02 00:00:00', 2222.00, 'I002');INSERT INTO `ordermaster` VALUES ('2023003', 'C2023003', 'E2023003', '2023-03-03 00:00:00', 3333.00, 'I003');INSERT INTO `ordermaster` VALUES ('2023004', 'C2023004', 'E2023004', '2023-04-04 00:00:00', 4444.00, 'I004');INSERT INTO `ordermaster` VALUES ('2023005', 'C2023005', 'E2023005', '2023-05-05 00:00:00', 5555.00, 'I005');INSERT INTO `ordermaster` VALUES ('2023006', 'C2023006', 'E2023006', '2023-06-06 00:00:00', 6666.00, 'I006');3. 展示
六、订单明细表 OrderDetail
1. 创建 OrderDetail表
CREATE TABLE `OrderDetail` ( `orderNo` char(12) NOT NULL, `productNo` char(9) NOT NULL, `quantity` int NOT NULL, `price` decimal(7,2) NOT NULL, PRIMARY KEY (`orderNo`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;2. 插入数据
INSERT INTO `OrderDetail` VALUES ('2023001', 'P2023001', 1, 3000.00);INSERT INTO `OrderDetail` VALUES ('2023002', 'P2023002', 2, 4000.00);INSERT INTO `OrderDetail` VALUES ('2023003', 'P2023003', 3, 3000.00);INSERT INTO `OrderDetail` VALUES ('2023004', 'P2023004', 4, 5000.00);INSERT INTO `OrderDetail` VALUES ('2023005', 'P2023005', 5, 6000.00);INSERT INTO `OrderDetail` VALUES ('2023006', 'P2023006', 6, 2000.00);3. 展示
来源地址：https://blog.csdn.net/e2788666/article/details/131330119
