Mysql tutorial for Beginners Part1

Here I’ll show you all the things that you will need to know to work with mysql programming. So lets get started. I am assuming that you have mysql installed in your PC and we won’t be using any GUI tools, we’ll do all the work in our command prompt which is advisable at the beginning.

                                   How to Connect to your Mysql server.

Click Start>run>type cmd in the run button> and you will see the command promptIn your command prompt type mysql  -u username –p and  press enter and then you will have to give your password to authenticate that you are a valid user. Or directly open your mysql command line and give your password, which is much more easier. I have shown both the diagrams.

Than you will see the following messages “Welcome to the Mysql Monitor…..” You can type help in the command line to see the list of mysql commands.

Type : SHOW databases;  note that all mysql commands should end with a semicolon(;) except for some commands like use ,quit etc…which I’ll describe in my next tutorials. 

You will see there are three default databases in your database server if this is the first time you are accessing it. (Information_Schema, mysql and test) provided by mysql.Now we’ll create our own database. We will create a database called myfriends.  Type  >CREATE  DATABASE  myfriends;

This command (CREATE DATABASE) gives the instruction to the Mysql server to create a new database and instruct it to name it as myfriends . If your command is successful it produces the result Query OK, 1 row affected (0.00 sec). It is now time for us to create some table in our database. Before we create the table we’ll have to use the database (myfriends) to use it we’ll have to give the following statements> USE myfriends; note Mysql does not differentiate between upper case and lower case letters. It recognizes USE and use as the same. Now you are ready to fill up the database.

It produces the result Database changed.  Which means your command is successful.                             Now we will create a table named friend.  To do that type in the statement                              CREATE TABLE friend(f_name VARCHAR(10), nick_name VARCHAR(10), L_name VARCHAR(10), D_O_B DATE, sex CHAR(1), Home_Town VARCHAR(20), Mobile VARCHAR(20)) ; Press enter

You can use DESCRIBE statement to see the column and data type of your table. To do that type:>                                                     DESCRIBE friend;

You will see a list of columns that we have created using the CREATE statement in the previous steps.

You will see the above results…Now its time to load some data in our table….There are two ways to populate the table LOAD & INSERT statements we will be using both the statements to load our table. First we will do it with INSERT statement. Type> INSERT INTO friend VALUES(‘karma’,’chaps’,’dorji’,’1984-10-22’,’m’,’PemaGatshel’,’17824166’);

You will get the result as Query OK. 1 row affected. Note you will have to enter the date as YYYY-MM-DD format.Now to view the data that we have inserted just type the following                                                                                        SELECT * FROM friend;                     

If you have done it correctly then you will see the result as shown above. Now we will use the LOAD statement to populate the database. It is very hectic to insert data one by one using the INSERT statement…because usually in a database you will have to populate it with hundreds of data.  To use the LOAD  statement . First you will have to open a notepad or any other text editor.  Enter your data’s in your text file separated by tabs in the order in which the columns were listed in the CREATE TABLE statement. You can type \N for null values .

Save your file in your hard drive as friend.txt. I have saved the file in My Documents. You can choose any directory you to save your text file. Now we will load the file in our database. To do that you will have to type the following statements.                                    type>LOAD DATA LOCAL INFILE '/My Documents/friend.txt' INTO TABLE friend.

If your command is done right then you will see the above result 8 rows affected. Now let’s see the data in our table. To do that type >SELECT * FROM friend;                                                                                                                   

Then you will see all the list of friends that you have loaded from the file. Now we will learn about data selection and the various ways to select data. We’ve already used (select * from) statement queries. This query retrieves all the data from the table. We will use other statements to retrieve particular rows and columns. You can retrieve particular rows and columns. Suppose you want to retrieve the list of your female friends, you can type this statement                                                    SELECT * FROM friend where sex=’f’;

Here you are presented with the list of female friends that you have in your table.

You can also retrieve particular columns. To do that type the query>                                                                                 SELECT nick_name,Mobile,f_name FROM friend;

You can also use conditions on any column (AND,OR etc….) I’ll write about that in my next tutorial. Hope this tutorial helped you...please make sure to give me a comment so that i can improve my tutorial next time...i'll write more about mysql in my next post..