MySQL table design tutorial: Create a simple questionnaire

MySQL table design tutorial: Create a simple questionnaire

In terms of data storage and management, MySQL is a commonly used relational database management system. In order to better understand the MySQL table design process, we will take creating a simple questionnaire as an example.

First, we need to create a database to store our questionnaire data. Assuming that our database is named survey_db, we can use the following SQL command to create the database:

CREATE DATABASE survey_db;

Next, we need to create a table in the database to store the survey questions and Answer. Assuming that our table is named questions, we can use the following SQL command to create the table:

USE survey_db;

CREATE TABLE questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question_text VARCHAR(255) NOT NULL
);

In the above SQL command, we defined two columns, which are id and question_text. The id column is an auto-increasing integer type that serves as the unique identifier of the question. question_textThe column is a non-empty string type used to store the text of the question.

After creating the question table, we can insert some questions into the table:

INSERT INTO questions (question_text) VALUES
    ('你最喜欢的颜色是什么？'),
    ('你最喜欢的电影类型是什么？'),
    ('你最喜欢的食物是什么？');

Next, we need to create a table to store the user's answers to the questions. Suppose our table is named answers, we can use the following SQL command to create the table:

CREATE TABLE answers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question_id INT,
    answer_text VARCHAR(255) NOT NULL,
    FOREIGN KEY (question_id) REFERENCES questions(id)
);

In the above SQL command, we defined three columns, namely id, question_id and answer_text. The id column is an auto-increasing integer type that serves as the unique identifier of the answer. The question_id column is an integer type used to associate the id of the question. answer_textThe column is a non-empty string type used to store the text of the answer.

After creating the answer table, we can insert some answers into the table:

INSERT INTO answers (question_id, answer_text) VALUES
    (1, '红色'),
    (1, '蓝色'),
    (2, '喜剧片'),
    (2, '动作片'),
    (3, '披萨'),
    (3, '寿司');

So far, we have successfully created a simple questionnaire. Through the above table design, we can easily store and manage questionnaire data.

When we need to query the answer to a question, we can use the following SQL command:

SELECT question_text, answer_text
FROM questions JOIN answers
ON questions.id = answers.question_id
WHERE questions.id = 1;

In the above SQL command, we use the JOIN keyword to associate the question table with the answer table. Through the JOIN operation, we can find the corresponding question text and answer based on the id of the question.

Summary:

Through the above MySQL table design tutorial, we learned how to create a simple questionnaire. In practical applications, we can expand and adjust the table structure as needed to better adapt to actual needs. After mastering the table design method and related SQL operations, we can easily store and query data, improving the efficiency and accuracy of data management. I hope this tutorial will be helpful to everyone in MySQL table design.

The above is the detailed content of MySQL table design tutorial: Create a simple questionnaire. For more information, please follow other related articles on the PHP Chinese website!