MySQL - The Basics // Learn SQL in 23 Easy Steps

relational databases using sql are king in my recent poll nearly two-thirds of viewers said that sql was their go-to database and that's despite the fact that it's 40 year old tech with billions of dollars invested in disruptive technologies that try to knock sql off its throne today you'll learn everything you need to know about mysql an open source database that powers content management systems like wordpress ecommerce platforms like shopify and social media giants like twitter we'll take a hands-on approach by recreating airbnb's database which happens to use mysql in real life in the process you'll learn why sql is so popular how to install and interact with the database using modern tooling and vs code the essential syntax to create read update and delete data and most importantly how to model and join relational data there's also another giveaway with this video the mysql pillow all you have to do is be a subscriber and leave a comment below to start things off let's travel back in time 50 years to meet ted codd the author of the legendary paper a relational model of data for large shared data banks prior to this paper databases were not relational they were network-based or hierarchical in the paper he outlines how a relational model can reduce duplication and redundancy and it changed the way people would approach databases going forward a few years later donald chamberlain and raymond boyce would use this paper as the basis for developing structured query language aka sql or sql and is the language we still use today to interact with data in a wide variety of relational databases but now i need to let you in on a little secret and that's the fact that most developers using relational databases aren't actually writing raw sql code more often they use libraries that perform object relational mapping which are tools that allow you to work with relational databases using the object-oriented programming language of your choice javascript has sqlize python has alchemy and ruby on rails has active record orms make it so you never have to touch raw sql code and that can actually be a great thing for productivity most of the time but learning how raw sql works will help you understand some of the magic that an orm does under the hood and that's what this video is all about when i say the word relational database i'm talking about a relational database management system which is comprised of two main things you have the database itself which is a collection of tables and in each table you have your actual data organized in columns and rows very similar to an excel spreadsheet the second component in the system is a language used to manipulate and read data in the database which is most often a variation of structured query language in addition to mysql there are many popular database options out there like postgres sql server sql lite and tools like cockroachdb that can easily be distributed on the cloud to scale high traffic apps now let's take a look at the data model for our airbnb clone using a tool called drawsql which can help you visualize and model data relationships we'll refer back to this diagram throughout the video but all you need to know for right now is that each one of these boxes here represents a database table then the lines between them represent different relationships like one-to-one or one-to-many what we're looking at here is the database schema which is basically a blueprint that defines the different rules or constraints within the database if we look at a table you'll notice that it's a dictionary where the key represents a column in the database and the value represents a data type that can be stored there there are many different data types and they can vary between different sql databases but for the most part they just represent different types of numbers and strings like in mysql we have int for integer or float for floating point numbers for small strings we might use varchar or varchar if you prefer or use text for longer strings you don't need to know all the data types at the beginning but choosing the right one will help you optimize the size and performance of your database when you build a serious app in addition to a data type a column might also have a special constraint a column may not be able to store a null value or maybe each value in that column needs to be unique in this schema you'll notice that every table has an id with the primary key constraint which means that it cannot be null and it must be unique that ensures that every row in the table can be identified that's essential when building relationships because that unique id can then be saved on another table to represent a one-to-one or one-to-many relationship like a user can have many rooms and a room can have many reviews and we'll look at that in more detail later in the video now the other thing i want to point out in this diagram is that there is very little data duplication between the tables every table is a unique entity which means that the rooms table for example doesn't need to store any information about the user just a reference to the user id we call this a normalized data structure because every entity is organized into its smallest normal form a concept that ted cod laid out over half a century ago for example we have a bookings table that requires information about a user and a room we could save user data and room data on that table but that would result in a lot of data duplication a better approach is to separate users and rooms into their own entities then join them back together with bookings when needed a great analogy is that normalized data is like breaking down a car into all of its individual parts when it comes time to build a car you just join all those parts back together okay so now that we know a little bit about sql from a high level it's time to get hands-on by first installing mysql on our system i use arch by the way but for this video we'll install mysql on windows if you go to the website there's an installer that you can download go ahead and download it and the only thing you need for this tutorial is mysql server go ahead and stick with the default settings for everything then it will ask you to create a password make sure to remember the password because we'll need it later to connect to the database then the installer should take care of everything else to configure your system if you're on a mac there's also an installer you can download that follows the same process or you can just use homebrew after the installation is done you should then have access to the mysql command from the command line now to access the database from the command line you'll run mysql followed by the u flag for username and then also add flag p if you used a password we now have access to mysql in the command line and the first thing we'll want to do is create a database we can do that by executing an sql statement that says create database followed by the database name which in this case will be airbnb we can then verify that it was actually created by typing show databases now we could do this entire tutorial from the command line but that wouldn't be very developer friendly a better option is to install the sql tools extension for vs code it allows us to store our connection details visualize the database and view a history of all the queries that we've run to set it up just install the extension and driver for mysql and then create a new connection you should be able to stick to the default settings just make sure to add the name of the database you just created along with your username and password go ahead and test the connection and hopefully you get a success message otherwise google is your best friend now go ahead and click the button to connect and we're ready to start writing some code to build out this database the first thing i want to point out is that you can create a comment in sql using a double dash a cool thing about this vs code extension is that if we create a comment followed by at block it will put a play button on top of that statement allowing us to run it individually so we don't have to jump around to a bunch of different files at this point we've already created a database now we just need to add the first table to it which can be accomplished with a create table statement now a couple things to note here a statement is code that does something and we know this is a statement because it ends with a semicolon the words highlighted in purple are sql keywords they're reserved words that the language will interpret to do something by convention we write them in uppercase but they're actually not case sensitive and we could write them in lowercase if we wanted to the third word users is known as an identifier and in this case it just identifies the name of the table inside the parentheses we can add a list of columns that we want to include on this table in this case we have an id email bio and country those are the column identifiers or names but they also need to have a data type and optionally one or more constraints let's give the id field an integer data type which means we can only store whole numbers here no decimals or strings the data type is important because if you try to save the wrong data to that column the database will throw an error because sql is very strict about data integrity next we'll add a constraint to this column called primarykey what that tells the database is that this column identifies a unique row therefore the id must be unique and must not be null constraints are kind of like an extra layer of data validation that you can add beyond just the data type one additional constraint that you'll often see on ids is auto increment this tells the database to automatically create the id for you it'll start at 1 then increment to 2 3 4 and so on from there we have email which is a string value so we'll give it a type of varchar it takes an argument of a number that represents the maximum length that string can be you'll often see it set to 255 because that's the max amount of characters that can be counted with an 8-bit number so adding one more character would just be a waste of a byte the email column will also have a constraint of not null that means there has to be something added to the email field on every row and we'll also add the unique constraint to ensure that every email is unique now moving on to the bio we need to potentially store a much larger string here so instead of varchar we'll use text which can store a string value of an unspecified size and lastly we have the country value which is also a string but in this case we know the country code will always be two characters so we'll go ahead and enforce that rule on the data type we're now ready to execute this sql statement by pushing the play button you should get a success message back then if you open up the vs code extension you should be able to see the schema for this table in the ide now that we have a table it's time to insert data or rows into it the way we do that is with an insert into statement we reference the user's table with its identifier then in parentheses add the columns that we want to update the order of the columns is important because after that we have values which is a list of values that should be inserted into these columns the first value is the email the second is the bio and the third is the country one thing you might notice is that i'm leaving out the id field because if you remember from the last step we added auto increment to it so it's going to be created by mysql automatically if i go ahead and run the query you'll notice i get an error back that says the value used for the country is too long if you remember earlier we gave it a max length of two so because it has three characters here it's not allowed to write that data to the database when we change it back to two characters the query then runs successfully that inserted the first row into the database and it's also worth noting that you can insert multiple rows at the same time by separating them with commas so now that we have some data in the database the question becomes how do we retrieve it or query it a select statement allows us to read data in the database and have it returned to us in a result table if we want to read an entire table we can say select star from the users table that will return the entire table along with every single column but in most cases you are not going to want all that data you can filter out columns by simply separating them with commas in the select statement for example we might only want the email and id in the result set but a table might also have millions upon millions of rows an easy way to limit the number of rows returned is to use the limit keyword followed by the number of rows that you want notice how only two rows are return now but what if we want them to be ordered by a certain field like a timestamp or id we can achieve that by adding an order by clause that specifies a column we want to order by then ascending or descending based on the direction of ordering ordering is useful but you'll often need to filter out rows with more complex logic sql starts to feel more like a real programming language when you introduce the where clause for example we may only want the rows that have a country code of us a where clause allows us to run conditional logic on the query in this case it will only return rows where the country equals us we can also chain additional conditions to it using and or or like where the country equals us and the id is greater than one but in some cases you may not want to filter by an exact value but rather a pattern like maybe you want all the emails that start with an h in which case you can say where email like followed by a pattern in this case it'll find all the columns where the email starts with an h we get two results back from that query but if we change the pattern to hello then we only get one result back so like is kind of like a basic full text search feature the problem with this query though is that it's going to become very slow as our data set grows larger if you need to retrieve data quickly you'll need an index which is basically a lookup table that allows you to retrieve data faster a database index is just like the index in the back of a textbook it helps the database find important keywords without having to scan the entire data set but it comes at a cost of slower rights and the requirement of additional memory in this case though we'll definitely want an index on the email field which we can do by creating a statement that says create index which will give a name of email index and point it to the users table email column and now when making a query for the email based on a pattern it should be much faster now that we have our users table set up we're ready to create our first relationship if you're a host on airbnb you might have multiple properties available so the relationship is that a user can have many rooms or a one-to-many relationship between users and rooms let's go ahead and write a query to create another table called rooms it has an id field for the room itself but also notice how it has an owner id field that represents the landlord or the id of the user who owns this room the id of the room is our primary key then the owner id is known as a foreign key because it references an id in a different table on the next line we can set up the primary key by pointing it to the id field then we can do the same thing for the foreign key but we also need to tell it which table and column to reference which in this case will be the user's table id column when you set up a foreign key constraint like this it tells the database not to delete anything that holds data about that relationship so that means it'll be impossible to delete users who have an associated room at the same time in other words you're guaranteed to have data integrity let's go ahead and create the table and then we'll need to insert some data into it i'm using an insert into statement just like we did in the previous example and notice how i'm setting the owner id to one that means that the user with an id of one owns these four properties and now we're ready to make our first relational query using a join in this case we want to query the users that own rooms and include some information about each room that they own there are four different types of joins inner left right and outer to understand the difference you first need to understand that when you make a join you're reading data from two different tables think of users as the left table and rooms as the right table when you perform a join you'll be matching the user id to the owner id on a room so the relationship will only exist between certain rows on each table the type of join you use will determine whether or not data with a corresponding relationship will still be returned in the query let's take a look at a few examples first we'll say select everything from the users table then run an inner join for rooms on the condition where the room owner id equals the user id that gives us a result set with four rows in it one for each room that has a corresponding owner in this case the owner is the same for every room but each row has also been joined with the room data now in some cases you may want to fetch all the users even if they don't have an associated room you can do that with a left join this time the result set has three more rows for the other users but all the room data is just null as you might imagine a right join is the exact opposite of this it would return all the rooms that don't have an associated owner and lastly you have a full outer join which actually isn't supported in mysql but it would look something like this in other databases now one thing you'll notice in the result set is that when we have conflicting column names in the join mysql will automatically rename them for us like it did with user's id and room's id you can easily change that by selecting a specific column then use the as operator to cast it to a different name let's use underscores to make the name more friendly with programming languages that use dot notation that's pretty cool but there's one more big question that we need to answer with our data model and that is how does a user book a room from another user we can model that relationship by creating a third intermediate table called reservations or bookings most importantly a row in the bookings table will have a check in time along with a guest id or user id and the room id both of which are foreign keys that allows us to create complex relationships where a user has reserved many rooms through the bookings table or on the other side a room has many past guests through the bookings table once we have that data model established we can then run joins through the bookings table for example we can get all the rooms a user has booked by joining rooms to bookings and filtering by the guest id or we can get a history of all the guests who stayed in a room by joining users to bookings and filtering by the room id and that's all you need to know about databases to build a multi-billion dollar startup actually wait there is one last thing i forgot to show you but you need to be careful with this one because if you use it incorrectly you could delete your entire database and lose a lot of money a drop statement allows you to delete data like an individual table or an entire database in fact youtube uses mysql under the hood so i should be able to delete this video by clicking

Channel: Fireship

Views: 156,960

Keywords: webdev, app development, lesson, tutorial, sql, database tutorial, mysql, sql beginners, mysql tutorial, mysql basics, mysql history

Id: Cz3WcZLRaWc

Length: 17min 17sec (1037 seconds)

Published: Fri Apr 16 2021