(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;
(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 work_fk FOREIGN KEY(d_id) REFERENCES department(d_id)
);
(3)删除department表
DROP TABLE department;
//存在外键约束,不能删除和更新父表
(4)删除worker表的外键约束
ALTER TABLE worker DROP FOREIGN KEY work_fk;
(5)重新删除department表
DROP TABLE department;
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;
(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 work_fk FOREIGN KEY(d_id) REFERENCES department(d_id)
);
(3)删除department表
DROP TABLE department;
//存在外键约束,不能删除和更新父表
(4)删除worker表的外键约束
ALTER TABLE worker DROP FOREIGN KEY work_fk;
(5)重新删除department表
DROP TABLE department;