Example: PostgreSQL Create DB and Table

for this particular session we're going to look at creating a database on the PostgreSQL database here and in order to get into the database once we've installed it this assumes we've installed it it's on a computer and we can find it here on a computer going to all programs scrolling down here we should be able to find Postgres database and we could look at the admin tool the SQL shell but for right now I'm going to take you directly from the command line to the SQ so if you type in CMD here we'll bring up the command prompt or Windows and from this particular command prompt let's log in to the database and do that we type in P SQL that's our command - you for user and the user name is Postgres and of course the database is the local host database since we've installed it on our machines for the first time probably this will be the local machine in which the database is on if it was on a server we would have to put the server address in there or the server name in there but for right now we should be able to get it on our own system so we hit enter and it may give us a little warning message for Windows type machines saying the 8-bit characters might not work correctly but that should not be a problem and notice here that we're in the Postgres database it shows the name of the database in which we're in and then it gives us a a pound sign to show that we can start working in this particular database the first thing we want to do is we want to create a database in order to do that let's just type in right here create database my DB with a semicolon and what this will do is although we are in a database called Postgres it will give us another database called my DB that we can work in okay so we hit enter make sure we also always have a semicolon behind our commands otherwise it won't work correctly and it should come back create database so we now have that database now we could you look at all our database by using what here a front slash and a list or L and it should show all the databases that we have I have a few on here you probably won't have that many and we can just scroll through all so once we've created a database let's create a user so that we can work in that database so we'll create a user let's say Bob with password my pass and a semicolon at the end and notice that inside single quotes we'll need our password whatever that happens to be I'll just call mine my password right now and then we've created that particular user now if we don't like the database that we created we're done with it until II we won't use this command here that I'm going to show you but if you needed to get rid of a database drop database my DB which I'm not going to do right now but that would get rid of the entire database I will move back out of that okay so we've created a database now we want to connect to that database we want to get inside of that database so that we can work in it and we want to create a schema and a schema is encapsulating object that will hold a bunch of tables that are somewhat related and so let's connect to the database and then build a schema so we'll use the backs the back slash C and that means connect to my DB in a semicolon at the end okay so now notice that down here on the command line we see that we are inside the database called my DB and we can work in that particular database there so with that said let's go ahead and create a table I actually create a schema more can put tables and inside of tables we can put data so a schema is it's not required but it's helpful because you can sort out tables and have tables with certain properties around them and then certain security requirements around a whole group of tables it will create a schema here that we're going to put tables of my friends in and we'll say create schema friends and a semicolon sit down I'm creating this big object hold friends and we'll put some tables in there okay so I have that schema there called my friends now let's create a test table so create a table in the schema friends and it's going to be called test right now and this particular table is only going to have two fields if we will perhaps one field to start with and that particular table let's say has the first name of the person and that's going to be a char character type fifteen possible characters maybe last name and that will be a character type field and maybe last name a little longer so okay so that would be how we would start creating a table called test if you we will and then we see that that's created and notice that that table is created in the schema friends the schema friends here and the table is called test so let's let's do something here let's create another table oh actually we'll select from them so we'll select information now notice I did not put any friends in there no first night no last names right now so we're missing any friends but we could still select everything that's wildcard star Asterix and we can select everything from friends which is a schema test and then a semicolon so show everything in that table and of course since I didn't put any friends in there we don't have it shows zero rows but that would be our first select statement and while we're here let's also look at something else we can do with this we can use the the backslash D and type in front type in test and it should show us here actually we're missing the schema so we'll type in the schema here which is friends test and it should describe that database for us one thing if we've used the schema we should put the schema dot test first so that we were able to see our particular table that we've created let's go over here and take a look at how that looks if we went to the Postgres admin tool and we'll pull up the Postgres admin tool here and we're going to go look at that particular database if we will and we see the database called my DB we see it has a schema of schemas called friends and inside our schema friends we have a table and the tables called test and table have two columns a first name and a last name so we can see we've created my DB we've created a friends we've created a table called test and which has two columns and if you think about these tables very similar to an Excel spreadsheet except we'll be able to enter relate all these spreadsheets together if you will we've got a first name and a last name that we can now put records and so a bunch of first names on a bunch of last names we'll be able to go into this particular database okay so we'll shut the admin tool down and look at some more things here okay so here let's take it one step further and let's insert some data so insert into friends dot test values so we're going to put some values in there and let's go Mike we have a first name and Smith it last night and we insert into the database there and let us select a star from friends dot test and we see one row there Mike Smith okay so for this particular video what we've done was we've created a database we've logged into the initial database that's created when you install it Postgres from that initial database we could have done a lot of things but we decided to create our own database for our own information we created a schema to encapsulate other tables we've created a table called schema called friends and we've created a table with in there called tests and we created two columns in their first name and last name and then we also created a one record we could have created multiple records but one record called Mike Smith and that's how you initially get this Postgres database up and running

Channel: Robert Rich

Views: 228,813

Rating: 4.9215684 out of 5

Keywords: postgresql, database, postgres

Id: fD7x8hd9yE4

Length: 10min 41sec (641 seconds)

Published: Tue Jan 14 2014