FreeKB - mySQL / MariaDB insert a value into a column
mySQL / MariaDB - insert a value into a column

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 Insert_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;

 

The following syntax is used to insert data into a table.

insert into tablename
->(columnname, columnname, columnname)
->values
->("data", "data", "data");
Query OK, 1 row affected, 0 warnings (0.00 sec)

 

To verify that the data was successfully inserted, enter this command:

select * from tablename;

If we have a large amount of data, entering the data one by one in the Terminal is impractical to impossible. We can enter the data into a TXT file, and then import the TXT file into MySQL. The data should be separated by tab. For cells that should have no data, use \ in the TXT file. For example, let's say the table in our database has these columns:

  • site
  • username
  • password
  • note

In our TXT file, we could have something like this:

Google     jeremy@gmail.com     supersecretpassword     youtube 

Facebook     jeremy@gmail.com     supersecretpassword     \

Twitter     JeremyC     supersecretpassword     \

 

Let's say we save this TXT file at /home/usernames.txt. Sign into your MySQL server.  Then, enter the table you want to load the data into.  Then, use the load data command to insert the data from the TXT file into MySQL.

use tablename;
load data infile '/home/usernames.txt' into table tablename;

 



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 6a242 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   |