SQL for Beginners: 30 Essential MySQL Functions & Installation Guide

What is SQL?

SQL (Structured Query Language) is the common language for databases. Whether using MySQL, Oracle, or SQL Server, SQL lets you command the database to query, modify, and delete data with a single statement. It is not a complex programming language; its syntax resembles everyday speech, so beginners can grasp the basics quickly.

Why Learn SQL?

Career Essential: Data analysis, backend development, operations, testing, and product management require SQL. Without it, tasks that take others minutes may take hours.

Efficiency Booster: Manually processing 1,000 rows is exhausting; a single SQL statement handles millions of rows in seconds.

Zero Barrier to Entry: No need to memorize complex syntax rules. Remembering a few core keywords is enough to start operating.

MySQL Installation (Windows)

The simplest, most worry-free steps:

Download the installer: Go to the MySQL official site ( https://dev.mysql.com/downloads/mysql/) and download the MySQL Installer for your system. If the official site is slow, use Alibaba Cloud or Huawei Cloud open-source mirror sites, search for MySQL, pick your version, then search for "win" to get the Windows package.

Wizard installation: The downloaded file comes in two types: .msi suffix: Wizard version, beginner-friendly. Double-click, follow prompts, choose Developer Default (safe default for beginners). .zip suffix: Archive version, requires manual environment variable configuration. Beginners should avoid this to prevent pitfalls.

Critical: When setting the root password, either save it in your phone notes or write it on a sticky note. Forgetting it forces a reinstall.

Verify installation: Open CMD, run mysql -u root -p, enter your password. If you enter the MySQL command line, installation succeeded.

Management Tools (GUI over Command Line)

These three tools are beginner-approved:

Navicat: Polished UI, simple operation. Covers database creation, table design, SQL writing, and data export in one flow. Free or trial version is fully sufficient for core needs.

DBeaver: Open-source, free, lightweight, supports all mainstream databases. Ideal if you don't want to spend money.

MySQL Workbench: Official MySQL GUI, can be installed alongside MySQL, no extra config. UI is less attractive but stable and reliable.

Core Query Operations: The Heart of SQL (20 High-Frequency Functions)

Querying is the most fundamental and frequently used SQL operation. Master it and you grasp the essence of SQL.

Basic Query Syntax (Three Patterns)

-- Query entire table (beginners avoid on large tables)
SELECT * FROM table_name;
-- Query specific columns (precise, efficient)
SELECT col1, col2 FROM table_name;
-- Conditional query (most common, e.g., users older than 18)
SELECT col1 FROM table_name WHERE col2 = 'value';

Must-Know Functions (5 Categories)

1. String Functions (5)

CONCAT(col1, col2)

: Concatenate. Example: CONCAT(name, '_', age) → "ZhangSan_20". LENGTH(col): Calculate length, useful for detecting garbled characters. SUBSTR(col, start, len): Substring extraction (start position is 1-based). TRIM(col): Remove leading/trailing spaces. REPLACE(col, old, new): Replace. Example: REPLACE(addr, 'Beijing', 'Beijing City').

2. Numeric Functions (5)

ROUND(col, decimals)

: Round to specified decimals (e.g., price to 2 places). ABS(col): Absolute value. CEIL(col): Round up (10.1 → 11). FLOOR(col): Round down (10.9 → 10). MOD(col1, col2): Modulo (check odd/even: MOD(age, 2)).

3. Date Functions (5)

NOW()

: Current datetime (year-month-day hour:minute:second). DATE(col): Extract date part (ignore time). YEAR(col) / MONTH(col): Extract year / month. DATEDIFF(col1, col2): Difference in days. Example: DATEDIFF(NOW(), create_time).

4. Aggregate Functions (5, Statistics Essentials)

COUNT(*)

: Count rows (total headcount). SUM(col): Sum (total sales). AVG(col): Average (average score). MAX(col) / MIN(col): Maximum / minimum (most expensive / cheapest product).

5. Conditional Function (1)

IF(condition, true_val, false_val)

: Example: IF(score >= 60, 'Pass', 'Fail').

Advanced Techniques (3)

GROUP BY col

: Grouped statistics (e.g., average score per class). ORDER BY col DESC/ASC: Descending / ascending sort. LIMIT n: Restrict row count (top 10: LIMIT 10).

Core Update Operations (3 Functions, Safety First)

Basic Syntax (Always Add WHERE!)

UPDATE table_name SET col1 = new_val, col2 = new_val WHERE condition; -- Safe update
UPDATE table_name SET col1 = new_val; -- Full table update (use with extreme caution!)

Common Functions & Examples

NOW()

: Update timestamp → SET update_time = NOW(). IFNULL(col, default): Replace NULL → SET phone = IFNULL(phone, 'Not provided'). CONCAT(): Concatenate for update → SET name = CONCAT(name, '_VIP').

Core Insert Operations (3 Functions, Fast Inserts)

Basic Syntax

INSERT INTO table_name (col1, col2) VALUES (val1, val2); -- Single insert
INSERT INTO table_name (col1, col2) VALUES (val1, val2), (val3, val4); -- Batch insert

Common Functions & Examples (For Inserting New Data)

NOW()

: Record "creation time" → VALUES('ZhangSan', 20, NOW()). UUID(): Generate unique ID (avoid duplicates) → VALUES(UUID(), 'Test data'). IFNULL(): Handle NULL on insert → VALUES('LiSi', IFNULL(input_phone, '13800138000')).

Core Delete Operations (4 Functions, Survival Guide)

Basic Syntax (Always Add WHERE! Backup First!)

DELETE FROM table_name WHERE condition; -- Conditional delete (recommended)
TRUNCATE TABLE table_name; -- Empty entire table (irreversible! Use with extreme caution!)
-- Always backup first, whether via GUI tool or SQL.
-- Limitation: this SQL only copies data and structure;
-- indexes, primary keys, auto-increment are NOT synced.
CREATE TABLE backup_table AS SELECT col1, col2, col3 ... FROM table_name;

Auxiliary Functions & Examples

COUNT(*)

: Count before delete → SELECT COUNT(*) FROM user WHERE age > 60. DATEDIFF(): Delete expired data by time →

DELETE FROM orders WHERE DATEDIFF(NOW(), create_time) > 30

. LENGTH(): Delete invalid data → DELETE FROM user WHERE LENGTH(name) = 0. IS NULL: Delete rows with NULL → DELETE FROM user WHERE phone IS NULL.

Complete 30-Function Checklist

1. CONCAT, 2. LENGTH, 3. SUBSTR, 4. TRIM, 5. REPLACE, 6. ROUND, 7. ABS, 8. CEIL, 9. FLOOR, 10. MOD, 11. NOW, 12. DATE, 13. YEAR, 14. MONTH, 15. DATEDIFF, 16. COUNT, 17. SUM, 18. AVG, 19. MAX, 20. MIN, 21. IF, 22. IFNULL, 23. UUID, 24. LEFT (left substring), 25. RIGHT (right substring), 26. UPPER (uppercase), 27. LOWER (lowercase), 28. CURDATE (date only), 29. CURTIME (time only), 30. TIMESTAMPDIFF (arbitrary time difference).