MySQL基础操作示例

1、创建company数据库，并创建offices和employees表

---

#创建company数据库
CREATE DATABASE company DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
#创建offices表
CREATE TABLE offices(
officeCode INT(10) PRIMARY KEY UNIQUE NOT NULL,
city VARCHAR(50) NOT NULL,
address VARCHAR(50),
country VARCHAR(50) NOT NULL,
postalCode VARCHAR(15) UNIQUE
);
#创建employees表
CREATE TABLE employees(
employeeNumber INT(11) PRIMARY KEY NOT NULL UNIQUE AUTO_INCREMENT,
lastName VARCHAR(50) NOT NULL ,
firstName VARCHAR(50) NOT NULL,
moblie VARCHAR(25) UNIQUE,
officeCode INT(10) NOT NULL,
jobTitle VARCHAR(50) NOT NULL,
birth DATETIME NOT NULL,
note VARCHAR(255),
sex VARCHAR(5),
);
#添加外键约束
ALTER TABLE employees ADD CONSTRAINT fk_employees_offices FOREIGN KEY(officeCode) REFERENCES offices(officeCode);

2、查看该库下几个表以及查看两张表结构

---

#查看数据库下的表
SHOW TABLES FROM company;
#查看表结构
DESCRIBE offices;
DESC employees;

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

---

ALTER TABLE employees MODIFY COLUMN moblie VARCHAR(25) AFTER officeCode;

4、将表employees的birth字段改名为employee_birth

---

ALTER TABLE employees CHANGE birth employee_birth DATETIME;

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

---

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

6、删除字段note

---

ALTER TABLE employees DROP note;

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

---

ALTER TABLE employees ADD favoriate_activity VARCHAR(100);

8、删除offices

---

#删除表时先要删除该表所建立的外连接，即外键，最后再去删除表
ALTER TABLE employees DROP FOREIGN KEY fk_employees_offices;
DROP TABLE offices;

9、将表employees名称修改为employees_info

---

RENAME TABLE employees TO employees_info;