SQL Tutorial from Basic to Advance

SQL Tutorial from Basic to Advance


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

Comments

  1. Post
    Author
  2. Post
    Author
  3. Post
    Author
  4. Post
    Author
  5. Post
    Author
  6. Post
    Author
  7. Post
    Author
  8. Post
    Author
  9. Post
    Author
    Emilio FernándezJ

    Best tutorial ever!
    I applied for a job that requires basic SQL knowledge and in two days I have a test, so if I get the job I will owe you man!

  10. Post
    Author
  11. Post
    Author
    Asta Juškaitė

    Hey, I cant get the database from tnbforum.com and I cant find them either in https://northwinddatabase.codeplex.com/
    Any help to get the same data as in your video, would be appreciated 🙂

  12. Post
    Author
  13. Post
    Author
  14. Post
    Author
    Antonio Gomez

    Hey Bucky, I really liked your content as it is very informative, easy to follow, spot on and technically correct in evrery way. I learned a lot from you about SQL, which has given me the confidence to take my DBA Certification exam this coming Tuesday 02/27/18. But your slutty mom's comment at 108:50 made me a subscriber, because it was then that I realized, not only is Bucky a very smart, technical dude, but Bucky is down to earth and cool 🙂 Keep up the GREAT work 🙂

  15. Post
    Author
  16. Post
    Author
    Dean Dyer

    hey Bro, your tubforum.com web site is not working as it shows in the video… So getting the files that you mention is not possible. what's up? Please advise.

  17. Post
    Author
  18. Post
    Author
  19. Post
    Author
    Dev Roop

    Hello, I am really loving this tutorials but am not being able to get the sql files. i tried all the links mentioned in lesson and comment sections. Could you please help me get those sql files.

    Thanks!!

  20. Post
    Author
  21. Post
    Author
  22. Post
    Author
  23. Post
    Author
    botr run

    Hey Bucky, I had downloaded MySQL from another tutorial using MYSQL Workbench. Can I now use php My Admin to access it and download your sql files to follow your tutorial?

  24. Post
    Author
    Steve Burrus

    Uh you are WRONG HBucky ab out there not being a division key [""] on the standard keyboard. It 's right next to the Delete key and above the Enter key.

  25. Post
    Author
  26. Post
    Author
  27. Post
    Author
    Yolanda I

    Cant access the files for download it. I was able to fine the codes on github but I don't know if I can just copy and paste in mysql database on azure or what to do?help please

  28. Post
    Author
  29. Post
    Author
  30. Post
    Author
  31. Post
    Author
  32. Post
    Author
  33. Post
    Author
    Kevin Clarke

    Great, well-organised tutorial! I'll definitely be using this to hone my MaxDB skills… that's what SAP changed MySQL to after they bought it out, right?

  34. Post
    Author
    Tim Willemsen

    Any chance you can provide a link to your files because the URL you mention for your forum does not look like your video and I can't find the files you need to continue the tutorial.

  35. Post
    Author
  36. Post
    Author
  37. Post
    Author
  38. Post
    Author
  39. Post
    Author
  40. Post
    Author
  41. Post
    Author
    Rick Escamilla

    Thank you, finally someone explaining what SQL is, in normal day to day talk.
    Can you guide me on what open source I can you to create a support ticketing system? I want to assign tickets to certain people and a couple of groups.

  42. Post
    Author
  43. Post
    Author
    Ezekiel Kollie

    sir,my problem is, to get the data from the website.I put in the domain name you give me, l can't see exactly what you show.please help me up.

  44. Post
    Author
    Div Prabakar

    i checked the entire comment section. Couldn't find a link to download the employee table. i know you shared the w3resources link but i am kinda looking for a way to download it for my system. Any chance you got a download link?

  45. Post
    Author
  46. Post
    Author
  47. Post
    Author
    Danny L

    when using a subquery where the subquery creates a list use "IN" keyword to extract data from said subquery. see example at 2:10:00

  48. Post
    Author
  49. Post
    Author
  50. Post
    Author
    Ken Kupchyk

    Create SQL in Record Time With Stonefield Query – Download the free trial version and create create dashboards, detailed reports, high level overview, drill-down/drill-through, charts/graphs, pivot tables, and more in minutes, not hours or days.
    https://www.stonefieldquery.com

    There's no need to understand database structures, joins, the SQL language… Stonefield Query has you covered with its built-in knowledge of your database. Simply pick the fields you want and Stonefield Query figures out how to give you the results you need. You don't have to tell it how to get the data from the database like you do with other reporting tools.

    – It shows descriptive names for all fields and tables rather than cryptic names and symbols.

    – It displays descriptive operators such as "equals" and "greater than"

    – Stonefield Query takes care of joins between the tables automatically so you don't even have to know what a join is.

    – There's special handling of certain fields: it displays a description rather than a meaningless lookup ID.

    Stonefield Query provides a step-by-step method to create professional reports. Anyone can create detailed reports, high level overview, drill-down/drill-through, charts/graphs, pivot tables, and more in minutes, not hours or days.

  51. Post
    Author
    Sangam Chouchan

    Wanted to personally thank you for making this. Very well explained, and very clear. Thanks Bucky!

  52. Post
    Author
    Threelly AI

    https://chrome.google.com/webstore/detail/threelly-ai-for-youtube/dfohlnjmjiipcppekkbhbabjbnikkibo

  53. Post
    Author
    Abubakar yusuf

    please i visit the your forum but i didnt understand anything because everything changed not as you showed in this tutorial

  54. Post
    Author
  55. Post
    Author
  56. Post
    Author
    hppy c javs

    Wow! This is one kind of a long tutorial that you dont want to end that soon. Trust me guys. Don't have to adjust the playback speed either… so entertaining, fun, and so full of content for not only beginners, but for thoose who want to have a knowledge/an understanding about SQL and DBMS… Please do more! Salute from the Philippines…Thnx a lot Sir!!! You made it sound so easy, and it feels like so. Stay safe!!!

  57. Post
    Author
  58. Post
    Author
  59. Post
    Author
  60. Post
    Author
  61. Post
    Author

Leave a Reply

Your email address will not be published. Required fields are marked *