MySQL 语法与基础完全指南

MySQL 是最流行的开源关系型数据库管理系统之一，广泛应用于 Web 应用程序开发。本文将全面介绍 MySQL 的基础知识和完整语法结构。

一、MySQL 基础概念

1. 数据库基本术语

• 数据库(Database): 存储数据的集合

• 表(Table): 数据以表格形式组织

• 列(Column): 表中的一个字段

• 行(Row): 表中的一条记录

• 主键(Primary Key): 唯一标识表中每一行的列

• 外键(Foreign Key): 建立表之间关系的列

2. MySQL 数据类型

数值类型

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
FLOAT, DOUBLE
DECIMAL(M,D) - 精确小数

字符串类型

CHAR(M) - 固定长度
VARCHAR(M) - 可变长度
TEXT - 长文本
BLOB - 二进制数据

日期时间类型

DATE - YYYY-MM-DD
TIME - HH:MM:SS
DATETIME - YYYY-MM-DD HH:MM:SS
TIMESTAMP - 时间戳
YEAR - 年份

二、数据库操作

1. 创建数据库

CREATE DATABASE database_name;
CREATE DATABASE IF NOT EXISTS database_name;

2. 选择数据库

USE database_name;

3. 删除数据库

DROP DATABASE database_name;
DROP DATABASE IF EXISTS database_name;

4. 查看数据库

SHOW DATABASES;

三、表操作

1. 创建表

CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...,
PRIMARY KEY (one_or_more_columns)
);

示例：
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
email VARCHAR(100) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_active BOOLEAN DEFAULT TRUE
);

2. 查看表结构

DESCRIBE table_name;
DESC table_name; -- 简写
SHOW COLUMNS FROM table_name;

3. 修改表

添加列

ALTER TABLE table_name
ADD column_name datatype constraints;

修改列

ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype new_constraints;

删除列

ALTER TABLE table_name
DROP COLUMN column_name;

重命名表

RENAME TABLE old_name TO new_name;
ALTER TABLE old_name RENAME TO new_name;

4. 删除表

DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;

5. 查看所有表

SHOW TABLES;

四、数据操作语言(DML)

1. 插入数据

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

多行插入：
INSERT INTO table_name (column1, column2, ...)
VALUES
(value1, value2, ...),
(value1, value2, ...),
...;

2. 查询数据

基本查询：
SELECT column1, column2, ...
FROM table_name;

查询所有列：
SELECT * FROM table_name;

条件查询：
SELECT column1, column2, ...
FROM table_name
WHERE condition;

3. 更新数据

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

4. 删除数据

DELETE FROM table_name WHERE condition;

删除所有数据但保留表结构：
DELETE FROM table_name;
或
TRUNCATE TABLE table_name;

五、数据查询进阶

1. 排序

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

2. 限制结果

SELECT column1, column2, ...
FROM table_name
LIMIT number;

分页查询：
SELECT column1, column2, ...
FROM table_name
LIMIT offset, count;

3. 去重

SELECT DISTINCT column1, column2, ...
FROM table_name;

4. 聚合函数

COUNT(), SUM(), AVG(), MAX(), MIN()

示例：
SELECT COUNT(*) FROM table_name;
SELECT AVG(price) FROM products;

5. 分组

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

6. 过滤分组

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;

7. 连接查询

内连接

SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b ON a.common_field = b.common_field;

左连接

SELECT a.column1, b.column2
FROM table1 a
LEFT JOIN table2 b ON a.common_field = b.common_field;

右连接

SELECT a.column1, b.column2
FROM table1 a
RIGHT JOIN table2 b ON a.common_field = b.common_field;

全连接

SELECT a.column1, b.column2
FROM table1 a
FULL JOIN table2 b ON a.common_field = b.common_field;

8. 子查询

SELECT column1, column2
FROM table1
WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);

六、约束

1. 主键约束

CREATE TABLE table_name (
id INT PRIMARY KEY,
...
);

或
CREATE TABLE table_name (
id INT,
...,
PRIMARY KEY (id)
);

2. 外键约束

CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
...,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

3. 唯一约束

CREATE TABLE table_name (
email VARCHAR(100) UNIQUE,
...
);

4. 非空约束

CREATE TABLE table_name (
username VARCHAR(50) NOT NULL,
...
);

5. 默认值

CREATE TABLE table_name (
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
...
);

6. 检查约束

CREATE TABLE table_name (
age INT CHECK (age >= 18),
...
);

七、索引

1. 创建索引

CREATE INDEX index_name
ON table_name (column1, column2, ...);

2. 唯一索引

CREATE UNIQUE INDEX index_name
ON table_name (column_name);

3. 删除索引

DROP INDEX index_name ON table_name;

八、视图

1. 创建视图

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

2. 使用视图

SELECT * FROM view_name;

3. 修改视图

ALTER VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE new_condition;

4. 删除视图

DROP VIEW IF EXISTS view_name;

九、存储过程和函数

1. 创建存储过程

DELIMITER //
CREATE PROCEDURE procedure_name(parameters)
BEGIN
-- SQL语句
END //
DELIMITER ;

2. 调用存储过程

CALL procedure_name(arguments);

3. 创建函数

DELIMITER //
CREATE FUNCTION function_name(parameters)
RETURNS datatype
BEGIN
-- SQL语句
RETURN value;
END //
DELIMITER ;

4. 调用函数

SELECT function_name(arguments);

十、触发器

1. 创建触发器

DELIMITER //
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON table_name FOR EACH ROW
BEGIN
-- 触发器逻辑
END //
DELIMITER ;

2. 删除触发器

DROP TRIGGER IF EXISTS trigger_name;

十一、事务控制

1. 开始事务

START TRANSACTION;

2. 提交事务

COMMIT;

3. 回滚事务

ROLLBACK;

4. 设置保存点

SAVEPOINT savepoint_name;

5. 回滚到保存点

ROLLBACK TO savepoint_name;

十二、用户和权限管理

1. 创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

2. 授予权限

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';
GRANT SELECT, INSERT ON database_name.table_name TO 'username'@'host';

3. 撤销权限

REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';

4. 刷新权限

FLUSH PRIVILEGES;

5. 删除用户

DROP USER 'username'@'host';

十三、备份与恢复

1. 备份数据库

mysqldump -u username -p database_name > backup.sql

2. 恢复数据库

mysql -u username -p database_name < backup.sql

十四、常用函数

1. 字符串函数

CONCAT(), SUBSTRING(), TRIM(), UPPER(), LOWER(), LENGTH()

2. 数值函数

ROUND(), CEIL(), FLOOR(), ABS(), MOD()

3. 日期函数

NOW(), CURDATE(), CURTIME(), DATE_FORMAT(), DATEDIFF()

4. 条件函数

IF(), CASE WHEN THEN ELSE END, COALESCE(), NULLIF()

总结

本文涵盖了 MySQL 的基础知识和完整语法结构，从数据库和表的创建、数据操作到高级查询、事务控制和用户管理。MySQL 是一个功能强大且灵活的关系型数据库系统，掌握这些基础知识将为您开发数据库驱动的应用程序打下坚实基础。

在实际应用中，还需要根据具体业务需求设计合理的数据库结构，创建适当的索引以提高查询性能，并使用事务来保证数据的一致性。随着对 MySQL 的深入理解，您还可以探索更多高级特性，如分区表、复制、集群等。