sql records

时间:2023-03-08 19:50:12
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT NOT NULL AUTO_INCREMENT,
student_name VARCHAR(20) NOT NULL,
PRIMARY KEY(id)
); DROP TABLE IF EXISTS course;
CREATE TABLE course (
id INT NOT NULL AUTO_INCREMENT,
course_name VARCHAR(20) NOT NULL,
description VARCHAR(100) NOT NULL,
PRIMARY KEY(id)
); DROP TABLE IF EXISTS student_course_score;
CREATE TABLE student_course_score(
student_id INT NOT NULL,
course_id INT NOT NULL,
score FLOAT NOT NULL,
PRIMARY KEY(student_id,course_id),
FOREIGN KEY(student_id) REFERENCES student(id),
FOREIGN KEY(course_id) REFERENCES course(id)
);
INSERT INTO student (student_name) VALUES
("Chris"),
("Chen"),
("Yunzheng"); INSERT INTO course (course_name,description) VALUES
("Math","tool for analysis"),
("Art","improve your qulity of life"),
("Computer","make your life effective"); SELECT * FROM
student,course;

一直尝试到这里,才对题目的意图以及数据库表之间的关系有个清晰的理解。。。

1. 题目意图:提供学生名+课程名 => 得到分数

2. 之前用Hibernate根据对象关系生成表仅考虑了学生和课程之间的多对多关系,没考虑分数关系,离题十万八千里