CREATE DATABASE test;
use test;
CREATE TABLE Customer (
ID_card VARCHAR(50) NOT NULL PRIMARY KEY,
AccountNumber VARCHAR(50) NOT NULL,
Name VARCHAR(50) NOT NULL,
Gender CHAR(1) NOT NULL,
Birthday DATE NOT NULL,
Address VARCHAR(255) NOT NULL,
Phone VARCHAR(255) NOT NULL,
Email VARCHAR(255) NOT NULL,
Company VARCHAR(255) NOT NULL,
Age INT NOT NULL
);
INSERT INTO Customer VALUES ('123456789012345678', '1001', '张三', '男', '2004-12-11', '成都市双流区', '13700000001', '123456@qq.com', 'A', 25);
INSERT INTO Customer VALUES ('876543210987654321', '1002', '李四', '女', '2004-12-12', '成都市金牛区', '13800000002', '234567@qq.com', 'B', 28);
INSERT INTO Customer VALUES ('987654321098765432', '1003', '王五', '男', '2004-12-13', '成都市郫都区', '13900000003', '345678@qq.com', 'C', 30);
desc Customer;
SELECT * FROM Customer;
//增加提条数据
INSERT INTO Customer VALUES ('987654321234567890', '1004', '小刘', '女', '2004-12-14', '成都市温江区', '13900000004', '456789@qq.com', 'D', 27);
CREATE TABLE Depositor (
Card_number VARCHAR(50) NOT NULL PRIMARY KEY,
AccountNumber VARCHAR(50) NOT NULL,
Name VARCHAR(50) NOT NULL,
Gender CHAR(1) NOT NULL,
Password VARCHAR(50) NOT NULL,
Account_balance DECIMAL(10,2) NOT NULL,
Open_date DATE NOT NULL,
Open_address VARCHAR(100) NOT NULL
);
INSERT INTO Depositor VALUES ('1111-2222-3333-4444', '1001', '张三', '男', 'pass123', 10000.00, '2020-10-11', '成都市双流区支行');
INSERT INTO Depositor VALUES ('5555-6666-7777-8888', '1002', '李四', '女', 'pass456', 15000.00, '2020-10-14', '成都市金牛区支行');
INSERT INTO Depositor VALUES ('9999-0000-1111-2222', '1003', '王五', '男', 'pass789', 20000.00, '2020-10-18', '成都市郫都区支行');
desc Depositor;
SELECT * FROM Depositor;
CREATE TABLE CunrrentDeposit (
AccountNumber VARCHAR(50) NOT NULL PRIMARY KEY,
Money DECIMAL(10,2) NOT NULL,
Type VARCHAR(50) NOT NULL,
Transaction_date DATE NOT NULL,
Interest DECIMAL(5,2) NOT NULL,
Account_balance DECIMAL(10,2) NOT NULL
);
INSERT INTO CunrrentDeposit VALUES ('1001', 5000.00, '存入', '2022-11-12', 0.50, 15000.00);
INSERT INTO CunrrentDeposit VALUES ('1002', 4000.00, '取出', '2022-11-13', 0.30, 11000.00);
INSERT INTO CunrrentDeposit VALUES ('1003', 3000.00, '存入', '2022-11-14', 0.20, 23000.00);
desc CunrrentDeposit;
SELECT * FROM CunrrentDeposit;
CREATE TABLE FixedDeposit (
AccountNumber VARCHAR(50) NOT NULL,
Deposit_Name VARCHAR(50) NOT NULL PRIMARY KEY,
Money DECIMAL(10,2) NOT NULL,
Storage_year INT NOT NULL,
Storage_date DATE NOT NULL,
Annual_interest_rate DECIMAL(5,2) NOT NULL
);
INSERT INTO FixedDeposit VALUES ('1001', '张三', 1000.00, 2, '2023-12-11', 0.20);
INSERT INTO FixedDeposit VALUES ('1002', '李四', 1500.00, 3, '2023-12-12', 0.30);
INSERT INTO FixedDeposit VALUES ('1003', '王五', 2000.00, 4, '2023-12-13', 0.40);
desc FixedDeposit;
SELECT * FROM FixedDeposit;
CREATE TABLE FixedWithdarwal (
AccountNumber VARCHAR(50) NOT NULL,
Withdarwal_Name VARCHAR(50) NOT NULL PRIMARY KEY,
Withdarwal_amount DECIMAL(10,2) NOT NULL,
Withdarwal_date DATE NOT NULL
);
INSERT INTO FixedWithdarwal VALUES ('1001', '张三', 1000.00, '2023-12-11');
INSERT INTO FixedWithdarwal VALUES ('1002', '李四', 1500.00, '2023-12-12');
INSERT INTO FixedWithdarwal VALUES ('1003', '王五', 2000.00, '2023-12-13');
desc FixedWithdarwal;
SELECT * FROM FixedWithdarwal;
CREATE TABLE FinancialProductPurchase (
Order_number VARCHAR(50) NOT NULL PRIMARY KEY,
AccountNumber VARCHAR(50) NOT NULL,
Name VARCHAR(50) NOT NULL,
Sales_region VARCHAR(50) NOT NULL,
Product_code VARCHAR(50) NOT NULL,
Purchase_date DATE NOT NULL,
Purchase_quantity INT NOT NULL,
Purchase_amount DECIMAL(10,2) NOT NULL
);
INSERT INTO FinancialProductPurchase VALUES ('A001', '1001', '张三', '成都市双流区', 'P001', '2024-10-11', 5, 2000.00);
INSERT INTO FinancialProductPurchase VALUES ('A002', '1002', '李四', '成都市金牛区', 'P002', '2024-10-14', 7, 2500.00);
INSERT INTO FinancialProductPurchase VALUES ('A003', '1003', '王五', '成都市郫都区', 'P003', '2024-10-18', 9, 3000.00);
desc FinancialProductPurchase;
SELECT * FROM FinancialProductPurchase;
CREATE TABLE FinancialProduct (
Product_code VARCHAR(50) NOT NULL PRIMARY KEY,
Product_type VARCHAR(50) NOT NULL,
Product_name VARCHAR(50) NOT NULL,
Product_price DECIMAL(10,2) NOT NULL,
Return_period INT NOT NULL
);
INSERT INTO FinancialProduct VALUES ('P001', '床上用品', '床上三件套', 2000.00, 1);
INSERT INTO FinancialProduct VALUES ('P002', '电子产品', '数媒照相机', 2500.00, 2);
INSERT INTO FinancialProduct VALUES ('P003', '生活用品', '高压电饭煲', 3000.00, 3);
desc FinancialProduct;
SELECT * FROM FinancialProduct;
CREATE TABLE Promotion (
Promotion_number VARCHAR(50) NOT NULL PRIMARY KEY,
Promotion_title VARCHAR(50) NOT NULL,
Promotion_time DATE NOT NULL,
Customer_level VARCHAR(50) NOT NULL
);
INSERT INTO Promotion VALUES ('S001', '全场85折', '2024-10-11', '黄金卡');
INSERT INTO Promotion VALUES ('S002', '全场75折', '2024-10-14', '铂金卡');
INSERT INTO Promotion VALUES ('S003', '全场65折', '2024-10-18', '白银卡');
desc Promotion;
SELECT * FROM Promotion;
CREATE TABLE PromotionContent (
Promotion_number VARCHAR(50) NOT NULL,
Promotion_detail VARCHAR(255) NOT NULL PRIMARY KEY
);
INSERT INTO PromotionContent VALUES ('S001', '消费满1000元打85折');
INSERT INTO PromotionContent VALUES ('S002', '消费满1500元打75折');
INSERT INTO PromotionContent VALUES ('S003', '消费满2000元打65折');
desc PromotionContent;
SELECT * FROM PromotionContent;