MySQL语法清单
数据库操作
创建数据库
-- 创建数据库
CREATE DATABASE database_name;
-- 创建数据库并指定字符集
CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 如果不存在则创建
CREATE DATABASE IF NOT EXISTS database_name;
删除数据库
-- 删除数据库
DROP DATABASE database_name;
-- 如果存在则删除
DROP DATABASE IF EXISTS database_name;
使用数据库
-- 选择数据库
USE database_name;
-- 查看当前使用的数据库
SELECT DATABASE();
查看数据库
-- 查看所有数据库
SHOW DATABASES;
-- 查看数据库创建语句
SHOW CREATE DATABASE database_name;
表操作
创建表
-- 基本创建表语法
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
...
);
-- 完整示例
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL,
password VARCHAR(255) NOT NULL,
age INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 如果不存在则创建
CREATE TABLE IF NOT EXISTS table_name (...);
-- 根据其他表创建表
CREATE TABLE new_table AS SELECT * FROM existing_table;
CREATE TABLE new_table LIKE existing_table;
修改表结构
-- 添加列
ALTER TABLE table_name ADD column_name datatype constraint;
ALTER TABLE table_name ADD COLUMN column_name datatype constraint;
-- 删除列
ALTER TABLE table_name DROP COLUMN column_name;
-- 修改列
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;
ALTER TABLE table_name CHANGE old_column_name new_column_name datatype;
-- 重命名表
ALTER TABLE old_table_name RENAME TO new_table_name;
RENAME TABLE old_table_name TO new_table_name;
-- 添加主键
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
-- 删除主键
ALTER TABLE table_name DROP PRIMARY KEY;
-- 添加外键
ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES other_table(column_name);
-- 删除外键
ALTER TABLE table_name DROP FOREIGN KEY fk_name;
删除表
-- 删除表
DROP TABLE table_name;
-- 如果存在则删除
DROP TABLE IF EXISTS table_name;
-- 清空表数据但保留结构
TRUNCATE TABLE table_name;
查看表信息
-- 查看所有表
SHOW TABLES;
-- 查看表结构
DESC table_name;
DESCRIBE table_name;
SHOW COLUMNS FROM table_name;
-- 查看表创建语句
SHOW CREATE TABLE table_name;
-- 查看表状态
SHOW TABLE STATUS LIKE 'table_name';
数据类型
数值类型
-- 整数类型
TINYINT -- 1字节，-128到127
SMALLINT -- 2字节，-32768到32767
MEDIUMINT -- 3字节，-8388608到8388607
INT -- 4字节，-2147483648到2147483647
BIGINT -- 8字节，非常大的整数范围
-- 无符号整数（在类型后加UNSIGNED）
INT UNSIGNED
-- 浮点数类型
FLOAT -- 4字节单精度浮点数
DOUBLE -- 8字节双精度浮点数
DECIMAL(M,D) -- 精确的小数类型，M是总位数，D是小数位数
-- 示例
price DECIMAL(10,2) -- 总共10位，小数点后2位
字符串类型
-- 定长字符串
CHAR(n) -- 定长字符串，最大255字符
-- 变长字符串
VARCHAR(n) -- 变长字符串，最大65535字符
-- 文本类型
TINYTEXT -- 最大255字符
TEXT -- 最大65535字符
MEDIUMTEXT -- 最大16777215字符
LONGTEXT -- 最大4294967295字符
-- 二进制类型
BINARY(n) -- 定长二进制
VARBINARY(n) -- 变长二进制
BLOB -- 二进制大对象
日期时间类型
DATE -- 日期 'YYYY-MM-DD'
TIME -- 时间 'HH:MM:SS'
DATETIME -- 日期时间 'YYYY-MM-DD HH:MM:SS'
TIMESTAMP -- 时间戳
YEAR -- 年份 'YYYY'
-- 示例
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
其他类型
-- 枚举类型
ENUM('value1', 'value2', 'value3')
-- 集合类型
SET('value1', 'value2', 'value3')
-- JSON类型（MySQL 5.7+）
JSON
-- 示例
status ENUM('active', 'inactive', 'pending')
tags SET('tag1', 'tag2', 'tag3')
data JSON
增删改查操作
插入数据 (INSERT)
-- 插入单条记录
INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);
-- 插入多条记录
INSERT INTO table_name (column1, column2, column3) VALUES
(value1, value2, value3),
(value4, value5, value6),
(value7, value8, value9);
-- 插入所有列的数据
INSERT INTO table_name VALUES (value1, value2, value3);
-- 从其他表插入数据
INSERT INTO table_name (column1, column2) SELECT column1, column2 FROM other_table;
-- 如果重复则忽略
INSERT IGNORE INTO table_name (column1, column2) VALUES (value1, value2);
-- 如果重复则更新
INSERT INTO table_name (column1, column2) VALUES (value1, value2)
ON DUPLICATE KEY UPDATE column2 = VALUES(column2);
-- 替换插入
REPLACE INTO table_name (column1, column2) VALUES (value1, value2);
查询数据 (SELECT)
-- 基本查询
SELECT * FROM table_name;
SELECT column1, column2 FROM table_name;
-- 去重查询
SELECT DISTINCT column_name FROM table_name;
-- 条件查询
SELECT * FROM table_name WHERE condition;
-- 排序
SELECT * FROM table_name ORDER BY column_name ASC; -- 升序
SELECT * FROM table_name ORDER BY column_name DESC; -- 降序
SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC; -- 多列排序
-- 限制结果数量
SELECT * FROM table_name LIMIT 10; -- 前10条
SELECT * FROM table_name LIMIT 5, 10; -- 从第6条开始的10条
SELECT * FROM table_name LIMIT 10 OFFSET 5; -- 跳过前5条，取10条
-- 分组查询
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
-- 分组后过滤
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1;
-- 条件运算符
WHERE column = value
WHERE column != value
WHERE column <> value
WHERE column > value
WHERE column < value
WHERE column >= value
WHERE column <= value
WHERE column BETWEEN value1 AND value2
WHERE column IN (value1, value2, value3)
WHERE column NOT IN (value1, value2, value3)
WHERE column IS NULL
WHERE column IS NOT NULL
WHERE column LIKE 'pattern' -- %表示任意字符，_表示单个字符
WHERE column NOT LIKE 'pattern'
WHERE column REGEXP 'pattern' -- 正则表达式
-- 逻辑运算符
WHERE condition1 AND condition2
WHERE condition1 OR condition2
WHERE NOT condition
更新数据 (UPDATE)
-- 基本更新
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
-- 更新所有记录（危险操作）
UPDATE table_name SET column1 = value1;
-- 使用表达式更新
UPDATE table_name SET price = price * 1.1 WHERE category = 'electronics';
-- 多表更新
UPDATE table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id
SET t1.column1 = value1, t2.column2 = value2
WHERE condition;
-- 限制更新数量
UPDATE table_name SET column1 = value1 WHERE condition LIMIT 10;
删除数据 (DELETE)
-- 基本删除
DELETE FROM table_name WHERE condition;
-- 删除所有记录（危险操作）
DELETE FROM table_name;
-- 多表删除
DELETE t1, t2 FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id
WHERE condition;
-- 限制删除数量
DELETE FROM table_name WHERE condition LIMIT 10;
-- 快速清空表
TRUNCATE TABLE table_name;
索引操作
创建索引
-- 创建普通索引
CREATE INDEX index_name ON table_name (column_name);
-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column_name);
-- 创建复合索引
CREATE INDEX index_name ON table_name (column1, column2, column3);
-- 在表创建时创建索引
CREATE TABLE table_name (
id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100),
INDEX idx_name (name),
UNIQUE INDEX idx_email (email)
);
-- 添加索引到已存在的表
ALTER TABLE table_name ADD INDEX index_name (column_name);
ALTER TABLE table_name ADD UNIQUE INDEX index_name (column_name);
-- 创建全文索引
CREATE FULLTEXT INDEX index_name ON table_name (column_name);
ALTER TABLE table_name ADD FULLTEXT INDEX index_name (column_name);
删除索引
-- 删除索引
DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;
-- 删除主键
ALTER TABLE table_name DROP PRIMARY KEY;
查看索引
-- 查看表的索引
SHOW INDEX FROM table_name;
SHOW INDEXES FROM table_name;
-- 查看索引使用情况
EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';
约束条件
主键约束 (PRIMARY KEY)
-- 单列主键
CREATE TABLE table_name (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50)
);
-- 复合主键
CREATE TABLE table_name (
id1 INT,
id2 INT,
name VARCHAR(50),
PRIMARY KEY (id1, id2)
);
-- 添加主键
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
-- 删除主键
ALTER TABLE table_name DROP PRIMARY KEY;
外键约束 (FOREIGN KEY)
-- 创建表时添加外键
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
product_name VARCHAR(100),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- 添加外键约束
ALTER TABLE table_name ADD CONSTRAINT fk_name
FOREIGN KEY (column_name) REFERENCES other_table(column_name);
-- 外键约束选项
ON DELETE CASCADE -- 删除主表记录时，从表记录也删除
ON DELETE SET NULL -- 删除主表记录时，从表外键设为NULL
ON DELETE RESTRICT -- 有从表记录时，不允许删除主表记录
ON UPDATE CASCADE -- 更新主表记录时，从表记录也更新
ON UPDATE SET NULL -- 更新主表记录时，从表外键设为NULL
ON UPDATE RESTRICT -- 有从表记录时，不允许更新主表记录
-- 删除外键约束
ALTER TABLE table_name DROP FOREIGN KEY fk_name;
唯一约束 (UNIQUE)
-- 单列唯一
CREATE TABLE table_name (
id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE,
username VARCHAR(50) UNIQUE
);
-- 复合唯一
CREATE TABLE table_name (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
UNIQUE (first_name, last_name)
);
-- 添加唯一约束
ALTER TABLE table_name ADD CONSTRAINT uk_name UNIQUE (column_name);
-- 删除唯一约束
ALTER TABLE table_name DROP INDEX uk_name;
非空约束 (NOT NULL)
-- 创建时指定
CREATE TABLE table_name (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL
);
-- 修改为非空
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
-- 修改为可空
ALTER TABLE table_name MODIFY column_name datatype NULL;
检查约束 (CHECK) - MySQL 8.0+
-- 创建表时添加检查约束
CREATE TABLE table_name (
id INT PRIMARY KEY,
age INT CHECK (age >= 0 AND age <= 150),
status ENUM('active', 'inactive') CHECK (status IN ('active', 'inactive'))
);
-- 添加检查约束
ALTER TABLE table_name ADD CONSTRAINT chk_name CHECK (condition);
-- 删除检查约束
ALTER TABLE table_name DROP CHECK chk_name;
默认值约束 (DEFAULT)
-- 创建表时指定默认值
CREATE TABLE table_name (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
status VARCHAR(20) DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 修改默认值
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 'new_default_value';
-- 删除默认值
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;
连接查询
内连接 (INNER JOIN)
-- 基本内连接
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
-- 等值连接（古老语法）
SELECT columns
FROM table1, table2
WHERE table1.column = table2.column;
-- 示例
SELECT u.username, o.order_date, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 多表连接
SELECT u.username, o.order_date, p.product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;
左外连接 (LEFT JOIN)
-- 左外连接
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
-- 示例：查询所有用户和他们的订单（包括没有订单的用户）
SELECT u.username, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- 查询没有订单的用户
SELECT u.username
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;
右外连接 (RIGHT JOIN)
-- 右外连接
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
-- 示例
SELECT u.username, o.order_date
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;
全外连接 (FULL OUTER JOIN)
-- MySQL不直接支持FULL OUTER JOIN，可以用UNION模拟
SELECT columns FROM table1 LEFT JOIN table2 ON condition
UNION
SELECT columns FROM table1 RIGHT JOIN table2 ON condition;
自连接 (SELF JOIN)
-- 自连接：查询员工和他们的经理
SELECT e.name AS employee, m.name AS manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id;
-- 查询同一城市的用户
SELECT u1.name AS user1, u2.name AS user2, u1.city
FROM users u1
INNER JOIN users u2 ON u1.city = u2.city AND u1.id != u2.id;
交叉连接 (CROSS JOIN)
-- 交叉连接（笛卡尔积）
SELECT columns
FROM table1
CROSS JOIN table2;
-- 等价写法
SELECT columns
FROM table1, table2;
子查询
标量子查询
-- 返回单个值的子查询
SELECT username, email
FROM users
WHERE age > (SELECT AVG(age) FROM users);
-- 在SELECT子句中使用子查询
SELECT username,
(SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;
列子查询
-- 返回一列值的子查询
SELECT username
FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);
-- 使用ANY/SOME
SELECT username
FROM users
WHERE age > ANY (SELECT age FROM users WHERE city = 'Beijing');
-- 使用ALL
SELECT username
FROM users
WHERE age > ALL (SELECT age FROM users WHERE city = 'Shanghai');
行子查询
-- 返回一行值的子查询
SELECT username, age
FROM users
WHERE (username, age) = (SELECT username, age FROM users WHERE id = 1);
表子查询
-- 返回多行多列的子查询
SELECT *
FROM (SELECT username, age FROM users WHERE age > 18) AS adult_users;
-- 相关子查询
SELECT username
FROM users u1
WHERE age > (SELECT AVG(age) FROM users u2 WHERE u2.city = u1.city);
EXISTS 子查询
-- EXISTS：检查子查询是否返回结果
SELECT username
FROM users
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = users.id);
-- NOT EXISTS
SELECT username
FROM users
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE user_id = users.id);
聚合函数
基本聚合函数
-- 计数
COUNT(*) -- 统计行数（包括NULL）
COUNT(column_name) -- 统计非NULL值的行数
COUNT(DISTINCT column_name) -- 统计不重复非NULL值的行数
-- 求和
SUM(column_name) -- 求和
SUM(DISTINCT column_name) -- 去重求和
-- 平均值
AVG(column_name) -- 平均值
AVG(DISTINCT column_name) -- 去重平均值
-- 最大值和最小值
MAX(column_name) -- 最大值
MIN(column_name) -- 最小值
-- 示例
SELECT
COUNT(*) AS total_users,
COUNT(email) AS users_with_email,
AVG(age) AS average_age,
MAX(age) AS oldest_age,
MIN(age) AS youngest_age,
SUM(salary) AS total_salary
FROM users;
其他聚合函数
-- 标准差
STD(column_name) -- 标准差
STDDEV(column_name) -- 标准差
-- 方差
VARIANCE(column_name) -- 方差
-- 分组连接
GROUP_CONCAT(column_name) -- 将组内值连接成字符串
GROUP_CONCAT(column_name SEPARATOR ';') -- 指定分隔符
GROUP_CONCAT(DISTINCT column_name ORDER BY column_name) -- 去重并排序
-- 示例
SELECT
department,
GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS employees
FROM employees
GROUP BY department;
窗口函数 (MySQL 8.0+)
-- 行号
SELECT username, age, ROW_NUMBER() OVER (ORDER BY age DESC) AS row_num
FROM users;
-- 排名
SELECT username, age,
RANK() OVER (ORDER BY age DESC) AS rank_num,
DENSE_RANK() OVER (ORDER BY age DESC) AS dense_rank_num
FROM users;
-- 分组排名
SELECT username, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;
-- 累计值
SELECT username, salary,
SUM(salary) OVER (ORDER BY salary) AS running_total,
AVG(salary) OVER (ORDER BY salary ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;
-- 前后值
SELECT username, salary,
LAG(salary, 1) OVER (ORDER BY salary) AS prev_salary,
LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary
FROM employees;
字符串函数
字符串基本操作
-- 长度
LENGTH(str) -- 字节长度
CHAR_LENGTH(str) -- 字符长度
CHARACTER_LENGTH(str) -- 字符长度
-- 连接
CONCAT(str1, str2, ...) -- 连接字符串
CONCAT_WS(separator, str1, str2, ...) -- 用分隔符连接
-- 示例
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
SELECT CONCAT_WS('-', year, month, day) AS date_str FROM dates;
字符串截取和查找
-- 截取
LEFT(str, len) -- 从左边截取
RIGHT(str, len) -- 从右边截取
SUBSTRING(str, pos) -- 从指定位置截取到末尾
SUBSTRING(str, pos, len) -- 从指定位置截取指定长度
MID(str, pos, len) -- 同SUBSTRING
-- 查找
LOCATE(substr, str) -- 查找子串位置（从1开始）
POSITION(substr IN str) -- 查找子串位置
INSTR(str, substr) -- 查找子串位置
FIND_IN_SET(str, strlist) -- 在逗号分隔的字符串列表中查找
-- 示例
SELECT SUBSTRING(email, 1, LOCATE('@', email) - 1) AS username FROM users;
字符串转换
-- 大小写转换
LOWER(str) -- 转小写
UPPER(str) -- 转大写
LCASE(str) -- 转小写
UCASE(str) -- 转大写
-- 去除空格
LTRIM(str) -- 去除左边空格
RTRIM(str) -- 去除右边空格
TRIM(str) -- 去除两边空格
TRIM(char FROM str) -- 去除指定字符
-- 替换
REPLACE(str, old_str, new_str) -- 替换字符串
INSERT(str, pos, len, new_str) -- 在指定位置插入/替换
-- 示例
SELECT UPPER(username), TRIM(email) FROM users;
SELECT REPLACE(phone, '-', '') AS clean_phone FROM users;
字符串格式化
-- 重复
REPEAT(str, count) -- 重复字符串
-- 反转
REVERSE(str) -- 反转字符串
-- 填充
LPAD(str, len, pad_str) -- 左填充
RPAD(str, len, pad_str) -- 右填充
-- 格式化
FORMAT(number, decimals) -- 格式化数字
-- 示例
SELECT LPAD(id, 6, '0') AS padded_id FROM users; -- 000001
SELECT FORMAT(price, 2) AS formatted_price FROM products; -- 1,234.56
日期时间函数
当前日期时间
-- 获取当前时间
NOW() -- 当前日期时间
CURRENT_TIMESTAMP -- 当前时间戳
CURDATE() -- 当前日期
CURRENT_DATE -- 当前日期
CURTIME() -- 当前时间
CURRENT_TIME -- 当前时间
SYSDATE() -- 系统当前时间
-- 示例
SELECT NOW(), CURDATE(), CURTIME();
日期时间提取
-- 提取部分
YEAR(date) -- 年份
MONTH(date) -- 月份
DAY(date) -- 日期
HOUR(time) -- 小时
MINUTE(time) -- 分钟
SECOND(time) -- 秒
DAYOFWEEK(date) -- 星期几（1=周日）
DAYOFMONTH(date) -- 月中第几天
DAYOFYEAR(date) -- 年中第几天
WEEK(date) -- 年中第几周
QUARTER(date) -- 季度
-- 提取并格式化
EXTRACT(unit FROM date) -- 提取指定单位
DATE(datetime) -- 提取日期部分
TIME(datetime) -- 提取时间部分
-- 示例
SELECT YEAR(created_at), MONTH(created_at), DAY(created_at) FROM orders;
SELECT EXTRACT(YEAR FROM created_at) AS order_year FROM orders;
日期时间计算
-- 日期加减
DATE_ADD(date, INTERVAL expr unit) -- 日期加法
DATE_SUB(date, INTERVAL expr unit) -- 日期减法
ADDDATE(date, INTERVAL expr unit) -- 日期加法
SUBDATE(date, INTERVAL expr unit) -- 日期减法
-- 单位：YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, WEEK 等
-- 日期差值
DATEDIFF(date1, date2) -- 日期差（天数）
TIMEDIFF(time1, time2) -- 时间差
TIMESTAMPDIFF(unit, start, end) -- 指定单位的时间差
-- 示例
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY) AS next_week;
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH) AS last_month;
SELECT DATEDIFF(NOW(), created_at) AS days_ago FROM orders;
SELECT TIMESTAMPDIFF(HOUR, created_at, NOW()) AS hours_ago FROM orders;
日期时间格式化
-- 格式化输出
DATE_FORMAT(date, format) -- 格式化日期
TIME_FORMAT(time, format) -- 格式化时间
-- 常用格式符
%Y -- 四位年份
%y -- 两位年份
%m -- 月份（01-12）
%c -- 月份（1-12）
%M -- 月份名称
%d -- 日期（01-31）
%e -- 日期（1-31）
%H -- 小时（00-23）
%h -- 小时（01-12）
%i -- 分钟（00-59）
%s -- 秒（00-59）
%W -- 星期名称
%w -- 星期几（0-6）
-- 字符串转日期
STR_TO_DATE(str, format) -- 字符串转日期
-- 示例
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS formatted_date;
SELECT DATE_FORMAT(created_at, '%Y年%m月%d日') AS chinese_date FROM orders;
SELECT STR_TO_DATE('2023-12-25', '%Y-%m-%d') AS christmas;
其他日期函数
-- 最后一天
LAST_DAY(date) -- 月份最后一天
-- 日期名称
DAYNAME(date) -- 星期名称
MONTHNAME(date) -- 月份名称
-- Unix时间戳
UNIX_TIMESTAMP() -- 当前Unix时间戳
UNIX_TIMESTAMP(date) -- 指定日期的Unix时间戳
FROM_UNIXTIME(timestamp) -- Unix时间戳转日期
-- 示例
SELECT LAST_DAY(NOW()) AS month_end;
SELECT DAYNAME(NOW()) AS today_name;
SELECT UNIX_TIMESTAMP(NOW()) AS current_timestamp;
数学函数
基本数学运算
-- 基本运算
ABS(x) -- 绝对值
SIGN(x) -- 符号函数（-1, 0, 1）
MOD(x, y) -- 取模运算
x % y -- 取模运算（同MOD）
-- 幂运算
POWER(x, y) -- x的y次幂
POW(x, y) -- x的y次幂
SQRT(x) -- 平方根
EXP(x) -- e的x次幂
LOG(x) -- 自然对数
LOG10(x) -- 以10为底的对数
LOG2(x) -- 以2为底的对数
-- 示例
SELECT ABS(-5), POWER(2, 3), SQRT(16), MOD(10, 3);
舍入函数
-- 舍入
ROUND(x) -- 四舍五入到整数
ROUND(x, d) -- 四舍五入到d位小数
CEILING(x) -- 向上取整
CEIL(x) -- 向上取整
FLOOR(x) -- 向下取整
TRUNCATE(x, d) -- 截断到d位小数
-- 示例
SELECT ROUND(3.14159, 2), CEILING(3.1), FLOOR(3.9), TRUNCATE(3.14159, 2);
三角函数
-- 三角函数
SIN(x) -- 正弦
COS(x) -- 余弦
TAN(x) -- 正切
ASIN(x) -- 反正弦
ACOS(x) -- 反余弦
ATAN(x) -- 反正切
ATAN2(y, x) -- 两参数反正切
-- 常数
PI() -- 圆周率
-- 示例
SELECT SIN(PI()/2), COS(0), TAN(PI()/4);
随机函数
-- 随机数
RAND() -- 0到1之间的随机数
RAND(seed) -- 使用种子的随机数
-- 示例
SELECT RAND(), FLOOR(RAND() * 100) AS random_int;
SELECT * FROM users ORDER BY RAND() LIMIT 5; -- 随机选择5个用户
条件控制
CASE 语句
-- 简单CASE语句
SELECT
username,
age,
CASE
WHEN age < 18 THEN '未成年'
WHEN age < 60 THEN '成年人'
ELSE '老年人'
END AS age_group
FROM users;
-- 搜索CASE语句
SELECT
product_name,
price,
CASE price
WHEN 0 THEN '免费'
WHEN price < 100 THEN '便宜'
WHEN price < 1000 THEN '中等'
ELSE '昂贵'
END AS price_level
FROM products;
-- 在聚合函数中使用CASE
SELECT
COUNT(CASE WHEN age < 18 THEN 1 END) AS minors,
COUNT(CASE WHEN age >= 18 THEN 1 END) AS adults
FROM users;
IF 函数
-- IF函数
IF(condition, value_if_true, value_if_false)
-- 示例
SELECT
username,
age,
IF(age >= 18, '成年', '未成年') AS status
FROM users;
-- 嵌套IF
SELECT
username,
score,
IF(score >= 90, 'A',
IF(score >= 80, 'B',
IF(score >= 70, 'C', 'F'))) AS grade
FROM students;
IFNULL 和 NULLIF 函数
-- IFNULL：如果第一个参数为NULL，返回第二个参数
IFNULL(expr1, expr2)
-- NULLIF：如果两个参数相等，返回NULL，否则返回第一个参数
NULLIF(expr1, expr2)
-- COALESCE：返回第一个非NULL值
COALESCE(expr1, expr2, expr3, ...)
-- 示例
SELECT
username,
IFNULL(phone, '未提供') AS phone_display,
COALESCE(mobile, phone, '无联系方式') AS contact
FROM users;
视图操作
创建视图
-- 创建视图
CREATE VIEW view_name AS
SELECT columns FROM table_name WHERE condition;
-- 创建或替换视图
CREATE OR REPLACE VIEW view_name AS
SELECT columns FROM table_name WHERE condition;
-- 示例：创建活跃用户视图
CREATE VIEW active_users AS
SELECT id, username, email, created_at
FROM users
WHERE status = 'active' AND last_login > DATE_SUB(NOW(), INTERVAL 30 DAY);
-- 复杂视图示例
CREATE VIEW user_order_summary AS
SELECT
u.id,
u.username,
u.email,
COUNT(o.id) AS total_orders,
COALESCE(SUM(o.total), 0) AS total_spent,
MAX(o.created_at) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email;
使用视图
-- 查询视图
SELECT * FROM view_name;
SELECT columns FROM view_name WHERE condition;
-- 示例
SELECT * FROM active_users WHERE username LIKE 'john%';
SELECT username, total_orders FROM user_order_summary WHERE total_orders > 5;
修改和删除视图
-- 修改视图
ALTER VIEW view_name AS
SELECT new_columns FROM table_name WHERE new_condition;
-- 删除视图
DROP VIEW view_name;
DROP VIEW IF EXISTS view_name;
-- 查看视图定义
SHOW CREATE VIEW view_name;
-- 查看所有视图
SHOW FULL TABLES WHERE Table_type = 'VIEW';
存储过程和函数
创建存储过程
-- 基本存储过程
DELIMITER //
CREATE PROCEDURE procedure_name(IN param1 datatype, OUT param2 datatype)
BEGIN
-- 存储过程体
DECLARE variable_name datatype DEFAULT default_value;
-- SQL语句
SELECT COUNT(*) INTO param2 FROM table_name WHERE column = param1;
END //
DELIMITER ;
-- 示例：获取用户订单数量
DELIMITER //
CREATE PROCEDURE GetUserOrderCount(IN user_id INT, OUT order_count INT)
BEGIN
SELECT COUNT(*) INTO order_count
FROM orders
WHERE user_id = user_id;
END //
DELIMITER ;
-- 带条件控制的存储过程
DELIMITER //
CREATE PROCEDURE UpdateUserStatus(IN user_id INT, IN new_status VARCHAR(20))
BEGIN
DECLARE current_status VARCHAR(20);
-- 获取当前状态
SELECT status INTO current_status FROM users WHERE id = user_id;
-- 条件判断
IF current_status != new_status THEN
UPDATE users SET status = new_status WHERE id = user_id;
SELECT CONCAT('用户状态已更新为: ', new_status) AS message;
ELSE
SELECT '用户状态未发生变化' AS message;
END IF;
END //
DELIMITER ;
调用存储过程
-- 调用存储过程
CALL procedure_name(param1, @param2);
-- 查看输出参数
SELECT @param2;
-- 示例
CALL GetUserOrderCount(1, @count);
SELECT @count AS user_order_count;
CALL UpdateUserStatus(1, 'active');
创建函数
-- 创建函数
DELIMITER //
CREATE FUNCTION function_name(param1 datatype) RETURNS return_datatype
READS SQL DATA -- 或 DETERMINISTIC, NO SQL, MODIFIES SQL DATA
BEGIN
DECLARE result datatype;
-- 函数体
SELECT value INTO result FROM table_name WHERE condition;
RETURN result;
END //
DELIMITER ;
-- 示例：计算用户总消费
DELIMITER //
CREATE FUNCTION GetUserTotalSpent(user_id INT) RETURNS DECIMAL(10,2)
READS SQL DATA
BEGIN
DECLARE total DECIMAL(10,2) DEFAULT 0;
SELECT COALESCE(SUM(total), 0) INTO total
FROM orders
WHERE user_id = user_id;
RETURN total;
END //
DELIMITER ;
-- 使用函数
SELECT username, GetUserTotalSpent(id) AS total_spent FROM users;
删除存储过程和函数
-- 删除存储过程
DROP PROCEDURE IF EXISTS procedure_name;
-- 删除函数
DROP FUNCTION IF EXISTS function_name;
-- 查看存储过程和函数
SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;
SHOW CREATE PROCEDURE procedure_name;
SHOW CREATE FUNCTION function_name;
触发器
创建触发器
-- 创建触发器
CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE
ON table_name
FOR EACH ROW
BEGIN
-- 触发器体
END;
-- 示例：用户创建时自动创建配置文件
DELIMITER //
CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
INSERT INTO user_profiles (user_id, created_at)
VALUES (NEW.id, NOW());
END //
DELIMITER ;
-- 示例：更新时记录修改日志
DELIMITER //
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
INSERT INTO user_audit_log (user_id, old_email, new_email, changed_at)
VALUES (OLD.id, OLD.email, NEW.email, NOW());
END //
DELIMITER ;
-- 示例：删除时的安全检查
DELIMITER //
CREATE TRIGGER before_user_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
DECLARE order_count INT;
SELECT COUNT(*) INTO order_count FROM orders WHERE user_id = OLD.id;
IF order_count > 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '无法删除有订单的用户';
END IF;
END //
DELIMITER ;
查看和删除触发器
-- 查看触发器
SHOW TRIGGERS;
SHOW TRIGGERS LIKE 'pattern';
-- 查看触发器定义
SHOW CREATE TRIGGER trigger_name;
-- 删除触发器
DROP TRIGGER IF EXISTS trigger_name;
事务控制
基本事务操作
-- 开始事务
START TRANSACTION;
-- 或
BEGIN;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
-- 示例：转账操作
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 检查是否有错误，如果有则回滚
IF @@ERROR != 0 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
保存点
-- 创建保存点
SAVEPOINT savepoint_name;
-- 回滚到保存点
ROLLBACK TO SAVEPOINT savepoint_name;
-- 释放保存点
RELEASE SAVEPOINT savepoint_name;
-- 示例
START TRANSACTION;
INSERT INTO table1 VALUES (1, 'data1');
SAVEPOINT sp1;
INSERT INTO table2 VALUES (2, 'data2');
SAVEPOINT sp2;
-- 如果需要，可以回滚到sp1
ROLLBACK TO SAVEPOINT sp1;
COMMIT;
事务隔离级别
-- 查看隔离级别
SELECT @@transaction_isolation;
-- 设置会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 为单个事务设置隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- 事务语句
COMMIT;
锁
-- 表级锁
LOCK TABLES table_name READ; -- 读锁
LOCK TABLES table_name WRITE; -- 写锁
UNLOCK TABLES; -- 释放锁
-- 行级锁（在事务中使用）
SELECT * FROM table_name WHERE condition FOR UPDATE; -- 排他锁
SELECT * FROM table_name WHERE condition LOCK IN SHARE MODE; -- 共享锁
用户和权限管理
用户管理
-- 创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
-- 示例
CREATE USER 'john'@'localhost' IDENTIFIED BY 'password123';
CREATE USER 'admin'@'%' IDENTIFIED BY 'admin_password';
-- 修改用户密码
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';
SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');
-- 删除用户
DROP USER 'username'@'host';
-- 查看用户
SELECT User, Host FROM mysql.user;
权限管理
-- 授予权限
GRANT privileges ON database.table TO 'username'@'host';
-- 常用权限示例
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost'; -- 所有权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'user'@'%'; -- 基本操作权限
GRANT SELECT ON mydb.users TO 'readonly'@'localhost'; -- 只读权限
GRANT CREATE, DROP ON mydb.* TO 'developer'@'localhost'; -- 创建删除权限
-- 授予权限并允许转授
GRANT SELECT ON mydb.* TO 'user'@'localhost' WITH GRANT OPTION;
-- 撤销权限
REVOKE privileges ON database.table FROM 'username'@'host';
-- 示例
REVOKE DELETE ON mydb.* FROM 'user'@'localhost';
REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'host';
-- 查看权限
SHOW GRANTS FOR 'username'@'host';
SHOW GRANTS FOR CURRENT_USER();
-- 刷新权限
FLUSH PRIVILEGES;
角色管理 (MySQL 8.0+)
-- 创建角色
CREATE ROLE 'role_name';
-- 授予权限给角色
GRANT SELECT, INSERT ON mydb.* TO 'read_write_role';
-- 将角色授予用户
GRANT 'read_write_role' TO 'username'@'host';
-- 设置默认角色
SET DEFAULT ROLE 'read_write_role' TO 'username'@'host';
-- 激活角色
SET ROLE 'read_write_role';
-- 删除角色
DROP ROLE 'role_name';
备份和恢复
逻辑备份
-- 使用mysqldump命令（在系统命令行中执行）
-- 备份单个数据库
mysqldump -u username -p database_name > backup.sql
-- 备份多个数据库
mysqldump -u username -p --databases db1 db2 > backup.sql
-- 备份所有数据库
mysqldump -u username -p --all-databases > all_backup.sql
-- 备份单个表
mysqldump -u username -p database_name table_name > table_backup.sql
-- 只备份结构不备份数据
mysqldump -u username -p --no-data database_name > structure_only.sql
-- 只备份数据不备份结构
mysqldump -u username -p --no-create-info database_name > data_only.sql
-- 备份时添加删除表语句
mysqldump -u username -p --add-drop-table database_name > backup.sql
恢复数据
-- 从备份文件恢复（在系统命令行中执行）
mysql -u username -p database_name < backup.sql
-- 在MySQL中执行备份文件
SOURCE /path/to/backup.sql;
-- 或
\. /path/to/backup.sql
二进制日志备份
-- 查看二进制日志
SHOW BINARY LOGS;
-- 查看当前使用的二进制日志
SHOW MASTER STATUS;
-- 刷新二进制日志
FLUSH BINARY LOGS;
-- 从二进制日志恢复（在系统命令行中执行）
mysqlbinlog binlog_file | mysql -u username -p
-- 从指定位置开始恢复
mysqlbinlog --start-position=position binlog_file | mysql -u username -p
-- 从指定时间开始恢复
mysqlbinlog --start-datetime="2023-12-01 10:00:00" binlog_file | mysql -u username -p
表空间备份
-- 导出表空间
FLUSH TABLES table_name FOR EXPORT;
-- 导入表空间
ALTER TABLE table_name DISCARD TABLESPACE;
-- 复制.ibd文件到数据目录
ALTER TABLE table_name IMPORT TABLESPACE;
常用系统函数和变量
系统信息函数
-- 版本和连接信息
VERSION() -- MySQL版本
CONNECTION_ID() -- 连接ID
USER() -- 当前用户
CURRENT_USER() -- 当前用户
DATABASE() -- 当前数据库
SCHEMA() -- 当前模式
CHARSET(str) -- 字符串字符集
COLLATION(str) -- 字符串校对规则
-- 示例
SELECT VERSION(), USER(), DATABASE(), CONNECTION_ID();
系统变量
-- 查看系统变量
SHOW VARIABLES;
SHOW VARIABLES LIKE 'pattern';
-- 查看状态变量
SHOW STATUS;
SHOW STATUS LIKE 'pattern';
-- 设置会话变量
SET SESSION variable_name = value;
SET @@session.variable_name = value;
-- 设置全局变量
SET GLOBAL variable_name = value;
SET @@global.variable_name = value;
-- 常用变量示例
SELECT @@version; -- MySQL版本
SELECT @@datadir; -- 数据目录
SELECT @@port; -- 端口号
SELECT @@max_connections; -- 最大连接数
SELECT @@character_set_server; -- 服务器字符集
SELECT @@sql_mode; -- SQL模式
性能相关
-- 查看进程列表
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
-- 杀死进程
KILL connection_id;
-- 查看引擎状态
SHOW ENGINE INNODB STATUS;
-- 查看表状态
SHOW TABLE STATUS;
-- 分析表
ANALYZE TABLE table_name;
-- 优化表
OPTIMIZE TABLE table_name;
-- 检查表
CHECK TABLE table_name;
-- 修复表
REPAIR TABLE table_name;
总结
这份MySQL基础语法指南涵盖了MySQL数据库的核心功能和语法，包括：
- 数据库和表操作 - 创建、修改、删除数据库和表
- 数据类型 - 数值、字符串、日期时间等类型的使用
- CRUD操作 - 数据的增删改查
- 索引和约束 - 提高查询性能和数据完整性
- 高级查询 - 连接查询、子查询、聚合函数
- 函数 - 字符串、日期时间、数学函数
- 存储过程和函数 - 复杂业务逻辑的封装
- 事务控制 - 保证数据一致性
- 用户权限管理 - 数据库安全
- 备份恢复 - 数据保护
掌握这些基础语法，可以有效地使用MySQL进行数据库开发和管理工作。在实际应用中，建议根据具体需求选择合适的语法和功能。