SQL implementation

sudo mysql

sudo mysql -e "CREATE USER $USER"

mysql -e 'SHOW DATABASES'

sudo mysql -e 'CREATE USER user0 IDENTIFIED WITH mysql_native_password BY "a"'
sudo mysql -e 'GRANT ALL PRIVILEGES ON database_name.* TO "user0"'

mysql -u user0 -p

mysql -u user0 -pmypassword

sudo mysql -e 'SELECT * FROM mysql.user'

sudo mysql -e 'SELECT * FROM mysql.db'

sudo mysql -e 'SHOW DATABASES'

sudo mysql -e 'CREATE DATABASE mydb0'

sudo mysql -e 'DROP DATABASE mydb0'

sudo mysql -e 'SHOW TABLES' mydb0

sudo mysql -e 'SHOW TABLES FROM mydb0'

sudo -u postgres createuser -s $(whoami)
createdb $(whoami)

sudo cat /etc/postgresql/12/main/pg_hba.conf

local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer

psql -c '\du'

                                    List of roles
  Role name  |                         Attributes                         | Member of 
-------------+------------------------------------------------------------+-----------
 ciro        | Superuser, Create role, Create DB                          | {}
 owning_user |                                                            | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

psql -c 'DROP USER username;'

createdb testdb0

help

\?

\h

\h SELECT

psql -c '\l'

    Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-------------+----------+----------+-------------+-------------+-----------------------
 ciro        | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 postgres    | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 template0   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
 template1   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
 testdb0     | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
(6 rows)

psql -c 'DROP DATABASE "testdb0";'

psql

\c testdb0

psql testdb0 -c 'CREATE TABLE table0 (int0 INT, char0 CHAR(16));'

psql testdb0 -c '\dt'

        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+-------
 public | table0 | table | ciro
(1 row)

psql testdb0 -c '\d+ table0'

                                      Table "public.table0"
 Column |     Type      | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------------+-----------+----------+---------+----------+--------------+-------------
 int0   | integer       |           |          |         | plain    |              | 
 char0  | character(16) |           |          |         | extended |              | 

psql testdb0 -c "INSERT INTO table0 (int0, char0) VALUES (2, 'two'), (3, 'three'), (5, 'five'), (7, 'seven');"
psql testdb0 -c 'SELECT * FROM table0;'

 int0 |      char0
------+------------------
    2 | two
    3 | three
    5 | five
    7 | seven
(4 rows)

psql testdb0 -c 'DROP TABLE table0;'

with symbols(characters) as (VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'))
select string_agg(substr(characters, (random() * (length(characters) - 1) + 1)::INTEGER, 1), '')
from symbols
join generate_series(1,8) as word(chr_idx) on 1 = 1 -- word length
join generate_series(1,10000) as words(idx) on 1 = 1 -- # of words
group by idx;

create table tmp(s text);
insert into tmp(s)
  select s from
  (
    with symbols(characters) as (VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'))
    select string_agg(substr(characters, (random() * (length(characters) - 1) + 1)::INTEGER, 1), '') as asdf
    from symbols
    join generate_series(1,8) as word(chr_idx) on 1 = 1 -- word length
    join generate_series(1,10000) as words(idx) on 1 = 1 -- # of words
    group by idx
  ) as sub(s);

CREATE OR REPLACE FUNCTION random_string(int) RETURNS TEXT as $$
select
  string_agg(substr(characters, (random() * length(characters) + 1)::integer, 1), '') as random_word
from (values('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789    --')) as symbols(characters)
  join generate_series(1, $1) on 1 = 1
$$ language sql;  

create table tmp(s text, t text);
insert into tmp(s) select random_string(10) from generate_series(10);

createuser -P user0
createdb user0

psql -c "create role NewRole with login password 'secret'"

psql -U user0 -h localhost

PGPASSWORD=a psql -U user0 -h localhost

createdb user0db0
psql -c 'GRANT ALL PRIVILEGES ON DATABASE user0db0 TO user0'

psql -c '\l'

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 user0db0  | ciro     | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/ciro             +
           |          |          |             |             | ciro=CTc/ciro        +
           |          |          |             |             | user0=CTc/ciro

PGPASSWORD=a psql -U user0 -h localhost user0db0 -c 'CREATE TABLE table0 (int0 INT, char0 CHAR(16));'
PGPASSWORD=a psql -U user0 -h localhost user0db0 -c "INSERT INTO table0 (int0, char0) VALUES (2, 'two'), (3, 'three'), (5, 'five'), (7, 'seven');"
PGPASSWORD=a psql -U user0 -h localhost user0db0 -c 'SELECT * FROM table0;'

/var/log/postgresql/postgresql-13-main.log

log_statement = 'all'

/etc/postgresql/13/main/postgresql.conf

sudo service restart postgresql

sqlite3 db.sqlite3 "
CREATE TABLE 'IntegerNames' (int0 INT, char0 CHAR(16));
INSERT INTO 'IntegerNames' (int0, char0) VALUES (2, 'two'), (3, 'three'), (5, 'five'), (7, 'seven');
"

sqlite3 db.sqlite3 '.tables'

IntegerNames

sqlite3 db.sqlite3 '.schema IntegerNames'

CREATE TABLE IF NOT EXISTS 'IntegerNames' (int0 INT, char0 CHAR(16));

sqlite3 db.sqlite3 'SELECT * FROM IntegerNames'

2|two
3|three
5|five
7|seven