A list of MySQL and SQL commands

mysqlcheatsheet.md

MySQL Cheat Sheet

Table of contents

• Terminal
  • Users and Permissions
  • Database Operations
  • Metadata
• Table Manipulations
  • Creating
  • Dropping
  • Altering
• CRUD Operations
  • Insert
  • Select
    • Joins
    • Unions
  • Update
  • Delete
• Useful Tips and Recommandations
  • Column Types
  • Security
  • Prepare your Queries
  • Transaction
  • Consider using views
• Resources

Terminal / Command Line

Accessing the MySQL command line from the terminal

$ mysql -u root -p

---

Users and Permissions

---

Database Operations

mysql> CREATE DATABASE [IF NOT EXISTS] database_name; 
# Creates a database as discribed by database_name

mysql> DROP DATABASE database_name; 
# Deletes a database

mysql> USE database_name; 
# selects a database

---

Metadata

mysql> SHOW DATABASES; 
# Lists all available databases

mysql> SHOW tables; 
# Shows database tables

mysql> DESCRIBE table_name; 
# Describes a table format

Tables

---

Creating

CREATE TABLE [IF NOT EXISTS] table_name (column1 type, column2 type, ...);

mysql> CREATE TABLE table_name (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    username VARCHAR(20),
    email VARCHAR(60) NOT NULL,
    pass char(60), 
    signup_date DATE
); # Creates a new table within the database being used

---

Dropping

mysql> DROP TABLE table_name

---

Altering

mysql> ALTER TABLE table_name ADD confirmed TINYINT(1); 
# Adds a new column called 'confirmed' at the end of the table

mysql> ALTER TABLE table_name ADD confirmed TINYINT(1) AFTER pass; 
# The new column goes after the column pass

mysql> ALTER TABLE table_name ADD INDEX index_name (column_name);
# Indexes a column in the table as specified by column_name

mysql> ALTER TABLE table_name DROP signup_date; 
# Deletes the column signup_date fromt the table

Altering multiple columns

mysql> ALTER TABLE table_name 
    ADD confirmed TINYINT(1), # Adding confirmed coloumn
    DROP signup_date, # Dropping signup_date column
    CHANGE id user_id MEDIUMINT; # Renaming column id to user_id and changing type definition

CRUD Operations

---

Inserts

Syntax*

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Example

INSERT INTO `table_name` -- inserts information into each column
(`id`, `username`, `email`, `pass`, `signup_date`) 
VALUES (NULL, "john", "[email protected]", PASSWORD("doesntexistJOHN"), CURDATE());

---

Selects

Syntax*

Example

 SELECT 

---

Updates

Syntax*

UPDATE table_name SET column_name = 'value1' WHERE column1 = 'column_value';

Examples

UPDATE `table_name` 
SET `username` = 'johndoe' 
WHERE `email` = '[email protected]';

---

Deletes

Syntax*

DELETE FROM table_name WHERE column_name = 'column_value';

Examples

DELETE FROM `table_name` 
WHERE `email` = '[email protected]';

Useful Tips and Recommandations

---

Column Types

Column Name	Column Type
id	MEDIUMINT
first_name	VARCHAR(20)
last_name	VARCHAR(40)
email	VARCHAR(60)
pass	CHAR(40)
registration_date	DATETIME

---

Security

---

Prepare your statements

Prepared statements can provide un extra layer of protection againg SQL injections

PREPARE req FROM 'SELECT * FROM table_name WHERE email = "[email protected]" '; -- Prepares a statement for execution

EXECUTE req; -- Prepared statement execution

DEALLOCATE [DROP] PREPARE req; -- Removes a prepared statement from the session 

PREPARE req FROM 'SELECT * FROM table_name WHERE email = ?'; -- with a string liberal

SET @email = '[email protected]'; -- Creates a variable that stores user email

EXECUTE req USING @email; -- Executing the statement and suppling text as email variable

---

Use Transactions

When using the MySQL command line you might want to use transactions

mysql> START TRANSACTION;
# Starts a new transaction, staging area

mysql> SAVEPOINT identifier_name;
# Creates a transaction savepoint (snapshot), identical identifier will be overwritten

mysql> ROLLBACK;
# Rolls back the current transaction, to where we started it, cancelling all the changes

mysql> ROLLBACK to point_name;
# Rolls back to a specific savepoint created with SAVEPOINT. facultatif : Executing ROLLBACK without a savepoint deletes all savepoints
mysql> COMMIT;
# Commits the current transaction and persists the changes

Statements that cannot be rolled back

Implicit Commit

Read more...

---

Consider using views

Syntax*

CREATE [REPLACE] VIEW database_name.view_name AS SELECT * FROM table_name

Example

CREATE VIEW `view_name` AS
SELECT `username`, `email` 
FROM `table_name`
WHERE email = '[email protected]';

Resources

MySQL Reference Manual

sql.sh 🇫🇷

syntax* -> not the official syntax