Breaking News
yeni film izle hd film seyret film izle

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.

Check Also

Copying, Renaming, and Deleting Files

PHP also allows you to copy, rename, and delete files. The functions to perform these …