Developer
MySQL Guide
Install MySQL, learn basic usage, and copy common SQL statements.
| Category | SQL / Command | Explanation | |
|---|---|---|---|
| Connect | mysql -u root -p | Open the MySQL client and authenticate as root. | |
| Database | SHOW DATABASES; | List all databases visible to the current user. | |
| Database | CREATE DATABASE app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; | Create a UTF-8 compatible database. | |
| Database | USE app_db; | Select a database for following statements. | |
| Table | SHOW TABLES; | List tables in the current database. | |
| Table | CREATE TABLE users (id BIGINT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(255) UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP); | Create a basic users table. | |
| Table | DESCRIBE users; | Show columns, types, nullability, keys, and defaults. | |
| CRUD | INSERT INTO users (email) VALUES ('demo@example.com'); | Insert one row. | |
| CRUD | SELECT id, email, created_at FROM users WHERE email LIKE '%@example.com' ORDER BY created_at DESC LIMIT 20; | Query filtered rows with sorting and pagination. | |
| CRUD | UPDATE users SET email = 'new@example.com' WHERE id = 1; | Update matching rows. | |
| CRUD | DELETE FROM users WHERE id = 1; | Delete matching rows. | |
| Join | SELECT u.email, o.total FROM users u JOIN orders o ON o.user_id = u.id; | Join related tables. | |
| Index | CREATE INDEX idx_users_created_at ON users (created_at); | Add an index for faster filtering or sorting. | |
| User | CREATE USER 'app'@'localhost' IDENTIFIED BY 'strong_password'; | Create an application database user. | |
| User | GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app'@'localhost'; | Grant limited application privileges. | |
| Backup | mysqldump -u root -p app_db > app_db.sql | Export a database to a SQL file. | |
| Restore | mysql -u root -p app_db < app_db.sql | Import a SQL dump into a database. |
FAQ
Questions people ask
What is MySQL?
MySQL is a relational database that stores structured data in tables and uses SQL for querying, constraints, joins, indexes, and transactions.
How do I start learning MySQL?
Start with database creation, table design, SELECT/INSERT/UPDATE/DELETE, indexes, joins, and safe backup and restore commands.
Which GUI tools work well with MySQL?
MySQL Workbench, DBeaver, TablePlus, DataGrip, Sequel Ace, and many IDE database panels can connect to MySQL.
Related tools