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

下面给你整理一份 ​MySQL 常用语句与函数大全及举例，涵盖 DDL、DML、DQL、DCL、常用函数（字符串、数值、日期、聚合、流程控制等），并附带简单示例，方便查阅与实战参考。

---

​一、数据库与表操作（DDL）​​

1. 数据库操作

-- 创建数据库
CREATE DATABASE db_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

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

-- 使用数据库
USE db_test;

-- 删除数据库
DROP DATABASE db_test;

2. 表操作

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    age TINYINT CHECK (age >= 0 AND age <= 120),
    email VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 查看表结构
DESC users;

-- 修改表：添加字段
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 修改字段类型
ALTER TABLE users MODIFY COLUMN age INT;

-- 删除字段
ALTER TABLE users DROP COLUMN phone;

-- 删除表
DROP TABLE users;

​二、数据增删改（DML）​​

-- 插入数据
INSERT INTO users (username, age, email) VALUES 
('Alice', 25, 'alice@example.com'),
('Bob', 30, 'bob@example.com');

-- 更新数据
UPDATE users SET age = 26 WHERE username = 'Alice';

-- 删除数据
DELETE FROM users WHERE age > 100;  -- 安全删除
-- TRUNCATE TABLE users;  -- 清空表（不可回滚）

​三、数据查询（DQL）​​

1. 基础查询

SELECT * FROM users;
SELECT username, age FROM users WHERE age >= 25;
SELECT DISTINCT age FROM users;  -- 去重

2. 排序、限制

SELECT * FROM users ORDER BY age DESC LIMIT 5;  -- 前5条
SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 20;  -- 分页

3. 分组与聚合

SELECT COUNT(*) AS total_users, AVG(age) AS avg_age FROM users;
SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 1;

4. 多表连接

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

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

5. 子查询

SELECT username FROM users 
WHERE age > (SELECT AVG(age) FROM users);

​四、权限与账户（DCL）​​

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

-- 授权
GRANT SELECT, INSERT ON db_test.* TO 'testuser'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

-- 撤销权限
REVOKE INSERT ON db_test.* FROM 'testuser'@'localhost';

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

​五、常用函数大全及举例​

1. 字符串函数

函数	说明	示例
CONCAT(str1,str2,...)	拼接字符串	SELECT CONCAT(username,'-',age) FROM users;
LOWER(str) / UPPER(str)	大小写转换	SELECT UPPER(username) FROM users;
SUBSTRING(str,pos,len)	截取子串	SELECT SUBSTRING(email,1,5) FROM users;
TRIM(str)	去除首尾空格	SELECT TRIM(' abc '); → 'abc'
REPLACE(str,from_str,to_str)	替换	SELECT REPLACE(username,'a','A') FROM users;
LENGTH(str)	字节长度	SELECT LENGTH('你好'); → 6（UTF8下每个汉字3字节）
CHAR_LENGTH(str)	字符长度	SELECT CHAR_LENGTH('你好'); → 2

2. 数值函数

函数	说明	示例
ABS(x)	绝对值	SELECT ABS(-10); → 10
ROUND(x,d)	四舍五入	SELECT ROUND(3.14159,2); → 3.14
CEIL(x) / FLOOR(x)	向上/向下取整	SELECT CEIL(3.2); → 4
RAND()	随机数(0~1)	SELECT RAND();
MOD(x,y)	取余	SELECT MOD(10,3); → 1
POWER(x,y)	x的y次方	SELECT POWER(2,3); → 8

3. 日期时间函数

函数	说明	示例
NOW()	当前日期时间	SELECT NOW(); → ‘2024-06-01 10:20:30’
CURDATE() / CURTIME()	当前日期/时间	SELECT CURDATE(); → ‘2024-06-01’
DATE_FORMAT(date,fmt)	格式化	SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');
DATEDIFF(d1,d2)	相差天数	SELECT DATEDIFF('2024-06-10','2024-06-01'); → 9
TIMESTAMPDIFF(unit,d1,d2)	按单位差值	SELECT TIMESTAMPDIFF(MONTH,'2024-01-01','2024-06-01'); → 5
DATE_ADD(date,INTERVAL expr unit)	日期加减	SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);

4. 聚合函数

函数	说明	示例
COUNT(*)	计数	SELECT COUNT(*) FROM users;
SUM(col)	求和	SELECT SUM(age) FROM users;
AVG(col)	平均值	SELECT AVG(age) FROM users;
MAX(col) / MIN(col)	最大/最小值	SELECT MAX(age) FROM users;
GROUP_CONCAT(col)	分组拼接	SELECT dept,GROUP_CONCAT(name) FROM emp GROUP BY dept;

5. 流程控制函数

函数	说明	示例
IF(expr,v1,v2)	条件判断	SELECT IF(age>=18,'成年','未成年') FROM users;
CASE WHEN … THEN … END	多条件分支

SELECT username,
  CASE 
    WHEN age < 18 THEN '少年'
    WHEN age BETWEEN 18 AND 35 THEN '青年'
    ELSE '中老年'
  END AS age_group
FROM users;
``` |

### 6. 其他常用函数
- `ISNULL(expr)`：判断是否为 NULL  
- `COALESCE(val1,val2,…)`：返回第一个非 NULL 值  
- `UUID()`：生成 UUID  
- `MD5(str)`：加密  


## **六、实用技巧示例**

1. **分页查询**  
```sql
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;  -- 第3页，每页10条

2. ​模糊查询​

SELECT * FROM users WHERE username LIKE 'A%';  -- 以A开头
SELECT * FROM users WHERE email LIKE '%@example.com';

3. ​批量更新（CASE WHEN）​​

UPDATE users SET age = CASE 
  WHEN username='Alice' THEN 27
  WHEN username='Bob' THEN 31
END
WHERE username IN ('Alice','Bob');

4. ​生成序号（变量法，MySQL 8.0可用 ROW_NUMBER()）​​
   MySQL 8.0+：

SELECT ROW_NUMBER() OVER (ORDER BY age DESC) AS rn, username, age FROM users;

---

✅ ​建议​：

• 常用语句熟背，函数和语法结合实战记忆更牢。
• MySQL 8.0 新增窗口函数、CTE（WITH）等高级特性，可在复杂查询中提升效率。
• 在生产环境谨慎使用 DELETE、DROP，记得备份。

如果需要，我可以帮你做一个 ​PDF/Cheatsheet 速查表，方便打印携带。你要吗？