MYSQL PART I

Database :
A database is a collection of data that is organized so that its contents can be easily accessed, managed and updated. The software used to manage and query a database is known as a Database Management System (DBMS). Then came the concept of Relational Database Management System(RDBMS). Relational database is a database where data are stored in more than one table, each one containing different types of data. The different tables can be linked so that information from the separate files can be used together. This is explained below using an example.

Example :
Consider the Student's personal information and the test marks in a school. Suppose the student's infomation and test results are stored seperately, we can get information regarding the student's personal information like Address from the first file. And also a student's mark at a test can be obtained from the other file.
But consider a situation where we want to get the Address of a student as well as his marks. These things become hard when we have a large volume of data. If we have a studentID stored in two files then we can easily relate the details and recollect them.
In relational databases, a table is a set of data elements(cells) that is organized, defined and stored using a model of horizontal rows and vertical columns. A table has a specified number of columns but can have any number of rows(i.e should have specified structure of date but can have any no. of data). Here every column is known as a field, every row is called as record.
MySQL
MySQL is one of the popular Relational Database Management System. Now let us see an example for a simple database which consists of a table. Consider the same example we took earlier, a student database. The table may have different fields such as StudID, Name, Marks, Address, Phone. These five fields constitutes a table named as student.StudID, Name are fileds and the particular row is a record.
StudID Name Marks Address Phone
1 steve 100 5th cross street 2456987
Now we slightly move to MySQL and see how to create database, use database and remove database
The following will not be needed if you have installed MySQL as a service.
Starting MySQL using command line:
Lets see how to start MySQL from the windows command line manually.
To start the mysqld from the command line, first you should open a console window i.e., Start -> Run.., type cmd or command to open the console window. After opening the console window, enter the path where your MySQL is installed. For example:
C:\> "C:Program Files\MySQL\MySQL Server 4.1\bin"
After giving the path, start the MySQL as given below:
C:\Program Files\MySQL\MySQL Server 4.1\bin> mysqld
The version depends upon the mysql server you have installed. The path may also vary depending on the MySQL installation on your system.
You can stop the MySQL server using the below command:
C:\> "C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqladmin" -u root shutdown
The above commands will help you to start and stop the MySQL server.
Connecting MySQL server :
There are three ways to connect to a MySQL server. They are:
Command Prompt
MySQL Command Line Client
External MySQL Tools
Command Prompt :
You can connect MySQL from your Console window i.e., Start -> Run.., type cmd or command to open the Command prompt window.
After opening the console window, enter the path where your MySQL is installed. For example:
C:\> "C:Program Files\MySQL\MySQL Server 4.1\bin"
After giving the path, enter the below command to connect to MySQL server:
C:\Program Files\MySQL\MySQL Server 4.1\bin> mysql.exe -u root
The path may vary depending on the MySQL installation on your system. Instead of root you can also connect by giving your username.
MySQL Command Line Client :
To connect a MySQL server using the command line client, go to
Start -> Programs -> MySQL -> MySQL Server 4.1 -> MySQL Command Line Client. The command line client window will be opened and enter the password to start your queries.
External MySQL Tools :
You can also get connected to MySQL, using external tool like MySQL Query Browser.
Before going to create a database check whether there is any database with the name you are going to create. Check this by the following SHOW statement:
mysql> show databases;
This query will list the available databases. Please note that MySQL is case insensitive. So you can give the query with different cases also. So show dataBASES; , SHOW dataBASES; will also work.
Once you have confirmed that you don't have a database with the name you intended to create, then you can create your own database by,
mysql> create database sample;
Please note that only in Unix the database name is case sensitive. The above query will create an empty database and it wouldn't contain any tables.
If you want to create tables for a database first you have to select the database. For selecting a database you have to enter the following query :
mysql> USE sample;
Database changed
Here sample is the database you want to select. The USE command dosen't need a semicolon at the end of the query.
You can use the following command to view the current database that you're connected to:
mysql> select database();
+------------+
database()
+------------+
sample
+------------+
Understand the difference between Use database and select database() as the former is selecting a database and the later one is displaying the currently selected one. After selecting the database you can create tables and other such operations.
Note : You have to select the database using the USE statement everytime you are entering into Mysql server or when you want to change the database.
If you type the following query you can see an information like Empty set (ie.,) there are no tables in the selected database.
mysql> show tables;
Empty set (0.00 sec)
Database can be removed or deleted using the DROP statement. The following example deletes the database sample.
mysql> drop database sample;
Query OK, 1 row affected (0.05 sec)
This query will delete the database sample. The query will permanently remove the database.
DROP DATABASE drops all tables in the database and deletes the database. Once the DROP command is used, then we cannot use that database. So, we should be careful with this statement.
Data types :
Definition : Data type is the characteristic of columns and variables that defines what types of data values they can store. The characteristic indicating whether a data item represents a number, date, character string, etc.
Data types are used to indicate the type of the field we are creating into the table. MySQL supports a number of datatypes in three important categories:
Numeric types
Date and Time types
String(Character) types
Before creating a table, identify whether a column should be a text, number, or date type. Each column in a table is made of a data type. The size of the value should be the smallest value depending upon the largest input value.
For example, if the number of students in a school are in hundreds set the column as an unsigned three-digit SMALLINT(allowing for up to 999 values).
We should be concise in inserting a string of five characters long into a char(3) field, the final two characters will be truncated. It is better to set the maximum length for text and number columns as well as other attributes such as UNSIGNED.
Square brackets ('[' and ']') indicate optional parts of type definitions.
Now we slightly move to the overview of MySQL datatypes.
Numeric Datatypes :
The numeric data types are as follows:
BIT TINYINT BOOLEAN SMALLINT MEDIUMINT INT INTEGER BIGINT
FLOAT DOUBLE DECIMAL
Lets see the numeric datatypes briefly.
BIT :
BIT is a synonym for TINYINT(1).
TINYINT[(M)] :
A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.
BOOL, BOOLEAN :
These types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.
SMALLINT :
A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.
MEDIUMINT :
A medium-sized integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.
INT :
A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.
INTEGER :
This type is a synonym for INT.
BIGINT :
A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.
FLOAT :
A small(single-precision) floating-point number. The values are from 3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38.
DOUBLE :
A normal-size(double-precision) floating-point number. The values are from 1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308.
DECIMAL :
The maximum number of digits(M) for DECIMAL is 64.
Date and Time Data Types :
DATE TIME DATETIME TIMESTAMP YEAR
DATE :
A Date. The range is 1000-01-01 to 9999-12-31. The date values are displayed in YYYY-MM-DD format.
TIME :
A Time. The range is -838:59:59 to 838:59:59. The time values are displayed in HH:MM:SS format.
DATETIME :
A Date and Time combination. The range is 1000-01-01 00:00:00 to 9999-12-31 23:59:59. The datetime values are displayed in YYYY-MM-DD HH:MM:SS format.
TIMESTAMP :
A Timestamp. The range is 1970-01-01 00:00:01 UTC to partway through the year 2037. A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation.
YEAR :
A Year. The year values are displayed either in two-digit or four-digit format. The range of values for a four-digit is 1901 to 2155. For two-digit, the range is 70 to 69, representing years from 1970 to 2069.
For all the date and time columns, we can also assign the values using either string or numbers.
String data types :
CHAR VARCHAR TINYTEXT TEXT BLOB MEDIUMTEXT LONGTEXT
BINARY VARBINARY ENUM SET
CHAR() :
It is a fixed length string and is mainly used when the data is not going to vary much in it's length. It ranges from 0 to 255 characters long. While storing CHAR values they are right padded with spaces to the specified length. When retrieving the CHAR values, trailing spaces are removed.
VARCHAR() :
It is a variable length string and is mainly used when the data may vary in length. It ranges from 0 to 255 characters long. VARCHAR values are not padded when they are stored.
TINYTEXT, TINYBLOB :
A string with a maximum length of 255 characters.
TEXT :
TEXT columns are treated as character strings(non-binary strings). It contains a maximum length of 65535 characters.
BLOB :

BLOB stands for Binary Large OBject. It can hold a variable amount of data. BLOB columns are treated as byte strings(binary strings). It contains a maximum length of 65535 characters.
MEDIUMTEXT, MEDIUMBLOB :
It has a maximum length of 16777215 characters.
LONGTEXT, LONGBLOB :
It has a maximum length of 4294967295 characters.
BINARY :
The BINARY is similar to the CHAR type. It stores the value as binary byte strings instead of non-binary character strings.
VARBINARY :
The VARBINARY is similar to the VARCHAR type. It stores the value as binary byte strings instead of non-binary character strings.

ENUM() :
An enumeration. Each column may have one of a specified possible values. It can store only one of the values that are declared in the specified list contained in the ( ) brackets. The ENUM list ranges up to 65535 values.
SET() :
A set. Each column may have more than one of the specified possible values. It contains up to 64 list items and can store more than one choice. SET values are represented internally as integers.
If CHAR and VARCHAR options are used in the same table, then MySQL will automatically change the CHAR into VARCHAR for compatability reasons. The ( ) bracket allows to enter a maximum number of characters that will be used in the column.