挑战全网最全的50sql（从环境到sql，每天5道）

建库建表加数据

# 创建数据库，并且使用
CREATE DATABASE IF NOT EXISTS df_school;
USE df_school;
# 创建学生表
CREATE TABLE IF NOT EXISTS student(
    s_id VARCHAR(10),
    s_name VARCHAR(20),
    s_age DATE,
    s_sex VARCHAR(10)
);
# 创建课程表
CREATE TABLE IF NOT EXISTS course(
    c_id VARCHAR(10),
    c_name VARCHAR(20),
    t_id VARCHAR(10)
);
# 创建教师表
CREATE TABLE IF NOT EXISTS teacher (
    t_id VARCHAR(10),
    t_name VARCHAR(20)
);
# 插入教师数据
INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');
INSERT INTO teacher VALUES('04', '赵六');
INSERT INTO teacher VALUES('05', '孙七');
# 创建成绩表
CREATE TABLE IF NOT EXISTS score (
    s_id VARCHAR(10),
    c_id VARCHAR(10),
    score VARCHAR(10)
);
# 往成绩表插入数据
INSERT INTO Score VALUES('01' , '01' , 80);
INSERT INTO Score VALUES('01' , '02' , 90);
INSERT INTO Score VALUES('01' , '03' , 99);
INSERT INTO Score VALUES('02' , '01' , 70);
INSERT INTO Score VALUES('02' , '02' , 60);
INSERT INTO Score VALUES('02' , '03' , 80);
INSERT INTO Score VALUES('03' , '01' , 80);
INSERT INTO Score VALUES('03' , '02' , 80);
INSERT INTO Score VALUES('03' , '03' , 80);
INSERT INTO Score VALUES('04' , '01' , 50);
INSERT INTO Score VALUES('04' , '02' , 30);
INSERT INTO Score VALUES('04' , '03' , 20);
INSERT INTO Score VALUES('05' , '01' , 76);
INSERT INTO Score VALUES('05' , '02' , 87);
INSERT INTO Score VALUES('06' , '01' , 31);
INSERT INTO Score VALUES('06' , '03' , 34);
INSERT INTO Score VALUES('07' , '02' , 89);
INSERT INTO Score VALUES('07' , '03' , 98);
# 往学生表中插入数据
INSERT INTO student VALUES('01' , '赵雷' , '1990-01-01' , '男');
INSERT INTO Student VALUES('02' , '钱电' , '1990-12-21' , '男');
INSERT INTO Student VALUES('03' , '孙风' , '1990-05-20' , '男');
INSERT INTO Student VALUES('04' , '李云' , '1990-08-06' , '男');
INSERT INTO Student VALUES('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO Student VALUES('06' , '吴兰' , '1992-03-01' , '女');
INSERT INTO Student VALUES('07' , '郑竹' , '1989-07-01' , '女');
INSERT INTO Student VALUES('08' , '王菊' , '1990-01-20' , '女');
INSERT INTO student VALUES('09', '冯柳', '1991-06-10', '女');
INSERT INTO student VALUES('10', '陈梅', '1992-09-15', '女');
INSERT INTO student VALUES('11', '刘洋', '1991-03-25', '男');
INSERT INTO student VALUES('12', '陈飞', '1992-07-18', '男');
INSERT INTO student VALUES('13', '林雪', '1990-11-30', '女');
INSERT INTO student VALUES('14', '黄华', '1991-09-05', '女');
INSERT INTO student VALUES('15', '许琴', '1992-04-12', '女');
-- 插入课程数据
INSERT INTO Course VALUES('01' , '语文' , '02');
INSERT INTO Course VALUES('02' , '数学' , '01');
INSERT INTO Course VALUES('03' , '英语' , '03');
INSERT INTO course VALUES('04', '物理', '02');
INSERT INTO course VALUES('05', '化学', '01');
-- 插入更多成绩数据
INSERT INTO score VALUES('08', '04', 85);
INSERT INTO score VALUES('08', '05', 92);
INSERT INTO score VALUES('09', '04', 78);
INSERT INTO score VALUES('09', '05', 86);
INSERT INTO score VALUES('10', '04', 90);
INSERT INTO score VALUES('10', '05', 88);
INSERT INTO score VALUES('11', '04', 75);
INSERT INTO score VALUES('11', '05', 82);
INSERT INTO score VALUES('12', '04', 68);
INSERT INTO score VALUES('12', '05', 79);
INSERT INTO score VALUES('13', '04', 91);
INSERT INTO score VALUES('13', '05', 85);
INSERT INTO score VALUES('14', '04', 73);
INSERT INTO score VALUES('14', '05', 77);
INSERT INTO score VALUES('15', '04', 84);
INSERT INTO score VALUES('15', '05', 88)