当前位置：网站首页>mysql中数据表的基本操作很难嘛，由这个实验来带你从头走一遍
mysql中数据表的基本操作很难嘛，由这个实验来带你从头走一遍
2022-07-19 20:15:00 【运维家】
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运维工程师高级；
智能制造运维工程师培训课程，远程办公的运维工程师，迈瑞医疗运维工程师工资待遇，后台运维工程师是做什么的；
风力运维工程师怎样，浪潮云运维工程师，医疗设备运维工程师证书样本，运维工程师男朋友，运维工程师暴躁。
边栏推荐
- Dest0g3 520迎新赛-web-funny_upload
- 【云图说】第248期 图解公网域名解析：轻松实现域名访问网站/邮箱
- 实现统一账号登录，sonarqube集成ldap
- 如何快速上手find，及xargs命令
- 最受IT公司欢迎的 30 款开源软件
- 发布两年仍未上市，苹果AirPower被迫取消！原因揭秘！
- Redis 高可用篇：你管这叫主从架构数据同步原理？
- CSAPP：cap2
- 金仓数据库 KingbaseES SQL 语言参考手册 (3.8. 数据库对象、3.9. 数据库对象名称和限定符)
- College student party building website system based on SSH
猜你喜欢
Vmware解决无法识别USB的问题
服务器内网与外网的四大区别？
Given the preorder traversal and the inorder traversal order of a binary tree, find the postorder traversal of the tree
DNS principle and configuration
Redis 实战篇：巧用数据类型实现亿级数据统计
robotframework实战（三）衍生——以百度搜索功能为例编写测试用例
每日牛客刷题之链表
JSON basic use
Link list of daily Niuke questions
Microblogging system based on BS architecture
随机推荐
How can Web3 enterprises use tokens to motivate employees?
CSAPP：cap2
MetInfo 函数public function getcity() 错误：xxx function no permission load!!!
LVGL 8.2 Spinbox
01-创建项目仓库
Project knowledge points
Code source du système vidéo court, séquence de chargement des fichiers principaux dans le projet uni app
源码 | OpenCV DNN + YOLOv7目标检测
最受IT公司欢迎的 30 款开源软件
stm32移植RT-Thread Nano实现finsh全步骤
山西省第二届网络安全技能大赛（企业组）部分赛题WP（六）
College student party building website system based on SSH
【历史上的今天】7 月 19 日：IMAP 协议之父出生；Project Kotlin 公开亮相；CT 成像实现新突破
【每日一题】731. 我的日程安排表Ⅱ
Redis 实战篇：巧用 Bitmap 实现亿级海量数据统计
Solution to the first game of 2022 Hangzhou Electric Multi school league
Microblogging system based on BS architecture
SAP mm transaction code Migo mobile type 561 error after saving -document number was already assigned
R语言使用ggpubr包的gghistogram函数可视化分组箱图、添加分组均值、自定义分组色彩、添加轴须图(rug)、添加密度曲线、添加双y轴分别表示频率以及密度曲线的密度值
Redis practice: skillfully use data types to achieve 100 million level data statistics