In this tutorial, we will show you how to insert into and manipulate data in your tables. To find out how to create and manage databases and tables you can check out our previous tutorial: Basic MySQL database administration on a Linux VPS. If your Linux VPS is up and running and has MySQL installed, then you are good to go.
One of the most essential database administration tasks is to insert data into your tables. To do this, you’ll need to log in to your Linux VPS via SSH, log into your MySQL database server as described in the previous tutorial, and use the correct database. To insert data into your tables, you will need to make sure that you are entering all information for its corresponding column. If you have followed our previous tutorial, you created the ‘test_table’ table in your ‘test’ database by using the command:
CREATE TABLE test_table (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(30) NOT NULL, comment VARCHAR (100));
You can enter data using the ‘INSERT INTO’ command. Here are two example entries:
INSERT INTO test_table (name,comment) VALUES ("Jim","Sample comment");
INSERT INTO test_table (name,comment) VALUES ("John","Another comment");
Now, if you execute the command:
SELECT * FROM test_table;
You will see something like this:
mysql> SELECT * FROM test_table; +----+------+-----------------+ | id | name | comment | +----+------+-----------------+ | 1 | Jim | Sample comment | | 2 | John | Another comment | +----+------+-----------------+
In normal circumstances, you will eventually need to update the data entries in your tables. To update entries, you can use the UPDATE and SET commands. Here’s an example:
UPDATE test_table SET comment="New comment" WHERE name="Jim";
To see the result, run:
SELECT * FROM test_table;
This is the result:
mysql> SELECT * FROM test_table; +----+------+-----------------+ | id | name | comment | +----+------+-----------------+ | 1 | Jim | New comment | | 2 | John | Another comment | +----+------+-----------------+
Sometimes you may want to delete a record in your tables. You can do that using the DELETE command:
DELETE FROM test_table WHERE name="John";
Execute this to check the changes:
SELECT * FROM test_table;
mysql> SELECT * FROM test_table; +----+------+-------------+ | id | name | comment | +----+------+-------------+ | 1 | Jim | New comment | +----+------+-------------+
One of the most useful commands in MySQL is ‘ALTER TABLE’. This command can be used to add, modify or delete columns in your tables. Here are some examples:
To add a new column to your ‘test_table’ you can use:
ALTER TABLE test_table ADD age INT;
This is the result:
mysql> SELECT * FROM test_table; +----+------+-------------+------+ | id | name | comment | age | +----+------+-------------+------+ | 1 | Jim | New comment | NULL | +----+------+-------------+------+
The age column for ‘Jim’ is null because no age value has been set.
To delete a column from your table you can use:
ALTER TABLE test_table DROP COLUMN age;
This is the result:
mysql> SELECT * FROM test_table; +----+------+-------------+ | id | name | comment | +----+------+-------------+ | 1 | Jim | New comment | +----+------+-------------+
And if you like to modify a certain column, for example you want to change the data type, you can use:
ALTER TABLE test_table MODIFY COLUMN comment INT;
This sets the ‘comment’ column to be of the integer type. Here is the result:
mysql> SELECT * FROM test_table; +----+------+---------+ | id | name | comment | +----+------+---------+ | 1 | Jim | 0 | +----+------+---------+
These are some of the most essential and useful commands to insert and manipulate data in MySQL on your Linux VPS. Of course, it goes without saying that if you are a database administrator, these are rather basic commands – you will need to look into more advanced commands. For that, you can use the Internet, which has many useful tutorials written by MySQL experts.
Of course, you don’t have to do any of this if you use one of our MySQL VPS hosting services, in which case you can simply ask our expert Linux admins to do this for you. They are available 24×7 and will take care of your request immediately.
PS. If you liked this post, please share it with your friends on the social networks using the buttons below, or simply leave a comment. Thanks.