SQL
###


Show Tablestructure
===================
.. code-block:: sql

   DESCRIBE Table1;

Work with Timestamps
====================
.. code-block:: sql

   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
===============
.. code-block:: sql

   SELECT * FROM my_table; -- <comment>


Basic-Setup
===========
.. code-block:: sql

    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
===================
.. code-block:: sql

   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
=====================
.. code-block:: sql

   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 :file:`/etc/my.cnf` or :file:`/etc/mysql/my.cnf` and put the following at the **end** of the file

.. code-block:: bash

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

Save the file and restart the mysqld daemon

.. code-block:: bash

   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:

.. code-block:: sql

   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

.. code-block:: php

   $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';