Exploring the databases and running SQL code - SQL教程

(upbeat electronic music) - [Instructor] When I first connect to the code space I'm prompted to install the SQLite Viewer extension. I'll do that because this extension is useful for exploring our databases. I'll click install. Then here in the search bar in the extensions area, I'll remove this text. There's also another extension pre-installed in this environment that lets us run SQL queries against SQLite databases. I'll spend a little time organizing my screen here. I'll click back on the explorer in the left side bar and find the notes.sql file to start out, and I'll mouse over the tab here and right click and choose Split Down. This makes another pane down at the bottom of the screen which I'll use for database results. I'll close my terminal and I'll slide this pane down for now. I'll also point your attention to these two directories here, 01_restaurant and 02_library. These or where the databases we'll use are stored and where the challenge and solution files for each set of exercises are. The challenges are in one directory and my solutions are in another. This file here, for example, is the challenge for the first exercise with a prompt, but no SQL code and you can find the solution under a similar name in the solutions directory which includes the SQL code I wrote in response to a given challenge. So if you want to peek, you can, and if you don't just don't click on the solution until it's time. More importantly, right now though, I'll click on the restaurant.db file here. That's the SQLite database that we'll use in the first set of exercises. Over here, I'll click open anyway, and here at the top I'll choose SQLite Viewer. This is the extension we were prompted to install earlier and it's what lets us browse around but not edit our databases. Here on the left side I see a list of tables in this database. I'll click on one and adjust this a little bit and here I can see the fields in the table and their data type. Over here on the right, when I have a table selected I can view the records in it. Being able to explore the structure of this database and its friend called library.db in the next set of exercises will be an important tool as you decide how to create SQL statements to solve the various exercises. Before we move on, I want to show you how to run SQL statements here in this environment. I'll go back to notes.sql and here we see some basic statements. Feel free to use this file for notes or anything else throughout the course. I'll highlight this statement here and I'll right click and then I'll select Run Selected Query. The first time I do that for each SQL file I use I'll be asked to choose which database to use, and in this case, I'll choose restaurant. When I do, notice down here at the bottom, I'll see the results. This is the output of the SQLite database. There's a little pagination control here in case there are a lot of results to display and there are export options here that are useful but that we won't really be using in this course. I'll highlight this other statement here and right click and choose Run Selected Query, and the results of this query have replaced the ones from before. As you plan your responses to these challenges be sure to use the tools available to explore the database and test out your solutions. If you're using other tools to follow this course, like the free program DB Browser for SQLite you can open .db files one at a time and explore them and use that program's SQL Query editing features to write your code.