mysql中数据表的基本操作很难嘛，由这个实验来带你从头走一遍

mysql表中数据表的各种操作，创建表、添加各类约束、查看表结构、修改和删除表。这次带你捋清楚，从头再走一遍。

实验目的

创建、修改和删除表，掌握数据表的基本操作。

实验结果

创建数据库​​company​​​，按照以下两个表结构在​​company​​​数据库中创建两个数据表​​offices​​​和​​employees​​。

表1（offices）：

表2（employees）：

实验过程

1、登录数据库

PS C:\Users\22768> mysql -uroot -p
Enter password: *************
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.29 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

2、创建数据库

创建数据库​​company​​：

mysql> create database company;
Query OK, 1 row affected (0.01 sec)

mysql>

3、进入数据库

切换到我们新创建的数据库中；

mysql> use company;
Database changed
mysql>

4、创建表一

创建数据表​​offices​​；

mysql> create table offices (officeCode int not null unique, city varchar(50) not null, address varchar(50) not null, country varchar(50) not null, postalCode varchar(15) not null, primary key (officeCode));
Query OK, 0 rows affected (0.06 sec)

mysql> desc offices;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| officeCode | int | NO | PRI | NULL | |
| city | varchar(50) | NO | | NULL | |
| address | varchar(50) | NO | | NULL | |
| country | varchar(50) | NO | | NULL | |
| postalCode | varchar(15) | NO | | NULL | |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql>

5、创建表二

创建数据库​​employees​​；

mysql> create table employees (employeeNumber int not null primary key auto_increment, lastname varchar(50) not null, firstname varchar(50) not null, mobile varchar(25) not null, officeCode int not null, jobTitle varchar(50) not null, birth datetime, note varchar(255), sex varchar(5), constraint office_fk foreign key(officeCode) references offices(officeCode));
Query OK, 0 rows affected (0.05 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) | NO | | NULL | |
| officeCode | int | NO | MUL | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| birth | datetime | YES | | NULL | |
| note | varchar(255) | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

mysql>

6、mysql字段移动

将数据表​​employees​​​的​​mobile​​​字段修改到​​officeCode​​字段后面；

mysql> alter table employees modify mobile varchar(25) after officeCode;
Query OK, 0 rows affected (0.07 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 | | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| birth | datetime | YES | | NULL | |
| note | varchar(255) | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

mysql>

7、mysql字段修改

将数据表​​employees​​​的​​birth​​​字段改名为​​employee_birth​​；

mysql> alter table employees change birth employee_birth datetime;
Query OK, 0 rows affected (0.03 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 | | 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)

mysql>

8、mysql修改字段类型

将数据表​​sex​​​字段的数据类型，改成​​char(1)​​，非空约束；

mysql> alter table employees modify sex char(1) not null;
Query OK, 0 rows affected (0.08 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 | | 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)

mysql>

9、mysql删除字段

删除数据表​​employees​​​中的​​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 | | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| employee_birth | datetime | YES | | NULL | |
| sex | char(1) | NO | | NULL | |
+----------------+-------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

mysql>

10、mysql增加字段

在数据表​​employees​​​中新增字段​​favoriate_activity​​​，并设置数据类型为​​varchar(100)​​；

mysql> alter table employees add favoriate_activiry varchar(100);
Query OK, 0 rows affected (0.03 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 | | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| employee_birth | datetime | YES | | NULL | |
| sex | char(1) | NO | | NULL | |
| favoriate_activiry | varchar(100) | YES | | NULL | |
+--------------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

mysql>

11、mysql删除表

删除数据表​​offices​​；

需要注意的是数据表​​offices​​中存在着外键，所以我们删除该数据表的时候，需要先删除外键关系，然后再删除这个数据表方可。

（1）mysql删除外键

先看下外键的名字叫啥；

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,
`favoriate_activiry` varchar(100) DEFAULT NULL,
PRIMARY KEY (`employeeNumber`),
KEY `office_fk` (`officeCode`),
CONSTRAINT `office_fk` FOREIGN KEY (`officeCode`) REFERENCES `offices` (`officeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

可以看到外键名字是​​office_fk​​​，然后我们删除​​employees​​​数据表的外键约束​​office_fk​​；

mysql> alter table employees drop foreign key office_fk;
Query OK, 0 rows affected (0.02 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,
`favoriate_activiry` varchar(100) DEFAULT NULL,
PRIMARY KEY (`employeeNumber`),
KEY `office_fk` (`officeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

可以看到​​employees​​​数据表中的外键已经删除了，然后我们再返回来删除数据表​​offices​​；

（2）mysql删除数据表

mysql> drop table offices;
Query OK, 0 rows affected (0.03 sec)

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

mysql>

我们删除了数据表之后查询确实只有一个表了。

12、mysql修改存储引擎

将​​employees​​​数据表的存储引擎修改为​​myisam​​；

（1）查看数据表​​employees​​现在的存储引擎是什么；

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,
`favoriate_activiry` varchar(100) DEFAULT NULL,
PRIMARY KEY (`employeeNumber`),
KEY `office_fk` (`officeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

（2）修改数据表的存储引擎；

mysql> alter table employees engine=myisam;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>

（3）再次查看数据表的存储引擎；

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,
`favoriate_activiry` varchar(100) DEFAULT NULL,
PRIMARY KEY (`employeeNumber`),
KEY `office_fk` (`officeCode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

13、mysql修改表名

将数据表​​employees​​​的数据表名修改成​​employees_info​​；

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)

mysql>

至此，本文结束。 

更多内容请转至VX公众号 “运维家” ，获取最新文章。

------ “运维家”  ------

------ “运维家”  ------

------ “运维家”  ------

系统运维工程师面试，运维工程师优秀员工提名词，tr运维工程师，特来电运维工程师工作日常，IT运维工程师高级；

智能制造运维工程师培训课程，远程办公的运维工程师，迈瑞医疗运维工程师工资待遇，后台运维工程师是做什么的；

风力运维工程师怎样，浪潮云运维工程师，医疗设备运维工程师证书样本，运维工程师男朋友，运维工程师暴躁。