1.操作teacher表
(1)首先创建数据库school,代码如下:
CREATE DATABASE school;
(2)创建teacher表,代码如下:
CREATE TABLE teacher(
id INT(4) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
num INT(10) NOT NULL UNIQUE ,
name VARCHAR(20) NOT NULL ,
sex VARCHAR(4) NOT NULL ,
birthday DATETIME ,
address VARCHAR(50)
);
(3)将teacher表的name字段的数据类型改为VARCHAR(30),代码如下:
ALTER TABLE teacher MODIFY name VARCHAR(30) NOT NULL;
(4)将birthday字段的位置改到sex字段的前面,代码如下:
ALTER TABLE teacher MODIFY birthday DATETIME AFTER name;
(5)将num字段改名为t_id,代码如下:
ALTER TABLE teacher CHANGE num t_id INT(10) NOT NULL;
(6)将teacher表的address字段删除,代码如下:
ALTER TABLE teacher DROP address;
(7)在teacher表中增加名为wages的字段,数据类型为FLOAT,代码如下:
ALTER TABLE teacher ADD wages FLOAT;
(8)将teacher表改名为teacherInfo,代码如下:
ALTER TABLE teacher RENAME teacherInfo;
(9)将teacher表的存储引擎更改为MyISAM类型,代码如下:
ALTER TABLE teacherInfo ENGINE=MyISAM;
2.操作department表和worker表
(1)创建department表,代码如下:
CREATE TABLE department(
d_id INT(4) NOT NULL UNIQUE PRIMARY KEY ,
d_name VARCHAR(20) NOT NULL UNIQUE ,
function VARCHAR(50) ,
address VARCHAR(50)
);
(2)创建worker表,代码如下:
CREATE TABLE worker (
id INT(4) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
num INT(10) NOT NULL UNIQUE ,
d_id INT(4) ,
name VARCHAR(20) NOT NULL ,
sex VARCHAR(4) NOT NULL ,
birthday DATE ,
address VARCHAR(50) ,
CONSTRAINT worker_fk FOREIGN KEY (d_id)
REFERENCES department (d_id)
);
(3)删除department表,代码如下:
DROP TABLE department;
4)删除worker表的外键约束,代码如下:
ALTER TABLE worker DROP FOREIGN KEY worker_fk;
(5)重新删除department表,代码如下:
DROP TABLE department;