MySQL Cheat Sheet

mysql_cheat_sheet.md

MySQL Cheat Sheet

Login:

mysql -u root -p

Show Users:

SELECT User, Host FROM mysql.user;

Create User:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Grant Privileges:

GRANT ALL PRIVILEGES ON databaseName.columnName TO 'username'@'localhost';

Show Grants

SHOW GRANTS FOR 'someuser'@'localhost';

Remove Grants

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

Delete User

DROP USER 'username'@'localhost';

Show Databases

SHOW DATABASES

Create Database

CREATE DATABASE databaseName;

Delete Database

DROP DATABASE databaseName;

Work on Specific Database

USE databaseName;

Create Table

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

Delete / Remove Table

DROP TABLE tablename;

Show Tables

SHOW TABLES;

Insert Row / Record

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

Insert Multiple Rows

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

Show Data From Table

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

Where Clause

SELECT * FROM users WHERE location='Massachusetts';

Conditions

field1 = value1 --equal
field1 <> value1 --not equal
field1 IS NULL
field1 IS NOT NULL
field1 IS IN (value1, value2)
field1 IS NOT IN (value1, value2)
condition1 AND condition2
condition1 OR condition2

Limit Output

SELECT * FROM users WHERE location='Massachusetts' LIMIT 10;

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);

Sort / Order By

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

Add Columns

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

Select Unique Rows

SELECT DISTINCT location FROM users;

Select from Range / BETWEEN

SELECT * FROM users
WHERE age BETWEEN 20 AND 25;

Searching / Like

SELECT * FROM users WHERE first_name LIKE 'p%';
SELECT * FROM users WHERE first_name LIKE 'pol%';
SELECT * FROM users WHERE first_name LIKE '%k';
SELECT * FROM users WHERE first_name LIKE '%ol%';

Not Like

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

IN

SELECT * FROM users WHERE first_name IN ('Polak', 'Shawon');

Create New Table With Foreign Key

CREATE TABLE posts(
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    title VARCHAR(100),
    body TEXT,
    publish_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    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;

Backup Database to SQL file

mysqldump -u Username -p dbNameYouWant > databasename_backup.sql;

Backup Database to SQL file

mysql -u Username -p dbNameYouWant < databasename_backup.sql;

Data Types

TINYINT (1o: -128 to +127)
SMALLINT (2o: +-65 000)
MEDIUMINT (3o: +-16 000 000)
INT (4o: +- 2 000 000 000)
BIGINT (8o: +-9.10^18)

FLOAT(M,D)
DOUBLE(M,D)
FLOAT(D=0->53)

TIME (HH:MM)
YEAR (AAAA)
DATE (AAAA-MM-JJ)
DATETIME (AAAA-MM-JJ HH:MM; années 1000->9999)
TIMESTAMP (like DATETIME, but 1970->2038, compatible with Unix)

VARCHAR (single-line; explicit size)
TEXT (multi-lines; max size=65535)
BLOB (binary; max size=65535)

ENUM ('value1', 'value2', ...) -- (default NULL, or '' if NOT NULL)

Reset root Password

sudo systemctl stop mysql

mysqld_safe --skip-grant-tables

mysql # another tab
mysql> UPDATE mysql.user SET password=PASSWORD('new_pass') WHERE user='root';

sudo systemctl start mysql