TASKS 02 : Introduction to MySQL & Basic Database Operations - RadLeoOFC/laravel-admin-panel GitHub Wiki

1. MySQL Fundamentals

Creating a Database

CREATE DATABASE intern_db;

• Description: Creates the intern_db database to store project data.
• Screenshot: Execution of the command and list of databases (SHOW DATABASES;).

---

Selecting the Database

USE intern_db;

• Description: Sets intern_db as the active database for operations.
• Screenshot: Confirmation of the active database.

---

Creating a Table

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• Description: Creates a users table with columns for id, name, email, and created_at.
• Screenshot: Table structure displayed using DESCRIBE users.

---

Inserting Data

INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]');

• Description: Adds a new user to the users table.
• Screenshot: The table content after inserting the data.

---

Reading Data

SELECT * FROM users;

The command retrieves all rows and all columns from the users table.

• SELECT: Retrieves data.
• *: Selects all columns.
• FROM users: Specifies the table.

Use Case: To view all data in the users table after performing operations like INSERT, UPDATE, or DELETE.

• Description: Retrieves all records from the users table.
• Screenshot: Table content displaying all rows after the query.

---

Updating Data

UPDATE users SET name = 'Jane Doe' WHERE email = '[email protected]';

• Description: Updates the name of the user with the email [email protected] to Jane Doe.
• Screenshot: Table content after the update query.

---

Deleting Data

DELETE FROM users WHERE email = '[email protected]';

• Description: Deletes the user with the email [email protected] from the users table.
• Screenshot: Table content before and after the delete query.

---

---

2. Laravel Database Configuration

Updating the .env File

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=intern_db
DB_USERNAME=root
DB_PASSWORD=secret

• Description: Configures Laravel to connect to the intern_db MySQL database using the specified credentials.
• Screenshot: The .env file with updated database credentials (sensitive data like passwords should to be blurred).

---

Testing the Connection

php artisan migrate

• Description: Verifies Laravel's connection to the database by running the default migrations.
• Screenshot: Terminal output showing the success or errors of the migration command.

---

---

3. Introduction to Migrations

Laravel default migration

• Description: Locate the create_users_table.php file in the database/migrations directory
• Screenshot: A screenshot of the database/migrations directory showing the create_users_table.php file.

Generating a Migration for the products Table

php artisan make:migration create_products_table --create=products

• Description: Generates a new migration file to create the products table.
• Screenshot: The generated migration file in the database/migrations directory.

---

Defining the Table Structure

public function up()
{
    Schema::create('products', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->decimal('price', 8, 2);
        $table->text('description')->nullable();
        $table->timestamps();
    });
}

• Description: Defines the table products with the following columns:
  • id: Auto-incrementing primary key.
  • name: A string for the product name.
  • price: A decimal value with two decimal places.
  • description: An optional text field for product details.
  • timestamps: created_at and updated_at fields.
• Screenshot: Display the content of the migration file.

---

Running the Migration

php artisan migrate

• Description: Applies the migration, creating the products table in the database.
• Screenshot: Terminal output of the migration command and the productstable visible in the database (e.g., MySQL Workbench or phpMyAdmin).

---

---

4. Push Changes to GitHub

Adding and Committing Changes

git add .

git commit -m "Added products table migration"

• Description: Stages all changes and commits them to the local Git repository with a clear commit message.
• Screenshot: Terminal output of git status and git commit commands showing successful staging and commit.

---

Pushing Changes to the Remote Repository

git push --set-upstream origin master

git push

• Description: Pushes the committed changes to the remote repository on GitHub.
• Screenshot: Terminal output confirming that the changes were successfully pushed to the remote repository.

---

---

5. Best Practices

1. Use snake_case for Table and Column Names

Consistently naming tables and columns in lowercase with underscores (e.g., users, order_items) enhances readability and ensures compatibility across various database systems. This naming convention is widely accepted and avoids conflicts that may arise due to case sensitivity in certain databases.

Examples:

• Tables: products, user_profiles
• Columns: first_name, created_at

2. Keep Migrations Atomic

• Each migration should be limited to a single, specific change. For instance:
  • Creating one table.
  • Adding a single column to an existing table.
  • Removing or modifying one specific database feature.
• This approach simplifies debugging and makes it easier to identify the source of an issue if a migration fails.
• Atomic migrations also allow for smoother rollback operations without affecting unrelated database changes.

3. Write Clear Commit Messages

• Commit messages should provide a brief and descriptive summary of the changes made, helping others (and future developers) quickly understand the purpose of the commit.
• Use a simple structure for commit messages:
  • Title: A concise description of the change (e.g., "Added products table migration").
  • Optional Details: Additional context or explanation if necessary (e.g., "Added columns for name, price, and description.").
• Clear commit messages improve version control transparency and make collaboration within teams more effective.

---

Conclusion

The task provided basic knowledge of working with MySQL and integrating it with Laravel, focusing on fundamental database operations and migrations.

1. Learned basic SQL commands for managing databases and tables.
2. Configured Laravel to connect to MySQL using the .env file.
3. Applied migrations to create and manage database tables.

These skills serve as a starting point for further exploration of database management and Laravel development.

Task completed by Radislav Lebedev