# Database Cheat Sheets



{% tabs %}
{% tab title="MS SQL " %}
* <mark style="color:yellow;">Connection Commands</mark>
  * Instance Name - `sqlcmd -S serverName\InstanceName` &#x20;
  * IP Address - `sqlcmd -S IP_Add\InstanceName`&#x20;
  * IP Address/Port - `sqlcmd -S IP_Add,Port_Num`
* <mark style="color:yellow;">Useful Commands</mark> -&#x20;
  * _Display All Databases_ - `SELECT * FROM sys.databases;`&#x20;
  * _Use a Database_ - `USE db_name;`
  * _Display Tables_ - `SELECT * FROM sys.tables;`
  * _Get Table Information_ - `exec sp_help 'table_name';`
  * _Delete Database_ - `DROP DATABASE db_name;`&#x20;
  * _View Database Information_ - `sp_who` ; provides information about the current users, sessions, and processes for an SQL DB Engine instance.
* <mark style="color:yellow;">SELECT Statement</mark> -&#x20;
  * `SELECT col1, col2, coln FROM table;` &#x20;
  * `SELECT col1, col2, coln FROM table WHERE condition;`
  * `SELECT col1, col2, coln FROM table ORDER BY col1 DESC;`
  * `SELECT col1, COUNT(*) FROM table GROUP BY col1;`
  * `SELECT col1, COUNT(*) FROM table GROUP by col1 HAVING condition;`&#x20;
* <mark style="color:yellow;">JOIN Commands</mark>
  * **INNER JOIN** - Returns rows with matching values in both tables. `SELECT * FROM table1 INNER JOIN table2 ON table1.col=table2.col;`
  * **LEFT JOIN/LEFT OUTER JOIN** - Returns all rows from the left table (first table) and the matching rows from the right table (second table). `SELECT * FROM table1 LEFT JOIN table2 on table1.col=table2.col;`
  * **RIGHT JOIN/RIGHT OUTER JOIN** - Returns all rows from the right table (second table) and the matching rows from the left table (first table). `SELECT * FROM table1 RIGHT JOIN table2 ON table1.col=table2.col;`&#x20;
  * **FULL JOIN/FULL OUTER JOIN** - Returns all rows when there is a match in either the left or right table. `SELECT * FROM table1 FULL JOIN table2 ON table1.col=table2.col;`&#x20;
  * **CROSS JOIN** - Combines every row from the first table with every row from the second table, creating a 'Cartesian' product. `SELECT * FROM table1 t1, table2 t2 WHERE t1.col=t2.col;`&#x20;
* <mark style="color:yellow;">Data Manipulation Commands</mark> -&#x20;
  * **INSERT** - `INSERT INTO table (col1, col2, coln) VALUES (value1, value2, valuen);`&#x20;
  * **UPDATE** - `UPDATE table SET col1=val1, col2=val2, coln=valn WHERE condition;`&#x20;
  * **DELETE** - `DELETE FROM table WHERE condition;`
* <mark style="color:yellow;">Data Definition Commands</mark> -
  * **CREATE** - Create a new database or object - table, index, view, or stored procedure. `CREATE TABLE table_name (col1 datatype, col2 datatype, col_n datatype);` For example: `CREATE TABLE departments (dpt_id INT PRIMARY KEY, dpt_name VARCHAR(50), dpt_code VARCHAR(6));` &#x20;
  * **ALTER** - Add, delete, or modify columns in an existing table. `ALTER TABLE table_name ADD col_name datatype;` For example:&#x20;
    * `ALTER TABLE departments ADD dpt_abr VARCHAR(10);`&#x20;
    * `ALTER TABLE table_name DROP COLUMN column_name;`
  * **DROP** - Delete ('drop') an existing table from a database. `DROP TABLE table_name;`&#x20;
  * **TRUNCATE** - Delete data inside a table but not the table itself. `TRUNCATE TABLE table_name;`&#x20;
* <mark style="color:yellow;">Data Control Commands</mark> -&#x20;
  * **GRANT** - Give specific privileges to users or roles. `GRANT SELECT, INSERT ON employees TO 'User Name';`&#x20;
  * **REVOKE** - Take away privileges from users or roles. `REVOKE SELECT, INSERT ON departments FROM 'User Name';`
{% endtab %}

{% tab title="MySQL/MariaDB" %}
* <mark style="color:yellow;">Connection Commands</mark>
  * `mysql -u username -p`
  * `mysql -h host_name -u username -p [db_name]`
* <mark style="color:yellow;">Useful Commands</mark> -
  * _Display Databases_ - `SHOW DATABASES;`&#x20;
  * `USE db_name;`
  * _Display Tables_ - `SHOW TABLES;`
  * _Table Information_ - `SHOW FIELDS FROM table_name` or `DESCRIBE table_name`
  * _Display DB Information_ - `SHOW PROCESSLIST;`&#x20;
  * _Display Roles/Users_ - `SELECT User, Host FROM mysql.user;`&#x20;
  * _Display Locked/Password Expired Users_ - `SELECT user, Host, Account_locked, password_expired FROM mysql.user;`
  * _Display Logged In Users_ - `SELECT user();`&#x20;
  * _Display User Privileges_ - `SHOW GRANTS FOR user_name;`
  * _Display Logged In DB Users (Root Permission)_ - `SELECT user, host, db, command FROM information_schema.processlist;`&#x20;
  * _Backup DB to SQL File_ - `mysqldump -u username -p db_name > db_bkup_file_name.sql`&#x20;
  * _Restore DB From SQL File_ - `mysql -u username -p db_name < db_bkup_file_name.sql`
  * _Repair Tables_ - `mysqlcheck --all-databases;` or `mysqlcheck --all-databases --fast;`
* <mark style="color:yellow;">Select Commands</mark> -&#x20;
  * `SELECT * FROM table;`&#x20;
  * `SELECT * FROM table1, table2;`
  * `SELECT col_1, col_2, col_n FROM table1, table2;`
  * `SELECT col_1, col_n FROM table WHERE condition;`
  * `SELECT col_1, col_n FROM table WHERE condition GROUP BY col_1;`
  * `SELECT col_1, col_n FROM table WHERE condition GROUP BY col_1 HAVING condtion;`
  * `SELECT col_1, col_n FROM table WHERE condition ORDER BY col_1 DESC;`
  * `SELECT DISTINCT col_1, col_2 FROM table;`
* <mark style="color:yellow;">Conditions</mark> -&#x20;
  * column = value
  * column <> value
  * column LIKE 'value \_ %'
  * column IS NULL
  * column IS NOT NULL
  * column IS IN (value\_1, value\_2)
  * column IS NOT IN (value\_1, value\_2)
  * condition\_1 AND condition\_2
  * condition\_1 OR condition\_2
* <mark style="color:yellow;">Reset Root Password</mark>:
  * `/etc/init.d/mysql stop`
  * `mysql_safe --skip-grant-tables`
  * (Different terminal) `mysql`
    * `UPDATE mysql.user SET password=PASSWORD('new_pass') WHERE user ='root';`
  * (Return to first terminal) `/etc/init.d/mysql start`
* <mark style="color:yellow;">Data Definition Commands</mark>
  * `CREATE DATABASE db_name;`
  * `DROP DATABASE db_name;`
  * `ALTER TABLE table_name ADD column_name;`
  * `ALTER TABLE table_name DROP COLUMN column_name;`
  * `ALTER TABLE old_name RENAME TO new_name;`&#x20;
  * `ALTER TABLE table_name RENAME old_col_name TO new_col_name;` &#x20;
  * `DROP TABLE table_name;`&#x20;
  * `TRUNCATE TABLE table_name;`&#x20;
* <mark style="color:yellow;">Data Control Commands</mark>
  * `CREATE USER 'user_name'@localhost;`
  * `DROP USER 'user_name'@localhost;`&#x20;
  * `GRANT ALL PRIVILEGES ON db_name.* TO 'user_name'@localhost IDENTIFIED BY 'password';`
  * `GRANT SELECT, INSERT, DELETE ON db_name.* TO 'user'@'localhost' IDENTIFIED BY 'password';`
  * `REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host';`  - all permissions
  * `FLUSH PRIVILEGES;`&#x20;
  * `SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass');`
{% endtab %}

{% tab title="PostgreSQL" %}
* <mark style="color:yellow;">Useful Commands</mark> -&#x20;
  * _List Databases_ - `\l`
  * _Connect to Database_ - `\c database_name`
  * _List Current DB Tables_ - `dt`&#x20;
  * _Display Table Schema_ - `\d table_name`&#x20;
  * _Display All Roles/Users_ - `\du`&#x20;
  * _Display Last Command_ - `\e`&#x20;
  * _Execute Commands From File_ - `\i file_name`&#x20;
  * _Exit PostgreSQL Prompt_ - `\q`
{% endtab %}

{% tab title="SQLite" %}
* <mark style="color:yellow;">Useful Commands</mark> -&#x20;
  * _Display Databases_ - `.databases`
  * _Display Tables_ - `.tables`
  * _Display Table Schema_ - `.schema table_name`
  * _Open New or Existing DB File_ - `.open file_name`
  * _Save Current DB to File_ - `.save file_name`&#x20;
  * _Exit SQLite Prompt_ - `.quit`&#x20;
  * _Execute Linux Command_ - `.system ls -l`
* <mark style="color:yellow;">Select Commands</mark>
  * `SELECT * FROM table;`
  * `SELECT col_1, col_2 FROM table;`
  * `SELECT * FROM table ORDER BY column DESC;`
  * `SELECT * FROM table LIMIT 10;`
* J<mark style="color:yellow;">oin Commands</mark>
  * _Inner Join_ - `SELECT * FROM table_1 INNER JOIN table_2 ON table_1.col=table_2.col;`
  * _Left Join_ - `SELECT * FROM table_1 LEFT JOIN ON table_1.col=table_2.col;`&#x20;
  * _Cross Join_ - `SELECT * FROM table_1 CROSS JOIN table_2;`&#x20;
* <mark style="color:yellow;">Data Manipulation Commands</mark>&#x20;
  * `INSERT INTO table (col_1, col_2) VALUES ('value_1', value_2);`
  * `UPDATE table SET column_1=value_1 WHERE column_2='value_2';`
  * `DELETE FROM table WHERE column_1='value_1';`
* <mark style="color:yellow;">Data Definition Commands</mark>
  * Create Database - `.open db_filename.db`
  * Create Table & Define Fields - `CREATE TABLE IF NOT EXISTS table_name (col1 INT PRIMARY KEY, col2 VARCHAR(20) NOT NULL);`
  * Add Column to Table - `ALTER TABLE table_name ADD column_name VARCHAR(15);`
  * Delete a Table - `DROP TABLE table_name;`
  * Rename a Table - `ALTER TABLE old_table_name RENAME TO new_table_name;`
{% endtab %}
{% endtabs %}

