MySQL is a powerful relational database management system that uses SQL (Structured Query Language) to manage and query data. This guide focuses on the fundamental MySQL syntax, including how to create databases, design tables, and perform basic operations like inserting, querying, updating, and deleting data.

1. Creating a Database

To create a new database in MySQL, use the following command:

1
CREATE DATABASE my_database;

To select and start using the database:

1
USE my_database;

2. Creating Tables

Tables are the core structures in a relational database. Here’s how to create a basic table:

1
2
3
4
5
6
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Explanation:

  • id: A unique identifier with auto-increment.
  • name: Stores the user’s name, up to 100 characters.
  • email: Ensures emails are unique for each user.
  • created_at: Automatically stores the record’s creation time.

3. Inserting Data

Add records to a table using the INSERT INTO command:

1
2
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');

4. Querying Data

Retrieve data from a table using the SELECT statement:

Select All Records:

1
SELECT * FROM users;

Select Specific Columns:

1
SELECT name, email FROM users;

Adding Conditions with WHERE:

1
SELECT * FROM users WHERE name = 'Alice';

5. Updating Data

Modify existing records in a table with the UPDATE command:

1
UPDATE users SET email = 'alice.new@example.com' WHERE name = 'Alice';

6. Deleting Data

Remove records from a table using the DELETE statement:

1
DELETE FROM users WHERE name = 'Bob';

7. Additional SQL Concepts

Sorting Results (ORDER BY):

1
SELECT * FROM users ORDER BY created_at DESC;

Filtering Results (LIMIT):

1
SELECT * FROM users LIMIT 5;

Counting Records:

1
SELECT COUNT(*) AS user_count FROM users;

8. Combining Data with Joins

To retrieve data from multiple tables, use JOIN:

1
2
3
SELECT users.name, orders.order_date
FROM users
JOIN orders ON users.id = orders.user_id;

9. Dropping Databases or Tables

Deleting a Table:

1
DROP TABLE users;

Deleting a Database:

1
DROP DATABASE my_database;

Conclusion

This guide covers the essential MySQL commands needed to start working with databases. By practicing these operations, you’ll develop a strong foundation in SQL and MySQL. Continue exploring advanced features like indexing, stored procedures, and query optimization to further enhance your database skills.