mysql数据库题目

– 一.准备数据

已知有如下4张表：
学生表：student(学号,学生姓名,出生年月,性别)
成绩表：score(学号,课程号,成绩)
课程表：course(课程号,课程名称,教师号)
教师表：teacher(教师号,教师姓名)
根据以上信息按照下面要求写出对应的SQL语句。

--auto_increment   是属性自增

drop database if exists school;
create database school charset utf8;
use school;
create table if not exists student(
    id int primary key, 
    name varchar(50),
    birthday date,
    gender char(1)
);
	create table if not exists teacher(
    id int primary key,
    name varchar(50)
);
	 create table if not exists course(
    id int primary key,
    name varchar(50),
    t_id int,
    foreign key(t_id) references teacher(id)
);
create table if not exists score(
    s_id int,
    c_id int,
    score int,
			primary key(s_id,c_id),
    foreign key(s_id) references student(id),
    foreign key(c_id) references course(id)
);

准备数据
添加数据的sql
student表

insert into student 
values('0001' , '猴子' , '1989-01-01' , '男');

insert into student
values('0002' , '猴子' , '1990-12-21' , '女');

insert into student
values('0003' , '马云' , '1991-12-21' , '男');

insert into student
values('0004' , '王思聪' , '1990-05-20' , '男');

teacher表

insert into teacher
values('0001' , '孟扎扎');

insert into teacher
values('0002&