MYSQL数据库系统第2次实验 数据表的创建

实验目的：

1. 熟悉数据表约束；
2. 熟练掌握创建数据表。
   实验设备及器材：
   PC，mysql 等

实验内容：
1.创建数据库teaching;

mysql>create database if not exists teaching;

2.选择数据库teaching；

mysql> use teaching;

3.在teaching数据库中根据以下要求创建以下6张表：

表student

mysql> create table if not exists student(
    -> studentno char(11) not null comment'学号',
    -> sname char(8) not null comment'姓名',
    -> sex enum('男','女') default '男' comment'性别',
    -> brithdate date not null comment'出生日期',
    -> entrance int(3) null comment'入学成绩',
    -> phone varchar(12) not null comment'电话',
    -> Email varchar(20) not null comment'电子邮箱',
    -> primary key(studentno),
    -> unique (Email)
    -> )charset='utf8';

表course

mysql> create table if not exists course(
    -> courseno char(6) not null comment'课程编号',
    -> cname char(6) not null comment'课程名称',
    -> type char(8) not null comment'类别',
    -> period int(2) not null comment'总学时',
    -> exp int (2) not null comment'实验学时',
    -> term int(2) not null comment '开学学期',
    -> primary key(courseno)
    -> )charset='utf8';

表score

mysql> create table if not exists score(
    -> studentno char(11) not null comment'学号',
    -> courseno char(6) not null comment'课程编号',
    -> daily float(3,1) default '0.0' comment'平时成绩',
    -> final float(3,1) default '0.0' comment'期末成绩',
    -> primary key(studentno,courseno)
    -> )charset='utf8';

表teacher

mysql> create table if not exists teacher(
    -> teacherno char(6) not null comment'教师编号',
    -> tname char(8) not null comment'教师姓名',
    -> major char(10) not null comment'专业',
    -> prof char(10) not null comment'职称',
    -> department char(16) not null comment'部门',
    -> primary key(teacherno)
    -> )charset='utf8';

表teach_course

mysql> create table if not exists teach_course(
    -> teacherno char(6) not null comment'教师编号',
    -> courseno char(6) not null comment'课程编号',
    -> primary key(teacherno,courseno)
    -> )charset='utf8';

表sc

mysql> create table if not exists sc(
    -> sc_no int(6) not null auto_increment comment'选课编号',
    -> student char(11) not null comment'学生编号',
    -> courseno char(6) not null comment'课程编号',
    -> teacherno char(6) not null comment'教师编号',
    -> sc_time timestamp not null default now() comment'选课时间',
    -> primary key(sc_no)
    -> )charset='utf8';