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;
@Zed-M

This comment has been minimized.

Copy link

@Zed-M Zed-M commented Apr 23, 2019

Thank you brad

@A-Kiwams

This comment has been minimized.

Copy link

@A-Kiwams A-Kiwams commented Apr 23, 2019

Just started going through. Thanks

@ridl27

This comment has been minimized.

Copy link

@ridl27 ridl27 commented Apr 23, 2019

Very useful! thx <3

@cyrilechristian

This comment has been minimized.

Copy link

@cyrilechristian cyrilechristian commented Apr 24, 2019

You are awesome! Keep doing what you are doing for the industry

@saddamcrr7

This comment has been minimized.

Copy link

@saddamcrr7 saddamcrr7 commented Apr 24, 2019

Thanks a lot brad

@Fandry01

This comment has been minimized.

Copy link

@Fandry01 Fandry01 commented Apr 25, 2019

Very useful!

@rohankewal

This comment has been minimized.

Copy link

@rohankewal rohankewal commented Apr 26, 2019

Very helpful! Thanks brad @bradtraversy

@sohel1999

This comment has been minimized.

Copy link

@sohel1999 sohel1999 commented Apr 27, 2019

very useful!

@zhouyang159

This comment has been minimized.

Copy link

@zhouyang159 zhouyang159 commented Apr 27, 2019

xiexie ni de crash course :) (someone from China)

@YuanruiZhang

This comment has been minimized.

Copy link

@YuanruiZhang YuanruiZhang commented Apr 29, 2019

Thanks alot Brad!

@Foss-Bee

This comment has been minimized.

Copy link

@Foss-Bee Foss-Bee commented May 4, 2019

Thanks very much @bradtraversy. You always make me love programming. Keep up with the good work.

@xelinel32

This comment has been minimized.

Copy link

@xelinel32 xelinel32 commented May 6, 2019

thx, man)

@kailichou

This comment has been minimized.

Copy link

@kailichou kailichou commented May 6, 2019

Hi, there
is there anyone also struggling to add mysql to the path?
Screenshot 2019-05-06 at 15 26 38

@LinmeiJ

This comment has been minimized.

Copy link

@LinmeiJ LinmeiJ commented May 20, 2019

THANK YOU SO MUCH brad! very helpful!

@mymmoonoa

This comment has been minimized.

Copy link

@mymmoonoa mymmoonoa commented Jun 9, 2019

nice work!

@abdalahshaban

This comment has been minimized.

Copy link

@abdalahshaban abdalahshaban commented Jun 12, 2019

nice work

@zaki1001

This comment has been minimized.

Copy link

@zaki1001 zaki1001 commented Jun 12, 2019

thanks a ton mate

@ball97

This comment has been minimized.

Copy link

@ball97 ball97 commented Jun 18, 2019

Thank you Brad. You are the best !!

@Ajmal0197

This comment has been minimized.

Copy link

@Ajmal0197 Ajmal0197 commented Jun 18, 2019

Thanks master

@ghost

This comment has been minimized.

Copy link

@ghost ghost commented Jun 20, 2019

Thanks

@MbuguaCaleb

This comment has been minimized.

Copy link

@MbuguaCaleb MbuguaCaleb commented Jun 25, 2019

Thank you very Much Brad!!

@samdsg

This comment has been minimized.

Copy link

@samdsg samdsg commented Jun 28, 2019

God Bless you Mr. Brad

@samdsg

This comment has been minimized.

Copy link

@samdsg samdsg commented Jun 28, 2019

I have a question Mr. Brad.

$sql = 'SELECT c.categoryName as catname, m.img as img, p.id, p.product_name, p.product_desc FROM ' . $this->products . ' p RIGHT JOIN images m ON m.product_id = p.id LEFT JOIN categories c ON p.product_category = c.slug ORDER BY p.id DESC ';

i want to limit the image to only one image.. the image is a bucket with not less than four images.

I will really appreciate your response to me shortly.

@imaginaries

This comment has been minimized.

Copy link

@imaginaries imaginaries commented Jun 30, 2019

Very useful, thanks.

@AbbottWilloughby

This comment has been minimized.

Copy link

@AbbottWilloughby AbbottWilloughby commented Jul 9, 2019

Super useful, thank you!

@Zhixin-Jack-Wang

This comment has been minimized.

Copy link

@Zhixin-Jack-Wang Zhixin-Jack-Wang commented Jul 16, 2019

GOOD WORK, THANK YOU

@YosiLeibman

This comment has been minimized.

Copy link

@YosiLeibman YosiLeibman commented Jul 17, 2019

I have a question Mr. Brad.

$sql = 'SELECT c.categoryName as catname, m.img as img, p.id, p.product_name, p.product_desc FROM ' . $this->products . ' p RIGHT JOIN images m ON m.product_id = p.id LEFT JOIN categories c ON p.product_category = c.slug ORDER BY p.id DESC ';

i want to limit the image to only one image.. the image is a bucket with not less than four images.

I will really appreciate your response to me shortly.

there's a LIMIT clause. here you can read about.

@sukhdevVyas

This comment has been minimized.

Copy link

@sukhdevVyas sukhdevVyas commented Aug 16, 2019

It was a great refresher. Thank you :-) Expecting more videos or cheat seats for mySql administration.

@maggiesb

This comment has been minimized.

Copy link

@maggiesb maggiesb commented Sep 10, 2019

This is awesome! Thanks so much, Brad.

@ghost

This comment has been minimized.

Copy link

@ghost ghost commented Sep 12, 2019

Thank you for this , Sir!

@chrisvaughan83

This comment has been minimized.

Copy link

@chrisvaughan83 chrisvaughan83 commented Sep 23, 2019

Thanks! this is super helpful!

@lin3yx

This comment has been minimized.

Copy link

@lin3yx lin3yx commented Oct 20, 2019

Thank you so much for your wonderful work!

@domnet-webdesign

This comment has been minimized.

Copy link

@domnet-webdesign domnet-webdesign commented Nov 7, 2019

Thanks you very much for this sample documentation

@mouadTaoussi

This comment has been minimized.

Copy link

@mouadTaoussi mouadTaoussi commented Nov 7, 2019

Thank You Brad Traversy !
You're Work Apperciated Honestly!
Congratulations 900k Subscriber!

@vTechSavyy

This comment has been minimized.

Copy link

@vTechSavyy vTechSavyy commented Nov 18, 2019

Thank you Brad! This is really useful. Helped me a lot to learn MySQL.

@chuckchukwuma

This comment has been minimized.

Copy link

@chuckchukwuma chuckchukwuma commented Nov 22, 2019

Hello Brad. You have so much demystified MySQL and made it look learning 1+1=2. Great Job!

@nikkhilchauhan

This comment has been minimized.

Copy link

@nikkhilchauhan nikkhilchauhan commented Nov 23, 2019

Great

@salah3x

This comment has been minimized.

Copy link

@salah3x salah3x commented Nov 29, 2019

Thanks Brad!

@jayDevaney

This comment has been minimized.

Copy link

@jayDevaney jayDevaney commented Dec 9, 2019

Many thanks Brad! Great tutorial.

@geekvert

This comment has been minimized.

Copy link

@geekvert geekvert commented Dec 27, 2019

Thank you sir, it's great and helped me a lot. Please keep doing stuff like this.

@hh088

This comment has been minimized.

Copy link

@hh088 hh088 commented Dec 31, 2019

Thank you very much.

@KingAbesh

This comment has been minimized.

Copy link

@KingAbesh KingAbesh commented Jan 11, 2020

You have been far to kind and helpful, Brad. Your videos have made me a solid javascript developer and I recently just picked up your PHP course. Thank you for doing this. I am grateful

@milainmaroo

This comment has been minimized.

Copy link

@milainmaroo milainmaroo commented Jan 28, 2020

Very helpful..thank you very much!!

@czatarain

This comment has been minimized.

Copy link

@czatarain czatarain commented Feb 24, 2020

Thank you Brad.

@alexander-rusiecki

This comment has been minimized.

Copy link

@alexander-rusiecki alexander-rusiecki commented Mar 15, 2020

Thank You Brad!

@erinoggz

This comment has been minimized.

Copy link

@erinoggz erinoggz commented Mar 19, 2020

Thanks Man. Really appreciate...

@muhammadabir64

This comment has been minimized.

Copy link

@muhammadabir64 muhammadabir64 commented Mar 28, 2020

its very helpful......๐Ÿ˜„
thank you so much๐Ÿ™‚

@bougatsa

This comment has been minimized.

Copy link

@bougatsa bougatsa commented Apr 26, 2020

Thanks Brad! This is super helpful

@azuba1

This comment has been minimized.

Copy link

@azuba1 azuba1 commented Apr 27, 2020

Thank you very much my boss keep on the good works

@KunalKatiyar

This comment has been minimized.

Copy link

@KunalKatiyar KunalKatiyar commented Jun 2, 2020

Great Work! Helped a lot!

@abiaki997-tech

This comment has been minimized.

Copy link

@abiaki997-tech abiaki997-tech commented Jun 4, 2020

Thank you sir :)

@philsav

This comment has been minimized.

Copy link

@philsav philsav commented Jun 16, 2020

How do you order a query by date posted

@SinghiHarsh

This comment has been minimized.

Copy link

@SinghiHarsh SinghiHarsh commented Jul 6, 2020

Thank you!, This is very helpful :)

@Clinzho1996

This comment has been minimized.

Copy link

@Clinzho1996 Clinzho1996 commented Jul 14, 2020

thanks alot. this is absolute stunner

@OmRajpurkar

This comment has been minimized.

Copy link

@OmRajpurkar OmRajpurkar commented Jul 25, 2020

Thanks you! It was a great tutorial on YouTube๐Ÿ‘

@kingkongwebdev

This comment has been minimized.

Copy link

@kingkongwebdev kingkongwebdev commented Aug 11, 2020

thank you Brad.

@CHANG-CHING-CHUNG

This comment has been minimized.

Copy link

@CHANG-CHING-CHUNG CHANG-CHING-CHUNG commented Aug 20, 2020

Learning PHP and MySQL. Thanks for your work for this.

@AlyNeumann

This comment has been minimized.

Copy link

@AlyNeumann AlyNeumann commented Sep 15, 2020

Thank you so much Brad!

@LawrenceMadali

This comment has been minimized.

Copy link

@LawrenceMadali LawrenceMadali commented Oct 26, 2020

Thanks a lot!

@rajasivashankarjasthi

This comment has been minimized.

Copy link

@rajasivashankarjasthi rajasivashankarjasthi commented Nov 6, 2020

thanks pal , you save lot of time .

@yashish25

This comment has been minimized.

Copy link

@yashish25 yashish25 commented Nov 11, 2020

Thanks buddy!

@Temi-t

This comment has been minimized.

Copy link

@Temi-t Temi-t commented Dec 4, 2020

This was very helpful.

@abubakarMana01

This comment has been minimized.

Copy link

@abubakarMana01 abubakarMana01 commented Dec 10, 2020

That you master. Was really helpful

@markyoung010

This comment has been minimized.

Copy link

@markyoung010 markyoung010 commented Dec 17, 2020

Thanks! ๐Ÿ˜ƒ

@Qais-Haidari

This comment has been minimized.

Copy link

@Qais-Haidari Qais-Haidari commented Dec 30, 2020

many many thanks

@ben031

This comment has been minimized.

Copy link

@ben031 ben031 commented Jan 6, 2021

It is what i was looking for. awesome! ๐Ÿ‘ Thanks! ๐Ÿ˜Š

@rykumar13

This comment has been minimized.

Copy link

@rykumar13 rykumar13 commented Jan 13, 2021

thanks ๐Ÿ‘Œ

@rtheerdham

This comment has been minimized.

Copy link

@rtheerdham rtheerdham commented Jan 15, 2021

Thanks

@bharathjinka09

This comment has been minimized.

Copy link

@bharathjinka09 bharathjinka09 commented Jan 23, 2021

Thanks Brad ๐Ÿ˜€

@AhmedMaqbool

This comment has been minimized.

Copy link

@AhmedMaqbool AhmedMaqbool commented Jan 26, 2021

Thank You Brad sensei !!

@BasWorld

This comment has been minimized.

Copy link

@BasWorld BasWorld commented Jan 31, 2021

Thanks boss

@sameeruj

This comment has been minimized.

Copy link

@sameeruj sameeruj commented Feb 16, 2021

Thank you!

@nazish5590

This comment has been minimized.

Copy link

@nazish5590 nazish5590 commented Feb 20, 2021

Thank you so much sir from pakistan

@soufianekremcht

This comment has been minimized.

Copy link

@soufianekremcht soufianekremcht commented Feb 22, 2021

Thank you Brad For making this.

@madhumitathakur

This comment has been minimized.

Copy link

@madhumitathakur madhumitathakur commented Feb 28, 2021

Thanks a lot

@aayardev

This comment has been minimized.

Copy link

@aayardev aayardev commented Mar 9, 2021

Tks Brad !

@snehal222000

This comment has been minimized.

Copy link

@snehal222000 snehal222000 commented Mar 21, 2021

Thanks Brad

@mark-antal-csizmadia

This comment has been minimized.

Copy link

@mark-antal-csizmadia mark-antal-csizmadia commented Mar 23, 2021

Thanks a lot, great stuff

@doupongzeng

This comment has been minimized.

Copy link

@doupongzeng doupongzeng commented Apr 2, 2021

Thanks Brad!

@kashyapkale

This comment has been minimized.

Copy link

@kashyapkale kashyapkale commented Apr 6, 2021

Thanks a lot Brad!

@mourice-oduor

This comment has been minimized.

Copy link

@mourice-oduor mourice-oduor commented Apr 26, 2021

Thank you so much Brad.

@aivantuquero

This comment has been minimized.

Copy link

@aivantuquero aivantuquero commented May 1, 2021

This is great!

@ajay2312

This comment has been minimized.

Copy link

@ajay2312 ajay2312 commented May 1, 2021

Thanks a lot!

@AB-koricha

This comment has been minimized.

Copy link

@AB-koricha AB-koricha commented May 24, 2021

Thanks very much @bradtraversy. You always make me love programming. Keep up with the good work.its because of you i am running my startup here in kenya

@anoldKupara

This comment has been minimized.

Copy link

@anoldKupara anoldKupara commented Jun 8, 2021

Thanks very much.

@VikumRavinayake

This comment has been minimized.

Copy link

@VikumRavinayake VikumRavinayake commented Jun 14, 2021

I've no words to say. It's a smart coverage of all the basics for me. Thank you @bradtraversy...

@aarfiahmad

This comment has been minimized.

Copy link

@aarfiahmad aarfiahmad commented Jun 29, 2021

Thanks a lot to #BradTraversy for creating mysql such a short cheat sheet from India.

@maylilly

This comment has been minimized.

Copy link

@maylilly maylilly commented Jul 21, 2021

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

@suzuhaa

This comment has been minimized.

Copy link

@suzuhaa suzuhaa commented Aug 14, 2021

Just finished reading. Nice work!

@mourice-oduor

This comment has been minimized.

Copy link

@mourice-oduor mourice-oduor commented Aug 14, 2021

@justintoronto

This comment has been minimized.

Copy link

@justintoronto justintoronto commented Aug 14, 2021

Great. Thank you. All works on Linux MySQL.

@ChinemeremUgagu

This comment has been minimized.

Copy link

@ChinemeremUgagu ChinemeremUgagu commented Aug 16, 2021

Thanks

@mefercs

This comment has been minimized.

Copy link

@mefercs mefercs commented Aug 18, 2021

kisses on whatever you want Brad๐Ÿ˜ณ

@sanphem

This comment has been minimized.

Copy link

@sanphem sanphem commented Aug 19, 2021

Thank you so much, Brad.

@Rajarshi1001

This comment has been minimized.

Copy link

@Rajarshi1001 Rajarshi1001 commented Sep 3, 2021

Thanks!!

@pjguitar15

This comment has been minimized.

Copy link

@pjguitar15 pjguitar15 commented Oct 8, 2021

SHOW COLUMNS command is lacking here.

@erinoggz

This comment has been minimized.

Copy link

@erinoggz erinoggz commented Oct 8, 2021

@Jakaza

This comment has been minimized.

Copy link

@Jakaza Jakaza commented Oct 12, 2021

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

@Juliana-crsp

This comment has been minimized.

Copy link

@Juliana-crsp Juliana-crsp commented Nov 23, 2021

This helped so much!!! Thank you!

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