stevebaros · September 8, 2022 17:13 · Apr 22, 2019 · Apr 22, 2019 · Apr 22, 2019 · Apr 22, 2019
diff --git a/mysql_cheat_sheet.md b/mysql_cheat_sheet.md
@@ -142,7 +142,8 @@ SELECT first_name, last_name FROM users;
 ## Where Clause
 
 ```sql
-SELECT * FROM users WHERE location = 'massachusetts';
+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;
 ```

diff --git a/mysql_cheat_sheet.md b/mysql_cheat_sheet.md
@@ -102,7 +102,7 @@ id INT AUTO_INCREMENT,
    password VARCHAR(20),
    location VARCHAR(100),
    dept VARCHAR(100),
-   isAdmin TINYINT(1),
+   is_admin TINYINT(1),
    register_date DATETIME,
    PRIMARY KEY(id)
 );
@@ -123,13 +123,13 @@ SHOW TABLES;
 ## Insert Row / Record
 
 ```sql
-INSERT INTO users (first_name, last_name, email, password, location, dept, isAdmin, register_date) values ('Brad', 'Traversy', 'brad@gmail.com', '123456','Massachusetts', 'development', 1, now());
+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
 
 ```sql
-INSERT INTO users (first_name, last_name, email, password, location, dept,  isAdmin, 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());
+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
@@ -143,8 +143,8 @@ SELECT first_name, last_name FROM users;
 
 ```sql
 SELECT * FROM users WHERE location = 'massachusetts';
-SELECT * FROM users WHERE isAdmin = 1;
-SELECT * FROM users WHERE isAdmin > 0;
+SELECT * FROM users WHERE is_admin = 1;
+SELECT * FROM users WHERE is_admin > 0;
 ```
 
 ## Delete Row

diff --git a/mysql_cheat_sheet.md b/mysql_cheat_sheet.md
@@ -104,7 +104,7 @@ id INT AUTO_INCREMENT,
    dept VARCHAR(100),
    isAdmin TINYINT(1),
    register_date DATETIME,
-   PRIMARY_KEY(id)
+   PRIMARY KEY(id)
 );
 ```
 

diff --git a/mysql_cheat_sheet.md b/mysql_cheat_sheet.md
@@ -3,7 +3,7 @@
 > Help with SQL commands to interact with a MySQL database
 
 ## MySQL Locations
-* Mac/Linux       */usr/local/mysql/bin*
+* Mac             */usr/local/mysql/bin*
 * Windows         */Program Files/MySQL/MySQL _version_/bin*
 * Xampp           */xampp/mysql/bin*
 

diff --git a/mysql_cheat_sheet.md b/mysql_cheat_sheet.md
@@ -16,7 +16,7 @@ export PATH=${PATH}:/usr/local/mysql/bin
 echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> ~/.bash_profile
 ```
 
-On Windows, use the GUI and go to System->Environment Variables
+On Windows - https://www.qualitestgroup.com/resources/knowledge-center/how-to-guide/add-mysql-path-windows/
 
 ## Login
 

diff --git a/mysql_cheat_sheet.md b/mysql_cheat_sheet.md
@@ -3,9 +3,9 @@
 > Help with SQL commands to interact with a MySQL database
 
 ## MySQL Locations
-* Mac/Linux  */usr/local/mysql/bin*
-* Windows    /Program Files/MySQL/MySQL _version_/bin
-* Xampp      /xampp/mysql/bin
+* Mac/Linux       */usr/local/mysql/bin*
+* Windows         */Program Files/MySQL/MySQL _version_/bin*
+* Xampp           */xampp/mysql/bin*
 
 ## Add mysql to your PATH
 

diff --git a/mysql_cheat_sheet.md b/mysql_cheat_sheet.md
@@ -3,7 +3,7 @@
 > Help with SQL commands to interact with a MySQL database
 
 ## MySQL Locations
-* Mac/Linux  /usr/local/mysql/bin
+* Mac/Linux  */usr/local/mysql/bin*
 * Windows    /Program Files/MySQL/MySQL _version_/bin
 * Xampp      /xampp/mysql/bin
 

diff --git a/mysql_cheat_sheet.md b/mysql_cheat_sheet.md
@@ -2,6 +2,11 @@
 
 > Help with SQL commands to interact with a MySQL database
 
+## MySQL Locations
+* Mac/Linux  /usr/local/mysql/bin
+* Windows    /Program Files/MySQL/MySQL _version_/bin
+* Xampp      /xampp/mysql/bin
+
 ## Add mysql to your PATH
 
 ```bash

diff --git a/mysql_cheat_sheet.md b/mysql_cheat_sheet.md
@@ -31,7 +31,7 @@ SELECT User, Host FROM mysql.user;
 CREATE USER 'someuser'@'localhost' IDENTIFIED BY 'somepassword';
 ```
 
-## Grant priveleges
+## Grant All Priveleges On All Databases
 
 ```sql
 GRANT ALL PRIVILEGES ON * . * TO 'someuser'@'localhost';

diff --git a/mysql_cheat_sheet.md b/mysql_cheat_sheet.md
@@ -19,19 +19,43 @@ On Windows, use the GUI and go to System->Environment Variables
 mysql -u root -p
 ```
 
+## Show Users
+
+```sql
+SELECT User, Host FROM mysql.user;
+```
+
 ## Create User
 
 ```sql
-CREATE USER 'brad'@'localhost' IDENTIFIED BY '123456';
+CREATE USER 'someuser'@'localhost' IDENTIFIED BY 'somepassword';
 ```
 
 ## Grant priveleges
 
 ```sql
-GRANT ALL PRIVILEGES ON * . * TO 'brad'@'localhost';
+GRANT ALL PRIVILEGES ON * . * TO 'someuser'@'localhost';
 FLUSH PRIVILEGES;
 ```
 
+## Show Grants
+
+```sql
+SHOW GRANTS FOR 'someuser'@'localhost';
+```
+
+## Remove Grants
+
+```sql
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'someuser'@'localhost';
+```
+
+## Delete User
+
+```sql
+DROP USER 'someuser'@'localhost';
+```
+
 ## Exit
 
 ```sql

diff --git a/mysql_cheat_sheet.md b/mysql_cheat_sheet.md
@@ -50,6 +50,12 @@ SHOW DATABASES
 CREATE DATABASE acme;
 ```
 
+## Delete Database
+
+```sql
+DROP DATABASE acme;
+```
+
 ## Select Database
 
 ```sql

diff --git a/mysql_cheat_sheet.md b/mysql_cheat_sheet.md
@@ -5,7 +5,10 @@
 ## Add mysql to your PATH
 
 ```bash
+# Current Session
 export PATH=${PATH}:/usr/local/mysql/bin
+# Permanantly
+echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> ~/.bash_profile
 ```
 
 On Windows, use the GUI and go to System->Environment Variables

diff --git a/mysql_cheat_sheet.md b/mysql_cheat_sheet.md
@@ -0,0 +1,292 @@
+# MySQL Cheat Sheet
+
+> Help with SQL commands to interact with a MySQL database
+
+## Add mysql to your PATH
+
+```bash
+export PATH=${PATH}:/usr/local/mysql/bin
+```
+
+On Windows, use the GUI and go to System->Environment Variables
+
+## Login
+
+```bash
+mysql -u root -p
+```
+
+## Create User
+
+```sql
+CREATE USER 'brad'@'localhost' IDENTIFIED BY '123456';
+```
+
+## Grant priveleges
+
+```sql
+GRANT ALL PRIVILEGES ON * . * TO 'brad'@'localhost';
+FLUSH PRIVILEGES;
+```
+
+## Exit
+
+```sql
+exit;
+```
+
+## Show Databases
+
+```sql
+SHOW DATABASES
+```
+
+## Create Database
+
+```sql
+CREATE DATABASE acme;
+```
+
+## Select Database
+
+```sql
+USE acme;
+```
+
+## Create Table
+
+```sql
+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),
+   isAdmin TINYINT(1),
+   register_date DATETIME,
+   PRIMARY_KEY(id)
+);
+```
+
+## Delete / Drop Table
+
+```sql
+DROP TABLE tablename;
+```
+
+## Show Tables
+
+```sql
+SHOW TABLES;
+```
+
+## Insert Row / Record
+
+```sql
+INSERT INTO users (first_name, last_name, email, password, location, dept, isAdmin, register_date) values ('Brad', 'Traversy', 'brad@gmail.com', '123456','Massachusetts', 'development', 1, now());
+```
+
+## Insert Multiple Rows
+
+```sql
+INSERT INTO users (first_name, last_name, email, password, location, dept,  isAdmin, 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
+
+```sql
+SELECT * FROM users;
+SELECT first_name, last_name FROM users;
+```
+
+## Where Clause
+
+```sql
+SELECT * FROM users WHERE location = 'massachusetts';
+SELECT * FROM users WHERE isAdmin = 1;
+SELECT * FROM users WHERE isAdmin > 0;
+```
+
+## Delete Row
+
+```sql
+DELETE FROM users WHERE id = 6;
+```
+
+## Update Row
+
+```sql
+UPDATE users SET email = 'freddy@gmail.com' WHERE id = 2;
+
+```
+
+## Add New Column
+
+```sql
+ALTER TABLE users ADD age VARCHAR(3);
+```
+
+## Modify Column
+
+```sql
+ALTER TABLE users MODIFY COLUMN age INT(3);
+```
+
+## Order By (Sort)
+
+```sql
+SELECT * FROM users ORDER BY last_name ASC;
+SELECT * FROM users ORDER BY last_name DESC;
+```
+
+## Concatenate Columns
+
+```sql
+SELECT CONCAT(first_name, ' ', last_name) AS 'Name', dept FROM users;
+
+```
+
+## Select Distinct Rows
+
+```sql
+SELECT DISTINCT location FROM users;
+
+```
+
+## Between (Select Range)
+
+```sql
+SELECT * FROM users WHERE age BETWEEN 20 AND 25;
+```
+
+## Like (Searching)
+
+```sql
+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
+
+```sql
+SELECT * FROM users WHERE dept NOT LIKE 'd%';
+```
+
+## IN
+
+```sql
+SELECT * FROM users WHERE dept IN ('design', 'sales');
+```
+
+## Create & Remove Index
+
+```sql
+CREATE INDEX LIndex On users(location);
+DROP INDEX LIndex ON users;
+```
+
+## New Table With Foreign Key (Posts)
+
+```sql
+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
+
+```sql
+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
+
+```sql
+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
+
+```sql
+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
+
+```sql
+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
+
+```sql
+SELECT
+comments.body,
+posts.title
+FROM comments
+LEFT JOIN posts ON posts.id = comments.post_id
+ORDER BY posts.title;
+
+```
+
+## Join Multiple Tables
+
+```sql
+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
+
+```sql
+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
+
+```sql
+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;
+
+```