Differences

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

--- myhelp:sql [2023/08/28 05:28] – [Get the lastest timestamp] 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 57: / Line 69: @@
   - Restart the DBMS <code>systemctl restart postgresql.service</code>
-===== Get the lastest timestamp =====
+===== 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>
+<code>SELECT something FROM table
+WHERE time_stamp = (SELECT time_stamp FROM table ORDER BY time_stamp DESC NULLS LAST LIMIT 1);</code>
-The good example:
+===== Other usefull stuff =====
-<code>SELECT something FROM table WHERE time_stamp = (SELECT MAX(time_stamp) FROM table);</code>
+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]}}