MySQL 11: modifying data tables (1)-mysql tutorial

Modifying a data table includes adding a column, deleting a column, adding a constraint, deleting a constraint, modifying a column definition, and modifying a data table name. you must be cautious when using the following two data tables. The following describes how to modify A data table separately: 1. add A column in the data table (1) add A single column to the MySQL database. The syntax for adding A single column to the data table is as follows:

Modifying a data table includes adding a column, deleting a column, adding a constraint, deleting a constraint, modifying a column definition, and modifying a data table name. you must be cautious when using the following two data tables. The following describes how to modify A data table separately: 1. add A column in the data table (1) add A single column to the MySQL database. The syntax for adding A single column to the data table is as follows:

Modifying a data table includes adding columns, deleting columns, adding constraints, deleting constraints, modifying column definitions, and modifying table names.

Be cautious and try not to use it.

The following describes how to modify a data table one by one:

1. add a single column to the column (1) in the data table

The syntax format for adding a single column to a data table in 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 10;

Show columns from users1;

Add a password field and put this column behind the username field:

Alter table users1 ADD password VARCHAR (32) not null after username;

Show columns from users1;

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

Alter table users1 ADD truename VARCHAR (20) not null first;

Show columns from users1;

(2) add multiple columns

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

Alter table table_name ADD [COLUMN] (col_name columns_definition ,...);

Example: here we only add two columns

Alter table users1 ADD (password VARCHAR (32) not null after username, age TINYINT UNSIGNED

Not null default 10 );

DESC users1;

Different from adding a single column, you can only add multiple columns after all columns.

2. delete columns in a data table (1) delete a single column

Syntax format for deleting a single column in a data table in the MySQL database:

Alter table table_name DROP [COLUMN] col_name;

Example;

Alter table users1 DROP truename;

Show columns from users1;

(2) delete multiple columns

Syntax format for deleting multiple columns in a MySQL database data table:

Alter table table_name DROP [COLUMN] col_name1, DROP [COLUMN] col_name2 ,...;

Example: only two columns are deleted.

Alter table users1 DROP password, DROP age;

Show columns from users1;

3. add a constraint (1) add a primary key constraint

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

Alter table table_name ADD [CONSTRAINT [symbol] primary key [index_type] (index_col_name ,...);

Create a data table users2:

Create table users2 (

Username VARCHAR (10) 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 used as the primary key:

Alter table users2 ADD id smallint unsigned;

Show columns from users2;

2) ADD the primary key constraint to the column to be used as the primary key: alter table users2 add constraint PK_users2_id primary key (id );

Show columns from users2;

In the above example, we can also add the CONSTRAINT keyword, and we can name the primary key CONSTRAINT.

The primary key name is PK_users2_id. to facilitate the operation of the primary key in the future, we 'd better name the primary key.

(2) add a unique constraint

Syntax format for adding a unique constraint to a 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;