How to add, delete and modify columns in MySQL database

How to add, delete and modify columns in MySQL database

This article uses an example to describe how to add, delete, and modify columns in a MySQL database. Share with you for your reference, the details are as follows:

Create a new table user_info:

CREATE TABLE user_info(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
username CHAR(20) NOT NULL DEFAULT '',
gender TINYINT UNSIGNED NOT NULL DEFAULT 0,
weight TINYINT UNSIGNED NOT NULL DEFAULT 0
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

The new column is the last column of the table by default.

Syntax: alter table table name add column name column type column attribute

alter table user_info add height tinyint unsigned not null default 0;

Deleting a column

Syntax: alter table table name drop column name

alter table user_info drop height;

Add a column and put it after the specified column

Syntax: alter table table name add column name type attribute [default value] after specify column name

alter table user_info add height tinyint not null default 0 after username;

Modify the specified column name

Syntax: alter table table name change old column name new column name type attribute default value

alter table user_info change height shengao smallint not null default 0;

modify Modify the column, but cannot modify the column name

Syntax: alter table table name modify column name type attribute default value

alter table user_info modify shengao tinyint not null default 0;

Readers who are interested in more MySQL-related content can check out the following topics: "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in:
  • The difference between MySQL database stored procedures and transactions
  • The meaning and calculation method of QPS and TPS of MySQL database
  • How to backup and restore the mysql database if it is too large
  • PHP backend source code example for backing up MySQL database
  • How to use shell scripts to automatically back up multiple MySQL databases every day
  • Introduction to using mysqli's prepare to operate the database in PHP5
  • MySQL database migration quickly exports and imports large amounts of data
  • Shell script to operate MySQL database to delete duplicate data
  • Combining insert and select to implement the method of "inserting the maximum value of a field in the database + 1"
  • Talk about some experience in database field design

<<:  Implementation process of nginx high availability cluster

>>:  Real-time refresh of long connection on Vue+WebSocket page

Recommend

Summary of the 10 most frequently asked questions in Linux interviews

Preface If you are going to interview for a Linux...

Implementation of nested jump of vue routing view router-view

Table of contents 1. Modify the app.vue page 2. C...

Analysis of the methods of visual structure layout design for children's websites

1. Warm and gentle Related address: http://www.web...

TypeScript decorator definition

Table of contents 1. Concept 1.1 Definition 1.2 D...

dl, dt, dd list label examples

The dd and dt tags are used for lists. We usually...

A detailed explanation of how React Fiber works

Table of contents What is React Fiber? Why React ...

Detailed explanation of when javascript scripts will be executed

JavaScript scripts can be embedded anywhere in HT...

A brief discussion on several advantages of Vue3

Table of contents 1. Source code 1.1 Monorepo 1.2...

Detailed explanation of SSH password-free login configuration under Linux

Assume there are two Linux servers A and B, and w...

Solution to 1045 error in mysql database

How to solve the problem of 1045 when the local d...

Detailed explanation of the usage of image tags in HTML

In HTML, the <img> tag is used to define an...

MySQL-8.0.26 Configuration Graphics Tutorial

Preface: Recently, the company project changed th...

How to view the type of mounted file system in Linux

Preface As you know, Linux supports many file sys...