Video summary

MySQL Course for Beginners

Main summary

Key takeaways

Educational

Main ideas & lessons conveyed

  • MySQL basics for beginners

    • MySQL is a relational database management system used for structuring and managing data.
    • It is part of common web stacks such as:
      • LAMP: Linux, Apache, MySQL, PHP
      • LEMP: Linux, Nginx (engine X), MySQL, PHP

  • Course structure (what you’ll learn)

    • Install MySQL on Rocky Linux 9
    • Start/enable the MySQL service
    • Log into MySQL and run foundational SQL commands
    • Create/drop databases, tables, and manage schemas
    • Import a sample dataset for realistic practice
    • Manage users and privileges
    • Write and refine SELECT queries (filtering, ordering, limits, patterns)
    • Modify data using INSERT / UPDATE / DELETE
    • Understand primary keys and foreign keys
    • Learn relational concepts via JOINs
    • Use views
    • Use indexes (performance tradeoffs)
    • Learn subqueries
    • Create stored procedures
    • Create triggers for automatic auditing
    • Use MySQL dump for backup and restore

  • Hands-on emphasis

    • Commands are explained as “sentence-like” SQL:
      • Most statements end with a semicolon ;
      • Some statements require multi-line structure (e.g., CREATE TABLE)

Step-by-step methodology & instruction bullets (detailed)

1) Install MySQL on Rocky Linux 9

  • Verify OS version/release (informational)
  • Update system packages:
    • Run: sudo dnf update
  • Install MySQL server:
    • Run: sudo dnf install mysql-server
    • Accept dependencies (respond y when prompted)

2) Start and enable the MySQL service

  • Check service status:
    • Run: sudo systemctl status mysqld
    • If inactive, note that Rocky may not start it automatically
  • Start the service:
    • Run: sudo systemctl start mysqld
  • Enable service on boot:
    • Run: sudo systemctl enable mysqld
  • Re-check status to confirm it is active/running and enabled

3) Log into MySQL

  • Use the MySQL client command:
    • sudo mysql -u root -p
  • Notes included:
    • The default root account may have no password set, so login may prompt for password behavior depending on setup.
    • Best practice warning: don’t rely on the insecure default root user; create dedicated users.

4) Basic SQL navigation and exploration

  • Help
    • help <command>;
    • Alternative shown: \h
  • Show databases
    • SHOW DATABASES;
  • Show tables
    • SHOW TABLES;

5) Create/drop databases and tables

  • Create a database
    • CREATE DATABASE <db_name>;
  • Select a database to work with
    • USE <db_name>;
  • Create a table (example: employees)
    • CREATE TABLE employees ( ...columns... );
    • Lesson inside example:
      • Use a primary key (unique identifier)
      • Use AUTO_INCREMENT for sequential IDs
      • Use NOT NULL to prevent empty values
  • Describe a table structure
    • DESCRIBE <table_name>;
  • Drop a table
    • DROP TABLE <table_name>;
  • Drop a database
    • DROP DATABASE <db_name>;
    • Warning: this removes all tables/data in the database

6) Import a large sample dataset (for practice)

  • Dataset hosted on GitHub as an SQL dump (employees dataset)
  • Steps shown:
    • Exit MySQL: exit;
    • Install git:
      • sudo dnf install git
    • Clone repository:
      • git clone <repo_link>
    • CD into dataset directory (choose small, large, or full; full recommended)
    • Import SQL into MySQL:
      • sudo mysql -u root -p < employees.sql (using the chosen dump file)
  • Verify import:
    • Run an included test command using MySQL to check integrity (example: an md5 test file/script referenced as test_employees.md5.sql)

7) Manage MySQL user accounts and privileges

Create users

  • CREATE USER '<user>'@'<host>' IDENTIFIED BY '<password>';
    • Example hosts:
      • localhost (local-only access)
      • % wildcard (access from any host)

Alter user password

  • ALTER USER '<user>'@'<host>' IDENTIFIED BY '<new_password>';

List users

  • SELECT user, host FROM mysql.user;

Drop users

  • DROP USER '<user>'@'<host>';

Grant privileges

  • GRANT <privileges> ON <db>.<table> TO '<user>'@'<host>';
    • Demonstrations include:
      • Granting on one database/all tables: <db_name>.*
      • Granting on all databases/tables: *.*
      • Granting narrowly (database + specific table)

Revoke privileges

  • REVOKE ALL PRIVILEGES ON <db>.<table> FROM '<user>'@'<host>';
  • Lesson: Use REVOKE before re-granting if you want to remove previous broader access.
  • Show current grants
    • SHOW GRANTS FOR '<user>'@'<host>';

Verification

  • Log in as the new user and confirm:
    • SHOW DATABASES; returns only what privileges allow
    • Selected permissions allow limited operations

Querying data with SELECT (core SQL instruction set)

8) Basic SELECT and result control

  • Select all columns
    • SELECT * FROM <table>;
  • Limit rows
    • SELECT * FROM <table> LIMIT 20; (example shown)

9) Select specific columns and reorder them

  • SELECT <col1>, <col2>, <col3> FROM <table> LIMIT <n>;
  • Columns can be listed in any order for output

10) Filtering with WHERE and AND

  • Equality filter:
    • WHERE <column> = '<value>';
  • Compound filter:
    • WHERE <column1> = '<value1>' AND <column2> = '<value2>';

11) Pattern matching with LIKE

  • Example concept shown:
    • WHERE <column> LIKE '%<text>%';
  • Used to filter birth dates “within a year” by pattern matching

12) Ordering results with ORDER BY

  • ORDER BY <column>;
  • Demonstrations:
    • Order by hire_date (oldest → newest)
    • Order by birth_date

Data modification (DML) instructions

13) INSERT

  • INSERT INTO <table> (<col1>, <col2>) VALUES ('<val1>', '<val2>');

14) UPDATE

  • Update with unique identifier:
    • UPDATE <table> SET <col>= '<new_value>' WHERE <unique_col>= '<id_or_value>';

15) DELETE

  • DELETE FROM <table> WHERE <unique_col>= '<id_or_value>';

Keys, relationships, and data integrity

16) Primary keys

  • Defined as a unique identifier for each row.
  • Example concept used:
    • employee_number as primary key

17) Foreign keys

  • Defined as a “bridge” between two tables.
  • Steps shown:
    • Add a new column to the “child” table (employees) to store the reference:
      • ALTER TABLE employees ADD COLUMN department_number CHAR(4); (type adjusted)
    • Add a foreign key constraint:
      • ALTER TABLE employees ADD CONSTRAINT <fk_name> FOREIGN KEY (<department_number>) REFERENCES departments(<department_number>);

18) Verifying join behavior using NULLs

  • Triggered by setting some department numbers to NULL to demonstrate join differences.

JOINs (relational query instruction set)

19) INNER JOIN

  • Returns only rows that match on the join condition:
    • SELECT ... FROM employees INNER JOIN departments ON employees.department_number = departments.department_number;
  • NULL join keys are excluded.

20) LEFT JOIN

  • Returns all rows from the left table and matching rows from the right:
    • Unmatched rows show NULL for right-table columns.

21) RIGHT JOIN (mentioned/demonstrated)

  • Returns all rows from the right table and matching rows from the left.
  • In the demo, it behaves similarly to inner join due to the dataset/relationship setup.

Aggregations + advanced querying

22) Join with GROUP BY + COUNT (example)

  • Goal shown: count employees per department
  • Pattern used:
    • SELECT departments.department_number, departments.department_name, COUNT(employees.employee_number) AS num_employees FROM departments LEFT JOIN employees ON departments.department_number = employees.department_number GROUP BY departments.department_number, departments.department_name;

Views

23) Create a view

  • CREATE VIEW <view_name> AS SELECT ...;
  • Views:
    • Act like a virtual table
    • Auto-update based on underlying base tables

24) Query and drop views

  • Query:
    • SELECT * FROM <view_name>;
  • Drop view:
    • DROP VIEW <view_name>;

25) Example view workflow

  • Add a new column to a base table (departments.emails)
  • Populate it with UPDATE ... WHERE ...
  • Create views that show a subset of columns (e.g., department number + email)
  • After inserting new department rows, the view reflects updates automatically

Indexes

26) Inspect indexes

  • SHOW INDEXES FROM <table>;

27) Create an index

  • CREATE INDEX <index_name> ON <table>(<column>);
  • Example:
    • Create index on last_name

28) Validate performance impact

  • Compare query speed with and without the index using a WHERE filter on the indexed column

29) Drop an index (as demonstrated)

  • Dropping is shown via table/alter pattern:
    • ALTER TABLE <table> DROP INDEX <index_name>;

30) Tradeoffs noted

  • Indexes speed reads, but can slow writes and consume disk/memory and require maintenance.

Subqueries

31) Filtering with a subquery (example shown)

  • Main SELECT filters employees by a department discovered in a subquery:
    • SELECT * FROM employees WHERE department_number = (SELECT department_number FROM departments WHERE department_name='Sales');

Stored procedures

32) Create a stored procedure

  • Need to change delimiter while defining the procedure:
    • DELIMITER //// (example uses slashes)
  • Then:
    • CREATE PROCEDURE <procedure_name>(...) BEGIN ... <query> ... END ////;
  • Demo procedure is based on the earlier aggregation/join query.

33) Call the stored procedure

  • CALL <procedure_name>();

34) Drop stored procedure

  • DROP PROCEDURE <procedure_name>;

Triggers

35) Purpose: auditing changes

  • Create an audit table that logs events when updates occur to the main table (employees).

36) Create audit log table

  • CREATE TABLE employee_audits ( audit_id INT AUTO_INCREMENT PRIMARY KEY, department_number <type>, action VARCHAR(50), action_date TIMESTAMP );

37) Create an AFTER UPDATE trigger

  • Change delimiter for multi-statement trigger definition:
    • DELIMITER ////
  • Trigger structure:
    • CREATE TRIGGER <trigger_name> AFTER UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO employee_audits (...) VALUES (... NEW.<column> ...); END ////
  • Demo:
    • Insert audit record using updated/new department values
    • Uses NOW() for action timestamp
    • Writes action text like “update”

38) Verify triggers

  • Show triggers:
    • SHOW TRIGGERS;
  • Perform an UPDATE on employees
  • Confirm insert into audit table via SELECT:
    • SELECT * FROM employee_audits;

39) Debugging note

  • The demo includes a schema mistake (audit table column type mismatched) and fixes it using:
    • ALTER TABLE <audit_table> MODIFY <column> <correct_type>;

Backup and restore with MySQL dump

40) Backup with mysqldump (MySQL dump)

  • Exit MySQL first (because it’s a different utility/flow in the terminal)
  • Backup command shown as:
    • sudo mysqldump -u root -p <db_name> > <output_file>.sql
  • Demo output:
    • Creates an .sql dump file in the working directory
    • Shows file size via ls -lh (human-readable)

41) Restore from backup

  • Create an empty database with the same name as the dump expects:
    • CREATE DATABASE <db_name>;
  • Restore:
    • sudo mysql -u root -p <db_name> < <backup_file>.sql
  • Verify:
    • SHOW DATABASES;
    • USE <db_name>;
    • SHOW TABLES;
    • Run SELECT queries to confirm data and dependent objects (e.g., audit/trigger-related artifacts)

Speakers / sources featured

  • Josh from KeepItTechie (primary speaker/teacher; also identified as a SQL Server database administrator)
  • External dataset source: a GitHub repository providing the “employees” test database (referenced as created by “bike base” in the subtitles; exact account name unclear due to subtitle errors)

Original video