MySQL Basics for beginners

I thought of writing this post as a reply for a comment published in the last post I wrote on connectivity of Java and MySQL. From a previous post I showed you how to install and configure MySQL server on your PC. From this post you can learn some of the basic but essential operations you can do using MySQL. 

First open the command prompt. These are the very first commands you should give.

For normal login to MySQL server, type mysql and press Enter.

If you have a user name but no password, type the following and press Enter. Here my user name is 'root'.

mysql -u root

If you have a username and also a password, type the following and press Enter. Here my user name is 'root'.

mysql -u root -p

Then it will prompt to enter the password. Type the password and press Enter.

Now you have successfully logged into the MySQL server. In your command prompt, the following will appear.

mysql >

Now let's see how to view all the databases currently in the server. The command is,

show databases;

The command to create a new database is the following. Assume my database name is sliit.

create database sliit;

In order to manipulate data inside a database, first we need to go inside the particular database. The command is the following. Assume  my database name is sliit.

use sliit;

Now you are inside the database. You can view the tables inside the database using this command.

show tables;

You can create tables using MySQL statements that you already know. Given below is an example.

CREATE TABLE Students

(

      id INT PRIMARY KEY,

      name VARCHAR(20)

);

To view data in a table, you can easily type a query like this.

SELECT * FROM Students;

I hope now you are familiar with the basics. If you don't like to work in command line, there is a tool called 'MySQL GUI Tools'. It provides a easy to use graphical user interface to handle MySQL operations. 

If you have any questions, feel free to put as comments. 

-Tharindu Edirisinghe-
-SLIIT 10'-

How to install and configure mySQL on Windows

Before going to install mySQL, first lets check whether we have already installed mySQL on the PC. Go to the command prompt and type 'mysql' and press Enter key.

If it is not installed you will get an error message saying 'mysql is not reconnized as an internal or external command, operable program or batch file'. Now lets look at how to install it.

First you need to have the MySQL community server. You can download the installer package from this link. Select the platform as 'Microsoft Windows' and download the MSI installer according to your operating system (either 32 bit or 64 bit). 

Once you have downloaded the MSI package, it will appear like this.

Double click on the installer and run it. The the following window appears. 

Click Next.

select 'I accept the terms in the license agreement' and click Next.

Now you need to select the setup type. I'll select 'Typical and click Next.

Now it shows the destination folder of the installation. Click 'Install'.

It may take few minutes. For the next dialogue boxes appear, click Next.

Now you get the following window.

Tick 'Configure the MySQL Server now' and click 'Finish'.

Click Next.

Select 'Standard Configuration' and click Next.

Make the selections as in the above image and click Next.

Select 'Modify Security Settings' and type the root password. This password is needed when we do modifications to the server so don't forget it. Click Next.

Click 'Execute'.

Click Finish. Now we have successfully installed the mySQL server on the PC.

Go to command prompt and type 'mysql' and press Enter.

Now we are getting an error message because it cannot identify the user. So lets create a user account on the server. First you need to login to the root account. Type the following in the command prompt and press Enter.

mysql -u root -p

It will prompt to enter the root password. Type your root password and press enter.

Now you are in the root account. So lets create a user. The commands are shown in the image below. Don't forget to put semicolons (;) at the end of each statement.

CREATE USER username ;

GRANT ALL ON *.* TO username WITH GRANT OPTION ;

FLUSH PRIVILEGES ;

UPDATE mysql.user SET Password = PASSWORD ('your password') WHERE User = 'username' ;

FLUSH PRIVILEGES ;

exit ;

Here I have granted all the permission for this user also. If not you can grant permissions for a selected set of commands. Setting a password for the account is optional. You need to change the words appearing in red color.

If you didn't set a password for the account, this is the command to login to the account.

mysql -u username

If you set a password this is the command to login.

mysql -u username -p

Then it will prompt to enter the password of the account. Type it and press enter.

Instead of command prompt, you can also use the 'MySQL Command Line Client'. You can find it in 

Start -> All Programs - > MySQL - > MySQL Server 

if you are using Windows 7.

From my next post I'll show you some important commands in mySQL. So keep in touch.

-Tharindu Edirisinghe-
-SLIIT 10-