1. Access monitor
1 | mysql -u [username] -p; (will prompt for password) |
2. Show all databases:
1 | show databases; |
3. Access database:
1 | mysql -u [username] -p [database] (will prompt for password) |
4.Create new database:
1 | create database [database]; |
5.Select database:
1 | use [database]; |
6. Determine what database is in use:
1 | select database(); |
7. Show all tables:
1 | show tables; |
8. Show table structure:
1 | describe [table]; |
9. List all indexes on a table:
1 | show index from [table]; |
10. Create new table with columns:
1 | CREATE TABLE [table] ([column] VARCHAR(120), [another-column] DATETIME); |
11. Adding a column:
1 | ALTER TABLE [table] ADD COLUMN [column] VARCHAR(120); |
12. Adding a column with an unique, auto-incrementing ID:
1 | ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY; |
13. Inserting a record:
1 | INSERT INTO [table] ([column], [column]) VALUES ('[value]', [value]'); |
14. MySQL function for datetime input:
1 | NOW() |
15. Selecting records:
1 | SELECT * FROM [table]; |
16.Explain records:
1 | EXPLAIN SELECT * FROM [table]; |
17. Selecting parts of records:
1 | SELECT [column], [another-column] FROM [table]; |
18. Counting records:
1 | SELECT COUNT([column]) FROM [table]; |
19.Counting and selecting grouped records:
1 | SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY [column]; |
20. Delete all records in a table:
1 | truncate table [table]; |
21. Removing table columns:
1 | ALTER TABLE [table] DROP COLUMN [column]; |
22. Deleting tables:
1 | DROP TABLE [table]; |
23.Deleting databases:
1 | DROP DATABASE [database]; |
24. Custom column output names:
1 | SELECT [column] AS [custom-column] FROM [table]; |
25. Export a database dump:
1 | mysqldump -u [username] -p [database] > db_backup.sql |
26. Import a database dump (more info here):
1 | mysql -u [username] -p -h localhost [database] < db_backup.sql |
27. Logout:
1 | exit; |
