数据操作

-- 创建一个包含不同字符串类型的表
CREATE TABLE 用户信息表 (
    用户编号 INT PRIMARY KEY,
    用户名 CHAR(10),        -- 固定长度用户名
    真实姓名 VARCHAR(50),    -- 可变长度姓名
    个人简介 TEXT            -- 大文本个人简介
);

-- 向用户信息表插入数据
INSERT INTO 用户信息表 (用户编号, 用户名, 真实姓名, 个人简介) 
VALUES (1, '小明', '张小明', '热爱编程的大学生，主修计算机科学专业');

-- 这个操作会失败，因为真实姓名超过了50个字符的限制
INSERT INTO 用户信息表 (真实姓名) 
VALUES ('这是一个非常非常非常非常非常非常非常长的姓名，肯定会超过五十个字符的限制');

-- 正确处理包含单引号的字符串
INSERT INTO 用户信息表 (个人简介) 
VALUES ('我很喜欢''Hello World''这个经典例子');
 
-- 或者使用反斜杠转义（MySQL支持）
INSERT INTO 用户信息表 (个人简介) 
VALUES ('我很喜欢\'Hello World\'这个经典例子');

-- 使用CHAR函数生成特殊字符（ASCII码）
SELECT CHAR(65, 66, 67) AS 字母;  -- 结果：'ABC'
 
-- 结合CONCAT函数使用特殊字符
SELECT CONCAT('你好', CHAR(65), '世界') AS 问候语;  -- 结果：'你好A世界'

-- 创建测试数据
INSERT INTO 用户信息表 VALUES 
(2, '小红', '李小红', '这是一段测试文本');
 
-- 获取各列的字符串长度
SELECT 
    真实姓名,
    LENGTH(真实姓名) AS 姓名长度,
    LENGTH(个人简介) AS 简介长度
FROM 用户信息表 
WHERE 用户编号 = 2;

-- 查找"测试"在个人简介中的位置
SELECT 
    个人简介,
    POSITION('测试' IN 个人简介) AS 测试位置
FROM 用户信息表 
WHERE 用户编号 = 2;

-- 生成完整的用户介绍
SELECT 
    CONCAT('用户姓名：', 真实姓名, '，个人简介：', 个人简介) AS 完整介绍
FROM 用户信息表;

-- 提取姓名的姓氏（假设姓氏是第一个字符）
SELECT 
    真实姓名,
    -- 提取真实姓名的姓氏（第1个字符）
    SUBSTRING(真实姓名, 1, 1) AS 姓氏,
    -- 提取真实姓名的名字（从第2个字符开始）
    SUBSTRING(真实姓名, 2) AS 名字
FROM 用户信息表;

-- 将个人简介中的"测试"替换为"示例"
SELECT 
    个人简介,
    REPLACE(个人简介, '测试', '示例') AS 修改后简介
FROM 用户信息表;

-- 查找姓名中包含"小"字的用户
SELECT * FROM 用户信息表 
WHERE 真实姓名 LIKE '%小%';
 
-- 查找以"张"开头的用户
SELECT * FROM 用户信息表 
WHERE 真实姓名 LIKE '张%';

-- 创建一个产品信息表来演示字符串处理
CREATE TABLE 产品信息表 (
    产品ID INT PRIMARY KEY,
    产品名称 VARCHAR(100),
    产品描述 TEXT,
    价格 DECIMAL(10,2)
);
 
-- 插入测试数据
INSERT INTO 产品信息表 VALUES 
(1, 'iPhone 15 Pro', '最新款苹果手机，配备A17芯片', 8999.00

+---------------------------+-----------------------------------+--------------+
| 产品标题                  | 简短描述                          | 名称长度     |
+---------------------------+-----------------------------------+--------------+
| iPhone 15 Pro - ¥8999.00  | 最新款苹果手机，配备...           |           13 |
| AirPods Pro - ¥1999.00    | 主动降噪无线耳机...               |           11 |
+---------------------------+-----------------------------------+--------------+

-- 基本的算术运算示例
SELECT 
    (25 * 8) AS 乘法结果,
    (100 - 25) AS 减法结果,
    (200 / 8) AS 除法结果,
    (17 + 8) AS 加法结果;

+--------------+--------------+--------------+--------------+
| 乘法结果     | 减法结果     | 除法结果     | 加法结果     |
+--------------+--------------+--------------+--------------+
|          200 |           75 |      25.0000 |           25 |
+--------------+--------------+--------------+--------------+

-- 计算产品折扣价格
SELECT 
    产品名称,
    价格 AS 原价,
    ROUND(价格 * 0.8, 2) AS 八折价格,
    ROUND(价格 * 0.9, 2) AS 九折价格
FROM 产品信息表;

-- 判断产品ID是否为偶数
SELECT 
    产品ID,
    产品名称,
    MOD(产品ID, 2) AS 余数,
    CASE WHEN MOD(产品ID, 2) = 0 THEN '偶数ID' ELSE '奇数ID' END AS ID类型
FROM 产品信息表;

-- 计算投资复利（假设年利率5%，投资3年）
SELECT 
    10000 AS 本金,
    POW(1.05, 3) AS 利率因子,
    ROUND(10000 * POW(1.05, 3), 2) AS 三年后金额;

-- 不同精度的四舍五入
SELECT 
    价格,
    ROUND(价格) AS 整数价格,
    ROUND(价格, 1) AS 一位小数,
    ROUND(价格 * 1.15, 2) AS 含税价格
FROM 产品信息表;

-- 计算需要的包装箱数量（向上取整）
SELECT 
    产品名称,
    价格,
    CEIL(价格 / 1000) AS 需要千元大钞数量,
    FLOOR(价格 / 1000) AS 完整千元数
FROM 产品信息表;

-- 比较截断和四舍五入的差别
SELECT 
    价格,
    TRUNCATE(价格, 1) AS 截断结果,
    ROUND(价格, 1) AS 四舍五入结果
FROM 产品信息表;

-- 创建一个账户余额表来演示
CREATE TABLE 账户余额表 (
    账户ID INT PRIMARY KEY,
    账户名称 VARCHAR(50),
    余额 DECIMAL(10,2)
);
 
INSERT INTO 账户余额表 VALUES 
(1, '张三的储蓄账户', 5000.00),
(2, '李四的信用卡', -

 
+-----------------------+----------+--------------+--------+--------------+
| 账户名称              | 余额     | 绝对金额     | 符号   | 账户状态     |
+-----------------------+----------+--------------+--------+--------------+
| 张三的储蓄账户        |  5000.00 |      5000.00 |      1 | 正余额       |
| 李四的信用卡          | -1200.50 |      1200.50 |     -1 | 负余额       |
| 王五的投资账户        |     0.00

-- 查看当前数据库的时区设置
SELECT @@global.time_zone AS 全局时区, @@session.time_zone AS 会话时区;
 
-- 获取当前时间（不同格式）
SELECT 
    CURRENT_DATE() AS 当前日期,
    CURRENT_TIME() AS 当前时间,
    CURRENT_TIMESTAMP() AS 当前时间戳;

+--------------+--------------+---------------------+
| 当前日期     | 当前时间     | 当前时间戳          |
+--------------+--------------+---------------------+
| 2025-09-25   | 11:05:03     | 2025-09-25 11:05:03 |
+--------------+--------------+---------------------+
 

-- 创建员工考勤表
CREATE TABLE 员工考勤表 (
    考勤ID INT PRIMARY KEY AUTO_INCREMENT,
    员工姓名 VARCHAR(50),
    上班时间 DATETIME,
    下班时间 DATETIME,
    考勤日期 DATE
);
 
-- 使用字符串插入时间数据
INSERT INTO 员工考勤表 (员工姓名, 上班时间, 下班时间, 考勤日期) VALUES 
('张小明', '2024-03-15 09:00:00', '2024-03-15 18:00:00', '2024-03-15'),

-- 转换不同格式的时间字符串
SELECT 
    CAST('2024-03-15' AS DATE) AS 标准日期,
    CAST('14:30:00' AS TIME) AS 标准时间,
    CAST('2024-03-15 14:30:00' AS DATETIME) AS 标准时间戳;

-- 计算员工工作时间和未来日期
SELECT 
    员工姓名,
    上班时间,
    下班时间,
    TIMEDIFF(下班时间, 上班时间) AS 工作时长,
    DATE_ADD(考勤日期, INTERVAL 7 DAY) AS 一周后日期,
    DATE_ADD(上班时间, INTERVAL 30 MINUTE) AS 迟到截止时间
FROM 员工考勤表;

-- 计算项目进度
CREATE TABLE 项目进度表 (
    项目ID INT PRIMARY KEY,
    项目名称 VARCHAR(100),
    开始日期 DATE,
    计划结束日期 DATE,
    实际结束日期 DATE
);
 
INSERT INTO 项目进度表 VALUES 
(1, '电商网站开发', '2024-01-01', '2024-03-31', NULL),
(

+--------------------+--------------+--------------------+--------------------+-----------------+-------------------------+
| 项目名称           | 开始日期     | 计划结束日期       | 计划工期天数       | 已进行天数      | 项目状态                |
+--------------------+--------------+--------------------+--------------------+-----------------+-------------------------+
| 电商网站开发       | 2024-01-01   | 2024-03-31         |                 90 |             633 | 已超期543天             |
| 移动APP开发        | 2024-02-01   | 2024-04-30         |                 89 |

-- 分析员工考勤模式
SELECT 
    员工姓名,
    考勤日期,
    EXTRACT(YEAR FROM 考勤日期) AS 年份,
    EXTRACT(MONTH FROM 考勤日期) AS 月份,
    EXTRACT(DAY FROM 考勤日期) AS 日期,
    DAYNAME(考勤日期) AS 星期几,
    EXTRACT(HOUR FROM 上班时间) AS 上班小时
FROM 员工考勤表;

-- 计算月末报表日期
SELECT 
    CURRENT_DATE() AS 今天,
    LAST_DAY(CURRENT_DATE()) AS 本月最后一天,
    LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)) AS 上月最后一天;

-- 创建订单表来演示时间数据的实际应用
CREATE TABLE 订单表 (
    订单ID INT PRIMARY KEY AUTO_INCREMENT,
    客户姓名 VARCHAR(50),
    下单时间 DATETIME,
    发货时间 DATETIME,
    期望收货时间 DATETIME,
    实际收货时间 DATETIME,
    订单金额 DECIMAL(10,2)
);
 
-- 插入测试数据
INSERT INTO 订单表 (客户姓名, 下单时间, 发货时间, 期望收货时间, 实际收货时间, 订单金额) VALUES

+----------+--------------+--------------+---------------------+----------------------------+-----------------+
| 订单ID   | 客户姓名     | 订单金额     | 下单时间            | 发货状态                   | 收货状态        |
+----------+--------------+--------------+---------------------+----------------------------+-----------------+
|        1 | 王小明       |       299.00 | 2024-03-10 10:30:00 | 已发货（5小时后）          | 按时收货        |
|        2 | 李小华       |      1588.00 | 2024-03-11 14:20:00

-- CAST函数的基本语法演示
SELECT 
    CAST('12345' AS SIGNED INTEGER) AS 字符串转整数,
    CAST(123.456 AS DECIMAL(5,2)) AS 浮点数转定点数,
    CAST('2024-03-15' AS DATE) AS 字符串转日期,
    CAST(CURRENT_TIMESTAMP() AS DATE) AS

+--------------------+-----------------------+--------------------+--------------------+
| 字符串转整数       | 浮点数转定点数        | 字符串转日期       | 时间戳转日期       |
+--------------------+-----------------------+--------------------+--------------------+
|              12345 |                123.46 | 2024-03-15         | 2025-09-25         |
+--------------------+-----------------------+--------------------+--------------------+

-- 创建一个包含文本数字的表
CREATE TABLE 销售数据表 (
    记录ID INT PRIMARY KEY AUTO_INCREMENT,
    产品名称 VARCHAR(50),
    销售数量_文本 VARCHAR(10),  -- 存储为文本的数量
    单价_文本 VARCHAR(10)       -- 存储为文本的价格
);
 
INSERT INTO 销售数据表 (产品名称, 销售数量_文本, 单价_文本) VALUES 
('笔记本电脑', '15', '5999.99'),
(

+-----------------+--------------+---------+--------------+
| 产品名称        | 销售数量     | 单价    | 总销售额     |
+-----------------+--------------+---------+--------------+
| 笔记本电脑      |           15 | 5999.99 |     89999.85 |
| 鼠标            |          120 |   89.50 |     10740.00 |
| 键盘            |           45 |  299.00 |     13455.00 |
+-----------------+--------------+---------+--------------+

-- 演示部分转换和转换失败的情况
SELECT 
    '123abc' AS 原始字符串,
    CAST('123abc' AS UNSIGNED INTEGER) AS 转换结果,  -- 结果：123
    '999999999999999999999' AS 超大数字,
    CAST('999999999999999999999' AS UNSIGNED INTEGER) AS 转换结果2;  -- 可能溢出

-- 创建原始导入数据表
CREATE TABLE 客户导入数据 (
    客户ID VARCHAR(20),
    客户姓名 VARCHAR(50),
    注册日期 VARCHAR(20),
    消费金额 VARCHAR(15),
    是否VIP VARCHAR(5)
);
 
-- 插入混合格式的数据
INSERT INTO 客户导入数据 VALUES 
('C001', 

+--------------+--------------+--------------------+--------------------+-----------+
| 客户编号     | 客户姓名     | 标准注册日期       | 标准消费金额       | VIP标志   |
+--------------+--------------+--------------------+--------------------+-----------+
|            1 | 张三         | 2024-03-15         |            1299.50 |         1 |
|            2 | 李四         | 2024-03-16         |            2500.00 |         0 |
|            3 |

-- 创建月度业绩表
CREATE TABLE 月度业绩表 (
    年份 INT,
    月份 INT,
    销售额 DECIMAL(12,2),
    目标额 DECIMAL(12,2)
);
 
INSERT INTO 月度业绩表 VALUES 
(2024, 1, 125000.00, 120000.

+--------------+-----------------+-----------------+-----------+---------------+
| 报告期间     | 实际销售额      | 目标销售额      | 完成率    | 达标状态      |
+--------------+-----------------+-----------------+-----------+---------------+
| 2024年1月    | ¥ 125,000.00    | ¥ 120,000.00    | 4.2%      | ✅ 达标        |
| 2024年2月    | ¥ 98,000.00     | ¥ 110,000.00    | -10.9%    | ❌ 未达标

-- 处理多种格式的电话号码
CREATE TABLE 联系人表 (
    姓名 VARCHAR(50),
    电话号码 VARCHAR(20)
);
 
INSERT INTO 联系人表 VALUES 
('张小明', '138-1234-5678'),
('李小红', '13812345678'),
('王小华', '+86 138 1234 5678'),
('赵小强', '(138) 1234-5678');

+-----------+-------------------+---------------+
| 姓名      | 原始格式          | 标准格式      |
+-----------+-------------------+---------------+
| 张小明    | 138-1234-5678     | 13812345678   |
| 李小红    | 13812345678       | 13812345678   |
| 王小华    | +86 138 1234 5678 | 8613812345678 |
| 赵小强    | (138) 1234-5678

-- 避免在WHERE子句中进行类型转换
-- 不好的做法：
-- SELECT * FROM 销售数据表 WHERE CAST(销售数量_文本 AS INTEGER) > 50;
 
-- 更好的做法：先转换数据类型，或者在应用层面处理
SELECT * FROM 销售数据表 WHERE 销售数量_文本 > '50';

SELECT 
    用户ID,
    用户名,
    手机号,
    REGEXP_REPLACE(手机号, '[^0-9]', '') AS 标准化手机号
FROM 用户表;