MySQL50道经典_MySQL最经典50道练习题

表名和字段

学生表

Student(s_id,s_name,s_birth,s_sex)：学生编号、学生姓名、出生年月、学生性别。

课程表

Course(c_id,c_name,t_id)：课程编号、课程名称、教师编号。

教师表

Teacher(t_id,t_name)：教师编号、教师姓名。

成绩表

Score(s_id,c_id,s_score)：学生编号、课程编号、分数。

建表语句

# 建表语句

# 学生表

CREATE TABLE `student`(

`s_id` VARCHAR(20),

`s_name` VARCHAR(20) NOT NULL DEFAULT ‘‘,

`s_birth` VARCHAR(20) NOT NULL DEFAULT ‘‘,

`s_sex` VARCHAR(10) NOT NULL DEFAULT ‘‘,

PRIMARY KEY(`s_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`)

);

# 教师表

CREATE TABLE `teacher`(

`t_id` VARCHAR(20),

`t_name` VARCHAR(20) NOT NULL DEFAULT ‘‘,

PRIMARY KEY(`t_id`)

);

# 成绩表

CREATE TABLE `score`(

`s_id` VARCHAR(20),

`c_id` VARCHAR(20),

`s_score` INT(3),

PRIMARY KEY(`s_id`,`c_id`)

);

# 插入学生表测试数据

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‘, ‘女‘);

# 插入课程表测试数据

insert into Course values(‘1‘, ‘语文‘, ‘2‘);

insert into Course values(‘2‘, ‘数学‘, ‘1‘);

insert into Course values(‘3‘, ‘英语‘, ‘3‘);

# 插入教师表测试数据

insert into Teacher values(‘1‘, ‘张三‘);

insert into Teacher values(‘2‘, ‘李四‘);

insert into Teacher values(‘3‘, ‘王五‘);

# 插入成绩表测试数据

insert into Score values(‘1‘, ‘1‘, 80);

insert into Score values(‘1‘, ‘2‘, 90);

insert into Score values(‘1‘, ‘3‘, 99);

insert into Score values(‘2‘, ‘1‘, 70);

insert into Score values(‘2‘, ‘2‘, 60);

insert into Score values(‘2‘, ‘3‘, 80);

insert into Score values(‘3‘, ‘1‘, 80);

insert into Score values(‘3‘, ‘2‘, 80);

insert into Score values(‘3‘, ‘3‘, 80);

insert into Score values(‘4‘, ‘1‘, 50);

insert into Score values(‘4‘, ‘2‘, 30);

insert into Score values(‘4‘, ‘3‘, 20);

insert into Score values(‘5‘, ‘1‘, 76);

insert into Score values(‘5‘, ‘2‘, 87);

insert into Score values(‘6‘, ‘1‘, 31);

insert into Score values(‘6‘, ‘3‘, 34);

insert into Score values(‘7‘, ‘2‘, 89);

insert into Score values(‘7‘, ‘3‘, 98);

原文：https://www.cnblogs.com/SunnyGao/p/13874078.html