Mysql schema - v0 by Vercel

/*
Navicat Premium Dump SQL

Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 80036 (8.0.36)
Source Host : localhost:3306
Source Schema : waimai

Target Server Type : MySQL
Target Server Version : 80036 (8.0.36)
File Encoding : 65001

Date: 24/12/2024 12:39:54
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Table structure for 商品

DROP TABLE IF EXISTS 商品;
CREATE TABLE 商品 (
id int NOT NULL AUTO_INCREMENT,
name varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
business_id int NOT NULL,
price double(16, 2) NOT NULL,
limit_num int NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE,
INDEX business_id(business_id) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- Records of 商品

INSERT INTO 商品 VALUES (1, '鸡公煲', 1, 11.90, 1);
INSERT INTO 商品 VALUES (2, '麻辣香锅', 4, 12.50, 2);
INSERT INTO 商品 VALUES (3, '牛腩', 2, 13.90, NULL);
INSERT INTO 商品 VALUES (4, '韭菜猪肉', 3, 14.90, 3);

-- Table structure for 商家

DROP TABLE IF EXISTS 商家;
CREATE TABLE 商家 (
id int NOT NULL AUTO_INCREMENT,
name varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
turnover double(16, 2) NOT NULL DEFAULT 0.00,
phone varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
address varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- Records of 商家

INSERT INTO 商家 VALUES (1, '我要吃饭', 0.00, '18430983251', '三水区乐平大道');
INSERT INTO 商家 VALUES (2, '饺子', 0.00, '17430893654', '三水区桥头东道');
INSERT INTO 商家 VALUES (3, '港式', 0.00, '16554324238', '三水区桥头西道');

-- Table structure for 客户

DROP TABLE IF EXISTS 客户;
CREATE TABLE 客户 (
id int NOT NULL AUTO_INCREMENT,
name varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
consumption double(16, 2) NOT NULL DEFAULT 0.00,
phone varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
address varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- Records of 客户

INSERT INTO 客户 VALUES (1, '张三', 11.90, '15877062980', '佛山市三水区大鹏公寓');
INSERT INTO 客户 VALUES (2, '李四', 44.70, '15257885321', '佛山市三水区大鹏村青年公寓');
INSERT INTO 客户 VALUES (3, '王五', 55.60, '19054322637', '佛山市三水区五间寮');
INSERT INTO 客户 VALUES (4, '赵六', 0.00, '13579431270', '佛山市三水区塘边村');

-- Table structure for 订单

DROP TABLE IF EXISTS 订单;
CREATE TABLE 订单 (
id int NOT NULL AUTO_INCREMENT,
order_time datetime NOT NULL,
customer_id int NOT NULL,
商品_id int NOT NULL,
number int NOT NULL,
total_cost double(16, 2) NOT NULL,
state int NOT NULL DEFAULT 0,
PRIMARY KEY (id) USING BTREE,
INDEX customer_id(customer_id) USING BTREE,
INDEX 商品_id(商品_id) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Fixed;

-- Records of 订单

INSERT INTO 订单 VALUES (1, '2024-12-12 14:14:35', 1, 1, 1, 11.90, 0);
INSERT INTO 订单 VALUES (2, '2024-12-12 13:45:12', 2, 4, 3, 44.70, 0);
INSERT INTO 订单 VALUES (3, '2024-12-11 01:14:35', 3, 3, 4, 55.60, 0);

-- View structure for order_details

DROP VIEW IF EXISTS order_details;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW order_details AS select 订单.id AS 订单编号,商家.name AS 商家名称,商品.name AS 商品名称,订单.number AS 数量,订单.total_cost AS 总额,订单.state AS 订单状态,客户.name AS 客户姓名,客户.phone AS 电话,客户.address AS 地址,订单.order_time AS 时间 from (((商家 join 商品 on((商家.id = 商品.business_id))) join 订单 on((商品.id = 订单.商品_id))) join 客户 on((订单.customer_id = 客户.id)));

-- Triggers structure for table 订单

DROP TRIGGER IF EXISTS limit_buy;
delimiter ;;
CREATE TRIGGER limit_buy BEFORE INSERT ON 订单 FOR EACH ROW BEGIN
DECLARE p DOUBLE(16,2); /商品单价/
DECLARE lim INT; /限购数量/
SET p=(SELECT price FROM 商品 WHERE 商品.id=NEW.商品_id);
SET lim=(SELECT limit_num FROM 商品 WHERE 商品.id=NEW.商品_id);
IF (lim IS NOT NULL AND NEW.number>lim) THEN
SET NEW.number=lim;
END IF ;
SET NEW.total_cost=NEW.number*p;
END
;;
delimiter ;

-- Triggers structure for table 订单

DROP TRIGGER IF EXISTS calculate_consumption;
delimiter ;;
CREATE TRIGGER calculate_consumption AFTER INSERT ON 订单 FOR EACH ROW BEGIN
UPDATE 客户
SET consumption=consumption+NEW.total_cost
WHERE 客户.id=NEW.customer_id;
END
;;
delimiter ;

-- Triggers structure for table 订单

DROP TRIGGER IF EXISTS calculate_turnover;
delimiter ;;
CREATE TRIGGER calculate_turnover BEFORE UPDATE ON 订单 FOR EACH ROW BEGIN
IF (NEW.state=1 AND OLD.state=0) THEN
UPDATE 商家
SET turnover=turnover+OLD.total_cost
WHERE id=(SELECT business_id FROM 商品 WHERE 商品.id=NEW.商品_id);
ELSEIF (NEW.state=3 AND OLD.state=0) THEN
UPDATE 客户
SET consumption=consumption-OLD.total_cost
WHERE id=OLD.customer_id;
ELSEIF (NEW.state<OLD.state) THEN
SET NEW.state=OLD.state;
END IF ;
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;
根据这个数据库写出单表查询、多表查询、排序、分组、分组后的帅选、统计函数的应用、自身连接等内容

Show full message

465d ago

I'll provide you with a series of SQL queries that demonstrate various database operations based on the provided schema. I'll use comments to explain each query.

SQL Queries

queries.js

This script demonstrates various SQL operations:

Single table query: Retrieves all customers.
Multi-table query: Joins the 订单 (order), 客户 (customer), and 商品 (product) tables to show order details.
Sorting: Lists all products sorted by price in descending order.
Grouping: Calculates total sales for each business by grouping orders.
Filtering after grouping: Shows businesses with total sales over 50.
Use of aggregate functions: Calculates the average order value and total number of orders.
Self-join: Finds businesses in the same area by comparing the first three characters of their addresses.

Note that this script uses the mysql2 library for Node.js. To run it, you'd need to install the library (npm install mysql2) and replace 'your_username' and 'your_password' with your actual MySQL credentials.

These queries cover a range of SQL operations and should give you a good starting point for working with this database schema. You can modify and expand upon these queries as needed for your specific requirements.

465d ago

const mysql = require('mysql2/promise');

async function runQueries() {
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'your_username',
    password: 'your_password',
    database: 'waimai'
  });

  try {
    // 1. Single table query
    console.log("1. List all customers:");
    let [rows] = await connection.execute('SELECT * FROM 客户');
    console.log(rows);

    // 2. Multi-table query using JOIN
    console.log("\n2. List all orders with customer and product details:");
    [rows] = await connection.execute(`
      SELECT 订单.id, 客户.name AS customer_name, 商品.name AS product_name, 订单.number, 订单.total_cost
      FROM 订单
      JOIN 客户 ON 订单.customer_id = 客户.id
      JOIN 商品 ON 订单.商品_id = 商品.id
    `);
    console.log(rows);

    // 3. Sorting
    console.log("\n3. List all products sorted by price in descending order:");
    [rows] = await connection.execute('SELECT * FROM 商品 ORDER BY price DESC');
    console.log(rows);

    // 4. Grouping
    console.log("\n4. Total sales for each business:");
    [rows] = await connection.execute(`
      SELECT 商家.name, SUM(订单.total_cost) AS total_sales
      FROM 商家
      JOIN 商品 ON 商家.id = 商品.business_id
      JOIN 订单 ON 商品.id = 订单.商品_id
      GROUP BY 商家.id
    `);
    console.log(rows);

    // 5. Filtering after grouping
    console.log("\n5. Businesses with total sales over 50:");
    [rows] = await connection.execute(`
      SELECT 商家.name, SUM(订单.total_cost) AS total_sales
      FROM 商家
      JOIN 商品 ON 商家.id = 商品.business_id
      JOIN 订单 ON 商品.id = 订单.商品_id
      GROUP BY 商家.id
      HAVING total_sales > 50
    `);
    console.log(rows);

    // 6. Use of aggregate functions
    console.log("\n6. Average order value and total number of orders:");
    [rows] = await connection.execute(`
      SELECT AVG(total_cost) AS avg_order_value, COUNT(*) AS total_orders
      FROM 订单
    `);
    console.log(rows);

    // 7. Self-join (assuming we want to find businesses in the same area)
    console.log("\n7. Businesses in the same area:");
    [rows] = await connection.execute(`
      SELECT a.name AS business1, b.name AS business2, a.address
      FROM 商家 a
      JOIN 商家 b ON LEFT(a.address, 3) = LEFT(b.address, 3) AND a.id < b.id
    `);
    console.log(rows);

  } catch (error) {
    console.error('Error executing queries:', error);
  } finally {
    await connection.end();
  }
}

runQueries();

No Output

Run the code to generate an output.

/*
Navicat Premium Dump SQL

Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 80036 (8.0.36)
Source Host : localhost:3306
Source Schema : waimai

Target Server Type : MySQL
Target Server Version : 80036 (8.0.36)
File Encoding : 65001

Date: 24/12/2024 12:39:54
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Table structure for 商品

-- Records of 商品

-- Table structure for 商家

-- Records of 商家

-- Table structure for 客户

-- Records of 客户

-- Table structure for 订单

-- Records of 订单

-- View structure for order_details

-- Triggers structure for table 订单

SET FOREIGN_KEY_CHECKS = 1;
根据这个数据库写出单表查询、多表查询、排序、分组、分组后的帅选、统计函数的应用、自身连接等内容

I'll provide you with a series of SQL queries that demonstrate various database operations based on the provided schema. I'll use comments to explain each query.

SQL Queries

queries.js

This script demonstrates various SQL operations:

Single table query: Retrieves all customers.
Multi-table query: Joins the 订单 (order), 客户 (customer), and 商品 (product) tables to show order details.
Sorting: Lists all products sorted by price in descending order.
Grouping: Calculates total sales for each business by grouping orders.
Filtering after grouping: Shows businesses with total sales over 50.
Use of aggregate functions: Calculates the average order value and total number of orders.
Self-join: Finds businesses in the same area by comparing the first three characters of their addresses.

465d ago

const mysql = require('mysql2/promise');

async function runQueries() {
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'your_username',
    password: 'your_password',
    database: 'waimai'
  });

  try {
    // 1. Single table query
    console.log("1. List all customers:");
    let [rows] = await connection.execute('SELECT * FROM 客户');
    console.log(rows);

    // 2. Multi-table query using JOIN
    console.log("\n2. List all orders with customer and product details:");
    [rows] = await connection.execute(`
      SELECT 订单.id, 客户.name AS customer_name, 商品.name AS product_name, 订单.number, 订单.total_cost
      FROM 订单
      JOIN 客户 ON 订单.customer_id = 客户.id
      JOIN 商品 ON 订单.商品_id = 商品.id
    `);
    console.log(rows);

    // 3. Sorting
    console.log("\n3. List all products sorted by price in descending order:");
    [rows] = await connection.execute('SELECT * FROM 商品 ORDER BY price DESC');
    console.log(rows);

    // 4. Grouping
    console.log("\n4. Total sales for each business:");
    [rows] = await connection.execute(`
      SELECT 商家.name, SUM(订单.total_cost) AS total_sales
      FROM 商家
      JOIN 商品 ON 商家.id = 商品.business_id
      JOIN 订单 ON 商品.id = 订单.商品_id
      GROUP BY 商家.id
    `);
    console.log(rows);

    // 5. Filtering after grouping
    console.log("\n5. Businesses with total sales over 50:");
    [rows] = await connection.execute(`
      SELECT 商家.name, SUM(订单.total_cost) AS total_sales
      FROM 商家
      JOIN 商品 ON 商家.id = 商品.business_id
      JOIN 订单 ON 商品.id = 订单.商品_id
      GROUP BY 商家.id
      HAVING total_sales > 50
    `);
    console.log(rows);

    // 6. Use of aggregate functions
    console.log("\n6. Average order value and total number of orders:");
    [rows] = await connection.execute(`
      SELECT AVG(total_cost) AS avg_order_value, COUNT(*) AS total_orders
      FROM 订单
    `);
    console.log(rows);

    // 7. Self-join (assuming we want to find businesses in the same area)
    console.log("\n7. Businesses in the same area:");
    [rows] = await connection.execute(`
      SELECT a.name AS business1, b.name AS business2, a.address
      FROM 商家 a
      JOIN 商家 b ON LEFT(a.address, 3) = LEFT(b.address, 3) AND a.id < b.id
    `);
    console.log(rows);

  } catch (error) {
    console.error('Error executing queries:', error);
  } finally {
    await connection.end();
  }
}

runQueries();

No Output

Run the code to generate an output.