Database Design and MySQL

Learn char and varchar data types in MySQL, compare fixed-length versus variable-length, how spaces are stored, and when to use char for fixed fields like zip codes.

Choose the smallest text type that fits your data: varchar for small entries like names, and text for larger content, mindful of storage overhead and utf-8 characters.

Explore enum and set data types in MySQL, comparing single-select enumeration with multi-select sets, illustrated by gender, states, and book genres, and how they relate to primary keys.

Explore binary, varbinary, and blobs in MySQL, comparing binary storage as byte strings without a character set, with byte sizes and padding, and its relation to text types.

Master signed and unsigned integers, from 1-byte 0-255 and -128 to 127 to 0-65535, and choose the smallest type that fits.

Explore the differences between decimal, float, and double in MySQL, covering precision and scale, storage sizes, and when to prefer exact decimal values for numeric accuracy.

Learn how bit and boolean values map to true and false. Explore unsigned big integers, autoincrement, unique constraints, and serial numbers in MySQL.

Explore date, datetime, time, and year data types, including formats, acceptable ranges, and six-decimal microseconds, with guidance on server requests and handling invalid inputs.

Explain that a timestamp is seconds since the unix epoch (1970) and that UTC stands for Coordinated Universal Time, which standardizes time zones for database inserts.

Discover how MySQL operates as a relational database management system that stores customer data in tables and rows, and how server-side scripts enable web forms, authentication, and data access.

Learn how data is stored in tables and how normalization reduces redundancy by splitting information into related tables, covering first, second, and third normal forms and primary keys.

Explore database relationships: one-to-one, one-to-many, and many-to-many, with practical examples and learn how to convert many-to-many into one-to-many using keys.

Learn how primary keys and foreign keys design relational MySQL databases, using auto-incremented IDs to keep records unique and linked across customers and orders.

Explore string and binary data types in MySQL, including char, varchar, binary, blob, and text variants; compare enum and set and introduce database normalization.

Learn about numeric data types in databases, including signed versus unsigned integers, floats, doubles, decimals for exact precision, and booleans, with guidance on choosing appropriate sizes.

Explore date and time datatypes in MySQL, including date, time, and timestamp with fractional seconds and epoch milliseconds since 1970.

Explore column features in MySQL, including signed versus unsigned values, not null constraints, auto increment keys, default values, and zero fill to preserve leading zeros.

Learn how indexes speed data retrieval through sorting and limits, with examples like last names and account balances, and explore four types: plain, full text, primary key, and unique.

Learn how quoted strings work and how to escape characters with backslashes, including escaping quotes, using double or single quotes, and producing new lines and tabs.

Learn how to build a database from a gym example, including creating databases and tables. Explore defining fields, normalization, and a many-to-many relationship between members and workouts with primary keys.

Learn how to alter tables, add columns such as first name and last name, and use where clauses to update and retrieve precise MySQL data.

Explore MySQL joins and select queries by using where clauses to filter gold members, sort by last name or sign-up date, and limit results to the first 10.

Install XAMPP on Windows, run the installer, and open phpMyAdmin from the control panel to manage databases and export or import data.

Define the gym database structure by outlining columns such as an auto-incrementing primary key, split name into first and last, birthday, 10-character phone with default zeros, and a membership status enum.

Create a database and a members table via command line or phpmyadmin. Define member_id, name, birthday, address, phone, and membership_status with a primary key and autoincrement.

Learn capitalization rules for SQL keywords, data types, and primary keys, and understand case insensitivity and whitespace in create table statements. Explore comments, semicolon usage, and dropping tables.

Alter the members table to add first name and last name columns with appropriate data types, then drop the old column, demonstrating basic sql alter table commands.

This lecture shows how to insert rows into the members table using insert into and values, and how to update a member's birthday with a where clause on member_id.

Insert rows into the Members table with insert into, listing columns and values, including multiple rows. Retrieve birthdays, phone numbers, and names from the Members table using select statements.

Discover show commands in MySQL to inspect a table’s structure, using show tables, show columns from the members table, and show create table to reveal fields, types, keys, and defaults.

Explore alter table commands in a database, including renaming tables, adding a column like favorite food, modifying and changing column properties, and dropping unused columns.

Learn to design consistent MySQL databases using descriptive names for databases, tables, and columns, with clear primary and foreign keys. Maintain lowercase identifiers, single-quote usage, and readable, line-by-line queries.

Learn to write select statements with where clauses to filter rows, pick specific columns like first name, last name, and phone, and combine conditions with and/or.

Learn how to use the order by clause to sort records, specify ascending or descending order, and organize results by fields like last name or membership status.

Use the limit clause with order by to return a small set of results, such as the three oldest people by birthday from the members table.

Learn how to update specific columns in MySQL using the update command, set clauses, and where conditions, with a focus on using the primary key to target a single row.

Learn to delete data in MySQL by using delete from table with a where clause to target specific rows, apply a limit for safety, and beware drop table risks.

Explore character sets and collations, including UTF-8 and ASCII, and learn how different encodings use bytes and how collations determine case sensitive or case insensitive ordering.

Discover how to search your database using like and not like with wildcards. Use percent and underscore to match patterns, such as John, Bonnie, or Bobby in a where clause.

Explore MySQL storage engines, the two most common options, and how transactions, full text search, and foreign key constraints shape data handling and engine choice.

Create a messages table with id and message, set message-id as primary key, and use the MyISAM storage engine for full text search.

Explore comparison operators in MySQL, including the equal sign, less than, greater than, and the in, between, not between, and is null tests, applied in where clauses with and/or logic.

Learn how foreign key constraints in the InnoDB engine link customers, orders, and comments to preserve referential integrity, with cascade and set options for updates and deletes.

Explore how joins combine data from two tables, understand inner and outer joins, and see how normalization with primary and foreign keys enables meaningful, efficient queries.

Explore how an inner join combines data from two tables, such as customers and orders, to return only matching records using on and customer ID relationships.

Learn how left, right, and full outer joins differ from inner joins using a users and reviews example, and how left joins retain unmatched records from the left table.

Master inner joins to combine customers with their reviews, replacing ids with first and last names for results. Learn join syntax, on clauses, and tips on inner and outer joins.

Explore how outer and inner joins differ using left joins and examples with customers and reviews, and learn to combine queries with unions and distinct results.

Explore how mysql functions act as shortcuts that take input and return output. See upper and cat/concat examples to format names and join fields.

Learn how to use aliases in MySQL to rename query results with as, turning expressions into readable column names. Use examples like date format and employee ID to simplify queries.

Learn to use aliases with joins and functions in MySQL, including inner joins, concatenating first and last names, applying separators, and aliasing tables for readable queries.

Explore text functions in MySQL, including length, char_length, left, right, and trim, and learn how to count bytes and characters and remove leading or trailing spaces.

Master string manipulation in MySQL with upper and lower, substring to extract sections, replace to censor, and locate to find character positions, including negative indices.

Learn the six basic math operators—plus, minus, multiplication, division, and modulus—and their precedence. Use parentheses to clarify complex expressions.

Explore numeric functions in MySQL, learning how absolute value returns distance from zero, how square roots are computed, and how truncation limits decimals.

Explore MySQL date and time functions to extract year, month, day, and time, format dates, and work with now, UTC timestamps, Unix timestamps, and time zones.

Learn how to format dates for MySQL using percent codes, including year, month, and day specifiers, and how to reorder components to produce database-friendly date values.

Explore encryption as a method to hide messages. Use one-way encryption that cannot be reversed and two-way encryption that can be decoded, like encrypting passwords and social security numbers.

Master one way encryption by hashing passwords with a function, compare submitted passwords to stored hashes on the server, and understand why original data cannot be recovered.

Learn two-way encryption in database design, using an encrypt function and a corresponding decrypt function with a salt to encode data and restore the original values.

Define a banking system database design by setting the goal of money transactions, then apply normalization to organize accounts, personal data, and security details like access codes and passwords.

Design bank system datatypes by modeling member and account tables with primary keys and auto increment, using varchar for names, 10-digit phone, decimal balances, and hashing with binary access codes.

Define primary keys and a foreign key with constraints to ensure unique identifiers like member ID and account ID, and use a storage engine that supports foreign keys and transactions.

Develop a bank database in phpMyAdmin with member info and bank account tables, illustrating primary keys, auto increment, foreign keys, sha1 hashing with salt, and insert and decrypt workflows.

aggregate functions summarize data across a column, not a single row, using count, average, min, max, and sum. they group results and count rows using a key or asterisk.

Learn how aggregate functions use distinct to count unique values in a column, illustrated with a ratings table linked by foreign keys, and why max with distinct can be misleading.

Group by organizes data by customer, applying aggregate functions like max and count to ratings. Learn to group by id to compute per-user ratings statistics.

Learn how to apply aggregate functions in MySQL, including avg, max, and distinct, using group by and inner joins to analyze sales and client data.

Explore MySQL comparison functions, including greatest and least, coalescing the first non-null value, in-set tests, and true/false evaluations for data comparisons.

Explain the if function in sql, detailing its test, true result, and false result, with practical name selection examples. Introduce the related case statement for conditional queries.

Explore the case operator in MySQL to test a column and return conditional results with multiple when-then branches and an optional else.

Explore how full text indexes speed searches by indexing multiple columns and using the match against syntax, including MyISAM storage engine, stop words, and relevance rules.

Learn boolean mode in MySQL full-text search, using plus and minus to require or exclude terms, comparison operators to adjust relevance, parentheses for exact phrases, and wildcards to broaden matches.

Explore how MySQL full-text search ranks results by relevance in boolean mode. Learn to use match against and plus operators and compare boolean and full-text behavior.