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.