Programming in SQL: PostgreSQL, MySQL/MariaDB

Relational SQL databases are a constant and the main type of databases in 2019. SQL databases are many, for example the embedded database SQlite. MySQL/MariaDB and PostgreSQL databases are excellent and most commonly used. And this is a tutorial on how to get started with MySQL/MariaDB or PostgreSQL databases.

Relational database

What is it The relational database is described by Wikipedia. There are also so-called NoSQL databases such as MongoDB. But relational databases will always be with us. We have many SQL databases for example the embedded database SQlite. Microsoft's MS SQL database, which I personally avoid. Oracle database, which is popular in corporates, but despite its fame it also has many flies. The MySQL database, which is popular with web developers and PHP hosts. And now its fork MariaDB is being used. And PostgreSQL, which is generally considered the best relational database, and it's free software.

And there are many other and especially historical database systems.

What is SQL and how does the language SQL to use is on wikipedia.

In this article and in the videos, I will describe and demonstrate the installation and basic use of MySQL and PostgreSQL relational databases.
Performance tuning of PostgreSQL or MySQL is not the focus of this article or the videos. But of course, proper database tuning relative to server performance and application needs is a critical thing for stable and fast SQL query execution. For MySQL/MariaDB database performance tuning and optimization, please request a consultation.

In the videos, you will learn what a SQL database is. How to install MySQL and PostgreSQL databases and how to use them.

MySQL

Video stream content:

• Installing MariaDB
• Console Client
• Connecting to the database
• Creating a database and table user
• SQL commands insert, select, update.
• web administration: phpmyadmin, (adminer)
• Backup: mysqldump

Some commands for MySQL/MariaDB:

CREATE USER 'livestream'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON livestream.* TO 'livestream'@'localhost' ;

FLUSH PRIVILEGES;

MySQL/MariaDB video

Live stream on MySQL:

PostgreSQL

Video stream content:

• Installing MariaDB and Postgre SQL
• Console Client
• Connecting to the database
• Creating a database user and tables
• SQL commands insert, select, update.
• web administration: phppgadmin (pgadmin)
• Backup: mysqldump, pg_dump

Installation of SQL relational database PostgreSQL on Debian:

apt-get install postgresql-9.3
apt-get install postgresql-client
apt-get install phppgadmin

Phppgadmin will be available at the URL http://IP/phppgadmin. To be able to log in via the web interface you need to unregister local in the /etc/apache2/conf-enabled/phppgadmin.conf file. And in /etc/phppgadmin/config.inc.php set:

    $conf['extra_login_security'] = false;

PostgreSQL video

Live stream on PostgreSQL:

PosgreSQL commands:
Connecting to the database. PostreSQL has a default account tied to the system account, so you need to log in to it first:

sudo -u postgres
psql

Connecting from the console:

psql -h localhost -p 5432 -U myprojectuser test

Help:

\?

Selected database:

\c test

Creating a database:

CREATE DATABASE test;

Listing databases:

\l
                                     List of databases
   Name | Owner | Encoding | Collation | CType | Access rights
-----------+----------+----------+-------------+-------------+----------------------------
 postgres | postgres | UTF8 | en_CZ.UTF-8 | en_CZ.UTF-8 |
 template0 | postgres | UTF8 | cs_CZ.UTF-8 | cs_CZ.UTF-8 | =c/postgres +
           | | | | | postgres=CTc/postgres
 template1 | postgres | UTF8 | cs_CZ.UTF-8 | cs_CZ.UTF-8 | =c/postgres +
           | | | | | postgres=CTc/postgres
 test | postgres | UTF8 | cs_CZ.UTF-8 | cs_CZ.UTF-8 | =Tc/postgres +
           | | | | | postgres=CTc/postgres +
           | | | | | myprojectuser=CTc/postgres
(4 lines)

Listing tables:

\dt

Create a user with a password:

CREATE USER myprojectuser WITH PASSWORD 'password';

Create a role with a password:

CREATE USER david WITH PASSWORD 'ks839#@S';

Assign rights to the user:

GRANT ALL PRIVILEGES ON DATABASE test TO myprojectuser;

Create table:

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

Inserting data:

INSERT INTO Persons (PersonID, LastName, FirstName,Address, City ) VALUES (1, 'Tom B. Erichsen', 'Stavanger','Skagen 21', 'Norway');

Data update:

update persons set firstname='dggg' where city='Norwayssss';

Dump database:

pg_dump test > test.sql

Import data into the database:

 psql -d test < test.sql