FreeKB - mySQL / MariaDB Create a new table
mySQL / MariaDB - Create a new table

To create a MySQL table, first you 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.

Use the show databases command to view the databases you have already created.

show databases;

 

Something like this should be returned.

+--------------------+
| Database           |
+--------------------+
| information_schema |
| db001              |
| mysql              |
| performance_schema |
+--------------------+

 

Enter the database you wish to use.  In this example, the database named db001 will be used.

use db001;

 

Show the tables in the database.

show tables;

 

Something like this should be returned.

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

 

Create a new table. 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 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;

 

Describe the table.

describe 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 ff496 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   |