To interact with MySQL Server we can use either MySQL Command Line Tool or any third party tools. The MySQL command line utility automatically gets installed along with MySQL Server.
The MySQL Command Line Tool is character based SQL tool. This means you need to type in all the SQL commands.
There are many party tools which provides GUI interface to interact with MySQL. In these GUI tools you don't need hard code all the SQL commands, these tools will automatically generates all the SQL commands for you in the background. One such popular tool is HiediSQL
We will start our tutorial with MySQL command line tool because we will need to get familiar SQL language and it's syntax first and later on we can utilize GUI tools once we get command over SQL language.
Start MySQL Command Line Tool
Since MySQL CLI is automatically installed along with MySQL Server, click on Start button in Windows and go to MySQL Menu and Select MySQL Command Line Client as shown below
Once you click on it, a new window will open and MySQL client will ask you for the password as shown below
Here enter the "root" password you have chosen at the time of installation and press enter.
Once you type in the correct password you will get a welcome message and other information and "mysql>" prompt as shown below
This is where we need to enter all the SQL commands.
Now lets us give our first command, type in the following at mysql> prompt
This command will list all the databases existing in this MySQL Server. Once you enter this command in client utility you will get a output similar to as shown below
In the output we are seeing 6 databases namely
The mysql, sys, information_schema and performance_schema are MySQL system databases and they are present in every MySQL server. These databases contains all the meta data and essential for running MySQL.
The other databases: sakila and world are example databases which we have installed after installation of MySQL Server in previous chapter.
To create a database in MySQL we have to give CREATE DATABASE command, the syntax of this command is given below
CREATE DATABASE <database_name>
For example if we want to create a new database by name 'sales' then give the following command at mysql> prompt
create database sales;
The name of the database is case insensitive i.e. later you can refer to the database name as 'Sales' or 'SALES' or 'sales', it makes no difference but if you are working under UNIX then these names becomes Case Sensitive and you have to refer to it using the same case as you have used at the time of creation.
Since we are working under Windows, this case sensitiveness is not a problem for us.
When you give the create database command you should get the following output
Once you create a database you can now create tables, views, procedures, triggers etc. in it.
Before you want to work in any database you have to select it by giving USE <database_name> command.
So to select our database 'sales' give the following command
Similarly if you want to see which database you are currently in, you can give the following command
To drop the database you can give the DROP DATABASE <database_name> command.
To drop the just created database 'sales' give the following command
drop database sales;
and you will get the following output
This drop database command will drop the database along with any tables in it. And this command is irreversible, so you should be very careful while issuing this command. The MySQL doesn't even prompt you to confirm before dropping the database.
For our practice, lets create the database 'sales' again by giving the create database command
CREATE DATABASE sales;
Next let's see how to create tables in MySQL