| * | MySQL | SELECT DISTINCT age FROM students; | Return distinct values |
| mysqldump remote | MySQL | mysqldump -u root -p$PASS -h $HOST --no-create-info --no-tablespaces --where="created_at >= 1647450000 AND created_at < 1648918800" delivery_partner orders > dump.sql | Dump remote MySQL |
| mysql execute remote | MySQL | mysql -h $HOST -u root -p$PASS --default-character-set=utf8mb4 $DB < update_2022_17_03.sql | Execute query on remote MySQL |
| mysql login | MySQL | mysql -u root -p | Login to MySQL |
| mysqldump backup | MySQL | mysqldump -P 3306 -h [ip_address] -u [username] -p db_name > db_backup.sql | Backup database |
| ALTER USER | MySQL | ALTER USER user IDENTIFIED BY 'MoWwYFeWtCzzT43'; | Change password |
| GRANT PRIVILEGES | PostgreSQL | GRANT ALL PRIVILEGES ON . TO 'username'@'localhost' IDENTIFIED BY 'password'; | Grant privileges |
| SELECT version() | PostgreSQL | SELECT version(); | Check version |
| pg_indexes | PostgreSQL | SELECT * FROM pg_indexes WHERE schemaname LIKE 'vexe%' AND tablename LIKE '%orders%'; | Find indexes |
| ALTER USER | PostgreSQL | ALTER USER user_name WITH PASSWORD 'new_password'; | Change password |
| SHOW max_connections | PostgreSQL | SHOW max_connections; | Show max connections |
| pg_stat_activity | PostgreSQL | SELECT * FROM pg_stat_activity; | Show active connections |
| setval sequence | PostgreSQL | SELECT pg_catalog.setval(pg_get_serial_sequence('search_requests', 'id'), (SELECT MAX(id) FROM search_requests) + 1000); | Increase sequence by arbitrary number |
| connection stats | PostgreSQL | SELECT datname, count(datname) FROM pg_stat_activity GROUP BY datname ORDER BY datname; | Statistics of active connections |
| CREATE INDEX | PostgreSQL | CREATE UNIQUE INDEX orders_pkey ON vexe.orders USING hash/btree (id); | Create index |
| DROP INDEX | PostgreSQL | DROP INDEX idx_department_id; | Drop index |
| CHECK constraint | PostgreSQL | CREATE TABLE person (name text, current_mood text CHECK (current_mood IN ('sad','ok','happy'))); ALTER TABLE person DROP CONSTRAINT person_current_mood_check; ALTER TABLE person ADD CONSTRAINT person_current_mood_check CHECK (current_mood IN ('sad','happy')) NOT VALID; ALTER TABLE person VALIDATE CONSTRAINT person_current_mood_check; | Add and modify CHECK constraint |
| CREATE INDEX | Oracle | CREATE INDEX FILM_QUERY_INDX1 ON FILM_QUERY(PAY_CODE); | Create index |
| EXPLAIN PLAN | Oracle | EXPLAIN PLAN FOR SELECT * FROM FILM_QUERY fq ORDER BY CREATED_DATE DESC; | Explain query |
| DBMS_XPLAN.DISPLAY | Oracle | SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); | Show explain result |
| all_indexes | Oracle | SELECT index_name, index_type, visibility, status, table_name, owner FROM all_indexes WHERE table_name = 'FILM_QUERY'; | Show indexes |
| DROP INDEX | Oracle | DROP INDEX ord_customer_ix_demo; | Drop index |
| index size | Oracle | SELECT idx.index_name, seg.TABLESPACE_NAME, idx.TABLE_NAME, idx.INDEX_TYPE, SUM(bytes)/1024/1024 AS MB FROM USER_SEGMENTS seg, USER_INDEXES idx WHERE idx.index_name = seg.segment_name GROUP BY idx.index_name, seg.TABLESPACE_NAME, idx.TABLE_NAME, idx.INDEX_TYPE ORDER BY MB DESC; | Check index size |
| user_tab_partitions | Oracle | SELECT table_name, partition_name, last_analyzed, high_value FROM user_tab_partitions WHERE table_name = 'Table_Name' ORDER BY last_analyzed DESC; | Show partitions |
| table size | Oracle | SELECT DS.TABLESPACE_NAME, SEGMENT_NAME, ROUND(SUM(DS.BYTES)/(1024*1024)) AS MB FROM USER_SEGMENTS DS WHERE SEGMENT_NAME IN (SELECT TABLE_NAME FROM USER_TABLES) GROUP BY DS.TABLESPACE_NAME, SEGMENT_NAME ORDER BY MB DESC; | Check table size |
| v$version | Oracle | SELECT * FROM v$version; | Check version |
| CREATE MATERIALIZED VIEW | Oracle | CREATE MATERIALIZED VIEW MV_APPROVED_TRANS_SUMMARY AS SELECT * FROM TABLE; | Create materialized view |
| DBMS_MVIEW.REFRESH | Oracle | BEGIN DBMS_MVIEW.REFRESH('MATERIALIZED_VIEW_NAME','C'); END; | Refresh materialized view (C=Complete, F=Fast, FORCE=Auto) |
| USER_MVIEWS | Oracle | SELECT mview_name, last_refresh_date, refresh_mode, refresh_method, last_refresh_type FROM USER_MVIEWS WHERE mview_name = 'MV_APPROVED_TRANS_SUMMARY'; | Check materialized view status |
| CREATE PARTITION | Oracle | CREATE TABLE sales (sale_id NUMBER, sale_date DATE, amount NUMBER) PARTITION BY RANGE (sale_date) (PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-APR-2025','DD-MON-YYYY')), PARTITION sales_q2 VALUES LESS THAN (TO_DATE('01-JUL-2025','DD-MON-YYYY')), PARTITION sales_q3 VALUES LESS THAN (TO_DATE('01-OCT-2025','DD-MON-YYYY')), PARTITION sales_q4 VALUES LESS THAN (MAXVALUE)); | Create partition |
| ADD PARTITION | Oracle | ALTER TABLE sales ADD PARTITION sales_q5 VALUES LESS THAN (TO_DATE('01-JAN-2026','DD-MON-YYYY')); | Add partition |