MYSQL Console Questions and Answers | Peter Fisher | Skillshare

MYSQL Console Questions and Answers

Peter Fisher, Web development teacher and consultant

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
5 Lessons (31m)
    • 1. Welcome to MYSQL Console questions and answers

      1:19
    • 2. How to select times and dates

      8:06
    • 3. What is a primary key? What does NULL mean?

      8:26
    • 4. How to back up a database

      5:22
    • 5. How to delete records without knowing field names

      8:11

About This Class

Welcome to MYSQL console questions and answers from How To Code Well by Peter Fisher.

These 4 tutorials answer some of the questions that my students raised in the MYSQL console course.

If you haven't done so already the I highly recommend that you take part in that course to fully understand these answers

Checkout my MYSQL console course

Transcripts

1. Welcome to MYSQL Console questions and answers: hello and welcome to another how to code. Well, course. My name is Peter Fischer, more freelance Web and mobile applications developer. Now, in this course, what we're going to do is take a look at four questions that I was asked regarding Beat my SQL console course. If you haven't done that course yet, then I highly recommend you do check that out because thes tutorials will make more sense. The four tutorials in this course cover different areas of my SQL. They answer the questions that I had in the Maya school console course. Ah, the 1st 1 is regarding my SQL dates and selecting records where you only have the date and not the time. The 2nd 1 is all about primary keys on null values. The 3rd 1 is how to back up a my SQL database through the command line on the last tutorial covers deleting data when we don't know what the field names are in the my SQL table. As I've mentioned, these four tutorials are from questions I've had from students in the my SQL Consul course that I created. If you haven't seen that already, I highly recommend you do so because these questions and answers will make more sense. Happy coding everyone, and I'll see you again in the next tutorial. 2. How to select times and dates: Hello. Welcome to another. How to cope. Well, video tutorial. My name's Peter Fischer. Today we're gonna take a look at a question asked on the 10th my school video console tutorial. That was how to select records from a my SQL table. The question was asked by Steve Baker, and he's asking how to select records from the my sequel table, based on the created date when you're only caring about the date instead of the time. So the table that we created in the on the 10th video we had be created Andi updated dates set to the current time stamps, so that included the time as well as the date. So Steve here is asking, how do we run a select weary and we were where we're reducing the return records to those that contain just a certain date So we don't care about the time. How do we go about doing this? He's suggesting one of the ways could be to use a wild card after the date. Now, you suppose you could do that, but remember that these are date, date, time, fields. They're not strings. Um, I'll demonstrate a couple of ways to do that in a minute. There is a couple of functions that will also play with, um, is well, so Okay, so we're in the console right now, and if I just did a let me just do a describe off the table, just get everyone up to speed. So we're gonna describe the customer again. We can see that these are the fields that Steve's talking about created, underscored date and the updated underscored date. They both have a type of daytime, so they're not virtuous without strings. They are date times. Okay, um, that default is current time stamps. On the extra on the updated one is the current time stamp, too. So if we just did a normal just we're just gonna do a slept all from customer. We'll see what we can, what we get. Okay, so here we've got the date times on their done in this format. So you've got the date first and then a space and then the time. So how do we reduce? How do we do a slight query where we only want where we're only wanting to select records that were created on a certain date, so we don't care about the time in which they were which they were done. So to do that, we could use the date function that my SQL has. There's two functions that the date function, and there's the time function. So I'm gonna show you shape both ones. So what? What we would do here is we would do select. Actually, let me just clear that screen down. So we have, uh, no. Sorry. Bring him a cup. So select. Oh, from, um uh, customer. Uh, where now what we want to do is we want to use the date function. So we due date on, we open the parentheses, we supply the field that has the date time. So in this case, we're going to do what is it created on school date. So created, underscored date, and then we're gonna equal that to be one of these dates. So let's pick. Um, Let's pick this one. This one was when I just entered into the, uh into the table. Okay. So, notice I haven't selected the the time here. I'm only doing it by date. So that's let's run that. See what we get. So here we've We've only returned the Tony start record idea, for We've returned that because that's the only one that was created on that particular date. Okay, if we were to if we were to do this with just a general like query, then we will We will potentially pullback different records because off their different times, um, I just get on to that in a moment. Let me just show you what the time function shall we. So let's do slept all from customer where time and we're going to focus on Lets say thats one for himself, for instance. So 1300 hours where time it's equal to two that Okay, so that's run that query and we can see that both records that were returned have the times have the time off. 13 15 25. Perhaps not the best one to demonstrate, but let's let's clear the screen on. Let's do another example. So let's run, Um, let's run the 1st 1 So where time is equal to this one here because there's only one record like so Okay, so only one record has been turned. But if I was to scroll up and say let's find all records that have the time off anywhere in the 10th hour. Then what I would do is do they like, um, Query? So we removed the equal sign put in the like we remove. Um, the minutes On the seconds on, we put in the percentage, which is the like, so anything After the 10 hours off, obviously, this is going to get anything in different dates that were done in the 10th hour. And we run this, we can see that we put these two back, and we've pulled these two back because both of them have the time off. 10. Likewise, we can do this. That's just run that back. Likewise, we can do this with the dates so that see, we've got two dates here. So the eighth, the eighth, let's try that. So let's go to, uh, let's go back a bit. So we got date and instead, off date equals with gonna due date is like, Now, this is gonna be slightly different because it's 08 that we're looking for, and it's going to be on the fifth month. We're just gonna do that any year. Okay, So that would be if we had different records of different years that would potentially get everything was that was created on the eighth off the fifth off any year. And because it's of any years because I've got the percentage in here. Okay, so that's the wild card. Okay, so I hope that better explains how I would search for records using particular dates and particular times. Remember, the danger off off using the like is because you might return more records than you potentially need. So if you know that you want to slept records off a specific date, then I wouldn't bother with the light clause. I would just run the date. Um uh like Like that, for example. Okay, so I hope this helps if you got any more questions than please, let me know. Happy Cody. 3. What is a primary key? What does NULL mean?: Welcome to another house code. Well, video tutorial mining to Peter Fischer on today. We're gonna do a question and answers video based on one of my old my school console videos . Um, that was a demonstration off. How to create a table in the mice. Your console. The video was done many, many moons ago, and I put a link to to in this description for anyone to take a look. Now, the question was sent in by Ba Hannam and Anzi, and I do apologize behind, um, if I've mispronounced your name. So the question is, what does it mean by the dock default? No. When you're creating my school table. So what does the default no statement do when creating the table on also, what does the primary key do? What does that mean? So there's two questions here. I'm gonna focus on the primary key first, and then I'm gonna look at the default. No, afterwards. Okay. So really my secure consul now. And if I was to just quickly do a describe off the the customer table So this is the structure. This is the schemer off the customer table here. We can see that We've got a field column on the left and the's are the fields. Allow the columns, which are within the table on. Then we've got subsequent columns, um, that represent different settings and values that make up the structure for each of these fields. Now let's focus on the 1st 1 The i d. The I d has a key off primary PR. I we could see that's the only key in this table. Um, it has a type of 11. It's It cannot be no so knowledge. Seto know that basically means here that you cannot. It cannot be black, okay? It must always be present for every record, So there must always be a value. There must always be an i. D. Value for each record in this in this table. It's the primary key, and I'll get on to that in a minute. It's default is no, which means that the default value off the primary key cannot be back also is blank. Um, and it's got an extra setting off auto underscore increment. Now what this does, and I've mentioned this before in a previous video, which I'll link in the description below. But what this does is it means that for every record that you insert into the table, it will increment the value based on previous inserted I. D. So, for example, if you've got a table of five records and you insert a new record on you, do not supply the I D than the I D will be implemented to be six, because that's the next one. And then the next one after that will be seven and eight and so forth. So what does the primary key actually do? What does that mean? Well, but the primary key is used as a unique identify. A for that record on its helpful. When using running, delete Andi update queries where you can reduce the Queary to just the records that you want to deal with. So let's say you have a table that had 1000 records and you needed to delete the 500 record , but only that record you didn't want to delete anything else. You just wanted to delete that one record. Well, you would do a where clause and I've done this in the video before, which I'll link to you. Put in a where clause where the idea is equal to 500 Andi, because primary key, because it's unique because it's auto increment ing, which means that for every record it's it's gonna be different. Um, you are guaranteeing yourself that you are going to only delete that one record. You're not gonna delete any other record because no other record will have an i d with the same value. Okay, so that's what primary key means. That it is the It is the the three unique value, the unique identify for that 11 record se Also increment means that it's gonna increment the value each time the record is inserted, each time record is inserted into the table. And as I mentioned, I've I've talked about this before and I'll put in a link in the description below. So the next one is about the default knoll, and what does that mean? Well, okay, so we got the default column here on, uh what different values. So we've got no in some of these are from the i d to the email. That's no, but we have different values for sale is active on the gender on the title. Even the created in the update dates. Um, on if it was an updated or created date, then we defaulted to the current time stuff. So what a default does is it defaults the value off the record that you're inserting to the Michael table. Um, it changes its value to be the default if no value is supplied. So if you go into a table, sorry. If you go into a record into the table without created date, then its value will always be the current time. Stamp on in the same light. If you insert a customer record into this customer table without supplying agenda than agenda will always default to mail on. If you do one with. If you insert one without the is underscore active field, then its value will always be set to zero. So what does it mean when you insert what when the default value is set to know? Well, no means that it's black, which means that you know it's not. There isn't 84 value given Andi so so. If so, if you insert a record and it doesn't have the first and you don't supply first name while no default value will be used, Andi if the default is no and it's used in conjunction with, there's no column here. So if if the default is no on the no value is no, we're basically saying that the first name must be supplied because it can't be no on the default is no. Well, that can't happen, which is why you get an era. So I'll just I'll just prove that. So let's let's run an insert. So we're inserting into the customer table where resupplying the first name and we're saying the value of the first name is going to be set to food. So we're not supplying the last name of the email or the is active. We know even supplying the I d. On d. As I mentioned before, we don't need to supply the I d because we were always going to auto increment. But this won't work, and it won't work because the last name has the Noah setting off. No, but which means that it cannot be No, but the default is no. Which means that that's going to throw an error. Okay, so let's just run that and see what happens so we can see that the foot the field. Last name doesn't have a default value, and that's because the default value is no and it cannot be no. Okay, so I hope that has explained what a primary key on what the null means, as I've mentioned before, if I have touched on these in a bit more detail in other videos, which I will link in the description below. But if you've got any more questions than please do, let me know on Happy Cody. 4. How to back up a database: pizza, Fisher. And welcome to another question and answers video based on the my SQL Consul tutorials today we're gonna take a look. A question asked by Emmanuel over Twitter Emanuel says Hi, Peter. I have just been following your tutorials on SQL and I really appreciate it. However, I do not know how to back up. Well, Emmanuel, I'm assuming you mean my SQL rather than SQL because they're very different on the way in which you back them up is very different to So for now, I'm just going to assume you mean my SQL. So there's quite a few ways to back up with my SQL database. You could, for instance, use a gooey such as PHP, my admin or my SQL workbench. Both of those have tools to allow you to export the data base schema as well as its content to a file on. Then you would use that far as the backup. But as this is a my school console, Siri's, I'm going to demonstrate how to do a my SQL database backup using a command using the terminal on the command, my SQL dump. So we're in the terminal right now, and in fact, we're in a a blank directory. So if I didn't l s You can see this. Nothing in here. Now, the thing output off this demonstration will be a a file, an SQL file containing the database schema and its contents that's gonna be produced by command called my SQL Dump. But before I get into any of that, I just want to do my SQL dump minus minus help. And that's going to show you the help text off the my schooled on command. And as you can see, there's quite a few variables on arguments and options that you can pass to the my school dump command on that Taylor. It's tailor its output, and perhaps I'll do a couple of videos in the future going into more depth off the mice quell dump command using some of these advanced features. But for now, we just want to focus on creating a back up off the database in its current state. So let's run my school dump on. You need to provide my squirrel jump with Theo user credentials off the user who can access the database that you want to back up. So to do that you do minus you. And that's the user name. So you type in the user name there and then Meyers. P Now we're going. We're not going. Teoh, enter the password into the command because the minus P flag will prompt you for that password once the the command is executed. Okay, so the next argument that you need to provide is the name of the database that you wish to back up. So in this case is just pull test. Now, if I was to run this as it is now, it would just return the output off my SQL Teoh to the standard output. So funny. Press enter. Now, of course, it's gonna ask me for the password because of then minus p ah, argument there. So just type that in on. Yes. So that's the output that's being produced by my SQL dump. Now that's pretty useless in its own in this form, what we really need to do is produce a file from this output on. The way to do that is to use something called UNIX streaming output streaming. So we want to stream the output off my SQL dump to a file and we do that using output redirection. So you want to redirect the output of my SQL Don't generate a new fall called, uh, test on the school be KP SQL. So that's going to redirect the output from here on. Put it into this file. Okay, So putting the password again, and then if I was to type ls again, we can see that the file has been generated. So if I did unless on that file, just to display its contents, we can see that. Yeah, we can see that the customer table the schemer has bean produced. So that's the create table command for the customer table. And then under that we should have yet the the insert commands for the customer table. So this will in certain values and then the underneath that will have the various other commands to generate the other tables and their contents and so forth. Okay, so that's Ah, very quick demonstration off my SQL dump. How to back up a my SQL database to a file Violet terminal. So, Emanuel, I hope that has helped in some way. Thank you for watching please click the like button. If you found it useful and make sure to subscribe to get the next tutorial. If you have any comments, questions or queries than please leave them in the comments section below. Alternatively, you can tweet them to my Twitter handle, which is P F W de facts again, and I'll see you soon. 5. How to delete records without knowing field names: Peter Fischer on Welcome to a new section Off Question and Answers based on the My SQL console tutorials today, I'm gonna focus on a question that was sent in quite some time ago by a Praveen Kadhem. I do apologize if I've mispronounced your name. Ravine eso. He asks how to delete data from a table on, then insert dated back into the table when you don't know the column headers. And then he goes on to ask how to change a field data type from double two decimal. So there's a couple questions here today. I'm gonna focus on how to delete data from a table when you're unsure. Off the column headers. The question regarding how to insert data into the table when you don't know the column headers. You can't really do that, unfortunately, because you need to know what the column headers are in order to associate the values to the to the the fields in the table schema. The question about the changing off the double to the decimal kind of warrants. It's own explanation, so I'm going to focus on that later on. So today I'm going to deal with how to delete data from a table when you don't know the column Headers. Okay, so we're in my school concert right now. I've created attempt table and I've added some dummy data in there to play with. So let's just show the tables, lips, tables with s on. Yes. So we've added the temp table in here. I'll just do a quick, select pull from temp, and we can see that this best five records in this in this my SQL table. So the usual way off deleting data from a table would be to describe the table to get the table schemer and then from that work out which, um, field is represented off the primary key and then delete the records or records using the primary key. Of course, you can't delete the records using any other value, such as the total. Here, you could delete records that have the total of 2.5. However, you're not going to guarantee that you're only going to pluck out the records that you really want to delete, because there could be other records that have that value using the primary key. If I was to just describe the table, it's him looks it so using the primary key, which you can see here represented by the The key column. The P A r. I value here using the primary key, you're kind of guaranteeing that you're you're isolating that record to be removed because it's got the extra setting off auto increment. Every new record that gets added to that table has its own unique value. The Valley gets implemented each time it gets added on. Therefore, you can isolate which record you wish to be removed or records. So I'll just quickly show you that the usual way. So let's delete from temp, and then you would give Aware could also where I d. So that's the primary key is equal to four. So that's going to delete the record that has a primary key or force and its run that we could see that one row has being affected. Um, on then that's selectable from temp. Yes, so the fourth record here has been removed. Please. But the question really was how to do this when you're unsure. Off the off the column headers. Now there are two ways to do this. However, both ways will remove all the data in that table, and they also are that they will also do different things to the table, which I'll demonstrate now. So let's just clear that down. So the first method I'll show you is how to delete from the temp table eso that's deleting from temp from the temp table because I haven't specified a where clause. It's going to remove every record from that from that table. So let's execute that we can see that full rows have been affected, which means that four O's were altered by Brian is query and also deleted because it was a delete routine. So let's just quickly run a select so that will return an empty set. So there's no records in that table anymore. Now the downside to doing it this way is that we don't reset the auto increments setting off the primary key. If we, for instance, if we described attempt table, we can see that the I D here has an extra setting of water increment. Now, as I've described before, this means that for every new value that gets added to the table, the i d value will be implemented. So the last one we added, was five. So the next one will be six, and then after that will be seven and so forth. Now this is stored in the my sequel memory. And even though we've gone and deleted all of the records, it's still got the last inserted I D in memory. So the next time we insert something into this table, it will have the next the next implemented I devalue. So I'll just demonstrate that. Now let's some amorphous Let's clear that down. I'm gonna do a reverse search collected this earlier, so uncertain to tempt. So we're inserting these five value these five records into the temporal table. We're not specifying the I d field here, so it will be using that extra setting to get its incremental value. So let's run that. That's added five records into the table. It's clear that down and then run Slept all from Tim. So this is what I mean. The i d. Here is six, whereas before it was obviously one, and I will just quickly show that again. Let's delete from temp. So remember the last one here a 10. And then we insert those five records again, um, clear that down and then slept, and we can see that we're starting from 11 now. The alternative way to do this is to truncate the table. Now what truncate does is it removes all the values in all the all the records sorry in the table as well as resetting the the auto increment Extra, uh, setting. So to do that, we do truncate and then we cant feed tempt table thing. Okay, So even though it says zero rows of being affected, the query did run. Okay, so let's slept all from temp. We can see that it's definitely empty. And then let's insert those back in, and then I'll play a screen and then slept from town when we can see that the I. D has been reset back toe one. Okay, so that's the two ways to delete records from a table when you're unsure. Off the column headers. Thank you for watching. Please click the like button if you found it useful and make sure to subscribe to get the next tutorial. If you have any comments, questions or queries than please leave them in the comments section below. Alternatively, you can tweet them to my Twitter handle, which is P F w de facts again, and I'll see you soon