Insert sample data

In this tutorial you will learn to insert sample data to Percona Server for MySQL.

We will enter SQL statements via the same MySQL shell we used to connect to the database.

1. Let’s create a separate database for our experiments:

   CREATE DATABASE mydb;
   use mydb;
   

   Output

   Query OK, 1 row affected (0.01 sec)
   Database changed
   

2. Now let’s create a table which we will later fill with some sample data:

   CREATE TABLE extraordinary_gentlemen (
       id int NOT NULL AUTO_INCREMENT,
       name varchar(255) NOT NULL,
       occupation varchar(255),
       PRIMARY KEY (id)
   );
   

   Output

   Query OK, 0 rows affected (0.04 sec)
   

3. Adding data to the newly created table will look as follows:

   INSERT INTO extraordinary_gentlemen (name, occupation)
     VALUES
     ("Allan Quartermain","hunter"),
     ("Nemo","fish"),
     ("Dorian Gray", NULL),
     ("Tom Sawyer", "secret service agent");
   

   Output

   Query OK, 4 rows affected (0.01 sec)
   Records: 4  Duplicates: 0  Warnings: 0
   

4. Query the collection to verify the data insertion

   SELECT *
   FROM extraordinary_gentlemen;
   

   Output

   +----+-------------------+----------------------+
   | id | name              | occupation           |
   +----+-------------------+----------------------+
   |  1 | Allan Quartermain | hunter               |
   |  2 | Nemo              | fish                 |
   |  3 | Dorian Gray       | NULL                 |
   |  4 | Tom Sawyer        | secret service agent |
   +----+-------------------+----------------------+
   

5. Updating data in the database would be not much more difficult:

   UPDATE  extraordinary_gentlemen
      SET occupation = "submariner"
   WHERE name = "Nemo";
   

   Output

   Query OK, 1 row affected (0.00 sec)
   Rows matched: 1  Changed: 1  Warnings: 0
   

6. Now if you repeat the SQL statement from step 4, you will see the changes take effect:

   SELECT *
   FROM extraordinary_gentlemen;
   

   Output

   +----+-------------------+----------------------+
   | id | name              | occupation           |
   +----+-------------------+----------------------+
   |  1 | Allan Quartermain | hunter               |
   |  2 | Nemo              | submariner           |
   |  3 | Dorian Gray       | NULL                 |
   |  4 | Tom Sawyer        | secret service agent |
   +----+-------------------+----------------------+
   

Next steps

Make a backup

Get expert help

If you need assistance, visit the community forum for comprehensive and free database knowledge, or contact our Percona Database Experts for professional support and services. Join K8S Squad to benefit from early access to features and “ask me anything” sessions with the Experts.

Community Forum Get a Percona Expert Join K8S Squad

---

Last update: 2024-04-02