请帮我生成一个学生、课程、教师和学生课程数据库表的sql语句以及往表中插入数据的语句
以下是学生、课程、教师和学生课程表的SQL语句:
学生表:
CREATE TABLE student ( student_id INT PRIMARY KEY, student_name VARCHAR(50) NOT NULL, student_age INT, student_gender VARCHAR(10), student_address VARCHAR(100), student_phone VARCHAR(20) );
课程表:
CREATE TABLE course ( course_id INT PRIMARY KEY, course_name VARCHAR(50) NOT NULL, course_description VARCHAR(200), course_teacher_id INT, FOREIGN KEY (course_teacher_id) REFERENCES teacher(teacher_id) );
教师表:
CREATE TABLE teacher ( teacher_id INT PRIMARY KEY, teacher_name VARCHAR(50) NOT NULL, teacher_age INT, teacher_gender VARCHAR(10), teacher_address VARCHAR(100), teacher_phone VARCHAR(20) );
学生课程表:
CREATE TABLE student_course ( student_id INT, course_id INT, score INT, PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES student(student_id), FOREIGN KEY (course_id) REFERENCES course(course_id) );
以下是向表中插入数据的SQL语句:
向学生表中插入数据:
INSERT INTO student (student_id, student_name, student_age, student_gender, student_address, student_phone) VALUES (1, '张三', 20, '男', '北京市海淀区', '13333333333'), (2, '李四', 21, '女', '北京市朝阳区', '14444444444'), (3, '王五', 22, '男', '北京市丰台区', '15555555555');
向课程表中插入数据:
INSERT INTO course (course_id, course_name, course_description, course_teacher_id) VALUES (1, '数据库原理', '数据库的基本概念和设计方法', 1), (2, '操作系统原理', '操作系统的功能和实现原理', 2), (3, '计算机网络', '计算机网络的基本概念和协议', 3);
向教师表中插入数据:
INSERT INTO teacher (teacher_id, teacher_name, teacher_age, teacher_gender, teacher_address, teacher_phone) VALUES (1, '张老师', 35, '女', '北京市海淀区', '16666666666'), (2, '李老师', 40, '男', '北京市朝阳区', '17777777777'), (3, '王老师', 45, '女', '北京市丰台区', '18888888888');
向学生课程表中插入数据:
INSERT INTO student_course (student_id, course_id, score) VALUES (1, 1, 90), (2, 1, 85), (3, 1, 95), (1, 2, 80), (2, 2, 75), (3, 2, 85), (1, 3, 85), (2, 3, 90), (3, 3, 95)
原文地址: https://www.cveoy.top/t/topic/eHqF 著作权归作者所有。请勿转载和采集!