SQL 入门：初学者实践指南

add table and 100 Indian cities in the database named 'saman' (we just created)

add one more column 'Population' and add dummy population count

add one more column 'Developers' and add dummy developer count

npm init -y

npm install - save mysql2

npm install express

import mysql from 'mysql2/promise';
import express from 'express';

// TODO: adjust these connection details to match your SingleStore deployment:
const HOST = '<your host end point>';
const USER = 'admin';
const PASSWORD = '<your password>';
const DATABASE = 'saman';

const app = express();
const port = 3009;

app.get('/', async (req, res) => {
    // Create the connection
    const conn = await mysql.createConnection({
        host: HOST,
        user: USER, 
        password: PASSWORD,
        database: DATABASE
    });

    // Execute the query
    const [rows, fields] = await conn.execute('SELECT * FROM cities');

    // Return the results
    res.send(rows);

    await conn.end();
});

app.listen(port, () => {
    console.log(`App running on http://localhost:${port}`);
});

node index.js

-- Create a new database
CREATE DATABASE database_name;

-- Create a new table
CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
);

-- Add a new column
ALTER TABLE table_name ADD column_name datatype;

-- Drop a column
ALTER TABLE table_name DROP COLUMN column_name;

-- Delete a table
DROP TABLE table_name;

-- Delete a database
DROP DATABASE database_name;

-- Insert data into a table
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

-- Update data in a table
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

-- Delete data from a table
DELETE FROM table_name WHERE condition;

- Selecting Data
-- Select all columns
SELECT * FROM table_name;

-- Select specific columns
SELECT column1, column2, ... FROM table_name;

-- Using WHERE clause for filtering
SELECT * FROM table_name WHERE condition;

-- Using multiple conditions with AND, OR
SELECT * FROM table_name WHERE condition1 AND/OR condition2;

-- Sorting data in ascending (ASC) or descending (DESC) order
SELECT * FROM table_name ORDER BY column_name ASC/DESC;

-- Count records
SELECT COUNT(column_name) FROM table_name;

-- Average of a column
SELECT AVG(column_name) FROM table_name;

-- Sum of a column
SELECT SUM(column_name) FROM table_name;

-- INNER JOIN
SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

-- LEFT JOIN
SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;