SQL

Show Tablestructure

DESCRIBE Table1;

Work with Timestamps

INSERT INTO test_timestamp(t1)
VALUES('2008-01-01 00:00:01');

SELECT * FROM stt_tasks WHERE pit_task_added > NOW() - INTERVAL 7 DAY ORDER BY task_id DESC;

Create Comments

SELECT * FROM my_table; -- <comment>

Basic-Setup

CREATE USER '<user_name>'@'localhost' IDENTIFIED BY '<user_password>';
DROP DATABASE IF EXISTS <database_name>;
CREATE DATABASE <database_name>;
USE <database_name>;

DROP TABLE IF EXISTS <table_1> , <table_2>;

CREATE TABLE <table_1> (
  <value_1.0> int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  <value_1.1> timestamp DEFAULT CURRENT_TIMESTAMP,
  <value_1.2> int,
  <value_1.3> varchar(512),
  <value_1.4> text,
  <value_1.5> BOOLEAN
);

CREATE TABLE <table_2> (
  <value_2.1> int PRIMARY KEY,
  <value_2.2> varchar(512)
);

GRANT ALL PRIVILEGES ON <database_name>.* TO '<user_name>'@'localhost';

FLUSH PRIVILEGES;

View Size of Tables

SELECT
   table_schema 'Database Name',
   SUM(data_length + index_length) 'Size in Bytes',
   ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size in MiB'
FROM information_schema.tables
GROUP BY table_schema;

Select every n-th row

select * from <table_name> where <table_name.id> mod <n-th_row> = 0;

Access Database from another Host (MariaDB)

Info at the official Website: https://mariadb.com/kb/en/configuring-mariadb-for-remote-client-access/

Edit /etc/my.cnf or /etc/mysql/my.cnf and put the following at the end of the file

[mysqld]
   skip-networking=0
   skip-bind-address

Save the file and restart the mysqld daemon

systemctl restart mariadb.service

Configure phpMyAdmin to have acces to these Databases

Create a mysql-user on the machine with the database, that is able to acces the database from another machine:

CREATE USER '<username>'@'<remote_fqdn_or_ip>' IDENTIFIED BY '<password>';
GRANT ALL PRIVILEGES ON *.* TO '<username>'@'<remote_fqdn_or_ip>' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Edit config.inc.php in you phpMyAdmin base-folder

$i++;
$cfg['Servers'][$i]['host']             = '<remote_fqdn_or_ip>';
$cfg['Servers'][$i]['connect_type']     = 'tcp';
$cfg['Servers'][$i]['compress']         = 'FALSE';
$cfg['Servers'][$i]['auth_type']        = 'config';
$cfg['Servers'][$i]['user']             = '<username>';
$cfg['Servers'][$i]['password']         = '<password>';
$cfg['Servers'][$i]['verbose_check']    = 'TRUE';