二手商品交易数据库sql语句
时间: 2023-11-21 22:04:15 浏览: 28
以下是一个简单的二手商品交易数据库的 SQL 语句:
创建商品表:
CREATE TABLE items (
item_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
description TEXT NOT NULL,
price DECIMAL(10, 2) NOT NULL,
seller_id INT NOT NULL,
image_url VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (seller_id) REFERENCES users(user_id)
);
创建用户表:
CREATE TABLE users (
user_id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
创建订单表:
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
buyer_id INT NOT NULL,
seller_id INT NOT NULL,
item_id INT NOT NULL,
price DECIMAL(10, 2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (buyer_id) REFERENCES users(user_id),
FOREIGN KEY (seller_id) REFERENCES users(user_id),
FOREIGN KEY (item_id) REFERENCES items(item_id)
);
查询商品表中的所有商品:
SELECT * FROM items;
查询指定价格范围内的商品:
SELECT * FROM items WHERE price BETWEEN 50 AND 100;
查询指定卖家的所有商品:
SELECT * FROM items WHERE seller_id = 1;
查询订单表中的所有订单:
SELECT * FROM orders;
查询指定买家的所有订单:
SELECT * FROM orders WHERE buyer_id = 1;
查询指定卖家的所有订单:
SELECT * FROM orders WHERE seller_id = 1;
以上是一个简单的二手商品交易数据库的 SQL 语句,你可以根据需要进行修改和扩展。