1. What is a Data Table?¶

In a database, a data table is like an Excel spreadsheet used to store structured data. It consists of multiple columns (fields), where each column defines the data type (e.g., integers, text, dates), and each row represents a specific record. For example, a “students” table might include columns like “student ID”, “name”, “age”, and “class”, with each row corresponding to a student’s information.

2. Creating a Data Table (CREATE TABLE)¶

To create a table, use the CREATE TABLE statement with the following syntax:

CREATE TABLE table_name (
    column1 data_type [constraints],
    column2 data_type [constraints],
    ...
);

Key Elements:
- Table Name: Customized, with a descriptive name (e.g., students).
- Column Name: Customized, avoiding conflicts with keywords (e.g., name is valid, but MySQL keywords should be wrapped in backticks ` if necessary).
- Data Type: Defines the type of data the column can store (e.g., integer INT, string VARCHAR, date DATE).
- Constraints: Rules for data (e.g., primary key PRIMARY KEY, non-null NOT NULL, default value DEFAULT).

Common Data Types Examples:¶

Constraint Examples:¶

• Primary Key (PRIMARY KEY): Uniquely identifies a record (non-repeating, non-null). Typically implemented with INT AUTO_INCREMENT for auto-incrementing values (no manual input needed).
• NOT NULL: The column value must be provided (e.g., name cannot be empty).
• DEFAULT: Uses a default value if not specified during insertion (e.g., age defaults to 18).

Practical Case: Creating a “Students” Table¶

-- Create a students table with auto-incrementing ID, non-null name, default age/gender, and date of birth
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,         -- Primary key, auto-incrementing integer (unique student ID)
    name VARCHAR(50) NOT NULL,                 -- Name, non-null, max 50 characters
    age INT DEFAULT 18,                        -- Age, default 18 (used if not specified during insertion)
    gender VARCHAR(10) DEFAULT 'Male',         -- Gender, default 'Male'
    birth_date DATE                            -- Date of birth (format: YYYY-MM-DD)
);

Tips:
- AUTO_INCREMENT works only with integers and must be a primary key or unique key.
- VARCHAR requires a length (e.g., VARCHAR(50)); omitting it causes an error.
- Commas separate columns; no trailing comma after the last column.

3. Viewing Table Structure¶

After creating a table, use DESCRIBE or SHOW COLUMNS to check its structure:

-- View structure of the students table
DESCRIBE students;
-- Or shorthand:
SHOW COLUMNS FROM students;

Output:
Displays column names, data types, nullability (NULL/NO), key type (e.g., PRI for primary key), default values, etc.

4. Operating on Data Tables (CRUD)¶

4.1 Insert Data (INSERT INTO)¶

Add new records with:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Example: Insert a student record (using default values for unspecified columns):

INSERT INTO students (name, birth_date) VALUES ('Zhang San', '2003-05-10');
-- Notes:
-- 1. Strings (e.g., name, birth_date) use single quotes; numbers (e.g., age) do not.
-- 2. The auto-incrementing ID is generated automatically by the database.
-- 3. Avoid omitting the column list (risky for clarity):
INSERT INTO students VALUES (NULL, 'Li Si', 19, 'Female', '2004-03-15');  -- Not recommended

4.2 Query Data (SELECT)¶

Retrieve data from a table with:

SELECT column1, column2, ... FROM table_name [WHERE condition];

Examples:
- Get all student information:

  SELECT * FROM students;  -- * = "all columns"

• Get names and ages of students over 18:

  SELECT name, age FROM students WHERE age > 18;

4.3 Update Data (UPDATE)¶

Modify existing records with:

UPDATE table_name SET column1=value1, column2=value2, ... [WHERE condition];

Critical: Always include WHERE; otherwise, all records will be updated!

Example: Update Zhang San’s age to 20:

UPDATE students SET age = 20 WHERE name = 'Zhang San';
-- Error example (no WHERE, dangerous!): UPDATE students SET age = 20;  -- Updates all students' age to 20

4.4 Delete Data (DELETE)¶

Remove records with:

DELETE FROM table_name [WHERE condition];

Critical: Always include WHERE; otherwise, the entire table will be emptied!

Example: Delete the student with ID=1:

DELETE FROM students WHERE id = 1;
-- Error example (no WHERE, dangerous!): DELETE FROM students;  -- Clears all records

5. Notes¶

1. Syntax Correctness:
   - Strings use single quotes (e.g., 'Zhang San'), while numbers/dates do not (e.g., 20 or '2003-05-10').
   - Column/table names are recommended to be lowercase to avoid keyword conflicts (e.g., `date` for reserved words).

2. Data Type Matching:
   - Inserted values must match the column’s data type (e.g., VARCHAR columns cannot store numbers).

3. Primary Key Uniqueness:
   - AUTO_INCREMENT ensures unique values; avoid manual insertion of duplicate primary keys.

4. Avoid Accidental Operations:
   - Always use WHERE in UPDATE and DELETE to prevent mass data modification/deletion.

6. Summary¶

Creating and operating on tables is a core SQL skill. Master CREATE TABLE (structure), INSERT/SELECT/UPDATE/DELETE (CRUD), and constraints (primary key, non-null). Practice examples and focus on syntax details to quickly get started. Next, explore advanced topics like complex queries and indexes.