本文共 2636 字，大约阅读时间需要 8 分钟。

MySQL Operations Guide

1. Database Operations

MySQL operations can be broken down into three main categories: database management, table operations, and data manipulation.

1.1 Understanding Databases

Databases are collections of tables, and tables are collections of columns. Here are some fundamental operations:

Create a Database

create database IF NOT EXISTS westos;

Delete a Database

drop database IF EXISTS westos;

Use a Database

use `westos`; -- Note: Use backticks for special characters

List All Databases

show databases;

1.2 Database Data Types

1.3 Database Field Properties

Unsigned

• Disallows negative values.
• Useful for storing numbers that cannot be negative.

Zero Fill

• Pads the values with zeros if they don't fill the entire width.

Auto Increment

• Automatically assigns the next value in a sequence.
• Best for primary keys.

Non-Null

• Ensures that the database won't accept NULL values for this column.

2. Creating a Table

CREATE TABLE IF NOT EXISTS student (    id INT NOT NULL AUTO_INCREMENT COMMENT 'Student ID',    name VARCHAR(100) NOT NULL DEFAULT '匿名'COMMENT 'Student Name',    pwd VARCHAR(50) NOT NULL DEFAULT '123456' COMMENT 'Password',    sex VARCHAR(10) NOT NULL DEFAULT '男' COMMENT 'Sex',    birth_date DATETIME DEFAULT NULL COMMENT 'Birthday',    address VARCHAR(255) DEFAULT NULL COMMENT 'Address',    email VARCHAR(100) DEFAULT NULL COMMENT 'Email',    PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Table Attributes

• InnoDB：TECHNIQUEكزمان Font Awesome Free

3. Modifying and Deleting Tables

Rename a Table

ALTER TABLE teacher RENAME AS teacher1;

Add a Column

ALTER TABLE teacher1 ADD COLUMN age INT(5);

Modify a Column

ALTER TABLE teacher1 CHANGE age idade INT(3);

Delete a Column

ALTER TABLE teacher1 DROP idade;

Delete a Table

DROP TABLE IF EXISTS teacher1;

4. Table Types

5. Character Set

• Default: utf8
• Configure in my.ini: character-set-server=utf8

6. Database Location

• All databases stored in the data directory.
• Each database corresponds to a single file.

This guide covers essential MySQL operations for database management. Stay curious and keep exploring! If you have any questions, feel free to discuss them below!