Wednesday, May 09, 2007

Learning SQL

This post will help you to kick start SQL learning. There are many tutorials on net that will teach you SQL in 9 minutes. All you learn is only theory. You never get to try out even a single SQL query that you learn. So if you just wanna learn theory and forget it tomorrow you have reached a wrong place. Please close this window now.
So you wanna learn SQL the real way? Smart. I am not going to write a 40 pager that will make you a expert in all statements in SQL. I am just going to help you in setting up something so that you can use the other tutorials on internet to learn the better way. Pause here for a moment and read my other post for setting up MySQL server on your box if you have not - MySQL Setup
Now that you have everything ready on your box we will create a database and a table to play with. After connecting to the server from the command prompt, that is from your MySQL client, follow the following steps.

Enter the commands as below to create a database called learnsql, a table called test and some queries to insert values and select them.

C:\Documents and Settings\prathap>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.0.37-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database learnsql;
Query OK, 1 row affected (0.00 sec)

mysql> use learnsql;
Database changed
mysql> create table test (Id Integer not null, Name varchar(255) not null);
Query OK, 0 rows affected (0.08 sec)

mysql> desc test;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| Id | int(11) | NO | | | |
| Name | varchar(255) | NO | | | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


C:\Documents and Settings\prathap>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.0.37-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database learnsql;
Query OK, 1 row affected (0.00 sec)

mysql> use learnsql;
Database changed
mysql> create table test (Id Integer not null, Name varchar(255) not null);
Query OK, 0 rows affected (0.08 sec)

mysql> desc test;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| Id | int(11) | NO | | | |
| Name | varchar(255) | NO | | | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into test values (1, 'prathap');
Query OK, 1 row affected (0.05 sec)

mysql> select * from test;
+----+---------+
| Id | Name |
+----+---------+
| 1 | prathap |
+----+---------+
1 row in set (0.00 sec)

Now follow the tutorial at sql-tutorial Make sure that you try all the statements in the client.

Tuesday, May 08, 2007

Setup MySQL server in Windows

I aim to help you folks in setting up mysql server in windows box in this post. Read the steps below and of course follow them Before you go ahead make sure you view the presentations at the end of the steps.

Steps:
* Go to http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-5.0.37-win32.zip/from/pick
* Pick the mirror that is closest to you and download the zip file
* Extract the zip file to your preferred location - you can use programs like 7-zip or winzip for this
* Double click the .exe file to install the mysql server
* Follow the wizard to complete the setup
* Enter the new root password if it asks for (make sure to note this down or remember)
* Once the setup is complete, start the server
* Now launch a command prompt - start -> run -> type 'cmd' and enter
* Now type mysql -uroot -p and press enter

Presentation Links:
* Install MySQL Server & configure a instance
* Connect to the server using mysql client and running a query