Understanding Relational Databases

A relational database is a database system that allows data to be associated and grouped by common attributes. Basically, a relational database arranges data into tables, where each table is divided into rows and columns of data.

In database, each row in a table represents a data record and each column represents a field.
The terms “row” and “record” are often interchangeable, as are “column” and “field”.

Here, we will illustrate an example of database table. Suppose a company needs to maintain a track of its employees where their personal details will be kept. Create a table called as eBIZEmployees.

EmpId	EmpName	DOB	Address	ContactNo	Designation	Salary	Date of Joining
101	Aman	3/8/1988	Delhi	9586223454	Associate	25000	1/7/2011
102	Ankita	23/4/1990	Faridabad	8988554271	Manager	50000	18/4/2014
103	Sakshi	1/7/1989	Noida	9911548726	HR	30000	8/9/2012
104	Rahul	16/2/1990	Gurgoan	8052467351	Content Writer	20000	21/4/2014
105	Namrata	14/6/1988	Delhi	8854762518	Technical	15000	11/2/2011

In this table, you can see that each row represents a particular set of information about an Employee and each column contain a specific type of data for each Employee.

Databases with SQL

SQL is the Structured Query Language. It is a simple language that is used for communicating with relational databases. SQL allows you to work with any database related task, including creating databases and tables, as well as saving, retrieving, deleting, and updating data in databases.

MySQL Data Types

When you create a database table, you need to specify the type and size of each field. A field is similar to a PHP variable except that it can store only the specified type and size of data in a given field.

MySQL supports three main groups of data types:

• Numeric
• date/time
• String

i)Numeric Data Types

Here, we will see the different numeric data types:

Numeric Data Type	Range of Values
TINYINT	– 128 to 127, or 0 to 255 if UNSIGNED
SMALLINT	– 32768 to 32767, or 0 to 65535 if UNSIGNED
MEDIUMINT	– 8388608 to 8388607, or 0 to 16777215 if UNSIGNED
INT	– 2147483648 to 2147483647, or 0 to 4294967295 if UNSIGNED
BIGINT	– 9223372036854775808 to 9223372036854775807, or 0 to 18446744073709551615 if UNSIGNED
FLOAT	Smallest non – zero value: ± 1.176 × 10 ^ – 38 ; largest value: ± 3.403 × 10 ^38
DOUBLE	Smallest non – zero value: ± 2.225 × 10 ^ – 308 ; largest value: ± 1.798 × 10 ^ 308
DECIMAL( precision ,scale )	Same as DOUBLE, but fixed – point rather than floating – point. Precision specifies the total number of allowed digits, whereas scale specifies how many digits sit to the right of the decimal point.
BIT	0 or 1

ii)Date and Time Data Types

Here, we will see the different Date and Time Data Types:

Date/Time Data Type	Range of Values
DATE	1 Jan 1000 to 31 Dec 9999
DATETIME	Midnight, 1 Jan 1000 to 23:59:59, 31 Dec 9999
TIMESTAMP	00:00:01, 1 Jan 1970 to 03:14:07, 9 Jan 2038, UTC (Universal Coordinated Time)
TIME	– 838:59:59 to 838:59:59
YEAR	YEAR

When you need to specify a literal DATE, DATETIME, or TIMESTAMP value in MySQL, you can use any of the following formats:

• YYYY – MM – DD / YY – MM – DD

 

• YYYY – MM – DD HH:MM:SS / YY – MM – DD HH:MM:SS
• YYYYMMDD / YYMMDD

 

• YYYYMMDDHHMMSS / YYMMDDHHMMSS

iii)String Data Types

Here, we will see the different String Data Types:

String Data Type	Range of Lengths
CHAR( n )	0 – 255 characters
VARCHAR( n )	0 – 65535 characters
BINARY( n )	0 – 255 bytes
VARBINARY( n )	0 – 65535 bytes
TINYTEXT	0 – 255 characters
TEXT	0 – 65535 characters
MEDIUMTEXT	0 – 16777215 characters
LONGTEXT	0 – 4294967295 characters
TINYBLOB	0 – 255 bytes
BLOB	0 – 65535 bytes
MEDIUMBLOB	0 – 16777215 bytes (16MB)
LONGBLOB	0 – 4294967295 bytes (4GB)
ENUM	The field can contain one value from a predefined list of up to 65,535 values
SET	The field can contain zero or more values from a predefined list of up to 64 values

iv) SQL Statements

To actually work with databases and tables, you use SQL statements. Common statements include:

• SELECT — Retrieves data from one or more tables
• INSERT — Inserts data into a table
• REPLACE — Replaces data in a table. If the same record exists in the table, the statement overwrites the record with the new data
• UPDATE — Updates data in a table
• DELETE — Deletes data from a table

Other often – used statements create or modify tables and databases themselves, rather than manipulating the data stored in a table:

• CREATE — Creates a database, table or index
• ALTER — Modifies the structure of a table
• DROP — Wipes out a database or table

The MySQL database system comes with a number of different programs. The two important ones which you will learn here are:

• The MySQL server – This is the database engine itself.
• The MySQL command – line tool You can use this tool to talk directly to the MySQL server so that you can create databases and tables, and add, view, and delete data.