-- MySQL中多表查询练习题,题目有点难,多想想
-- 答案仅提供参考,不唯一
#创建表及插入记录
CREATE TABLE class ( cid INT ( 11 ) NOT NULL AUTO_INCREMENT, caption VARCHAR ( 32 ) NOT NULL, PRIMARY KEY ( cid ) ) ENGINE = INNODB CHARSET = utf8;
INSERT INTO class
VALUES
( 1, '三年二班' ),
( 2, '三年三班' ),
( 3, '一年二班' ),
( 4, '二年九班' );
CREATE TABLE student (
sid INT ( 11 ) NOT NULL AUTO_INCREMENT,
gender CHAR ( 1 ) NOT NULL,
class_id INT ( 11 ) NOT NULL,
sname VARCHAR ( 32 ) NOT NULL,
PRIMARY KEY ( sid ),
KEY fk_class ( class_id ),
CONSTRAINT fk_class FOREIGN KEY ( class_id ) REFERENCES class ( cid )
) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO student
VALUES
( 1, '男', 1, '理解' ),
( 2, '女', 1, '钢蛋' ),
( 3, '男', 1, '张三' ),
( 4, '男', 1, '张一' ),
( 5, '女', 1, '张二' ),
( 6, '男', 1, '张四' ),
( 7, '女', 2, '铁锤' ),
( 8, '男', 2, '李三' ),
( 9, '男', 2, '李一' ),
( 10, '女', 2, '李二' ),
( 11, '男', 2, '李四' ),
( 12, '女', 3, '如花' ),
( 13, '男', 3, '刘三' ),
( 14, '男', 3, '刘一' ),
( 15, '女', 3, '刘二' ),
( 16, '男', 3, '刘四' );
CREATE TABLE teacher ( tid INT ( 11 ) NOT NULL AUTO_INCREMENT, tname VARCHAR ( 32 ) NOT NULL, PRIMARY KEY ( tid ) ) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO teacher
VALUES
( 1, '张磊老师' ),
( 2, '李平老师' ),
( 3, '刘海燕老师' ),
( 4, '朱云海老师' ),
( 5, '李杰老师' );
CREATE TABLE course (
cid INT ( 11 ) NOT NULL AUTO_INCREMENT,
cname VARCHAR ( 32 ) NOT NULL,
teacher_id INT ( 11 ) NOT NULL,
PRIMARY KEY ( cid ),
KEY fk_course_teacher ( teacher_id ),
CONSTRAINT fk_course_teacher FOREIGN KEY ( teacher_id ) REFERENCES teacher ( tid )
) ENGINE = INNODB