6.笔记 MySQL学习——通过脚本创建表

6.笔记 MySQL学习——通过脚本创建表

此处需要一个脚本如下：

DROP TABLE IF EXISTS student;

#@ _CREATE_TABLE_

CREATE TABLE student

(

 name       VARCHAR(20) NOT NULL,

 sex        ENUM('F','M') NOT NULL,

 student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

 PRIMARY KEY (student_id)

) ENGINE=InnoDB;

然后执行如下：

[root@OEL sampdb]# mysql sampdb <create_student.sql  -u root -p

Enter password:

查看如下：

mysql> show tables;

+------------------+

| Tables_in_sampdb |

+------------------+

| member           |

| president        |

| student          |

+------------------+

3 rows in set (0.00 sec)

已经存在student表。

同理创建grade_event,score,absence表

[root@OEL sampdb]# mysql sampdb <create_grade_event.sql  -u root -p

Enter password:

[root@OEL sampdb]# mysql sampdb <create_score.sql  -u root -p

Enter password:

[root@OEL sampdb]# mysql sampdb <create_absence.sql  -u root -p

Enter password:

内容分别如下：

create_grade_event.sql

DROP TABLE IF EXISTS grade_event;

CREATE TABLE grade_event

(

  date     DATE NOT NULL,

  categoryENUM('T','Q') NOT NULL,

  event_id INTUNSIGNED NOT NULL AUTO_INCREMENT,

  PRIMARY KEY(event_id)

) ENGINE=InnoDB;

create_score.sql

DROP TABLE IF EXISTS score;

CREATE TABLE score

(

  student_idINT UNSIGNED NOT NULL,

  event_id   INT UNSIGNED NOT NULL,

  score      INT NOT NULL,

  PRIMARY KEY(event_id, student_id),

  INDEX(student_id),

  FOREIGN KEY(event_id) REFERENCES grade_event (event_id),

  FOREIGN KEY(student_id) REFERENCES student (student_id)

) ENGINE=InnoDB;

create_absence.sql

DROP TABLE IF EXISTS absence;

CREATE TABLE absence

(

  student_idINT UNSIGNED NOT NULL,

  date       DATE NOT NULL,

  PRIMARY KEY(student_id, date),

  FOREIGN KEY(student_id) REFERENCES student (student_id)

) ENGINE=InnoDB;