Module 3 - 任务列表

第一步：安装 MySQL 和 phpMyAdmin

• 在 EC2 实例上 安装 (opens in a new tab) MySQL 和 phpMyAdmin，跟着老师的步骤走完。

第二步：设置数据库

• 创建一个名为 wustl 的数据库。

CREATE DATABASE wustl;

• 创建一个名为 wustl_inst 的用户，密码为 wustl_pass。并且刷新权限表以确保所有更改生效

CREATE USER 'wustl_inst'@'localhost' IDENTIFIED BY 'wustl_pass';
GRANT ALL PRIVILEGES ON wustl.* TO 'wustl_inst'@'localhost';
FLUSH PRIVILEGES;

第三步：创建表

• 请阅读 MySQL Schema and State (opens in a new tab) 这一节，学习所有sql语句。我会提供所有的sql语句，但是请不要直接复制粘贴，先自己写写看。

students 表

创建一个名为 students 的表，包含以下字段：

• id：适当大小的无符号整数类型（例如 INT UNSIGNED）。
• first_name：类型为 VARCHAR(50)。
• last_name：类型为 VARCHAR(50)。
• email_address：类型为 VARCHAR(50)。

主键应为 id 字段。

CREATE TABLE students (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email_address VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

departments 表

创建一个名为 departments 的表，包含以下字段：

• school_code：类型为 ENUM('L', 'B', 'A', 'F', 'E', 'T', 'I', 'W', 'S', 'U', 'M')。
• dept_id：适当大小的无符号整数类型（例如 INT UNSIGNED）。
• abbreviation：类型为 VARCHAR(9)。
• dept_name：类型为 VARCHAR(200)。

主键应为 school_code 和 dept_id 字段。

CREATE TABLE departments (
    school_code ENUM('L', 'B', 'A', 'F', 'E', 'T', 'I', 'W', 'S', 'U', 'M') NOT NULL,
    dept_id INT UNSIGNED NOT NULL,
    abbreviation VARCHAR(9) NOT NULL,
    dept_name VARCHAR(200) NOT NULL,
    PRIMARY KEY (school_code, dept_id)
) ENGINE=InnoDB;

courses 表

创建一个名为 courses 的表，包含以下字段：

• school_code：类型为 ENUM（与 departments 表中的 school_code ENUM 相匹配）。
• dept_id：与 departments 表中相同的整数类型。
• course_code：类型为 CHAR(5)。
• name：类型为 VARCHAR(150)。

确保适当字段引用 departments 表的外键。选择合适的字段作为主键。

CREATE TABLE courses (
    school_code ENUM('L', 'B', 'A', 'F', 'E', 'T', 'I', 'W', 'S', 'U', 'M') NOT NULL,
    dept_id INT UNSIGNED NOT NULL,
    course_code CHAR(5) NOT NULL,
    name VARCHAR(150) NOT NULL,
    PRIMARY KEY (school_code, dept_id, course_code),
    FOREIGN KEY (school_code, dept_id) REFERENCES departments(school_code, dept_id) ON DELETE CASCADE
) ENGINE=InnoDB;

grades 表

创建一个名为 grades 的表，包含以下字段：

• pk_grade_ID：适当大小的无符号整数类型（例如 BIGINT UNSIGNED），设置为 AUTO_INCREMENT 并作为主键。
• student_id：与 students 表中相同的整数类型。
• grade：类型为 DECIMAL(5,2)。
• school_code：类型为 ENUM。
• dept_id：与其他表中相同的整数类型。
• course_code：与 courses 表中相同的类型。

将外键添加到 students 表和 courses 表。

CREATE TABLE grades (
    pk_grade_ID BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    student_id INT UNSIGNED NOT NULL,
    grade DECIMAL(5,2) NOT NULL,
    school_code ENUM('L', 'B', 'A', 'F', 'E', 'T', 'I', 'W', 'S', 'U', 'M') NOT NULL,
    dept_id INT UNSIGNED NOT NULL,
    course_code CHAR(5) NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (school_code, dept_id, course_code) REFERENCES courses(school_code, dept_id, course_code) ON DELETE CASCADE
) ENGINE=InnoDB;

SHOW CREATE TABLE <TableName>

第四步：导入数据

• 从以下文件下载并将数据加载到表中：

• students.txt

• departments.txt

• courses.txt

• grades.txt

• 使用 phpMyAdmin 导入数据

  • 打开 phpMyAdmin，选择你要导入的表，这里由于老师要求我们使用外键链接，所以需要按照顺序导入，students -> departments -> courses -> grades

  • 选择完表后点击上方的import 按钮

  • 上传我提供的对应的csv文件，Format 选择 CSV，然后点击 Import 按钮

第五步：插入更多数据

• 插入学校的课程 CSE 330S, CSE 131, 和 CSE 260M. 这里你可以思考以下如何找到 department code. 我把sql语句放在下面，如果实在想不出来的话再使用

SELECT school_code, dept_id, abbreviation, dept_name
FROM departments
WHERE abbreviation = 'CSE';

• 插入老师提供的这些数据 (opens in a new tab)，老师说到的这些数据不在一个表内，所以我们需要在对应的表里使用插入语句，这里有外键限制所以需要按顺序插入哦~ 这里同样建议先尝试自己写一下再使用我的sql语句。

  • 插入课程数据

  INSERT INTO courses (school_code, dept_id, course_code, name)
  VALUES 
  ('E', '81', '330S', 'Rapid Prototype Development and Creative Programming'),
  ('E', '81', '131', 'Introduction to Computer Science'),
  ('E', '81', '260M', 'Introduction to Digital Logic and Computer Design');
  • 插入学生数据

  INSERT INTO students (id, first_name, last_name, email_address)
  VALUES 
  (88, 'Ben', 'Harper', 'bharper@ffym.com'),
  (202, 'Matt', 'Freeman', 'mfreeman@kickinbassist.net'),
  (115, 'Marc', 'Roberge', 'mroberge@ofarevolution.us');
  • 插入成绩数据

  INSERT INTO grades (student_id, grade, school_code, dept_id, course_code)
  VALUES 
  (88, 35.5, 'E', '81', '330S'),
  (88, 0, 'E', '81', '131'),
  (88, 95, 'E', '81', '260M'),
  (202, 100, 'E', '81', '330S'),
  (202, 90.5, 'E', '81', '131'),
  (202, 94.8, 'E', '81', '260M'),
  (115, 75, 'E', '81', '330S'),
  (115, 37, 'E', '81', '131'),
  (115, 45.5, 'E', '81', '260M');

第六步：查询数据

• 运行以下查询 (opens in a new tab)，并将结果保存到文本文件中（例如 query1.txt、query2.txt 等）这里总共有五个查询，所以总共要提交五个txt到你的github仓库哦！同样的，请先试试再使用我都sql语句~

  • 选择整个 grades 表

  SELECT * FROM grades;
  • 选择描述 College of Arts & Sciences（学校代码 L）提供的所有课程的字段

  SELECT * FROM courses 
  WHERE school_code = 'L';
  • 列出所有学生的姓名、学生 ID 和 CSE330 成绩（涉及连接）

  SELECT students.first_name, students.last_name, grades.student_id, grades.grade
  FROM students
  JOIN grades ON students.id = grades.student_id
  WHERE grades.course_code = '330S';
  • 列出平均成绩低于 50 的学生的姓名、电子邮件和平均成绩（使用聚合函数和 HAVING）

  SELECT students.first_name, students.last_name, students.email_address, AVG(grades.grade) AS average_grade
  FROM students
  JOIN grades ON students.id = grades.student_id
  GROUP BY students.id
  HAVING AVG(grades.grade) < 50;
  • 生成 Jack Johnson 的个人报告卡，仅显示他的学生 ID、电子邮件地址和平均成绩（使用连接和聚合函数)
  ⚠️

  这里要注意搜索要用名字而不是 students.id

  SELECT students.id, students.email_address, AVG(grades.grade) AS average_grade
  FROM students
  JOIN grades ON students.id = grades.student_id
  WHERE students.first_name = 'Jack' AND students.last_name = 'Johnson'
  GROUP BY students.id;