MySQL数据库-子查询-表联接（内/外）-综合练习

– 学生信息表
CREATE TABLE students(
studentid INT AUTO_INCREMENT PRIMARY KEY,-- 学号
studentName VARCHAR(20) NOT NULL,-- 姓名
gender CHAR(20),-- 性别
birthday DATE
)ENGINE=INNODB;
INSERT INTO students(studentname,gender,birthday) VALUES
(‘张三’,‘男’,‘1987-02-01’),
(‘李斯’,‘女’,‘1990-12-01’),
(‘王二’,‘男’,‘1988-09-01’),
(‘黎明’,‘男’,‘1991-08-01’);

– 老师信息表
CREATE TABLE teachers(
teacherid INT AUTO_INCREMENT NOT NULL PRIMARY KEY, – 老师编号
teacherName VARCHAR(20) NOT NULL-- 老师姓名
)ENGINE=INNODB;
INSERT INTO teachers(teacherName)
VALUES(‘张三’),(‘李四’),(‘王五’),(‘Jacky’),(‘Helen’),(‘Kevin’);

– 课程表
CREATE TABLE courses(
Courseid INT AUTO_INCREMENT NOT NULL, – 课程号
courseName VARCHAR(20) NOT NULL UNIQUE, – 课程名
TeacherID INT NOT NULL , – 教师编号
FOREIGN KEY (teacherID) REFERENCES teachers(teacherId),
PRIMARY KEY (co