Programming in SQL: PostgreSQL, MySQL/MariaDB

March 14, 2019

Lang: cs en de es

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

Články na podobné téma

How to use MailCatcher to test emails
Python OpenAI API
Creating a WebSocket web application and setting up a proxy
Project management: agile software development
How to run old PHP applications
What a good programmer should know
Rust programming language
NodeJS: development, server configuration
Nette security bug CVE-2020-15227
REST API: platform API
Custom web and mail hosting with ISP Config software
HTTPS: secure web
NoSQL database Mongo DB
Connecting to Microsoft SQL Server from Linux
What is the job description of a programmer
Which mail and web hosting to choose
Digispark - Program Atmel ATtiny microcontroller with Arduino IDE
Development for ARM processors with Arduino IDE
How to program the ESP8266 WiFi processor
Open smartphone with Linux - Openmoko Neo FreeRunner

Newsletter

If you are interested in receiving occasional news by email.
You can register by filling in your email news subscription.


+