MySQL: Commands Cheat Sheet
To login (from unix shell) use -h only if needed.
[mysql dir]/bin/mysql -h hostname -u root -p
Create a database on the sql server.
create database [databasename];
List all databases on the sql server.
show databases;
Switch to a database.
use [db name];
To see all the tables in the db.
show tables;
To see database’s field formats.
describe [table name];
To delete a db.
drop database [database name];
To delete a table.
drop table [table name];
SQL Server: Functions Cheat Sheet
String Functions
- ASCII
- CHAR
- CHARINDEX
- DIFFERENCE
- LEFT
- LEN
- LOWER
- LTRIM
- NCHAR
- PATINDEX
- REPLACE
- QUOTENAME
- REPLICATE
- REVERSE
- RIGHT
- RTRIM
- SOUNDEX
- SPACE
- STR
- STUFF
- SUBSTRING
- UNICODE
- UPPER
Exact Numerics
- bit
- tinyint
- smallint
- bigint
Approximate Numerics
- float
Date and Time
- smalldatetime
- datetime
- Strings
- char
- varchar
Unicode Strings
- nchar
- nvarchar
Binary Strings
- binary
- varbinary
Miscellaneous
- cursor
- sql_variant
- decimal
- money
- numeric
- real
- timestamp
- text
- ntext
- image
- table
- xml
Date Functions
- DATEADD (datepart, number, date)
- DATEDIFF (datepart, start, end)
- DATENAME (datepart, date)
- DATEPART (datepart, date)
- DAY (date)
- GETDATE()
- GETUTCDATE()
- MONTH (date)
- YEAR (date)
Dateparts
Type Conversion
- CAST (expression AS datatype)
- CONVERT (datatype, expression)
Ranking Functions
- RANK
- DENSE_RANK
- NTILE
- ROW_NUMBER
Grouping (Aggregate) Functions
- AVG
- BINARY_CHECKSUM
- CHECKSUM
- CHECKSUM_AVG
- COUNT
- COUNT_BIG
- GROUPING
- MAX
- MIN
- SUM
- STDEV
- STDEVP
- VAR
- VARP
Table Functions
- ALTER
- CREATE
- DROP
- TRUNCATE
Mathematical Functions
- ABS
- ACOS
- ASIN
- ATAN
- ATN2
- CEILING
- COS
- COT
- DEGREES
- EXP
- FLOOR
- LOG
- LOG10
- PI
- POWER
- RADIANS
- RAND
- ROUND
- SIGN
- SIN
- SQUARE
- SQRT
- TAN
PostgreSQL: Configuration Cheat Sheet
Service management commands:
sudo service postgresql stop
sudo service postgresql start
sudo service postgresql restartChanging verbosity & querying Postgres log:
1) First edit the config file, set a decent verbosity, save and restart postgres:
sudo vim /etc/postgresql/9.3/main/postgresql.conf
# Uncomment/Change inside:
log_min_messages = debug5
log_min_error_statement = debug5
log_min_duration_statement = -1
sudo service postgresql restartNow you will get tons of details of every statement, error, and even background tasks like VACUUMs
tail -f /var/log/postgresql/postgresql-9.3-main.logHow to add user who executed a PG statement to log (editing postgresql.conf):
log_line_prefix = '%t %u %d %a 'Managing Triggers
Create or modify a trigger
CREATE OR MODIFY TRIGGER trigger_name
WHEN EVENT
ON table_name TRIGGER_TYPE
EXECUTE stored_procedure;
Create a trigger invoked before a new row is inserted into the person table
WHEN BEFORE – invoke before the event occurs AFTER – invoke after the event occurs EVENT INSERT – invoke for INSERT UPDATE – invoke for UPDATE DELETE – invoke for DELETE TRIGGER_TYPE FOR EACH ROW FOR EACH STATEMENT CREATE TRIGGER before_insert_person BEFORE INSERT ON person FOR EACH ROW EXECUTE stored_procedure;
Delete a specific trigger
DROP TRIGGER trigger_name
SQL Aggregate: Functuions Cheat Sheet
AVG returns the average of a list
COUNT returns the number of elements of a list
SUM returns the total of a list
MAX returns the maximum value in a list
MIN returns the minimum value in a list
PostgreSQL: Commands Cheat Sheet
Login to postgresql
psql -U postgres
Some interesting flags (to see all, use -h or --help depending on your psql version):
-E: will describe the underlaying queries of the\commands (cool for learning!)-l: psql will list all databases and then exit (useful if the user you connect with doesn’t has a default database, like at AWS RDS)
Most \d commands support additional param of __schema__.name__ and accept wildcards like *.*
\q: Quit/Exit\c __database__: Connect to a database\d __table__: Show table definition (columns, etc.) including triggers\d+ __table__: More detailed table definition including description and physical disk size\l: List databases\dy: List events\df: List functions\di: List indexes\dn: List schemas\dt *.*: List tables from all schemas (if*.*is omitted will only show SEARCH_PATH ones)\dT+: List all data types\dv: List views\dx: List all extensions installed\df+ __function__: Show function SQL code.\x: Pretty-format query results instead of the not-so-useful ASCII tables\copy (SELECT * FROM __table_name__) TO 'file_path_and_name.csv' WITH CSV: Export a table as CSV\des+: List all foreign servers\dE[S+]: List all foreign tables
User Related:
\du: List users\du __username__: List a username if present.create role __test1__: Create a role with an existing username.create role __test2__ noinherit login password __passsword__;: Create a role with username and password.set role __test__;: Change role for current session to__test__.grant __test2__ to __test1__;: Allow__test1__to set its role as__test2__.\deu+: List all user mapping on server
Managing Indexes
Create an index on c1 and c2 of the table t
CREATE INDEX idx_name
ON t(c1,c2);
Create a unique index on c3, c4 of the table t
CREATE UNIQUE INDEX idx_name
ON t(c3,c4);
Drop an index
DROP INDEX idx_name;
MongoDB: Shell JavaScript Operations Cheat Sheet
Modifying Data
Insert one row into a table
INSERT INTO t(column_list)
VALUES(value_list);
Insert multiple rows into a table
INSERT INTO t(column_list)
VALUES (value_list),
(value_list), ….;
Insert rows from t2 into t1
INSERT INTO t1(column_list)
SELECT column_list
FROM t2;
Update values in the column c1, c2 that match
the condition
UPDATE t SET c1 = new_value, c2 = new_value WHERE condition;
Delete all data in a table
DELETE FROM t;
Delete subset of rows in a table
DELETE FROM t WHERE condition;
MongoDB: Queries Cheat Sheet
Using SQL Constraints
Set c1 and c2 as a primary key
CREATE TABLE t(
c1 INT, c2 INT, c3 VARCHAR,
PRIMARY KEY (c1,c2)
);
Set c2 column as a foreign key
CREATE TABLE t1(
c1 INT PRIMARY KEY,
c2 INT,
FOREIGN KEY (c2) REFERENCES t2(c2)
);
Make the values in c1 and c2 unique
CREATE TABLE t(
c1 INT, c1 INT,
UNIQUE(c2,c3)
);
Ensure c1 > 0 and values in c1 >= c2
CREATE TABLE t(
c1 INT, c2 INT,
CHECK(c1> 0 AND c1 >= c2)
);
Set values in c2 column not NULL
CREATE TABLE t(
c1 INT PRIMARY KEY,
c2 VARCHAR NOT NULL
);
MongoDB Keyboard Shortcuts
Managing Tables
Create a new table with three columns
CREATE TABLE t (
id INT PRIMARY KEY,
name VARCHAR NOT NULL,
price INT DEFAULT 0
);
Delete the table from the database
DROP TABLE t ;
Add a new column to the table
ALTER TABLE t ADD column;
Drop column c from the table
ALTER TABLE t DROP COLUMN c ;
Add a constraint
ALTER TABLE t ADD constraint;
Drop a constraint
ALTER TABLE t DROP constraint;
Rename a table from t1 to t2
ALTER TABLE t1 RENAME TO t2
Rename column c1 to c2
ALTER TABLE t1 RENAME c1 TO c2 ;
Remove all data in a table
TRUNCATE TABLE t;
MongoDB: Commands Cheat Sheet
Start and stop the MongoDB Database:
sudo service mongod start
sudo service mongod stop
Access the MongoDB database using Shell:
mongo --host localhost:27017
Show all databases:
show dbs
Create a database, say, testdb; Switch to the database:
use testdb
Until a collection is created in a database, the database name is not listed as a result of execution of the command, “show dbs.”
Add a collection:
db.createCollection("user")Show all collections in a database; Execute the “use dbname” command to access the database before executing the command given below.
show collections
show tables
The following command also work:
db.getCollectionNames()
Insert a record in the collection; A record is inserted in the collection, “user.”
db.user.insert({"name": "Ajitesh Shukla", "location": "hyderabad", "username": "eajitesh"})Display list of records of a collection; “user” collection is used.
db.user.find()
db.user.find().pretty()
Display a list of records matching with value (s) of specific fields:
db.user.find({"username": "eajitesh"})db.user.find({"username": "eajitesh", "location": "hyderabad"})Drop the collection:
db.user.drop()
Create users in the database; The below command creates a user with username as “ajitesh” and having the role such as “readWrite” and “dbAdmin”
db.createUser({"user": "ajitesh", "pwd": "gurukul", "roles": ["readWrite", "dbAdmin"]})Show users; If executed without selecting a database, it displays all users along with database information.
show users
Login into the database with username and password:
mongo -u USERNAME -p PASSWORD --authenticationDatabase DATABASENAME
For user created in above command, the login command would look like the following:
mongo -u ajitesh -p gurukul --authenticationDatabase testdb
Using SQL Operators
Combine rows from two queries
SELECT c1, c2 FROM t1
UNION [ALL]
SELECT c1, c2 FROM t2;
Return the intersection of two queries
SELECT c1, c2 FROM t1
INTERSECT
SELECT c1, c2 FROM t2;
Subtract a result set from another result set
SELECT c1, c2 FROM t1
MINUS
SELECT c1, c2 FROM t2;
Query rows using pattern matching %, _
SELECT c1, c2 FROM t1
WHERE c1 [NOT] LIKE pattern;
Query rows in a list
SELECT c1, c2 FROM t
WHERE c1 [NOT] IN value_list;
Query rows between two values
SELECT c1, c2 FROM t
WHERE c1 BETWEEN low AND high;
Check if values in a table is NULL or not
SELECT c1, c2 FROM t
WHERE c1 IS [NOT] NULL;
Managing Views
Create a new view that consists of c1 and c2
CREATE VIEW v(c1,c2)
AS
SELECT c1, c2
FROM t;
Create a new view with check option
CREATE VIEW v(c1,c2)
AS
SELECT c1, c2
FROM t;
WITH [CASCADED | LOCAL] CHECK OPTION;
Create a recursive view
CREATE RECURSIVE VIEW v
AS
select-statement -- anchor part
UNION [ALL]
select-statement; -- recursive part
Create a temporary view
CREATE TEMPORARY VIEW v
AS
SELECT c1, c2
FROM t;
Delete a view
DROP VIEW view_name
Inner join t1 and t2
SELECT c1, c2
FROM t1
INNER JOIN t2 ON condition;
Left join t1 and t1
SELECT c1, c2
FROM t1
LEFT JOIN t2 ON condition;
Perform full outer join
SELECT c1, c2
FROM t1
FULL OUTER JOIN t2 ON condition;
Produce a Cartesian product of rows in tables
SELECT c1, c2
FROM t1
CROSS JOIN t2;
Join t1 to itself using INNER JOIN clause
SELECT c1, c2
FROM t1 A
INNER JOIN t2 B ON condition;
Querying Data From Table
Query data in columns c1, c2 from a table
SELECT c1, c2 FROM t;
Query all rows and columns from a table
SELECT * FROM t;
Query data and filter rows with a condition
SELECT c1, c2 FROM t WHERE condition;
Query distinct rows from a table
SELECT DISTINCT c1 FROM t WHERE condition;
Group rows using an aggregate function
SELECT c1, aggregate(c2) FROM t GROUP BY c1;
Filter groups using HAVING clause
SELECT c1, aggregate(c2)
FROM t
GROUP BY c1
HAVING condition;