数据库系统概念第六版mysql加载university数据库

课本提供的是SQLite的数据库.db文件格式，网上大多数都是用SQLite数据库的创建表（DDL）语句来创建的，我想用MySQL，就有了下面这些。

文中提到的内容，百度网盘下载链接：

链接：https://pan.baidu.com/s/1tIJlzUeyigMC8LJfjHm4Qw
提取码：1234

一、打开mysql

Windows+R，输入cmd打开终端。

输入：

mysql -uroot -p

密码自己输入自己设置的密码。成功进来应该是这样：

二、创建一个自己的数据库

（不能与目前有的数据库名重复，使用下面语句查看当前数据库系统里面有哪些数据库）

show databases;

创建数据库：(我已经创建过一个university的数据库了，不能重名，可以自己起名)

create database university_1;

创建成功后，再次查看已有的数据库，就有了university_1：

三、创建表

先确定对哪个数据库操作，刚才创建的那个空数据库:

use university_1;

然后创建里面所有的表（不会生成sql文件，复制粘贴一下就行）：

CREATE TABLE classroom
    (building       varchar(15),
     room_number        varchar(7),
     capacity       numeric(4,0),
     primary key (building, room_number)
    );

CREATE TABLE department
    (dept_name      varchar(20),
     building       varchar(15),
     budget             numeric(12,2) check (budget > 0),
     primary key (dept_name)
    );

CREATE TABLE course
    (course_id      varchar(8),
     title          varchar(50),
     dept_name      varchar(20),
     credits        numeric(2,0) check (credits > 0),
     primary key (course_id),
     foreign key (dept_name) references department(dept_name)
        on delete set null
    );

CREATE TABLE instructor
    (ID         varchar(5),
     name           varchar(20) not null,
     dept_name      varchar(20),
     salary         numeric(8,2) check (salary > 29000),
     primary key (ID),
     foreign key (dept_name) references department(dept_name)
        on delete set null
    );

CREATE TABLE section
    (course_id      varchar(8),
         sec_id         varchar(8),
     semester       varchar(6)
        check (semester in ('Fall', 'Winter', 'Spring', 'Summer')),
     year           numeric(4,0) check (year > 1701 and year < 2100),
     building       varchar(15),
     room_number        varchar(7),
     time_slot_id       varchar(4),
     primary key (course_id, sec_id, semester, year),
     foreign key (course_id) references course(course_id)
        on delete cascade,
     foreign key (building, room_number) references classroom(building, room_number)
        on delete set null
    );

CREATE TABLE teaches
    (ID         varchar(5),
     course_id      varchar(8),
     sec_id         varchar(8),
     semester       varchar(6),
     year           numeric(4,0),
     primary key (ID, course_id, sec_id, semester, year),
     foreign key (course_id,sec_id, semester, year) references section(course_id, sec_id, semester, year)
        on delete cascade,
     foreign key (ID) references instructor(ID)
        on delete cascade
    );

CREATE TABLE student
    (ID         varchar(5),
     name           varchar(20) not null,
     dept_name      varchar(20),
     tot_cred       numeric(3,0) check (tot_cred >= 0),
     primary key (ID),
     foreign key (dept_name) references department(dept_name)
        on delete set null
    );

CREATE TABLE takes
    (ID         varchar(5),
     course_id      varchar(8),
     sec_id         varchar(8),
     semester       varchar(6),
     year           numeric(4,0),
     grade              varchar(2),
     primary key (ID, course_id, sec_id, semester, year),
     foreign key (course_id,sec_id, semester, year) references section(course_id, sec_id, semester, year)
        on delete cascade,
     foreign key (ID) references student(ID)
        on delete cascade
    );

CREATE TABLE advisor
    (s_ID           varchar(5),
     i_ID           varchar(5),
     primary key (s_ID),
     foreign key (i_ID) references instructor (ID)
        on delete set null,
     foreign key (s_ID) references student (ID)
        on delete cascade
    );

CREATE TABLE time_slot
    (time_slot_id       varchar(4),
     day            varchar(1),
     start_hr       numeric(2) check (start_hr >= 0 and start_hr < 24),
     start_min      numeric(2) check (start_min >= 0 and start_min < 60),
     end_hr         numeric(2) check (end_hr >= 0 and end_hr < 24),
     end_min        numeric(2) check (end_min >= 0 and end_min < 60),
     primary key (time_slot_id, day, start_hr, start_min)
    );

CREATE TABLE prereq
    (course_id      varchar(8),
     prereq_id      varchar(8),
     primary key (course_id, prereq_id),
     foreign key (course_id) references course(course_id)
        on delete cascade,
     foreign key (prereq_id) references course(course_id)
    );

生成完应该是这样：