MySQL实训项目——餐饮点餐系统

       项目简介：餐饮点餐系统是一款为餐厅和顾客提供便捷点餐服务的在线平台。通过该系统，餐厅能够展示其菜单，顾客可以浏览菜品，并将其加入购物车或直接下单。系统还提供了订单管理功能，方便餐厅跟踪和处理顾客的订单。

1. 建表DDL

CREATE TABLE `carts` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '购物车ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `restaurant_id` int(11) NOT NULL COMMENT '餐厅ID',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `restaurant_id` (`restaurant_id`)
) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COMMENT='购物车表';

CREATE TABLE `cart_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '购物车项ID',
  `cart_id` int(11) NOT NULL COMMENT '购物车ID',
  `dish_id` int(11) NOT NULL COMMENT '菜品ID',
  `quantity` int(11) NOT NULL COMMENT '数量',
  PRIMARY KEY (`id`),
  KEY `cart_id` (`cart_id`),
  KEY `dish_id` (`dish_id`)
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COMMENT='购物车项表';

CREATE TABLE `dishes` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜品ID',
  `name` varchar(100) NOT NULL COMMENT '菜品名称',
  `description` text COMMENT '菜品描述',
  `price` decimal(10,2) NOT NULL COMMENT '菜品价格',
  `category_id` int(11) NOT NULL COMMENT '所属分类ID',
  `restaurant_id` int(11) NOT NULL COMMENT '所属餐厅ID',
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  KEY `restaurant_id` (`restaurant_id`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COMMENT='菜品表';
CREATE TABLE `dish_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜品分类ID',
  `name` varchar(50) NOT NULL COMMENT '分类名称',
  `restaurant_id` int(11) NOT NULL COMMENT '所属餐厅ID',
  PRIMARY KEY (`id`),
  KEY `restaurant_id` (`restaurant_id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='菜品分类表';

CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `restaurant_id` int(11) DEFAULT NULL COMMENT '餐厅ID',
  `order_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '订单日期',
  `total_price` decimal(10,2) DEFAULT '0.00' COMMENT '订单总价',
  `status` enum('待支付','已支付','已取消','已完成') DEFAULT '待支付' COMMENT '订单状态',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `restaurant_id` (`restaurant_id`)
) ENGINE=MyISAM AUTO_INCREMENT=43 DEFAULT CHARSET=utf8 COMMENT='订单表';

CREATE TABLE `order_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单项ID',
  `order_id` int(11) NOT NULL COMMENT '订单ID',
  `dish_id` int(11) NOT NULL COMMENT '菜品ID',
  `quantity` int(11) NOT NULL COMMENT '数量',
  `price_per_item` decimal(10,2) NOT NULL COMMENT '单价',
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  KEY `dish_id` (`dish_id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='订单项表';

CREATE TABLE `restaurants` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '餐厅ID',
  `name` varchar(100) NOT NULL COMMENT '餐厅名称',
  `address` varchar(255) NOT NULL COMMENT '餐厅地址',
  `opening_hours` varchar(50) DEFAULT NULL COMMENT '营业时间',
  `contact_number` varchar(20) DEFAULT NULL COMMENT '联系电话',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COMMENT='餐厅表';

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(255) NOT NULL COMMENT '密码',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱地址',
  `gender` enum('男','女') NOT NULL COMMENT '性别',
  `phone` varchar(20) DEFAULT NULL COMMENT '电话号码',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 COMMENT='用户表';

2. 插入数据（DML）

INSERT INTO `carts` VALUES ('1', '1', '1');
INSERT INTO `carts` VALUES ('2', '1', '2');
INSERT INTO `carts` VALUES ('3', '2', '4');
INSERT INTO `carts` VALUES ('4', '3', '3');
INSERT INTO `carts` VALUES ('5', '3', '1');
INSERT INTO `carts` VALUES ('6', '4', '1');
INSERT INTO `carts` VALUES ('7', '8', '2');
INSERT INTO `carts` VALUES ('8', '5', '1');
INSERT INTO `carts` VALUES ('9', '3', '1');
INSERT INTO `carts&