bradtraversy / mysql_cheat_sheet.
md Sign in Sign up
MySQL Cheat Sheet
mysql_cheat_sheet.md
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, r
Insert Multiple Rows
INSERT INTO users (first_name, last_name, email, password, location, dept, is_admin,
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,
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,
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
commented 3 months ago
Thank you brad
NobleKiwam
commented 3 months ago
Just started going through. Thanks
ridl27
commented 3 months ago
Very useful! thx <3
Niyokwizerwa250
commented 3 months ago
You are awesome! Keep doing what you are doing for the industry
saddamcrr7
commented 3 months ago
Thanks a lot brad
fendiversace
commented 3 months ago
Very useful!
rohankewal
commented 3 months ago
Very helpful! Thanks brad @bradtraversy
Sohel1999
commented 3 months ago
very useful!
zhouyang159
commented 3 months ago
xiexie ni de crash course :) (someone from China)
YuanruiZhang
commented 3 months ago
Thanks alot Brad!
Foss-Bee
commented 3 months ago
Thanks very much @bradtraversy. You always make me love programming. Keep up with the good work.
xelinel32
commented 3 months ago
thx, man)
kailichou
commented 3 months ago
Hi, there
is there anyone also struggling to add mysql to the path?
LinmeiJ
commented 2 months ago
THANK YOU SO MUCH brad! very helpful!
mymmoonoa
commented about 1 month ago
nice work!
abdalahshaban
commented about 1 month ago
nice work
zaki1001
commented about 1 month ago
thanks a ton mate
ball97
commented about 1 month ago
Thank you Brad. You are the best !!
Ajmal0197
commented about 1 month ago
Thanks master
joshuaOgwang
commented about 1 month ago
Thanks
MbuguaCaleb
commented 26 days ago
Thank you very Much Brad!!
samdsg
commented 23 days ago
God Bless you Mr. Brad
samdsg
commented 23 days ago
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
commented 20 days ago
Very useful, thanks.
matthewheimark
commented 11 days ago
Super useful, thank you!
Zhixin-Jack-Wang
commented 5 days ago
GOOD WORK, THANK YOU
YosiLeibman
commented 4 days ago
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.
Comment on gist
Sign in to comment
or sign up to join this conversation on GitHub
Desktop version
