Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL Cheat Sheet

MySQL Cheat Sheet

Help with SQL commands to interact with a MySQL database

MySQL Locations

  • Mac /usr/local/mysql/bin
  • Windows /Program Files/MySQL/MySQL version/bin
  • Xampp /xampp/mysql/bin

Add mysql to your PATH

# Current Session
export PATH=${PATH}:/usr/local/mysql/bin
# Permanantly
echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> ~/.bash_profile

On Windows - https://www.qualitestgroup.com/resources/knowledge-center/how-to-guide/add-mysql-path-windows/

Login

mysql -u root -p

Show Users

SELECT User, Host FROM mysql.user;

Create User

CREATE USER 'someuser'@'localhost' IDENTIFIED BY 'somepassword';

Grant All Priveleges On All Databases

GRANT ALL PRIVILEGES ON * . * TO 'someuser'@'localhost';
FLUSH PRIVILEGES;

Show Grants

SHOW GRANTS FOR 'someuser'@'localhost';

Remove Grants

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'someuser'@'localhost';

Delete User

DROP USER 'someuser'@'localhost';

Exit

exit;

Show Databases

SHOW DATABASES

Create Database

CREATE DATABASE acme;

Delete Database

DROP DATABASE acme;

Select Database

USE acme;

Create Table

CREATE TABLE users(
id INT AUTO_INCREMENT,
   first_name VARCHAR(100),
   last_name VARCHAR(100),
   email VARCHAR(50),
   password VARCHAR(20),
   location VARCHAR(100),
   dept VARCHAR(100),
   is_admin TINYINT(1),
   register_date DATETIME,
   PRIMARY KEY(id)
);

Delete / Drop Table

DROP TABLE tablename;

Show Tables

SHOW TABLES;

Insert Row / Record

INSERT INTO users (first_name, last_name, email, password, location, dept, is_admin, register_date) values ('Brad', 'Traversy', 'brad@gmail.com', '123456','Massachusetts', 'development', 1, now());

Insert Multiple Rows

INSERT INTO users (first_name, last_name, email, password, location, dept,  is_admin, register_date) values ('Fred', 'Smith', 'fred@gmail.com', '123456', 'New York', 'design', 0, now()), ('Sara', 'Watson', 'sara@gmail.com', '123456', 'New York', 'design', 0, now()),('Will', 'Jackson', 'will@yahoo.com', '123456', 'Rhode Island', 'development', 1, now()),('Paula', 'Johnson', 'paula@yahoo.com', '123456', 'Massachusetts', 'sales', 0, now()),('Tom', 'Spears', 'tom@yahoo.com', '123456', 'Massachusetts', 'sales', 0, now());

Select

SELECT * FROM users;
SELECT first_name, last_name FROM users;

Where Clause

SELECT * FROM users WHERE location='Massachusetts';
SELECT * FROM users WHERE location='Massachusetts' AND dept='sales';
SELECT * FROM users WHERE is_admin = 1;
SELECT * FROM users WHERE is_admin > 0;

Delete Row

DELETE FROM users WHERE id = 6;

Update Row

UPDATE users SET email = 'freddy@gmail.com' WHERE id = 2;

Add New Column

ALTER TABLE users ADD age VARCHAR(3);

Modify Column

ALTER TABLE users MODIFY COLUMN age INT(3);

Order By (Sort)

SELECT * FROM users ORDER BY last_name ASC;
SELECT * FROM users ORDER BY last_name DESC;

Concatenate Columns

SELECT CONCAT(first_name, ' ', last_name) AS 'Name', dept FROM users;

Select Distinct Rows

SELECT DISTINCT location FROM users;

Between (Select Range)

SELECT * FROM users WHERE age BETWEEN 20 AND 25;

Like (Searching)

SELECT * FROM users WHERE dept LIKE 'd%';
SELECT * FROM users WHERE dept LIKE 'dev%';
SELECT * FROM users WHERE dept LIKE '%t';
SELECT * FROM users WHERE dept LIKE '%e%';

Not Like

SELECT * FROM users WHERE dept NOT LIKE 'd%';

IN

SELECT * FROM users WHERE dept IN ('design', 'sales');

Create & Remove Index

CREATE INDEX LIndex On users(location);
DROP INDEX LIndex ON users;

New Table With Foreign Key (Posts)

CREATE TABLE posts(
id INT AUTO_INCREMENT,
   user_id INT,
   title VARCHAR(100),
   body TEXT,
   publish_date DATETIME DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY(id),
   FOREIGN KEY (user_id) REFERENCES users(id)
);

Add Data to Posts Table

INSERT INTO posts(user_id, title, body) VALUES (1, 'Post One', 'This is post one'),(3, 'Post Two', 'This is post two'),(1, 'Post Three', 'This is post three'),(2, 'Post Four', 'This is post four'),(5, 'Post Five', 'This is post five'),(4, 'Post Six', 'This is post six'),(2, 'Post Seven', 'This is post seven'),(1, 'Post Eight', 'This is post eight'),(3, 'Post Nine', 'This is post none'),(4, 'Post Ten', 'This is post ten');

INNER JOIN

SELECT
  users.first_name,
  users.last_name,
  posts.title,
  posts.publish_date
FROM users
INNER JOIN posts
ON users.id = posts.user_id
ORDER BY posts.title;

New Table With 2 Foriegn Keys

CREATE TABLE comments(
	id INT AUTO_INCREMENT,
    post_id INT,
    user_id INT,
    body TEXT,
    publish_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(id),
    FOREIGN KEY(user_id) references users(id),
    FOREIGN KEY(post_id) references posts(id)
);

Add Data to Comments Table

INSERT INTO comments(post_id, user_id, body) VALUES (1, 3, 'This is comment one'),(2, 1, 'This is comment two'),(5, 3, 'This is comment three'),(2, 4, 'This is comment four'),(1, 2, 'This is comment five'),(3, 1, 'This is comment six'),(3, 2, 'This is comment six'),(5, 4, 'This is comment seven'),(2, 3, 'This is comment seven');

Left Join

SELECT
comments.body,
posts.title
FROM comments
LEFT JOIN posts ON posts.id = comments.post_id
ORDER BY posts.title;

Join Multiple Tables

SELECT
comments.body,
posts.title,
users.first_name,
users.last_name
FROM comments
INNER JOIN posts on posts.id = comments.post_id
INNER JOIN users on users.id = comments.user_id
ORDER BY posts.title;

Aggregate Functions

SELECT COUNT(id) FROM users;
SELECT MAX(age) FROM users;
SELECT MIN(age) FROM users;
SELECT SUM(age) FROM users;
SELECT UCASE(first_name), LCASE(last_name) FROM users;

Group By

SELECT age, COUNT(age) FROM users GROUP BY age;
SELECT age, COUNT(age) FROM users WHERE age > 20 GROUP BY age;
SELECT age, COUNT(age) FROM users GROUP BY age HAVING count(age) >=2;
@maylilly
Copy link

maylilly commented Jul 21, 2021

This is so helpful , thanks alot Brad! blessings to you!

@suzuhaa
Copy link

suzuhaa commented Aug 14, 2021

Just finished reading. Nice work!

@mourice-oduor
Copy link

mourice-oduor commented Aug 14, 2021

@justintoronto
Copy link

justintoronto commented Aug 14, 2021

Great. Thank you. All works on Linux MySQL.

@ChinemeremUgagu
Copy link

ChinemeremUgagu commented Aug 16, 2021

Thanks

@mefercs
Copy link

mefercs commented Aug 18, 2021

kisses on whatever you want Brad😳

@sanphem
Copy link

sanphem commented Aug 19, 2021

Thank you so much, Brad.

@Rajarshi1001
Copy link

Rajarshi1001 commented Sep 3, 2021

Thanks!!

@pjguitar15
Copy link

pjguitar15 commented Oct 8, 2021

SHOW COLUMNS command is lacking here.

@erinoggz
Copy link

erinoggz commented Oct 8, 2021

@Jakaza
Copy link

Jakaza commented Oct 12, 2021

Thank you very much.. Your work is clean and clear

@Juliana-crsp
Copy link

Juliana-crsp commented Nov 23, 2021

This helped so much!!! Thank you!

@Engeniusam
Copy link

Engeniusam commented Jan 29, 2022

Very helpful.Thank you Sir.

@Abrish-b
Copy link

Abrish-b commented Feb 4, 2022

much help thank you, please add more if you can?

@mohrizkifajar
Copy link

mohrizkifajar commented Feb 12, 2022

Really help.

@markfire326
Copy link

markfire326 commented Mar 1, 2022

Thanks for this, really helpful

@tombstone-10
Copy link

tombstone-10 commented Mar 15, 2022

this is so helpful, what my lab teacher has been trying to do for a month; you have explained it pretty well in just less than an hour!
your videos have been always so helpful!

@kynart
Copy link

kynart commented Apr 9, 2022

I want to thank you for these cheat sheet I have been using these every time T need to find a reference for a mysql project.

@tmalikJK14
Copy link

tmalikJK14 commented Apr 10, 2022

Thanks brother

@Kevin-Khachi
Copy link

Kevin-Khachi commented Apr 17, 2022

I LOVE YOU BRAD!

@meshack-ai
Copy link

meshack-ai commented Apr 25, 2022

This is awesome.

@chalewt
Copy link

chalewt commented Apr 27, 2022

Thank you! It helped me a lot.

@Vyom-Yadav
Copy link

Vyom-Yadav commented May 2, 2022

Thanks a ton.

@EngineerWabuko
Copy link

EngineerWabuko commented May 13, 2022

wow wow, splendid

@ltaaf
Copy link

ltaaf commented May 16, 2022

merci bien

@Say-D
Copy link

Say-D commented May 22, 2022

How to Download this?

@bogalenigussie
Copy link

bogalenigussie commented May 29, 2022

Great Material! thank you

@tristak17
Copy link

tristak17 commented Jun 3, 2022

Great material!
I would like to add one missing command,
To Select a Database:
USE database_name;

@Matu-sunuwawa
Copy link

Matu-sunuwawa commented Jun 7, 2022

just i wish your youtube follower gonna 200 million and i want to say, you are the best......yeah one of the best .....there is nothing to fit your performance....just god bless your health,family,future destiny......

@waseem-arif
Copy link

waseem-arif commented Jun 20, 2022

Thanks man. its helpful.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment