我想创建SQL脚本,将分裂成3个MySQL表的CSV
然而,每当,我运行下面的sql脚本,它抛出一个错误,没有主题或在字段列表中.我已经加倍交叉我的csv,所有的标题和数据都完好无损.这是下面的sql脚本:***
脚本的第一部分创建所有必要的表来实现此任务。
第二部分使用loaddatainfile将csv加载到这些表中
-- Create students table
SET FOREIGN_KEY_CHECKS = 0;
-- Create students table
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
class VARCHAR(10) NOT NULL
);
-- Create subjects table
CREATE TABLE IF NOT EXISTS subjects (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
subject_name VARCHAR(50) NOT NULL,
subject_ca_score INT DEFAULT NULL,
subject_exam_score INT DEFAULT NULL,
subject_score INT DEFAULT NULL,
subject_type VARCHAR(10) DEFAULT NULL,
FOREIGN KEY (student_id) REFERENCES students(id)
);
-- Create results table
CREATE TABLE IF NOT EXISTS results (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
subject_name VARCHAR(50) NOT NULL,
subject_type VARCHAR(10) NOT NULL,
subject_ca_score INT NOT NULL,
subject_exam_score INT NOT NULL,
subject_score INT NOT NULL,
grade VARCHAR(2) DEFAULT NULL,
FOREIGN KEY (student_id) REFERENCES students(id)
);
-- Load data into the subjects table from CSV
LOAD DATA INFILE 'C:/Users/HP USER/Documents/mytest.csv'
INTO TABLE subjects
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS
(student_id, math_score_ca, math_score_exam, econ_score_ca, econ_score_exam, civic_score_ca, civic_score_exam, lit_score_ca, lit_score_exam)
SET
subject_name = 'Math',
subject_ca_score = math_score_ca,
subject_exam_score = math_score_exam,
subject_score = math_score_ca + math_score_exam,
subject_type = 'CA',
student_id = @last_student_id + 1;
-- Insert records for each subject into the subjects table
INSERT INTO subjects (student_id, subject_name, subject_ca_score, subject_exam_score, subject_score, subject_type)
SELECT student_id, 'Econ', econ_score_ca, econ_score_exam, econ_score_ca + econ_score_exam, 'CA'
FROM subjects
WHERE student_id = @last_student_id + 1;
INSERT INTO subjects (student_id, subject_name, subject_ca_score, subject_exam_score, subject_score, subject_type)
SELECT student_id, 'Civic', civic_score_ca, civic_score_exam, civic_score_ca + civic_score_exam, 'CA'
FROM subjects
WHERE student_id = @last_student_id + 1;
INSERT INTO subjects (student_id, subject_name, subject_ca_score, subject_exam_score, subject_score, subject_type)
SELECT student_id, 'Lit', lit_score_ca, lit_score_exam, lit_score_ca + lit_score_exam, 'CA'
FROM subjects
WHERE student_id = @last_student_id + 1;
-- Calculate grades and insert them into the results table
INSERT INTO results (student_id, subject_name, subject_type, subject_ca_score, subject_exam_score, subject_score, grade)
SELECT student_id, subject_name, 'CA', subject_ca_score, subject_exam_score, subject_ca_score + subject_exam_score,
CASE
WHEN subject_ca_score + subject_exam_score IS NULL THEN NULL
WHEN subject_ca_score + subject_exam_score >= 90 THEN 'A'
WHEN subject_ca_score + subject_exam_score >= 80 THEN 'B'
WHEN subject_ca_score + subject_exam_score >= 70 THEN 'C'
WHEN subject_ca_score + subject_exam_score >= 60 THEN 'D'
ELSE 'F'
END
FROM subjects
WHERE student_id = @last_student_id +1;
-- Display the student's name and their results
SELECT s.name, r.subject_name, r.grade
FROM students s
INNER JOIN results r ON s.id = r.student_id
WHERE s.id = @last_student_id;
-- End of script.
SET FOREIGN_KEY_CHECKS = 1;
这就是错误:
SET FOREIGN_KEY_CHECKS = 0
> OK
> Query Time: 0.001s
-- Create students table
SET FOREIGN_KEY_CHECKS = 0
> OK
> Query Time: 0.009s
-- Create students table
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
class VARCHAR(10) NOT NULL
)
> OK
> Query Time: 0.001s
-- Create subjects table
CREATE TABLE IF NOT EXISTS subjects (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
subject_name VARCHAR(50) NOT NULL,
subject_ca_score INT DEFAULT NULL,
subject_exam_score INT DEFAULT NULL,
subject_score INT DEFAULT NULL,
subject_type VARCHAR(10) DEFAULT NULL,
FOREIGN KEY (student_id) REFERENCES students(id)
)
> OK
> Query Time: 0.006s
-- Create results table
CREATE TABLE IF NOT EXISTS results (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
subject_name VARCHAR(50) NOT NULL,
subject_type VARCHAR(10) NOT NULL,
subject_ca_score INT NOT NULL,
subject_exam_score INT NOT NULL,
subject_score INT NOT NULL,
grade VARCHAR(2) DEFAULT NULL,
FOREIGN KEY (student_id) REFERENCES students(id)
)
> OK
> Query Time: 0.002s
-- Load data into the subjects table from CSV
LOAD DATA INFILE 'C:/Users/HP USER/Documents/mytest.csv'
INTO TABLE subjects
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS
(student_id, math_score_ca, math_score_exam, econ_score_ca, econ_score_exam, civic_score_ca, civic_score_exam, lit_score_ca, lit_score_exam)
SET
subject_name = 'Math',
subject_ca_score = math_score_ca,
subject_exam_score = math_score_exam,
subject_score = math_score_ca + math_score_exam,
subject_type = 'CA',
student_id = @last_student_id + 1
> 1054 - Unknown column 'math_score_ca' in 'field list'
> Query Time: 0.009s
这是csv文件,当用文本编辑器notepad++打开时
name,class,math_score_ca,math_score_exam,econ_score_ca,econ_score_exam,civic_score_ca,civic_score_exam,lit_score_ca,lit_score_exam
John Doe,SS3,70,80,75,85,80,90,85,95
Jane Doe,SS3,85,90,80,75,90,85,70,80
2条答案
按热度按时间oprakyz71#
你的计划不合逻辑。
您将创建:
Class
和SubjectType
;Student
,其引用Class
;Subject
,其引用SubjectType
;Result
引用Student
和Subject
。根据主题领域的详细信息,方案可能包含更多表。此外,这些表必须包含适当的唯一约束。
khbbv19g2#
对于您当前使用现有结构的方法(坏主意,请参阅Akina的答案),您需要执行CSV的多次传递,首先插入学生(不知道您从哪里获得
@last_student_id
),然后为每个主题传递。一个更好的方法是规范化你的数据结构(这只是一个例子,需要 “抛光” 才能满足你的要求):
并将CSV解析为临时表:
然后使用它来填充其他表: