Master MySQL: Essential Commands, Data Types & Optimization Tips

MySQL Official Documentation

https://dev.mysql.com/doc/refman/5.7/en/

Common MySQL Commands

1. Log in to MySQL:

<code>mysql -u username -p</code>

2. Start or stop the MySQL service:

<code>service mysql start</code>

<code>service mysql stop</code>

3. Check MySQL service status:

<code>service mysql status</code>

4. Show all databases:

<code>show databases</code>

5. Describe a table's columns and types:

<code>describe table_name;</code>

SQL Statement Classification

SQL (Structured Query Language) statements are divided into three categories:

DDL (Data Definition Language) : defines database objects such as databases, tables, columns, and indexes. Common keywords: create, drop, alter.

DML (Data Manipulation Language) : manipulates data within tables. Common keywords: insert, delete, update, select.

DCL (Data Control Language) : controls access permissions. Common keywords: grant, revoke.

MySQL Help Commands

Use ? followed by a keyword to quickly find usage examples:

<code>mysql> ? contents;</code>

Examples:

<code>mysql> ? Data Types;</code>

<code>mysql> ? show create table;</code>

<code>mysql> ? show</code>

<code>mysql> ? int</code>

MySQL Supported Data Types

1. Numeric Types

Common numeric types include TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, each with specific storage sizes and range limits.

2. Date and Time Types

Key points: TIMESTAMP can automatically set and update the current time; defined as

`create_time` timestamp DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

. DATETIME and TIMESTAMP store values to the second; TIMESTAMP uses only 4 bytes.

Be aware of the 2038‑year limitation for TIMESTAMP.

3. String Types

Differences between CHAR(N) and VARCHAR(N): CHAR(N) stores fixed‑length strings (max 255 bytes); shorter values are padded with spaces. VARCHAR(N) stores variable‑length strings (max 65,535 bytes); only the actual length plus 1–2 bytes overhead is used.

Storage size depends on character set: latin1 = 1 byte/char, gbk = 2 bytes/char, utf8 = 3 bytes/char.

Data Type Selection and Optimization Cases

1. Storing Phone Numbers

Use BIGINT instead of CHAR or VARCHAR. A CHAR(11) with utf8 occupies 33 bytes, while BIGINT uses only 8 bytes and can store the 11‑digit number.

2. Storing IP Addresses

Convert IP strings to integers with INET_ATON() and back with INET_NTOA(). Store the result in an unsigned INT (max 4294967295), which exactly matches the numeric value of 255.255.255.255.

<code>INSERT INTO test VALUES (1, INET_ATON('192.168.1.213'));</code>

<code>SELECT id, INET_NTOA(ip) FROM test;</code>

3. Prefer TINYINT over ENUM

4. Use VARBINARY for case‑sensitive variable‑length strings or binary data

Common MySQL Operators

Arithmetic operators (e.g., +, -, *, /) and comparison operators (e.g., =, <>, >, <) are illustrated in the following images:

Common MySQL Functions

String functions, numeric functions, and date‑time functions are shown in the images below:

Common Commands to View MySQL Status

Show server status:

<code>show status;</code>

Filter global status:

<code>show global status like 'Max_used_connections';</code>

Show all variables (vertical format):

<code>show variables\G;</code>

Show variables matching a pattern:

<code>show variables like '%max_con%';</code>

Show full process list (useful for diagnosing "Too many connections"):

<code>show full processlist;</code>

Explain query execution plan for performance analysis:

<code>explain SELECT ...;</code>