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

View the frequently used SQL statements in MySQL (detailed explanation)

#mysql -uroot -p Enter password mysql> show fu...

Detailed explanation of setting up DNS server in Linux

1. DNS server concept Communication on the Intern...

Detailed configuration of mysql8.x docker remote access

Table of contents Environmental conditions Errors...

How to check if data exists before inserting in mysql

Business scenario: The visitor's visit status...

Summary of MySQL basic common commands

Table of contents MySQL basic common commands 1. ...

Example code of vue icon selector

Source: http://www.ruoyi.vip/ import Vue from ...

N ways to achieve two-column layout with CSS

1. What is a two-column layout? There are two typ...

Example analysis of the use of GROUP_CONCAT in MySQL

This article uses an example to describe how to u...

Node.js solves the problem of Chinese garbled characters in client request data

Node.js solves the problem of Chinese garbled cha...

How to build a tomcat image based on Dockerfile

Dockerfile is a file used to build a docker image...

Tutorial on using portainer to connect to remote docker

Portainer is a lightweight docker environment man...