数据表的基本操作（实例）

数据表的基本操作

12个步骤

# 步骤一：创建数据库 company
mysql> create database company;
Query OK, 1 row affected (0.00 sec)

mysql> use company
Database changed
mysql> show tables;
Empty set (0.00 sec)


# 步骤二：创建数据表 offices
mysql> drop table if exists offices;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `offices` (
    ->   `officeCode` int(11) NOT NULL unique primary key,
    ->   `city` varchar(50) NOT NULL,
    ->   `address` varchar(50),
    ->   `country` varchar(50) NOT NULL,
    ->   `postalCode` varchar(15)
    -> );
Query OK, 0 rows affected, 1 warning (0.01 sec)

# 步骤三：创建数据表 employees
mysql> drop table if exists employees;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TABLE `employees` (
    ->   `employeeNumber` int(11) NOT NULL unique primary key auto_increment,
    ->   `lastName` varchar(50) NOT NULL,
    ->   `firstName` varchar(50) NOT NULL,
    ->     `mobile` varchar(25) unique,
    ->       `officeCode` int(11) not null,
    ->   `jobTitle` varchar(50) not null,
    ->     `birth` datetime not null,
    ->   `note` varchar(255) ,
    ->   `sex` varchar(5),
    ->    CONSTRAINT `fk_offices` FOREIGN KEY (`officeCode`) REFERENCES `offices` (`officeCode`)
    -> );
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> desc employees
    -> ;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employeeNumber | int          | NO   | PRI | NULL    | auto_increment |
| lastName       | varchar(50)  | NO   |     | NULL    |                |
| firstName      | varchar(50)  | NO   |     | NULL    |                |
| mobile         | varchar(25)  | YES  | UNI | NULL    |                |
| officeCode     | int          | NO   | MUL | NULL    |                |
| jobTitle       | varchar(50)  | NO   |     | NULL    |                |
| birth          | datetime     | NO   |     | NULL    |                |
| note           | varchar(255) | YES  |     | NULL    |                |
| sex            | varchar(5)   | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)


mysql> show create table employees \G;
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `employeeNumber` int NOT NULL AUTO_INCREMENT,
  `lastName` varchar(50) NOT NULL,
  `firstName` varchar(50) NOT NULL,
  `mobile` varchar(25) DEFAULT NULL,
  `officeCode` int NOT NULL,
  `jobTitle` varchar(50) NOT NULL,
  `birth` datetime NOT NULL,
  `note` varchar(255) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`employeeNumber`),
  UNIQUE KEY `employeeNumber` (`employeeNumber`),
  UNIQUE KEY `mobile` (`mobile`),
  KEY `fk_offices` (`officeCode`),
  CONSTRAINT `fk_offices` FOREIGN KEY (`officeCode`) REFERENCES `offices` (`officeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

ERROR:
No query specified

mysql> show create table offices \G;
*************************** 1. row ***************************
       Table: offices
Create Table: CREATE TABLE `offices` (
  `officeCode` int NOT NULL,
  `city` varchar(50) NOT NULL,
  `address` varchar(50) DEFAULT NULL,
  `country` varchar(50) NOT NULL,
  `postalCode` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`officeCode`),
  UNIQUE KEY `officeCode` (`officeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> desc employees;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employeeNumber | int          | NO   | PRI | NULL    | auto_increment |
| lastName       | varchar(50)  | NO   |     | NULL    |                |
| firstName      | varchar(50)  | NO   |     | NULL    |                |
| mobile         | varchar(25)  | YES  | UNI | NULL    |                |
| officeCode     | int          | NO   | MUL | NULL    |                |
| jobTitle       | varchar(50)  | NO   |     | NULL    |                |
| birth          | datetime     | NO   |     | NULL    |                |
| note           | varchar(255) | YES  |     | NULL    |                |
| sex            | varchar(5)   | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

# 步骤四：将表employees 的mobile 字段移到officeCode 后面
mysql> alter table employees modify mobile varchar(25) after officeCode;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employees;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employeeNumber | int          | NO   | PRI | NULL    | auto_increment |
| lastName       | varchar(50)  | NO   |     | NULL    |                |
| firstName      | varchar(50)  | NO   |     | NULL    |                |
| officeCode     | int          | NO   | MUL | NULL    |                |
| mobile         | varchar(25)  | YES  | UNI | NULL    |                |
| jobTitle       | varchar(50)  | NO   |     | NULL    |                |
| birth          | datetime     | NO   |     | NULL    |                |
| note           | varchar(255) | YES  |     | NULL    |                |
| sex            | varchar(5)   | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

# 步骤五：修改字段名
mysql> alter table employees change birth employee_birth datetime;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employees;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employeeNumber | int          | NO   | PRI | NULL    | auto_increment |
| lastName       | varchar(50)  | NO   |     | NULL    |                |
| firstName      | varchar(50)  | NO   |     | NULL    |                |
| officeCode     | int          | NO   | MUL | NULL    |                |
| mobile         | varchar(25)  | YES  | UNI | NULL    |                |
| jobTitle       | varchar(50)  | NO   |     | NULL    |                |
| employee_birth | datetime     | YES  |     | NULL    |                |
| note           | varchar(255) | YES  |     | NULL    |                |
| sex            | varchar(5)   | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

# 步骤六：修改sex 数据类型
mysql> alter table employees modify sex char(1) not null;;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

ERROR:
No query specified

mysql> desc employees;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employeeNumber | int          | NO   | PRI | NULL    | auto_increment |
| lastName       | varchar(50)  | NO   |     | NULL    |                |
| firstName      | varchar(50)  | NO   |     | NULL    |                |
| officeCode     | int          | NO   | MUL | NULL    |                |
| mobile         | varchar(25)  | YES  | UNI | NULL    |                |
| jobTitle       | varchar(50)  | NO   |     | NULL    |                |
| employee_birth | datetime     | YES  |     | NULL    |                |
| note           | varchar(255) | YES  |     | NULL    |                |
| sex            | char(1)      | NO   |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

# 步骤七：删除字段 note
mysql> alter table employees drop note;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employees;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| employeeNumber | int         | NO   | PRI | NULL    | auto_increment |
| lastName       | varchar(50) | NO   |     | NULL    |                |
| firstName      | varchar(50) | NO   |     | NULL    |                |
| officeCode     | int         | NO   | MUL | NULL    |                |
| mobile         | varchar(25) | YES  | UNI | NULL    |                |
| jobTitle       | varchar(50) | NO   |     | NULL    |                |
| employee_birth | datetime    | YES  |     | NULL    |                |
| sex            | char(1)     | NO   |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

# 步骤八：新增字段
mysql> alter table employees add favorite_activiy varchar(100);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employees;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| employeeNumber   | int          | NO   | PRI | NULL    | auto_increment |
| lastName         | varchar(50)  | NO   |     | NULL    |                |
| firstName        | varchar(50)  | NO   |     | NULL    |                |
| officeCode       | int          | NO   | MUL | NULL    |                |
| mobile           | varchar(25)  | YES  | UNI | NULL    |                |
| jobTitle         | varchar(50)  | NO   |     | NULL    |                |
| employee_birth   | datetime     | YES  |     | NULL    |                |
| sex              | char(1)      | NO   |     | NULL    |                |
| favorite_activiy | varchar(100) | YES  |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

# 步骤九：移除 employees 表的外键约束
mysql> alter table employees drop foreign key fk_offices;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 步骤十：删除offices表
mysql> drop table offices;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employees         |
+-------------------+
1 row in set (0.00 sec)

# 步骤十一：修改employees表 存储引擎
mysql> alter table employees engine=myisam
    -> ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table employees \G;
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `employeeNumber` int NOT NULL AUTO_INCREMENT,
  `lastName` varchar(50) NOT NULL,
  `firstName` varchar(50) NOT NULL,
  `officeCode` int NOT NULL,
  `mobile` varchar(25) DEFAULT NULL,
  `jobTitle` varchar(50) NOT NULL,
  `employee_birth` datetime DEFAULT NULL,
  `sex` char(1) NOT NULL,
  `favorite_activiy` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`employeeNumber`),
  UNIQUE KEY `employeeNumber` (`employeeNumber`),
  UNIQUE KEY `mobile` (`mobile`),
  KEY `fk_offices` (`officeCode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

# 步骤十二：改表名字
mysql> alter table employees rename employees_info;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employees_info    |
+-------------------+
1 row in set (0.00 sec)