数据库基本操作练习-EW帮帮网

建一个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);

数据库基本操作练习