DDL Queries
- Show all dbs -
SHOW DATABASES; - Select one -
USE {$db}; - Show tables in a db -
SHOW TABLES; - Show description of DB -
DESCRIBE {$db}
Create new table
CREATE TABLE table_name (
id numeric,
description text);
DML Queries
Update
UPDATE table_redacted
SET state = "IMPORTED"
WHERE state = "EXPORTED_FAILED";
Selects
join
SELECT *
FROM data_transaction t JOIN
transaction_item i
ON(t.data_transaction_number
= i.data_transaction_id)
WHERE t.data_transaction_number = 123456;
and / or preference order
The category HAS to be SG OR SH + all the previous conditions.
SELECT COUNT(*), type, operator, category, state
FROM prefix.table_redacted
WHERE type = "S"
AND operator != "ASD"
AND (category = "SG"
OR category = "SH")
LIMIT 200;
having
SELECT title, start_date, COUNT(*)
FROM appointment
GROUP BY title, start_date
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC;
date like
SELECT COUNT(*)
FROM invoice_position
WHERE reference_date LIKE "%2019-01%";
between
SELECT id, state
FROM invoice_position
WHERE id BETWEEN 140 AND 144;
Subqueries
Find all Items from all Invoices, which belong to a transaction with a card_uid 0410ed522d5e81
SELECT *
FROM transaction_item
WHERE data_transaction_id IN (
SELECT data_transaction_number
FROM data_transaction
WHERE card_uid = "0410ed522d5e81"
);
Delete all invoices except the ones with the card uids I want
DELETE
FROM data_transaction
WHERE data_transaction_number NOT IN(
SELECT data_transaction_number
FROM
(SELECT data_transaction_number
FROM data_transaction
WHERE card_uid = "0410ed522d5e81"
OR card_uid = "0464cd3a2b5e80"
) invoice
);