实验平台:mysql5.7,
案例:学生-课程-成绩
实际的数据库设计可能和课本中有出入
E-R如下:
代码如下:
一、表设计
#1. 数据库创建
DROP DATABASE IF EXISTS scoredb;
CREATE DATABASE scoredb;
#2. 创建表
#1) 创建新表
USE scoredb;
CREATE TABLE student
(
student_id SMALLINT UNSIGNED NOT NULL,
student_name VARCHAR(50) NOT NULL,
student_sex TINYINT(1) NOT NULL DEFAULT 0,
student_phone VARCHAR(50) DEFAULT NULL,
student_addr_id SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (student_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
#2) 根据已有的表创建新表
USE scoredb;
-1. CREATE TABLE student like student_info;
-2. CREATE TABLE student AS SELECT student_info.student_id,
student_info.student_name, student_info.student_sex,
student_info.student_phone, student_info.student_addr_id
FROM student_info;
#3. 删除表
USE scoredb;
DROP TABLE student_info;
#4 修改表
USE scoredb;
ALTER TABLE student ADD COLUMN depart_id SMALLINT UNSIGNED NOT NULL;
ALTER TABLE student ADD PRIMARY KEY(student_id);
ALTER TABLE student DROP PRIMARY KEY(student_id);
#5 创建其他相关表
USE scoredb;
CREATE TABLE address
(
addr_id SMALLINT UNSIGNED NOT NULL,
addr_info VARCHAR(100) NOT NULL,
PRIMARY KEY(addr_id)
) ENGINE=INNODB,CHARSET=utf8;
create TABLE department
(
depart_id SMALLINT UNSIGNED NOT NULL,
depart_name VARCHAR(100) NOT NULL,
PRIMARY key(depart_id)
) ENGINE=INNODB,CHARSET=utf8;
create