MySQL Tables and CRUD Basics: Best Practices for Creating Tables, Inserting Data, and Querying with SELECT - Devuly | Smart Analytics for Developers & Projects

This article focuses on MySQL table management and CRUD fundamentals. It covers three core workflows—creating tables, inserting data, and querying data—to address common beginner pain points such as fragmented SQL knowledge, inconsistent query habits, and inefficient batch writes. Keywords: MySQL, CRUD, SELECT.

Table of Contents

Technical Specification Snapshot

Parameter	Details
Technical Topic	MySQL Table Operations and CRUD
Language	SQL
License/Copyright	CC 4.0 BY-SA (as declared by the source content)
Star Count	Not provided
Core Dependencies	MySQL Server, SQL Client
Core Capabilities	Table creation, insertion, querying, expressions, aliases

MySQL table-level operations are the prerequisite foundation for CRUD.

Before you manipulate data, you must first understand the table lifecycle, including inspection, creation, modification, and deletion. If the table schema is defined incorrectly, it will directly affect the correctness of subsequent INSERT and SELECT operations.

The most common table-level commands are show tables, create table, desc, alter table, and drop table. They correspond to listing tables, creating tables, inspecting schema, changing schema, and deleting tables.

USE school; -- Switch to the target database first
SHOW TABLES; -- List all tables in the current database
DESC student; -- Inspect the schema of the student table

This snippet confirms the current database context and quickly checks existing tables and field definitions.

Clear columns and data types should be the first priority when creating a table.

Data types determine both storage boundaries and how well the schema expresses business meaning. For example, identifiers often use INT or BIGINT, names commonly use VARCHAR, descriptive text can use TEXT, and time-based fields often use DATETIME.

CREATE TABLE student (
  id INT, -- Student ID
  name VARCHAR(20), -- Student name
  gender INT -- Gender: 1 for male, 0 for female
);

This snippet defines a minimally usable student table that is suitable for demonstrating insert and query syntax.

INSERT is the most fundamental way to write data.

INSERT corresponds to Create in CRUD. Its core rule is simple: columns and values must match one-to-one in count and order. If you explicitly specify column names, the values only need to match the order of the listed columns.

INSERT INTO student VALUES (1, '张三', 1); -- Insert a full row in table-defined order
INSERT INTO student (id, name) VALUES (2, '李四'); -- Insert into specified columns
INSERT INTO student (name, id) VALUES ('王五', 3); -- Column order can change, but values must still match

This snippet demonstrates two common patterns: full-row insertion and insertion with explicitly specified columns.

Batch inserts are usually more efficient than multiple single-row inserts.

Databases follow a typical client-server architecture. Every SQL statement introduces one network round trip and one execution overhead. Combining multiple records into a single INSERT statement usually saves both time and transaction cost.

INSERT INTO student VALUES
(5, '赵六', 0), -- First record
(6, '田七', 1), -- Second record
(7, '周八', 0); -- Third record

This snippet writes multiple rows with a single SQL statement, which is better suited for data initialization and batch import scenarios.

Naming conventions affect long-term maintainability.

For database fields, snake_case naming such as student_name is generally recommended. This is a common convention across the database ecosystem and also works better with camel-case mapping in frameworks such as MyBatis.

Compared with studentName, student_name is more stable across SQL, reporting, and cross-language collaboration. Consistency matters more than what feels convenient in the moment.

CREATE TABLE user_profile (
  user_id BIGINT, -- User ID
  student_name VARCHAR(50), -- Field using snake_case naming
  created_at DATETIME -- Creation timestamp
);

This snippet demonstrates field naming that is closer to real-world engineering practice.

SELECT queries should follow the principle of reading only what you need by default.

SELECT corresponds to Retrieve in CRUD. The simplest syntax is select * from table_name;, but this is usually not a best practice in production because it fetches every column.

For large tables, select * can amplify disk I/O, network transfer, and client-side processing cost. A safer approach is to retrieve only the columns that the current business logic actually needs.

SELECT * FROM student; -- Acceptable for learning, but use carefully in production
SELECT name, id FROM student; -- Query only needed columns to reduce data transfer

This snippet contrasts full-column queries with explicit column selection. The latter is usually more controllable.

Expression-based queries can perform lightweight calculations directly.

SELECT does more than read raw columns. It can also perform calculations during query execution, such as score adjustments, aggregation, or derived metrics. This reduces repeated computation in the application layer.

CREATE TABLE exam (
  id BIGINT,
  name VARCHAR(20), -- Name
  chinese FLOAT, -- Chinese score
  math FLOAT, -- Math score
  english FLOAT -- English score
);

SELECT name, chinese + 10 FROM exam; -- Temporarily add 10 points to the Chinese score
SELECT name, chinese + math + english AS total FROM exam; -- Calculate total score and assign an alias

This snippet demonstrates expression-based queries and aliases, which are useful for producing result sets with clearer business meaning.

Aliases can significantly improve the readability of query results.

If you directly output chinese + math + english, the result column name is often long and hard to read. Using AS total makes the result more meaningful and easier for downstream programs to consume.

Table aliases are equally important, especially in multi-table queries, because they shorten SQL statements and reduce ambiguity. Even though this example uses a single table, it is still worth building the habit of explicit naming early.

SELECT name, chinese + math + english AS total
FROM exam AS e; -- Assign the alias e to the table

This snippet reflects the SQL writing principle of prioritizing readability.

The source image is primarily a page element rather than a technical diagram.

AI Visual Insight: This image shows a column cover-style visual used to identify the article as part of a MySQL learning series. It does not contain technical details such as database schema, execution plans, ER diagrams, or query results. Its main value is content classification rather than technical evidence.

You can only move into full CRUD practice after mastering table creation, insertion, and querying.

This article covers the first three and most important steps of SQL fundamentals: define the structure first, write data second, and then read data selectively. Real engineering practice extends further into UPDATE, DELETE, filtering, sorting, pagination, indexing, and transactions.

For beginners, the three most important baseline rules are these: choose reasonable data types, keep insertion order accurate, and stay disciplined about selected columns. If you do these three things well, the cost of learning everything else drops significantly.

FAQ

Why is frequent use of `select *` discouraged in production?

Because it reads every column indiscriminately. On large tables, this can increase disk I/O, network overhead, and client-side processing burden, and it may also expose sensitive columns that were not actually needed.

Which should you prefer: inserting with specified columns or inserting directly with `values (...)`?

In engineering practice, inserting with explicitly specified columns is generally preferred. It is more resilient to schema changes, easier to read, and helps avoid data misalignment when column order changes.

Why is batch insertion usually faster?

Because after multiple rows are merged into one SQL statement, the database performs fewer network round trips and incurs less execution overhead. In many cases, it also reduces transaction management cost, which improves overall throughput.

Core Summary

This article provides a structured walkthrough of MySQL table fundamentals and the CRUD entry workflow. It covers table creation, schema inspection, schema modification and deletion, INSERT, SELECT, expressions, and aliases, while emphasizing the risks of select *, the efficiency of batch inserts, and the importance of naming conventions. It is well suited for beginners who want to build a maintainable SQL foundation quickly.