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

 
-- 创建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);

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
);

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;

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 ;

 
-- 开始事务
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;

@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

#!/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