How to create a MySQL table

Home > Search > How-to
  by

To create a MySQL table, first you need to log into your MySQL server.  Next, you need to enter the database you want to use.  Use the show databases command to view the databases you have already established.

show databases;

 

You need to enter the database you wish to use.  If the database you wish to use already exists, use the use database command.  If the database you wish to use does not exist, use the create database command.

use database databasename;
create database databasename;

 

Then, the following commands can be issued to create a new table.

create table tablename (
  id INT(1) not null primary key auto_increment,
  last_updated DATE,
  columnname VARCHAR(50) not null,
  columnname VARCHAR(50) not null
);

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.  

 

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;

 



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 in the box below so that we can be sure you are a human.




Comments