MySQL-----DDL与DML语句

最新推荐文章于 2024-07-10 22:41:59 发布

JSYRID

最新推荐文章于 2024-07-10 22:41:59 发布

阅读量315

点赞数 11

文章标签：数据库 mysql sql java 学习大数据

本文链接：https://blog.csdn.net/JSYRID/article/details/139680647

版权

根据ER图与数据库模型图设计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
);

生成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');

问题查询

#1.查询用户信息，仅显示用户的姓名与手机号，用中文显示列名。中文显示姓名列与手机号列
select username as '姓名',phone '手机号' from `user`;

#2.根据商品名称进行模糊查询，模糊查询需要可以走索引，需要给出explain语句。
explain select * from product where product_name like '小%';

#3.统计用户订单信息，查询所有用户的下单数量，并进行倒叙排序。
select order_id '订单号',count(*) '订单数量'
from`order`
group by user_id
order by count(*) desc;

#题目1:查询用户的基本信息，钱包信息。
select
username,
`password`，
email,
phone,
uw.created_at,
wallet_id,
balance
from `user` u join user_wallet uw on u.user_id=uw.user_id;
#问题2:查看订单中下单最多的产品对应的类别。
#1、订单中下单最多的产品——订单详情表
select * from order_info;
#2、计算并获取产品id
select product_id '产品编号' from order_info group by product_id order by count(*) desc limit 1;
#3、确认多表联合查询
select type_name '产品类别'from product p join product_type pt on p.type_id=pt.type_id;
#4、根据产品id兑换产品类别.组合sql
select type_name '产品类别'from product p join product_type pt on p.type_id=pt.type_id
where product_id=(
select product_id '产品编号'from order_info group by product_id order by count(*) desc limit 1);