-- 创建用户表CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 新增一列ALTER TABLE users ADD COLUMN age TINYINT UNSIGNED;-- 删除列ALTER TABLE users DROP COLUMN email;-- 重命名表RENAME TABLE users TO accounts;-- 通过表查询创建表DROP TABLE IF EXISTS company_in_out_label_tmp;create table company_in_out_label_tmp asselect *from data_center.t_company_changesWHERE project_name = "地址变更（住所地址、经营场所、驻在地址等变更）" and credit_code != ''
如何为导入数据创建表格
表中数据的增删改（DML）
操作
常用语句
关键点
增（插入行）
INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2); INSERT INTO 表名 SET 列1=值1, 列2=值2; INSERT INTO 表名 SELECT ...;
单行插入或多行：VALUES (...), (...), (...);REPLACE INTO 会在主键/唯一键冲突时先删除旧行再插入。 INSERT ... ON DUPLICATE KEY UPDATE 冲突时执行更新。 insert into ... select ... 利用查询数据进行插入数据。
删（删除行）
DELETE FROM 表名 WHERE 条件;
必须慎用：不加 WHERE 会删除全部行！ 删除全表用 TRUNCATE TABLE 表名; 更快，且重置自增计数器（不可回滚）。 - TRUNCATE 与 DELETE 区别：TRUNCATE 不记日志、不可回滚、重置自增列，DELETE 可加条件、触发触发器、逐行记录日志。 有表连接时需要指明要删除的表： DELETE 表1, 表2 — 删除哪几张表的记录 FROM 表1 JOIN 表2 ON 条件 [WHERE 条件];
改（更新行）
UPDATE 表名 SET 列1 = 值1, 列2 = 值2 WHERE 条件;
必须带 WHERE，否则全表更新。 可结合 ORDER BY 和 LIMIT 控制更新行数。 更新单表：（左连接时没有匹配的字段会视为Null） UPDATE 表1 JOIN 表2 ON 表1.关联字段 = 表2.关联字段 SET 表1.要更新的字段 = 新值 WHERE 条件; 更新多表： UPDATE 表1 JOIN 表2 ON 条件 SET 表1.字段 = 值1, 表2.字段 = 值2 WHERE …;
-- 插入单行INSERT INTO users (username, age) VALUES ('张三', 28);-- 批量插入INSERT INTO users (username, age) VALUES('李四', 25),('王五', 30);-- 插入查询结果INSERT INTO vip_users SELECT * FROM users WHERE age > 28;INSERT INTO hxnew_center.com_base (unifiedSocialCreditCode,companyName,listImportDate)select t1.credit_code,t1.comp_name,CURDATE()from (select * from data_center.t_company_base WHERE biz_date = (select max(biz_date) from data_center.t_company_base WHERE credit_code != '') and credit_code != '') t1left join hxnew_center.com_base t2WHERE t2.unifiedSocialCreditCode is NULL;-- 删除一条记录DELETE FROM users WHERE id = 10;-- 更新指定用户的年龄UPDATE users SET age = 29 WHERE username = '张三';-- 如果希望保留左表所有行，即使右表没有匹配（没有匹配的字段会视为 NULL）UPDATE orders oLEFT JOIN customers c ON o.customer_id = c.idSET o.discount = IF(c.level = 'VIP', 0.1, 0);-- 删除没有订单的客户DELETE cFROM customers cLEFT JOIN orders o ON c.id = o.customer_idWHERE o.id IS NULL;
日期时间处理
MySQL 提供了丰富的日期时间函数，可以灵活地进行获取、提取、运算、格式化和转换。下面按功能分类总结，并附上常用的计算场景和示例。
GROUP_CONCAT([DISTINCT] expr ORDER BY … SEPARATOR …)
行转列，将分组内的值拼接成一个字符串
SELECT CONCAT('Hello', ' ', 'World'); -- Hello WorldSELECT CONCAT_WS('-', '2026', '05', '26'); -- 2026-05-26SELECT GROUP_CONCAT(DISTINCT city ORDER BY city SEPARATOR '/') FROM users;
-- MySQL 8.0+SELECT REGEXP_SUBSTR(address, '[0-9]+(?=号楼)') AS building_no FROM houses;-- 5.7 简易方式：截取“号楼”前的部分再处理（受限）SELECT SUBSTRING_INDEX(address, '号楼', 1) AS prefix FROM houses;-- 再提取末尾数字较为复杂
5. 判断字符串是否包含某子串
-- 返回 1 表示包含SELECT LOCATE('admin', user_name) > 0 AS is_admin FROM users;-- 或用 REGEXPSELECT user_name REGEXP 'admin' FROM users;
6. 逗号分隔字段查询（多值匹配）
-- 表中 tags 列存储 'a,b,c'，查询包含 'b' 的记录SELECT * FROM articles WHERE FIND_IN_SET('b', tags);
如果 ORDER BY 的列是字符串类型（如 VARCHAR）但存的是数字，排序会按字典序而不是数值大小。
-- num_str 列值：'1','10','2','20'SELECT num_str FROM t ORDER BY num_str; -- '1','10','2','20' (字典序)SELECT num_str FROM t ORDER BY CAST(num_str AS UNSIGNED); -- '1','2','10','20'
4. 常用业务场景中的类型转换
4.1 将存储为字符串的数字用于数值排序或计算
-- 正确排序SELECT * FROM products ORDER BY CAST(price_str AS DECIMAL(10,2));-- 计算总和SELECT SUM(CAST(amount_str AS UNSIGNED)) FROM payments;
4.2 数字补零 / 格式化
-- 订单编号：'ORD' + 6位流水号SELECT CONCAT('ORD', LPAD(CAST(id AS CHAR), 6, '0')) AS order_noFROM orders;-- 如果 id 是数字，LPAD 会隐式转为字符串
4.3 提取字符串中的数字并转换为数值
-- MySQL 8.0 正则提取并转换SELECT CAST(REGEXP_SUBSTR('楼层15-16室', '[0-9]+') AS UNSIGNED) AS floor;-- 结果为 15
4.4 安全处理空值或无效值
-- 如果字段可能为 NULL 或非数字，计算时给默认值SELECT IFNULL(CAST(score AS SIGNED), 0) FROM exams;-- 或者用 CASE 判断SELECT CASE WHEN score REGEXP '^[0-9]+$' THEN CAST(score AS SIGNED) ELSE 0 ENDFROM exams;
4.5 判断字符串能否转为日期，并转换
-- 尝试转为日期，如果失败则返回 NULLSELECT IF(STR_TO_DATE(date_str, '%Y-%m-%d') IS NULL, NULL, CAST(date_str AS DATE))FROM raw_data;-- 或者直接用 STR_TO_DATESELECT STR_TO_DATE(date_str, '%Y-%m-%d') FROM raw_data; -- 无法转换返回 NULL
4.6 JSON 字段值提取并转换类型
SELECT JSON_UNQUOTE(JSON_EXTRACT(extra, '$.age')) AS age_str, CAST(JSON_UNQUOTE(JSON_EXTRACT(extra, '$.age')) AS UNSIGNED) AS age_intFROM profiles;-- MySQL 5.7.8+ 也可使用 ->> 操作符SELECT extra->>'$.age' AS age_str, CAST(extra->>'$.age' AS UNSIGNED) AS age_intFROM profiles;
4.7 字符串比较忽略大小写（通过字符集转换实现）
SELECT * FROM users WHERE CONVERT(username USING utf8mb4) = 'admin';-- 或者使用 COLLATE，更规范
4.8 二进制与字符串互转
-- 字符串转二进制（如存储哈希值）SELECT UNHEX(SHA2('hello', 256));-- 二进制转可读字符串SELECT HEX(binary_field) FROM t;
4.9 避免隐式转换带来的”诡异”结果
-- 错误：'a' 被转为 0，导致 WHERE 条件为真SELECT * FROM t WHERE 0 = 'a'; -- 返回所有行！-- 正确做法：使用 CAST 或显式类型确保类型一致SELECT * FROM t WHERE CAST(col AS UNSIGNED) = 0;
总结要点
类型判断多借助 REGEXP、ISNULL、STR_TO_DATE 等函数来间接实现。
显式转换使用 CAST(expr AS type) 或 CONVERT(expr, type)，可使代码清晰、避免隐式陷阱。
注意 字符串转数字时非数字开头会变成 0，这种隐式行为可能导致逻辑错误和性能问题（索引失效）。
处理用户输入或不确定格式的数据时，务必显式转换并配合判空/默认值，提高 SQL 的健壮性。
变量与函数
MySQL 没有严格命名为“变量函数”的内置函数分类，但围绕变量的定义、赋值、读取和系统变量获取，有一套丰富的语法和机制。下面从变量类型、使用方式、常用技巧以及相关“函数式”操作进行详解。
1. MySQL 变量的三种类型
类型
声明方式
作用域
前缀
用户变量
SET @var = value 或 SELECT @var := expr
当前会话（连接）
@
系统变量
预先定义，通过 SET 修改
全局（GLOBAL）或会话（SESSION）
@@
局部变量
存储过程/函数内 DECLARE var TYPE
存储程序内部
无前缀
2. 用户变量（@var）详解
用户变量无需声明类型，MySQL 根据赋值自动推断，且在整个会话期间有效。
2.1 赋值方式
-- 方式一：SET（可同时赋值多个，使用 = 或 :=）SET @name = 'Alice';SET @age := 30, @city := 'Hefei';-- 方式二：SELECT ... INTO（必须返回单行，或使用 LIMIT 1）SELECT phone INTO @phone FROM users WHERE id = 1;-- 方式三：在 SELECT 语句中使用 := 进行赋值（边查询边赋值）SELECT @num := COUNT(*) FROM orders;
2.2 读取
SELECT @name, @age, @phone;-- 或用于表达式SELECT * FROM products WHERE price > @avg_price;
CREATE PROCEDURE demo()BEGIN DECLARE total INT DEFAULT 0; DECLARE done INT DEFAULT FALSE; DECLARE cur CURSOR FOR SELECT amount FROM sales; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO total; IF done THEN LEAVE read_loop; END IF; END LOOP; CLOSE cur; SELECT total;END;
局部变量的作用域在 BEGIN...END 块内，不能带有 @ 前缀，且必须声明类型。
6. 与变量相关的“函数式”语法
严格来说并没有函数叫“变量函数”，但以下几个常与变量配合使用，或能产生类似动态变量的效果：
6.1 SELECT ... INTO 语句
虽然不是函数，但可以把查询结果赋值给变量：
SELECT COUNT(*), AVG(score) INTO @cnt, @avg FROM exams;
UPDATE serial_table SET current = LAST_INSERT_ID(current + 1) WHERE name = 'invoice';SELECT LAST_INSERT_ID(); -- 获得下一个编号
（这里使用了 LAST_INSERT_ID 函数，虽然不是直接变量，但可当作无锁序列号生成器）
缓存中间结果：
SET @total = (SELECT SUM(amount) FROM orders WHERE year = 2026);SELECT product, SUM(amount), SUM(amount)/@total AS percentFROM orders WHERE year = 2026GROUP BY product;
存储过滤条件：
SET @min_price = 100;SELECT * FROM products WHERE price >= @min_price;