How are you? How are you? I hope you're fantastically prepared I hope because today we're going to create a database from scratch step by step how to create the Twitter database with mysql I don't know exactly today but before at least I used mysql to store all your information let's create tables the links How the keys should be communicated what strategy we could follow to do it we are going to make update queries insert data joints we are going to make subqueries We are going to see triggers obviously it is not the entire Twitter database but you will see that the ideas are there and in fact it can be a wonderful exercise that what we leave as a base you take it to the next level mysql so that he does not know what it is in case you have any doubts relational database management system is widely used by all sites to store, organize, manage large amounts of data efficiently and it was developed in its day by some Swedish programmers and later acquired by Oracle, it uses content management systems such as wordpress for web applications, which it uses behind electronic commerce and Commerce or I remember or You go to downloads To install it if you have Windows you have to install the version to see if you see that Enterprise has I don't know what it is that Oracle is a rat until the end look That here is this separate super tiny Download and all this is that the version that you have to install is this one here mysql Community ok this one here if you have Windows it's super simple it's this one here Installer for Windows Perfect there you all install it I install the warrench and also the database with the engine you want ok if what you have is macos, what you have to install is mysql warrench and I recommend that you install it completely separately, whatever the engine you can use. For example, if it's macos you have brew or directly I recommend this one here and if you already have another application Well, another application but I actually love this application You are going to see now that it is a totally free simple application you give here to the most here you can choose What type of service you want to create a mysql dessert a Network and this will create it like this and that's it and here you can put the version you want that I also recommend that you use the version if you have silicon the ones from Arm that go faster but hey I already have one created which is this one here that I have put a title on it you see And also that You can enter directly but I'm going to put Start to start the process, okay And from here on we are going to start with our business since this is already up, all you have to do is enter Word bench mysql warrench, which will be what you have already installed And this application has something quite cool That it directly detects the mysql connections you see here it is detecting the mysql connection and tells you Ok, well, if you have given it a password it will ask you for the password if you have not given it a password then you can enter directly but here you will enter all the databases that you have created, you simply click on it and we are already here in here we already have access to our database Here we are going to be working all day Here we are going to create the database Twitter data from scratch and we are going to go Chinese Chano okay the first thing we could do is see what databases we have for that there is a command called show dataces I am going to also explain a little bit and look here what you have Well here we have a tab you can do as many as you want you can open as you want save the ones you want I love that a floppy disk still comes out a diskette this people who are from the 2000s for example who were born from the 2000s you understand this you understand this you know what this is because this It makes a lot of sense, right, that is, it's like super weird for you, it doesn't make any sense. Well, the important thing is, above all, these two, these two, look at this Ray, this Ray is to execute what you have selected, or the entire Script is valid in this case if you I give, well, we are going to execute this and this is to execute the line where the cursor is. These two are the most important. This would be to stop in the case that I am making a query that you want to stop. These are the most important that we are going to have So if we click on this little button I'll tell you a short Cut okay so you're not if you click here I have the answer and it has made us Show the databases our database engine in this case mysql when we have entered directly note that it already has a database, okay It already has a database where it has system issues, a schema on performance, the information from the database itself, information from the schema and these schemas that we could constantly ask there, we could not Look what is inside and you can also look at it visually. You can see here the Esquimas tab, so you can click here and here, although it is true that you only see the esis because the others are supposed to be hidden . here you could see the columns that you have this means that you could already request information for example from these columns here it is okay if you click on the right button I give it seller Rose limit it will create another account for you to see what is inside and here you go Now the syntax of the query selects all the columns of this this table we are going to create our tables but just so you can see what it already has in the database we haven't created anything yet and it already has information it has its own databases when We are talking about the database, we must understand the database engine that will have the database collection and we will be able to have more than one. We are going to create a Twitter database, but as you can see, we already have four databases. and we are going to have one more that we are going to create So we are going to create our first database and we are going to create create database and we name our database to create a database we would have to do create database and the name of the database we press enter and we have already created the database now it does not appear here but if you click on this little icon that you have here for refresh you see here we already have our database Not now we have the esis but also we have this but notice that in the tables right now it is empty it does not have any tables we should start creating a table But before creating a table if I now create a table here mysql will tell me Hey what table that is where you want to create the table In which database do you want to create the table, we have to tell it which of the available databases we want to use. Look, we are going to put the ataves show here again and I am going to explain a little trick to avoid having to hit this one again little button that I remind you of are two buttons, the one on the left executes everything you have or what you have selected and the one on the right only executes the line you are on without having to come here execute the line what you can do is Command enter no I know if in Windows it is control enter I don't know that but I think so So I give it Command enter it will only execute this line okay it has not executed this one it has only executed this one and here we can see the information from the database The next thing we have to say is Hey, I want to use the Twitter database, you must Command enter and now we don't have any response, but we do see here that you're giving us a check, okay, here we have the output of each of the actions where we going to say Hey this went well this went wrong here is an error How many How many rows have been affected when we have done this ok we are using this database we are going to create our first table No because if we think about Twitter We already had a table here which was the most important number of users if you think about it coldly all the products in life all the products in life are always born through at least one table many people may think that the most important table is Twitch but tweets could not exist If we didn't have users, it's not because the users are the ones who will create the tweets. So let's go for the first thing we're going to do is create a table, okay, and we'll tell it the name of the table, which is users. Here we're going to have quite a bit of information. We are going to review each one and I will comment on the most important thing is that each user has to have an identifier. We are going to make a user ID and here we could tell it in a different way, we could not make it a Hash which would surely make the most sense is for it to be a text string, but since we are doing everything from scratch, it might also make sense that we start with something auto-incremental. In other words, the first user is one two three four such because we it will help to easily identify the users when we delete them to know who we are referring to So we put our first column our first most important information is the ID of the user that will be an int we are going to tell it that it is not null That is not the use can be null and it will auto-increment here we would have the name of the column we would have the type of the column here we would have restrictions that the column could have such as that it cannot be null and here we are setting it as a default value more than a default value is the function that must be executed when a new user is being created automatically, what the database is going to do, that is to say, okay, if the user has stayed in one, the next one will be 2 3 4, that is, many times mysql desserts sql sql Lite This is quite common in all of them because auto-increment is the most typical thing in the world because they are like internal functions that are going to work for you so that they do it automatically And you don't have to worry about this at all then we would have the user handle is not the for example at is not going to be an integer this could be a bar charge and note that we have here an autocomplete with a lot of things that already tells you all the things that can be can be binary bikini you can even save data binary you can save photos in the database is not recommended but you can make volleyballs that can be a character a date decimal floating point integers of different sizes also strings that is, there is everything in this case we are going to put bar charge bar char It is like the most typical way in which text strings are made, okay, but text strings delimited in length. What does this mean? For example, normally this handle cannot be as long as the user wants, that is, it cannot be here. super Miguel Ángel Durán García I don't know what I don't know how much blah blah, but rather it has a limit that means that people don't go too far. We're going to put a limit of 100, it seems a lot to me. all all users have that I have a handle and one thing that is also important another restriction is that it has to be why it has to be unique because no two users have the same handler otherwise we are screwed okay And this is very interesting because you have to know that sometimes we can have the user's identifier on the one hand and on the other hand we can have a field that, although it is not its identifier, is also unique and this, well, we have another example also the email address the email address that we are going to put also 50 chats no It can be null and it also has to be unique. All users have to have an email address and if we don't allow them to be repeated, we can put unique. This is what is called in English, they are like constraints or they can be more like time restrictions Talking about our database, there is some information that we can say is unique that is not null and all of this we are going to put the First name, we are going to put a march as well, we are going to change because otherwise everything will be the same no no null Last name, well, it's going to take it too and Here we're going to put 100 no null also the same with the phone number no phone number here you could put it for example also because the chat bar here you can put the chat of 10 smaller and put it also Make it unique because the Birthday cannot be repeated. Look, I'm going to teach a very interesting create that when we create a user, we want to know when the user has been created, no, and obviously this is not something that we are going to want the backen to pass the Time to. stamp and such, but we want the database to add it internally and automatically What is the Time stamp of that property does not mean Hey, when was it created in the database And this already has a type called Time Stand In other words, it already has a type defined for when you refer to time, you can say that it cannot be null but of course if it cannot be null or We pass it to it or we give it a default value so we are going to give it the default value and we are going to say that the default value that it has could be Now the value of now not I mean and the nau as you see here is Another function that mysql would have internally and what it will do is that every time a user is inserted in this table by default, if we do not tell it the created value, we will already have this value in this column, we will see no when we add a new user. and we will see how it is automatically setting the Time stamp Although we will not tell you before finishing and this is super important when we create the tables is that we have to know what your primary ki is What is primary here Look let's think about this table of users that we have here ok you see we have here and D1 obviously the primary key would be like the field or set of Fields because it can be more than one field that uniquely and unequivocally identifies an element of the table but be careful with this because there are people What could I say Hey and why is the username not a primary key no and it can't be a primary key Because not only and this is super important ok Not only does it have to be a form of a unique identifier but it also has to be immutable that is, it has to be a key that is not going to change once it has been assigned once we have told him that elon mus has identifier 1 this identifier cannot change what happens with the username the username cannot be the first because yes that it is true that it is unique but it is not immutable you can come to Twitter and you can change this midd handle you can change it to whatever you can change it Then it would no longer be immutable once it has been assigned you can change it and so Therefore, it does not guarantee that this record is identifiable in a consistent way, that is, the difference between the user ID is super important, yes it can be because we are not going to change the usd, we will never have it that is not null a first and that it cannot be null that is obviously second it has to be a unique identifier it has to have uniqueness Ok and the third thing that has to be immutable that is, it cannot be changed forever so that is why it often makes sense that it be the idea of the user Because the idea of the user is a more internal record that will never change But on the other hand, the user's name can change, for example, it could not be the only email address, it could not be the first team either, because the email address could change perfectly and I have put this and this here It is like this with this we have already created our first table with the first one that has to be with uniqueness not nullity And also immutability of the user ID that will never change with this I know now we are going to execute it and we would have our first table if we enter here in We are going to refresh the tables, notice that now we can see that here we have users and we do a Select Rose, we have users. That is, the users table, but for now it is empty, for that we are going to see how to insert information in a mysql database, we say insert users and here we can tell you in which Fields we want to put the information for example the user Hunter the First good the email address the First name the Last name the phone number ok This is the information that we are going to provide you but the rest is both the user ID like him, we are not going to comment on it because it is not necessary and he will automatically be able to say Ah well, I have to auto-increment the user ID and the create by default has Now, that is, he will put the Time and now here we could give you the values here you can do one thing you can go one by one nothing would happen no I mean you can put here for example user handle miduder miduder midude@gmail.com ok First name Miguel Ángel and here we would have the phone number 6999 this be one not this we could give enter And this would work and I would be introducing a single value I am not going to do it so you can see ok ok inserting to this has worked it has told me to check and here I don't know if it is always seen but here you always see that puts oneted will always say the number of rows that have been affected, whether you are deleting them adding consulting or whatever, for example, the show Data sometimes you see here we are consulting it and as it has shown me four data bases data Well, Rose has given me four if you select results every day it will tell you that they have been affected 10 If you delete four it will tell you that four this gives you an idea that is not always real but it helps you determine how detocha your skin is because imagine that you do a Select whatever to attract information and it tells you 8 million Rose affected Well that's when you shit on yourself in fact in fact I already tell you one thing normally when you do when you do a delete When you do a delete and you forget the Where is more difficult today and then I'll explain why it's worth But when you do a delete and you forget the Where And then the delete and it tells you a million and a half rows affected and you get the balls here like there It's when you have the problem, no Or you do any delete and you wait, not to wait for two or three rows to be affected and suddenly you see there that thousands of rows have been affected So you shit yourself but one thing has happened to me it has happened to me it rises to your throat and they escape and everything Ah, look, look, we're going to see one of the restrictions, look, I've made this No, I've already hit enter, we're going to go back to Let's hit it again, now it should kill me, okay, you see, He has squeezed me, he has blown me away because here we have the answer because he tells me hey there has been an error that the one here has been duplicated User Hunter You see that there are good ways, not in this case when we insert as it measures we told him that you had the handle had to be unique and we are trying to insert another one like it already tells us Hey this can't be no this doesn't work what you can also do if you want Look at this I'm going to comment that if you don't know how to comment you can put script script but I I'm going to paste it to put more people Ok I'm going to add more values and so that you can see that you can do it more than one at a time, you don't have to go one by one you can do something like that, it's worth what you can do Well wait because this is it I have done it before in an example and I have to move some things so you can do an insert of more than one thing at the same time, there is no problem, there are times when people say Ah, it is that there is one by one, not the only thing you have to do to do is separate it with a comma and you can put as many as you want from handle you have it here the imeladres First name the Last name phone number I'm going to put one more good two more will see one more Let's go feral let's put a Random number ok let's see another Iván come astract astract 3 Pablo told me it was Hernández Well there you have it But with this Now we have it here we press enter and you see again we have added we have said that we have added five now in users we press enter and here we have all our users with all their information an important issue is when a primer as I told you and it has to be unique immutable and cannot be null therefore when you have a primary key this not null is no longer necessary it's okay if you put it as prime key no longer makes sense you can't tell it the non null constraint as if we were saying it twice so it's no longer necessary for you to say not null in this case you can remove it nothing happens if you leave it let's see how we can drop a base of data Imagine that it is this table for whatever we do not like it for this change no that we have not done for whatever we have said Hey Now I want to recreate it all again imagine I want to re- run everything I want to run everything What if I I try to execute everything that you see here I am going to remove the show Data sometimes that is not interesting of course I want to create the database I want to use Twitter from B I don't know what it is like it has Too much information that you don't know it doesn't work or it goes away to duplicate obviously This is going to cause problems if we click here ok Notice that when trying to execute the entire file and make believe it already tells me hey when trying to re-execute everything the database already exists this is an error like a no to say ah but now what do I do because I don't know what's wrong, you can do a Drop of the database you can do Drop data and what it does is drop the entire database you can ask if it exists because if it's the first time you run it and it doesn't exist yet you can do this now we know it exists but if you try to do a Drop of a database that does not exist it will tell you Hey that it does not exist you have to tell it Hey make a Drop of this database it does exist and tell it the name of the database you can do the same for each one of them the tables ok you can do for example the create tablet this before doing the droptable you could that is before doing a create table you could do a Drop Table if users exists what we are doing is good if the users table exists delete the before creating it in In this case, how do we do a beige Data Drop? It should not be necessary for good practice, we could do it like this, we could repeat it again with all the tables that we want to do now with this, if we execute the entire file, now you can see who has executed all the tables. things the Drop the create the use the Drop the create the insert everything has been executed and no matter how many times we give it what we are doing is cleaning everything again we are cleaning it because it no longer had any problem there is one that gives a Warning which is the droptable if it exists of users because it does not exist before creating it it does not exist because we are rotating the entire database So we have already created the users table But obviously the Twitter users table is not enough we need to have follow What is what is Twitter without followers is that we make the droptable and flexist of followers Ok and we are going to create the table of followers there are different strategies many times when making relationships in the database they all have their good things and their bad things For example depending on the strategy that use followers and we could put the follower ID and the following here and I will explain why Normally these types of connections are made we can say that 2 3 this is equal to 1 and this follows 5 and this cannot be followed itself same and this is interesting because we will see a problem with this and we will see How how to fix normally what we do with database is to create these relationships where we know that this user here refers this user refers to this this user 3 refers this c5 refers to this one we are not creating as relationships why do we do this because what we normally want is to ensure the integrity of the data and avoid repeating the data that is, here we have the users and here we have with the ID 5 that we know that It is a Nova 6 and here with only the ideas we are already able to make relationships between the information that we have in the tables and we no longer need to recreate everything, we don't even need here within users to have a field that is all the followers that it has or all or all the people who are following what we can do is extract that information to an intermediate table to make these relationships and why we do this because otherwise the relationships within a table would be very difficult imagine here what we would have in users Imagine that I would like to have apart from the ID and the name Imagine that I want to have all his followers, it would be super complicated to have all the information there and the same with all the people who follow, so what we are doing to make sure that we have the data is much more normalize them in a table where they will have these relationships we will have user 1 follow user 3 user 4 user 5 and each of these relationships will be one of the elements this is often what are called relationships one to many many to many one to one a user, for example, has a relationship that can follow many and a user can be many people too, so this type of relationship often times You will see that they can be separated into an AND table This is going to help you a lot when it comes to normalizing the data, avoiding duplication of data, avoiding problems when updating the data, it will be much easier now to extract the data. Why? the followers of the elonmas user Ok well I just have to go and look Here What is the idea of the most and once I have the ID of the most I say Ok then bring me all the followers where the following ID is 1 and then I will look everyone that here is one that here in this case would not have followers like It doesn't matter that this is not the database that he controls let's see it with code that we are going to see much clearer we are going to create the table of followers create table followers we are going to see how we are going to make the relationships Well we need on the one hand the follower ID which is an integer it cannot be null the follower ID is going to be a user ID, that is, it will be the relationship of the usa ID So, later we will see how we do this, we will have the follower ID, that is, the person who follows Who follows, then following ID, an integer and now the primary, which will be the q primary that will never be able to change that element because the primerick and as we have said before can be more than one field and in this case we can say that the primary qui in the follower and following ID because it will never ever change that as we have already since the user ID never changes if user 2 is following user 3 that unique relationship from user 2 to user 3 is created from the two ideas because those will never change therefore the primerick and of the followers table are in that relationship, now , something is still not missing, and that is that we have to indicate how the data is related, not these arrows that you see here, how we tell mysql that these arrows have to travel, how the two tables are related, we have to tell it that there is a relationship between this idea that we have here and this follower ID so that there are constraints and certain improvements and optimizations that happen with these two tables. which is a parameriquin but a bit different because it is not its own So what is the foreign key in this case the foreign key is like a concept to relate the database and what we need to tell it is that it was not already in foreign here forenky ok we tell him Hey the follower ID What reference does this refer to the users table to the user ID ok And we would say the same thing with the other one we are going to copy this and tell him and let him know that the following ID also references the users table to the field user ID and then with this you already know what the relationship between one and the other is, okay we would close it here with a semicolon okay We would have it [ __ ] Wait, what is he complaining about here or is it the same as the first team it has to go all the way down Yes the kit has to go all the way down [ __ ] seriously I'm freaking it out Well I thought it wouldn't be necessary but if the first kit has to go all the way down with this we'll create the table of followers we could do this the foreign key again a field or collection of Fields can also be a collection of Fields because for example here we have a first team that is two Fields for example it means that we could have a foreign key that is also two Fields or more Then These values are required to match the values of the primary key of another table. In this case, we tell it that follower ID has to be a value that references a uservy of a user. What this will also mean is that we are not going to create We are not going to be able to create followers that do not exist within the users table, okay, that is, if I try to insert a follower with the idea 83 and there are only 60 users, it will not let me, it will say Hey, this does not exist and we can try it now So that we can see it, it could restrict at the table level that follow and follow coincide very well. We are going to see that now because look, we are going to execute it, everything is okay to see if it is okay, everyone has been executed, we would have here in users, now we would have followers and we see that I have it empty We are going to add here some followers if you think insert into followers we are going to have follower ID following ID we put values and we are going to put here into no info Ok and we are going to put some relationship here for example user 1 which I think was my question Well, go on , let's put Alexa on 2, follow my dudev, Marta, follow my dudev, Itzia, follow my dudev, okay, but what happens, midef is smart and says, Ah, well, I follow myself because the guy is too much says Ah well myself because idea 1 if we look here the id1 is my dudev And then my dudef says well I'm going to follow myself but that doesn't make sense in business logic But we haven't made any changes here of course this means that this works and that now if we go here to followers we can see that midef is following my dudev this doesn't make any sense so what we're going to do here is change it what can we do I think from version 8 of mysql I don't remember if it's 8.1 or period I don't remember but the version 8 can be added Constraints can be added to do checks not before doing the insert because if not before they had to be done with triggers or in baked and it was a bit of a pain but they added a way to do constraint so I'm going to show you How can you modify a table once you have created it so that you do not have to create it again and I am also going to show you how you can Add a constrain is valid as a restriction when it comes to how the data works, for example here we would say altertable we are going to alter the table of followers and what we are going to do is add one with with strain that this came out with String that you have to give it a name that is check follower ID for example it is important well you have to give it a name Because the constraints are you can remove them you can list and all this and this restriction we are going to tell you that you have to check that the follower ID is different or you can do it like this or like that it is different from following because that way we will prevent a user from being able to follow himself same What happens if I now try to execute This is going to tell me that it doesn't work, it tells me Hey, this is violated, it's with strain, it's violated because we're already late, we're already doing it too late, how have we done this, it won't let me do this anymore insert, that is, this insert already allows it to be done, but I can't make a restriction that would just violate what is already in the database. So what we can do is move it here. It is going to recreate the table and such now the [ __ ] now the [ __ ] is giving it to me in the insert Because now the insert tells me hey It is that the constraint that you have placed so that a person cannot be followed you are violating yourself because of this we are going to remove it we are going to see if this works and now when we execute everything Now we do have everything in green and now we have the restriction that a user cannot follow himself No Now that we have these tables there, we can add some more here, not for example, how many users we had 3 4 5 6 well, we can make 5 follow 6, which is 6 gigabytes 5, 2 follows 5, okay, we're going to put like some followers and things like that, the interesting thing here is also that if we put the one following the two and we do here the enter Note that here I was already following and here I tried to put it back and it won't let me because since this is a Here, primary, that relationship cannot occur twice, that is, what we have done here for the first team, this is also interesting because it already makes us a restriction that the same operation cannot occur twice, okay, it cannot follow user 1 twice. It wouldn't make sense to user 2, we're going to execute this and Here we're going to put the followers, we already have here the relationships of the followers It's that here you can start, I know how to recover the follower table, it would be a Select follower ID from followers no and here We would already have the same information that we were seeing before, no, and now we are beginning to complicate it, this is a bit what we saw the other day, okay, here we would have this, we could recover the followers of my doubt, no, that follower ID from followers following is the idea 1 Which is my question. Ok, well, we would have here all the users who follow mudef, which are three, we could count the number of users who are being a followers count or followers, we are going to put from followers, ok And this would be exactly the same but counting for having the account is okay And here we have three followers what you are seeing, you can imagine what the queries that the database would make to show Twitter are. Imagine that you want to make the list Look, we are going to complicate the top 3 users with the highest number of Followers ok to make a top 3 this I think Twitter does but it could be we are going to select the follywin ID we are going to count the number of follower ID as followers this is exactly the same as We have done before from followers, okay, now we are going to group them so that the following id so that they do not repeat themselves, we group, we say, okay, I want to know the top 3 of all the users. Let's see how many followers they have, therefore, we are going to count the follow ID of the forum and we are going to group them by following, that is, we are going to group all the people who follow ID 1 and now we could set a limit of 3. I think it will surely come out messy, see why it puts followers 3 1 3 what we could do here before after del Group is an order by and we tell it not to order it by followers in descending order and with this we should already have the ones with the most followers for example my dudev who is the one has three followers following ID the 5 I don't know who he is has three and 2 has 1, as we can see, the ones with the most followers come out if we look at the following table here of followers, we can see that there are actually three following IDs. These are three and there are 3 of 5, that is, they are there correctly. He will tell me This is very good but you do not see that it is my doubt and this is where the complicated thing is and what we saw and what we did another day last week that if you missed it and you are interested I recommend that you see in the course of sql from scratch last week what we did were also the joints that allow you to extract information from one table to the other precisely thanks to forensics what we can do here based on this we are going to put top 3 users but doing but doing We are going to base ourselves on this, okay. We have the following here, we are going to put a semicolon here, which I do not like, but we are going to want more information, for example, about the users of the users table, we are going to bring us the user ID and the user table, we are going to bring us the user Hunter ok we are going to want this information we could also bring us the users point First name come then from followers we want the information from the followers table but it turns out that from the users table where we got it we have to make a joint we have to make the connection between the two tables and this connection is what we have done Here you see this these arrows this would be what we are telling them how you have to bring you the information how we make this link this follower ID this one to whom it refers to well this one we are going to have our information and This it will be the users point and username in this case we have called it user handle I don't think so That is how we are doing the link and we are doing it with a join Hey select this information for me but you expand the information Thanks to the fact that you know that this one refers to this one and so you will be able to recover the name of the most and we will not only know the idea we will also know the name so to make the joint we are going to say ore joint followers where users or the user ID is equal to the followers followers point and here we can put following ID not to know who is following whom and with this if I have not been mistaken and we do like this not United table alias followers where Allied it could be that this is so I have messed it up Yes it is that the join is done uses Non followers I don't know why I have put followers again the join we do it in the users table not in the followers table in the followers table is where you are already extracting the information that is, it would be like the primary table so the one we want to extract from is users So there we would have it and this would be the result what we have done with this Look in a moment we are already bringing the information from two tables it takes nothing is consistent obviously we don't have much data yet but we not only know the user ID but thanks Namely, the user ID of the table, we have also extracted the data from the users table, where we do have the information of the user's name, his first name and all this, yes. We could extract all the information we need and much more, so there we would have the already interesting join with only two tables that we have and now we are going to create the tweets table we could do the Drop again But hey, since you already know how to do it, you can do it yourselves we are going to do the droptable create in the tweets table that we would have a tweet ID also the same we could say that it is auto incremental the user ID is worth that it cannot be null and this is super important because Obviously all tweets have to have a use ID the text of the tweet is worth that as they are Well now there are more if you have Premium they can be more but they were supposed to be 280 If I'm not mistaken, no, and it can't be null, you can't send an empty tweet, we can put the number of likes, for example, it's an integer that starts at zero by default. a default value that is zero That is, I didn't start, don't leave it at null and the same with me, I know tweets, well, without default 0 even comments, we copied the create that we had done here, we had done when creating a user create no Times tam not null This is this line Well, it's super typical so you're going to be able to do it every two by three When the tweet was created, well, we're going to automatically set the Time now We need the forensics that's going to be the user ID that user created this so we have to refer to the table of users by the user ID and the same thing Which is the primary here this case is the tweet ID cannot change it will never change the other can change but the tweet ID will not change so The primary one is going to be the ID of the tweet because it is the idea of the primary one. Well, if you go to any tweet, check that you enter here and this idea that you have. Here this would be the primary ID, because you can't change that idea. It is going to refer to this tweet here so we would have the table of tweets with this with this here look I have here I have here just a few tweets with the user ID and the tweet text and here we would have the ID of the one that this refers to my doubt this refers to the second user okay that is important look I have here more that is we are going to put more tweets to have a few Note that you only have to put the idea of user because we only put two fields of the tweet when we have created here a a lot because if you look closely the idea of the tweet is auto-increasing. So the USB is already created automatically, yes, the tweet text does need it, but the number of likes, retweets, and comments by default 0 is not necessary, and the create By default, it is the moment in which it is created, so you should already have it, you don't need to do anything else, we are going to execute everything to see if we have done everything correctly, okay, here I am getting the query that we have done before. the selects is that every time we execute this they will appear there. I think you can also put This yes so let's see if you like this and we are going to comment the Selects so that they are not appearing there all the time every time we do it let's run everything and these here too but I'll tell you about them because later I'll give you the file and that's how you'll have them We run everything and when we run it all if we go for example here to tweets and run this well we already have here all the tweets of each user now what we can do we can for example to complicate this we could know how many tweets a user has made here we could complicate a little more and see how to use the Group the Group that we have seen before but for example how many tweets a user has made to see this would be with a count In other words, neither because we can look at that we can only do a Select of the user ID to count everything although you can also count and count it Since how much has been done we get it from tweets we don't even have to do anything else and what we can do is group them by user ID so what we are going to know with this is how many tweets each user has made what happens is that only Let's see now the usd you see user 1 has made 5 user 2 has made two user three has made two this which may also seem silly but something like this let's see I'm going to tell you something something like this I mean, for example when you see this of 200 followers or you see here that it puts 12.4 thousand tweets of course someone can think ostia what Twitter is doing It's every time you need that information, it's doing something like this, no, and where it says Where user ID, it's one that's the miduder. No, oh, he has five. Okay, well, it's going to show a 5 here. This doesn't work like that, okay. This is not what Twitter does. exactly. Because if I had to do this, the cost that the API would have would be very beastly, no there are different ways, no, one could be to cache it, say Hey, I'm going to cache this query for 4 hours, yes, I do it, but the cache is okay, that may make sense, but there are times that we are going to see it now, when it can be done in other ways, okay, it can be done what to do with a Trigger that we will see now we will do the updates and then I will show you how the triggers are but so that you get a little idea that it is not done exactly like that Look, we are going to make a subquery because many times you have asked me not why example to obtain the tweets of users who have more than two followers which is a subquery because on Twitter we ask me about a person you are going to make your queries to see the subqueries it is the easiest thing in the world sometimes it is an alternative to make joins or by making two queries sequentially and one a query basically a query within another query here we have to obtain the tweets of the users who have more than two followers if we already know here we know that for example user 1 has 5 user 2 has 2 user 3 has two ok Imagine that we want those who have more than Ah no These are from tweets well they have made tweets Let's see tweets Look here we have Twitch And we have user 1 who has made three four five and user 2 h2 and user 3 has made two, we are also going to make all the users who have more than two, so only the tweets of one should appear , not of my doubt. obtain the tweets of these people we are going to obtain the tweet ID to see the tweets tweets we have the tweet ID tweets ok tweet text and the usaid and we are going to extract this from the tweets and here we tell it where the user is inside and here we can do it your query what we are doing here what we are saying is Hey retrieve me the ID of the tweet the text of the tweet and the user ID of the Twitch table of all those users that are within this query and here we can make the query of select where the following ID we are not going to see that it has more than two followers so we see that the following ID of followers we group it no this we had done before with the following ID following ID where the account is greater than two where count to see if the has messed up No it's not with the güero Of course we are Ah my God what a mistake when you group it's that I don't know how to play when we group the Where of the Group is the javin what it is select we are going to select following from followers we group them by the following ID and What we have is that the count of those that we group is greater than 2 The were In any case it goes up it goes up and of course it would not be for the group that we are doing now we are going to see the difference so you can see why it has blown my mind this but what we are doing with this look we are going to put it here okay is Ah no this is not necessary to have this we are going to put it here okay here we would have let's remove the javin first okay look here first we would have all the following ID of followers that they are grouped by following no It should be grouped by follower ID wait a moment let's select it silly [Music] from followers we have Select follower ID let's see how I put that how I put this Select following ID following ID ok people who follow from followers and what We had grouped to see how you have grouped them by following ID the following ID is selected Group by follow ok it makes sense no, that is, it is the person who follows Well, with the name it is a bit strange but this is the person who starts the follow-up Y this is the target person to whom it is being sent, it makes sense what we are doing select the following ID from followers and we group it by follow, that is, this is to know, for example, how many users are following this one from here to the following and when we group it here to know clearly When we do this right now what we are is grouping all the ones that have followers and here in following ID this one has followers this one has followers they all have followers but we don't know those who have more than two followers for that we tell them that those who we are grouping or we could put here look count if we do like that ok we already know the Followers that each one has, one has three followers followers okay we put this name one has three followers 2 has one follower 5 has three followers 6 has one follower and now we say okay But what I want is for me to filter those who have followers greater than two for example no and so We would have it if they have more than two and now we do have all those who have more than two followers So yes this the having Of course postql has it mysql postresql has it and all this the javin would be a way of filtering not with the were but we are filtering the group with this now that we would already have the followers that each one has what we could have here those who have more than two we could already put this here this would return the following ID the bad thing is and what we want is that it returns only and precisely the acidity of the user to use it as a subquery this count that we have made here we can move it here and not do the Select Because we need this to return only the acidity of the users it is worth as we only want These ideas when let's make the user ID in what we want is exactly this and now we would have the tweets of only those users who have more than two and it's already set it would be, for example, the subquery very well come on let's continue let's see how sqls is set up we'll see not today not today because today the idea was to mess with this and that's it Look let's see so we can see all the topics for example delete them no If we want to delete a tweet by ID per user we could delete it Look delete if you want to delete an element of tweets we delete where you do not forget Although look at one thing this is one of the typical errors not delete from tweets although this is a mistake that many people say they make and such The truth is that it is not usually so easy look you see It hasn't worked for me look here you see it has put delete from Twitch in red and it tells me you are using safe update Mode and you have tried to make a day of a table Without putting a Where for a while you can use this as a save Mode in the one that somehow if it sees that you do a dilette a delete without the Where it warns you, let's see you can go to the sack of serious companies This cannot be done there are sql modes in which you can no longer do this you have the safe sql Mode it you can remove in fact you can and in fact there are even ways to throw back what you just did but what you can do for if you want to go to the bag no and say No It's not that you can change the set sql updates you set it to zero and now it would work ok now yes say Ah ok perfect look we are going to do it ok you see delete from tweets he has deleted them but note that you have to force it normally in the database nowadays you no longer do the Direct from and take it for granted I'm going to say it so you know because the meme is very funny but today there are many companies that have a lot of this type of checks, so you don't aliáis you can delete the ID of the tweet for example from 1 this well you can do it and it works you can also do delete which is that we have to execute it all ok ok now we could also delete tweets you can do it by user ID ok to delete miduder tweets you can even delete what happens is It might not work, let's see, you can delete it even by text say look at all the ones that put elon Mask Delete all the ones that put in the text of tweets Where tweet text like elonmask that is, all the ones that talk about the most look here there are one who talks about the most is supposed to delete one you should delete one you see and he has deleted one and now the one who talked about the most well he no longer deletes it this must be something similar to what Twitter does to screw up the algorithm and things like this No That detects texts and things this would be a bit like the delets and you can do the web that you would do with the selects the same thing you can do with the update when updating the number of comments of a tweet not that we had seen before that we had here in tweets Select all from twits ok we had here for example the number of comments and this is important what we are going to do now because now when we see the triggers this is something we are going to do I want to update tweets and set the number of comments to be equal You can do the comment number plus one. This is fine, you don't need to put this, but there are times when you can use the same value and do more than one. In fact, this can be a good practice because this is always how the set States of relaxation are You have done this to ensure that it uses the last value and not the value that you are passing through Back and things and you can do this with the tweet that you want. In other words, we would have the same update what we want to set and you can also do an operation use Put the web in the same field that you wanted to update and thus make sure that only the tweet with the idea is going to be updated and now [ __ ] Oh of course, because we have deleted it, we have deleted this one, so it has not found it Okay now yes Now we should see that the tweet 2 you see has the comment in one and this we could take it, it is not replace replace replace text you could also do it, it is worth updating the tweets and set the tweet text and you can use functions for example replace text we replace the tweetx and you tell him that where he puts to see I love entering Twitter Look at Twitter where he puts Twitter because I don't know if you know that they are going to launch a new Twitter we are going to put ex so we reuse all our tweets and we can put the War tweet text be like and have Twitter where we see that the word Twitter exists we replace the Twitter ports so we avoid trying to update them all or update where you really need it let's see if this has worked ok And now looking here well now we see three ok he has done a replacement of the text For those who don't know what this is about Fresh, it is the new social network that is going to release a meta that seems to be going to release it tomorrow and that the idea is that it will be a copy of Twitter, that is, it is a Twitter Killer. a Twitter Killer I'm quite curious The truth is I'm quite curious I'm going to open an account the wise men will not appear in Europe due to data protection I can't believe it I can't believe it if that's true things look bad things look bad Well, it means that a lot of data is going to be taken away from us. Well, we've already seen delete update create Table, we've seen a lot. What would the likes be like? You enter a tweet Well, I don't know if you can do it, if you click here, you can see who has liked it, you can't see who has liked it, so there is a relationship there, not only of the number of likes, but also a relationship of Who has made those likes and it's super interesting and this is where you can see the power of mysql in which you have to keep creating tables to create these relationships This is not sql, it would surely come out in another way because you already know that they work with collections and documents But here we are going to have to make a table again for a relationship where we are going to have on the one hand the Who has made the clear the user ID would be the person who has made the like we are going to have the Twitter ID which would be the tweet that he likes then we would have we could say when he did it I don't know if it's important When he did the like I think it's not important I think it's not important So we're going to put here where we have the user ID that this refers to users user ID ok we have the forensic where we have the tweet ID that this refers to the tweets the tweet ID and we had the first key which again is that the user ID and the tweet because this relationship can only occur once That is, there is no use [ __ ] I always forget this not a user cannot like the same tweet twice therefore the primary and unique unrepeatable and immutable key is the relationship that the user ID likes exactly that tweet ID and cannot like it twice that has to be unique this would be the table of tweets and now we could start to put some of these relations not the same tweet likes what we have done before we have to pass the user ID the tweet ID and here what values we put Well here we are going to invent no the user miduder likes tweet 3 mudev likes tweet 6 I don't know who likes 7 Well I don't know how many tweets 7 is worth 7 is worth 3 4 7 1 the 7 Here there is no need to make a restriction that a user cannot like his own tweet because that is something that is correct But good so that you more or less get an idea Let's see let's put some more three six two seven three seven five seven ok we are going to put the relationships of a few Likes we are going to execute everything so that it does not escape us ok it cannot constrain something has been done wrong reference tweets is that 6 This seems to me that we have eliminated 6 at some point for something it may be you know I'm referencing someone That doesn't exist I don't know exactly which one we're going to see this one yes it does exist 14 let's see if you like this no 4 that is the 4 the 4 doesn't exist two three okay 23 yes it does exist okay that is which is the 4 that the tweet id4 at some point maybe we have put some delets there and such Maybe of course I'm going to remove the delets because you see that I'm deleting it here And surely that's why it disappears I'm going to comment on it and so they don't disappear and now it's easier for us to make relationships 4 2 likes one three likes one five likes 2 and he likes 3 okay let's do there are a few relationships we put it we recite everything and now yes yes we put tweet likes here we should have likes relations like we are going to do some queries for example we do one and so on and we go to the triggers ok because the triggers are something super interesting and I leave you here is that here you can worry about doing it joints What have we done before to get the information and such? For example, I know we are going to get this, it is very typical, we are not going to do this, it is to obtain the number of likes per tweet, no, because that is important and this is quite easy or It is to obtain the number of likes for each tweet, this is quite important and I leave it up to you to join, which is a moment. Look, we would have to select the tweet ID, we would have to count. Well, I will put everything as light count . If the Twitter ID appears in this table, it means that it is a like and we have to count each time it appears and we have to do this from the tweet likes and all we have to do is group them by the tweet ID and that's it, that is, this would be to have the number of likes of each of the tweets you see and here we would have that tweet ID 1 has 3 likes we do not know what has done that we can also recover but the important thing is that we already have each one of the number of likes that have each tweet and this as we have done it because thanks to Group by what Group by does is that every time they are repeated or what we are doing is this tweet ID How many times does one two three come out well we are going to flatten it we are going to group and also in the other field we are putting How many times it has happened we do not count and this is how we are grouping it we do not group How many times this is repeated 3 we leave it in a single row we group them and next to them in another column we tell them the number of times it had appeared this is super typical super typical let's see the triggers this is a more advanced topic but I think it's super interesting what are the triggers let's see triggers traders well if you have seen this about triggers what is a Trigger But This is what happens many times in Trigger meme This is what happens many times on Twitter when you say something and people happen to something Like this These are triggers basically when you say something and suddenly they make faces like this let's see if we look for the grifos Look, the one I like is this one, this one is super cool, this one is yelling [ __ ] what a break Trigger Trigger Well, the truth is that this is what a Trigger is literally What is a Trigger a Trigger is that when something happens in our base For example, when before or after updating or inserting, what we are going to want is to do something, look at our tables that we had users for, we have users in users, one thing that will be cool here is to have, for example, a follower count, no, and that this be an integer that is not and that by default is do Ok it could not be interesting that all users have a followers account because it does not make sense that every time we want to see how many users are following or how many followers a user has we have to do one a leather with a join I don't know what Because that will make the database go much slower So there are times when it can be interesting that we have certain Fields or certain queries calculated before having to look at them continuously What happens to a user they can Follow maybe I know a thousand people a day but it is not something that is going to happen that suddenly 100 million users will follow you in a day it does not make sense no normally the writings of how many people follow you is usually quite few quite staggered and such So what you can do is this, that is, we are going to think every time a follower is inserted in the followers table because we already have the followers table, we have it here, okay, we have this followers table where it tells you the relationship of who follows who? Well, instead of having to do this, let's see how many followers you see, how many followers you have, my question. Well, instead of doing this Select, which is still very expensive and that table is going to get bigger every time, what we are going to do is say Look, we are going to create a Trigger that we call increase follower count and what we do is that this must occur after making an insertion in the follower table, what it is told is at what moment it must occur after or before what action and in which table we have to look, that is, after inserting itself in the followers table here we can tell it for each for each row or we can indicate it for specific rows And such But in this case for each row we are going to tell it what we have to start is and here we tell you how we want to update our database or if we want to do a delete an update we can do what we want in this case what we want we are going to put the end here ok And now I will explain something that is quite controversial I don't like msql very much But at least here what we need is here to say Ok when a new follower is inserted in the followers table what I am going to want is to say Ok then in users we are going to update it and we are going to tell it that the follower count is equal to the follower count + 1 ok we are going to increase the number of users by one and what we have to make sure is that we are updating the correct user. That is, the user ID has to be the same and here we use a keyword that has mysql that here it is New where it refers to the new to what I know what we are adding in this line not what we are adding here and now now we will see an example okay that is after Inc followers that is when you do an insert follower no I know that I don't know how much that object that we are inserting is this that we have here in New and therefore we can access, for example, the following of that element that we are adding that we are doing the insert, we can capture it here and say okay Make me an update of users we increase it by one and we make sure that the counter that we are increasing is the one of the user that is now following And this is what we would do here now here The controversial thing comes a bit that what happens that the sentences to finish them in mysql well in sql in In general, it is done with a semicolon, as you can see here it is not done with a semicolon. But what is the problem with the semicolon? I think that I have set the Trigger well, not that it is that this one complains to me steinment is incomplete update user something wrong Good if not now I look at the one from limeter which surely is this the issue is that to end a statement in sql you end with the semicolon but what happens that we can't use the semicolon also in the Trigger because if not you don't know when is finishing this or when the Trigger is finishing So what you have to do here is indicate how you want to delimit and you can do it with the format that you want So you have to change the sql delimiter at this moment and you can put whatever you want you can put This good This doesn't make much sense Could you put This ok then we say ok delimit me using this So now here at this end we can tell you that this is where it will end and now we have the usual one again which is the semicolon so we make sure that we correctly delimit the Trigger that we can use here the sentence with the semicolon and that way he will understand it perfectly and the good thing about this is Now when we do an insert in the followers table, the user table will automatically be updated and the number of users that are being followed will increase. In fact, we had insertz of followers here if I'm not mistaken, here we don't have how many followers this is We are going to move it here to see if our Trigger works, we have not messed it up, we are going to move it here, we are going to do it after the Trigger, okay, notice that it has a name, because you can eliminate the triggers, you can do a Drop, that is, in fact, we can Drop Trigger and flexist Inc And so you make sure that if it already exists we delete it we create it after insert I don't know I don't know how much and then we do the insert not by being followers here we are inserting followers for example the one follow the 2 here the 2 And to the 1 the 3 and the 1 that is, here the 1 should have the numcount at 3 we are going to execute all this Ok and you can round discommand Now [ __ ] Drop Trigger and FX says no Ah is that I have put it here in the middle It is that this In any case It should be here eh I put it right after the Delimeter Trigger And this doesn't make sense Ok then we put now your Where the usky ok update tweets userscan Let's see he says you are Ah He is complaining about the replace now replace from an update that we had done Here good We are going to remove this for now I don't know why now it complains about that okay okay we have made the inserts these inserts and now if we look at the users from users okay here we can see that the follower count has been updated automatically, that is, this is the users table and what has happened is that every time we have inserted here at the end a follower in the followers table, the Trigger has been fired and has said okay after inserting in followers for each one of those who you have inserted that is for this for this for this for this every time you have inserted one what we have done is update the table you use because many times people believe that this is done in backen and they make us backen they do not backen us I mean This is not very dangerous, many times what makes sense is to do it exactly here in the database Because what we do in the database is to make sure that it is happening when it makes sense, and not that it can be cut or what happens good things and bad things good things the normalization the making sure things are updated bad things this has a performance problem where you have to be careful the number of triggers you do the much bigger the database and the more triggers you fire each Every time you update something, it may take longer to finish, it can be a bit dangerous, there may also be a root condition. Imagine, a Trigger will not ensure that it has finished exactly when you better do a Select from another place, yes, you will not have problems at all. the time to update it because it does block it But you may have a moment when the information is not Exactly The same as you expect So it is another thing if you have many triggers especially that can be problematic So it is an issue that you have you have to be careful Ok, you have to be careful because many times what it could be is that the database throws you away What can you do alternative things that you can do The Trigger can be fine for things that you do want to normalize but There are times when maybe this can be a process in the background that is updated every x time or that is accumulated. Not to say oysters, 10 people have followed you, because when 10 people follow you, we update the counter, okay, we are not going to update it every time What is useful for a person? Why? Because everything that you can differ in charge, better, and you can do this type of thing, the fact of accumulating as in a pile and that you say, Well, that's it, because imagine if this worked exactly like that, it's worth what it costs. You could do is also burst this Trigger. Keep in mind that it is now incomplete because if someone stops following the person now, what we would be is continuously increasing the number of users. So we would have to do the same thing for when it is deleted. can do that is, we could do create Trigger increase instead of increase The Chris after instead of insert would delete no When we do the delete for each one and the place of a plus would be a minus So now each time it is deleted that each time we stop following someone now what we do is make sure that we are decreasing the number vale So as we would have it correctly and this would be The triggers would already be, which is quite good, man. In the end, we have seen how to create a database very similar to what Twitter would be, which would obviously be missing a lot, for example, we should do the same with retweets, with chats, comments, and such, but what is it? based on what could be done Well, a Clone of Twitter with all this and we have also seen how to make somewhat complicated selects with joints with groups with having we have seen How to update How to delete how to drop tables how to alter tables How to make constraints how to do triggers that is, it is not bad in what comes a semester we saw it here you have seen it is not bad eh in less than two hours it has not been bad at all he says It depends on how you need the data you can have a scheduled task that what we have commented does not make a moment yes indeed more than reliable it is how fresh you need the data to be for example in a bank surely it is super important that your checking account the number of money you have is something that is almost in real time instead the number of Twitter followers Well, it doesn't have to be in real time. Surely so much has happened to you when you see a YouTube video, views and things like that, you have noticed that the views, likes and such are updated every so often, so Uff, it's not bad, I'm doing it in Speed Run from the university yes yes it's not bad he says [ __ ] he says I would like you now to make sure you are safe [ __ ] let's calm down
