Skip to main content

Step-by-Step MariaDB

Step-by-Step MariaDB

Setting up a database:

  1. Create a database
  2. Create a user
  3. Create a table
  4. Create a column
  5. Create indexes
  6. Add foreign keys

Using the database:

  1. INSERT
  2. SELECT
  3. UPDATE
  4. DELETE

Create a Table

A table should be considered the definition of an object. Each column is a property of the object and each row is an instance of that object.

For example:

person
person_id first_name last_name date_of_birth
22 John Smith 1970-jan-01

This is a table defining an object named person. Each object has 4 properties: person_id, first_name, last_name and date_of_birth. There is one object recorded (one row), one that has person_id = 22, first_name = John, last_name = Smith and date_of_birth = 1970-jan-01.

MariaDB Statement

CREATE TABLE IF NOT EXISTS tbl_name
(tbl_name_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);

Where tbl_name is the name of the table.

phpMyAdmin

Notes

As of today (2017), the default storage engine in MariaDB is InnoDB. There are no reasons to use another storage engine unless there is a very specific need.

Primary key

Within the parenthesis of the statement above, you have the definition of a single column (a table must have at least one column when created). The example here shows a column defined as PRIMARY KEY. Every table should have a column, or set of columns, with a primary index (or key) declared. The chosen column should be as short as possible and be the one you use in your most vital queries. It will most likely be an ID column with an AUTO_INCREMENT value, as in the example presented above. These unique IDs can serve as pointers to corresponding rows in other tables when you join tables using foreign keys.

Such ID column:

  • Must be declared as NOT NULL;
  • Should preferably be declared as the smallest integer type needed:
    • TINYINT UNSIGNED (table is expected to have less than 255 rows)
    • SMALLINT UNSIGNED (table is expected to have less than 65 535 rows)
    • MEDIUMINT UNSIGNED (table is expected to have less than 16 777 215 rows)
    • INT UNSIGNED (table is expected to have less than 4 294 967 295 rows)
    • BIGINT UNSIGNED (shouldn't be used unless you're Google or Facebook!)

    Note: AUTO_INCREMENT will set the column value automatically to the column's maximum value + 1 when inserting a new row. When you delete a row, the number is not reused, so take that into account when selecting the proper integer type.

  • Should not be a column where you store data that you will refer to. It should be considered an internal pointer to the row it belongs only;
  • Should be named tbl_name_id or something similar for proper maintenance of the database ( Ex.: If the table name is person, the column name should be person_id ). It will be helpful when joining tables and keeping track of foreign keys.

References

MariaDB

MySQL