mysql Various operations of the data table in the table , Create table 、 Add various constraints 、 View table structure 、 Modify and delete tables . This time I'll show you how , Walk again from the beginning .

The experiment purpose

establish 、 Modify and delete tables , Master the basic operation of data table .

experimental result

Create database company, Follow the following two table structures in company Create two data tables in the database offices and employees.

surface 1（offices）：

surface 2（employees）：

Experimental process

1、 Log in to the database

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、 Create database

Create database company：

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

mysql>

3、 Access to database

Switch to our newly created database ;

mysql> use company;
Database changed
mysql>

4、 Create table 1

Create data table 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、 Create table 2

Create database 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 Field movement

Put the data sheet employees Of mobile Modify the field to officeCode The back of the field ;

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 Field modification

Put the data sheet employees Of birth The field is renamed 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 Modify field type

Put the data sheet sex The data type of the field , Change to char(1), Non empty constraint ;

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 Delete field

Delete data table employees Medium note Field ;

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 Add fields

In the data table employees New field in favoriate_activity, And set the data type to 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 Delete table

Delete data table offices;

Note the data sheet offices There are foreign keys in , So when we delete the data table , You need to delete the foreign key relationship first , Then delete this data table .

（1）mysql Delete foreign key

First look at the name of the foreign key ;

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>

You can see that the foreign key name is office_fk, Then we delete employees Foreign key constraint of data table 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>

You can see employees The foreign key in the data table has been deleted , Then we go back and delete the data table offices;

（2）mysql Delete data table

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>

After we delete the data table, there is really only one table in the query .

12、mysql Modify the storage engine

take employees The storage engine of the data table is modified to myisam;

（1） Look at the data sheet employees What is the current storage engine ;

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） Modify the storage engine of the data table ;

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

mysql>

（3） Check the storage engine of the data table again ;

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 Modify the name of the table

Put the data sheet employees The data table name of is modified to 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>

thus , In this paper, the end .

For more information, go to VX official account “ Operation and maintenance home ” , Get the latest article .

------ “ Operation and maintenance home ” ------

------ “ Operation and maintenance home ” ------

------ “ Operation and maintenance home ” ------

Interview with system operation and maintenance engineer , Nominations for excellent staff of operation and maintenance engineer ,tr O & M Engineer , I'm calling the operation and maintenance engineer for daily work ,IT Senior operation and maintenance engineer ;
Intelligent manufacturing operation and maintenance engineer training course , Remote office operation and maintenance engineer , Salary of Mindray medical operation and maintenance engineer , What does the backstage O & M engineer do ;
How about the wind operation and maintenance engineer , Inspur cloud operation and maintenance engineer , Sample certificate of medical equipment operation and maintenance engineer , Boyfriend of operation and maintenance engineer , The O & M engineer is grumpy .