mySQL / MariaDB - Create a new table

The create <table name> command can be used to create a new table in a database. You will first need to log into your MariaDB or mySQL server or configure passwordless authentication and then use the -e command line option and the user must have the Create_priv.

You will probably want to first list the tables in the database. One option is to first use the use <database name> command and then issue the show tables command. In this example, the database named db001 will be used. 

use db001; show tables;

 

Or like this.

show db001.tables;

 

Something like this should be returned.

+------------------+
| Tables_in_db001  |
+------------------+
| table001         |
| table002         |
+------------------+

 

In this example, table003 is created. The reason we set some of the columns to not null is so that the text NULL gets entered into the table if no data is provided.

create table db001.table003 (
  id INT(1) not null primary key auto_increment,
  last_updated datetime not null,
  column001 varchar(50) not null,
  column002 varchar(2000) not null
);

 

Another options is to inport to .sql file which has the create table commands. As an example, the following markup could be saved to a file name test.sql. You would replace database_name, table_name, and column_name with the names you wish to use. 

use database_name;
create table `table_name` (`column_name` varchar(123)) engine=InnoDB default charset=latin1;
lock tables `table_name` write;
insert into `table_name` values ('test data 1', 'test data 2', 'test data 3', 'test data 4');
unlock tables;

 

To import this file, use the following command:

mysql -uusername -ppassword > /path/to/text.sql;

 

The show columns or describe <tablename> commands can be used to list the columns in the table.

describe db001.table003;

 

Something like this should be returned.

+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int(11)       | NO   |     | NULL    |       |
| column001 | varchar(50)   | NO   |     | NULL    |       |
| column002 | varchar(2000) | NO   |     | NULL    |       |
| updated   | datetime      | NO   |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+

 



Add a Comment




We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.





Please enter 2ce41 in the box below so that we can be sure you are a human.




Comments

Web design by yours truely - me, myself, and I   |   jeremy.canfield@freekb.net   |