MySQL Learning 11: Modifying Data tables (i)

Modifying a data table includes adding columns, deleting columns, adding constraints, deleting constraints, modifying column definitions, and modifying data table names, and the following two when we use

Be cautious and try not to use it.

Here is a separate description of how to modify the data table:

to add a column in a data table (1) Add a single column

The syntax format for adding a single column to the data table of the MySQL database is:

ALTER TABLE table_name ADD [COLUMN] col_name columns_definition [First | After Col_name];

Example:

SHOW COLUMNS from Users1;

ALTER TABLE users1 ADD age TINYINT UNSIGNED not NULL DEFAULT ten;

SHOW COLUMNS from Users1;

Add a password field and put the column after the username field:

ALTER TABLE users1 ADD password VARCHAR (+) not NULL after username;

SHOW COLUMNS from Users1;

Add a truename field and place the column in the first column (that is, put it in front of all the fields):

ALTER TABLE users1 ADD truename VARCHAR (a) not NULL first;

SHOW COLUMNS from Users1;

(2) Adding multiple columns

The syntax format for adding multiple columns to the data table of a MySQL database is:

ALTER TABLE table_name ADD [COLUMN] (col_name columns_definition,...);

Example: Here we just add two columns

ALTER TABLE users1 ADD (password VARCHAR (+) not NULL after username, ageTINYINT UNSIGNED /c1>

Not NULL DEFAULT ten);

DESC users1;

Unlike adding a single column, adding multiple columns can only add multiple columns after all columns.

Two Delete a column from a data table (1) Delete a single column

The data table in the MySQL database deletes a single-column syntax format:

ALTER TABLE table_name DROP [COLUMN] col_name;

Example

ALTER TABLE users1 DROP truename;

SHOW COLUMNS from Users1;

(2) Delete multiple columns

The MySQL database data table removes the syntax format for multiple columns:

ALTER TABLE table_name DROP [column] col_name1,drop [column] col_name2,...;

Example: Here just delete two columns

ALTER TABLE users1 DROP password,drop age;

SHOW COLUMNS from Users1;

Three add constraints (1) Adding a PRIMARY KEY constraint

The syntax format for adding a PRIMARY key constraint to a data table in a MySQL database is:

ALTER TABLE table_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [Index_type] (Index_col_name,...);

First, create a data table users2:

CREATE TABLE users2 (

Username VARCHAR (Ten) is not NULL,

PID SMALLINT UNSIGNED

);

SHOW CREATE TABLE users2;

The above indicates that there is no primary KEY constraint in the data table.

Example of adding a PRIMARY KEY constraint:

1) First add a column to be the primary key:

ALTER TABLE users2 ADD ID SMALLINT UNSIGNED;

SHOW COLUMNS from Users2;

2) Add a PRIMARY KEY constraint on the column to be the primary key: ALTER TABLE users2 ADD CONSTRAINT pk_users2_id PRIMARY KEY (ID);

SHOW COLUMNS from Users2;

In the example above we can also not add the keyword constraint, plus it we can have a name for this PRIMARY KEY constraint, we have

The primary key name is pk_users2_id, in order to facilitate the operation of the primary key, we'd better give a name to the primary key.

(2) Adding a unique constraint

Add a UNIQUE constraint syntax format to the data table in the MySQL database:

ALTER TABLE table_name ADD [CONSTRAINT [symbol]] UNIQUE [index| KEY] [index_name] [Index_type]

(Index_col_name,...);

Example:

ALTER TABLE users2 ADD UNIQUE (username);

SHOW COLUMNS from Users2;

MySQL Learning 11: Modifying Data tables (i)