I have the following table:
create table pessoas(
id tinyint,
nome char,
peso float,
altura float(3),
sexo int(1),
nacionalidade varchar (20)
);
But I want to change all fields at once
alter table pessoas modify nome varchar (20) not null;
alter table pessoas modify peso decimal(5,3);
alter table pessoas modify sexo enum ('M','F');
Finally, can you change to a command only the " modify "?
The SQL standard defines a change only every ALTER TABLE , but MySQL has extended the default to allow this syntax:
ALTER TABLE PESSOAS MODIFY
nome VARCHAR(20) NOT NULL,
peso DECIMAL(5,3),
sexo ENUM('M','F')
;
You can even change the order of the columns, with AFTER or BEFORE , for example:
ALTER TABLE PESSOAS MODIFY
nome VARCHAR(20) NOT NULL,
peso DECIMAL(5,3),
sexo ENUM('M','F') AFTER nome
;
In this last example, in addition to changing the types, the order of the columns will be nome , sexo and peso .
In the manual you have more details:
link
And the section that interests you most is what you asked for here:
Multiple ADD, ALTER, DROP, and CHANGE clauses are permitted in a single ALTER TABLE statement, separated by commas. This is a MySQL extension to standard SQL, which allows only one of each clause per ALTER TABLE statement. For example, to drop multiple columns in a single statement, do this:
ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
What do you mean more or less this:
Multiple ADD, ALTER, DROP, and CHANGE clauses are allowed in a single ALTER TABLE command, separated by commas. This is an extension of MySQL to the SQL standard, which allows only one change per ALTER TABLE statement. For example, to remove multiple columns in a single command, it looks like this:
ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;