Differences

This shows you the differences between two versions of the page.

--- myhelp:sql [2023/08/09 08:00] – ulrich
+++ myhelp:sql [2024/10/05 07:51] (current) – [mySQL / MariaDB] ulrich
@@ Line 11: / Line 11: @@
 sqlite> UPDATE T_names SET birthyear=1997 WHERE name = 'Jack Doe';
 sqlite> SELECT * FROM T_names LIMIT 1;
+</code>
+====== mySQL / MariaDB ======
+<code>
+CREATE DATABASE IF NOT EXISTS meineDB;
+CREATE USER 'benutzer'@'localhost' IDENTIFIED BY 'passwort';
+GRANT ALL PRIVILEGES ON meineDB . * TO 'benutzer'@'localhost';
+</code>
+Grant Access from remote IP (10.11.12.13)
+<code>
+GRANT ALL ON meineDB.* to 'benutzer'@'10.11.12.13' IDENTIFIED BY 'passwort' WITH GRANT OPTION;
 </code>
@@ Line 53: / Line 65: @@
   - Find your pg_hba.conf (on RHEL based distros: /var/lib/pgsql/data/pg_hba.conf)
   - Add the following lines <code># Allow external
-host    all             all             10.11.12.0/24              md5 # for a network
+host    all             all             10.11.12.0/24              scram-sha-256 # for a network
-host    all             all             192.168.1.111/32           md5 # for a single IP</code>
+host    all             all             192.168.1.111/32           scram-sha-256 # for a single IP</code>
   - Restart the DBMS <code>systemctl restart postgresql.service</code>
+===== A method to get the lastest timestamp =====
+From my point of view I've a good and a bad example
+The good example:
+<code>SELECT something FROM table
+WHERE time_stamp = (SELECT MAX(time_stamp) FROM table);</code>
+The bad example:
+<code>SELECT something FROM table
+WHERE time_stamp = (SELECT time_stamp FROM table ORDER BY time_stamp DESC NULLS LAST LIMIT 1);</code>
+===== Other usefull stuff =====
+Switch off the pages from CLI(shell)
+<code>sudo -u postgres psql -P pager=off -d database -c "SELECT something FROM table;"</code>
+Copy a SELECT to a CSV from CLI(shell)
+<code>
+sudo -u postgres psql -d database -c "COPY (SELECT  something, other_stuff FROM table
+GROUP BY something) TO '/tmp/result.csv' WITH CSV HEADER;"
+</code>
 {{tag>[Good2Know]}}