数据库系统概念示例

Windows PowerShell
版权所有（C） Microsoft Corporation。保留所有权利。

安装最新的 PowerShell，了解新功能和改进！https://aka.ms/PSWindows

PS C:/Users/iyang> mysql -u root -p
Enter password: ************
Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 8
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.

mysql> create database if not exists school;
Query OK, 1 row affected (0.00 sec)

mysql> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
| test               |
+--------------------+
6 rows in set (0.01 sec)

mysql> use school
Database changed
mysql> create table if not exists classroom (
    ->  building   varchar(15),
    ->  room_number   varchar(7),
    ->  capacity     numeric(4,0),
    ->  primary key(building, room_number));
Query OK, 0 rows affected (0.02 sec)

mysql> create table if not exists department (
    ->  dept_name   varchar(20),
    ->  building   varchar(15),
    ->  budget     numeric(12, 2) check(budget > 0),
    ->  primary key(dept_name));
Query OK, 0 rows affected (0.01 sec)

mysql> create table if not exists course (
    ->  course_id   varchar(8),
    ->  title   varchar(50),
    ->  dept_name    varchar(20),
    ->  credits     numeric(2,0) check(credits > 0),
    ->  primary key(course_id),
    ->  constraint foreign key (dept_name) references department(dept_name) on delete set null);
Query OK, 0 rows affected (0.02 sec)

mysql> create table if not exists instructor (
    ->  ID varchar(5),
    ->  name varchar(20) not null,
    ->  dept_name  varchar(20),
    ->  salary   numeric(8,2) check(salary > 29000),
    ->  primary key(ID),
    ->  constraint foreign key (dept_name) references department(dept_name) on delete set null);
Query OK, 0 rows affected (0.02 sec)

mysql> create table if not exists 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),
    ->  constraint foreign key (course_id) references course(course_id) on delete cascade,
    ->  constraint foreign key (building, room_number) references classroom(building, room_number) on delete set null);
Query OK, 0 rows affected (0.03 sec)

mysql> create table if not exists 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),
    ->  constraint foreign key(course_id, sec_id, semester, year) references section(course_id, sec_id, semester, year) on delete cascade,
    ->  constraint foreign key(ID) references instructor(ID) on delete cascade);
Query OK, 0 rows affected (0.02 sec)

mysql> create table if not exists 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),
    ->  constraint foreign key (dept_name) references department(dept_name) on delete set null);
Query OK, 0 rows affected (0.02 sec)

mysql> create table if not exists 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),
    ->  constraint foreign key(course_id, sec_id, semester, year) references section(course_id, sec_id, semester, year) on delete cascade,
    ->  constraint foreign key(ID) references student(ID) on delete cascade);
Query OK, 0 rows affected (0.03 sec)

mysql> create table if not exists advisor(
    ->  s_ID        varchar(5),
    ->  i_ID            varchar(5),
    ->  primary key(s_ID),
    ->  constraint foreign key(i_ID) references instructor(ID) on delete set null,
    ->  constraint foreign key(s_ID) references student(ID) on delete cascade);
Query OK, 0 rows affected (0.03 sec)

mysql> 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) on delete cascade
    ->     );
Query OK, 0 rows affected (0.02 sec)

mysql> 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)
    ->     );
Query OK, 0 rows affected (0.01 sec)