Database Cheat Sheets

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

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

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

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;