【mysql经典题】数据准备

#  创建学生表
DROP TABLE IF EXISTS student;
CREATE TABLE IF NOT EXISTS student(
    sno INT(11) NOT NULL AUTO_INCREMENT,
    sname VARCHAR(20) NOT NULL,
    birth DATE DEFAULT NULL,
    gender ENUM('男', '女', '保密') DEFAULT '保密',
    PRIMARY KEY(sno)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
# 插入数据
INSERT INTO student VALUES(1, '赵雷', '1990-01-01', '男');
INSERT INTO student VALUES(2, '钱电', '1990-12-21', '男');
INSERT INTO student VALUES(3, '孙风', '1990-05-20', '男');
INSERT INTO student VALUES(4, '李云', '1990-08-06', '男');
INSERT INTO student VALUES(5, '周梅', '1991-12-01', '女');
INSERT INTO student VALUES(6, '吴兰', '1992-03-01', '女');
INSERT INTO student VALUES(7, '郑竹', '1989-07-01', '女');
INSERT INTO student VALUES(8, '王菊', '1990-01-20', '女');

# 创建教师表
DROP TABLE IF EXISTS teacher;
CREATE TABLE IF NOT EXISTS teacher(
    tno INT(11) NOT NULL AUTO_INCREMENT,
    tname VARCHAR(20) NOT NULL,
    PRIMARY KEY(tno)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
# 插入数据
INSERT INTO teacher VALUES(1, '张三');
INSERT INTO teacher VALUES(2, '李四');
INSERT INTO teacher VALUES(3, '王五');


# 创建课程表
DROP TABLE IF EXISTS course;
CREATE TABLE IF NOT EXISTS course(
    cno INT(11) NOT NULL AUTO_INCREMENT,
    cname VARCHAR(20) NOT NULL,
    tno INT(11),
    PRIMARY KEY(cno),
    FOREIGN KEY(tno) REFERENCES teacher(tno)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
# 插入数据
INSERT INTO course VALUES(1, '语文', 2);
INSERT INTO course VALUES(2, '数学', 1);
INSERT INTO course VALUES(3, '英语', 3);

# 创建成绩表
DROP TABLE IF EXISTS sc;
CREATE TABLE IF NOT EXISTS sc(
    sno INT(11) NOT NULL AUTO_INCREMENT,
    cno INT(11) NOT NULL,
    score INT(11),
    FOREIGN KEY(sno) REFERENCES student(sno),
    FOREIGN KEY(cno) REFERENCES course(cno)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
# 插入数据
insert into sc values(1, 1, 80);
insert into sc values(1, 2, 90);
insert into sc values(1, 3, 99);
insert into sc values(2, 1, 70);
insert into sc values(2, 2, 60);
insert into sc values(2, 3, 80);
insert into sc values(3, 1, 80);
insert into sc values(3, 2, 80);
insert into sc values(3, 3, 80);
insert into sc values(4, 1, 50);
insert into sc values(4, 2, 30);
insert into sc values(4, 3, 20);
insert into sc values(5, 1, 76);
insert into sc values(5, 2, 87);
insert into sc values(6, 1, 31);
insert into sc values(6, 3, 34);
insert into sc values(7, 2, 89);
insert into sc values(7, 3, 98);

注意：

• 每日一题，大家一起监督、讨论学习。