Getting started with GitHub Codespaces - SQL教程

- [Instructor] Okay, we are almost ready to get started actually working with SQL. But before we do that, I want to spend a little time talking about the development environment or the learning environment that we're going to be working with. We are going to use a service called Codespaces, which is a virtualized environment that's provided by GitHub. And, GitHub, if you're not familiar with it, is a service which provides repositories for version-controlled code, and it makes it very easy to share and collaborate on code development. Well, that's GitHub. GitHub plus Codespaces goes a step further by providing an environment in which we can execute our code. So we are actually going to be using Codespaces for installing and running a Postgres server and actually interacting with Postgres using our SQL commands that we're going to be learning about in this course. So the first thing we need to do is to navigate to the GitHub repository for this course. Now, I've highlighted the URL here. And the URL is github.com/linkedinlearning /intermediate-sql-for-data-scientists-5925685. So that's the URL where you'll find this. Now, when you first go to github.com, if you aren't logged in, you may be prompted to log in. But once you are into GitHub and you see this page, you will be able to see a couple of things. First of all, this is a standard GitHub repository landing page. And so, for example, we have a list of different files and folders in here as well as a README which provides some basic information about how files are organized. We are primarily concerned with exercise files, and I'll just show you the structure of that folder. In here, we have folders associated with each chapter that has exercise files, and within each chapter or each folder, there's another set of files. Now, most of these follow the naming convention of the number of the chapter plus the number of the video within that chapter, and that's the name of the SQL exercise file. So, for example, 02_02 is Chapter 2, second video. This is the exercise file associated with that. We do have one exception in this folder called Exercise Data, which is actually the file that we will use to create our schema and load some initial data. So we'll be talking about that shortly. But that is basically an overview of the exercise files. And now what we need to do when we're ready to start working with Postgres is to go to this button labeled Code. This will bring up some Codespaces. Now, if you have some existing Codespaces, they may be listed here, or you may just see this one option of Codespaces. So I'm going to create a new Codespace by clicking the plus. And now we are going to go through a series of steps which Codespace is executing for us to create a virtualized environment for us. Now, this virtualized environment will include an editor or a IDE that we can work in based on Visual Studio, and it will also include an extension called SQLTools. And SQLTools is where we have Postgres installed. And we will be able to interact using both our IDE and a command line for executing SQL commands with the Postgres that's installed in Codespaces. Now, this operation is going to take a few minutes, so we may edit some of this out, but we'll be back as soon as the environment has completed setting up. Okay, the first part of the setup has completed now. We have our basic Codespace created and we have our files loaded. And the next operation will be installing Postgres. Okay, it looks like we are done with the installation, and I just want to briefly describe the environment within the Codespaces. What we see here in the main panel is the README that we saw in the GitHub repository. So this is a good indication that we have loaded our repository. And we also see, over to the left, we see a hierarchical environment where we can work, and we see that under our workspace within Codespace, we have some of the folders that we saw in our repository, like the dev container. But we also have exercise files. So we see, for example, we have the files from Chapter 2. So all of this looks like, you know, exactly what I would expect once we have our repo loaded. Now, I'm working under the explorer, and we're looking at the contents of the GitHub repository that have been brought into our container. Now, another tool that we're going to use is one called SQLTools, and it has this little barrel icon down here. If you click on that, you'll see that we have a new prompt here or a new working area called Connections. Well, one of the things we want to do is click Add New Connection, and what we're going to do is create a connection or our ability to communicate between our container and our Postgres database. So I'm going to select, the kind of connection I want to use is a PostgreSQL connection. So I'll click on that. And we can give this a connection name. So I'm going to call it data_sci. I'm not creating any connection groups. My connect string is correct. The server address is correct. Port 5432 is correct. Now, we do have to add database, username, and user password. The database name is postgres. The username is also postgres. And for the password, we're simply going to select Save as Plain Text in Settings, and we're going to enter the password. And the password here is postgres. Now, this is not standard security practice, but we are in a learning environment, so that's why we are doing things like using the default password for the Postgres database and saving it in plain text. Nothing we are working with requires any more security than this. So I'm just going to then scroll down through the other options. We don't need to change any of the other default options. And I'm just going to go over to the bottom. There's Save Connection. I will save that connection. And let's connect now. And if we look over here, back to the left in the Connections work area, we'll see that we have an icon with the Postgres icon and a green dot and data_sci. I'm going to click on the arrow over on the left to expand hierarchically on this. And what we're interested in is the schemas. And what we see here is we have four standard schemas that are created by Postgres. One is called public, and then also there are two related to tiger and one to topology. These have to do with extensions like geo-encoding extensions related to Postgres. We won't be using them. You can delete them if you like. I'm just going to leave them, but I just want to explain why you might see these three schemas that you might not be familiar with. They're fairly new additions to the default Postgres configuration, but they're definitely not going to be in our way. So what we have now at this point is we have a connection to the Postgres database. If we look in the public schema and drill down to Tables, again, there's some default tables that are created. So basically what this means is, we have successfully navigated to the course GitHub repository, and we have successfully launched Codespaces, including a Postgres server that we will be working with.