mysql中数据表的基本操作很难嘛，由这个实验来带你从头走一遍
本文共 13159 字，大约阅读时间需要 43 分钟。
mysql表中数据表的各种操作，创建表、添加各类约束、查看表结构、修改和删除表。这次带你捋清楚，从头再走一遍。
实验目的
创建、修改和删除表，掌握数据表的基本操作。
实验结果
创建数据库company，按照以下两个表结构在company数据库中创建两个数据表offices和employees。
表1（offices）：
表2（employees）：
实验过程
1、登录数据库
PS C:\Users\22768> mysql -uroot -pEnter password: *************Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 9Server version: 8.0.29 MySQL Community Server - GPLCopyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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 changedmysql>
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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: employeesCreate 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_ci1 row in set (0.00 sec)ERROR:No query specifiedmysql>
可以看到外键名字是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: 0mysql> show create table employees\G;*************************** 1. row *************************** Table: employeesCreate 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_ci1 row in set (0.00 sec)ERROR:No query specifiedmysql>
可以看到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: employeesCreate 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_ci1 row in set (0.00 sec)ERROR:No query specifiedmysql>
（2）修改数据表的存储引擎；
mysql> alter table employees engine=myisam;Query OK, 0 rows affected (0.06 sec)Records: 0 Duplicates: 0 Warnings: 0mysql>
（3）再次查看数据表的存储引擎；
mysql> show create table employees\G;*************************** 1. row *************************** Table: employeesCreate 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_ci1 row in set (0.00 sec)ERROR:No query specifiedmysql>
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运维工程师高级；
智能制造运维工程师培训课程，远程办公的运维工程师，迈瑞医疗运维工程师工资待遇，后台运维工程师是做什么的； 风力运维工程师怎样，浪潮云运维工程师，医疗设备运维工程师证书样本，运维工程师男朋友，运维工程师暴躁。转载地址：http://zvbfk.baihongyu.com/
你可能感兴趣的文章
MySQL 的存储引擎有哪些？为什么常用InnoDB？
查看>>
Mysql 知识回顾总结-索引
查看>>
Mysql 笔记
查看>>
MySQL 精选 60 道面试题（含答案）
查看>>
mysql 索引
查看>>
MySQL 索引失效的 15 种场景！
查看>>
MySQL 索引深入解析及优化策略
查看>>
MySQL 索引的面试题总结
查看>>
mysql 索引类型以及创建
查看>>
MySQL 索引连环问题，你能答对几个？
查看>>
Mysql 索引问题集锦
查看>>
Mysql 纵表转换为横表
查看>>
mysql 编译安装 window篇
查看>>
mysql 网络目录_联机目录数据库
查看>>
MySQL 聚簇索引&&二级索引&&辅助索引
查看>>
Mysql 脏页 脏读 脏数据
查看>>
mysql 自增id和UUID做主键性能分析，及最优方案
查看>>
Mysql 自定义函数
查看>>
mysql 行转列 列转行
查看>>
Mysql 表分区
查看>>