SQL Tutorial for Beginners - Advanced SQL Tutorials

what is going on guys my name is Bucky Robertson welcome to your very first tutorial in MySQL now in order to understand what exactly MySQL is we first need to take a look at what a database is now a database is pretty much a collection of data on a computer now it isn't just a whole bunch of data packed into a file or randomly jammed on your computer it's organized in a very specific fashion it's actually made up of something called tables now each table is going to store similar information sounds pretty confusing well let's go ahead and take a look at a quick example say you were making the website YouTube now YouTube stores a bunch of information like usernames passwords links to videos people's comments favorites they don't just want to store all of this information in random files so what they decided to do is better organize this in tables so instead of just taking all the information they need and throwing it randomly onto a file they broke it up into a user's table videos table and comments table so as we can see we can use tables to organize similar information so now all of the users would go in this table all of the videos in another table of the comments and a separate table and now that you are separating specific in similar information it just helps you organize your databases better so now that isn't only the only way that we break up to organize that we actually break up these tables in a very specific way as well so let's go ahead and take a look at those so tables each individual table is made up of columns in rows now the columns are like categories and the rows are like each separate entry so if you're saying what the heck columns in a row is kind of like a grid well yes it is kind of like a grid now if we took a look at a table here say I wanted to make a table to organize all of my friends so I would have each column with a specific category for example first column would be name all of the items in this column would be the name I would have another column or category which would be age in this case now only age is going here and my last category for this example is profession so again as you can see columns are kind of like categories now the roles are a little bit different because each individual entry goes on a separate row the first entry I have is Bucky who's 25 and his profession is a shepherd now on the second one like I said each row is like an individual entry so for Alisa's entry it would be named Lisa age 68 profession magician so as you can see once we have each column with a separate category in each row is a separate entry it could really help you identify and organize a bunch of data very specifically because then if you're saying okay how old is Ashley well you know to go in the H category and you know to scroll down Ashley and where the two meet that's the answer you're looking for so instead of just scrolling through random data breaking databases up into tables and then furthermore into columns and rows could really help organize your data in a very productive manner so as we can see say we wanted to make a huge database that was going to store all of the people in the United States well whenever we were making the database say we organized it like this we would say okay database look for a person named Bucky and I would say okay um I didn't find one there actually six thousand people in the United States named Bucky so we say okay what about a person named Ashley who is unemployed and it says okay and think about this for a second how many people named Ashley are unemployed in the United States right now probably five thousand I'm guessing well this isn't a really effective way to make a table because you can have a lot of duplicate entries even you can and have actually that's 21 and unemployed they're probably a couple hundred of those so instead of just making a table like this what you need to use is a primary key now remember this whenever you make a table in a database you need to have a primary key and that is a column that's guaranteed to be 100% unique so basically no entries or no rows can have the same primary key so let's go ahead and take a look at this table with primary keys so whenever you're making a website aside from letting the user have a user name which may be the primary key it's always good to assign them an ID number or our primary keys in America are our social security number no one else has our same social security number so basically whenever I'm saying okay find all the Bucky's well it may return 600 but if we say okay return the person with a primary key of one it's only gonna return one result and you can say to your database okay find all the ashley's who are 21 and unemployed well they that may give you 500 results but we only are looking for this Ashley so instead we would say okay find the member or find the person with ID of 3 and that is why you use a primary key because it's guaranteed to only return one result so say this Ashley right here died and we needed to remove her for a database from the database instead of saying okay remove Ashley from the database which may remove 500 people we just say okay remove the person with the ID of 3 and bam we're good to go we didn't screw up our database so now before I let you guys go I'm all I want to cover one more thing what is SQ oh because these tutorials are going to cover MySQL SQL is the language that databases use now we just can't say you know shout into our computer hey database um delete Ashley it doesn't understand English it needs to use a special language and look at that in a couple minutes that's called SQL now what my SQL is is basically a program that understands SQL now whenever we learn this SQL we just can't open notepad or Microsoft Office and start typing this in it's not going to know what the heck this crap is so instead my SQ o is the program that understands SQL and also there is other software aside from my SQL that can understand this language but MySQL is the best it's free it's fast and that I mentioned it's free oh yeah it is so anyways there you go there is your quick background on databases tables in SQL so yes thank you guys for watching I'm gonna be making a bunch of tutorials so don't forget to check out the next tutorial don't forget to subscribe and I will see you guys in the next video what is going on guys welcome to your second MySQL tutorial and in this lesson we're gonna be talking about my SQL servers no more slide shows just I'm gonna get our hands dirty and start doing some stuff on the computer so like I said the very first thing that you need in order to start practicing MySQL is a my SQL Server what this is is a server with MySQL installed on it so if you're learning this because maybe your boss said hey you need to learn MySQL or maybe your work in your taking a class on database in then 99% of the time they already have a server with everything set up ready to go the only thing that you need to know how to do is log in and they're gonna give you the login information however if you're just learning this at home because I don't know maybe you just want to learn about MySQL or learn how to make awesome websites then you also need access to a MySQL server now it isn't like you can just go look under your bed and find one laying around it's not like a shoelace you probably need to go Google web hosting companies basically you need to find somewhere where you can access a server and install MySQL on it now like I said you can either Google server space and buy an account then install it or what you can do is you can just look for companies that offer my SQL already installed on the server and it's not like this is an uncommon thing where you guys are gonna have to search for hours 90% of the companies already offer servers with MySQL install it on and that's how common this is the only thing that you need to know how to do is basically you're gonna just need to create an account log in and you're gonna be ready to go everything's gonna be set up now the company I recommend if you guys don't have access to a server already and you need one I'm gonna recommend going through Hostgator comm and I don't say this because they pay me or anything although I kind of wish they did but this is an awesome company and they have all the tools awesome support and I just love them I actually been with him for a long time but I want to mention this if you guys can find a free one then I highly encourage that and if anyone can actually find a free one then go ahead and leave a comment below don't keep the secrets to yourself like I said I'm always encouraging free schooling in education especially when it comes to software so like I said leave a comment below or on my forum and I'm sure there are free ones out there so anyways once you have a server set up whether it's through your school or work or you have to buy one online then what the heck do you do where are you supposed to start typing this SQL code well there are a couple of different places you can start practicing SQL MySQL now I want to mention this before you guys you know get disappointed if you are doing this through school then your teacher is probably going to make you use some command my prompt that looks like this let me actually go step in the wrong thing there CMD they're gonna make you use a command prompt that looks like this it kind of looks like you're programming the matrix or something I don't know why they do this it's just torturing you guys but anyways whenever I was in college that's what they made me do now I'm gonna be using a different tool that's a little bit prettier why well first of all because I'm not 60 years old and I don't want to use a tool that looks like this but I want to mention that no matter what tool you use you're gonna be able to follow along with these tutorials just fine so don't think just because I'm using a different tool then you're not gonna understand what's going on the core language is basically the same no matter what tool you use and for those of you who are wondering what tool I'm gonna use it's called phpMyAdmin whenever you log into your account it's gonna be right there so anyways that's what tool i'm gonna be using and like i said no matter how you log into your account no matter where you get your server from no matter what tool you use you're gonna be able to follow along with my tutorials just fine so in the next tutorial what we're gonna be doing is we're gonna be creating our very first database and I'm gonna be showing you guys how to import all of the tables that you need and yes I made them in there available for 100% free thank you very much and I'm gonna be showing you guys where to download them from my website and how to import them so you can follow along with my tutorials but for now thank you guys very much for watching and I will see you in the next video oh and by the way don't forget to subscribe what is going on guys welcome to your third MySQL tutorial and before we continue I want to mention this whenever I start teaching guys about all the different MySQL commands all the different things that you can do to the database I'm gonna be starting teaching you guys some very simple commands now these commands may include something like how to view your information how to sort your information in your database however before we use those commands we obviously need some information we need some data so we can sort something or view something obviously now lucky for you guys I already made all of the tables that you guys need to follow along with these tutorials you can download the files and use them whenever you want for 100% free now in order to use this information in order to use this data you need to create a database first because that's where you're going to be putting the data so in order to create a database what you need to do is log in to your account and you can either hit this my SQL database wizard or this my SQL database icon I think this one is the easiest one although some people may disagree with me but if you want to follow along with me click on this MySQL databases icon and what you do is you create a database by just naming it something I'm gonna name mine YouTube and hit create database now you hit it and it says okay created my SQL database it basically gives you a confirmation if you don't see this then you did something wrong so now we have a database created we can begin filling it with data however I want to add a MySQL user to this database now I don't know in the advanced tutorials if we're gonna need this user because typically whenever you're writing a website in PHP or something you need this user but anyways I don't know if we're gonna need it now but in case we need it later on I don't want to come back to this section and do it all again so might as well do it now so in order to create a user just go ahead and give them a name I'm gonna name him Bucky now you can write your own password but what I like to do is I use the like to use the password generator it gives you a weird password there's mine and just go ahead and click I've copied this in a safe place click use password and then once you have a name and a password go ahead and hit create user so now we created a database and we created a user now when do we need this user well sometimes we need a user to access the database which we'll see later on in the more advanced tutorials but anyways in order to add that user to the database we pretty much want to give that user access to use that database you need to scroll down to the very bottom section and choose your user and choose your database and hit add now whenever you're programming this is like really advanced stuff that you guys don't need to know now but you only want to give your user specific privileges only the privileges that they need however for the sake of these tutorials I'm just gonna go ahead and give Bucky all privileges give him access to do anything he wants to the database so now we go ahead and make changes and now pretty much Bucky has access to do anything to this database now that really wasn't important for this tutorial so let's go ahead and click home the key thing that you need to make sure is is that pretty much you have a user and a database so now we have a dad base oh that's pretty cool okay so how do you see it does it have tables in it already does it have information in it well in order to see your database go ahead and click this icon called phpMyAdmin now whenever you click it it says okay wait wait and now a tool called PHP myadmin opens up now in this tool is where we're going to be typing all of our SQL commands this is where we're going to be spending most of our time now all of your databases are listed on the left hand side now obviously we just created this one called Danielle underscore YouTube so that's why that one's there you also may have one called information Gemma or however the heck you say it and some other ones depending on if your teacher made some dummy one's mine anyways the one you created should definitely be there now in order to view any tables in it just go ahead and click the name of your tab database and if you have any tables already like for example obviously we didn't create any tables yet but if you do have a table like from your work or if your teacher already created some tables for you they're gonna be listed on the left hand side however well I'm gonna be doing is I'm gonna be teaching you guys in the next tutorial actually I think I have time in this tutorial so that's what I'm gonna do now obviously we need some data so in order to get some data to following in these tutorials go to my website called T n B forum com it's my forum and by the way if you guys have any questions that you need answering just go ahead and ask me I'm gonna forum and I'll answer for you there by anyway scroll down to the section that says SQL and databases make sure to ask your questions there now at the very top you're gonna see a link that says files from tutorials go ahead and click that and whenever you do you're gonna be able to click on this MySQL files zip folder now go ahead and click that and you're gonna start downloading this MySQL files these are all of the data files that you guys need so whenever I'm working with a table in one of my tutorials and you guys whoa Bucky I don't have that table then just go ahead and follow along download the file this is where it is so in order to pretty much abstract or excuse me extract it just go ahead and double click this and depending on whatever program you have just go ahead and click one click unzip and then it pretty much extracts it to your downloads file so remember downloads you're gonna have a folder called mysql' files and then another one and then you're gonna have a table name followed by dot SQL now remember in the very first tutorial that I said that pretty much all of the information is contained in tables you may have a table that has all the user information you may have a table with all the product information you may have a table with all the video information I'm gonna name each of these files just the table name so you're saying okay I followed it along with the years tutorial so far I downloaded this SQL file which whatever you say has data in it now how do I get it into my database right here well in order to do that you need again make sure you're in PHP myadmin and make sure you're in the database you're working in so make sure you have the database selected in this case it's Danielle underscore YouTube now once you have the database that you want to import your table into go ahead and hit the button import this means pretty much import outside data now my data or your data as it is now it's in that downloads file and if you click MySQL files it's the folder or excuse me it's the file customers sq oh so go ahead and double click that and now it says ok it verifies that excuse me I click something wrong it verifies what follow you're trying to open or import and then leave everything else at default now if you accidentally click the button then make sure pause the screen for a second and make sure all of your you know settings and check boxes are the same as mine format SQL so on and so forth and once you choose your file just go ahead and hit go now check this out it says import has been successfully finished 11 queries executed ok kind of confusing more importantly is you now have all that data I made for you guys on your database how awesome is that for free didn't have to pay for anything so you're saying ok I don't believe yeah well if you don't just go ahead and click on this customers table over here and now check it out if you scroll you can now see that all of the information that you guys want is right here and by the way if you're saying ok I just imported a table called customers what the heck is this well this isn't any real customers form any real company or anything it's just a quick little sample table I made up for teaching tutorials basically it has an ID number a name and these are just fake names except mine that's real address city state and zip basically just a bunch of fake data that we can play along with and basically practice my SQL and SQL queries on it so there you go there you have it that is how you get free data and by the way I know even though there is one file in here for now in the upcoming tutorials I'm gonna be making more tables and whenever I do make another table then just go ahead and download it again and it's gonna be there so I'm gonna be updating this as I'm teaching these tutorials so if you ever say hey I didn't get that table well just go ahead and download it again but anyways I'm rambling and I need to drink of water or something so thank you guys for watching don't forget to subscribe and I'll see you guys in the next video alright ladies and gentlemen welcome to your fourth my SQL tutorial and now that we have some data to play with let's go ahead and play with it so open up PHP myadmin by of course clicking the link and once you do your PHP myadmin tool should open up now let's go ahead and serve with some really simple commands now in order to type in these commands you always go up to this SQL tab now whenever you click it you have this text box that opens up and this is where you're going to be writing your commands so let's start with something very type in show databases and once your command is complete just go ahead and click go and check it out whenever you hit go it says your SQL query has been executed successfully if you have any errors in your queries then this is gonna be red and it's gonna say you know there's air on line two or something but anyways what this query does whenever we see show databases it basically lists all of the databases in your servers so right now we have information Shaima or schema however the heck you say that was already built into MySQL we didn't make that one and we also have Danielle underscore YouTube so if you're just running this for the first time and you want to see what databases your work or school has go ahead and run that command so basically like I said that is how you run a simple command and another thing I want to take note of is that sq oh the language is typed standard in all capital letters now the only thing that's not going to be in capital letters is table names and column names those are gonna be in lowercase but all of the language itself for example like the keywords those are gonna be typed in all capital letters by standard now if you already are in a database for example R if you already are in the database Danielle YouTube and when I say in the database in order to get in a database then I mean like if you have the database selected already then let's go ahead and run some simple commands from here so let's go ahead and select our Danielle underscore YouTube and hop over to this SQL tab now now that we have this database selected let's see what tables we have in the stat base so I'm gonna go ahead and run the command show tables and I'm gonna go ahead and hit go and see what tables we have in here well right now we only have the customers tables so that is basically what the command does whenever you hit show table is it basically returns a list of tables for the currently selected database which was in this case Danielle underscore YouTube so what if we want to I don't know see all the column names for a specific table well of course go up to this SQL and again make sure we're gonna be working in danielle underscore youtube for the rest of these tutorials so basically i want to say i want to see all of the column names from the table customers because remember customers if we click on it we can see that it has ID name address city state and zip but we want to do it through SQL because that's what we're learning so go ahead in your SQL tab and in order to do that hit show columns i always spell columns wrong from and remember like i said the language SQ o is all in caps but your table names are in lowercase so customers so show comes from customers whenever you run this command what its gonna do is it's gonna return the names of the columns for example ID name address city state zip basically those categories as well as some other information and it will cover this other other information later on this is the type of data and like i said i'm gonna be covering this later on but one key thing i want to point out is this PRI that stands for primary key now remember in the first tutorial i told you guys about primary key was pretty much the one thing in your table that you can't have repeated so if you look it says the ID was the primary key and that's why all of these numbers are unique by anyways let's go ahead and learn a few more commands so as you can see already probably that s qo it isn't you know a weird computer program language that you're gonna need an interpreter for it it's made up of these keywords that are pretty much written in plain english very easy to understand now one of the most common commands that we're gonna be working with is the Select command SEL ECT now this command does is pretty much select the information or data that you want so let me go ahead and hop over to this customers table real quick you see that this customers table has a bunch of information on it ID numbers names addresses city states zip so let's go ahead and select one of these columns so of course SQL and I'm gonna go ahead and select what can I select City from customers now I'm gonna go ahead and run this and then I'm gonna talk about it so go ahead and hit go and as you can see from our customers table it returns all of the cities so anytime you ran a query you can either look at that query right here or if you want to see it exactly like you typed it hit show query box and that's gonna go ahead and show your query but for the purposes of this tutorial or excuse me for the purposes of this query I'm just gonna go ahead and read right from here so what the Select statement does is it pretty much selects what information do you want and you type this right after select so we want to select the city from and from is a keyword what table so basically we wanted to select what information the city from what table customers it's our only table we have to work with right now and there you go there you have it the results of our query and by the way I probably should have mentioned this a while ago whenever I say query I just mean a command or instruction that we're telling MySQL to do so that's what query means I know I said it for like the last five minutes I didn't explain but it's pretty much an instruction what we've been typing in are all queries on the subject of queries I guess I might as well teach you guys well I guess I might as well go over a few things before I continue with these tutorials first of all whenever you make a query or command in your results are not in the same order as mine don't worry it doesn't mean it's broken or it doesn't mean you know yours is messing up or mine is messing up these aren't sorted or they aren't filtered or anything I'm gonna be teaching you guys about filtering and sorting your results later on but for now just make sure that as long as you get the results of Cities from the right table then everything's good to go another thing is whenever you run your queries you can run multiple queries or commands at a time for example you can select city from customers and you can also run another command at the same time for example um select ID from customers ID now take note that whenever you run multiple queries at the same time then they must have a semicolon at the end whenever you just run one the semicolon is optional but whenever you run multiple queries like this then as you can see it must have a semicolon at the end of each one so I just want to point that out and also whenever you're working in the command line then you don't have that option of with one query in phpMyAdmin the semicolon is optional but if you're um watching these tutorials for school and you're running these in the command line then please make sure that you have the semicolon because the command line is very picky and needs that semicolon at the end another thing is that and I probably shouldn't even tell you this to be honest but I'm gonna because I just want to teach you guys everything you know how I said that by standard SQL is capitalized and everything else is lowercase well that's the standard but it isn't 100% necessary SQ o is not case-sensitive you can run something like this select city from customers and you can go ahead and run this query and it's gonna run just fine however it's not standard it's really hard to read when you do that and if you do this for a company you will literally get fired if you do this that's how much people hate it so please even though you can do it please I'm begging you don't like I said I probably shouldn't have even told you guys about that now the last little rule I want to talk about is this white space and multiple lines are ignores for example whenever I run a query like select city from from customers wow really messed that up customer okay take a deep breath customers like that I'm just go ahead and copy this real quick I can run it and it's going to run just fine however I can also run it with a bunch of white space in between and white space are just characters pretty much spaces that don't do anything I can go ahead and run this and it's gonna run just fine and last but not least I can go ahead and even put a bunch of lines in between here go ahead and run it and then it will run just fine so the reason that they made it set up they set up the language to do this was for readability purposes say you wanted to only write one MySQL keyword per line well you can go ahead and write it like that and it's fine it doesn't affect the results at all it's just for your purposes whatever you think is the best basically whenever you're working for a company whatever your boss says that's what you got to go ahead and do but if you're working on the website your own just whatever you think is the easiest to read then do it so white spaces and line breaks are totally ignored they don't have any effect on the results so that is all you get for this tutorial I know a lot of information but in the next story what we have to do is we need to learn about more commands other than show and select so um thank you guys for watching don't forget to subscribe and I'll see you guys in the next video what is going on guys welcome to your let's see what number it is a sixth I believe I forget this huh I forget this more than I should but welcome to your sixth MySQL tutorial and in this lesson we're gonna be going over the Select statement a little bit more because in the last couple tutorials what I did is I told you guys how to retrieve one piece of information using the Select statement for example I taught you guys how to retrieve that name or you could retrieve the city and if you wanted to retrieve more than one piece of information you would have to write multiple select statements or do you well let me go ahead and show you guys this first you can retrieve more than one column in the same select statement for example let's say you want to retrieve name and zip code from customers well you go ahead and write name and then go ahead and right , and then write the additional piece of information you want to retrieve so as you can see using just one select statement it's easier than using you know two or three just separate the columns you want to retrieve with a comma now let me go ahead and type this thing explain something now one very important thing that you want to keep note of is this whenever you retrieve multiple select statements you don't put a comma after the last one I see people do this a lot and it's a mistake so for example since we're retrieving two pieces of information that means we would use one less comma so one if we retrieved all of these what are those six columns then that means we would use five commas so that's the rule one less comma than columns remember that never put a comma over the last one so let me go ahead and copy this first then hit go and check it out it now retrieves both the name and zip in the exact same select statement we don't need multiple select statements so that would save you having to type a whole bunch of select statements however let's go ahead and take a look at this customers table again let's say that you know okay our boss wants us to retrieve all of the information in this table so we say okay boss I mean go ahead and write your script that can do that ID we would write something like this select ID comma name comma address comma city take a break here go eat some lunch comma stay calm as if that can take a whole lot of time in whenever we type more than one columns then the chance that we're gonna make it air greatly increases so let me show you guys a little trick instead of writing every single column whenever you need to retrieve all of the data you can just go ahead and write this little asterisk now this is formally called the wild-card like symbol or whatever so I call that the asterisks or the all statements that a little key above the a on your keyboard but if you're in school and you're taking a test make sure to call it the wild-card statement so basically what this means is whenever you need to retrieve all of the columns you can just go ahead and write this little asterisks and it just is like a shortcut so now let me go ahead and hit go and check it out it retrieves all of the columns and all of the rows from Roche ever table you specify now I want to mention this I know I didn't talk to you guys about how to use MySQL with PHP or whenever you're making websites or in any type of application actually but whenever you do you can do something like this you can retrieve all of this or excuse me all of the data that you want to using MySQL and only display what you need in your application and by application I mean you can go ahead and retrieve all this data but only display the name and city on your website or something so a lot of people just use this statement all the time select all from customers and then they worry about what information they want to show the user later on but I want to say this don't do that please that is a big mistake that beginners do only use what is absolutely necessary because if you do this whenever you select all of the information and you don't even use some of the information then it's going to slow down your query or the execution of this MySQL script well it's not really important and this one because this is a relatively small table but whenever you're working with a whole bunch of data with a whole bunch of users it can cause significant problems and it could slow down the website for people it could run into you can have something called MySQL slow queries and then whoever your host is for example hostgator would start limiting my bandwidth so guys I just want to say even though you can retrieve a whole bunch of data and only display what's necessary to the user please don't do this please only select what is ever necessary and only uses special shortcut whenever you absolutely need to so I know I talked about that for a while but guys it's a very important more important than I could stress oh so anyways thank you I need to go get a drink of water or something so anyways I'm gonna save the rest of the information for the next tutorial so like I said thank you guys for watching and we'll just subscribe and I'll see you guys then what's going on guys welcome to your seventh MySQL tutorial and in this lesson be learning more about MySQL pretty cool huh so the first thing I want to tell you guys is say you were in this scenario you're sitting there working at your desk and the boss comes in and says Bucky I need you to give me a list of all of the states our customers are from so we say okay just give me an hour no problem select state from customers so we go ahead and run this query an hour later after taking an extended break we call our boss back and say hey there you go check it out so he's reading through this lesson he's like all right looks pretty good New York North Carolina yadda yadda yadda okay what the heck is this New York again North Carolina Bucky what are you doing you giving me a wrong list that has like all these duplicate and non distinct results what did you just like copy this twice and we're saying no our customers are actually you know maybe we have to from New York maybe we have three from North Carolina and he's like dude because evidently he calls me dude he's like no I only want a unique list I don't want to get results so we say Oh idiot you should have told me that boy I don't say that out loud so in order to get a list of unique or distinct results in other words we only want each row to display once even though we may have additional customers for example four or five customers from New York if this is the case we only want to return at once so in order to do that we use the keyword distinct right before the column names now whenever we go ahead and run this query check it out we say okay call him back he checks our work again New York North Carolina okay make sure they didn't repeat and they did it excellent so that's what we have to remember whenever you want distinct or unique results even though you may have a duplicate result in your data use the keyword distinct and it's only gonna return that row one time for each value so now our boss is looking at all of this in the same okay how come every time you run a query it returns you know like a hundred results and we say well because that's how many rows we have in our database but what if he's saying okay is there a way that we can limit the number of results because I don't want to be looking at a hundred results every time I ask you a question and we say whoa boss indeed there is so in order to do that just go ahead and run a select statement so say we wanted to retrieve the ID and the name from customers we just go ahead and copy this and run it now whenever we were do this it would return however many customers were in our table so a goof I think our table has like 100 customers in it so we would go and we show this to our boss and he's like dude I don't want that many I only want the first five so in order to limit your results use a keyword called limit that's the beautiful thing about MySQL it just makes sense in everyday terms you don't have to like translator or anything whenever you want to limit your results use the GUI limit makes sense so we go ahead and show him this and he's like ah I'm not bad so based on the first column you select it takes that criteria and basically we're saying okay limit the first five people based on their ID number so as you can see that is why we got the first five people one two three four five oh excuse me so now we're saying okay that's nice and all but what if I don't want to start at the very beginning what if instead of you know getting the first 10 or 20 customers I wanted to retrieve ten customers but only I don't know maybe I wanted to start with number six or something well in order to do that we need to use an additional variation of limit so of course we know that whenever we use limit with one number it automatically starts at the very beginning however there's also variation where we can use it with two numbers so let me run this query and talk to you guys a little bit about this instead of a limit five I use limit five comma 10 and hit go and now I can see that a retrieved 10 pieces of data starting with number 5 and if you're saying ok this actually starts at number 6 so what the heck is going on well first let me talk to you a little bit about how with two numbers worse you can use two number limit to specify a range now whenever you're doing this the first number is your starting point and the second number is how many rows or how many pieces of information do you want to retrieve now remember why does it start at six even though I put five here the answer to that is because unlike people where we start counting at one one two three four five computers start counting at zero zero one two three four and that is why our first result is six instead of five so one last time remember this limit with one value whenever you hit something like limit five that always starts at the beginning however if you want to do a range then you need two values the first value is your starting point and the second value is how many pieces of information or how many rows you want to retrieve so basically you can use this variation to start any well anywhere that you wanted to so there you go there you have it hopefully you guys understand limit now because we're gonna be using it a lot in the future and the game if you guys have any questions just go ahead and ask me on my forum tnb forum com so again thank you guys for watching don't forget subscribe and I'll see you guys in the next video what is going on guys welcome to your eighth my SQL tutorial and in this lesson I want to start off by talking about fully-qualified names now let me go ahead and show you guys what the heck I'm talking about select address from customers I'll go ahead and show you guys the variation that we've been doing and then I'll show you guys how to make fully-qualified names so if you go ahead and run this query we can see that it selects all the addresses from the customers in our table simple enough so what is a fully qualified name a fully qualified name would be something like this select customers dot address from customers we go ahead and run this query it gives the exact same thing so basically am I saying that there is two different forms of writing the same query two different variations of the same syntax well yes that's exactly what I'm saying so why is this the case why would they make minus glow to basically be we can give it to different instructions in the exact same way so first of all whenever you're working in a class and you have a really strict teacher they may make you write the fully qualified name just to you know make your life more miserable or a more useful way that you could you would write fully qualified names is say you were making a huge website and you had something like eBay where you had buyers and sellers now buyers and sellers both have addresses so you may want to write ok buyers address sellers address and then quickly just by looking at your code you can determine what table you're working with so whenever you have a lot of tables and you had the same a column in multiple tables it may be useful so that is you know a different way when you would want to use fully qualified names but for the rest of these tutorials I'm just gonna be using the I don't know what they're called fully unqualified names I guess I don't know I just made that up don't quote me but now that we understand fully qualified names basically it doesn't you know matter performance wise it's just you know okay I'm just gonna move on because enough with fully qualified names they're boring what isn't boring though is learning how to sort our results so let me go ahead and run a simple query like select the name from customers go ahead and hit go and look at our results Bucky Noah Kelsey Corey Harry okay that's nice but our boss is gonna come in he's gonna be I did but key these names are way too randomized I want this in alphabetical order and we're like oh crap how the heck do we do that but lucky for you you've been watching my tutorials and you know that if you add the keyword order by and then you go ahead and write the criteria you want to order your results by you can go ahead and hit go and check it out whenever we hit order by name it takes the results in orders by the name now whenever we're ordering um like letters it's going to order alphabetically and whenever we're ordering numbers it's going to order that what's it called alphabetically but by number I guess I don't know how to say that there we go BAM baby so by default it orders it from A to Z and we can change that later on I'll show you guys how to order in reverse alphabetical order or later on but now I want to mention this because now that we know how to order results you may be thinking okay what if I wanted to retrieve some information for example I want to retrieve the name and address from customers but I want it to order by the ID number can I do this even though the ID number isn't retrieved well yes you can this is perfectly legal results can be sorted by their ID even though we don't want the ID to be retrieved so let me go and check it out as you can see in our result set we get Noah or excuse me Bucky Noah Kelsey Cory Harry and at first this may look unsorted totally but if we browse the entire customer staple check out the IDS Bucky Noah Kelsey Cory Harry so this information is sorted blur by their ID number even though the ID number was not retrieved now I want to mention this another thing that people get confused a lot about is whenever they want to sort multiple columns now we already learn how to retrieve multiple columns for example we can retrieve the ID name and address of you know a table but what if we wanted to do something like this we wanted to retrieve multiple columns well let me just go ahead and show you guys an example because if I talk try to explain it without showing you guys an example it's gonna get kind of confusing so I just show you guys the example first select state city and name so this is the information we want from customers and we want to order by state in name so let me go ahead and run this in check it out well I'm trying to say is this at first the first criteria we give it to order by estate and so it takes in orders the states alphabetically are Kansas Alabama I don't know what these I'm not very good it's like Alabama I think California I know that one but check it out even though all these states are sorted alphabetically look what happens whenever we have one two three four five six California's so it's gonna say okay I order the states but how do you want me to order all of the people that are in California well whenever we have multiple states we say okay now that you've done that now the next criteria to sort by is the name of the people so says okay first I sorted by state and then within those states I'm gonna order the people in that state based on you know of course alphabetical order so basically states are arranged in order and then each person in that state are arranged alphabetically and we did this by giving it more than one order criteria first order by state and then order by name so again the rule for this is the same is whenever we're selecting multiple columns one less comma then column name so just remember that and you'll be good to go so anyways that is how you retrieve multiple columns and order by multiple columns so okay probably brain overload right now so I'm gonna let you guys take a break go get a glass of chocolate milk come back and watch the next video but for now thank you guys for watching and we're gonna subscribe and yeah I'll see you next time and don't forget if you have any questions just ask me I'm reformed alright guys now that we know how to sort that I want to teach you guys how to sort that in reverse order so of course like before we can have something like select name and zip from customers and we can order by zip and now by default what this does is it basically sorts it from low to high whether it's alphabetical or numerical it arranges the ZIP code in this case from low to high but say we wanted to arrange this alphabetically from Z to a or numerically from the high numbers to the low numbers well in order to do that we need to add a special keyword at the end called I see now this is short it's the keyword that shorts for descending by default everything is sorted ascending now remember if you can't remember ascending from descending a lot of people don't remember it like a mountain whenever you ascend a mountain you're going from low to high whenever you're descending a mountain you're going from high to low you're going down the mountain so descending means high to low so basically we're saying okay sort by the zip code but go from high to low and now whenever we do that as you can see our data is now sorted by zip code the high zip codes first and it goes all the way all the way to the lowest and another thing is whenever we do this alphabetically for example if we wanted to sort name descending instead of 8 to Z it would go from Z to a now of course the first name V T s R so of course this is reverse alphabetical order descending as high to low now I want to mention this you can go ahead and use ASC which basically means ascending but a lot of people don't do that since MySQL default sorts it as ascending there's no need to explicitly write ASC so there you go that's a little tip now another thing I want to talk about is a lot of people are wondering how can I just extract or extract the highest or lowest value well in order to do this probably the easiest way is to select let me go ahead and give you guys an example so that name ID from customers order by ID and like I said say we wanted to get the I don't know the highest value of the ID number the customer with the highest ID number what we need to do is first we would need to sort it from highest to lowest so remember des see basically sorts it numerically from the biggest numbers first to the lowest numbers and then we would just go ahead and hit limit 1 so at first we're saying ok sort this information by ID number 4 highest ID number two lowest ID number but only return one result so basically returns the very first result which would be the highest number so as we can see Lucy Bronson has the highest ID number of 96 now let me just go ahead and show you guys one more example say we wanted the oh crap I messed up can I get my query again oh crap I can't say we wanted I don't know the person with the lowest name we would go ahead and select name from customers limit 1 now like I said whenever we want the lowest value there's no explicit need to write a SC because that's the default sorting ascending low to high so whenever we would sort my name and I forgot to put that cause in order by name and go ahead and hit go obviously Alexander has the very first name if we were to sort this by dictionary sorting or alphabetical sorting because by default it sorts ascending order so there you go there you have it there's your little tip shortcuts and all the information you need whenever it comes to sorting and in the next tutorials I'm gonna show you guys how to filter data and basically showing you guys a whole bunch of stuff so anyways thank you guys for watching don't forget to subscribe and I'll see you guys in the next video alright guys welcome to your tenth my SQL tutorial in in this tutorial we're gonna learn about the basics of filtering now we already learned kind of the basics of filtering because we can now limit our data well that's nice and all but sometimes we just want to be able to filter it a little bit more so let me go ahead and show you guys a quick example of say if we want to select the ID and the name of our customers so of course customers but we only want to select the person with the ID number of 54 so we go ahead and write where ID equals 54 so basically what this where Clause is is basically saying okay we don't want all the data we only want the data to be filtered and after you give it kind of a condition the first condition is just this equal sign you write the name of the column and then you write what you want the results to be equal to and since look I just bit my freaking tongue since there's only one person with the ID of 54 it returns one result the person with ID and name ID being 54 now I told you guys that there were several different conditions one of those being the equal sign there's also the not equal sign so if you wanted to say something like with the ID not equal to 54 you can go ahead and do that and another thing you can go ahead and write a space between the equals and the values and the columns you can either have it like this or excuse me accidentally deleted the wrong thing like this or you can have a space I usually switch it up a bit depending on what I'm doing but I just want to mention that again it goes back to that white space rule so whenever I were to run this test it would return all the results however if we take a look you see that 54 is excluded from the result set that's because we said okay give me all the results where the ID is not equal to 54 now there are a couple other tests that I'm going to just go and talk to you guys about real quick you can also have the less than sign so if you say okay where ID is less than 8 or something like that go ahead and hit go and now you can see there are 7 people 1 2 3 4 5 6 7 who have an ID of less than 8 there's also a variation of this it's a less than equals and what this means is you know how this result set ended at 7 if we go ahead and run this query right here as you can see it includes 8 so less than or equal signs means is it less than or equal to the value of 8 and that is what I want to be in another thing I'm not going to show you guys a test but there's also the greater than or greater than equals 2 I don't think I need to show you guys that test because it's pretty self-explanatory however there is one other test that I want to show you guys and that is the between test so where ID is between and you give it two values the first value I'll say is 25 and the second value is third you may be thinking that you may use a comma for this bout you actually need another keyword called and so this is kind of a weird test it's not like an equal sign or a less than sign you actually need some key words and what this is going to do is it's going to return all the data where the ID or this value is between this in this so remember whenever you use the key word between you need to values using the + sign so of course 2530 there you go just as expecting now the last thing I want to touch on on this tutorial is so far we only worked with ID which is numbers but what happens whenever we want to work with text or characters well let me go ahead and select the name and the state from customers where state you can't go ahead and write something like this where state is equal to California because if you do it's gonna think that California is either you know a MySQL key word that it doesn't understand or maybe a name of a table or a column so whenever you are working with where clauses and the value is text or letters you need to surround it with single quotation marks now whenever we run this notice that CA is surrounded by single quotation marks and you go ahead and run this it returns it basically whenever you surround something with quotation marks it treats it as value instead of a keyword or a column name so now as you can see it returns all the rows where the state is equal to California pretty cool huh so there you go there you have it hopefully you guys understand the beginning about filtering data and all the different tests that you can do not much to it but uh there's a little bit more that we haven't touched on yet some advanced filtering and we're going to be covering then the next sorrell so thank you guys for watching I'm gonna subscribe and I'll see you guys then alright guys welcome to your eleventh MySQL tutorial and in this lesson what we're gonna be doing is we're gonna be taking a look at some advanced filtering using the and keyword and the or keyword so let's go ahead and make a simple statement say select the name state and we'll also select the city from customers where and say we want to do this we want to select the customers that live in California and they also must live in Hollywood because there are a lot of different places to live in California but we want to say okay if you live in California and furthermore you live in Hollywood those are the customers you want so let's go ahead and put state equals and remember whenever we're working with text we need to put it in between quotation marks and we also want the city to be Hollywood hopefully I spelled that right Hollywood so basically what we can do is we already know that we can filter the results using a where statement and then making a test but if we want both tests to be true then we need to add a keyword called and and that only filters the results where this condition and this condition are true so let's go ahead and we just copy this run it and check it out so not only does Jack Nicholson live in California but he also lives in Hollywood now the rest of the database of course other people live in California but since they don't also live in Hollywood then that also isn't true and what if there is another Hollywood somewhere else like I don't know Idaho or maybe there's a Hollywood in New York well that also wouldn't be true because those people didn't live in California so basically just remember this it's basically the same is you know plain English whenever you say and both the first and the second thing must be true so let's go ahead and take it take a look at the keyword or now if we go ahead and select name state and city from customers where I'll do something like City equals Boston or I might as well stick with California state equals CA then remember I said last time whenever use and then this test and this test must be true whenever we run this query and we go ahead and hit go and then check it out either the first test or the second test need to be true so they don't both need to be true just one or the other so that's the difference between an and or whenever you use and all of the tests need to be true whenever you use or only one of them need to be true so of course you're saying okay why do we get this California but it isn't Boston it's because as long as someone lives in California or Boston it's gonna give us the results and what if were you saying okay what if someone lived in Boston California even though that is not in place well that still would return the results because it's either one of them it's basically like this whenever this one's true or this one's true or if they're both true then whenever you're using or then it's going to return that result now the last thing I want to touch on is this sometimes you're gonna get kind of weird queries or orders maybe from your boss or teacher let me go ahead and type you guys something weird that I'm gonna just type it because if I try to explain it it's gonna be really hard to explain okay select and let me think of something ID name and city from customers where ID equals one we don't need parentheses for that or ID equals two so that makes sense and city equals Raleigh okay so what maybe your boss comes in and says okay we want to find someone whose ID is one or two and they live in Raleigh so for example if my ID was one or two and I lived in Raleigh then that's what we would be looking for so let me go ahead and read this and then it's gonna say okay my ID is one or two but what's going on here Bucky Roberts doesn't live in Raleigh so why is that selecting Bucky Roberts because what we were trying to say is okay make sure that the person has an ID of one or two which was true and they must live in Raleigh so if we scroll down here saying okay something right around here because of course Bucky Roberts has the idea of one but we said and they must live in Raleigh and that wasn't the case so what's going on is this whenever my SQL is going to read this it's gonna read it in left-to-right fashion so it's gonna say okay basically it's gonna say anyone with the ID of one or anyone with the ID of two that lives in Raleigh okay but that's not what we wanted we wanted someone with the ID of one or two and someone who lives in Raleigh so basically it gets kind of confusing but I'm gonna show you guys what we wanted and what MySQL was looking at we wanted something like this we wanted someone with the ID of one or two and we also wanted them to live in Raleigh but whenever MySQL read this it said okay I wanted someone with the ID of one or someone with ID of two and City of Raleigh so that's another thing I want to point out whenever you have something complicated it use multiple or or and statements make sure that use you you use parentheses because let me go ahead and put parentheses around this so now when we say okay minus QL this is how we meant it sorry for confusing you I want someone with ID of one or two okay you got that and they must also live in Raleigh so since Bucky doesn't live in Raleigh he would be excluded from this result so now when I use parentheses and go ahead and run this query we get a different result Noah is the only person with the ID of either one or two and he also lives in Raleigh so again one last time whenever you have multiple or and ant statements please make sure to use parentheses and this pretty much is very important whenever you're using we're with the filter so that's all I want to point out um thank you guys for watching don't forget to subscribe and I'll see you guys in the next lesson alright guys welcome to big and number 12 and in this lesson where we're gonna be talking about is the in and not in statements so say we were working on this awesome database and the police come storming in the door in there like Bucky we just caught the murder and I'm like what murderer who the heck murdered anyone he needs like don't worry about it anyways they're in your database and we know that they either live in California North Carolina or New York can you give us a list of all those suspects so you were like heck yeah select name and state from customers we're and we're like hold on we typed that wrong I'm gonna have to type that again we're state equals and we're like what's your list because we forgot California or state equals North Carolina or state equals New York and we're just gonna go ahead and say okay just go ahead and let me run this query and bam and there is your list it he's like yeah okay oh oh wait wait wait hold on did you just say something sorry I fell asleep that query took way too long to type and check it out we only have three options here but if we were to have more states then look at this query it's gonna get very messy and you know with three options that's all it took but what if we wanted you know to include ten or fifteen states then things would get really complicated so I'm gonna show you guys a shortcut whenever you need to use a bunch of or statements you can just use the statement in and it's going to make it a whole lot easier so I'm just going to go ahead and hit select name and I think we saw the state from customers where State now the very first thing you do is you give it the condition to search for I want to search for a bunch of different states so I want to search first state in now right after in go ahead and type parentheses and instead of making a whore did I just say whore I am sorry I apologized all the slutty moms out there shouldn't have said that that's why you know people don't let my tutorials be washed in college or high school or anything because sometimes I just say inappropriate things like that but anyways after in we're gonna give a huge list for example CA or what was my next one and see and I think it was New York if I'm correct or NY now that's basically all we do so as you can see instead of making three different or or statements we just go ahead and say okay where the state is in California North Carolina or New York and then we just go ahead and hit go and check it out exact same results except we didn't have to use all of those or statements now typically whenever you are searching for a bunch of different things in the list then you add a condition just so order it at the end it just makes it organize a little bit better so we would probably want to order this by state and now check it out this is just gonna make it a little bit prettier so as you can see instead of saying okay search for all the people who live in California North Carolina or New York even though we're sorta khaleesi for three different or tests we can just go ahead and make one test with a list of all the states and it makes it a whole lot easier now say that the police was like ah you know what in fact I figured out that the suspect the one we were trying to catch he actually does not live in these three states he must live in the other 47 states so can you give me a list of all the suspects who live in the other 47 states and you may be thinking oh crap so before we learned about this nice little n thing we might have to make a huge or that says or Arkansas or Alabama or and we would have to list 47 or statements however check this out if we want to say okay search for all the people who do not live in these three states all we have to do is do this guys this is probably the coolest thing I ever taught you not in California North Carolina or New York how awesome is that so isn't that a whole lot easier than making 47 or statements basically whenever we want to search for customers who do not live in any of these states then instead of in where would be the opposite they would have to live in these states just go ahead and type not now you're gonna see this not not only when you were working with lists but whenever you put not before any testing condition it pretty much reverses the test so we could have something well I show you guys test later on but now let me just go ahead and run this query real quick and check it out we now get a list of all the customers who do not live in these three states in other words we get a list of all the customers who live in the other 47 states so first of all that police officer isn't doing a very good job because you know there's a lot of people left who live in 47 states so you know they need to kind of brush up on the work a little bit but anyways what we are worried about are these queries so basically like I said whenever you want to use a bunch of or statements you can just use in and then type a list and parentheses instead and it does the exact same thing it just makes it a whole lot easier or whenever you want to exclude a list or use something like not in just go ahead and type the keyword not in and then make sure to put all your options in a list in guys another key thing make sure not to forget the parentheses because it's very important so there you go there you have it that is what in and not in does basically takes a bunch of or statement and clean some up make some a whole lot easier so thank you guys for watching don't forget subscribe and I'll see you guys in the next video what is going on guys welcome to your 13th minus QL tutorial and in this tutorial I want to talk to you guys about something called a wild cards now wild cards are basically special symbols that you could use to make your filtering more advanced it's also what search engines use whenever they use MySQL to kind of like I said make your search more smart more advanced because whenever you type a dog or something in Google it just doesn't give you the results of dog it gives you dog house dog bones dog food and like I said this is I don't know I'm you know I'm just gonna shut up and give you guys an example but before I do I want to mention this that customer table was you know it was pretty cool for a few examples but it could get a little stale after a while so what I did is I pretended that we would make a website like eBay or Amazon comm and I made a whole list of pretty much a bunch of items now each of these items have an ID which is just one two three four five just a primary key so we can identify it a name such as brand new I might computer or you know a bed a stove whatever actually had someone else fill these in a cost which is the price of it a seller ID which is whoever is selling this item that's their ID number not very important for this tutorial and the number of bids on this item so this would be like a website like eBay and again if you guys want this table want to practice follow along with me then just go ahead and download that file again and this items table is going to be there whenever you import your file so anyways with that long intro let me go ahead and show you guys a quick wild card example now let's go ahead and say we wanted to search for items that started with the keyword new like a new dog or a new computer so what we do is write select name because remember name is the name of the items like you know carpet or brand new iMac computer select name from items items is a table remember where the name of the item is alike now whenever you use like you can go ahead and use something like this new % now this % is pretty much the heart of this tutorial this is the wild card and there's also another underscore wild card and I'll be talking that about that in a second but what % means is basically it means anything so if something starts with the new such as new computer that % could be a computer if it's new house new computer or I already said computer new sweater new pair of jeans this wild card can mean anything so let me go ahead and run this query and see what we get ok new gym socks new iPads stolen from Best Buy new curtain from bedroom it even can be a word or text after it so like I said that % can either have spaces basically we're saying if anything starts with the word new then go ahead and return that in the result set pretty cool huh so you may be thinking okay if I go ahead and look at this items table I see brand new iMac computer how come that was in the result set well that is because whenever we are in our query we said ok this is like saying it needs to start with the word new and then anything after it doesn't matter what's after it so what if we wanted to you know maybe search for the word computer but we didn't want to search like computer desk computer chair we wanted something like brand new computer chair or something so it doesn't matter what was before it or after it well what we would need to do is put two percent signs one before the word one after it so now when we search your computer then it doesn't matter what is before or after a computer as long as computer is somewhere in that name then check it out a brand new iMac computer this is at the end awesome alien computer game this is in the middle super computer at the end with no space in computer at the beginning or the end depending on how you look at it so basically remember whenever you use this percent as a wildcard filter it means that do you want anything to come before it or after it and if you do just go ahead and put it wherever you want now there's one other way that you can use that wildcard filter and is having something weird like putting it in the middle of your term now whenever you put it in the middle of your term then you must match the first and last character but the middle can be anything you want so let me go ahead and select the city from I'm gonna use the old table customers in or remember that's the table that we've been using where the city name is like and instead of using it instead of using that wildcard before or after I'm gonna use it in the middle so any city that starts with H and ends with D it can be anything it can be any name no matter what the length as long as it starts with H and ends with D then we're gonna go ahead and get those results so let me go ahead and hit go and check it out Hollywood Highland it doesn't matter the only thing that matters is it starts with H and ends with D what's in between those two doesn't really matter and I want to point out one other thing as you notice I put a lowercase H here but it's searched uppercase H and that's because like I said my SQ o is not case-sensitive the only reason that we like to use uppercase from lower case is it just makes it easier to read whenever we're programming so we can quickly see what's MySQL and what are basically whatever we created so even though we're searching for H D this can be uppercase or lowercase like I said my SQL is not case-sensitive so that's why we got the results Hollywood and Highland so there are other wildcards that I want to talk about and I also want to talk about something called regular expressions but I don't have time in this tutorial so I guess you guys are just gonna have to wait to the next video so thank guys for watching don't forget subscribe and I'll see you guys then what is going on guys welcome to your fourteenth MySQL tutorial and in this us and we're going to be talking more about wildcards so we already know that the percent sign is a wildcard that can pretty much mean anything it can mean any number of characters whether it's a space whether it's nothing whether it's a whole bunch of text it can mean anything at all but what if we want to say okay we want a wild card that can only match one character well lucky for you there is a wild card for that and I'll show you guys what I'm talking about in a second so let's go ahead and select the name of item from the table items of course where the name of the item is like now instead of using that % wild card I'm gonna use an underscore or wild card now I happen to know this site sells boxes of frogs frogs I say yes frogs so let me go ahead and run this query and see what we get three boxes of frogs seven boxes of frogs now if we go ahead and look at the items table and scroll down the user that selling frogs we can see that there indeed selling 48 boxes of frogs as well so how come forty-eight boxes of frogs didn't pop up in only three and seven well that is because in our query we used the underscore character now unlike the percent sign this underscore character means only a single character so therefore if there are more than one characters that can fit this place for example 48 that's more than one character and we were asking for only a single character that could fit in this place so that's why what was it like forty three boxes of frogs or something forty eight boxes frogs and that's why that didn't pop up but three and seven did make sense now what if we said okay but what if we did want to include that forty boxes of frogs well if we did want to include that then we would need to put their % instead of the underscore so again whenever we use the percent sign and make our query then it's going to include everything it doesn't only have to be one it doesn't only have to be two characters but it could be a hundred a thousand it could be like fifty seven characters if it wants again like I said the percent sign means anything and the underscore means one character and just like the percent sign you can use it in the middle at the end just like we would % but I don't want to go through all those examples because they're pretty self-explanatory so again whenever you need to use wildcards for example if you're making a website like eBay or Amazon and also if you were ever and make something like search engine like Google or just a simple search engine for your site you can use the wildcards but sometimes the % in underscore wildcards just aren't enough and when that's the case we need to use something called regular expressions and in the next tutorial we're gonna be learning about regular expressions but for this tutorial well that's all you get so we're done with wildcards in the next tutorial regular expressions I'll see you then alright guys welcome to your 15th tutorial in MySQL and in this lesson I want to talk to you guys about something called regular expressions now regular expressions is basically something that's built into MySQL and it's a way that you can search for more complex patterns other than just using the wildcards % and underscore I meant to type those % and underscore so it's kind of like a language all on its own and it's actually incorporated not only into MySQL but a bunch of different other programming languages like PHP and JavaScript I believe so learning regular expressions on its own is a whole nother beast but for this tutorial I just want to tell you guys how to incorporate it into MySQL so let me go ahead and let me go and select the name of item from the table items where the name and instead of hitting like I'm going to type our EG e XP that means that we want to work with regular expressions now in single quotation marks you go ahead and you type your regular expression now the easiest regular expression is basically just a word for example knew what this regular expression means just a symbol single word is basically saying search for anything that contains the word new so whenever we hit go as you can see it doesn't matter if it appears at the beginning at in the middle or as part of another word as long as it contains the word new then there you go so of course we can achieve this just by using like in some wild cards but this is how you incorporate regular expressions now another thing that may come in handy is the dot or the period whatever you want to call it now the period means any single character so if you were to write something like doc boxes then if any single character came before boxes in your term then that would go ahead and hit any results so for example a space came before all these and before that a character excuse me a number so that's why dot boxes matched all of these terms right here so of course we could also do this now there are some other regular expressions and I want to talk to you guys about a few other ones the next regular expression I want to talk to you guys about is the pipe I forgot what it's called technically the pipe line the pipe symbol whatever is it's the little thing above the enter on your keyboard when you hit shift it gives you a straight up-and-down this is basically the same thing as or in other words buh are and basically when I'm saying it's the same thing as or you can go ahead and hit two words on either end of this pipe sign for example gold pipe sign car and what this is saying is basically this search for any item that either includes the word gold or the word car it doesn't have to include gold car or anything like that just one or the other so basically the pipe sign is the same thing as or so let me just go ahead and take a look at our results traditional carpet now even though this isn't like a traditional car in car is separate as long as it includes the word car or gold somewhere in there so basically it's like using parentheses on either side of gold or car it just may be a little quicker whenever you're coding so again as you are seeing already regular expressions are just using symbols in really weird ways now the last regular expression I want to talk guys about is something called a set now a set is basically like a list or a bunch of different or statements now I want to show you guys this because it can save you guys a lot of time instead of having to type a whole bunch of different or statements now if you were let's say you wanted to match a bunch of specific characters like for example one two three four or five let's say we wanted to find one boxes of frogs two boxes frogs three boxes frogs four boxes frogs well we can do that using a bunch of different my SQL statements or we can use a set in regular expressions now anytime you want to use a set make sure to include it in square brackets and then type all of the different terms you want to search for now make sure not to include commas in between them it's not like a list in MySQL so don't forget that now go ahead and type boxes of frogs and what this statement is basically saying is okay search for one boxes of frogs two boxes the frogs three boxes four boxes or five boxes of frogs so it's basically five different MySQL or statements in one so now I'm just go ahead and copy that and run and as we can see it returns three boxes of frogs now we also have seven boxes in 48 boxes but those that are returned because they weren't in the set now another cool thing that you can do real quick is you can negate the set and then negate a set means basically okay search for anything except this list so basically we're saying okay search for any boxes of frogs but since we included that little caret right there and this is the symbol above the six on your keyboard we're saying okay search for any number boxes of frogs besides one two three four and five so now whenever we hit go that is when we get 48 and seven and again this in the gate symbol that can be used with other regular expressions to not only sets again regular expressions are pretty much taking all the symbols on your keyboard that you thought you would never use and putting them to use I think I think that's what the creator of regular expressions wanted to do he's like wow since all of these crap you know all this crap I don't use I'm gonna put a use to it and that's how regular expressions was probably invented and oh yeah I wanted teach you guys one more thing you know how I said that you can include a set like one two three four or five anytime you want a set not only of numbers like one through five but you can use this with the letters like a to Z you can go ahead and just write one minus five like you're writing one through five and that's just a little shortcut instead of you know having to write the whole set so whenever you want to do something two three four five six seven eight nine you can just do two to nine and that's the same thing so if we go ahead and search this since three is in the range of one to five that's just a little shortcut I thought to tell you guys real quick so again like I said there are a ton more regular expressions it's like it's own language made up of special characters and it really for me to cover all of the regular expressions would be a waste of time because regular expressions are really outside the scope of these tutorials they're just incorporated into MySQL so that's why I wanted to show you guys this story real quick because whenever you come across them this is how you put them and incorporate them into - grow so again I encourage you guys to go online look at the list of are the regular expressions and then once you understand them learn how to use them this is how you use them in MySQL but for now thank you guys for watching how to use regular expressions in MySQL and yeah I'll see you guys in the next video oh by the way don't forget to add me on Google+ alright guys welcome to your 16th MySQL tutorial and in this lesson I want to talk to you guys about creating custom columns now create them custom columns wow that's kind of like a tongue twister creating custom columns is something that you can do and it's going to come in handy a whole lot for example say that you were working with your customers table and your boss wanted to send out a letter to all your customers so of course in order to address a letter at least in the United States you write the name the address and under that you write the city and state but in order to write the city and state you typically write like this instead of writing writing the city and state like that you write the city with a comma and then the state after however if we go ahead and we look at our customers table there's no way that we can achieve this because the city and stay are two different things and the comma doesn't come after the city name so your boss comes in and he's like lucky I need a column that has City comma state and we're like okay well these are the only columns we have to work with what do you want us to do lucky for you you have been watching my tutorials and you know that there is a way that you can temporarily make your own custom columns so let me go ahead and use a select statement but instead of selecting city and state I'm gonna teach you guys about a simple function now I'll go more I'll go on more about functions later on but for now go ahead and type Co n CA T this is short for concatenate and it's basically a job that MySQL can do that it takes a list of items and it combines those items now the first item we want to combine is city now of course whenever you're using lists just separate it with commas now the second item we want to combine is just a string of text we want to include a comma because remember and this isn't MySQL this is just an example atoms comma and why would be an example of where I live so in between the city and state I want a comma and a space so any time you were just including raw text go ahead and put it in between the single quotation marks so a comma and space is the next thing I want and then the last thing in my list of course I need a comma to separate my list is the state so again city comment space state now those three things need to be separated with a comma so now that I'm gonna go ahead and select that column and again this is a column that I'm gonna make up from customers I can go ahead and run this and check it out I now created a new custom column that is made up of the city name with a comma in a space after it and then the state so Adams is a steady instead I say steady with the XS steady Adams comma space New York Gary come space Indiana I think that is Phoenix comma space Arizona so that is how you can use the concat to pretty much like I said concat is short for concatenate which means join things together so it takes a bunch of items as a list and it joins them together you can either join together two columns that already exist or whenever you want to use text like maybe a comma or maybe you want to use a hyphen or a plus sign you need to include that in between single quotation marks now you're saying okay well I know PHP or some other program already or maybe Java and in order to reference this column it needs to have a name so what is the name of this column is it city no is it state no is it concat city comma state comma whatever no in order to give this new column a name because check it out whenever you make a table every column has a name in order to name your new column you need to specify that whenever you're writing your query so here's the query ahead before select this column the custom column we made up from customers however it doesn't have a name yet so in order to name it go ahead and say select that as and after the word as you give a name such as new underscore address now whenever you select this column it's gonna have the name new address so select my custom column as the name new address now I just go ahead and I messed that up so let me go ahead and run this and check it out now we have our custom column formatted exactly how we wanted it to be formatted and it now has the name new address so now whenever we are working with the program like PHP that needs to know the name of this it knows whenever uses the name new address to use this information it's pretty much like a nickname that you can give your column so whenever you're programming you can refer to it now the last thing I want to teach you guys about whenever used in custom columns is how to use mathematical operations so say for example you were having I don't know like say we had this website and this was like eBay and you were having a sale of all your items where you were selling them for $1 off so instead of 204 this would be 104 and instead of one $49.99 this would be 148 99 well in order to you know if we don't want to ruin this column already let's just go ahead and make an additional custom column with the new price so let's go ahead hop over to where we can type in our queries and let me show you it beforehand first select the name and the cost from items now what this does is it goes head and gives us the name of the item and the cost of the item so now let's go ahead and add an additional custom column on here and we will call it sale price or something so we want to select the name and cost and just like before but now we want to go ahead and select cost minus 1 now what this is going to do is it's going to create a new column that basically takes the price or the cost of the item and subtracts 1 from it so our first column is going to be 148 99 the second one is going to be 104 the third one's gonna be 13 99 so on and so forth however just like before this third column is a custom column therefore it doesn't have a name yet so in order to name it we need to use as and then we give a name like sale price or something clever like that so now it creates a third custom column whenever you run this query and it treats it it gives it the nickname sale price so whenever we were to code this with our you know whenever we were programming our new improved eBay we can use the sale price column and we still retain our important information now a couple things you may want to take note of computers have a weird way whenever they perform math on well just whenever they perform math because they don't perform math like a human and they make these little rounding errors and I'll talk about more on that later on but we're gonna learn how to format this properly for example drop all these unneeded decimal points and I'll explain to you guys why computers can't perform math in the way that mean you can later on but for now that isn't the you know that's not I don't want to get into all that right now I just want to go ahead and talk about custom columns and how to nickname your custom columns so the last thing I want to talk about is you know how I use - as a simple mathematical operation you can also use plus and whenever you want to multiply you use the asterisks the symbol above the eight and that means multiply it by one and divide is the forward slash so again plus - asterisks four multiply and forward slash four divide so you know don't look for like a division key on your keyboard or something because it doesn't exist but anyways I bet your brains are jam-packed full of information so please go take a break go do something else go watch some TV or something in when you're ready to finally come back to these tutorials because I know it's a lot then you're welcome back so anyways thank you guys for watching don't forget to subscribe don't forget to add me on Google+ and I will see you guys in the next video alright guys welcome to tutorial number 17 and in this lesson what we're gonna do is I'm gonna talk more about functions because in the last tutorial what we did is we took a look at the concat function which basically took a list of items and put them all together but now I want to talk a little bit more about functions and the different type of functions so basically what a function is is it's a name that does something for example concat does something to a list of items aka string it together but other functions have different names so let's go ahead and take a look at some simple examples first so I'm gonna go ahead and select the name of a customer and I'm gonna also select another custom column what I'm gonna do is I'm gonna use the function called upper now again like I said after every function they always have parenthesis and in between the parentheses is when you put the extra information now this information differs from functional function for example upper just takes the name of a column concat takes a list it really depends on the function what you have to put inside those parentheses but what this function is going to do is it basically takes to this column and it changes it all to uppercase letters so you just go ahead and run this query and then you'll see what I'm talking about from customers so given the job of concat was the tie things together the job of upper is just the upper case everything and each different function has a different job so that is how you can think of functions just jobs so as you can see here is the original name just standard format and whenever you apply the upper function to the column name it changes everything to upper so using functions is actually really simple just go ahead and write the function name and then put whatever information you need to write in between the parentheses now another cool thing is we already saw concat and upper those are called string functions basically they're functions that you can apply to characters or a text but you can also use a numeric functions for example get the absolute value of numbers get the square root of numbers let me go ahead and show you guys an example select cost and I'll also make my second column to find the square root of cost so square root is the name of the function and cost and by the way I'm not going to show you all the functions because once you learn how to you know use functions in a very basic fashion then it gets kind of you know repetitive so instead of showing you all the functions I'm just going to show you the basics and if you want a list then go on the MySQL website or probably a bunch of different websites have the list of functions by anyways in the first column it's going to be the original cost of the item and in the second column I'm just going to get the square root of that cost from a now to be honest I would have no idea when you would ever want the square root of something how much something cost because you never go into a grocery store see box of cereal that's like five dollars bike hmm I need you to know the square root of this you know but this is just an example so basically as you can see the first column is the cost 14 - or excuse me 150 - 15 bucks and the second column is the square root of that number so the square root of 149.99 is 1224 so on and so forth so that is how you can use very simple basic functions both character functions and numeric functions however since I have time I might as well show you guys this there is another type of function and that's called an aggregate function you see what these functions did is they basically changed the data in some way so you ended up with an entire column of information so the original column was this and then your new column was the exact same length for exhibit in this example each one was like 30 rows long well what an aggregate function does is it takes an entire column of information and it gives you one answer for example let me go ahead and I would say I want the average of this column so what it would do is it would add up all of these numbers and give you the average and of course the average isn't 30 columns long it's only one answer so say we wanted to add all of the items together the price of all the items and figure out the average cost of all of the items on my website in order to do that just go ahead and select average is the aggregate function so this is only gonna give you one number as a result not a whole list the average cost of all the items so let me go ahead and run this and check it out instead of giving you a huge list of numbers it just adds up all of the prices of all the items on your website and the average cost of all of them is 463 dollars there you go but what if you wanted to figure out something like how many bids are on all of the items well what we could do is we could hop over to items and say okay bids 3 plus 32 is 35 + 566 is I've got to go ahead and get my graphing calculator or what we could do is we could just use an aggregate function so in order to do this the aggregate function for this is sum what sum does is it takes a column of bids and that adds up pretty much adds them all up figures out the sum of all of the bids so let me just finish this query from items and check it out there were ten thousand nine hundred and thirty nine bids made on all of the items on my website pretty sweet huh so in the next tutorial what I'm gonna do is I'm gonna talk to you guys about the different aggregate functions and also show you guys some other cool examples and how to run several aggregate functions at once so there's a lot more to cover with functions and aggregate functions so sorry but you aren't done yet so thank you guys for watching don't forget subscribe and I'll see you guys in the next video alright guys welcome to lesson number 18 and now that we understand functions and aggregate functions I want to show you guys how you are typically gonna use them in a useful manner because if we go ahead and we look at like the items table yeah we can go ahead and figure out the average of all of these or we can add up all the bids but that isn't really useful I mean it's kind of a neat bit of neat piece of information but that's not something we would actually use when we were making a website like eBay what we would typically do is we would want to find out not the price of all of the items on the website but maybe the average price of a single sellers items or maybe how many items did I don't know user number eleven half for sale one two three so let me go ahead and show you guys how to make aggregate functions for a single person or for a single parameter so let's go ahead and let me think of a real-life problem say we wanted to figure out how many items seller number six was selling and by the way seller ID these are just like different sellers so let's say that my ID number is number six and I'm selling a microwave 48 boxes of frogs in shampooing which I had someone else fill this out but I guess I'm selling shampoo maybe it's the brand shampooing I don't know but anyways I'm selling three different items so let's go ahead and figure out how to calculate that so select now the next aggregate function I want to talk to you guys about is called count what count does is you give a parameter and that counts all of the rows in the column for example if you were just to count the name in from items it would give you like a hundred items because that's how many items are listed on that website however if you only wanted to count my items you would add a clause after this for example where seller underscore ID equals six so remember my seller ID is equal to 6 so this is only gonna say okay count the number of items where I am the seller so now if we go ahead and hit go check it out now it comes up with that answer three items because what was i selling oh crap I forgot already I remember two of them like a box of frogs in shampoo what's the other one oh yeah a microwave box of frogs and shampoo so basically you could go ahead and count anything you can go ahead and count the name or count the cost it doesn't matter what you count but as long as you have that clause let's see if I nope copy the wrong thing so as long as you have that clause count rows from the items where seller ID equals six now what if you wanted to say okay I wonder what the average price of all of my items are well we can do that as well select average cost from items now if we run this it's gonna go ahead and figure out the average cost for all of the items on the entire website but I only want to get the average cost of my items so what we do is we would add a where clause seller underscore ID equals six now we go ahead and run that and check it out the average price of my items my microwave box of frogs and my shampoo if you add them together and get the average it's around 80 bucks pretty cool huh so that is you know a more useful piece of information maybe you wanted to give everyone their own profile and at the top you would have their statistics you would run a query like that and show their statistics right at the top the web page so now that I got that taken care of I want to show you guys one more thing and that is how to run several aggregate functions at once now what we can do is we can just go ahead and run each thing then put a semicolon at the end run a query again put a semicolon at the end but I want to show you guys a shortcut in order to run several aggregate functions at once let's go ahead and tie everything we learned together so far and let's see let's say we were okay we know my items already I'm seller ID number six let's get another good seller sort by seller ID okay number twelve has a bunch of items a hairdryer or Playstation a used-car a bikini an air conditioner so I'm guessing that they live in Florida with those things but anyways that's what we're gonna be you know running our data on so first of all let's go ahead and count all of their items so in order to do that we can just go ahead and select count now again since the rows don't really matter or what column you want to count because any column it's gonna come up with the same number which is like five or six whatever it was so I'm just going to go ahead and hit count all as item underscore counts now remember whenever I hit as since we're creating our own custom columns what we're gonna do is I'm just going to name it item count in there for you later on it it's easier to see what because I'm gonna be doing a bunch of different jobs or functions and it's easier to see if I give each one a custom name what function I ran now another function I want to talk to you guys about is Max and min mi M whenever you run this max it's gonna get the highest value so if you had three different items one was $10 one was $20 one was $30 the max would be thirty in the min would be ten so again Max is the highest value of that list and min is the lowest value so let's figure out which item cost the most so max cost of course because we're working with price and we'll just name this as max so whenever we see it we'll just name it as Max that's what I just said just wanted to say it twice so now we already know average let's go ahead and get the average price of their items average cost and I'll just name this as average from items now again if we run this query right now what it's going to do is it's gonna get it's gonna count all of the items get the maximum cost and get the average cost for the entire website but I only want to figure out that the item count in the max in the average price where seller ID equals 12 so that's what I'm gonna do go ahead and run this and check it out so again seller 12 who is will say he's my friend named Tony he's selling five items his maximum item is selling for $5,700 that must have been the bikini and the average price for his items were fourteen hundred and twenty one and about a quarter so now if we go over in the items table and verify this we'll just make sure that the max check it out a used car for fifty seven hundred dollars and fifty cents so that is indeed has maximum priced item so there you go that is how you can run several aggregate functions at once again the easiest way is just to put each on a new line in make sure to give it a nickname or name it ask something because whenever you're figuring it out later on it's easier to go back and look oh yeah that's the max or that's the average so that's your little tip and there you go so guys take a deep breath you are done with functions and aggregate functions thank you guys for watching don't forget subscribe and I will see you guys in the next video what is going on guys welcome to your 19th MySQL tutorial and I want to show you guys a nice little tip that's gonna save you guys a whole bunch of time in this tutorial so in the last tutorial what I did is I showed you guys how you could find a number of listings for each seller so let's go ahead and run an aggregate function for example if we wanted to find the number of listings for each seller we would go ahead and select seller underscore ID and we would also do something like count all and what this would do is count all their listings and I'm just gonna name this as item count from items now in order to get an individual seller what we would do is we would add a where clause for example where seller underscore ID equals one now what this line of code would do was basically get the seller ID and count their items where the seller ID was equal to one so let's go ahead and hit go and obviously seller ID is selling two items okay now we need to do this first seller number two seller number two let's go ahead and run this they are selling two items as well okay how many people do we have in this oh wow we have like a hundred different sellers okay let me go grab a book this is gonna take some time or is it what if there is the easier way that we could do this instead of using this where Clause what we could do is we could use a new little trick called group by and instead of having to type each individual seller let me just go ahead and show you this whenever you use group by just go ahead and type what you want to group by for example seller ID and what this is gonna do is it's gonna list the seller ID and the number of items for each seller ID in other words for each seller so again instead of having a bunch of different where clauses for each seller we can just go ahead and group them my seller ID and it's gonna do all of this automatically for us so let me go ahead and hit go and check this out how amazingly awesome is this again using group by we can see that seller one is selling two items the next seller has two items listed and let's see like this seller has five items listed the seller has four items listed it's a lot easier than having to type each seller individually pretty dang sweet huh now furthermore you can also filter the results of this for example let's say that we only wanted to list the users that have three or more items listed on our website we only wanted to list the power sellers so in order to do that go up to your query and of course we need to still group by seller ID and now instead of a where clause you use a keyword called Havoc now having is kind of like the wear of groups so having and then you write the test we want to list all sellers having account in other words having items listed that are greater or equal to I don't know let's just say greater equal to three so now not only is it gonna list all of the sellers and the number of items but it's only gonna list the ones that have a count higher than 3 and that kind of looks like the kind of reminds me is something I can't tell exactly what by anyways whenever we list this check it out now the only sellers that are listed are the ones that have an item count of 3 or higher 3 4 5 etc so now if you wanted to list like the top sellers for your website or something this is how you would do that again one last time having is basically it's similar to the where clause where you can give a test and can filter your results just remember this whenever you're using groups remember to use the keyword having whenever you're just using rows like a typical test use the where clause but having is for groups where is for just basic tests so the last thing I want to tell you guys in this story is you can also order by a group for example you see how we got the list of all the power sellers all of the sellers who listed three or more items well I want to arrange this a little bit prettier I want to arrange these sellers from highest to lowest the people who have the most items listed to the people who have the lowest items listed or the least items oh I guess I'm freaking Fleming my throat so in order to do that after this query we can just go ahead and add a simple order by statement now I want to mention this because a lot of people ask me about this you know how these are the columns that were originally in the table ID name cost seller ID and bids now we also have this extra column that we named as item count now this column is a temporarily temporary column it's kind of like a nickname a column so can we go ahead in order by that column even though it isn't a permanent column well yes we can whenever we order by item count it's gonna order by that temporary column in other words the number of items for our sellers now since we want to order from highest to lowest we can't leave it at default because default is ascending from low to high we want it from high to low descending just like that so basically let me go ahead and run this query and then I'll talk you guys through it as you can see and now list the sellers in order for the sellers that have the most items listed do the sellers that have the least items listed all the way down to three so basically even though this is a really long and probably confusing query at first glance I'm gonna tell you guys basically you can read it in plain English it's like it's saying this take a deep breath give me the cellar ID in the number of items from each seller who listed three or more items and arrange them from highest to lowest so again if you just read it in plain English it's gonna make a lot more sense and that's typically what I do whenever I come into a database for the first time that someone else was working on I just try to read it like playing English again each of these keywords not only the MySQL but also the column names the written in plain English English so therefore you can just say okay give me the seller ID and number of items from each seller who lists the three or more items and then once you have that information just arrange them from highest to lowest simple enough so hopefully you can understand how to read complex queries by converting that to plain English but anyways that's all I have for you guys for this story probably brain overload but anyways thank you guys for watching don't forget to subscribe and in the next tutorial I promise we're done with all this crap it's gonna get a whole lot easier so thank you guys for watching and I'll see you then alright guys welcome to xx my SQL tutorial congratulations on making it this far but I will have to say that you better be ready to get your mind blown in about one minute because that's what I'm about to do what I'm about to teach you guys is about sub queries now what a sub query is is a query inside another query inception so it sounds weird it is but it can come in useful a whole lot so let me go ahead and show you guys a really quick example first now the first example I want to show you guys I have to admit it's pretty useless in there's a lot easier way that you can do this but it's the easiest example I can possibly think of so say we wanted to do something like we have all of these items on our website our eBay website now what we want to do is we want to list the items whose price is above average so how do we solve this problem let's take this step at a time the first thing that we need to do is we need to add up the price of all of these items and get the average once we have the average price we can use that number to list all the items that are greater than that price for example if the average price of these items was $500 we would figure that out first and then once we figured out the average price is $500 we will list all the items greater than $500 it sounds simple well now you all righty guys welcome to your 21st - crowd sorrow and in this lesson I'm gonna give you guys another more confusing example of a sub-query however once I show you guys this example it should pound the idea of so queries through your head so hard that you couldn't forget it if you wanted to so in this problem what we're gonna be solving is we want to find out which seller has the cheapest frogs so of course we know that if we go over to our items table they're a bunch of different sellers was a bunch of different frogs but we want to find the best price on them so let's go ahead and hop over to where we type in our queries and in order to solve this problem again let's break it into a couple steps the first thing we need to do is find out what sellers are even selling frogs and then once we have a list of all the sellers that are selling frogs we need to figure out the cheapest of those in the list so again like I said the first thing we need to do is get a list of the sellers that are selling frogs so let's go ahead and select the seller ID which is basically the seller from the table items where the name and remember the name is just the name of the items is like and we'll just use that wildcard % boxes of frogs but you have to spell it correctly that's a key so let's go ahead and search and see what we get up with we ended up with 68 6 and 18 and those are the three sellers who are selling frogs now even though whenever we get our results in MySQL it presents the results is a table with the column 68 6 and 18 but not only is this a table but this is a list as well so therefore you know how I told you that in some functions like concat use a list and whenever you use the keyword in you use a list well you can also use this entire query right here I know what Rita is a list as well so basically whenever we use a list in MySQL we can just go ahead and plug in this query instead and therefore a query inside another query inception aka sub-queries but it's just so much funner to say inception so now let's go ahead and copy this entire query I already have it copied so basically we solved step number one we found out what sellers are selling frogs so hopefully you copied that query so the next thing we need to do is we need to get the cheapest price of frogs out of that list so let me go ahead and do that now so select the name of the item which will be however many boxes of frogs and the minimum cost from items where name and let me see how I can do this where a name is like boxes of frogs now we need to include this name because whenever we select the items how can I explain this whenever we select all the items from our list of sellers those sellers may be selling different items too but what we want is we only want the items who are boxes of frogs basically we're only in it for the frogs sorry to sound greedy but you know we only are and seller underscore ID is in now remember I told you guys whenever you use the keyword in in MySQL it takes a list now our list was 68 6 in 18 those were the three sellers so basically we're saying okay give me the name of the product in the minimum cost from basically all of the items on our website but the name has to be like boxes of frogs so basically they have to be boxes of frogs and the seller has to be one of these sellers well we already know that these sellers are the ones that are selling boxes of frogs so that should save our query a little bit of time now whenever we hit go we see that okay the cheapest boxes of frogs that we can buy is three boxes of frogs for 30 dollars and basically 50 Cent's however instead of having the type in this list every single time we need to add a sub-query in here because you know what what our users that are actually using the website are looking for information like we just did we're not going to be there every single second to monitor what they're doing on the website and whenever they find a list we can't type it in manually for them so in order to do something like this we need to add a sub query as a query in here so go ahead and copy that query that you have before and remember this is the very first query that you're in this is what returns 68 6 and 18 so remember from last time my SQL works from the inside out so not only whenever well I want to mention this in the last tutorial it figured out one value but it could also figure out a list of values as well so again the first thing my SQL is gonna do whenever it runs a query is it's gonna figure out what's in between the parentheses so it runs this query first and it gets the result of 68 6 and 18 so basically it runs this entire query but when it gets to this it's going to treat this query as literally the numerical value 68 6 and 18 so select a name and minimum cost from items where the name of the product is like boxes of frogs and the seller ID is in the list 68 6 and 18 so now let's go ahead and run this and we should get the exact same result so how awesome is that basically you plug in a list how can I say this take a deep breath basically instead of typing in a list manually we just plug in another query and it treats it as a list simple enough and I want to mention this I won't show you guys an example because if I do you're probably just gonna stop watching my tutorials but we can also run a query within a query within a query so you know how I put one query inside another query well we could also put another query inside that query and it would be like 3 layers deep if you ever watch the movie Inception you know exactly what I'm talking about by the way I really like the movie Inception I probably should to mention that but I won't show you guys an example of that because if I do your brains are probably going to explode and no one really does that anyways whenever they're doing complex queries like that like four or five sub queries deep it's easier just to use join tables and I'll show you guys that later on but anyways I'm gonna do you favor and I'm gonna shut up right now and I'm gonna be done with sub queries for now in the next tutorial we're probably going to learn about joining tables it's gonna be awesome so thank you guys for watching hopefully you understand sub queries and I'll see you in the next lesson alright guys welcome to your 22nd MySQL tutorial and in this lesson I'm gonna be talking to you guys about one of the coolest most important and also powerful features of MySQL and that is how to join together tables so let's go ahead and take a look at our tables we have so far we have a customers table which pretty much all the users of the website this is their personal information and also an items table because remember our website was kind of like eBay where the user can list an item and other people can bid on it so check it out what if our boss comes in and he's like Bucky these items you're doing a pretty good job on these you have the name of the item the cost of the item the number of bids that's a pretty good information but we also need the sellers information whenever you list the item in the table you need the sellers name and address and zip code and state therefore whenever anyone is bidding on your item you know then they know if they win it where to send the money to or whenever they win your item they can expect they know where the items coming from to calculate shipping cost so we say okay that's gonna be a problem so you're telling me that I have to redesign my entire items table to include the sellers name address zip code on each row well check this out first of all whenever you have a user that is selling like 20 items that means that we would have 20 different rows that say the same thing Bucky Roberts Bucky Roberts Bucky Roberts Bucky Roberts new york new york new york new york new york okay so that can get kind of repetitive in know that anytime we see repetitive information in MySQL that means we probably did something wrong aside from that say that I'm a user on this website and I have like I said 20 or 30 items listed now I'm looking at my address and I accidentally typed North Carolina instead of New York is my state so does that mean that I'm gonna have to go through every item and change my address from New York to North Carolina or vice versa well that can be a pain in the butt too so what can we do to solve this problem because we can't get away from the fact that we need the sellers information on each row well thankfully for you in MySQL you can do something called join tables together and whenever you do this you create a temporary table that pretty much grabs information from any table that you want it to it makes a temporary table with all the information you want so let me go ahead and show you guys how to do this real quick say our boss says ok I won't make you get all this information but you at least need the customers and guys the customer is pretty much like the user you need the users ID in the users name and you also need the items name that they're listing and the cost of the item so for each item you also need the users ID and the user's name and we say okay the only problem is some of the information is in our customers table and some of the information is in our items table so how the heck do we want to do that well check this out whenever I made this table I arranged it in very specific way and I did it for a very specific purpose in our items table we not only have the name of the item and the price of that item but we also have the ID number of who listed that item so for example the seller ID of this used diaper for my sister is 1 so therefore we know that in our customers table whoever has the ID of 1 that's who's selling the used diaper who happens to be me who lives at Hungerford Ave Adams in New York 1 3 605 so that way instead of having to type all the other information we can just reference their ID number and then you can hop over to the customers table and say hey the ID number is this person or the person who was selling that car is Cynthia because her I seller ID was six but how do we take this information and combine it into a new table well in order to do that I'll show you go ahead and use your select statement just like before and what you need to do is you need to type the name of the columns that you want to use from each table but check this out in customers we have a column named ID okay that's nice why are you telling me this well in items we also have a column name ID okay so that can get kind of confusing whenever you're typing in MySQL and you say select ID my SQL is gonna look at you in BEC hey what ID do you want me to select the customers ID or the items ID in order to do this we need to use fully qualified names so remember customers da ID means okay select the ID column from the customers table we also want to select the customers name from the or excuse me the name from the customers table because remember name exists both in items and customers so now what we wanted to do is we want to select two more columns our boss said items name and items cost so now we selected four columns that we want to make up our brand new table that our boss required so now we add a from and we say okay these are the columns now what tables is this information coming from the customers tables and the items tables now whenever you are making a join just go ahead and hit all or excuse me list all of the tables that you want to join and separate them with a comma but make sure not to put a comma after the last one now after this you need to add a where clause and this where Clause is basically saying okay how are these tables related well they're related because the customers ID is equal to the seller ID now by default my SQL doesn't know that the seller ID in items is actually the ID number of the customers so that's pretty much the heart of this query that's where we need to tell it how and where the tables are related they're related right here so now I'm just gonna add a simple order clause order by customers ID now let me just go ahead and copy this in case I have NER run this baby and check it out we now have a new custom table with all of the information that our boss required without having to edit our customers table or items table for each of the sellers we listed their ID number my ID number was number one the name of the sellers and these two bits of information are from the customers table now these last two columns of information are from the items table for each of the sellers we listed the items they're selling and also the cost of the item so now check this out instead of going into our items table and having to write Bucky Roberts Bucky Roberts for all my items and then in case we need to edit my name we don't need to edit that 10 or 20 or 50 times for each item the only thing we need to do is hop over to this customers table and say okay I'm just going to edit this once and there you go so it's a whole lot easier in like I said anytime you are working with joining tables in MySQL what you do is you go ahead and you select all of the columns that you want to use in your new table and make sure to use the fully qualified name so MySQL doesn't get confused and then you write from and select what are the original tables that you're selecting those columns from next you write your where clause and your where clause is basically how your tables are related what do they have in common and also if you want to order by them you know just organize them in any fashion you can sort them with the order by Clause I decided to order mine by customers ID so therefore go I can't even talk now I talked a lot during this tutorial I might be running out of time but there you go there's your simple on how to join tables and in the next lesson I'm gonna be showing you guys an alternative syntax that may be a little easier by anyways thank you guys for watching don't forget subscribe and I'll see you guys in the next video what is going on guys welcome to your 23rd - ql tutorial and in this lesson well actually before we begin I want to mention this I updated the MySQL files so if you guys want that new table go ahead download the file you can get it now there you go and if you don't know it's a my website is T&B forum calm and just go in the database section it's right there for you guys but anyways what I want to talk about in this tutorial is outer joins but before I begin that I want to mention something that I probably should have mentioned last time remember when we were talking about column names and how you can have a column and you can give a nickname by writing like as C or something and then you can refer to that column as see well you can also give tables nicknames so I just wanted to show you guys a quick example because I'm probably gonna be giving tables and nicknames and up come tutorials and I didn't want you you didn't want you guys to get confused so let me go ahead and type the whole query because for these it's easier if I type the entire query and then explain it so I'm just gonna go ahead and select I got seller ID and I'll give you guys a couple I dot name and seed ID I might as well talk about difference from customers as see so you guys can probably see what's going on already items as I so basically what we did is just go ahead and finish this before I start blabbing on seller underscore ID equals and you see I do now let me run this and make sure this makes sense there we go no air is good so basically like I said not only can we use the as keyword to give a column a nickname but we can also give a table a nickname so whenever we do that just go ahead and write your table name as and then give it a nickname hopefully shorter because you know if it's longer it's not really a nickname and do that with any table you want and then on your entire query instead of referring to this as item seller ID items name customers ID you just go ahead and you use that nickname so not only whenever your affront referring to the columns but also in your where clause as well so again like I said basically you can give tables nicknames as well as columns nicknames and it just makes your coding a little bit faster maybe a little bit easier to read many ways I'm gonna be giving nicknames in the upcoming tutorials just didn't want you guys to get confused but now we can get to the good stuff the heart of the sasural which is outer joins so what is that outer join well let's go ahead and remind ourselves what an inner join was an inner join was basically when we have two columns and we want to match them together okay that's nice but sometimes and you guys probably noticed this already you're gonna have these instances where your rows don't line up exactly maybe you have some items that don't have a seller ID or maybe you have some customers or users for your website who aren't listing items yet well that's nice but what if our boss comes in and he's like Bucky listen I'm gonna need a list of all the customers and the items that they're selling for example if Bucky's selling a tuna fish sandwich and a Corvette and a life vest I'm gonna need a list of those so again the name of the user and the items are selling okay simple enough we'll just do a simple joint should be on your way but then he's like oh by the way I also need to know I also need the name of the user even if they aren't selling any items and we're like okay that might get confusing so let's go ahead and try to tackle this problem so we go ahead and select customers dot name which is basically like the username in items dot name so basically the username in the items that they're selling from customers and items where remember where is basically saying how are these tables related so customers customers dots ID should equal seller underscore ID so we go ahead and run this go check it out and we'll just go ahead and sort it and make it look a little bit prettier we hand this to our boss and he's like wow this is looking pretty good wait a minute I could have sworn that there are some more users on our website what the heck happened to them some people's names are messing up well the reason that some people's names aren't showing up is because they don't have any items listed right now but what if we wanted to show them anyways okay that could get kind of confusing so let's go ahead and rework this query a little bit so of course we want the customers in the items name and we also want that we also want to use the customers table but instead of just going customers in items where like we did before what we want to do is write list left outer join and I know the syntax looks weird so I'm just gonna go ahead and type this query and then tell you guys so left outer join items so take note that there's a table name here and then you're right basically join with this table on and remember the on keyword is basically saying how are these tables related so customers dot ID in this is basically the same condition is last time equal seller underscore ID so let's go ahead and run this and see what we get well check it out not only did we get basically the same list as last time but as you can see it now lists the users that do not have any items listed as well so when they don't have anything to associate it with when your conditions aren't met and they don't have any related data it still shows the table on the left so basically this query is different from a simple joint because whenever you hit left outer join it's basically saying that show all customers with items even if they aren't selling any items weird in the keyword left what this means is you know how that look at the syntax right here you see that right there there's a table on the left in I know it's kind of like below it right now but if this is one long line then this would be on the right so customers is on the left and items is on the right of the syntax right here whenever you use the keyword left you're saying left means to include all the rows from the table on the left therefore the customers table no matter what so include all the customers no matter what no matter if they have items or not if we were to go ahead and hit write right here then what it would do is it would include all of the items no matter if they had a seller going with them are not so let's go ahead and take a look at that so as you can see now all of the items are listed but say this user went ahead and they deleted their user name and they got banned or something but the item was still on the website well even though they wouldn't have a user associated with that item since we use that right outer join it takes all of the rows from the table on the right which is in this case all of the items and it forces those to be shown so even though it doesn't have a customer associated with it it forces those items to be shown so that is the difference between the left outer join and right outer join basically do you want to force these rows to be shown or these rows to be shown so again that is what an outer join is basically forcing rows to be joined or excuse me forcing rows to be shown different from a regular join where both of the basically they have to have related data so there you go I'm gonna be done talking hopefully you guys understand and don't forget to download the files go to my website tnb form comm and they're right there so thank you guys for watching don't forget subscribe and I'll see you guys in the next lesson what is going on guys welcome to your 24th MySQL tutorial and guys I got some good news for you you're just gonna go ahead and sit back breathe a sigh of relief because in this lesson it's gonna be the first easy lesson it's gonna be really simple easy to understand I just need to talk to you guys about really cool concept real quick and that is something called unions so quite often whenever you're making a website you're gonna need to run multiple queries but you're gonna want a single result set because you know let's say that you wanted to make a website like eBay and you Ronen to run a query that returned all the items over a hundred ninety bids so like all the really popular items now you also wanted all the items over a thousand dollars regardless of bids maybe you're making this for your home page or something you wanted to display the cool items so all the items that have over 190 bids and also all the items over a thousand dollars regardless of the number of bids so what you would do is you would do something like this select the name cost and bids from items where bids is greater than 190 and remember our other thing that we wanted is whenever we have an item that on it's the good Mouse whenever we have an item that costs more than a thousand dollars I cannot type today I'm hitting all the wrong keys here so basically those are the two queries that we want to run however we only want one result set we only want one set of data because you know it's just a lot easier to work that way so whenever we would have run these queries right here we would get a table for this and the table for this well that's not good we only want one set of data so in order to basically take more than one query and end up with only one result set just go ahead and in between your queries type the word Union that's it that's a that's basically all this tutorial is this word right here takes this query and this query it runs it and it gives you one set of data how awesome is that and I know what you guys are saying right now Bucky unions are absolutely worthless because first of all you could have done this and this and you could have accomplished the same thing by just using where or in ant statements well it may look a little messy but the truth is yes you could so why would you ever use Union well first of all whenever you're doing more complex filtering guys I just showed you guys a really simple example but in real life they aren't this easy you're gonna do complex filtering and you're gonna have to use a bunch of different tables instead of the same table and whenever you do using unions is a whole lot more simple cleaner and easier to manage so don't forget about unions and also another thing there are some times that you have to use unions and you can't use where clauses and I'll show you that in just a second before I get to that I want to mention this please don't forget this remember since what we're doing is basically taking multiple queries and tying them together into one result set whenever you use unions your columns have to be the same you can't do something like select name cost bids from items and select name zip code address postal address and state from another table it won't work out because your tables won't tie together so again make sure that your columns are the same so that whenever they unite they can tie together nicely now what I was talking about earlier on whenever you use unions it automatically MySQL automatically removes duplicate entries so you know how we said okay select the items that are greater than 190 bits or they cost more than $1000 well what if the case in this case the baby seat where it costs more than $1000 and it has more than 190 bits well it doesn't list it twice that only lists at once you only see one baby see here so what if your boss said okay actually this is like the homepage of YouTube or something where you can have the top rated video and you can have the most viewed or most watched video well if you want to get a list that doesn't remove the duplicate entries then just go ahead and use Union all what this does is it basically leaves those duplicate entries and so now you can go ahead and hit go and check it out if this was the front page of you know your website you would see baby sweet seat twice once because it costs more than a thousand dollars and another time because it has more than 100 90 bits so sometimes you want this like I said if you're making a website like YouTube and you know maybe a video was the most favorited and the most viewed they deserve both of those honors so you know there you go so anyways that's all you guys have for this tutorial and another thing before I let you guys go whenever you use a Union y'all that is one instance where a where Clause won't do in you know that's it you basically have to use Union and all you can't substitute it for anywhere but anyways I'm gonna shut up thank you guys for watching don't forget subscribe and I'll see you guys in the next video what is going on guys welcome to your 25th tutorial and in this lesson I want to talk to you guys about something called full-text searching it's an awesome feature that's built into MySQL basically if you ever thought about making a search engine or maybe you just have a website where you already have a search functionality and maybe you using like wildcards or a like statement or regular expressions to let users search your website for something that's nice and all but now let me intrigue I introduce you guys to full-text searching it just hands-down way better than any other search feature that you can build an awesome thing is it's already built into MySQL so the first thing we need to do is we actually need to enable this now typically whenever you create a table for the very first time that's when you decide what columns you want to enable for full-text searching on however since we already created this table or I did rather we just want to alter the table a little bit so I know I didn't tell you guys how to create tables or alter table so we're jumping a little bit ahead here but just go ahead and type in this code alter table and then go ahead and type in the table name which is items and what we want to do is how do we want to alter it we want to add a functionality called full text not test text now inside the parentheses just go ahead and write a column that has some text in it you don't want to write any numerical columns here or else it's not gonna work out now go ahead and run this query I'm not gonna go ahead and run it again because I already have full text enabled because well I was playing around with it later on or earlier on so go ahead and run this and once you do you might see a little prompt on your screen as long as you don't get any error messages you're good to go but anyways now that you're in that query full text is now enabled on that column name so we can now basically treat it like a search engine and search for things in that column so let me go ahead and run a real quick example select name and cost from items where now instead of having a wear statement like maybe the word is like baby or maybe I'm gonna use a regular expression to make it baby maybe make some wild cards uh-huh that's for noobs we are here to learn about full text searching and the syntax is a little weird the first thing you're want to write is match now for this argument or parameter whatever you want to call it inside the parentheses you're right what column do you want to search in and I want to search in the name column because well quite frankly it's the only one that we enabled full text searching on but if you have multiple columns make sure to specify here so basically what column do you want to search in we want to search in the name column now what do you want to search for and you do that by writing the word against now against just like match it takes parentheses and inside the parentheses you write your search term so what do you want to search for well I'm just gonna search for the word baby because I know that there's some items with word baby it's like a baby coat baby pacifier maybe I don't know we'll find out so go ahead and hit go and what it does is it takes the keyword baby and it's search it basically searches all the items in the name column and it ends up with baby coat baby seat baby soap baby bottle pretty cool couple baby I'll just maybe I'll buy something for my little cousin who knows so basically if you're like okay I could have done this exact same thing using the light keyword because you told me that before it seemed pretty neat I'm gonna use that for my search engine instead well you can but there's a couple of things going on behind the scenes and hopefully I want to tell you guys about those and hopefully I can talk you guys out of it even though you can't see it in this example my SQL is doing is its ranking all of the results depending on how well it's matched for example whenever you go to google and type in the word I don't know the dog or something it just doesn't get random results and you know give you whatever is on its mind whatever its feeling it actually has these results ranked depending on you know what people click before whatever algorithm they use they rank them in a very smart fashion and that's what this is doing right here even though you can't see it because I don't know these are really bad examples but it's ranking it based on the number of keywords the number of total words in the name column and a bunch of other criteria that a bunch of smart people figure it out so aside from that excuse me I got whooping cough aside from ranking them automatically for you which is awesome it's not only better than like array expressions for that but it's a lot faster too I know you guys can't tell because these queries are really simple and my database isn't that big of a full-text searching is usually a ton faster than using regular expressions or the like statement and another cool thing that you can do is you can customize these a little bit further so say for example that I don't know your user wanted to look for baby items but they didn't want to look for baby coats well this has a special I want to say mmm as a special feature I guess would be the best way and it's called a boonie mode so go ahead and after your against but make sure you're in your parentheses still type in bullying mode now I'm just gonna show you guys a couple symbols but there are like 10 or 12 symbols that you can use I'm gonna show you guys two real quick now symbol the plus sign is whenever you type plus a word it means make sure that this word is included so it's only gonna give you results where the word baby is included just like before now another easy one that we can understand - is make sure that this word isn't included so basically we want to search again using full text searching but what we want to do is only search for the items that have the word baby they don't include the word coat so last time we ended up with baby coat baby seat baby so baby bottle but now we want to take out baby coat so we would do something like this so run this beast and check it out we get baby seat baby soap baby baby bottle wow that's kind of like a tongue tongue twister but no baby coat so again like I said those are two easy symbols those are pretty probably the easiest ones that I can think of right now but there are a lot other ones and a lot of them deal with how the results are ranked and um guys I encourage you to go on the website go look at all the symbols but I'm not going to bore you going through them all and show you all the little itty Gertie details but anyways whenever you want to use the symbols that's how you use them in buidling mode so anyways I know you guys are probably not sold on full text searching yet but whenever you make a huge database trust me guys don't forget about it use it and you'll see the benefits of it but for now thank you guys for watching don't forget subscribe don't forget to add me on Google+ and I will see you guys in the next video alright guys what is going on welcome to your 26 minus QL tutorial and in this lesson we're gonna start finally editing the database because before we have these tables we have this data and we can look at it you know maybe select some columns select some rows that's nice but we can never update edit delete make new tables in this tutorial we're gonna begin on that and I'm gonna show you guys how to insert a row of data first so each of these is obviously a row but what happens whenever we want to add a new one maybe we have a new user that signs it for our website or maybe someone lists a new item well let's go ahead and learn how to do that right now so take know before it begin that I'm gonna be working in the items table in every item has an ID number a name a cost a seller ID which was who was selling the item and a number of bids for that item so let's go ahead and let me show you guys a really basic way the core basics of inserting an item into a database go ahead and type insert into and then after you type insert into you write the name of the table you want to insert the data into items so now what do you want to insert into items well we just want to insert one row so go ahead and type the word values now inside here these are the values now remember I said that each row has an ID number a name of the item a price a user ID or excuse me a seller ID which is like the user ID in the number of bids so five things so you need five things whenever you insert in this kind of way one two three four five I just go ahead and do that so I just I don't know I like doing it because I don't forget any commas or you know whatever so the next row I happen to know is 101 that's the ID number of the next item and now we have to give that a name we'll just say we're selling like bacon strips or something and now how much are we gonna sell these babies for nine ninety five it seems like a fair price now let's just say that the user ID is me I remember from our customers stapled that Bucky Roberts is one and last but not least the number of bids it starts it is zero so now that is how we insert a single row into our table right insert into write the table name and write the values and make sure however many columns you have in your table not only do these have to be in the correct order well I'll show you this right now you see whenever we look at our table the items table how these columns are arranged first the ID then the name then the cost that's what order you have to write into whenever you're writing your SQL statement so remember you can't change the order it's given to you so that's nice and why am i stressing that so much because what if your boss comes in and he says you know what we actually don't need the number of bids right here or we actually want to put the name at the end or he changes your table structure in some way maybe adds columns takes away columns well that's nice and off because now we can just write new SQL statements but what if we programmed our entire website set up where it was just those statements those statements where we had to insert the items in that specific order well now we have to go back to our website rewrite all of our code and it's gonna take a whole lot of time and we're probably gonna have to fix a whole bunch of crap so I want to teach you guys a better way to insert data other than explicitly write on each column by column so here is an alternative and better way go ahead and write insert into you do this every time you insert any data now of course write your table name but instead of just writing values after this and typing in whatever data you want in your new row go ahead and add parentheses and inside here is where you write the column names of what data you want to insert so IB name cost seller underscore ID and bids now you can go ahead and write Wow definitely spelled that wrong values and how many one two three four five remember not to add a comma after your last one so this is item 102 I didn't even show you guys the road that we just inserted I'll show you in the suckit and this item can be fish in chips and how much can this be $7.99 it seems like reasonable price and of course my seller ID is one and it doesn't have any bid so far so this is an alternative way by explicitly writing the column names so now let's go ahead and hit go and check it out one row inserted don't believe me I'll prove it to you sorbet ID descending and check it out that was the first thing that we inserted 101 baking strips and 102 is fish and chips so those are two alternative ways that we can insert data into a database row by row but why did I say this was better I want to talk to you guys about this you know how our boss said maybe um you know we don't need to put the number of bids or maybe it's gonna become you know maybe it's gonna go after ID or name is at the end well even if your table structure changes which it quite often does if you ever work for a web design or a program a company this is still gonna work because you're not saying okay you're not deleting this and said okay ID has to be first name has to be second the price has to be next even if those values are or columns are all mixed up you're telling it right here what order you're gonna insert this data into so let me show you guys an awesome example even if your columns are ID cost name now we know our table is set up not in this order as ID name than cost and then it has these two extra things but it doesn't even matter because what we're saying is okay we only want to insert a row for the ID cost and name so now whenever we type something like um let's see well item is this 103 103 and I don't know what am I gonna sell this for seventy seventy seven and the name is beef on a stick much better than beef off a stick so what happens is unlike before where we have to explicitly give it the exact structure where the tables built we can just insert data however we want to however the programmer wants to insert the data that's what we're gonna do so this is much better in anyways if our boss comes in and changes the table structure this code is still going to work so we're gonna say okay we're inserting the ID number first which is 103 then the cost then the name I don't care how the tables set up this is how I'm going to insert data so we can go ahead and hit go and make sure it's inserted just like this checking out 103 beef on a stick 1777 and these two are default values which I'll talk about in the next tutorial but anyways that is your tutorial again the key thing the takeaway is you can go ahead and insert data without adding the explicit column names but it's very nice to add the column names not only because it tells MySQL how you want to insert the data but you can also alter the number of columns that you want to insert and also the order of the columns so it gives more control to the programmer instead of just the database the designer saying this is how you need to insert data so there you go thank you guys for watching don't forget subscribe don't forget to add me on Google Plus don't forget that send me $500 and I'll see you in the next video alright my friends welcome to your 27th MySQL tutorial in I don't even know what I'm going to teach in this tutorial I think I'm just gonna wing it but in the last tutorial what we did is we left off with inserting this row of data now whenever we did we explicitly told it that we want to insert an ID a name and a cost but we didn't give it any values for the seller ID in bits now by default there are some times when you cannot give it a value and it's gonna go ahead and just insert the default value which was in this case 0 now the default value for numerical numbers not the other kind of numbers the numerical numbers the default value for numbers is 0 in whenever you have strings of text or characters like this that might be a better way if I just highlight the column name instead whenever you have text the default value is null it's gonna say n you ll and it basically means nothing whenever you see the word null it means nothing empty void space nada so again the default values for numbers is 0 in the default for text is not however some tables will not allow you to insert no or no value some tables are gonna explicitly say that you have to insert a value for every single column and if you don't you're and get error message so just be prepared for that you know in the last story I showed you guys how to insert only some columns just be prepared that depending on how you create your table you have to or not have to insert all the values and I'll teach you guys about that whenever I show you guys how to create tables in like two or three tutorials but for now is I just want to warn you guys so heads up so what I want to teach you guys in this tutorial is how to insert multiple rows of data so what you can do is you can just go ahead and type a bunch of insert into values and you know just make a bunch of those statements and insert them row by row by row or you can do all in one query and I'm gonna show you guys how to do that right now so go ahead and hit insert into I say hit a lot whenever I say hit I usually mean type insert into items and of course we wanted to do it the proper way so ID name might as well insert everything cost seller underscore ID and bids now after this go ahead and type the word values just like before now how I like to do it is I like to type the word values right here and whenever I'm working with multiple rows of data I like to put each row on a new line so on the first line again what do we have one two three four five five items in each row or five pieces of information for each row or each item one two actually I'm gonna use one two okay calm down take a deep breath one two three four five now again after every piece of information you need a comma after every row basically so I'm gonna add I don't know let's just say three rows for this tutorial and my mouse isn't working there we go so that and this one now remember after the last row you don't add a comma people do this a lot and if you do you're gonna get a error message so for the first piece of information again I think we left off at 103 we inserted like beef on a stick or something so this is 104 and I'm gonna sell some beef whoa take it easy caps locks I'm gonna order or excuse me sell some beef chops my favorite kind of chops and I'm gonna sell these for $7.99 my seller ID is one and bids equals zero now my next item is 105 of course and I'm gonna sell some jelly pockets my favorite kind of pockets and these are 450 each and again one for seller ID that's Bucky Roberts and zero now for the last thing that I'm gonna sell it's 106 and what do I want to sell they go out sack of ham and I'm gonna sell this for 995 quite the deal if you ask me and my seller ID and number of bids so as you can see basically the rules are quite similar to what you would expect you go ahead and write insert into and then you write the order and number of columns basically how do you want to insert your data so whenever you type values each of these is a new row of data again how do you insert data well however you defined it up here now just remember after each row there's a comma except after the last one if you're programming this in PHP or something there would be a semicolon right here but since you're just writing this in phpMyAdmin you can just leave it out so now let me go ahead and hit go and well looking pretty good so we can go ahead and verify this if we just click on the items table do a little browse or by ID descending and come on baby no whammy no whammy there we go so there's my nice beef chops jelly pockets in the sack ham by the way if anyone wants it quite the deal and no bids so that is how you insert multiple rows of data again I like to take each row and put it on a new line it just makes it a little bit easier now I wanted you guys about one last thing when it comes to inserting data to be honest this is one of those things that I don't even know if I should show you because it's usually a waste of space but it's information that may come in handy one day so you can also insert items into a database or a table or rather using a select statement now let me give you an example of when you would do this say that I don't know say that you had a website and you just spot out eBay because you know your website is pretty successful now you want to merge your websites together so you have all these items in your table but they have all their items in that table so are you saying that we're gonna have to hire some guy to go through and physically type like a thousand or two thousand insert statements no what you can do is you can insert data into a table from a simple select statement now again I only have one table items here but pretend that we have a different table set up with the exact same structure named more items and it just had a bunch of other items so what you would do is something like this insert into items and you would of course write how you would want to insert them ID name cost seller ID and bids and now instead of writing values which you would have to explicitly type each piece of information row by row you can just use a select statement so select ID name cost seller underscore ID and bids from more items now this more items table is just a fake table so actioning them a fake table so again if you had another table then what this would do is it would select all of the rows from that table and it would insert them into your items table in this order right here so again remember whenever you're doing this is probably better to use a where Clause because if you don't use a where Clause then it's gonna select every single row now again I don't have a fake table but this is how you would do that basically take all the rows from another table and insert them into a single table so I guess it's good if you're merging tables but otherwise why do I say that this is not a good idea because at first glance it might seem quite useful well it's not a good practice because then you're gonna have the information on this table and this table and as we know one of the rules in MySQL whenever you have duplicate information it's usually a waste of space so guys please this is your warning so basically um that's it that's a tutorial you may use this whenever you maybe you're making a new table with your high-priced items or your new items that you want to appear on your homepage but generally stay away from it that's my warning so anyways thank you guys for watching and yeah don't forget to subscribe and me on Google+ all that good stuff send me a million dollars on my website and I'll see you in the next video in the next video we're gonna be talking about updates see you then alright everyone welcome to your 28th MySQL tutorial I have no idea why you guys are still watching these total waste of time I'm just kidding but anyways welcome to your 28th tutorial and now that we know how to do also school stuff insert rows into our table we know how to do a whole bunch of query select statements I think it's time we learn how to edit rows so check it out let's say that we just inserted an item into this website so I'm selling some I don't know a sack of ham or something but then I was like oh wait a minute mom you said pudding hammock I thought you said sack of ham now I have to sell a frickin sack of ham well we need a way to edit items that are already in the database now we can do this in one of two ways the first way is that we can just go head in delete this row and rewrite it but there's an easier more simple way and I'm going to show you that right now so in order to edit a row you use the term update I think they should have chose edit but they chose update instead it means the same thing now you're saying okay what table do you want to update well I want to update a row from the items table so there you go go ahead and hit items now after this you need to say okay which piece of information did you want to update did you want to update the ID the name of it maybe you wanted to change the price or the number of bids well in order to do that we first need to use the set keyword now set means change basically now after this rewrite the name of the column or the piece of information that we want to change so of course what was I say a sack of ham or something I forgot what it was by anyways we want to change it so we want to change the name whoa what do you want to change it to just go ahead and give it a new value using equals and then type the new value we want to sell a pudding hammock and if you don't know what that is I would recommend that you open dictionary that shiz we wanted to actually sell pudding hammock instead of a Sega ham so we can't just go ahead and run this baby right now because look what happens whenever we would run this it would update the items table and it would set the name of all of your items to pudding hammock now unless you're running your website and you're selling 300 pudding hammocks I don't recommend you do this so whenever you use the update excuse me I can't talk whenever you use update to edit rows it is critical that you use a where Clause now the where clause is saying okay what row do you want to edit well we only want to edit where ID is equal to 106 we only want to edit this item we don't want to change the name of all of the rows in our table so now let's go ahead and run this and check it out it now says one row affected and if we go look at items in a sort find 106 it now changed the name of 106 - pudding hammock now how awesome is that so check this out say that we I don't know we were maybe editing this in maybe we had to take it down because we're not allowed to sell pudding hammocks but then we talked to the owner of the website and he allowed us to put it back up well that's nice and all but why the heck did you reset my bids to zero these are hot items they're selling like hotcakes this thing had like 66 bids on it well of course we need to edit that too so if you're saying okay not only do I want to edit one column but I want to edit multiple columns we can do that as well now I'm not gonna have to type the whole thing let me just well I might as well change this again so let's say you know what instead of selling pudding hammocks I'm gonna sell for outpaced it's the best pace trust me guys now whenever you want to edit more than one columns again this is pretty self-explanatory but you just separate it with a comma now you go ahead and write the other name of the column for example the bids and you can edit these two so again just like before seperate your columns with a comma and just write the new value of each so now we're changing the item 106 again we're changing the name to frog-faced and we're gonna add 66 bits because I don't know why not it's just an example give me a break guys so now we go ahead and hit go and it says one row affected but of course you can never trust computers did you ever see Eagle Eye so um we're gonna make sure Fred pastes and bids 66 so that is not only how you update items but also how you update an item with multiple well you know I can't even explain it multiple columns at once so now I want to teach you guys about the lead and you guys are thinking okay next tutorial but I'm not gonna teach you in the next store oh because delete in how to delete rows of items for example if we no longer want to sell this frog paste or we no and longer want this beef on a stick on the website I'm going to show you guys how to delete this in the reason I'm not waiting for another tutorial is because this is the easiest thing I have ever taught anyone in my life so in order to delete rows just go ahead and hit delete from and write what table do you want to delete a row from well I want to delete from items where ID equals 106 you guys probably think I'm going to type something else after this but I'm not that's it the only thing you're right is what row do you want to delete and what table are you deleting that row from now again the where Clause is very important because if you just say delete from items it might delete your entire table so let me go ahead and delete the item I'll go ahead and delete 104 so it says do you really want to delete okay yes I do and it says okay I freaking delete it before you you said okay you've even verified it so now we got to go ahead and check we got 106 105 104 is now deleted from our table when a 3 1 2 2 1 1 so on and so forth so I can't remember what that item was but that is how you delete items from a database and again I know a lot of you guys are just gonna be so tempted to not use that where clause and it's gonna delete your whole table but hey there you go so another thing I want to mention this is the last thing I promise before you know you guys can go out weird cat videos or something but that's why primary keys and ID numbers are so important because you know how in our where clause I said delete items where ID equals 106 well say that you decided not to use primary keys or ID numbers because you know you just don't take my advice well then you started this website and a bunch of people were selling DVDs well you want it to leave this item right here because this is a spammer who posted this so you said delete where name equals DVD so yes you're in that query in would delete this row too but it would delete all those other people who also name their items DVDs so whenever you use primary keys instead of giving a general name that may delete 10 or 20 other rows accidentally you say delete item 99 because that's the primary key no other row can have that primary key and you give it an ID so that way it knows which specific want to delete so that is the beauty of primary keys and again that's one little gem of information plenty more to come in the next tutorial what I'm gonna be doing is I'm gonna be showing you guys how to create entire tables it's gonna be awesome it's gonna be amazing so thank you guys for watching and if you subscribe and add me on Google+ and I will see you guys in the next video what is going on everyone welcome to your 29th my SQL tutorial and this is it's ro that some of you have been waiting for it the lesson where I finally did you guys how to create tables now if you already played around with PHP myadmin you can of probably guess that if you want to create a tip on your database just go ahead and click the database name and then you can create a table just name your table say how many columns but we're not here to learn PHP myadmin we're friend we are here to learn MySQL so I'm gonna teach you guys how to create a table through command line so go ahead and wherever you can type in your SQL statements the syntax to create a table is this you guys are gonna think this is totally confusing and baffling but it's actually create table amazing huh so now that we want to create a table what we want to name it say we were making a website I don't know we'll just make like a very simple website where we can have user sign up so I'll just name this table users now after this go ahead and write parenthesis now I'm gonna show you guys how I create a table and I just think it's the easiest way but each column I like to put on a new line so every time you create a new column I'm gonna put on a new line and every time you create a column it's gonna need information about that column the first piece of information is the column name what you want your name or column you want to name ID bid seller ID what is it I'm just gonna name this ID this is gonna be the primary key next is what type of data do you want to store in this well I just want to store numbers just regular integers now I don't want to talk to you guys about all the different types of data because there's like I don't know probably like 50 different types of data and a lot of them are kind of redundant so I'm just gonna tell you guys about the different types as we go along but the first type is int int and what this means is integer basically whole numbers numbers without a decimal point so now I want to create a column that's going to hold integers and I'm gonna name it ID that's how I do that now on my next one I want to create another column for as well I'm gonna create another column on this table named username where users can pick your username you should pick the New Boston because it's a pretty good one and now the type of dad for this you can use text character but I like to use var char what this means is is pretty much strings or if you don't know what a string is if you're not very familiar with programming just think of it as like text so now I probably should mention this some data types okay back up even more whenever you specify that you want to create a column on your table I told you guys that it needs a couple pieces of information the name of the column the type of data that it's going to be storing for example integers it also needs a maximum stored size so basically how big of integers can you put in here how big of strings or how many characters of text can you put in here well every datatype needs a maximum sort size some datatypes already have default sizes so that's why I didn't explicitly need to write how many bytes of memory or whatever we're gonna be storing in here but for varchar' we need to explicitly tell it in parentheses how many characters is the maximum basically how big can a person's username be well whenever we write 30 here this means that their username can be no longer than 30 characters and again this is kind of confusing because some data types like int they have a default maximum size so you don't need to specify it but other ones like varchar' you Dinu to specifies and in order to determine does my data type or doesn't it have a default size you can just look on the mysql website it's probably changing all the time anyways but now we have a primary key a username and we also want a password form so go ahead and type password and this of course is just text to letters and numbers of our chart and I'll just give a maximum size of 20 so their password can be no longer than 20 characters now you know how I said that every table you create should have a primary key now in this table it was obviously the ID the first one we created not the username not the password however our MySQL our database doesn't know that already it's not smart I just can't say hey this is my primary key we need to say that the primary key for this table is going to be ID now whenever we do that we need to go ahead and type on a new line well it doesn't need to be on a new line but I like to type it on a new line primary key now in order to specify your primary key inside parenthesis you go ahead and write the name of the column that's the primary key in this ID so basically to create a table you go ahead and write create table and then you give it a table name now once you're done with that you need to say what columns are going to be in your table now every time you make a column you need the name of the column which is username the type of data that column is going to store which is this text or varchar' and the maximum data size or the maximum storage size so in this case it's 30 and again the reason why we don't have to use it whenever we create tables that hold floats or in is because intz and floats in a bunch of numerical datatypes they already have default sizes so now just go ahead and hit go or run your query and we now have our table so check it out if you go ahead and click on left hand side users we can now browse it so again the column name ID username and password and whenever something is underlined in PHP myadmin means it's the primary key now the type of data the idea is just gonna be integers 1 2 3 4 5 so on and so forth username and password is just gonna be text and again these are the maximum storage size 30 characters 20 characters or 11 bytes which is basically it's a it's not like 0 to 11 it's 11 it's a really big number I just want to mention this I don't know it off the top of my head but it's a really big number so now we got all this other stuff like attributes null but the hexanol default what the heck is this crap in the next tutorial I'm gonna show you guys some other settings that whenever you're making a table that they're gonna come in handy such as null Auto increment all that good stuff as well so that is the basics of how to create a simple table but in the next lesson I'm gonna show you guys how to create a more advanced table so trust me guys it's going to be probably the best oriole ever trust me so thank you guys for watching don't forget subscribe and I'll see you guys then what is going on guys welcome to your 30th MySQL tutorial and in this lesson I want to talk to you guys about some additional pieces of information that you can use whenever you're creating your tables they're gonna come in handy the first piece is null or not null now whenever you create a table and you want to allow the people to insert rows there are certain columns where you can leave empty and it doesn't really matter for example whenever you're making a website in you want a user to sign up and type in their username and password you don't want them to leave this empty so in other words you do not want this no you do not want this empty there are other times when you may have forums on your website that it's okay to leave empty for example you know those address forms that say address 1 and there's an optional address 2 if you live in an apartment or maybe you have some foreign address well not everyone needs to fill out the address 2 or the second address so it's ok if they leave that empty but other times such as name zip code you need to fill that in so let me go ahead and give you guys a quick example use your name bar chart if you were to insert a row like this where you definitely needed the information to be required they couldn't leave empty such as a username you definitely want to use your name go ahead and after you're done writing your datatype type the word not what I say nuts not null and what this means is not empty they can't enter a blank for a username so if you were to have something like address to you would go ahead and not even include that because address too is optional but again remember whenever you want the information to be required and you can't have empty go ahead and type not null and this means that if they try to insert a row of data without a username or with a blank username it won't insert it into your table and it'll give them an error so that's what not in all means it can't be empty another thing is something called auto increment now whenever you're making a primary ID say you want to make a primary D it's of course an int and of course it's not no because that's the unique identifier you can't have it empty now we don't want the user to choose their own primary key we want to do that automatically now in order to do this every time a row gets inserted into your database we want it to increase by one for example we want the first easier that signs up to have the primary key of one we want the second one to have the ID number of two three four five and so forth so how can we do this well at first you may be thinking okay so I'm gonna have to make a query I'm gonna have to select the most recent user I'm gonna have to select their ID number maybe make a formula add one to it and then throw it in for this one pretty confusing huh or you can just use this Curt keyword right here Auto underscore increment now whenever you use the keyword Auto increment that means that it's automatically gonna add one to the last ID you don't have to do anything manually it automatically adds one whenever you create a new row so there you go instead of having to grab the last piece of data and can formula to add one it automatically EADS adds one to each entry sounds confusing it's once you get the hang of it it's really not so now let me show you guys what a final full table creation will look like so let's say we want to create a table we already have users and customers so let's just name this I don't know people you can even name it bacon or something stupid doesn't matter so of course let's just go ahead and enter the same information as before ID username and password now ID is gonna be the primary key so int now of course not no and aside from that auto-increment so this is going to start by default at 1 and it's gonna keep going and going add 1 to it each time the user doesn't have to worry about the ID number you don't have to worry about the ID number my s qo is gonna take care of everything for you so again their primary key is not null it can't be empty and it's gonna increase by one each time now the next one the next column is username which is just text letters and numbers and we'll give this a max of 30 and this can't be empty either and the third one is password varchar' I think this was 20 and of course they need a password they can't have this empty either now last but not least we already know that we want this to be the primary key premiere at key there we go I was about to say primary IKEA's totally different but MySQL doesn't know that yet so we need to tell it so primary key we want one of these to be the primary key which one you say ID so now we go ahead and create this table and it says okay table created BAM there you go so now if we look at bacon it has ID is the primary key username and password before but now none of these are allowed to be null and also ID gets Auto incremented so if we were to add some information in the bacon table say we wanted to I don't know insert two users or something check it out for ID I'm not gonna put anything by default and whenever you're programming your website or something you don't insert any value in here explicitly you let MySQL take care of it so the first user comes along in his name is Bucky he must be a pretty cool guy and it's password is password because he's an idiot now the next user comes along in her name is Sally and her password is slam bacon so now we go ahead and hit go and it says two rows inserted but now we go ahead and look at our bacon table and check it out MySQL automatically gave Bucky the user ID of one and he gave Sally the user ID of two how awesome is that and if we were to add more we would just need the username and password in MySQL would automatically give them the next ID of three four five six and seven so on and so forth so there you go that is how you well make awesome tables and um is there anything else let me think no that's it so I mean I guess I could stay here and not talk about anything else but what would I do that for so anyways I'm gonna go out some videos and maybe go get a milkshake at McDonald's I know what's unhealthy about come on guys give me a break so anyways thank you guys for watching and no idea what I'm gonna be teaching in the next lesson but it's gonna be awesome so I will see you then what is going on guys welcome to your 31st my SQL lesson and in this tutorial I want to finish up kind of talking about messing with tables the last main things that we need to talk about the first thing is I already showed you guys how to create a table and how to make like all the datatypes and columns and stuff but what if you want to maybe add an additional column maybe you make your table and say ok this is good but you have your website up and running and about a month later you're like dang I really need a extra piece of information when people sign up so let's go ahead and learn how to do that anytime you already have an existing table and you want to add a column you can do it in one of two ways I'll show you guys the first way alter table and we'll just go ahead and alter this bacon table right here so go ahead and write the name of your table after which is bacon and if you want to add a column just go ahead and hit add and then write the name of your column the new one which is sample column or whatever and then of course just like whenever you're creating a column for the first time you need a data type which can be well it doesn't really matter for this example I'm just gonna make a bar chart with ten so now what this does whenever you run this query it's gonna add a new column to this bacon table which is called sample column which has the data type of varchar' ten simple enough now in order to drop a column or in other words delete a column from the stable let's go ahead and run another query it's basically the same thing but instead of add you hit drop column in I'll discuss the differences later on so alter table basically means change the table to bacon and drop column now what column do we want to drop well it turns out that we didn't need that sample column after all now the other thing that's different aside from add turns into drop column you know how when we added a column we needed to specify the data type well whenever we're dropping in column MySQL it doesn't really matter what that data type is it just cares about the column name so if we go ahead and run this right here it's gonna say it's gonna give us you know other confirmation do you really want to drop that column yes we do go ahead and hit OK and now our column is dropped so that is one way that we could add columns in drop columns another way is and sometimes you may want to do this whenever you want to kind of preserve the data and just make a new table you can just go ahead and make an entirely new table so say we had this bacon table right here bacon has ID username and password now if I wanted to make a new table with an additional column but I didn't wanna you know maybe mess up this table or I was afraid of editing it what I would do was I would create an entirely different table maybe name it bacon - or something like that and I would take all of this data and pour it in - bacon - and that way if I messed up anything I still had just this original table so that is another variation that you can do that so say that you did that and now all of your data is successfully transferred into a new table called bacon - well now we want to get rid of this bacon table because hey it's just taking up space on our database so in order to delete an entire table here's how you do that drop table bacon now whenever you do this be absolutely sure that you never want to use the table again so I'm gonna go ahead and delete this and it's gonna give us our confirmation and not only does it delete the table but all of the information so again make sure that you definitely are very careful with this command so drop table okay the bacon table is dropped so now actually now that I think about it I probably should have done these last two little lessons in reverse order but I'm gonna show you guys how to rename a table so now that we don't have bacon to work with anymore I guess I can't rename it to anything like sausage or anything but check this out you know how we have this customers table and it kind of has all the users information well I've been doing these tutorials and I was kind of thinking you know what would it be handy if this customers table was actually named users instead hmm that would be maybe make things a lot more clear so what if I could rename this table customers to users wouldn't that be nice yes it would so let's go ahead and learn how to rename a table any time you want to not you know import data don't mess with the data at all just change the title of the table go ahead and hit rename table and then give it the name of your original table which is customers and now go ahead and write - and then give it a new name so I want to rename the customers table to users so I'm gonna go ahead and run this and it's gonna say okay your query has been successfully executed now it doesn't update here but if you go ahead and look on your database you can see that it does indeed so I just want to mention that not only with you know whenever you're renaming stuff but sometimes whenever you're deleting things in phpMyAdmin it takes a little bit to update on the left hand side but if you go ahead and you know exit out then look back in it should be updated so anyways that is how you change columns add columns drop columns delete tables rename tables so now that's basically all there is for table wise how to create tables and mess with tables so in upcoming tutorials we're gonna be learning about some advanced techniques that go beyond the basics and for right now how can I say this you are an intermediate MySQL user but the next 10 tutorials are gonna take you to the next level they're gonna be awesome and amazing so thank you guys for watching don't forget to subscribe and I will see you guys then alright guys welcome to your 30 second lesson and in this lesson I want to talk to you guys about something called views now views are kind of tricky but once you get the hang of them you're gonna be able to do some amazing things with them and they're gonna save you a whole bunch of time so definitely pay attention in this tutorial because it's gonna be a good one now views kind of think of them right now as temporary tables now remember this views don't contain any real data they're just holding data from other tables so that's all a view is a view into another table so let me give you guys an example of when you would use a view say you had a website like eBay and you had all of these items and on your home page you wanted to show the items that have the most bids the hottest items so what you would do is you just go ahead and run a query sort by bids and show the top ten most bitted items so let's go ahead and run a query to do that we'll go ahead and select ID name and the number of bids from items and we'll just go ahead and in order to show the top ten we'll just order by the number of bits so get highest the lowest and in order to do that we need of course descending highest the lowest and say we only want ten we only want the 10 most fitted items so let's go ahead and run this query and make sure it works so clearly as we can see what this query returns is the top 10 most bitted items but check this out we not only want to put this on the home page but maybe on every user's page we want to run this query on so they can see the most top 10 bitted items and then maybe we're gonna make a little app so people can put it on their own page and I don't know we just want to run this query a bunch of times so what we can do is every time we want to show the user the top 10 most fitted items for our website we can go ahead and run this query again or we could use a view now let me show you guys how to make a view out of this query what you would do and this is how I do it this is probably the easiest way to make a view go ahead and run your query and make sure it works and then once you have your query set up how you want it to go ahead and just hit enter and bump it down in line now at the top line go ahead and write create view and then go ahead and name your view and remember a view is pretty much like a temporary table so I'm just gonna go ahead and name this view something like most bids and then go ahead and write as so we're pretty much saying go ahead and create a temporary table called view name in most bids and as the data just go ahead and use the data from this query right here so now go ahead and hit go and it says okay we created it now as you can see it creates a view on our left-hand side and if you can tell by the little icon a view is different from a table because there is actually no data inside this view the data that you're seeing is actually the data that exists inside items now this is good for a couple reasons first of all whenever the items change up maybe you know it's a week later and all the items are sold and we get new items with the highest bids so do the items in the most bids view this is pretty much a dynamic table it updates automatically and therefore it's better than creating a new table because check it out if we were to just create an entirely new table out of the top 10 items in items then every time those items change we would need to update the new table but with a view a view gets updated automatically whenever you run this query it's gonna take the top 10 most fitted items at the time so that's pretty much a cool thing whenever the items table changes so does its view so that's cool thing it saves you from having to rework rewrite their entire table every time you you know want to update your table so pretty much just remember that a view is dynamic and updates automatically any times the info anytime the information in the items table changes so I know it's kind of hard to get used to but well that's a views are kind of complicated at first but once you get the hang of them you'll begin now another thing and maybe this will give you guys a better idea of when you would use views say that you know in our example oh man it must been like 15 tutorials ago I said that you can maybe make a new table or even a new column to have the properly formatted mailing address because for example whenever you want to mail something to someone in the United States you write the name of their city and then their state separated by a column but if we just use the data straight from here there's no way that you can use a column so what we can do is we could actually add a temporary column on to here or we can Joe go ahead and just add a view so let me go ahead and turn this baby into a new view so go ahead and SQL in let's run our query first so what we want to do is we want to tie the city and state together so city comma state in a proper mailing format so what we do is select and remember that concat function it takes pretty much three things and ties it together or any number of things actually so city and then after this I'm just gonna add a comma with a space after it and then add state so for example it would say Las Vegas Nevada or something like that and I'm just gonna go ahead and write as address from users now remember whenever you use the ASCII word it pretty much gives it a nickname so let's go ahead and see what this baby does now what it does is it makes an entirely new column called address by taking the old city adding a comma after in a space and then writing the state so say that we actually wanted to save this address not only as a temporary column but we want it to save it as a new view so go ahead to rare you where you wrote your query and right above it just go ahead and write create view and now remember a view is pretty much a temporary table so give a temporary table name and since this we're using this for a mailing address I'll just go ahead and write mailing as and under your ass just go ahead and write that query that we had before so what this is gonna do is it's gonna create a temporary table or review called mailing and the information that's going to be inside that table is whatever we selected in this query right here so go ahead and hit go and check it out on the left-hand side if we click to browse our table or view we can see that we now have our properly formatted address now you guys are probably saying okay so why wouldn't you just make a temporary column well whenever you have a view you can treat it just like you would a table for example you know how we could you know of course run select queries on items and users we can also run basic select queries on these new views for example if we go ahead and select if I can type select name and bids from most bids what this is going to do is it's gonna select the name and the number of bids from our most bids view now remember our most bids view is the one we made in our first example which took the top ten most bidded items and put them into an entirely new table so let's go ahead and run this query and see we've got the top ten most bidded items how awesome is that so basically the thing you have to remember is views do not store any data themselves that is one benefit they have over making a duplicate table for example that would store the top 10 items because they don't store any additional data they just show you data from other tables and that's why they don't take up any extra memory it's basically a shortcut for having to retype a query each and every time you want the top 10 items or anything else for example and I know I showed you guys some examples in this tutorial that were probably pretty useless but trust me views can come in handy especially when you have queries that you run quite often duplicate queries or you know you'll figure it out whenever you're programming but for this tutorial that's all I have for you I bet you guys are probably sick about hearing of views so anyways thank you guys for watching don't forget to subscribe me and I'll see you guys in the next lesson what's going on guys welcome to your 33rd and final video of MySQL now I know there's something such as triggers cursors stored procedures some things that I didn't cover but a lot of these technologies are not fully supported yet in all versions of MySQL so what I'm gonna do is I decided not to teach these other things until the technology is fully developed and that MySQL in all versions of them support these things such as triggers cursor stored procedures so on and so forth so for now there you go that's your beginners tutorial in MySQL database in congratulations you made it to the end and before I guys before I let you guys go I want to tell you guys a couple of things first of all whenever you want to back up your database in PHP myadmin go ahead and log in and click on the database that you want them back up for example mine is named YouTube so I'm gonna go ahead and click on that and now all you have to do is hit this export tab right here now you can go ahead and choose custom if you don't want to export all the tables or something but the easiest way is just to use quick selection quick export and make sure you export it as SQL format now when you go ahead and head go what it's gonna do is it's gonna save the file for you now this is your SQL fire file pretty much the file that you have been importing for my file to use an example it's basically the same type of file so there you go there's your backup if you ever lose your database or something just create a new database or a quick and import this file using the import tab and you'll be good to go so that is how easy it is to export and import pretty much how to backup a database using PHP myadmin now I said that that was the last thing but I actually want to give you guys a few more tips that are gonna make your database life a whole lot easier now the first tip is whenever you create a database and you're creating your tables take the time to create your table structure properly make sure you have all the columns you need make sure you name them properly and especially make sure to take time to choose the right data type in the right amount of memory for each column it's gonna save you guys a whole lot of headache in the future now the next step is make sure you never retrieve more data than is necessary this is probably the biggest mistake I see beginners use for example they pretty much use select all for pretty much their only query and then they pick out the pieces but this isn't good you typically want to use select all basically never unless it's absolutely necessary again never get more data than you absolutely need and the last piece of advice is full text is usually generally better than like remember this full text greater than like it's just faster and it's cleaner and it's the technology is just better in every way I can think of so with those little pieces of information I'll finally let you guys go because I bet your brain is about to explode from all this MySQL so go take a break go do something else in again like I said whenever these technologies are fully supported I'm gonna come back and do either add more videos under this series or do an advanced tutorial series on MySQL covering triggers cursors sort procedures all that good stuff but for now that's all you guys get so thank you guys so much for watching this series and if you have any further questions just go to my forum tnb forum comm and I'll answer for you there so again thank you guys for watching don't forget subscribe and don't forget to add me on Google+ I'll see you later and thanks again

Channel: Extern Code

Views: 157,905

Rating: 4.9047174 out of 5

Keywords: sql tutorial for beginners, sql tutorial advanced, sql tutorial for beginners full, sql tutorial for beginners using xampp, sql, Databases, advance sql, sql advance, sql advance tutorial, advance sql course, sql advance course

Id: _vFiyFaQCPA

Length: 220min 6sec (13206 seconds)

Published: Fri Jan 05 2018