一、实验目的
(1) 理解并掌握数据库设计过程
(2) 熟悉数据库相关操作的命令
二、实验内容
1、完成一个电子商务网站的数据库建设。
2、商品购物流程数据库的设计与实现。
3、完成相关数据库以及表的创建。
三、相关操作
(1)完成一个电子商务网站的数据库建设
1、创建一个数据库命名为shop
- create database shop;
- use shop;
- 在数据库中建立各张表。
- CREATE TABLE `sh_goods_comment` (
- `id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '评论id',
- `parent_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级评论id',
- `user_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户id',
- `goods_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品id',
- `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '评论内容',
- `is_staff` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '工作人员',
- `is_show` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否显示',
- `is_del` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否删除',
- `create_time` datetime(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '创建时间',
- `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
- PRIMARY KEY (`id`) USING BTREE
- ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
- CREATE TABLE `sh_goods_selector_value` (
- `id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '筛选值id',
- `goods_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品id',
- `selector_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '筛选id',
- `selector_value` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '筛选值',
- PRIMARY KEY (`id`) USING BTREE
- ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
- CREATE TABLE `sh_goods` (
- `id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '商品id',
- `category_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '分类id',
- `spu_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'SPU id',
- `sn` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '编号',
- `name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '名称',
- `keyword` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '关键词',
- `picture` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '图片',
- `tips` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '提示',
- `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '描述',
- `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '详情',
- `price` decimal(10, 2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '价格',
- `stock` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '库存',
- `score` decimal(3, 2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '评分',
- `is_on_sale` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否上架',
- `is_del` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否删除',
- `is_free_shipping` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否包邮',
- `sell_count` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '销量计数',
- `comment_count` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '评论计数',
- `on_Sale_ time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '上架时间',
- `create_time` datetime(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '创建时间',
- `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
- PRIMARY KEY (`id`) USING BTREE
- ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
- CREATE TABLE `sh_goods_attr` (
- `id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '属性id',
- `parent_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级属性 id',
- `category_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品分类 id',
- `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '名称',
- `sort` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '排序',
- PRIMARY KEY (`id`) USING BTREE
- ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
- CREATE TABLE `sh_goods_attr_value` (
- `id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '属性值id',
- `goods_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品id',
- `attr_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '属性id',
- `attr_value` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '属性值',
- PRIMARY KEY (`id`) USING BTREE
- ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
- CREATE TABLE `sh_goods_category` (
- `id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '分类id',
- `parent_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级id\r\n',
- `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '名称',
- `sort` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '排序',
- `is_show` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否显示',
- `create_time` datetime(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '创建时间',
- `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
- PRIMARY KEY (`id`) USING BTREE
- ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
- CREATE TABLE `sh_goods_selector` (
- `id` int(0) UNSIGNED NOT NULL COMMENT '筛选id',
- `parent_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级筛选id',
- `category_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品分类id',
- `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '名称',
- `sort` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '排序',
- PRIMARY KEY (`id`) USING BTREE
- ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
- CREATE TABLE `sh_goods_spec` (
- `id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '规格 id',
- `name` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '规格名称',
- PRIMARY KEY (`id`) USING BTREE
- ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
- CREATE TABLE `sh_goods_spec_item` (
- `id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '规格项id',
- `spec_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '规格id',
- `name` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '名称',
- `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '描述',
- `picture` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '可选图',
- PRIMARY KEY (`id`) USING BTREE
- ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
- CREATE TABLE `sh_goods_spec_set` (
- `goods_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'SKU id',
- `spec_item_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '规格 id',
- PRIMARY KEY (`goods_id`) USING BTREE
- ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
- CREATE TABLE `sh_goods_spu` (
- `id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'SPU id',
- `name` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT 'SPU 名称',
- PRIMARY KEY (`id`) USING BTREE
- ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
- CREATE TABLE `sh_user` (
- `id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户id',
- `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '用户名',
- `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '密码',
- `salt` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '密码盐',
- `email` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '邮箱',
- `mobile` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '手机号',
- `level` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户级别',
- `money` decimal(10, 2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '金额',
- `gender` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '性别',
- `qq` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT 'QQ',
- `is_active` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否激活',
- `reg_time` datetime(0) NULL DEFAULT NULL COMMENT '注册时间',
- `create_time` datetime(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '创建时间',
- `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
- PRIMARY KEY (`id`) USING BTREE,
- UNIQUE INDEX `name`(`name`) USING BTREE
- ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
在每张表中根据情况加入外键:
- ALTER TABLE `sb_goods_comment` ADD CONSTRAINT `fk_sb_goods_comment_sb_goods_comment_1` FOREIGN KEY (`goods_id`) REFERENCES `sh_goods` (`id`);
- ALTER TABLE `sb_goods_comment` ADD CONSTRAINT `fk_sb_goods_comment_sb_goods_comment_2` FOREIGN KEY (`user_id`) REFERENCES `sh_user` (`id`);
- ALTER TABLE `sb_goods_selector_value` ADD CONSTRAINT `fk_sb_goods_selector_value_sb_goods_selector_value_1` FOREIGN KEY (`selector_id`) REFERENCES `sh_goods_selector` (`id`);
- ALTER TABLE `sb_goods_selector_value` ADD CONSTRAINT `fk_sb_goods_selector_value_sb_goods_selector_value_2` FOREIGN KEY (`goods_id`) REFERENCES `sh_goods` (`id`);
- ALTER TABLE `sh_goods` ADD CONSTRAINT `fk_sh_goods_sh_goods_1` FOREIGN KEY (`spu_id`) REFERENCES `sh_goods_spu` (`id`);
- ALTER TABLE `sh_goods` ADD CONSTRAINT `fk_sh_goods_sh_goods_2` FOREIGN KEY (`category_id`) REFERENCES `sh_goods_category` (`id`);
- ALTER TABLE `sh_goods` ADD CONSTRAINT `fk_sh_goods_sh_goods_3` FOREIGN KEY (`spu_id`) REFERENCES `sh_goods_spec_set` (`goods_id`);
- ALTER TABLE `sh_goods_attr_value` ADD CONSTRAINT `fk_sh_goods_attr_value_sh_goods_attr_value_1` FOREIGN KEY (`attr_id`) REFERENCES `sh_goods_attr` (`id`);
- ALTER TABLE `sh_goods_attr_value` ADD CONSTRAINT `fk_sh_goods_attr_value_sh_goods_attr_value_2` FOREIGN KEY (`goods_id`) REFERENCES `sh_goods` (`id`);
- ALTER TABLE `sh_goods_spec_item` ADD CONSTRAINT `fk_sh_goods_spec_item_sh_goods_spec_item_1` FOREIGN KEY (`spec_id`) REFERENCES `sh_goods_spec` (`id`);
- ALTER TABLE `sh_goods_spec_set` ADD CONSTRAINT `fk_sh_goods_spec_set_sh_goods_spec_set_1` FOREIGN KEY (`spec_item_id`) REFERENCES `sh_goods_spec` (`id`);
(2)商品购物流程数据库的设计与实现
1、创建一个数据库命名为shop01
1.create database shop01;
2.use shop01;
2、在数据库中建立各张表。
- CREATE TABLE `sh_goods_score` (
- `id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '评分id',
- `user_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户id',
- `goods_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品id',
- `goods_score` tinyint(255) UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品评分',
- `service_score` tinyint(255) UNSIGNED NOT NULL DEFAULT 0 COMMENT '服务评分',
- `express_score` tinyint(255) UNSIGNED NOT NULL DEFAULT 0 COMMENT '物流评分',
- `is_invalid` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否有效',
- `create_time` datetime(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '评分时间',
- PRIMARY KEY (`id`) USING BTREE
- ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
- CREATE TABLE `sh_order` (
- `id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单id',
- `user_id` int NOT NULL DEFAULT 0 COMMENT '用户id',
- `total_price` decimal(10, 2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '订单总价',
- `order_price` decimal(10, 2) UNSIGNED NOT NULL COMMENT '应付金额',
- `province` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '省',
- `city` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '市',
- `district` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '区',
- `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '具体地址',
- `zip` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '邮编',
- `consignee` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '收件人',
- `phone` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '联系电话',
- `is_valid` tinyint UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否有效',
- `is_cancel` tinyint UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否取消',
- `is_pay` tinyint UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否付款',
- `status` tinyint NOT NULL DEFAULT 0 COMMENT '物流状态',
- `is_del` tinyint NOT NULL DEFAULT 0 COMMENT '是否删除',
- `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
- `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
- PRIMARY KEY (`id`) USING BTREE
- ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
- CREATE TABLE `sh_order_goods` (
- `id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
- `order_id` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单id',
- `goods_id` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品id',
- `goods_name` varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '商品名称',
- `goods_num` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '购买数量',
- `goods_price` decimal(10, 2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '单价',
- `user_note` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '用户备注',
- `staff_note` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '卖家备注',
- PRIMARY KEY (`id`) USING BTREE
- ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
- CREATE TABLE `sh_user_address` (
- `id` int NOT NULL AUTO_INCREMENT COMMENT '地址id',
- `user_id` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户id',
- `is_default` tinyint UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否默认',
- `province` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '省',
- `city` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '市',
- `district` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '区',
- `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '具体地址',
- `zip` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '邮编',
- `consignee` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '收件人',
- `phone` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '联系电话',
- `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
- `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
- PRIMARY KEY (`id`) USING BTREE
- ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
- CREATE TABLE `sh_user_shopcart` (
- `id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '购物车id',
- `user_id` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户id',
- `goods_id` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品id',
- `goods_price` decimal(10, 2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '单价',
- `goods_num` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '购买件数',
- `is_select` tinyint UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否选中',
- `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
- `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
- PRIMARY KEY (`id`) USING BTREE
- ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
在每张表中根据情况加入外键:
- ALTER TABLE `sh_order` ADD CONSTRAINT `fk_sh_order_sh_order_1` FOREIGN KEY (`address`) REFERENCES `sh_user_address` (`address`);
- ALTER TABLE `sh_order` ADD CONSTRAINT `fk_sh_order_sh_order_2` FOREIGN KEY (`user_id`) REFERENCES `sh_goods_score` (`user_id`, `goods_id`);
- ALTER TABLE `sh_order_goods` ADD CONSTRAINT `fk_sh_order_goods_sh_order_goods_1` FOREIGN KEY (`order_id`) REFERENCES `sh_order` (`id`);
- ALTER TABLE `sh_user_shopcart` ADD CONSTRAINT `fk_sh_user_shopcart_sh_user_shopcart_1` FOREIGN KEY (`goods_id`) REFERENCES `sh_order` (`user_id`);
E_R图:
- 问题思考与总结
1、数据库设计的过程有哪些步骤?
(1)需求分析(2)概念数据库设计(3)逻辑数据库设计(4)物理数据库设计(5)数据库实施(6)数据库运行和维护
- 确定数据类型需要注意哪些问题?
- 为避免冲突,字段名使用小写字母。
- 小数类型为decimal,使用float和double会有精度损失。
- varchar是可变长字符串,不会预先分配空间,但长度不能超过5000,如果超过要
用text类型,且要独立出来一张表。
- 字段可以适当冗余提高查询性能。
- 其他自己遇到的问题整理总计
本次实验在表的建立上没有太大的问题,所有的问题都出现在E_R图的构建上。首先是外键的设置语句ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表中的某属性) REFERENCES 外表名(外表中的某属性)。其次在设置外键的时候要搞清楚表与表之间的关系,理清楚哪个是父表,哪个是子表。此外,在构建E_R图时,要分别考虑到它的概念模型、逻辑模型和物理模型。
- 本章重要命令的总结。
①ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表中的某属性) REFERENCES 外表名(外表中的某属性);//添加外键
②CREATE TABLE 表名(
属性名 属性类型 [数据类型有无符号] 是否为空 是否自增 默认值 注释,
);//创建表
③INSERT INTO 表名 [要赋值的属性字段] VALUES (各字段对应的值), …();//在表中插入数据
④SELECT 要查询的字段 FROM 表名;(*代表所有字段)//查询字段信息值
⑤ALTER TABLE 表名 MODIFY 字段名 字段类型 有无符号 AUTO_INCREMENT;//自增设置