SQL编程——MySQL数据库基本操作

实验平台：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