Intro To PostgreSQL Databases With PgAdmin For Beginners - Full Course

what's going on guys John elder here from Konami calm and in this video I'm going to teach you Postgres database with PG admin alright guys like I said in this video we're going to learn the Postgres database using PG admin the graphical user interface but before we get started if you liked this video I want to see more like it be sure to smash the like button below subscribe to the channel and check out coding me comm where I have dozens of courses with hundreds of videos that teach you to code use coupon code youtube to get twenty two dollars off membership that's all my courses videos and books for a one-time fee adjust twenty seven dollars which is insanely cheap so this video is actually a compilation of my full PG and Men Postgres course that's available a code me.com and you to me it's the first half of the course so there's going to be like transitions because they're individual videos that I've edited together so they'll be like a little splash screen transition between each one but not a big deal and for free can't really complain so let's go ahead and get started alright welcome to the course my name is John elder from Cody me calm and I'll be your instructor today in this video I want to spend just a minute or two talking about some sort of things we need to talk about before we get into the course and start actually learning this stuff first thing I want to mention is questions if you have any questions along the way if you don't understand something if you try something and it doesn't quite work if you get stuck if you're just lost shoot me a message any time I'm here all day every day answering questions sort of what I do and I'll be happy to walk you through absolutely anything so I'm located in Las Vegas West Coast time Pacific Standard Time whatever so if you're on the other side of the world just sort of keep that in mind when you sort of factor in response time you know I'll answer it as quickly as I can during you know normal business hours I'm here a lot in the evenings as well answering questions but you know if it's 2:00 in the morning or something you just have to wait till didn't ill the next day when I wake up to get an answer but for the most part I try and respond just as quick as I can what else I'm on a Windows computer if you're on a Windows computer that's great if you're not if you're on a Linux or a Mac computer you should be just fine you should be able to be able to follow along no problems PG admin is a browser-based solution so it'll work on any web browser on any computer Windows Linux Mac doesn't matter it's just gonna be a matter of downloading postgrads on your computer and there are Linux Mac and Windows versions for that we'll see that in the next video so you shouldn't have any problem there at all we're gonna be working locally we're gonna have everything on our local computer I'm not going to set up any sort of web server nothing online you know obviously you're gonna probably in the future want to use an online web server that's beyond the scope of this course in this course I'm just gonna be teaching you you know Postgres itself with the PG admin sort of interface I guess and so we can do you know all that locally the good thing is locally and up on a web server it's not much different you're still going to be interacting with it with the same browser front-end solution so everything we learn in this course will be applicable when you actually you know get online and start doing this in real time with web servers we're just not going to focus on that part of this so I think that's it in the next video we're going to jump in and download postcards okay in this video we're gonna dive right in and download Postgres download PG admin download all the tools we're gonna need for the whole course now postgrads is completely free PG and min is completely free so there's nothing to purchase ever which is a great reason to use postgrads you know there are other databases that are free my sequel but for the most part Postgres is thought to be a sort of more advanced database just a better database I don't know how true that is or not but that's we're gonna focus on in this course so head over to PG admin org or you could just google you know postgrads and it'll you know give you a link to this website and this is the site we've got the postgrads elephants that's cool so just come over here to the left-hand side and click this download link now right now we're on PG admin for 4.3 or or or something like that it doesn't really matter if this is changed by the time you've watched this video no big deal if it's changed to PG admin 5 there may be some differences you know major releases have differences but if it's just a different version of four you're absolutely fine they should be able to get by even if it's changed to 5 but maybe not we'll have to see what five comes out so I just come down here and find your operating system you see we have Mac we have C Windows apt that's probably Linux now there are older versions PGM in three I don't recommend you do this this is no longer supported if you need it for a legacy project of some sort you can get it here so that's cool so just click on the one you want we want Windows now here is let's see just want the latest version and I suppose and we're on version 3.4 it looks like of PGM in for not sure why it's a strange 3.4 of version for shouldn't be 4.4 I don't know anyway click the latest one whatever it is and you can see we've got this stuff right here now I'm not sure I've never actually downloaded this I don't think we want this we want to come over here to download a binary we want something that's already packaged already works for us and is ready to go otherwise we're gonna be compiling code and doing all kinds of crazy things we don't want any part of that so now you see we have a list of binary packages and this is Postgres sequel org forward slash download and so we've we've switched to a completely different website this is no longer PG admin and so find your operating system here we have some looks like FreeBSD and open BSD some Linux more linux mac solaris and windows so we want windows and there's a couple of different options for installers windows installer x' we don't want this big SQL we probably want this one up here now the thing about this one 1 in is it'll try to install the stack builder thing it has all kinds of other things in it and we don't need any of that so when we get right down to it we'll unclick the stack builder thing and when we're installing this but I'll talk about that later so just click the download the installer thing and it asks for the version of post sequel that you want we'll just get the latest one version 10.5 if it's 10 points something else that's fine to just pick the latest one and our operating system is Windows x86 64 bit so you're either on a Windows 32 bit or 64 bit chances are you're on a Windows 64 bit there just aren't 32-bit computers around anymore I don't think so once you've selected that click download now and box should pop up shortly okay so you can save this anywhere I'm just gonna save this on my desktop and you can see there's a one next to mine because I've already downloaded this in the past and in fact I'm just gonna navigate to that right now so I'm gonna head over to my desktop this is off-screen on my other monitor so you can't see this but you'll have to wait till the file downloads you can see down here and then once it does you could just click it well it's almost downloaded already so maybe we'll just wait for a second here I mean it's speeding right down um yeah okay so it's done most okay all right so when you see this it's done downloading you can just click it here or you can go to your desktop and double click it whichever you prefer I'm just gonna click this and it's a relatively painless installation but there are a couple of little things we need to look at so as soon as it pops up here okay so the first thing that it's installing is the Microsoft Visual C++ 2013 redistributable for the x86 you know 64-bit computer you may already have this on your computer I probably do but it looks like it's installing it anyway and I'm just going to go ahead and pause this and we're going to wait for this to do its thing and we'll pick up as soon as it's finished okay so that finished doing its thing and now we get this set of Postgres sequel sort of installation screen so we can just go ahead and click Next and it asks you where you want to install this it really doesn't matter so I'm just gonna leave it in Program Files post-grad sequel backslash 10 but if you want to put it in like C postgrads or something you can go ahead and do that no big deal next now here is where I was talking about earlier this stack builder thing we don't need this it says it may be used to download install additional tools drivers applications bla bla bla we're not going to be doing anything like that in this course so we can get rid of that now we don't also we're not gonna be using the command line but you need this to install the rest of these things if you read through here it says it's a required option so we leave that checked and we definitely want PG admin for and we definitely want posts equal or Postgres sequel server and so we're good to go click Next and now it's gonna ask you where you want to save your data I recommend just doing the default in the same sort of directory structure you know Program Files post sequel 10 slash data heading click Next now it's asking for a password for your main Postgres database so I'm just gonna type in password strut again PA SS wo rdu one two three PA SS wo Rd one two three now obviously you're gonna want to pick a stronger password if you're doing this for like real world application if you're going to deploy this to a web server somewhere pick a strong password but we're just sort of messing around and learning this so I'm just going to pick password one two three but whatever you do pick write it down because we're going to need it a little bit later to connect to the database so click Next now it's gonna ask you what port number the sequel server should run on and it's 5432 that's the default write that down as well you may need that in fact you're definitely gonna well we'll see you know we're gonna enter this into the web browser to actually connect to PG admin for but I think it'll put it up there for us so we don't have to actually memorize this but write it down anyway just in case but this is the default port click Next this is asking for the new database cluster the locale we just want the default it's asking for like the actual country I guess so it was default okay so here's a summary of everything now here's one more thing to sort of note the database super user name the user name is Postgres a lot of times you know if you have any sort of experience with other databases like my sequel your user name is likely going to be root not so here the one they've given us given us is Postgres just out of the box that's the way it is and so just sort of keep note of that - and I think that's it we can click Next it's ready to get going click Next again and it's actually now installing now this actually takes a little bit of time so I'm gonna go ahead and pause this and we'll pick this up as soon as it finishes doing its thing ok so set up his finish installing post sequel on your computer click finish and we are done so in the next video we'll fire this thing up and take a look okay before we get started and start playing around with PG admin I want to take just a couple of minutes to talk about sort of the concept of databases I find that a lot of people have a hard time sort of conceptualizing what a database is you know it's sort of an abstract abstract thing it exists often up in a cloud you're not quite sure what it is what is a database and I found this is a pretty good way to explain just what a database is here I've got Excel this is just a basic spreadsheet and most people are familiar with spreadsheets a pretty basic basically this is a database really when you get right down to it a database is just a collection of information and that information is sort of contained in rows and columns so this is a row and this is a column right so in database terms we call rows records right and rows go together so everything in this row goes together this is a row for John elder this is John elders email this is John elders age this is John elders address his city state zip code everything in this row goes together now everything in this row is different every column is different this one has text this one has numbers you know those are different types of data or data types so when you're dealing with data databases it's important to understand the different types of data types and generally speaking data types fall into a couple of categories you have text of certain kinds and you have numbers of certain columns there are other data types as well and we'll probably talk about that later you know dates things like that but strings of text and numbers and columns designate those data types you'll notice everything in this column is a first name and everything is of the same data type columns in database language I guess are called fields so columns are fields rows are records records and fields and anyway columns like I said they have to have the same data type and things in each column don't go together right here we have Johnson Smith elder those are three different people they're not the same person but they're all of the same data type and they're of the same thing you know these are all last names you know this column right here these are all email addresses they don't they don't belong to the same person they belong to you know in this case three different people but they're of the same data type and the same sort of thing right the same thing here age these are all numbers they're all ages each age belongs to a different person so if you think about if you're sort of confused of what a database is this is all it is a database is just a big thing of this it's just a big spreadsheet it has a little bit more functionality than just a basic spreadsheet but databases are made up of tables in this case this whole thing is the table all of these boxes right the excel itself is the database right but think about a spreadsheet you don't really do anything with the database it's so if you're doing all this stuff here in this table you know you're adding stuff into each row and column the database itself you don't really ever do anything with it just sort of holds all your tables and that's true for postgrads my sequel any sort of database a database is just a big container for a bunch of tables and you know like down here with Excel you see these different sheets if we click here we have another sheet and we can put all kinds of new information in this sheet too same thing with this sheet these are each tables and a database can have as many tables as you want in our case here you see we have three you know we can easily add more if we want same thing with postcards or any database you work with you can have as many tables as you you might have a table for past customers you may have a table for future customers you know leads that you haven't converted yet you might have a table for newsletter subscribers or email list subscribers of some sort you might have a table of customers who have picking you up on a special sale offer or something you know you can have dozens hundreds thousands of tables and they're all contained inside the database and the database itself we don't really do anything with it just holds everything so anytime you're gonna do something with a database what you're really doing is something to a table and what you're really doing to the table is something with a row or a column and rows our records they all exist together so everything in this row belongs together everything in this row belongs together Tim Johnson Tim Johnson's email Tim Johnson's age Tim Johnson's address they're all Tim Johnson things columns on the other hand are of the same data type and of the same thing right they're all addresses they're all ages and if you just think about it like that that's all it is that's all really databases are the act of putting stuff into these tables these columns and rows the act of taking things out creating little reports based on the stuff that we pull out that's all databases really are that's all they're really used for and that's all we really need to learn what to do so and that's what we're gonna learn for out throughout the rest of this course how to do those different things with Postgres and sequel now the language the sort of programming language we use to do things like that is called sequel it's called stands for structured query language every database has its own sort of little version of sequel Postgres uses Postgres sequel so we'll get into all that later on but yeah that's really all there is to it so if you're confused about databases hopefully now you have sort of a better picture in your mind of what a database is and it's you know it's a lot easier to be able to visualize a spreadsheet when thinking about doing things with databases because that's something tangible you can go oh yeah I know what a data there I know what a spreadsheet is rows and columns that's easy and that's all databases really are so in the next video we'll dive in we'll start learning the Postgres PG admins system and should be fun okay so we've downloaded this thing let's fire it up so just start this thing head over to your Windows Start menu and go to all programs and just scroll down till you see Postgres equal ten should be highlighted since we just downloaded it and installed it go ahead and click that and then you want PG admin four right here so just go ahead and click that and this is the screen that comes up now you'll notice it came up in my web browser I'm using Chrome doesn't really matter what browser you're using but look at the address it's at 127.0.0.1 and then semicolon four nine seven six two four slash browser not really sure why the four nine seven six two is on there it shouldn't be well we'll see in just a second actually so this is the screen and you'll see you're currently running version 3.2 uh however version 3.4 is available we can just ignore this this is because of the installation or the Installer that we use just had a slightly older version in it we don't really care at all though it's all pretty much the same so this is the main dashboard and you can see there's some things you can configure it there's some you can add a new server but what we want to do is come here and just click on this and see the little plus sign here click on that and then we see this posts equal 10 so again hit that little thing and then we see databases hit again hit that thing and now we see this Postgres and so this is the actual Postgres database that has sort of come with it and we don't really care about this but just kind of neat to look at this what we want to do is create our own database so click on databases right click and create and then database and let's go ahead and name this my database doesn't really matter um look through these parameters we can add different things if we want oops we don't want to do that now when I clicked on this it didn't ask me for a password and I think that's because I've installed and run this in the past it may ask you for a password if you do if it does just enter that password we selected it in the last video you know ours was password 1 2 3 though a little box will pop up but for some reason it didn't ask us asking me to do that probably cuz I've installed this in the past anyway so create database and let's go my DB and you can see the actual sequel that is gonna you know use well so let's just click Save and okay took a second and boom we see my DB has popped up there if we click here we get all of these things now all this stuff is overwhelming there's all kinds of stuff and you can you know take a few minutes and just start clicking through all these and kind of look and see what's what none of that's really gonna make any sense to you and that's fine right now we just want to focus on schemas so click on that and then public and then tables and there's no table so this is our database right my DB right here and you know databases are composed of tables and inside of the tables there are columns and inside of the columns there's actual data and we're gonna get into all that shortly but this is pretty much it here's our database here's our tables we don't have any tables so we should probably create some and we're gonna do that very soon but yeah this is pretty much it this is the overall interface we're gonna be dealing with for the rest of the course this has everything it's in our web browser very cool so it's already connected to post sequel as we can see I'll say let's look through here there's preferences you can you can check out all this stuff if you want to sort of play around with this I always just leave everything default there are objects we don't care about that query tool look at that a little bit later there's a online hope there's a help file that comes with that it comes with this I just looked real quick yeah when we went to the Windows Start menu to launch this thing we clicked on the post-grad sequel 10 folder and then we saw that PG admin for that we clicked on to start this directly under there there's a little folder called documentation and there is a PG admin documentation there's a post-grad sequel documentation and there's some installation notes we don't really care about but if you look at the PG admin documentation this is it so there's all kinds of stuff you can read through here if you get stuck somewhere that's cool what else let's look at the the actual Postgres sequel documentation as well all kinds of stuff if there's something in this course that I don't cover and you or you're curious about you can come through here and this documentation and probably find it very quickly so that's cool as well so that's pretty much it this is a year's user interface none of it means a whole lot to you right now but in the next video we're gonna jump right in and start building a table start adding data and then start playing around with this and should be fun so we'll get started with that in the next video okay so we're back at PG admin for here this is the user interface and head over here to this my DB database that we just created and click the little plus thing if the drop down come down to schemas and then click on this public one and then come down here to tables and right click and let's create a new table and we can call this anything we want let's call this customers we're gonna create a customers table right I'm gonna do lower case just because I like lower case and you can see right off the bat we can designate we can add different columns so let's go ahead and add just a few columns to get started here click the little plus button and then come down here and I like to do click this thing here so let's give this a name of ID we want an ID is this a primary key yes primary keys are if you're not familiar they allow every sort of row in our database to have a unique ID so a lot of times if you want to for instance delete a row and you say okay delete John right there maybe 30 John's so you don't know which John but if each row has a unique ID you don't say delete John you see it you say delete ID 407 right there's only one ID of 407 we know exactly which one to delete so it's always a good idea whenever you're designing a database to give give a a column with an ID that's a primary key so we'll go ahead and do that definition the data type so we want to make this serial and big serial new serial is just basically a number in sequence 1 2 3 4 or 5 right that's a serial that's just what we do not null yes we want this to be not null so we want it to be impossible for there not to be a value right no means nothing so it's not like we can't have a number there every row has to have an ID and that's what not no not null means so go ahead and do that variables nothing really we need to do their security nothing we need to do their so for the data type big cereal if you're not familiar every column has a data type and everything in that column has to be of the same data type and here we have big cereal another one would be integer you know one two three four five that's an integer we might have a var car and that means uh you know characters for Strings of texts and things like that so we'll get into all this in a bit but for now I think that's fine come down here and click Save it just created one thing I guess we should add it more okay well well play around with this in a bit here but now we can here we can come over to customers and we can right-click or we can click the little plus and it expands and now we get this we can see columns and we can expand that and there's just one column of ID and we can look at the properties of that if we want we'll see the data type is a big integer it created a big integer when we said serial it did the thing so that's cool not know yes here we have the sequence cool securities equal general okay so cancel that now we can take a look at this column by right clicking and let's see actually we need to come up to customers and you edit data and all rows and if we do that we get this little query tool that pops up and it writes the sequel for us so it's saying select from this public customers which is this public thing dot customers which is the table and it's order by ID and you can see there aren't any in there yet so that's cool now we want to flush this out a little bit so come to the column section and create a column and let's call this name and next comes to definitions and let's make this character varying and that's just PG admins way of saying far car so if you're familiar with sort of typical database datatypes VAR car is one of the main ones that you're always gonna use it means you know characters of varying lengths and here you can designate the links so if you want to say 255 you could I tend to just leave this blank if you leave it blank than it allows it to be any sort of size that you want you know if you put 5 or 4 here and then somebody tried to enter a name with more than four characters you get an error and you wouldn't be able to do that so that's what that is we don't care if this is not and also we'll leave that no variable security sequel there's nothing really to do there alright so go ahead and save this so now we have ID and name let's create one more just for now and let's give this one we'll call this email let's see the definition we want I just type in var and we get this characters varying you that's good click Save ok so now we can come back to customers right click click this view edit and all rows and we see now we have the ID and it has a PK next to it meaning primary key name and email and it also shows the data type underneath it so that's cool too so one cool thing about this is from this screen we can actually type in new data into the database now you can import data in and mass sort of or you could just you know start typing stuff in so we'll look at that in the next video okay in this video we're gonna start to add some data to our table so there's a several different ways you can do this and I mentioned in the last video that you can import database tables into PGM in really easily and to do that we're not going to do that because I don't have anything to import but I'll show you how to do it so head over here to our customers table right click and just come down here and see this import export you can also export as well and we'll look at that later on but if you want to import a table you just come here click import and then just select whatever wherever the file is located on your computer you know and you can pick the different format CSV txt binary or whatever and click okay and it'll import it we're not gonna do that like I said instead we're gonna do by hand a couple of different ways and the first way is just gonna be sort of manually typing this in so you can come to our customers and then our column and right click let's right click on customers right and then come to this view edit data we've already done this once and just click all rows and it'll sort of refresh and we get this little thing down here that lets us type in stuff data into our our table so this is really cool so we can come to name well let's go John elder oops Jo HN elder and email we want John at Kota me.com that is my email if you want to contact me at any time that's cool and then we just click Save and now this hasn't actually been committed into the database yet to do that we need to come up here to this little Save icon right here and just click this and boom you see it gave us a 1 automatically and that's because this is we've designated this as a primary key and labeled as not null so and also labeled it as a serial so it adds it automatically for us which is very cool we could do it by hand but we don't have to so let's do another one real quick let's go Tim Smith and this let's go Tim at smith.com save and then come back up here again and commit it click save you see down here it says data saved successfully so that's cool awesome cancel this so that's how to do it sort of manually by typing this stuff in now of course we can always just write sequel to do this as well and you know if you're familiar with sequel that's sometimes just as easy or easier so what we want to do is come over here to customers right click and then come down here to query tool and the query tool is cool it allows us to run any kind of sequel queries that we want so this is very cool so let's go insert into and now we want to insert into our customers table right so to do that remember this is in our public thing right there so we go public dot and then quotation marks and then the name of the table so customers I spell all right customers right quotation marks and then designate the columns we want to insert into in our case it is let's say we want name well we need to put these in quotes so name and then a comma and then email we don't need to do ID right so we're just gonna leave that blank and okay so that's cool now for the value we want values we want Mary Brown and Mary at brown.com I guess now actually I'm going to do single quotes here finicky sometimes with a double quote I like to use single and how does that look yes I think that will do so now we just need to execute this sequel query all right and to do that we use this little lightning bolt button up here you know it's execute slash refresh so go ahead and do that and query return successfully it inserted it everything's fine now if you made a mistake here if you did something weird or wrong you'll get a little error right here so very cool now if you're not familiar with sequel this is the sort of format always of sequel we have our command where we want the command to go and then you know some other thing we'll get in all this later there's all kinds of cool stuff to learn about this but the thing to note here is we have two columns right that means we have to have two fields down here so if we went name email and then you know did this we get an error right so let's let's change this to Steve real quick Steve Brown and click this thing and obviously we get this error because we have two fields here and only one field there so and it's telling us exactly that see it's pointing to this email and it's saying a insert has more target columns and expressions these are the expressions these are the two target columns so very cool you know in that you know we get the error exactly we could see exactly how to fix it and very cool so let's make sure that Mary Mary Brown was added so come over down come down over here to customers right click and this view edit data thing as our friend we're gonna do this a lot like all rows and we say yes Mary Brown has been added Steve Brown has not because we got that error and it didn't add it so very cool Mary Brown has been given a primary key of three automatically very awesome and that's pretty cool so those are the ways that you can sort of quickly add data to your columns remember you can just import a table in mass it's gonna probably delete whatever you have in there already but if you're just getting started and you have a database that you want to use you can import that like that very very easy so in the next video I think we're gonna start walking through different sequel statements showing you how to use them specifically with PG admin and go from there so we've got this all installed we've added a database a table and some columns and we've entered some data now from here on out for the rest of the course I just want to go through basic sequel things see sequel commands and just show you how to do them in PG admin for so if you are already familiar with sequel this is going to be sort of obvious to you if you're not this would be cool either way it's sort of probably what we're worth watching yeah just because there are some little things here and there that PG admin does a little bit differently then maybe you might expect or if you have experience with my sequel or sequel server or some other version of sequel might be a little bit confusing so I'll try and point those out as we come to them but for now we're just gonna jump right in and start learning sequel so we've already looked at some sequel up until now sort of put the cart before the horse with some of this stuff but now we're gonna go through and talk about it so the first thing I want to talk about is a basic sequel select statement so let's head over to our customers table here and click query tool and like I said earlier this is where we can just write any kind of sequel that we want and query the database and get read results so let's do select this is your basic select statement and you can see I've capitalized you don't technically have to but that's the convention so we want to select everything from and that we designate our table public dot customers all right so if we click this boom we get absolutely everything that's what this star means everything from our table here so you see we have our ID our names and our email columns and we have three rows and it returns all of that so that's cool you can also designate specific columns that you want to return so we can select name from public customers appear and execute and boom we just get the name column so that's often useful if we want more than one column we can just put a comma and then name the other column and if you've forgotten which columns are in your table you can always just sort of look here and click on this columns and boom ID neat name and email right there so that's useful all right click this guy everyone we get name and email very cool if you forget the comma here's an interesting thing it will just return name that it'll give it a new name instead of up here it's saying name it'll it'll list it as email that's kind of interesting so let's click this and see and you say email but it's actually names so this is useful if you want to sort of create a report type of thing and you want your header to be a little different than the thing is actually called so we could say we can go custom customer names for instance oops that works and up here we get customer names sort of like a pseudonym pseudonym type of thing I guess and kind of cool so that's select you know just a very basic sequel thing in the next video we'll look at the where clause okay in this video I'm gonna talk about the where clause and the where clause lets us search for specific things within our select statement so you can see I've added a few more rows of data just to give us a little bit more stuff to play with Tina Smith Tina Smith calm Jerry Johnson and Jerry at Johnson comm so now we have five rows so let's look at the where clause let's head back over to our customers table right-click and go to query tool and it starts out like what we did in the last video we select and let's go select everything from public dot customers and here we designate our where where and then we enter some condition right so we can say we're ID equals three right and so let's run this and we get Mary Brown whose ID is three so you know you can use any kind of sort of comparison operators here so we have the equal to sign we can go not equal to write with an exclamation point in front of it if we run this we get 1 2 4 & 5 so it's returning everything that's not 3 with the ID right so 1 2 4 & 5 you notice 3 is missing we can use greater than or less than where ID is greater than 3 so we get four and five we can go greater than or equal to right so we get three four and five same thing with less than or equal to one two three or just straight out less than - so you know there's another way to do not equal to we can do sort of these two brackets like this and we get the same thing one two four and five so that's cool you can do like so let's change this to name is like and then let's go John now you'll notice when you run this we don't get any anything returned because we have to be very specific when we use like we'll talk about that in a bit if we change it John elder then we get John elder likewise you can use equal to where the name equals John Eyre elder and we were on this we get John elder again so you know this is not great because say we want you know we don't know we don't know who the last name is we just know there's John's in there so if we run this we get nothing so there needs to be a way to sort of search less complete information I guess and we do that with something called wild cards and we'll talk about those in the next video but for right now I just want to stick with this where you know that's pretty much it you know if you if you're familiar with programming you're familiar with if statements and if statements have conditions conditional statements they're called and they have comparison operators and the comparison operators in any computer programming language are basically what we just looked at equal to less than greater than etc and so you can use all of those things in your where clause so it's very sort of useful yeah I think that's all I want to say about that in the next video we'll look at those wild cards to make this a little bit more sort of excuse me an advanced searching functionality I guess and we'll look at that in the next video okay in the last video we looked at the where clause in this video I want to look at wildcards in the where clause so we noticed that it was you know you can search with where but it's kind of not great right if you know specifically what you're looking for you can you can do it if you're looking for a range of numbers greater than 3 greater than 200 something like that it works very well if you're searching for a name it doesn't work well at all so to get around this we can use wildcards and the wild-card is the this percent sign right so basically what this is saying is anything that starts with John and then has anything after John that's what this wildcard means so if we run this we got absolutely nothing oh we're name Eagles we need to change this to like so where the name is like and it starts with John and ends with any old thing so now if we're on this boom we get John elder very very cool now we can put the wild card first and that means anything it'll return anything at all so if we just run this we get a list of everything right now I'll look through here we have Smith and Smith we have Tim Smith and Tina Smith so what if we want to return all the names ending in Smith well we would just go Smith right put the wild-card first this is saying anything in front of Smith and then ending in Smith return that so if we run this we'll get two things Tim Smith and Tina Smith very cool if we put this behind here and run this we get nothing because there's nothing that starts with Smith and then has stuff after right so that's sort of interesting so let's run this again let's just return anything and okay what else can we do so we have the before like that we have the after like that so this will return John and then anything we don't have any anything like that what about stuff in the middle so we can do that by using two of these things and then putting between them anything we want to search for so let's see what do we have h n right so this is anything before and then H n and anything after so this should return John elder Johnson Jerry Johnson because there's an agent an N and H and an N and Johnson so very cool we can do at to return any email address from change just email right boom we get everything let's see there's two Smith's smith.com so we can go at Smith if we want it to turn the to Smith's because they each have Smith in the middle right there's Tim and and dot-com after but in between is at Smith for both of these so very cool so that is wildcards very very useful it might take you a second to memorize the before and the after thing might switch those around before you oh yeah no no that's how that works but very very simple once you hang of it in the next video we're going to look at and or and not okay in the last video we looked at wildcards in this video I want to look at and or and not and basically these allow us to string together more than one conditional statement in aware and if you're familiar with computer programming if-else statements conditional statements comparison operators the same sort of idea exists there you can string together as many comparison operators in a condition as you like using and' and or' and that's really all all it is so let's go let's say let's stick with the two Smiths we want to return something we're email is like at Smith and now we could just add another conditional statement on here so and ID equals four let's say right so for this to be true and return something this has to be true and this has to be true so if we run this okay so we get Tina Smith and for right so we can do the same thing we can go or but run this we'll get the look at two things and the reason why we get two things here is because for this to evaluate into true either this has to be true or this has to be true so in this case this is true with two records right Tim Smith and Tina Smith this is only true with one record but it doesn't matter because this is true with two so it shows to both of these don't have to be true in order for this to return something so it's gonna come through here and it's gonna find all of the records that have Smith in the middle of it and in our case we have two or all of the records that have four well there's only one record that as far as the ID but this one overrides it because it has more so those are those are our and and or so finally we want to look at not so let's get rid of that and not as a little bit different not goes right behind the where clause so and not as just a negative right so we're a thing is we're saying where it's not right so we're not email-like so this is gonna return everything but at Smith so we have one three and five the two Smiths are not shown here so um relatively simple and and or I tend to use and a lot because I'm stringing things together sometimes you use or I hardly ever use not but that's just sort of me so that's an or not in the next video we'll look at order by okay in this video I want to look at order by so let's just get rid of this and let's just run this real quick here so select everything from our customers database table and we get one two three four five right now there are all kinds of times when you need to order these by certain things so to do that we just use order by and now designate what you want to order this by so we want to order this by let's say the name column and now you need to designate a sending or descending so a sending is a SC these sending is des des see right so what does that mean well a sending is like going up so that would be like one two three four five six seven eight nine ten descending is going down ten nine eight seven six five four three two one or in this case alphabetical ABCD efg is a sending g8 ABCD efg GF e c DB a whatever I mess that up you get the idea so let's go a SC a sending and see what we got notice well we'll be able to see here by the ID things have been changed so a sending J J M T so J comes before M in the alphabet M comes before T in the alphabet likewise if you just leave off the ascending we get the same thing so ascending is the default if you want to change it you can change it to descending so we should see 4 2 3 1 5 4 2 3 1 5 t is last T t int I M isn't excuse me next to last M is before that Jo is excuse me before that and je is even before that so um that's you know that's cool let's do the same thing with ID just to make it easier to look at right so descending IDs so this is numerical we're gonna order by this column and we want it to be descending so highest to lowest 5 4 3 2 1 right we could go lowest to highest one two three four five a very very cool and pretty simple yeah and that's pretty much all there is to it so in the next video we're gonna look at insert into so that is the first half of the course I hope you enjoyed it if you'd like to see the second half of the course we have a couple of options for you you can take this course at you to me and there's a coupon code in the description below for 97 percent off so I think you'd pay $9.99 for that course or you can sign up at my website go to me.com total membership there get you all of my courses including this one and over forty other ones and membership there is usually $49 but if you use coupon code youtube you get another $22 off membership so they pay just $27 for all of my courses you also get all the PDF versions of my best-selling coding books have several best-selling coding books on Amazon you get those for free you get access to me access to other students you can ask questions it's just a really cool community so coupon code are linked below just go to Kota me.com use coupon code youtube and that should be cool so my name is John elder I hope you enjoyed this video and we'll see in the next one

Channel: Codemy.com

Views: 136,718

Rating: 4.7065067 out of 5

Keywords: postgres, postgresql, pgadmin, postgres pgadmin, intro to postgres, postgres database, postgresql database, postgreSQL intro, postgres and pgadmin, learn postgres, learn pgadmin, intro to postgres with pgadmin, postgres course, postgresql course, pgadmin course

Id: Dd2ej-QKrWY

Length: 55min 24sec (3324 seconds)

Published: Mon May 27 2019