Some sql / nosql

sql sql-join

Monitor

# check log
$ mysql -u root -p -se "SHOW VARIABLES" | grep -e log_error -e general_log -e slow_query_log
$ mysql -u root -p
$ mysql> SHOW VARIABLES LIKE "general_log%";

$ check connection
$ mysqladmin -u root -p -i 1 processlist

Example Databases and Tables

  • https://memto.github.io/linux/program/tooltips/2021/08/13/example-databases-and-tables-sqlfiddle/

  • Auto Shop Database:

    • http://sqlfiddle.com/#!9/ff1d04
    • https://stackoverflow.com/a/46380990
-- Relationships between tables
-- Each Department may have 0 or more Employees
-- Each Employee may have 0 or 1 Manager
-- Each Customer may have 0 or more Cars
  • Library Database:
    • http://sqlfiddle.com/#!9/ba7ded
    • https://stackoverflow.com/a/46381062
-- Relationships between tables
-- Each author can have 1 or more books
-- Each book can have 1 or more authors
  • Countries Table:
    • http://sqlfiddle.com/#!9/c7b3c2
    • https://stackoverflow.com/a/46381130

SQL Joins: Difference Between Inner/Left/Right/Outer Joins

  • https://joins.spathon.com/

PG Admin gen INSERT query

-- https://wiki.postgresql.org/wiki/Create_INSERT_statement

CREATE OR REPLACE FUNCTION create_insert_statement(regclass, anyelement) RETURNS text
    LANGUAGE plpgsql
    AS $$
DECLARE
_schemaname text;
_tablename text;
_key text;
_value text;
_columns text[];
_values text[];
BEGIN
SELECT pg_namespace.nspname, pg_class.relname
INTO STRICT _schemaname, _tablename
FROM pg_class
JOIN pg_namespace
    ON (pg_namespace.oid = pg_class.relnamespace)
WHERE pg_class.oid = $1;

FOR _key IN
    SELECT columns.column_name
    FROM information_schema.columns
    WHERE columns.table_schema = _schemaname
      AND columns.table_name = _tablename
    ORDER BY columns.ordinal_position
LOOP
    EXECUTE format($s$SELECT format('%%L', ((($1)::%s).%I))$s$, $1, _key) USING $2 INTO STRICT _value;
    _columns := _columns || _key;
    _values := _values || _value;
END LOOP;
RETURN format('INSERT INTO %s (%s) VALUES (%s)', $1, array_to_string(_columns, ','), array_to_string(_values, ','));
END;
$$;


CREATE TABLE mytable(a int, b int);
INSERT INTO mytable VALUES (0, -999);

SELECT create_insert_statement(tableoid, mytable) FROM mytable;

-- returns
--            create_insert_statement            
-------------------------------------------------
-- INSERT INTO mytable (a,b) VALUES ('0','-999')
--(1 row)