MariaDB CRUD operations Hello World

Let’s see a slightly elaborated “Hello world”, to demonstrate the most common SQL operations with MariaDB: creating a database and a table, inserting a row, reading it, modifying it, deleting it.

All the examples were run in the MariaDB command-line client.

Creating databases and tables

Let’s create a database called tutorial and a table called example:

MariaDB [(none)]> CREATE SCHEMA tutorial;
Query OK, 1 row affected (0.003 sec)

MariaDB [(none)]> USE tutorial;
Database changed
MariaDB [tutorial]> CREATE TABLE example(
    -> id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> word1 VARCHAR(50) NOT NULL,
    -> word2 VARCHAR(50) NOT NULL
    -> );
Query OK, 0 rows affected (0.024 sec)

NOTE: In MariaDB, schema is just a synonym for database.

Inserting a row

Let’s insert a row:

MariaDB [tutorial]> INSERT INTO example (word1, word2) VALUES ('Hello', 'world');
Query OK, 1 row affected (0.009 sec)

Reading and counting rows

Let’s use SELECT to read all rows and to count rows:

MariaDB [tutorial]> SELECT * FROM example;
+----+-------+-------+
| id | word1 | word2 |
+----+-------+-------+
|  1 | Hello | world |
+----+-------+-------+
1 row in set (0.006 sec)

MariaDB [tutorial]> SELECT count(*) FROM example;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.007 sec)

Modifying rows

Let’s use UPDATE to modify the row we created:

MariaDB [tutorial]> UPDATE example SET word1 = 'Halloween' WHERE word1 = 'Hello';
Query OK, 1 row affected (0.022 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [tutorial]> SELECT * FROM example;
+----+-----------+-------+
| id | word1     | word2 |
+----+-----------+-------+
|  1 | Halloween | world |
+----+-----------+-------+
1 row in set (0.000 sec)

Deleting rows

Let’s use DELETE to delete the row:

MariaDB [tutorial]> DELETE FROM example WHERE word2 = 'world';
Query OK, 1 row affected (0.004 sec)

MariaDB [tutorial]> SELECT * FROM example;
Empty set (0.002 sec)