MySQL: A beginner's guide to MySQL

Learn how structured query language, or SQL, lets you interact with databases to create, read, update, and delete data, with the MySQL flavor highlighted.

Open the MySQL command line on Windows or Mac, log in with your user and password, create a database, and use it to perform create, read, update, and delete operations.

Learn the seven most commonly used MySQL data types—int, big int, decimal, date, date time, time, and varchar—to design tables and store precise numbers, dates, and strings.

Learn to create a mysql table by naming it with underscores, defining columns with proper data types, and setting a primary key; also drop tables with if exists.

Learn the CRUD foundation of SQL, focusing on create and read: insert data into a users table and read it back, while understanding DDL and DML and the database schema.

Learn to read data in MySQL using select from the users table, and follow a hands-on walkthrough of creating a database, and inserting and querying data.

Learn to update and delete data in MySQL by targeting a table, selecting columns, and using a where clause tied to the primary key to affect one row.

Explore how database relationships link tables with foreign keys, differentiate primary keys from foreign keys, and learn why normalization reduces redundancy and anomalies.

Explore normalization in databases, reduce insertion, update, and deletion anomalies by breaking big tables into smaller ones, and learn first to third normal forms and Boyce-Codd normal form.

This lecture explains second and third normal forms, non-key attributes dependent on the entire key, and transitive dependencies, and shows how to split tables to avoid anomalies.

Master one-to-many relationships by identifying the one and many sides, their parent and child tables, and normalization with foreign keys.

Explore real world one to one relationships in databases, identify parent and child tables, and enforce normalization up to third normal form to avoid anomalies.

Explore the many-to-many relationship with an author_book join table linking authors and books. Identify parent and child roles and real-world examples such as authors and books.

Learn to install and use Toad for MySQL, a free tool, connect to databases, write and execute SQL, generate insert and create table scripts, including foreign keys.

learn how to join two database tables using inner and outer joins, including left and right joins, with practical examples using user and address data.

Master left outer joins in MySQL, returning all left-table rows with matching right-table data or nulls. Compare inner and outer joins and briefly explore cross joins.

Explore when to use inner joins versus outer joins, and master left and right joins with practical syntax and examples, plus how to handle missing address data in users.

Learn to group data by imagining buckets and visualizing categories. Use examples like debit and credit transactions and website traffic by hour to quantify totals and peak times.

Explore group by syntax in MySQL, grouping transactions by type and summarizing amounts with aggregate functions, while understanding why select star is inappropriate for grouped data.

Explore aggregate functions and how they operate on grouped data. See how to apply group by to create buckets and calculate max, min, average, sum, and count for each group.

learn how to filter by aggregate functions using the having clause after grouping by bank account and transaction type, to identify customers with balances over $80.

Learn how to use MySQL control flow functions and the having clause to sum adjusted transactions, converting debits to negative, and filter results above 80.

learn how to order data returned by MySQL select queries with order by, using ascending or descending, including multiple columns and placing order by at the end.

Master inserting data with insert into … select, backing up tables, and rolling up data using concat, year, and month functions.

Learn to use year and month string functions in MySQL to extract date parts, concatenate them into proper date strings, and populate a back up table with the 15th.

Learn how to alter existing tables in MySQL by adding, removing, or renaming columns, changing data types, and managing constraints such as foreign keys and primary keys.

Explore how views read data across multiple tables, simplifying complex queries into a single select. Create or replace views, execute them with a select from a view, and hide columns.

Explore subqueries in SQL, including nested selects with the in keyword, compare them to joins, and learn advantages like readability and the ability to run each subquery independently.

Explore real life examples of subqueries, including in, not in, and like, and learn when to use them versus joins in production level queries, with performance considerations.

Learn how a database index speeds up queries by sorting data and keeping the primary key, turning full table scans into faster searches on amounts in a transactions table.

Explore how to create and name indexes in MySQL, including unique and full-text options, and see how indexing affects query speed with real-world examples on the transactions table.

Learn trade-offs of indexing in MySQL, including when to use indexes, how inserts slow down, and index types: primary, unique, regular, and full text, to optimize slow and frequent queries.

Learn to back up your entire MySQL databases with mysql dump, store backups in Dropbox cloud, and automate the process with Windows Task Scheduler for hands-off reliability.

Learn to automate MySQL backups with mysqldump, store backups in safe paths, and schedule daily batch-file backups via task scheduler, then restore from dumps and back up to the cloud.

Learn the differences between stored procedures and stored functions, their advantages, how they use parameters, and how pre-compiled routines perform multiple operations, return data, and reduce application-database round trips.

This lecture covers the syntax of stored procedures and functions in MySQL, focusing on in parameters and delimiter usage with real-world examples like get transactions over 1000.

Master stored procedures in MySQL with conditional logic and in, out, and inout parameters. See examples like get transactions by type and the first transaction on a date.

Learn to create and use stored functions in MySQL, including returns, in parameters, and how they differ from procedures. Use in select and where clauses.

Learn how to navigate MySQL documentation, distinguish ddl and dml concepts, and read core syntax for alter table, update, and insert with practical tips.