SQL

SQLite3

.help
.database
.table
.headers on

Database

# create db
CREATE DATABASE db_name;

# drop db
DROP DATABASE db_name;

User

# Create
CREATE USER username WITH PASSWORD '**********';

# Rename
alter user osint_read_only rename to read_only;
alter USER ${USER} with password '${PASSWORD}';

# Grant
GRANT ALL ON DATABASE dbname TO username;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ${USER};

# Revoke
REVOKE SELECT on ${TABLE} from ${USER};

Table

Misc

# SQLite
.headers on
# PostgreSQL
\d+ Table

Table

# create table
CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  column3 datatype
);

# create table with constraints
CREATE TABLE table_name {
  id INTEGER PRIMARY KEY,
  name TEXT UNIQUE,
  grade INTEGER NOT NULL,
  age INTEGER DEFAULT 10
};

# delete
TRUNCATE ${TABLE} RESTART IDENTITY CASCADE;
DELETE FROM ${TABLE}

# drop
DROP TABLE table_name;

# rename
ALTER TABLE table_name RENAME TO new_table_name;

# alter table
ALTER TABLE table_name
ADD column_name datatype;

CURD

# add/delete/update/query table

# Insert into columns in order:
INSERT INTO table_name
VALUES (value1, value2);

# Insert into columns by name:
INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

# delete
DELETE FROM table_name
WHERE column = value;

# update
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE column = value;

# query
SELECT FROM table_name
WHERE column = value;

# alter & add
ALTER TABLE table_name
ADD column_name datatype;

Select

SELECT * FROM table_name;

# and
SELECT column 
FROM table_name
WHERE column1 = value1
  AND column2 = value2;

# or
SELECT column
FROM table_name
WHERE column1 = value1
   OR column2 = value2;

# as
SELECT column AS column_alias
FROM table_name;

# like & regex
SELECT column
FROM table_name
WHERE column LIKE 'regex%'; 

SELECT column
FROM table_name
WHERE name LIKE '_regex';

# order
SELECT *
FROM table_name
ORDER BY column ASC/DESC;

# distinct
SELECT DISTINCT column
FROM table_name;

# between
SELECT *
FROM movies
WHERE year BETWEEN 1980 AND 1990;

# limit
SELECT *
FROM table_name
LIMIT 5;

# null
SELECT column1
FROM table_name
WHERE column2 IS NOT NULL;

# last
SELECT column
FROM table_name
ORDER BY id DESC
LIMIT 1;

Table - Aggregate Functions

# count
SELECT COUNT(*)
FROM table_name
WHERE column = value;

# avg
SELECT AVG(column)
FROM table_name;

# sum
SELECT SUM(column)
FROM table_name;

# max
SELECT MAX(column) 
FROM table_name;

# min
SELECT MIN(column) 
FROM table_name;

# group by
SELECT column, 
   COUNT(*) 
FROM table_name
GROUP BY column;

# having
SELECT year, 
   COUNT(*) 
FROM movies 
GROUP BY year
HAVING COUNT(*) > 5;

# round
SELECT year, 
   ROUND(AVG(rating), 2) 
FROM movies 
WHERE year = 2015;

Table - Multiple Tables

# union
SELECT column1
FROM table_name1
UNION
SELECT column2
FROM table_name2

# with
WITH temporary_movies AS (
   SELECT *
   FROM movies
)
SELECT *
FROM temporary_movies
WHERE year BETWEEN 2000 AND 2020;

# join
SELECT column_name(s)
FROM table1
LEFT JOIN table2
  ON table1.column_name = table2.column_name;

SELECT * 
FROM books
JOIN authors
  ON books.author_id = authors.id;

JSON

Select

# select json
select data->'title' from onion_ransomware where id =3;
# select text
select data->>'title' from onion_ransomware where id =3;
# json cast
select data->'peer_id' from telegram_messages where (data->'id')::int=1015;

Update

# update field
update onion_ransomware set  data = jsonb_set(data,'{catagory}','"ransomware"',True);

Sequence

# reset sequence
ALTER SEQUENCE ${SEQUENCE} RESTART WITH 1;

Export

# PostgreSQL
## Server
COPY tablename TO '/tmp/filename.csv' WITH (FORMAT CSV, HEADER);
## Client
\copy tablename TO '/tmp/filename.csv' CSV DELIMITER ','

Statistics

# count by something
select data->'author',count(*) from onion_ransomware group by data->'author';

\l+

# table size
select table_name, pg_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'public'
order by 2;

Misc

MySQL

# show
SHOW DATABASES;
SHOW TABLES;
SHOW FIELDS FROM table;
DESCRIBE table;
SHOW CREATE TABLE table;
SHOW PROCESSLIST;
KILL process_number;

# basic
CREATE DATABASE database;
CREATE USER 'user'@'localhost' IDENTIFIED by 'password';
CREATE USER 'user'@'%' IDENTIFIED by 'password';
GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO 'user'@'%' IDENTIFIED BY 'password';