How to create table like another table with additional columns in MYSQL

Question

I have 'Name, Age, Gender' columns in a 'test' table.

I now, want to create another table 'test1' which will have these columns : 'Name, Age, Gender, Occupation'.

How do I do it without creating and then altering? Is there any way to do it in a single line?

I tried adding one column, but I was able to add it at the start only, like: CREATE TABLE test1 (Occupation VARCHAR(50)) LIKE test;

This would result in 'Occupation, Name, Age, Gender'.

I tried CREATE TABLE test1 LIKE test (Occupation VARCHAR(50)) which gave me an error.

How to add columns at last while creation?

Answer 1

Though you have obligation regarding adding the column at the begining, I am sharing here two approaches with which you can add a column along with the constraint while creating new table from old one.

 CREATE TABLE test (
     name varchar(100),
     age int,
     gender char(1)
 );

 INSERT INTO test values('name1',30,'M');

 INSERT INTO test values('name2',50,'F');

Create table statement from old table:

 CREATE TABLE test1 (id INT NOT NULL AUTO_INCREMENT,
             PRIMARY KEY (id))
             SELECT *  FROM test;

Select query on old table:

 SELECT* FROM test;

Output:

name	age	gender
name1	30	M
name2	50	F

Slect query on new table:

 select * from test1

OUtput:

id	name	age	gender
1	name1	30	M
2	name2	50	F

Another approach is to create a new table with all the column names from old table with the new column at the end

 CREATE TABLE test2 (
     name varchar(100),
     age int,
     gender char(1),
     id int not null auto_increment,
     primary key(id)
 );

Then insert the data from old table to new table with a single insert statement:

 insert into test2 (name,age,gender) select name, age, gender from test

Select query on new table:

 select * from test2 

Output:

name	age	gender	id
name1	30	M	1
name2	50	F	2

db<>fiddle here