ebarault · March 29, 2018 09:31 · Mar 29, 2018 · Mar 29, 2018 · Mar 21, 2018 · Mar 21, 2018
diff --git a/MySQL.md b/MySQL.md
@@ -16,7 +16,7 @@ Create new user: `CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';`
 
 Grant `ALL` access on `database` to user for `*` tables: `GRANT ALL ON database.* TO 'user'@'localhost';`
 
-Show grants for current user or given user: `SHOW GRANTS [FOR user]`
+Show grants for current user or given user: `SHOW GRANTS [FOR user];`
 
 Change user's password: `ALTER USER 'userName'@'localhost' IDENTIFIED BY 'New-Password-Here';`
 

diff --git a/MySQL.md b/MySQL.md
@@ -18,6 +18,8 @@ Grant `ALL` access on `database` to user for `*` tables: `GRANT ALL ON database.
 
 Show grants for current user or given user: `SHOW GRANTS [FOR user]`
 
+Change user's password: `ALTER USER 'userName'@'localhost' IDENTIFIED BY 'New-Password-Here';`
+
 
 Databases functions
 -----------

diff --git a/README.md → MySQL.md b/README.md → MySQL.md
diff --git a/README.md b/README.md
@@ -2,7 +2,7 @@
 Commands
 -----------
 
-Access monitor: `mysql -h [host] -u [username] -p;` (will prompt for password)
+Access monitor: `mysql -h [host] -u [username] -p` (will prompt for password)
 
 Access database: `mysql -h [host] -u [username] -p [database]` (will prompt for password)
 
@@ -31,6 +31,18 @@ Select database: `use [database];`
 Determine what database is in use: `select database();`
 
 
+Dump/restore functions
+-----------
+
+Dump a single database: `mysqldump -h [host] -u [username] -p [database] | gzip -c > dump.sql.gz`
+
+Dump all databases: `mysqldump -h [host] -u [username] -p --all-databases | gzip -c > dump.sql.gz`
+
+Restore a single database: `mysql -h [host] -u [username] -p [database] < dump.sql`
+
+Resstore all databases: `mysql -h [host] -u [username] -p < dump.sql` 
+
+
 Data functions
 -----------
 

diff --git a/README.md b/README.md
@@ -2,7 +2,8 @@
 Commands
 -----------
 
-Access monitor: `mysql -h [host] -u [username] -p;` (will prompt for password)  
+Access monitor: `mysql -h [host] -u [username] -p;` (will prompt for password)
+
 Access database: `mysql -h [host] -u [username] -p [database]` (will prompt for password)
 
 

diff --git a/README.md b/README.md
@@ -2,7 +2,7 @@
 Commands
 -----------
 
-Access monitor: `mysql -h [host] -u [username] -p;` (will prompt for password)
+Access monitor: `mysql -h [host] -u [username] -p;` (will prompt for password)  
 Access database: `mysql -h [host] -u [username] -p [database]` (will prompt for password)
 
 

diff --git a/bash_profile b/bash_profile
@@ -1 +0,0 @@
-alias mysql=/Applications/MAMP/Library/bin/mysql

diff --git a/my.cnf b/my.cnf
@@ -1,2 +0,0 @@
-[mysqld]
-max_allowed_packet=64M

diff --git a/README.md b/README.md
@@ -1,35 +1,38 @@
-MySQL
-===============
 
-Getting started: 
-- http://www.sqlteaching.com/
-- https://www.codecademy.com/courses/learn-sql
-
-Related tutorials:
-- [MySQL-CLI](https://www.youtube.com/playlist?list=PLfdtiltiRHWEw4-kRrh1ZZy_3OcQxTn7P)
-- [Analyzing Business Metrics](https://www.codecademy.com/learn/sql-analyzing-business-metrics)
-- [SQL joins infografic](https://lh4.googleusercontent.com/-RdjzcoAwBYg/UxTXWGJHgoI/AAAAAAAACrs/Gqbu6zyksgo/w852-h670/sql-joins.jpg)
+Commands
+-----------
 
-Tools:
-- [DataGrip](https://www.jetbrains.com/datagrip/)
-- [Sequel Pro](http://www.sequelpro.com/)
+Access monitor: `mysql -h [host] -u [username] -p;` (will prompt for password)
+Access database: `mysql -h [host] -u [username] -p [database]` (will prompt for password)
 
 
-Commands
+Users functions
 -----------
 
-Access monitor: `mysql -u [username] -p;` (will prompt for password)
+List all users: `SELECT User,Host FROM mysql.user;`
 
-Show all databases: `show databases;`
+Create new user: `CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';`
+
+Grant `ALL` access on `database` to user for `*` tables: `GRANT ALL ON database.* TO 'user'@'localhost';`
+
+Show grants for current user or given user: `SHOW GRANTS [FOR user]`
+
+
+Databases functions
+-----------
 
-Access database: `mysql -u [username] -p [database]` (will prompt for password)
+Show all databases: `show databases;`
 
 Create new database: `create database [database];`
 
 Select database: `use [database];`
 
 Determine what database is in use: `select database();`
 
+
+Data functions
+-----------
+
 Show all tables: `show tables;`
 
 Show table structure: `describe [table];`
@@ -124,16 +127,6 @@ Combine rows from different tables but do not require the join condition: `SELEC
 Rename column or table using an _alias_: `SELECT [table1].[column] AS '[value]', [table2].[column] AS '[value]' FROM [table1], [table2];`
 
 
-Users functions
------------
-
-List all users: `SELECT User,Host FROM mysql.user;`
-
-Create new user: `CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';`
-
-Grant `ALL` access to user for `*` tables: `GRANT ALL ON database.* TO 'user'@'localhost';`
-
-
 Find out the IP Address of the Mysql Host
 -----------
 `SHOW VARIABLES WHERE Variable_name = 'hostname';` ([source](http://serverfault.com/a/129646))
diff --git a/README.md b/README.md
@@ -6,13 +6,13 @@ Getting started:
 - https://www.codecademy.com/courses/learn-sql
 
 Related tutorials:
-- MySQL-CLI: https://www.youtube.com/playlist?list=PLfdtiltiRHWEw4-kRrh1ZZy_3OcQxTn7P
-- Analyzing Business Metrics: https://www.codecademy.com/learn/sql-analyzing-business-metrics
-- SQL joins infografic: http://cd64.de/sql-joins
+- [MySQL-CLI](https://www.youtube.com/playlist?list=PLfdtiltiRHWEw4-kRrh1ZZy_3OcQxTn7P)
+- [Analyzing Business Metrics](https://www.codecademy.com/learn/sql-analyzing-business-metrics)
+- [SQL joins infografic](https://lh4.googleusercontent.com/-RdjzcoAwBYg/UxTXWGJHgoI/AAAAAAAACrs/Gqbu6zyksgo/w852-h670/sql-joins.jpg)
 
 Tools:
-- DataGrip: https://www.jetbrains.com/datagrip/
-- Sequel Pro: http://www.sequelpro.com/
+- [DataGrip](https://www.jetbrains.com/datagrip/)
+- [Sequel Pro](http://www.sequelpro.com/)
 
 
 Commands

diff --git a/README.md b/README.md
@@ -28,10 +28,14 @@ Create new database: `create database [database];`
 
 Select database: `use [database];`
 
+Determine what database is in use: `select database();`
+
 Show all tables: `show tables;`
 
 Show table structure: `describe [table];`
 
+List all indexes on a table: `show index from [table];`
+
 Create new table with columns: `CREATE TABLE [table] ([column] VARCHAR(120), [another-column] DATETIME);`
 
 Adding a column: `ALTER TABLE [table] ADD COLUMN [column] VARCHAR(120);`
@@ -71,6 +75,8 @@ Deleting records: `DELETE FROM [table] WHERE [column] = [value];`
 Delete *all records* from a table (without dropping the table itself): `DELETE FROM [table];`
 (This also resets the incrementing counter for auto generated columns like an id column.)
 
+Delete all records in a table: `truncate table [table];`
+
 Removing table columns: `ALTER TABLE [table] DROP COLUMN [column];`
 
 Deleting tables: `DROP TABLE [table];`

diff --git a/README.md b/README.md
@@ -10,6 +10,10 @@ Related tutorials:
 - Analyzing Business Metrics: https://www.codecademy.com/learn/sql-analyzing-business-metrics
 - SQL joins infografic: http://cd64.de/sql-joins
 
+Tools:
+- DataGrip: https://www.jetbrains.com/datagrip/
+- Sequel Pro: http://www.sequelpro.com/
+
 
 Commands
 -----------

diff --git a/README.md b/README.md
@@ -5,9 +5,10 @@ Getting started:
 - http://www.sqlteaching.com/
 - https://www.codecademy.com/courses/learn-sql
 
-Related tutorial: http://cd64.de/mysql-cli
-
-SQL joins infografic: http://cd64.de/sql-joins
+Related tutorials:
+- MySQL-CLI: https://www.youtube.com/playlist?list=PLfdtiltiRHWEw4-kRrh1ZZy_3OcQxTn7P
+- Analyzing Business Metrics: https://www.codecademy.com/learn/sql-analyzing-business-metrics
+- SQL joins infografic: http://cd64.de/sql-joins
 
 
 Commands

diff --git a/README.md b/README.md
@@ -125,4 +125,4 @@ Grant `ALL` access to user for `*` tables: `GRANT ALL ON database.* TO 'user'@'l
 
 Find out the IP Address of the Mysql Host
 -----------
-`SHOW VARIABLES WHERE Variable_name = 'hostname';` ([source](http://serverfault.com/a/129646), contribution @junrillg)
+`SHOW VARIABLES WHERE Variable_name = 'hostname';` ([source](http://serverfault.com/a/129646))
diff --git a/README.md b/README.md
@@ -125,4 +125,4 @@ Grant `ALL` access to user for `*` tables: `GRANT ALL ON database.* TO 'user'@'l
 
 Find out the IP Address of the Mysql Host
 -----------
-`SHOW VARIABLES WHERE Variable_name = 'hostname';` ([source](http://serverfault.com/a/129646))
+`SHOW VARIABLES WHERE Variable_name = 'hostname';` ([source](http://serverfault.com/a/129646), contribution @junrillg)
diff --git a/README.md b/README.md
@@ -116,8 +116,13 @@ Rename column or table using an _alias_: `SELECT [table1].[column] AS '[value]',
 Users functions
 -----------
 
-List all users: `select User,Host from mysql.user;`
+List all users: `SELECT User,Host FROM mysql.user;`
 
 Create new user: `CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';`
 
-Grant `ALL` access to user for `*` tables: `GRANT ALL ON database.* TO 'user'@'localhost';`
+Grant `ALL` access to user for `*` tables: `GRANT ALL ON database.* TO 'user'@'localhost';`
+
+
+Find out the IP Address of the Mysql Host
+-----------
+`SHOW VARIABLES WHERE Variable_name = 'hostname';` ([source](http://serverfault.com/a/129646))
diff --git a/README.md b/README.md
@@ -39,6 +39,8 @@ MySQL function for datetime input: `NOW()`
 
 Selecting records: `SELECT * FROM [table];`
 
+Explain records: `EXPLAIN SELECT * FROM [table];`
+
 Selecting parts of records: `SELECT [column], [another-column] FROM [table];`
 
 Counting records: `SELECT COUNT([column]) FROM [table];`

diff --git a/README.md b/README.md
@@ -74,6 +74,8 @@ Custom column output names: `SELECT [column] AS [custom-column] FROM [table];`
 
 Export a database dump (more info [here](http://stackoverflow.com/a/21091197/1815847)): `mysqldump -u [username] -p [database] > db_backup.sql`
 
+Use `--lock-tables=false` option for locked tables (more info [here](http://stackoverflow.com/a/104628/1815847)).
+
 Import a database dump (more info [here](http://stackoverflow.com/a/21091197/1815847)): `mysql -u [username] -p -h localhost [database] < db_backup.sql`
 
 Logout: `exit;`

diff --git a/README.md b/README.md
@@ -97,6 +97,18 @@ Get average value: `SELECT AVG([column]) FROM [table];`
 Get rounded average value and group by `[category-column]`: `SELECT [category-column], ROUND(AVG([column]), 2) FROM [table] GROUP BY [category-column];`
 
 
+Multiple tables
+-----------
+
+Select from multiple tables: `SELECT [table1].[column], [table1].[another-column], [table2].[column] FROM [table1], [table2];`
+
+Combine rows from different tables: `SELECT * FROM [table1] INNER JOIN [table2] ON [table1].[column] = [table2].[column];`
+
+Combine rows from different tables but do not require the join condition: `SELECT * FROM [table1] LEFT OUTER JOIN [table2] ON [table1].[column] = [table2].[column];` (The left table is the first table that appears in the statement.)
+
+Rename column or table using an _alias_: `SELECT [table1].[column] AS '[value]', [table2].[column] AS '[value]' FROM [table1], [table2];`
+
+
 Users functions
 -----------
 

diff --git a/README.md b/README.md
@@ -97,7 +97,7 @@ Get average value: `SELECT AVG([column]) FROM [table];`
 Get rounded average value and group by `[category-column]`: `SELECT [category-column], ROUND(AVG([column]), 2) FROM [table] GROUP BY [category-column];`
 
 
-User functions
+Users functions
 -----------
 
 List all users: `select User,Host from mysql.user;`

diff --git a/README.md b/README.md
@@ -76,4 +76,32 @@ Export a database dump (more info [here](http://stackoverflow.com/a/21091197/181
 
 Import a database dump (more info [here](http://stackoverflow.com/a/21091197/1815847)): `mysql -u [username] -p -h localhost [database] < db_backup.sql`
 
-Logout: `exit;`
+Logout: `exit;`
+
+
+Aggregate functions
+-----------
+
+Select but without duplicates: `SELECT distinct name, email, acception FROM owners WHERE acception = 1 AND date >= 2015-01-01 00:00:00`
+
+Calculate total number of records: `SELECT SUM([column]) FROM [table];`
+
+Count total number of `[column]` and group by `[category-column]`: `SELECT [category-column], SUM([column]) FROM [table] GROUP BY [category-column];`
+
+Get largest value in `[column]`: `SELECT MAX([column]) FROM [table];`
+
+Get smallest value: `SELECT MIN([column]) FROM [table];`
+
+Get average value: `SELECT AVG([column]) FROM [table];`
+
+Get rounded average value and group by `[category-column]`: `SELECT [category-column], ROUND(AVG([column]), 2) FROM [table] GROUP BY [category-column];`
+
+
+User functions
+-----------
+
+List all users: `select User,Host from mysql.user;`
+
+Create new user: `CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';`
+
+Grant `ALL` access to user for `*` tables: `GRANT ALL ON database.* TO 'user'@'localhost';`
diff --git a/SPECIAL.md b/SPECIAL.md
@@ -1,4 +0,0 @@
-Commands
------------
-
-Select but without duplicates: `SELECT distinct name, email, acception FROM owners WHERE acception = 1 AND date >= 2015-01-01 00:00:00`

diff --git a/USERS.md b/USERS.md
@@ -1,8 +0,0 @@
-Commands
------------
-
-List all users: `select User,Host from mysql.user;`
-
-Create new user: `CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';`
-
-Grant `ALL` access to user for `*` tables: `GRANT ALL ON database.* TO 'user'@'localhost';`

diff --git a/README.md b/README.md
@@ -55,6 +55,8 @@ Select records starting with `val` and ending with `ue`: `SELECT * FROM [table]
 
 Select a range: `SELECT * FROM [table] WHERE [column] BETWEEN [value1] and [value2];`
 
+Select with custom order and only limit: `SELECT * FROM [table] WHERE [column] ORDER BY [column] ASC LIMIT [value];` (Order: `DESC`, `ASC`)
+
 Updating records: `UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value];`
 
 Deleting records: `DELETE FROM [table] WHERE [column] = [value];`

diff --git a/README.md b/README.md
@@ -45,13 +45,15 @@ Counting records: `SELECT COUNT([column]) FROM [table];`
 
 Counting and selecting grouped records: `SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY [column];`
 
-Selecting specific records: `SELECT * FROM [table] WHERE [column] = [value];` (Selectors: `<`, `>`, `!=`)
+Selecting specific records: `SELECT * FROM [table] WHERE [column] = [value];` (Selectors: `<`, `>`, `!=`; combine multiple selectors with `AND`, `OR`)
 
-Searching records for a word: `SELECT * FROM [table] WHERE [column] LIKE '%[value]%';`
+Select records containing `[value]`: `SELECT * FROM [table] WHERE [column] LIKE '%[value]%';`
 
-Searching records for a word starting with [value]: `SELECT * FROM [table] WHERE [column] LIKE '[value]%';`
+Select records starting with `[value]`: `SELECT * FROM [table] WHERE [column] LIKE '[value]%';`
 
-Searching for words starting with `val` and ending with `ue`: `SELECT * FROM [table] WHERE [column] LIKE '[val_ue]';`
+Select records starting with `val` and ending with `ue`: `SELECT * FROM [table] WHERE [column] LIKE '[val_ue]';`
+
+Select a range: `SELECT * FROM [table] WHERE [column] BETWEEN [value1] and [value2];`
 
 Updating records: `UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value];`
 

diff --git a/README.md b/README.md
@@ -51,6 +51,8 @@ Searching records for a word: `SELECT * FROM [table] WHERE [column] LIKE '%[valu
 
 Searching records for a word starting with [value]: `SELECT * FROM [table] WHERE [column] LIKE '[value]%';`
 
+Searching for words starting with `val` and ending with `ue`: `SELECT * FROM [table] WHERE [column] LIKE '[val_ue]';`
+
 Updating records: `UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value];`
 
 Deleting records: `DELETE FROM [table] WHERE [column] = [value];`

diff --git a/USERS.md b/USERS.md
@@ -1,6 +1,8 @@
 Commands
 -----------
 
+List all users: `select User,Host from mysql.user;`
+
 Create new user: `CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';`
 
 Grant `ALL` access to user for `*` tables: `GRANT ALL ON database.* TO 'user'@'localhost';`
diff --git a/README.md b/README.md
@@ -1,7 +1,9 @@
 MySQL
 ===============
 
-Getting started: http://www.sqlteaching.com/
+Getting started: 
+- http://www.sqlteaching.com/
+- https://www.codecademy.com/courses/learn-sql
 
 Related tutorial: http://cd64.de/mysql-cli
 

diff --git a/README.md b/README.md
@@ -66,4 +66,6 @@ Custom column output names: `SELECT [column] AS [custom-column] FROM [table];`
 
 Export a database dump (more info [here](http://stackoverflow.com/a/21091197/1815847)): `mysqldump -u [username] -p [database] > db_backup.sql`
 
+Import a database dump (more info [here](http://stackoverflow.com/a/21091197/1815847)): `mysql -u [username] -p -h localhost [database] < db_backup.sql`
+
 Logout: `exit;`
diff --git a/USERS.md b/USERS.md
@@ -3,4 +3,4 @@ Commands
 
 Create new user: `CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';`
 
-Grunt `ALL` access to user for `*` tables: `GRANT ALL ON database.* TO 'user'@'localhost';`
+Grant `ALL` access to user for `*` tables: `GRANT ALL ON database.* TO 'user'@'localhost';`
diff --git a/SPECIAL.md b/SPECIAL.md
@@ -1,4 +1,4 @@
 Commands
 -----------
 
-Select but without duplicates: `SELECT distinct name, email, acception FROM owners WHERE acception = 1`
+Select but without duplicates: `SELECT distinct name, email, acception FROM owners WHERE acception = 1 AND date >= 2015-01-01 00:00:00`
diff --git a/MORE.md → SPECIAL.md b/MORE.md → SPECIAL.md
Original file line number	Diff line number	Diff line change
		@@ -1 +0,0 @@
		alias mysql=/Applications/MAMP/Library/bin/mysql
No results found