ابزار برنامه نویسی

طراحی پایگاه داده MySQL

MySQL Database Design

Developers often dive in without taking the necessary time to learn the fundamentals of the technologies they're using. Basic database design is a perfect example of this, unfortunately. Let's fix this. Step by step, I'll introduce you to the techniques and jargon that you've been meaning to learn for years now.

سطح متوسط 17 جلسه آموزشی 01:42:00 ساعت
01
قسمت 1

The Example MySQL Database

Before we begin, let's import an example MySQL database to toy around with. We'll use the popular Sakila database, available on MySQL's main website.

02
قسمت 2

Primary and Foreign Keys

If a primary key is a unique identifier for a record within a table, then a foreign key is a reference to a primary key on a foreign table.

03
قسمت 3

Foreign Key Constraints

A constraint allows you to protect the integrity of your database tables. Imagine an order that is associated with a customer who no longer exists in your system. Luckily, we can guard ourselves against such orphans.

04
قسمت 4

Laravel and Foreign Key Constraints

Now that you understand keys a bit more, let's switch over to a fresh Laravel app and review how we can represent these relationships and constraints through Laravel migrations.

05
قسمت 5

The Difference Between MySQL Join Types

If the various MySQL join types confuse you, don't worry: you're not the only one. Inner Join, Outer Join, Left Join, Right Join...what the heck? But, as it turns out, once you understand the basic structure of a join, everything else should quickly...

06
قسمت 6

Group Results With Aggregate Functions

An excellent way to learn MySQL is to ask yourself: "If this was my domain, what sorts of questions might I ask?" One such question might be, "How many movies has each user rented?" To calculate numbers like this, we'll leverage MySQL aggregate funct...

07
قسمت 7

Multiple Joins in One Query

If necessary, you can join any number of tables as part of your SELECT statement. In this episode, we'll add a second LEFT JOIN clause to the query we wrote in the previous episode. This will also give us the opportunity to discuss subqueries.

08
قسمت 8

Filtering Aggregated Data

Here's another question we might ask: "Which movies have been the most profitable?" Once we solve this riddle, we can then answer a second question based on that information: "Of those results, which movies have made at least $200?"

09
قسمت 9

One-to-One

A "one-to-one" database relationship is incredibly simple to understand. If you can say that a single record from one table is related to a single record from another table, in both directions, then you have a one-to-one relationship. Here are a few...

10
قسمت 10

One-to-Many

A one-to-many relationship is the second easiest to understand. A user may have many posts. A post may have many comments. A team may have many members. In each of these examples, a single record is related to any number of related records from a rel...

11
قسمت 11

Many-to-Many

Many-to-many is one of the more confusing relationships to understand - at least initially. The most common example consists of posts and tags. Notice that any one post may have multiple tags, and any one tag may be associated with multiple posts. In...

12
قسمت 12

Use Unique Keys to Avoid Duplication

There's one issue that we didn't address in the previous episode on many-to-many relationships. Currently, there's nothing keeping us from creating multiple records with identical post_id and tag_id combinations. Let's apply a UNIQUE index to solve t...

13
قسمت 13

Apply an Index to Drastically Improve Performance

Database indexing can be a fairly confusing topic - at least initially. Over the next few episodes, let's review a series of examples that illustrate precisely why, when, and how you should apply an index.

14
قسمت 14

Reduce a Query From 12 Seconds to 1 Millisecond

Now that you have a basic understanding of indexing, let's review another example. If you're not careful, even with an index, a slight tweak to your query has the potential to skyrocket the time it takes to execute.

15
قسمت 15

MySQL Automatically Creates an Index for Foreign Keys

As it turns out, when registering a new foreign key constraint, MySQL will automatically generate an index if you don't include one. This is done to guarantee the best possible performance when filtering based on one of these keys.

16
قسمت 16

Fetch the Most Popular Authors

Let's begin working through some basic exercises. Using our blogging system, imagine that your boss wants to give a bonus to the ten most popular writers. Let's write a SQL query to determine who these people are.

17
قسمت 17

Determine the Average Rentals Per Day

For this next exercise, we'll return to the sample Sakila database. This time, your boss wants to know the average number of movie rentals per day across the entire business. Let's calculate that number in this episode.