dbml
Table Users {
id INT [PK, NOT NULL]
name VARCHAR(255) [NOT NULL]
email VARCHAR(255) [NOT NULL]
password VARCHAR(255) [NOT NULL]
}
Table UserProfile {
id INT [PK, NOT NULL, ref: > Users.id]
display_name VARCHAR(255) [NOT NULL]
avatar VARCHAR(255)
about TEXT
}
Table Posts {
id INT [PK, NOT NULL]
user_id INT [NOT NULL, ref: > Users.id]
title VARCHAR(255) [NOT NULL]
body TEXT [NOT NULL]
created_at TIMESTAMP [NOT NULL]
updated_at TIMESTAMP
}
Table Comments {
id INT [PK, NOT NULL]
post_id INT [NOT NULL, ref: > Posts.id]
user_id INT [NOT NULL, ref: > Users.id]
body TEXT [NOT NULL]
created_at TIMESTAMP [NOT NULL]
updated_at TIMESTAMP
}
Table Tags {
id INT [PK, NOT NULL]
name VARCHAR(255) [NOT NULL]
}
Table PostTags {
id INT [PK, NOT NULL]
post_id INT [NOT NULL, ref: > Posts.id]
tag_id INT [NOT NULL, ref: > Tags.id]
}
SELECT, WHERE, ORDER BY, LIMIT, OFFSET
SELECT * FROM table_name;
SELECT column_name1, column_name2, ... FROM table_name;
SELECT * FROM table_name WHERE column_name = value;
SELECT * FROM table_name WHERE column_name > value;
SELECT * FROM table_name WHERE column_name < value;
SELECT * FROM table_name WHERE column_name >= value;
SELECT * FROM table_name WHERE column_name <= value;
SELECT * FROM table_name WHERE column_name <> value;
SELECT * FROM table_name WHERE column_name != value;
SELECT * FROM table_name WHERE NOT column_name = value;
SELECT * FROM table_name WHERE column_name = value AND column_name = value;
SELECT * FROM table_name WHERE column_name = value OR column_name = value;
SELECT * FROM table_name WHERE column_name IN (value1, value2, ...);
SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;
SELECT * FROM table_name WHERE column_name LIKE '%value%';
SELECT * FROM table_name WHERE column_name IS NULL;
SELECT * FROM table_name WHERE column_name IS NOT NULL;
SELECT * FROM table_name ORDER BY column_name ASC;
SELECT * FROM table_name ORDER BY column_name DESC;
SELECT * FROM table_name ORDER BY column_name1 ASC, column_name2 DESC;
SELECT * FROM table_name LIMIT 10;
SELECT * FROM table_name LIMIT 10 OFFSET 10;
SELECT * FROM table_name LIMIT 10 OFFSET 20;
Aggregate Function, GROUP BY, HAVING
SELECT COUNT(column_name) FROM table_name;
SELECT SUM(column_name) FROM table_name;
SELECT AVG(column_name) FROM table_name;
SELECT MIN(column_name) FROM table_name;
SELECT MAX(column_name) FROM table_name;
SELECT COUNT(column_name) FROM table_name GROUP BY column_name;
SELECT COUNT(column_name) FROM table_name GROUP BY column_name HAVING COUNT(column_name) > 1;
JOIN
SELECT * FROM table1 JOIN table2 ON table1.column_name = table2.column_name;
SELECT * FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
SELECT * FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
SELECT * FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name;
SELECT * FROM table1 JOIN table2 ON table1.column_name = table2.column_name WHERE table1.column_name = value;
SELECT * FROM table1 JOIN table2 ON table1.column_name = table2.column_name AND table1.column_name = value;
INSERT, UPDATE, SELECT
INSERT INTO table_name VALUES (value1, value2, ...);
INSERT INTO table_name (column_name1, column_name2, ...) VALUES (value1, value2, ...);
UPDATE table_name SET column_name = value WHERE column_name = value;
UPDATE table_name SET column_name = value, column_name = value WHERE column_name = value;
DELETE FROM table_name WHERE column_name = value;
DELETE FROM table_name WHERE column_name = value AND column_name = value;
CREATE
CREATE DATABASE IF NOT EXISTS database_name;
CREATE TABLE IF NOT EXISTS table_name (
column_name data_type constraints,
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
name VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);
ALTER
ALTER TABLE table_name ADD COLUMN column_name data_type constraints;
ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;
ALTER TABLE table_name MODIFY COLUMN column_name data_type constraints;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES other_table(column_name);
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
DROP
DROP DATABASE IF EXISTS database_name;
DROP TABLE IF EXISTS table_name;
TRANSACTION
BEGIN TRANSACTION;
-- SQL queries
COMMIT;
ROLLBACK;
TRIGGER
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name
BEGIN
-- SQL queries
END;
Subquery
SELECT * FROM table_name
WHERE column_name = (SELECT column_name FROM table_name WHERE column_name = value);
UNION
SELECT * FROM table1
UNION
SELECT * FROM table2;
VIEW
CREATE VIEW view_name AS
SELECT * FROM table_name;