数据库实验一

-- 显示当前可用的数据库
SHOW DATABASES;

-- 选择当前数据库
SELECT DATABASE();

-- 创建一个名为studentsdb的数据库并选择它
CREATE DATABASE studentsdb;
USE studentsdb;

-- 创建名为stuinfo的学生信息表
CREATE TABLE stuinfo
(
    学号     CHAR(8) NOT NULL,    -- 学号
    姓名     CHAR(8) NOT NULL,    -- 姓名
    性别     CHAR(2),            -- 性别
    出生日期 DATE,               -- 出生日期
    籍贯     VARCHAR(10),        -- 籍贯
    PRIMARY KEY (学号)           -- 设置学号为主键
) COMMENT '学生表';

-- 创建名为course的课程表
CREATE TABLE course
(
    课程编号 CHAR(4) NOT NULL,    -- 课程编号
    课程名称 VARCHAR(50),         -- 课程名称
    学分     INT,                -- 学分
    PRIMARY KEY (课程编号)       -- 设置课程编号为主键
) COMMENT '课程表';

-- 创建名为grade的成绩表
CREATE TABLE grade
(
    学号     CHAR(8) NOT NULL,    -- 学号
    课程编号 CHAR(4) NOT NULL,    -- 课程编号
    分数     DECIMAL(5, 2),       -- 分数，修改数据类型为DECIMAL
    PRIMARY KEY (学号, 课程编号)  -- 设置学号和课程编号为联合主键
);

-- 插入学生信息
INSERT INTO stuinfo
VALUES
    (202001, '张青平', '男', '2000-10-01', '福州'),
    (2020002, '刘东阳', '男', '1998-12-09', '杭州'),
    (2020003, '马晓夏', '女', '1995-05-12', '合肥'),
    (2020004, '钱忠理', '男', '1994-09-23', '长沙'),
    (2020005, '孙海洋', '男', '1995-04-03', '北京'),
    (2020006, '郭小斌', '男', '1997-11-10', '上海'),
    (2020007, '肖月玲', '女', '1996-12-07', '南京'),
    (2020008, '张玲珑', '女', '1997-12-24', '苏州');

-- 插入课程信息
INSERT INTO course
VALUES
    ('001', '计算机应用基础', 2),
    ('002', 'C语言程序设计', 2),
    ('003', '数据库原理及应用', 2),
    ('004', '英语', 4),
    ('005', '高等数学', 4);

-- 插入成绩信息
INSERT INTO grade
VALUES
    ('2020001', '003', 88),
    ('2020001', '004', 85),
    ('2020001', '005', 77),
    ('2020002', '001', 73),
    ('2020002', '004', 79),
    ('2020002', '005', 73),
    ('2020003', '001', 84),
    ('2020003', '002', 92);

-- 修改课程表，删除课程名称列
ALTER TABLE course
    DROP COLUMN 课程名称;

-- 修改成绩表，将分数列的数据类型更改为DECIMAL(5, 2)
ALTER TABLE grade
    MODIFY COLUMN 分数 DECIMAL(5, 2);

-- 修改学生信息表，添加备注列
ALTER TABLE stuinfo
    ADD COLUMN 备注 VARCHAR(50);

-- 创建新的数据库studb
CREATE DATABASE studb;
-- 切换到新数据库studb
USE studb;
-- 创建一个新表stud，结构与studentsdb.stuinfo相同
CREATE TABLE studb.stud LIKE studentsdb.stuinfo;
-- 复制数据到新表
-- INSERT INTO studb.stud SELECT * FROM studentsdb.stuinfo;

-- 删除学号为2020004的学生记录
-- DELETE FROM studb.stud WHERE 学号='2020004';

-- 更新学号为2020002的学生的籍贯为蚌埠市
-- UPDATE studb.stud SET 籍贯='蚌埠市' WHERE 学号='2020002';

-- 删除备注列
-- ALTER TABLE studb.stud DROP COLUMN 备注;

-- 删除新的数据库studb
-- DROP DATABASE studb;

-- 创建一个临时表tempGrade，结构与grade表相同
CREATE TEMPORARY TABLE IF NOT EXISTS tempGrade LIKE grade;
-- 更新学生成绩，将低于平均分的女生的成绩增加10分
UPDATE grade g
SET 分数 = 分数 + 10
WHERE g.学号 IN (SELECT 学号 FROM stuinfo WHERE 性别 = '女')
  AND g.分数 < (SELECT AVG(tempGrade.分数) FROM tempGrade);
-- 删除临时表
DROP TEMPORARY TABLE tempGrade;

-- 更新课程编号为'002'的课程成绩，根据条件提高分数
UPDATE grade g
SET 分数 = IF(g.分数 > 75, g.分数 * 1.04, g.分数 * 1.05)
WHERE g.课程编号 = '002';

-- 删除分数小于60的成绩记录
DELETE FROM grade WHERE grade.分数 < 60;