当前位置：首页 > article >正文

50道题快速复习MySQL之准备篇

article 2025/2/25 13:21:44

文章目录

1.创建数据库表
2.创建表数据

本文旨在帮助大家快速复习MySQL,共有4张表,50道题.
本篇文章在做讲解50道题目之前，先将数据库表以及表中的数据创建好。

1.创建数据库表

建表语句如下，分别是学生表，课程表，教师表和成绩表。

#–1.学生表
#Student(s_id,s_name,s_birth,s_sex) – 学生编号,学生姓名, 出生年月,学生性别
CREATE TABLE `Student`
(
    `s_id`  VARCHAR(20),
    s_name  VARCHAR(20) NOT NULL DEFAULT '',
    s_brith VARCHAR(20) NOT NULL DEFAULT '',
    s_sex   VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY (s_id)
);

#–2.课程表
#Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
create table Course
(
    c_id   varchar(20),
    c_name VARCHAR(20) not null DEFAULT '',
    t_id   VARCHAR(20) NOT NULL,
    PRIMARY KEY (c_id)
);

/*
–3.教师表
Teacher(t_id,t_name) –教师编号,教师姓名
*/
CREATE TABLE Teacher
(
    t_id   VARCHAR(20),
    t_name VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY (t_id)
);

/*
–4.成绩表
Score(s_id,c_id,s_score) –学生编号,课程编号,分数
*/
Create table Score
(
    s_id    VARCHAR(20),
    c_id    VARCHAR(20) not null default '',
    s_score INT(3),
    primary key (`s_id`, `c_id`)
);

2.创建表数据

#--插入学生表测试数据
#('01' , '赵雷' , '1990-01-01' , '男')
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 Course
values ('01', '语文', '02');
insert into Course
values ('02', '数学', '01');
insert into Course
values ('03', '英语', '03');

#--教师表测试数据
insert into Teacher
values ('01', '张三');
insert into Teacher
values ('02', '李四');
insert into Teacher
values ('03', '王五');

#--成绩表测试数据
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);