Master MySQL DQL, DML, and DDL: Essential Syntax and Examples

Preface

Hello everyone, this part is called the DQL section, which is essential for learning MySQL. Below I will introduce other parts of MySQL.

DQL Section

First, the syntax and execution order of SQL statements (numbers 1‑9) are introduced:

select   // query list (7)
from     // table name (1)
join     // join table (2)
on       // join condition (3)
where    // filter condition (4)
group by // grouping fields (5)
having   // filter after grouping (6)
order by // sorting fields (8)
limit [offset] , size; // limit rows (9)

When writing SQL queries, these clauses are typically used to retrieve the desired data. Mastering them requires practice with SQL exercises, analyzing problems, and eventually implementing requirements quickly.

DML (Data Manipulation Language) Section

The following are the syntax and examples for database manipulation:

Insert Data

-- Method 1:
INSERT INTO table_name VALUES (...);

-- Example:
INSERT INTO beauty VALUES (17, '关晓彤', '女', NULL, '18788888888', NULL, NULL);

-- Method 2:
INSERT INTO table_name SET col1 = val1, col2 = val2, ...;

-- Example:
INSERT INTO beauty SET id = 18, name = '张静怡', phone = '19088888888';

Update Data

-- Single-table update:
UPDATE table_name SET column = value WHERE condition;

-- Example:
UPDATE boys SET boyName = '李晨', userCP = 50 WHERE id = 3;

-- Multi-table update (example 1):
UPDATE boys bo, beauty b
SET b.boyfriend_id = 3
WHERE bo.id = b.boyfriend_id AND bo.id IS NULL;

-- Multi-table update (example 2):
UPDATE boys bo
RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
SET b.boyfriend_id = 3
WHERE bo.id IS NULL;

Delete Data

-- Single-table delete:
DELETE FROM table_name WHERE condition;

-- Example:
DELETE FROM beauty WHERE phone LIKE '19%';

-- Multi-table delete (method 1):
DELETE b FROM beauty b
INNER JOIN boys bo ON b.boyfriend_id = bo.id
WHERE bo.boyName = '汪峰';

-- Multi-table delete (method 2):
DELETE FROM beauty b, boys bo WHERE b.boyfriend_id = bo.id AND bo.boyName = '汪峰';

-- Truncate (cannot use WHERE):
TRUNCATE TABLE boys;  -- clears the table

Comparison of DELETE and TRUNCATE:

DELETE can use WHERE; TRUNCATE cannot.

TRUNCATE is slightly more efficient.

After DELETE, auto‑increment continues from the last value; after TRUNCATE, it resets to 1.

DELETE returns affected rows; TRUNCATE returns no value.

TRUNCATE cannot be rolled back; DELETE can be rolled back.

DDL (Data Definition Language) Section

The following are the syntax and examples for defining databases and tables:

Create

# Database:
DROP DATABASE IF EXISTS books;
CREATE DATABASE IF NOT EXISTS books;

# Table:
DROP TABLE IF EXISTS books;
CREATE TABLE IF NOT EXISTS books (
    id INT,
    bName VARCHAR(20)
);

Alter

# Database charset:
ALTER DATABASE books CHARACTER SET 'utf8';

# Table modifications:
ALTER TABLE author ADD COLUMN age INT;
ALTER TABLE author DROP COLUMN age;
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;
ALTER TABLE author RENAME TO book_author;
ALTER TABLE table_name ADD FOREIGN KEY (column_name) REFERENCES other_table(column_name);

Drop

# Database:
DROP DATABASE IF EXISTS books;

# Table:
DROP TABLE IF EXISTS book_author;

Table Copy

# Copy structure only:
CREATE TABLE new_table LIKE author;

# Copy structure and data:
CREATE TABLE new_table SELECT * FROM author;

# Copy with condition:
CREATE TABLE new_table SELECT id, au_name FROM author WHERE 0;  -- structure only
CREATE TABLE new_table SELECT id, au_name FROM author WHERE 1;  -- structure + data

Common Data Types

Integer: int, bigint
Fixed‑point decimal: DECIMAL, NUMERIC
Floating‑point: float, double
Short text: char, varchar
Long text: text, blob
Date/time: date, datetime, timestamp
When uncertain, use varchar(255) for flexible length.

That concludes today's overview; more big‑data technology fundamentals will be shared daily.

DML mind map:

DDL mind map:

Source link: https://www.cnblogs.com/cjybigdatablog/p/18326252 (copyright belongs to original author).