实验：mysql一些基本操作2（增删改查）

实验一：创建一个包含用户名、身高、体重、出生日期和多个爱好的用户信息表，并插入10个用户信息，要求id号是自动递增的：

1. 创建用户信息表

使用 InnoDB 存储引擎来创建这个表，确保 ID 自动递增，并且可以存储多个爱好。

CREATE TABLE user_info (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    height DECIMAL(5,2) NOT NULL,
    weight DECIMAL(5,2) NOT NULL,
    birthdate DATE NOT NULL,
    hobbies TEXT
) ENGINE=InnoDB;

2. 插入10个用户信息

使用 INSERT INTO 语句插入10个用户信息。每个用户的信息包括用户名、身高、体重、出生日期和多个爱好。

INSERT INTO user_info (username, height, weight, birthdate, hobbies) VALUES
('Alice', 160.5, 55.0, '1990-01-01', 'Reading, Traveling, Cooking'),
('Bob', 175.0, 70.0, '1985-02-15', 'Sports, Music, Hiking'),
('Charlie', 180.2, 85.3, '1992-03-10', 'Gaming, Fishing, Swimming'),
('David', 165.5, 60.5, '1988-04-22', 'Photography, Reading, Running'),
('Eve', 158.3, 52.8, '1995-05-30', 'Drawing, Dancing, Traveling'),
('Frank', 172.0, 78.0, '1987-06-14', 'Cooking, Gaming, Traveling'),
('Grace', 168.7, 60.0, '1991-07-19', 'Music, Reading, Hiking'),
('Hank', 185.0, 90.5, '1983-08-25', 'Sports, Photography, Cooking'),
('Ivy', 155.2, 50.3, '1994-09-30', 'Reading, Drawing, Dancing'),
('Jack', 170.5, 68.2, '1989-10-10', 'Gaming, Hiking, Traveling');

最终完整的 SQL 代码

CREATE TABLE user_info (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    height DECIMAL(5,2) NOT NULL,
    weight DECIMAL(5,2) NOT NULL,
    birthdate DATE NOT NULL,
    hobbies TEXT
) ENGINE=InnoDB;

INSERT INTO user_info (username, height, weight, birthdate, hobbies) VALUES
('Alice', 160.5, 55.0, '1990-01-01', 'Reading, Traveling, Cooking'),
('Bob', 175.0, 70.0, '1985-02-15', 'Sports, Music, Hiking'),
('Charlie', 180.2, 85.3, '1992-03-10', 'Gaming, Fishing, Swimming'),
('David', 165.5, 60.5, '1988-04-22', 'Photography, Reading, Running'),
('Eve', 158.3, 52.8, '1995-05-30', 'Drawing, Dancing, Traveling'),
('Frank', 172.0, 78.0, '1987-06-14', 'Cooking, Gaming, Traveling'),
('Grace', 168.7, 60.0, '1991-07-19', 'Music, Reading, Hiking'),
('Hank', 185.0, 90.5, '1983-08-25', 'Sports, Photography, Cooking'),
('Ivy', 155.2, 50.3, '1994-09-30', 'Reading, Drawing, Dancing'),
('Jack', 170.5, 68.2, '1989-10-10', 'Gaming, Hiking, Traveling');

验证数据插入

运行以下查询以验证插入的数据：

SELECT * FROM user_info;

插入 Tom 的记录（如果数据库中没有）

INSERT INTO user_info (username, height, weight, birthdate, hobbies) VALUES
('Tom', 175.5, 70.0, '1995-08-20', 'Reading, Traveling');

组合查询

查出用户名为 Tom 的记录

为了查出用户名为 Tom 的记录，可以使用以下 SQL 查询：

SELECT * FROM user_info WHERE username = 'Tom';

查出年龄在 18 与 30 之间的用户信息

由于数据库中没有直接存储用户年龄的信息，而是存储了出生日期，我们需要根据当前日期计算用户的年龄。假设当前日期为 2024-06-14，可以使用以下查询来查找年龄在 18 与 30 之间的用户信息：

SELECT *,
       TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) AS age
FROM user_info
HAVING age BETWEEN 18 AND 30;

实验二、创建一个课程表来存储课程信息，包括课程名、授课老师名、授课老师级别、授课老师年龄和授课老师性别，并插入5条课程信息。

1. 创建课程表

首先，创建一个名为 course_info 的表：

CREATE TABLE course_info (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(255) NOT NULL,
    teacher_name VARCHAR(255) NOT NULL,
    teacher_level VARCHAR(255) NOT NULL,
    teacher_age INT NOT NULL,
    teacher_gender ENUM('Male', 'Female') NOT NULL
) ENGINE=InnoDB;

2. 插入5条课程信息

接下来，插入5条课程信息：

INSERT INTO course_info (course_name, teacher_name, teacher_level, teacher_age, teacher_gender) VALUES
('Mathematics', 'John Doe', 'Senior', 45, 'Male'),
('Physics', 'Jane Smith', 'Intermediate', 38, 'Female'),
('Chemistry', 'Alice Johnson', 'Senior', 50, 'Female'),
('Biology', 'Robert Brown', 'Junior', 30, 'Male'),
('History', 'Emily Davis', 'Intermediate', 40, 'Female');

最终完整的 SQL 代码

以下是创建表和插入数据的完整 SQL 代码：

CREATE TABLE course_info (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(255) NOT NULL,
    teacher_name VARCHAR(255) NOT NULL,
    teacher_level VARCHAR(255) NOT NULL,
    teacher_age INT NOT NULL,
    teacher_gender ENUM('Male', 'Female') NOT NULL
) ENGINE=InnoDB;

INSERT INTO course_info (course_name, teacher_name, teacher_level, teacher_age, teacher_gender) VALUES
('Mathematics', 'John Doe', 'Senior', 45, 'Male'),
('Physics', 'Jane Smith', 'Intermediate', 38, 'Female'),
('Chemistry', 'Alice Johnson', 'Senior', 50, 'Female'),
('Biology', 'Robert Brown', 'Junior', 30, 'Male'),
('History', 'Emily Davis', 'Intermediate', 40, 'Female');

3. 验证数据插入

运行以下查询以验证插入的数据：

SELECT * FROM course_info;

实验二优化

可能存在同一个老师教授多门课程的情况，可以通过将老师的信息存储在单独的表中，然后在课程表中引用老师的ID来进行优化。这种方式避免了冗余数据，提高了数据的一致性和管理的便捷性。

优化后的表结构

先把老的course_info表删除，然后创建两个表：一个用于存储老师信息 (teacher_info)，另一个用于存储课程信息 (course_info)。

1. 创建老师信息表

CREATE TABLE teacher_info (
    teacher_id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_name VARCHAR(255) NOT NULL,
    teacher_level VARCHAR(255) NOT NULL,
    teacher_age INT NOT NULL,
    teacher_gender ENUM('Male', 'Female') NOT NULL
) ENGINE=InnoDB;

2. 创建课程信息表

CREATE TABLE course_info (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(255) NOT NULL,
    teacher_id INT,
    FOREIGN KEY (teacher_id) REFERENCES teacher_info(teacher_id)
) ENGINE=InnoDB;

3. 插入老师信息

首先插入老师的信息：

INSERT INTO teacher_info (teacher_name, teacher_level, teacher_age, teacher_gender) VALUES
('John Doe', 'Senior', 45, 'Male'),
('Jane Smith', 'Intermediate', 38, 'Female'),
('Alice Johnson', 'Senior', 50, 'Female'),
('Robert Brown', 'Junior', 30, 'Male'),
('Emily Davis', 'Intermediate', 40, 'Female');

4. 插入课程信息

接下来，插入课程信息，并引用相应的老师ID：

INSERT INTO course_info (course_name, teacher_id) VALUES
('Mathematics', 1),
('Advanced Mathematics', 1),
('Physics', 2),
('Chemistry', 3),
('Biology', 4),
('History', 5),
('Modern History', 5);

最终完整的 SQL 代码

以下是创建表和插入数据的完整 SQL 代码：

-- 创建老师信息表
CREATE TABLE teacher_info (
    teacher_id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_name VARCHAR(255) NOT NULL,
    teacher_level VARCHAR(255) NOT NULL,
    teacher_age INT NOT NULL,
    teacher_gender ENUM('Male', 'Female') NOT NULL
) ENGINE=InnoDB;

-- 创建课程信息表
CREATE TABLE course_info (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(255) NOT NULL,
    teacher_id INT,
    FOREIGN KEY (teacher_id) REFERENCES teacher_info(teacher_id)
) ENGINE=InnoDB;

-- 插入老师信息
INSERT INTO teacher_info (teacher_name, teacher_level, teacher_age, teacher_gender) VALUES
('John Doe', 'Senior', 45, 'Male'),
('Jane Smith', 'Intermediate', 38, 'Female'),
('Alice Johnson', 'Senior', 50, 'Female'),
('Robert Brown', 'Junior', 30, 'Male'),
('Emily Davis', 'Intermediate', 40, 'Female');

-- 插入课程信息
INSERT INTO course_info (course_name, teacher_id) VALUES
('Mathematics', 1),
('Advanced Mathematics', 1),
('Physics', 2),
('Chemistry', 3),
('Biology', 4),
('History', 5),
('Modern History', 5);

5. 验证数据插入

运行以下查询以验证插入的数据：

验证老师信息

SELECT * FROM teacher_info;

验证课程信息

SELECT c.course_id, c.course_name, t.teacher_name, t.teacher_level, t.teacher_age, t.teacher_gender
FROM course_info c
JOIN teacher_info t ON c.teacher_id = t.teacher_id;

通过这种优化方式，可以减少数据冗余，并更有效地管理老师和课程之间的关系。如果需要更新某个老师的信息，只需在 teacher_info 表中更新一次，而无需在每个相关课程记录中重复更新。

实验一：创建一个包含用户名、身高、体重、出生日期和多个爱好的用户信息表，并插入10个用户信息，要求id号是自动递增的：

1. 创建用户信息表

2. 插入10个用户信息

最终完整的 SQL 代码

验证数据插入

插入 Tom 的记录（如果数据库中没有）

组合查询

查出用户名为 Tom 的记录

查出年龄在 18 与 30 之间的用户信息

实验二、创建一个课程表来存储课程信息，包括课程名、授课老师名、授课老师级别、授课老师年龄和授课老师性别，并插入5条课程信息。

1. 创建课程表

2. 插入5条课程信息

最终完整的 SQL 代码

3. 验证数据插入

实验二优化

优化后的表结构

1. 创建老师信息表

2. 创建课程信息表

3. 插入老师信息

4. 插入课程信息

最终完整的 SQL 代码

5. 验证数据插入

验证老师信息

验证课程信息

发送评论 编辑评论

发送评论编辑评论