一、创表

CREATE TABLE offices
(
    officecode INT(10),
    city       VARCHAR(50) NOT NULL,
    address    VARCHAR(50),
    country    VARCHAR(50) NOT NULL,
    postalcode VARCHAR(15) UNIQUE,
    PRIMARY KEY (officecode)
);

• 外键

CREATE TABLE employees
(
    employeenumber INT(11) PRIMARY KEY AUTO_INCREMENT,
    lastname       VARCHAR(50) NOT NULL,
    firstname      VARCHAR(50) NOT NULL,
    mobile         VARCHAR(25) UNIQUE,
    officecode     INT(10)     NOT NULL,
    jobtitle       VARCHAR(50) NOT NULL,
    birth          DATETIME    NOT NULL,
    note           VARCHAR(255),
    sex            VARCHAR(5),
    CONSTRAINT fk_emp_ofcode FOREIGN KEY (officecode) REFERENCES offices (officecode)
);

二、ALTER

• 将表employees的mobile字段修改到officeCode字段后面

ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER officeCode;

• 将表employees的birth字段改名为employee_birth

ALTER TABLE employees CHANGE birth employee_birth DATETIME; 

• 修改sex字段，数据类型为CHAR(1)，非空约束

ALTER TABLE employees MODIFY sex CHAR(1) NOT NULL;

• 删除字段note

ALTER TABLE employees DROP COLUMN note; 

• 增加字段名favoriate_activity，数据类型为VARCHAR(100)

ALTER TABLE employees ADD favoriate_activity VARCHAR(100); 

• 将表employees名称修改为employees_info

ALTER TABLE employees RENAME employees_info;