SQL
2018-12-24


Typically login with:
$ mysql -p -u [username] [databasename]
With the -p flag you'll be prompted to input password; databasename is optional.

To show databases available for you:
> show databases;
Then you can use the database:
> use [databasename]

Similarly you can show tables in a database:
> show tables;
And describe it's fields:
> desc [tablename];

To find out how to use mysql I highly suggest reading through MariaDB Documentation and Tutorials:
https://mariadb.com/kb/en/library/documentation/
https://mariadb.com/kb/en/library/training-tutorials/

Importing data to the database is fairly simple with well formatted input, but you need to have the database first:
> CREATE DATABASE [databasename];
Database dumps consist of executable commands, so you simply redirect the dumpfile as input:
$ mysql -p -u [username] [database_name] < [dumpfile]
If you have some .txt files (like on <i>_matura_</i>) you have to create tables yourself accordingly, and then you can load the data:
> CREATE TABLE [tablename] ([columnname] [options_and_datatype], [...]);
> LOAD DATA LOCAL INFILE '[filename.txt]' INTO TABLE [tablename] COLUMNS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 1 LINES ([columnname], @[variable], [...]) SET [columnname] = [expressions];
Mind that in the second statement after the tablename you can skip the rest.

Realistically you'd want to create some users and grant them privileges to work on certain databases. Here's how to do it:
> CREATE USER '[username]'@'[host]' IDENTIFIED BY '[password]';
> GRANT [privilege] ON [databasename].[tablename] TO '[username]'@'[host]';
Databasename and/or tablename can be a '*' wildcard (for all).
You can delete users just like you delete databases:
> DROP USER '[username]'@'[host]';
To show user's privileges:
> SHOW GRANTS FOR '[username]'@'[host]';
Users are saved in 'mysql' database - table 'user'. So to list all users:
> SELECT * FROM mysql.user;

Talking about privileges, I could explain the basic SQL syntax, but I won't.
To learn more about privileges specifically: https://mariadb.com/kb/en/library/grant/
Available privileges:  SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX, ALL, GRANT OPTION.

Privileges for basic queries let you execute those queries:
SELECT is probably the most important query. Basic syntax:
> SELECT [columnname] FROM [databasename];
Complex query:
> SELECT [columna] AS a, [columnb], [FUNC]([columnc]) AS c FROM [databasename] JOIN [otherdatabase] USING ([commoncolumnname]) WHERE [expr] GROUP BY [columnd] ORDER BY a DESC LIMIT 1;
It's worth mentioning that you can nest SELECT queries, just wrap them in parenthesis.

At that point you might want to learn SQL syntax and database manipulation.
Another important feature of SQL are built-in functions which let you execute conditional statements, manipulate data inline, etc. Example functions: SUM, COUNT, MAX, IF, LEFT, SUBSTR. 
MariaDB is capable of encrypting and decrypting AES and DES, it can compress and uncompress, and it can hash stuff with MD5, SHA1 and SHA2. For example:
> SELECT SHA2("[VAL]", [HASH_SIZE]);

This is all I've wanted to write down regarding SQL. I highly suggest you read the docs, both MariaDB and PostgreSQL are wonderfully documented (and they're both great software).
For something less specific - w3schools has a tutorial on SQL: https://www.w3schools.com/sql/default.asp