Database Cheatsheets

mysql.md

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.md

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.

postgres.md

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.md

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.