AI代码提示工具可用于教学功能示例——MySQL

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: AI代码提示工具可用于教学功能示例——MySQL

功能1、DDL设计

当我们需要比较复杂的表的时候,且我们有明确的列信息,就可以使用AI工具直接生成我们的DDL语句,如果需要插入一些DML语句也可以直接让其生成,自行执行插入即可。

示例

帮我创建2个表student与score表,要求student表有id,createDate,userName,phone,age,sex,introduce,

要求score表有id,scoreName,result,studentId(student表的id外键)。

要求student表中插入5条学生信息,都要是中文的。

要求score表中对应studentId插入10条信息,每个学生ID要有2个科目的成绩。

给出DDL与DML语句。

返回结果:

 
-- 创建student表
CREATE TABLE student (
  id INT PRIMARY KEY AUTO_INCREMENT,
  createDate DATE,
  userName VARCHAR(100),
  phone VARCHAR(20),
  age INT,
  sex CHAR(1),
  introduce TEXT
);
 
 
-- 创建score表
CREATE TABLE score (
  id INT PRIMARY KEY AUTO_INCREMENT,
  scoreName VARCHAR(100),
  result FLOAT,
  studentId INT,
  FOREIGN KEY (studentId) REFERENCES student(id)
);
 
 
-- 插入5条学生信息到student表
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES
  ('2023-10-23', '张三', '13800138000', 20, '男', '自我介绍1'),
  ('2023-10-24', '李四', '13800138001', 21, '女', '自我介绍2'),
  ('2023-10-25', '王五', '13800138002', 22, '男', '自我介绍3'),
  ('2023-10-26', '赵六', '13800138003', 23, '女', '自我介绍4'),
  ('2023-10-27', '孙七', '13800138004', 24, '男', '自我介绍5');
 
 
-- 插入10条成绩信息到score表(每个学生ID对应2个科目)
INSERT INTO score (scoreName, result, studentId)
VALUES
  ('数学', 90.5, 1), ('英语', 85.5, 1),
  ('数学', 92.5, 2), ('英语', 88.0, 2),
  ('数学', 88.0, 3), ('英语', 91.5, 3),
  ('数学', 89.5, 4), ('英语', 86.0, 4),
  ('数学', 93.0, 5), ('英语', 89.5, 5);

运行结果:

功能2、基本与复杂SQL查询

基本SQL:帮我根据DDL写一个SQL查找出成绩最高的学生的成绩、姓名、性别与简介信息使用中文写上列名的昵称。

返回结果:

SELECT s.userName AS '姓名', s.sex AS '性别', s.introduce AS '简介', sc.result AS '成绩'
FROM student s
JOIN score sc ON s.id = sc.studentId
WHERE sc.result = (
    SELECT MAX(result) FROM score
);

复杂SQL:帮我通过DDL语句写一个两表联查,要求根据学生的id进行分组,计算出成绩表学生的总成绩与平均成绩,并且根据总成绩进行倒序排列,只显示前三名的成绩,列名使用中文匿名,显示的信息不需要包含成绩表的id。

SELECT 
    s.id AS '学生ID',
    s.userName AS '姓名',
    SUM(sc.result) AS '总成绩',
    AVG(sc.result) AS '平均成绩'
FROM 
    student s
JOIN 
    score sc ON s.id = sc.studentId
GROUP BY 
    s.id, s.userName
ORDER BY 
    `总成绩` DESC
LIMIT 3;

功能3、触发器

根据DDL分别写出两个表的触发器,student表插入与修改操作的时候不能出现姓王的用户,性别只允许使用M或F代表性别,score不允result列信息。

返回结果:

DELIMITER //
CREATE TRIGGER before_student_insert
BEFORE INSERT ON student
FOR EACH ROW
BEGIN
    IF LEFT(NEW.userName, 1) = '王' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '不能插入姓王的用户';
    END IF;
    IF NOT (NEW.sex = 'M' OR NEW.sex = 'F') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '性别只允许使用M或F';
    END IF;
END;
//
DELIMITER ;
 
DELIMITER //
CREATE TRIGGER before_student_update
BEFORE UPDATE ON student
FOR EACH ROW
BEGIN
    IF LEFT(NEW.userName, 1) = '王' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '不能修改用户姓为王';
    END IF;
    IF NOT (NEW.sex = 'M' OR NEW.sex = 'F') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '性别只允许修改为M或F';
    END IF;
END;
//
DELIMITER ;
 
DELIMITER //
CREATE TRIGGER before_score_update
BEFORE UPDATE ON score
FOR EACH ROW
BEGIN
    IF NEW.result <> OLD.result THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '不允许修改score表的result列信息';
    END IF;
END;
//
DELIMITER ;

测试效果:

不允许修改成绩:

功能4、事务与存储过程

基础事务

根据DDL创建一个事务来保障修改用户手机号的时候如果出现问题回滚,否则正常提交数据。

 
-- 开始事务
START TRANSACTION;
 
 
-- 尝试修改手机号
UPDATE student
SET phone = '新手机号' -- 这里替换成实际的新手机号
WHERE id = 某个学生ID; -- 这里替换成实际的学生ID
 
 
-- 检查上一步操作是否成功
IF @@ERROR <> 0 OR ROW_COUNT() = 0 THEN
    -- 如果出现错误或者没有行被更新,则回滚事务
    ROLLBACK;
    -- 可以选择抛出错误或返回错误消息
    -- SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '更新手机号失败';
ELSE
    -- 如果没有错误且至少有一行被更新,则提交事务
    COMMIT;
END IF;

存储过程化处理

这里除了给出基础的事物外,还给出了存储过程,很完整的提示操作。

功能5、自动备份

请给我写一个每小时自动备份MySQL数据库的windows系统的bat脚本,数据库名称教做schooldb。

bat脚本

在Windows系统上,你可以使用批处理(.bat)脚本来执行MySQL数据库的备份。以下是一个示例脚本,该脚本使用mysqldump工具每小时自动备份名为schooldb的数据库。

首先,确保你已经安装了MySQL,并且mysqldump命令可在命令行中访问。

接下来,创建一个新的文本文件,将其保存为backup_schooldb.bat,并使用以下内容填充:

@echo off
setlocal
 
:: MySQL登录配置
set MYSQL_USER=your_mysql_username
set MYSQL_PASSWORD=your_mysql_password
set MYSQL_DATABASE=schooldb
set MYSQL_HOST=localhost
 
:: 备份目录和文件名
set BACKUP_DIR=C:\path\to\your\backup\directory
set DATE_TIME=%DATE:~-4,4%%DATE:~-10,2%%DATE:~-7,2%_%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%
set BACKUP_FILE=%BACKUP_DIR%\schooldb_%DATE_TIME%.sql
 
:: 使用mysqldump命令备份数据库
mysqldump -h %MYSQL_HOST% -u %MYSQL_USER% -p%MYSQL_PASSWORD% %MYSQL_DATABASE% > %BACKUP_FILE%
 
:: 检查备份是否成功
if %errorlevel% equ 0 (
    echo Database backup successful!
) else (
    echo Error: Database backup failed!
)
 
endlocal

注意:

替换your_mysql_username、your_mysql_password和C:\path\to\your\backup\directory为你的实际MySQL用户名、密码和备份目录路径。

%DATE_TIME%变量用于生成一个基于当前日期和时间的字符串,以便在备份文件名中使用。格式可能因系统区域设置而异,你可能需要调整DATE_TIME的组成部分以匹配你的日期和时间格式。

脚本中没有包含自动定时任务的功能。要在Windows上每小时自动执行此脚本,你需要使用“任务计划程序”来设置定时任务。

要使用“任务计划程序”设置定时任务:

打开“任务计划程序”(可以在开始菜单中搜索)。

选择“创建基本任务”,为任务命名并提供描述。

选择“每小时”作为触发器,并设置开始时间和重复间隔。

选择“启动程序”作为操作,并浏览选择你的backup_schooldb.bat脚本。

完成任务的创建过程。

现在,你的MySQL数据库应该会自动每小时备份一次了!确保定期检查备份文件以确保它们正确生成且没有损坏。


请给我写一个每小时自动备份MySQL数据库的脚本,数据库名称教做schooldb。

shell脚本

#!/bin/bash
 
# MySQL数据库登录配置
USER="your_mysql_username"
PASSWORD="your_mysql_password"
DATABASE="schooldb"
HOST="localhost"
 
# 备份目录和文件名
BACKUP_DIR="/path/to/your/backup/directory"
DATE=$(date +"%Y%m%d%H%M")
BACKUP_FILE="$BACKUP_DIR/schooldb_$DATE.sql"
 
# 使用mysqldump命令备份数据库
mysqldump -h $HOST -u $USER -p$PASSWORD $DATABASE > $BACKUP_FILE
 
# 检查备份是否成功
if [ $? == 0 ]; then
    echo "Database backup successful!"
else
    echo "Error: Database backup failed!"
fi

还可以做sql优化。

功能6、SQL优化

score会有几百万条数据,应该如何优化表会更稳妥?

返回结果:

当score表包含几百万条数据时,优化工作变得尤为重要,以确保查询性能、数据完整性和高效的存储管理。以下是一些建议来优化score表:

索引优化:

确保经常用于查询的列(如studentId、scoreName)有适当的索引。

考虑使用复合索引来优化多列的查询条件。

定期分析查询性能,并根据需要调整索引。

分区:

如果score表中的数据可以根据某些逻辑(如日期、学生ID范围等)进行分区,那么可以使用分区表来提高查询性能和管理效率。

分区可以将一个大表分割成较小的、更易于管理的片段,同时保持逻辑上作为一个表来处理。

数据类型优化:

检查每一列的数据类型,确保它们是最合适的类型。例如,如果result列的值总是整数,可以将其从FLOAT改为INT。

对于文本字段(如scoreName),如果长度固定或可以预估最大长度,则使用CHAR类型而不是VARCHAR,反之则使用VARCHAR以节省空间。

归档旧数据:

如果score表中包含大量旧数据,并且这些数据不经常访问,可以考虑将其归档到另一个存储解决方案中(如数据仓库)。

这可以保持主表的大小可管理,并提高当前数据的查询性能。

硬件和存储优化:

确保数据库服务器具备足够的RAM来缓存常用数据和索引,以减少磁盘I/O操作。

使用高性能的存储解决方案,如SSD,来提高I/O性能。

查询优化:

定期审查和优化SQL查询,避免不必要的全表扫描和复杂的联接操作。

使用数据库的查询执行计划工具来分析和优化查询性能。

数据库维护:

定期对数据库进行维护,如更新统计信息、重建索引等,以确保数据库性能处于最佳状态。

监控数据库性能,并根据需要进行调整和优化。

应用层优化:

在应用层实施缓存策略,如使用Redis等内存数据库缓存常用查询结果,减少对数据库的直接访问。

设计合理的数据访问模式,避免N+1查询问题等常见的性能陷阱。

优化score表需要从多个方面入手,包括索引、分区、数据类型、硬件和存储、查询优化以及数据库维护等。根据实际情况选择和实施这些策略,可以显著提高score表的性能和可管理性。

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
3天前
|
人工智能 自然语言处理 前端开发
深度解析Playwright MCP:功能、优势与挑战,AI如何提升测试效率与覆盖率
Playwright MCP通过AI与浏览器交互,实现自然语言驱动的自动化测试。它降低门槛、提升效率,助力测试工程师聚焦高价值工作,是探索性测试与快速验证的新利器。
|
6天前
|
人工智能 自然语言处理 算法
现代AI工具深度解析:从GPT到多模态的技术革命与实战应用
蒋星熠Jaxonic,AI技术探索者,深耕代码生成、多模态AI与提示词工程。分享AI工具架构、实战应用与优化策略,助力开发者提升效率,共赴智能编程新纪元。
35 4
|
8天前
|
人工智能 监控 算法
AI解决方案的决策工具
企业正借助AI实现精细化“微观决策”,需在自动化与人工干预间找到平衡。本文提出HITL、HITLFE、HOTL、HOOTL四种管理模型,指导如何设计人机协同机制,确保决策高效、可控,并随业务动态演进。
|
9天前
|
SQL 人工智能 搜索推荐
Dataphin功能Tips系列(71)X-数据管家:数据资产运营的「AI外挂」
在企业数据治理中,数据资产规模庞大、字段繁多,手动录入效率低且易出错。Dataphin推出「X-数据管家」,利用大模型智能生成标签、描述及字段类型等信息,支持一键批量上架,大幅提升资产运营效率。
人工智能 移动开发 JavaScript
40 0
|
人工智能 弹性计算 安全
创新场景丨元空智能:AI 工具创业,如何抓住新时代的出海机遇
大模型创业的本质是兑现新技术价值,而乘云出海,不仅是技术的输出,更是中国创新走向世界的一次实践。
|
25天前
|
人工智能 测试技术 项目管理
测试不再碎片化:AI智能体平台「项目资料套件」功能上线!
在实际项目中,需求文档分散、整理费时、测试遗漏等问题常困扰测试工作。霍格沃兹推出AI智能体测试平台全新功能——项目资料套件,可将多个关联文档打包管理,并一键生成测试用例,提升测试完整性与效率。支持套件创建、文档关联、编辑删除及用例生成,适用于复杂项目、版本迭代等场景,助力实现智能化测试协作,让测试更高效、更专业。
|
25天前
|
存储 人工智能 测试技术
用AI提升测试效率:智能体平台的「需求文档管理」功能上线啦!
霍格沃兹测试开发学社推出AI智能体测试平台,全新「需求文档管理」功能助力高效测试准备。集中管理需求文档,支持多种上传方式,智能生成测试用例,提升测试效率与准确性,助力迈向智能化测试新时代。
|
30天前
|
传感器 人工智能 安全
AI Agent架构全览:从LLM大脑到工具四肢的自主进化之路
人工智能正从工具时代迈向智能体时代,AI Agent作为核心载体,具备感知、决策与行动能力,能自主完成复杂任务。本文详解其工作原理与架构,探讨未来发展与挑战。

推荐镜像

更多