MySQL周内训参照2、DDL与DML语句

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介: MySQL周内训参照2、DDL与DML语句
编号 人员 题目 总分数 题干

提交内容

得分标准
3 DBA 设计表格 10 使用工具统一版本Navicat 15完成后续所有内容。
要求DDL语句完整度:
1、创建【hlwxy_shop】数据库语句。
2、根据ER关系图完成7张表的设计(设计完毕提交Navicat截图的ER图)。
3、使用变量类型合理且最优。
4、有合理的唯一约束与索引的添加。
5、有完整的列注释与表注释。
1、1个数据库创建语句
2、7张表的完整DDL语句
3、ER图截图
1、建库语句有完整的字符集(1分)与排序规则(1分)(共2分)
2、共计7张表的DDL语句,用户系列1分,商品系列1分,订单系列1分(共3分)
3、有合理的唯一索引与索引添加,用户表,商品表必须有合理的添加(共2分)
4、在Navicat 15的工具内选择ER图,可以看到完整的表关系连接(1分)
5、有完整的列注释与表注释(2分)
4 DBA 插入语数据 10 要求DML语句完整度:
1、根据主从表添加基础数据。
2、要求主表数据至少5条。
3、要求从表必须包含主表的数据,不少于5条。
4、数据标准细则:
规范性:数据内容、命名、格式、取值等规范统一。如时间信息都以yyyy-mm-dd格式存储,或者“性别”属性中,应使用“M”、“F”表示,还是用“1”,“0”表示,还是用“男”、“女”表示。
完整性:数据完整、没有缺失(包括看得见的缺失和看不见的缺失)。如人员信息完整涵盖性别、年龄等,或者身份证号码不能为空。
唯一性:同源或跨源的数据在信息含义上是一致不冲突的。如同一个人的性别都是一致的。
一致性:记录和字段都没有重复。如同一个ID没有重复记录。
准确性:数据内容及其含义是正确的。例如年龄在合理范围内。
关联性:不同表格之间数据的关联完整且正确。例如两张表建立的关联关系存在,不丢失数据。
完整的可运行DML语句 1、每张表的DML语句不得少于5条,共计至少35条,少1条口1分(共5分)
2、所有添加数据需要满足要求4的数据标注细则,有1条不符合扣1分。(共5分)

根据ER图与数据库模型图设计DDL语句

无注释DDL

CREATE TABLE user (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE user_wallet (
    wallet_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE
);
CREATE TABLE user_wallet_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    transaction_type VARCHAR(50) NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    transaction_time TIMESTAMP NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE
);
 
CREATE TABLE product_type (
    type_id INT AUTO_INCREMENT PRIMARY KEY,
    type_name VARCHAR(100) NOT NULL,
    parent_id INT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE product (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    stock INT NOT NULL DEFAULT 0,
    type_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (type_id) REFERENCES product_type(type_id) ON DELETE RESTRICT
);
 
CREATE TABLE `order` (
    `order_id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `order_status` VARCHAR(50) NOT NULL DEFAULT '待支付',
    `order_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `payment_status` VARCHAR(50) NOT NULL DEFAULT '未支付',
    `payment_time` TIMESTAMP NULL,
    `total_price` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`) ON DELETE RESTRICT
);
 
CREATE TABLE order_info (
    order_info_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES `order`(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES product(product_id) ON DELETE RESTRICT
);

要匹配ER图,我这里写了完整的注释。

-- 创建用户表
CREATE TABLE user (
    -- 用户ID,主键,自增
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    -- 用户名,非空且唯一
    username VARCHAR(50) NOT NULL UNIQUE,
    -- 密码,非空,假设密码使用哈希加密存储
    password VARCHAR(255) NOT NULL, 
    -- 邮箱,非空且唯一
    email VARCHAR(100) UNIQUE,
    -- 手机号,非空且唯一
    phone VARCHAR(20) UNIQUE,
    -- 创建时间,默认值为当前时间戳
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
 
-- 创建用户钱包表
CREATE TABLE user_wallet (
    -- 钱包ID,主键,自增
    wallet_id INT AUTO_INCREMENT PRIMARY KEY,
    -- 用户ID,外键,关联user表的user_id
    user_id INT NOT NULL,
    -- 钱包余额,非空,默认值为0.00
    balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    -- 创建时间,默认值为当前时间戳
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 外键约束,当user表删除用户时,级联删除该用户的钱包记录
    FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE
);
 
 
-- 创建用户钱包交易日志表
CREATE TABLE user_wallet_log (
    -- 日志ID,主键,自增
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    -- 用户ID,外键,关联user表的user_id
    user_id INT NOT NULL,
    -- 交易类型,非空
    transaction_type VARCHAR(50) NOT NULL,
    -- 交易金额,非空
    amount DECIMAL(10, 2) NOT NULL,
    -- 交易时间,非空
    transaction_time TIMESTAMP NOT NULL,
    -- 外键约束,当user表删除用户时,级联删除该用户的所有交易日志记录
    FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE
);
 
 
-- 创建商品类别表
CREATE TABLE product_type (
    -- 类别ID,主键,自增
    type_id INT AUTO_INCREMENT PRIMARY KEY,
    -- 类别名称,非空
    type_name VARCHAR(100) NOT NULL,
    -- 父类别ID,可为空
    parent_id INT NULL,
    -- 类别描述
    description TEXT,
    -- 创建时间,默认值为当前时间戳
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
 
-- 创建商品表
CREATE TABLE product (
    -- 商品ID,主键,自增
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    -- 商品名称,非空
    product_name VARCHAR(255) NOT NULL,
    -- 商品价格,非空,默认值为0.00
    price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    -- 库存数量,非空,默认值为0
    stock INT NOT NULL DEFAULT 0,
    -- 类别ID,非空,外键,关联product_type表的type_id
    type_id INT NOT NULL,
    -- 创建时间,默认值为当前时间戳
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 外键约束,当删除商品类别时,若商品仍属于该类别,则不允许删除
    FOREIGN KEY (type_id) REFERENCES product_type(type_id) ON DELETE RESTRICT
);
 
 
 
-- 创建订单表
CREATE TABLE `order` (
    -- 订单ID,主键,自增
    `order_id` INT AUTO_INCREMENT PRIMARY KEY,
    -- 用户ID,非空,关联用户表的user_id字段
    `user_id` INT NOT NULL,
    -- 订单状态,非空,默认值为'待支付'(表示订单等待支付的状态)
    `order_status` VARCHAR(50) NOT NULL DEFAULT '待支付',
    -- 下单时间,默认值为当前时间戳(记录订单创建的时间)
    `order_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 支付状态,非空,默认值为'未支付'(表示订单尚未完成支付的状态)
    `payment_status` VARCHAR(50) NOT NULL DEFAULT '未支付',
    -- 支付时间,可以为空(表示订单完成支付的时间,如果未支付则为NULL)
    `payment_time` TIMESTAMP NULL,
    -- 订单总价,非空,默认值为0.00(表示订单中所有商品的总价格)
    `total_price` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    -- 外键约束,关联用户表的user_id字段,当用户被删除时,限制删除该用户创建的订单(防止数据不一致)
    FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`) ON DELETE RESTRICT
);
 
 
-- 创建订单详情表
CREATE TABLE order_info (
    -- 订单详情ID,主键,自增
    order_info_id INT AUTO_INCREMENT PRIMARY KEY,
    -- 订单ID,非空,关联订单表的order_id字段
    order_id INT NOT NULL,
    -- 商品ID,非空,关联商品表的product_id字段
    product_id INT NOT NULL,
    -- 购买数量,非空(表示订单中每个商品的数量)
    quantity INT NOT NULL,
    -- 单价,非空(表示订单中每个商品的单位价格)
    unit_price DECIMAL(10, 2) NOT NULL,
    -- 外键约束,关联订单表的order_id字段,当订单被删除时,级联删除该订单的所有详情记录(保持数据完整性)
    FOREIGN KEY (order_id) REFERENCES `order`(order_id) ON DELETE CASCADE,
    -- 外键约束,关联商品表的product_id字段,当商品被删除时,限制删除该商品在订单中的记录(防止数据不一致)
    FOREIGN KEY (product_id) REFERENCES product(product_id) ON DELETE RESTRICT
);

生成DDL对应的DML语句

user表相关DML语句

 
-- 插入10条用户数据
INSERT INTO `user` VALUES ('1', '王语嫣', 'password1', 'user1@example.com', '13800000001', '2024-06-06 22:07:39');
INSERT INTO `user` VALUES ('2', '小龙女', 'password2', 'user2@example.com', '13800000002', '2024-06-09 22:07:39');
INSERT INTO `user` VALUES ('3', '赵灵儿', 'password3', 'user3@example.com', '13800000003', '2024-06-09 22:07:39');
INSERT INTO `user` VALUES ('4', '杨过', 'password4', 'user4@example.com', '13800000004', '2024-05-31 22:07:39');
INSERT INTO `user` VALUES ('5', '向问天', 'password5', 'user5@example.com', '13800000005', '2024-06-04 22:07:39');
INSERT INTO `user` VALUES ('6', '杨潇', 'password6', 'user6@example.com', '13800000006', '2024-06-01 22:07:39');
INSERT INTO `user` VALUES ('7', '郭靖', 'password7', 'user7@example.com', '13800000007', '2024-06-01 22:07:39');
INSERT INTO `user` VALUES ('8', '黄蓉', 'password8', 'user8@example.com', '13800000008', '2024-06-04 22:07:39');
INSERT INTO `user` VALUES ('9', '程英', 'password9', 'user9@example.com', '13800000009', '2024-06-08 22:07:39');
INSERT INTO `user` VALUES ('10', '郭襄', 'password10', 'user10@example.com', '13800000010', '2024-06-08 22:07:39');
 
 
-- 插入10条用户钱包数据,与user表对应
INSERT INTO `user_wallet` VALUES ('1', '1', '422.00', '2024-06-08 22:07:42');
INSERT INTO `user_wallet` VALUES ('2', '2', '897.00', '2024-06-04 22:07:42');
INSERT INTO `user_wallet` VALUES ('3', '3', '354.00', '2024-06-03 22:07:42');
INSERT INTO `user_wallet` VALUES ('4', '4', '758.00', '2024-06-07 22:07:42');
INSERT INTO `user_wallet` VALUES ('5', '5', '319.00', '2024-06-06 22:07:42');
INSERT INTO `user_wallet` VALUES ('6', '6', '350.00', '2024-06-08 22:07:42');
INSERT INTO `user_wallet` VALUES ('7', '7', '871.00', '2024-06-04 22:07:42');
INSERT INTO `user_wallet` VALUES ('8', '8', '515.00', '2024-06-05 22:07:42');
INSERT INTO `user_wallet` VALUES ('9', '9', '1042.00', '2024-06-05 22:07:42');
INSERT INTO `user_wallet` VALUES ('10', '10', '273.00', '2024-06-03 22:07:42');
 
 
 
-- 插入10条用户钱包交易日志数据
INSERT INTO `user_wallet_log` VALUES ('1', '1', '充值', '100.00', '2024-06-01 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('2', '2', '消费', '50.00', '2024-06-08 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('3', '3', '提现', '200.00', '2024-06-09 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('4', '4', '充值', '150.00', '2024-05-31 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('5', '5', '消费', '75.00', '2024-06-04 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('6', '6', '提现', '300.00', '2024-06-02 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('7', '7', '充值', '250.00', '2024-06-09 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('8', '8', '消费', '120.00', '2024-06-07 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('9', '9', '提现', '400.00', '2024-06-08 22:07:48');
INSERT INTO `user_wallet_log` VALUES ('10', '10', '充值', '300.00', '2024-05-31 22:07:48');

product对应DML语句

INSERT INTO `product_type` VALUES ('1', '智能手机', null, '包含各种品牌和型号的智能手机,涵盖不同价格区间和功能特点。', '2024-05-31 22:13:55');
INSERT INTO `product_type` VALUES ('2', '笔记本电脑', null, '提供多种类型和配置的笔记本电脑,适用于不同工作和学习需求。', '2024-06-01 22:13:55');
INSERT INTO `product_type` VALUES ('3', '智能家居', null, '包含智能家居设备,如智能音箱、智能照明、智能门锁等。', '2024-06-02 22:13:55');
INSERT INTO `product_type` VALUES ('4', '电视与显示器', null, '涵盖各种尺寸和分辨率的电视与显示器,适用于家庭和企业使用。', '2024-06-03 22:13:55');
INSERT INTO `product_type` VALUES ('5', '耳机与音响', null, '提供高品质耳机和音响设备,满足用户的音频体验需求。', '2024-06-04 22:13:55');
INSERT INTO `product` VALUES ('1', 'iPhone 13', '6999.00', '100', '1', '2024-06-05 22:13:55');
INSERT INTO `product` VALUES ('2', 'MacBook Pro', '14999.00', '50', '2', '2024-06-06 22:13:55');
INSERT INTO `product` VALUES ('3', '小米智能音箱', '199.00', '200', '3', '2024-06-07 22:13:55');
INSERT INTO `product` VALUES ('4', '三星65寸4K电视', '7999.00', '80', '4', '2024-06-08 22:13:55');
INSERT INTO `product` VALUES ('5', '索尼降噪耳机', '1299.00', '150', '5', '2024-06-09 22:13:55');
INSERT INTO `product` VALUES ('6', '华为MatePad Pro', '3999.00', '70', '2', '2024-05-31 22:13:55');
INSERT INTO `product` VALUES ('7', '小米米家智能灯泡', '79.00', '300', '3', '2024-06-01 22:13:55');
INSERT INTO `product` VALUES ('8', 'LG 27寸显示器', '1499.00', '120', '4', '2024-06-02 22:13:55');
INSERT INTO `product` VALUES ('9', '苹果AirPods Pro', '1599.00', '90', '5', '2024-06-03 22:13:55');
INSERT INTO `product` VALUES ('10', '一加9 Pro', '4999.00', '60', '1', '2024-05-30 22:13:55');

order对应DML语句

INSERT INTO `order` VALUES ('1', '2', '待支付', '2024-06-09 22:19:14', '未支付', '2024-06-09 22:19:50', '120.00');
INSERT INTO `order` VALUES ('2', '5', '已支付', '2024-06-09 22:19:14', '已支付', '2024-06-09 22:19:53', '230.50');
INSERT INTO `order` VALUES ('3', '7', '已完成', '2024-06-09 22:19:14', '已支付', '2024-06-05 22:19:58', '345.75');
INSERT INTO `order` VALUES ('4', '9', '已取消', '2024-06-09 22:19:14', '未支付', '2024-06-09 22:20:46', '0.00');
INSERT INTO `order` VALUES ('5', '2', '待支付', '2024-06-09 22:19:14', '未支付', '2024-06-09 22:20:49', '45.00');
INSERT INTO `order` VALUES ('6', '5', '已支付', '2024-06-09 22:19:14', '已支付', '2024-06-09 22:20:51', '78.00');
INSERT INTO `order` VALUES ('7', '7', '待评价', '2024-06-09 22:19:14', '已支付', '2024-06-09 22:20:53', '90.20');
INSERT INTO `order` VALUES ('8', '9', '待发货', '2024-06-09 22:19:14', '已支付', '2024-06-09 22:20:55', '150.00');
INSERT INTO `order` VALUES ('9', '2', '待支付', '2024-06-09 22:19:14', '未支付', '2024-06-09 22:20:57', '198.00');
INSERT INTO `order` VALUES ('10', '5', '待支付', '2024-06-09 22:19:14', '未支付', '2024-06-09 22:20:59', '66.50');
INSERT INTO `order_info` VALUES ('1', '1', '1', '2', '50.00');
INSERT INTO `order_info` VALUES ('2', '1', '2', '1', '60.00');
INSERT INTO `order_info` VALUES ('3', '2', '3', '3', '70.00');
INSERT INTO `order_info` VALUES ('4', '3', '2', '1', '60.00');
INSERT INTO `order_info` VALUES ('5', '3', '4', '2', '80.00');
INSERT INTO `order_info` VALUES ('6', '5', '1', '1', '50.00');
INSERT INTO `order_info` VALUES ('7', '6', '4', '1', '80.00');
INSERT INTO `order_info` VALUES ('8', '7', '3', '2', '70.00');
INSERT INTO `order_info` VALUES ('9', '8', '2', '2', '60.00');
INSERT INTO `order_info` VALUES ('10', '9', '1', '3', '50.00');
INSERT INTO `order_info` VALUES ('11', '9', '3', '1', '70.00');
INSERT INTO `order_info` VALUES ('12', '10', '2', '1', '60.00');


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
5天前
|
SQL 关系型数据库 MySQL
MySQL DDL(数据定义语言)深度解析
MySQL DDL(数据定义语言)深度解析
|
24天前
|
SQL 关系型数据库 MySQL
MySQL基础(二)----DML学习
MySQL基础(二)----DML学习
17 3
|
24天前
|
SQL 存储 关系型数据库
MySQL基础(一) 前置安装以及DDL详解
MySQL基础(一) 前置安装以及DDL详解
17 1
|
5天前
|
SQL 关系型数据库 MySQL
MySQL DML(数据操作语言)全面指南
MySQL DML(数据操作语言)全面指南
|
1月前
|
SQL 关系型数据库 MySQL
MySQL Online DDL原理解读
MySQL Online DDL原理解读
35 3
|
1月前
|
SQL 关系型数据库 MySQL
MySQL Online DDL(Data Definition Language)
MySQL Online DDL(Data Definition Language)
32 1
|
1月前
|
存储 关系型数据库 MySQL
MySQL周内训参照5、存储过程创建
MySQL周内训参照5、存储过程创建
31 1
|
1月前
|
存储 SQL 关系型数据库
MySQL周内训参照4、触发器-插入-修改-删除
MySQL周内训参照4、触发器-插入-修改-删除
20 1
|
1月前
|
SQL 关系型数据库 MySQL
MySQL周内训参照3、简单查询与多表联合复杂查询
MySQL周内训参照3、简单查询与多表联合复杂查询
24 1
|
19天前
|
SQL 存储 关系型数据库
MySQL数据库—初识数据库 | DDL语句 | DML语句
MySQL数据库—初识数据库 | DDL语句 | DML语句