Database Cheat Sheets

MS SQL

• Connection Commands

  • Instance Name - sqlcmd -S serverName\InstanceName

  • IP Address - sqlcmd -S IP_Add\InstanceName

  • IP Address/Port - sqlcmd -S IP_Add,Port_Num

• Useful Commands -

  • Display All Databases - SELECT * FROM sys.databases;

  • 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;

  • View Database Information - sp_who ; provides information about the current users, sessions, and processes for an SQL DB Engine instance.

• SELECT Statement -

  • SELECT col1, col2, coln FROM table;

  • 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;

• JOIN Commands

  • 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;

  • 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;

  • 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;

• Data Manipulation Commands -

  • INSERT - INSERT INTO table (col1, col2, coln) VALUES (value1, value2, valuen);

  • UPDATE - UPDATE table SET col1=val1, col2=val2, coln=valn WHERE condition;

  • DELETE - DELETE FROM table WHERE condition;

• Data Definition Commands -

  • 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));

  • ALTER - Add, delete, or modify columns in an existing table. ALTER TABLE table_name ADD col_name datatype; For example:

    • ALTER TABLE departments ADD dpt_abr VARCHAR(10);

    • ALTER TABLE table_name DROP COLUMN column_name;

  • DROP - Delete ('drop') an existing table from a database. DROP TABLE table_name;

  • TRUNCATE - Delete data inside a table but not the table itself. TRUNCATE TABLE table_name;

• Data Control Commands -

  • GRANT - Give specific privileges to users or roles. GRANT SELECT, INSERT ON employees TO 'User Name';

  • REVOKE - Take away privileges from users or roles. REVOKE SELECT, INSERT ON departments FROM 'User Name';

MySQL/MariaDB

• Connection Commands

  • mysql -u username -p

  • mysql -h host_name -u username -p [db_name]

• Useful Commands -

  • Display Databases - SHOW DATABASES;

  • USE db_name;

  • Display Tables - SHOW TABLES;

  • Table Information - SHOW FIELDS FROM table_name or DESCRIBE table_name

  • Display DB Information - SHOW PROCESSLIST;

  • Display Roles/Users - SELECT User, Host FROM mysql.user;

  • Display Locked/Password Expired Users - SELECT user, Host, Account_locked, password_expired FROM mysql.user;

  • Display Logged In Users - SELECT user();

  • Display User Privileges - SHOW GRANTS FOR user_name;

  • Display Logged In DB Users (Root Permission) - SELECT user, host, db, command FROM information_schema.processlist;

  • Backup DB to SQL File - mysqldump -u username -p db_name > db_bkup_file_name.sql

  • 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;

• Select Commands -

  • SELECT * FROM table;

  • 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;

• Conditions -

  • 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

• Reset Root Password:

  • /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

• Data Definition Commands

  • 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;

  • ALTER TABLE table_name RENAME old_col_name TO new_col_name;

  • DROP TABLE table_name;

  • TRUNCATE TABLE table_name;

• Data Control Commands

  • CREATE USER 'user_name'@localhost;

  • DROP USER 'user_name'@localhost;

  • 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;

  • SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass');

PostgreSQL

• Useful Commands -

  • List Databases - \l

  • Connect to Database - \c database_name

  • List Current DB Tables - dt

  • Display Table Schema - \d table_name

  • Display All Roles/Users - \du

  • Display Last Command - \e

  • Execute Commands From File - \i file_name

  • Exit PostgreSQL Prompt - \q

SQLite

• Useful Commands -

  • 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

  • Exit SQLite Prompt - .quit

  • Execute Linux Command - .system ls -l

• Select Commands

  • SELECT * FROM table;

  • SELECT col_1, col_2 FROM table;

  • SELECT * FROM table ORDER BY column DESC;

  • SELECT * FROM table LIMIT 10;

• Join Commands

  • 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;

  • Cross Join - SELECT * FROM table_1 CROSS JOIN table_2;

• Data Manipulation Commands

  • 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';

• Data Definition Commands

  • 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;