MySQL - Node.js 語法：初學者指南

Hello there, future coding superstar! Welcome to our journey into the exciting world of MySQL and Node.js. As your friendly neighborhood computer teacher, I'm here to guide you through this adventure. Don't worry if you've never written a line of code before – we'll start from scratch and build our way up. Ready? Let's dive in!

"mysql" 套件的安裝

Before we can start playing with MySQL in Node.js, we need to set up our playground. Think of it like preparing your art supplies before creating a masterpiece.

Step 1: 安裝 Node.js

First things first, make sure you have Node.js installed on your computer. If you don't, head over to the official Node.js website and download the version suitable for your operating system.

Step 2: 創建新專案

Open your terminal or command prompt and create a new directory for your project:

mkdir mysql-nodejs-tutorial
cd mysql-nodejs-tutorial

Now, let's initialize our project:

npm init -y

This command creates a package.json file, which is like a recipe book for your project, listing all the ingredients (dependencies) you'll need.

Step 3: 安裝 MySQL 套件

Now for the exciting part! Let's install the MySQL package:

npm install mysql

Congratulations! You've just installed your first Node.js package. It's like adding a new tool to your coding toolbox.

NodeJS 函數以存取 MySQL

Now that we have our tools ready, let's learn how to use them. Node.js provides several functions to interact with MySQL. Here's a table of the most common ones:

函數	描述
createConnection()	創建一個連接到 MySQL 資料庫的連接
connect()	建立連接
query()	執行一個 SQL 查詢
end()	關閉連接

Don't worry if these seem mysterious now. We'll explore each one in detail with examples.

基本範例：連接到 MySQL 並執行查詢

Let's put our newfound knowledge into practice with a simple example. We'll connect to a MySQL database, perform a query, and display the results.

const mysql = require('mysql');

// Create a connection to the database
const connection = mysql.createConnection({
host: 'localhost',
user: 'your_username',
password: 'your_password',
database: 'your_database_name'
});

// Establish the connection
connection.connect((err) => {
if (err) {
console.error('Error connecting to the database: ' + err.stack);
return;
}
console.log('Connected to the database as id ' + connection.threadId);
});

// Perform a query
connection.query('SELECT * FROM users', (error, results, fields) => {
if (error) throw error;
console.log('The users are: ', results);
});

// Close the connection
connection.end((err) => {
if (err) {
console.error('Error closing the database connection: ' + err.stack);
return;
}
console.log('Database connection closed.');
});

Now, let's break this down step by step:

1. First, we import the mysql package we installed earlier. It's like telling Node.js, "Hey, we're going to use MySQL today!"

2. We create a connection using mysql.createConnection(). This is like dialing a phone number – we're telling Node.js how to reach our MySQL database.

3. The connect() function actually establishes the connection. If something goes wrong (like entering the wrong password), it will let us know.

4. Once connected, we use the query() function to ask MySQL a question. In this case, we're saying, "Can you show me all the users?"

5. Finally, we close the connection with end(). Always remember to hang up the phone when you're done!

Each step has error handling, which is like having a safety net while you're learning to walk the coding tightrope.

實作練習：創建一個簡單的用户管理系統

Now that we've covered the basics, let's try something a bit more challenging. We're going to create a simple user management system. Don't worry, we'll take it step by step!

const mysql = require('mysql');

// Create a connection
const connection = mysql.createConnection({
host: 'localhost',
user: 'your_username',
password: 'your_password',
database: 'user_management'
});

// Connect to the database
connection.connect((err) => {
if (err) throw err;
console.log('Connected to the database!');

// Create the users table if it doesn't exist
const createTableQuery = `
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE
)
`;

connection.query(createTableQuery, (err) => {
if (err) throw err;
console.log('Users table created or already exists.');

// Insert a new user
const user = { name: 'John Doe', email: '[email protected]' };
connection.query('INSERT INTO users SET ?', user, (err, result) => {
if (err) throw err;
console.log('New user inserted with ID: ' + result.insertId);

// Retrieve all users
connection.query('SELECT * FROM users', (err, results) => {
if (err) throw err;
console.log('All users:', results);

// Close the connection
connection.end((err) => {
if (err) throw err;
console.log('Database connection closed.');
});
});
});
});
});

Let's break down what's happening here:

1. We start by creating a connection, just like in our previous example.

2. Once connected, we create a table called users if it doesn't already exist. This is like setting up a new filing cabinet for our user information.

3. We then insert a new user into our table. Think of this as filling out a new user card and putting it in our filing cabinet.

4. After inserting the user, we retrieve all users from the table. It's like taking all the cards out of our filing cabinet to see what we have.

5. Finally, we close the connection.

This example demonstrates how you can use Node.js and MySQL together to perform common database operations like creating tables, inserting data, and retrieving data.

結論

Congratulations! You've taken your first steps into the world of MySQL and Node.js. We've covered installation, basic syntax, and even created a simple user management system. Remember, learning to code is like learning a new language – it takes practice and patience. Don't be afraid to experiment and make mistakes – that's how we learn!

In our next lesson, we'll dive deeper into more advanced MySQL operations with Node.js. Until then, keep coding and have fun!

Credits: Image by storyset