You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Listed in commands.md, useful commands in mysql that I find myself either using frequently enough to note them or they are used infrequently but incredibly helpful that I don't want to forget them.
Example: UPDATE names SET first = replace(first, 'Mike', 'Michael');
This will update all records in the names table, first field that have Mike and change them to Michael.
UPDATE [table] SET [column] = replace([column], '[old string]', '[new string]'); WHERE [column] like '%[string]%'
This allows you to only affect rows with a certain string anywhere in them.
UPDATE [table] SET [column] = replace([column], '[old string]', '[new string]'); WHERE [column] like '%[string]'
This allows you to only affect rows that end with a certain string.
UPDATE [table] SET [column] = replace([column], '[old string]', '[new string]'); WHERE [column] like '[string]%'
This allows you to only affect rows that start with a certain string.
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];
Deleting databases: DROP DATABASE [database];
Custom column output names: SELECT [column] AS [custom-column] FROM [table];
Export a database dump (more info here): mysqldump -u [username] -p [database] > db_backup.sql
Use --lock-tables=false option for locked tables (more info here).
Import a database dump (more info here): mysql -u [username] -p -h localhost [database] < db_backup.sql
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];
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
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)