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

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?

CodePudding user response：

You may use CREATE TABLE AS ... with a select containing the new column:

CREATE TABLE test1 AS
SELECT Name, Age, Gender, '' AS Occupation
FROM test;

The above simply places empty string as the value for the new occupation column. If you want some other value, then you would need to provide logic for how to populate it.

CodePudding user response：

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