MySQL - Cheat Sheet

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

1mysql -u root -p

1SELECT User, Host FROM mysql.user;

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

1GRANT ALL PRIVILEGES ON * . * TO 'someuser'@'localhost';
2GRANT DELETE, INSERT, SELECT, UPDATE ON db2.invoice TO 'someuser'@'localhost';
3GRANT USAGE ON *.* TO 'someuser'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
4FLUSH PRIVILEGES;

1SHOW GRANTS FOR 'someuser'@'localhost';

1REVOKE INSERT ON *.* FROM 'someuser'@'localhost';
2REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'someuser'@'localhost';
3FLUSH PRIVILEGES;

1DROP USER 'someuser'@'localhost';

1exit;

1SHOW DATABASES;

1CREATE DATABASE acme;

1DROP DATABASE acme;

1USE acme;

 1CREATE TABLE users(
 2    id INT AUTO_INCREMENT,
 3    first_name VARCHAR(100),
 4    last_name VARCHAR(100),
 5    email VARCHAR(50),
 6    password VARCHAR(20),
 7    location VARCHAR(100),
 8    dept VARCHAR(100),
 9    is_admin TINYINT(1),
10    register_date DATETIME,
11    PRIMARY KEY(id)
12);

1DROP TABLE tablename;

1SHOW TABLES;

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

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

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

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

1DELETE FROM users WHERE id = 6;

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

1ALTER TABLE users ADD age VARCHAR(3);

1ALTER TABLE users MODIFY COLUMN age INT(3);

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

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

1SELECT DISTINCT location FROM users;

1SELECT * FROM users WHERE age BETWEEN 20 AND 25;

1SELECT * FROM users WHERE dept LIKE 'd%';
2SELECT * FROM users WHERE dept LIKE 'dev%';s
3SELECT * FROM users WHERE dept LIKE '%t';
4SELECT * FROM users WHERE dept LIKE '%e%';

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

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

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

1CREATE TABLE posts(
2    id INT AUTO_INCREMENT,
3    user_id INT,
4    title VARCHAR(100),
5    body TEXT,
6    publish_date DATETIME DEFAULT CURRENT_TIMESTAMP,
7    PRIMARY KEY(id),
8    FOREIGN KEY (user_id) REFERENCES users(id)
9);

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

1SELECT
2    users.first_name,
3    users.last_name,
4    posts.title,
5    posts.publish_date
6FROM users
7INNER JOIN posts
8ON users.id = posts.user_id
9ORDER BY posts.title;

 1CREATE TABLE comments(
 2    id INT AUTO_INCREMENT,
 3    post_id INT,
 4    user_id INT,
 5    body TEXT,
 6    publish_date DATETIME DEFAULT CURRENT_TIMESTAMP,
 7    PRIMARY KEY(id),
 8    FOREIGN KEY(user_id) references users(id),
 9    FOREIGN KEY(post_id) references posts(id)
10);

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

1SELECT
2    comments.body,
3    posts.title
4FROM comments
5LEFT JOIN posts ON posts.id = comments.post_id
6ORDER BY posts.title;

1SELECT
2    comments.body,
3    posts.title,
4    users.first_name,
5    users.last_name
6FROM comments
7INNER JOIN posts on posts.id = comments.post_id
8INNER JOIN users on users.id = comments.user_id
9ORDER BY posts.title;

1SELECT COUNT(id) FROM users;
2SELECT MAX(age) FROM users;
3SELECT MIN(age) FROM users;
4SELECT SUM(age) FROM users;
5SELECT UCASE(first_name), LCASE(last_name) FROM users;

1SELECT age, COUNT(age) FROM users GROUP BY age;
2SELECT age, COUNT(age) FROM users WHERE age > 20 GROUP BY age;
3SELECT age, COUNT(age) FROM users GROUP BY age HAVING count(age) >=2;

1mysqldump -u root -pPassword --opt db table > table.sql
2Restore:
3mysql -u root -p < table.sql