MySQL Cheat Sheet

Login (terminal):
mysql -u[username/root] -p[password] -h[host/localhost]Show Databases:
SHOW DATABASES;
Create Database:
CREATE [database_name]Select Database:
USE [database_name];
Delete Database:
DROP [database_name];
Create Table:

CREATE TABLE [table_name](
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)
);

Show Tables:
SHOW TABLES;

Delete Table:
DROP TABLE [table_name];

Insert Record (One row):
INSERT INTO users (first_name, last_name, email, password, location, dept, is_admin, register_date) values (‘Brad’, ‘Traversy’, ‘[email protected]’, ‘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’, ‘[email protected]’, ‘123456’, ‘New York’, ‘design’, 0, now()), (‘Sara’, ‘Watson’, ‘[email protected]’, ‘123456’, ‘New York’, ‘design’, 0, now()),(‘Will’, ‘Jackson’, ‘[email protected]’, ‘123456’, ‘Rhode Island’, ‘development’, 1, now()),(‘Paula’, ‘Johnson’, ‘[email protected]’, ‘123456’, ‘Massachusetts’, ‘sales’, 0, now()),(‘Tom’, ‘Spears’, ‘[email protected]’, ‘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;

Add New Column:
ALTER TABLE users ADD age VARCHAR(3);

Delete Row:
DELETE FROM users WHERE id = 6;

Concatenate Columns:
SELECT CONCAT(first_name, ‘ ‘, last_name) AS ‘Name’, dept FROM users;

Between (Select Range):
SELECT * FROM users WHERE age BETWEEN 20 AND 25;

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;

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;

Leave a Comment

Your email address will not be published.