Hey guys in this video we will be looking at connecting to a database instance for the my SQL Server and also creating our first database. Now post installation we would be greeted with the my SQL workbench screen. If not, then you can always go to the start menu, go down to M and then under my SQL you should be able to identify my SQL workbench. See, the version number may differ based on when you're doing this course. However, if you do not see then you can always go back to the installer and try and add that feature richer activity. If you need any assistance, drop me a line whether by message or QA or comment, and I will be sure to assist you in that regard.

Now, prior to the existence of a graphical user interface way to interact with my SQL, we would have had the command line client which is actually what is learned on. And if we just briefly go over to the command line clients, then we'll see that we're greeted with an interposer give me a Nazi this warning. This, this might be unique to me. But the fact is that you should see a prompt to enter the password. So you can enter that password that you would have entered during the installation process, which will help you gain access to the server. So this is the easiest way to connect to the server, you open up the command line client, you put in the password and bam, you're connected.

And then if we wanted to see certain things on both the server like the databases that are there, then we have the keyword show and the phrase databases. So this command and most other commands, you end with a semi colon in my SQL, and I will press enter. It shows us all of the databases that are in our my SQL Server, right No, you may have more often less depending on if you install the samples or not. But the fact is there are certain key ones that are more like system databases that I recommend you don't tamper with oil, so you might need to reinstall the entire server. So we're just going to do some cursory commands. Firstly, before you can execute, execute any commands on a database, you have to let the editor know whether it's the command line or not that you wish to use a particular database going forward, so we have to type in the keyword use.

And once again, I'm using all caps for all the SQL keywords. And I'm going to use the table sec ILA. So then, when she lets us know that the database in use has changed to sec, ILA, and then I want to see all the tables someone to say show once again, and the key word this time is tables, and then I press enter and then it will list all of the tables currently country In sec ILA. Alright, so I want to create my own database. So to do that, I would just go ahead and say create database and then specify the name. So I'm going to create a database called school, underscore dB, press semicolon, and then we will see that the query Okay, one row affected, but then there's still no way for me to actually verify that, at least not automatically.

So I have to run another command. And by using the up and down buttons on the keyboard, I can actually cycle through all the previously run commands. So I'm bringing back the show databases command and I'm pressing Enter. And there you will see that my newly created database has no content among the many. So that's it. So the command line interface can be used to interact with your my SQL engine.

But then you will find that when you're trying to read through data, it's not very user friendly. And if you write a certain script, or command or a set of commands that you wish to save for later, it's not really very easy to do that with this interface. And so they gave us my SQL workbench, which allows us to interact with our database engine and data in a more user friendly manner. Now, if we wanted to connect to our database, we see that we have a card here that says local instance is this, and they give us a user and they're letting us know that we're connecting to localhost on port 3306. localhost is the universally acceptable term for this computer, meaning whatever computer you're on, right? No, that is your local host. All right.

And it was a case where the mice to a server that we wish to connect to was on another computer, then we would have to create a new connection. So we do that by clicking that little plus sign right here. Was will bring up a dialog box. And then we could just say connection name. And let's say I wanted to connect to a school server. Alright, and then hostname here, by default is 127 dot zero dot zero dot one, which is what local horse resolves.

So you can either use the word local horse, or you can use this IP address if you want to refer to the computer that you're currently on. But if it is that you wanted another machine, then you could use that machines name or that machines IP address on the network in order to connect to it. Also, you want to make sure that the port is correct because sometimes they may change the port number during installation. If you recall, we had the opportunity to do so but we return the default ports. So it presents the default port but just confirm with your new database administrator, what port it is that you're connecting to. And then you would have a username that was probably assigned to your user.

So you want to confirm that the username that you're getting to use to facilitate this connection is correct. And then the password you just click store involves and then you would enter that password. So I'm going to test the connection. And I know this is going to fail but I want you to see exactly what it looks like when it fails. So I put in everything but there and it's will let you know that it failed, your access is denied. However, if you use correct information, then you would see a successful connection being made.

Alright, so then after knowing that you have a successful connection, and you've successfully set up this connection, then you can click OK. And you'll see that this card gets added to your my SQL workbench greeting screen. No, this is important because you know setting Maybe enterprise setting where you're a DBA and you have many servers that you need to administrate, then you can just add as many connections as you need. And it will form this kind of card here on the screen that you can just click and gain access to that set of databases on that particular server or instance, almost immediately. So for moving on, I'm just going to reuse our default connection card that came with our installation. And that's just going to jump me right in you may be prompted for a password. And if you are then just enter that password and you can opt to store it in the vault and you can get that direct access like I just did.

Now on this screen, you'll be greeted with a query editor, a text editor and you may have a toolbar to your right. Both SQL additions so you can actually drag this out or in or you can just choose the dock On on dockets, and our toolbars, to the right to the left and below this editor, but the editor means pretty much solid. But you can close this editor using the little x as it's tabbed. And if you do that by accident, you need a new file, then you can just click this SQL file to bring up a new editor screen. To the left of this editor, you'll see that there's a list of all the schemas or databases and in database lingo, you see schema and database being used interchangeably. So to the left, you'll see a list of all of them currently available to you.

And you'll see school dB, which we actually just created in our command line interface just now. And if we drill down into it, we see that it is empty, there's no nothing is there. Alright. So I'm actually just want to rewrite come on here, create database and I went to spool underscore DB one this time, all right, and we ended with a semicolon. So we can actually anything that we just wrote in our SQL command line, we can actually, we right here in our editor, and then I can just use this lightning bolt to execute. And once I do that, we'll see a log down here showing us what the status of our command is.

And this is saying that it was successful with that green tick. However, to the left, we don't see it listed. So we can always just refresh and that will update our to our database listing accordingly. So on one side, you have to run a command each time to say show databases on this side, we just click refresh, and it will adjust accordingly. So here we just looked at two different interfaces that we could use to execute a CREATE DATABASE script. Alright, so I'm actually going to proceed through this This score is using school underscore dB.

So I'll actually remove DB one eventually. But we'll just Save this script as if we were creating school dB. So like I said, an advantage to using this graphical user interfaces that we can actually save our commands for later use. So in the command line, we couldn't do that we had to execute and press enter and then say goodbye. But in this one, we can actually just proceed to save the file, save the script to file by using this Save icon here, and then we give it a name. So I'm just going to name it create school DB and click Save.

I already have a file by that name. So I'm just going to overwrite it with this. That's fine. And then we'll see that the scripts name is actually represented here. No, I'm actually going to add a comment which is represented by two hyphens. And the comment is essentially dead text meaning whatever you type after the two hyphens and space, make sure you put the space because if you don't With the space that's going to cause an error, right?

So two hyphens and a space, and then I can say this command is creating a T database. Alright? Just get my spelling correct. All right. So that's one way to write comments. Another way is by using the slash an asterisk asterisk star.

So essentially, I'm starting to comment with slash and an asterisk. And I'm ending it with an asterisk and slash. And essentially anything I type no matter how many lines see that will be ignored by the when we press the lightning bolt to execute, these won't be executed. So you have two ways to look at it. Is it a comment or does it have a blue dot because for each command that you write you will see that blue dots appear and you know that this line is about to be executed once you press that lightning bolts. So I made changes here.

I just save So you'll see that as you continue to develop on your scripts, you can actually save your changes. And those will be reflected and it becomes reusable over time. All right. So this is essentially how you go about creating a database. And you can choose either approach but then since we'll be working with workbench going forward, I just recommend you use the workbench approach. And this is how you do it using a script.

I have included a script in the resources for this video so you can review it if needs be. And when we get back here we look at another way that my SQL workbench allows us to create tables and databases