Mysql创建管理表的小练习

#练习

#创建数据库test03_company

CREATE DATABASE IF NOT EXISTS test03_company;

#创建表office

CREATE TABLE IF NOT EXISTS offices(

officeCode INT,

city VARCHAR(30),

address VARCHAR(50),

country VARCHAR(50),

postalCode VARCHAR(25)

);

DESC offices;

#创建表employees

CREATE TABLE IF NOT EXISTS employees(

empNum INT,

lastName VARCHAR(50),

firstName VARCHAR(50),  

mobile VARCHAR(50),

`code` INT,

jobTitle VARCHAR(50),

birth DATE,

note VARCHAR(255),

sex VARCHAR(5)

);

DESC empl oyees;  

#将表employees的mobile字段修改到code字段后

ALTER TABLE employees MODIFY mobile VARCHAR(50)

             AFTER  `code` ;

#将表employees的birth字段改名为birthday

#错误的：

ALTER TABLE employees MODIFY birth DATE RENAME TO birthday DATE;

#正确的

ALTER TABLE employees  

CHANGE birth birthday DATE;

#修改sex字段数据类型为char(1)

ALTER TABLE employees MODIFY sex CHAR(1);  

#删除字段note

ALTER TABLE employees DROP COLUMN note;

#增加字段favorite_activity,数据类型为varchar(100)

ALTER TABLE employees ADD favorite_activity VARCHAR(100);

#将表employees的名称修改为employees_info

RENAME TABLE employees TO employees_info;