MySQL Database Management: Master Data Optimization

Explore installing MySQL on Mac, Windows, and Linux, including headless server considerations. Use web-based clients, phpMyAdmin, and package managers like MAMP, WAMP, and XAMPP to set up the environment.

Install and manage package managers for local web servers, including MAMP and XAMPP-style tools, across Mac, Windows, and Linux. Configure Apache and MySQL ports, and use phpMyAdmin to manage users.

Set up a MySQL working environment via the command line by logging in, listing databases, importing files, and starting or stopping the server on Linux or Windows with utf-8.

Learn to configure MySQL time zone settings by checking the current time_zone, loading time zone data, and applying set time_zone values to align database times with your region.

Master MySQL basics by starting queries with keywords like select, update, delete, or create, using from and where clauses, count and ascii functions, and aliases with order by.

Explore mysql row selection: query without a table, select all vs specific columns, order by, alias with as, count rows, and limit offset for pagination.

Learn to select specific columns in MySQL, such as name, code, region, and population from country, instead of using *, and use as aliases with quotes for names with spaces.

Learn how to sort query results using order by with asc and desc, observe default ordering, and apply ordering to name, continent, and region in the country table.

Explore filtering with the where clause to show only country records by population and continent. Learn to handle no population values and combine conditions with and or.

Learn to use the MySQL like and in clauses to filter country data by name patterns and continent values, employing wildcard % and _ to match flexible search terms.

In this lecture, learn how to replace the like operator with regular expressions in MySQL using regexp and rlike, including anchors, character classes, ranges, and quantifiers, and see practical examples.

Master inserting data with insert into in MySQL by creating a test table with columns a int, b text, and c text, then inserting values and selecting results.

Update records with update set syntax and where clauses, targeting specific rows and changing the c column values from 2 to no while a remains 2.

Learn how delete and drop commands affect data and tables, use where clauses to target rows, and alter a table to delete a column.

Explore literal strings in MySQL, compare single and double quotes, and learn portable string construction; use concat and escaping techniques to safely concatenate text.

NULL represents no value in MySQL; use is null or is not null, not the equals sign. Differentiate empty string and zero; note empty string is not NULL.

Create and use databases in MySQL, and practice dropping databases and tables. Run basic queries to create, insert, select, and verify data.

Create test table in a scratch database, define id, name, city, state, and zip, then view its structure with describe test or show fields from test, and drop if exists.

Explore how indexes speed data access, implement on primary and foreign keys, and manage single- and multi-column indexes with create, drop, and test workflows.

Explore constraints in MySQL by creating and dropping test tables, inserting values, and applying not null, default, and unique constraints, while observing integrity violations and index implications.

Define a primary key with auto_increment or serial to generate unsigned, unique IDs, describe and show table structures, and retrieve the last_insert_id after inserts.

Alter existing tables in MySQL by adding or dropping columns, keys, and constraints, reorder columns with after, before, or first, and confirm changes with show create table.

Explore MySQL data types, including integers, fixed and floating point numbers, fixed and variable length strings (char and varchar), binary, blob and text, date and time, and enum versus set.

Explore MySQL numeric types, from tinyint and int—including unsigned ranges—to fixed-point decimal and numeric, and differentiate decimal from floating point types like float, double, and real, with money use cases.

Explore the full range of MySQL string types, including char, varchar, binary strings, and blob and text variants, with notes on length, padding with zeros, and usage.

Discover MySQL date and time types, including date, time, datetime, and timestamp, and learn best practices for four‑digit years, current timestamp, and time zone setup.

Examine the bit data type in MySQL by creating a table with id serial, then dropping and recreating it to illustrate 7 for 3 bits and 31 for 5 bits.

Explore how boolean types map to tinyint in MySQL, with true and false as 1 and 0, and set defaults to zero while using not for flags.

This lecture explains enum and set data types in MySQL, showing set allows multiple values via indexes and enum allows a single value, with examples using Pablo, Henry, and Jackson.

Explore MySQL string functions such as length, char_length, left, right, mid, concat, locate, upper, lower, and reverse, demonstrated on the world.country table.

Explore MySQL numeric functions, from division and modulus to power, absolute value, sign, base conversions, pi, rounding, truncation, floor, and random numbers with seeding.

Explore MySQL date and time functions, including now, current_timestamp, and unix_timestamp. Work with UTC time, day, day of year, month name, and interval arithmetic while setting up time zones.

Learn how MySQL handles time zones, view and set time_zone values to Europe/London or America/Los_Angeles, install time zone data, and use UTC timestamps for consistent timing across regions.

Explore the dtae_format function to format and extract date and time in MySQL, using two parameters and common specifiers for year, month, day, hour, minute, and second.

Explore MySQL grouping functions by counting rows, counting by column, and grouping by continent; learn group_concat with separators and basic aggregates like avg, min, max, and sum.

Explore MySQL conditional functions using boolean values, case when syntax, and true or false results, with examples of 1 and 0 and a primer on transactions.

Explore how to use transactions in MySQL to start a transaction, perform inventory and widget sales queries, and apply commit or roll back to ensure steps succeed or fail together.

Use transactions to boost database performance by wrapping insert operations with start transaction and commit, speeding up large data work and query counts.

Learn how to create a MySQL trigger that automatically updates a customer's last order id after inserting a new widget sale, using after insert and per-row logic.

Learn to prevent automatic updates with a MySQL before update trigger on widget_sale, using begin end blocks, conditional checks on new.reconciled, and error signaling to block updates.

Learn to implement a trigger and a transaction in MySQL to log sales events, update the last order id for a customer, and record timestamps in a log table.

Explore substring and substr functions and subselects, and learn to alias results and join with a country table to display country names and codes.

Learn to search within a result set by joining albums and tracks on their keys, filtering by duration under 90, and presenting album and track details with aliases.

Learn to create and use views in MySQL to save complex queries as reusable tables, then query, join, and drop them for streamlined data analysis.

Learn how to create a join view that combines artists and tracks, using a select statement and string functions like concatenation and lpad to format duration in minutes and seconds.

Explore stored routines, functions, and procedures centralized on the server, using create function, create procedure, and call statements to return values or result sets while boosting security and performance.

Create and use MySQL functions by defining parameters, return types, and deterministic behavior, including drop if exists, function syntax, and applying the function in queries to compute durations.

Explore MySQL stored procedures: drop and create, begin-end blocks, delimiter management, call syntax with in/out parameters, and how procedures differ from functions.

Learn how to connect a PHP application to a database using PDO, configure host, database name, username, and password, and embrace PDO’s object-oriented data access.

Explore using PDO and prepared statements in PHP to build queries with ? placeholders, execute with parameter arrays, and fetch results for display.