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

​

目录

功能1、DDL设计

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

功能3、触发器

功能4、事务与存储过程

功能5、自动备份

功能6、SQL优化

功能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 sJOIN score sc ON s.id = sc.studentIdWHERE 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 sJOIN

score sc ON s.id = sc.studentIdGROUP BY

s.id, s.userNameORDER BY

`总成绩` DESC

LIMIT 3;

​编辑

功能3、触发器

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

返回结果：

​编辑

DELIMITER //CREATE TRIGGER before_student_insert

BEFORE INSERT ON studentFOR EACH ROWBEGIN

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 studentFOR EACH ROWBEGIN

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 scoreFOR EACH ROWBEGIN

IF NEW.result <> OLD.result THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '不允许修改score表的result列信息';

END IF;END;//

DELIMITER ;

​编辑

测试效果：

​编辑

不允许修改成绩：

​编辑

功能4、事务与存储过程

基础事务

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

-- 开始事务START TRANSACTION;

-- 尝试修改手机号UPDATE studentSET phone = '新手机号' -- 这里替换成实际的新手机号WHERE id = 某个学生ID; -- 这里替换成实际的学生ID

-- 检查上一步操作是否成功

IF @@ERROR <> 0 OR ROW_COUNT() = 0 THEN

-- 如果出现错误或者没有行被更新，则回滚事务

ROLLBACK;

-- 可以选择抛出错误或返回错误消息

-- SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '更新手机号失败';ELSE

-- 如果没有错误且至少有一行被更新，则提交事务

COMMIT;END IF;

存储过程化处理

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

DELIMITER //CREATE PROCEDURE UpdateStudentPhone(IN student_id INT, IN new_phone VARCHAR(20))BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

-- 如果出现异常，回滚事务

ROLLBACK;

-- 可以记录错误日志或抛出错误消息

END;

-- 开始事务

START TRANSACTION;

-- 更新手机号

UPDATE student SET phone = new_phone WHERE id = student_id;

-- 检查是否有行被更新

IF ROW_COUNT() = 0 THEN

-- 如果没有行被更新，可以认为是一个错误，回滚事务并抛出消息

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '未找到指定学生ID';

END IF;

-- 提交事务

COMMIT;END //

DELIMITER ;

功能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_usernameset MYSQL_PASSWORD=your_mysql_passwordset MYSQL_DATABASE=schooldbset MYSQL_HOST=localhost

:: 备份目录和文件名set BACKUP_DIR=C:\path\to\your\backup\directoryset 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表的性能和可管理性。

​