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

PostgreSQL

SQLite