数据库基本操作练习

建一个company库

CREATE DATABASE company;

建表

CREATE TABLE offices(
    officeCode INT PRIMARY KEY NOT NULL UNIQUE KEY,
    city VARCHAR(50) NOT NULL,
    address VARCHAR(50),
    country VARCHAR(50) NOT NULL,
    postalCode VARCHAR(15) UNIQUE KEY
)ENGINE=INNODB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE employees(
    employeeNumber INT(11) PRIMARY KEY NOT NULL UNIQUE KEY AUTO_INCREMENT,
    lastName VARCHAR(50) NOT NULL,
    firstName VARCHAR(50) NOT NULL,
    mobile VARCHAR(50) UNIQUE KEY,
    officeCode INT(10) NOT NULL,
    jobTitle VARCHAR(50) NOT NULL,
    birth DATETIME NOT NULL,
    note VARCHAR(50),
    sex VARCHAR(5)    
)ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 在两表的基础上，建立officeCode的外键

这里我用的是在外面建立外键

ALTER TABLE employees ADD CONSTRAINT fk_id FOREIGN KEY(officeCode) REFERENCES offices(officeCode);

查看库里表和各表结构

SHOW TABLES FROM company;

DESCRIBE offices;
DESCRIBE employees;

 

 将表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);

删除字段note

ALTER TABLE employees DROP note;

增加字段名favoriate_activity,varchar(100)

删除表offices

由于外键还存在，所以先 删除外键，再删除表。

ALTER TABLE employees DROP FOREIGN KEY fk_id;

DROP TABLE offices;

将表employees名称改为employees_info

 ALTER TABLE employees ADD favoriate_activity VARCHAR(100);