Introduction of MYSQL-- Part II

Part I of this tutorial gave an introduction to MYSQL

Now, lets go ahead with the Part II of this tutorial which explains in brief some of the basic queries which can be executed

In the previous tutorial we ran the query

SELECT VERSION();

Now here ';' is essential but can be skipped and replaced by '\g'.

So the above query now becomes
SELELECT VERSION() \g

SELECT USER();
displays the current username

Queries can be executed on the command line but consider that you need to maintain a list of all the queries and execute it in one go instead of executing one at a time. This can be done by writing the queries in a single file.
A .sql file will contain the list of all these queries.

On the SQL prompt, simply typing this

mysql < queryscript.sql

will run all the queries which are stored in that .sql file.

SHOW TABLES;

This statement will display list of all the tables in the current database.

SHOW DATABASES;

This statement will display list of all the existing databases.


Now the next step is to create a database This database will act as a host to other tables, even procedures and triggers.

CREATE DATABASE mydb;

This is will create the database mydb.
But this wont set mydb as the default database, so in order to do that, write the following statement

USE mydb;

Now when you write the following

SELECT DATABASE();

It will give you information regarding mydb;

Now for all the examples below consider the following schema of the tables involved.

Table Name--> Employee
emp_id--> primary key---> varchar-->size 3
first_name--> not null----> varchar---> size 15
last_name--> not null----> varchar---> size 15
city--> varchar---> size 15
state--> varchar---> size 15
hiredate--->date
birthdate--->date

Now lets create such a table

On the prompt type the following

CREATE TABLE employee
(
emp_id VARCHAR(3) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(emp_id),
first_name VARCHAR(15) NOT NULL,
last_name VARCHAR(15),
city VARCHAR(15),
state VARCHAR(15),
hiredate DATE NOT NULL,
birthdate DATE
);

Now lets just walk through the table creation statement above.
The above table creates a tabe employee.
emp_id is the primary key. The primary key is the unique entry of the the employees in the table. No two primary keys can be the same and the primary key cannot be NULL also. The primary key is used to search uniquely or identify uniquely a single employee/row in the table.

AUTO_INCREMENT--> this is used to tell MYSQL that to automatically add new values to this column when new entries are made in the table.

VARCHAR(15) this tells that the entry in the table will store values of datatype VARCHAR. Now, VARCHAR is used when we want to store nos as well as text in that position. A unique advantage of VARCHAR over CHAR is that, a VARCHAR of size 15 does not necessarily occupy 15 memory spaces. That size will fluctuate depending upon the actual data which is stored in that position. On the other hand CHAR will occupy 15 positions in the memory irrespective of the value stored.

DATE is another datatype which stores the value by default in CCYY-MM-DD format, where
CC is the century
YY is the year
MM is the month
DD is the date

Now once the table is created, any of the following statements can be used to see the schema of the table.

DESCRIBE employee;
DESC employee;
EXPLAIN employee;
SHOW COLUMNS FROM employee;
SHOW FIELDS FROM employee;

The above statements can also be used in the following manner to filter the schema of the table

SHOW COLUMNS FROM employee LIKE '%date';

This statement will display information regarding the hirdate and birthdate from the employee table.

Now lets go back to the CREATE statement for a second.
We wrote the CREATE statment in the following manner

CREATE TABLE employee
(

-----

);

Now the above statement told MYSQL that the default engine to be used is MyISAM where ISAM stands for Indexed Sequential Access Method

The CREATE statement can also be written as

CREATE TABLE employee
(

)ENGINE=InnoDB;

The InnoDB engine offers referential integrity via foreign keys. This basically means that we can use MYSQL to enforce certain constrains on the relationships that exists between tables.

Now lets create another table

CREATE TABLE salary
(
emp_id VARCHAR(3) NOT NULL,
totsal INT NOT NULL
);

The totsal column has an integer datatype. Ideally that can be a floating point.

Now to insert into the employee table

INSERT INTO employeeVALUES('larry','david','mumbai','maharashtra','2008-09-09', '1980-09-09');

To insert multiple values into the table

INSERT INTO employee VALUES('larry','david','mumbai','maharashtra','2008-09-09', '1980-09-09'),
('david','larry','mumbai','maharashtra','2008-07-09', '1980-06-09');

Now to insert selective values into the table
INSERT INTO employee VALUES(first_name, hiredate) VALUES('david','2008-09-17');

The major operation that is performed on the tables is information retrieval.
The SELECT Query is used for the same

SELECT * FROM employee;
This query will display the entire table with all its rows and columns.

The SELECT query can be broken down as follows

SELECT what to retrieve(column names) from table/s where condition to be satisfied;

SELECT * FROM employee where hiredate='2008-09-09';

SELECT first_name, last_name from employee where emp_id='101';

The SELECT query can be used to retrieve any no. of information from the table in any format as requried by the developer.

This completes Part II of the III part series in introduction to MYSQL.
The next part will deal with more queries with MYSQL

Installing Apache, PHP and MYSQL on Windows

Although Apache, PHP and MYSQL are usually associated with Linux, thus forming LAMP, installing these on windows might be requirement for other programmers as well..
So heres a look at the HOW TO

Installing Apache

Get the Apache download from Apache Download and download the 2.0 version
Once the download is completed, run the installer and do the following

1. Click next unless told otherwise
2. Server Information (mere formality but needs to be filled up):
Network Domain : localhost
Server Name: localhost
Administrator's Email Address: localhost@localhost.com
Install Apache HTTP Server 2.0 programs and shortcuts for : By default for Allusers, on Port 80 as a service- Recommended " will be selected. Keep that selected.
3. Select Typical Instllation.
4. Select the location where you want to install it
5. Once finished. Go to your web browser and type http://localhost in the url.
If properly installed then information about Apache will be displayed confirming that

Additional Tasks
1. The DocumentRoot of Apache is htdocs directory, which hosts all the .php and .html file. Although this can be changed, but you need to mention it in the httpd.conf file which is present in the conf folder of your Apache directory. (By default : c:\program files\Apache Group\Apache2\conf\)
2. To start/stop/restart the server go to Start->Programs->Apache Http Server 2.0 ->Control Apache Server -> Start/Stop/Restart
3. Open the file httpd.conf and type the following
LoadModule php5_module php/php5apache2.dll
AddType application/x-httpd-php .php
AddType application/x-httpd-source .phps
Imp Note Regarding the LoadModule statement previously written. This path mentioned over here should be the path where you have put the php5apache2.dll. In case you create another folder inside the Apache installed directory (c:\program files\Apache Group\Apache2) say PHP then that line will change to the one mentioned above. Else you can skip the php/.



Installing PHP

Download php 5.2 from PHP Download

Once the download completes run on the installer.

1. Click next until told otherwise
2. The default directory of the installation is c:\program files. Can be changed as per the developers interest.
3. Continue clicking next and instllation gets over without breaking any sweat.


Additional jobs

1. Copy the php.ini file from the php installed directory to the windows directory c:\windows
2. Copy the php5apache2.dll from the php installed directory to another



Installing MYSQL
Get the download from MYSQL Download

No sweat again while installing MYSQL
1. Run the MYSQL setup and keep clicking next till you reach a button finish. Then click finish.
2. Considering the default directory in which it is stored (c:\mysql\bin\), go to this directory from dos prompt and type the following
mysql-nt --console
3. Now again open another command prompt and go to the directory c:\mysql\bin and then type the following
mysql
A success message from the console window will confirm the installation.

Thread Prioritization in Java - Set Priority

Prioritization in Java Threads

Java threads run in a virtual memory with a certain priority attached it.
Although thread scheduling varies from VM and OS, thread prioritization can be used for different threads to take control of the execution and thus improve the overall working of the application.

The setPriority() method returns an integer value which contains the current priority of the thread.

In order to explain regarding setPriority() lets take a closer look at the following code:

-------------SetPriority.java--------------------
class ThreadClass extends Thread
{
String threadName;

public ThreadClass(String threadName)
{
this.threadName=threadName;
}

public void run()
{
for(int i=0;i<5;i++)
{
System.out.println("Thread Name: " + Thread.currentThread().getName() + " Thread Priority: " + Thread.currentThread().getPriority());

try
{
Thread.sleep(1000);
}

catch(InterruptedException e)
{
System.out.println(e);
}
}
}

}

public class SetPriority
{
public static void main(String args[])
{
System.out.println("In Main: ");

ThreadClass tc1=new ThreadClass("Thread A");
ThreadClass tc2=new ThreadClass("Thread B");
ThreadClass tc3=new ThreadClass("Thread C");

tc1.setPriority(6);
tc2.setPriority(4);
tc3.setPriority(2);

tc1.start();
tc2.start();
tc3.start();
}
}

Explanation of the code:

The following code contains two classes
SetPriority and ThreadClass

The SetPriority class hosts the main function which is called first when the program runs.

The main function creates three objects of the class ThreadClass (tc1,tc2,tc3) with parameters "Thread A", "Thread B", "Thread C"
These names are used as Thread Names by Thread class when it allocates new Thread objects./

The setPriority() function changes the priority of the thread. It takes an integer argument
Now the setPriority() method can throw two arguments
1. IllegalArgumentException - If the priority is not in the range of MIN_PRIORITY and MAX_PRIORITY
2. SecurityException - If the current thread cannot modify this thread. The SecurityException is raised when the setPriority() calls the checkAccess function internall with no arguments.
Although these exceptions need to be accounted for in larger programs, they can be overlooked for smaller programs like this.
Also for SecurityException to occur, the security manager needs to be installed. In case the security manager rejects the change in priority of the thread, then the exception arises.
By default the security manager is not installed.

The start() function calls the run method and the thread starts executing.

The output of the code will be similar to something like

In Main:
Thread Name: Thread-0 Thread Priority: 6
Thread Name: Thread-2 Thread Priority: 2
Thread Name: Thread-1 Thread Priority: 4
Thread Name: Thread-1 Thread Priority: 4
Thread Name: Thread-2 Thread Priority: 2
Thread Name: Thread-0 Thread Priority: 6
Thread Name: Thread-1 Thread Priority: 4
Thread Name: Thread-2 Thread Priority: 2
Thread Name: Thread-0 Thread Priority: 6
Thread Name: Thread-1 Thread Priority: 4
Thread Name: Thread-2 Thread Priority: 2
Thread Name: Thread-0 Thread Priority: 6
Thread Name: Thread-0 Thread Priority: 6
Thread Name: Thread-2 Thread Priority: 2
Thread Name: Thread-1 Thread Priority: 4


One thing that is worth knowing is in case of multiple threads running, when a thread stops executing and the VM has to decide about giving the next thread control, then it chooses the thread which has the higher priority.

Thread Prioritization in Java - Get Priority

Prioritization in Java Threads

Java threads run in a virtual memory with a certain priority attached it.
Although thread scheduling varies from VM and OS, thread prioritization can be used for different threads to take control of the execution and thus improve the overall working of the application.

The getPriority() method returns an integer value which contains the current priority of the thread.

In order to explain regarding getPriority() lets take a closer look at the following code:

------------- GetPriority.java--------------------

class ThreadClass extends Thread
{
String threadName;

public ThreadClass(String threadName)
{
this.threadName=threadName;
}

public void run()
{
Thread t=new Thread(threadName);

System.out.println("Inside ThreadClass:run()-->Thread Name : " + t.getName() + "\nThread Priority: " + t.getPriority());

try
{
Thread.sleep(2000);
}
catch(InterruptedException e)
{
System.out.println(e);
}
}

}

public class GetPriority
{
public static void main(String args[])
{
System.out.println("In main : Thread name : " + Thread.currentThread().getName() + "\nThread Priority: " + Thread.currentThread().getPriority());

ThreadClass tc=new ThreadClass("thread2");
tc.start();

try
{
Thread.sleep(2000);
}
catch(InterruptedException e)
{
System.out.println(e);
}

System.out.println("In main: Thread Name: " +tc.getName() +"\nThread Priority:" +tc.getPriority());
}
}



Explanation of the code:

The following code contains two classes
GetPriority and ThreadClass

The GetPriority class hosts the main function, which gets called when this java code is run.
The Main thread is the one which runs first

The Thread.currentThread() is a function which returns the reference to the currently executing thread object.
The Thread.getName() returns the name of the currently executing thread.

The main function creates an object (tc) of the ThreadClass and passes and argument "thread2"
This value "thread2" is caught by the constructor of ThreadClass and is used as the name of the thread when it allocates a new thread object.


The output to the code will be:

In main : Thread name : main
Thread Priority: 5
Inside ThreadClass:run()-->Thread Name : thread2
Thread Priority: 5
In main: Thread Name: Thread-0
Thread Priority:5

In case the programmer uses loops inside the functions the output of the code can vary from time to time and sometimes even from machine to machine.