Workbench 1

[As the video begins, you will see the MySQL website being displayed, with the ‘Workbench’ tab having already been clicked on.]

Speaker: When you first open Workbench, you’re going to see [clicks on the MySQL Workbench app at the bottom], the home screen, and this provides access to the MySQL connections. We also have models [clicks on the second tab on the left to display the models] and the migration wizard [clicks on the last tab on the left to bring up the migration wizard].

So we’re going to be using the first two—we won’t be using the migration wizard. [Clicks on the first tab again.] The connection view displays a list of established connections to local and remote instances of MySQL. The model view [clicks on the second tab again] displays your most recently-used models, and you may not have any here. But the plus sign next to “Models” will allow you to add a new model later.

[Selects the connections tab.] So let’s go back to the connections view. When you click on the connection that you have [clicks on the connection], the visual—it’ll ask you for your password that you set up when you installed Workbench [enters the password]. And you’re going to go ahead and have the view here that we have for the visual SQL, or sequel editor, is going to open. We’ll spend most of the time in this view building, editing, and running queries.

Here are some of the main elements of this editor window. The home tab, you’ll notice up here, never closes and it does allow you to go back [clicks on the home tab] to that home screen that we were on. And then we have that connections tab [clicks on the “root at local host” tab] that was opened when we made that connection as well, so you can go back and forth.

The SQL query tab, and you’ll see that here—mine says, “Query 3,” yours might have a different number—this is a secondary tab that opens by default when you make connection to the MySQL server. They—it provides an area to write statements and to edit queries, and new tabs will open when you do—have different actions happen. And you can also create new query tabs with a File [clicks on ‘File’], New Query Tab [selects ‘New Query Tab’]—see now I have two different tabs that I can put different code or script into each one of those. If you want to close one of them, you can just click on the x [demonstrates]. If you want to save any script that you might have typed in here—so if I had typed in some script here [types “USE salon;” on line 1]—then I might go up and say [clicks on ‘File’], ‘Save Script’ [selects ‘Save Script’], and then I’d actually be able to save that code that was in that query tab [clicks ‘Cancel’].

Let’s see, we have the main menu bar that’s up here at the top—it might be in a different area a little bit in Windows machines. We also have the main toolbar, and that’s these tools here. Here we have a few—just a few of these here—this one will create a new SQL tab [from the toolbar located underneath the tab name “root at local host,” the narrator clicks on the icon to the far left]. So that’s kind of like the new query tab that we just saw. This one [referring to the second icon in the toolbar] will open an SQL script that we [clicks on the second icon] might want to open up into a tab. So I’d go here and find maybe an SQL file that I had already saved [clicks on the “v_art.sql” file and selects ‘Open’], and I could open that up as well [closes the tab containing the file that was just opened].

Let’s see, the last one here is good. [Clicks on the last icon in the toolbar.] This one is a reconnect button, and I’m already connected right now, but if, for some reason, you have lost a connection with your, um, connection that you had before, you’d notice that some of these icons, like the yellow lightning bolt, would be grayed out, and that would be a good sign that you are not connected anymore. And you could click this reconnect icon here at the end of the main toolbar and it would allow you to sign back into your connection.

Alright, so this area in the upper-right shows three panels that can be toggled between viewed and hidden. I’ve hidden the side panel—if I toggle it back on [clicks on the icon to the very right], you’ll see it’s the snippets section where you can add, save some of your code, and run pre-made code, but we’re not going to do—be doing much with that panel, so I’ll usually have that one hidden [hides the panel again]. But you can also see it may hide your action output panel. It can also hide the schema list that we’ll talk about here in a minute. So usually I’ll have these two open [referring to the two blue panels in the top-right area] and this one closed [referring to the one on the far right], but those will toggle on and off the different panels on the side in the bottom.

You’ll also notice that we have a gear up here next to those panel toggles—this will get you to the Preferences. If I click that you’ll see you get into Workbench Preferences [clicks ‘Cancel’]. You can also find that in, um—you Windows it might be a little different—but you can find it through the main menu as well.

But we’ll go ahead and click on the gear here [clicks on the gear button next to the panel toggles in the top-right portion of the screen] and open up the preferences. And we are going to do a few things here just to have it set for the rest of the course. When we create foreign key connections and our models later, it defaults a few different things that I want to turn off or change, and it gives us—we want it to be just the table name with ID—with the underscore (_id) next to it for the primary key. We also want to not have a table prefix on our foreign keys, so we’re going to change that right now, and I do want you to go ahead and do this on your workbench as well.

First, I want to show you something here in the SQL editor area [clicks on ‘SQL Editor’], so click on ‘SQL Editor.’ If you scroll all the way to the bottom [scrolls down to the bottom and clicks the box next to “Safe Updates (rejects UPDATEs and DELETEs with no restrictions”], you should have the ‘Safe Updates (rejects UPDATEs and DELETEs with no restrictions)”—that should be checked. It should probably already default checked on yours. So make sure that one stays checked—that’s going to keep our data safe. But the reason I’m showing you this area is because later, when we do updates and deletes and even dropping things, we’re going to have to uncheck that for those to work, but that is usually going to be checked.

Let’s look at the modeling defaults [clicks on ‘Defaults’ under ‘Modeling’], and here’s where I want you to make the changes. Mine have already been made, so I want you to make yours look like mine. In this ‘PK Column Name’ field, delete what you’ve got there, or change it to what—to say percent sign table percent sign underscore id (%table%_id). And what that’s going to do is it’s going to take the default table that you’re working with and then precede that with an underscore id at the end and that—it’s just a column name for the primary key that is something that I’ll be using a lot in class.

And then under ‘Column Name’ in the ‘Foreign Key/Relationship Defaults’ section, under ‘Column Name’ I want you to put—take off the “table” part of this that it’ll say percent sign table percent sign underscore, and then have percent sign column percent sign—I just want it to be percent sign column percent sign… so that you don’t have a big, long foreign key name. That just makes it so the foreign key and the primary key have the exact same name, which will make doing joins later a little easier. I know none of this is really making sense right now because we haven’t done joins and haven’t even created a model yet, but this will help you if you’ll change these two values to look like mine here, it will help. Make sure that you do click ‘OK’ when you’re done here so that it will put those into effect. I’m going to click ‘OK’ [clicks ‘OK’].

Alright. Let’s go back and let’s look at the sidebar panel. We have two parts to this sidebar panel: we have an administration tab and a schema tab. And you’ll see that the administration has some different information on it that I won’t use a whole lot, but the schema list we will look at quite a bit. You won’t have maybe anything here, or maybe some default databases that might have come up with installation, but I’ve got a lot more than you’ll have. But this is just a list of all your schemas or databases that will be seen. You’ll see sometimes they’re merged together—this little straight double arrows pointing to each other [clicks on the arrows] will merge those two tabs together. So you have the administration and the schema lists. So if yours looks like that, that’s fine, or you can separate them out. Again, mainly we’ll be looking at the schema list.

Also notice this double circle arrows—this is the refresh—the double circular arrows will refresh your schema list, which is nice when you run a new SQL script that will add a new database, which we’ll be doing a few times in class. The schema will need to be refreshed by clicking on that [clicks on the refresh button] in order for that new database to even show up in the list. So, we’ll have a lot more databases—you will have a lot more—by the end of the class. And don’t worry about when I say “databases” or “schemas,” these are a list of schemas or databases—for our course they mean the same thing. Technically, you can have many schemas in one database, but none of our databases are going to be that complex. So when I say “schema list,” it’s the same thing as a database list—these are all little databases that we have in here.

Alright, there is one internal schema that should already be in everybody’s because it’s kind of the metadata schema that comes with Workbench, so let’s look at it since we’ll all have that one in common. Mine is showing right now but yours might not be, so it’s called “mysql.” If yours is not showing, go in to Preferences [clicks on the Preferences icon] to the SQL Editor [clicks on ‘SQL Editor’] and check where it says, “Show Meta(data) and Internal Schemas” here [checks the box next to “Show Metadata and Internal Schemas”] and click ‘OK’ [clicks ‘OK’], and then refresh with the double circular arrow [clicks the refresh button]. And you should see this “mysql” internal schema show up. We’re not going to make any changes to it—this is a real schema that is used—so we’re just going to be looking at it. I just wanted to find one that we might all have in common.

Notice the triangle next to the schema lists. If I click on those triangles [clicks on the triangle next to “mysql”], that means there’s something inside of that, and it will expand it out. Now it’s showing “Tables,” “Views,” “Stored Procedures,” and “Functions.” We’ll mainly just be looking at the table portion, and the triangle next to the tables here indicates that there are tables available. So again, you can click the triangle [clicks on the triangle next to “Tables”] to open it up, and you’ll see all the tables in the database. Most of the databases we will see won’t have this many tables.

Now, each of the tables can be expanded—we’ll go down to the very last table here and expand that out [clicks on the triangle next to “user”]—and you’ll see—now we can see “Columns,” “Indexes,” “Foreign Keys,” and “Triggers.” We’re just going to be looking at “Columns” [clicks on the triangle next to “Columns”]—and you can click the triangle again to contract, um, to expand that out, and you will see all of the columns that are available in that table. You can then contract them back up [clicks on the triangle next to “Columns” again] by clicking the triangles again [clicks on the triangles next to “user” and “mysql” to collapse them], and that will also close them all back up.

Also notice next to each schema or database name we have some different icons. So let me open it back up [opens the “mysql” dropdown again] so you can see some of the tables in here. If I look at that same ‘user’ table again and I click on this last database table icon [clicks on the icon to the very right next to ‘user’], which is the last of the three, it will run a select everything from the table query, and the results of the table will show up here. There’s also a useful one here and it’s the wrench icon. If I click that [clicks on the middle icon next to ‘user’], it opens up the table editor for this table. So we can see some of the different design aspects of this table [clicks on the pause button in the bottom-right corner]. And notice as I’ve been doing these things, I’ve gotten new tabs up here in my—new different tabs up here—sub tabs, I should call them. And one showed the results here, and one shows that design of the table. So I can close those out, I can switch back and forth and different things. We’ll talk about—more about those other—those two things that we just saw here in a bit.

But notice also over here as I double-click on the different schema names, they will go bold [demonstrates]. So I double-clicked on ‘mysql’ and it is bold. If I double-click on my ‘employees’ one, it’s now the one that is bold. And that shows which database is currently in use. So it’s going to be bold. We can also write a USE statement, which we’ll do later, that would do the same thing that we can run.

Right-clicking on some of these objects in this schema list [right-clicks on ‘mysql’] will give us some different options here that we’ll talk about in another time [gets rid of the menu that came up after right-clicking]. And also the same with tables [right-clicks on ‘columns’]—if I right-click on them they might have some different options as well [gets rid of the menu].

The last area down here is the information area, and it shows information on whatever object is selected in the area up here. So if I go back to my users table [clicks on ‘user’] and I’ve got that selected, you’ll see that it shows all the columns in the user table. And what’s kind of helpful with this one is that it also shows the data type over here. So this can come in handy as we’re entering data into our database because it shows that data types—which ones are allowed as you’re putting the data in. So it’s pretty helpful.

So there we have the interface—a little bit at the beginning of the interface of Workbench—and we’ll continue on in next video looking a little bit better at the SQL query tab.

[End of Video.]