-- 创建数据库
CREATE DATABASE select_test;
-- 切换数据库
USE select_test;-- 创建学生表
CREATE TABLE student (no VARCHAR(20) PRIMARY KEY,name VARCHAR(20) NOT NULL,sex VARCHAR(10) NOT NULL,birthday DATE, -- 生日class VARCHAR(20) -- 所在班级
);-- 创建教师表
CREATE TABLE teacher (no VARCHAR(20) PRIMARY KEY,name VARCHAR(20) NOT NULL,sex VARCHAR(10) NOT NULL,birthday DATE,profession VARCHAR(20) NOT NULL, -- 职称department VARCHAR(20) NOT NULL -- 部门
);-- 创建课程表
CREATE TABLE course (no VARCHAR(20) PRIMARY KEY,name VARCHAR(20) NOT NULL,t_no VARCHAR(20) NOT NULL, -- 教师编号-- 表示该 tno 来自于 teacher 表中的 no 字段值FOREIGN KEY(t_no) REFERENCES teacher(no)
);-- 成绩表
CREATE TABLE score (s_no VARCHAR(20) NOT NULL, -- 学生编号c_no VARCHAR(20) NOT NULL, -- 课程号degree DECIMAL, -- 成绩-- 表示该 s_no, c_no 分别来自于 student, course 表中的 no 字段值FOREIGN KEY(s_no) REFERENCES student(no), FOREIGN KEY(c_no) REFERENCES course(no),-- 设置 s_no, c_no 为联合主键PRIMARY KEY(s_no, c_no)
);-- 查看所有表
SHOW TABLES;-- 添加学生表数据
INSERT INTO student VALUES('101', '曾华', '男', '1977-09-01', '95033');
INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031');
INSERT INTO student VALUES('103', '王丽', '女', '1976-01-23', '95033');
INSERT INTO student VALUES('104', '李军', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031');
INSERT INTO student VALUES('106', '陆军', '男', '1974-06-03', '95031');
INSERT INTO student VALUES('107', '王尼玛', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('108', '张全蛋', '男', '1975-02-10', '95031');
INSERT INTO student VALUES('109', '赵铁柱', '男', '1974-06-03', '95031');-- 添加教师表数据
INSERT INTO teacher VALUES('804', '李诚', '男', '1958-12-02', '副教授', '计算机系');
INSERT INTO teacher VALUES('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系');
INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '助教', '计算机系');
INSERT INTO teacher VALUES('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系');-- 添加课程表数据
INSERT INTO course VALUES('3-105', '计算机导论', '825');
INSERT INTO course VALUES('3-245', '操作系统', '804');
INSERT INTO course VALUES('6-166', '数字电路', '856');
INSERT INTO course VALUES('9-888', '高等数学', '831');-- 添加添加成绩表数据
INSERT INTO score VALUES('103', '3-105', '92');
INSERT INTO score VALUES('103', '3-245', '86');
INSERT INTO score VALUES('103', '6-166', '85');
INSERT INTO score VALUES('105', '3-105', '88');
INSERT INTO score VALUES('105', '3-245', '75');
INSERT INTO score VALUES('105', '6-166', '79');
INSERT INTO score VALUES('109', '3-105', '76');
INSERT INTO score VALUES('109', '3-245', '68');
INSERT INTO score VALUES('109', '6-166', '81');-- 查看表结构
SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;
SELECT * FROM teacher;
