A record is the definition for a row in a table.
Creating A Database
A single SQL instance can have one or more databases.
Creating A Table
If you only want to create a table if it doesn’t already exist, you can use
IF NOT EXISTS:
Changing A Position Of A Column
MySQL/MariaDB are somewhat unique in that you can both specify the insertion point of a column and change the position of an existing column. Many other databases such as PostgreSQL do not let you do this, and new columns are always added to the end of the table. This does not matter so much for code-driven front ends which can render the columns in whatever order they choose, but this ability to insert a column at a point of your choosing is really useful if you rely on generic database viewing tools such as DBeaver.
To change the position of an existing column, by inserting after an existing column:
If you want to insert the column as the first column, instead of
AFTER <existing_col_name> use
More info at https://mariadb.com/kb/en/alter-table/.
Checking If A Column Exists
You can use the
SHOW COLUMNS command to check if a column exists in a table:
For example, in Python:
Inserting A Record
To insert a record into a table, use the SQL
INSERT command. Let’s say we wanted to add a person called
josh to the
To do the same thing from Python:
Updating A Record
The SQL command
UPDATE is used to update existing records (table rows). Suppose we wanted to update the age of all people named
To do the same thing in Python:
TIMESTAMP data type holds temporal data in the form of a date and time. It can be used
There is special behaviour defined for the first column of this type that you add to a table, it automatically gets the following properties applied to it:
ON UPDATE CURRENT_TIMESTAMP
This means that the column records will automatically default to the current datetime when it is first added. It also means that when any other data in the record is changed, the column will update to the current datetime (so it acts like a “last modified” field).
See https://mariadb.com/kb/en/timestamp/ for more info.
You can get some debug info on the last foreign key failure by running the command:
MariaDB is a fully open-source, GPL licenced database engine that is designed to be a drop-in replacement for MySQL. Whilst MySQL supports Windows, Linux and Mac OS, MariaDB only supports Windows and Linux (no Mac OS support)1.
PostgreSQL is a object-orientated database. The main feature of a object-orientated database is that tables can inherit from other tables, which means they automatically get all the data columns of the table they inherit from (similar to object inheritance in object-orientated programming languages).
Inheritance is defined when creating tables with the
INHERITS clause with the
CREATE TABLE statement:
If a table already exists, you can inheritance via the
INHERIT variant of the
ALTER TABLE statement.
PostgreSQL does not let you easily alter the position of a column in a table – to do so you have to recreate the table with the desired column order.
Working With PostgreSQL In Python
One of the most popular PostgreSQL libraries for Python is
psycopg2. To install it on Ubuntu you will also need the
libpg-dev package on your system:
Vishwash Vishwakarma (2020, May 22). Difference Between MySQL and MariaDB. GeeksforGeeks. Retrieved 2022-07-18, from https://www.geeksforgeeks.org/difference-between-mysql-and-mariadb/. ↩︎
This work is licensed under a Creative Commons Attribution 4.0 International License .