我想创建sql脚本,将一个csv分成3个mysql表
然而,无论何时,我运行下面的sql脚本,它抛出一个错误,没有主题或在字段列表中。我已经交叉了我的csv,所有的标题和数据都是完整的。下面是sql脚本:***
脚本的第一部分创建实现此任务所需的所有表。
第二部分使用load data infile 将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 'rn'
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 'rn'
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
你的方案不合逻辑。
你创建:
- 单独的
Class
和SubjectType
表; - 表
Student
,指Class
; - 表
Subject
指SubjectType
; - 表
Result
指Student
和Subject
。
根据主题区域的细节,方案可能包含更多的表。此外,这些表必须包含合适的唯一约束。
对于您当前使用现有结构的方法(坏主意,请参阅Akina的回答),您需要对CSV进行多次传递,首先插入学生(不知道您从哪里获得@last_student_id
),然后通过每个主题。
-- ADD UNIQUE KEY TO students (name, class)
ALTER TABLE `students` ADD UNIQUE INDEX (`name`, `class`);
-- LOAD STUDENTS
LOAD DATA INFILE 'C:/Users/HP USER/Documents/mytest.csv' IGNORE
INTO TABLE students
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'rn'
IGNORE 1 ROWS
(@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)
SET id = NULL, name = @name, class = @class;
-- REPEAT THIS STEP FOR EACH SUBJECT
LOAD DATA INFILE 'C:/Users/HP USER/Documents/mytest.csv'
INTO TABLE subjects
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'rn'
IGNORE 1 ROWS
(@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)
SET
id = NULL,
student_id = (SELECT id FROM students WHERE name = @name AND class = @class),
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';
-- THEN YOUR INSERT INTO results
一个更好的方法是规范化你的数据结构(这只是一个例子,需要"抛光">以满足你的需求):
CREATE TABLE IF NOT EXISTS classes (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(10) NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS students (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
class_id INT UNSIGNED NOT NULL,
UNIQUE (name, class_id),
FOREIGN KEY (class_id) REFERENCES classes (id)
);
CREATE TABLE IF NOT EXISTS subjects (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
type ENUM('CA' /* ADD OTHER SUBJECT TYPES HERE OR MOVE TO THEIR OWN TABLE */) DEFAULT NULL
);
INSERT INTO subjects VALUES (1, 'Math', 'CA'), (2, 'Econ', 'CA'), (3, 'Civic', 'CA'), (4, 'Lit', 'CA');
CREATE TABLE IF NOT EXISTS results (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
student_id INT UNSIGNED NOT NULL,
subject_id INT UNSIGNED NOT NULL,
ca_score INT NOT NULL,
exam_score INT NOT NULL,
score INT AS (ca_score + exam_score),
grade VARCHAR(2) AS (CASE WHEN score IS NULL THEN NULL WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' WHEN score >= 70 THEN 'C' WHEN score >= 60 THEN 'D' ELSE 'F' END),
FOREIGN KEY (student_id) REFERENCES students (id),
FOREIGN KEY (subject_id) REFERENCES subjects (id)
);
并将CSV解析为临时表:
CREATE TEMPORARY TABLE import_csv (
name VARCHAR(255) NOT NULL,
class VARCHAR(10) NOT NULL,
math_score_ca TINYINT UNSIGNED NOT NULL,
math_score_exam TINYINT UNSIGNED NOT NULL,
econ_score_ca TINYINT UNSIGNED NOT NULL,
econ_score_exam TINYINT UNSIGNED NOT NULL,
civic_score_ca TINYINT UNSIGNED NOT NULL,
civic_score_exam TINYINT UNSIGNED NOT NULL,
lit_score_ca TINYINT UNSIGNED NOT NULL,
lit_score_exam TINYINT UNSIGNED NOT NULL
);
LOAD DATA INFILE 'C:/Users/HP USER/Documents/mytest.csv'
INTO TABLE import_csv
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'rn'
IGNORE 1 ROWS;
然后用它来填充你的其他表:
INSERT IGNORE INTO classes (name)
SELECT DISTINCT class
FROM import_csv;
INSERT IGNORE INTO students (name, class_id)
SELECT DISTINCT csv.name, c.id
FROM import_csv csv
JOIN classes c ON c.name = csv.class;
INSERT INTO results (student_id, subject_id, ca_score, exam_score)
SELECT s.id, 1 /* Math */, csv.math_score_ca, csv.math_score_exam
FROM import_csv csv
JOIN classes c ON csv.class = c.name
JOIN students s ON csv.name = s.name AND c.id = s.class_id;
INSERT INTO results (student_id, subject_id, ca_score, exam_score)
SELECT s.id, 2 /* Econ */, csv.econ_score_ca, csv.econ_score_exam
FROM import_csv csv
JOIN classes c ON csv.class = c.name
JOIN students s ON csv.name = s.name AND c.id = s.class_id;
INSERT INTO results (student_id, subject_id, ca_score, exam_score)
SELECT s.id, 3 /* Civic */, csv.civic_score_ca, csv.civic_score_exam
FROM import_csv csv
JOIN classes c ON csv.class = c.name
JOIN students s ON csv.name = s.name AND c.id = s.class_id;
INSERT INTO results (student_id, subject_id, ca_score, exam_score)
SELECT s.id, 4 /* Lit */, csv.lit_score_ca, csv.lit_score_exam
FROM import_csv csv
JOIN classes c ON csv.class = c.name
JOIN students s ON csv.name = s.name AND c.id = s.class_id;