Skip to content

Instantly share code, notes, and snippets.

@ephbaum
Last active January 6, 2024 04:55
Show Gist options
  • Save ephbaum/4b7600dfa01365e482ccba06c90b98a5 to your computer and use it in GitHub Desktop.
Save ephbaum/4b7600dfa01365e482ccba06c90b98a5 to your computer and use it in GitHub Desktop.
Database Cheatsheets

MySQL Commands Cheat Sheet

Basic Commands

  • Connect to MySQL server: mysql -u [username] -p
  • Show databases: SHOW DATABASES;
  • Create database: CREATE DATABASE [database_name];
  • Use database: USE [database_name];
  • Show tables: SHOW TABLES;
  • Create table: CREATE TABLE [table_name] ([column_definitions]);
  • Insert data: INSERT INTO [table_name] ([columns]) VALUES ([values]);
  • Select data: SELECT * FROM [table_name];
  • Update data: UPDATE [table_name] SET [column] = '[new_value]' WHERE [condition];
  • Delete data: DELETE FROM [table_name] WHERE [condition];
  • Drop table: DROP TABLE [table_name];
  • Drop database: DROP DATABASE [database_name];

Advanced Commands

  • Join tables (Inner Join): SELECT * FROM [table1] JOIN [table2] ON [table1].[column] = [table2].[column];
  • Left Join: SELECT * FROM [table1] LEFT JOIN [table2] ON [table1].[column] = [table2].[column];
  • Right Join: SELECT * FROM [table1] RIGHT JOIN [table2] ON [table1].[column] = [table2].[column];
  • Full Outer Join (via Union): SELECT * FROM [table1] LEFT JOIN [table2] ON [table1].[column] = [table2].[column] UNION SELECT * FROM [table1] RIGHT JOIN [table2] ON [table1].[column] = [table2].[column];
  • Cross Join: SELECT * FROM [table1] CROSS JOIN [table2];
  • Group data: SELECT [column], COUNT(*) FROM [table] GROUP BY [column];
  • Order data: SELECT * FROM [table] ORDER BY [column] ASC/DESC;
  • Create index: CREATE INDEX [index_name] ON [table_name]([column_name]);
  • Add foreign key: ALTER TABLE [table_name] ADD FOREIGN KEY ([column_name]) REFERENCES [other_table]([column_name]);

Note: Replace [username], [database_name], [table_name], [column_definitions], [columns], [values], [new_value], [condition], [size], [column], [table1], [table2], [index_name], and [other_table] with your specific details.

NoSQL Commands Cheat Sheet

MongoDB (Document-Based)

Basic Commands

  • Start MongoDB: mongod
  • Connect to MongoDB: mongo
  • Show databases: show dbs
  • Create or switch database: use [database_name]
  • Show collections: show collections
  • Create collection: db.createCollection('[collection_name]')
  • Insert document: db.[collection_name].insert({[document]})
  • Find documents: db.[collection_name].find()
  • Update document: db.[collection_name].update({[query]}, {$set: {[update_data]}})
  • Delete document: db.[collection_name].remove({[query]})

Redis (Key-Value Store)

Basic Commands

  • Start Redis: redis-server
  • Connect to Redis: redis-cli
  • Set key-value: SET [key] [value]
  • Get value: GET [key]
  • Delete key: DEL [key]
  • List keys: KEYS *
  • Set key expiration: EXPIRE [key] [seconds]

Memcached Commands Cheat Sheet

Basic Commands

  • Start Memcached: memcached -p [port] -m [memory] -c [connections]
  • Connect to Memcached (using Telnet): telnet [host] [port]
  • Set value: set [key] [flags] [exptime] [bytes] [noreply]\\r\\n[value]
  • Get value: get [key]
  • Get multiple values: get [key1] [key2] ... [keyN]
  • Add value (only if key doesn't exist): add [key] [flags] [exptime] [bytes] [noreply]\\r\\n[value]
  • Replace value (only if key exists): replace [key] [flags] [exptime] [bytes] [noreply]\\r\\n[value]
  • Delete key: delete [key] [noreply]
  • Increment numeric value: incr [key] [value]
  • Decrement numeric value: decr [key] [value]
  • Flush all data: flush_all [delay] [noreply]
  • Get stats: stats
  • Quit session: quit

Note: Replace [port], [memory], [connections], [host], [key], [flags], [exptime], [bytes], [value], and [delay] with your specific details.

Cassandra (Wide-Column Store)

Basic Commands

  • Start Cassandra: cassandra
  • Connect to Cassandra: cqlsh
  • Create keyspace: CREATE KEYSPACE [keyspace_name] WITH replication = {[options]};
  • Use keyspace: USE [keyspace_name];
  • Create table: CREATE TABLE [table_name] ([column_definitions]);
  • Insert data: INSERT INTO [table_name] ([columns]) VALUES ([values]);
  • Select data: SELECT * FROM [table_name];
  • Update data: UPDATE [table_name] SET [column] = '[new_value]' WHERE [condition];
  • Delete data: DELETE FROM [table_name] WHERE [condition];

Note: Replace [database_name], [collection_name], [document], [query], [update_data], [key], [value], [seconds], [keyspace_name], [options], [table_name], [column_definitions], [columns], [values], [new_value], and [condition] with your specific details.

PostgreSQL Commands Cheat Sheet

Basic Commands

  • Connect to PostgreSQL server: psql -U [username] -d [database_name]
  • List databases: \\l
  • Create database: CREATE DATABASE [database_name];
  • Connect to database: \\c [database_name]
  • List tables: \\dt
  • Create table: CREATE TABLE [table_name] ([column_definitions]);
  • Insert data: INSERT INTO [table_name] ([columns]) VALUES ([values]);
  • Select data: SELECT * FROM [table_name];
  • Update data: UPDATE [table_name] SET [column] = '[new_value]' WHERE [condition];
  • Delete data: DELETE FROM [table_name] WHERE [condition];
  • Drop table: DROP TABLE [table_name];
  • Drop database: DROP DATABASE [database_name];

Data Types

  • INTEGER: Integer numbers.
  • VARCHAR(size): String of text of length 'size'.
  • TEXT: Long text strings.
  • DATE: Date in YYYY-MM-DD format.
  • TIMESTAMP: Timestamp in YYYY-MM-DD HH:MM:SS format.

Advanced Commands

  • Join tables: SELECT * FROM [table1] INNER JOIN [table2] ON [table1].[column] = [table2].[column];
  • Group data: SELECT [column], COUNT(*) FROM [table] GROUP BY [column];
  • Order data: SELECT * FROM [table] ORDER BY [column] ASC/DESC;
  • Create index: CREATE INDEX [index_name] ON [table_name] ([column_name]);
  • Add foreign key: ALTER TABLE [table_name] ADD FOREIGN KEY ([column_name]) REFERENCES [other_table]([column_name]);

Note: Replace [username], [database_name], [table_name], [column_definitions], [columns], [values], [new_value], [condition], [size], [column], [table1], [table2], [index_name], and [other_table] with your specific details.

SQLite Commands Cheat Sheet

Basic Commands

  • Connect to SQLite database: sqlite3 [database_file]
  • Show tables: .tables
  • Create table: CREATE TABLE [table_name] ([column_definitions]);
  • Insert data: INSERT INTO [table_name] ([columns]) VALUES ([values]);
  • Select data: SELECT * FROM [table_name];
  • Update data: UPDATE [table_name] SET [column] = '[new_value]' WHERE [condition];
  • Delete data: DELETE FROM [table_name] WHERE [condition];
  • Drop table: DROP TABLE [table_name];

Data Types

  • INTEGER: Integer numbers.
  • TEXT: String of text.
  • BLOB: Binary large object.
  • REAL: Floating point numbers.
  • NUMERIC: Numeric values (includes booleans, dates, and timestamps).

Advanced Commands

  • Create index: CREATE INDEX [index_name] ON [table_name]([column_name]);
  • Add column: ALTER TABLE [table_name] ADD COLUMN [column_definition];
  • Transaction begin: BEGIN TRANSACTION;
  • Commit transaction: COMMIT;
  • Rollback transaction: ROLLBACK;

Note: Replace [database_file], [table_name], [column_definitions], [columns], [values], [new_value], [condition], [index_name], and [column_definition] with your specific details.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment