mysql常用语句与函数大全及举例

MySQL 常用语句与函数大全及举例

一、数据定义语句 (DDL)

1. 数据库操作

-- 创建数据库
CREATE DATABASE testdb;
CREATE DATABASE IF NOT EXISTS testdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 查看所有数据库
SHOW DATABASES;

-- 选择数据库
USE testdb;

-- 删除数据库
DROP DATABASE testdb;
DROP DATABASE IF EXISTS testdb;

-- 修改数据库字符集
ALTER DATABASE testdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. 表操作

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    age INT CHECK (age >= 0),
    salary DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

-- 创建带索引的表
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(10,2),
    order_date DATE,
    INDEX idx_user_id (user_id),
    INDEX idx_order_date (order_date),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 查看表结构
DESCRIBE users;
DESC users;
SHOW COLUMNS FROM users;

-- 查看创建表的SQL
SHOW CREATE TABLE users;

-- 修改表
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users MODIFY COLUMN email VARCHAR(150);
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(15);
ALTER TABLE users DROP COLUMN mobile;
ALTER TABLE users ADD INDEX idx_email (email);

-- 重命名表
RENAME TABLE users TO customers;
ALTER TABLE customers RENAME TO users;

-- 删除表
DROP TABLE users;
DROP TABLE IF EXISTS users;

-- 清空表数据
TRUNCATE TABLE users;

二、数据操作语句 (DML)

1. 插入数据

-- 插入单条记录
INSERT INTO users (username, email, age, salary) 
VALUES ('john', 'john@example.com', 25, 5000.00);

-- 插入多条记录
INSERT INTO users (username, email, age, salary) VALUES
('alice', 'alice@example.com', 30, 6000.00),
('bob', 'bob@example.com', 28, 5500.00),
('charlie', 'charlie@example.com', 35, 7000.00);

-- 插入查询结果
INSERT INTO user_backup (username, email, age)
SELECT username, email, age FROM users WHERE age > 25;

-- 替换数据（存在则替换）
REPLACE INTO users (id, username, email) 
VALUES (1, 'john', 'john_new@example.com');

2. 更新数据

-- 更新所有记录
UPDATE users SET is_active = TRUE;

-- 条件更新
UPDATE users SET salary = salary * 1.1 WHERE age > 30;

-- 更新多个字段
UPDATE users 
SET email = 'new_email@example.com', 
    age = age + 1 
WHERE username = 'john';

-- 使用子查询更新
UPDATE users u
SET salary = (
    SELECT AVG(salary) FROM users WHERE age = u.age
)
WHERE salary < 5000;

3. 删除数据

-- 删除所有记录
DELETE FROM users;

-- 条件删除
DELETE FROM users WHERE age > 60;

-- 删除重复记录（保留id最小的）
DELETE u1 FROM users u1
INNER JOIN users u2 
WHERE u1.id > u2.id 
AND u1.email = u2.email;

三、数据查询语句 (DQL)

1. 基本查询

-- 查询所有列
SELECT * FROM users;

-- 查询特定列
SELECT username, email, age FROM users;

-- 使用别名
SELECT 
    username AS '用户名',
    email AS '邮箱',
    salary * 12 AS '年薪'
FROM users;

-- 去重
SELECT DISTINCT age FROM users;

-- 限制结果
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 5, 10;  -- 跳过5条，取10条

-- 排序
SELECT * FROM users ORDER BY age DESC;
SELECT * FROM users ORDER BY salary DESC, age ASC;

-- 条件查询
SELECT * FROM users WHERE age >= 18 AND salary > 5000;
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
SELECT * FROM users WHERE username LIKE 'j%';
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE age IN (20, 25, 30);
SELECT * FROM users WHERE email IS NOT NULL;

2. 分组和聚合

-- 基本聚合函数
SELECT 
    COUNT(*) AS '总人数',
    AVG(salary) AS '平均工资',
    MAX(salary) AS '最高工资',
    MIN(salary) AS '最低工资',
    SUM(salary) AS '工资总额'
FROM users;

-- 分组统计
SELECT 
    age,
    COUNT(*) AS '人数',
    AVG(salary) AS '平均工资'
FROM users
GROUP BY age
HAVING COUNT(*) > 5  -- HAVING用于分组后筛选
ORDER BY age;

-- 分组后筛选
SELECT 
    age,
    AVG(salary) AS avg_salary
FROM users
WHERE is_active = TRUE
GROUP BY age
HAVING avg_salary > 5000
ORDER BY age DESC;

3. 连接查询

-- 内连接
SELECT 
    u.username,
    o.order_id,
    o.amount,
    o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 左连接
SELECT 
    u.username,
    o.order_id,
    o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 右连接
SELECT 
    u.username,
    o.order_id,
    o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 全外连接（MySQL模拟）
SELECT u.username, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.username, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 自连接
SELECT 
    e1.username AS '员工',
    e2.username AS '上级'
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

4. 子查询

-- WHERE子句中的子查询
SELECT * FROM users 
WHERE salary > (SELECT AVG(salary) FROM users);

-- IN子查询
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- EXISTS子查询
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.amount > 500
);

-- FROM子句中的子查询（派生表）
SELECT 
    age_group,
    COUNT(*) as user_count
FROM (
    SELECT 
        CASE 
            WHEN age < 20 THEN '青少年'
            WHEN age BETWEEN 20 AND 30 THEN '青年'
            WHEN age BETWEEN 31 AND 50 THEN '中年'
            ELSE '老年'
        END AS age_group
    FROM users
) AS temp
GROUP BY age_group;

5. 集合操作

-- UNION（去重）
SELECT username FROM users WHERE age < 30
UNION
SELECT username FROM users WHERE salary > 5000;

-- UNION ALL（不去重）
SELECT username FROM users WHERE age < 30
UNION ALL
SELECT username FROM users WHERE salary > 5000;

-- INTERSECT（MySQL 8.0+）
SELECT username FROM users WHERE age < 30
INTERSECT
SELECT username FROM users WHERE salary > 5000;

-- EXCEPT（MySQL 8.0+）
SELECT username FROM users WHERE age < 30
EXCEPT
SELECT username FROM users WHERE salary > 5000;

6. 分页查询

-- 传统分页
SELECT * FROM users 
ORDER BY id 
LIMIT 20 OFFSET 40;  -- 第三页，每页20条

-- MySQL 8.0+窗口函数分页
WITH ranked_users AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM users
)
SELECT * FROM ranked_users 
WHERE rn BETWEEN 41 AND 60;

四、常用函数

1. 字符串函数

-- 连接字符串
SELECT CONCAT('Hello', ' ', 'World');  -- 'Hello World'
SELECT CONCAT_WS('-', '2023', '01', '01');  -- '2023-01-01'

-- 字符串长度
SELECT LENGTH('Hello');  -- 5（字节数）
SELECT CHAR_LENGTH('你好');  -- 2（字符数）

-- 大小写转换
SELECT LOWER('HELLO');  -- 'hello'
SELECT UPPER('hello');  -- 'HELLO'

-- 修剪字符串
SELECT TRIM('  hello  ');  -- 'hello'
SELECT LTRIM('  hello');  -- 'hello'
SELECT RTRIM('hello  ');  -- 'hello'

-- 子字符串
SELECT SUBSTRING('Hello World', 1, 5);  -- 'Hello'
SELECT SUBSTRING('Hello World', 7);  -- 'World'
SELECT LEFT('Hello World', 5);  -- 'Hello'
SELECT RIGHT('Hello World', 5);  -- 'World'

-- 替换
SELECT REPLACE('Hello World', 'World', 'MySQL');  -- 'Hello MySQL'

-- 查找位置
SELECT INSTR('Hello World', 'World');  -- 7
SELECT LOCATE('or', 'Hello World');  -- 8

-- 填充
SELECT LPAD('123', 5, '0');  -- '00123'
SELECT RPAD('123', 5, '0');  -- '12300'

-- 其他
SELECT REVERSE('Hello');  -- 'olleH'
SELECT REPEAT('Ha', 3);  -- 'HaHaHa'
SELECT SPACE(5);  -- '     '

2. 数值函数

-- 基本运算
SELECT ABS(-10);  -- 10
SELECT CEIL(3.14);  -- 4
SELECT FLOOR(3.14);  -- 3
SELECT ROUND(3.14159, 2);  -- 3.14

-- 数学函数
SELECT POWER(2, 3);  -- 8
SELECT SQRT(16);  -- 4
SELECT MOD(10, 3);  -- 1
SELECT RAND();  -- 随机数 0-1
SELECT RAND() * 100;  -- 0-100随机数

-- 符号函数
SELECT SIGN(-10);  -- -1
SELECT SIGN(0);  -- 0
SELECT SIGN(10);  -- 1

-- 三角函数
SELECT SIN(RADIANS(30));
SELECT COS(RADIANS(60));
SELECT TAN(RADIANS(45));

-- 指数对数
SELECT EXP(1);  -- e ≈ 2.718
SELECT LN(2.718);  -- ≈1
SELECT LOG(10, 100);  -- 2
SELECT LOG10(100);  -- 2

3. 日期时间函数

-- 当前日期时间
SELECT NOW();  -- 2023-01-01 12:00:00
SELECT CURDATE();  -- 2023-01-01
SELECT CURTIME();  -- 12:00:00
SELECT SYSDATE();  -- 系统日期时间

-- 日期提取
SELECT YEAR('2023-01-15');  -- 2023
SELECT MONTH('2023-01-15');  -- 1
SELECT DAY('2023-01-15');  -- 15
SELECT DAYNAME('2023-01-15');  -- 'Sunday'
SELECT DAYOFWEEK('2023-01-15');  -- 1（周日）
SELECT WEEK('2023-01-15');  -- 第几周
SELECT QUARTER('2023-01-15');  -- 1

-- 时间提取
SELECT HOUR('12:30:45');  -- 12
SELECT MINUTE('12:30:45');  -- 30
SELECT SECOND('12:30:45');  -- 45

-- 日期计算
SELECT DATE_ADD('2023-01-01', INTERVAL 7 DAY);  -- 2023-01-08
SELECT DATE_SUB('2023-01-01', INTERVAL 1 MONTH);  -- 2022-12-01
SELECT DATEDIFF('2023-01-15', '2023-01-01');  -- 14
SELECT TIMESTAMPDIFF(DAY, '2023-01-01', '2023-01-15');  -- 14

-- 格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');  -- '2023-01-01 12:00:00'
SELECT TIME_FORMAT('12:30:45', '%H:%i');  -- '12:30'

-- UNIX时间戳
SELECT UNIX_TIMESTAMP();  -- 当前时间戳
SELECT FROM_UNIXTIME(1672531200);  -- 2023-01-01 00:00:00

4. 条件函数

-- IF函数
SELECT 
    username,
    salary,
    IF(salary > 5000, '高薪', '普通') AS salary_level
FROM users;

-- CASE WHEN
SELECT 
    username,
    age,
    CASE 
        WHEN age < 18 THEN '未成年'
        WHEN age BETWEEN 18 AND 35 THEN '青年'
        WHEN age BETWEEN 36 AND 60 THEN '中年'
        ELSE '老年'
    END AS age_group
FROM users;

-- IFNULL/NULL处理
SELECT 
    username,
    IFNULL(email, '未填写') AS email,
    COALESCE(email, phone, '无联系方式') AS contact
FROM users;

-- NULLIF
SELECT NULLIF(10, 10);  -- NULL
SELECT NULLIF(10, 20);  -- 10

5. 聚合函数

-- 基本聚合
SELECT 
    COUNT(*) AS total_users,
    COUNT(DISTINCT age) AS distinct_ages,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary,
    SUM(salary) AS total_salary,
    GROUP_CONCAT(username SEPARATOR ', ') AS user_list
FROM users
GROUP BY age;

-- 统计函数
SELECT 
    STD(salary) AS salary_stddev,
    VARIANCE(salary) AS salary_variance
FROM users;

6. 窗口函数（MySQL 8.0+）

-- 排名函数
SELECT 
    username,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK() OVER (ORDER BY salary DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM users;

-- 聚合窗口函数
SELECT 
    username,
    salary,
    AVG(salary) OVER () AS overall_avg,
    AVG(salary) OVER (PARTITION BY age) AS age_group_avg,
    SUM(salary) OVER (ORDER BY salary) AS running_total
FROM users;

-- 位移函数
SELECT 
    username,
    salary,
    LAG(salary) OVER (ORDER BY salary) AS prev_salary,
    LEAD(salary) OVER (ORDER BY salary) AS next_salary,
    FIRST_VALUE(salary) OVER (ORDER BY salary) AS first_salary,
    LAST_VALUE(salary) OVER (ORDER BY salary) AS last_salary
FROM users;

五、事务控制语句

-- 开始事务
START TRANSACTION;
-- 或
BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT savepoint_name;

-- 回滚到保存点
ROLLBACK TO savepoint_name;

-- 示例
START TRANSACTION;
INSERT INTO users (username, email) VALUES ('test1', 'test1@example.com');
SAVEPOINT sp1;
INSERT INTO users (username, email) VALUES ('test2', 'test2@example.com');
ROLLBACK TO sp1;  -- 回滚第二个插入
COMMIT;  -- 提交第一个插入

六、权限管理语句

-- 创建用户
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'username'@'%' IDENTIFIED BY 'password';

-- 修改密码
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';

-- 授予权限
GRANT SELECT, INSERT ON database.table TO 'username'@'localhost';
GRANT ALL PRIVILEGES ON database.* TO 'username'@'localhost';
GRANT CREATE USER, GRANT OPTION ON *.* TO 'admin'@'localhost';

-- 查看权限
SHOW GRANTS FOR 'username'@'localhost';

-- 撤销权限
REVOKE INSERT ON database.table FROM 'username'@'localhost';

-- 删除用户
DROP USER 'username'@'localhost';

七、实用技巧示例

1. 分页查询优化

-- 常规分页（大数据量可能慢）
SELECT * FROM users ORDER BY id LIMIT 1000000, 20;

-- 优化分页（使用索引）
SELECT * FROM users 
WHERE id > 1000000 
ORDER BY id 
LIMIT 20;

-- 或使用子查询
SELECT * FROM users 
WHERE id >= (
    SELECT id FROM users ORDER BY id LIMIT 1000000, 1
)
LIMIT 20;

2. 递归查询（MySQL 8.0+）

-- 递归查询组织结构
WITH RECURSIVE org_tree AS (
    -- 锚点成员
    SELECT id, name, parent_id, 1 AS level
    FROM organization
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归成员
    SELECT o.id, o.name, o.parent_id, ot.level + 1
    FROM organization o
    INNER JOIN org_tree ot ON o.parent_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, id;

3. 数据备份与恢复

-- 备份表结构
CREATE TABLE users_backup LIKE users;

-- 备份数据
INSERT INTO users_backup SELECT * FROM users;

-- 快速复制表（包含数据）
CREATE TABLE users_copy AS SELECT * FROM users;

-- 复制表结构（不包含数据）
CREATE TABLE users_structure LIKE users;

4. 性能分析

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE age > 25;

-- 详细执行计划（MySQL 8.0+）
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;

-- 查看索引信息
SHOW INDEX FROM users;

-- 分析表
ANALYZE TABLE users;

-- 优化表
OPTIMIZE TABLE users;

八、JSON函数（MySQL 5.7+）

-- 创建JSON数据
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON
);

INSERT INTO products VALUES 
(1, 'Laptop', '{"brand": "Dell", "ram": "16GB", "storage": "512GB SSD"}');

-- 查询JSON
SELECT 
    id,
    name,
    attributes->'$.brand' AS brand,
    JSON_EXTRACT(attributes, '$.ram') AS ram
FROM products;

-- 修改JSON
UPDATE products 
SET attributes = JSON_SET(attributes, '$.ram', '32GB')
WHERE id = 1;

-- 检查JSON
SELECT JSON_VALID('{"name": "John"}');  -- 1
SELECT JSON_TYPE(attributes->'$.brand') FROM products;

-- 聚合JSON数组
SELECT 
    JSON_ARRAYAGG(username) AS all_users
FROM users;

这个大全涵盖了MySQL日常开发中最常用的语句和函数。根据具体需求选择合适的语句和函数，可以提高开发效率并优化数据库性能。