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)