Skip to content

Instantly share code, notes, and snippets.

@logbasex

logbasex/README.md

Forked from hofmannsven/README.md
Last active November 3, 2020 07:41
Show Gist options
  • Save logbasex/c729d97f244bf9bab6a882f5860d7b86 to your computer and use it in GitHub Desktop.
Save logbasex/c729d97f244bf9bab6a882f5860d7b86 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. logbasex revised this gist Nov 3, 2020. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -2,9 +2,9 @@

    ## INSTALL

    **Access mysql from docker container**
    **[Access mysql from docker container](https://stackoverflow.com/questions/28389458/execute-mysql-command-from-the-host-to-container-running-mysql-server)**

    `docker exec -t -i {docker_container_id} mysql -uroot -proot`
    `docker exec -t -i {docker_container_id/name} mysql -uroot -proot`

    ## TERMS

  2. logbasex revised this gist Nov 3, 2020. 1 changed file with 3 additions and 1 deletion.
    4 changes: 3 additions & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -2,7 +2,9 @@

    ## INSTALL

    Access mysql from docker container: docker exec -t -i {docker_container_id} mysql -uroot -proot
    **Access mysql from docker container**

    `docker exec -t -i {docker_container_id} mysql -uroot -proot`

    ## TERMS

  3. logbasex revised this gist Nov 3, 2020. 2 changed files with 4 additions and 1 deletion.
    4 changes: 4 additions & 0 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,9 @@
    # MySQL

    ## INSTALL

    Access mysql from docker container: docker exec -t -i {docker_container_id} mysql -uroot -proot

    ## TERMS

    **[MySQL terminology: processes, threads & connections]**(http://code.openark.org/blog/mysql/mysql-terminology-processes-threads-connections)
    1 change: 0 additions & 1 deletion bash_profile
    View file
    Original file line number Diff line number Diff line change
    @@ -1 +0,0 @@
    alias mysql=/Applications/MAMP/Library/bin/mysql
  4. logbasex revised this gist Oct 30, 2020. 1 changed file with 4 additions and 0 deletions.
    4 changes: 4 additions & 0 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,9 @@
    # MySQL

    ## TERMS

    **[MySQL terminology: processes, threads & connections]**(http://code.openark.org/blog/mysql/mysql-terminology-processes-threads-connections)

    ## Tools
    - [TablePlus](https://tableplus.io/)
    - [DataGrip](https://www.jetbrains.com/datagrip/)
  5. logbasex revised this gist Oct 30, 2020. 1 changed file with 3 additions and 3 deletions.
    6 changes: 3 additions & 3 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -235,17 +235,17 @@ SET FOREIGN_KEY_CHECKS=1; -- to re-enable them

    ## Check AUTO_INCREMENT value

    SHOW TABLE STATUS FROM `prism_prepro` WHERE `name` LIKE 'breeding_contract_extra';
    SHOW TABLE STATUS FROM `database_name` WHERE `name` LIKE 'table_name';
    #### LAST_INSERT_ID()
    -- --------------NOT WORK IF INSERT ID------------------

    `insert into bonus_scheme (id, code, description, media_id) values (32, 'TASBOW', 'TASBRED - OWNER ONLY Bonus Scheme', null);`
    `insert into table_name (id, name) values (1, value);`

    `select LAST_INSERT_ID();`

    -- --------------RUN TWO QUERIES AT ONCE-----------------

    `insert into bonus_scheme (code, description, media_id) values ('TASBOW', 'TASBRED - OWNER ONLY Bonus Scheme', null);`
    `insert into table_name (name) values (value);`

    `select LAST_INSERT_ID();`

  6. logbasex revised this gist Oct 30, 2020. 1 changed file with 49 additions and 18 deletions.
    67 changes: 49 additions & 18 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -148,45 +148,73 @@ Use `--lock-tables=false` option for locked tables (more info [here](http://stac

    `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`
    **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]`:**

    Calculate total number of records: `SELECT SUM([column]) FROM [table];`
    `SELECT [category-column], SUM([column]) FROM [table] GROUP BY [category-column];`

    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]`:**

    Get largest value in `[column]`: `SELECT MAX([column]) FROM [table];`
    `SELECT MAX([column]) FROM [table];`

    Get smallest value: `SELECT MIN([column]) FROM [table];`
    **Get smallest value:**

    Get average value: `SELECT AVG([column]) FROM [table];`
    `SELECT MIN([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];`
    **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];`
    **Select from multiple tables:**

    `SELECT [table1].[column], [table1].[another-column], [table2].[column] FROM [table1], [table2];`

    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.)
    **Combine rows from different tables:**

    Rename column or table using an _alias_: `SELECT [table1].[column] AS '[value]', [table2].[column] AS '[value]' FROM [table1], [table2];`
    `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
    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';`
    **Create new user:**

    Grant `ALL` access to user for `*` tables: `GRANT ALL ON database.* TO 'user'@'localhost';`
    `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))

    ## Common problem
    View as another date format: `SELECT DATE_FORMAT(column_name, '%m/%d/%Y %H:%i') FROM table_reference;` (Default date format of MySQL is: `YYYY-MM-dd`)

    ## MySQL function
    Like concat: `SELECT url FROM media where url like concat('%','youtube','%');`

    @@ -202,6 +230,9 @@ SET FOREIGN_KEY_CHECKS=1; -- to re-enable them

    # Problems

    ## Format date
    `SELECT DATE_FORMAT(column_name, '%m/%d/%Y %H:%i') FROM table_reference;` (Default date format of MySQL is: `YYYY-MM-dd`)

    ## Check AUTO_INCREMENT value

    SHOW TABLE STATUS FROM `prism_prepro` WHERE `name` LIKE 'breeding_contract_extra';
  7. logbasex revised this gist Oct 30, 2020. 1 changed file with 62 additions and 21 deletions.
    83 changes: 62 additions & 21 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -62,50 +62,91 @@

    `NOW()`

    Selecting records: `SELECT * FROM [table];`
    **Selecting records:**

    Explain records: `EXPLAIN SELECT * FROM [table];`
    `SELECT * FROM [table];`

    Selecting parts of records: `SELECT [column], [another-column] FROM [table];`
    **Explain records:**

    Counting records: `SELECT COUNT([column]) FROM [table];`
    `EXPLAIN SELECT * FROM [table];`

    Counting and selecting grouped records: `SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY [column];`
    **Selecting parts of records:**

    Selecting specific records: `SELECT * FROM [table] WHERE [column] = [value];` (Selectors: `<`, `>`, `!=`; combine multiple selectors with `AND`, `OR`)
    `SELECT [column], [another-column] FROM [table];`

    Select records containing `[value]`: `SELECT * FROM [table] WHERE [column] LIKE '%[value]%';`
    **Counting records:**

    Select records starting with `[value]`: `SELECT * FROM [table] WHERE [column] LIKE '[value]%';`
    `SELECT COUNT([column]) FROM [table];`

    Select records starting with `val` and ending with `ue`: `SELECT * FROM [table] WHERE [column] LIKE '[val_ue]';`
    **Counting and selecting grouped records:**

    Select a range: `SELECT * FROM [table] WHERE [column] BETWEEN [value1] and [value2];`
    `SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY [column];`

    Select with custom order and only limit: `SELECT * FROM [table] WHERE [column] ORDER BY [column] ASC LIMIT [value];` (Order: `DESC`, `ASC`)
    **Selecting specific records:**

    Updating records: `UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value];`
    `SELECT * FROM [table] WHERE [column] = [value];` (Selectors: `<`, `>`, `!=`; combine multiple selectors with `AND`, `OR`)

    Deleting records: `DELETE FROM [table] WHERE [column] = [value];`
    **Select records containing `[value]`:**

    Delete *all records* from a table (without dropping the table itself): `DELETE FROM [table];`
    `SELECT * FROM [table] WHERE [column] LIKE '%[value]%';`

    **Select records starting with `[value]`:**

    `SELECT * FROM [table] WHERE [column] LIKE '[value]%';`

    **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];`

    **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];`

    **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];`
    **Delete all records in a table:**
    `truncate table [table];`

    **Removing table columns:**

    Removing table columns: `ALTER TABLE [table] DROP COLUMN [column];`
    `ALTER TABLE [table] DROP COLUMN [column];`

    Deleting tables: `DROP TABLE [table];`
    **Deleting tables:**

    Deleting databases: `DROP DATABASE [database];`
    `DROP TABLE [table];`

    Custom column output names: `SELECT [column] AS [custom-column] FROM [table];`
    **Deleting databases:**

    Export a database dump (more info [here](http://stackoverflow.com/a/21091197/1815847)): `mysqldump -u [username] -p [database] > db_backup.sql`
    `DROP DATABASE [database];`

    **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`
    **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;`

  8. logbasex revised this gist Oct 30, 2020. 1 changed file with 46 additions and 14 deletions.
    60 changes: 46 additions & 14 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -6,33 +6,61 @@
    - [Sequel Pro](http://www.sequelpro.com/) (abandoned)

    ## Commands
    Access monitor: `mysql -u [username] -p;` (will prompt for password)
    **Access monitor:**

    Show all databases: `show databases;`
    `mysql -u [username] -p;` (will prompt for password)

    Access database: `mysql -u [username] -p [database]` (will prompt for password)
    **Show all databases:**

    Create new database: `create database [database];`
    `show databases;`

    Select database: `use [database];`
    **Access database:**

    Determine what database is in use: `select database();`
    `mysql -u [username] -p [database]` (will prompt for password)

    Show all tables: `show tables;`
    **Create new database:**

    Show table structure: `describe [table];`
    `create database [database];`

    List all indexes on a table: `show index from [table];`
    **Select database:**

    Create new table with columns: `CREATE TABLE [table] ([column] VARCHAR(120), [another-column] DATETIME);`
    `use [database];`

    Adding a column: `ALTER TABLE [table] ADD COLUMN [column] VARCHAR(120);`
    **Determine what database is in use:**

    Adding a column with an unique, auto-incrementing ID: `ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY;`
    `select database();`

    Inserting a record: `INSERT INTO [table] ([column], [column]) VALUES ('[value]', '[value]');`
    **Show all tables:**

    MySQL function for datetime input: `NOW()`
    `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);`

    **Adding a column with an unique, auto-incrementing ID:**

    `ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY;`

    **Inserting a record:**

    `INSERT INTO [table] ([column], [column]) VALUES ('[value]', '[value]');`

    **MySQL function for datetime input:**

    `NOW()`

    Selecting records: `SELECT * FROM [table];`

    @@ -138,11 +166,15 @@ SET FOREIGN_KEY_CHECKS=1; -- to re-enable them
    SHOW TABLE STATUS FROM `prism_prepro` WHERE `name` LIKE 'breeding_contract_extra';
    #### LAST_INSERT_ID()
    -- --------------NOT WORK IF INSERT ID------------------

    `insert into bonus_scheme (id, code, description, media_id) values (32, 'TASBOW', 'TASBRED - OWNER ONLY Bonus Scheme', null);`

    `select LAST_INSERT_ID();`

    -- --------------RUN TWO QUERIES AT ONCE-----------------

    `insert into bonus_scheme (code, description, media_id) values ('TASBOW', 'TASBRED - OWNER ONLY Bonus Scheme', null);`

    `select LAST_INSERT_ID();`

    ## How to reset AUTO_INCREMENT
  9. logbasex revised this gist Oct 30, 2020. 1 changed file with 5 additions and 4 deletions.
    9 changes: 5 additions & 4 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -137,12 +137,13 @@ SET FOREIGN_KEY_CHECKS=1; -- to re-enable them

    SHOW TABLE STATUS FROM `prism_prepro` WHERE `name` LIKE 'breeding_contract_extra';
    #### LAST_INSERT_ID()
    insert into bonus_scheme (id, code, description, media_id) values (32, 'TASBOW', 'TASBRED - OWNER ONLY Bonus Scheme', null); -- not work with id
    select LAST_INSERT_ID();
    -- --------------NOT WORK IF INSERT ID------------------
    `insert into bonus_scheme (id, code, description, media_id) values (32, 'TASBOW', 'TASBRED - OWNER ONLY Bonus Scheme', null);`
    `select LAST_INSERT_ID();`

    -- --------------RUN TWO QUERIES AT ONCE-----------------
    insert into bonus_scheme (code, description, media_id) values ('TASBOW', 'TASBRED - OWNER ONLY Bonus Scheme', null);
    select LAST_INSERT_ID();
    `insert into bonus_scheme (code, description, media_id) values ('TASBOW', 'TASBRED - OWNER ONLY Bonus Scheme', null);`
    `select LAST_INSERT_ID();`

    ## How to reset AUTO_INCREMENT

  10. logbasex revised this gist Oct 30, 2020. 1 changed file with 19 additions and 1 deletion.
    20 changes: 19 additions & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -129,4 +129,22 @@ Convert milisecond to datetime: `select from_unixtime(1591711200000/1000);`

    SET FOREIGN_KEY_CHECKS=0; -- to disable foreign check

    SET FOREIGN_KEY_CHECKS=1; -- to re-enable them
    SET FOREIGN_KEY_CHECKS=1; -- to re-enable them

    # Problems

    ## Check AUTO_INCREMENT value

    SHOW TABLE STATUS FROM `prism_prepro` WHERE `name` LIKE 'breeding_contract_extra';
    #### LAST_INSERT_ID()
    insert into bonus_scheme (id, code, description, media_id) values (32, 'TASBOW', 'TASBRED - OWNER ONLY Bonus Scheme', null); -- not work with id
    select LAST_INSERT_ID();

    -- --------------RUN TWO QUERIES AT ONCE-----------------
    insert into bonus_scheme (code, description, media_id) values ('TASBOW', 'TASBRED - OWNER ONLY Bonus Scheme', null);
    select LAST_INSERT_ID();

    ## How to reset AUTO_INCREMENT

    ALTER TABLE tablename AUTO_INCREMENT = value;

  11. logbasex revised this gist Jul 3, 2020. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -128,4 +128,5 @@ Convert milisecond to datetime: `select from_unixtime(1591711200000/1000);`
    ## Another

    SET FOREIGN_KEY_CHECKS=0; -- to disable foreign check

    SET FOREIGN_KEY_CHECKS=1; -- to re-enable them
  12. logbasex revised this gist Jul 3, 2020. 1 changed file with 6 additions and 1 deletion.
    7 changes: 6 additions & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -123,4 +123,9 @@ Like concat: `SELECT url FROM media where url like concat('%','youtube','%');`

    Find mysql version: `SELECT @@version;`

    Convert milisecond to datetime: `select from_unixtime(1591711200000/1000);`
    Convert milisecond to datetime: `select from_unixtime(1591711200000/1000);`

    ## Another

    SET FOREIGN_KEY_CHECKS=0; -- to disable foreign check
    SET FOREIGN_KEY_CHECKS=1; -- to re-enable them
  13. logbasex revised this gist Jun 10, 2020. 1 changed file with 3 additions and 1 deletion.
    4 changes: 3 additions & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -121,4 +121,6 @@ View as another date format: `SELECT DATE_FORMAT(column_name, '%m/%d/%Y %H:%i')
    ## MySQL function
    Like concat: `SELECT url FROM media where url like concat('%','youtube','%');`

    Find mysql version: `SELECT @@version;`
    Find mysql version: `SELECT @@version;`

    Convert milisecond to datetime: `select from_unixtime(1591711200000/1000);`
  14. logbasex revised this gist May 28, 2020. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -120,4 +120,5 @@ View as another date format: `SELECT DATE_FORMAT(column_name, '%m/%d/%Y %H:%i')

    ## MySQL function
    Like concat: `SELECT url FROM media where url like concat('%','youtube','%');`

    Find mysql version: `SELECT @@version;`
  15. logbasex revised this gist May 28, 2020. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -119,4 +119,5 @@ Grant `ALL` access to user for `*` tables: `GRANT ALL ON database.* TO 'user'@'l
    View as another date format: `SELECT DATE_FORMAT(column_name, '%m/%d/%Y %H:%i') FROM table_reference;` (Default date format of MySQL is: `YYYY-MM-dd`)

    ## MySQL function
    Like concat: `SELECT url FROM media where url like concat('%','youtube','%');`
    Like concat: `SELECT url FROM media where url like concat('%','youtube','%');`
    Find mysql version: `SELECT @@version;`
  16. logbasex revised this gist May 15, 2020. 1 changed file with 0 additions and 10 deletions.
    10 changes: 0 additions & 10 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,15 +1,5 @@
    # MySQL

    ## Getting started
    - http://www.sqlteaching.com/
    - https://www.codecademy.com/learn/learn-sql
    - https://www.codecademy.com/catalog/language/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://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins)

    ## Tools
    - [TablePlus](https://tableplus.io/)
    - [DataGrip](https://www.jetbrains.com/datagrip/)
  17. logbasex revised this gist May 15, 2020. 1 changed file with 4 additions and 1 deletion.
    5 changes: 4 additions & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -126,4 +126,7 @@ Grant `ALL` access to user for `*` tables: `GRANT ALL ON database.* TO 'user'@'l
    `SHOW VARIABLES WHERE Variable_name = 'hostname';` ([source](http://serverfault.com/a/129646))

    ## Common problem
    Wiew as another date format: `SELECT DATE_FORMAT(column_name, '%m/%d/%Y %H:%i') FROM table_reference;` (Default date format of MySQL is: `YYYY-MM-dd`)
    View as another date format: `SELECT DATE_FORMAT(column_name, '%m/%d/%Y %H:%i') FROM table_reference;` (Default date format of MySQL is: `YYYY-MM-dd`)

    ## MySQL function
    Like concat: `SELECT url FROM media where url like concat('%','youtube','%');`
  18. logbasex revised this gist May 14, 2020. 1 changed file with 4 additions and 1 deletion.
    5 changes: 4 additions & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -123,4 +123,7 @@ 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))
    `SHOW VARIABLES WHERE Variable_name = 'hostname';` ([source](http://serverfault.com/a/129646))

    ## Common problem
    Wiew as another date format: `SELECT DATE_FORMAT(column_name, '%m/%d/%Y %H:%i') FROM table_reference;` (Default date format of MySQL is: `YYYY-MM-dd`)
  19. @hofmannsven hofmannsven revised this gist May 12, 2020. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -3,6 +3,7 @@
    ## Getting started
    - http://www.sqlteaching.com/
    - https://www.codecademy.com/learn/learn-sql
    - https://www.codecademy.com/catalog/language/sql

    ### Related tutorials
    - [MySQL-CLI](https://www.youtube.com/playlist?list=PLfdtiltiRHWEw4-kRrh1ZZy_3OcQxTn7P)
  20. @hofmannsven hofmannsven revised this gist May 12, 2020. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -39,7 +39,7 @@ Adding a column: `ALTER TABLE [table] ADD COLUMN [column] VARCHAR(120);`

    Adding a column with an unique, auto-incrementing ID: `ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY;`

    Inserting a record: `INSERT INTO [table] ([column], [column]) VALUES ('[value]', [value]');`
    Inserting a record: `INSERT INTO [table] ([column], [column]) VALUES ('[value]', '[value]');`

    MySQL function for datetime input: `NOW()`

  21. @hofmannsven hofmannsven revised this gist Jul 15, 2019. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -7,7 +7,7 @@
    ### 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)
    - [SQL joins infografic](https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins)

    ## Tools
    - [TablePlus](https://tableplus.io/)
  22. @hofmannsven hofmannsven revised this gist Mar 21, 2019. 1 changed file with 3 additions and 3 deletions.
    6 changes: 3 additions & 3 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,15 +1,15 @@
    # MySQL

    ## Getting started:
    ## Getting started
    - http://www.sqlteaching.com/
    - https://www.codecademy.com/learn/learn-sql

    Related tutorials:
    ### 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)

    Tools:
    ## Tools
    - [TablePlus](https://tableplus.io/)
    - [DataGrip](https://www.jetbrains.com/datagrip/)
    - [Sequel Pro](http://www.sequelpro.com/) (abandoned)
  23. @hofmannsven hofmannsven revised this gist Mar 21, 2019. 1 changed file with 10 additions and 24 deletions.
    34 changes: 10 additions & 24 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,23 +1,20 @@
    MySQL
    ===============
    # MySQL

    Getting started:
    ## Getting started:
    - http://www.sqlteaching.com/
    - https://www.codecademy.com/courses/learn-sql
    - https://www.codecademy.com/learn/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)

    Tools:
    - [TablePlus](https://tableplus.io/)
    - [DataGrip](https://www.jetbrains.com/datagrip/)
    - [Sequel Pro](http://www.sequelpro.com/)


    Commands
    -----------
    - [Sequel Pro](http://www.sequelpro.com/) (abandoned)

    ## Commands
    Access monitor: `mysql -u [username] -p;` (will prompt for password)

    Show all databases: `show databases;`
    @@ -93,10 +90,7 @@ Import a database dump (more info [here](http://stackoverflow.com/a/21091197/181

    Logout: `exit;`


    Aggregate functions
    -----------

    ## 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];`
    @@ -111,10 +105,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];`


    Multiple tables
    -----------

    ## 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];`
    @@ -123,17 +114,12 @@ 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
    -----------

    ## 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
    -----------
    ## Find out the IP Address of the Mysql Host
    `SHOW VARIABLES WHERE Variable_name = 'hostname';` ([source](http://serverfault.com/a/129646))
  24. @hofmannsven hofmannsven revised this gist Mar 14, 2019. No changes.
  25. @hofmannsven hofmannsven revised this gist Jul 4, 2017. 1 changed file with 5 additions and 5 deletions.
    10 changes: 5 additions & 5 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -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
  26. @hofmannsven hofmannsven revised this gist Jul 28, 2016. 1 changed file with 6 additions and 0 deletions.
    6 changes: 6 additions & 0 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -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];`
  27. @hofmannsven hofmannsven revised this gist Mar 15, 2016. 1 changed file with 4 additions and 0 deletions.
    4 changes: 4 additions & 0 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -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
    -----------
  28. @hofmannsven hofmannsven revised this gist Feb 26, 2016. 1 changed file with 4 additions and 3 deletions.
    7 changes: 4 additions & 3 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -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
  29. @hofmannsven hofmannsven revised this gist Feb 5, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -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))
  30. @hofmannsven hofmannsven revised this gist Feb 5, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -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)